-- =====================================================
-- DATABASE SCHEMA: SISTEM INFORMASI MANAJEMEN SDM
-- PUSKESMAS MUARA KUMPEH
-- PENERAPAN SECURITY BY DESIGN
-- =====================================================

-- Membuat Database
CREATE DATABASE IF NOT EXISTS sdm_puskesmas_muara_kumpeh 
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE sdm_puskesmas_muara_kumpeh;

-- =====================================================
-- 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 COMMENT 'Bcrypt hashed password',
    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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 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 COMMENT 'Nomor Induk Pegawai',
    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,
    golongan VARCHAR(10) NULL,
    pangkat VARCHAR(50) NULL,
    unit_kerja VARCHAR(100) NULL,
    status_pegawai ENUM('PNS', 'CPNS', 'Honorer', 'Kontrak') 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,
    
    -- Status
    status_aktif ENUM('Aktif', 'Non Aktif', 'Cuti', 'Pensiun') 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 (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,
    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),
    FOREIGN KEY (pegawai_id) REFERENCES pegawai(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 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),
    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
) 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 LOG (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),
    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,
    
    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;

-- =====================================================
-- INSERT DEFAULT PERMISSIONS
-- =====================================================
INSERT INTO permissions (permission_name, description) VALUES
('view_pegawai', 'Melihat data pegawai'),
('create_pegawai', 'Menambah data pegawai'),
('update_pegawai', 'Mengubah data pegawai'),
('delete_pegawai', 'Menghapus data pegawai'),
('view_absensi', 'Melihat data absensi'),
('create_absensi', 'Menambah data absensi'),
('update_absensi', 'Mengubah data absensi'),
('approve_cuti', 'Menyetujui pengajuan cuti'),
('view_cuti', 'Melihat data cuti'),
('create_cuti', 'Mengajukan cuti'),
('view_penilaian', 'Melihat penilaian kinerja'),
('create_penilaian', 'Membuat penilaian kinerja'),
('view_laporan', 'Melihat laporan'),
('view_audit_log', 'Melihat audit log'),
('manage_users', 'Mengelola pengguna sistem');

-- =====================================================
-- INSERT DEFAULT ROLE PERMISSIONS
-- =====================================================
-- Pegawai: Hanya bisa melihat data sendiri dan mengajukan cuti
INSERT INTO role_permissions (role, permission_id) 
SELECT 'pegawai', id FROM permissions 
WHERE permission_name IN ('view_pegawai', 'view_absensi', 'view_cuti', 'create_cuti');

-- Kepala Tata Usaha: Bisa mengelola pegawai, absensi, dan menyetujui cuti
INSERT INTO role_permissions (role, permission_id) 
SELECT 'kepala_tata_usaha', id FROM permissions 
WHERE permission_name IN (
    'view_pegawai', 'create_pegawai', 'update_pegawai',
    'view_absensi', 'create_absensi', 'update_absensi',
    'view_cuti', 'approve_cuti', 'view_penilaian', 'create_penilaian',
    'view_laporan'
);

-- Pimpinan: Full access termasuk audit log
INSERT INTO role_permissions (role, permission_id) 
SELECT 'pimpinan', id FROM permissions;

-- =====================================================
-- INSERT DEFAULT ADMIN USER
-- Password: admin123 (harus di-hash dengan bcrypt)
-- Default password hash untuk 'admin123': $2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi
-- =====================================================
INSERT INTO users (username, password_hash, email, role, status, created_by) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin@puskesmas-muarakumpeh.go.id', 'pimpinan', 'active', NULL);

INSERT INTO user_profiles (user_id, nama_lengkap, nip) VALUES
(1, 'Administrator', 'ADM001');

-- =====================================================
-- VIEWS untuk Security dan Reporting
-- =====================================================

-- View untuk melihat aktivitas user
CREATE VIEW v_user_activities AS
SELECT 
    al.id,
    u.username,
    up.nama_lengkap,
    al.action,
    al.table_name,
    al.description,
    al.ip_address,
    al.created_at
FROM audit_logs al
LEFT JOIN users u ON al.user_id = u.id
LEFT JOIN user_profiles up ON u.id = up.user_id
ORDER BY al.created_at DESC;

-- View untuk dashboard absensi
CREATE VIEW v_dashboard_absensi AS
SELECT 
    DATE(a.tanggal) as tanggal,
    COUNT(CASE WHEN a.status = 'Hadir' THEN 1 END) as hadir,
    COUNT(CASE WHEN a.status = 'Izin' THEN 1 END) as izin,
    COUNT(CASE WHEN a.status = 'Sakit' THEN 1 END) as sakit,
    COUNT(CASE WHEN a.status = 'Cuti' THEN 1 END) as cuti,
    COUNT(CASE WHEN a.status = 'Alpha' THEN 1 END) as alpha,
    COUNT(*) as total
FROM absensi a
GROUP BY DATE(a.tanggal);

-- =====================================================
-- STORED PROCEDURES untuk Security Operations
-- =====================================================

DELIMITER //

-- Procedure untuk mencatat audit log
CREATE PROCEDURE sp_log_audit(
    IN p_user_id INT,
    IN p_action VARCHAR(50),
    IN p_table_name VARCHAR(50),
    IN p_record_id INT,
    IN p_old_values JSON,
    IN p_new_values JSON,
    IN p_ip_address VARCHAR(45),
    IN p_user_agent TEXT,
    IN p_description TEXT
)
BEGIN
    INSERT INTO audit_logs (
        user_id, action, table_name, record_id, 
        old_values, new_values, ip_address, user_agent, description
    ) VALUES (
        p_user_id, p_action, p_table_name, p_record_id,
        p_old_values, p_new_values, p_ip_address, p_user_agent, p_description
    );
END //

-- Procedure untuk membersihkan session yang expired
CREATE PROCEDURE sp_cleanup_expired_sessions()
BEGIN
    DELETE FROM sessions WHERE expires_at < NOW();
    DELETE FROM password_reset_tokens WHERE expires_at < NOW() AND used_at IS NULL;
END //

DELIMITER ;

-- =====================================================
-- TRIGGERS untuk Auto Audit Logging
-- =====================================================

DELIMITER //

-- Trigger untuk logging perubahan pada tabel users
CREATE TRIGGER tr_users_after_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF OLD.password_hash != NEW.password_hash THEN
        CALL sp_log_audit(
            NEW.id,
            'UPDATE_PASSWORD',
            'users',
            NEW.id,
            JSON_OBJECT('password_hash', '***HIDDEN***'),
            JSON_OBJECT('password_hash', '***HIDDEN***'),
            NULL,
            NULL,
            CONCAT('Password updated for user: ', NEW.username)
        );
    END IF;
END //

-- Trigger untuk logging perubahan pada tabel pegawai
CREATE TRIGGER tr_pegawai_after_update
AFTER UPDATE ON pegawai
FOR EACH ROW
BEGIN
    CALL sp_log_audit(
        NEW.updated_by,
        'UPDATE',
        'pegawai',
        NEW.id,
        JSON_OBJECT(
            'nip', OLD.nip,
            'nama_lengkap', OLD.nama_lengkap,
            'jabatan', OLD.jabatan,
            'status_aktif', OLD.status_aktif
        ),
        JSON_OBJECT(
            'nip', NEW.nip,
            'nama_lengkap', NEW.nama_lengkap,
            'jabatan', NEW.jabatan,
            'status_aktif', NEW.status_aktif
        ),
        NULL,
        NULL,
        CONCAT('Data pegawai updated: ', NEW.nama_lengkap)
    );
END //

DELIMITER ;

-- =====================================================
-- INDEXES tambahan untuk Performance
-- =====================================================

-- Composite indexes untuk query yang sering digunakan
CREATE INDEX idx_absensi_pegawai_tanggal ON absensi(pegawai_id, tanggal);
CREATE INDEX idx_cuti_status_tanggal ON cuti(status, tanggal_mulai);
CREATE INDEX idx_audit_user_date ON audit_logs(user_id, created_at);

-- =====================================================
-- Selesai
-- =====================================================