-- =====================================================================
-- IAMS Module: Audit Queries (Kuiri), Maklum Balas & Closing Meetings
-- =====================================================================

USE `iams_db`;

-- 1. Link Auditees to their specific Department/Entity
ALTER TABLE `users`
ADD COLUMN `entity_id` INT UNSIGNED NULL COMMENT 'Links Auditee to their department' AFTER `role_id`,
ADD CONSTRAINT `fk_users_entity` FOREIGN KEY (`entity_id`) REFERENCES `auditable_entities`(`id`) ON DELETE SET NULL;

-- 2. Audit Queries (Kuiri Audit)
CREATE TABLE `audit_queries` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `engagement_id` INT UNSIGNED NOT NULL,
    `working_paper_id` INT UNSIGNED NOT NULL COMMENT 'Source of the observation',
    `query_ref_no` VARCHAR(50) NOT NULL UNIQUE COMMENT 'e.g., KUERI-KEW-01',
    `query_text` TEXT NOT NULL COMMENT 'The formal question/observation directed to auditee',
    `risk_rating` ENUM('HIGH', 'MEDIUM', 'LOW') NOT NULL,
    `deadline_date` DATE NOT NULL,
    `status` ENUM('DRAFT', 'ISSUED', 'REPLIED', 'CLOSED') DEFAULT 'DRAFT',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_query_eng` FOREIGN KEY (`engagement_id`) REFERENCES `engagements`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_query_wp` FOREIGN KEY (`working_paper_id`) REFERENCES `working_papers`(`id`) ON DELETE RESTRICT,
    INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Auditee Responses (Maklum Balas)
CREATE TABLE `query_responses` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `query_id` INT UNSIGNED NOT NULL,
    `response_text` TEXT NOT NULL COMMENT 'Official reply from auditee',
    `action_plan` TEXT NULL COMMENT 'Management Action Plan (MAP)',
    `responded_by` INT UNSIGNED NOT NULL,
    `responded_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_response_query` FOREIGN KEY (`query_id`) REFERENCES `audit_queries`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_response_user` FOREIGN KEY (`responded_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Auditee Evidence (Proof of Rectification)
CREATE TABLE `query_evidences` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `query_id` INT UNSIGNED NOT NULL,
    `original_filename` VARCHAR(255) NOT NULL,
    `stored_filename` VARCHAR(255) NOT NULL,
    `mime_type` VARCHAR(100) NOT NULL,
    `file_size` INT UNSIGNED NOT NULL,
    `uploaded_by` INT UNSIGNED NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_qev_query` FOREIGN KEY (`query_id`) REFERENCES `audit_queries`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Closing Meeting Tracker (Mesyuarat Penutup)
CREATE TABLE `closing_meetings` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `engagement_id` INT UNSIGNED NOT NULL UNIQUE,
    `meeting_date` DATE NOT NULL,
    `venue` VARCHAR(200) NOT NULL,
    `minutes` TEXT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_cm_eng` FOREIGN KEY (`engagement_id`) REFERENCES `engagements`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Closing Meeting Findings Status (Agreed vs Disputed)
CREATE TABLE `closing_meeting_findings` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `closing_meeting_id` INT UNSIGNED NOT NULL,
    `query_id` INT UNSIGNED NOT NULL,
    `status` ENUM('AGREED', 'DISPUTED') NOT NULL,
    `auditee_comments` TEXT NULL,
    CONSTRAINT `fk_cmf_cm` FOREIGN KEY (`closing_meeting_id`) REFERENCES `closing_meetings`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_cmf_query` FOREIGN KEY (`query_id`) REFERENCES `audit_queries`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;