// server.js — Express backend for the Airport NavCom E-Inspection System (MVP).
const path = require('path');
const express = require('express');
const database = require('./database');

const app = express();
const PORT = process.env.PORT || 3000;
const TODAY = '2026-06-03'; // demo "current date" so seeded dashboards line up
let db;

app.use(express.json({ limit: '5mb' })); // roomy enough for downscaled base64 abnormal-evidence photos
app.use(express.static(path.join(__dirname, 'public')));

// ---------- helpers ----------
const parseInspection = (row) => row && ({
  ...row,
  checklist_results: row.checklist_results ? JSON.parse(row.checklist_results) : []
});
const parseTemplate = (row) => row && ({ ...row, items: row.items ? JSON.parse(row.items) : [] });

function inspectionWithJoins() {
  return db.prepare(`
    SELECT i.*, e.name AS equipment_name, e.area AS equipment_area, e.location AS equipment_location,
           u.name AS assigned_name, c.name AS creator_name, v.name AS validator_name,
           t.name AS template_name
    FROM inspections i
    LEFT JOIN equipment e ON e.id = i.equipment_id
    LEFT JOIN users u ON u.id = i.assigned_to
    LEFT JOIN users c ON c.id = i.created_by
    LEFT JOIN users v ON v.id = i.validated_by
    LEFT JOIN checklist_templates t ON t.id = i.checklist_template_id
  `);
}
function findingWithJoins() {
  return db.prepare(`
    SELECT f.*, e.name AS equipment_name, e.area AS equipment_area,
           u.name AS assigned_name, c.name AS creator_name
    FROM findings f
    LEFT JOIN equipment e ON e.id = f.equipment_id
    LEFT JOIN users u ON u.id = f.assigned_to
    LEFT JOIN users c ON c.id = f.created_by
  `);
}
const touchInspection = (id) => db.prepare("UPDATE inspections SET updated_at = datetime('now') WHERE id = ?").run(id);
const touchFinding = (id) => db.prepare("UPDATE findings SET updated_at = datetime('now') WHERE id = ?").run(id);

const OPEN_FINDING = ['Created', 'Awaiting Review', 'Accepted', 'Assigned for Follow-Up', 'In Follow-Up', 'Awaiting Verification', 'Escalated'];

// =====================================================================
//  USERS  (master data — administrator)
// =====================================================================
app.get('/api/users', (req, res) => {
  const role = req.query.role;
  let rows;
  if (role) rows = db.prepare('SELECT * FROM users WHERE role = ? ORDER BY name').all(role);
  else rows = db.prepare('SELECT * FROM users ORDER BY role, name').all();
  res.json(rows);
});

app.post('/api/users', (req, res) => {
  const { name, role, department } = req.body || {};
  if (!name || !role) return res.status(400).json({ error: 'name and role are required' });
  const info = db.prepare('INSERT INTO users (name, role, department, active) VALUES (?, ?, ?, 1)')
    .run(name, role, department || 'Telecom & Navigation');
  res.status(201).json(db.prepare('SELECT * FROM users WHERE id = ?').get(info.lastInsertRowid));
});

app.put('/api/users/:id', (req, res) => {
  const cur = db.prepare('SELECT * FROM users WHERE id = ?').get(req.params.id);
  if (!cur) return res.status(404).json({ error: 'user not found' });
  const { name, role, department, active } = req.body || {};
  db.prepare('UPDATE users SET name = ?, role = ?, department = ?, active = ? WHERE id = ?')
    .run(name ?? cur.name, role ?? cur.role, department ?? cur.department,
         active === undefined ? cur.active : (active ? 1 : 0), req.params.id);
  res.json(db.prepare('SELECT * FROM users WHERE id = ?').get(req.params.id));
});

// DELETE = soft-deactivate if the user has inspection history, otherwise remove.
app.delete('/api/users/:id', (req, res) => {
  const id = req.params.id;
  const cur = db.prepare('SELECT * FROM users WHERE id = ?').get(id);
  if (!cur) return res.status(404).json({ error: 'user not found' });
  const used = db.prepare('SELECT COUNT(*) AS n FROM inspections WHERE assigned_to=? OR created_by=? OR validated_by=?').get(id, id, id).n;
  if (used > 0) {
    db.prepare('UPDATE users SET active = 0 WHERE id = ?').run(id);
    return res.json({ deactivated: true, message: 'User has history — deactivated instead of deleted.' });
  }
  db.prepare('DELETE FROM users WHERE id = ?').run(id);
  res.json({ deleted: true });
});

// =====================================================================
//  EQUIPMENT  (master data — administrator)
// =====================================================================
app.get('/api/equipment', (req, res) => {
  const all = req.query.all === '1';
  const rows = all
    ? db.prepare('SELECT * FROM equipment ORDER BY area, name').all()
    : db.prepare('SELECT * FROM equipment WHERE active = 1 ORDER BY area, name').all();
  res.json(rows);
});

app.post('/api/equipment', (req, res) => {
  const { name, type, location, area } = req.body || {};
  if (!name || !type || !area) return res.status(400).json({ error: 'name, type and area are required' });
  const info = db.prepare('INSERT INTO equipment (name, type, location, area, active) VALUES (?, ?, ?, ?, 1)')
    .run(name, type, location || '', area);
  res.status(201).json(db.prepare('SELECT * FROM equipment WHERE id = ?').get(info.lastInsertRowid));
});

app.put('/api/equipment/:id', (req, res) => {
  const cur = db.prepare('SELECT * FROM equipment WHERE id = ?').get(req.params.id);
  if (!cur) return res.status(404).json({ error: 'equipment not found' });
  const { name, type, location, area, active } = req.body || {};
  db.prepare('UPDATE equipment SET name=?, type=?, location=?, area=?, active=? WHERE id=?')
    .run(name ?? cur.name, type ?? cur.type, location ?? cur.location, area ?? cur.area,
         active === undefined ? cur.active : (active ? 1 : 0), req.params.id);
  res.json(db.prepare('SELECT * FROM equipment WHERE id = ?').get(req.params.id));
});

app.delete('/api/equipment/:id', (req, res) => {
  const id = req.params.id;
  const cur = db.prepare('SELECT * FROM equipment WHERE id = ?').get(id);
  if (!cur) return res.status(404).json({ error: 'equipment not found' });
  const used = db.prepare('SELECT COUNT(*) AS n FROM inspections WHERE equipment_id = ?').get(id).n;
  if (used > 0) {
    db.prepare('UPDATE equipment SET active = 0 WHERE id = ?').run(id);
    return res.json({ deactivated: true, message: 'Equipment has inspection history — deactivated instead of deleted.' });
  }
  db.prepare('DELETE FROM equipment WHERE id = ?').run(id);
  res.json({ deleted: true });
});

// =====================================================================
//  CHECKLIST TEMPLATES  (master data — administrator)
// =====================================================================
app.get('/api/checklists', (req, res) => {
  const type = req.query.type;
  let rows;
  if (type) rows = db.prepare('SELECT * FROM checklist_templates WHERE inspection_type = ? AND active = 1 ORDER BY name').all(type);
  else rows = db.prepare('SELECT * FROM checklist_templates ORDER BY inspection_type, name').all();
  res.json(rows.map(parseTemplate));
});

app.post('/api/checklists', (req, res) => {
  const { name, inspection_type, items } = req.body || {};
  if (!name || !inspection_type || !Array.isArray(items) || items.length === 0)
    return res.status(400).json({ error: 'name, inspection_type and a non-empty items array are required' });
  const info = db.prepare('INSERT INTO checklist_templates (name, inspection_type, items, active) VALUES (?, ?, ?, 1)')
    .run(name, inspection_type, JSON.stringify(items));
  res.status(201).json(parseTemplate(db.prepare('SELECT * FROM checklist_templates WHERE id = ?').get(info.lastInsertRowid)));
});

app.put('/api/checklists/:id', (req, res) => {
  const cur = db.prepare('SELECT * FROM checklist_templates WHERE id = ?').get(req.params.id);
  if (!cur) return res.status(404).json({ error: 'template not found' });
  const { name, inspection_type, items, active } = req.body || {};
  db.prepare('UPDATE checklist_templates SET name=?, inspection_type=?, items=?, active=? WHERE id=?')
    .run(name ?? cur.name, inspection_type ?? cur.inspection_type,
         items ? JSON.stringify(items) : cur.items,
         active === undefined ? cur.active : (active ? 1 : 0), req.params.id);
  res.json(parseTemplate(db.prepare('SELECT * FROM checklist_templates WHERE id = ?').get(req.params.id)));
});

app.delete('/api/checklists/:id', (req, res) => {
  const id = req.params.id;
  const cur = db.prepare('SELECT * FROM checklist_templates WHERE id = ?').get(id);
  if (!cur) return res.status(404).json({ error: 'template not found' });
  const used = db.prepare('SELECT COUNT(*) AS n FROM inspections WHERE checklist_template_id = ?').get(id).n;
  if (used > 0) {
    db.prepare('UPDATE checklist_templates SET active = 0 WHERE id = ?').run(id);
    return res.json({ deactivated: true, message: 'Template in use — deactivated instead of deleted.' });
  }
  db.prepare('DELETE FROM checklist_templates WHERE id = ?').run(id);
  res.json({ deleted: true });
});

// =====================================================================
//  INSPECTIONS
// =====================================================================
app.get('/api/inspections', (req, res) => {
  const { assigned_to, status, type } = req.query;
  const where = [];
  const params = [];
  if (assigned_to) { where.push('i.assigned_to = ?'); params.push(assigned_to); }
  if (status) { where.push('i.status = ?'); params.push(status); }
  if (type) { where.push('i.inspection_type = ?'); params.push(type); }
  const sql = inspectionWithJoins().source.replace(/\s+$/, '') +
    (where.length ? ' WHERE ' + where.join(' AND ') : '') +
    ' ORDER BY i.scheduled_date DESC, i.id DESC';
  const rows = db.prepare(sql).all(...params);
  res.json(rows.map(parseInspection));
});

app.get('/api/inspections/:id', (req, res) => {
  const row = db.prepare(inspectionWithJoins().source + ' WHERE i.id = ?').get(req.params.id);
  if (!row) return res.status(404).json({ error: 'inspection not found' });
  res.json(parseInspection(row));
});

app.post('/api/inspections', (req, res) => {
  const { title, equipment_id, inspection_type, scheduled_date, deadline, priority,
          assigned_to, created_by, checklist_template_id } = req.body || {};
  if (!title || !inspection_type || !scheduled_date)
    return res.status(400).json({ error: 'title, inspection_type and scheduled_date are required' });
  const status = assigned_to ? 'Assigned' : 'Scheduled';
  const info = db.prepare(`INSERT INTO inspections
    (title, equipment_id, inspection_type, status, scheduled_date, deadline, priority,
     assigned_to, created_by, checklist_template_id)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`)
    .run(title, equipment_id || null, inspection_type, status, scheduled_date,
         deadline || scheduled_date, priority || 'normal',
         assigned_to || null, created_by || null, checklist_template_id || null);
  res.status(201).json(parseInspection(db.prepare(inspectionWithJoins().source + ' WHERE i.id = ?').get(info.lastInsertRowid)));
});

app.put('/api/inspections/:id', (req, res) => {
  const cur = db.prepare('SELECT * FROM inspections WHERE id = ?').get(req.params.id);
  if (!cur) return res.status(404).json({ error: 'inspection not found' });
  const f = req.body || {};
  db.prepare(`UPDATE inspections SET
      title=?, equipment_id=?, inspection_type=?, scheduled_date=?, deadline=?, priority=?,
      assigned_to=?, checklist_template_id=?, checklist_results=?, result_notes=?, updated_at=datetime('now')
      WHERE id=?`)
    .run(f.title ?? cur.title, f.equipment_id ?? cur.equipment_id, f.inspection_type ?? cur.inspection_type,
         f.scheduled_date ?? cur.scheduled_date, f.deadline ?? cur.deadline, f.priority ?? cur.priority,
         f.assigned_to ?? cur.assigned_to, f.checklist_template_id ?? cur.checklist_template_id,
         f.checklist_results ? JSON.stringify(f.checklist_results) : cur.checklist_results,
         f.result_notes ?? cur.result_notes, req.params.id);
  res.json(parseInspection(db.prepare(inspectionWithJoins().source + ' WHERE i.id = ?').get(req.params.id)));
});

app.delete('/api/inspections/:id', (req, res) => {
  const cur = db.prepare('SELECT * FROM inspections WHERE id = ?').get(req.params.id);
  if (!cur) return res.status(404).json({ error: 'inspection not found' });
  db.prepare('DELETE FROM inspections WHERE id = ?').run(req.params.id);
  res.json({ deleted: true });
});

// Lifecycle transitions — lightly gated by role (PRD §10 "Role That Can Change It").
app.post('/api/inspections/:id/transition', (req, res) => {
  const cur = db.prepare('SELECT * FROM inspections WHERE id = ?').get(req.params.id);
  if (!cur) return res.status(404).json({ error: 'inspection not found' });
  const { action, role } = req.body || {};

  const techActions = ['start', 'submit'];
  const supActions = ['validate', 'return', 'assign', 'reschedule', 'cancel'];
  if (techActions.includes(action) && role !== 'technician')
    return res.status(403).json({ error: `Only a technician can "${action}" an inspection.` });
  if (supActions.includes(action) && role !== 'supervisor')
    return res.status(403).json({ error: `Only a supervisor can "${action}" an inspection.` });

  let sql, params;
  switch (action) {
    case 'start':
      sql = "UPDATE inspections SET status='In Progress', started_at=datetime('now'), updated_at=datetime('now') WHERE id=?";
      params = [req.params.id];
      break;
    case 'submit': {
      const results = req.body.checklist_results;
      const notes = req.body.result_notes ?? cur.result_notes;
      sql = "UPDATE inspections SET status='Awaiting Validation', submitted_at=datetime('now'), checklist_results=?, result_notes=?, updated_at=datetime('now') WHERE id=?";
      params = [results ? JSON.stringify(results) : cur.checklist_results, notes, req.params.id];
      break;
    }
    case 'validate':
      sql = "UPDATE inspections SET status='Completed', validated_by=?, validated_at=datetime('now'), updated_at=datetime('now') WHERE id=?";
      params = [req.body.validated_by || null, req.params.id];
      break;
    case 'return':
      if (!req.body.revision_notes) return res.status(400).json({ error: 'revision_notes are required to return an inspection' });
      sql = "UPDATE inspections SET status='Needs Revision', revision_notes=?, updated_at=datetime('now') WHERE id=?";
      params = [req.body.revision_notes, req.params.id];
      break;
    case 'assign':
      if (!req.body.assigned_to) return res.status(400).json({ error: 'assigned_to is required to assign' });
      sql = "UPDATE inspections SET status='Assigned', assigned_to=?, updated_at=datetime('now') WHERE id=?";
      params = [req.body.assigned_to, req.params.id];
      break;
    case 'reschedule':
      if (!req.body.scheduled_date) return res.status(400).json({ error: 'scheduled_date is required to reschedule' });
      sql = "UPDATE inspections SET status='Rescheduled', scheduled_date=?, deadline=?, updated_at=datetime('now') WHERE id=?";
      params = [req.body.scheduled_date, req.body.deadline || req.body.scheduled_date, req.params.id];
      break;
    case 'cancel':
      sql = "UPDATE inspections SET status='Cancelled', updated_at=datetime('now') WHERE id=?";
      params = [req.params.id];
      break;
    default:
      return res.status(400).json({ error: 'unknown action' });
  }
  db.prepare(sql).run(...params);
  res.json(parseInspection(db.prepare(inspectionWithJoins().source + ' WHERE i.id = ?').get(req.params.id)));
});

// =====================================================================
//  FINDINGS  (status spans both finding + follow-up lifecycle)
// =====================================================================
app.get('/api/findings', (req, res) => {
  const { status, urgency, open } = req.query;
  const where = [];
  const params = [];
  if (status) { where.push('f.status = ?'); params.push(status); }
  if (urgency) { where.push('f.urgency = ?'); params.push(urgency); }
  if (open === '1') { where.push(`f.status IN (${OPEN_FINDING.map(() => '?').join(',')})`); params.push(...OPEN_FINDING); }
  const sql = findingWithJoins().source + (where.length ? ' WHERE ' + where.join(' AND ') : '') +
    ' ORDER BY f.updated_at DESC, f.id DESC';
  res.json(db.prepare(sql).all(...params));
});

app.post('/api/findings', (req, res) => {
  const { inspection_id, equipment_id, description, location, urgency, evidence_note, recommendation, created_by } = req.body || {};
  if (!description) return res.status(400).json({ error: 'description is required' });
  const info = db.prepare(`INSERT INTO findings
    (inspection_id, equipment_id, description, location, urgency, status, evidence_note, recommendation, created_by)
    VALUES (?, ?, ?, ?, ?, 'Created', ?, ?, ?)`)
    .run(inspection_id || null, equipment_id || null, description, location || '',
         urgency || 'medium', evidence_note || '', recommendation || '', created_by || null);
  res.status(201).json(db.prepare(findingWithJoins().source + ' WHERE f.id = ?').get(info.lastInsertRowid));
});

app.put('/api/findings/:id', (req, res) => {
  const cur = db.prepare('SELECT * FROM findings WHERE id = ?').get(req.params.id);
  if (!cur) return res.status(404).json({ error: 'finding not found' });
  const f = req.body || {};
  db.prepare(`UPDATE findings SET description=?, location=?, urgency=?, evidence_note=?, recommendation=?,
      follow_up_notes=?, completion_evidence=?, updated_at=datetime('now') WHERE id=?`)
    .run(f.description ?? cur.description, f.location ?? cur.location, f.urgency ?? cur.urgency,
         f.evidence_note ?? cur.evidence_note, f.recommendation ?? cur.recommendation,
         f.follow_up_notes ?? cur.follow_up_notes, f.completion_evidence ?? cur.completion_evidence, req.params.id);
  res.json(db.prepare(findingWithJoins().source + ' WHERE f.id = ?').get(req.params.id));
});

// Finding / follow-up transitions.
app.post('/api/findings/:id/transition', (req, res) => {
  const cur = db.prepare('SELECT * FROM findings WHERE id = ?').get(req.params.id);
  if (!cur) return res.status(404).json({ error: 'finding not found' });
  const { action, role } = req.body || {};

  const supActions = ['accept', 'assign-followup', 'resolve', 'close', 'escalate', 'return'];
  const techActions = ['submit-followup'];
  if (supActions.includes(action) && role !== 'supervisor')
    return res.status(403).json({ error: `Only a supervisor can "${action}" a finding.` });
  if (techActions.includes(action) && role !== 'technician')
    return res.status(403).json({ error: `Only a technician can "${action}".` });

  const statusMap = {
    accept: 'Accepted',
    'assign-followup': 'Assigned for Follow-Up',
    'submit-followup': 'Awaiting Verification',
    resolve: 'Resolved',
    close: 'Closed',
    escalate: 'Escalated',
    return: 'Assigned for Follow-Up'
  };
  const next = statusMap[action];
  if (!next) return res.status(400).json({ error: 'unknown action' });

  if (action === 'assign-followup' && req.body.assigned_to)
    db.prepare('UPDATE findings SET assigned_to = ? WHERE id = ?').run(req.body.assigned_to, req.params.id);
  if (action === 'submit-followup' && req.body.completion_evidence !== undefined)
    db.prepare('UPDATE findings SET completion_evidence = ? WHERE id = ?').run(req.body.completion_evidence, req.params.id);
  if (req.body.follow_up_notes !== undefined)
    db.prepare('UPDATE findings SET follow_up_notes = ? WHERE id = ?').run(req.body.follow_up_notes, req.params.id);

  db.prepare("UPDATE findings SET status = ?, updated_at = datetime('now') WHERE id = ?").run(next, req.params.id);
  res.json(db.prepare(findingWithJoins().source + ' WHERE f.id = ?').get(req.params.id));
});

// =====================================================================
//  DASHBOARDS  (role-aware)
// =====================================================================
app.get('/api/dashboard', (req, res) => {
  const { role, user } = req.query;
  const allInspections = db.prepare(inspectionWithJoins().source + ' ORDER BY i.scheduled_date').all().map(parseInspection);
  const allFindings = db.prepare(findingWithJoins().source).all();

  if (role === 'technician') {
    const mine = allInspections.filter(i => String(i.assigned_to) === String(user));
    const active = mine.filter(i => !['Completed', 'Cancelled'].includes(i.status));
    res.json({
      cards: {
        today: active.filter(i => i.scheduled_date === TODAY).length,
        upcoming: active.filter(i => i.scheduled_date > TODAY).length,
        overdue: mine.filter(i => i.status === 'Overdue' || (i.scheduled_date < TODAY && !['Completed', 'Cancelled'].includes(i.status))).length,
        needsRevision: mine.filter(i => i.status === 'Needs Revision').length,
        inProgress: mine.filter(i => i.status === 'In Progress').length,
        followUps: allFindings.filter(f => String(f.assigned_to) === String(user) && ['Assigned for Follow-Up', 'In Follow-Up'].includes(f.status)).length
      },
      today: active.filter(i => i.scheduled_date === TODAY),
      upcoming: active.filter(i => i.scheduled_date > TODAY),
      overdue: mine.filter(i => i.status === 'Overdue' || (i.scheduled_date < TODAY && !['Completed', 'Cancelled'].includes(i.status))),
      needsRevision: mine.filter(i => i.status === 'Needs Revision'),
      followUps: allFindings.filter(f => String(f.assigned_to) === String(user) && ['Assigned for Follow-Up', 'In Follow-Up'].includes(f.status)),
      recent: mine.filter(i => i.status === 'Completed').slice(0, 5)
    });
    return;
  }

  if (role === 'supervisor') {
    const open = allFindings.filter(f => OPEN_FINDING.includes(f.status));
    const statusCounts = {};
    allInspections.forEach(i => { statusCounts[i.status] = (statusCounts[i.status] || 0) + 1; });
    // performance by technician
    const techs = db.prepare("SELECT * FROM users WHERE role='technician' AND active=1").all();
    const byTech = techs.map(t => {
      const list = allInspections.filter(i => i.assigned_to === t.id);
      return { name: t.name, total: list.length, completed: list.filter(i => i.status === 'Completed').length,
               overdue: list.filter(i => i.status === 'Overdue').length };
    });
    res.json({
      cards: {
        scheduled: allInspections.filter(i => ['Scheduled', 'Assigned'].includes(i.status)).length,
        inProgress: statusCounts['In Progress'] || 0,
        completed: statusCounts['Completed'] || 0,
        overdue: statusCounts['Overdue'] || 0,
        awaitingValidation: statusCounts['Awaiting Validation'] || 0,
        needsRevision: statusCounts['Needs Revision'] || 0,
        openFindings: open.length,
        criticalFindings: open.filter(f => f.urgency === 'critical').length
      },
      statusCounts,
      validationQueue: allInspections.filter(i => i.status === 'Awaiting Validation'),
      openFindings: open,
      byTech
    });
    return;
  }

  if (role === 'management') {
    const total = allInspections.length;
    const completed = allInspections.filter(i => i.status === 'Completed').length;
    const open = allFindings.filter(f => OPEN_FINDING.includes(f.status));
    // findings by area
    const areaCounts = {};
    allFindings.forEach(f => { const a = f.equipment_area || 'Unknown'; areaCounts[a] = (areaCounts[a] || 0) + 1; });
    // follow-up status distribution
    const followCounts = {};
    allFindings.forEach(f => { followCounts[f.status] = (followCounts[f.status] || 0) + 1; });
    res.json({
      cards: {
        totalInspections: total,
        completed,
        compliance: total ? Math.round((completed / total) * 100) : 0,
        overdue: allInspections.filter(i => i.status === 'Overdue').length,
        openFindings: open.length,
        criticalFindings: open.filter(f => f.urgency === 'critical').length,
        escalated: allFindings.filter(f => f.status === 'Escalated').length,
        resolvedFollowUps: allFindings.filter(f => ['Resolved', 'Closed'].includes(f.status)).length
      },
      byType: {
        daily: allInspections.filter(i => i.inspection_type === 'daily').length,
        weekly: allInspections.filter(i => i.inspection_type === 'weekly').length,
        monthly: allInspections.filter(i => i.inspection_type === 'monthly').length
      },
      areaCounts,
      followCounts,
      criticalFindings: open.filter(f => f.urgency === 'critical' || f.status === 'Escalated')
    });
    return;
  }

  if (role === 'administrator') {
    res.json({
      cards: {
        activeUsers: db.prepare('SELECT COUNT(*) AS n FROM users WHERE active=1').get().n,
        inactiveUsers: db.prepare('SELECT COUNT(*) AS n FROM users WHERE active=0').get().n,
        equipment: db.prepare('SELECT COUNT(*) AS n FROM equipment WHERE active=1').get().n,
        inactiveEquipment: db.prepare('SELECT COUNT(*) AS n FROM equipment WHERE active=0').get().n,
        checklists: db.prepare('SELECT COUNT(*) AS n FROM checklist_templates WHERE active=1').get().n,
        areas: db.prepare('SELECT COUNT(DISTINCT area) AS n FROM equipment').get().n
      },
      equipmentByType: db.prepare('SELECT type, COUNT(*) AS n FROM equipment WHERE active=1 GROUP BY type').all()
    });
    return;
  }

  res.status(400).json({ error: 'unknown role' });
});

// =====================================================================
//  REPORTS
// =====================================================================
app.get('/api/reports', (req, res) => {
  const type = req.query.type || 'daily'; // daily | weekly | monthly
  const list = db.prepare(inspectionWithJoins().source + ' WHERE i.inspection_type = ? ORDER BY i.scheduled_date DESC')
    .all(type).map(parseInspection);
  const statusCounts = {};
  list.forEach(i => { statusCounts[i.status] = (statusCounts[i.status] || 0) + 1; });
  const findings = db.prepare(findingWithJoins().source + ' ORDER BY f.urgency DESC').all();
  res.json({
    type,
    total: list.length,
    statusCounts,
    completed: list.filter(i => i.status === 'Completed').length,
    overdue: list.filter(i => i.status === 'Overdue').length,
    inspections: list,
    findings
  });
});

app.get('/api/meta', (req, res) => res.json({ today: TODAY }));

(async () => {
  try {
    await database.init();
    db = database;
    app.listen(PORT, () => {
      console.log(`Airport NavCom E-Inspection System running at http://localhost:${PORT}`);
    });
  } catch (err) {
    console.error('Failed to initialize database:', err);
    process.exit(1);
  }
})();
