-- =====================================================================
-- IAMS Module: Corrective Action Plan (CAP) & Remediation Tracking
-- Compliance: PS 3.2 (Follow-up on Audit Findings)
-- =====================================================================

USE `iams_db`;

-- 1. Corrective Action Plans (Trackable Action Items)
CREATE TABLE `corrective_action_plans` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `query_id` INT UNSIGNED NOT NULL COMMENT 'Links to the specific audit finding',
    `action_description` TEXT NOT NULL,
    `responsible_officer` VARCHAR(150) NOT NULL,
    `target_date` DATE NOT NULL,
    `status` ENUM('OPEN', 'IN_PROGRESS', 'COMPLETED', 'VERIFIED') DEFAULT 'OPEN',
    `completed_date` DATE NULL,
    `verified_by` INT UNSIGNED NULL COMMENT 'Auditor who verified the fix',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_cap_query` FOREIGN KEY (`query_id`) REFERENCES `audit_queries`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_cap_user` FOREIGN KEY (`verified_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_status` (`status`),
    INDEX `idx_target_date` (`target_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. CAP Evidence (Proof of Rectification)
CREATE TABLE `cap_evidences` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `cap_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_capev_cap` FOREIGN KEY (`cap_id`) REFERENCES `corrective_action_plans`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;