<?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 = isset($_GET['itemwise']) ? 1 : 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=profit_report_" . date("YmdHis") . ".xls");

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

if ($itemWise) {

    echo "<tr style='background:#ddd;font-weight:bold;'>
            <td>Invoice No</td>
            <td>Date</td>
            <td>Customer</td>
            <td>Product</td>
            <td>Batch</td>
            <td>Qty</td>
            <td>Purchase</td>
            <td>Selling Excl GST</td>
            <td>Profit</td>
          </tr>";

    $sql = "
        SELECT 
            si.invoice_no,
            si.invoice_date,
            c.name AS customer_name,
            p.name AS product_name,
            s.batch_no,
            s.sold_qty,
            s.purchase_price,
            s.selling_price_excl,
            ((s.selling_price_excl - s.purchase_price) * s.sold_qty) AS profit
        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()) {
        echo "<tr>
                <td>{$row['invoice_no']}</td>
                <td>{$row['invoice_date']}</td>
                <td>{$row['customer_name']}</td>
                <td>{$row['product_name']}</td>
                <td>{$row['batch_no']}</td>
                <td>{$row['sold_qty']}</td>
                <td>{$row['purchase_price']}</td>
                <td>{$row['selling_price_excl']}</td>
                <td>{$row['profit']}</td>
              </tr>";
    }

} else {

    echo "<tr style='background:#ddd;font-weight:bold;'>
            <td>Invoice No</td>
            <td>Date</td>
            <td>Customer</td>
            <td>Total Sales</td>
            <td>Total Cost</td>
            <td>Profit</td>
          </tr>";

    $sql = "
        SELECT 
            si.id,
            si.invoice_no,
            si.invoice_date,
            c.name AS customer_name,
            SUM(s.purchase_price * s.sold_qty) AS total_cost,
            SUM(s.selling_price_excl * s.sold_qty) AS total_sale_excl,
            SUM((s.selling_price_excl - s.purchase_price) * s.sold_qty) AS profit
        FROM sales_invoices si
        JOIN clients c ON c.id = si.client_id
        JOIN sales_items s ON s.sales_invoice_id = si.id
        $where
        GROUP BY si.id
        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()) {
        echo "<tr>
                <td>{$row['invoice_no']}</td>
                <td>{$row['invoice_date']}</td>
                <td>{$row['customer_name']}</td>
                <td>{$row['total_sale_excl']}</td>
                <td>{$row['total_cost']}</td>
                <td>{$row['profit']}</td>
              </tr>";
    }
}

echo "</table>";
exit;