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

exports.createBank = async (bank) => {
  const [result] = await db.query(
    'INSERT INTO banks (name, account_number, balance) VALUES (?, ?, ?)',
    [bank.name, bank.account_number, bank.balance || 0]
  );
  return result;
};

exports.getAllBanks = async () => {
  const [rows] = await db.query('SELECT * FROM banks WHERE is_deleted = 0');
  return rows;
};

exports.getBankById = async (bank_id) => {
  const [rows] = await db.query(`SELECT * FROM banks WHERE id = ? AND is_deleted = 0`, [bank_id]);
  return rows[0];
};

exports.updateBank = async (id, data) => {
  const [result] = await db.query(
    'UPDATE banks SET name = ?, account_number = ?, balance = ? WHERE id = ?',
    [data.name, data.account_number, data.balance, id]
  );
  return result;
};

exports.deleteBank = async (bank_id) => {
  await db.query(`UPDATE banks SET is_deleted = 1 WHERE id = ?`, [bank_id]);
};

exports.getBankTransfers = () => {
  return db.query(
    `SELECT bt.id, bt.created_at, bt.amount, bt.description,
            COALESCE(b1.name, 'Deleted Bank') AS from_bank,
            COALESCE(b2.name, 'Deleted Bank') AS to_bank,
            bt.bank_id AS from_bank_id,
            bt.related_bank_id
     FROM bank_transactions bt
     LEFT JOIN banks b1 ON bt.bank_id = b1.id AND b1.is_deleted = 0
     LEFT JOIN banks b2 ON bt.related_bank_id = b2.id AND b2.is_deleted = 0
     WHERE bt.type = 'transfer' AND bt.status = 'active'
     ORDER BY bt.created_at DESC`
  );
};

exports.reverseBankTransfer = async (id, conn) => {
  const [[tx]] = await conn.query('SELECT * FROM bank_transactions WHERE id = ?', [id]);

  if (!tx || !tx.related_bank_id || tx.status === 'reversed') return;

  const amount = Number(tx.amount);
  const bankA = tx.bank_id;
  const bankB = tx.related_bank_id;

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

  // ✅ Insert reversal: deposit back to original withdraw bank (bankA)
  await conn.query(`
    INSERT INTO bank_transactions 
    (bank_id, type, amount, description, transaction_date, related_bank_id, status, reference_type, reference_id)
    VALUES (?, 'deposit', ?, 'Reversal: returned from Bank B', ?, ?, 'active', 'transfer_reversal', ?)
  `, [bankA, amount, formattedDate, bankB, id]);

  // ✅ Insert reversal: withdraw back from original deposit bank (bankB)
  await conn.query(`
    INSERT INTO bank_transactions 
    (bank_id, type, amount, description, transaction_date, related_bank_id, status, reference_type, reference_id)
    VALUES (?, 'withdraw', ?, 'Reversal: sent back to Bank A', ?, ?, 'active', 'transfer_reversal', ?)
  `, [bankB, amount, now, bankA, id]);

  // ✅ Mark original transfer entries as reversed
  await conn.query('UPDATE bank_transactions SET status = ? WHERE id = ?', ['reversed', id]);

  await conn.query(`
    UPDATE bank_transactions
    SET status = ?
    WHERE bank_id = ? AND related_bank_id = ? AND amount = ? AND type = 'transfer' AND status = 'active'
  `, ['reversed', bankB, bankA, amount]);
};


exports.getBankCurrentBalances = async () => {
  const [rows] = await db.query(`
SELECT 
  bt.bank_id,
  COALESCE(b.name, 'Deleted Bank') AS bank_name,
  SUM(CASE WHEN bt.type = 'deposit' THEN bt.amount ELSE 0 END) AS total_credit,
  SUM(CASE WHEN bt.type = 'withdraw' THEN bt.amount ELSE 0 END) AS total_debit,
  SUM(CASE WHEN bt.type = 'deposit' THEN bt.amount ELSE 0 END) -
  SUM(CASE WHEN bt.type = 'withdraw' THEN bt.amount ELSE 0 END) AS balance
  FROM bank_transactions bt
  LEFT JOIN banks b ON bt.bank_id = b.id
  WHERE bt.status = 'active'
  GROUP BY bt.bank_id;
  `);

  return rows;
};

exports.getBanksWithLiveBalance = async () => {
  const [rows] = await db.query(`
  SELECT 
  b.id AS bank_id,
  b.name AS bank_name,
  b.account_number,
  b.created_at,
  COALESCE(SUM(CASE WHEN bt.type = 'deposit' THEN bt.amount ELSE 0 END), 0) AS total_deposit,
  COALESCE(SUM(CASE WHEN bt.type = 'withdraw' THEN bt.amount ELSE 0 END), 0) AS total_withdraw,
  COALESCE(SUM(CASE WHEN bt.type = 'deposit' THEN bt.amount ELSE 0 END), 0) -
  COALESCE(SUM(CASE WHEN bt.type = 'withdraw' THEN bt.amount ELSE 0 END), 0) AS balance
  FROM banks b
  LEFT JOIN bank_transactions bt ON bt.bank_id = b.id AND bt.status = 'active'
  WHERE b.is_deleted = 0
  GROUP BY b.id
  ORDER BY b.created_at DESC
  `);

  return rows;
};
