-- Create framework_procedures table for framework-specific audit procedure documents
CREATE TABLE IF NOT EXISTS framework_procedures (
    id INT PRIMARY KEY AUTO_INCREMENT,
    framework_id INT NOT NULL,
    
    -- General Information
    accreditation_status ENUM('accredited', 'non_accredited') DEFAULT 'accredited',
    accreditation_body VARCHAR(255),
    accreditation_number VARCHAR(100),
    
    -- Quote and Contracts
    client_application_process TEXT,
    duration_rules TEXT,
    scope_building_process TEXT,
    contract_requirements TEXT,
    multisite_certification BOOLEAN DEFAULT FALSE,
    multisite_process TEXT,
    integrated_audits BOOLEAN DEFAULT FALSE,
    integrated_process TEXT,
    transfer_approval_process TEXT,
    remote_audits_allowed BOOLEAN DEFAULT TRUE,
    remote_audit_conditions TEXT,
    
    -- Conduction
    team_selection_criteria TEXT,
    assessment_process_overview TEXT,
    audit_approach ENUM('announced', 'unannounced', 'both') DEFAULT 'announced',
    findings_management_process TEXT,
    closure_process TEXT,
    
    -- Technical Review
    technical_review_process TEXT,
    technical_reviewer_requirements TEXT,
    
    -- Certificate and Deliverables
    certificate_format TEXT,
    deliverables_list TEXT,
    certificate_validity_period INT DEFAULT 3, -- years
    
    -- Suspension and Withdrawal
    suspension_criteria TEXT,
    withdrawal_criteria TEXT,
    suspension_process TEXT,
    withdrawal_process TEXT,
    
    -- Public Information
    public_info_policy TEXT,
    certificate_publicity BOOLEAN DEFAULT TRUE,
    
    -- Feedback and Complaints
    feedback_process TEXT,
    complaints_process TEXT,
    appeals_process TEXT,
    
    -- Version Control
    document_version VARCHAR(20) DEFAULT '1.0',
    approval_date DATE,
    next_review_date DATE,
    approved_by VARCHAR(255),
    
    FOREIGN KEY (framework_id) REFERENCES frameworks(id) ON DELETE CASCADE,
    INDEX idx_framework_id (framework_id)
);

-- Insert default procedure for ISO 14001 (framework_id = 1)
INSERT INTO framework_procedures (
    framework_id,
    accreditation_status,
    client_application_process,
    duration_rules,
    scope_building_process,
    team_selection_criteria,
    assessment_process_overview,
    technical_review_process,
    certificate_validity_period,
    document_version,
    approved_by
) VALUES (
    1,
    'accredited',
    'Client submits formal application with organizational details, scope of certification, and supporting documentation.',
    'Audit duration calculated based on organizational size, complexity, and risk factors according to IAF MD5 guidelines.',
    'Certification scope defined through detailed review of organizational activities, processes, and applicable requirements.',
    'Auditors selected based on technical competence, industry experience, and absence of conflicts of interest.',
    'Two-stage audit process: Stage 1 (documentation review and readiness assessment) and Stage 2 (on-site implementation assessment).',
    'Independent technical review of audit findings and certification recommendation by qualified technical reviewer.',
    3,
    '1.0',
    'System Administrator'
) ON DUPLICATE KEY UPDATE
    framework_id = VALUES(framework_id);
