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

// Create Expense
exports.createExpense = async ({
  category_id,
  amount,
  note,
  bank_id,
  created_by,
  reference_type = null,
  reference_id = null
}) => {
  const [result] = await db.query(
    'INSERT INTO expenses (category, amount, note, bank_id, created_by, reference_type, reference_id) VALUES (?, ?, ?, ?, ?, ?, ?)',
    [category_id, amount, note, bank_id, created_by, reference_type, reference_id]
  );
  return result.insertId;
};


// Update Expense
exports.updateExpense = async ({ expense_id, category_id, amount, note, bank_id }) => {
  await db.query(
    `UPDATE expenses SET category = ?, amount = ?, note = ?, bank_id = ? WHERE id = ?`,
    [category_id, amount, note, bank_id, expense_id]
  );
};

// Get Expense by ID
exports.getExpenseById = async (expense_id) => {
  const [rows] = await db.query('SELECT * FROM expenses WHERE id = ?', [expense_id]);
  return rows[0];
};
exports.getSingleExpenseById = async (expense_id) => {
  const [rows] = await db.query(`
      SELECT * FROM expenses
      WHERE is_deleted = 0 AND id = ?
      LIMIT 1
  `, [expense_id]);

  return rows[0];  // Return single expense or undefined if not found
};

// Ledger Entry
exports.addLedgerEntry = async ({ reference, amount, bank_id, description }) => {
  await db.query(
    `INSERT INTO accounts_ledger (type, reference, debit, description, bank_id) 
       VALUES ('expense', ?, ?, ?, ?)`,
    [reference, amount, description, bank_id]
  );
};

// Bank Transaction
exports.addBankTransaction = async ({
  bank_id,
  amount,
  description,
  reference_type = null,
  reference_id = null
}) => {
  await db.query(
    `INSERT INTO bank_transactions 
     (bank_id, type, amount, description, reference_type, reference_id, transaction_date, status) 
     VALUES (?, 'withdraw', ?, ?, ?, ?, NOW(), 'active')`,
    [bank_id, amount, description, reference_type, reference_id]
  );
};

// Update Bank Balance
exports.updateBankBalance = async (amount, bank_id) => {
  await db.query('UPDATE banks SET balance = balance - ? WHERE id = ?', [amount, bank_id]);
};

// Reverse Financials
exports.reverseFinancials = async ({ reference, amount, bank_id }) => {
  await db.query('DELETE FROM accounts_ledger WHERE reference = ?', [reference]);
  await db.query('DELETE FROM bank_transactions WHERE description LIKE ?', [`%${reference}%`]);
  await db.query('UPDATE banks SET balance = balance + ? WHERE id = ?', [amount, bank_id]);
};
// Get All Expenses with Category & Bank Info
exports.getExpenses = async ({ startDate, endDate, category_id } = {}) => {
  let query = `
    SELECT 
      e.id, 
      ec.name AS category_name, 
      e.amount, 
      e.note, 
      b.name AS bank_name, 
      e.expense_date, 
      e.created_at
    FROM expenses e
    LEFT JOIN expense_categories ec ON e.category = ec.id
    LEFT JOIN banks b ON e.bank_id = b.id
    WHERE e.is_deleted = 0
  `;

  const params = [];

  if (startDate) {
    query += ` AND e.expense_date >= ?`;
    params.push(`${startDate} 00:00:00`);
  }

  if (endDate) {
    query += ` AND e.expense_date <= ?`;
    params.push(`${endDate} 23:59:59`);
  }

  // ✅ Only apply this if category_id is provided AND not empty/null
  if (category_id !== undefined && category_id !== null && category_id !== '') {
    query += ` AND e.category = ?`;
    params.push(category_id);
  }

  query += ` ORDER BY e.expense_date DESC`;

  const [rows] = await db.query(query, params);
  return rows;
};




// Soft Delete Expense
exports.deleteExpense = async (expense_id) => {
  await db.query(`UPDATE expenses SET is_deleted = 1 WHERE id = ?`, [expense_id]);
};

// Reverse Financials for Delete
exports.reverseExpenseFinancials = async (expense) => {
  const reference = `EXP-${expense.id}`;
  await db.query('DELETE FROM accounts_ledger WHERE reference = ?', [reference]);
  await db.query('DELETE FROM bank_transactions WHERE description LIKE ?', [`%${reference}%`]);
  await db.query('UPDATE banks SET balance = balance + ? WHERE id = ?', [expense.amount, expense.bank_id]);
};

exports.updateCategory = async ({ category_id, name, description }) => {
  await db.query(
    `UPDATE expense_categories SET name = ?, description = ? WHERE id = ?`,
    [name, description, category_id]
  );
};

// Get Category by ID
exports.getCategoryById = async (category_id) => {
  const [rows] = await db.query('SELECT * FROM expense_categories WHERE id = ?', [category_id]);
  return rows[0];  // Return single category or undefined
};