-- Updated Database schema for Assessment/Audit Programme Plan Matrix System
-- Framework-specific audit processes with global defaults

-- 1. Framework Audit Programmes table (unchanged)
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,
    duration_years INT NULL COMMENT 'Programme duration in years (for templates)',
    duration_months INT NULL COMMENT 'Additional months beyond years (for templates)', 
    duration_description VARCHAR(100) NULL COMMENT 'Human-readable duration description',
    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),
    INDEX idx_duration (duration_years, duration_months),
    UNIQUE KEY unique_framework_name (framework_id, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Global Audit Processes table (default processes available to all frameworks)
CREATE TABLE IF NOT EXISTS global_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,
    is_default BOOLEAN DEFAULT FALSE COMMENT 'Auto-added to new frameworks',
    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),
    INDEX idx_default (is_default),
    UNIQUE KEY unique_process_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Framework-specific audit processes (junction table)
CREATE TABLE IF NOT EXISTS framework_audit_processes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    framework_id INT NOT NULL,
    global_process_id INT NOT NULL,
    custom_name VARCHAR(255) NULL COMMENT 'Override name for this framework',
    custom_description TEXT NULL COMMENT 'Override description for this framework',
    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_framework_id (framework_id),
    INDEX idx_global_process_id (global_process_id),
    INDEX idx_display_order (display_order),
    INDEX idx_active (is_active),
    UNIQUE KEY unique_framework_process (framework_id, global_process_id),
    FOREIGN KEY (global_process_id) REFERENCES global_audit_processes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Custom Framework Processes (framework-specific processes)
CREATE TABLE IF NOT EXISTS custom_framework_processes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    framework_id INT NOT NULL,
    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_framework_id (framework_id),
    INDEX idx_category (category),
    INDEX idx_display_order (display_order),
    INDEX idx_active (is_active),
    UNIQUE KEY unique_custom_process (framework_id, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Add timing_months column to existing framework_audit_cycles table (if not exists)
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;

-- 6. Programme Timeline Events table (unchanged)
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,
    event_date DATE NOT NULL,
    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_date (event_date),
    INDEX idx_event_type (event_type),
    FOREIGN KEY (programme_id) REFERENCES framework_audit_programmes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default global processes (43 standard audit processes)
INSERT IGNORE INTO global_audit_processes (name, description, category, display_order, is_default) VALUES
-- Planning Phase
('Pre-audit Planning', 'Initial planning and preparation activities', 'planning', 1, TRUE),
('Audit Program Development', 'Develop comprehensive audit program', 'planning', 2, TRUE),
('Resource Allocation', 'Assign audit team and resources', 'planning', 3, TRUE),
('Risk Assessment', 'Conduct preliminary risk assessment', 'planning', 4, TRUE),
('Audit Scope Definition', 'Define audit scope and boundaries', 'planning', 5, TRUE),
('Timeline Planning', 'Establish audit timeline and milestones', 'planning', 6, TRUE),
('Stakeholder Communication', 'Communicate with key stakeholders', 'planning', 7, TRUE),
('Documentation Review', 'Review existing documentation and procedures', 'planning', 8, TRUE),

-- Execution Phase  
('Opening Meeting', 'Conduct audit opening meeting', 'execution', 9, TRUE),
('Document Examination', 'Examine documents and records', 'execution', 10, TRUE),
('Process Observation', 'Observe processes and activities', 'execution', 11, TRUE),
('Interview Conduct', 'Conduct interviews with personnel', 'execution', 12, TRUE),
('Sampling Procedures', 'Perform sampling and testing procedures', 'execution', 13, TRUE),
('Evidence Collection', 'Collect and document audit evidence', 'execution', 14, TRUE),
('Control Testing', 'Test internal controls and procedures', 'execution', 15, TRUE),
('Compliance Verification', 'Verify compliance with requirements', 'execution', 16, TRUE),
('Gap Analysis', 'Identify gaps and non-conformances', 'execution', 17, TRUE),
('Root Cause Analysis', 'Analyze root causes of issues', 'execution', 18, TRUE),
('Management Review', 'Review management systems and processes', 'execution', 19, TRUE),
('Performance Measurement', 'Assess performance metrics and KPIs', 'execution', 20, TRUE),
('Risk Control Assessment', 'Assess risk control mechanisms', 'execution', 21, TRUE),
('Corrective Action Review', 'Review previous corrective actions', 'execution', 22, TRUE),
('Best Practice Identification', 'Identify best practices and improvements', 'execution', 23, TRUE),
('Technology Assessment', 'Assess technology controls and systems', 'execution', 24, TRUE),
('Data Analytics', 'Perform data analysis and testing', 'execution', 25, TRUE),
('Continuous Monitoring', 'Review continuous monitoring processes', 'execution', 26, TRUE),
('Third Party Assessment', 'Assess third party relationships', 'execution', 27, TRUE),
('Regulatory Compliance', 'Verify regulatory compliance', 'execution', 28, TRUE),
('Financial Controls', 'Test financial controls and processes', 'execution', 29, TRUE),
('Operational Efficiency', 'Assess operational efficiency', 'execution', 30, TRUE),

-- Reporting Phase
('Findings Documentation', 'Document audit findings and observations', 'reporting', 31, TRUE),
('Draft Report Preparation', 'Prepare draft audit report', 'reporting', 32, TRUE),
('Management Response', 'Obtain management responses to findings', 'reporting', 33, TRUE),
('Report Review', 'Review and finalize audit report', 'reporting', 34, TRUE),
('Executive Summary', 'Prepare executive summary', 'reporting', 35, TRUE),
('Recommendations Development', 'Develop actionable recommendations', 'reporting', 36, TRUE),
('Report Distribution', 'Distribute final audit report', 'reporting', 37, TRUE),

-- Follow-up Phase
('Closing Meeting', 'Conduct audit closing meeting', 'followup', 38, TRUE),
('Action Plan Development', 'Develop corrective action plans', 'followup', 39, TRUE),
('Progress Monitoring', 'Monitor implementation progress', 'followup', 40, TRUE),
('Follow-up Testing', 'Conduct follow-up testing and verification', 'followup', 41, TRUE),

-- Closure Phase
('Final Validation', 'Final validation of corrective actions', 'closure', 42, TRUE),
('Audit Closure', 'Formal closure of audit process', 'closure', 43, TRUE);
