<?php
require_once __DIR__ . '/../../includes/functions.php';
requireLogin();

$fromDate   = $_GET['from']      ?? date('Y-m-01');
$toDate     = $_GET['to']        ?? date('Y-m-d');
$vendorId   = (int)($_GET['vendor_id'] ?? 0);
$invoiceNo  = trim($_GET['invoice_no'] ?? '');

$vendors = $pdo->query("SELECT * FROM purchase_vendors ORDER BY vendor_name")->fetchAll();

$params = [$fromDate, $toDate];
$where  = ["p.purchase_date BETWEEN ? AND ?"];
if ($vendorId > 0)    { $where[] = "p.vendor_id = ?";    $params[] = $vendorId; }
if ($invoiceNo !== '') { $where[] = "p.invoice_no LIKE ?"; $params[] = "%$invoiceNo%"; }
$whereSql = 'WHERE ' . implode(' AND ', $where);

// ── Summary ──────────────────────────────────────────────────
$stmt = $pdo->prepare("SELECT COUNT(DISTINCT p.id) orders,
    COALESCE(SUM(p.total_amount),0) subtotal,
    COALESCE(SUM(p.gst_amount),0) gst,
    COALESCE(SUM(p.grand_total),0) total,
    COALESCE(SUM(p.paid_amount),0) paid,
    COALESCE(SUM(p.grand_total - p.paid_amount),0) outstanding
    FROM purchases p $whereSql");
$stmt->execute($params); $summary = $stmt->fetch();

// ── All Purchase Orders ───────────────────────────────────────
$stmt = $pdo->prepare("SELECT p.*, v.vendor_name, v.vendor_code
    FROM purchases p JOIN purchase_vendors v ON v.id=p.vendor_id
    $whereSql ORDER BY p.purchase_date DESC, p.id DESC");
$stmt->execute($params); $orders = $stmt->fetchAll();

// ── Item-wise breakdown ───────────────────────────────────────
$stmt = $pdo->prepare("SELECT i.item_name, i.item_code,
    SUM(pi.qty) total_qty,
    AVG(pi.purchase_price) avg_price,
    SUM(pi.line_total - pi.gst_amount) subtotal,
    SUM(pi.gst_amount) gst,
    SUM(pi.line_total) total_amount
    FROM purchase_items pi
    JOIN purchases p ON p.id=pi.purchase_id
    JOIN items i ON i.id=pi.item_id
    $whereSql GROUP BY pi.item_id ORDER BY total_amount DESC");
$stmt->execute($params); $itemWise = $stmt->fetchAll();

// ── Vendor-wise breakdown ─────────────────────────────────────
$stmt = $pdo->prepare("SELECT v.vendor_name, v.vendor_code,
    COUNT(DISTINCT p.id) orders,
    SUM(p.total_amount) subtotal, SUM(p.gst_amount) gst, SUM(p.grand_total) total,
    SUM(p.grand_total - p.paid_amount) outstanding
    FROM purchases p JOIN purchase_vendors v ON v.id=p.vendor_id
    $whereSql GROUP BY p.vendor_id ORDER BY total DESC");
$stmt->execute($params); $vendorWise = $stmt->fetchAll();

// ── Handle Export ─────────────────────────────────────────────
$export = $_GET['export'] ?? '';
if ($export) {
    $filename = 'purchase_report_' . $fromDate . '_to_' . $toDate;
    if ($export === 'csv') {
        header('Content-Type: text/csv');
        header("Content-Disposition: attachment; filename=\"$filename.csv\"");
        $fp = fopen('php://output','w');
        fputcsv($fp, ['Purchase No','Vendor','Date','Invoice No','Subtotal','GST','Grand Total','Paid','Outstanding','Status']);
        foreach ($orders as $o) {
            fputcsv($fp, [$o['purchase_no'],$o['vendor_name'],date('d-M-Y',strtotime($o['purchase_date'])),
                $o['invoice_no'],$o['total_amount'],$o['gst_amount'],$o['grand_total'],
                $o['paid_amount'],$o['grand_total']-$o['paid_amount'],$o['payment_status']]);
        }
        fputcsv($fp, []);
        fputcsv($fp, ['--- ITEM WISE ---']);
        fputcsv($fp, ['Item','Code','Qty','Avg Price','Subtotal','GST','Total']);
        foreach ($itemWise as $i) {
            fputcsv($fp, [$i['item_name'],$i['item_code'],$i['total_qty'],
                number_format($i['avg_price'],2),$i['subtotal'],$i['gst'],$i['total_amount']]);
        }
        fclose($fp); exit;
    }
    if ($export === 'excel') {
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment; filename=\"$filename.xls\"");
        echo '<table border="1"><tr><th colspan="10"><b>Purchase Report: '.$fromDate.' to '.$toDate.'</b></th></tr>';
        echo '<tr><th>Purchase No</th><th>Vendor</th><th>Date</th><th>Invoice No</th><th>Subtotal</th><th>GST</th><th>Grand Total</th><th>Paid</th><th>Outstanding</th><th>Status</th></tr>';
        foreach ($orders as $o) {
            echo '<tr><td>'.htmlspecialchars($o['purchase_no']).'</td><td>'.htmlspecialchars($o['vendor_name']).'</td><td>'.date('d-M-Y',strtotime($o['purchase_date'])).'</td><td>'.htmlspecialchars($o['invoice_no']).'</td><td>'.$o['total_amount'].'</td><td>'.$o['gst_amount'].'</td><td>'.$o['grand_total'].'</td><td>'.$o['paid_amount'].'</td><td>'.($o['grand_total']-$o['paid_amount']).'</td><td>'.$o['payment_status'].'</td></tr>';
        }
        echo '</table>'; exit;
    }
    if ($export === 'pdf') {
        require_once __DIR__ . '/../../includes/PdfHelper.php';
        ob_start();
        $html = '<!DOCTYPE html><html><head><meta charset="UTF-8"><style>
        body{font-family:Arial,sans-serif;font-size:10px;padding:16px;}
        h2{color:#1D4E4A;} table{width:100%;border-collapse:collapse;margin-bottom:16px;}
        th{background:#1D4E4A;color:#fff;padding:5px;text-align:left;font-size:10px;}
        td{padding:4px 5px;border-bottom:1px solid #ddd;font-size:10px;}
        .num{text-align:right;} tr:nth-child(even) td{background:#f9f7f2;}
        .summary{display:table;width:100%;margin-bottom:12px;}
        .s{display:table-cell;padding:8px 12px;background:#f0f7f0;border:1px solid #ccc;text-align:center;}
        </style></head><body>';
        $html .= '<h2>Purchase Report</h2><p>Period: '.date('d-M-Y',strtotime($fromDate)).' to '.date('d-M-Y',strtotime($toDate)).'</p>';
        $html .= '<div class="summary"><div class="s"><div style="font-size:9px;color:#666;">ORDERS</div><strong>'.$summary['orders'].'</strong></div><div class="s"><div style="font-size:9px;color:#666;">SUBTOTAL</div><strong>&#8377;'.number_format($summary['subtotal'],2).'</strong></div><div class="s"><div style="font-size:9px;color:#666;">GST</div><strong>&#8377;'.number_format($summary['gst'],2).'</strong></div><div class="s"><div style="font-size:9px;color:#666;">TOTAL</div><strong>&#8377;'.number_format($summary['total'],2).'</strong></div><div class="s"><div style="font-size:9px;color:#666;">OUTSTANDING</div><strong style="color:#c00">&#8377;'.number_format($summary['outstanding'],2).'</strong></div></div>';
        $html .= '<h3>Purchase Orders</h3><table><tr><th>#</th><th>Purchase No</th><th>Vendor</th><th>Date</th><th class="num">Subtotal</th><th class="num">GST</th><th class="num">Total</th><th class="num">Paid</th><th class="num">Outstanding</th><th>Status</th></tr>';
        foreach ($orders as $i => $o) {
            $bal = $o['grand_total'] - $o['paid_amount'];
            $html .= '<tr><td>'.($i+1).'</td><td>'.htmlspecialchars($o['purchase_no']).'</td><td>'.htmlspecialchars($o['vendor_name']).'</td><td>'.date('d-M-Y',strtotime($o['purchase_date'])).'</td><td class="num">&#8377;'.number_format($o['total_amount'],2).'</td><td class="num">&#8377;'.number_format($o['gst_amount'],2).'</td><td class="num">&#8377;'.number_format($o['grand_total'],2).'</td><td class="num">&#8377;'.number_format($o['paid_amount'],2).'</td><td class="num" style="color:'.($bal>0?'#c00':'#080').'">&#8377;'.number_format($bal,2).'</td><td>'.strtoupper($o['payment_status']).'</td></tr>';
        }
        $html .= '</table><h3>Item-wise Breakdown</h3><table><tr><th>Item</th><th>Code</th><th class="num">Qty</th><th class="num">Avg Price</th><th class="num">Subtotal</th><th class="num">GST</th><th class="num">Total</th></tr>';
        foreach ($itemWise as $i) {
            $html .= '<tr><td>'.htmlspecialchars($i['item_name']).'</td><td>'.htmlspecialchars($i['item_code']).'</td><td class="num">'.$i['total_qty'].'</td><td class="num">&#8377;'.number_format($i['avg_price'],2).'</td><td class="num">&#8377;'.number_format($i['subtotal'],2).'</td><td class="num">&#8377;'.number_format($i['gst'],2).'</td><td class="num">&#8377;'.number_format($i['total_amount'],2).'</td></tr>';
        }
        $html .= '</table><h3>Vendor-wise Breakdown</h3><table><tr><th>Vendor</th><th class="num">Orders</th><th class="num">Subtotal</th><th class="num">GST</th><th class="num">Total</th><th class="num">Outstanding</th></tr>';
        foreach ($vendorWise as $v) {
            $html .= '<tr><td>'.htmlspecialchars($v['vendor_name']).'</td><td class="num">'.$v['orders'].'</td><td class="num">&#8377;'.number_format($v['subtotal'],2).'</td><td class="num">&#8377;'.number_format($v['gst'],2).'</td><td class="num">&#8377;'.number_format($v['total'],2).'</td><td class="num" style="color:'.($v['outstanding']>0?'#c00':'#080').'">&#8377;'.number_format($v['outstanding'],2).'</td></tr>';
        }
        $html .= '</table></body></html>';
        ob_end_clean();
        PdfHelper::output($html, $filename.'.pdf', true); exit;
    }
}

$pageTitle = 'Purchase Reports';
require_once __DIR__ . '/../../includes/header.php';
?>

<div class="page-header">
    <div><div class="eyebrow">Reports</div><h2 class="mb-0">Purchase Reports</h2></div>
    <div class="flex gap-8" style="flex-wrap:wrap;align-items:center;">
        <form method="GET" class="flex gap-8" style="flex-wrap:wrap;">
            <input type="date" name="from" value="<?= sanitize($fromDate) ?>">
            <input type="date" name="to"   value="<?= sanitize($toDate) ?>">
            <select name="vendor_id" style="min-width:160px;">
                <option value="0">All Vendors</option>
                <?php foreach ($vendors as $v): ?>
                <option value="<?= $v['id'] ?>" <?= $vendorId==$v['id']?'selected':'' ?>><?= sanitize($v['vendor_name']) ?></option>
                <?php endforeach; ?>
            </select>
            <input type="text" name="invoice_no" value="<?= sanitize($invoiceNo) ?>" placeholder="Invoice No filter" style="width:150px;">
            <button class="btn"><?= icon('filter') ?> Apply</button>
        </form>
        <?php $qe = http_build_query(['from'=>$fromDate,'to'=>$toDate,'vendor_id'=>$vendorId,'invoice_no'=>$invoiceNo]); ?>
        <a href="?<?= $qe ?>&export=csv"   class="btn"><?= icon('file-type-csv') ?> CSV</a>
        <a href="?<?= $qe ?>&export=excel" class="btn"><?= icon('file-spreadsheet') ?> Excel</a>
        <a href="?<?= $qe ?>&export=pdf"   target="_blank" class="btn"><?= icon('file-type-pdf') ?> PDF</a>
        <button class="btn" onclick="window.print()"><?= icon('printer') ?> Print</button>
    </div>
</div>

<!-- Summary Stats -->
<div class="stat-grid">
    <div class="stat-card"><div class="stat-label"><?= icon('shopping-cart') ?> Total Orders</div><div class="stat-value"><?= $summary['orders'] ?></div></div>
    <div class="stat-card"><div class="stat-label"><?= icon('receipt') ?> Subtotal</div><div class="stat-value"><?= formatMoney($summary['subtotal']) ?></div></div>
    <div class="stat-card"><div class="stat-label"><?= icon('percentage') ?> GST</div><div class="stat-value"><?= formatMoney($summary['gst']) ?></div></div>
    <div class="stat-card primary"><div class="stat-label"><?= icon('coins') ?> Grand Total</div><div class="stat-value"><?= formatMoney($summary['total']) ?></div></div>
    <div class="stat-card danger"><div class="stat-label"><?= icon('alert-circle') ?> Outstanding</div><div class="stat-value"><?= formatMoney($summary['outstanding']) ?></div></div>
</div>

<!-- All Purchase Orders -->
<div class="card">
    <div class="card-title"><?= icon('list') ?> Purchase Orders</div>
    <div class="table-wrap">
        <table>
            <thead><tr><th>#</th><th>Purchase No</th><th>Vendor</th><th>Date</th><th>Vendor Invoice</th><th class="text-right">Subtotal</th><th class="text-right">GST</th><th class="text-right">Total</th><th class="text-right">Paid</th><th class="text-right">Outstanding</th><th>Status</th></tr></thead>
            <tbody>
            <?php foreach ($orders as $i => $o):
                $bal = $o['grand_total'] - $o['paid_amount'];
            ?>
            <tr>
                <td><?= $i+1 ?></td>
                <td class="mono"><a href="../stock/purchases.php"><?= sanitize($o['purchase_no']) ?></a></td>
                <td><?= sanitize($o['vendor_name']) ?></td>
                <td><?= formatDate($o['purchase_date']) ?></td>
                <td class="text-sm"><?= sanitize($o['invoice_no']) ?></td>
                <td class="num"><?= formatMoney($o['total_amount']) ?></td>
                <td class="num"><?= formatMoney($o['gst_amount']) ?></td>
                <td class="num"><strong><?= formatMoney($o['grand_total']) ?></strong></td>
                <td class="num text-success"><?= formatMoney($o['paid_amount']) ?></td>
                <td class="num <?= $bal>0?'text-danger':'' ?>"><strong><?= formatMoney($bal) ?></strong></td>
                <td><span class="badge badge-<?= $o['payment_status']==='paid'?'success':($o['payment_status']==='partial'?'warning':'danger') ?>"><?= $o['payment_status'] ?></span></td>
            </tr>
            <?php endforeach; ?>
            <?php if (empty($orders)): ?><tr><td colspan="11" class="empty-state">No purchases found for this filter.</td></tr><?php endif; ?>
            </tbody>
        </table>
    </div>
</div>

<!-- Item-wise -->
<div class="card">
    <div class="card-title"><?= icon('package') ?> Item-wise Breakdown</div>
    <div class="table-wrap">
        <table>
            <thead><tr><th>#</th><th>Item Name</th><th>Code</th><th class="text-right">Qty Purchased</th><th class="text-right">Avg Price</th><th class="text-right">Subtotal</th><th class="text-right">GST</th><th class="text-right">Total Amount</th></tr></thead>
            <tbody>
            <?php foreach ($itemWise as $i => $item): ?>
            <tr>
                <td><?= $i+1 ?></td>
                <td><strong><?= sanitize($item['item_name']) ?></strong></td>
                <td class="mono"><?= sanitize($item['item_code']) ?></td>
                <td class="num"><?= number_format($item['total_qty']) ?></td>
                <td class="num"><?= formatMoney($item['avg_price']) ?></td>
                <td class="num"><?= formatMoney($item['subtotal']) ?></td>
                <td class="num"><?= formatMoney($item['gst']) ?></td>
                <td class="num"><strong><?= formatMoney($item['total_amount']) ?></strong></td>
            </tr>
            <?php endforeach; ?>
            <?php if (empty($itemWise)): ?><tr><td colspan="8" class="empty-state">No items found.</td></tr><?php endif; ?>
            </tbody>
        </table>
    </div>
</div>

<!-- Vendor-wise -->
<div class="card">
    <div class="card-title"><?= icon('truck-delivery') ?> Vendor-wise Breakdown</div>
    <div class="table-wrap">
        <table>
            <thead><tr><th>#</th><th>Vendor</th><th>Code</th><th class="text-right">Orders</th><th class="text-right">Subtotal</th><th class="text-right">GST</th><th class="text-right">Total</th><th class="text-right">Outstanding</th></tr></thead>
            <tbody>
            <?php foreach ($vendorWise as $i => $v): ?>
            <tr>
                <td><?= $i+1 ?></td>
                <td><strong><?= sanitize($v['vendor_name']) ?></strong></td>
                <td class="mono"><?= sanitize($v['vendor_code']) ?></td>
                <td class="num"><?= $v['orders'] ?></td>
                <td class="num"><?= formatMoney($v['subtotal']) ?></td>
                <td class="num"><?= formatMoney($v['gst']) ?></td>
                <td class="num"><strong><?= formatMoney($v['total']) ?></strong></td>
                <td class="num <?= $v['outstanding']>0?'text-danger':'' ?>"><strong><?= formatMoney($v['outstanding']) ?></strong></td>
            </tr>
            <?php endforeach; ?>
            </tbody>
        </table>
    </div>
</div>

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