-- =========================================================
-- SKEMA DATABASE: SISTEM SLIP GAJI GURU
-- Kompatibel MySQL/MariaDB (cPanel shared hosting, PHP 7.3)
-- =========================================================

CREATE TABLE IF NOT EXISTS admin (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,   -- disimpan dengan password_hash()
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tugas tambahan (dulu disebut "Jabatan"): Wali Kelas, Wakil Kepala, dll.
-- Boleh kosong untuk guru yang tidak punya tugas tambahan.
CREATE TABLE IF NOT EXISTS tugas_tambahan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(100) NOT NULL,
  nominal DECIMAL(12,0) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS guru (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(150) NOT NULL,
  tugas_tambahan_id INT NULL,
  jtm_per_minggu DECIMAL(6,2) NOT NULL DEFAULT 0,
  tarif_transport_per_hari DECIMAL(12,0) NOT NULL DEFAULT 0,
  masa_kerja DECIMAL(12,0) NOT NULL DEFAULT 0,
  aktif TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (tugas_tambahan_id) REFERENCES tugas_tambahan(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Pengaturan global (hanya 1 baris aktif, id selalu 1)
CREATE TABLE IF NOT EXISTS pengaturan (
  id INT PRIMARY KEY DEFAULT 1,
  tarif_per_jam DECIMAL(12,0) NOT NULL DEFAULT 0,
  jumlah_minggu DECIMAL(4,1) NOT NULL DEFAULT 4,
  tarif_makan_per_hari DECIMAL(12,0) NOT NULL DEFAULT 0,
  infaq DECIMAL(12,0) NOT NULL DEFAULT 0,
  tarif_piket_per_jtm DECIMAL(12,0) NOT NULL DEFAULT 0,
  tarif_at_per_jtm DECIMAL(12,0) NOT NULL DEFAULT 0,
  tarif_tt_per_jtm DECIMAL(12,0) NOT NULL DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO pengaturan (id, tarif_per_jam, jumlah_minggu, tarif_makan_per_hari, infaq,
                         tarif_piket_per_jtm, tarif_at_per_jtm, tarif_tt_per_jtm)
VALUES (1, 6000, 4, 2500, 5000, 25000, 2000, 4000)
ON DUPLICATE KEY UPDATE id = id;

-- Input bulanan per guru, dikelompokkan 3 kategori:
--   Kehadiran   -> hari_hadir            (dasar Transport & Konsumsi)
--   Tambahan    -> jtm_piket
--   Pengurangan -> jtm_at, jtm_tt
CREATE TABLE IF NOT EXISTS rekap_kehadiran (
  id INT AUTO_INCREMENT PRIMARY KEY,
  guru_id INT NOT NULL,
  periode CHAR(7) NOT NULL,          -- format 'YYYY-MM'
  hari_hadir INT NOT NULL DEFAULT 0,
  jtm_piket DECIMAL(6,2) NOT NULL DEFAULT 0,
  jtm_at DECIMAL(6,2) NOT NULL DEFAULT 0,
  jtm_tt DECIMAL(6,2) NOT NULL DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_guru_periode (guru_id, periode),
  FOREIGN KEY (guru_id) REFERENCES guru(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Snapshot hasil "Proses Gaji" per periode. Terkunci (status='terkunci')
-- setelah diproses, sehingga tidak berubah walau Pengaturan/Data Guru
-- diubah belakangan. Semua komponen mentah (jtm, tarif saat itu) ikut
-- disimpan supaya slip bisa dicetak ulang persis sama kapan saja.
CREATE TABLE IF NOT EXISTS slip_gaji (
  id INT AUTO_INCREMENT PRIMARY KEY,
  guru_id INT NOT NULL,
  periode CHAR(7) NOT NULL,
  nama_snapshot VARCHAR(150) NOT NULL,
  tugas_tambahan_snapshot VARCHAR(100) NULL,

  -- komponen mentah (untuk notasi "16 x 4 x 5.000" di slip)
  jtm_per_minggu DECIMAL(6,2) NOT NULL,
  jumlah_minggu DECIMAL(4,1) NOT NULL,
  tarif_per_jam DECIMAL(12,0) NOT NULL,
  hari_hadir INT NOT NULL,
  tarif_transport_per_hari DECIMAL(12,0) NOT NULL,
  tarif_makan_per_hari DECIMAL(12,0) NOT NULL,
  jtm_piket DECIMAL(6,2) NOT NULL,
  tarif_piket_per_jtm DECIMAL(12,0) NOT NULL,
  jtm_at DECIMAL(6,2) NOT NULL,
  tarif_at_per_jtm DECIMAL(12,0) NOT NULL,
  jtm_tt DECIMAL(6,2) NOT NULL,
  tarif_tt_per_jtm DECIMAL(12,0) NOT NULL,

  -- hasil hitung
  gaji_pokok DECIMAL(12,0) NOT NULL,
  tunjangan_tugas_tambahan DECIMAL(12,0) NOT NULL,
  tunjangan_transport DECIMAL(12,0) NOT NULL,
  tunjangan_makan DECIMAL(12,0) NOT NULL,
  masa_kerja DECIMAL(12,0) NOT NULL,
  total_penghasilan DECIMAL(12,0) NOT NULL,
  infaq DECIMAL(12,0) NOT NULL,
  potongan_at DECIMAL(12,0) NOT NULL,
  potongan_tt DECIMAL(12,0) NOT NULL,
  total_dikurangi DECIMAL(12,0) NOT NULL,
  nominal_piket DECIMAL(12,0) NOT NULL,
  take_home_pay DECIMAL(12,0) NOT NULL,

  status ENUM('terkunci','dibuka') NOT NULL DEFAULT 'terkunci',
  diproses_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_guru_periode (guru_id, periode),
  FOREIGN KEY (guru_id) REFERENCES guru(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Jejak audit setiap kali slip yang terkunci dibuka untuk dikoreksi
CREATE TABLE IF NOT EXISTS log_buka_kunci (
  id INT AUTO_INCREMENT PRIMARY KEY,
  slip_gaji_id INT NOT NULL,
  admin_username VARCHAR(50) NOT NULL,
  alasan VARCHAR(255) NULL,
  waktu TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (slip_gaji_id) REFERENCES slip_gaji(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;