const {pool} = require('../config/db');
const {listCustomers} = require('./customerService');
const {listLoans} = require('./loanService');

async function getSummary(shopId, query = {}) {
  const datePattern = /^\d{4}-\d{2}-\d{2}$/;
  const today = new Date().toISOString().slice(0, 10);
  const fromDate = datePattern.test(query.fromDate) ? query.fromDate : today;
  const toDate = datePattern.test(query.toDate) ? query.toDate : today;
  if (fromDate > toDate) {
    const error = new Error('From date cannot be after to date');
    error.statusCode = 400;
    throw error;
  }
  const [rows] = await pool.execute(
    `SELECT
       SUM(CASE WHEN loan_date BETWEEN ? AND ? THEN 1 ELSE 0 END) AS createdLoans,
       SUM(CASE WHEN status = 'Active' AND loan_date BETWEEN ? AND ? THEN 1 ELSE 0 END) AS activeLoans,
       SUM(CASE WHEN status IN ('Active', 'Overdue') AND due_date BETWEEN ? AND ? THEN 1 ELSE 0 END) AS dueLoans,
       SUM(CASE WHEN (status = 'Overdue' OR (status = 'Active' AND due_date < CURDATE())) AND due_date BETWEEN ? AND ? THEN 1 ELSE 0 END) AS overdueLoans,
       SUM(CASE WHEN status = 'Closed' THEN 1 ELSE 0 END) AS closedLoans,
       COALESCE(SUM(CASE WHEN loan_date BETWEEN ? AND ? THEN outstanding_balance ELSE 0 END), 0) AS outstandingAmount
     FROM loans
     WHERE shop_id = ? AND deleted_at IS NULL`,
    [fromDate, toDate, fromDate, toDate, fromDate, toDate, fromDate, toDate, fromDate, toDate, shopId],
  );

  const [customerRows] = await pool.execute('SELECT COUNT(*) AS totalCustomers FROM customers WHERE shop_id = ? AND deleted_at IS NULL', [shopId]);
  const [interestRows] = await pool.execute(
    `SELECT COALESCE(SUM(lp.interest_amount), 0) AS interestCollectedToday
     FROM loan_payments lp
     INNER JOIN loans l ON l.id = lp.loan_id
     WHERE l.shop_id = ? AND DATE(lp.payment_date) BETWEEN ? AND ?`,
    [shopId, fromDate, toDate],
  );

  return {
    todaysLoans: Number(rows[0].createdLoans || 0),
    createdLoans: Number(rows[0].createdLoans || 0),
    activeLoans: Number(rows[0].activeLoans || 0),
    dueLoans: Number(rows[0].dueLoans || 0),
    overdueLoans: Number(rows[0].overdueLoans || 0),
    closedLoans: Number(rows[0].closedLoans || 0),
    totalCustomers: Number(customerRows[0].totalCustomers || 0),
    interestCollectedToday: Number(interestRows[0].interestCollectedToday || 0),
    outstandingAmount: Number(rows[0].outstandingAmount || 0),
  };
}

async function recentLoans(shopId) {
  const response = await listLoans(shopId, {page: 1, pageSize: 5});
  return response.items;
}

async function recentCustomers(shopId) {
  const response = await listCustomers(shopId, {page: 1, pageSize: 5});
  return response.items;
}

module.exports = {getSummary, recentCustomers, recentLoans};
