const db = require('../db/config');

exports.getAllTransactions = async () => {
    const [rows] = await db.query('SELECT * FROM accounts ORDER BY created_at DESC');
    return rows;
};

exports.getTransactionByType = async (type) => {
    const [rows] = await db.query('SELECT * FROM accounts WHERE type = ? ORDER BY created_at DESC', [type]);
    return rows;
};

exports.getProfitLossSummary = async () => {
    const [rows] = await db.query(`
    SELECT 
      (SELECT IFNULL(SUM(credit), 0) FROM accounts WHERE type = 'sale') AS total_sales,
      (SELECT IFNULL(SUM(debit), 0) FROM accounts WHERE type = 'purchase') AS total_purchases,
      (SELECT IFNULL(SUM(debit), 0) FROM accounts WHERE type = 'expense') AS total_expenses
  `);
    return rows[0];
};
exports.getCustomerSummary = async (customerId) => {
    const [sales] = await db.query(
        'SELECT SUM(total) AS total_sales FROM sales WHERE customer_id = ?', [customerId]
    );
    const [payments] = await db.query(
        'SELECT SUM(amount) AS total_paid FROM customer_payments WHERE customer_id = ?', [customerId]
    );

    const total_sales = sales[0].total_sales || 0;
    const total_paid = payments[0].total_paid || 0;
    const balance = total_sales - total_paid;

    return { total_sales, total_paid, balance };
};
exports.getBankSummary = async (bankId) => {
    const [deposit] = await db.query(
      'SELECT SUM(amount) AS total_deposit FROM bank_transactions WHERE bank_id = ? AND type = "deposit"',
      [bankId]
    );
    const [withdraw] = await db.query(
      'SELECT SUM(amount) AS total_withdrawal FROM bank_transactions WHERE bank_id = ? AND type = "withdraw"',
      [bankId]
    );
  
    const total_deposit = deposit[0].total_deposit || 0;
    const total_withdrawal = withdraw[0].total_withdrawal || 0;
    const balance = total_deposit - total_withdrawal;
  
    return { total_deposit, total_withdrawal, balance };
  };