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

// ── Filters ──────────────────────────────────────────────────
$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'));

$where  = "WHERE i.status != 'cancelled'";
$params = [];

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

// Main query — one row per invoice_item
$sql = "
    SELECT
        c.name            AS customer_name,
        i.invoice_number,
        i.invoice_date,
        ii.product_name   AS product_name,
        COALESCE(p.sku,'') AS hsn_code,
        ii.quantity,
        ii.unit_price,
        ii.gst_percent,
        (ii.gst_percent / 2) AS cgst_percent,
        (ii.gst_percent / 2) AS sgst_percent,
        ii.gst_amount,
        (ii.gst_amount / 2) AS cgst_amount,
        (ii.gst_amount / 2) AS sgst_amount,
        ii.line_total,
        i.total_amount,
        i.subtotal,
        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
    $where
    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);

// Row-level totals for table
$totalQty = 0;
foreach ($rows as $r) {
    $totalQty += $r['quantity'];
}

// Invoice summary totals (correctly account for discounts)
$summarySql = "SELECT COALESCE(SUM(subtotal),0) AS total_sales, COALESCE(SUM(discount_amount),0) AS total_discount, COALESCE(SUM(gst_amount),0) AS total_gst, COALESCE(SUM(total_amount),0) AS grand_total FROM invoices i $where";
$summaryStmt = $db->prepare($summarySql);
$summaryStmt->execute($params);
$summary = $summaryStmt->fetch(PDO::FETCH_ASSOC);

$totalSales = $summary['total_sales'];
$totalDiscount = $summary['total_discount'];
$totalGST = $summary['total_gst'];
$grandTotal = $summary['grand_total'];

// ── Export handlers ───────────────────────────────────────────
$export = trim($_GET['export'] ?? '');

if ($export === 'excel') {
    // Simple Excel XML (opens in Excel/LibreOffice)
    header('Content-Type: application/vnd.ms-excel; charset=utf-8');
    header('Content-Disposition: attachment; filename="Sales_Report_' . date('Ymd_His') . '.xls"');
    header('Cache-Control: no-cache');
    echo "\xEF\xBB\xBF"; // UTF-8 BOM

    $months = ['','January','February','March','April','May','June','July','August','September','October','November','December'];
    $label  = 'All Data';
    if ($filter_type === 'date' && $filter_date)  $label = date('d-M-Y', strtotime($filter_date));
    elseif ($filter_type === 'month')              $label = $months[$filter_month] . ' ' . $filter_year;
    elseif ($filter_type === 'year')               $label = 'Year ' . $filter_year;

    echo "<html xmlns:o='urn:schemas-microsoft-com:office:office'
               xmlns:x='urn:schemas-microsoft-com:office:excel'
               xmlns='http://www.w3.org/TR/REC-html40'>
<head><meta charset='UTF-8'>
<style>
  .header { background:#6174f5; color:white; font-weight:bold; text-align:center; font-size:14pt; }
  .subheader { background:#e0e9ff; color:#1e1f52; font-weight:bold; text-align:center; }
  .th { background:#1e1f52; color:white; font-weight:bold; border:1px solid #999; }
  .td { border:1px solid #ddd; }
  .num { text-align:right; border:1px solid #ddd; }
  .total { background:#f0f4ff; font-weight:bold; border:1px solid #999; }
  .totalnum { background:#f0f4ff; font-weight:bold; text-align:right; border:1px solid #999; }
</style>
</head><body>
<table>
<tr><td colspan='10' class='header'>SALES REPORT — " . htmlspecialchars($label) . "</td></tr>
<tr><td colspan='10' class='subheader'>Generated: " . date('d M Y H:i') . "</td></tr>
<tr></tr>
<tr>
  <td class='th'>S.No</td>
  <td class='th'>Customer Name</td>
  <td class='th'>Product / Sales</td>
  <td class='th'>HSN Code</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'>CGST Amt (₹)</td>
  <td class='th'>SGST Amt (₹)</td>
  <td class='th'>Total (₹)</td>
  <td class='th'>Date</td>
</tr>\n";

    $sno = 1;
    foreach ($rows as $r) {
        $base = $r['line_total'] - $r['gst_amount'];
        echo "<tr>
  <td class='td'>{$sno}</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='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['cgst_amount'], 2) . "</td>
  <td class='num'>" . number_format($r['sgst_amount'], 2) . "</td>
  <td class='num'>" . number_format($r['line_total'], 2) . "</td>
  <td class='td'>" . date('d-M-Y', strtotime($r['invoice_date'])) . "</td>
</tr>\n";
        $sno++;
    }

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

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

// ── Available years from DB ───────────────────────────────────
$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'];

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

<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">Sales Report</h2>
        <p class="text-sm text-slate-400"><?= count($rows) ?> line item<?= count($rows) != 1 ? 's' : '' ?></p>
    </div>
    <div class="flex gap-2">
        <a href="?<?= http_build_query(array_merge($_GET, ['export'=>'excel'])) ?>"
           class="btn-primary text-sm">
            <i class="fas fa-file-excel"></i> Download Excel
        </a>
        <a href="?<?= http_build_query(array_merge($_GET, ['export'=>'csv'])) ?>"
           class="btn-outline text-sm">
            <i class="fas fa-file-csv"></i> Download CSV
        </a>
    </div>
</div>

<!-- Filter Card -->
<div class="card p-4 mb-5">
    <form method="GET" class="flex flex-wrap gap-3 items-end">
        <div>
            <label class="block text-xs font-semibold text-slate-500 mb-1">Filter By</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>
            <button type="submit" class="btn-primary">
                <i class="fas fa-filter"></i> Apply
            </button>
        </div>
        <?php if ($filter_type !== 'all'): ?>
        <div>
            <a href="sales_report.php" class="btn-outline">
                <i class="fas fa-times"></i> Clear
            </a>
        </div>
        <?php endif; ?>
    </form>
</div>

<!-- Summary Cards -->
<div class="grid grid-cols-2 lg:grid-cols-5 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 Sales (excl. GST)</p>
        <p class="text-2xl font-bold text-slate-800"><?= formatCurrency($totalSales) ?></p>
    </div>
    <div class="card p-4">
        <p class="text-xs text-slate-400 font-semibold uppercase tracking-wide mb-1">Total Discount</p>
        <p class="text-2xl font-bold text-slate-800">- <?= formatCurrency($totalDiscount) ?></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>

<!-- Report Table -->
<div class="card overflow-hidden">
    <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">Customer</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-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">Qty</th>
                    <th class="text-right px-4 py-3.5 text-slate-500 font-semibold text-xs uppercase tracking-wide">Unit Price</th>
                    <th class="text-right px-4 py-3.5 text-slate-500 font-semibold text-xs uppercase tracking-wide">CGST Amt</th>
                    <th class="text-right px-4 py-3.5 text-slate-500 font-semibold text-xs uppercase tracking-wide">SGST Amt</th>
                    <th class="text-right px-4 py-3.5 text-slate-500 font-semibold text-xs uppercase tracking-wide">Total</th>
                    <th class="text-left px-4 py-3.5 text-slate-500 font-semibold text-xs uppercase tracking-wide">Date</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-bar text-3xl mb-2 block"></i>
                        No sales data found for the selected filter.
                    </td>
                </tr>
                <?php else: $sno=1; foreach ($rows as $r): ?>
                <tr class="table-row">
                    <td class="px-4 py-3 text-slate-500 text-xs"><?= $sno++ ?></td>
                    <td class="px-4 py-3 font-medium text-slate-800"><?= htmlspecialchars($r['customer_name']) ?></td>
                    <td class="px-4 py-3 text-slate-700"><?= 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-right text-slate-600"><?= number_format($r['cgst_percent'],2) ?>%</td>
                    <td class="px-4 py-3 text-right text-slate-600"><?= number_format($r['sgst_percent'],2) ?>%</td>
                    <td class="px-4 py-3 text-right text-slate-700 font-medium"><?= $r['quantity'] ?></td>
                    <td class="px-4 py-3 text-right text-slate-600"><?= formatCurrency($r['unit_price']) ?></td>
                    <td class="px-4 py-3 text-right text-slate-600"><?= formatCurrency($r['cgst_amount']) ?></td>
                    <td class="px-4 py-3 text-right text-slate-600"><?= formatCurrency($r['sgst_amount']) ?></td>
                    <td class="px-4 py-3 text-right font-bold text-slate-800"><?= formatCurrency($r['line_total']) ?></td>
                    <td class="px-4 py-3 text-slate-500 text-xs"><?= formatDate($r['invoice_date']) ?></td>
                </tr>
                <?php endforeach; endif; ?>
            </tbody>
            <?php if (!empty($rows)): ?>
            <tfoot class="bg-slate-50 border-t-2 border-slate-200">
                <tr>
                    <td colspan="6" class="px-4 py-3 font-bold text-slate-700 text-sm">TOTAL</td>
                    <td class="px-4 py-3 text-right font-bold text-slate-800"><?= $totalQty ?></td>
                    <td class="px-4 py-3"></td>
                    <td class="px-4 py-3 text-right font-bold text-slate-800"><?= formatCurrency($totalGST/2) ?></td>
                    <td class="px-4 py-3 text-right font-bold text-slate-800"><?= formatCurrency($totalGST/2) ?></td>
                    <td class="px-4 py-3 text-right font-bold text-brand-700 text-base"><?= formatCurrency($grandTotal) ?></td>
                    <td class="px-4 py-3"></td>
                </tr>
            </tfoot>
            <?php endif; ?>
        </table>
    </div>
</div>

<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'; ?>
