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

async function invoiceReport(from, to) {
  const [rows] = await db.query(`
    SELECT invoice_number, invoice_date, customer_name, customer_gstin, customer_state_name, customer_state_code,
      taxable_amount, cgst_amount, sgst_amount, igst_amount, round_off, grand_total, status
    FROM invoices
    WHERE invoice_date BETWEEN ? AND ?
    ORDER BY invoice_date ASC, id ASC
  `, [from, to]);
  return rows;
}

async function gstSummary(from, to) {
  const [rows] = await db.query(`
    SELECT i.invoice_number, i.invoice_date, i.customer_name, i.customer_gstin, i.customer_state_name, i.customer_state_code,
      it.hsn_code, it.description, it.paper_type, it.quantity, it.taxable_amount,
      it.cgst_percent, it.cgst_amount, it.sgst_percent, it.sgst_amount, it.igst_percent, it.igst_amount, it.total_amount
    FROM invoices i
    INNER JOIN invoice_items it ON it.invoice_id = i.id
    WHERE i.status = 'active' AND i.invoice_date BETWEEN ? AND ?
    ORDER BY i.invoice_date ASC, i.id ASC, it.id ASC
  `, [from, to]);
  return rows;
}

async function hsnSummary(from, to) {
  const [rows] = await db.query(`
    SELECT it.hsn_code, it.description,
      SUM(it.quantity) AS total_quantity,
      SUM(it.taxable_amount) AS taxable_amount,
      SUM(it.cgst_amount) AS cgst_amount,
      SUM(it.sgst_amount) AS sgst_amount,
      SUM(it.igst_amount) AS igst_amount,
      SUM(it.total_amount) AS total_amount
    FROM invoices i
    INNER JOIN invoice_items it ON it.invoice_id = i.id
    WHERE i.status = 'active' AND i.invoice_date BETWEEN ? AND ?
    GROUP BY it.hsn_code, it.description
    ORDER BY it.hsn_code ASC
  `, [from, to]);
  return rows;
}

module.exports = { invoiceReport, gstSummary, hsnSummary };
