// database.js — opens SQLite, creates tables, and seeds realistic demo data once.
const fs = require('fs');
const path = require('path');
const initSqlJs = require('sql.js');

const DB_FILE = path.join(__dirname, 'database.sqlite');
let SQL;
let db;

function exportDb() {
  const data = db.export();
  fs.writeFileSync(DB_FILE, Buffer.from(data));
}

function getLastInsertRowid() {
  const result = db.exec('SELECT last_insert_rowid() AS id');
  return result && result[0] && result[0].values && result[0].values[0] ? result[0].values[0][0] : null;
}

function prepare(sql) {
  return {
    source: sql,
    all: (...params) => {
      const stmt = db.prepare(sql);
      if (params.length) stmt.bind(params);
      const rows = [];
      while (stmt.step()) rows.push(stmt.getAsObject());
      stmt.free();
      return rows;
    },
    get: (...params) => {
      const stmt = db.prepare(sql);
      if (params.length) stmt.bind(params);
      const row = stmt.step() ? stmt.getAsObject() : null;
      stmt.free();
      return row;
    },
    run: (...params) => {
      const stmt = db.prepare(sql);
      if (params.length) stmt.bind(params);
      stmt.step();
      stmt.free();
      exportDb();
      return { changes: db.getRowsModified(), lastInsertRowid: getLastInsertRowid() };
    }
  };
}

async function init() {
  if (db) return db;
  SQL = await initSqlJs({ locateFile: file => path.join(__dirname, 'node_modules', 'sql.js', 'dist', file) });
  const fileData = fs.existsSync(DB_FILE) ? fs.readFileSync(DB_FILE) : null;
  db = fileData ? new SQL.Database(new Uint8Array(fileData)) : new SQL.Database();
  db.run('PRAGMA foreign_keys = ON;');

  db.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id          INTEGER PRIMARY KEY AUTOINCREMENT,
      name        TEXT NOT NULL,
      role        TEXT NOT NULL,
      department  TEXT NOT NULL DEFAULT 'Telecom & Navigation',
      active      INTEGER NOT NULL DEFAULT 1,
      created_at  TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS equipment (
      id          INTEGER PRIMARY KEY AUTOINCREMENT,
      name        TEXT NOT NULL,
      type        TEXT NOT NULL,
      location    TEXT NOT NULL,
      area        TEXT NOT NULL,
      active      INTEGER NOT NULL DEFAULT 1,
      created_at  TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS checklist_templates (
      id              INTEGER PRIMARY KEY AUTOINCREMENT,
      name            TEXT NOT NULL,
      inspection_type TEXT NOT NULL,
      items           TEXT NOT NULL,
      active          INTEGER NOT NULL DEFAULT 1,
      created_at      TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS inspections (
      id                    INTEGER PRIMARY KEY AUTOINCREMENT,
      title                 TEXT NOT NULL,
      equipment_id          INTEGER REFERENCES equipment(id),
      inspection_type       TEXT NOT NULL,
      status                TEXT NOT NULL,
      scheduled_date        TEXT NOT NULL,
      deadline              TEXT,
      priority              TEXT NOT NULL DEFAULT 'normal',
      assigned_to           INTEGER REFERENCES users(id),
      created_by            INTEGER REFERENCES users(id),
      checklist_template_id INTEGER REFERENCES checklist_templates(id),
      checklist_results     TEXT,
      result_notes          TEXT,
      revision_notes        TEXT,
      validated_by          INTEGER REFERENCES users(id),
      started_at            TEXT,
      submitted_at          TEXT,
      validated_at          TEXT,
      created_at            TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at            TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS findings (
      id                  INTEGER PRIMARY KEY AUTOINCREMENT,
      inspection_id       INTEGER REFERENCES inspections(id),
      equipment_id        INTEGER REFERENCES equipment(id),
      description         TEXT NOT NULL,
      location            TEXT,
      urgency             TEXT NOT NULL DEFAULT 'medium',
      status              TEXT NOT NULL,
      evidence_note       TEXT,
      recommendation      TEXT,
      assigned_to         INTEGER REFERENCES users(id),
      follow_up_notes     TEXT,
      completion_evidence TEXT,
      created_by          INTEGER REFERENCES users(id),
      created_at          TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at          TEXT NOT NULL DEFAULT (datetime('now'))
    );
  `);

  const seeded = prepare('SELECT COUNT(*) AS n FROM users').get().n > 0;
  if (!seeded) {
    const insUser = prepare('INSERT INTO users (name, role, department, active) VALUES (?, ?, ?, ?)');
    const techA = insUser.run('Aderito Soares', 'technician', 'Telecom & Navigation', 1).lastInsertRowid;
    const techF = insUser.run('Filomena Pereira', 'technician', 'Telecom & Navigation', 1).lastInsertRowid;
    const techJ = insUser.run('Joaquim Da Costa', 'technician', 'Telecom & Navigation', 1).lastInsertRowid;
    const sup = insUser.run('Domingos Ximenes', 'supervisor', 'Telecom & Navigation', 1).lastInsertRowid;
    const mgr = insUser.run('Mariana Guterres', 'management', 'Telecom & Navigation', 1).lastInsertRowid;
    const adm = insUser.run('Sistema Admin', 'administrator', 'Telecom & Navigation', 1).lastInsertRowid;
    insUser.run('Paulo Belo (retired)', 'technician', 'Telecom & Navigation', 0);

    const insEq = prepare('INSERT INTO equipment (name, type, location, area, active) VALUES (?, ?, ?, ?, ?)');
    const eVOR = insEq.run('DVOR/DME COM-50', 'Navigation', 'Nav Aid Field North', 'DVOR Site', 1).lastInsertRowid;
    const eLOC = insEq.run('ILS Localizer RWY 08', 'Navigation', 'Runway 08 Threshold', 'ILS Site', 1).lastInsertRowid;
    const eGP = insEq.run('ILS Glide Path RWY 08', 'Navigation', 'Runway 08 Aiming Point', 'ILS Site', 1).lastInsertRowid;
    const eVHF = insEq.run('VHF Air-Ground Radio Main', 'Communication', 'Control Tower', 'Control Tower', 1).lastInsertRowid;
    const eNDB = insEq.run('NDB Beacon DIL', 'Navigation', 'Beacon Field', 'NDB Site', 1).lastInsertRowid;
    const eRAD = insEq.run('Primary Surveillance Radar', 'Surveillance', 'Radar Hill', 'Radar Site', 1).lastInsertRowid;
    const eATIS = insEq.run('ATIS Broadcast Unit', 'Communication', 'Equipment Room', 'Equipment Room', 1).lastInsertRowid;
    const eGEN = insEq.run('Standby Generator 250kVA', 'Power', 'Power House', 'Power House', 1).lastInsertRowid;
    const eUPS = insEq.run('UPS Bank Equipment Room', 'Power', 'Equipment Room', 'Equipment Room', 1).lastInsertRowid;
    insEq.run('Old VSAT Modem (decommissioned)', 'Communication', 'Equipment Room', 'Equipment Room', 0);

    const insCl = prepare('INSERT INTO checklist_templates (name, inspection_type, items, active) VALUES (?, ?, ?, ?)');
    const clDaily = insCl.run('Daily Communication/Nav Check', 'daily', JSON.stringify([
      'Verify equipment is powered and ON',
      'Check transmit / receive audio quality',
      'Verify operating frequency / channel',
      'Inspect antenna and feeder cable',
      'Record power output / signal readings',
      'Confirm no active alarms on panel'
    ]), 1).lastInsertRowid;
    const clWeekly = insCl.run('Weekly Facility & Power Check', 'weekly', JSON.stringify([
      'Inspect equipment shelter and ventilation',
      'Check air-conditioning / temperature log',
      'Test standby generator auto-start',
      'Inspect UPS battery indicators',
      'Verify earthing / grounding connections',
      'Clean filters and inspect for corrosion'
    ]), 1).lastInsertRowid;
    const clMonthly = insCl.run('Monthly Navigation Calibration Check', 'monthly', JSON.stringify([
      'Verify ILS/VOR alignment within tolerance',
      'Check monitor and changeover function',
      'Review monthly fault and event log',
      'Inspect tower lighting and obstruction lamps',
      'Test remote control and monitoring link',
      'Confirm calibration records are up to date'
    ]), 1).lastInsertRowid;

    const itemsOf = (tplId) => JSON.parse(prepare('SELECT items FROM checklist_templates WHERE id = ?').get(tplId).items);
    const resultsAll = (tplId, condition, abnormalNote) => {
      const items = itemsOf(tplId);
      return JSON.stringify(items.map((item, i) => ({
        item,
        condition: (abnormalNote && i === items.length - 1) ? 'abnormal' : condition,
        note: (abnormalNote && i === items.length - 1) ? abnormalNote : ''
      })));
    };

    const insIns = prepare(`INSERT INTO inspections
      (title, equipment_id, inspection_type, status, scheduled_date, deadline, priority,
       assigned_to, created_by, checklist_template_id, checklist_results, result_notes,
       revision_notes, validated_by, started_at, submitted_at, validated_at)
      VALUES (@title,@equipment_id,@inspection_type,@status,@scheduled_date,@deadline,@priority,
       @assigned_to,@created_by,@checklist_template_id,@checklist_results,@result_notes,
       @revision_notes,@validated_by,@started_at,@submitted_at,@validated_at)`);

    const base = {
      checklist_results: null, result_notes: null, revision_notes: null,
      validated_by: null, started_at: null, submitted_at: null, validated_at: null,
      priority: 'normal', deadline: null
    };
    const mk = (o) => insIns.run({ ...base, ...o }).lastInsertRowid;

    mk({ title: 'Daily check — VHF Air-Ground Radio Main', equipment_id: eVHF, inspection_type: 'daily',
      status: 'Assigned', scheduled_date: '2026-06-03', deadline: '2026-06-03', priority: 'high',
      assigned_to: techA, created_by: sup, checklist_template_id: clDaily });
    mk({ title: 'Daily check — DVOR/DME COM-50', equipment_id: eVOR, inspection_type: 'daily',
      status: 'In Progress', scheduled_date: '2026-06-03', deadline: '2026-06-03',
      assigned_to: techA, created_by: sup, checklist_template_id: clDaily,
      started_at: '2026-06-03 08:15:00',
      checklist_results: resultsAll(clDaily, 'normal') });
    mk({ title: 'Daily check — ATIS Broadcast Unit', equipment_id: eATIS, inspection_type: 'daily',
      status: 'Assigned', scheduled_date: '2026-06-03',
      assigned_to: techF, created_by: sup, checklist_template_id: clDaily });

    mk({ title: 'Weekly check — Standby Generator 250kVA', equipment_id: eGEN, inspection_type: 'weekly',
      status: 'Scheduled', scheduled_date: '2026-06-06', priority: 'normal',
      assigned_to: techA, created_by: sup, checklist_template_id: clWeekly });
    mk({ title: 'Monthly calibration — ILS Localizer RWY 08', equipment_id: eLOC, inspection_type: 'monthly',
      status: 'Assigned', scheduled_date: '2026-06-10', priority: 'high',
      assigned_to: techJ, created_by: sup, checklist_template_id: clMonthly });
    mk({ title: 'Weekly check — UPS Bank Equipment Room', equipment_id: eUPS, inspection_type: 'weekly',
      status: 'Scheduled', scheduled_date: '2026-06-08',
      assigned_to: techF, created_by: sup, checklist_template_id: clWeekly });

    mk({ title: 'Daily check — Primary Surveillance Radar', equipment_id: eRAD, inspection_type: 'daily',
      status: 'Overdue', scheduled_date: '2026-06-01', deadline: '2026-06-01', priority: 'high',
      assigned_to: techA, created_by: sup, checklist_template_id: clDaily });
    mk({ title: 'Daily check — NDB Beacon DIL', equipment_id: eNDB, inspection_type: 'daily',
      status: 'Overdue', scheduled_date: '2026-06-02', deadline: '2026-06-02',
      assigned_to: techJ, created_by: sup, checklist_template_id: clDaily });

    mk({ title: 'Daily check — ILS Glide Path RWY 08', equipment_id: eGP, inspection_type: 'daily',
      status: 'Awaiting Validation', scheduled_date: '2026-06-03', deadline: '2026-06-03',
      assigned_to: techA, created_by: sup, checklist_template_id: clDaily,
      started_at: '2026-06-03 07:40:00', submitted_at: '2026-06-03 08:55:00',
      result_notes: 'Glide path operating normally, all readings within tolerance.',
      checklist_results: resultsAll(clDaily, 'normal') });

    mk({ title: 'Weekly check — VHF Air-Ground Radio Main', equipment_id: eVHF, inspection_type: 'weekly',
      status: 'Needs Revision', scheduled_date: '2026-06-02', deadline: '2026-06-02',
      assigned_to: techA, created_by: sup, checklist_template_id: clWeekly,
      started_at: '2026-06-02 09:00:00', submitted_at: '2026-06-02 10:10:00',
      revision_notes: 'Generator auto-start result missing a reading and no photo/evidence note. Please add and resubmit.',
      checklist_results: resultsAll(clWeekly, 'normal') });

    const cmp1 = mk({ title: 'Monthly calibration — DVOR/DME COM-50', equipment_id: eVOR, inspection_type: 'monthly',
      status: 'Completed', scheduled_date: '2026-05-12', deadline: '2026-05-12',
      assigned_to: techJ, created_by: sup, checklist_template_id: clMonthly, validated_by: sup,
      started_at: '2026-05-12 08:00:00', submitted_at: '2026-05-12 11:30:00', validated_at: '2026-05-12 14:00:00',
      result_notes: 'Calibration within tolerance. Changeover tested OK.',
      checklist_results: resultsAll(clMonthly, 'normal') });
    const cmp2 = mk({ title: 'Weekly check — Standby Generator 250kVA', equipment_id: eGEN, inspection_type: 'weekly',
      status: 'Completed', scheduled_date: '2026-05-30', deadline: '2026-05-30',
      assigned_to: techA, created_by: sup, checklist_template_id: clWeekly, validated_by: sup,
      started_at: '2026-05-30 09:00:00', submitted_at: '2026-05-30 10:20:00', validated_at: '2026-05-30 12:00:00',
      result_notes: 'Generator started on first attempt. Found minor coolant seepage (see finding).',
      checklist_results: resultsAll(clWeekly, 'abnormal', 'Minor coolant seepage near lower hose clamp.') });
    mk({ title: 'Daily check — ATIS Broadcast Unit', equipment_id: eATIS, inspection_type: 'daily',
      status: 'Completed', scheduled_date: '2026-06-01', deadline: '2026-06-01',
      assigned_to: techF, created_by: sup, checklist_template_id: clDaily, validated_by: sup,
      started_at: '2026-06-01 08:10:00', submitted_at: '2026-06-01 08:45:00', validated_at: '2026-06-01 09:30:00',
      result_notes: 'ATIS broadcasting normally.', checklist_results: resultsAll(clDaily, 'normal') });

    const insF = db.prepare(`INSERT INTO findings
      (inspection_id, equipment_id, description, location, urgency, status, evidence_note,
       recommendation, assigned_to, follow_up_notes, completion_evidence, created_by)
      VALUES (@inspection_id,@equipment_id,@description,@location,@urgency,@status,@evidence_note,
       @recommendation,@assigned_to,@follow_up_notes,@completion_evidence,@created_by)`);
    const fbase = { evidence_note: null, recommendation: null, assigned_to: null,
      follow_up_notes: null, completion_evidence: null };
    const mkF = (o) => insF.run({ ...fbase, ...o });

    db.exec('BEGIN TRANSACTION;');

    mkF({ inspection_id: cmp2, equipment_id: eGEN,
      description: 'Coolant seepage at standby generator lower hose clamp.',
      location: 'Power House', urgency: 'high', status: 'Assigned for Follow-Up',
      evidence_note: 'Photo logged: damp residue under clamp.',
      recommendation: 'Replace hose clamp and top up coolant.',
      assigned_to: techA, follow_up_notes: 'Spare clamp requested from store.', created_by: techA });

    mkF({ inspection_id: cmp1, equipment_id: eVOR,
      description: 'DVOR monitor showing intermittent low-level alarm overnight.',
      location: 'DVOR Site', urgency: 'critical', status: 'Escalated',
      evidence_note: 'Event log entries attached.',
      recommendation: 'Escalate to division head; may affect approach guidance.',
      assigned_to: techJ, follow_up_notes: 'Escalated to management for vendor support decision.', created_by: techJ });

    mkF({ inspection_id: null, equipment_id: eRAD,
      description: 'Radar cooling fan noise increasing — possible bearing wear.',
      location: 'Radar Site', urgency: 'medium', status: 'In Follow-Up',
      recommendation: 'Schedule fan replacement during next maintenance window.',
      assigned_to: techA, follow_up_notes: 'Replacement fan on order.', created_by: sup });

    mkF({ inspection_id: null, equipment_id: eUPS,
      description: 'UPS battery bank temperature slightly high.',
      location: 'Equipment Room', urgency: 'medium', status: 'Resolved',
      recommendation: 'Improve room ventilation.',
      assigned_to: techF, follow_up_notes: 'Extra exhaust fan installed.',
      completion_evidence: 'Temperature back to normal range; verified over 3 days.', created_by: techF });

    mkF({ inspection_id: null, equipment_id: eNDB,
      description: 'NDB antenna guy-wire tension below spec.',
      location: 'NDB Site', urgency: 'low', status: 'Closed',
      recommendation: 'Re-tension guy wires.',
      assigned_to: techJ, follow_up_notes: 'Guy wires re-tensioned.',
      completion_evidence: 'Tension within spec; supervisor verified.', created_by: techJ });

    mkF({ inspection_id: null, equipment_id: eVHF,
      description: 'VHF main transmitter occasional audio crackle on transmit.',
      location: 'Control Tower', urgency: 'high', status: 'Awaiting Review',
      evidence_note: 'Audio sample noted by tower controller.',
      recommendation: 'Inspect connectors and switch to standby for testing.', created_by: techA });

    db.exec('COMMIT;');

    exportDb();
    console.log('Database seeded with demo data.');
  }

  return db;
}

module.exports = { init, prepare };
