-- Create database schema for Candidate Management System

-- Users table for authentication
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    email VARCHAR(100),
    role ENUM('admin', 'hr', 'manager') DEFAULT 'hr',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Sources table for dynamic source management
CREATE TABLE sources (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Profiles table for dynamic profile management
CREATE TABLE profiles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    department VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Candidates table - main candidate information
CREATE TABLE candidates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    candidate_id VARCHAR(20) UNIQUE NOT NULL, -- IX-10001 format
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    lineup_date DATE NOT NULL,
    source_id INT,
    profile_id INT,
    status ENUM('pending', 'shortlisted', 'selected', 'rejected', 'hold', 'joined', 'selected_not_joined', 'rescheduled', 'process_ended') DEFAULT 'pending',
    added_date DATE NOT NULL,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (source_id) REFERENCES sources(id),
    FOREIGN KEY (profile_id) REFERENCES profiles(id),
    FOREIGN KEY (created_by) REFERENCES users(id),
    
    INDEX idx_candidate_id (candidate_id),
    INDEX idx_email (email),
    INDEX idx_phone (phone),
    INDEX idx_lineup_date (lineup_date),
    INDEX idx_status (status)
);

-- Interview process tracking
CREATE TABLE interviews (
    id INT PRIMARY KEY AUTO_INCREMENT,
    candidate_id INT NOT NULL,
    interview_date DATE NOT NULL,
    interview_type ENUM('initial', 'technical', 'hr', 'final', 'reschedule') DEFAULT 'initial',
    status ENUM('scheduled', 'completed', 'no_show', 'cancelled') DEFAULT 'scheduled',
    result ENUM('pending', 'shortlisted', 'selected', 'rejected', 'hold') DEFAULT 'pending',
    interviewer_name VARCHAR(100),
    notes TEXT,
    next_interview_date DATE,
    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_interview (candidate_id),
    INDEX idx_interview_date (interview_date),
    INDEX idx_status (status)
);

-- Candidate status history for audit trail
CREATE TABLE candidate_status_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    candidate_id INT NOT NULL,
    previous_status VARCHAR(50),
    new_status VARCHAR(50) NOT NULL,
    changed_by INT,
    reason TEXT,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
    FOREIGN KEY (changed_by) REFERENCES users(id),
    
    INDEX idx_candidate_history (candidate_id),
    INDEX idx_changed_at (changed_at)
);

-- Joining details for selected candidates
CREATE TABLE joining_details (
    id INT PRIMARY KEY AUTO_INCREMENT,
    candidate_id INT NOT NULL,
    joining_date DATE,
    actual_joining_date DATE,
    joining_status ENUM('scheduled', 'joined', 'not_joined', 'postponed') DEFAULT 'scheduled',
    salary_offered DECIMAL(10,2),
    department VARCHAR(50),
    position VARCHAR(100),
    reporting_manager VARCHAR(100),
    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,
    
    INDEX idx_candidate_joining (candidate_id),
    INDEX idx_joining_date (joining_date)
);

-- Reschedule tracking
CREATE TABLE reschedules (
    id INT PRIMARY KEY AUTO_INCREMENT,
    candidate_id INT NOT NULL,
    original_date DATE NOT NULL,
    new_date DATE NOT NULL,
    reason ENUM('Personal Emergency', 'Health Issues', 'Transport Issues', 'Other Commitment', 'Changed Mind', 'Other') NOT NULL,
    reason_details TEXT,
    rescheduled_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
    FOREIGN KEY (rescheduled_by) REFERENCES users(id),
    
    INDEX idx_candidate_reschedule (candidate_id),
    INDEX idx_original_date (original_date)
);
