-- ============================================
-- AUTO TESTER Recruitment System Database Schema
-- Migration 003: Create Recruitment System Tables
-- ============================================

-- ============================================
-- Create jobs table
-- ============================================
CREATE TABLE IF NOT EXISTS `jobs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(100) NOT NULL,
    `title_ar` VARCHAR(100) NOT NULL,
    `department` VARCHAR(50) NOT NULL,
    `location` VARCHAR(100) NOT NULL,
    `employment_type` ENUM('full_time', 'part_time', 'contract', 'internship') DEFAULT 'full_time',
    `experience_years` INT DEFAULT 0,
    `salary_range_min` DECIMAL(10,2),
    `salary_range_max` DECIMAL(10,2),
    `description` TEXT NOT NULL,
    `description_ar` TEXT NOT NULL,
    `requirements` TEXT NOT NULL,
    `requirements_ar` TEXT NOT NULL,
    `benefits` TEXT,
    `benefits_ar` TEXT,
    `is_active` BOOLEAN DEFAULT TRUE,
    `is_featured` BOOLEAN DEFAULT FALSE,
    `views_count` INT DEFAULT 0,
    `applications_count` INT DEFAULT 0,
    `created_by` INT,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME ON UPDATE CURRENT_TIMESTAMP,
    `expires_at` DATETIME,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_department` (`department`),
    INDEX `idx_active` (`is_active`),
    INDEX `idx_featured` (`is_featured`),
    INDEX `idx_created` (`created_at`),
    INDEX `idx_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Create job_applications table
-- ============================================
CREATE TABLE IF NOT EXISTS `job_applications` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `job_id` INT NOT NULL,
    `application_number` VARCHAR(20) UNIQUE,
    `full_name` VARCHAR(100) NOT NULL,
    `email` VARCHAR(100) NOT NULL,
    `phone` VARCHAR(20) NOT NULL,
    `birth_date` DATE,
    `address` TEXT,
    `education_level` VARCHAR(50),
    `years_of_experience` INT DEFAULT 0,
    `current_salary` DECIMAL(10,2),
    `expected_salary` DECIMAL(10,2),
    `notice_period_days` INT DEFAULT 30,
    `cv_file` VARCHAR(255) NOT NULL,
    `cover_letter` TEXT,
    `portfolio_url` VARCHAR(255),
    `linkedin_url` VARCHAR(255),
    `skills` TEXT,
    `languages` TEXT,
    `certifications` TEXT,
    `status` ENUM('pending', 'reviewing', 'shortlisted', 'interview', 'rejected', 'accepted', 'hired') DEFAULT 'pending',
    `reviewed_by` INT,
    `review_notes` TEXT,
    `interview_date` DATETIME,
    `interview_type` ENUM('online', 'phone', 'onsite'),
    `interview_notes` TEXT,
    `rejection_reason` TEXT,
    `offer_details` TEXT,
    `hired_date` DATE,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`job_id`) REFERENCES `jobs`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`reviewed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_job` (`job_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_email` (`email`),
    INDEX `idx_created` (`created_at`),
    INDEX `idx_application_number` (`application_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Create interviews table
-- ============================================
CREATE TABLE IF NOT EXISTS `interviews` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `application_id` INT NOT NULL,
    `interviewer_id` INT NOT NULL,
    `interview_date` DATETIME NOT NULL,
    `interview_type` ENUM('online', 'phone', 'onsite') NOT NULL,
    `meeting_link` VARCHAR(255),
    `location` VARCHAR(255),
    `duration_minutes` INT DEFAULT 60,
    `status` ENUM('scheduled', 'completed', 'cancelled', 'rescheduled') DEFAULT 'scheduled',
    `feedback` TEXT,
    `technical_score` DECIMAL(5,2),
    `communication_score` DECIMAL(5,2),
    `experience_score` DECIMAL(5,2),
    `attitude_score` DECIMAL(5,2),
    `overall_score` DECIMAL(5,2),
    `notes` TEXT,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`application_id`) REFERENCES `job_applications`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`interviewer_id`) REFERENCES `users`(`id`),
    INDEX `idx_application` (`application_id`),
    INDEX `idx_interviewer` (`interviewer_id`),
    INDEX `idx_date` (`interview_date`),
    INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Create applicant_evaluations table
-- ============================================
CREATE TABLE IF NOT EXISTS `applicant_evaluations` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `application_id` INT NOT NULL,
    `evaluator_id` INT NOT NULL,
    `criteria_name` VARCHAR(100) NOT NULL,
    `score` DECIMAL(5,2) NOT NULL,
    `weight` DECIMAL(5,2) DEFAULT 1.0,
    `notes` TEXT,
    `evaluated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`application_id`) REFERENCES `job_applications`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`evaluator_id`) REFERENCES `users`(`id`),
    INDEX `idx_application` (`application_id`),
    INDEX `idx_evaluator` (`evaluator_id`),
    INDEX `idx_criteria` (`criteria_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Create recruitment_notifications table
-- ============================================
CREATE TABLE IF NOT EXISTS `recruitment_notifications` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `applicant_email` VARCHAR(100) NOT NULL,
    `application_id` INT,
    `type` ENUM('application_received', 'status_update', 'interview_scheduled', 'offer_sent', 'rejection', 'reminder') NOT NULL,
    `title` VARCHAR(200) NOT NULL,
    `message` TEXT NOT NULL,
    `sent_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `is_read` BOOLEAN DEFAULT FALSE,
    `sent_via` ENUM('email', 'sms', 'whatsapp', 'system') DEFAULT 'system',
    FOREIGN KEY (`application_id`) REFERENCES `job_applications`(`id`) ON DELETE SET NULL,
    INDEX `idx_email` (`applicant_email`),
    INDEX `idx_application` (`application_id`),
    INDEX `idx_type` (`type`),
    INDEX `idx_sent` (`sent_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Create recruitment_settings table
-- ============================================
CREATE TABLE IF NOT EXISTS `recruitment_settings` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `setting_key` VARCHAR(100) NOT NULL UNIQUE,
    `setting_value` TEXT,
    `description` TEXT,
    `updated_at` DATETIME ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Create job_skills table
-- ============================================
CREATE TABLE IF NOT EXISTS `job_skills` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `job_id` INT NOT NULL,
    `skill_name` VARCHAR(100) NOT NULL,
    `skill_level` ENUM('required', 'preferred', 'optional') DEFAULT 'required',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`job_id`) REFERENCES `jobs`(`id`) ON DELETE CASCADE,
    INDEX `idx_job` (`job_id`),
    INDEX `idx_skill` (`skill_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Insert default recruitment settings
-- ============================================
INSERT INTO `recruitment_settings` (`setting_key`, `setting_value`, `description`) VALUES
('auto_reply_enabled', 'true', 'Enable automatic reply to job applications'),
('whatsapp_notifications', 'true', 'Send WhatsApp notifications for application updates'),
('email_notifications', 'true', 'Send email notifications for application updates'),
('application_expiry_days', '30', 'Days after which applications are automatically closed'),
('interview_reminder_hours', '24', 'Hours before interview to send reminder'),
('max_applications_per_job', '100', 'Maximum number of applications per job posting'),
('auto_shortlist_score', '70', 'Minimum score to auto-shortlist applications'),
('default_interview_duration', '60', 'Default interview duration in minutes'),
('recruitment_email', 'hr@autotester.com', 'Email address for recruitment correspondence'),
('company_description', 'AUTO TESTER is a leading automotive inspection company...', 'Company description for job postings'),
('company_benefits', 'Health insurance, Training programs, Career growth...', 'Company benefits for job postings');

-- ============================================
-- Insert sample job postings
-- ============================================
INSERT INTO `jobs` (`title`, `title_ar`, `department`, `location`, `employment_type`, `experience_years`, `salary_range_min`, `salary_range_max`, `description`, `description_ar`, `requirements`, `requirements_ar`, `benefits`, `benefits_ar`, `is_featured`) VALUES 
('Senior Software Engineer', 'مهندس برمجيات أول', 'engineering', 'cairo', 'full_time', 5, 15000, 25000, 'We are looking for a Senior Software Engineer to join our development team and help build innovative automotive inspection solutions.', 'نبحث عن مهندس برمجيات أول للانضمام إلى فريق التطوير والمساعدة في بناء حلول فحص السيارات المبتكرة.', '5+ years of experience in software development, Strong knowledge of PHP, JavaScript, MySQL, Experience with REST APIs', '5+ سنوات خبرة في تطوير البرمجيات، معرفة قوية بـ PHP, JavaScript, MySQL، خبرة في REST APIs', 'Health insurance, Training programs, Performance bonuses, Flexible working hours', 'تأمين صحي، برامج تدريب، مكافآت أداء، ساعات عمل مرنة', true),

('AI/Machine Learning Engineer', 'مهندس ذكاء اصطناعي/تعلم آلة', 'ai', 'remote', 'full_time', 3, 18000, 30000, 'Join our AI team to develop cutting-edge machine learning models for automotive inspection and predictive maintenance.', 'انضم إلى فريق الذكاء الاصطناعي لتطوير نماذج تعلم الآلة المتقدمة لفحص السيارات والصيانة التنبؤية.', '3+ years in ML/AI, Python, TensorFlow/PyTorch, Computer vision experience', '3+ سنوات في الذكاء الاصطناعي/تعلم الآلة، Python, TensorFlow/PyTorch، خبرة في رؤية الكمبيوتر', 'Remote work flexibility, Latest hardware, Conference attendance, Research budget', 'مرونة العمل عن بعد، أحدث الأجهزة، حضور المؤتمرات، ميزانية بحثية', true),

('Automotive Inspector', 'فني فحص سيارات', 'inspection', 'cairo', 'full_time', 2, 8000, 12000, 'Looking for experienced automotive inspectors to join our team and provide high-quality vehicle inspection services.', 'نبحث عن فنيي فحص سيارات ذوي خبرة للانضمام إلى فريقنا وتقديم خدمات فحص سيارات عالية الجودة.', '2+ years automotive inspection experience, Valid driver license, Technical certification', '2+ سنوات خبرة في فحص السيارات، رخصة قيادة سارية، شهادة فنية', 'Training opportunities, Performance bonuses, Company vehicle, Health insurance', 'فرص تدريب، مكافآت أداء، سيارة شركة، تأمين صحي', false),

('Sales Executive', 'مندوب مبيعات', 'sales', 'cairo', 'full_time', 2, 6000, 10000, 'We need a motivated Sales Executive to promote our automotive inspection services and expand our customer base.', 'نحتاج إلى مندوب مبيعات متحفز للترويج لخدمات فحص السيارات وتوسيع قاعدة عملائنا.', '2+ years sales experience, Excellent communication skills, Results-oriented', '2+ سنوات خبرة في المبيعات، مهارات تواصل ممتازة، يركز على النتائج', 'Commission structure, Sales training, Company car, Performance bonuses', 'هيكل عمولات، تدريب مبيعات، سيارة شركة، مكافآت أداء', false),

('Customer Support Specialist', 'أخصائي دعم عملاء', 'support', 'remote', 'full_time', 1, 5000, 8000, 'Join our support team to provide excellent customer service and technical support for our automotive inspection platform.', 'انضم إلى فريق الدعم لتقديم خدمة عملاء ممتازة ودعم فني لمنصة فحص السيارات الخاصة بنا.', '1+ year customer support experience, Technical aptitude, Excellent problem-solving skills', '1+ سنة خبرة في دعم العملاء، كفاءة تقنية، مهارات حل مشاكل ممتازة', 'Remote work, Training programs, Health insurance, Career growth', 'عمل عن بعد، برامج تدريب، تأمين صحي، نمو مهني', false);

-- ============================================
-- Insert job skills
-- ============================================
INSERT INTO `job_skills` (`job_id`, `skill_name`, `skill_level`) VALUES
(1, 'PHP', 'required'),
(1, 'JavaScript', 'required'),
(1, 'MySQL', 'required'),
(1, 'REST APIs', 'required'),
(1, 'Git', 'preferred'),
(1, 'Docker', 'optional'),

(2, 'Python', 'required'),
(2, 'TensorFlow', 'required'),
(2, 'Computer Vision', 'required'),
(2, 'Machine Learning', 'required'),
(2, 'PyTorch', 'preferred'),
(2, 'Deep Learning', 'preferred'),

(3, 'Automotive Systems', 'required'),
(3, 'Vehicle Inspection', 'required'),
(3, 'Technical Diagnosis', 'required'),
(3, 'Customer Service', 'required'),
(3, 'Report Writing', 'preferred'),

(4, 'Sales', 'required'),
(4, 'Communication', 'required'),
(4, 'Negotiation', 'required'),
(4, 'CRM Software', 'preferred'),
(4, 'Automotive Knowledge', 'preferred'),

(5, 'Customer Support', 'required'),
(5, 'Technical Support', 'required'),
(6, 'Problem Solving', 'required'),
(5, 'Communication', 'required'),
(5, 'Multilingual', 'optional');

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

-- Recruitment dashboard view
CREATE VIEW `recruitment_dashboard` AS
SELECT 
    COUNT(*) as total_applications,
    COUNT(CASE WHEN ja.status = 'pending' THEN 1 END) as pending_applications,
    COUNT(CASE WHEN ja.status = 'interview' THEN 1 END) as interview_scheduled,
    COUNT(CASE WHEN ja.status = 'accepted' THEN 1 END) as offers_sent,
    COUNT(CASE WHEN ja.status = 'hired' THEN 1 END) as hired_candidates,
    COUNT(CASE WHEN ja.status = 'rejected' THEN 1 END) as rejected_applications,
    COUNT(DISTINCT ja.job_id) as active_jobs,
    AVG(CASE WHEN i.overall_score IS NOT NULL THEN i.overall_score END) as average_interview_score
FROM job_applications ja
LEFT JOIN interviews i ON ja.id = i.application_id;

-- Job performance view
CREATE VIEW `job_performance` AS
SELECT 
    j.id,
    j.title_ar,
    j.department,
    j.views_count,
    j.applications_count,
    COUNT(ja.id) as actual_applications,
    COUNT(CASE WHEN ja.status = 'hired' THEN 1 END) as hired_count,
    ROUND((COUNT(CASE WHEN ja.status = 'hired' THEN 1 END) * 100.0 / NULLIF(COUNT(ja.id), 0)), 2) as hire_rate,
    j.created_at
FROM jobs j
LEFT JOIN job_applications ja ON j.id = ja.job_id
WHERE j.is_active = TRUE
GROUP BY j.id;

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

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

-- Trigger to update job applications count
DELIMITER $$
CREATE TRIGGER `update_job_applications_count` 
AFTER INSERT ON `job_applications`
FOR EACH ROW
BEGIN
    UPDATE jobs 
    SET applications_count = applications_count + 1 
    WHERE id = NEW.job_id;
END$$
DELIMITER ;

-- Trigger to update job applications count on delete
DELIMITER $$
CREATE TRIGGER `update_job_applications_count_delete` 
AFTER DELETE ON `job_applications`
FOR EACH ROW
BEGIN
    UPDATE jobs 
    SET applications_count = GREATEST(applications_count - 1, 0) 
    WHERE id = OLD.job_id;
END$$
DELIMITER ;

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

-- Composite indexes for common queries
CREATE INDEX `idx_job_applications_status_created` ON `job_applications` (`status`, `created_at`);
CREATE INDEX `idx_interviews_application_status` ON `interviews` (`application_id`, `status`);
CREATE INDEX `idx_evaluations_application_evaluator` ON `applicant_evaluations` (`application_id`, `evaluator_id`);
CREATE INDEX `idx_notifications_email_type` ON `recruitment_notifications` (`applicant_email`, `type`);

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

-- Ensure salary range is valid
ALTER TABLE `jobs` ADD CONSTRAINT `chk_salary_range` 
CHECK (`salary_range_min` IS NULL OR `salary_range_max` IS NULL OR `salary_range_min` <= `salary_range_max`);

-- Ensure experience years is non-negative
ALTER TABLE `job_applications` ADD CONSTRAINT `chk_experience_years` 
CHECK (`years_of_experience` >= 0);

-- Ensure scores are within valid range
ALTER TABLE `interviews` ADD CONSTRAINT `chk_interview_scores` 
CHECK (
    (`technical_score` IS NULL OR (`technical_score` >= 0 AND `technical_score` <= 100)) AND
    (`communication_score` IS NULL OR (`communication_score` >= 0 AND `communication_score` <= 100)) AND
    (`experience_score` IS NULL OR (`experience_score` >= 0 AND `experience_score` <= 100)) AND
    (`attitude_score` IS NULL OR (`attitude_score` >= 0 AND `attitude_score` <= 100)) AND
    (`overall_score` IS NULL OR (`overall_score` >= 0 AND `overall_score` <= 100))
);

-- Ensure evaluation scores are within valid range
ALTER TABLE `applicant_evaluations` ADD CONSTRAINT `chk_evaluation_scores` 
CHECK (`score` >= 0 AND `score` <= 100);

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