-- Additional tables for enhanced interview process

-- Process logs table
CREATE TABLE IF NOT EXISTS process_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    candidate_id INT NOT NULL,
    action VARCHAR(50) NOT NULL,
    reason VARCHAR(255),
    notes TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Enhanced interviews table (if not exists, create with additional fields)
CREATE TABLE IF NOT EXISTS interviews_enhanced (
    id INT AUTO_INCREMENT PRIMARY KEY,
    candidate_id INT NOT NULL,
    interview_date DATE NOT NULL,
    interview_type ENUM('initial', 'technical', 'hr', 'final', 'group_discussion', 'practical_test') NOT NULL,
    interview_mode ENUM('in_person', 'video_call', 'phone_call', 'online_test') DEFAULT 'in_person',
    duration_minutes INT,
    interviewer_name VARCHAR(100) NOT NULL,
    interviewer_id INT,
    status ENUM('scheduled', 'completed', 'cancelled', 'rescheduled') DEFAULT 'scheduled',
    result ENUM('pending', 'shortlisted', 'selected', 'rejected', 'hold') DEFAULT 'pending',
    notes TEXT,
    next_interview_date DATE,
    next_interview_type VARCHAR(50),
    next_interviewer VARCHAR(100),
    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
);

-- Enhanced joining_details table
CREATE TABLE IF NOT EXISTS joining_details_enhanced (
    id INT AUTO_INCREMENT PRIMARY KEY,
    candidate_id INT NOT NULL,
    expected_joining_date DATE,
    actual_joining_date DATE,
    joining_status ENUM('pending', 'joined', 'not_joined', 'delayed') DEFAULT 'pending',
    salary_offered DECIMAL(10,2),
    department VARCHAR(100),
    position VARCHAR(100),
    reporting_manager VARCHAR(100),
    work_location VARCHAR(200),
    joining_notes TEXT,
    joining_feedback TEXT,
    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
);

-- Enhanced reschedules table
CREATE TABLE IF NOT EXISTS reschedules_enhanced (
    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,
    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) ON DELETE SET NULL
);

-- Add indexes for better performance
CREATE INDEX idx_interviews_candidate ON interviews_enhanced(candidate_id);
CREATE INDEX idx_interviews_date ON interviews_enhanced(interview_date);
CREATE INDEX idx_joining_candidate ON joining_details_enhanced(candidate_id);
CREATE INDEX idx_reschedules_candidate ON reschedules_enhanced(candidate_id);
CREATE INDEX idx_process_logs_candidate ON process_logs(candidate_id);
