-- =====================================================================
-- IAMS Module: Working Papers (KKA) & Evidence Management
-- Compliance: PS 3.1 (Working Papers Standards)
-- =====================================================================

USE `iams_db`;

-- 1. Working Papers (Kertas Kerja Audit - KKA)
CREATE TABLE `working_papers` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `engagement_id` INT UNSIGNED NOT NULL,
    `reference_no` VARCHAR(50) NOT NULL UNIQUE COMMENT 'e.g., WP-KEW-01',
    `title` VARCHAR(255) NOT NULL,
    `kriteria` TEXT NOT NULL COMMENT 'Criteria / Law / Circular reference',
    `keadaan` TEXT NOT NULL COMMENT 'Condition / Actual observation',
    `sebab` TEXT NOT NULL COMMENT 'Cause / Reason for gap',
    `kesan` TEXT NOT NULL COMMENT 'Effect / Financial or operational impact',
    `risk_rating` ENUM('HIGH', 'MEDIUM', 'LOW') NOT NULL DEFAULT 'LOW',
    `status` ENUM('DRAFT', 'SUBMITTED', 'REVIEWED', 'APPROVED') DEFAULT 'DRAFT',
    `reviewer_comments` TEXT NULL,
    `approver_comments` TEXT NULL,
    `created_by` INT UNSIGNED NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_wp_engagement` FOREIGN KEY (`engagement_id`) REFERENCES `engagements`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_wp_user` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    INDEX `idx_status` (`status`),
    INDEX `idx_risk` (`risk_rating`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Evidence Attachments Metadata
CREATE TABLE `kka_evidences` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `kka_id` INT UNSIGNED NOT NULL,
    `original_filename` VARCHAR(255) NOT NULL,
    `stored_filename` VARCHAR(255) NOT NULL COMMENT 'Secure hashed filename',
    `mime_type` VARCHAR(100) NOT NULL,
    `file_size` INT UNSIGNED NOT NULL COMMENT 'Size in bytes',
    `uploaded_by` INT UNSIGNED NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_evidence_kka` FOREIGN KEY (`kka_id`) REFERENCES `working_papers`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_evidence_user` FOREIGN KEY (`uploaded_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;