-- =====================================================
-- DATABASE SCHEMA: SISTEM INFORMASI MANAJEMEN SDM
-- PUSKESMAS MUARA KUMPEH
-- VERSI UNTUK DRAW.IO / DRAW SQL
-- =====================================================
-- CATATAN: File ini menghilangkan syntax MySQL yang tidak didukung Draw SQL:
-- - CREATE DATABASE dan USE
-- - DELIMITER
-- - Stored Procedures (dihilangkan atau diubah menjadi komentar)
-- - Triggers (dihilangkan atau diubah menjadi komentar)
-- - Views (dihilangkan atau diubah menjadi komentar)
-- =====================================================

-- =====================================================
-- TABEL USERS (Authentication & Authorization)
-- Security: Password hashing, role-based access, account status
-- =====================================================
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 ENUM('pegawai', 'kepala_tata_usaha', 'pimpinan') NOT NULL,
    status ENUM('active', 'inactive', 'suspended') 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 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by INT NULL,
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_role (role),
    INDEX idx_status (status),
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

-- =====================================================
-- TABEL PROFIL USERS
-- Security: Data separation, encryption ready fields
-- =====================================================
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 ENUM('L', 'P') NULL,
    alamat TEXT NULL,
    no_telepon VARCHAR(20) NULL,
    foto_profil VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABEL PEGAWAI (Data Master Pegawai)
-- Security: Access control, data integrity
-- =====================================================
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 ENUM('L', 'P') NOT NULL,
    agama VARCHAR(20) NULL,
    status_perkawinan ENUM('Belum Menikah', 'Menikah', 'Cerai Hidup', 'Cerai Mati') 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,
    
    -- Data Pekerjaan
    jabatan VARCHAR(100) NULL,
    tipe_jabatan ENUM('Struktural', 'Fungsional', 'Pelaksana') NULL,
    golongan VARCHAR(10) NULL,
    pangkat VARCHAR(50) NULL,
    unit_kerja VARCHAR(100) NULL,
    poli_ruangan VARCHAR(100) NULL,
    jenis_tenaga ENUM('Medis', 'Paramedis', 'Non Medis', 'Administrasi') NULL,
    atasan_langsung_id INT NULL,
    status_pegawai ENUM('PNS', 'CPNS', 'PPPK', 'Honorer', 'Kontrak') NULL,
    nomor_sk_pengangkatan VARCHAR(100) NULL,
    tanggal_sk DATE NULL,
    jenis_kontrak ENUM('Tetap', 'Tidak Tetap', 'Periode Tertentu') NULL,
    masa_berlaku_kontrak_awal DATE NULL,
    masa_berlaku_kontrak_akhir DATE NULL,
    tmt_pegawai DATE NULL COMMENT 'Terhitung Mulai Tanggal',
    tmt_pensiun DATE NULL,
    
    -- Data Pendidikan
    pendidikan_terakhir VARCHAR(50) NULL,
    jurusan VARCHAR(100) NULL,
    nama_sekolah VARCHAR(100) NULL,
    tahun_lulus YEAR NULL,
    profesi_kesehatan VARCHAR(100) NULL,
    
    -- Data Legalitas Profesi
    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 ENUM('Aktif', 'Tidak Aktif', 'Kadaluarsa', 'Belum Ada') NULL,
    
    -- Data Penjadwalan & Penugasan
    jadwal_kerja ENUM('Pagi', 'Siang', 'Malam', 'Shift', 'Fleksibel') NULL,
    hari_kerja VARCHAR(50) NULL,
    poli_wilayah_tugas VARCHAR(200) NULL,
    status_penugasan ENUM('Aktif', 'Non Aktif', 'Cuti', 'Penugasan Khusus') NULL,
    
    -- Status
    status_aktif ENUM('Aktif', 'Non Aktif', 'Cuti', 'Pensiun', 'Mutasi') DEFAULT 'Aktif',
    
    -- Audit
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by INT NULL,
    updated_by INT NULL,
    
    INDEX idx_nip (nip),
    INDEX idx_nama (nama_lengkap),
    INDEX idx_status_aktif (status_aktif),
    INDEX idx_jabatan (jabatan),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (atasan_langsung_id) REFERENCES pegawai(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABEL ABSENSI
-- Security: Time-based access, data integrity
-- =====================================================
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 ENUM('Hadir', 'Izin', 'Sakit', 'Cuti', 'Alpha', 'Terlambat') DEFAULT 'Hadir',
    keterangan TEXT NULL,
    lokasi_masuk VARCHAR(255) NULL COMMENT 'GPS/Coordinate untuk verifikasi',
    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 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by INT NULL,
    
    UNIQUE KEY unique_absensi (pegawai_id, tanggal),
    INDEX idx_pegawai (pegawai_id),
    INDEX idx_tanggal (tanggal),
    INDEX idx_status (status),
    INDEX idx_absensi_pegawai_tanggal (pegawai_id, tanggal),
    FOREIGN KEY (pegawai_id) REFERENCES pegawai(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

-- =====================================================
-- TABEL CUTI
-- Security: Approval workflow, access control
-- =====================================================
CREATE TABLE cuti (
    id INT PRIMARY KEY AUTO_INCREMENT,
    pegawai_id INT NOT NULL,
    jenis_cuti ENUM('Tahunan', 'Sakit', 'Melahirkan', 'Besar', 'Khusus', 'Lainnya') 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 ENUM('Menunggu Persetujuan', 'Disetujui', 'Ditolak', 'Dibatalkan') DEFAULT 'Menunggu Persetujuan',
    disetujui_oleh INT NULL COMMENT 'User yang menyetujui',
    tanggal_persetujuan DATETIME NULL,
    catatan_persetujuan TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by INT NULL,
    
    INDEX idx_pegawai (pegawai_id),
    INDEX idx_status (status),
    INDEX idx_tanggal_mulai (tanggal_mulai),
    INDEX idx_cuti_status_tanggal (status, tanggal_mulai),
    FOREIGN KEY (pegawai_id) REFERENCES pegawai(id) ON DELETE CASCADE,
    FOREIGN KEY (disetujui_oleh) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

-- =====================================================
-- TABEL GAJI
-- Security: Confidentiality, access control
-- =====================================================
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 ENUM('Draft', 'Disetujui', 'Ditolak', 'Dibayar') 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 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by INT NULL,
    updated_by INT NULL,
    
    UNIQUE KEY unique_gaji_periode (pegawai_id, periode_tahun, periode_bulan),
    INDEX idx_pegawai (pegawai_id),
    INDEX idx_periode (periode_tahun, periode_bulan),
    INDEX idx_status (status),
    FOREIGN KEY (pegawai_id) REFERENCES pegawai(id) ON DELETE CASCADE,
    FOREIGN KEY (disetujui_oleh) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABEL PENILAIAN KINERJA
-- Security: Access control, confidentiality
-- =====================================================
CREATE TABLE penilaian_kinerja (
    id INT PRIMARY KEY AUTO_INCREMENT,
    pegawai_id INT NOT NULL,
    periode_tahun YEAR NOT NULL,
    periode_bulan TINYINT NULL COMMENT '1-12 untuk penilaian bulanan',
    aspek_penilaian VARCHAR(100) NOT NULL,
    nilai INT NOT NULL COMMENT 'Skala 1-100',
    catatan TEXT NULL,
    penilai_id INT NOT NULL COMMENT 'User yang melakukan penilaian',
    status ENUM('Draft', 'Final', 'Ditolak') DEFAULT 'Draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_pegawai (pegawai_id),
    INDEX idx_periode (periode_tahun, periode_bulan),
    INDEX idx_penilai (penilai_id),
    FOREIGN KEY (pegawai_id) REFERENCES pegawai(id) ON DELETE CASCADE,
    FOREIGN KEY (penilai_id) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABEL SESSIONS (Session Management)
-- Security: Secure session storage, timeout handling
-- =====================================================
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 ON UPDATE CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    
    INDEX idx_user_id (user_id),
    INDEX idx_expires_at (expires_at),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABEL AUDIT LOGS (Security Audit Trail)
-- Security: Complete activity logging, non-repudiation
-- =====================================================
CREATE TABLE audit_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NULL COMMENT 'NULL jika action dilakukan oleh sistem',
    action VARCHAR(50) NOT NULL COMMENT 'CREATE, UPDATE, DELETE, LOGIN, LOGOUT, etc',
    table_name VARCHAR(50) NULL,
    record_id INT NULL,
    old_values JSON NULL COMMENT 'Data sebelum perubahan',
    new_values JSON NULL COMMENT 'Data setelah perubahan',
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    description TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user_id (user_id),
    INDEX idx_action (action),
    INDEX idx_table_name (table_name),
    INDEX idx_created_at (created_at),
    INDEX idx_audit_user_date (user_id, created_at),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABEL PERMISSIONS (Role-Based Access Control)
-- Security: Fine-grained access control
-- =====================================================
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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABEL ROLE PERMISSIONS (Mapping Role ke Permission)
-- =====================================================
CREATE TABLE role_permissions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    role ENUM('pegawai', 'kepala_tata_usaha', 'pimpinan') NOT NULL,
    permission_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE KEY unique_role_permission (role, permission_id),
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABEL PASSWORD RESET TOKENS
-- Security: Secure password reset mechanism
-- =====================================================
CREATE TABLE password_reset_tokens (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    token VARCHAR(255) UNIQUE NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    used_at TIMESTAMP NULL,
    ip_address VARCHAR(45) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_token (token),
    INDEX idx_user_id (user_id),
    INDEX idx_expires_at (expires_at),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABEL NOTIFIKASI
-- =====================================================
CREATE TABLE notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    type ENUM('info', 'warning', 'success', 'error') 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 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_user_id (user_id),
    INDEX idx_is_read (is_read),
    INDEX idx_created_at (created_at),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABEL RIWAYAT JABATAN
-- =====================================================
CREATE TABLE riwayat_jabatan (
    id INT PRIMARY KEY AUTO_INCREMENT,
    pegawai_id INT NOT NULL,
    jabatan VARCHAR(100) NOT NULL,
    tipe_jabatan ENUM('Struktural', 'Fungsional', 'Pelaksana') 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 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_pegawai_id (pegawai_id),
    FOREIGN KEY (pegawai_id) REFERENCES pegawai(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABEL SERTIFIKASI & PELATIHAN
-- =====================================================
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 ENUM('Aktif', 'Kadaluarsa', 'Tidak Berlaku') DEFAULT 'Aktif',
    keterangan TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_pegawai_id (pegawai_id),
    FOREIGN KEY (pegawai_id) REFERENCES pegawai(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABEL RIWAYAT DIKLAT
-- =====================================================
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 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_pegawai_id (pegawai_id),
    FOREIGN KEY (pegawai_id) REFERENCES pegawai(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABEL RIWAYAT PENUGASAN
-- =====================================================
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 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_pegawai_id (pegawai_id),
    FOREIGN KEY (pegawai_id) REFERENCES pegawai(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABEL DOKUMEN PEGAWAI
-- =====================================================
CREATE TABLE dokumen_pegawai (
    id INT PRIMARY KEY AUTO_INCREMENT,
    pegawai_id INT NOT NULL,
    jenis_dokumen VARCHAR(100) NOT NULL,
    nama_file VARCHAR(255) NOT NULL,
    path_file VARCHAR(500) NOT NULL,
    ukuran_file INT NULL COMMENT 'Ukuran dalam bytes',
    keterangan TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_pegawai_id (pegawai_id),
    FOREIGN KEY (pegawai_id) REFERENCES pegawai(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABEL PELANGGARAN & SANKSI
-- =====================================================
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 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_pegawai_id (pegawai_id),
    FOREIGN KEY (pegawai_id) REFERENCES pegawai(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- CATATAN: STORED PROCEDURES, TRIGGERS, DAN VIEWS
-- =====================================================
-- Draw SQL tidak mendukung:
-- 1. DELIMITER // dan DELIMITER ;
-- 2. CREATE PROCEDURE
-- 3. CREATE TRIGGER
-- 4. CREATE VIEW
-- 
-- Jika Anda membutuhkan stored procedures, triggers, dan views,
-- silakan gunakan file schema.sql yang lengkap dan import ke MySQL langsung.
-- =====================================================