<?php
require_once '../includes/auth.php';
require_once '../db.php';

$from = $_GET['from'] ?? date('Y-m-01');
$to   = $_GET['to'] ?? date('Y-m-d');
$customer_id = $_GET['customer_id'] ?? '';
$itemWise = $_GET['itemwise'] ?? 0;
$hsnWise  = $_GET['hsnwise'] ?? 0;

$where = " WHERE si.invoice_date BETWEEN ? AND ? AND si.company_id = ? ";
$params = [$from, $to, $cid];
$types = "ssi";

if (!empty($customer_id)) {
    $where .= " AND si.client_id = ? ";
    $params[] = $customer_id;
    $types .= "i";
}

header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=gst_report_" . date("YmdHis") . ".xls");

echo "<table border='1'>";

if ($hsnWise) {

    echo "<tr style='background:#ddd;font-weight:bold;'>
        <td>HSN Code</td>
        <td>GST %</td>
        <td>Taxable</td>
        <td>CGST</td>
        <td>SGST</td>
        <td>Total GST</td>
    </tr>";

    $sql = "
        SELECT 
            p.hsn_code,
            s.gst_percent,
            SUM(s.taxable_amount) AS taxable_total,
            SUM(s.gst_amount) AS gst_total
        FROM sales_items s
        JOIN sales_invoices si ON si.id = s.sales_invoice_id
        JOIN products p ON p.id = s.product_id
        $where
        GROUP BY p.hsn_code, s.gst_percent
        ORDER BY p.hsn_code ASC
    ";

    $stmt = $conn->prepare($sql);
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    $res = $stmt->get_result();

    while($row = $res->fetch_assoc()) {
        $cgst = $row['gst_total']/2;
        $sgst = $row['gst_total']/2;

        echo "<tr>
            <td>{$row['hsn_code']}</td>
            <td>{$row['gst_percent']}%</td>
            <td>{$row['taxable_total']}</td>
            <td>$cgst</td>
            <td>$sgst</td>
            <td>{$row['gst_total']}</td>
        </tr>";
    }

} elseif ($itemWise) {

    echo "<tr style='background:#ddd;font-weight:bold;'>
        <td>Invoice No</td>
        <td>Date</td>
        <td>Customer</td>
        <td>Product</td>
        <td>HSN</td>
        <td>Qty</td>
        <td>Taxable</td>
        <td>GST %</td>
        <td>CGST</td>
        <td>SGST</td>
        <td>Total GST</td>
        <td>Total</td>
    </tr>";

    $sql = "
        SELECT 
            si.invoice_no,
            si.invoice_date,
            c.name AS customer_name,
            p.name AS product_name,
            p.hsn_code,
            s.sold_qty,
            s.taxable_amount,
            s.gst_percent,
            s.gst_amount,
            (s.taxable_amount + s.gst_amount) AS total_amount
        FROM sales_items s
        JOIN sales_invoices si ON si.id = s.sales_invoice_id
        JOIN clients c ON c.id = si.client_id
        JOIN products p ON p.id = s.product_id
        $where
        ORDER BY si.id DESC
    ";

    $stmt = $conn->prepare($sql);
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    $res = $stmt->get_result();

    while($row = $res->fetch_assoc()) {
        $cgst = $row['gst_amount']/2;
        $sgst = $row['gst_amount']/2;

        echo "<tr>
            <td>{$row['invoice_no']}</td>
            <td>{$row['invoice_date']}</td>
            <td>{$row['customer_name']}</td>
            <td>{$row['product_name']}</td>
            <td>{$row['hsn_code']}</td>
            <td>{$row['sold_qty']}</td>
            <td>{$row['taxable_amount']}</td>
            <td>{$row['gst_percent']}%</td>
            <td>$cgst</td>
            <td>$sgst</td>
            <td>{$row['gst_amount']}</td>
            <td>{$row['total_amount']}</td>
        </tr>";
    }

} else {

    echo "<tr style='background:#ddd;font-weight:bold;'>
        <td>Invoice No</td>
        <td>Date</td>
        <td>Customer</td>
        <td>Taxable</td>
        <td>CGST</td>
        <td>SGST</td>
        <td>Total GST</td>
        <td>Net Total</td>
    </tr>";

    $sql = "
        SELECT 
            si.invoice_no,
            si.invoice_date,
            c.name AS customer_name,
            si.total_taxable,
            si.total_gst,
            si.net_total
        FROM sales_invoices si
        JOIN clients c ON c.id = si.client_id
        $where
        ORDER BY si.id DESC
    ";

    $stmt = $conn->prepare($sql);
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    $res = $stmt->get_result();

    while($row = $res->fetch_assoc()) {
        $cgst = $row['total_gst']/2;
        $sgst = $row['total_gst']/2;

        echo "<tr>
            <td>{$row['invoice_no']}</td>
            <td>{$row['invoice_date']}</td>
            <td>{$row['customer_name']}</td>
            <td>{$row['total_taxable']}</td>
            <td>$cgst</td>
            <td>$sgst</td>
            <td>{$row['total_gst']}</td>
            <td>{$row['net_total']}</td>
        </tr>";
    }
}

echo "</table>";
exit;
