import fs from "node:fs";
import path from "node:path";
import bcrypt from "bcryptjs";
import Database from "better-sqlite3";

export type StaffUser = {
  id: number;
  username: string;
  password_hash: string;
};

const globalForDb = globalThis as unknown as { __ihDb?: Database.Database };

export function sqlitePath() {
  const configured = process.env.SQLITE_PATH?.trim();
  if (!configured) return path.join(process.cwd(), "data", "panel.sqlite");
  return path.isAbsolute(configured) ? configured : path.join(process.cwd(), configured);
}

export function getDb() {
  if (!globalForDb.__ihDb) {
    const file = sqlitePath();
    fs.mkdirSync(path.dirname(file), { recursive: true });
    const db = new Database(file);
    db.pragma("journal_mode = WAL");
    db.pragma("foreign_keys = ON");
    db.pragma("busy_timeout = 5000");
    migrate(db);
    seed(db);
    globalForDb.__ihDb = db;
  }
  return globalForDb.__ihDb;
}

function migrate(db: Database.Database) {
  db.exec(`
    CREATE TABLE IF NOT EXISTS staff_users (
      id INTEGER PRIMARY KEY,
      username TEXT NOT NULL UNIQUE,
      password_hash TEXT NOT NULL,
      created_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS cases (
      id INTEGER PRIMARY KEY,
      case_number TEXT NOT NULL UNIQUE,
      public_token TEXT NOT NULL UNIQUE,
      first_name TEXT NOT NULL,
      last_name TEXT NOT NULL,
      mobile TEXT NOT NULL,
      mobile_key TEXT NOT NULL,
      language TEXT NOT NULL,
      country TEXT NOT NULL,
      city TEXT NOT NULL,
      group_id TEXT NOT NULL,
      specialty_id TEXT NOT NULL,
      requested_services TEXT NOT NULL,
      received_services TEXT NOT NULL,
      center_name TEXT NOT NULL DEFAULT '',
      summary TEXT NOT NULL DEFAULT '',
      arrived_iran_on TEXT,
      visited_company_on TEXT,
      visited_center_on TEXT,
      departed_iran_on TEXT,
      internal_note TEXT NOT NULL DEFAULT '',
      sms_text TEXT NOT NULL DEFAULT '',
      sms_status TEXT NOT NULL DEFAULT 'unsent',
      sms_error TEXT,
      sms_attempted_at TEXT,
      code_failures INTEGER NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS path_dates (
      id INTEGER PRIMARY KEY,
      case_id INTEGER NOT NULL REFERENCES cases(id) ON DELETE CASCADE,
      title TEXT NOT NULL,
      event_date TEXT NOT NULL,
      sort_order INTEGER NOT NULL
    );

    CREATE TABLE IF NOT EXISTS timeline_events (
      id INTEGER PRIMARY KEY,
      case_id INTEGER NOT NULL REFERENCES cases(id) ON DELETE CASCADE,
      stage_id TEXT NOT NULL,
      sentence_override TEXT,
      happened_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS patient_codes (
      id INTEGER PRIMARY KEY,
      case_id INTEGER NOT NULL REFERENCES cases(id) ON DELETE CASCADE,
      code_hash TEXT NOT NULL,
      code_plain TEXT,
      visible_to_staff INTEGER NOT NULL DEFAULT 0,
      expires_at TEXT NOT NULL,
      used_at TEXT,
      created_at TEXT NOT NULL
    );

    CREATE INDEX IF NOT EXISTS idx_cases_mobile ON cases(mobile_key);
    CREATE INDEX IF NOT EXISTS idx_cases_specialty ON cases(specialty_id);
    CREATE INDEX IF NOT EXISTS idx_events_case ON timeline_events(case_id, happened_at);
    CREATE INDEX IF NOT EXISTS idx_codes_case ON patient_codes(case_id);
    CREATE INDEX IF NOT EXISTS idx_dates_case ON path_dates(case_id, sort_order);
  `);
}

function seed(db: Database.Database) {
  if (process.env.NEXT_PHASE === "phase-production-build") return;
  const row = db.prepare("SELECT COUNT(*) AS count FROM staff_users").get() as { count: number };
  if (row.count > 0) return;
  const username = process.env.ADMIN_INITIAL_USERNAME?.trim() || "admin";
  const password = process.env.ADMIN_INITIAL_PASSWORD?.trim() || "IranHealth-1405";
  db.prepare(
    "INSERT INTO staff_users (username, password_hash, created_at) VALUES (?, ?, ?)",
  ).run(username, bcrypt.hashSync(password, 10), new Date().toISOString());
}

export function getStaffByUsername(username: string) {
  return getDb()
    .prepare("SELECT id, username, password_hash FROM staff_users WHERE username = ?")
    .get(username) as StaffUser | undefined;
}

export function getStaffUser(id: number) {
  return getDb()
    .prepare("SELECT id, username, password_hash FROM staff_users WHERE id = ?")
    .get(id) as StaffUser | undefined;
}

export function updateStaffPassword(id: number, passwordHash: string) {
  getDb().prepare("UPDATE staff_users SET password_hash = ? WHERE id = ?").run(passwordHash, id);
}