-- =====================================================================
-- IAMS Module: Reports (LAD), AGD Dashboard & Accountability Index
-- =====================================================================

USE `iams_db`;

-- 1. Laporan Audit Dalaman (LAD) Registry
CREATE TABLE `lad_reports` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `engagement_id` INT UNSIGNED NOT NULL,
    `report_ref_no` VARCHAR(50) NOT NULL UNIQUE COMMENT 'e.g., LAD-KEW-2024-01',
    `status` ENUM('DRAFT', 'FINAL') DEFAULT 'DRAFT',
    `generated_by` INT UNSIGNED NOT NULL,
    `file_path` VARCHAR(255) NOT NULL COMMENT 'Path to stored PDF in storage/reports/',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_lad_eng` FOREIGN KEY (`engagement_id`) REFERENCES `engagements`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_lad_user` FOREIGN KEY (`generated_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Accountability Index (AI) Pre-Assessment Scores
CREATE TABLE `accountability_scores` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `entity_id` INT UNSIGNED NOT NULL,
    `assessment_year` YEAR NOT NULL,
    `financial_compliance` DECIMAL(5,2) NOT NULL DEFAULT 0.00 COMMENT 'Percentage 0-100',
    `asset_management` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `procurement_compliance` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `overall_percentage` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `star_rating` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '1 to 5 Stars',
    `assessed_by` INT UNSIGNED NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_entity_year` (`entity_id`, `assessment_year`),
    CONSTRAINT `fk_ai_entity` FOREIGN KEY (`entity_id`) REFERENCES `auditable_entities`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ai_user` FOREIGN KEY (`assessed_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;