-- Create comprehensive interview process tracking tables

-- Enhanced interviews table with all process data
CREATE TABLE IF NOT EXISTS interviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    candidate_id INT NOT NULL,
    interview_date DATE NOT NULL,
    interview_time TIME DEFAULT NULL,
    interview_type ENUM('initial', 'technical', 'hr', 'final', 'group_discussion', 'practical_test', 'management') NOT NULL DEFAULT 'initial',
    duration_minutes INT DEFAULT NULL,
    interviewer_id INT DEFAULT NULL,
    interviewer_name VARCHAR(255) NOT NULL,
    status ENUM('scheduled', 'completed', 'cancelled', 'rescheduled') NOT NULL DEFAULT 'scheduled',
    result ENUM('pending', 'selected', 'rejected', 'shortlisted', 'hold', 'process_ended', 'rescheduled') NOT NULL DEFAULT 'pending',
    notes TEXT,
    feedback TEXT,
    technical_score INT DEFAULT NULL,
    communication_score INT DEFAULT NULL,
    overall_score INT DEFAULT NULL,
    strengths TEXT,
    weaknesses TEXT,
    recommendations TEXT,
    next_interview_date DATE DEFAULT NULL,
    next_interview_type VARCHAR(100) DEFAULT NULL,
    next_interview_notes TEXT,
    turn_up_status ENUM('turned_up', 'no_show', 'cancelled') DEFAULT 'turned_up',
    absence_reason VARCHAR(255) DEFAULT NULL,
    absence_details TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
    FOREIGN KEY (interviewer_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_candidate_date (candidate_id, interview_date),
    INDEX idx_interview_type (interview_type),
    INDEX idx_result (result),
    INDEX idx_status (status)
);

-- Enhanced joining_details table
CREATE TABLE IF NOT EXISTS joining_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    candidate_id INT NOT NULL,
    expected_joining_date DATE DEFAULT NULL,
    actual_joining_date DATE DEFAULT NULL,
    salary_offered DECIMAL(12,2) DEFAULT NULL,
    salary_negotiated DECIMAL(12,2) DEFAULT NULL,
    position VARCHAR(255) DEFAULT NULL,
    department VARCHAR(255) DEFAULT NULL,
    reporting_manager VARCHAR(255) DEFAULT NULL,
    work_location VARCHAR(255) DEFAULT NULL,
    employment_type ENUM('full_time', 'part_time', 'contract', 'internship') DEFAULT 'full_time',
    joining_status ENUM('scheduled', 'joined', 'not_joined', 'delayed') NOT NULL DEFAULT 'scheduled',
    joining_notes TEXT,
    joining_feedback TEXT,
    documents_required TEXT,
    onboarding_completed BOOLEAN DEFAULT FALSE,
    probation_period_months INT DEFAULT 6,
    offer_letter_sent BOOLEAN DEFAULT FALSE,
    offer_letter_date DATE DEFAULT NULL,
    offer_acceptance_date DATE DEFAULT NULL,
    background_check_status ENUM('pending', 'completed', 'failed', 'not_required') DEFAULT 'pending',
    medical_check_status ENUM('pending', 'completed', 'failed', 'not_required') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
    INDEX idx_candidate_joining (candidate_id),
    INDEX idx_joining_status (joining_status),
    INDEX idx_joining_date (expected_joining_date)
);

-- Enhanced reschedules table
CREATE TABLE IF NOT EXISTS reschedules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    candidate_id INT NOT NULL,
    interview_id INT DEFAULT NULL,
    original_date DATE NOT NULL,
    new_date DATE NOT NULL,
    original_time TIME DEFAULT NULL,
    new_time TIME DEFAULT NULL,
    reason ENUM('candidate_request', 'interviewer_unavailable', 'technical_issues', 'emergency', 'weather', 'other') NOT NULL,
    reason_details TEXT,
    requested_by ENUM('candidate', 'interviewer', 'hr', 'system') NOT NULL DEFAULT 'hr',
    rescheduled_by INT NOT NULL,
    rescheduled_by_name VARCHAR(255) NOT NULL,
    approval_status ENUM('pending', 'approved', 'rejected') DEFAULT 'approved',
    notification_sent BOOLEAN DEFAULT FALSE,
    reschedule_count INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
    FOREIGN KEY (interview_id) REFERENCES interviews(id) ON DELETE SET NULL,
    FOREIGN KEY (rescheduled_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_candidate_reschedule (candidate_id),
    INDEX idx_reschedule_date (new_date),
    INDEX idx_reason (reason)
);

-- Interview process timeline table
CREATE TABLE IF NOT EXISTS interview_process_timeline (
    id INT AUTO_INCREMENT PRIMARY KEY,
    candidate_id INT NOT NULL,
    interview_id INT DEFAULT NULL,
    step_number INT NOT NULL,
    step_name VARCHAR(255) NOT NULL,
    step_type ENUM('turn_up', 'interview', 'result', 'offer', 'joining', 'reschedule', 'end_process') NOT NULL,
    step_status ENUM('pending', 'in_progress', 'completed', 'skipped', 'failed') NOT NULL DEFAULT 'pending',
    step_data JSON DEFAULT NULL,
    started_at TIMESTAMP DEFAULT NULL,
    completed_at TIMESTAMP DEFAULT NULL,
    duration_minutes INT DEFAULT NULL,
    performed_by INT DEFAULT NULL,
    performed_by_name VARCHAR(255) DEFAULT NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
    FOREIGN KEY (interview_id) REFERENCES interviews(id) ON DELETE SET NULL,
    FOREIGN KEY (performed_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_candidate_timeline (candidate_id),
    INDEX idx_step_type (step_type),
    INDEX idx_step_status (step_status)
);

-- Offer management table
CREATE TABLE IF NOT EXISTS offers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    candidate_id INT NOT NULL,
    offer_type ENUM('verbal', 'written', 'conditional') NOT NULL DEFAULT 'written',
    offer_status ENUM('draft', 'sent', 'accepted', 'declined', 'expired', 'withdrawn') NOT NULL DEFAULT 'draft',
    salary_offered DECIMAL(12,2) NOT NULL,
    salary_negotiated DECIMAL(12,2) DEFAULT NULL,
    benefits_offered TEXT,
    position VARCHAR(255) NOT NULL,
    department VARCHAR(255) NOT NULL,
    start_date DATE DEFAULT NULL,
    offer_valid_until DATE DEFAULT NULL,
    offer_sent_date DATE DEFAULT NULL,
    offer_response_date DATE DEFAULT NULL,
    offer_letter_path VARCHAR(500) DEFAULT NULL,
    terms_conditions TEXT,
    special_conditions TEXT,
    created_by INT NOT NULL,
    created_by_name VARCHAR(255) NOT NULL,
    approved_by INT DEFAULT NULL,
    approved_by_name VARCHAR(255) DEFAULT NULL,
    approved_at TIMESTAMP DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_candidate_offer (candidate_id),
    INDEX idx_offer_status (offer_status),
    INDEX idx_offer_date (offer_sent_date)
);

-- Interview feedback and scoring table
CREATE TABLE IF NOT EXISTS interview_feedback (
    id INT AUTO_INCREMENT PRIMARY KEY,
    interview_id INT NOT NULL,
    candidate_id INT NOT NULL,
    interviewer_id INT NOT NULL,
    interviewer_name VARCHAR(255) NOT NULL,
    technical_skills_score INT DEFAULT NULL,
    communication_score INT DEFAULT NULL,
    problem_solving_score INT DEFAULT NULL,
    cultural_fit_score INT DEFAULT NULL,
    leadership_score INT DEFAULT NULL,
    overall_score INT DEFAULT NULL,
    strengths TEXT,
    weaknesses TEXT,
    detailed_feedback TEXT,
    recommendations TEXT,
    would_hire ENUM('yes', 'no', 'maybe') DEFAULT NULL,
    feedback_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (interview_id) REFERENCES interviews(id) ON DELETE CASCADE,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
    FOREIGN KEY (interviewer_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_interview_feedback (interview_id),
    INDEX idx_candidate_feedback (candidate_id),
    INDEX idx_interviewer_feedback (interviewer_id)
);

-- Process completion tracking
CREATE TABLE IF NOT EXISTS process_completion (
    id INT AUTO_INCREMENT PRIMARY KEY,
    candidate_id INT NOT NULL,
    process_status ENUM('active', 'completed', 'terminated', 'on_hold') NOT NULL DEFAULT 'active',
    completion_reason VARCHAR(255) DEFAULT NULL,
    final_result ENUM('hired', 'rejected', 'withdrawn', 'on_hold', 'not_joined') DEFAULT NULL,
    total_interviews INT DEFAULT 0,
    total_reschedules INT DEFAULT 0,
    process_duration_days INT DEFAULT NULL,
    started_date DATE NOT NULL,
    completed_date DATE DEFAULT NULL,
    completed_by INT DEFAULT NULL,
    completed_by_name VARCHAR(255) DEFAULT NULL,
    process_notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
    FOREIGN KEY (completed_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_candidate_process (candidate_id),
    INDEX idx_process_status (process_status),
    INDEX idx_final_result (final_result)
);
