-- ================================
-- FRAMEWORK EMPLOYEE ASSIGNMENT SYSTEM
-- ================================
-- This system follows the same pattern as framework_auditor_requirements
-- but extends it to cover all employee roles within each framework

-- ================================
-- 1. FRAMEWORK EMPLOYEE REQUIREMENTS TABLE (STEP 1: Define Requirements)
-- ================================
-- Requirements/competencies needed for employees in specific frameworks and roles
CREATE TABLE `framework_employee_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-PM, ISO27001-TS)',
  `role_type` enum('project_manager','account_manager','sales_representative','technical_specialist','admin_support','quality_manager','compliance_officer','other') NOT NULL,
  `category` enum('certification','experience','technical','industry','language','software','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('pre_audit','audit_execution','post_audit','client_management','reporting','follow_up') NULL COMMENT 'Which 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','portfolio','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',
  `required_quantity` int(11) NOT NULL DEFAULT 1 COMMENT 'How many employees with this requirement are needed',
  `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_employee_requirement_reference` (`framework_id`, `reference`),
  KEY `idx_framework_employee_requirements_framework` (`framework_id`),
  KEY `idx_framework_employee_requirements_role` (`role_type`),
  KEY `idx_framework_employee_requirements_category` (`category`),
  KEY `idx_framework_employee_requirements_priority` (`priority`),
  KEY `idx_framework_employee_requirements_mandatory` (`mandatory`),
  KEY `idx_framework_employee_requirements_active` (`is_active`),
  CONSTRAINT `fk_framework_employee_requirements_framework` FOREIGN KEY (`framework_id`) REFERENCES `frameworks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Define employee requirements for specific frameworks and roles';

-- ================================
-- 2. FRAMEWORK EMPLOYEES TABLE (STEP 2: Assign Employees to Frameworks)
-- ================================
-- Assign specific employees to specific frameworks with their roles
CREATE TABLE `framework_employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `framework_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL COMMENT 'References hr_employee.id',
  `role_type` enum('project_manager','account_manager','sales_representative','technical_specialist','admin_support','quality_manager','compliance_officer','other') NOT NULL,
  `assignment_date` date NOT NULL,
  `assignment_status` enum('active','inactive','pending','suspended') NOT NULL DEFAULT 'active',
  `primary_assignment` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Is this their primary framework assignment for this role',
  `availability_percentage` decimal(5,2) NOT NULL DEFAULT 100.00 COMMENT 'Percentage of time available for this framework',
  `assignment_reason` text NULL COMMENT 'Reason for assignment',
  `assigned_by` varchar(100) DEFAULT NULL,
  `effective_from` date NULL,
  `effective_to` date NULL,
  `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_employee_role` (`framework_id`, `employee_id`, `role_type`),
  KEY `idx_framework_employees_framework` (`framework_id`),
  KEY `idx_framework_employees_employee` (`employee_id`),
  KEY `idx_framework_employees_role` (`role_type`),
  KEY `idx_framework_employees_status` (`assignment_status`),
  KEY `idx_framework_employees_primary` (`primary_assignment`),
  KEY `idx_framework_employees_active` (`is_active`),
  CONSTRAINT `fk_framework_employees_framework` FOREIGN KEY (`framework_id`) REFERENCES `frameworks` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_framework_employees_employee` FOREIGN KEY (`employee_id`) REFERENCES `hr_employee` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Assign employees to frameworks with specific roles';

-- ================================
-- 3. FRAMEWORK EMPLOYEE REQUIREMENT COMPLIANCE TABLE (STEP 3: Track Compliance)
-- ================================
-- Track how well assigned employees meet the requirements
CREATE TABLE `framework_employee_requirement_compliance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_assignment_id` int(11) NOT NULL COMMENT 'References framework_employees.id',
  `requirement_id` int(11) NOT NULL COMMENT 'References framework_employee_requirements.id',
  `compliance_status` enum('compliant','non_compliant','partial','pending','not_applicable') NOT NULL DEFAULT 'pending',
  `compliance_percentage` decimal(5,2) DEFAULT NULL COMMENT 'Percentage of compliance (0-100)',
  `evidence_type` enum('certificate','document','assessment','experience_record','competency_matrix','other') NULL,
  `evidence_details` text NULL COMMENT 'Details of evidence provided',
  `evidence_file_path` varchar(500) NULL COMMENT 'Path to evidence file',
  `verified_by` varchar(100) DEFAULT NULL,
  `verification_date` date NULL,
  `expiry_date` date NULL COMMENT 'When compliance expires (e.g., certificate expiry)',
  `next_review_date` date NULL,
  `compliance_notes` text NULL,
  `action_required` text NULL COMMENT 'What actions are needed to achieve compliance',
  `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_employee_requirement_compliance` (`employee_assignment_id`, `requirement_id`),
  KEY `idx_compliance_employee_assignment` (`employee_assignment_id`),
  KEY `idx_compliance_requirement` (`requirement_id`),
  KEY `idx_compliance_status` (`compliance_status`),
  KEY `idx_compliance_verified` (`verified_by`),
  KEY `idx_compliance_expiry` (`expiry_date`),
  KEY `idx_compliance_active` (`is_active`),
  CONSTRAINT `fk_compliance_employee_assignment` FOREIGN KEY (`employee_assignment_id`) REFERENCES `framework_employees` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_compliance_requirement` FOREIGN KEY (`requirement_id`) REFERENCES `framework_employee_requirements` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Track compliance of employee assignments with framework requirements';

-- ================================
-- 4. VIEWS FOR REPORTING AND DASHBOARD
-- ================================

-- View for employee assignment overview
CREATE VIEW `framework_employee_assignment_overview` AS
SELECT 
    fe.id as assignment_id,
    fe.framework_id,
    f.framework_name,
    fe.employee_id,
    e.full_name as employee_name,
    e.employee_id as employee_number,
    e.work_email_address,
    e.department,
    e.designation,
    fe.role_type,
    fe.assignment_status,
    fe.primary_assignment,
    fe.availability_percentage,
    fe.assignment_date,
    fe.effective_from,
    fe.effective_to,
    -- Compliance summary
    COUNT(ferc.id) as total_requirements,
    SUM(CASE WHEN ferc.compliance_status = 'compliant' THEN 1 ELSE 0 END) as compliant_requirements,
    SUM(CASE WHEN ferc.compliance_status = 'non_compliant' THEN 1 ELSE 0 END) as non_compliant_requirements,
    SUM(CASE WHEN ferc.compliance_status = 'pending' THEN 1 ELSE 0 END) as pending_requirements,
    CASE 
        WHEN COUNT(ferc.id) = 0 THEN 0 
        ELSE ROUND((SUM(CASE WHEN ferc.compliance_status = 'compliant' THEN 1 ELSE 0 END) / COUNT(ferc.id)) * 100, 2) 
    END as compliance_percentage
FROM framework_employees fe
JOIN frameworks f ON fe.framework_id = f.id
JOIN hr_employee e ON fe.employee_id = e.id
LEFT JOIN framework_employee_requirement_compliance ferc ON fe.id = ferc.employee_assignment_id AND ferc.is_active = 1
WHERE fe.is_active = 1
GROUP BY fe.id, fe.framework_id, f.framework_name, fe.employee_id, e.full_name, e.employee_id, e.work_email_address, 
         e.department, e.designation, fe.role_type, fe.assignment_status, fe.primary_assignment, 
         fe.availability_percentage, fe.assignment_date, fe.effective_from, fe.effective_to;

-- View for framework requirement coverage
CREATE VIEW `framework_employee_requirement_coverage` AS
SELECT 
    fer.id as requirement_id,
    fer.framework_id,
    f.framework_name,
    fer.reference,
    fer.role_type,
    fer.requirement_title,
    fer.priority,
    fer.mandatory,
    fer.required_quantity,
    -- Coverage analysis
    COUNT(fe.id) as assigned_employees,
    SUM(CASE WHEN ferc.compliance_status = 'compliant' THEN 1 ELSE 0 END) as compliant_employees,
    CASE 
        WHEN fer.required_quantity <= COUNT(fe.id) THEN 'Adequate'
        WHEN COUNT(fe.id) > 0 THEN 'Partial'
        ELSE 'Not Covered'
    END as coverage_status,
    CASE 
        WHEN COUNT(fe.id) = 0 THEN 0 
        ELSE ROUND((COUNT(fe.id) / fer.required_quantity) * 100, 2) 
    END as coverage_percentage
FROM framework_employee_requirements fer
JOIN frameworks f ON fer.framework_id = f.id
LEFT JOIN framework_employees fe ON fer.framework_id = fe.framework_id AND fer.role_type = fe.role_type AND fe.is_active = 1
LEFT JOIN framework_employee_requirement_compliance ferc ON fe.id = ferc.employee_assignment_id AND fer.id = ferc.requirement_id AND ferc.is_active = 1
WHERE fer.is_active = 1
GROUP BY fer.id, fer.framework_id, f.framework_name, fer.reference, fer.role_type, fer.requirement_title, 
         fer.priority, fer.mandatory, fer.required_quantity;

-- ================================
-- 5. SAMPLE DATA
-- ================================

-- Sample framework employee requirements
INSERT INTO `framework_employee_requirements` 
(`framework_id`, `reference`, `role_type`, `category`, `requirement_title`, `description`, `priority`, `competency_level`, `mandatory`, `required_quantity`, `created_by`) VALUES
(1, 'ISO9001-PM-01', 'project_manager', 'certification', 'ISO 9001 Foundation Knowledge', 'Project manager must have ISO 9001 foundation certification or equivalent knowledge', 'High', 'intermediate', 1, 1, 'system'),
(1, 'ISO9001-PM-02', 'project_manager', 'experience', 'Quality Management Experience', 'Minimum 2 years experience in quality management systems', 'High', 'intermediate', 1, 1, 'system'),
(1, 'ISO9001-AM-01', 'account_manager', 'experience', 'Client Relationship Management', 'Experience in managing certification clients and understanding their needs', 'Medium', 'basic', 1, 1, 'system'),
(1, 'ISO9001-TS-01', 'technical_specialist', 'certification', 'ISO 9001 Lead Auditor', 'Technical specialist should be a qualified ISO 9001 lead auditor', 'High', 'advanced', 1, 1, 'system'),
(1, 'ISO9001-QM-01', 'quality_manager', 'certification', 'Quality Management Qualification', 'Quality manager must have formal quality management qualification', 'High', 'expert', 1, 1, 'system');

-- Note: To use this system:
-- 1. First define employee requirements for each framework and role in framework_employee_requirements
-- 2. Then assign employees to frameworks with their roles in framework_employees  
-- 3. Track compliance with requirements in framework_employee_requirement_compliance
-- 4. Use the views for reporting and dashboard displays
