-- ISO 17021 Framework Enhancement Tables
-- Created: 2025-01-31
-- Purpose: Audit duration rules, conditional formulas, question banks, and auditor management

-- ================================
-- 1. CONDITIONAL FORMULA BUILDER
-- ================================

-- Framework-specific audit duration rules with conditional logic
CREATE TABLE `framework_audit_duration_rules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `framework_id` int(11) NOT NULL,
  `rule_name` varchar(255) NOT NULL,
  `rule_order` int(11) NOT NULL DEFAULT 0,
  `condition_type` enum('base','conditional') NOT NULL DEFAULT 'conditional',
  `condition_logic` text NULL COMMENT 'JSON structure for IF/AND/OR conditions',
  `condition_display` text NULL COMMENT 'Human readable condition description',
  `action_type` enum('add_days','multiply','set_minimum','set_maximum') NOT NULL DEFAULT 'add_days',
  `action_value` decimal(4,2) NOT NULL DEFAULT 0.00,
  `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`),
  KEY `idx_framework_duration_rules` (`framework_id`),
  KEY `idx_rule_order` (`rule_order`),
  FOREIGN KEY (`framework_id`) REFERENCES `frameworks`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Framework audit cycle programme types
CREATE TABLE `framework_audit_cycles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `framework_id` int(11) NOT NULL,
  `cycle_name` varchar(255) NOT NULL,
  `cycle_type` enum('stage_1','stage_2','stage_1_2_combined','surveillance_1','surveillance_2','surveillance_3','recertification') NOT NULL,
  `default_duration_days` decimal(4,2) NOT NULL DEFAULT 1.50,
  `applies_duration_rules` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Whether to apply conditional duration rules',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `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`),
  KEY `idx_framework_cycles` (`framework_id`),
  KEY `idx_cycle_type` (`cycle_type`),
  FOREIGN KEY (`framework_id`) REFERENCES `frameworks`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================
-- 2. AUDIT DURATION CALCULATOR
-- ================================

-- Variables used in duration calculations (employee_count, site_size, etc.)
CREATE TABLE `framework_duration_variables` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `framework_id` int(11) NOT NULL,
  `variable_name` varchar(100) NOT NULL COMMENT 'e.g., employee_count, site_size, location_count',
  `variable_label` varchar(255) NOT NULL COMMENT 'Human readable label',
  `variable_type` enum('number','boolean','text','select') NOT NULL DEFAULT 'number',
  `variable_options` text NULL COMMENT 'JSON array for select options',
  `default_value` text NULL,
  `is_required` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `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`),
  KEY `idx_framework_variables` (`framework_id`),
  KEY `idx_variable_name` (`variable_name`),
  FOREIGN KEY (`framework_id`) REFERENCES `frameworks`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================
-- 4. QUESTION BANK (WITNESS ASSESSMENTS)
-- ================================

-- Question banks for witness assessments against auditors
CREATE TABLE `framework_witness_question_banks` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`framework_id` INT(11) NOT NULL,
	`bank_name` VARCHAR(255) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`bank_description` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`assessment_type` ENUM('technical_competence','audit_skills','communication','professional_conduct','overall_performance') NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`target_role` ENUM('lead_auditor','auditor','technical_expert','trainee') NOT NULL DEFAULT 'auditor' COLLATE 'utf8mb4_unicode_ci',
	`is_active` TINYINT(1) NOT NULL DEFAULT '1',
	`created` DATETIME NOT NULL DEFAULT current_timestamp(),
	`created_by` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`modified` DATETIME NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
	`modified_by` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	PRIMARY KEY (`id`) USING BTREE,
	INDEX `idx_framework_witness_banks` (`framework_id`) USING BTREE,
	INDEX `idx_assessment_type` (`assessment_type`) USING BTREE,
	INDEX `idx_target_role` (`target_role`) USING BTREE,
	CONSTRAINT `framework_witness_question_banks_ibfk_1` FOREIGN KEY (`framework_id`) REFERENCES `frameworks` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=4
;

-- Individual questions within witness assessment banks
CREATE TABLE `framework_witness_questions` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`question_bank_id` INT(11) NOT NULL,
	`question_text` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`question_type` ENUM('rating_1_5','rating_1_10','yes_no','text','multiple_choice') NOT NULL DEFAULT 'rating_1_5' COLLATE 'utf8mb4_unicode_ci',
	`question_options` TEXT NULL DEFAULT NULL COMMENT 'JSON array for multiple choice options' COLLATE 'utf8mb4_unicode_ci',
	`is_mandatory` TINYINT(1) NOT NULL DEFAULT '1',
	`weight_factor` DECIMAL(3,2) NOT NULL DEFAULT '1.00' COMMENT 'Importance weighting for scoring',
	`sort_order` INT(11) NOT NULL DEFAULT '0',
	`created` DATETIME NOT NULL DEFAULT current_timestamp(),
	`created_by` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`modified` DATETIME NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
	`modified_by` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	PRIMARY KEY (`id`) USING BTREE,
	INDEX `idx_question_bank` (`question_bank_id`) USING BTREE,
	INDEX `idx_question_type` (`question_type`) USING BTREE,
	INDEX `idx_sort_order` (`sort_order`) USING BTREE,
	CONSTRAINT `framework_witness_questions_ibfk_1` FOREIGN KEY (`question_bank_id`) REFERENCES `framework_witness_question_banks` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=11
;

-- ================================
-- 3. AUDITOR PROFILES & COMPETENCIES
-- ================================

-- Auditor profiles and personal information
CREATE TABLE `auditor_profiles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `auditor_code` varchar(50) NOT NULL UNIQUE,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `email` varchar(255) NOT NULL UNIQUE,
  `phone` varchar(50) NULL,
  `status` enum('active','inactive','suspended','retired') NOT NULL DEFAULT 'active',
  `certification_level` enum('trainee','auditor','lead_auditor','technical_expert') NOT NULL DEFAULT 'auditor',
  `employment_type` enum('employee','contractor','partner') NOT NULL DEFAULT 'employee',
  `hire_date` date NULL,
  `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_auditor_code` (`auditor_code`),
  KEY `idx_email` (`email`),
  KEY `idx_status` (`status`),
  KEY `idx_certification_level` (`certification_level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Auditor competencies and qualifications
CREATE TABLE `auditor_competencies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `auditor_id` int(11) NOT NULL,
  `competency_type` enum('standard','industry_sector','technical_area','language') NOT NULL,
  `competency_code` varchar(100) NOT NULL COMMENT 'e.g., ISO9001, ISO14001, Automotive, Healthcare',
  `competency_name` varchar(255) NOT NULL,
  `competency_level` enum('basic','intermediate','advanced','expert') NOT NULL DEFAULT 'basic',
  `qualification_date` date NULL,
  `expiry_date` date NULL,
  `renewal_required` tinyint(1) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `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`),
  KEY `idx_auditor_competencies` (`auditor_id`),
  KEY `idx_competency_type` (`competency_type`),
  KEY `idx_competency_code` (`competency_code`),
  KEY `idx_qualification_date` (`qualification_date`),
  KEY `idx_expiry_date` (`expiry_date`),
  FOREIGN KEY (`auditor_id`) REFERENCES `auditor_profiles`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================
-- 5. CONFLICT OF INTEREST
-- ================================

-- Conflict of interest declarations and tracking
CREATE TABLE `auditor_conflicts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `auditor_id` int(11) NOT NULL,
  `conflict_type` enum('client_employment','financial_interest','family_relation','business_partnership','consultation_services','other') NOT NULL,
  `organization_name` varchar(255) NOT NULL,
  `conflict_description` text NOT NULL,
  `start_date` date NULL,
  `end_date` date NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `impact_level` enum('low','medium','high','prohibitive') NOT NULL DEFAULT 'medium',
  `mitigation_measures` text NULL,
  `approved_by` varchar(100) NULL,
  `approval_date` date NULL,
  `review_required` tinyint(1) NOT NULL DEFAULT 1,
  `next_review_date` date 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`),
  KEY `idx_auditor_conflicts` (`auditor_id`),
  KEY `idx_conflict_type` (`conflict_type`),
  KEY `idx_organization` (`organization_name`),
  KEY `idx_impact_level` (`impact_level`),
  KEY `idx_is_active` (`is_active`),
  KEY `idx_review_date` (`next_review_date`),
  FOREIGN KEY (`auditor_id`) REFERENCES `auditor_profiles`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================
-- SAMPLE DATA
-- ================================

-- Insert sample duration variables
INSERT INTO `framework_duration_variables` (`framework_id`, `variable_name`, `variable_label`, `variable_type`, `default_value`, `sort_order`) VALUES
(1, 'employee_count', 'Number of Employees', 'number', '0', 1),
(1, 'site_size', 'Site Size (sq ft)', 'number', '0', 2),
(1, 'location_count', 'Number of Locations', 'number', '1', 3),
(1, 'complexity_factor', 'Organization Complexity', 'select', 'standard', 4);

-- Insert sample audit cycle types
INSERT INTO `framework_audit_cycles` (`framework_id`, `cycle_name`, `cycle_type`, `default_duration_days`, `sort_order`) VALUES
(1, 'Stage 1 Assessment', 'stage_1', 1.50, 1),
(1, 'Stage 2 Assessment', 'stage_2', 3.00, 2),
(1, 'Combined Stage 1 & 2', 'stage_1_2_combined', 4.00, 3),
(1, 'Surveillance Visit 1', 'surveillance_1', 2.00, 4),
(1, 'Surveillance Visit 2', 'surveillance_2', 2.00, 5),
(1, 'Surveillance Visit 3', 'surveillance_3', 2.00, 6),
(1, 'Recertification Audit', 'recertification', 3.50, 7);

-- Insert sample duration rules
INSERT INTO `framework_audit_duration_rules` (`framework_id`, `rule_name`, `rule_order`, `condition_type`, `condition_logic`, `condition_display`, `action_type`, `action_value`) VALUES
(1, 'Base Duration', 1, 'base', '{"type":"base","value":1.5}', 'Minimum base duration of 1.5 days', 'set_minimum', 1.50),
(1, 'Medium Organization', 2, 'conditional', '{"type":"and","conditions":[{"variable":"employee_count","operator":"gt","value":100},{"variable":"employee_count","operator":"lt","value":200}]}', 'IF employee count > 100 AND < 200', 'add_days', 0.50),
(1, 'Large Organization', 3, 'conditional', '{"type":"and","conditions":[{"variable":"employee_count","operator":"gte","value":200},{"variable":"employee_count","operator":"lt","value":300}]}', 'IF employee count >= 200 AND < 300', 'add_days', 0.75),
(1, 'Large Site', 4, 'conditional', '{"type":"condition","conditions":[{"variable":"site_size","operator":"gt","value":10000}]}', 'IF site size > 10,000 sq ft', 'add_days', 0.50),
(1, 'Multiple Locations', 5, 'conditional', '{"type":"condition","conditions":[{"variable":"location_count","operator":"gt","value":1}]}', 'IF location count > 1', 'add_days', 0.50);

-- Insert sample witness question bank
INSERT INTO `framework_witness_question_banks` (`framework_id`, `bank_name`, `bank_description`, `assessment_type`, `target_role`) VALUES
(1, 'Technical Competence Assessment', 'Questions to assess auditor technical knowledge and application', 'technical_competence', 'auditor'),
(1, 'Audit Skills Evaluation', 'Questions to evaluate audit methodology and execution skills', 'audit_skills', 'auditor'),
(1, 'Communication Assessment', 'Questions to assess communication and interpersonal skills', 'communication', 'auditor');

-- Insert sample witness questions
INSERT INTO `framework_witness_questions` (`question_bank_id`, `question_text`, `question_type`, `weight_factor`, `sort_order`) VALUES
(1, 'Demonstrated understanding of the standard requirements', 'rating_1_5', 1.50, 1),
(1, 'Applied standard requirements correctly to organization context', 'rating_1_5', 1.25, 2),
(1, 'Identified nonconformities accurately', 'rating_1_5', 1.50, 3),
(2, 'Conducted effective opening meeting', 'rating_1_5', 1.00, 1),
(2, 'Maintained professional demeanor throughout audit', 'rating_1_5', 1.00, 2),
(2, 'Effectively managed audit time and schedule', 'rating_1_5', 1.25, 3),
(3, 'Communicated clearly with auditees', 'rating_1_5', 1.00, 1),
(3, 'Asked appropriate and effective questions', 'rating_1_5', 1.25, 2),
(3, 'Provided constructive feedback', 'rating_1_5', 1.00, 3);

-- Add indexes for performance
CREATE INDEX `idx_framework_duration_rules_framework` ON `framework_audit_duration_rules` (`framework_id`, `is_active`, `rule_order`);
CREATE INDEX `idx_framework_cycles_framework` ON `framework_audit_cycles` (`framework_id`, `is_active`, `sort_order`);
CREATE INDEX `idx_auditor_competencies_search` ON `auditor_competencies` (`auditor_id`, `is_active`, `competency_type`);
CREATE INDEX `idx_auditor_conflicts_active` ON `auditor_conflicts` (`auditor_id`, `is_active`, `impact_level`);
