-- Database Schema for Recruitment Portal

CREATE DATABASE IF NOT EXISTS recruitment_db;
USE recruitment_db;

-- 1. Users Table (Admins, HR)
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin', 'hr', 'interviewer') DEFAULT 'hr',
    avatar_url VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. Jobs Table
CREATE TABLE IF NOT EXISTS jobs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(150) NOT NULL,
    department VARCHAR(100),
    location VARCHAR(100),
    type ENUM('Full-time', 'Contract', 'Remote', 'Part-time', 'Internship') DEFAULT 'Full-time',
    type ENUM('Full-time', 'Contract', 'Remote', 'Part-time', 'Internship') DEFAULT 'Full-time',
    status ENUM('Active', 'Closed', 'Draft') DEFAULT 'Draft',
    slug VARCHAR(255) UNIQUE,
    description TEXT,
    requirements TEXT,
    posted_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 3. Candidates Table
CREATE TABLE IF NOT EXISTS candidates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL,
    phone VARCHAR(50),
    location VARCHAR(100),
    resume_path VARCHAR(255),
    portfolio_url VARCHAR(255),
    experience_years INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(email)
);

-- 4. Applications Table
CREATE TABLE IF NOT EXISTS applications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    job_id INT NOT NULL,
    candidate_id INT NOT NULL,
    status ENUM('New', 'Screening', 'Interview', 'Offer', 'Rejected') DEFAULT 'New',
    applied_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    match_score INT DEFAULT 0, -- AI Score 0-100
    cover_letter TEXT,
    answers TEXT, -- JSON string of custom question answers
    FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE
);

-- 5. Interviews Table
CREATE TABLE IF NOT EXISTS interviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    application_id INT NOT NULL,
    interviewer_id INT,
    scheduled_at DATETIME NOT NULL,
    type ENUM('Video', 'Phone', 'On-site') DEFAULT 'Video',
    status ENUM('Scheduled', 'Completed', 'Cancelled') DEFAULT 'Scheduled',
    notes TEXT,
    FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE,
    FOREIGN KEY (interviewer_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Initial Dummy Data

-- Users
INSERT INTO users (name, email, password_hash, role) VALUES 
('Admin User', 'admin@recruitpro.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin'); -- password: password

-- Jobs
INSERT INTO jobs (title, department, location, type, status, posted_date) VALUES 
('Senior Frontend Developer', 'Engineering', 'Remote (US)', 'Full-time', 'Active', NOW()),
('Product Designer', 'Design', 'New York, NY', 'Full-time', 'Active', DATE_SUB(NOW(), INTERVAL 5 DAY)),
('Marketing Manager', 'Marketing', 'London, UK', 'Full-time', 'Closed', DATE_SUB(NOW(), INTERVAL 20 DAY));

-- Candidates
INSERT INTO candidates (full_name, email, location, experience_years) VALUES 
('John Doe', 'john.doe@example.com', 'San Francisco, CA', 5),
('Alice Smith', 'alice.smith@example.com', 'Remote', 3),
('Mike Ross', 'm.ross@law.com', 'New York, NY', 2);

-- Applications
INSERT INTO applications (job_id, candidate_id, status, match_score, applied_date) VALUES 
(1, 1, 'Screening', 85, NOW()),
(2, 2, 'Interview', 92, DATE_SUB(NOW(), INTERVAL 1 DAY)),
(1, 3, 'New', 60, DATE_SUB(NOW(), INTERVAL 2 DAY));
