-- Sigma Recruitment — MySQL schema
-- Import this once via cPanel's phpMyAdmin (or `mysql -u user -p dbname < schema.sql`
-- over SSH if your host gives you that) against an EMPTY database you created
-- with cPanel's "MySQL Databases" tool. Safe to re-run: every statement is
-- idempotent (CREATE TABLE IF NOT EXISTS).

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================
-- users — single table for all three roles (admin/jobseeker/company),
-- mirroring the original design: one login endpoint, one `role` column.
-- ============================================================
CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin', 'jobseeker', 'company') NOT NULL DEFAULT 'jobseeker',
    -- Only set when role = 'company' — which company this portal login belongs to.
    company_id INT UNSIGNED NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_users_role (role),
    INDEX idx_users_company (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- companies — SME/private-company records, fully admin-managed.
-- ============================================================
CREATE TABLE IF NOT EXISTS companies (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(190) NOT NULL,
    slug VARCHAR(200) NOT NULL UNIQUE,
    type ENUM('SME', 'Private Company', 'Startup', 'NGO', 'Other') NOT NULL DEFAULT 'SME',
    industry VARCHAR(120) NULL,
    description TEXT NULL,
    contact_email VARCHAR(190) NULL,
    contact_phone VARCHAR(60) NULL,
    website VARCHAR(190) NULL,
    logo_url VARCHAR(255) NULL,
    city VARCHAR(120) NULL,
    country VARCHAR(120) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    managed_by INT UNSIGNED NULL COMMENT 'admin user who created it',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FULLTEXT INDEX ft_companies_search (name, industry, description)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE users
    ADD CONSTRAINT fk_users_company
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL;

-- ============================================================
-- jobs — postings tied to a company, posted by an admin.
-- ============================================================
CREATE TABLE IF NOT EXISTS jobs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    title VARCHAR(190) NOT NULL,
    slug VARCHAR(220) NOT NULL UNIQUE,
    description TEXT NOT NULL,
    category VARCHAR(120) NOT NULL,
    statute ENUM('Employee', 'Freelance', 'Interim', 'Internship') NOT NULL DEFAULT 'Employee',
    employment_type ENUM('Full-time', 'Part-time', 'Temporary') NOT NULL DEFAULT 'Full-time',
    work_regime ENUM('On-site', 'Remote', 'Hybrid') NOT NULL DEFAULT 'On-site',
    city VARCHAR(120) NULL,
    country VARCHAR(120) NULL,
    salary_min INT NULL,
    salary_max INT NULL,
    salary_currency VARCHAR(10) NULL DEFAULT 'USD',
    status ENUM('draft', 'published', 'closed') NOT NULL DEFAULT 'draft',
    posted_by INT UNSIGNED NULL COMMENT 'admin user who created it',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_jobs_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    INDEX idx_jobs_status (status),
    INDEX idx_jobs_category (category),
    FULLTEXT INDEX ft_jobs_search (title, description)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- jobseeker_profiles — one per jobseeker user.
-- ============================================================
CREATE TABLE IF NOT EXISTS jobseeker_profiles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL UNIQUE,
    first_name VARCHAR(120) NOT NULL DEFAULT '',
    last_name VARCHAR(120) NOT NULL DEFAULT '',
    phone VARCHAR(60) NULL,
    date_of_birth DATE NULL,
    address VARCHAR(255) NULL,
    city VARCHAR(120) NULL,
    country VARCHAR(120) NULL,
    headline VARCHAR(190) NULL,
    summary TEXT NULL,
    skills TEXT NULL COMMENT 'comma-separated list, kept simple on purpose',
    languages TEXT NULL COMMENT 'comma-separated list',
    resume_path VARCHAR(255) NULL COMMENT 'relative path under public/uploads',
    photo_path VARCHAR(255) NULL COMMENT 'relative path under public/uploads',
    profile_completeness TINYINT UNSIGNED NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_profiles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- experiences — professional background, many per profile.
-- ============================================================
CREATE TABLE IF NOT EXISTS experiences (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    profile_id INT UNSIGNED NOT NULL,
    job_title VARCHAR(190) NOT NULL,
    employer VARCHAR(190) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NULL,
    is_current TINYINT(1) NOT NULL DEFAULT 0,
    description TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_experiences_profile FOREIGN KEY (profile_id) REFERENCES jobseeker_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- educations — academic background, many per profile.
-- ============================================================
CREATE TABLE IF NOT EXISTS educations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    profile_id INT UNSIGNED NOT NULL,
    degree VARCHAR(190) NOT NULL,
    institution VARCHAR(190) NOT NULL,
    start_date DATE NULL,
    end_date DATE NULL,
    description TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_educations_profile FOREIGN KEY (profile_id) REFERENCES jobseeker_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- applications — links a job + a jobseeker user. Simple 5-status pipeline
-- for this MVP (see the "Growing this later" notes at the bottom of this
-- file for how to extend it into a fuller ATS pipeline without breaking
-- anything already built).
-- ============================================================
CREATE TABLE IF NOT EXISTS applications (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    job_id INT UNSIGNED NOT NULL,
    jobseeker_id INT UNSIGNED NOT NULL,
    cover_letter TEXT NULL,
    resume_snapshot_path VARCHAR(255) NULL COMMENT 'copy of profile.resume_path at time of applying',
    status ENUM('submitted', 'reviewed', 'shortlisted', 'rejected', 'hired') NOT NULL DEFAULT 'submitted',
    admin_notes TEXT NULL,
    company_notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_applications_job FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE,
    CONSTRAINT fk_applications_jobseeker FOREIGN KEY (jobseeker_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY uq_application_job_seeker (job_id, jobseeker_id),
    INDEX idx_applications_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- Growing this later
-- ============================================================
-- This schema deliberately covers the MVP + company portal only. The
-- earlier Node/MongoDB version of this project also had a fuller ATS
-- pipeline, interview scheduling, onboarding checklists, and company
-- headcount requisitions — none of that is dropped forever, just not part
-- of this first PHP pass. When you're ready to add it back in, here's the
-- shape it would take (uncomment/adapt as needed — none of this runs today):
--
-- 1. Richer pipeline: widen the `applications.status` ENUM to add stages
--    (e.g. 'screening','interview','debrief','offer','accepted' instead of
--    the current 5), and add an `application_status_history` table
--    (application_id, status, note, changed_by, changed_at) to keep a
--    timestamped trail instead of just the current status.
--
-- 2. Interviews:
--    CREATE TABLE interviews (
--      id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
--      application_id INT UNSIGNED NOT NULL,
--      scheduled_at DATETIME NOT NULL,
--      duration_minutes SMALLINT UNSIGNED DEFAULT 45,
--      mode ENUM('Phone','Video','On-site') DEFAULT 'Video',
--      interviewers TEXT NULL,          -- comma-separated, or a child table
--      questions TEXT NULL,             -- JSON array of {competency, question}
--      scorecard TEXT NULL,             -- JSON array of {interviewer, competency, rating, notes}
--      overall_recommendation ENUM('strong_yes','yes','no','strong_no') NULL,
--      status ENUM('scheduled','completed','cancelled') DEFAULT 'scheduled',
--      created_by INT UNSIGNED NULL,
--      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
--    );
--
-- 3. Onboarding: one row per accepted application (start_date, buddy,
--    pre_start_tasks/day1_tasks/week1_tasks as JSON, goals_30/60/90, notes),
--    auto-created the moment an application reaches the final stage.
--
-- 4. Requisitions: company or admin requests new headcount
--    (company_id, title, headcount, category, target_start_date, status,
--    requested_by, reviewed_by, linked_job_id) — admin approves and
--    converts it into a draft row in `jobs`.
--
-- MySQL's JSON column type (5.7.8+/MariaDB 10.2.7+) works well for the
-- semi-structured bits above (questions/scorecard/tasks) without needing
-- extra child tables, if your host's MySQL version supports it.