/**
 * Finance — Chart of Accounts Controller (§10a)
 *
 * CRUD on the nominal ledger. Returns a tree (default) or flat list
 * (?flat=true). Soft-delete is preserved so reconciled history stays queryable.
 */

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

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

router.get('/', auth, financeAccess, (req, res) => {
  try {
    const { flat, type } = req.query;
    let sql = 'SELECT * FROM chart_of_accounts WHERE is_deleted = 0';
    const params = [];
    if (type) {
      sql += ' AND type = ?';
      params.push(type);
    }
    sql += ' ORDER BY code';
    const rows = db.prepare(sql).all(...params);

    if (flat === 'true' || flat === '1') return res.json(rows);

    // Build tree (parent → children)
    const byId = new Map();
    rows.forEach((r) => byId.set(r.id, { ...r, children: [] }));
    const roots = [];
    rows.forEach((r) => {
      const node = byId.get(r.id);
      if (r.parent_id && byId.has(r.parent_id)) {
        byId.get(r.parent_id).children.push(node);
      } else {
        roots.push(node);
      }
    });
    res.json(roots);
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

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

router.get('/:uid', auth, financeAccess, (req, res) => {
  try {
    const row = db.prepare('SELECT * FROM chart_of_accounts WHERE uid = ? AND is_deleted = 0').get(req.params.uid);
    if (!row) return res.status(404).json({ error: 'Chart account not found' });
    res.json(row);
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

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

router.post('/', auth, financeAccess, async (req, res) => {
  try {
    const { code, name, type, parent_uid, color, is_active } = req.body;
    if (!code || !name || !type) return res.status(400).json({ error: 'code, name and type are required' });
    if (!['asset', 'liability', 'equity', 'revenue', 'expense'].includes(type)) {
      return res.status(400).json({ error: 'type must be asset|liability|equity|revenue|expense' });
    }

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

    const uid = uuidv4();
    db.prepare(`
      INSERT INTO chart_of_accounts (uid, code, name, type, parent_id, color, is_active)
      VALUES (?, ?, ?, ?, ?, ?, ?)
    `).run(uid, code, name, type, parent_id, color || null, is_active === undefined ? 1 : (is_active ? 1 : 0));

    await AuditService.log(req, 'CREATE_CHART_ACCOUNT', { entityType: 'chart_of_accounts', entityId: uid, newData: { code, name, type } });
    const row = db.prepare('SELECT * FROM chart_of_accounts WHERE uid = ?').get(uid);
    res.status(201).json(row);
  } catch (e) {
    if (String(e.message).includes('UNIQUE')) return res.status(409).json({ error: 'code must be unique' });
    res.status(500).json({ error: e.message });
  }
});

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

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

    const updates = [];
    const params = [];
    for (const f of ['code', 'name', 'type', 'color']) {
      if (req.body[f] !== undefined) { updates.push(`${f} = ?`); params.push(req.body[f]); }
    }
    if (req.body.parent_uid !== undefined) {
      if (req.body.parent_uid === null) {
        updates.push('parent_id = NULL');
      } else {
        const p = db.prepare('SELECT id FROM chart_of_accounts WHERE uid = ?').get(req.body.parent_uid);
        if (!p) return res.status(400).json({ error: 'parent_uid not found' });
        updates.push('parent_id = ?'); params.push(p.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 chart_of_accounts SET ${updates.join(', ')} WHERE uid = ?`).run(...params);

    await AuditService.log(req, 'UPDATE_CHART_ACCOUNT', { entityType: 'chart_of_accounts', entityId: req.params.uid });
    const row = db.prepare('SELECT * FROM chart_of_accounts WHERE uid = ?').get(req.params.uid);
    res.json(row);
  } catch (e) {
    if (String(e.message).includes('UNIQUE')) return res.status(409).json({ error: 'code must be unique' });
    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 chart_of_accounts WHERE uid = ? AND is_deleted = 0').get(req.params.uid);
    if (!existing) return res.status(404).json({ error: 'Chart account not found' });

    // Block delete when children still reference it.
    const children = db.prepare('SELECT COUNT(*) AS n FROM chart_of_accounts WHERE parent_id = ? AND is_deleted = 0').get(existing.id);
    if (children.n > 0) {
      return res.status(409).json({ error: 'Cannot delete: account still has child accounts.' });
    }
    // Block delete when bank_accounts still reference it.
    const linked = db.prepare('SELECT COUNT(*) AS n FROM bank_accounts WHERE chart_account_id = ? AND is_deleted = 0').get(existing.id);
    if (linked.n > 0) {
      return res.status(409).json({ error: 'Cannot delete: account is linked to a bank account.' });
    }

    db.prepare(`
      UPDATE chart_of_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_CHART_ACCOUNT', { entityType: 'chart_of_accounts', entityId: req.params.uid });
    res.json({ success: true });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

module.exports = router;
