CREATE TABLE IF NOT EXISTS admins (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(120) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS jobs (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 title VARCHAR(180) NOT NULL,
 slug VARCHAR(190) NOT NULL UNIQUE,
 department VARCHAR(120) NULL,
 location VARCHAR(180) NULL,
 employment_type VARCHAR(80) NULL,
 salary_range VARCHAR(120) NULL,
 summary TEXT NULL,
 description LONGTEXT NOT NULL,
 requirements LONGTEXT NULL,
 preferred_skills LONGTEXT NULL,
 assessment_type ENUM('none','disc','dope','both') NOT NULL DEFAULT 'none',
 status ENUM('draft','published','closed') NOT NULL DEFAULT 'draft',
 closing_date DATE NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS candidates (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 candidate_code VARCHAR(30) NOT NULL UNIQUE,
 full_name VARCHAR(180) NOT NULL,
 phone VARCHAR(60) NOT NULL,
 email VARCHAR(190) NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS applications (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 candidate_id INT UNSIGNED NOT NULL,
 job_id INT UNSIGNED NOT NULL,
 resume_path VARCHAR(255) NOT NULL,
 status ENUM('new','resume_review','assessment_pending','assessment_completed','shortlisted','interview','offer','hired','rejected','talent_pool') NOT NULL DEFAULT 'new',
 ai_resume_score DECIMAL(5,2) NULL,
 ai_resume_summary TEXT NULL,
 admin_notes TEXT NULL,
 applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_app_candidate FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
 CONSTRAINT fk_app_job FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS status_history (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 application_id INT UNSIGNED NOT NULL,
 old_status VARCHAR(50) NULL,
 new_status VARCHAR(50) NOT NULL,
 note TEXT NULL,
 changed_by INT UNSIGNED NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_hist_app FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;