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

exports.getAccountSummaryByDateRange = async (start_date, end_date) => {
  const summaryQuery = `
    SELECT 
      b.id AS bank_id,
      b.name AS bank_name,
      b.account_number, -- ✅ added here
      COALESCE(SUM(CASE 
        WHEN bt.status = 'active' AND bt.type = 'deposit' AND bt.transaction_date BETWEEN ? AND ? 
        THEN bt.amount ELSE 0 END), 0) AS total_credit,
      COALESCE(SUM(CASE 
        WHEN bt.status = 'active' AND bt.type = 'withdraw' AND bt.transaction_date BETWEEN ? AND ? 
        THEN bt.amount ELSE 0 END), 0) AS total_debit,
      COALESCE(SUM(CASE 
        WHEN bt.status = 'active' AND bt.type = 'deposit' AND bt.transaction_date BETWEEN ? AND ? 
        THEN bt.amount ELSE 0 END), 0) -
      COALESCE(SUM(CASE 
        WHEN bt.status = 'active' AND bt.type = 'withdraw' AND bt.transaction_date BETWEEN ? AND ? 
        THEN bt.amount ELSE 0 END), 0) AS balance
      FROM banks b
      LEFT JOIN bank_transactions bt ON bt.bank_id = b.id
      WHERE b.is_deleted = 0
      GROUP BY b.id
      ORDER BY b.name;
  `;

  const lifetimeQuery = `
    SELECT 
      SUM(CASE WHEN status = 'active' AND type = 'deposit' THEN amount ELSE 0 END) AS total_credit,
      SUM(CASE WHEN status = 'active' AND type = 'withdraw' THEN amount ELSE 0 END) AS total_debit,
      SUM(CASE WHEN status = 'active' AND type = 'deposit' THEN amount ELSE 0 END) -
      SUM(CASE WHEN status = 'active' AND type = 'withdraw' THEN amount ELSE 0 END) AS balance
    FROM bank_transactions
  `;

  const [summaryRows] = await db.query(summaryQuery, [
    start_date, end_date,
    start_date, end_date,
    start_date, end_date,
    start_date, end_date
  ]);

  const [[lifetime]] = await db.query(lifetimeQuery);

  return {
    summary: summaryRows,
    lifetime: {
      total_credit: Number(lifetime.total_credit || 0),
      total_debit: Number(lifetime.total_debit || 0),
      balance: Number(lifetime.balance || 0)
    }
  };
};

exports.createTransaction = async (transaction) => {
  const {
    bank_id,
    type, // 'deposit', 'withdraw', or 'transfer'
    amount,
    description,
    related_bank_id = null,
    reference_type = null,
    reference_id = null
  } = transaction;

  if (!bank_id || !type || !amount) {
    throw new Error('Missing required fields: bank_id, type, amount');
  }

  const now = new Date();
   const formattedDate = now.toISOString().slice(0, 19).replace('T', ' ');

  if (type === 'transfer') {
    if (!related_bank_id) throw new Error('related_bank_id is required for transfer');

    // Withdraw from source bank
    await db.query(`
      INSERT INTO bank_transactions 
        (bank_id, type, amount, description, transaction_date, related_bank_id, reference_type, reference_id, status)
      VALUES (?, 'withdraw', ?, ?, ?, ?, 'transfer', ?, 'active')
    `, [bank_id, amount, `Transfer to Bank ${related_bank_id}`, formattedDate, related_bank_id, reference_id]);

    // Deposit to target bank
    await db.query(`
      INSERT INTO bank_transactions 
        (bank_id, type, amount, description, transaction_date, related_bank_id, reference_type, reference_id, status)
      VALUES (?, 'deposit', ?, ?, ?, ?, 'transfer', ?, 'active')
    `, [related_bank_id, amount, `Transfer from Bank ${bank_id}`, now, bank_id, reference_id]);

  } else {
    // Simple deposit or withdraw
    await db.query(`
      INSERT INTO bank_transactions 
        (bank_id, type, amount, description, transaction_date, related_bank_id, reference_type, reference_id, status)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'active')
    `, [bank_id, type, amount, description, now, related_bank_id, reference_type, reference_id]);
  }
};

exports.getFilteredTransactions = async (bank_id, start_date, end_date, type) => {
  let query = `
    SELECT bt.*, COALESCE(b.name, 'Deleted Bank') AS bank_name
    FROM bank_transactions bt
    LEFT JOIN banks b ON bt.bank_id = b.id AND b.is_deleted = 0
    WHERE 1=1
  `;
  const params = [];

  if (bank_id) {
    query += ' AND bt.bank_id = ?';
    params.push(bank_id);
  }

  if (start_date) {
    query += ' AND bt.transaction_date >= ?';
    params.push(start_date);
  }

  if (end_date) {
    query += ' AND bt.transaction_date <= ?';
    params.push(end_date);
  }

  if (type) {
    query += ' AND bt.type = ?';
    params.push(type);
  }
  query += ' ORDER BY bt.transaction_date DESC';

  const [rows] = await db.query(query, params);
  return rows;
};
exports.getTransactionById = async (id) => {
  const [rows] = await db.query('SELECT * FROM bank_transactions WHERE id = ?', [id]);
  return rows[0];
};

exports.getSaleDetails = async (saleId) => {
  const [rows] = await db.query('SELECT * FROM sales WHERE id = ?', [saleId]);
  return rows[0];
};

exports.getPurchaseDetails = async (purchaseId) => {
  const [rows] = await db.query('SELECT * FROM purchases WHERE id = ?', [purchaseId]);
  return rows[0];
};

exports.getTransferDetails = async (transferId) => {
  const [rows] = await db.query(`
    SELECT bt.*, 
           b1.name AS from_bank, 
           b2.name AS to_bank
    FROM bank_transactions bt
    LEFT JOIN banks b1 ON bt.bank_id = b1.id
    LEFT JOIN banks b2 ON bt.related_bank_id = b2.id
    WHERE bt.id = ? AND bt.type = 'transfer'
  `, [transferId]);
  return rows[0];
};

exports.getSaleDetails = async (saleId) => {
  const [[sale]] = await db.query('SELECT * FROM sales WHERE id = ?', [saleId]);

  if (!sale) return null;

  const [items] = await db.query(`
    SELECT si.*, i.name AS item_name, i.unit
    FROM sale_items si
    JOIN items i ON si.item_id = i.id
    WHERE si.sale_id = ?
  `, [saleId]);

  return { ...sale, items };
};

exports.getPurchaseDetails = async (purchaseId) => {
  const [[purchase]] = await db.query('SELECT * FROM purchases WHERE id = ?', [purchaseId]);

  if (!purchase) return null;

  const [items] = await db.query(`
    SELECT pi.*, i.name AS item_name, i.unit
    FROM purchase_items pi
    JOIN items i ON pi.item_id = i.id
    WHERE pi.purchase_id = ?
  `, [purchaseId]);

  return { ...purchase, items };
};

exports.getReversalHistoryByTransferId = async (transferId) => {
  const [rows] = await db.query(`
    SELECT 
      bt.id,
      bt.bank_id,
      b.name AS bank_name,
      bt.related_bank_id,
      bt.amount,
      bt.type,
      bt.description,
      bt.transaction_date
    FROM bank_transactions bt
    LEFT JOIN banks b ON bt.bank_id = b.id
    WHERE bt.reference_type = 'transfer_reversal' AND bt.reference_id = ?
    ORDER BY bt.transaction_date ASC
  `, [transferId]);

  return rows;
};
