-- Database schema for Assessment/Audit Programme Plan Matrix System
-- Creates tables for programme management and matrix functionality

-- 1. Framework Audit Programmes table
CREATE TABLE IF NOT EXISTS framework_audit_programmes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    framework_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    status ENUM('draft', 'active', 'completed', 'on_hold', 'cancelled') DEFAULT 'draft',
    start_date DATE NULL,
    end_date DATE NULL,
    is_template BOOLEAN DEFAULT FALSE,
    matrix_data JSON NULL COMMENT 'Stores process-cycle matrix selections',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_framework_id (framework_id),
    INDEX idx_status (status),
    INDEX idx_template (is_template),
    UNIQUE KEY unique_framework_name (framework_id, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Audit Processes table (standardized audit process steps)
CREATE TABLE IF NOT EXISTS audit_processes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    category ENUM('planning', 'execution', 'reporting', 'followup', 'closure') DEFAULT 'execution',
    display_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_category (category),
    INDEX idx_display_order (display_order),
    INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Add timing_months column to existing framework_audit_cycles table (if not exists)
-- This allows for timeline planning (e.g., "2-4 months", "9 months", "12 months")
ALTER TABLE framework_audit_cycles 
ADD COLUMN IF NOT EXISTS timing_months VARCHAR(50) NULL COMMENT 'Timeline period (e.g., "2-4 months", "9 months")' 
AFTER description;

-- 4. Programme Timeline Events table (for advanced timeline features)
CREATE TABLE IF NOT EXISTS programme_timeline_events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    programme_id INT NOT NULL,
    event_type ENUM('audit', 'milestone', 'deadline', 'review') DEFAULT 'audit',
    title VARCHAR(255) NOT NULL,
    description TEXT,
    scheduled_date DATE,
    completed_date DATE NULL,
    status ENUM('planned', 'in_progress', 'completed', 'cancelled') DEFAULT 'planned',
    cycle_id INT NULL COMMENT 'Links to framework_audit_cycles if applicable',
    process_id INT NULL COMMENT 'Links to audit_processes if applicable',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_programme_id (programme_id),
    INDEX idx_event_type (event_type),
    INDEX idx_scheduled_date (scheduled_date),
    INDEX idx_status (status),
    FOREIGN KEY (programme_id) REFERENCES framework_audit_programmes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Insert default audit processes
INSERT IGNORE INTO audit_processes (name, description, category, display_order) VALUES
-- Planning Phase
('Application Review', 'Review client application and documentation', 'planning', 1),
('Scope Definition', 'Define and confirm audit scope', 'planning', 2),
('Risk Assessment', 'Assess audit risks and plan accordingly', 'planning', 3),
('Audit Team Selection', 'Select qualified audit team members', 'planning', 4),
('Audit Plan Preparation', 'Prepare detailed audit plan', 'planning', 5),
('Document Review', 'Review management system documentation', 'planning', 6),

-- Execution Phase
('Opening Meeting', 'Conduct audit opening meeting', 'execution', 10),
('Stage 1 Audit', 'Documentation review and site preparation assessment', 'execution', 11),
('Stage 1 Review', 'Review Stage 1 findings and plan Stage 2', 'execution', 12),
('Stage 2 Audit', 'On-site implementation assessment', 'execution', 13),
('Evidence Collection', 'Collect and verify audit evidence', 'execution', 14),
('Interviews', 'Conduct interviews with personnel', 'execution', 15),
('Process Observation', 'Observe processes and activities', 'execution', 16),
('Findings Documentation', 'Document nonconformities and observations', 'execution', 17),
('Closing Meeting', 'Present findings in closing meeting', 'execution', 18),

-- Reporting Phase
('Report Preparation', 'Prepare comprehensive audit report', 'reporting', 20),
('Technical Review', 'Independent technical review of findings', 'reporting', 21),
('Report Finalization', 'Finalize and issue audit report', 'reporting', 22),
('Client Communication', 'Communicate results to client', 'reporting', 23),

-- Follow-up Phase
('Corrective Action Review', 'Review client corrective actions', 'followup', 30),
('Evidence Verification', 'Verify corrective action effectiveness', 'followup', 31),
('Follow-up Audit', 'Conduct follow-up audit if required', 'followup', 32),

-- Closure Phase
('Certification Decision', 'Make certification recommendation/decision', 'closure', 40),
('Certificate Issuance', 'Issue certification documents', 'closure', 41),
('Record Keeping', 'Maintain audit records and files', 'closure', 42),
('Client Feedback', 'Collect client feedback on audit process', 'closure', 43);

-- 6. Add sample timing values to existing audit cycles (if they exist)
-- This will need to be customized based on existing data
-- UPDATE framework_audit_cycles SET timing_months = 'Initial' WHERE name LIKE '%initial%' OR name LIKE '%stage 1%';
-- UPDATE framework_audit_cycles SET timing_months = '0-1 month' WHERE name LIKE '%stage 2%';
-- UPDATE framework_audit_cycles SET timing_months = '6 months' WHERE name LIKE '%surveillance%' AND name LIKE '%1%';
-- UPDATE framework_audit_cycles SET timing_months = '12 months' WHERE name LIKE '%surveillance%' AND name LIKE '%2%';
-- UPDATE framework_audit_cycles SET timing_months = '36 months' WHERE name LIKE '%recertification%';

-- 7. Create indexes for better performance (MariaDB compatible)
CREATE INDEX IF NOT EXISTS idx_programme_framework ON framework_audit_programmes(framework_id);
CREATE INDEX IF NOT EXISTS idx_programme_status ON framework_audit_programmes(status);
CREATE INDEX IF NOT EXISTS idx_programme_template ON framework_audit_programmes(is_template);
CREATE INDEX IF NOT EXISTS idx_programme_dates ON framework_audit_programmes(start_date, end_date);

-- Note: MariaDB JSON indexing is different from MySQL 8.0+
-- For MariaDB, we'll use a simpler approach for JSON data indexing
-- The matrix_data column will be indexed through framework_id which is the main search criteria

-- 8. Comments for documentation
ALTER TABLE framework_audit_programmes COMMENT = 'Stores audit programme definitions with timeline-based matrix planning';
ALTER TABLE audit_processes COMMENT = 'Standardized audit process steps used across all programmes';
ALTER TABLE programme_timeline_events COMMENT = 'Timeline events and milestones for audit programmes';
