-- Migration script for Framework-Specific Audit Process Management
-- This script migrates existing audit_processes table to the new framework-aware structure
-- Run this script AFTER backing up your database

-- Step 1: Create backup of existing data
CREATE TABLE IF NOT EXISTS audit_processes_backup AS SELECT * FROM audit_processes;

-- Step 2: Create new global_audit_processes table
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;

-- Step 3: Migrate existing audit_processes data to global_audit_processes
INSERT INTO global_audit_processes (name, description, category, display_order, is_active, is_default, created_at, updated_at)
SELECT 
    name, 
    description, 
    category, 
    display_order, 
    is_active, 
    TRUE as is_default, -- Mark existing processes as default
    created_at, 
    updated_at
FROM audit_processes
ON DUPLICATE KEY UPDATE
    description = VALUES(description),
    category = VALUES(category),
    display_order = VALUES(display_order),
    is_active = VALUES(is_active),
    updated_at = VALUES(updated_at);

-- Step 4: Create framework_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;

-- Step 5: Create custom_framework_processes table
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;

-- Step 6: Update framework_audit_programmes table with new columns (if needed)
ALTER TABLE framework_audit_programmes 
ADD COLUMN IF NOT EXISTS duration_years INT NULL COMMENT 'Programme duration in years (for templates)' AFTER is_template,
ADD COLUMN IF NOT EXISTS duration_months INT NULL COMMENT 'Additional months beyond years (for templates)' AFTER duration_years,
ADD COLUMN IF NOT EXISTS duration_description VARCHAR(100) NULL COMMENT 'Human-readable duration description' AFTER duration_months;

-- Step 7: Populate framework_audit_processes with default processes for existing frameworks
-- This assumes you have a frameworks table or similar - adjust the query accordingly
INSERT IGNORE INTO framework_audit_processes (framework_id, global_process_id, display_order, is_active)
SELECT 
    f.id as framework_id,
    gp.id as global_process_id,
    gp.display_order,
    gp.is_active
FROM (
    -- Replace this query with your actual frameworks table/data source
    -- Example: SELECT DISTINCT framework_id as id FROM framework_audit_programmes WHERE framework_id IS NOT NULL
    SELECT DISTINCT framework_id as id FROM framework_audit_programmes WHERE framework_id IS NOT NULL
) f
CROSS JOIN global_audit_processes gp
WHERE gp.is_default = TRUE;

-- Step 8: Add enhanced global processes if they don't exist
INSERT IGNORE INTO global_audit_processes (name, description, category, display_order, is_default) VALUES
-- Enhanced 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),
('Stakeholder Communication', 'Communicate with key stakeholders', 'planning', 7, TRUE),

-- Enhanced Execution Phase  
('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),
('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),

-- Enhanced Reporting Phase
('Draft Report Preparation', 'Prepare draft audit report', 'reporting', 32, TRUE),
('Management Response', 'Obtain management responses to findings', 'reporting', 33, 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),

-- Enhanced Follow-up Phase
('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),

-- Enhanced Closure Phase
('Final Validation', 'Final validation of corrective actions', 'closure', 42, TRUE);

-- Step 9: Optional - Rename old audit_processes table (uncomment if you want to keep it)
-- RENAME TABLE audit_processes TO audit_processes_old;

-- Step 10: Optional - Drop old audit_processes table (CAUTION: Only after confirming migration worked)
-- DROP TABLE IF EXISTS audit_processes;
-- DROP TABLE IF EXISTS audit_processes_backup;

-- Verification queries (run these to check migration success)
-- SELECT COUNT(*) as global_processes FROM global_audit_processes;
-- SELECT COUNT(*) as framework_mappings FROM framework_audit_processes;
-- SELECT COUNT(*) as custom_processes FROM custom_framework_processes;
-- SELECT framework_id, COUNT(*) as process_count FROM framework_audit_processes GROUP BY framework_id;
