-- =====================================================================
-- IAMS Database Schema for MySQL 8.0
-- Compliance: Audit Act 1957, Financial Procedure Act 1957
-- =====================================================================

CREATE DATABASE IF NOT EXISTS `iams_db` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `iams_db`;

-- 1. Roles Table (PS 3.1 & PS 3.2 Compliance)
CREATE TABLE `roles` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `role_key` VARCHAR(50) NOT NULL UNIQUE COMMENT 'System identifier for RBAC',
    `role_name` VARCHAR(100) NOT NULL COMMENT 'Official Malay/English title',
    `description` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed the 5 Mandatory Government Roles
INSERT INTO `roles` (`role_key`, `role_name`, `description`) VALUES
('controlling_officer', 'Pegawai Pengawal (KSU/SUK/Mayor)', 'Ultimate responsibility for ministry/department finances and operations.'),
('audit_committee', 'Jawatankuasa Audit (PS 3.2)', 'Oversees internal audit function and reviews audit reports.'),
('head_internal_audit', 'Ketua Audit Dalam (PS 3.1)', 'Leads the internal audit unit, approves RAT and LAD.'),
('auditor', 'Auditor (Ketua/Pembantu)', 'Executes fieldwork, prepares KKA (4Cs), and issues Kuiri.'),
('auditee', 'Auditee (Representative)', 'Department representative responding to Kuiri and CAP.');

-- 2. Users Table
CREATE TABLE `users` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `staff_id` VARCHAR(50) NOT NULL UNIQUE COMMENT 'Government Staff ID (NRIC/Service No)',
    `email` VARCHAR(150) NOT NULL UNIQUE,
    `password_hash` VARCHAR(255) NOT NULL,
    `full_name` VARCHAR(150) NOT NULL,
    `role_id` INT UNSIGNED NOT NULL,
    `is_active` TINYINT(1) DEFAULT 1 COMMENT '1=Active, 0=Suspended',
    `last_login` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_users_role` FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
    INDEX `idx_email` (`email`),
    INDEX `idx_staff_id` (`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Tamper-Evident Audit Logs Table
-- Uses a hash chain to ensure logs cannot be altered without breaking the chain.
CREATE TABLE `audit_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NULL COMMENT 'NULL for system/guest actions',
    `role_id` INT UNSIGNED NULL,
    `module` VARCHAR(50) NOT NULL,
    `action` VARCHAR(50) NOT NULL,
    `ip_address` VARCHAR(45) NOT NULL,
    `user_agent` VARCHAR(255) DEFAULT NULL,
    `details` JSON COMMENT 'Payload of the action (e.g., changed fields)',
    `record_hash` CHAR(64) NOT NULL COMMENT 'SHA-256 hash for tamper-evidence',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_module_action` (`module`, `action`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;