-- Missing Framework Tables
-- These tables are required for the auditor management functionality

-- WORKFLOW:
-- 1. Define framework auditor requirements (what qualifications are needed)
-- 2. Select auditors from auditor_profiles who meet requirements  
-- 3. Assign auditor codes for industry-specific job allocation
-- 4. Use for validation during job allocation

-- ================================
-- 1. FRAMEWORK AUDITOR REQUIREMENTS TABLE (STEP 1: Define Requirements)
-- ================================
-- Requirements/competencies needed for auditors in specific frameworks
CREATE TABLE `framework_auditor_requirements` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `framework_id` int(11) NOT NULL,
  `reference` varchar(100) NOT NULL COMMENT 'Requirement reference (e.g., ISO9001-LEAD)',
  `category` enum('certification','experience','technical','industry','language','other') NOT NULL DEFAULT 'technical',
  `requirement_title` varchar(255) NOT NULL COMMENT 'Short title of requirement',
  `description` text NOT NULL COMMENT 'Detailed requirement description',
  `priority` enum('High','Medium','Low') NOT NULL DEFAULT 'Medium',
  `applicable_phases` set('stage_1','stage_2','surveillance','recertification','special','follow_up') NULL COMMENT 'Which audit phases this applies to',
  `competency_level` enum('basic','intermediate','advanced','expert') NOT NULL DEFAULT 'basic',
  `mandatory` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Is this requirement mandatory',
  `verification_method` enum('certificate','experience','assessment','competency_record','other') NOT NULL DEFAULT 'certificate',
  `industry_codes` text NULL COMMENT 'Specific industry codes this requirement applies to (comma separated)',
  `minimum_experience_years` decimal(3,1) NULL COMMENT 'Minimum years of experience required',
  `notes` text NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` varchar(100) DEFAULT NULL,
  `modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified_by` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_framework_requirement_reference` (`framework_id`, `reference`),
  KEY `idx_framework_requirements_framework` (`framework_id`),
  KEY `idx_framework_requirements_category` (`category`),
  KEY `idx_framework_requirements_priority` (`priority`),
  KEY `idx_framework_requirements_mandatory` (`mandatory`),
  KEY `idx_framework_requirements_active` (`is_active`),
  FOREIGN KEY (`framework_id`) REFERENCES `frameworks`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='Step 1: Define requirements for auditors in specific frameworks';

-- ================================
-- 2. FRAMEWORK AUDITORS TABLE (STEP 2: Select Qualified Auditors)
-- ================================
-- Links auditors to frameworks based on meeting requirements
CREATE TABLE `framework_auditors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `framework_id` int(11) NOT NULL,
  `auditor_id` int(11) NOT NULL COMMENT 'References auditor_profiles.id',
  `auditor_code` varchar(50) NOT NULL COMMENT 'Framework-specific auditor code for job allocation',
  `auditor_name` varchar(255) NOT NULL COMMENT 'Denormalized name for quick display',
  `certification_body` varchar(255) NULL COMMENT 'Primary certification body for this framework',
  `is_lead_auditor` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Qualified to lead audits for this framework',
  `is_technical_expert` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Can serve as technical expert',
  `status` enum('active','inactive','suspended','pending_approval') NOT NULL DEFAULT 'pending_approval',
  `specializations` text NULL COMMENT 'Framework-specific specializations/industry sectors',
  `industry_codes` text NULL COMMENT 'Industry codes this auditor is qualified for (comma separated)',
  `approval_date` date NULL COMMENT 'Date approved for this framework',
  `expiry_date` date NULL COMMENT 'Framework approval expiry date',
  `last_audit_date` date NULL COMMENT 'Date of last audit in this framework',
  `audit_count` int(11) NOT NULL DEFAULT 0 COMMENT 'Number of audits conducted in this framework',
  `notes` text NULL,
  `requirements_verified` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'All requirements have been verified',
  `verified_by` varchar(100) NULL COMMENT 'Who verified the requirements',
  `verified_date` datetime NULL COMMENT 'When requirements were verified',
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` varchar(100) DEFAULT NULL,
  `modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified_by` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_framework_auditor_unique` (`framework_id`, `auditor_id`),
  UNIQUE KEY `idx_framework_auditor_code` (`framework_id`, `auditor_code`),
  KEY `idx_framework_auditors_framework` (`framework_id`),
  KEY `idx_framework_auditors_auditor` (`auditor_id`),
  KEY `idx_framework_auditors_status` (`status`),
  KEY `idx_framework_auditors_code` (`auditor_code`),
  KEY `idx_framework_auditors_lead` (`is_lead_auditor`),
  KEY `idx_framework_auditors_verified` (`requirements_verified`),
  FOREIGN KEY (`framework_id`) REFERENCES `frameworks`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`auditor_id`) REFERENCES `auditor_profiles`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='Step 2: Auditors approved for frameworks after meeting requirements';

-- ================================
-- 3. FRAMEWORK AUDITOR REQUIREMENT COMPLIANCE TABLE (STEP 3: Track Compliance)
-- ================================
-- Tracks which auditors meet which requirements for validation
CREATE TABLE `framework_auditor_requirement_compliance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `framework_auditor_id` int(11) NOT NULL COMMENT 'References framework_auditors.id',
  `requirement_id` int(11) NOT NULL COMMENT 'References framework_auditor_requirements.id',
  `compliance_status` enum('compliant','non_compliant','pending','expired','waived') NOT NULL DEFAULT 'pending',
  `verification_date` date NULL,
  `expiry_date` date NULL,
  `evidence_type` enum('certificate','experience_record','assessment_result','competency_record','other') NULL,
  `evidence_reference` varchar(255) NULL COMMENT 'Certificate number, file reference, etc.',
  `evidence` text NULL COMMENT 'Description of evidence provided',
  `assessor_notes` text NULL COMMENT 'Notes from person who verified compliance',
  `verified_by` varchar(100) NULL COMMENT 'Who verified this compliance',
  `waiver_reason` text NULL COMMENT 'Reason if compliance was waived',
  `next_review_date` date NULL COMMENT 'When this compliance should be reviewed again',
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` varchar(100) DEFAULT NULL,
  `modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified_by` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_auditor_requirement_unique` (`framework_auditor_id`, `requirement_id`),
  KEY `idx_compliance_framework_auditor` (`framework_auditor_id`),
  KEY `idx_compliance_requirement` (`requirement_id`),
  KEY `idx_compliance_status` (`compliance_status`),
  KEY `idx_compliance_expiry` (`expiry_date`),
  KEY `idx_compliance_review` (`next_review_date`),
  FOREIGN KEY (`framework_auditor_id`) REFERENCES `framework_auditors`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`requirement_id`) REFERENCES `framework_auditor_requirements`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='Step 3: Tracks compliance with requirements for job allocation validation';

-- ================================
-- 4. AUDITOR CODE ALLOCATION TABLE (STEP 4: Industry-Specific Codes)
-- ================================
-- Manages auditor code allocation for industry-specific job assignments
CREATE TABLE `auditor_code_allocations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `framework_auditor_id` int(11) NOT NULL COMMENT 'References framework_auditors.id',
  `industry_code` varchar(20) NOT NULL COMMENT 'Industry code (e.g., NACE, SIC codes)',
  `industry_name` varchar(255) NOT NULL COMMENT 'Industry description',
  `auditor_industry_code` varchar(50) NOT NULL COMMENT 'Specific code for this auditor in this industry',
  `competency_level` enum('basic','intermediate','advanced','expert') NOT NULL DEFAULT 'basic',
  `qualification_date` date NULL COMMENT 'Date qualified for this industry',
  `expiry_date` date NULL COMMENT 'Qualification expiry date',
  `max_company_size` enum('micro','small','medium','large','any') NOT NULL DEFAULT 'any',
  `max_audit_days` decimal(4,1) NULL COMMENT 'Maximum audit days this auditor can handle in this industry',
  `specialization_notes` text NULL COMMENT 'Specific areas of expertise within this industry',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` varchar(100) DEFAULT NULL,
  `modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified_by` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_auditor_industry_code` (`framework_auditor_id`, `industry_code`),
  UNIQUE KEY `idx_industry_auditor_code` (`auditor_industry_code`, `industry_code`),
  KEY `idx_code_allocations_framework_auditor` (`framework_auditor_id`),
  KEY `idx_code_allocations_industry` (`industry_code`),
  KEY `idx_code_allocations_active` (`is_active`),
  KEY `idx_code_allocations_competency` (`competency_level`),
  FOREIGN KEY (`framework_auditor_id`) REFERENCES `framework_auditors`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='Step 4: Industry-specific auditor codes for job allocation';

-- ================================
-- SAMPLE DATA FOR WORKFLOW
-- ================================

-- Step 1: Sample framework auditor requirements for ISO 9001
INSERT INTO `framework_auditor_requirements` 
(`framework_id`, `reference`, `category`, `requirement_title`, `description`, `priority`, `applicable_phases`, `competency_level`, `mandatory`, `verification_method`, `minimum_experience_years`) 
VALUES 
(1, 'ISO9001-LEAD', 'certification', 'ISO 9001 Lead Auditor Certification', 'Valid ISO 9001 Lead Auditor certification from accredited training provider', 'High', 'stage_1,stage_2,surveillance,recertification', 'advanced', 1, 'certificate', 0),
(1, 'ISO9001-EXP-LEAD', 'experience', 'Lead Auditor Experience', 'Minimum 3 years experience as lead auditor in ISO 9001 audits', 'High', 'stage_2,surveillance,recertification', 'advanced', 1, 'experience', 3.0),
(1, 'ISO9001-EXP-TEAM', 'experience', 'Team Member Experience', 'Minimum 1 year experience in ISO 9001 audits as team member', 'Medium', 'stage_1,stage_2', 'intermediate', 1, 'experience', 1.0),
(1, 'QMS-KNOWLEDGE', 'technical', 'QMS Technical Knowledge', 'In-depth knowledge of Quality Management Systems principles and ISO 9001 requirements', 'High', 'stage_1,stage_2', 'advanced', 1, 'assessment', 0),
(1, 'RISK-ASSESS', 'technical', 'Risk-Based Thinking', 'Understanding and application of risk-based thinking in quality management', 'Medium', 'stage_1,stage_2', 'intermediate', 1, 'competency_record', 0),
(1, 'AUTOMOTIVE-SPEC', 'industry', 'Automotive Industry Specialization', 'Specific knowledge of automotive industry requirements and standards', 'Medium', 'stage_1,stage_2,surveillance', 'intermediate', 0, 'certificate', 2.0);
