/**
 * Finance Controller
 *
 * Phase 0 (stabilise): payroll was previously duplicated here with a simpler
 * (no-tax, no-deductions) implementation. Per the locked decision in
 * `.harness/plans/hr-finance-fiscalisation.md` §6, HR is the single source of
 * truth for payroll data, and Finance "imports" from it. So this controller now
 * proxies `/api/finance/payroll/*` requests to `hr.controller.js`.
 *
 * Path translation (Finance client → HR router):
 *   GET  /finance/payroll/runs              → GET  /hr/payroll-runs
 *   POST /finance/payroll/runs              → POST /hr/payroll-runs
 *   POST /finance/payroll/runs/:id/...      → POST /hr/payroll-runs/:id/...
 *   GET  /finance/payroll/payslips          → GET  /hr/payslips
 *
 * Future Phase 2 endpoints (invoices, expenses, suppliers, bank accounts,
 * chart of accounts) will be added below the proxy.
 */

const express = require('express');
const router = express.Router();
const path = require('path');
const Database = require('better-sqlite3');
const { v4: uuidv4 } = require('uuid');
const hrRouter = require('./hr.controller');
const { outstandingForFee } = require('../lib/outstanding');

const db = new Database(process.env.DB_PATH || path.join(__dirname, '../../data/school.db'));
db.pragma('foreign_keys = ON');

const auth = (req, res, next) => {
  const token = req.headers.authorization?.split(' ')[1];
  if (!token) return res.status(401).json({ error: 'No token provided' });
  try {
    const jwt = require('jsonwebtoken');
    const { jwtSecret: JWT_SECRET } = require('../config');
    req.user = jwt.verify(token, JWT_SECRET);
    next();
  } catch (err) {
    res.status(401).json({ error: 'Invalid token' });
  }
};

// Finance Access — includes bursar + accountant (the people who consume the
// Finance portal). Principals and admins are also allowed for read-only access.
// `hr` is included so HR staff can view finance dashboard tiles (B2 spec).
const financeAccess = (req, res, next) => {
  const allowedRoles = ['school_admin', 'systems_admin', 'principal', 'bursar', 'accountant', 'hr'];
  if (!allowedRoles.includes(req.user.role)) {
    return res.status(403).json({ error: 'Finance, Bursar, Accountant, HR, Principal or Admin access required' });
  }
  next();
};

// Rewrite Finance's /payroll/* URLs into HR's payroll-runs / payslips shape,
// then hand the request to HR's router. All query strings are preserved
// because Express keeps them inside req.url.
/**
 * @openapi
 * /api/finance/payroll/runs:
 *   get:
 *     summary: List payroll runs (Finance proxy)
 *     description: |
 *       Forwards to `GET /api/hr/payroll-runs`. Documented at HR.
 *       Authentication and `financeAccess` are enforced before the
 *       request is rewritten.
 *     tags:
 *       - Finance
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200: { description: Payroll run list }
 *       401: { description: Unauthorized }
 *       403: { description: Finance, Bursar, Accountant, HR, Principal or Admin access required }
 *   post:
 *     summary: Create payroll run (Finance proxy)
 *     description: |
 *       Forwards to `POST /api/hr/payroll-runs`. Documented at HR.
 *     tags:
 *       - Finance
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200: { description: Created run identifiers }
 *       401: { description: Unauthorized }
 *       403: { description: Finance, Bursar, Accountant, HR, Principal or Admin access required }
 * /api/finance/payroll/runs/{id}/calculate:
 *   post:
 *     summary: Calculate payroll run (Finance proxy)
 *     description: |
 *       Forwards to `POST /api/hr/payroll-runs/{id}/calculate`.
 *       Documented at HR.
 *     tags:
 *       - Finance
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200: { description: Number of payslips generated }
 *       404: { description: Payroll run not found }
 *       401: { description: Unauthorized }
 *       403: { description: Finance, Bursar, Accountant, HR, Principal or Admin access required }
 * /api/finance/payroll/runs/{id}/pay:
 *   post:
 *     summary: Mark payroll run paid (Finance proxy)
 *     description: |
 *       Forwards to `POST /api/hr/payroll-runs/{id}/pay`.
 *       Documented at HR.
 *     tags:
 *       - Finance
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200: { description: Marked paid }
 *       404: { description: Payroll run not found }
 *       401: { description: Unauthorized }
 *       403: { description: Finance, Bursar, Accountant, HR, Principal or Admin access required }
 * /api/finance/payroll/runs/{id}/cancel:
 *   post:
 *     summary: Cancel payroll run (Finance proxy)
 *     description: |
 *       Forwards to `POST /api/hr/payroll-runs/{id}/cancel`.
 *       Documented at HR.
 *     tags:
 *       - Finance
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200: { description: Cancelled }
 *       400: { description: Cannot cancel a paid payroll run }
 *       404: { description: Payroll run not found }
 *       401: { description: Unauthorized }
 *       403: { description: Finance, Bursar, Accountant, HR, Principal or Admin access required }
 * /api/finance/payroll/payslips:
 *   get:
 *     summary: List payslips (Finance proxy)
 *     description: |
 *       Forwards to `GET /api/hr/payslips`. Documented at HR.
 *     tags:
 *       - Finance
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200: { description: Payslip list }
 *       401: { description: Unauthorized }
 * /api/finance/payroll/payslips/{id}:
 *   get:
 *     summary: Get a single payslip (Finance proxy)
 *     description: |
 *       Forwards to `GET /api/hr/payslips/{id}`. Documented at HR.
 *     tags:
 *       - Finance
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200: { description: Payslip detail }
 *       400: { description: Invalid payslip id }
 *       403: { description: You may only view your own payslips }
 *       404: { description: Payslip not found }
 *       401: { description: Unauthorized }
 */
const rewriteAndProxy = (req, _res, next) => {
  const incoming = req.url; // e.g. '/runs', '/runs/1/calculate', '/payslips?foo=bar'
  if (incoming.startsWith('/runs')) {
    // /runs          -> /payroll-runs
    // /runs/1/...    -> /payroll-runs/1/...
    req.url = '/payroll-runs' + incoming.slice(5);
  } else if (incoming.startsWith('/payslips')) {
    // /payslips[?...]-> /payslips[?...]
    req.url = '/payslips' + incoming.slice(9);
  }
  // Other paths under /payroll fall through and 404 inside HR's router —
  // intentional: Finance URLs are a closed set defined above.
  hrRouter(req, _res, next);
};
router.use('/payroll', auth, financeAccess, rewriteAndProxy);

// ============================================================
// Phase 1 — Backend gaps B2, B3–B6, B8
// Owner: backend-expert (pre-step)
// Plan: .harness/plans/hr-finance-fiscalisation.md §3a
//
// Notes:
// - All writes set sync_status='pending' so SyncEngine picks them up.
// - Soft-delete via is_deleted=1.
// - Finance dashboard collected_mtd uses payments.payment_date (NOT
//   paid_at) — the payments table schema does not have a paid_at column.
//   payments.controller.js:251 references paid_at but the column doesn't
//   exist in init.js — flagged as deviation.
// ============================================================

// ============ FINANCE DASHBOARD (B2) ============
// Aggregates:
//   collected_mtd   — SUM(payments.amount) for completed payments since
//                     start of current month (local time).
//   outstanding_fees — sum of each active fee's canonical outstanding balance
//                     (amount + fine - discount - paid, clamped at zero).
//   payroll_last_run — same shape as B1, latest payroll_runs row.
//   expenses_mtd     — SUM(expenses.amount) since start of current month.
//   pending_expenses — COUNT(expenses WHERE status='pending' AND is_deleted=0).
/**
 * @openapi
 * /api/finance/dashboard/summary:
 *   get:
 *     summary: Finance dashboard summary
 *     description: |
 *       Aggregates five tiles for the Finance landing page: fees
 *       collected month-to-date (sums `payments.amount` joined to
 *       `student_fees.status='paid'`), outstanding fees (sum of each
 *       pending/partial fee's clamped `amount + fine - discount - paid` balance), the
 *       most recent payroll run, total expenses this month, and
 *       the count of pending expenses. Open to finance, bursar,
 *       accountant, HR, principal and admin roles.
 *     tags:
 *       - Finance
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Finance dashboard tiles
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 collected_mtd: { type: number }
 *                 outstanding_fees: { type: number }
 *                 payroll_last_run:
 *                   type: object
 *                   nullable: true
 *                   properties:
 *                     id: { type: integer }
 *                     period: { type: string }
 *                     status: { type: string }
 *                 expenses_mtd: { type: number }
 *                 pending_expenses: { type: integer }
 *       401: { description: Unauthorized }
 *       403: { description: Finance, Bursar, Accountant, HR, Principal or Admin access required }
 *       500: { description: Server error }
 */
router.get('/dashboard/summary', auth, financeAccess, (_req, res) => {
  try {
    // collected_mtd — sum of payment amounts this month, for payments whose
    // associated student_fees row is fully paid. NOTE: payments table has no
    // `status` column (see init.js line 187) so we can't filter payments.status
    // directly; instead we join student_fees.status='paid'. This is also why
    // payments.controller.js:250's reference to payments.paid_at errors — both
    // are pre-existing inconsistencies flagged for Phase 2.
    const collectedRow = db.prepare(
      `SELECT COALESCE(SUM(p.amount), 0) AS total
       FROM payments p
       JOIN student_fees sf ON p.student_fee_id = sf.id
       WHERE sf.status = 'paid'
         AND date(p.payment_date, 'localtime') >= date('now', 'localtime', 'start of month')`
    ).get();

    const outstandingFees = db.prepare(
      `SELECT amount, fine_amount, discount_amount, paid_amount
       FROM student_fees
       WHERE is_deleted = 0
         AND status IN ('pending', 'partial')`
    ).all();
    const outstanding = outstandingFees.reduce(
      (sum, fee) => sum + outstandingForFee(fee).outstanding,
      0,
    );

    const lastRunRow = db.prepare(
      `SELECT id, period_month, period_year, status
       FROM payroll_runs
       WHERE is_deleted = 0
       ORDER BY period_year DESC, period_month DESC, id DESC
       LIMIT 1`
    ).get();
    const payroll_last_run = lastRunRow
      ? {
          id: lastRunRow.id,
          period: `${lastRunRow.period_year}-${String(lastRunRow.period_month).padStart(2, '0')}`,
          status: lastRunRow.status,
        }
      : null;

    const expensesMtdRow = db.prepare(
      `SELECT COALESCE(SUM(amount), 0) AS total
       FROM expenses
       WHERE is_deleted = 0
         AND date(expense_date, 'localtime') >= date('now', 'localtime', 'start of month')`
    ).get();

    const pendingExpensesRow = db.prepare(
      `SELECT COUNT(*) AS n
       FROM expenses
       WHERE is_deleted = 0 AND status = 'pending'`
    ).get();

    // §10c — additional Finance dashboard tiles. kept lightweight so
    // the existing dashboard consumer doesn't need to change shape.
    const overdueInvoicesRow = db.prepare(`
      SELECT COUNT(*) AS n FROM invoices
      WHERE is_deleted = 0
        AND status NOT IN ('paid', 'cancelled', 'draft')
        AND due_date IS NOT NULL
        AND date(due_date, 'localtime') < date('now', 'localtime')
    `).get();

    const openInvoicesRow = db.prepare(`
      SELECT COUNT(*) AS n FROM invoices
      WHERE is_deleted = 0 AND status IN ('issued', 'partial', 'overdue')
    `).get();

    const upcomingTripsRow = db.prepare(`
      SELECT COUNT(*) AS n FROM trips
      WHERE is_deleted = 0
        AND status = 'published'
        AND departure_date IS NOT NULL
        AND date(departure_date, 'localtime') >= date('now', 'localtime')
        AND date(departure_date, 'localtime') <= date('now', 'localtime', '+30 days')
    `).get();

    const pendingReconRow = db.prepare(`
      SELECT COUNT(*) AS n FROM bank_reconciliations
      WHERE is_deleted = 0 AND status = 'discrepancy'
    `).get();

    const installmentsDueRow = db.prepare(`
      SELECT COUNT(*) AS n FROM student_fees
      WHERE is_deleted = 0
        AND status IN ('pending', 'partial')
        AND due_date IS NOT NULL
        AND date(due_date, 'localtime') >= date('now', 'localtime')
        AND date(due_date, 'localtime') <= date('now', 'localtime', '+14 days')
    `).get();

    const studentGroupsRow = db.prepare(`
      SELECT COUNT(*) AS n FROM student_groups WHERE is_deleted = 0
    `).get();

    res.json({
      collected_mtd: Math.round((collectedRow.total || 0) * 100) / 100,
      outstanding_fees: Math.round(outstanding * 100) / 100,
      payroll_last_run,
      expenses_mtd: Math.round((expensesMtdRow.total || 0) * 100) / 100,
      pending_expenses: pendingExpensesRow.n || 0,
      // §10c — new tiles
      overdue_invoices: overdueInvoicesRow.n || 0,
      open_invoices: openInvoicesRow.n || 0,
      upcoming_trips_30d: upcomingTripsRow.n || 0,
      pending_reconciliations: pendingReconRow.n || 0,
      installments_due_14d: installmentsDueRow.n || 0,
      student_groups_count: studentGroupsRow.n || 0,
    });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// ============ EXPENSES (B3–B6, B8) ============
// Mounted under /api/finance/expenses via sub-router. RBAC: financeAccess
// (bursar / accountant / hr / principal / admin).

const expensesRouter = express.Router();

const VALID_CATEGORIES = ['utilities', 'supplies', 'maintenance', 'transport', 'salaries', 'other'];

// B8 — categories dropdown helper (optional but implemented for completeness).
/**
 * @openapi
 * /api/finance/expenses/categories:
 *   get:
 *     summary: List expense categories
 *     description: |
 *       Returns the closed enum of valid `expenses.category`
 *       values used by the form. Open to finance, bursar,
 *       accountant, HR, principal and admin roles.
 *     tags:
 *       - Finance
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Category list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: string }
 *               example: ["utilities", "supplies", "maintenance", "transport", "salaries", "other"]
 *       401: { description: Unauthorized }
 *       403: { description: Finance, Bursar, Accountant, HR, Principal or Admin access required }
 */
expensesRouter.get('/categories', auth, financeAccess, (_req, res) => {
  res.json(VALID_CATEGORIES);
});

// B3 — list with filters and pagination.
/**
 * @openapi
 * /api/finance/expenses:
 *   get:
 *     summary: List expenses
 *     description: |
 *       Returns active expenses joined with optional filters
 *       (`status`, `category`, `date_from`, `date_to`), ordered
 *       by `expense_date` descending. `limit` defaults to 50
 *       and is capped at 500; `offset` defaults to 0. Response
 *       includes `pagination` (total / limit / offset / returned).
 *       Open to finance, bursar, accountant, HR, principal and
 *       admin roles.
 *     tags:
 *       - Finance
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: status
 *         schema: { type: string }
 *       - in: query
 *         name: category
 *         schema: { type: string }
 *       - in: query
 *         name: date_from
 *         schema: { type: string, format: date }
 *       - in: query
 *         name: date_to
 *         schema: { type: string, format: date }
 *       - in: query
 *         name: limit
 *         schema: { type: integer, default: 50, maximum: 500 }
 *       - in: query
 *         name: offset
 *         schema: { type: integer, default: 0 }
 *     responses:
 *       200:
 *         description: Expense list with pagination
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 expenses:
 *                   type: array
 *                   items: { type: object }
 *                 pagination: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Finance, Bursar, Accountant, HR, Principal or Admin access required }
 *       500: { description: Server error }
 */
expensesRouter.get('/', auth, financeAccess, (req, res) => {
  const { status, category, date_from, date_to, limit, offset } = req.query;
  const where = ['is_deleted = 0'];
  const params = [];

  if (status) { where.push('status = ?'); params.push(status); }
  if (category) { where.push('category = ?'); params.push(category); }
  if (date_from) { where.push('date(expense_date, \'localtime\') >= ?'); params.push(date_from); }
  if (date_to) { where.push('date(expense_date, \'localtime\') <= ?'); params.push(date_to); }

  const lim = Math.min(parseInt(limit, 10) || 50, 500);
  const off = parseInt(offset, 10) || 0;

  const rows = db.prepare(
    `SELECT * FROM expenses
     WHERE ${where.join(' AND ')}
     ORDER BY expense_date DESC, id DESC
     LIMIT ? OFFSET ?`
  ).all(...params, lim, off);

  const total = db.prepare(
    `SELECT COUNT(*) AS n FROM expenses WHERE ${where.join(' AND ')}`
  ).get(...params).n;

  res.json({
    expenses: rows,
    pagination: { total, limit: lim, offset: off, returned: rows.length },
  });
});

// B4 — create. Validates amount > 0 and expense_date <= today.
/**
 * @openapi
 * /api/finance/expenses:
 *   post:
 *     summary: Create expense
 *     description: |
 *       Inserts a new expense row. `category` must be one of
 *       the values returned by `/expenses/categories`; `amount`
 *       must be a finite number greater than zero; `expense_date`
 *       cannot be in the future. `created_by` is taken from the
 *       JWT. Open to finance, bursar, accountant, HR, principal
 *       and admin roles.
 *     tags:
 *       - Finance
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - category
 *               - description
 *               - amount
 *             properties:
 *               category:
 *                 type: string
 *                 description: "One of: utilities, supplies, maintenance, transport, salaries, other"
 *               subcategory: { type: string }
 *               description: { type: string }
 *               amount: { type: number, exclusiveMinimum: 0 }
 *               expense_date: { type: string, format: date }
 *               vendor: { type: string }
 *               invoice_number: { type: string }
 *               payment_method: { type: string }
 *               status: { type: string, default: pending }
 *               receipt_image: { type: string }
 *     responses:
 *       201:
 *         description: Created expense
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: Validation error (bad category, amount, or future date) }
 *       401: { description: Unauthorized }
 *       403: { description: Finance, Bursar, Accountant, HR, Principal or Admin access required }
 *       500: { description: Server error }
 */
expensesRouter.post('/', auth, financeAccess, (req, res) => {
  try {
    const {
      category, subcategory, description, amount, expense_date,
      vendor, invoice_number, payment_method, status, receipt_image,
    } = req.body || {};

    if (!category || !VALID_CATEGORIES.includes(category)) {
      return res.status(400).json({ error: `category must be one of ${VALID_CATEGORIES.join(',')}` });
    }
    if (!description || typeof description !== 'string') {
      return res.status(400).json({ error: 'description is required' });
    }
    const amt = Number(amount);
    if (!Number.isFinite(amt) || amt <= 0) {
      return res.status(400).json({ error: 'amount must be > 0' });
    }
    const today = new Date().toISOString().slice(0, 10);
    if (expense_date && expense_date > today) {
      return res.status(400).json({ error: 'expense_date cannot be in the future' });
    }

    const uid = uuidv4();
    const result = db.prepare(`
      INSERT INTO expenses (
        uid, category, subcategory, description, amount, expense_date,
        vendor, invoice_number, payment_method, status, receipt_image,
        created_by, sync_status
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending')
    `).run(
      uid,
      category,
      subcategory || null,
      description,
      amt,
      expense_date || today,
      vendor || null,
      invoice_number || null,
      payment_method || null,
      status || 'pending',
      receipt_image || null,
      req.user.id
    );

    const row = db.prepare('SELECT * FROM expenses WHERE id = ?').get(result.lastInsertRowid);
    res.status(201).json(row);
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// B5 — update. Partial: only fields present in body are written.
/**
 * @openapi
 * /api/finance/expenses/{id}:
 *   patch:
 *     summary: Update expense (partial)
 *     description: |
 *       Updates a subset of fields on an existing expense by
 *       numeric `id`. Only fields present in the body are written.
 *       `category` and `amount` re-validate against the same
 *       rules as create. Returns 400 if no editable fields are
 *       supplied. Open to finance, bursar, accountant, HR,
 *       principal and admin roles.
 *     tags:
 *       - Finance
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *         description: Numeric expense ID
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               category: { type: string }
 *               subcategory: { type: string }
 *               description: { type: string }
 *               amount: { type: number, exclusiveMinimum: 0 }
 *               expense_date: { type: string, format: date }
 *               vendor: { type: string }
 *               invoice_number: { type: string }
 *               payment_method: { type: string }
 *               status: { type: string }
 *               receipt_image: { type: string }
 *     responses:
 *       200:
 *         description: Updated expense
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: Validation error or no editable fields supplied }
 *       401: { description: Unauthorized }
 *       403: { description: Finance, Bursar, Accountant, HR, Principal or Admin access required }
 *       404: { description: Expense not found }
 *       500: { description: Server error }
 */
expensesRouter.patch('/:id', auth, financeAccess, (req, res) => {
  try {
    const id = parseInt(req.params.id, 10);
    if (Number.isNaN(id)) return res.status(400).json({ error: 'Invalid id' });

    const existing = db.prepare('SELECT * FROM expenses WHERE id = ? AND is_deleted = 0').get(id);
    if (!existing) return res.status(404).json({ error: 'Expense not found' });

    const editable = [
      'category', 'subcategory', 'description', 'amount', 'expense_date',
      'vendor', 'invoice_number', 'payment_method', 'status', 'receipt_image',
    ];

    const updates = [];
    const params = [];
    for (const field of editable) {
      if (Object.prototype.hasOwnProperty.call(req.body || {}, field)) {
        let val = req.body[field];
        if (field === 'category') {
          if (!VALID_CATEGORIES.includes(val)) {
            return res.status(400).json({ error: `category must be one of ${VALID_CATEGORIES.join(',')}` });
          }
        }
        if (field === 'amount') {
          const amt = Number(val);
          if (!Number.isFinite(amt) || amt <= 0) {
            return res.status(400).json({ error: 'amount must be > 0' });
          }
          val = amt;
        }
        if (field === 'expense_date' && val) {
          const today = new Date().toISOString().slice(0, 10);
          if (val > today) {
            return res.status(400).json({ error: 'expense_date cannot be in the future' });
          }
        }
        updates.push(`${field} = ?`);
        params.push(val);
      }
    }

    if (updates.length === 0) {
      return res.status(400).json({ error: 'No editable fields supplied' });
    }

    updates.push(`updated_at = datetime('now', 'localtime')`);
    updates.push(`sync_status = 'pending'`);
    params.push(id);

    db.prepare(`UPDATE expenses SET ${updates.join(', ')} WHERE id = ?`).run(...params);
    const row = db.prepare('SELECT * FROM expenses WHERE id = ?').get(id);
    res.json(row);
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// B6 — soft-delete.
/**
 * @openapi
 * /api/finance/expenses/{id}:
 *   delete:
 *     summary: Soft-delete expense
 *     description: |
 *       Sets `is_deleted = 1` on an expense by numeric `id`,
 *       clears `last_synced_at`, and marks `sync_status = 'pending'`.
 *       Open to finance, bursar, accountant, HR, principal and
 *       admin roles.
 *     tags:
 *       - Finance
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *         description: Numeric expense ID
 *     responses:
 *       200:
 *         description: Deleted
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 success: { type: boolean }
 *       400: { description: Invalid id }
 *       401: { description: Unauthorized }
 *       403: { description: Finance, Bursar, Accountant, HR, Principal or Admin access required }
 *       404: { description: Expense not found }
 *       500: { description: Server error }
 */
expensesRouter.delete('/:id', auth, financeAccess, (req, res) => {
  try {
    const id = parseInt(req.params.id, 10);
    if (Number.isNaN(id)) return res.status(400).json({ error: 'Invalid id' });

    const existing = db.prepare('SELECT id FROM expenses WHERE id = ? AND is_deleted = 0').get(id);
    if (!existing) return res.status(404).json({ error: 'Expense not found' });

    db.prepare(`
      UPDATE expenses
      SET is_deleted = 1,
          updated_at = datetime('now', 'localtime'),
          sync_status = 'pending',
          last_synced_at = NULL
      WHERE id = ?
    `).run(id);

    res.json({ success: true });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// ============ Sprint 1b — B9, B10, B11 ============
// Owner: backend-expert
// Plan: .harness/plans/hr-finance-fiscalisation.md §3b

// B9 — approve state transition. Validates status='pending' (409 otherwise).
// Records approved_by + approved_at. Sets sync_status='pending'.
expensesRouter.put('/:id/approve', auth, financeAccess, (req, res) => {
  try {
    const id = parseInt(req.params.id, 10);
    if (Number.isNaN(id)) return res.status(400).json({ error: 'Invalid id' });

    const existing = db.prepare(
      'SELECT id, status FROM expenses WHERE id = ? AND is_deleted = 0'
    ).get(id);
    if (!existing) return res.status(404).json({ error: 'Expense not found' });
    if (existing.status !== 'pending') {
      return res.status(409).json({
        error: `Cannot approve expense in status '${existing.status}'; only 'pending' is valid.`,
        current_status: existing.status,
      });
    }

    db.prepare(`
      UPDATE expenses
      SET status = 'approved',
          approved_by = ?,
          approved_at = datetime('now', 'localtime'),
          updated_at = datetime('now', 'localtime'),
          sync_status = 'pending'
      WHERE id = ?
    `).run(req.user.id, id);

    const row = db.prepare('SELECT * FROM expenses WHERE id = ?').get(id);
    res.json(row);
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// B9 — reject state transition. Requires `reason` in body, else 400.
expensesRouter.put('/:id/reject', auth, financeAccess, (req, res) => {
  try {
    const id = parseInt(req.params.id, 10);
    if (Number.isNaN(id)) return res.status(400).json({ error: 'Invalid id' });

    const reason = (req.body && typeof req.body.reason === 'string') ? req.body.reason.trim() : '';
    if (!reason) return res.status(400).json({ error: 'reason is required' });

    const existing = db.prepare(
      'SELECT id, status FROM expenses WHERE id = ? AND is_deleted = 0'
    ).get(id);
    if (!existing) return res.status(404).json({ error: 'Expense not found' });
    if (existing.status !== 'pending') {
      return res.status(409).json({
        error: `Cannot reject expense in status '${existing.status}'; only 'pending' is valid.`,
        current_status: existing.status,
      });
    }

    db.prepare(`
      UPDATE expenses
      SET status = 'rejected',
          approved_by = ?,
          approved_at = datetime('now', 'localtime'),
          rejection_reason = ?,
          updated_at = datetime('now', 'localtime'),
          sync_status = 'pending'
      WHERE id = ?
    `).run(req.user.id, reason, id);

    const row = db.prepare('SELECT * FROM expenses WHERE id = ?').get(id);
    res.json(row);
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// B10 — summary tile. Powers the Finance dashboard "Approval queue" widget
// (the dashboard endpoint already counts pending; this breaks out approved /
// rejected MTD on top). Read-only; aggregates live from `expenses`.
expensesRouter.get('/summary', auth, financeAccess, (_req, res) => {
  try {
    const pendingRow = db.prepare(`
      SELECT COUNT(*) AS n, COALESCE(SUM(amount), 0) AS total
      FROM expenses
      WHERE is_deleted = 0 AND status = 'pending'
    `).get();

    const approvedMtd = db.prepare(`
      SELECT COUNT(*) AS n, COALESCE(SUM(amount), 0) AS total
      FROM expenses
      WHERE is_deleted = 0
        AND status IN ('approved', 'paid')
        AND date(approved_at, 'localtime') >= date('now', 'localtime', 'start of month')
    `).get();

    const rejectedMtd = db.prepare(`
      SELECT COUNT(*) AS n, COALESCE(SUM(amount), 0) AS total
      FROM expenses
      WHERE is_deleted = 0
        AND status = 'rejected'
        AND date(approved_at, 'localtime') >= date('now', 'localtime', 'start of month')
    `).get();

    res.json({
      pending_count: pendingRow.n || 0,
      pending_amount: Math.round((pendingRow.total || 0) * 100) / 100,
      approved_this_month: {
        count: approvedMtd.n || 0,
        total: Math.round((approvedMtd.total || 0) * 100) / 100,
      },
      rejected_this_month: {
        count: rejectedMtd.n || 0,
        total: Math.round((rejectedMtd.total || 0) * 100) / 100,
      },
    });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// B11 — `?status=` filter is already supported by GET / at line 179:
//   GET /api/finance/expenses?status=pending|approved|rejected|paid
// documented here so FE writes don't reinvent a client-side filter.

router.use('/expenses', expensesRouter);

module.exports = router;