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

exports.getSalesReport = async (start, end) => {
  const [rows] = await db.query(
    `SELECT s.*, c.name AS customer_name, i.name AS item_name, si.quantity, si.price, si.total
     FROM sales s
     JOIN customers c ON s.customer_id = c.id
     JOIN sale_items si ON si.sale_id = s.id
     JOIN items i ON si.item_id = i.id
     WHERE s.sale_date BETWEEN ? AND ?`,
    [start, end]
  );
  return rows;
};

exports.getPurchaseReport = async (start, end) => {
  const [rows] = await db.query(
    `SELECT 
      p.id AS purchase_id,
      p.purchase_date,
      s.name AS supplier_name,
      i.name AS item_name,
      pi.quantity,
      pi.price,
      pi.total,
      p.total AS grand_total
    FROM purchases p
    JOIN suppliers s ON p.supplier_id = s.id
    JOIN purchase_items pi ON pi.purchase_id = p.id
    JOIN items i ON pi.item_id = i.id
    WHERE p.purchase_date BETWEEN ? AND ?`,
    [start, end]
  );
  return rows;
};

exports.getProfitLossReport = async (start = null, end = null) => {
  let query = `
    SELECT
      (SELECT IFNULL(SUM(credit), 0) FROM accounts_ledger WHERE type = 'sale'{{DATE_FILTER}}) AS total_sales,
      (SELECT IFNULL(SUM(debit), 0) FROM accounts_ledger WHERE type = 'purchase'{{DATE_FILTER}}) AS total_purchases,
      (SELECT IFNULL(SUM(debit), 0) FROM accounts_ledger WHERE type = 'expense'{{DATE_FILTER}}) AS total_expenses
  `;

  const params = [];
  let dateFilter = '';

  if (start && end) {
    dateFilter = ` AND created_at BETWEEN ? AND ?`;
    const startDate = `${start} 00:00:00`;
    const endDate = `${end} 23:59:59`;
    params.push(startDate, endDate, startDate, endDate, startDate, endDate);
  }

  query = query.replace(/{{DATE_FILTER}}/g, dateFilter);

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


exports.getExpenseReport = async (start, end) => {
  const [rows] = await db.query(
    `SELECT * FROM expenses WHERE expense_date BETWEEN ? AND ? ORDER BY expense_date DESC`,
    [start, end]
  );
  return rows;
};
exports.getItemSummary = async () => {
  const [rows] = await db.query(
    `SELECT id, name, sku, quantity, price, (quantity * price) AS total_value FROM items`
  );
  return rows;
};
exports.getCustomerSummary = async () => {
  const [rows] = await db.query(
    `SELECT c.id, c.name, c.phone, SUM(s.total) AS total_spent
       FROM sales s
       JOIN customers c ON s.customer_id = c.id
       GROUP BY s.customer_id`
  );
  return rows;
};
exports.getSupplierSummary = async () => {
  const [rows] = await db.query(
    `SELECT s.id, s.name, s.phone, SUM(p.total) AS total_supplied
       FROM purchases p
       JOIN suppliers s ON p.supplier_id = s.id
       GROUP BY p.supplier_id`
  );
  return rows;
};

exports.getSalesItemProfitSummary = (start, end, status) => {
  let sql = `SELECT 
        i.id AS item_id,
        i.name AS item_name,
        SUM(si.quantity) AS total_qty,
        SUM(si.total) AS total_sales,
        i.purchase_price,
        (SUM(si.total) / SUM(si.quantity)) AS avg_sale_price,
        ((SUM(si.total) / SUM(si.quantity)) - i.purchase_price) AS profit_per_unit,
        (SUM(si.total) - (i.purchase_price * SUM(si.quantity))) AS total_profit,
        s.status
      FROM sale_items si
      JOIN items i ON si.item_id = i.id
      JOIN sales s ON si.sale_id = s.id
      WHERE s.sale_date BETWEEN ? AND ?`;
  const params = [start, end];

  if (status && status !== 'all') {
    sql += " AND s.status = ?";
    params.push(status);
  }

  sql += " GROUP BY i.id, i.name, i.purchase_price, s.status ORDER BY profit_per_unit DESC";

  return db.query(sql, params);
};

exports.getUnsoldItems = (start, end) => {
  return db.query(
    `
    SELECT 
      i.id,
      i.name AS item_name,
      i.quantity,
      i.purchase_price_last,
      i.sales_price AS sales_price,
      COALESCE((
        SELECT 
          ROUND(SUM(pi.price * pi.quantity) / SUM(pi.quantity), 2)
        FROM purchase_items pi
        JOIN purchases p ON pi.purchase_id = p.id
        WHERE pi.item_id = i.id AND p.status = 'Received'
      ), 0) AS purchase_price_avg
    FROM items i
    WHERE i.id NOT IN (
      SELECT DISTINCT si.item_id
      FROM sale_items si
      JOIN sales s ON si.sale_id = s.id
      WHERE s.sale_date BETWEEN ? AND ?
        AND s.status = 'final'
        AND s.deleted_at IS NULL
    )
    ORDER BY i.name
    `,
    [`${start} 00:00:00`, `${end} 23:59:59`]
  );
};



exports.getInvoiceProfitReport = async (start = null, end = null) => {
  let query = `
    SELECT 
      s.id AS sale_id,
      s.sale_date,
      c.name AS customer_name,
      s.total AS sale_total,
      s.paid_amount,
      SUM(si.quantity * i.purchase_price_last) AS total_cost,
      (s.total - SUM(si.quantity * i.purchase_price_last)) AS profit
    FROM sales s
    JOIN sale_items si ON s.id = si.sale_id
    JOIN items i ON si.item_id = i.id
    LEFT JOIN customers c ON s.customer_id = c.id
    WHERE s.status = 'final' AND s.is_deleted = 0
  `;

  const params = [];

  if (start && end) {
    query += ` AND s.sale_date BETWEEN ? AND ?`;
    params.push(`${start} 00:00:00`, `${end} 23:59:59`);
  }

  query += `
    GROUP BY s.id
    ORDER BY s.sale_date DESC
  `;

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

exports.fetchItemValuationReport = async ({ category, brand, item }) => {
  let sql = `
    SELECT 
      i.id,
      i.sku,
      i.name AS item_name,
      br.name AS brand,
      cat.name AS category,
      i.sales_price,

      (
        SELECT pi.price
        FROM purchase_items pi
        JOIN purchases p ON p.id = pi.purchase_id AND p.deleted_at IS NULL
        WHERE pi.item_id = i.id
        ORDER BY p.purchase_date DESC, pi.id DESC
        LIMIT 1
      ) AS latest_purchase_price,

      GROUP_CONCAT(CONCAT(isz.size, ':', isz.quantity) ORDER BY isz.size SEPARATOR ', ') AS sizes,
      SUM(IFNULL(isz.quantity, 0)) AS current_qty,

      IFNULL((
        SELECT SUM(pi.quantity)
        FROM purchase_items pi
        JOIN purchases p ON p.id = pi.purchase_id AND p.deleted_at IS NULL
        WHERE pi.item_id = i.id
      ), 0) AS purchases_qty,

      IFNULL((
        SELECT SUM(si.quantity)
        FROM sale_items si
        JOIN sales s ON s.id = si.sale_id AND s.deleted_at IS NULL
        WHERE si.item_id = i.id
      ), 0) AS sales_qty,

      IFNULL((
        SELECT SUM(pi.quantity * pi.price)
        FROM purchase_items pi
        JOIN purchases p ON p.id = pi.purchase_id AND p.deleted_at IS NULL
        WHERE pi.item_id = i.id
      ), 0) AS total_purchase_value,

      IFNULL((
        SELECT SUM(si.quantity * si.price)
        FROM sale_items si
        JOIN sales s ON s.id = si.sale_id AND s.deleted_at IS NULL
        WHERE si.item_id = i.id
      ), 0) AS total_sales_value

    FROM items i
    LEFT JOIN brands br ON i.brand_id = br.id
    LEFT JOIN categories cat ON i.category_id = cat.id
    LEFT JOIN item_sizes isz ON i.id = isz.item_id
    WHERE 1 = 1
  `;

  const params = [];

  if (category) {
    sql += ' AND i.category_id = ?';
    params.push(category);
  }

  if (brand) {
    sql += ' AND i.brand_id = ?';
    params.push(brand);
  }

  if (item) {
    sql += ' AND i.id = ?';
    params.push(item);
  }

  sql += `
    GROUP BY i.id, i.sku, i.name, br.name, cat.name, i.sales_price
    ORDER BY i.name ASC
  `;

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