-- ============================================================
-- MIGRATION: Shift System v1
-- Date: 2026-05-11
-- Adds: tb_shift, tb_assignment_shift
-- Alters: tb_absensi, tb_lembur
-- Updates: tb_konfigurasi
-- ============================================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+07:00";

-- ============================================================
-- TABEL MASTER SHIFT
-- ============================================================
CREATE TABLE IF NOT EXISTS `tb_shift` (
  `id_shift`             INT(11)      NOT NULL AUTO_INCREMENT,
  `kode_shift`           VARCHAR(20)  NOT NULL UNIQUE,
  `nama_shift`           VARCHAR(100) NOT NULL,
  `jam_masuk`            TIME         NOT NULL,
  `jam_keluar`           TIME         NOT NULL,
  `toleransi_menit`      INT(3)       NOT NULL DEFAULT 15 COMMENT 'Menit toleransi sebelum dianggap terlambat',
  `lintas_tengah_malam`  TINYINT(1)   NOT NULL DEFAULT 0 COMMENT '1 jika jam_keluar < jam_masuk (shift malam)',
  `is_default`           TINYINT(1)   NOT NULL DEFAULT 0 COMMENT 'Hanya 1 boleh default=1 sebagai fallback',
  `is_active`            TINYINT(1)   NOT NULL DEFAULT 1,
  `created_at`           DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`           DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_shift`),
  KEY `idx_shift_active` (`is_active`),
  KEY `idx_shift_default` (`is_default`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Shift Default sebagai fallback
INSERT INTO `tb_shift` (`kode_shift`, `nama_shift`, `jam_masuk`, `jam_keluar`, `toleransi_menit`, `is_default`, `is_active`)
VALUES ('DEFAULT', 'Normal (08:00 - 17:00)', '08:00:00', '17:00:00', 15, 1, 1);

-- ============================================================
-- TABEL ASSIGNMENT SHIFT (per pegawai ATAU per divisi)
-- Prioritas: PEGAWAI > DIVISI
-- ============================================================
CREATE TABLE IF NOT EXISTS `tb_assignment_shift` (
  `id_assignment`  INT(11)      NOT NULL AUTO_INCREMENT,
  `tipe`           ENUM('PEGAWAI','DIVISI') NOT NULL COMMENT 'PEGAWAI override DIVISI',
  `id_referensi`   INT(11)      NOT NULL COMMENT 'id_pegawai jika PEGAWAI, id_divisi jika DIVISI',
  `id_shift`       INT(11)      NOT NULL,
  `berlaku_mulai`  DATE         NOT NULL,
  `berlaku_selesai` DATE        NULL COMMENT 'NULL = berlaku selamanya',
  `keterangan`     VARCHAR(255) NULL,
  `created_at`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_assignment`),
  KEY `idx_asgn_tipe_ref`    (`tipe`, `id_referensi`),
  KEY `idx_asgn_shift`       (`id_shift`),
  KEY `idx_asgn_berlaku`     (`berlaku_mulai`, `berlaku_selesai`),
  CONSTRAINT `fk_asgn_shift` FOREIGN KEY (`id_shift`) REFERENCES `tb_shift` (`id_shift`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- ALTER tb_absensi: tambah kolom terlambat + sumber
-- ============================================================
ALTER TABLE `tb_absensi`
  ADD COLUMN `is_terlambat`    TINYINT(1)   NOT NULL DEFAULT 0 AFTER `keterangan`,
  ADD COLUMN `menit_terlambat` INT(5)       NOT NULL DEFAULT 0 AFTER `is_terlambat`,
  ADD COLUMN `sumber`          ENUM('IMPORT','MANUAL','AUTO') NOT NULL DEFAULT 'MANUAL' AFTER `menit_terlambat`;

-- Tambah id_import jika belum ada (safe guard)
-- Jalankan hanya jika kolom belum exist:
ALTER TABLE `tb_absensi`
  ADD COLUMN IF NOT EXISTS `id_import` INT(11) NULL AFTER `sumber`;

-- ============================================================
-- ALTER tb_lembur: tambah kolom sumber (MANUAL/AUTO dari shift)
-- ============================================================
ALTER TABLE `tb_lembur`
  ADD COLUMN IF NOT EXISTS `sumber` ENUM('MANUAL','AUTO') NOT NULL DEFAULT 'MANUAL' AFTER `alasan`;

-- ============================================================
-- UPDATE tb_konfigurasi: tambah parameter shift
-- ============================================================
INSERT IGNORE INTO `tb_konfigurasi` (`kunci`, `nilai`, `keterangan`) VALUES
('minimum_lembur_menit', '30', 'Minimum menit lembur yang dihitung otomatis dari selisih jam_keluar aktual vs shift'),
('default_shift_id',     '1',  'ID shift yang digunakan sebagai fallback jika pegawai tidak punya assignment shift');
