-- Add missing fields to framework_auditors table for phase management
-- Run this after creating the base tables

-- Add qualified_phases field to framework_auditors
ALTER TABLE `framework_auditors` 
ADD COLUMN `qualified_phases` set('stage_1','stage_2','surveillance','recertification','special','follow_up') NULL 
COMMENT 'Which audit phases this auditor is qualified to conduct' 
AFTER `industry_codes`;

-- Add auditor_code generator trigger or default if not exists
-- This ensures every framework auditor gets a unique code
-- Note: You may need to populate existing records manually

-- Sample data for testing (optional)
-- UPDATE framework_auditors SET qualified_phases = 'stage_1,stage_2,surveillance' WHERE id > 0;

-- Create index for qualified_phases
ALTER TABLE `framework_auditors` 
ADD KEY `idx_framework_auditors_phases` (`qualified_phases`);

-- Ensure auditor_code has a default value mechanism
-- This can be handled in PHP instead of triggers for simplicity
