-- ============================================================
-- PENGGAJIAN SGI - Proposed MySQL schema
-- Derived from the VB6/Access source plus the agreed web rules.
-- NOTE: This is a design/migration baseline, not a direct dump of SGI.mdb.
-- ============================================================

CREATE TABLE roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(30) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) ENGINE=InnoDB;

CREATE TABLE cabang (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    kode VARCHAR(20) NOT NULL UNIQUE,
    nama VARCHAR(150) NOT NULL,
    alamat TEXT NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    radius_presensi_meter INT UNSIGNED NULL,
    aktif TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) ENGINE=InnoDB;

CREATE TABLE bagian (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    kode VARCHAR(20) NOT NULL UNIQUE,
    nama VARCHAR(150) NOT NULL,
    aktif TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) ENGINE=InnoDB;

CREATE TABLE jabatan (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    kode VARCHAR(20) NOT NULL UNIQUE,
    nama VARCHAR(150) NOT NULL,
    bagian_id BIGINT UNSIGNED NULL,
    tunjangan_default DECIMAL(15,2) NOT NULL DEFAULT 0,
    transport_default DECIMAL(15,2) NOT NULL DEFAULT 0,
    aktif TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    CONSTRAINT fk_jabatan_bagian FOREIGN KEY (bagian_id) REFERENCES bagian(id)
) ENGINE=InnoDB;

CREATE TABLE pegawai (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nip VARCHAR(30) NOT NULL UNIQUE,
    nama VARCHAR(150) NOT NULL,
    tempat_lahir VARCHAR(100) NULL,
    tanggal_lahir DATE NULL,
    alamat TEXT NULL,
    jk VARCHAR(20) NULL,
    agama VARCHAR(30) NULL,
    status_pernikahan VARCHAR(30) NULL,
    pendidikan VARCHAR(50) NULL,
    status_pegawai VARCHAR(30) NOT NULL DEFAULT 'AKTIF',
    tanggal_masuk DATE NOT NULL,
    tanggal_keluar DATE NULL,
    alasan_keluar TEXT NULL,
    cabang_id BIGINT UNSIGNED NOT NULL,
    bagian_id BIGINT UNSIGNED NULL,
    jabatan_id BIGINT UNSIGNED NULL,
    gaji_pokok DECIMAL(15,2) NOT NULL DEFAULT 0,
    tunjangan DECIMAL(15,2) NOT NULL DEFAULT 0,
    transport DECIMAL(15,2) NOT NULL DEFAULT 0,
    no_telp VARCHAR(30) NULL,
    nik_ktp VARCHAR(30) NULL,
    foto VARCHAR(255) NULL,
    aktif TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    CONSTRAINT fk_pegawai_cabang FOREIGN KEY (cabang_id) REFERENCES cabang(id),
    CONSTRAINT fk_pegawai_bagian FOREIGN KEY (bagian_id) REFERENCES bagian(id),
    CONSTRAINT fk_pegawai_jabatan FOREIGN KEY (jabatan_id) REFERENCES jabatan(id),
    INDEX idx_pegawai_nama (nama),
    INDEX idx_pegawai_cabang_status (cabang_id, status_pegawai)
) ENGINE=InnoDB;

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    pegawai_id BIGINT UNSIGNED NULL UNIQUE,
    username VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role_code VARCHAR(30) NOT NULL,
    cabang_id BIGINT UNSIGNED NULL,
    aktif TINYINT(1) NOT NULL DEFAULT 1,
    last_login_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    CONSTRAINT fk_users_pegawai FOREIGN KEY (pegawai_id) REFERENCES pegawai(id) ON DELETE SET NULL,
    CONSTRAINT fk_users_cabang FOREIGN KEY (cabang_id) REFERENCES cabang(id),
    INDEX idx_users_role_cabang (role_code, cabang_id)
) ENGINE=InnoDB;

CREATE TABLE settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    kode VARCHAR(100) NOT NULL,
    nama VARCHAR(150) NOT NULL,
    tipe VARCHAR(30) NOT NULL DEFAULT 'STRING',
    nilai TEXT NULL,
    berlaku_mulai DATE NULL,
    berlaku_sampai DATE NULL,
    aktif TINYINT(1) NOT NULL DEFAULT 1,
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_settings_kode_periode (kode, berlaku_mulai, berlaku_sampai)
) ENGINE=InnoDB;

CREATE TABLE jadwal_kerja (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(100) NOT NULL,
    jam_masuk TIME NOT NULL,
    jam_pulang TIME NOT NULL,
    berlaku_mulai DATE NOT NULL,
    berlaku_sampai DATE NULL,
    toleransi_menit INT UNSIGNED NOT NULL DEFAULT 0,
    aktif TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) ENGINE=InnoDB;

CREATE TABLE aturan_terlambat (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    menit_min INT UNSIGNED NOT NULL,
    menit_max INT UNSIGNED NULL,
    nilai_potongan DECIMAL(15,2) NOT NULL DEFAULT 0,
    berlaku_mulai DATE NOT NULL,
    berlaku_sampai DATE NULL,
    aktif TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) ENGINE=InnoDB;

CREATE TABLE hari_libur (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tanggal DATE NOT NULL UNIQUE,
    nama VARCHAR(150) NOT NULL,
    jenis VARCHAR(30) NULL,
    aktif TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) ENGINE=InnoDB;

CREATE TABLE mutasi_pegawai (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    pegawai_id BIGINT UNSIGNED NOT NULL,
    tanggal DATE NOT NULL,
    cabang_lama_id BIGINT UNSIGNED NULL,
    cabang_baru_id BIGINT UNSIGNED NULL,
    bagian_lama_id BIGINT UNSIGNED NULL,
    bagian_baru_id BIGINT UNSIGNED NULL,
    jabatan_lama_id BIGINT UNSIGNED NULL,
    jabatan_baru_id BIGINT UNSIGNED NULL,
    keterangan TEXT NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (pegawai_id) REFERENCES pegawai(id),
    FOREIGN KEY (cabang_lama_id) REFERENCES cabang(id),
    FOREIGN KEY (cabang_baru_id) REFERENCES cabang(id),
    FOREIGN KEY (bagian_lama_id) REFERENCES bagian(id),
    FOREIGN KEY (bagian_baru_id) REFERENCES bagian(id),
    FOREIGN KEY (jabatan_lama_id) REFERENCES jabatan(id),
    FOREIGN KEY (jabatan_baru_id) REFERENCES jabatan(id),
    INDEX idx_mutasi_pegawai_tanggal (pegawai_id, tanggal)
) ENGINE=InnoDB;

CREATE TABLE resign (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    pegawai_id BIGINT UNSIGNED NOT NULL,
    tanggal DATE NOT NULL,
    alasan TEXT NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (pegawai_id) REFERENCES pegawai(id),
    INDEX idx_resign_tanggal (tanggal)
) ENGINE=InnoDB;

CREATE TABLE pengajuan_pegawai (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    pegawai_id BIGINT UNSIGNED NULL,
    cabang_id BIGINT UNSIGNED NOT NULL,
    jenis VARCHAR(50) NOT NULL,
    data_pengajuan JSON NULL,
    alasan TEXT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'MENUNGGU',
    diajukan_oleh BIGINT UNSIGNED NULL,
    approved_by BIGINT UNSIGNED NULL,
    approved_at TIMESTAMP NULL,
    approval_note TEXT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (pegawai_id) REFERENCES pegawai(id),
    FOREIGN KEY (cabang_id) REFERENCES cabang(id),
    INDEX idx_pengajuan_pegawai_status (cabang_id, status)
) ENGINE=InnoDB;

CREATE TABLE pengajuan_pinjaman (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    kode VARCHAR(40) NOT NULL UNIQUE,
    pegawai_id BIGINT UNSIGNED NOT NULL,
    cabang_id BIGINT UNSIGNED NOT NULL,
    tanggal DATE NOT NULL,
    jumlah DECIMAL(15,2) NOT NULL,
    tenor INT UNSIGNED NOT NULL,
    alasan TEXT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'MENUNGGU',
    diajukan_oleh BIGINT UNSIGNED NULL,
    approved_by BIGINT UNSIGNED NULL,
    approved_at TIMESTAMP NULL,
    approval_note TEXT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (pegawai_id) REFERENCES pegawai(id),
    FOREIGN KEY (cabang_id) REFERENCES cabang(id),
    INDEX idx_pengajuan_pinjaman (cabang_id, tanggal, status)
) ENGINE=InnoDB;

CREATE TABLE pengajuan_kasbon (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    kode VARCHAR(40) NOT NULL UNIQUE,
    pegawai_id BIGINT UNSIGNED NOT NULL,
    cabang_id BIGINT UNSIGNED NOT NULL,
    tanggal DATE NOT NULL,
    jumlah DECIMAL(15,2) NOT NULL,
    alasan TEXT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'MENUNGGU',
    diajukan_oleh BIGINT UNSIGNED NULL,
    approved_by BIGINT UNSIGNED NULL,
    approved_at TIMESTAMP NULL,
    approval_note TEXT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (pegawai_id) REFERENCES pegawai(id),
    FOREIGN KEY (cabang_id) REFERENCES cabang(id)
) ENGINE=InnoDB;

CREATE TABLE pengajuan_kendaraan (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    kode VARCHAR(40) NOT NULL UNIQUE,
    pegawai_id BIGINT UNSIGNED NOT NULL,
    cabang_id BIGINT UNSIGNED NOT NULL,
    tanggal DATE NOT NULL,
    jumlah DECIMAL(15,2) NOT NULL,
    tenor INT UNSIGNED NOT NULL,
    alasan TEXT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'MENUNGGU',
    diajukan_oleh BIGINT UNSIGNED NULL,
    approved_by BIGINT UNSIGNED NULL,
    approved_at TIMESTAMP NULL,
    approval_note TEXT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (pegawai_id) REFERENCES pegawai(id),
    FOREIGN KEY (cabang_id) REFERENCES cabang(id)
) ENGINE=InnoDB;

CREATE TABLE pinjaman (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    kode VARCHAR(40) NOT NULL UNIQUE,
    pegawai_id BIGINT UNSIGNED NOT NULL,
    tanggal DATE NOT NULL,
    periode TINYINT UNSIGNED NULL,
    tahun SMALLINT UNSIGNED NULL,
    jumlah DECIMAL(15,2) NOT NULL,
    tenor INT UNSIGNED NOT NULL,
    angsuran_normal DECIMAL(15,2) NOT NULL,
    sisa DECIMAL(15,2) NOT NULL,
    alasan TEXT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'BERJALAN',
    sumber_pengajuan_id BIGINT UNSIGNED NULL,
    created_by BIGINT UNSIGNED NULL,
    approved_by BIGINT UNSIGNED NULL,
    approved_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (pegawai_id) REFERENCES pegawai(id),
    INDEX idx_pinjaman_pegawai_status (pegawai_id, status, sisa)
) ENGINE=InnoDB;

CREATE TABLE pembayaran_pinjaman (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    pinjaman_id BIGINT UNSIGNED NOT NULL,
    payroll_id BIGINT UNSIGNED NULL,
    periode TINYINT UNSIGNED NOT NULL,
    tahun SMALLINT UNSIGNED NOT NULL,
    jumlah_normal DECIMAL(15,2) NOT NULL DEFAULT 0,
    jumlah_dipotong DECIMAL(15,2) NOT NULL DEFAULT 0,
    jumlah_ditunda DECIMAL(15,2) NOT NULL DEFAULT 0,
    sisa_setelah DECIMAL(15,2) NOT NULL DEFAULT 0,
    status VARCHAR(30) NOT NULL DEFAULT 'NORMAL',
    alasan_penyesuaian TEXT NULL,
    adjusted_by BIGINT UNSIGNED NULL,
    adjusted_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (pinjaman_id) REFERENCES pinjaman(id),
    INDEX idx_bayar_pinjaman_periode (pinjaman_id, tahun, periode)
) ENGINE=InnoDB;

CREATE TABLE kasbon (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    kode VARCHAR(40) NOT NULL UNIQUE,
    pegawai_id BIGINT UNSIGNED NOT NULL,
    tanggal DATE NOT NULL,
    jumlah DECIMAL(15,2) NOT NULL DEFAULT 0,
    periode TINYINT UNSIGNED NULL,
    tahun SMALLINT UNSIGNED NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'BELUM_DIPOTONG',
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (pegawai_id) REFERENCES pegawai(id),
    INDEX idx_kasbon_pegawai_status (pegawai_id, status)
) ENGINE=InnoDB;

CREATE TABLE kendaraan (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    kode VARCHAR(40) NOT NULL UNIQUE,
    pegawai_id BIGINT UNSIGNED NOT NULL,
    tanggal DATE NOT NULL,
    periode TINYINT UNSIGNED NULL,
    tahun SMALLINT UNSIGNED NULL,
    jumlah DECIMAL(15,2) NOT NULL,
    tenor INT UNSIGNED NOT NULL,
    angsuran_normal DECIMAL(15,2) NOT NULL,
    sisa DECIMAL(15,2) NOT NULL,
    alasan TEXT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'BERJALAN',
    sumber_pengajuan_id BIGINT UNSIGNED NULL,
    created_by BIGINT UNSIGNED NULL,
    approved_by BIGINT UNSIGNED NULL,
    approved_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (pegawai_id) REFERENCES pegawai(id),
    INDEX idx_kendaraan_pegawai_status (pegawai_id, status, sisa)
) ENGINE=InnoDB;

CREATE TABLE pembayaran_kendaraan (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    kendaraan_id BIGINT UNSIGNED NOT NULL,
    payroll_id BIGINT UNSIGNED NULL,
    periode TINYINT UNSIGNED NOT NULL,
    tahun SMALLINT UNSIGNED NOT NULL,
    jumlah_normal DECIMAL(15,2) NOT NULL DEFAULT 0,
    jumlah_dipotong DECIMAL(15,2) NOT NULL DEFAULT 0,
    jumlah_ditunda DECIMAL(15,2) NOT NULL DEFAULT 0,
    sisa_setelah DECIMAL(15,2) NOT NULL DEFAULT 0,
    status VARCHAR(30) NOT NULL DEFAULT 'NORMAL',
    alasan_penyesuaian TEXT NULL,
    adjusted_by BIGINT UNSIGNED NULL,
    adjusted_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (kendaraan_id) REFERENCES kendaraan(id),
    INDEX idx_bayar_kendaraan_periode (kendaraan_id, tahun, periode)
) ENGINE=InnoDB;

CREATE TABLE punishments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    kode VARCHAR(40) NOT NULL UNIQUE,
    pegawai_id BIGINT UNSIGNED NOT NULL,
    tanggal DATE NOT NULL,
    jenis VARCHAR(100) NOT NULL,
    keterangan TEXT NULL,
    nilai DECIMAL(15,2) NOT NULL,
    metode_potongan VARCHAR(30) NOT NULL DEFAULT 'CICILAN',
    jumlah_potongan_per_bulan DECIMAL(15,2) NOT NULL DEFAULT 0,
    jumlah_cicilan INT UNSIGNED NULL,
    sisa DECIMAL(15,2) NOT NULL,
    periode_mulai TINYINT UNSIGNED NULL,
    tahun_mulai SMALLINT UNSIGNED NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'DRAFT',
    created_by BIGINT UNSIGNED NULL,
    approved_by BIGINT UNSIGNED NULL,
    approved_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (pegawai_id) REFERENCES pegawai(id),
    INDEX idx_punishment_pegawai_status (pegawai_id, status, sisa)
) ENGINE=InnoDB;

CREATE TABLE pembayaran_punishment (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    punishment_id BIGINT UNSIGNED NOT NULL,
    payroll_id BIGINT UNSIGNED NULL,
    periode TINYINT UNSIGNED NOT NULL,
    tahun SMALLINT UNSIGNED NOT NULL,
    jumlah_normal DECIMAL(15,2) NOT NULL DEFAULT 0,
    jumlah_dipotong DECIMAL(15,2) NOT NULL DEFAULT 0,
    jumlah_ditunda DECIMAL(15,2) NOT NULL DEFAULT 0,
    sisa_setelah DECIMAL(15,2) NOT NULL DEFAULT 0,
    status VARCHAR(30) NOT NULL DEFAULT 'NORMAL',
    alasan_penyesuaian TEXT NULL,
    adjusted_by BIGINT UNSIGNED NULL,
    adjusted_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (punishment_id) REFERENCES punishments(id),
    INDEX idx_bayar_punishment_periode (punishment_id, tahun, periode)
) ENGINE=InnoDB;

CREATE TABLE presensi (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    pegawai_id BIGINT UNSIGNED NOT NULL,
    cabang_id BIGINT UNSIGNED NOT NULL,
    tanggal DATE NOT NULL,
    jam_masuk DATETIME NULL,
    jam_pulang DATETIME NULL,
    latitude_masuk DECIMAL(10,7) NULL,
    longitude_masuk DECIMAL(10,7) NULL,
    latitude_pulang DECIMAL(10,7) NULL,
    longitude_pulang DECIMAL(10,7) NULL,
    jarak_masuk_meter DECIMAL(10,2) NULL,
    jarak_pulang_meter DECIMAL(10,2) NULL,
    selfie_masuk VARCHAR(255) NULL,
    selfie_pulang VARCHAR(255) NULL,
    status_masuk VARCHAR(30) NULL,
    status_pulang VARCHAR(30) NULL,
    menit_terlambat INT UNSIGNED NOT NULL DEFAULT 0,
    menit_pulang_cepat INT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (pegawai_id) REFERENCES pegawai(id),
    FOREIGN KEY (cabang_id) REFERENCES cabang(id),
    UNIQUE KEY uq_presensi_pegawai_tanggal (pegawai_id, tanggal),
    INDEX idx_presensi_cabang_tanggal (cabang_id, tanggal)
) ENGINE=InnoDB;

CREATE TABLE presensi_potongan (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    presensi_id BIGINT UNSIGNED NOT NULL,
    jenis VARCHAR(30) NOT NULL,
    dasar DECIMAL(15,2) NOT NULL DEFAULT 0,
    hari_kerja DECIMAL(8,2) NULL,
    hari_terhitung DECIMAL(8,2) NOT NULL DEFAULT 1,
    nilai_normal DECIMAL(15,2) NOT NULL DEFAULT 0,
    override TINYINT(1) NOT NULL DEFAULT 0,
    nilai_override DECIMAL(15,2) NULL,
    nilai_final DECIMAL(15,2) NOT NULL DEFAULT 0,
    alasan_override TEXT NULL,
    approved_by BIGINT UNSIGNED NULL,
    approved_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (presensi_id) REFERENCES presensi(id),
    INDEX idx_presensi_potongan (presensi_id, jenis)
) ENGINE=InnoDB;

CREATE TABLE pengajuan_keberatan_presensi (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    presensi_id BIGINT UNSIGNED NOT NULL,
    pegawai_id BIGINT UNSIGNED NOT NULL,
    alasan TEXT NOT NULL,
    reviewer_id BIGINT UNSIGNED NULL,
    reviewer_status VARCHAR(30) NOT NULL DEFAULT 'MENUNGGU_ATASAN',
    reviewer_note TEXT NULL,
    reviewer_at TIMESTAMP NULL,
    admin_id BIGINT UNSIGNED NULL,
    admin_status VARCHAR(30) NULL,
    admin_note TEXT NULL,
    admin_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (presensi_id) REFERENCES presensi(id),
    FOREIGN KEY (pegawai_id) REFERENCES pegawai(id),
    INDEX idx_keberatan_status (reviewer_status, admin_status)
) ENGINE=InnoDB;

CREATE TABLE payroll (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    kode VARCHAR(50) NOT NULL UNIQUE,
    pegawai_id BIGINT UNSIGNED NOT NULL,
    cabang_id BIGINT UNSIGNED NOT NULL,
    tanggal DATE NOT NULL,
    periode TINYINT UNSIGNED NOT NULL,
    tahun SMALLINT UNSIGNED NOT NULL,
    gaji_pokok DECIMAL(15,2) NOT NULL DEFAULT 0,
    tunjangan DECIMAL(15,2) NOT NULL DEFAULT 0,
    transport DECIMAL(15,2) NOT NULL DEFAULT 0,
    insentif DECIMAL(15,2) NOT NULL DEFAULT 0,
    bonus DECIMAL(15,2) NOT NULL DEFAULT 0,
    masuk_lain DECIMAL(15,2) NOT NULL DEFAULT 0,
    iuran_koperasi DECIMAL(15,2) NOT NULL DEFAULT 0,
    potongan_koperasi DECIMAL(15,2) NOT NULL DEFAULT 0,
    jpk DECIMAL(15,2) NOT NULL DEFAULT 0,
    pinjaman DECIMAL(15,2) NOT NULL DEFAULT 0,
    kasbon DECIMAL(15,2) NOT NULL DEFAULT 0,
    potongan_kendaraan DECIMAL(15,2) NOT NULL DEFAULT 0,
    potongan_absensi DECIMAL(15,2) NOT NULL DEFAULT 0,
    potongan_tiket DECIMAL(15,2) NOT NULL DEFAULT 0,
    potongan_punishment DECIMAL(15,2) NOT NULL DEFAULT 0,
    keluar_lain DECIMAL(15,2) NOT NULL DEFAULT 0,
    total_penerimaan DECIMAL(15,2) NOT NULL DEFAULT 0,
    total_potongan DECIMAL(15,2) NOT NULL DEFAULT 0,
    gaji_bersih DECIMAL(15,2) NOT NULL DEFAULT 0,
    status VARCHAR(30) NOT NULL DEFAULT 'DRAFT',
    finalized_by BIGINT UNSIGNED NULL,
    finalized_at TIMESTAMP NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (pegawai_id) REFERENCES pegawai(id),
    FOREIGN KEY (cabang_id) REFERENCES cabang(id),
    UNIQUE KEY uq_payroll_pegawai_periode (pegawai_id, tahun, periode),
    INDEX idx_payroll_cabang_periode (cabang_id, tahun, periode)
) ENGINE=InnoDB;

CREATE TABLE payroll_obligation_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payroll_id BIGINT UNSIGNED NOT NULL,
    jenis VARCHAR(30) NOT NULL,
    referensi_id BIGINT UNSIGNED NULL,
    jumlah_normal DECIMAL(15,2) NOT NULL DEFAULT 0,
    jumlah_dipotong DECIMAL(15,2) NOT NULL DEFAULT 0,
    jumlah_ditunda DECIMAL(15,2) NOT NULL DEFAULT 0,
    status VARCHAR(30) NOT NULL DEFAULT 'NORMAL',
    alasan_penyesuaian TEXT NULL,
    adjusted_by BIGINT UNSIGNED NULL,
    adjusted_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (payroll_id) REFERENCES payroll(id),
    INDEX idx_payroll_obligation (payroll_id, jenis)
) ENGINE=InnoDB;

CREATE TABLE thr (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    kode VARCHAR(50) NOT NULL UNIQUE,
    pegawai_id BIGINT UNSIGNED NOT NULL,
    cabang_id BIGINT UNSIGNED NOT NULL,
    tanggal DATE NOT NULL,
    periode TINYINT UNSIGNED NULL,
    tahun SMALLINT UNSIGNED NOT NULL,
    tanggal_referensi DATE NOT NULL,
    masa_kerja_aktual DECIMAL(8,2) NOT NULL DEFAULT 0,
    masa_kerja_dihitung DECIMAL(8,2) NOT NULL DEFAULT 0,
    metode_perhitungan VARCHAR(30) NOT NULL DEFAULT 'PROPORSIONAL',
    gaji_pokok DECIMAL(15,2) NOT NULL DEFAULT 0,
    nilai_thr_normal DECIMAL(15,2) NOT NULL DEFAULT 0,
    nilai_thr_final DECIMAL(15,2) NOT NULL DEFAULT 0,
    override TINYINT(1) NOT NULL DEFAULT 0,
    alasan TEXT NULL,
    approved_by BIGINT UNSIGNED NULL,
    approved_at TIMESTAMP NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'DRAFT',
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (pegawai_id) REFERENCES pegawai(id),
    FOREIGN KEY (cabang_id) REFERENCES cabang(id),
    UNIQUE KEY uq_thr_pegawai_tahun (pegawai_id, tahun)
) ENGINE=InnoDB;

CREATE TABLE import_batches (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    jenis VARCHAR(50) NOT NULL,
    nama_file VARCHAR(255) NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'UPLOADED',
    total_baris INT UNSIGNED NOT NULL DEFAULT 0,
    valid_baris INT UNSIGNED NOT NULL DEFAULT 0,
    invalid_baris INT UNSIGNED NOT NULL DEFAULT 0,
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) ENGINE=InnoDB;

CREATE TABLE audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    aksi VARCHAR(50) NOT NULL,
    tabel VARCHAR(100) NULL,
    record_id BIGINT UNSIGNED NULL,
    data_lama JSON NULL,
    data_baru JSON NULL,
    alasan TEXT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    created_at TIMESTAMP NULL,
    INDEX idx_audit_tabel_record (tabel, record_id),
    INDEX idx_audit_user_tanggal (user_id, created_at)
) ENGINE=InnoDB;