-- =====================================================================
-- IAMS Module: Audit Engagement, Checklists & Opening Meetings
-- =====================================================================

USE `iams_db`;

-- 1. Audit Engagements (The actual audit assignment)
CREATE TABLE `engagements` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `rat_plan_id` INT UNSIGNED NULL COMMENT 'Links back to the approved RAT',
    `entity_id` INT UNSIGNED NOT NULL,
    `engagement_code` VARCHAR(50) NOT NULL UNIQUE COMMENT 'e.g., AUDIT-KEW-2024-01',
    `scope_description` TEXT NOT NULL,
    `start_date` DATE NOT NULL,
    `end_date` DATE NOT NULL,
    `status` ENUM('PLANNED', 'IN_PROGRESS', 'COMPLETED', 'CLOSED') DEFAULT 'PLANNED',
    `created_by` INT UNSIGNED NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_eng_entity` FOREIGN KEY (`entity_id`) REFERENCES `auditable_entities`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_eng_rat` FOREIGN KEY (`rat_plan_id`) REFERENCES `annual_audit_plans`(`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_eng_user` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Standardized Inspection Checklists (JSON Responses)
CREATE TABLE `engagement_checklists` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `engagement_id` INT UNSIGNED NOT NULL,
    `category` ENUM('CASH', 'PROCUREMENT', 'ASSET') NOT NULL,
    `responses` JSON NOT NULL COMMENT 'Stores checklist answers as JSON {item_id: "Comply"/"Non-Comply"/"N/A"}',
    `status` ENUM('DRAFT', 'COMPLETED') DEFAULT 'DRAFT',
    `completed_at` TIMESTAMP NULL,
    `completed_by` INT UNSIGNED NULL,
    CONSTRAINT `fk_checklist_eng` FOREIGN KEY (`engagement_id`) REFERENCES `engagements`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_checklist_user` FOREIGN KEY (`completed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Opening Meeting Tracker (Mesyuarat Pembukaan)
CREATE TABLE `opening_meetings` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `engagement_id` INT UNSIGNED NOT NULL UNIQUE,
    `meeting_date` DATE NOT NULL,
    `meeting_time` TIME NOT NULL,
    `venue` VARCHAR(200) NOT NULL,
    `attendees` JSON NOT NULL COMMENT 'List of attendees [{name, designation, role}]',
    `minutes` TEXT NOT NULL,
    `agreed_timelines` JSON NOT NULL COMMENT 'Key milestones [{milestone, deadline}]',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_meeting_eng` FOREIGN KEY (`engagement_id`) REFERENCES `engagements`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;