-- =========================================================
-- HADIRMU SUPERAPP v4.0 CLEAN
-- Database migration for SDM, Employee Attendance, Payroll,
-- School Calendar, and Duty Journal.
--
-- IMPORTANT:
-- Nalarzk cleanup (dynamic DROP of legacy tables/columns/indexes)
-- is handled safely by the PHP installer because database versions differ.
-- =========================================================

ALTER TABLE users
MODIFY role ENUM(
    'admin',
    'piket',
    'bk',
    'walikelas',
    'kepsek',
    'guru',
    'pegawai',
    'demo'
) NOT NULL DEFAULT 'piket';

ALTER TABLE user_roles
MODIFY role ENUM(
    'admin',
    'piket',
    'bk',
    'walikelas',
    'kepsek',
    'guru',
    'pegawai',
    'demo'
) NOT NULL;

CREATE TABLE IF NOT EXISTS pegawai (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    guru_id INT NULL,
    nama VARCHAR(150) NOT NULL,
    nip VARCHAR(60) NULL,
    jenis_pegawai ENUM('Guru','Karyawan')
        NOT NULL DEFAULT 'Karyawan',
    status_kepegawaian VARCHAR(60)
        NOT NULL DEFAULT 'Belum Diatur',
    jabatan VARCHAR(120) NULL,
    unit_kerja VARCHAR(120)
        NOT NULL DEFAULT 'Sekolah',
    no_wa VARCHAR(25) NULL,
    skema_kehadiran ENUM('Harian','Jadwal Mengajar')
        NOT NULL DEFAULT 'Harian',
    aktif ENUM('Ya','Tidak')
        NOT NULL DEFAULT 'Ya',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP
        DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_pegawai_user (user_id),
    UNIQUE KEY uniq_pegawai_guru (guru_id),
    KEY idx_pegawai_jenis (jenis_pegawai),
    KEY idx_pegawai_aktif (aktif),
    KEY idx_pegawai_nama (nama)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS presensi_pegawai (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    pegawai_id INT NOT NULL,
    tanggal DATE NOT NULL,
    status ENUM(
        'Hadir',
        'Sakit',
        'Izin',
        'Dinas',
        'Alfa',
        'Libur'
    ) NOT NULL,
    jam_masuk TIME NULL,
    jam_pulang TIME NULL,
    terlambat_menit INT NOT NULL DEFAULT 0,
    pulang_awal_menit INT NOT NULL DEFAULT 0,
    lembur_menit INT NOT NULL DEFAULT 0,
    keterangan VARCHAR(255) NULL,
    input_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP
        DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_presensi_pegawai_tanggal (
        pegawai_id,
        tanggal
    ),
    KEY idx_presensi_pegawai_tanggal (tanggal),
    KEY idx_presensi_pegawai_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS kalender_sekolah (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tanggal DATE NOT NULL,
    nama VARCHAR(180) NOT NULL,
    jenis ENUM(
        'Kegiatan',
        'Libur',
        'Ujian',
        'Rapat',
        'Hari Kerja',
        'Lainnya'
    ) NOT NULL DEFAULT 'Kegiatan',
    hari_kerja ENUM('Default','Ya','Tidak')
        NOT NULL DEFAULT 'Default',
    keterangan TEXT NULL,
    created_by INT NULL,
    updated_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP
        DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_kalender_tanggal (tanggal),
    KEY idx_kalender_jenis (jenis)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pegawai_payroll_setting (
    id INT AUTO_INCREMENT PRIMARY KEY,
    pegawai_id INT NOT NULL,
    gaji_pokok DECIMAL(15,2) NOT NULL DEFAULT 0,
    tunjangan_tetap DECIMAL(15,2) NOT NULL DEFAULT 0,
    tunjangan_jabatan DECIMAL(15,2) NOT NULL DEFAULT 0,
    uang_hadir_per_hari DECIMAL(15,2) NOT NULL DEFAULT 0,
    uang_makan_per_hari DECIMAL(15,2) NOT NULL DEFAULT 0,
    transport_per_hari DECIMAL(15,2) NOT NULL DEFAULT 0,
    tarif_lembur_per_menit DECIMAL(15,2) NOT NULL DEFAULT 0,
    potongan_alfa_per_hari DECIMAL(15,2) NOT NULL DEFAULT 0,
    potongan_terlambat_per_menit DECIMAL(15,2) NOT NULL DEFAULT 0,
    potongan_pulang_awal_per_menit DECIMAL(15,2) NOT NULL DEFAULT 0,
    potongan_tetap DECIMAL(15,2) NOT NULL DEFAULT 0,
    status_harian_dibayar VARCHAR(100)
        NOT NULL DEFAULT 'Hadir,Dinas',
    updated_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP
        DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_payroll_setting_pegawai (pegawai_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS komponen_gaji_pegawai (
    id INT AUTO_INCREMENT PRIMARY KEY,
    pegawai_id INT NOT NULL,
    nama VARCHAR(150) NOT NULL,
    tipe ENUM('Penghasilan','Potongan') NOT NULL,
    nominal DECIMAL(15,2) NOT NULL DEFAULT 0,
    aktif ENUM('Ya','Tidak') NOT NULL DEFAULT 'Ya',
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP
        DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_komponen_gaji_pegawai (pegawai_id),
    KEY idx_komponen_gaji_aktif (aktif)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS penggajian (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    pegawai_id INT NOT NULL,
    periode CHAR(7) NOT NULL,
    expected_days INT NOT NULL DEFAULT 0,
    recorded_days INT NOT NULL DEFAULT 0,
    hadir INT NOT NULL DEFAULT 0,
    sakit INT NOT NULL DEFAULT 0,
    izin INT NOT NULL DEFAULT 0,
    dinas INT NOT NULL DEFAULT 0,
    alfa INT NOT NULL DEFAULT 0,
    terlambat_menit INT NOT NULL DEFAULT 0,
    pulang_awal_menit INT NOT NULL DEFAULT 0,
    lembur_menit INT NOT NULL DEFAULT 0,
    bruto DECIMAL(15,2) NOT NULL DEFAULT 0,
    potongan DECIMAL(15,2) NOT NULL DEFAULT 0,
    netto DECIMAL(15,2) NOT NULL DEFAULT 0,
    status ENUM('Draft','Final','Dibayar')
        NOT NULL DEFAULT 'Draft',
    calculated_by INT NULL,
    finalized_by INT NULL,
    finalized_at DATETIME NULL,
    paid_by INT NULL,
    paid_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP
        DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_penggajian_pegawai_periode (
        pegawai_id,
        periode
    ),
    KEY idx_penggajian_periode (periode),
    KEY idx_penggajian_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS penggajian_detail (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    penggajian_id BIGINT NOT NULL,
    nama VARCHAR(180) NOT NULL,
    tipe ENUM('Penghasilan','Potongan') NOT NULL,
    qty DECIMAL(15,2) NOT NULL DEFAULT 1,
    tarif DECIMAL(15,2) NOT NULL DEFAULT 0,
    jumlah DECIMAL(15,2) NOT NULL DEFAULT 0,
    sumber VARCHAR(100) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_penggajian_detail_parent (penggajian_id),
    KEY idx_penggajian_detail_tipe (tipe)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS jurnal_piket (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tanggal DATE NOT NULL,
    jam TIME NOT NULL,
    kategori ENUM(
        'Kedisiplinan',
        'Pembelajaran',
        'Sarpras',
        'Tamu',
        'Kejadian',
        'Kesehatan',
        'Lainnya'
    ) NOT NULL DEFAULT 'Lainnya',
    judul VARCHAR(180) NOT NULL,
    keterangan TEXT NULL,
    tindak_lanjut TEXT NULL,
    status ENUM('Terbuka','Proses','Selesai')
        NOT NULL DEFAULT 'Terbuka',
    created_by INT NULL,
    updated_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP
        DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_jurnal_piket_tanggal (tanggal),
    KEY idx_jurnal_piket_status (status),
    KEY idx_jurnal_piket_kategori (kategori)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO app_settings(setting_key,setting_value)
VALUES
('superapp_workdays','1,2,3,4,5,6'),
('superapp_workday_label','Senin, Selasa, Rabu, Kamis, Jumat, Sabtu'),
('superapp_jam_masuk','07:00'),
('superapp_jam_pulang','15:00'),
(
    'superapp_payroll_note',
    'Penggajian dihitung dari komponen gaji, kehadiran, keterlambatan, lembur, dan komponen tambahan sesuai kebijakan sekolah.'
);