-- Framework Auditors Junction Table
-- Links auditors to specific frameworks they are approved for

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_name` varchar(255) NOT NULL COMMENT 'Denormalized name for quick display',
  `certification_body` varchar(255) NULL COMMENT 'Certification body for this framework',
  `is_lead_auditor` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Can lead audits for this framework',
  `status` enum('active','inactive','suspended') NOT NULL DEFAULT 'active',
  `specializations` text NULL COMMENT 'Framework-specific specializations',
  `approval_date` date NULL COMMENT 'Date approved for this framework',
  `expiry_date` date NULL COMMENT 'Approval expiry date',
  `notes` text NULL,
  `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`),
  KEY `idx_framework_auditors_framework` (`framework_id`),
  KEY `idx_framework_auditors_auditor` (`auditor_id`),
  KEY `idx_framework_auditors_status` (`status`),
  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='Links auditors to frameworks they are approved to audit';
