-- =====================================================
-- DATABASE SCHEMA: SISTEM INFORMASI MANAJEMEN SDM
-- PUSKESMAS MUARA KUMPEH
-- VERSI MINIMAL UNTUK DRAW.IO / DRAW SQL
-- HANYA TABEL YANG DIPAKAI DI SISTEM
-- =====================================================
-- Perubahan untuk kompatibilitas maksimal dengan Draw SQL:
-- 1. Menghapus semua COMMENT syntax
-- 2. Mengganti ENUM dengan VARCHAR
-- 3. Menghapus ENGINE dan CHARSET
-- 4. Mengganti JSON dengan TEXT
-- 5. Menyederhanakan DEFAULT values
-- 6. Memisahkan FOREIGN KEY dengan ALTER TABLE (setelah semua tabel dibuat)
-- 7. Menghapus tabel yang TIDAK DIGUNAKAN SAMA SEKALI:
--    - password_reset_tokens (tidak ada model/controller/route)
--    - penilaian_kinerja (ada model tapi tidak ada controller/route untuk CRUD)
-- =====================================================

-- =====================================================
-- TABEL 1: USERS (Tabel Parent - Dasar)
-- Fungsi: Authentication & Authorization
-- =====================================================
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    role VARCHAR(20) NOT NULL,
    status VARCHAR(20) DEFAULT 'active',
    last_login DATETIME NULL,
    failed_login_attempts INT DEFAULT 0,
    account_locked_until DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL
);

CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_users_status ON users(status);

-- =====================================================
-- TABEL 2: USER_PROFILES (Child dari users)
-- Fungsi: Profil lengkap user (digunakan di ProfileController)
-- =====================================================
CREATE TABLE user_profiles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNIQUE NOT NULL,
    nip VARCHAR(50) UNIQUE NULL,
    nama_lengkap VARCHAR(100) NOT NULL,
    tempat_lahir VARCHAR(50) NULL,
    tanggal_lahir DATE NULL,
    jenis_kelamin VARCHAR(1) NULL,
    alamat TEXT NULL,
    no_telepon VARCHAR(20) NULL,
    foto_profil VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL
);

CREATE INDEX idx_user_profiles_user_id ON user_profiles(user_id);

-- =====================================================
-- TABEL 3: PERMISSIONS (Tabel Standalone)
-- Fungsi: RBAC - Permission management (digunakan di PermissionMiddleware)
-- =====================================================
CREATE TABLE permissions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    permission_name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =====================================================
-- TABEL 4: ROLE_PERMISSIONS (Child dari permissions)
-- Fungsi: RBAC - Mapping role ke permission (digunakan di PermissionMiddleware)
-- =====================================================
CREATE TABLE role_permissions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    role VARCHAR(20) NOT NULL,
    permission_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_role_permission (role, permission_id)
);

CREATE INDEX idx_role_permissions_permission_id ON role_permissions(permission_id);

-- =====================================================
-- TABEL 5: PEGAWAI (Child dari users, Self-referencing untuk atasan)
-- Fungsi: Data master pegawai (digunakan di PegawaiController)
-- =====================================================
CREATE TABLE pegawai (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNIQUE NULL,
    nip VARCHAR(50) UNIQUE NOT NULL,
    nama_lengkap VARCHAR(100) NOT NULL,
    gelar_depan VARCHAR(20) NULL,
    gelar_belakang VARCHAR(20) NULL,
    tempat_lahir VARCHAR(50) NULL,
    tanggal_lahir DATE NULL,
    jenis_kelamin VARCHAR(1) NOT NULL,
    agama VARCHAR(20) NULL,
    status_perkawinan VARCHAR(20) NULL,
    alamat TEXT NULL,
    rt VARCHAR(5) NULL,
    rw VARCHAR(5) NULL,
    kelurahan VARCHAR(50) NULL,
    kecamatan VARCHAR(50) NULL,
    kota VARCHAR(50) NULL,
    provinsi VARCHAR(50) NULL,
    kode_pos VARCHAR(10) NULL,
    no_telepon VARCHAR(20) NULL,
    email VARCHAR(100) NULL,
    foto_profil VARCHAR(255) NULL,
    jabatan VARCHAR(100) NULL,
    tipe_jabatan VARCHAR(20) NULL,
    golongan VARCHAR(10) NULL,
    pangkat VARCHAR(50) NULL,
    unit_kerja VARCHAR(100) NULL,
    poli_ruangan VARCHAR(100) NULL,
    jenis_tenaga VARCHAR(20) NULL,
    atasan_langsung_id INT NULL,
    status_pegawai VARCHAR(20) NULL,
    nomor_sk_pengangkatan VARCHAR(100) NULL,
    tanggal_sk DATE NULL,
    jenis_kontrak VARCHAR(20) NULL,
    masa_berlaku_kontrak_awal DATE NULL,
    masa_berlaku_kontrak_akhir DATE NULL,
    tmt_pegawai DATE NULL,
    tmt_pensiun DATE NULL,
    pendidikan_terakhir VARCHAR(50) NULL,
    jurusan VARCHAR(100) NULL,
    nama_sekolah VARCHAR(100) NULL,
    tahun_lulus INT NULL,
    profesi_kesehatan VARCHAR(100) NULL,
    nomor_str VARCHAR(50) NULL,
    tanggal_str DATE NULL,
    masa_berlaku_str DATE NULL,
    nomor_sip VARCHAR(50) NULL,
    tanggal_sip DATE NULL,
    masa_berlaku_sip DATE NULL,
    status_legalitas VARCHAR(20) NULL,
    jadwal_kerja VARCHAR(20) NULL,
    hari_kerja VARCHAR(50) NULL,
    poli_wilayah_tugas VARCHAR(200) NULL,
    status_penugasan VARCHAR(20) NULL,
    status_aktif VARCHAR(20) DEFAULT 'Aktif',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL,
    created_by INT NULL,
    updated_by INT NULL
);

CREATE INDEX idx_pegawai_nip ON pegawai(nip);
CREATE INDEX idx_pegawai_nama ON pegawai(nama_lengkap);
CREATE INDEX idx_pegawai_status_aktif ON pegawai(status_aktif);
CREATE INDEX idx_pegawai_jabatan ON pegawai(jabatan);
CREATE INDEX idx_pegawai_user_id ON pegawai(user_id);
CREATE INDEX idx_pegawai_atasan_langsung_id ON pegawai(atasan_langsung_id);
CREATE INDEX idx_pegawai_created_by ON pegawai(created_by);
CREATE INDEX idx_pegawai_updated_by ON pegawai(updated_by);

-- =====================================================
-- TABEL 6: ABSENSI (Child dari pegawai dan users)
-- Fungsi: Data absensi pegawai (digunakan di AbsensiController)
-- =====================================================
CREATE TABLE absensi (
    id INT PRIMARY KEY AUTO_INCREMENT,
    pegawai_id INT NOT NULL,
    tanggal DATE NOT NULL,
    jam_masuk TIME NULL,
    jam_keluar TIME NULL,
    status VARCHAR(20) DEFAULT 'Hadir',
    keterangan TEXT NULL,
    lokasi_masuk VARCHAR(255) NULL,
    lokasi_keluar VARCHAR(255) NULL,
    foto_masuk VARCHAR(255) NULL,
    foto_keluar VARCHAR(255) NULL,
    device_name VARCHAR(100) NULL,
    device_ip VARCHAR(45) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL,
    created_by INT NULL,
    UNIQUE KEY unique_absensi (pegawai_id, tanggal)
);

CREATE INDEX idx_absensi_pegawai ON absensi(pegawai_id);
CREATE INDEX idx_absensi_tanggal ON absensi(tanggal);
CREATE INDEX idx_absensi_status ON absensi(status);
CREATE INDEX idx_absensi_created_by ON absensi(created_by);

-- =====================================================
-- TABEL 7: CUTI (Child dari pegawai dan users)
-- Fungsi: Data pengajuan cuti (digunakan di CutiController)
-- =====================================================
CREATE TABLE cuti (
    id INT PRIMARY KEY AUTO_INCREMENT,
    pegawai_id INT NOT NULL,
    jenis_cuti VARCHAR(20) NOT NULL,
    tanggal_mulai DATE NOT NULL,
    tanggal_selesai DATE NOT NULL,
    jumlah_hari INT NOT NULL,
    alasan TEXT NOT NULL,
    alamat_cuti TEXT NULL,
    no_telepon_cuti VARCHAR(20) NULL,
    status VARCHAR(20) DEFAULT 'Menunggu Persetujuan',
    disetujui_oleh INT NULL,
    tanggal_persetujuan DATETIME NULL,
    catatan_persetujuan TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL,
    created_by INT NULL
);

CREATE INDEX idx_cuti_pegawai ON cuti(pegawai_id);
CREATE INDEX idx_cuti_status ON cuti(status);
CREATE INDEX idx_cuti_tanggal_mulai ON cuti(tanggal_mulai);
CREATE INDEX idx_cuti_disetujui_oleh ON cuti(disetujui_oleh);
CREATE INDEX idx_cuti_created_by ON cuti(created_by);

-- =====================================================
-- TABEL 8: GAJI (Child dari pegawai dan users)
-- Fungsi: Data gaji pegawai (digunakan di GajiController)
-- =====================================================
CREATE TABLE gaji (
    id INT PRIMARY KEY AUTO_INCREMENT,
    pegawai_id INT NOT NULL,
    periode_tahun INT NOT NULL,
    periode_bulan INT NOT NULL,
    gaji_pokok DECIMAL(15,2) NOT NULL,
    tunjangan_jabatan DECIMAL(15,2) DEFAULT 0,
    tunjangan_keluarga DECIMAL(15,2) DEFAULT 0,
    tunjangan_transport DECIMAL(15,2) DEFAULT 0,
    tunjangan_makan DECIMAL(15,2) DEFAULT 0,
    bonus DECIMAL(15,2) DEFAULT 0,
    potongan_absensi DECIMAL(15,2) DEFAULT 0,
    potongan_lainnya DECIMAL(15,2) DEFAULT 0,
    total_gaji DECIMAL(15,2) NOT NULL,
    jumlah_hadir INT DEFAULT 0,
    jumlah_izin INT DEFAULT 0,
    jumlah_sakit INT DEFAULT 0,
    jumlah_cuti INT DEFAULT 0,
    jumlah_alpha INT DEFAULT 0,
    total_hari_kerja INT DEFAULT 0,
    tarif_potongan_per_hari DECIMAL(15,2) NULL,
    tarif_potongan_per_hari_sakit DECIMAL(15,2) NULL,
    status VARCHAR(20) DEFAULT 'Draft',
    keterangan TEXT NULL,
    disetujui_oleh INT NULL,
    tanggal_persetujuan DATETIME NULL,
    tanggal_pembayaran DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL,
    created_by INT NULL,
    updated_by INT NULL,
    UNIQUE KEY unique_gaji_periode (pegawai_id, periode_tahun, periode_bulan)
);

CREATE INDEX idx_gaji_pegawai ON gaji(pegawai_id);
CREATE INDEX idx_gaji_periode ON gaji(periode_tahun, periode_bulan);
CREATE INDEX idx_gaji_status ON gaji(status);
CREATE INDEX idx_gaji_disetujui_oleh ON gaji(disetujui_oleh);
CREATE INDEX idx_gaji_created_by ON gaji(created_by);
CREATE INDEX idx_gaji_updated_by ON gaji(updated_by);

-- =====================================================
-- TABEL 9: SESSIONS (Child dari users)
-- Fungsi: Session management (digunakan di Session model untuk cleanup)
-- =====================================================
CREATE TABLE sessions (
    id VARCHAR(128) PRIMARY KEY,
    user_id INT NOT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL,
    is_active BOOLEAN DEFAULT TRUE
);

CREATE INDEX idx_sessions_user_id ON sessions(user_id);
CREATE INDEX idx_sessions_expires_at ON sessions(expires_at);

-- =====================================================
-- TABEL 10: AUDIT_LOGS (Child dari users)
-- Fungsi: Audit trail untuk security (digunakan di AuditLogController)
-- =====================================================
CREATE TABLE audit_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NULL,
    action VARCHAR(50) NOT NULL,
    table_name VARCHAR(50) NULL,
    record_id INT NULL,
    old_values TEXT NULL,
    new_values TEXT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    description TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_audit_logs_user_id ON audit_logs(user_id);
CREATE INDEX idx_audit_logs_action ON audit_logs(action);
CREATE INDEX idx_audit_logs_table_name ON audit_logs(table_name);
CREATE INDEX idx_audit_logs_created_at ON audit_logs(created_at);

-- =====================================================
-- TABEL 11: NOTIFICATIONS (Child dari users)
-- Fungsi: Notifikasi sistem (digunakan di NotificationController)
-- =====================================================
CREATE TABLE notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    type VARCHAR(20) DEFAULT 'info',
    is_read BOOLEAN DEFAULT FALSE,
    related_table VARCHAR(50) NULL,
    related_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL
);

CREATE INDEX idx_notifications_user_id ON notifications(user_id);
CREATE INDEX idx_notifications_is_read ON notifications(is_read);
CREATE INDEX idx_notifications_created_at ON notifications(created_at);

-- =====================================================
-- TABEL 12: RIWAYAT_JABATAN (Child dari pegawai)
-- Fungsi: Riwayat jabatan pegawai (digunakan untuk display di view show pegawai)
-- Catatan: Tidak ada CRUD controller, hanya digunakan untuk display
-- =====================================================
CREATE TABLE riwayat_jabatan (
    id INT PRIMARY KEY AUTO_INCREMENT,
    pegawai_id INT NOT NULL,
    jabatan VARCHAR(100) NOT NULL,
    tipe_jabatan VARCHAR(20) NULL,
    tanggal_mulai DATE NOT NULL,
    tanggal_selesai DATE NULL,
    sk_jabatan VARCHAR(100) NULL,
    keterangan TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL
);

CREATE INDEX idx_riwayat_jabatan_pegawai_id ON riwayat_jabatan(pegawai_id);

-- =====================================================
-- TABEL 13: SERTIFIKASI_PELATIHAN (Child dari pegawai)
-- Fungsi: Sertifikasi dan pelatihan pegawai (digunakan untuk display di view show pegawai)
-- Catatan: Tidak ada CRUD controller, hanya digunakan untuk display
-- =====================================================
CREATE TABLE sertifikasi_pelatihan (
    id INT PRIMARY KEY AUTO_INCREMENT,
    pegawai_id INT NOT NULL,
    nama_sertifikat VARCHAR(200) NOT NULL,
    lembaga_penerbit VARCHAR(200) NULL,
    nomor_sertifikat VARCHAR(100) NULL,
    tanggal_terbit DATE NULL,
    tanggal_berlaku DATE NULL,
    status VARCHAR(20) DEFAULT 'Aktif',
    keterangan TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL
);

CREATE INDEX idx_sertifikasi_pelatihan_pegawai_id ON sertifikasi_pelatihan(pegawai_id);

-- =====================================================
-- TABEL 14: RIWAYAT_DIKLAT (Child dari pegawai)
-- Fungsi: Riwayat diklat pegawai (digunakan untuk display di view show pegawai)
-- Catatan: Tidak ada CRUD controller, hanya digunakan untuk display
-- =====================================================
CREATE TABLE riwayat_diklat (
    id INT PRIMARY KEY AUTO_INCREMENT,
    pegawai_id INT NOT NULL,
    nama_diklat VARCHAR(200) NOT NULL,
    lembaga_penyelenggara VARCHAR(200) NULL,
    lokasi VARCHAR(200) NULL,
    tanggal_mulai DATE NULL,
    tanggal_selesai DATE NULL,
    jumlah_jam INT NULL,
    sertifikat VARCHAR(255) NULL,
    keterangan TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL
);

CREATE INDEX idx_riwayat_diklat_pegawai_id ON riwayat_diklat(pegawai_id);

-- =====================================================
-- TABEL 15: RIWAYAT_PENUGASAN (Child dari pegawai)
-- Fungsi: Riwayat penugasan pegawai (digunakan untuk display di view show pegawai)
-- Catatan: Tidak ada CRUD controller, hanya digunakan untuk display
-- =====================================================
CREATE TABLE riwayat_penugasan (
    id INT PRIMARY KEY AUTO_INCREMENT,
    pegawai_id INT NOT NULL,
    jenis_penugasan VARCHAR(100) NOT NULL,
    lokasi_penugasan VARCHAR(200) NULL,
    tanggal_mulai DATE NOT NULL,
    tanggal_selesai DATE NULL,
    keterangan TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL
);

CREATE INDEX idx_riwayat_penugasan_pegawai_id ON riwayat_penugasan(pegawai_id);

-- =====================================================
-- TABEL 16: PELANGGARAN_SANKSI (Child dari pegawai)
-- Fungsi: Riwayat pelanggaran dan sanksi pegawai (digunakan untuk display di view show pegawai)
-- Catatan: Tidak ada CRUD controller, hanya digunakan untuk display
-- =====================================================
CREATE TABLE pelanggaran_sanksi (
    id INT PRIMARY KEY AUTO_INCREMENT,
    pegawai_id INT NOT NULL,
    jenis_pelanggaran VARCHAR(100) NOT NULL,
    tanggal_pelanggaran DATE NOT NULL,
    sanksi VARCHAR(200) NULL,
    tanggal_sanksi DATE NULL,
    keterangan TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL
);

CREATE INDEX idx_pelanggaran_sanksi_pegawai_id ON pelanggaran_sanksi(pegawai_id);

-- =====================================================
-- FOREIGN KEY CONSTRAINTS
-- Semua FOREIGN KEY dibuat setelah semua tabel dibuat
-- =====================================================

-- Foreign Keys untuk user_profiles
ALTER TABLE user_profiles ADD FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

-- Foreign Keys untuk role_permissions
ALTER TABLE role_permissions ADD FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE;

-- Foreign Keys untuk pegawai
ALTER TABLE pegawai ADD FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE pegawai ADD FOREIGN KEY (atasan_langsung_id) REFERENCES pegawai(id) ON DELETE SET NULL;
ALTER TABLE pegawai ADD FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE pegawai ADD FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL;

-- Foreign Keys untuk absensi
ALTER TABLE absensi ADD FOREIGN KEY (pegawai_id) REFERENCES pegawai(id) ON DELETE CASCADE;
ALTER TABLE absensi ADD FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL;

-- Foreign Keys untuk cuti
ALTER TABLE cuti ADD FOREIGN KEY (pegawai_id) REFERENCES pegawai(id) ON DELETE CASCADE;
ALTER TABLE cuti ADD FOREIGN KEY (disetujui_oleh) REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE cuti ADD FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL;

-- Foreign Keys untuk gaji
ALTER TABLE gaji ADD FOREIGN KEY (pegawai_id) REFERENCES pegawai(id) ON DELETE CASCADE;
ALTER TABLE gaji ADD FOREIGN KEY (disetujui_oleh) REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE gaji ADD FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE gaji ADD FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL;

-- Foreign Keys untuk sessions
ALTER TABLE sessions ADD FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

-- Foreign Keys untuk audit_logs
ALTER TABLE audit_logs ADD FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL;

-- Foreign Keys untuk notifications
ALTER TABLE notifications ADD FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

-- Foreign Keys untuk riwayat_jabatan
ALTER TABLE riwayat_jabatan ADD FOREIGN KEY (pegawai_id) REFERENCES pegawai(id) ON DELETE CASCADE;

-- Foreign Keys untuk sertifikasi_pelatihan
ALTER TABLE sertifikasi_pelatihan ADD FOREIGN KEY (pegawai_id) REFERENCES pegawai(id) ON DELETE CASCADE;

-- Foreign Keys untuk riwayat_diklat
ALTER TABLE riwayat_diklat ADD FOREIGN KEY (pegawai_id) REFERENCES pegawai(id) ON DELETE CASCADE;

-- Foreign Keys untuk riwayat_penugasan
ALTER TABLE riwayat_penugasan ADD FOREIGN KEY (pegawai_id) REFERENCES pegawai(id) ON DELETE CASCADE;

-- Foreign Keys untuk pelanggaran_sanksi
ALTER TABLE pelanggaran_sanksi ADD FOREIGN KEY (pegawai_id) REFERENCES pegawai(id) ON DELETE CASCADE;