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

exports.insertPurchase = (conn, supplier_id, total, bank_id) => {
  return conn.query(
    'INSERT INTO purchases (supplier_id, total, bank_id) VALUES (?, ?, ?)',
    [supplier_id, total, bank_id]
  );
};

exports.insertPurchaseItem = (conn, purchase_id, item_id, quantity, price, total) => {
  return conn.query(
    'INSERT INTO purchase_items (purchase_id, item_id, quantity, price, total) VALUES (?, ?, ?, ?, ?)',
    [purchase_id, item_id, quantity, price, total]
  );
};
exports.getFilteredPurchases = (filters) => {
  let sql = `
    SELECT s.*, a.name AS user_name, c.name AS supplier_name, b.name AS bank_name
    FROM purchases s
    LEFT JOIN users a ON s.created_by = a.id
    LEFT JOIN suppliers c ON s.supplier_id = c.id
    LEFT JOIN banks b ON s.bank_id = b.id
    WHERE 1=1
  `;
  const params = [];

  if (filters.start_date) {
    sql += ' AND s.purchase_date >= ?';
    params.push(filters.start_date);
  }

  if (filters.end_date) {
    sql += ' AND s.purchase_date <= ?';
    params.push(filters.end_date);
  }

  if (filters.payment_status) {
    sql += ' AND s.payment_status = ?';
    params.push(filters.payment_status);
  }

  if (filters.status) {
    sql += ' AND s.status = ?';
    params.push(filters.status);
  }

  if (filters.supplier_id) {
    sql += ' AND s.customer_id = ?';
    params.push(filters.supplier_id);
  }

  sql += ' ORDER BY s.purchase_date DESC';

  return db.query(sql, params);
};
exports.incrementStock = (conn, item_id, quantity) => {
  return conn.query(
    'UPDATE items SET qty = qty + ? WHERE id = ?',
    [quantity, item_id]
  );
};

exports.logLedger = (conn, reference, total, bank_id) => {
  return conn.query(
    'INSERT INTO accounts_ledger (type, reference, debit, description, bank_id) VALUES (?, ?, ?, ?, ?)',
    ['purchase', reference, total, 'Auto debit from purchase', bank_id]
  );
};

exports.logBankTransaction = (conn, reference, total, bank_id) => {
  return conn.query(
    'INSERT INTO bank_transactions (bank_id, type, amount, description) VALUES (?, ?, ?, ?)',
    [bank_id, 'withdraw', total, `Purchase paid: ${reference}`]
  );
};

exports.updateBankBalance = (conn, bank_id, change) => {
  return conn.query(
    'UPDATE banks SET balance = balance + ? WHERE id = ?',
    [change, bank_id]
  );
};

exports.getAll = () =>
  db.query(
    `SELECT p.id, p.total, p.purchase_date, s.name AS supplier_name
     FROM purchases p
     JOIN suppliers s ON p.supplier_id = s.id
     WHERE p.deleted_at IS NULL
     ORDER BY p.purchase_date DESC`
  );
exports.getAllPurchaseItemsWithSizes = () =>
  db.query(
    `SELECT pi.purchase_id, pi.item_id, i.name AS item_name, pi.size, pi.quantity, pi.price, pi.discount, pi.total
     FROM purchase_items pi
     JOIN items i ON pi.item_id = i.id`
  );
exports.getById = (id) =>
  db.query(`SELECT * FROM purchases WHERE id = ?`, [id]);

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

exports.getItems = (purchase_id) =>
  db.query(
    `SELECT pi.*, i.name FROM purchase_items pi
     JOIN items i ON pi.item_id = i.id
     WHERE pi.purchase_id = ?`,
    [purchase_id]
  );

exports.updateHeader = (id, supplier_id, bank_id, total) =>
  db.query('UPDATE purchases SET supplier_id = ?, bank_id = ?, total = ? WHERE id = ?', [
    supplier_id, bank_id, total, id
  ]);

exports.clearItems = (purchase_id) =>
  db.query('DELETE FROM purchase_items WHERE purchase_id = ?', [purchase_id]);

exports.addItem = (purchase_id, item_id, quantity, price, total) =>
  db.query(
    'INSERT INTO purchase_items (purchase_id, item_id, quantity, price, total) VALUES (?, ?, ?, ?, ?)',
    [purchase_id, item_id, quantity, price, total]
  );

exports.softDelete = (id) =>
  db.query('UPDATE purchases SET deleted_at = NOW() WHERE id = ?', [id]);

exports.restore = (id) =>
  db.query('UPDATE purchases SET deleted_at = NULL WHERE id = ?', [id]);

exports.getArchived = () =>
  db.query(
    `SELECT p.id, p.total, p.purchase_date, s.name AS supplier_name, p.deleted_at
     FROM purchases p
     JOIN suppliers s ON p.supplier_id = s.id
     WHERE p.deleted_at IS NOT NULL
     ORDER BY p.deleted_at DESC`
  );
