/**
 * Finance — Banking Controller (§10a)
 *
 * Bank accounts + reconciliation sessions. The recon endpoint receives the
 * statement line list, matches each against unmatched `payments` rows by
 * reference + amount (within a small tolerance), and returns matched /
 * unmatched counts. The full statement line is stored as `bank_reconciliation_lines`
 * so the operator can resolve stragglers manually.
 */

const express = require('express');
const { v4: uuidv4 } = require('uuid');
const Database = require('better-sqlite3');
const path = require('path');
const AuditService = require('../services/AuditService');

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

const router = express.Router();

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) {
    return res.status(401).json({ error: 'Invalid token' });
  }
};

const financeAccess = (req, res, next) => {
  if (!['school_admin', 'systems_admin', 'principal', 'bursar', 'accountant', 'hr'].includes(req.user.role)) {
    return res.status(403).json({ error: 'Finance / accounting role required' });
  }
  next();
};

// ============ ACCOUNTS ============

router.get('/accounts', auth, financeAccess, (req, res) => {
  try {
    const rows = db.prepare(`
      SELECT ba.*, coa.code AS chart_code, coa.name AS chart_name
      FROM bank_accounts ba
      LEFT JOIN chart_of_accounts coa ON ba.chart_account_id = coa.id
      WHERE ba.is_deleted = 0
      ORDER BY ba.name
    `).all();
    res.json(rows);
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

router.get('/accounts/:uid', auth, financeAccess, (req, res) => {
  try {
    const row = db.prepare(`
      SELECT ba.*, coa.code AS chart_code, coa.name AS chart_name
      FROM bank_accounts ba LEFT JOIN chart_of_accounts coa ON ba.chart_account_id = coa.id
      WHERE ba.uid = ? AND ba.is_deleted = 0
    `).get(req.params.uid);
    if (!row) return res.status(404).json({ error: 'Bank account not found' });
    res.json(row);
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

router.post('/accounts', auth, financeAccess, async (req, res) => {
  try {
    const { name, bank_name, account_number, account_type, currency, opening_balance, chart_account_uid, is_active } = req.body;
    if (!name) return res.status(400).json({ error: 'name is required' });

    let chart_account_id = null;
    if (chart_account_uid) {
      const c = db.prepare('SELECT id FROM chart_of_accounts WHERE uid = ?').get(chart_account_uid);
      if (!c) return res.status(400).json({ error: 'chart_account_uid not found' });
      chart_account_id = c.id;
    }

    const uid = uuidv4();
    const ob = Number(opening_balance) || 0;
    db.prepare(`
      INSERT INTO bank_accounts (
        uid, name, bank_name, account_number, account_type, currency,
        opening_balance, current_balance, chart_account_id, is_active
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(
      uid, name, bank_name || null, account_number || null,
      ['bank', 'cash', 'mobile_money'].includes(account_type) ? account_type : 'bank',
      currency || 'USD',
      ob, ob,
      chart_account_id,
      is_active === undefined ? 1 : (is_active ? 1 : 0),
    );
    await AuditService.log(req, 'CREATE_BANK_ACCOUNT', { entityType: 'bank_account', entityId: uid, newData: { name, bank_name } });
    res.status(201).json(db.prepare('SELECT * FROM bank_accounts WHERE uid = ?').get(uid));
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

router.patch('/accounts/:uid', auth, financeAccess, async (req, res) => {
  try {
    const existing = db.prepare('SELECT id FROM bank_accounts WHERE uid = ? AND is_deleted = 0').get(req.params.uid);
    if (!existing) return res.status(404).json({ error: 'Bank account not found' });

    const updates = [];
    const params = [];
    for (const f of ['name', 'bank_name', 'account_number', 'account_type', 'currency']) {
      if (req.body[f] !== undefined) { updates.push(`${f} = ?`); params.push(req.body[f]); }
    }
    if (req.body.opening_balance !== undefined) { updates.push('opening_balance = ?'); params.push(Number(req.body.opening_balance) || 0); }
    if (req.body.current_balance !== undefined)  { updates.push('current_balance = ?');  params.push(Number(req.body.current_balance) || 0); }
    if (req.body.chart_account_uid !== undefined) {
      if (req.body.chart_account_uid === null) {
        updates.push('chart_account_id = NULL');
      } else {
        const c = db.prepare('SELECT id FROM chart_of_accounts WHERE uid = ?').get(req.body.chart_account_uid);
        if (!c) return res.status(400).json({ error: 'chart_account_uid not found' });
        updates.push('chart_account_id = ?'); params.push(c.id);
      }
    }
    if (req.body.is_active !== undefined) { updates.push('is_active = ?'); params.push(req.body.is_active ? 1 : 0); }
    if (updates.length === 0) return res.status(400).json({ error: 'No updatable fields supplied' });

    updates.push(`updated_at = datetime('now', 'localtime')`, `sync_status = 'pending'`);
    params.push(req.params.uid);
    db.prepare(`UPDATE bank_accounts SET ${updates.join(', ')} WHERE uid = ?`).run(...params);
    await AuditService.log(req, 'UPDATE_BANK_ACCOUNT', { entityType: 'bank_account', entityId: req.params.uid });
    res.json(db.prepare('SELECT * FROM bank_accounts WHERE uid = ?').get(req.params.uid));
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

router.delete('/accounts/:uid', auth, financeAccess, async (req, res) => {
  try {
    const existing = db.prepare('SELECT id FROM bank_accounts WHERE uid = ? AND is_deleted = 0').get(req.params.uid);
    if (!existing) return res.status(404).json({ error: 'Bank account not found' });
    db.prepare(`
      UPDATE bank_accounts
      SET is_deleted = 1, is_active = 0, updated_at = datetime('now', 'localtime'),
          sync_status = 'pending', last_synced_at = NULL
      WHERE uid = ?
    `).run(req.params.uid);
    await AuditService.log(req, 'DELETE_BANK_ACCOUNT', { entityType: 'bank_account', entityId: req.params.uid });
    res.json({ success: true });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// ============ RECONCILIATIONS ============

router.get('/reconciliations', auth, financeAccess, (req, res) => {
  try {
    const { bank_account_uid } = req.query;
    let sql = `
      SELECT r.*, ba.name AS account_name, ba.currency AS account_currency
      FROM bank_reconciliations r
      LEFT JOIN bank_accounts ba ON r.bank_account_id = ba.id
      WHERE r.is_deleted = 0
    `;
    const params = [];
    if (bank_account_uid) {
      sql += ' AND ba.uid = ?';
      params.push(bank_account_uid);
    }
    sql += ' ORDER BY r.period_start DESC';
    res.json(db.prepare(sql).all(...params));
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

router.get('/reconciliations/:uid', auth, financeAccess, (req, res) => {
  try {
    const head = db.prepare(`
      SELECT r.*, ba.name AS account_name, ba.currency AS account_currency
      FROM bank_reconciliations r LEFT JOIN bank_accounts ba ON r.bank_account_id = ba.id
      WHERE r.uid = ? AND r.is_deleted = 0
    `).get(req.params.uid);
    if (!head) return res.status(404).json({ error: 'Reconciliation not found' });
    const lines = db.prepare(`
      SELECT l.*, p.amount AS payment_amount, p.payment_date AS payment_date, p.reference_number AS payment_reference
      FROM bank_reconciliation_lines l LEFT JOIN payments p ON l.payment_id = p.id
      WHERE l.reconciliation_id = ?
      ORDER BY l.statement_date DESC, l.id ASC
    `).all(head.id);
    res.json({ ...head, lines });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// ============ RECONCILE — the load-bearing endpoint ============
//
// Body: { bank_account_uid, period_start, period_end, statement_balance,
//         lines: [{ date, amount, reference }, ...] }
//
// For each statement line, find a single unmatched `payments` row that
// matches by exact-amount AND reference_number (or transaction_id, or
// paynow_poll_url tail). Once matched, the payment_id is recorded on the
// reconciliation line. The summary returns matched / unmatched counts; the
// operator then iterates on the unmatched subset.
router.post('/reconcile', auth, financeAccess, (req, res) => {
  try {
    const { bank_account_uid, period_start, period_end, statement_balance, lines } = req.body;
    if (!bank_account_uid || !period_start || !period_end || !Array.isArray(lines)) {
      return res.status(400).json({ error: 'bank_account_uid, period_start, period_end and lines[] are required' });
    }
    const acct = db.prepare('SELECT * FROM bank_accounts WHERE uid = ? AND is_deleted = 0').get(bank_account_uid);
    if (!acct) return res.status(404).json({ error: 'Bank account not found' });

    const reconUid = uuidv4();
    const insertHead = db.prepare(`
      INSERT INTO bank_reconciliations (
        uid, bank_account_id, period_start, period_end,
        statement_balance, status, created_by
      ) VALUES (?, ?, ?, ?, ?, 'draft', ?)
    `);
    const insertLine = db.prepare(`
      INSERT INTO bank_reconciliation_lines (
        uid, reconciliation_id, payment_id, statement_date,
        statement_amount, statement_reference, match_status
      ) VALUES (?, ?, ?, ?, ?, ?, ?)
    `);

    let matched = 0;
    let unmatched = 0;

    const tx = db.transaction(() => {
      const result = insertHead.run(reconUid, acct.id, period_start, period_end, Number(statement_balance) || 0, req.user.id);
      const reconId = result.lastInsertRowid;

      // Book balance = sum of payments in the period (same window, date inclusive).
      const bookRow = db.prepare(`
        SELECT COALESCE(SUM(amount),0) AS total
        FROM payments
        WHERE is_deleted = 0
          AND status = 'completed'
          AND date(payment_date, 'localtime') BETWEEN date(?, 'localtime') AND date(?, 'localtime')
      `).get(period_start, period_end);
      const bookBalance = bookRow.total || 0;

      // Updatestatement diff so the operator immediately sees status.
      const diff = (Number(statement_balance) || 0) - bookBalance;
      db.prepare(`
        UPDATE bank_reconciliations
        SET book_balance = ?, status = ?
        WHERE id = ?
      `).run(
        bookBalance,
        Math.abs(diff) < 0.01 ? 'balanced' : 'discrepancy',
        reconId,
      );

      // For each statement line, try to auto-match.
      for (const ln of lines) {
        const amount = Number(ln.amount);
        if (!Number.isFinite(amount)) { unmatched++; continue; }
        // Look for an unmatched payment in the period that matches amount and
        // either reference_number / transaction_id / paynow_poll_url tail.
        const pay = db.prepare(`
          SELECT id FROM payments
          WHERE is_deleted = 0
            AND ABS(amount - ?) < 0.01
            AND (
              reference_number = ?
              OR transaction_id = ?
              OR paynow_poll_url LIKE '%' || ? || '%'
              OR (reference_number IS NULL AND transaction_id IS NULL AND ? LIKE '%' || CAST(id AS TEXT) || '%')
            )
            AND id NOT IN (SELECT payment_id FROM bank_reconciliation_lines WHERE payment_id IS NOT NULL AND match_status <> 'ignored' AND is_deleted = 0)
            AND date(payment_date, 'localtime') BETWEEN date(?, 'localtime') AND date(?, 'localtime')
          LIMIT 1
        `).get(amount, String(ln.reference || ''), String(ln.reference || ''), String(ln.reference || ''), String(ln.reference || ''), period_start, period_end);

        if (pay) {
          insertLine.run(uuidv4(), reconId, pay.id, ln.date || null, amount, ln.reference || null, 'matched');
          matched++;
        } else {
          insertLine.run(uuidv4(), reconId, null, ln.date || null, amount, ln.reference || null, 'unmatched');
          unmatched++;
        }
      }
    });

    tx();
    AuditService.log(req, 'RUN_RECONCILIATION', {
      entityType: 'bank_reconciliation', entityId: reconUid,
      newData: { bank_account_uid, period_start, period_end, matched, unmatched },
    });

    res.json({ uid: reconUid, matched, unmatched });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// Resolve a single unmatched line — either ignore it or attach a manual payment.
router.post('/reconciliations/:uid/resolve', auth, financeAccess, (req, res) => {
  try {
    const { line_id, action, payment_id } = req.body;
    if (!line_id || !['ignore', 'attach'].includes(action)) {
      return res.status(400).json({ error: 'line_id and action (ignore|attach) are required' });
    }
    const head = db.prepare('SELECT id FROM bank_reconciliations WHERE uid = ?').get(req.params.uid);
    if (!head) return res.status(404).json({ error: 'Reconciliation not found' });

    const line = db.prepare('SELECT id FROM bank_reconciliation_lines WHERE id = ? AND reconciliation_id = ?').get(line_id, head.id);
    if (!line) return res.status(404).json({ error: 'Reconciliation line not found' });

    if (action === 'ignore') {
      db.prepare(`
        UPDATE bank_reconciliation_lines SET match_status = 'ignored', updated_at = datetime('now','localtime') WHERE id = ?
      `).run(line.id);
    } else {
      if (!payment_id) return res.status(400).json({ error: 'payment_id is required for attach' });
      db.prepare(`
        UPDATE bank_reconciliation_lines SET payment_id = ?, match_status = 'matched', updated_at = datetime('now','localtime') WHERE id = ?
      `).run(payment_id, line.id);
    }

    // Recompute summary so the operator sees a fresh status.
    const lines = db.prepare('SELECT match_status FROM bank_reconciliation_lines WHERE reconciliation_id = ? AND is_deleted = 0').all(head.id);
    const unmatched = lines.filter((l) => l.match_status === 'unmatched').length;
    db.prepare(`UPDATE bank_reconciliations SET status = ?, updated_at = datetime('now','localtime') WHERE id = ?`)
      .run(unmatched === 0 ? 'balanced' : 'discrepancy', head.id);

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

module.exports = router;
