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

require_once "../vendors/dompdf/autoload.inc.php";
use Dompdf\Dompdf;

$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";
}

$html = "
<style>
body { font-family: DejaVu Sans, sans-serif; font-size: 11px; }
h2 { text-align:center; margin-bottom:5px; }
p { text-align:center; margin:0; font-size: 11px; }
table { width:100%; border-collapse: collapse; margin-top:10px; }
th, td { border: 1px solid #000; padding: 5px; font-size: 10px; }
th { background: #f2f2f2; }
.total-box { margin-top:15px; padding:10px; border:1px solid #000; }
</style>

<h2>Profit Report</h2>
<p>From: $from | To: $to</p>
";

$totalSales = 0;
$totalCost = 0;
$totalProfit = 0;

if ($itemWise) {

    $html .= "<table>
        <thead>
            <tr>
                <th>Invoice No</th>
                <th>Date</th>
                <th>Customer</th>
                <th>Product</th>
                <th>Batch</th>
                <th>Qty</th>
                <th>Purchase</th>
                <th>Selling Excl GST</th>
                <th>Profit</th>
            </tr>
        </thead>
        <tbody>";

    $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()) {

        $sale = $row['selling_price_excl'] * $row['sold_qty'];
        $cost = $row['purchase_price'] * $row['sold_qty'];

        $totalSales += $sale;
        $totalCost += $cost;
        $totalProfit += $row['profit'];

        $html .= "<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>".number_format($row['purchase_price'],2)."</td>
            <td>".number_format($row['selling_price_excl'],2)."</td>
            <td><b>".number_format($row['profit'],2)."</b></td>
        </tr>";
    }

    $html .= "</tbody></table>";

} else {

    $html .= "<table>
        <thead>
            <tr>
                <th>Invoice No</th>
                <th>Date</th>
                <th>Customer</th>
                <th>Total Sales</th>
                <th>Total Cost</th>
                <th>Profit</th>
            </tr>
        </thead>
        <tbody>";

    $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()) {

        $totalSales += $row['total_sale_excl'];
        $totalCost += $row['total_cost'];
        $totalProfit += $row['profit'];

        $html .= "<tr>
            <td>{$row['invoice_no']}</td>
            <td>{$row['invoice_date']}</td>
            <td>{$row['customer_name']}</td>
            <td>".number_format($row['total_sale_excl'],2)."</td>
            <td>".number_format($row['total_cost'],2)."</td>
            <td><b>".number_format($row['profit'],2)."</b></td>
        </tr>";
    }

    $html .= "</tbody></table>";
}

$html .= "
<div class='total-box'>
<b>Total Sales:</b> ₹ ".number_format($totalSales,2)." <br>
<b>Total Cost:</b> ₹ ".number_format($totalCost,2)." <br>
<b>Total Profit:</b> ₹ ".number_format($totalProfit,2)."
</div>
";

$dompdf = new Dompdf();
$dompdf->loadHtml($html);
$dompdf->setPaper("A4", "landscape");
$dompdf->render();

$pdfFileName = "profit_report_" . date("YmdHis") . ".pdf";

header("Content-Type: application/pdf");
header("Content-Disposition: attachment; filename=$pdfFileName");
echo $dompdf->output();
exit;