<?php
require_once '../includes/auth.php';
requirePermission('reports_master_profit', 'export');
require_once '../db.php';

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

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

echo "<table border='1'>";
echo "<tr style='background:#cce5ff;font-weight:bold;' colspan='10'><td colspan='10'>Master Sales Profit Report | From: $from | To: $to</td></tr>";

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

if ($itemWise) {
    echo "<tr style='background:#ddd;font-weight:bold;'>
            <td>Master Ref</td><td>Date</td><td>Customer</td><td>Product</td><td>Batch</td>
            <td>Sold</td><td>Returned</td><td>Net Qty</td>
            <td>Purchase Rate</td><td>Sell Rate (Excl GST)</td><td>Net Profit</td>
          </tr>";
    while ($row = $res->fetch_assoc()) {
        $totalSales  += $row['selling_price_excl'] * $row['net_qty'];
        $totalCost   += $row['purchase_price']     * $row['net_qty'];
        $totalProfit += $row['profit'];
        echo "<tr>
                <td>{$row['master_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['returned_qty']}</td>
                <td>{$row['net_qty']}</td>
                <td>" . number_format($row['purchase_price'], 2) . "</td>
                <td>" . number_format($row['selling_price_excl'], 2) . "</td>
                <td>" . number_format($row['profit'], 2) . "</td>
              </tr>";
    }
} elseif ($dateWise) {
    echo "<tr style='background:#ddd;font-weight:bold;'>
            <td>Date</td>
            <td>Net Sales (Excl GST)</td>
            <td>Net Cost</td>
            <td>Net Profit</td>
            <td>Margin %</td>
          </tr>";
    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) . '%' : '-';
        echo "<tr>
                <td>{$row['invoice_date']}</td>
                <td>" . number_format($row['total_sale_excl'], 2) . "</td>
                <td>" . number_format($row['total_cost'], 2) . "</td>
                <td>" . number_format($row['profit'], 2) . "</td>
                <td>$margin</td>
              </tr>";
    }
} else {
    echo "<tr style='background:#ddd;font-weight:bold;'>
            <td>Master Ref</td><td>Date</td><td>Customer</td>
            <td>Net Sales (Excl GST)</td><td>Net Cost</td><td>Net Profit</td>
          </tr>";
    while ($row = $res->fetch_assoc()) {
        $totalSales  += $row['total_sale_excl'];
        $totalCost   += $row['total_cost'];
        $totalProfit += $row['profit'];
        echo "<tr>
                <td>{$row['master_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>" . number_format($row['profit'], 2) . "</td>
              </tr>";
    }
}

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

echo "<tr></tr>
      <tr style='background:#e2f0cb;font-weight:bold;'>
        <td colspan='3'>TOTALS</td>
        <td>Net Sales: " . number_format($totalSales, 2) . "</td>
        <td>Net Cost: " . number_format($totalCost, 2) . "</td>
        <td>Net Profit: " . number_format($totalProfit, 2) . " | Margin: $margin</td>
      </tr>";

echo "</table>";
exit;
