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

// Get all non-deleted sales
// === Core Sale Queries ===
exports.getAll = () => db.query(`
 SELECT 
    s.*, 
    u1.id AS created_by_id,
    u1.name AS created_by_name,
    u2.id AS modified_by_id,
    u2.name AS modified_by_name,
    c.name AS customer_name,
    b.name AS bank_name
  FROM sales s
  LEFT JOIN users u1 ON s.created_by = u1.id
  LEFT JOIN users u2 ON s.modified_by = u2.id
  LEFT JOIN customers c ON s.customer_id = c.id
  LEFT JOIN banks b ON s.bank_id = b.id
  WHERE s.deleted_at IS NULL
  ORDER BY s.sale_date DESC
`);

exports.getFilteredSales = (filters) => {
  let sql = `
        SELECT 
      s.*, 
      u1.id AS created_by_id,
      u1.name AS created_by_name,
      u2.id AS modified_by_id,
      u2.name AS modified_by_name,
      c.name AS customer_name,
      b.name AS bank_name
    FROM sales s
    LEFT JOIN users u1 ON s.created_by = u1.id
    LEFT JOIN users u2 ON s.modified_by = u2.id
    LEFT JOIN customers c ON s.customer_id = c.id
    LEFT JOIN banks b ON s.bank_id = b.id
    WHERE s.deleted_at IS NULL
  `;
  const params = [];

  if (filters.start_date) {
    sql += ' AND s.sale_date >= ?';
    params.push(filters.start_date);
  }
  if (filters.end_date) {
    sql += ' AND s.sale_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.customer_id) {
    sql += ' AND s.customer_id = ?';
    params.push(filters.customer_id);
  }

  sql += ' ORDER BY s.sale_date DESC';
  return db.query(sql, params);
};

exports.getSaleById = (conn, id) => {
  const saleId = Number(id);
  if (isNaN(saleId)) throw new Error("Invalid sale ID passed to getSaleById");
  return conn.query('SELECT * FROM sales WHERE id = ? AND deleted_at IS NULL', [saleId]);
};

exports.getOldSaleItems = (conn, sale_id) => {
  return conn.query('SELECT * FROM sale_items WHERE sale_id = ?', [sale_id]);
};

exports.getItemsBySaleId = async (sale_id) => {
  const [rows] = await db.query(`
    SELECT si.item_id, si.size, si.quantity, si.price, si.discount, si.total,
           si.discount_type, i.name
    FROM sale_items si
    JOIN items i ON si.item_id = i.id
    WHERE si.sale_id = ?
  `, [sale_id]);

  const grouped = {};
  for (const row of rows) {
    const key = `${row.item_id}_${row.price}_${row.discount}`;
    if (!grouped[key]) {
      grouped[key] = {
        item_id: row.item_id,
        name: row.name,
        price: Number(row.price),
        discount: Number(row.discount),
        discount_type: row.discount_type || 'flat',
        total: 0,
        sizes: []
      };
    }
    grouped[key].sizes.push({ size: row.size, quantity: Number(row.quantity) });
    grouped[key].total += Number(row.total);
  }
  return [Object.values(grouped)];
};

exports.insertSale = (conn, ...args) => {
  const sql = `
    INSERT INTO sales (
      customer_id, sale_date, bank_id, total, status,
      paid_amount, payment_status, reference, note,
      discount, other_charges, created_by
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
  return conn.query(sql, args);
};

exports.updateSaleInfo = (conn, data) => {
  const {
    sale_id, customer_id, sale_date, bank_id, total,
    status, reference, note, discount, discount_type,
    other_charges, modified_by
  } = data;

  return conn.query(`
    UPDATE sales SET
      customer_id = ?, sale_date = ?, bank_id = ?, total = ?, status = ?,
      reference = ?, note = ?, discount = ?, discount_type = ?,
      other_charges = ?, modified_by = ?
    WHERE id = ?`,
    [customer_id, sale_date, bank_id, total, status, reference, note, discount, discount_type, other_charges, modified_by, sale_id]
  );
};

exports.insertSaleItem = (conn, item) => {
  const {
    sale_id, item_id, size, quantity, price,
    discount_type, discount, total
  } = item;
  return conn.query(`
    INSERT INTO sale_items
    (sale_id, item_id, size, quantity, price, discount_type, discount, total)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
    [sale_id, item_id, size, quantity, price, discount_type, discount, total]
  );
};

exports.restoreItemSize = (conn, quantity, item_id, size) => {
  return conn.query(
    'UPDATE item_sizes SET quantity = quantity + ? WHERE item_id = ? AND size = ?',
    [quantity, item_id, size]
  );
};

exports.reduceItemSize = (conn, quantity, item_id, size) => {
  return conn.query(
    'UPDATE item_sizes SET quantity = quantity - ? WHERE item_id = ? AND size = ?',
    [quantity, item_id, size]
  );
};

exports.clearLedger = (conn, reference) => {
  return conn.query('DELETE FROM accounts_ledger WHERE reference = ?', [reference]);
};

exports.clearSaleItems = (conn, sale_id) => {
  return conn.query('DELETE FROM sale_items WHERE sale_id = ?', [sale_id]);
};

exports.insertBankTransaction = (conn, { bank_id, amount, description, reference_type, reference_id }) => {
  return conn.query(`
    INSERT INTO bank_transactions (bank_id, type, amount, description, reference_type, reference_id)
    VALUES (?, 'deposit', ?, ?, ?, ?)`,
    [bank_id, amount, description, reference_type, reference_id]
  );
};

exports.getTotalPaid = async (conn, sale_id) => {
  const [[{ total_paid }]] = await conn.query(`
    SELECT IFNULL(SUM(amount), 0) AS total_paid
    FROM bank_transactions
    WHERE reference_type = 'sale' AND reference_id = ?`, [sale_id]);
  return total_paid;
};

exports.updatePaymentStatus = (conn, sale_id, paid_amount, status) => {
  return conn.query(
    'UPDATE sales SET paid_amount = ?, payment_status = ? WHERE id = ?',
    [paid_amount, status, sale_id]
  );
};

exports.insertLedgerEntry = (conn, { reference, amount, bank_id }) => {
  return conn.query(
    `INSERT INTO accounts_ledger (type, reference, credit, description, bank_id)
     VALUES ("sale", ?, ?, "Updated sale ledger entry", ?)`,
    [reference, amount, bank_id]
  );
};

exports.insertAuditLog = (conn, { sale_id, changed_by, old_data, new_data }) => {
  return conn.query(
    `INSERT INTO sale_audit_log (sale_id, changed_by, old_data, new_data, changed_at)
     VALUES (?, ?, ?, ?, NOW())`,
    [sale_id, changed_by, JSON.stringify(old_data), JSON.stringify(new_data)]
  );
};

exports.autoUpdateCustomerTotals = async (sale_id) => Promise.resolve();

// Get a specific sale
exports.getById = (id) =>
  db.query('SELECT * FROM sales WHERE id = ? AND deleted_at IS NULL', [id]);

// Get items for a sale
exports.getItemsBySaleId = async (sale_id) => {
  const [rows] = await db.query(`
    SELECT si.item_id, si.size, si.quantity, si.price, si.discount, si.total, i.name, si.discount_type
    FROM sale_items si
    JOIN items i ON si.item_id = i.id
    WHERE si.sale_id = ?
  `, [sale_id]);

  // Group rows by item_id + price + discount to merge sizes
  const grouped = {};
  for (const row of rows) {
    const key = `${row.item_id}_${row.price}_${row.discount}`;
    if (!grouped[key]) {
      grouped[key] = {
        item_id: row.item_id,
        name: row.name,
        price: Number(row.price),
        discount: Number(row.discount),
        discount_type: row.discount_type || 'flat',
        total: 0,
        sizes: []
      };
    }
    grouped[key].sizes.push({
      size: row.size,
      quantity: Number(row.quantity)
    });
    grouped[key].total += Number(row.total);
  }

  return [Object.values(grouped)];
};



// Insert a new sale
exports.insertSale = (
  conn,
  customer_id,
  sale_date,
  bank_id,
  total,
  status,
  paid_amount,
  payment_status,
  reference,
  note,
  discount,
  other_charges,
  created_by
) => {
  const sql = `
    INSERT INTO sales (
      customer_id, sale_date, bank_id, total, status, paid_amount,
      payment_status, reference, note, discount, other_charges, created_by
    )
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `;

  const values = [
    customer_id, sale_date, bank_id, total, status,
    paid_amount, payment_status, reference, note,
    discount, other_charges, created_by
  ];

  return conn.query(sql, values);
};

exports.insertSaleItems = async (conn, sale_id, items, saleStatus) => {
  for (const item of items) {
    const itemId = Number(item.item_id);
    const sizes = item.sizes || [{ size: '', quantity: item.quantity }];

    for (const s of sizes) {
      const qty = Number(s.quantity || 0);
      const total = qty * item.price;

      await conn.query(
        'INSERT INTO sale_items (sale_id, item_id, size, quantity, price, discount, total) VALUES (?, ?, ?, ?, ?, ?, ?)',
        [sale_id, itemId, s.size, qty, item.price, item.discount || 0, total]
      );

      if (saleStatus === 'final') {
        const [exists] = await conn.query(
          'SELECT quantity FROM item_sizes WHERE item_id = ? AND size = ?',
          [itemId, s.size]
        );

        if (exists.length > 0) {
          await conn.query(
            'UPDATE item_sizes SET quantity = quantity - ? WHERE item_id = ? AND size = ?',
            [qty, itemId, s.size]
          );
        }
      }
    }
  }
};

exports.insertLedgerAndTransaction = async (conn, sale_id, bank_id, paid_amount) => {
  const refCode = `SALE-${sale_id}`;

  await conn.query(
    'INSERT INTO accounts_ledger (type, reference, credit, description, bank_id) VALUES (?, ?, ?, ?, ?)',
    ['sale', refCode, Number(paid_amount), 'Auto credit from sale', Number(bank_id)]
  );

  await conn.query(
    'INSERT INTO bank_transactions (bank_id, type, amount, description, reference_type, reference_id) VALUES (?, ?, ?, ?, ?, ?)',
    [Number(bank_id), 'deposit', Number(paid_amount), `Sale received: ${refCode}`, 'sale', sale_id]
  );
};





// Update sale details
exports.updateSale = async (
  conn,
  {
    id,
    customer_id,
    sale_date,
    bank_id,
    total,
    status,
    paid_amount,
    payment_status,
    reference,
    note,
    discount,
    discount_type,
    other_charges
  }
) => {
  // Basic validation
  if (isNaN(total)) {
    throw new Error("Total cannot be NaN.");
  }
  if (isNaN(other_charges)) {
    throw new Error("Other charges cannot be NaN.");
  }

  const query = `
    UPDATE sales SET
      customer_id = ?, 
      sale_date = ?, 
      bank_id = ?, 
      total = ?, 
      status = ?,
      paid_amount = ?, 
      payment_status = ?, 
      reference = ?, 
      note = ?,
      discount = ?, 
      discount_type = ?, 
      other_charges = ?
    WHERE id = ?
  `;

  const params = [
    customer_id,
    sale_date,
    bank_id,
    total,
    status,
    paid_amount,
    payment_status,
    reference,
    note,
    discount,
    discount_type,
    other_charges,
    id
  ];

  return conn.query(query, params);
};


// Batch insert for sale items
exports.batchAddSaleItems = async (conn, values) => {
  const sql = `
    INSERT INTO sale_items 
    (sale_id, item_id, quantity, price, discount_type, discount, total) 
    VALUES ?
  `;
  await conn.query(sql, [values]);
};

// Add one sale item
exports.addSaleItem = (sale_id, item_id, quantity, price, total) =>
  db.query(
    'INSERT INTO sale_items (sale_id, item_id, quantity, price, total) VALUES (?, ?, ?, ?, ?)',
    [sale_id, item_id, quantity, price, total]
  );

// Soft delete a sale
exports.softDelete = (id) =>
  db.query('UPDATE sales SET deleted_at = NOW() WHERE id = ?', [id]);

// Restore a soft-deleted sale
exports.restore = (id) =>
  db.query('UPDATE sales SET deleted_at = NULL WHERE id = ?', [id]);

// Get archived (soft-deleted) sales
exports.getArchived = () =>
  db.query('SELECT * FROM sales WHERE deleted_at IS NOT NULL ORDER BY deleted_at DESC');