/**
 * Finance — Invoices Controller (§10a)
 *
 * CRUD on `invoices` plus a small "send" stub that just logs the action.
 * Each row represents one billing event for one student. `subtotal`,
 * `discount_amount`, `total`, `paid_amount` are all kept on the invoice
 * for ease of view; the actual line-level resolution lives in
 * student_fees / student_fee_discounts.
 *
 * RBAC: financeAccess (admin, principal, bursar, accountant, hr).
 */

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();
};

const generateInvoiceNumber = () => {
  const now = new Date();
  const y = now.getFullYear();
  const m = String(now.getMonth() + 1).padStart(2, '0');
  // Pseudo-unique with timestamp + random suffix (5 chars). Real uniqueness
  // is enforced by the UNIQUE constraint on `invoice_number`; retries add
  // a new suffix.
  const tail = Math.random().toString(36).slice(2, 7).toUpperCase();
  return `INV-${y}${m}-${tail}`;
};

// ============ LIST ============

router.get('/', auth, financeAccess, (req, res) => {
  try {
    const { student_uid, status, fee_group_id, period_label, search, limit, offset } = req.query;
    let sql = `
      SELECT i.*,
             u.first_name, u.last_name, u.email,
             fg.name AS fee_name, fg.type AS fee_type
      FROM invoices i
      JOIN users u ON i.student_id = u.id
      LEFT JOIN fee_groups fg ON i.fee_group_id = fg.id
      WHERE i.is_deleted = 0
    `;
    const params = [];
    if (student_uid) { sql += ' AND u.uid = ?'; params.push(student_uid); }
    if (status)      { sql += ' AND i.status = ?'; params.push(status); }
    if (fee_group_id) { sql += ' AND i.fee_group_id = ?'; params.push(Number(fee_group_id)); }
    if (period_label) { sql += ' AND i.period_label = ?'; params.push(period_label); }
    if (search) {
      sql += ' AND (i.invoice_number LIKE ? OR u.first_name LIKE ? OR u.last_name LIKE ?)';
      const like = `%${search}%`;
      params.push(like, like, like);
    }
    sql += ' ORDER BY i.issued_at DESC, i.id DESC LIMIT ? OFFSET ?';
    params.push(Number(limit) || 100, Number(offset) || 0);

    const rows = db.prepare(sql).all(...params);
    res.json(rows);
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// ============ DETAIL ============

router.get('/:uid', auth, financeAccess, (req, res) => {
  try {
    const head = db.prepare(`
      SELECT i.*, u.first_name, u.last_name, u.email, u.phone,
             fg.name AS fee_name, fg.type AS fee_type
      FROM invoices i JOIN users u ON i.student_id = u.id
      LEFT JOIN fee_groups fg ON i.fee_group_id = fg.id
      WHERE i.uid = ? AND i.is_deleted = 0
    `).get(req.params.uid);
    if (!head) return res.status(404).json({ error: 'Invoice not found' });
    // Linked student_fees (the underlying lines)
    const lines = db.prepare(`
      SELECT sf.*, fg.name AS fee_name, fg.type AS fee_type
      FROM student_fees sf LEFT JOIN fee_groups fg ON sf.fee_group_id = fg.id
      WHERE sf.student_id = ? AND sf.is_deleted = 0
        AND (sf.due_date IS NULL OR date(sf.due_date) <= date(?, 'localtime'))
      ORDER BY sf.due_date ASC
    `).all(head.student_id, head.due_date || '2999-12-31');
    res.json({ ...head, lines });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// ============ CREATE ============

router.post('/', auth, financeAccess, async (req, res) => {
  try {
    const { student_uid, fee_group_id, plan_id, period_label, subtotal, discount_amount, total, currency, due_date, notes } = req.body;
    if (!student_uid) return res.status(400).json({ error: 'student_uid is required' });

    const student = db.prepare('SELECT id FROM users WHERE uid = ? AND is_deleted = 0').get(student_uid);
    if (!student) return res.status(404).json({ error: 'Student not found' });

    let fgId = null;
    if (fee_group_id) {
      const fg = db.prepare('SELECT id FROM fee_groups WHERE id = ? AND is_deleted = 0').get(Number(fee_group_id));
      if (!fg) return res.status(400).json({ error: 'fee_group_id not found' });
      fgId = fg.id;
    }
    let planId = null;
    if (plan_id) {
      // Plan_id may be uid (preferred) or numeric id.
      const plan = isNaN(Number(plan_id))
        ? db.prepare('SELECT id FROM fee_plans WHERE uid = ?').get(plan_id)
        : db.prepare('SELECT id FROM fee_plans WHERE id = ?').get(Number(plan_id));
      if (!plan) return res.status(400).json({ error: 'plan_id not found' });
      planId = plan.id;
    }

    const sub = Number(subtotal) || 0;
    const disc = Number(discount_amount) || 0;
    const tot = Number.isFinite(Number(total)) ? Number(total) : Math.max(0, sub - disc);

    const uid = uuidv4();
    let invoiceNumber;
    // Retry on the rare UNIQUE collision.
    for (let attempt = 0; attempt < 3; attempt++) {
      invoiceNumber = generateInvoiceNumber();
      try {
        db.prepare(`
          INSERT INTO invoices (
            uid, invoice_number, student_id, fee_group_id, plan_id,
            period_label, subtotal, discount_amount, total, paid_amount,
            currency, status, issued_at, due_date, notes, created_by
          ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 0, ?, 'issued', datetime('now','localtime'), ?, ?, ?)
        `).run(
          uid, invoiceNumber, student.id, fgId, planId,
          period_label || null,
          sub, disc, tot,
          currency || 'USD',
          due_date || null, notes || null,
          req.user.id,
        );
        break;
      } catch (e2) {
        if (attempt === 2 || !String(e2.message).includes('UNIQUE')) throw e2;
      }
    }

    await AuditService.log(req, 'CREATE_INVOICE', { entityType: 'invoice', entityId: uid, newData: { invoiceNumber, total: tot } });
    res.status(201).json(db.prepare('SELECT * FROM invoices WHERE uid = ?').get(uid));
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// ============ UPDATE ============

router.patch('/:uid', auth, financeAccess, async (req, res) => {
  try {
    const existing = db.prepare('SELECT id FROM invoices WHERE uid = ? AND is_deleted = 0').get(req.params.uid);
    if (!existing) return res.status(404).json({ error: 'Invoice not found' });
    const updates = [];
    const params = [];
    const allowed = ['period_label', 'discount_amount', 'due_date', 'notes', 'status', 'currency'];
    for (const f of allowed) {
      if (req.body[f] !== undefined) { updates.push(`${f} = ?`); params.push(req.body[f]); }
    }
    if (req.body.subtotal !== undefined || req.body.discount_amount !== undefined) {
      // Recompute total if either amount changes.
      const cur = db.prepare('SELECT subtotal, discount_amount FROM invoices WHERE id = ?').get(existing.id);
      const subtotal = req.body.subtotal !== undefined ? Number(req.body.subtotal) : cur.subtotal;
      const discount = req.body.discount_amount !== undefined ? Number(req.body.discount_amount) : cur.discount_amount;
      if (!updates.find((u) => u.startsWith('discount_amount'))) { updates.push('discount_amount = ?'); params.push(discount); }
      updates.push('total = ?'); params.push(Math.max(0, subtotal - (updates.find((u) => u.startsWith('discount_amount')) ? params[updates.length - 1] : discount)));
    }
    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 invoices SET ${updates.join(', ')} WHERE uid = ?`).run(...params);
    await AuditService.log(req, 'UPDATE_INVOICE', { entityType: 'invoice', entityId: req.params.uid });
    res.json(db.prepare('SELECT * FROM invoices WHERE uid = ?').get(req.params.uid));
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// ============ SEND — stub mailer ============
//
// Real SMTP wiring isn't part of §10a (no email infra). The endpoint logs
// the action and flips status from draft → issued if needed.
router.post('/:uid/send', auth, financeAccess, async (req, res) => {
  try {
    const inv = db.prepare('SELECT id, status FROM invoices WHERE uid = ? AND is_deleted = 0').get(req.params.uid);
    if (!inv) return res.status(404).json({ error: 'Invoice not found' });

    db.prepare(`
      UPDATE invoices SET status = CASE WHEN status = 'draft' THEN 'issued' ELSE status END,
                          issued_at = COALESCE(issued_at, datetime('now','localtime')),
                          updated_at = datetime('now','localtime'),
                          sync_status = 'pending'
      WHERE id = ?
    `).run(inv.id);

    await AuditService.log(req, 'SEND_INVOICE', { entityType: 'invoice', entityId: req.params.uid });
    res.json({ success: true, status: 'sent' });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// ============ SOFT DELETE ============

router.delete('/:uid', auth, financeAccess, async (req, res) => {
  try {
    const existing = db.prepare('SELECT id FROM invoices WHERE uid = ? AND is_deleted = 0').get(req.params.uid);
    if (!existing) return res.status(404).json({ error: 'Invoice not found' });
    if (Number(existing.paid_amount) > 0) {
      return res.status(409).json({ error: 'Cannot delete an invoice that has received payments; cancel it instead.' });
    }
    db.prepare(`
      UPDATE invoices SET is_deleted = 1, status = 'cancelled', updated_at = datetime('now','localtime'),
                          sync_status = 'pending', last_synced_at = NULL
      WHERE uid = ?
    `).run(req.params.uid);
    await AuditService.log(req, 'DELETE_INVOICE', { entityType: 'invoice', entityId: req.params.uid });
    res.json({ success: true });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

module.exports = router;
