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

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

$customers = $pdo->query("SELECT * FROM sale_vendors ORDER BY customer_name")->fetchAll();

$params = [$fromDate, $toDate];
$where  = ["i.invoice_date BETWEEN ? AND ?", "i.invoice_type='sale'"];
if ($customerId > 0)  { $where[] = "i.customer_id = ?";  $params[] = $customerId; }
if ($invoiceNo !== '') { $where[] = "i.invoice_no LIKE ?"; $params[] = "%$invoiceNo%"; }
$whereSql = 'WHERE ' . implode(' AND ', $where);

// ── Summary ──────────────────────────────────────────────────
$stmt = $pdo->prepare("SELECT COUNT(*) invoices,
    COALESCE(SUM(i.subtotal),0) subtotal,
    COALESCE(SUM(i.cgst_amount+i.sgst_amount),0) gst,
    COALESCE(SUM(i.grand_total),0) total,
    COALESCE(SUM(i.paid_amount),0) received,
    COALESCE(SUM(i.grand_total - i.paid_amount),0) outstanding
    FROM invoices i $whereSql");
$stmt->execute($params); $summary = $stmt->fetch();

// ── All Invoices ──────────────────────────────────────────────
$stmt = $pdo->prepare("SELECT i.*, c.customer_name, c.customer_code
    FROM invoices i JOIN sale_vendors c ON c.id=i.customer_id
    $whereSql ORDER BY i.invoice_date DESC, i.id DESC");
$stmt->execute($params); $invoices = $stmt->fetchAll();

// ── Customer-wise ─────────────────────────────────────────────
$stmt = $pdo->prepare("SELECT c.customer_name, c.customer_code,
    COUNT(*) invoices, SUM(i.grand_total) total, SUM(i.paid_amount) received,
    SUM(i.grand_total - i.paid_amount) outstanding
    FROM invoices i JOIN sale_vendors c ON c.id=i.customer_id
    $whereSql GROUP BY i.customer_id ORDER BY total DESC");
$stmt->execute($params); $customerWise = $stmt->fetchAll();

// ── Item-wise sales ───────────────────────────────────────────
$iparams = [$fromDate, $toDate];
$iwhere  = ["inv.invoice_date BETWEEN ? AND ?", "inv.invoice_type='sale'"];
if ($customerId > 0)  { $iwhere[] = "inv.customer_id = ?"; $iparams[] = $customerId; }
$iwhereSql = 'WHERE ' . implode(' AND ', $iwhere);

$stmt = $pdo->prepare("SELECT i.item_name, i.item_code,
    SUM(ii.qty) total_qty,
    AVG(ii.unit_price) avg_rate,
    SUM(ii.line_subtotal) subtotal,
    SUM(ii.line_cgst + ii.line_sgst) gst,
    SUM(ii.line_total) total_amount,
    GROUP_CONCAT(DISTINCT c.customer_name ORDER BY c.customer_name SEPARATOR ', ') customers,
    GROUP_CONCAT(DISTINCT inv.invoice_no ORDER BY inv.invoice_no SEPARATOR ', ') invoice_nos
    FROM invoice_items ii
    JOIN invoices inv ON inv.id=ii.invoice_id
    JOIN items i ON i.id=ii.item_id
    JOIN sale_vendors c ON c.id=inv.customer_id
    $iwhereSql GROUP BY ii.item_id ORDER BY total_amount DESC");
$stmt->execute($iparams); $itemWise = $stmt->fetchAll();

// ── Export ────────────────────────────────────────────────────
$export = $_GET['export'] ?? '';
if ($export) {
    $filename = 'sales_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, ['Invoice No','Customer','Date','Subtotal','CGST','SGST','Grand Total','Received','Outstanding','Status']);
        foreach ($invoices as $inv) {
            fputcsv($fp, [$inv['invoice_no'],$inv['customer_name'],date('d-M-Y',strtotime($inv['invoice_date'])),
                $inv['subtotal'],$inv['cgst_amount'],$inv['sgst_amount'],$inv['grand_total'],
                $inv['paid_amount'],$inv['grand_total']-$inv['paid_amount'],$inv['payment_status']]);
        }
        fputcsv($fp, []);
        fputcsv($fp, ['--- ITEM WISE ---']);
        fputcsv($fp, ['Item','Code','Qty Sold','Avg Rate','Subtotal','GST','Total','Customers','Invoices']);
        foreach ($itemWise as $i) {
            fputcsv($fp, [$i['item_name'],$i['item_code'],$i['total_qty'],
                number_format($i['avg_rate'],2),$i['subtotal'],$i['gst'],$i['total_amount'],
                $i['customers'],$i['invoice_nos']]);
        }
        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>Sales Report: '.$fromDate.' to '.$toDate.'</b></th></tr>';
        echo '<tr><th>Invoice No</th><th>Customer</th><th>Date</th><th>Subtotal</th><th>CGST</th><th>SGST</th><th>Grand Total</th><th>Received</th><th>Outstanding</th><th>Status</th></tr>';
        foreach ($invoices as $inv) {
            echo '<tr><td>'.htmlspecialchars($inv['invoice_no']).'</td><td>'.htmlspecialchars($inv['customer_name']).'</td><td>'.date('d-M-Y',strtotime($inv['invoice_date'])).'</td><td>'.$inv['subtotal'].'</td><td>'.$inv['cgst_amount'].'</td><td>'.$inv['sgst_amount'].'</td><td>'.$inv['grand_total'].'</td><td>'.$inv['paid_amount'].'</td><td>'.($inv['grand_total']-$inv['paid_amount']).'</td><td>'.$inv['payment_status'].'</td></tr>';
        }
        echo '</table>'; exit;
    }
    if ($export === 'pdf') {
        require_once __DIR__ . '/../../includes/PdfHelper.php';
        $html = '<!DOCTYPE html><html><head><meta charset="UTF-8"><style>body{font-family:Arial,sans-serif;font-size:10px;padding:16px;}h2,h3{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;background:#f0f7f0;border:1px solid #ccc;text-align:center;}</style></head><body>';
        $html .= '<h2>Sales 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;">INVOICES</div><strong>'.$summary['invoices'].'</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;">RECEIVED</div><strong>&#8377;'.number_format($summary['received'],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>Invoices</h3><table><tr><th>Invoice No</th><th>Customer</th><th>Date</th><th class="num">Subtotal</th><th class="num">CGST</th><th class="num">SGST</th><th class="num">Total</th><th class="num">Received</th><th class="num">Outstanding</th><th>Status</th></tr>';
        foreach ($invoices as $inv) {
            $bal = $inv['grand_total'] - $inv['paid_amount'];
            $html .= '<tr><td>'.htmlspecialchars($inv['invoice_no']).'</td><td>'.htmlspecialchars($inv['customer_name']).'</td><td>'.date('d-M-Y',strtotime($inv['invoice_date'])).'</td><td class="num">&#8377;'.number_format($inv['subtotal'],2).'</td><td class="num">&#8377;'.number_format($inv['cgst_amount'],2).'</td><td class="num">&#8377;'.number_format($inv['sgst_amount'],2).'</td><td class="num">&#8377;'.number_format($inv['grand_total'],2).'</td><td class="num">&#8377;'.number_format($inv['paid_amount'],2).'</td><td class="num" style="color:'.($bal>0?'#c00':'#080').'">&#8377;'.number_format($bal,2).'</td><td>'.strtoupper($inv['payment_status']).'</td></tr>';
        }
        $html .= '</table><h3>Item-wise Sales</h3><table><tr><th>Item</th><th>Code</th><th class="num">Qty</th><th class="num">Avg Rate</th><th class="num">Subtotal</th><th class="num">GST</th><th class="num">Total</th><th>Customers</th><th>Invoices</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_rate'],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><td>'.htmlspecialchars(substr($i['customers'],0,40)).'</td><td>'.htmlspecialchars(substr($i['invoice_nos'],0,40)).'</td></tr>';
        }
        $html .= '</table></body></html>';
        PdfHelper::output($html, $filename.'.pdf', true); exit;
    }
}

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

<div class="page-header">
    <div><div class="eyebrow">Reports</div><h2 class="mb-0">Sales 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="customer_id" style="min-width:160px;">
                <option value="0">All Customers</option>
                <?php foreach ($customers as $c): ?>
                <option value="<?= $c['id'] ?>" <?= $customerId==$c['id']?'selected':'' ?>><?= sanitize($c['customer_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,'customer_id'=>$customerId,'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('receipt') ?> Invoices</div><div class="stat-value"><?= $summary['invoices'] ?></div></div>
    <div class="stat-card"><div class="stat-label"><?= icon('coins') ?> 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('cash') ?> Grand Total</div><div class="stat-value"><?= formatMoney($summary['total']) ?></div></div>
    <div class="stat-card success"><div class="stat-label"><?= icon('arrow-down-circle') ?> Received</div><div class="stat-value"><?= formatMoney($summary['received']) ?></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 Invoices -->
<div class="card">
    <div class="card-title"><?= icon('receipt') ?> Invoices</div>
    <div class="table-wrap">
        <table>
            <thead><tr><th>#</th><th>Invoice No</th><th>Customer</th><th>Date</th><th class="text-right">Subtotal</th><th class="text-right">CGST</th><th class="text-right">SGST</th><th class="text-right">Total</th><th class="text-right">Received</th><th class="text-right">Outstanding</th><th>Status</th></tr></thead>
            <tbody>
            <?php foreach ($invoices as $i => $inv):
                $bal = $inv['grand_total'] - $inv['paid_amount'];
            ?>
            <tr>
                <td><?= $i+1 ?></td>
                <td class="mono"><a href="../invoice/invoice_view.php?id=<?= $inv['id'] ?>"><?= sanitize($inv['invoice_no']) ?></a></td>
                <td><?= sanitize($inv['customer_name']) ?></td>
                <td><?= formatDate($inv['invoice_date']) ?></td>
                <td class="num"><?= formatMoney($inv['subtotal']) ?></td>
                <td class="num"><?= formatMoney($inv['cgst_amount']) ?></td>
                <td class="num"><?= formatMoney($inv['sgst_amount']) ?></td>
                <td class="num"><strong><?= formatMoney($inv['grand_total']) ?></strong></td>
                <td class="num text-success"><?= formatMoney($inv['paid_amount']) ?></td>
                <td class="num <?= $bal>0?'text-danger':'' ?>"><strong><?= formatMoney($bal) ?></strong></td>
                <td><span class="badge badge-<?= $inv['payment_status']==='paid'?'success':($inv['payment_status']==='partial'?'warning':'danger') ?>"><?= $inv['payment_status'] ?></span></td>
            </tr>
            <?php endforeach; ?>
            <?php if (empty($invoices)): ?><tr><td colspan="11" class="empty-state">No invoices found.</td></tr><?php endif; ?>
            </tbody>
        </table>
    </div>
</div>

<!-- Item-wise Sales -->
<div class="card">
    <div class="card-title"><?= icon('package') ?> Item-wise Sales Breakdown</div>
    <div class="table-wrap">
        <table>
            <thead><tr><th>#</th><th>Item Name</th><th>Code</th><th class="text-right">Qty Sold</th><th class="text-right">Avg Rate</th><th class="text-right">Subtotal</th><th class="text-right">GST</th><th class="text-right">Total</th><th>Customers</th><th>Invoices</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_rate']) ?></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>
                <td class="text-sm text-muted"><?= sanitize(substr($item['customers'],0,40)) ?></td>
                <td class="text-sm mono"><?= sanitize(substr($item['invoice_nos'],0,40)) ?></td>
            </tr>
            <?php endforeach; ?>
            <?php if (empty($itemWise)): ?><tr><td colspan="10" class="empty-state">No items found.</td></tr><?php endif; ?>
            </tbody>
        </table>
    </div>
</div>

<!-- Customer-wise -->
<div class="card">
    <div class="card-title"><?= icon('users') ?> Customer-wise Breakdown</div>
    <div class="table-wrap">
        <table>
            <thead><tr><th>#</th><th>Customer</th><th>Code</th><th class="text-right">Invoices</th><th class="text-right">Total</th><th class="text-right">Received</th><th class="text-right">Outstanding</th></tr></thead>
            <tbody>
            <?php foreach ($customerWise as $i => $c): ?>
            <tr>
                <td><?= $i+1 ?></td>
                <td><strong><?= sanitize($c['customer_name']) ?></strong></td>
                <td class="mono"><?= sanitize($c['customer_code']) ?></td>
                <td class="num"><?= $c['invoices'] ?></td>
                <td class="num"><strong><?= formatMoney($c['total']) ?></strong></td>
                <td class="num text-success"><?= formatMoney($c['received']) ?></td>
                <td class="num <?= $c['outstanding']>0?'text-danger':'' ?>"><strong><?= formatMoney($c['outstanding']) ?></strong></td>
            </tr>
            <?php endforeach; ?>
            </tbody>
        </table>
    </div>
</div>

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