const express = require('express');
const { all, now, one, parseJson, run } = require('../db');
const { bookLedger } = require('../services/books');
const { runReport } = require('../services/reportEngine');

const router = express.Router();

router.get('/dashboard', (req, res) => {
  const cid = req.companyId || null;
  const stats = one(`
    SELECT
      COUNT(*) AS asset_count,
      COALESCE(SUM(acquisition_cost), 0) AS total_cost,
      COALESCE(SUM(CASE WHEN status = 'disposed' THEN 1 ELSE 0 END), 0) AS disposed_count,
      COALESCE(SUM(CASE WHEN status = 'in_service' THEN 1 ELSE 0 END), 0) AS in_service_count
    FROM assets WHERE (? IS NULL OR entity_id = ?)
  `, [cid, cid]);
  const byCategory = all('SELECT category, COUNT(*) AS count, COALESCE(SUM(acquisition_cost), 0) AS value FROM assets WHERE (? IS NULL OR entity_id = ?) GROUP BY category ORDER BY value DESC', [cid, cid]);
  const byStatus = all('SELECT status, COUNT(*) AS count FROM assets WHERE (? IS NULL OR entity_id = ?) GROUP BY status ORDER BY count DESC', [cid, cid]);
  const upcomingTasks = all(`
    SELECT t.*, a.asset_id, a.description
    FROM tasks t
    LEFT JOIN assets a ON a.id = t.asset_id
    WHERE t.status != 'complete' AND (? IS NULL OR a.entity_id = ?)
    ORDER BY t.due_date IS NULL, t.due_date
    LIMIT 8
  `, [cid, cid]);
  const auditTrail = all('SELECT al.*, u.name AS actor_name FROM audit_logs al LEFT JOIN users u ON u.id = al.actor_id ORDER BY al.id DESC LIMIT 10');
  res.json({ stats, byCategory, byStatus, upcomingTasks, auditTrail });
});

// ---- Per-user dashboard layout (the drag-and-drop widget arrangement) --------

router.get('/dashboard/layout', (req, res) => {
  const row = one('SELECT layout_json FROM dashboard_layouts WHERE user_id = ?', [req.user.id]);
  res.json({ layout: row ? parseJson(row.layout_json, null) : null });
});

router.put('/dashboard/layout', (req, res) => {
  const layout = Array.isArray(req.body.layout) ? req.body.layout : [];
  run(
    `INSERT INTO dashboard_layouts (user_id, layout_json, updated_at) VALUES (?, ?, ?)
     ON CONFLICT(user_id) DO UPDATE SET layout_json = excluded.layout_json, updated_at = excluded.updated_at`,
    [req.user.id, JSON.stringify(layout), now()]
  );
  res.json({ layout });
});

// ---- Depreciation aggregator for the dashboard charts (one round-trip) -------
// byBook: YTD + accumulated per enabled depreciation book; byCategory: accumulated by category (primary book).
router.get('/dashboard/depreciation', (req, res) => {
  const cid = req.companyId || null;
  const year = Number(req.query.year) || new Date().getFullYear();
  const books = all(
    "SELECT code, is_primary FROM books WHERE (? IS NULL OR entity_id = ?) AND enabled = 1 AND book_type != 'maintenance' ORDER BY is_primary DESC, sort_order, id",
    [cid, cid]
  );
  const byBook = books.map((b) => {
    const ledger = bookLedger(b.code, year, cid);
    return { book: b.code, ytd: ledger ? ledger.summary.depreciation : 0, accumulated: ledger ? ledger.summary.accumulated : 0 };
  });
  const primary = (books.find((b) => b.is_primary) || books[0] || {}).code || 'GAAP';
  let byCategory = [];
  try {
    byCategory = runReport('depreciation_summary', { book: primary, year, entity_id: cid }).rows
      .map((r) => ({ category: r.category, accumulated: r.accumulated, depreciation: r.depreciation }));
  } catch {
    byCategory = [];
  }
  res.json({ year, book: primary, byBook, byCategory });
});

module.exports = router;