-- =====================================================================
-- IAMS Module: Audit Universe & Annual Audit Plan (RAT)
-- Compliance: PS 3.1 (Risk Assessment), PS 3.2 (Audit Planning)
-- =====================================================================

USE `iams_db`;

-- 1. Auditable Entities (Audit Universe)
CREATE TABLE `auditable_entities` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `entity_code` VARCHAR(50) NOT NULL UNIQUE COMMENT 'e.g., BAHE, UNIT KEWANGAN',
    `entity_name` VARCHAR(200) NOT NULL,
    `entity_type` ENUM('Division', 'Branch', 'Procurement Unit', 'Project') NOT NULL,
    
    -- Risk Assessment Fields (4 Categories: Financial, Performance, Compliance, Media)
    `fin_l` TINYINT UNSIGNED DEFAULT 0 COMMENT 'Financial Likelihood (1-5)',
    `fin_i` TINYINT UNSIGNED DEFAULT 0 COMMENT 'Financial Impact (1-5)',
    `perf_l` TINYINT UNSIGNED DEFAULT 0, `perf_i` TINYINT UNSIGNED DEFAULT 0,
    `comp_l` TINYINT UNSIGNED DEFAULT 0, `comp_i` TINYINT UNSIGNED DEFAULT 0,
    `media_l` TINYINT UNSIGNED DEFAULT 0, `media_i` TINYINT UNSIGNED DEFAULT 0,
    
    `risk_score` TINYINT UNSIGNED DEFAULT 0 COMMENT 'Max score across 4 categories',
    `risk_band` ENUM('Low', 'Medium', 'High') DEFAULT 'Low',
    
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_risk_band` (`risk_band`),
    INDEX `idx_entity_code` (`entity_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Annual Audit Plan (RAT - Rancangan Audit Tahunan)
CREATE TABLE `annual_audit_plans` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `entity_id` INT UNSIGNED NOT NULL,
    `target_year` YEAR NOT NULL,
    `planned_quarter` ENUM('Q1', 'Q2', 'Q3', 'Q4') NOT NULL,
    `lead_auditor_id` INT UNSIGNED NOT NULL,
    `allocated_man_days` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `status` ENUM('DRAFT', 'SUBMITTED', 'ENDORSED', 'APPROVED') DEFAULT 'DRAFT',
    `remarks` TEXT,
    
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    CONSTRAINT `fk_rat_entity` FOREIGN KEY (`entity_id`) REFERENCES `auditable_entities`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_rat_auditor` FOREIGN KEY (`lead_auditor_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
    INDEX `idx_target_year` (`target_year`),
    INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;