<?php
require_once '../includes/auth.php';
requirePermission('reports_master_profit', 'export');
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'] ?? '';
$view        = $_GET['view']        ?? 'invoice';

$itemWise = ($view === 'item');
$dateWise = ($view === 'date');

$types  = 'ss';
$params = [$from, $to];

$clientWhere = '';
if (!empty($customer_id)) {
    $clientWhere = ' AND mi.client_id = ? ';
    $params[]    = $customer_id;
    $types      .= 'i';
}

$retJoin = "
    LEFT JOIN (
        SELECT sri.sales_item_id,
               SUM(sri.return_qty)                     AS ret_qty,
               SUM(sri.taxable_amount)                 AS ret_sale_excl,
               SUM(sri.return_qty * s2.purchase_price) AS ret_cost
        FROM sales_return_items sri
        JOIN sales_returns sr ON sr.id = sri.sales_return_id AND sr.status = 'final'
        JOIN sales_items   s2 ON s2.id = sri.sales_item_id
        GROUP BY sri.sales_item_id
    ) ret ON ret.sales_item_id = s.id
";

if ($itemWise) {
    $sql = "
        SELECT mi.master_no, mi.invoice_date, c.name AS customer_name,
               p.name AS product_name, s.batch_no,
               s.sold_qty,
               COALESCE(ret.ret_qty, 0) AS returned_qty,
               (s.sold_qty - COALESCE(ret.ret_qty, 0)) AS net_qty,
               s.purchase_price, s.selling_price_excl,
               ((s.selling_price_excl - s.purchase_price) * (s.sold_qty - COALESCE(ret.ret_qty, 0))) AS profit
        FROM master_invoices mi
        JOIN clients        c  ON c.id  = mi.client_id
        JOIN sales_invoices si ON si.master_invoice_id = mi.id AND si.status = 'final'
        JOIN sales_items    s  ON s.sales_invoice_id   = si.id
        JOIN products       p  ON p.id  = s.product_id
        $retJoin
        WHERE mi.invoice_date BETWEEN ? AND ?
          $clientWhere
        ORDER BY mi.id DESC, p.name ASC
    ";
} elseif ($dateWise) {
    $sql = "
        SELECT mi.invoice_date,
               SUM(s.selling_price_excl * s.sold_qty) - SUM(COALESCE(ret.ret_sale_excl, 0)) AS total_sale_excl,
               SUM(s.purchase_price     * s.sold_qty) - SUM(COALESCE(ret.ret_cost,      0)) AS total_cost,
               SUM((s.selling_price_excl - s.purchase_price) * s.sold_qty)
                   - SUM(COALESCE(ret.ret_sale_excl, 0) - COALESCE(ret.ret_cost, 0))         AS profit
        FROM master_invoices mi
        JOIN clients        c  ON c.id  = mi.client_id
        JOIN sales_invoices si ON si.master_invoice_id = mi.id AND si.status = 'final'
        JOIN sales_items    s  ON s.sales_invoice_id   = si.id
        $retJoin
        WHERE mi.invoice_date BETWEEN ? AND ?
          $clientWhere
        GROUP BY mi.invoice_date
        ORDER BY mi.invoice_date ASC
    ";
} else {
    $sql = "
        SELECT mi.master_no, mi.invoice_date, c.name AS customer_name,
               SUM(s.selling_price_excl * s.sold_qty) - SUM(COALESCE(ret.ret_sale_excl, 0)) AS total_sale_excl,
               SUM(s.purchase_price     * s.sold_qty) - SUM(COALESCE(ret.ret_cost,      0)) AS total_cost,
               SUM((s.selling_price_excl - s.purchase_price) * s.sold_qty)
                   - SUM(COALESCE(ret.ret_sale_excl, 0) - COALESCE(ret.ret_cost, 0))         AS profit
        FROM master_invoices mi
        JOIN clients        c  ON c.id  = mi.client_id
        JOIN sales_invoices si ON si.master_invoice_id = mi.id AND si.status = 'final'
        JOIN sales_items    s  ON s.sales_invoice_id   = si.id
        $retJoin
        WHERE mi.invoice_date BETWEEN ? AND ?
          $clientWhere
        GROUP BY mi.id
        ORDER BY mi.id DESC
    ";
}

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

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

$html = "
<style>
body { font-family: DejaVu Sans, sans-serif; font-size: 11px; }
h2 { text-align:center; margin-bottom:4px; }
p  { text-align:center; margin:0 0 8px; font-size:11px; color:#555; }
table { width:100%; border-collapse:collapse; margin-top:8px; }
th, td { border:1px solid #aaa; padding:4px 6px; font-size:9px; }
th { background:#e8f0fe; text-align:center; }
td.r { text-align:right; }
td.c { text-align:center; }
.total-row td { background:#e2f0cb; font-weight:bold; }
.summary { margin-top:14px; padding:8px 12px; border:1px solid #aaa; font-size:10px; }
.summary span { margin-right:24px; }
</style>
<h2>Master Sales Profit Report</h2>
<p>Period: $from &mdash; $to</p>
";

if ($itemWise) {
    $html .= "<table>
        <thead><tr>
            <th>Master Ref</th><th>Date</th><th>Customer</th><th>Product</th><th>Batch</th>
            <th>Sold</th><th>Returned</th><th>Net Qty</th>
            <th>Purchase Rate</th><th>Sell Rate (Excl GST)</th><th>Net Profit</th>
        </tr></thead><tbody>";
    while ($row = $res->fetch_assoc()) {
        $totalSales  += $row['selling_price_excl'] * $row['net_qty'];
        $totalCost   += $row['purchase_price']     * $row['net_qty'];
        $totalProfit += $row['profit'];
        $html .= "<tr>
            <td>{$row['master_no']}</td>
            <td class='c'>{$row['invoice_date']}</td>
            <td>{$row['customer_name']}</td>
            <td>{$row['product_name']}</td>
            <td class='c'>{$row['batch_no']}</td>
            <td class='c'>{$row['sold_qty']}</td>
            <td class='c'>{$row['returned_qty']}</td>
            <td class='c'><b>{$row['net_qty']}</b></td>
            <td class='r'>" . number_format($row['purchase_price'], 2) . "</td>
            <td class='r'>" . number_format($row['selling_price_excl'], 2) . "</td>
            <td class='r'><b>" . number_format($row['profit'], 2) . "</b></td>
        </tr>";
    }
    $html .= "</tbody></table>";

} elseif ($dateWise) {
    $html .= "<table>
        <thead><tr>
            <th>Date</th>
            <th>Net Sales (Excl GST)</th>
            <th>Net Cost</th>
            <th>Net Profit</th>
            <th>Margin %</th>
        </tr></thead><tbody>";
    while ($row = $res->fetch_assoc()) {
        $totalSales  += $row['total_sale_excl'];
        $totalCost   += $row['total_cost'];
        $totalProfit += $row['profit'];
        $margin = $row['total_sale_excl'] != 0
            ? number_format(($row['profit'] / $row['total_sale_excl']) * 100, 1) . '%' : '-';
        $html .= "<tr>
            <td class='c'><b>{$row['invoice_date']}</b></td>
            <td class='r'>" . number_format($row['total_sale_excl'], 2) . "</td>
            <td class='r'>" . number_format($row['total_cost'], 2) . "</td>
            <td class='r'><b>" . number_format($row['profit'], 2) . "</b></td>
            <td class='c'>$margin</td>
        </tr>";
    }
    $html .= "</tbody></table>";

} else {
    $html .= "<table>
        <thead><tr>
            <th>Master Ref</th><th>Date</th><th>Customer</th>
            <th>Net Sales (Excl GST)</th><th>Net Cost</th><th>Net Profit</th>
        </tr></thead><tbody>";
    while ($row = $res->fetch_assoc()) {
        $totalSales  += $row['total_sale_excl'];
        $totalCost   += $row['total_cost'];
        $totalProfit += $row['profit'];
        $html .= "<tr>
            <td><b>{$row['master_no']}</b></td>
            <td class='c'>{$row['invoice_date']}</td>
            <td>{$row['customer_name']}</td>
            <td class='r'>" . number_format($row['total_sale_excl'], 2) . "</td>
            <td class='r'>" . number_format($row['total_cost'], 2) . "</td>
            <td class='r'><b>" . number_format($row['profit'], 2) . "</b></td>
        </tr>";
    }
    $html .= "</tbody></table>";
}

$margin = $totalSales > 0 ? number_format(($totalProfit / $totalSales) * 100, 1) . '%' : '-';

$html .= "
<div class='summary'>
    <span><b>Net Sales:</b> &#8377; " . number_format($totalSales, 2) . "</span>
    <span><b>Net Cost:</b> &#8377; " . number_format($totalCost, 2) . "</span>
    <span><b>Net Profit:</b> &#8377; " . number_format($totalProfit, 2) . "</span>
    <span><b>Margin:</b> $margin</span>
</div>
";

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

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