-- Complete database structure for HR Portal

-- Create database if not exists
CREATE DATABASE IF NOT EXISTS aigroyal_hrportalnew;
USE aigroyal_hrportalnew;

-- Candidates table
CREATE TABLE IF NOT EXISTS candidates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    candidate_id VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(15) NOT NULL,
    email VARCHAR(100),
    status ENUM('pending', 'shortlisted', 'selected', 'rejected', 'joined', 'hold', 'rescheduled', 'process_ended', 'offer_declined', 'not_joined') DEFAULT 'pending',
    source VARCHAR(50),
    lineup_date DATE NOT NULL,
    experience VARCHAR(50),
    skills TEXT,
    current_company VARCHAR(100),
    current_salary DECIMAL(10,2),
    expected_salary DECIMAL(10,2),
    notice_period VARCHAR(50),
    location VARCHAR(100),
    resume_path VARCHAR(255),
    notes TEXT,
    added_by INT,
    added_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (added_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_candidate_id (candidate_id),
    INDEX idx_status (status),
    INDEX idx_lineup_date (lineup_date),
    INDEX idx_added_by (added_by)
);

-- Interviews table
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,
    interview_type ENUM('initial', 'technical', 'hr', 'final', 'group_discussion', 'practical_test', 'management') NOT NULL,
    duration_minutes INT,
    interviewer_id INT,
    interviewer_name VARCHAR(100) NOT NULL,
    status ENUM('scheduled', 'completed', 'cancelled', 'rescheduled', 'no_show') DEFAULT 'scheduled',
    result ENUM('pending', 'shortlisted', 'selected', 'rejected', 'hold', 'rescheduled', 'process_ended') DEFAULT 'pending',
    notes TEXT,
    feedback TEXT,
    technical_score INT CHECK (technical_score >= 1 AND technical_score <= 10),
    communication_score INT CHECK (communication_score >= 1 AND communication_score <= 10),
    overall_score INT CHECK (overall_score >= 1 AND overall_score <= 10),
    strengths TEXT,
    weaknesses TEXT,
    recommendations TEXT,
    next_interview_date DATE,
    next_interview_type VARCHAR(50),
    next_interview_notes TEXT,
    turn_up_status ENUM('turned_up', 'no_show') DEFAULT 'turned_up',
    absence_reason VARCHAR(255),
    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_id (candidate_id),
    INDEX idx_interview_date (interview_date),
    INDEX idx_interviewer_id (interviewer_id),
    INDEX idx_status (status),
    INDEX idx_result (result)
);

-- Interview feedback 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,
    interviewer_name VARCHAR(100) NOT NULL,
    technical_skills_score INT CHECK (technical_skills_score >= 1 AND technical_skills_score <= 10),
    communication_score INT CHECK (communication_score >= 1 AND communication_score <= 10),
    overall_score INT CHECK (overall_score >= 1 AND overall_score <= 10),
    strengths TEXT,
    weaknesses TEXT,
    detailed_feedback TEXT,
    recommendations TEXT,
    would_hire ENUM('yes', 'no', 'maybe') DEFAULT 'maybe',
    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 SET NULL,
    INDEX idx_interview_id (interview_id),
    INDEX idx_candidate_id (candidate_id)
);

-- Candidate status history table
CREATE TABLE IF NOT EXISTS candidate_status_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    candidate_id INT NOT NULL,
    previous_status VARCHAR(50),
    new_status VARCHAR(50) NOT NULL,
    changed_by INT,
    changed_by_name VARCHAR(100),
    reason TEXT,
    additional_data JSON,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
    FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_candidate_id (candidate_id),
    INDEX idx_changed_at (changed_at)
);

-- Reschedules table
CREATE TABLE IF NOT EXISTS reschedules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    candidate_id INT NOT NULL,
    original_date DATE NOT NULL,
    new_date DATE NOT NULL,
    reason VARCHAR(255),
    reason_details TEXT,
    requested_by ENUM('candidate', 'hr', 'interviewer') DEFAULT 'hr',
    rescheduled_by INT,
    rescheduled_by_name VARCHAR(100),
    reschedule_count INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
    FOREIGN KEY (rescheduled_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_candidate_id (candidate_id),
    INDEX idx_original_date (original_date),
    INDEX idx_new_date (new_date)
);

-- 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,
    actual_joining_date DATE,
    salary_offered DECIMAL(10,2),
    salary_negotiated DECIMAL(10,2),
    position VARCHAR(100),
    department VARCHAR(100),
    reporting_manager VARCHAR(100),
    work_location VARCHAR(200),
    employment_type ENUM('full_time', 'part_time', 'contract', 'internship') DEFAULT 'full_time',
    joining_notes TEXT,
    joining_feedback TEXT,
    documents_required TEXT,
    probation_period_months INT DEFAULT 6,
    joining_status ENUM('pending', 'joined', 'not_joined', 'delayed') DEFAULT 'pending',
    created_by INT,
    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 SET NULL,
    INDEX idx_candidate_id (candidate_id),
    INDEX idx_expected_joining_date (expected_joining_date),
    INDEX idx_actual_joining_date (actual_joining_date)
);

-- 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,
    step_number INT NOT NULL,
    step_name VARCHAR(100) NOT NULL,
    step_type ENUM('interview', 'result', 'reschedule', 'end_process', 'offer', 'joining') NOT NULL,
    step_status ENUM('pending', 'in_progress', 'completed', 'skipped') DEFAULT 'pending',
    step_data JSON,
    started_at TIMESTAMP,
    completed_at TIMESTAMP,
    performed_by INT,
    performed_by_name VARCHAR(100),
    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_id (candidate_id),
    INDEX idx_step_number (step_number),
    INDEX idx_step_status (step_status)
);

-- Process completion table
CREATE TABLE IF NOT EXISTS process_completion (
    id INT AUTO_INCREMENT PRIMARY KEY,
    candidate_id INT NOT NULL,
    process_status ENUM('in_progress', 'completed', 'terminated') DEFAULT 'in_progress',
    completion_reason VARCHAR(255),
    final_result ENUM('hired', 'rejected', 'withdrawn', 'offer_declined', 'not_joined') NOT NULL,
    started_date DATE,
    completed_date DATE,
    total_interviews INT DEFAULT 0,
    total_days INT,
    completed_by INT,
    completed_by_name VARCHAR(100),
    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,
    UNIQUE KEY unique_candidate (candidate_id),
    INDEX idx_process_status (process_status),
    INDEX idx_final_result (final_result),
    INDEX idx_completed_date (completed_date)
);

-- Interview process state table (for saving progress)
CREATE TABLE IF NOT EXISTS interview_process_state (
    id INT AUTO_INCREMENT PRIMARY KEY,
    candidate_id INT NOT NULL,
    current_step VARCHAR(10) NOT NULL,
    step_data JSON,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by INT,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_candidate_id (candidate_id),
    INDEX idx_current_step (current_step)
);

-- Sources table (for managing candidate sources)
CREATE TABLE IF NOT EXISTS sources (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Options table (for managing dropdown options)
CREATE TABLE IF NOT EXISTS options (
    id INT AUTO_INCREMENT PRIMARY KEY,
    option_type VARCHAR(50) NOT NULL,
    option_value VARCHAR(100) NOT NULL,
    option_label VARCHAR(100) NOT NULL,
    sort_order INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_option_type (option_type)
);

-- Insert default sources
INSERT INTO sources (name, description) VALUES 
('Naukri.com', 'Job portal - Naukri.com'),
('LinkedIn', 'Professional network - LinkedIn'),
('Indeed', 'Job portal - Indeed'),
('Referral', 'Employee referral'),
('Walk-in', 'Walk-in candidate'),
('Company Website', 'Applied through company website'),
('Recruitment Agency', 'Through recruitment agency'),
('Campus Hiring', 'Campus recruitment')
ON DUPLICATE KEY UPDATE name = name;

-- Insert default options
INSERT INTO options (option_type, option_value, option_label, sort_order) VALUES 
('interview_type', 'initial', 'Initial Screening', 1),
('interview_type', 'technical', 'Technical Interview', 2),
('interview_type', 'hr', 'HR Interview', 3),
('interview_type', 'final', 'Final Interview', 4),
('interview_type', 'group_discussion', 'Group Discussion', 5),
('interview_type', 'practical_test', 'Practical Test', 6),
('interview_type', 'management', 'Management Round', 7),

('status', 'pending', 'Pending', 1),
('status', 'shortlisted', 'Shortlisted', 2),
('status', 'selected', 'Selected', 3),
('status', 'rejected', 'Rejected', 4),
('status', 'joined', 'Joined', 5),
('status', 'hold', 'On Hold', 6),
('status', 'rescheduled', 'Rescheduled', 7),
('status', 'process_ended', 'Process Ended', 8),
('status', 'offer_declined', 'Offer Declined', 9),
('status', 'not_joined', 'Not Joined', 10),

('rejection_reason', 'Technical Skills Gap', 'Technical Skills Gap', 1),
('rejection_reason', 'Communication Issues', 'Communication Issues', 2),
('rejection_reason', 'Experience Not Matching', 'Experience Not Matching', 3),
('rejection_reason', 'Cultural Fit Issues', 'Cultural Fit Issues', 4),
('rejection_reason', 'Salary Expectations Too High', 'Salary Expectations Too High', 5),
('rejection_reason', 'Attitude Issues', 'Attitude Issues', 6),
('rejection_reason', 'Failed Technical Test', 'Failed Technical Test', 7),
('rejection_reason', 'Other', 'Other', 8)
ON DUPLICATE KEY UPDATE option_value = option_value;

-- Create indexes for better performance
CREATE INDEX idx_candidates_status_lineup ON candidates(status, lineup_date);
CREATE INDEX idx_interviews_candidate_date ON interviews(candidate_id, interview_date);
CREATE INDEX idx_status_history_candidate_date ON candidate_status_history(candidate_id, changed_at);
CREATE INDEX idx_joining_details_candidate ON joining_details(candidate_id);
CREATE INDEX idx_timeline_candidate_step ON interview_process_timeline(candidate_id, step_number);
