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

// Create Customer
exports.createCustomer = async (customer) => {
  const [result] = await db.query(
    `INSERT INTO customers (name, email, phone, address, total_paid, sales_due)
       VALUES (?, ?, ?, ?, 0, 0)`,
    [customer.name, customer.email, customer.phone, customer.address]
  );
  return result.insertId;
};

// Update Customer Totals
exports.updateCustomerTotals = async (customer_id) => {
  // Calculate total sales
  const [[salesData]] = await db.query(`
      SELECT IFNULL(SUM(total), 0) AS total_sales
      FROM sales
      WHERE customer_id = ? AND is_deleted = 0
  `, [customer_id]);

  // Calculate total paid
  const [[paidData]] = await db.query(`
      SELECT IFNULL(SUM(amount), 0) AS total_paid
      FROM bank_transactions
      WHERE reference_type = 'sale' AND reference_id IN (
          SELECT id FROM sales WHERE customer_id = ? AND is_deleted = 0
      )
  `, [customer_id]);

  const sales_due = salesData.total_sales - paidData.total_paid;

  // Update customer table
  await db.query(`
      UPDATE customers SET total_paid = ?, sales_due = ?
      WHERE id = ?
  `, [paidData.total_paid, sales_due, customer_id]);
};

// Get Customer With Payment History
exports.getCustomerDetails = async (customer_id) => {
  const [[customer]] = await db.query('SELECT * FROM customers WHERE id = ?', [customer_id]);
  if (!customer) return null;

  const [paymentHistory] = await db.query(`
      SELECT id, bank_id, type, amount, description, created_at
      FROM bank_transactions
      WHERE reference_type = 'sale' AND reference_id IN (
          SELECT id FROM sales WHERE customer_id = ? AND is_deleted = 0
      )
      ORDER BY created_at DESC
  `, [customer_id]);

  return {
    ...customer,
    payment_history: paymentHistory
  };
};

// Update Customer
exports.updateCustomer = async (customer_id, customerData) => {
  await db.query(`
      UPDATE customers 
      SET name = ?, email = ?, phone = ?, address = ?
      WHERE id = ? AND is_deleted = 0
  `, [
    customerData.name,
    customerData.email,
    customerData.phone,
    customerData.address,
    customer_id
  ]);

  // 🔸 Auto-update financial totals after customer info update
  await exports.updateCustomerTotals(customer_id);
};

// Soft Delete Customer
exports.deleteCustomer = async (customer_id) => {
  await db.query(`
      UPDATE customers 
      SET is_deleted = 1 
      WHERE id = ?
  `, [customer_id]);
};

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

  // Optional search filter
  if (search) {
    query += ` AND (name LIKE ? OR email LIKE ? OR phone 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 Customer by ID (for validation)
exports.getCustomerById = async (customer_id) => {
  const [rows] = await db.query(`
      SELECT * FROM customers 
      WHERE id = ? AND is_deleted = 0
  `, [customer_id]);
  return rows[0];
};
exports.getCustomerWithFinancials = async (customer_id) => {
  const [[customer]] = await db.query(`
      SELECT * FROM customers WHERE id = ? AND is_deleted = 0
  `, [customer_id]);

  if (!customer) return null;

  const [paymentHistory] = await db.query(`
      SELECT id, bank_id, type, amount, description, created_at
      FROM bank_transactions
      WHERE reference_type = 'sale' AND reference_id IN (
          SELECT id FROM sales WHERE customer_id = ? AND is_deleted = 0
      )
      ORDER BY created_at DESC
  `, [customer_id]);

  return {
    ...customer,
    payment_history: paymentHistory
  };
};

exports.getCustomerById = async (customer_id) => {
  const [rows] = await db.query(`
      SELECT id, name, email, phone, address, total_paid, sales_due, created_at
      FROM customers
      WHERE id = ? AND is_deleted = 0
  `, [customer_id]);

  return rows[0];  // Return single customer or undefined
};

exports.getCustomerPaymentHistory = async (customer_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 = 'sale'
        AND bt.reference_id IN (
            SELECT id FROM sales WHERE customer_id = ? AND is_deleted = 0
        )
      ORDER BY bt.created_at DESC
  `, [customer_id]);

  return payments;
};