<?php
$pageTitle = 'Purchase Report';
require_once '../includes/functions.php';
requireLogin();
$db = getDB();

// ── Filters ──────────────────────────────────────────────────
$report_type  = trim($_GET['report_type']  ?? 'purchase');  // purchase | product
$filter_type  = trim($_GET['filter_type']  ?? 'all');        // all | date | month | year
$filter_date  = trim($_GET['filter_date']  ?? '');
$filter_month = (int)($_GET['filter_month'] ?? date('n'));
$filter_year  = (int)($_GET['filter_year']  ?? date('Y'));
$filter_product = trim($_GET['filter_product'] ?? '');

// Date WHERE clause
$dateWhere  = "WHERE i.status != 'cancelled'";
$params     = [];

if ($filter_type === 'date' && $filter_date) {
    $dateWhere .= " AND i.invoice_date = ?";
    $params[] = $filter_date;
} elseif ($filter_type === 'month') {
    $dateWhere .= " AND MONTH(i.invoice_date) = ? AND YEAR(i.invoice_date) = ?";
    $params[] = $filter_month;
    $params[] = $filter_year;
} elseif ($filter_type === 'year') {
    $dateWhere .= " AND YEAR(i.invoice_date) = ?";
    $params[] = $filter_year;
}

if ($filter_product) {
    $dateWhere .= " AND ii.product_name LIKE ?";
    $params[] = "%$filter_product%";
}

// ── Main Query ────────────────────────────────────────────────
$sql = "
    SELECT
        i.invoice_number,
        i.invoice_date,
        c.name              AS customer_name,
        ii.product_name,
        COALESCE(p.sku, '') AS hsn_code,
        'Goods'             AS product_type,
        (ii.gst_percent / 2) AS cgst_percent,
        (ii.gst_percent / 2) AS sgst_percent,
        (ii.gst_amount  / 2) AS cgst_amount,
        (ii.gst_amount  / 2) AS sgst_amount,
        ii.gst_percent,
        ii.gst_amount,
        ii.quantity,
        ii.unit_price,
        (ii.unit_price * (1 + ii.margin_percent/100)) AS selling_price,
        ii.line_total,
        i.status
    FROM invoices i
    JOIN customers c       ON i.customer_id  = c.id
    JOIN invoice_items ii  ON ii.invoice_id  = i.id
    LEFT JOIN products p   ON p.id           = ii.product_id
    $dateWhere
    ORDER BY i.invoice_date DESC, i.id DESC, ii.id ASC
";

$stmt = $db->prepare($sql);
$stmt->execute($params);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Totals
$totalQty   = 0; $totalGST = 0; $grandTotal = 0;
$totalCGST  = 0; $totalSGST = 0; $totalBase = 0;
foreach ($rows as $r) {
    $totalQty   += $r['quantity'];
    $totalGST   += $r['gst_amount'];
    $totalCGST  += $r['cgst_amount'];
    $totalSGST  += $r['sgst_amount'];
    $grandTotal += $r['line_total'];
    $totalBase  += ($r['line_total'] - $r['gst_amount']);
}

// Product summary (for product report tab)
$productSql = "
    SELECT
        ii.product_name,
        COALESCE(p.sku,'') AS hsn_code,
        'Goods'            AS product_type,
        SUM(ii.quantity)   AS total_qty,
        AVG(ii.unit_price) AS avg_price,
        SUM(ii.gst_amount / 2) AS total_cgst,
        SUM(ii.gst_amount / 2) AS total_sgst,
        SUM(ii.line_total) AS total_amount,
        AVG(ii.gst_percent) AS gst_percent
    FROM invoices i
    JOIN invoice_items ii ON ii.invoice_id = i.id
    LEFT JOIN products p  ON p.id = ii.product_id
    $dateWhere
    GROUP BY ii.product_name, p.sku
    ORDER BY total_amount DESC
";
$pStmt = $db->prepare($productSql);
$pStmt->execute($params);
$productRows = $pStmt->fetchAll(PDO::FETCH_ASSOC);

// ── Export ────────────────────────────────────────────────────
$export = trim($_GET['export'] ?? '');
if ($export === 'excel') {
    header('Content-Type: application/vnd.ms-excel; charset=utf-8');
    header('Content-Disposition: attachment; filename="Purchase_Report_' . date('Ymd_His') . '.xls"');
    echo "\xEF\xBB\xBF";

    echo "<html><head><meta charset='UTF-8'>
<style>
  .hdr { background:#1e1f52; color:white; font-weight:bold; border:1px solid #999; font-size:13pt; text-align:center; }
  .th  { background:#4f56ea; color:white; font-weight:bold; border:1px solid #999; }
  .td  { border:1px solid #ddd; }
  .num { text-align:right; border:1px solid #ddd; }
  .tot { background:#f0f4ff; font-weight:bold; border:1px solid #999; }
  .totn{ background:#f0f4ff; font-weight:bold; text-align:right; border:1px solid #999; }
</style></head><body><table>
<tr><td colspan='12' class='hdr'>PURCHASE REPORT — " . APP_NAME . " — Generated: " . date('d M Y H:i') . "</td></tr>
<tr></tr>
<tr>
  <td class='th'>S.No</td>
  <td class='th'>Invoice No</td>
  <td class='th'>Date</td>
  <td class='th'>Customer Name</td>
  <td class='th'>Product / Sales</td>
  <td class='th'>HSN Code</td>
  <td class='th'>Type</td>
  <td class='th'>CGST %</td>
  <td class='th'>SGST %</td>
  <td class='th'>Quantity</td>
  <td class='th'>Unit Price (₹)</td>
  <td class='th'>Total (₹)</td>
</tr>\n";

    $sno = 1;
    foreach ($rows as $r) {
        echo "<tr>
  <td class='td'>{$sno}</td>
  <td class='td'>" . htmlspecialchars($r['invoice_number']) . "</td>
  <td class='td'>" . date('d-M-Y', strtotime($r['invoice_date'])) . "</td>
  <td class='td'>" . htmlspecialchars($r['customer_name']) . "</td>
  <td class='td'>" . htmlspecialchars($r['product_name']) . "</td>
  <td class='td'>" . htmlspecialchars($r['hsn_code']) . "</td>
  <td class='td'>" . htmlspecialchars($r['product_type']) . "</td>
  <td class='num'>" . number_format($r['cgst_percent'], 2) . "%</td>
  <td class='num'>" . number_format($r['sgst_percent'], 2) . "%</td>
  <td class='num'>" . $r['quantity'] . "</td>
  <td class='num'>" . number_format($r['unit_price'], 2) . "</td>
  <td class='num'>" . number_format($r['line_total'], 2) . "</td>
</tr>\n";
        $sno++;
    }

    echo "<tr>
  <td colspan='9' class='tot'>TOTAL</td>
  <td class='totn'>{$totalQty}</td>
  <td class='tot'></td>
  <td class='totn'>" . number_format($grandTotal, 2) . "</td>
</tr>
</table></body></html>";
    exit;
}

if ($export === 'csv') {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="Purchase_Report_' . date('Ymd_His') . '.csv"');
    echo "\xEF\xBB\xBF";
    $out = fopen('php://output', 'w');
    fputcsv($out, ['S.No','Invoice No','Date','Customer Name','Product/Sales','HSN Code','Type','CGST %','SGST %','Quantity','Unit Price','Total']);
    $sno = 1;
    foreach ($rows as $r) {
        fputcsv($out, [
            $sno++,
            $r['invoice_number'],
            $r['invoice_date'],
            $r['customer_name'],
            $r['product_name'],
            $r['hsn_code'],
            $r['product_type'],
            $r['cgst_percent'],
            $r['sgst_percent'],
            $r['quantity'],
            $r['unit_price'],
            $r['line_total'],
        ]);
    }
    fputcsv($out, ['', '', '', '', '', '', '', '', 'TOTAL', $totalQty, '', number_format($grandTotal, 2)]);
    fclose($out);
    exit;
}

// Available years
$years = $db->query("SELECT DISTINCT YEAR(invoice_date) as y FROM invoices ORDER BY y DESC")->fetchAll(PDO::FETCH_COLUMN);
if (empty($years)) $years = [date('Y')];

$months_list = ['1'=>'January','2'=>'February','3'=>'March','4'=>'April','5'=>'May','6'=>'June',
                '7'=>'July','8'=>'August','9'=>'September','10'=>'October','11'=>'November','12'=>'December'];

// All products for filter dropdown
$allProducts = $db->query("SELECT DISTINCT product_name FROM invoice_items ORDER BY product_name")->fetchAll(PDO::FETCH_COLUMN);

require_once '../includes/header.php';
?>

<!-- Header -->
<div class="flex flex-col sm:flex-row sm:items-center sm:justify-between gap-3 mb-5">
    <div>
        <h2 class="text-lg font-bold text-slate-800">Purchase Report</h2>
        <p class="text-sm text-slate-400"><?= count($rows) ?> line item<?= count($rows) != 1 ? 's' : '' ?></p>
    </div>
    <div class="flex gap-2 flex-wrap">
        <a href="?<?= http_build_query(array_merge($_GET, ['export'=>'excel'])) ?>" class="btn-primary text-sm">
            <i class="fas fa-file-excel"></i> Excel
        </a>
        <a href="?<?= http_build_query(array_merge($_GET, ['export'=>'csv'])) ?>" class="btn-outline text-sm">
            <i class="fas fa-file-csv"></i> CSV
        </a>
    </div>
</div>

<!-- Report Type Tabs -->
<div class="flex gap-1 mb-5 bg-slate-100 rounded-xl p-1 w-fit">
    <a href="?<?= http_build_query(array_merge($_GET, ['report_type'=>'purchase'])) ?>"
        class="px-5 py-2 rounded-lg text-sm font-semibold transition <?= $report_type === 'purchase' ? 'bg-white text-brand-600 shadow-sm' : 'text-slate-500 hover:text-slate-700' ?>">
        <i class="fas fa-receipt mr-1.5"></i>Purchase Report
    </a>
    <a href="?<?= http_build_query(array_merge($_GET, ['report_type'=>'product'])) ?>"
        class="px-5 py-2 rounded-lg text-sm font-semibold transition <?= $report_type === 'product' ? 'bg-white text-brand-600 shadow-sm' : 'text-slate-500 hover:text-slate-700' ?>">
        <i class="fas fa-box mr-1.5"></i>Product Report
    </a>
</div>

<!-- Filters -->
<div class="card p-4 mb-5">
    <form method="GET" class="flex flex-wrap gap-3 items-end">
        <input type="hidden" name="report_type" value="<?= sanitize($report_type) ?>">

        <div>
            <label class="block text-xs font-semibold text-slate-500 mb-1">Filter By Date</label>
            <select name="filter_type" id="filterType" onchange="toggleFilters(this.value)" class="form-input w-36">
                <option value="all"   <?= $filter_type==='all'   ? 'selected':'' ?>>All Data</option>
                <option value="date"  <?= $filter_type==='date'  ? 'selected':'' ?>>Specific Date</option>
                <option value="month" <?= $filter_type==='month' ? 'selected':'' ?>>Month</option>
                <option value="year"  <?= $filter_type==='year'  ? 'selected':'' ?>>Year</option>
            </select>
        </div>

        <div id="dateField" class="<?= $filter_type==='date' ? '' : 'hidden' ?>">
            <label class="block text-xs font-semibold text-slate-500 mb-1">Date</label>
            <input type="date" name="filter_date" value="<?= htmlspecialchars($filter_date) ?>" class="form-input">
        </div>

        <div id="monthField" class="<?= $filter_type==='month' ? '' : 'hidden' ?>">
            <label class="block text-xs font-semibold text-slate-500 mb-1">Month</label>
            <select name="filter_month" class="form-input w-36">
                <?php foreach($months_list as $m => $mname): ?>
                <option value="<?=$m?>" <?= $filter_month==$m?'selected':'' ?>><?= $mname ?></option>
                <?php endforeach; ?>
            </select>
        </div>

        <div id="yearField" class="<?= in_array($filter_type,['month','year']) ? '' : 'hidden' ?>">
            <label class="block text-xs font-semibold text-slate-500 mb-1">Year</label>
            <select name="filter_year" class="form-input w-28">
                <?php foreach($years as $y): ?>
                <option value="<?=$y?>" <?= $filter_year==$y?'selected':'' ?>><?=$y?></option>
                <?php endforeach; ?>
            </select>
        </div>

        <div>
            <label class="block text-xs font-semibold text-slate-500 mb-1">Product</label>
            <select name="filter_product" class="form-input w-44">
                <option value="">All Products</option>
                <?php foreach ($allProducts as $pn): ?>
                <option value="<?= htmlspecialchars($pn) ?>" <?= $filter_product === $pn ? 'selected' : '' ?>>
                    <?= htmlspecialchars($pn) ?>
                </option>
                <?php endforeach; ?>
            </select>
        </div>

        <div class="flex gap-2">
            <button type="submit" class="btn-primary"><i class="fas fa-filter"></i> Apply</button>
            <?php if ($filter_type !== 'all' || $filter_product): ?>
            <a href="purchase_report.php?report_type=<?= $report_type ?>" class="btn-outline"><i class="fas fa-times"></i> Clear</a>
            <?php endif; ?>
        </div>
    </form>
</div>

<!-- Summary Cards -->
<div class="grid grid-cols-2 lg:grid-cols-4 gap-4 mb-5">
    <div class="card p-4">
        <p class="text-xs text-slate-400 font-semibold uppercase tracking-wide mb-1">Records</p>
        <p class="text-2xl font-bold text-slate-800"><?= count($rows) ?></p>
    </div>
    <div class="card p-4">
        <p class="text-xs text-slate-400 font-semibold uppercase tracking-wide mb-1">Total (excl. GST)</p>
        <p class="text-2xl font-bold text-slate-800"><?= formatCurrency($totalBase) ?></p>
    </div>
    <div class="card p-4">
        <p class="text-xs text-slate-400 font-semibold uppercase tracking-wide mb-1">Total GST</p>
        <p class="text-2xl font-bold text-slate-800"><?= formatCurrency($totalGST) ?></p>
    </div>
    <div class="card p-4" style="background:linear-gradient(135deg,#6174f5,#4143d0)">
        <p class="text-xs text-white/70 font-semibold uppercase tracking-wide mb-1">Grand Total</p>
        <p class="text-2xl font-bold text-white"><?= formatCurrency($grandTotal) ?></p>
    </div>
</div>

<?php if ($report_type === 'purchase'): ?>
<!-- ═══════════════════ PURCHASE REPORT TABLE ═══════════════════ -->
<div class="card overflow-hidden">
    <div class="px-5 py-4 border-b border-slate-100 flex items-center gap-2">
        <i class="fas fa-receipt text-brand-500"></i>
        <h3 class="font-bold text-slate-700">Purchase Report</h3>
    </div>
    <div class="overflow-x-auto">
        <table class="w-full text-sm">
            <thead class="bg-slate-50 border-b border-slate-100">
                <tr>
                    <th class="text-left px-3 py-3.5 text-slate-500 font-semibold text-xs uppercase tracking-wide">#</th>
                    <th class="text-left px-3 py-3.5 text-slate-500 font-semibold text-xs uppercase tracking-wide">Invoice</th>
                    <th class="text-left px-3 py-3.5 text-slate-500 font-semibold text-xs uppercase tracking-wide">Date</th>
                    <th class="text-left px-3 py-3.5 text-slate-500 font-semibold text-xs uppercase tracking-wide">Name</th>
                    <th class="text-left px-3 py-3.5 text-slate-500 font-semibold text-xs uppercase tracking-wide">Product / Sales</th>
                    <th class="text-left px-3 py-3.5 text-slate-500 font-semibold text-xs uppercase tracking-wide">HSN Code</th>
                    <th class="text-left px-3 py-3.5 text-slate-500 font-semibold text-xs uppercase tracking-wide">Type</th>
                    <th class="text-right px-3 py-3.5 text-slate-500 font-semibold text-xs uppercase tracking-wide">CGST %</th>
                    <th class="text-right px-3 py-3.5 text-slate-500 font-semibold text-xs uppercase tracking-wide">SGST %</th>
                    <th class="text-right px-3 py-3.5 text-slate-500 font-semibold text-xs uppercase tracking-wide">Qty</th>
                    <th class="text-right px-3 py-3.5 text-slate-500 font-semibold text-xs uppercase tracking-wide">Unit Price</th>
                    <th class="text-right px-3 py-3.5 text-slate-500 font-semibold text-xs uppercase tracking-wide">Total</th>
                </tr>
            </thead>
            <tbody class="divide-y divide-slate-50">
                <?php if (empty($rows)): ?>
                <tr>
                    <td colspan="12" class="text-center py-12 text-slate-400">
                        <i class="fas fa-chart-line text-3xl mb-2 block text-slate-200"></i>
                        No purchase data for the selected filter.
                    </td>
                </tr>
                <?php else: $sno = 1; foreach ($rows as $r): ?>
                <tr class="table-row">
                    <td class="px-3 py-3 text-slate-400 text-xs"><?= $sno++ ?></td>
                    <td class="px-3 py-3">
                        <span class="font-mono text-xs bg-brand-50 text-brand-600 px-2 py-0.5 rounded font-semibold">
                            <?= htmlspecialchars($r['invoice_number']) ?>
                        </span>
                    </td>
                    <td class="px-3 py-3 text-slate-500 text-xs whitespace-nowrap"><?= formatDate($r['invoice_date']) ?></td>
                    <td class="px-3 py-3 font-semibold text-slate-800 text-xs"><?= htmlspecialchars($r['customer_name']) ?></td>
                    <td class="px-3 py-3 text-slate-700 text-xs"><?= htmlspecialchars($r['product_name']) ?></td>
                    <td class="px-3 py-3">
                        <span class="font-mono text-xs bg-slate-100 px-2 py-0.5 rounded text-slate-600">
                            <?= htmlspecialchars($r['hsn_code']) ?: '—' ?>
                        </span>
                    </td>
                    <td class="px-3 py-3 text-xs text-slate-600"><?= htmlspecialchars($r['product_type']) ?></td>
                    <td class="px-3 py-3 text-right text-xs text-slate-600"><?= number_format($r['cgst_percent'], 2) ?>%</td>
                    <td class="px-3 py-3 text-right text-xs text-slate-600"><?= number_format($r['sgst_percent'], 2) ?>%</td>
                    <td class="px-3 py-3 text-right font-bold text-slate-700"><?= $r['quantity'] ?></td>
                    <td class="px-3 py-3 text-right text-slate-600"><?= formatCurrency($r['unit_price']) ?></td>
                    <td class="px-3 py-3 text-right font-bold text-slate-800"><?= formatCurrency($r['line_total']) ?></td>
                </tr>
                <?php endforeach; endif; ?>
            </tbody>
            <?php if (!empty($rows)): ?>
            <tfoot class="bg-slate-50 border-t-2 border-slate-200">
                <tr>
                    <td colspan="9" class="px-3 py-3.5 font-bold text-slate-700">TOTAL</td>
                    <td class="px-3 py-3.5 text-right font-bold text-slate-800"><?= $totalQty ?></td>
                    <td class="px-3 py-3.5"></td>
                    <td class="px-3 py-3.5 text-right font-bold text-brand-700 text-base"><?= formatCurrency($grandTotal) ?></td>
                </tr>
            </tfoot>
            <?php endif; ?>
        </table>
    </div>
</div>

<?php else: ?>
<!-- ═══════════════════ PRODUCT REPORT TABLE ═══════════════════ -->
<div class="card overflow-hidden">
    <div class="px-5 py-4 border-b border-slate-100 flex items-center gap-2">
        <i class="fas fa-box text-brand-500"></i>
        <h3 class="font-bold text-slate-700">Product-wise Sales Summary</h3>
    </div>
    <div class="overflow-x-auto">
        <table class="w-full text-sm">
            <thead class="bg-slate-50 border-b border-slate-100">
                <tr>
                    <th class="text-left px-4 py-3.5 text-slate-500 font-semibold text-xs uppercase tracking-wide">#</th>
                    <th class="text-left px-4 py-3.5 text-slate-500 font-semibold text-xs uppercase tracking-wide">Product / Sales</th>
                    <th class="text-left px-4 py-3.5 text-slate-500 font-semibold text-xs uppercase tracking-wide">HSN Code</th>
                    <th class="text-left px-4 py-3.5 text-slate-500 font-semibold text-xs uppercase tracking-wide">Type</th>
                    <th class="text-right px-4 py-3.5 text-slate-500 font-semibold text-xs uppercase tracking-wide">CGST</th>
                    <th class="text-right px-4 py-3.5 text-slate-500 font-semibold text-xs uppercase tracking-wide">SGST</th>
                    <th class="text-right px-4 py-3.5 text-slate-500 font-semibold text-xs uppercase tracking-wide">Total Qty</th>
                    <th class="text-right px-4 py-3.5 text-slate-500 font-semibold text-xs uppercase tracking-wide">Avg Price</th>
                    <th class="text-right px-4 py-3.5 text-slate-500 font-semibold text-xs uppercase tracking-wide">Total Amount</th>
                </tr>
            </thead>
            <tbody class="divide-y divide-slate-50">
                <?php if (empty($productRows)): ?>
                <tr>
                    <td colspan="9" class="text-center py-12 text-slate-400">
                        <i class="fas fa-box-open text-3xl mb-2 block text-slate-200"></i>
                        No product data for the selected filter.
                    </td>
                </tr>
                <?php else: $sno = 1; $pGrandTotal = 0; $pTotalQty = 0;
                    foreach ($productRows as $r):
                    $pGrandTotal += $r['total_amount'];
                    $pTotalQty   += $r['total_qty'];
                ?>
                <tr class="table-row">
                    <td class="px-4 py-3 text-slate-400 text-xs"><?= $sno++ ?></td>
                    <td class="px-4 py-3 font-bold text-slate-800"><?= htmlspecialchars($r['product_name']) ?></td>
                    <td class="px-4 py-3">
                        <span class="font-mono text-xs bg-slate-100 px-2 py-0.5 rounded text-slate-600">
                            <?= htmlspecialchars($r['hsn_code']) ?: '—' ?>
                        </span>
                    </td>
                    <td class="px-4 py-3 text-xs text-slate-600"><?= $r['product_type'] ?></td>
                    <td class="px-4 py-3 text-right text-xs text-slate-600">
                        <?= number_format($r['gst_percent']/2, 2) ?>%
                        <div class="text-slate-400"><?= formatCurrency($r['total_cgst']) ?></div>
                    </td>
                    <td class="px-4 py-3 text-right text-xs text-slate-600">
                        <?= number_format($r['gst_percent']/2, 2) ?>%
                        <div class="text-slate-400"><?= formatCurrency($r['total_sgst']) ?></div>
                    </td>
                    <td class="px-4 py-3 text-right font-bold text-slate-700 text-base"><?= number_format($r['total_qty']) ?></td>
                    <td class="px-4 py-3 text-right text-slate-600"><?= formatCurrency($r['avg_price']) ?></td>
                    <td class="px-4 py-3 text-right font-bold text-slate-800"><?= formatCurrency($r['total_amount']) ?></td>
                </tr>
                <?php endforeach; endif; ?>
            </tbody>
            <?php if (!empty($productRows)): ?>
            <tfoot class="bg-slate-50 border-t-2 border-slate-200">
                <tr>
                    <td colspan="6" class="px-4 py-3.5 font-bold text-slate-700">TOTAL</td>
                    <td class="px-4 py-3.5 text-right font-bold text-slate-800"><?= number_format($pTotalQty) ?></td>
                    <td class="px-4 py-3.5"></td>
                    <td class="px-4 py-3.5 text-right font-bold text-brand-700 text-base"><?= formatCurrency($pGrandTotal) ?></td>
                </tr>
            </tfoot>
            <?php endif; ?>
        </table>
    </div>
</div>
<?php endif; ?>

<script>
function toggleFilters(val) {
    document.getElementById('dateField').classList.toggle('hidden', val !== 'date');
    document.getElementById('monthField').classList.toggle('hidden', val !== 'month');
    document.getElementById('yearField').classList.toggle('hidden', val !== 'month' && val !== 'year');
}
</script>

<?php require_once '../includes/footer.php'; ?>
