-- ============================================================
-- ASKUREX JOB HUB — Database Schema (MySQL 8+)
-- ============================================================
CREATE DATABASE IF NOT EXISTS askurex_job_hub
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE askurex_job_hub;

-- ---------------------------------------------------------
-- CORE ACCOUNTS
-- ---------------------------------------------------------
CREATE TABLE users (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    unique_id       VARCHAR(20) NOT NULL UNIQUE,       -- e.g. AJH-CND-000123 / AJH-EMP-000045
    role            ENUM('candidate','employer','admin') NOT NULL,
    full_name       VARCHAR(150) NOT NULL,
    email           VARCHAR(150) NOT NULL UNIQUE,
    phone           VARCHAR(20) NOT NULL UNIQUE,
    password_hash   VARCHAR(255) NOT NULL,
    email_verified  BOOLEAN DEFAULT FALSE,
    phone_verified  BOOLEAN DEFAULT FALSE,
    status          ENUM('active','suspended','pending') DEFAULT 'pending',
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE candidate_profiles (
    user_id             INT PRIMARY KEY,
    nid_passport_no     VARCHAR(50),
    nid_verified        BOOLEAN DEFAULT FALSE,
    dob                 DATE,
    address             VARCHAR(255),
    education_summary   TEXT,
    experience_summary  TEXT,
    languages            VARCHAR(255),
    expected_salary     DECIMAL(10,2),
    availability        ENUM('immediate','1_month','negotiable') DEFAULT 'negotiable',
    resume_path         VARCHAR(255),
    portfolio_url       VARCHAR(255),
    profile_strength    TINYINT DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE employer_profiles (
    user_id           INT PRIMARY KEY,
    company_name      VARCHAR(150) NOT NULL,
    trade_license_no  VARCHAR(100),
    trade_license_path VARCHAR(255),
    tin_bin_no        VARCHAR(100),
    office_address    VARCHAR(255),
    company_website   VARCHAR(255),
    verified          BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Multiple HR accounts under one company
CREATE TABLE hr_members (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    employer_id   INT NOT NULL,
    user_id       INT NOT NULL,
    designation   VARCHAR(100),
    FOREIGN KEY (employer_id) REFERENCES employer_profiles(user_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- ---------------------------------------------------------
-- JOBS
-- ---------------------------------------------------------
CREATE TABLE jobs (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    employer_id     INT NOT NULL,
    title           VARCHAR(200) NOT NULL,
    category        ENUM('government','private','bank','ngo','international','remote','part_time','internship','freelance') NOT NULL,
    description     TEXT NOT NULL,
    location        VARCHAR(150),
    salary_min      DECIMAL(10,2),
    salary_max      DECIMAL(10,2),
    experience_req  VARCHAR(100),
    education_req   VARCHAR(150),
    deadline        DATE NOT NULL,
    is_featured     BOOLEAN DEFAULT FALSE,
    status          ENUM('pending','approved','rejected','closed') DEFAULT 'pending',
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employer_id) REFERENCES employer_profiles(user_id) ON DELETE CASCADE
);

CREATE TABLE applications (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    job_id          INT NOT NULL,
    candidate_id    INT NOT NULL,
    status          ENUM('applied','shortlisted','interview','offered','rejected','hired') DEFAULT 'applied',
    applied_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE,
    FOREIGN KEY (candidate_id) REFERENCES candidate_profiles(user_id) ON DELETE CASCADE,
    UNIQUE KEY uniq_application (job_id, candidate_id)
);

CREATE TABLE saved_jobs (
    candidate_id  INT NOT NULL,
    job_id        INT NOT NULL,
    saved_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (candidate_id, job_id)
);

-- ---------------------------------------------------------
-- EXAM SYSTEM
-- ---------------------------------------------------------
CREATE TABLE exams (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    created_by    INT NOT NULL,                 -- employer_id or admin user id
    title         VARCHAR(200) NOT NULL,
    type          ENUM('mcq','written','typing','iq','english','computer_skill','viva') NOT NULL,
    duration_min  INT NOT NULL,
    total_marks   INT NOT NULL,
    job_id        INT NULL,                      -- optional link to a specific job
    is_public     BOOLEAN DEFAULT FALSE,          -- available in "practice" section
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE SET NULL
);

CREATE TABLE exam_questions (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    exam_id       INT NOT NULL,
    question_text TEXT NOT NULL,
    option_a      VARCHAR(255),
    option_b      VARCHAR(255),
    option_c      VARCHAR(255),
    option_d      VARCHAR(255),
    correct_option CHAR(1),                       -- A/B/C/D, null for written
    marks         DECIMAL(5,2) DEFAULT 1,
    FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE
);

CREATE TABLE exam_results (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    exam_id       INT NOT NULL,
    candidate_id  INT NOT NULL,
    score         DECIMAL(6,2),
    evaluation    ENUM('auto','manual') DEFAULT 'auto',
    started_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    submitted_at  TIMESTAMP NULL,
    FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
    FOREIGN KEY (candidate_id) REFERENCES candidate_profiles(user_id) ON DELETE CASCADE
);

-- ---------------------------------------------------------
-- NOTIFICATIONS
-- ---------------------------------------------------------
CREATE TABLE notifications (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    user_id     INT NOT NULL,
    channel     ENUM('sms','email','whatsapp','push') NOT NULL,
    category    ENUM('new_job','exam_result','interview','deadline','admit_card') NOT NULL,
    message     VARCHAR(500) NOT NULL,
    is_read     BOOLEAN DEFAULT FALSE,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- ---------------------------------------------------------
-- REVENUE / SUBSCRIPTIONS
-- ---------------------------------------------------------
CREATE TABLE subscriptions (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    user_id       INT NOT NULL,
    plan          ENUM('candidate_premium','employer_featured','employer_branding','resume_db_access') NOT NULL,
    amount        DECIMAL(10,2) NOT NULL,
    starts_at     DATE NOT NULL,
    expires_at    DATE NOT NULL,
    payment_ref   VARCHAR(100),
    status        ENUM('active','expired','cancelled') DEFAULT 'active',
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- ---------------------------------------------------------
-- VERIFICATION / TRUST
-- ---------------------------------------------------------
CREATE TABLE scam_reports (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    reported_by  INT NOT NULL,
    job_id       INT NULL,
    employer_id  INT NULL,
    reason       TEXT NOT NULL,
    status       ENUM('open','reviewed','dismissed') DEFAULT 'open',
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (reported_by) REFERENCES users(id) ON DELETE CASCADE
);

-- ---------------------------------------------------------
-- BASIC INDEXES for common lookups
-- ---------------------------------------------------------
CREATE INDEX idx_jobs_category   ON jobs(category);
CREATE INDEX idx_jobs_status     ON jobs(status);
CREATE INDEX idx_jobs_deadline   ON jobs(deadline);
CREATE INDEX idx_apps_status     ON applications(status);