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

exports.createSupplier = async (supplier) => {
  const [result] = await db.query(`
      INSERT INTO suppliers (name, email, phone, address, company, total_paid, purchase_due)
      VALUES (?, ?, ?, ?, ?, 0, 0)
  `, [supplier.name, supplier.email, supplier.phone, supplier.address, supplier.company]);
  return result.insertId;
};

// Update Supplier
exports.updateSupplier = async (supplier_id, data) => {
  await db.query(`
      UPDATE suppliers
      SET name = ?, email = ?, phone = ?, address = ?, company = ?
      WHERE id = ? AND is_deleted = 0
  `, [data.name, data.email, data.phone, data.address, data.company, supplier_id]);

  await exports.updateSupplierTotals(supplier_id);
};

// Soft Delete Supplier
exports.deleteSupplier = async (supplier_id) => {
  await db.query(`UPDATE suppliers SET is_deleted = 1 WHERE id = ?`, [supplier_id]);
};

exports.listSuppliers = async (search = '') => {
  let query = `
      SELECT id, name, email, phone, company, address, total_paid, purchase_due, created_at
      FROM suppliers
      WHERE is_deleted = 0
  `;
  const params = [];

  if (search) {
      query += ` AND (name LIKE ? OR email LIKE ? OR phone LIKE ? OR company LIKE ?)`;
      const searchTerm = `%${search}%`;
      params.push(searchTerm, searchTerm, searchTerm, searchTerm);
  }

  query += ` ORDER BY created_at DESC`;

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

// Get Supplier By ID
exports.getSupplierById = async (supplier_id) => {
  const [rows] = await db.query(`
      SELECT * FROM suppliers WHERE id = ? AND is_deleted = 0
  `, [supplier_id]);
  return rows[0];
};

exports.getSupplierByIdWithTotals = async (supplier_id) => {
  const [rows] = await db.query(`
      SELECT id, name, email, phone, address, company, total_paid, purchase_due, created_at
      FROM suppliers
      WHERE id = ? AND is_deleted = 0
  `, [supplier_id]);
  return rows[0];
};
// Auto-Update Supplier Totals
exports.updateSupplierTotals = async (supplier_id) => {
  const [[purchaseData]] = await db.query(`
      SELECT IFNULL(SUM(total), 0) AS total_purchases
      FROM purchases
      WHERE supplier_id = ? AND is_deleted = 0
  `, [supplier_id]);

  const [[paidData]] = await db.query(`
      SELECT IFNULL(SUM(amount), 0) AS total_paid
      FROM bank_transactions
      WHERE reference_type = 'purchase'
      AND reference_id IN (
          SELECT id FROM purchases WHERE supplier_id = ? AND is_deleted = 0
      )
  `, [supplier_id]);

  const purchase_due = purchaseData.total_purchases - paidData.total_paid;

  await db.query(`
      UPDATE suppliers SET total_paid = ?, purchase_due = ? WHERE id = ?
  `, [paidData.total_paid, purchase_due, supplier_id]);
};
exports.getSupplierPaymentHistory = async (supplier_id) => {
  const [payments] = await db.query(`
      SELECT 
          bt.id,
          bt.bank_id,
          b.name AS bank_name,
          bt.type,
          bt.amount,
          bt.description,
          bt.created_at
      FROM bank_transactions bt
      JOIN banks b ON bt.bank_id = b.id
      WHERE bt.reference_type = 'purchase'
        AND bt.reference_id IN (
            SELECT id FROM purchases WHERE supplier_id = ? AND is_deleted = 0
        )
      ORDER BY bt.created_at DESC
  `, [supplier_id]);

  return payments;
};