-- ============================================================
-- DATABASE: db_penggajian_jig
-- Aplikasi Penggajian JIG - PT Peternakan Ayam
-- Schema Version: 1.0 | Sprint: Fase 1 & 2
-- ============================================================

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

-- ============================================================
-- FASE 1: AUTENTIKASI
-- ============================================================

CREATE TABLE IF NOT EXISTS `tb_users` (
  `id_user`       INT(11)      NOT NULL AUTO_INCREMENT,
  `username`      VARCHAR(50)  NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `nama_lengkap`  VARCHAR(100) NOT NULL,
  `role`          ENUM('SUPERADMIN','ADMIN_PAYROLL','VIEWER') NOT NULL DEFAULT 'ADMIN_PAYROLL',
  `is_active`     TINYINT(1)   NOT NULL DEFAULT 1,
  `last_login`    DATETIME     NULL DEFAULT NULL,
  `created_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default superadmin: password = Admin123!
INSERT INTO `tb_users` (`username`, `password_hash`, `nama_lengkap`, `role`, `is_active`) VALUES
('superadmin', '$2y$10$QuUpzlVAipvCabax7Mbl6u2dVslUdu93Oj8TIdnv60Z0ZpnGxW4KO', 'Super Admin', 'SUPERADMIN', 1),
('admin',      '$2y$10$QuUpzlVAipvCabax7Mbl6u2dVslUdu93Oj8TIdnv60Z0ZpnGxW4KO', 'Admin Payroll', 'ADMIN_PAYROLL', 1);

CREATE TABLE IF NOT EXISTS `tb_login_attempts` (
  `id`          INT(11)      NOT NULL AUTO_INCREMENT,
  `identifier`  VARCHAR(100) NOT NULL COMMENT 'username or IP',
  `ip_address`  VARCHAR(45)  NOT NULL,
  `attempt_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_identifier` (`identifier`),
  KEY `idx_ip`         (`ip_address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `tb_audit_log` (
  `id`          BIGINT(20)   NOT NULL AUTO_INCREMENT,
  `id_user`     INT(11)      NULL,
  `username`    VARCHAR(50)  NULL,
  `aksi`        VARCHAR(100) NOT NULL,
  `modul`       VARCHAR(50)  NOT NULL,
  `keterangan`  TEXT         NULL,
  `ip_address`  VARCHAR(45)  NULL,
  `created_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_modul`   (`modul`),
  KEY `idx_user`    (`id_user`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- FASE 2: MASTER DATA ORGANISASI
-- ============================================================

CREATE TABLE IF NOT EXISTS `tb_divisi` (
  `id_divisi`   INT(11)      NOT NULL AUTO_INCREMENT,
  `kode_divisi` VARCHAR(10)  NOT NULL UNIQUE,
  `nama_divisi` VARCHAR(100) NOT NULL,
  `keterangan`  TEXT         NULL,
  `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_divisi`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `tb_divisi` (`kode_divisi`, `nama_divisi`) VALUES
('PROD',  'Produksi'),
('FARM',  'Peternakan'),
('ADM',   'Administrasi'),
('HRD',   'Human Resource Development'),
('FIN',   'Keuangan'),
('MKT',   'Pemasaran'),
('MAINT', 'Pemeliharaan & Teknik');

CREATE TABLE IF NOT EXISTS `tb_jabatan` (
  `id_jabatan`   INT(11)      NOT NULL AUTO_INCREMENT,
  `id_divisi`    INT(11)      NOT NULL,
  `kode_jabatan` VARCHAR(10)  NOT NULL UNIQUE,
  `nama_jabatan` VARCHAR(100) NOT NULL,
  `level_jabatan` TINYINT(1)  NOT NULL DEFAULT 3 COMMENT '1=Manager 2=Supervisor 3=Staff',
  `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_jabatan`),
  KEY `idx_divisi` (`id_divisi`),
  CONSTRAINT `fk_jabatan_divisi` FOREIGN KEY (`id_divisi`) REFERENCES `tb_divisi` (`id_divisi`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `tb_jabatan` (`id_divisi`, `kode_jabatan`, `nama_jabatan`, `level_jabatan`) VALUES
(1, 'KPROD',   'Kepala Produksi',       1),
(1, 'SPVPROD', 'Supervisor Produksi',   2),
(1, 'STPROD',  'Staff Produksi',        3),
(2, 'KFARM',   'Kepala Peternakan',     1),
(2, 'STFARM',  'Staff Peternakan',      3),
(3, 'KADM',    'Kepala Administrasi',   1),
(3, 'STADM',   'Staff Administrasi',    3),
(4, 'MHRD',    'Manager HRD',           1),
(4, 'STHRD',   'Staff HRD',             3),
(5, 'MFIN',    'Manager Keuangan',      1),
(5, 'STFIN',   'Staff Keuangan',        3);

-- ============================================================
-- FASE 2: MASTER DATA PEGAWAI
-- ============================================================

CREATE TABLE IF NOT EXISTS `tb_pegawai` (
  `id_pegawai`            INT(11)      NOT NULL AUTO_INCREMENT,
  `nip`                   VARCHAR(20)  NOT NULL UNIQUE,
  `nik`                   VARCHAR(16)  NULL,
  `nama_lengkap`          VARCHAR(150) NOT NULL,
  `jenis_kelamin`         ENUM('L','P') NOT NULL DEFAULT 'L',
  `tempat_lahir`          VARCHAR(100) NULL,
  `tanggal_lahir`         DATE         NULL,
  `alamat`                TEXT         NULL,
  `no_telepon`            VARCHAR(20)  NULL,
  `email`                 VARCHAR(100) NULL,
  `id_divisi`             INT(11)      NOT NULL,
  `id_jabatan`            INT(11)      NOT NULL,
  `tanggal_masuk`         DATE         NOT NULL,
  `tanggal_keluar`        DATE         NULL,
  `status_pegawai`        ENUM('AKTIF','NONAKTIF','CUTI_PANJANG') NOT NULL DEFAULT 'AKTIF',
  `npwp`                  VARCHAR(20)  NULL,
  `no_bpjs_kesehatan`     VARCHAR(30)  NULL,
  `no_bpjs_ketenagakerjaan` VARCHAR(30) NULL,
  `no_rekening`           VARCHAR(30)  NULL,
  `nama_bank`             VARCHAR(50)  NULL,
  `ptkp_status`           ENUM('TK0','TK1','TK2','TK3','K0','K1','K2','K3','K_I0','K_I1','K_I2','K_I3') NOT NULL DEFAULT 'TK0',
  `gaji_pokok`            DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `foto`                  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_pegawai`),
  KEY `idx_divisi`  (`id_divisi`),
  KEY `idx_jabatan` (`id_jabatan`),
  KEY `idx_status`  (`status_pegawai`),
  KEY `idx_nip`     (`nip`),
  CONSTRAINT `fk_pegawai_divisi`  FOREIGN KEY (`id_divisi`)  REFERENCES `tb_divisi`  (`id_divisi`)  ON UPDATE CASCADE,
  CONSTRAINT `fk_pegawai_jabatan` FOREIGN KEY (`id_jabatan`) REFERENCES `tb_jabatan` (`id_jabatan`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `tb_pegawai_histori` (
  `id`          BIGINT(20)   NOT NULL AUTO_INCREMENT,
  `id_pegawai`  INT(11)      NOT NULL,
  `jenis_perubahan` VARCHAR(50) NOT NULL COMMENT 'JABATAN|DIVISI|GAJI|STATUS|DATA_POKOK',
  `nilai_lama`  TEXT         NULL,
  `nilai_baru`  TEXT         NULL,
  `keterangan`  VARCHAR(255) NULL,
  `id_user`     INT(11)      NULL,
  `created_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_pegawai` (`id_pegawai`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- FASE 2: PARAMETER PENGGAJIAN
-- ============================================================

CREATE TABLE IF NOT EXISTS `tb_komponen_gaji` (
  `id_komponen`   INT(11)      NOT NULL AUTO_INCREMENT,
  `kode_komponen` VARCHAR(20)  NOT NULL UNIQUE,
  `nama_komponen` VARCHAR(100) NOT NULL,
  `jenis`         ENUM('TUNJANGAN','POTONGAN') NOT NULL,
  `tipe_nilai`    ENUM('NOMINAL','PERSEN') NOT NULL DEFAULT 'NOMINAL',
  `cara_hitung`   ENUM('FLAT','PER_HARI','FORMULA') NOT NULL DEFAULT 'FLAT',
  `nilai_default` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `formula_expression` TEXT NULL,
  `is_wajib`      TINYINT(1)   NOT NULL DEFAULT 0 COMMENT '1=otomatis, 0=manual',
  `is_active`     TINYINT(1)   NOT NULL DEFAULT 1,
  `urutan`        TINYINT(3)   NOT NULL DEFAULT 10,
  `created_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_komponen`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `tb_komponen_gaji` (`kode_komponen`, `nama_komponen`, `jenis`, `tipe_nilai`, `cara_hitung`, `is_wajib`, `urutan`) VALUES
('GAPOK',    'Gaji Pokok',              'TUNJANGAN', 'NOMINAL', 'FLAT', 1, 1),
('TJ_MAKAN', 'Tunjangan Makan',         'TUNJANGAN', 'NOMINAL', 'FLAT', 0, 2),
('TJ_TRANS', 'Tunjangan Transport',     'TUNJANGAN', 'NOMINAL', 'FLAT', 0, 3),
('TJ_POSISI','Tunjangan Jabatan',       'TUNJANGAN', 'NOMINAL', 'FLAT', 0, 4),
('LEMBUR',   'Upah Lembur',             'TUNJANGAN', 'NOMINAL', 'FLAT', 0, 5),
('POT_BPJSK','Potongan BPJS Kesehatan', 'POTONGAN',  'PERSEN',  'FLAT', 1, 10),
('POT_BPJSTK','Potongan BPJS TK JHT',  'POTONGAN',  'PERSEN',  'FLAT', 1, 11),
('POT_PPH21','Potongan PPh 21',         'POTONGAN',  'NOMINAL', 'FLAT', 1, 12),
('POT_KASBON','Potongan Kasbon',        'POTONGAN',  'NOMINAL', 'FLAT', 0, 13);

CREATE TABLE IF NOT EXISTS `tb_konfigurasi` (
  `kunci`     VARCHAR(50)  NOT NULL,
  `nilai`     TEXT         NOT NULL,
  `keterangan` VARCHAR(200) NULL,
  `updated_at` DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`kunci`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `tb_konfigurasi` (`kunci`, `nilai`, `keterangan`) VALUES
('nama_aplikasi',    'PAYROLL JIG',             'Nama aplikasi pada judul dan menu'),
('nama_perusahaan',  'PT Peternakan Ayam JIG', 'Nama perusahaan di slip gaji'),
('alamat_perusahaan','Jl. Contoh No. 1, Kota',  'Alamat perusahaan'),
('logo_perusahaan',  '',                          'Path file logo'),
('favicon_aplikasi', '',                          'Path file favicon aplikasi'),
('jam_kerja_per_hari','8',                         'Jam kerja normal per hari'),
('tarif_lembur_per_jam','0',                       'Nominal lembur per jam (0=otomatis dari gaji pokok)'),
('payroll_mode_periode','CALENDAR',               'Mode periode payroll: CALENDAR atau CUSTOM'),
('payroll_tanggal_gajian','1',                    'Tanggal gajian untuk mode CUSTOM (1-31; nilai 1 = mode bulanan penuh)');


-- --------------------------------------------------------
-- Sprint 3: Hari Libur & Log Import Pegawai
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `tb_hari_libur` (
  `id`          INT(11)      NOT NULL AUTO_INCREMENT,
  `tanggal`     DATE         NOT NULL,
  `nama_libur`  VARCHAR(100) NOT NULL,
  `jenis`       ENUM('NASIONAL','PERUSAHAAN') NOT NULL DEFAULT 'NASIONAL',
  `created_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_tanggal` (`tanggal`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `tb_import_pegawai_log` (
  `id_import`   INT(11)      NOT NULL AUTO_INCREMENT,
  `nama_file`   VARCHAR(255) NOT NULL,
  `total_baris` INT(11)      NOT NULL DEFAULT 0,
  `berhasil`    INT(11)      NOT NULL DEFAULT 0,
  `gagal`       INT(11)      NOT NULL DEFAULT 0,
  `status`      ENUM('SUCCESS','PARTIAL','FAILED') NOT NULL DEFAULT 'SUCCESS',
  `detail_log`  TEXT         NULL,
  `id_user`     INT(11)      NULL,
  `created_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_import`),
  KEY `fk_import_user` (`id_user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- --------------------------------------------------------
-- Sprint 4: Absensi, Izin/Cuti, Lembur
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `tb_absensi` (
  `id_absensi`   BIGINT(20)   NOT NULL AUTO_INCREMENT,
  `id_pegawai`   INT(11)      NOT NULL,
  `tanggal`      DATE         NOT NULL,
  `jam_masuk`    TIME         NULL,
  `jam_keluar`   TIME         NULL,
  `status`       ENUM('MASUK','ALPHA','SAKIT','CUTI','LIBUR','LEMBUR') NOT NULL DEFAULT 'MASUK',
  `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_absensi`),
  UNIQUE KEY `uq_absensi_pegawai_tanggal` (`id_pegawai`,`tanggal`),
  KEY `idx_absensi_tanggal` (`tanggal`),
  KEY `idx_absensi_status` (`status`),
  CONSTRAINT `fk_absensi_pegawai` FOREIGN KEY (`id_pegawai`) REFERENCES `tb_pegawai` (`id_pegawai`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `tb_import_absensi_log` (
  `id_import`    INT(11)      NOT NULL AUTO_INCREMENT,
  `nama_file`    VARCHAR(255) NOT NULL,
  `total_baris`  INT(11)      NOT NULL DEFAULT 0,
  `berhasil`     INT(11)      NOT NULL DEFAULT 0,
  `gagal`        INT(11)      NOT NULL DEFAULT 0,
  `status`       ENUM('SUCCESS','PARTIAL','FAILED') NOT NULL DEFAULT 'SUCCESS',
  `detail_log`   TEXT         NULL,
  `id_user`      INT(11)      NULL,
  `created_at`   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_import`),
  KEY `idx_import_absensi_created` (`created_at`),
  KEY `fk_import_absensi_user` (`id_user`),
  CONSTRAINT `fk_import_absensi_user` FOREIGN KEY (`id_user`) REFERENCES `tb_users` (`id_user`) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `tb_izin_cuti` (
  `id_izin`         BIGINT(20)   NOT NULL AUTO_INCREMENT,
  `id_pegawai`      INT(11)      NOT NULL,
  `tipe`            ENUM('IZIN','SAKIT','CUTI_TAHUNAN','CUTI_BERSAMA','CUTI_KHUSUS') NOT NULL,
  `tanggal_mulai`   DATE         NOT NULL,
  `tanggal_selesai` DATE         NOT NULL,
  `durasi_hari`     INT(11)      NOT NULL DEFAULT 1,
  `alasan`          TEXT         NOT NULL,
  `status`          ENUM('DRAFT','APPROVED','REJECTED') NOT NULL DEFAULT 'DRAFT',
  `catatan`         TEXT         NULL,
  `approved_by`     INT(11)      NULL,
  `approved_at`     DATETIME     NULL,
  `created_at`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_izin`),
  KEY `idx_izin_pegawai` (`id_pegawai`),
  KEY `idx_izin_status` (`status`),
  KEY `idx_izin_tanggal` (`tanggal_mulai`,`tanggal_selesai`),
  KEY `fk_izin_approved_by` (`approved_by`),
  CONSTRAINT `fk_izin_pegawai` FOREIGN KEY (`id_pegawai`) REFERENCES `tb_pegawai` (`id_pegawai`) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT `fk_izin_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `tb_users` (`id_user`) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `tb_lembur` (
  `id_lembur`      BIGINT(20)   NOT NULL AUTO_INCREMENT,
  `id_pegawai`     INT(11)      NOT NULL,
  `tanggal`        DATE         NOT NULL,
  `jam_mulai`      TIME         NOT NULL,
  `jam_selesai`    TIME         NOT NULL,
  `durasi_jam`     DECIMAL(5,2) NOT NULL DEFAULT 0.50,
  `alasan`         TEXT         NOT NULL,
  `status`         ENUM('DRAFT','APPROVED','REJECTED') NOT NULL DEFAULT 'DRAFT',
  `catatan`        TEXT         NULL,
  `approved_by`    INT(11)      NULL,
  `approved_at`    DATETIME     NULL,
  `created_at`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_lembur`),
  KEY `idx_lembur_pegawai` (`id_pegawai`),
  KEY `idx_lembur_status` (`status`),
  KEY `idx_lembur_tanggal` (`tanggal`),
  KEY `fk_lembur_approved_by` (`approved_by`),
  CONSTRAINT `fk_lembur_pegawai` FOREIGN KEY (`id_pegawai`) REFERENCES `tb_pegawai` (`id_pegawai`) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT `fk_lembur_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `tb_users` (`id_user`) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- --------------------------------------------------------
-- Sprint 5: Engine Payroll
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `tb_payroll_periode` (
  `id_periode`      INT(11)      NOT NULL AUTO_INCREMENT,
  `kode_periode`    VARCHAR(10)  NOT NULL,
  `bulan`           TINYINT(2)   NOT NULL,
  `tahun`           SMALLINT(4)  NOT NULL,
  `tanggal_mulai`   DATE         NOT NULL,
  `tanggal_selesai` DATE         NOT NULL,
  `status`          ENUM('DRAFT','PROCESSED','APPROVED','LOCKED') NOT NULL DEFAULT 'DRAFT',
  `catatan`         VARCHAR(255) NULL,
  `processed_by`    INT(11)      NULL,
  `processed_at`    DATETIME     NULL,
  `approved_by`     INT(11)      NULL,
  `approved_at`     DATETIME     NULL,
  `locked_by`       INT(11)      NULL,
  `locked_at`       DATETIME     NULL,
  `created_at`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_periode`),
  UNIQUE KEY `uq_payroll_periode_bulan_tahun` (`bulan`,`tahun`),
  UNIQUE KEY `uq_payroll_periode_kode` (`kode_periode`),
  KEY `idx_payroll_periode_status` (`status`),
  CONSTRAINT `fk_periode_processed_by` FOREIGN KEY (`processed_by`) REFERENCES `tb_users` (`id_user`) ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT `fk_periode_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `tb_users` (`id_user`) ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT `fk_periode_locked_by` FOREIGN KEY (`locked_by`) REFERENCES `tb_users` (`id_user`) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `tb_payroll` (
  `id_payroll`       BIGINT(20)   NOT NULL AUTO_INCREMENT,
  `id_periode`       INT(11)      NOT NULL,
  `id_pegawai`       INT(11)      NOT NULL,
  `gaji_pokok`       DECIMAL(15,2) NOT NULL DEFAULT 0,
  `total_tunjangan`  DECIMAL(15,2) NOT NULL DEFAULT 0,
  `total_potongan`   DECIMAL(15,2) NOT NULL DEFAULT 0,
  `total_lembur`     DECIMAL(15,2) NOT NULL DEFAULT 0,
  `gaji_bruto`       DECIMAL(15,2) NOT NULL DEFAULT 0,
  `gaji_neto`        DECIMAL(15,2) NOT NULL DEFAULT 0,
  `status`           ENUM('PROCESSED','APPROVED','LOCKED') NOT NULL DEFAULT 'PROCESSED',
  `created_at`       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_payroll`),
  UNIQUE KEY `uq_payroll_periode_pegawai` (`id_periode`,`id_pegawai`),
  KEY `idx_payroll_periode` (`id_periode`),
  KEY `idx_payroll_pegawai` (`id_pegawai`),
  CONSTRAINT `fk_payroll_periode` FOREIGN KEY (`id_periode`) REFERENCES `tb_payroll_periode` (`id_periode`) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT `fk_payroll_pegawai` FOREIGN KEY (`id_pegawai`) REFERENCES `tb_pegawai` (`id_pegawai`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `tb_payroll_komponen` (
  `id_detail`       BIGINT(20)   NOT NULL AUTO_INCREMENT,
  `id_payroll`      BIGINT(20)   NOT NULL,
  `id_komponen`     INT(11)      NULL,
  `nama_komponen`   VARCHAR(100) NOT NULL,
  `jenis`           ENUM('TUNJANGAN','POTONGAN') NOT NULL,
  `tipe_nilai`      ENUM('NOMINAL','PERSEN') NOT NULL DEFAULT 'NOMINAL',
  `nilai`           DECIMAL(15,2) NOT NULL DEFAULT 0,
  `sumber`          VARCHAR(30)  NOT NULL DEFAULT 'SYSTEM_DEFAULT',
  `created_at`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_detail`),
  KEY `idx_payroll_detail_payroll` (`id_payroll`),
  KEY `idx_payroll_detail_komponen` (`id_komponen`),
  CONSTRAINT `fk_payroll_detail_payroll` FOREIGN KEY (`id_payroll`) REFERENCES `tb_payroll` (`id_payroll`) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT `fk_payroll_detail_komponen` FOREIGN KEY (`id_komponen`) REFERENCES `tb_komponen_gaji` (`id_komponen`) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;