const db = require('../config/db');
const { buildMonthlyReportSnapshot } = require('../utils/monthlyReportSnapshot');

/**
 * Dashboard KPIs: today's sales, revenue, tickets sold, low-stock count
 */
exports.dashboard = async (_req, res, next) => {
  try {
    const today = new Date().toISOString().slice(0, 10);

    const [salesRes, ticketsRes, lowStockRes, revenueRes] = await Promise.all([
      db.query(
        `SELECT COUNT(*) AS count, COALESCE(SUM(total_amount), 0) AS total
         FROM transactions WHERE type = 'sale' AND COALESCE(is_deleted, 0) = 0 AND DATE(created_at) = ?`, [today]
      ),
      db.query(
        `SELECT COUNT(*) AS count, COALESCE(SUM(selling_price), 0) AS total
         FROM tickets WHERE DATE(created_at) = ?`, [today]
      ),
      db.query(
        `SELECT COUNT(*) AS count FROM products
         WHERE stock_level <= low_stock_threshold AND status != 'inactive'`
      ),
      db.query(
        `SELECT COALESCE(SUM(total_amount), 0) AS total
         FROM transactions WHERE COALESCE(is_deleted, 0) = 0 AND DATE(created_at) = ?`, [today]
      ),
    ]);

    res.json({
      today_sales_count: parseInt(salesRes[0][0].count),
      today_sales_total: parseFloat(salesRes[0][0].total),
      today_tickets_count: parseInt(ticketsRes[0][0].count),
      today_tickets_total: parseFloat(ticketsRes[0][0].total),
      low_stock_count: parseInt(lowStockRes[0][0].count),
      today_revenue: parseFloat(revenueRes[0][0].total),
    });
  } catch (err) {
    next(err);
  }
};

/**
 * Sales summary over a date range, grouped by day
 */
exports.salesSummary = async (req, res, next) => {
  try {
    const { from_date, to_date } = req.query;
    let sql = `
      SELECT DATE(created_at) AS date,
             COUNT(*) AS transaction_count,
                    SUM(total_amount) AS revenue
                  FROM transactions
                  WHERE type = 'sale' AND COALESCE(is_deleted, 0) = 0
    `;
    const params = [];
    if (from_date) { params.push(from_date); sql += ' AND created_at >= ?'; }
    if (to_date) { params.push(to_date); sql += ' AND created_at <= DATE_ADD(?, INTERVAL 1 DAY)'; }
    sql += ' GROUP BY DATE(created_at) ORDER BY date DESC';

    const [rows] = await db.query(sql, params);
    res.json(rows);
  } catch (err) {
    next(err);
  }
};

/**
 * Revenue breakdown by category
 */
exports.categoryBreakdown = async (req, res, next) => {
  try {
    const { from_date, to_date } = req.query;
    let sql = `
      SELECT c.name AS category, COUNT(ti.id) AS items_sold,
             SUM(ti.line_total) AS revenue
      FROM transaction_items ti
      JOIN products p ON p.id = ti.product_id
      JOIN categories c ON c.id = p.category_id
      JOIN transactions t ON t.id = ti.transaction_id
      WHERE t.type = 'sale' AND COALESCE(t.is_deleted, 0) = 0
    `;
    const params = [];
    if (from_date) { params.push(from_date); sql += ' AND t.created_at >= ?'; }
    if (to_date) { params.push(to_date); sql += ' AND t.created_at <= DATE_ADD(?, INTERVAL 1 DAY)'; }
    sql += ' GROUP BY c.name ORDER BY revenue DESC';

    const [rows] = await db.query(sql, params);
    res.json(rows);
  } catch (err) {
    next(err);
  }
};

/**
 * Profit/loss: sales margin (selling - cost) + daily_entries (profit) vs daily_entries (expense) + expense_entries
 */
exports.profitLoss = async (req, res, next) => {
  try {
    const { from_date, to_date } = req.query;
    let profitSql = "SELECT COALESCE(SUM(total), 0) AS total FROM daily_entries WHERE type = 'profit'";
    let expenseSql = "SELECT COALESCE(SUM(total), 0) AS total FROM daily_entries WHERE type = 'expense'";
    let directExpSql = 'SELECT COALESCE(SUM(amount), 0) AS total FROM expense_entries WHERE 1=1';
    let salesMarginSql = `
      SELECT COALESCE(SUM(ti.line_total), 0) AS total_revenue,
             COALESCE(SUM(ti.line_cost), 0) AS total_cost,
             COALESCE(SUM(t.discount_amount), 0) AS total_discounts
      FROM transaction_items ti
      JOIN transactions t ON t.id = ti.transaction_id
      WHERE t.type = 'sale' AND COALESCE(t.is_deleted, 0) = 0
    `;
    const pParams = [];
    const eParams = [];
    const deParams = [];
    const smParams = [];

    if (from_date) {
      pParams.push(from_date); profitSql += ' AND entry_date >= ?';
      eParams.push(from_date); expenseSql += ' AND entry_date >= ?';
      deParams.push(from_date); directExpSql += ' AND entry_date >= ?';
      smParams.push(from_date); salesMarginSql += ' AND t.created_at >= ?';
    }
    if (to_date) {
      pParams.push(to_date); profitSql += ' AND entry_date <= ?';
      eParams.push(to_date); expenseSql += ' AND entry_date <= ?';
      deParams.push(to_date); directExpSql += ' AND entry_date <= ?';
      smParams.push(to_date); salesMarginSql += ' AND t.created_at <= DATE_ADD(?, INTERVAL 1 DAY)';
    }

    const [profitRes, expenseRes, directExpRes, salesMarginRes] = await Promise.all([
      db.query(profitSql, pParams),
      db.query(expenseSql, eParams),
      db.query(directExpSql, deParams),
      db.query(salesMarginSql, smParams),
    ]);

    const manualProfit = parseFloat(profitRes[0][0].total);
    const totalExpense = parseFloat(expenseRes[0][0].total) + parseFloat(directExpRes[0][0].total);
    const rawRevenue = parseFloat(salesMarginRes[0][0].total_revenue);
    const totalDiscounts = parseFloat(salesMarginRes[0][0].total_discounts) || 0;
    const salesRevenue = rawRevenue - totalDiscounts;
    const salesCost = parseFloat(salesMarginRes[0][0].total_cost);
    const salesMargin = salesRevenue - salesCost;
    const totalProfit = manualProfit + salesMargin;

    res.json({
      total_profit: totalProfit,
      total_expense: totalExpense,
      net: totalProfit - totalExpense,
      sales_revenue: salesRevenue,
      sales_cost: salesCost,
      sales_margin: salesMargin,
      manual_profit: manualProfit,
    });
  } catch (err) {
    next(err);
  }
};

/**
 * Top-selling products
 */
exports.topProducts = async (req, res, next) => {
  try {
    const limit = parseInt(req.query.limit) || 10;
    const [rows] = await db.query(
      `SELECT p.id, p.name, p.sku, c.name AS category,
              SUM(ti.quantity) AS total_qty, SUM(ti.line_total) AS total_revenue
      FROM transaction_items ti
      JOIN products p ON p.id = ti.product_id
      JOIN categories c ON c.id = p.category_id
      JOIN transactions t ON t.id = ti.transaction_id
      WHERE COALESCE(t.is_deleted, 0) = 0
      GROUP BY p.id, p.name, p.sku, c.name
       ORDER BY total_qty DESC
       LIMIT ?`,
      [limit]
    );
    res.json(rows);
  } catch (err) {
    next(err);
  }
};

/**
 * Customer balances overview
 */
exports.customerBalances = async (_req, res, next) => {
  try {
    const [rows] = await db.query(
      `SELECT id, CONCAT(first_name, ' ', last_name) AS name, customer_type,
              risk_level, ledger_balance, balance_status
       FROM customers
       WHERE ledger_balance != 0
       ORDER BY ledger_balance DESC`
    );
    const [totals] = await db.query(
      'SELECT COALESCE(SUM(ledger_balance), 0) AS total_outstanding FROM customers WHERE ledger_balance > 0'
    );
    res.json({ total_outstanding: parseFloat(totals[0].total_outstanding), customers: rows });
  } catch (err) {
    next(err);
  }
};

/**
 * Create a monthly snapshot for the specified month (or current previous month when called without body).
 * This stores a JSON snapshot of customers + totals to allow historical comparisons.
 */
exports.createSnapshot = async (req, res, next) => {
  try {
    const { year, month } = req.body || {};
    const now = new Date();
    // Default snapshot month: previous month
    const target = (year && month)
      ? new Date(year, month - 1, 1)
      : new Date(now.getFullYear(), now.getMonth() - 1, 1);

    const snapshotMonth = `${target.getFullYear()}-${String(target.getMonth() + 1).padStart(2, '0')}`;

    // Avoid duplicates
    const [exists] = await db.query('SELECT id FROM monthly_report_snapshots WHERE snapshot_month = ?', [snapshotMonth]);
    if (exists[0] && exists[0].id) {
      return res.status(200).json({ ok: true, message: 'Snapshot already exists', snapshot_month: snapshotMonth });
    }

    const [customerRows] = await db.query(
      `SELECT id, first_name, last_name, ledger_balance, risk_level, balance_status, created_at
       FROM customers`
    );
    const [paymentRows] = await db.query(
      `SELECT customer_id, amount, created_at
       FROM customer_payments
       ORDER BY created_at ASC`
    );

    const payload = buildMonthlyReportSnapshot({
      snapshotMonth,
      generatedAt: now,
      accountManager: (req.user && (req.user.full_name || req.user.username)) || 'SYSTEM',
      customers: customerRows || [],
      payments: paymentRows || [],
    });

    await db.query(
      'INSERT INTO monthly_report_snapshots (snapshot_month, generated_at, total_outstanding, data) VALUES (?, NOW(), ?, ?)',
      [snapshotMonth, payload.totalOutstanding, JSON.stringify(payload)]
    );

    res.json({ ok: true, snapshot_month: snapshotMonth });
  } catch (err) {
    next(err);
  }
};

exports.listSnapshots = async (req, res, next) => {
  try {
    const limit = parseInt(req.query.limit, 10) || 24;
    const [rows] = await db.query('SELECT id, snapshot_month, generated_at, total_outstanding, data, created_at FROM monthly_report_snapshots ORDER BY snapshot_month DESC LIMIT ?', [limit]);
    res.json(rows);
  } catch (err) {
    next(err);
  }
};

exports.getSnapshot = async (req, res, next) => {
  try {
    const month = req.params.month;
    if (!month) return res.status(400).json({ error: 'Month is required (YYYY-MM)' });
    const [rows] = await db.query('SELECT id, snapshot_month, generated_at, total_outstanding, data, created_at FROM monthly_report_snapshots WHERE snapshot_month = ? LIMIT 1', [month]);
    const row = (rows && rows[0]) ? rows[0] : null;
    if (!row) return res.status(404).json({ error: 'Snapshot not found' });
    res.json(row);
  } catch (err) {
    next(err);
  }
};
