-- AUTO TESTER - Team Inspection System Migration
-- Created: 2026-04-22
-- Description: Multi-inspector collaborative inspection system for main centers

-- ============================================
-- 1. Team Management Tables
-- ============================================

-- Inspection Teams
CREATE TABLE IF NOT EXISTS `inspection_teams` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `team_name` VARCHAR(100) NOT NULL,
    `team_code` VARCHAR(20) UNIQUE NOT NULL COMMENT 'Unique team identifier',
    `team_description` TEXT,
    `team_leader_id` INT NOT NULL COMMENT 'Lead inspector ID',
    `center_id` INT NOT NULL COMMENT 'Main center ID',
    `specialization` ENUM('general', 'engine', 'electrical', 'body', 'diagnostic', 'heavy_vehicle') DEFAULT 'general',
    `max_team_size` INT DEFAULT 5,
    `current_team_size` INT DEFAULT 0,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`team_leader_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`center_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    
    INDEX `idx_team_leader` (`team_leader_id`),
    INDEX `idx_center_id` (`center_id`),
    INDEX `idx_specialization` (`specialization`),
    INDEX `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Team Members
CREATE TABLE IF NOT EXISTS `team_members` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `team_id` INT NOT NULL,
    `inspector_id` INT NOT NULL,
    `role_in_team` ENUM('leader', 'senior_inspector', 'inspector', 'junior_inspector', 'assistant') NOT NULL,
    `specialization_area` VARCHAR(100) COMMENT 'Area of expertise',
    `joined_team_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `is_active` BOOLEAN DEFAULT TRUE,
    `assigned_by` INT COMMENT 'Who assigned this member',
    
    FOREIGN KEY (`team_id`) REFERENCES `inspection_teams`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`inspector_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`assigned_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    
    UNIQUE KEY `unique_team_inspector` (`team_id`, `inspector_id`),
    INDEX `idx_inspector_id` (`inspector_id`),
    INDEX `idx_role_in_team` (`role_in_team`),
    INDEX `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 2. Team Inspection Assignment Tables
-- ============================================

-- Team Inspection Requests
CREATE TABLE IF NOT EXISTS `team_inspection_requests` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `request_number` VARCHAR(50) UNIQUE NOT NULL,
    `team_id` INT NOT NULL,
    `primary_vehicle_id` INT COMMENT 'Main vehicle being inspected',
    `inspection_type_id` INT NOT NULL,
    `customer_id` INT NOT NULL,
    `requested_by` INT NOT NULL,
    `priority_level` ENUM('low', 'medium', 'high', 'urgent', 'emergency') DEFAULT 'medium',
    `inspection_mode` ENUM('single_vehicle', 'multiple_vehicles', 'fleet') DEFAULT 'single_vehicle',
    `estimated_duration_minutes` INT DEFAULT 60,
    `scheduled_start_time` DATETIME,
    `scheduled_end_time` DATETIME,
    `actual_start_time` DATETIME,
    `actual_end_time` DATETIME,
    `location_type` ENUM('center', 'on_site', 'customer_location') DEFAULT 'center',
    `location_address` TEXT,
    `special_instructions` TEXT,
    `equipment_needed` JSON COMMENT 'Equipment required for team inspection',
    `safety_requirements` JSON COMMENT 'Safety requirements',
    `status` ENUM('assigned', 'in_progress', 'paused', 'completed', 'cancelled') DEFAULT 'assigned',
    `completion_percentage` DECIMAL(5,2) DEFAULT 0,
    `team_leader_notes` TEXT,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`team_id`) REFERENCES `inspection_teams`(`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`primary_vehicle_id`) REFERENCES `vehicles`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`inspection_type_id`) REFERENCES `inspection_types`(`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`customer_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`requested_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    
    INDEX `idx_team_id` (`team_id`),
    INDEX `inspection_type_id` (`inspection_type_id`),
    INDEX `idx_customer_id` (`customer_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_priority_level` (`priority_level`),
    INDEX `idx_scheduled_start_time` (`scheduled_start_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Multiple Vehicles for Team Inspection
CREATE TABLE IF NOT EXISTS `team_inspection_vehicles` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `team_request_id` INT NOT NULL,
    `vehicle_id` INT NOT NULL,
    `vehicle_sequence` INT DEFAULT 1 COMMENT 'Order in which vehicle should be inspected',
    `inspection_priority` ENUM('primary', 'secondary', 'reference') DEFAULT 'secondary',
    `assigned_inspectors` JSON COMMENT 'Array of inspector IDs assigned to this vehicle',
    `inspection_status` ENUM('pending', 'in_progress', 'completed', 'skipped') DEFAULT 'pending',
    `start_time` DATETIME,
    `end_time` DATETIME,
    `notes` TEXT,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`team_request_id`) REFERENCES `team_inspection_requests`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles`(`id`) ON DELETE CASCADE,
    
    INDEX `idx_team_request_id` (`team_request_id`),
    INDEX `idx_vehicle_id` (`vehicle_id`),
    INDEX `idx_inspection_status` (`inspection_status`),
    INDEX `idx_vehicle_sequence` (`vehicle_sequence`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 3. Collaborative Inspection Tables
-- ============================================

-- Team Inspection Tasks
CREATE TABLE IF NOT EXISTS `team_inspection_tasks` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `team_request_id` INT NOT NULL,
    `task_name` VARCHAR(200) NOT NULL,
    `task_description` TEXT,
    `task_category` ENUM('visual', 'mechanical', 'electrical', 'diagnostic', 'safety', 'documentation') NOT NULL,
    `assigned_inspector_id` INT NOT NULL,
    `support_inspectors` JSON COMMENT 'Array of supporting inspector IDs',
    `vehicle_id` INT COMMENT 'Specific vehicle this task applies to',
    `inspection_point_id` INT COMMENT 'Link to inspection points table',
    `task_order` INT DEFAULT 0 COMMENT 'Order of task execution',
    `estimated_duration_minutes` INT DEFAULT 30,
    `actual_duration_minutes` INT,
    `task_status` ENUM('assigned', 'in_progress', 'completed', 'failed', 'skipped') DEFAULT 'assigned',
    `completion_percentage` DECIMAL(5,2) DEFAULT 0,
    `task_dependencies` JSON COMMENT 'Array of task IDs that must be completed first',
    `required_tools` JSON COMMENT 'Tools needed for this task',
    `safety_precautions` JSON COMMENT 'Safety measures for this task',
    `task_notes` TEXT,
    `task_result_data` JSON COMMENT 'Results and findings from this task',
    `assigned_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `started_at` DATETIME,
    `completed_at` DATETIME,
    
    FOREIGN KEY (`team_request_id`) REFERENCES `team_inspection_requests`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`assigned_inspector_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`inspection_point_id`) REFERENCES `inspection_points`(`id`) ON DELETE SET NULL,
    
    INDEX `idx_team_request_id` (`team_request_id`),
    INDEX `idx_assigned_inspector` (`assigned_inspector`),
    INDEX `idx_task_status` (`task_status`),
    INDEX `idx_task_category` (`task_category`),
    INDEX `idx_task_order` (`task_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Team Inspection Checkpoints
CREATE TABLE IF NOT EXISTS `team_inspection_checkpoints` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `team_request_id` INT NOT NULL,
    `checkpoint_name` VARCHAR(200) NOT NULL,
    `checkpoint_type` ENUM('milestone', 'safety_check', 'quality_gate', 'progress_review') NOT NULL,
    `checkpoint_description` TEXT,
    `required_approvals` JSON COMMENT 'Array of inspector roles that must approve',
    `checkpoint_status` ENUM('pending', 'in_review', 'approved', 'rejected', 'skipped') DEFAULT 'pending',
    `approvals_received` JSON COMMENT 'Array of approval records',
    `checkpoint_notes` TEXT,
    `checkpoint_order` INT DEFAULT 0,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`team_request_id`) REFERENCES `team_inspection_requests`(`id`) ON DELETE CASCADE,
    
    INDEX `idx_team_request_id` (`team_request_id`),
    INDEX `idx_checkpoint_type` (`checkpoint_type`),
    INDEX `idx_checkpoint_status` (`checkpoint_status`),
    INDEX `idx_checkpoint_order` (`checkpoint_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 4. Real-time Collaboration Tables
-- ============================================

-- Team Communication Log
CREATE TABLE IF NOT EXISTS `team_communication_log` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `team_request_id` INT NOT NULL,
    `sender_id` INT NOT NULL,
    `message_type` ENUM('text', 'image', 'file', 'alert', 'status_update') NOT NULL,
    `message_content` TEXT NOT NULL,
    `message_data` JSON COMMENT 'Additional data for images, files, etc.',
    `recipients` JSON COMMENT 'Array of recipient IDs (empty for all team members)',
    `is_urgent` BOOLEAN DEFAULT FALSE,
    `message_priority` ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    `delivery_status` ENUM('sending', 'delivered', 'read', 'failed') DEFAULT 'delivered',
    `read_by` JSON COMMENT 'Array of who has read the message',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`team_request_id`) REFERENCES `team_inspection_requests`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`sender_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    
    INDEX `idx_team_request_id` (`team_request_id`),
    INDEX `idx_sender_id` (`sender_id`),
    INDEX `idx_message_type` (`message_type`),
    INDEX `idx_created_at` (`created_at`),
    INDEX `idx_is_urgent` (`is_urgent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Team Activity Stream
CREATE TABLE IF NOT EXISTS `team_activity_stream` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `team_request_id` INT NOT NULL,
    `actor_id` INT NOT NULL,
    `activity_type` ENUM('task_assigned', 'task_started', 'task_completed', 'checkpoint_reached', 'issue_reported', 'note_added', 'status_changed', 'team_joined', 'team_left') NOT NULL,
    `activity_description` TEXT NOT NULL,
    `activity_data` JSON COMMENT 'Additional context data',
    `target_inspectors` JSON COMMENT 'Array of inspector IDs this activity relates to',
    `is_system_generated` BOOLEAN DEFAULT FALSE,
    `activity_timestamp` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`team_request_id`) REFERENCES `team_inspection_requests`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`actor_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    
    INDEX `idx_team_request_id` (`team_request_id`),
    INDEX `idx_actor_id` (`actor_id`),
    INDEX `idx_activity_type` (`activity_type`),
    INDEX `idx_activity_timestamp` (`activity_timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 5. Quality Control for Team Inspections
-- ============================================

-- Team Inspection Quality Reviews
CREATE TABLE IF NOT EXISTS `team_quality_reviews` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `team_request_id` INT NOT NULL,
    `reviewer_id` INT NOT NULL,
    `review_type` ENUM('interim', 'final', 'spot_check', 'customer_complaint') NOT NULL,
    `review_criteria` JSON COMMENT 'Review checklist and criteria',
    `review_scores` JSON COMMENT 'Scores for each criterion',
    `overall_score` DECIMAL(5,2),
    `findings` JSON COMMENT 'Issues found during review',
    `recommendations` JSON COMMENT 'Recommendations for improvement',
    `required_actions` JSON COMMENT 'Actions that must be taken',
    `review_status` ENUM('in_progress', 'completed', 'requires_follow_up') DEFAULT 'in_progress',
    `follow_up_required` BOOLEAN DEFAULT FALSE,
    `follow_up_date` DATETIME,
    `review_notes` TEXT,
    `reviewed_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`team_request_id`) REFERENCES `team_inspection_requests`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`reviewer_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    
    INDEX `idx_team_request_id` (`team_request_id`),
    INDEX `idx_reviewer_id` (`reviewer_id`),
    INDEX `idx_review_type` (`review_type`),
    INDEX `idx_review_status` (`review_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Team Performance Metrics
CREATE TABLE IF NOT EXISTS `team_performance_metrics` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `team_id` INT NOT NULL,
    `metric_date` DATE NOT NULL,
    `total_inspections` INT DEFAULT 0,
    `completed_inspections` INT DEFAULT 0,
    `average_inspection_time_minutes` DECIMAL(8,2),
    `quality_score_average` DECIMAL(5,2),
    `customer_satisfaction_score` DECIMAL(5,2),
    `team_efficiency_score` DECIMAL(5,2),
    `issues_reported` INT DEFAULT 0,
    `safety_incidents` INT DEFAULT 0,
    `equipment_utilization` JSON COMMENT 'Equipment usage statistics',
    `team_collaboration_score` DECIMAL(5,2),
    `additional_metrics` JSON COMMENT 'Other team-specific metrics',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`team_id`) REFERENCES `inspection_teams`(`id`) ON DELETE CASCADE,
    
    UNIQUE KEY `unique_team_date` (`team_id`, `metric_date`),
    INDEX `idx_team_id` (`team_id`),
    INDEX `idx_metric_date` (`metric_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 6. Equipment and Resource Management
-- ============================================

-- Team Equipment Assignment
CREATE TABLE IF NOT EXISTS `team_equipment_assignment` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `team_request_id` INT NOT NULL,
    `equipment_id` INT NOT NULL,
    `equipment_name` VARCHAR(200) NOT NULL,
    `equipment_type` ENUM('diagnostic_tool', 'measuring_device', 'safety_equipment', 'lifting_equipment', 'computer', 'other') NOT NULL,
    `assigned_to_inspector_id` INT,
    `assignment_status` ENUM('assigned', 'in_use', 'returned', 'maintenance_needed', 'lost') DEFAULT 'assigned',
    `assigned_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `returned_at` DATETIME,
    `usage_notes` TEXT,
    `condition_before` JSON COMMENT 'Equipment condition before use',
    `condition_after` JSON COMMENT 'Equipment condition after use',
    
    FOREIGN KEY (`team_request_id`) REFERENCES `team_inspection_requests`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`assigned_to_inspector_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    
    INDEX `idx_team_request_id` (`team_request_id`),
    INDEX `idx_equipment_id` (`equipment_id`),
    INDEX `idx_assigned_to_inspector` (`assigned_to_inspector`),
    INDEX `idx_assignment_status` (`assignment_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 7. Default Data
-- ============================================

-- Default Inspection Teams for Main Centers
INSERT INTO `inspection_teams` (`team_name`, `team_code`, `team_description`, `team_leader_id`, `center_id`, `specialization`, `max_team_size`) VALUES
('Main Center Team A', 'MCT-A', 'Primary inspection team for main center - Focus on general inspections', 1, 1, 'general', 5),
('Main Center Team B', 'MCT-B', 'Specialized team for engine and diagnostic inspections', 2, 1, 'engine', 4),
('Main Center Team C', 'MCT-C', 'Electrical systems and computer diagnostics team', 3, 1, 'electrical', 4),
('Main Center Team D', 'MCT-D', 'Heavy vehicle and fleet inspection team', 4, 1, 'heavy_vehicle', 6),
('Main Center Team E', 'MCT-E', 'Body work and structural inspection team', 5, 1, 'body', 4);

-- Default Team Members (assuming users 1-10 are inspectors)
INSERT INTO `team_members` (`team_id`, `inspector_id`, `role_in_team`, `specialization_area`) VALUES
(1, 1, 'leader', 'general'),
(1, 6, 'senior_inspector', 'general'),
(1, 7, 'inspector', 'diagnostic'),
(1, 8, 'inspector', 'mechanical'),
(1, 9, 'junior_inspector', 'visual'),
(2, 2, 'leader', 'engine'),
(2, 10, 'senior_inspector', 'engine'),
(2, 11, 'inspector', 'diagnostic'),
(2, 12, 'inspector', 'mechanical'),
(3, 3, 'leader', 'electrical'),
(3, 13, 'senior_inspector', 'electrical'),
(3, 14, 'inspector', 'computer'),
(3, 15, 'inspector', 'diagnostic'),
(4, 4, 'leader', 'heavy_vehicle'),
(4, 16, 'senior_inspector', 'heavy_vehicle'),
(4, 17, 'inspector', 'mechanical'),
(4, 18, 'inspector', 'safety'),
(4, 19, 'inspector', 'documentation'),
(5, 5, 'leader', 'body'),
(5, 20, 'senior_inspector', 'body'),
(5, 21, 'inspector', 'structural'),
(5, 22, 'inspector', 'paint');

-- Update team sizes
UPDATE `inspection_teams` SET `current_team_size` = 5 WHERE `id` = 1;
UPDATE `inspection_teams` SET `current_team_size` = 4 WHERE `id` = 2;
UPDATE `inspection_teams` SET `current_team_size` = 4 WHERE `id` = 3;
UPDATE `inspection_teams` SET `current_team_size` = 5 WHERE `id` = 4;
UPDATE `inspection_teams` SET `current_team_size` = 4 WHERE `id` = 5;

-- ============================================
-- 8. Views for Team Management
-- ============================================

-- Team Overview View
CREATE VIEW `team_overview` AS
SELECT 
    it.id,
    it.team_name,
    it.team_code,
    it.specialization,
    it.current_team_size,
    it.max_team_size,
    it.is_active,
    u.full_name as team_leader_name,
    COUNT(tir.id) as total_inspections,
    SUM(CASE WHEN tir.status = 'completed' THEN 1 ELSE 0 END) as completed_inspections,
    AVG(tipm.quality_score_average) as avg_quality_score,
    AVG(tipm.team_efficiency_score) as avg_efficiency_score
FROM inspection_teams it
LEFT JOIN users u ON it.team_leader_id = u.id
LEFT JOIN team_inspection_requests tir ON it.id = tir.team_id
LEFT JOIN team_performance_metrics tipm ON it.id = tipm.team_id
GROUP BY it.id, it.team_name, it.team_code, it.specialization, it.current_team_size, it.max_team_size, it.is_active, u.full_name;

-- Active Team Inspections View
CREATE VIEW `active_team_inspections` AS
SELECT 
    tir.id,
    tir.request_number,
    tir.team_id,
    it.team_name,
    tir.primary_vehicle_id,
    tir.inspection_type_id,
    itype.type_name_ar,
    tir.status,
    tir.priority_level,
    tir.scheduled_start_time,
    tir.actual_start_time,
    tir.completion_percentage,
    COUNT(tit.id) as total_tasks,
    SUM(CASE WHEN tit.task_status = 'completed' THEN 1 ELSE 0 END) as completed_tasks,
    COUNT(tiv.id) as total_vehicles,
    SUM(CASE WHEN tiv.inspection_status = 'completed' THEN 1 ELSE 0 END) as completed_vehicles
FROM team_inspection_requests tir
JOIN inspection_teams it ON tir.team_id = it.id
LEFT JOIN inspection_types itype ON tir.inspection_type_id = itype.id
LEFT JOIN team_inspection_tasks tit ON tir.id = tit.team_request_id
LEFT JOIN team_inspection_vehicles tiv ON tir.id = tiv.team_request_id
WHERE tir.status IN ('assigned', 'in_progress', 'paused')
GROUP BY tir.id, tir.request_number, tir.team_id, it.team_name, tir.primary_vehicle_id, tir.inspection_type_id, itype.type_name_ar, tir.status, tir.priority_level, tir.scheduled_start_time, tir.actual_start_time, tir.completion_percentage;

-- Team Performance Summary View
CREATE VIEW `team_performance_summary` AS
SELECT 
    it.id as team_id,
    it.team_name,
    it.specialization,
    DATE(tipm.metric_date) as performance_date,
    tipm.total_inspections,
    tipm.completed_inspections,
    tipm.average_inspection_time_minutes,
    tipm.quality_score_average,
    tipm.customer_satisfaction_score,
    tipm.team_efficiency_score,
    tipm.issues_reported,
    tipm.safety_incidents,
    tipm.team_collaboration_score
FROM inspection_teams it
JOIN team_performance_metrics tipm ON it.id = tipm.team_id
WHERE tipm.metric_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
ORDER BY performance_date DESC, it.team_name;

-- ============================================
-- 9. Triggers for Team Management
-- ============================================

DELIMITER $$

-- Update team size when members are added or removed
CREATE TRIGGER `update_team_size_on_member_add`
AFTER INSERT ON `team_members`
FOR EACH ROW
BEGIN
    UPDATE inspection_teams 
    SET current_team_size = (
        SELECT COUNT(*) FROM team_members 
        WHERE team_id = NEW.team_id AND is_active = TRUE
    )
    WHERE id = NEW.team_id;
END$$

CREATE TRIGGER `update_team_size_on_member_remove`
AFTER UPDATE ON `team_members`
FOR EACH ROW
BEGIN
    IF NEW.is_active != OLD.is_active THEN
        UPDATE inspection_teams 
        SET current_team_size = (
            SELECT COUNT(*) FROM team_members 
            WHERE team_id = NEW.team_id AND is_active = TRUE
        )
        WHERE id = NEW.team_id;
    END IF;
END$$

-- Log team activity
CREATE TRIGGER `log_team_activity`
AFTER INSERT ON `team_inspection_tasks`
FOR EACH ROW
BEGIN
    INSERT INTO team_activity_stream 
    (team_request_id, actor_id, activity_type, activity_description, activity_data)
    VALUES (
        NEW.team_request_id,
        NEW.assigned_inspector_id,
        'task_assigned',
        CONCAT('Task "', NEW.task_name, '" assigned to inspector'),
        JSON_OBJECT('task_id', NEW.id, 'task_name', NEW.task_name, 'inspector_id', NEW.assigned_inspector_id)
    );
END$$

-- Update completion percentage
CREATE TRIGGER `update_team_completion_percentage`
AFTER UPDATE ON `team_inspection_tasks`
FOR EACH ROW
BEGIN
    IF NEW.task_status != OLD.task_status THEN
        UPDATE team_inspection_requests 
        SET completion_percentage = (
            SELECT ROUND(
                (SUM(CASE WHEN task_status = 'completed' THEN 1 ELSE 0 END) * 100.0) / 
                COUNT(*), 2
            )
            FROM team_inspection_tasks 
            WHERE team_request_id = NEW.team_request_id
        )
        WHERE id = NEW.team_request_id;
    END IF;
END$$

DELIMITER ;

-- ============================================
-- Migration Complete
-- ============================================

-- Log the migration
INSERT INTO system_logs (log_type, message, details, created_at) VALUES
('migration', 'Team inspection system migration completed', JSON_OBJECT(
    'migration_version', '007',
    'tables_created', 12,
    'views_created', 3,
    'triggers_created', 3,
    'default_teams_inserted', 5,
    'default_members_inserted', 22,
    'migration_date', CURRENT_TIMESTAMP
), CURRENT_TIMESTAMP);
