-- ============================================
-- AUTO TESTER Training System Database Schema
-- Migration 002: Create Training System Tables
-- ============================================

-- ============================================
-- Create trainees table
-- ============================================
CREATE TABLE IF NOT EXISTS `trainees` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `university` VARCHAR(100) NOT NULL,
    `faculty` VARCHAR(100) NOT NULL,
    `department` VARCHAR(100),
    `academic_year` INT NOT NULL,
    `gpa` DECIMAL(3,2),
    `graduation_year` INT,
    `student_id` VARCHAR(50),
    `national_id` VARCHAR(20),
    `training_start_date` DATE NOT NULL,
    `training_end_date` DATE NOT NULL,
    `status` ENUM('pending', 'accepted', 'rejected', 'active', 'completed', 'cancelled') DEFAULT 'pending',
    `supervisor_id` INT,
    `attendance_percentage` DECIMAL(5,2) DEFAULT 0,
    `final_score` DECIMAL(5,2),
    `certificate_issued` BOOLEAN DEFAULT FALSE,
    `certificate_url` VARCHAR(255),
    `notes` TEXT,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`supervisor_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_status` (`status`),
    INDEX `idx_dates` (`training_start_date`, `training_end_date`),
    INDEX `idx_university` (`university`),
    INDEX `idx_supervisor` (`supervisor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Create training programs table
-- ============================================
CREATE TABLE IF NOT EXISTS `training_programs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `description` TEXT,
    `duration_weeks` INT DEFAULT 4,
    `topics` JSON,
    `requirements` TEXT,
    `objectives` TEXT,
    `max_trainees` INT DEFAULT 10,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Create training applications table
-- ============================================
CREATE TABLE IF NOT EXISTS `training_applications` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `trainee_id` INT NOT NULL,
    `application_number` VARCHAR(20) UNIQUE,
    `application_date` DATE NOT NULL,
    `cv_file` VARCHAR(255),
    `cover_letter` TEXT,
    `recommendation_letter` VARCHAR(255),
    `preferred_start_date` DATE,
    `preferred_end_date` DATE,
    `status` ENUM('pending', 'reviewing', 'accepted', 'rejected', 'waiting_list') DEFAULT 'pending',
    `reviewed_by` INT,
    `reviewed_at` DATETIME,
    `rejection_reason` TEXT,
    `interview_date` DATETIME,
    `interview_notes` TEXT,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`trainee_id`) REFERENCES `trainees`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`reviewed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_status` (`status`),
    INDEX `idx_date` (`application_date`),
    INDEX `idx_application_number` (`application_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Create trainee attendance table
-- ============================================
CREATE TABLE IF NOT EXISTS `trainee_attendance` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `trainee_id` INT NOT NULL,
    `date` DATE NOT NULL,
    `check_in_time` TIME,
    `check_out_time` TIME,
    `status` ENUM('present', 'absent', 'late', 'excused', 'half_day') DEFAULT 'present',
    `working_hours` DECIMAL(4,2) DEFAULT 0,
    `notes` TEXT,
    `recorded_by` INT,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`trainee_id`) REFERENCES `trainees`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`recorded_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_date` (`date`),
    INDEX `idx_trainee` (`trainee_id`),
    INDEX `idx_status` (`status`),
    UNIQUE KEY `unique_attendance` (`trainee_id`, `date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Create trainee evaluations table
-- ============================================
CREATE TABLE IF NOT EXISTS `trainee_evaluations` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `trainee_id` INT NOT NULL,
    `evaluator_id` INT NOT NULL,
    `week_number` INT NOT NULL,
    `practical_score` DECIMAL(5,2),
    `theoretical_score` DECIMAL(5,2),
    `attitude_score` DECIMAL(5,2),
    `teamwork_score` DECIMAL(5,2),
    `overall_score` DECIMAL(5,2),
    `feedback` TEXT,
    `strengths` TEXT,
    `weaknesses` TEXT,
    `recommendations` TEXT,
    `evaluated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`trainee_id`) REFERENCES `trainees`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`evaluator_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_trainee` (`trainee_id`),
    INDEX `idx_week` (`week_number`),
    INDEX `idx_evaluator` (`evaluator_id`),
    UNIQUE KEY `unique_evaluation` (`trainee_id`, `week_number`, `evaluator_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Create training schedule table
-- ============================================
CREATE TABLE IF NOT EXISTS `training_schedule` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `program_id` INT NOT NULL,
    `week_number` INT NOT NULL,
    `day_number` INT NOT NULL,
    `title` VARCHAR(200) NOT NULL,
    `description` TEXT,
    `topics` JSON,
    `duration_hours` INT DEFAULT 4,
    `type` ENUM('lecture', 'practical', 'workshop', 'exam', 'project') DEFAULT 'lecture',
    `instructor_id` INT,
    `location` VARCHAR(100),
    `materials` JSON,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`program_id`) REFERENCES `training_programs`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`instructor_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_program` (`program_id`),
    INDEX `idx_week` (`week_number`),
    INDEX `idx_instructor` (`instructor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Create training certificates table
-- ============================================
CREATE TABLE IF NOT EXISTS `training_certificates` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `trainee_id` INT NOT NULL,
    `certificate_number` VARCHAR(50) UNIQUE,
    `issue_date` DATE NOT NULL,
    `completion_date` DATE NOT NULL,
    `final_score` DECIMAL(5,2),
    `grade` ENUM('excellent', 'very_good', 'good', 'pass', 'fail'),
    `certificate_url` VARCHAR(255),
    `qr_code_url` VARCHAR(255),
    `issued_by` INT NOT NULL,
    `is_verified` BOOLEAN DEFAULT FALSE,
    `verification_code` VARCHAR(50) UNIQUE,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`trainee_id`) REFERENCES `trainees`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`issued_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_trainee` (`trainee_id`),
    INDEX `idx_certificate_number` (`certificate_number`),
    INDEX `idx_verification_code` (`verification_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Create training notifications table
-- ============================================
CREATE TABLE IF NOT EXISTS `training_notifications` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `trainee_id` INT NOT NULL,
    `type` ENUM('application_received', 'application_accepted', 'application_rejected', 'training_reminder', 'evaluation_scheduled', 'certificate_issued') NOT NULL,
    `title` VARCHAR(200) NOT NULL,
    `message` TEXT NOT NULL,
    `is_read` BOOLEAN DEFAULT FALSE,
    `sent_via` ENUM('email', 'sms', 'whatsapp', 'system') DEFAULT 'system',
    `sent_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`trainee_id`) REFERENCES `trainees`(`id`) ON DELETE CASCADE,
    INDEX `idx_trainee` (`trainee_id`),
    INDEX `idx_type` (`type`),
    INDEX `idx_read` (`is_read`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Insert default training program
-- ============================================
INSERT INTO `training_programs` (`name`, `description`, `duration_weeks`, `topics`, `requirements`, `objectives`, `max_trainees`) VALUES 
('Engineering Internship Program', 'Comprehensive 4-week training program for engineering students covering vehicle inspection systems and AI technologies', 4, 
JSON_OBJECT(
    'week1', JSON_ARRAY('System Introduction', 'Inspection Types', 'UI Training', 'Request Management'),
    'week2', JSON_ARRAY('200-Point Inspection', 'Engine Testing', 'Brakes & Suspension', 'Electrical & AC'),
    'week3', JSON_ARRAY('ECU Diagnostics', 'Dyno Testing', 'Engine Performance', 'Delivery Inspection'),
    'week4', JSON_ARRAY('AI in Automotive', 'Predictive Models', 'Computer Vision', 'Final Project')
),
'Engineering students (3rd year+), Basic automotive knowledge, Good academic standing',
'Provide practical experience in vehicle inspection, Introduce AI applications in automotive, Prepare students for industry careers',
10);

-- ============================================
-- Insert training schedule (4 weeks program)
-- ============================================
INSERT INTO `training_schedule` (`program_id`, `week_number`, `day_number`, `title`, `description`, `duration_hours`, `type`) VALUES
-- Week 1: System Basics
(1, 1, 1, 'Introduction to AUTO TESTER', 'Company overview and system introduction', 3, 'lecture'),
(1, 1, 2, 'Inspection Types Overview', 'Understanding different inspection types', 4, 'lecture'),
(1, 1, 3, 'System Interface Training', 'Hands-on system navigation', 4, 'practical'),
(1, 1, 4, 'Request Management', 'Managing inspection requests', 4, 'workshop'),
(1, 1, 5, 'Week 1 Evaluation', 'Assessment of first week learning', 4, 'exam'),

-- Week 2: Comprehensive Inspection
(1, 2, 1, '200-Point Inspection', 'Complete vehicle inspection process', 4, 'practical'),
(1, 2, 2, 'Engine Testing', 'Engine diagnostics and testing', 4, 'practical'),
(1, 2, 3, 'Brakes & Suspension', 'Brake and suspension systems', 4, 'practical'),
(1, 2, 4, 'Electrical & AC Systems', 'Electrical and air conditioning', 4, 'practical'),
(1, 2, 5, 'Week 2 Practical Test', 'Hands-on assessment', 4, 'exam'),

-- Week 3: Advanced Diagnostics
(1, 3, 1, 'ECU Computer Diagnostics', 'Vehicle computer systems', 4, 'practical'),
(1, 3, 2, 'Dyno Testing', 'Performance testing with dynamometer', 4, 'practical'),
(1, 3, 3, 'Engine Performance', 'Advanced engine analysis', 4, 'practical'),
(1, 3, 4, 'Delivery Inspection', 'Pre-delivery inspection process', 4, 'practical'),
(1, 3, 5, 'Week 3 Assessment', 'Advanced diagnostics evaluation', 4, 'exam'),

-- Week 4: AI and Final Project
(1, 4, 1, 'AI in Automotive Industry', 'Introduction to AI applications', 4, 'lecture'),
(1, 4, 2, 'Predictive Models', 'Machine learning in automotive', 4, 'workshop'),
(1, 4, 3, 'Computer Vision Applications', 'Image recognition in inspection', 4, 'workshop'),
(1, 4, 4, 'Final Project Development', 'Capstone project work', 8, 'project'),
(1, 4, 5, 'Project Presentations & Graduation', 'Final presentations and certificates', 6, 'exam');

-- ============================================
-- Create views for reporting
-- ============================================

-- Trainee overview view
CREATE VIEW `trainee_overview` AS
SELECT 
    t.id,
    t.university,
    t.faculty,
    t.status,
    t.training_start_date,
    t.training_end_date,
    t.attendance_percentage,
    t.final_score,
    u.full_name,
    u.email,
    u.phone,
    u.created_at as user_created_at,
    CASE 
        WHEN t.status = 'completed' AND t.final_score >= 85 THEN 'Excellent'
        WHEN t.status = 'completed' AND t.final_score >= 75 THEN 'Very Good'
        WHEN t.status = 'completed' AND t.final_score >= 65 THEN 'Good'
        WHEN t.status = 'completed' AND t.final_score >= 60 THEN 'Pass'
        WHEN t.status = 'completed' THEN 'Fail'
        ELSE 'In Progress'
    END as performance_grade
FROM trainees t
JOIN users u ON t.user_id = u.id;

-- Training statistics view
CREATE VIEW `training_statistics` AS
SELECT 
    COUNT(*) as total_trainees,
    COUNT(CASE WHEN status = 'active' THEN 1 END) as active_trainees,
    COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed_trainees,
    COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_applications,
    AVG(CASE WHEN final_score IS NOT NULL THEN final_score END) as average_score,
    AVG(CASE WHEN attendance_percentage IS NOT NULL THEN attendance_percentage END) as average_attendance,
    COUNT(DISTINCT university) as universities_count,
    COUNT(DISTINCT faculty) as faculties_count
FROM trainees;

-- ============================================
-- Create triggers for data integrity
-- ============================================

-- Trigger to update attendance percentage
DELIMITER $$
CREATE TRIGGER `update_attendance_percentage` 
AFTER INSERT ON `trainee_attendance`
FOR EACH ROW
BEGIN
    UPDATE trainees t 
    SET t.attendance_percentage = (
        SELECT 
            (COUNT(CASE WHEN status IN ('present', 'late') THEN 1 END) * 100.0 / COUNT(*))
        FROM trainee_attendance ta 
        WHERE ta.trainee_id = NEW.trainee_id
    )
    WHERE t.id = NEW.trainee_id;
END$$
DELIMITER ;

-- Trigger to generate application number
DELIMITER $$
CREATE TRIGGER `generate_application_number` 
BEFORE INSERT ON `training_applications`
FOR EACH ROW
BEGIN
    IF NEW.application_number IS NULL THEN
        SET NEW.application_number = CONCAT('TRN', YEAR(CURDATE()), LPAD(COALESCE(
            (SELECT MAX(CAST(SUBSTRING(application_number, 8) AS UNSIGNED)) 
             FROM training_applications 
             WHERE application_number LIKE CONCAT('TRN', YEAR(CURDATE()), '%')
            ), 0) + 1, 4, '0'));
    END IF;
END$$
DELIMITER ;

-- ============================================
-- Add indexes for performance optimization
-- ============================================

-- Composite indexes for common queries
CREATE INDEX `idx_trainee_status_dates` ON `trainees` (`status`, `training_start_date`, `training_end_date`);
CREATE INDEX `idx_attendance_trainee_date` ON `trainee_attendance` (`trainee_id`, `date`, `status`);
CREATE INDEX `idx_evaluation_trainee_week` ON `trainee_evaluations` (`trainee_id`, `week_number`, `overall_score`);
CREATE INDEX `idx_application_status_date` ON `training_applications` (`status`, `application_date`);

-- ============================================
-- Add constraints for data validation
-- ============================================

-- Ensure training end date is after start date
ALTER TABLE `trainees` ADD CONSTRAINT `chk_training_dates` 
CHECK (`training_end_date` > `training_start_date`);

-- Ensure GPA is within valid range
ALTER TABLE `trainees` ADD CONSTRAINT `chk_gpa_range` 
CHECK (`gpa` IS NULL OR (`gpa` >= 0.0 AND `gpa` <= 4.0));

-- Ensure scores are within valid range
ALTER TABLE `trainee_evaluations` ADD CONSTRAINT `chk_score_range` 
CHECK (
    (`practical_score` IS NULL OR (`practical_score` >= 0 AND `practical_score` <= 100)) AND
    (`theoretical_score` IS NULL OR (`theoretical_score` >= 0 AND `theoretical_score` <= 100)) AND
    (`attitude_score` IS NULL OR (`attitude_score` >= 0 AND `attitude_score` <= 100)) AND
    (`teamwork_score` IS NULL OR (`teamwork_score` >= 0 AND `teamwork_score` <= 100)) AND
    (`overall_score` IS NULL OR (`overall_score` >= 0 AND `overall_score` <= 100))
);

-- ============================================
-- Migration completed successfully
-- ============================================
