<?php
/**
 * Delmas Apparels Career Module — Database bootstrap (SQLite via PDO)
 * Auto-creates the database file and tables on first run.
 * No MySQL setup needed — everything lives in career/data/careers.db
 */

define('CAREER_ROOT', dirname(__DIR__));
define('DB_PATH', CAREER_ROOT . '/data/careers.db');
define('UPLOAD_DIR', CAREER_ROOT . '/uploads/resumes/');

function get_db(): PDO {
    static $pdo = null;
    if ($pdo !== null) return $pdo;

    $isNew = !file_exists(DB_PATH);
    $pdo = new PDO('sqlite:' . DB_PATH);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->exec('PRAGMA foreign_keys = ON;');

    if ($isNew) {
        create_schema($pdo);
    } else {
        migrate_schema($pdo);
    }
    return $pdo;
}

function create_schema(PDO $pdo): void {
    $pdo->exec("CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        username TEXT NOT NULL UNIQUE,
        email TEXT,
        password_hash TEXT NOT NULL,
        role TEXT NOT NULL DEFAULT 'admin', -- 'superadmin' or 'admin'
        active INTEGER NOT NULL DEFAULT 1,
        created_at TEXT NOT NULL DEFAULT (datetime('now'))
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS jobs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        department TEXT,
        location TEXT,
        job_type TEXT,
        vacancy TEXT,
        age_range TEXT,
        experience TEXT,
        salary TEXT,
        description TEXT,
        read_before_apply TEXT,
        apply_procedure TEXT,
        deadline TEXT,              -- YYYY-MM-DD, null = no deadline
        status TEXT NOT NULL DEFAULT 'published', -- 'published' or 'draft'
        created_by INTEGER,
        created_at TEXT NOT NULL DEFAULT (datetime('now')),
        updated_at TEXT NOT NULL DEFAULT (datetime('now')),
        FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS job_links (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        job_id INTEGER NOT NULL,
        label TEXT NOT NULL,       -- e.g. 'Apply on BDJobs', 'Apply on LinkedIn'
        url TEXT NOT NULL,
        FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS skills (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS job_skills (
        job_id INTEGER NOT NULL,
        skill_id INTEGER NOT NULL,
        PRIMARY KEY (job_id, skill_id),
        FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE,
        FOREIGN KEY (skill_id) REFERENCES skills(id) ON DELETE CASCADE
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS applications (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        job_id INTEGER NOT NULL,
        applicant_name TEXT NOT NULL,
        email TEXT NOT NULL,
        phone TEXT,
        cover_note TEXT,
        cv_path TEXT,
        cv_original_name TEXT,
        doc_path TEXT,
        doc_original_name TEXT,
        ip_address TEXT,
        applied_at TEXT NOT NULL DEFAULT (datetime('now')),
        FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE
    )");

    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_jobs_status ON jobs(status)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_apps_job ON applications(job_id)");

    seed_default_skills($pdo);
}

/** Adds new columns/tables to an existing database created by an earlier version. */
function migrate_schema(PDO $pdo): void {
    $cols = array_column($pdo->query("PRAGMA table_info(jobs)")->fetchAll(PDO::FETCH_ASSOC), 'name');
    $wanted = [
        'vacancy' => "ALTER TABLE jobs ADD COLUMN vacancy TEXT",
        'age_range' => "ALTER TABLE jobs ADD COLUMN age_range TEXT",
        'experience' => "ALTER TABLE jobs ADD COLUMN experience TEXT",
        'salary' => "ALTER TABLE jobs ADD COLUMN salary TEXT",
        'read_before_apply' => "ALTER TABLE jobs ADD COLUMN read_before_apply TEXT",
        'apply_procedure' => "ALTER TABLE jobs ADD COLUMN apply_procedure TEXT",
    ];
    foreach ($wanted as $col => $sql) {
        if (!in_array($col, $cols, true)) {
            $pdo->exec($sql);
        }
    }

    $pdo->exec("CREATE TABLE IF NOT EXISTS skills (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE
    )");
    $pdo->exec("CREATE TABLE IF NOT EXISTS job_skills (
        job_id INTEGER NOT NULL,
        skill_id INTEGER NOT NULL,
        PRIMARY KEY (job_id, skill_id),
        FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE,
        FOREIGN KEY (skill_id) REFERENCES skills(id) ON DELETE CASCADE
    )");

    $count = (int)$pdo->query("SELECT COUNT(*) FROM skills")->fetchColumn();
    if ($count === 0) {
        seed_default_skills($pdo);
    }
}

function seed_default_skills(PDO $pdo): void {
    $defaults = ['CCTV', 'IT Support Service', 'LAN/WAN', 'Networking', 'Hardware Troubleshooting',
        'Server Management', 'MS Office', 'ERP Software', 'Adobe Photoshop', 'Communication'];
    $ins = $pdo->prepare("INSERT OR IGNORE INTO skills (name) VALUES (?)");
    foreach ($defaults as $s) {
        $ins->execute([$s]);
    }
}
