// server/utils/configDb.ts
// Shared SQLite utility for device configuration (screens, anjungan, klinik-ruang, etc.)
// Designed to be a temporary layer - can be swapped to an external API with minimal changes.

import Database from 'better-sqlite3';
import { join } from 'path';
import { existsSync, mkdirSync } from 'fs';

const getDbPath = () => {
  const dbDir = join(process.cwd(), 'data');
  if (!existsSync(dbDir)) mkdirSync(dbDir, { recursive: true });
  return join(dbDir, 'users.db'); // Reuse same DB file
};

// ─── Default Seed Data ───────────────────────────────────────────────────────
// Used when tables are empty on first run. Mirrors the hardcoded defaults in Pinia stores.

const SEED_SCREENS = [
  { namaScreen: 'Layar Screen 1', nomorScreen: 'SCR-001', klinik: JSON.stringify(['AN', 'AS', 'BD', 'GI', 'GR', 'GZ', 'IP', 'JT']) },
  { namaScreen: 'Layar Screen 2', nomorScreen: 'SCR-002', klinik: JSON.stringify(['JW', 'KK', 'MT', 'SR', 'OB', 'PR']) },
  { namaScreen: 'Layar Screen 3', nomorScreen: 'SCR-003', klinik: JSON.stringify(['RT', 'RM', 'HO']) },
];

const SEED_ANJUNGAN = [
  { namaAnjungan: 'Anjungan Reguler', jenisPasien: 'Reguler', klinik: JSON.stringify(['AK', 'AN', 'BD', 'GR', 'GM', 'GZ', 'HO', 'IP', 'JT', 'JW', 'KD', 'KK', 'KM', 'KO', 'MC', 'MT', 'ON', 'PR', 'RD', 'RM', 'RT', 'SR', 'TH']) },
  { namaAnjungan: 'Anjungan Eksekutif', jenisPasien: 'Eksekutif', klinik: JSON.stringify(['AK', 'AN', 'BD', 'GM', 'GR', 'GZ', 'HO', 'IP', 'JT', 'JW', 'KD', 'KK', 'KM', 'KO', 'MC', 'MT', 'ON', 'PR', 'RD', 'RM', 'RT', 'SR', 'TH']) },
];

const SEED_ANTREAN_MASUK_SCREENS = [
  { namaScreen: 'Layar Antrean Masuk 1', nomorScreen: 'AM-001', loket: JSON.stringify([1, 2, 12, 14]) },
  { namaScreen: 'Layar Antrean Masuk 2', nomorScreen: 'AM-002', loket: JSON.stringify([3, 4]) },
];

const SEED_KLINIK_RUANG = [
  { kodeKlinik: 'AN', namaKlinik: 'ANAK', ruangList: JSON.stringify([{ nomorRuang: '1', namaRuang: 'R. TINDAKAN', nomorScreen: '101' }]) },
  { kodeKlinik: 'AS', namaKlinik: 'ANESTESI', ruangList: JSON.stringify([{ nomorRuang: '1', namaRuang: 'Ruang 1', nomorScreen: '201' }, { nomorRuang: '2', namaRuang: 'Ruang 2', nomorScreen: '202' }, { nomorRuang: '3', namaRuang: 'Ruang 3', nomorScreen: '203' }]) },
  { kodeKlinik: 'BD', namaKlinik: 'BEDAH', ruangList: JSON.stringify([{ nomorRuang: '1', namaRuang: 'Ruang Konsultasi', nomorScreen: '301' }]) },
  { kodeKlinik: 'GR', namaKlinik: 'GERIATRI', ruangList: JSON.stringify([{ nomorRuang: '1', namaRuang: 'Ruang Pemeriksaan', nomorScreen: '401' }]) },
  { kodeKlinik: 'GI', namaKlinik: 'GIGI DAN MULUT', ruangList: JSON.stringify([{ nomorRuang: '1', namaRuang: 'Ruang 1', nomorScreen: '501' }, { nomorRuang: '2', namaRuang: 'Ruang 2', nomorScreen: '502' }, { nomorRuang: '3', namaRuang: 'Ruang 3', nomorScreen: '503' }]) },
];

// ─── DB Initialization ────────────────────────────────────────────────────────

let _db: InstanceType<typeof Database> | null = null;

export const getConfigDb = () => {
  if (_db) return _db;

  const dbPath = getDbPath();
  const db = new Database(dbPath);
  db.pragma('journal_mode = WAL'); // Better concurrent performance

  db.exec(`
    CREATE TABLE IF NOT EXISTS config_screen (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      namaScreen TEXT NOT NULL,
      nomorScreen TEXT UNIQUE NOT NULL,
      klinik TEXT DEFAULT '[]',
      createdAt INTEGER DEFAULT (strftime('%s', 'now')),
      updatedAt INTEGER DEFAULT (strftime('%s', 'now'))
    );

    CREATE TABLE IF NOT EXISTS config_anjungan (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      namaAnjungan TEXT NOT NULL,
      jenisPasien TEXT NOT NULL,
      klinik TEXT DEFAULT '[]',
      createdAt INTEGER DEFAULT (strftime('%s', 'now')),
      updatedAt INTEGER DEFAULT (strftime('%s', 'now'))
    );

    CREATE TABLE IF NOT EXISTS config_antrean_masuk_screen (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      namaScreen TEXT NOT NULL,
      nomorScreen TEXT UNIQUE NOT NULL,
      loket TEXT DEFAULT '[]',
      createdAt INTEGER DEFAULT (strftime('%s', 'now')),
      updatedAt INTEGER DEFAULT (strftime('%s', 'now'))
    );

    CREATE TABLE IF NOT EXISTS config_klinik_ruang (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      kodeKlinik TEXT NOT NULL,
      namaKlinik TEXT NOT NULL,
      ruangList TEXT DEFAULT '[]',
      createdAt INTEGER DEFAULT (strftime('%s', 'now')),
      updatedAt INTEGER DEFAULT (strftime('%s', 'now'))
    );
  `);

  // Seed defaults if tables are empty
  _seedIfEmpty(db, 'config_screen', SEED_SCREENS);
  _seedIfEmpty(db, 'config_anjungan', SEED_ANJUNGAN);
  _seedIfEmpty(db, 'config_antrean_masuk_screen', SEED_ANTREAN_MASUK_SCREENS);
  _seedIfEmpty(db, 'config_klinik_ruang', SEED_KLINIK_RUANG);

  console.log('✅ [configDb] Config database initialized');
  _db = db;
  return db;
};

function _seedIfEmpty(db: InstanceType<typeof Database>, table: string, rows: Record<string, any>[]) {
  const count = (db.prepare(`SELECT COUNT(*) as c FROM ${table}`).get() as any).c;
  if (count === 0) {
    console.log(`🌱 [configDb] Seeding ${table} with ${rows.length} default rows`);
    const keys = Object.keys(rows[0]);
    const placeholders = keys.map(() => '?').join(', ');
    const stmt = db.prepare(`INSERT INTO ${table} (${keys.join(', ')}) VALUES (${placeholders})`);
    rows.forEach(row => stmt.run(...keys.map(k => row[k])));
  }
}

// ─── Helper: Parse JSON columns ───────────────────────────────────────────────
export const parseConfigRow = (row: any) => {
  if (!row) return null;
  const parsed = { ...row };
  // Auto-parse any column that looks like a JSON array/object
  for (const key of Object.keys(parsed)) {
    if (typeof parsed[key] === 'string' && (parsed[key].startsWith('[') || parsed[key].startsWith('{'))) {
      try { parsed[key] = JSON.parse(parsed[key]); } catch {}
    }
  }
  return parsed;
};
