<?php
$adminTitle = 'Reports';
require_once __DIR__ . '/includes/header.php';

// Revenue by month (last 12 months)
$monthlyRevenue = Database::fetchAll(
    "SELECT DATE_FORMAT(created_at, '%Y-%m') as month, SUM(amount_paid) as revenue, COUNT(*) as invoice_count
     FROM invoices WHERE status IN ('paid','partial') AND created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
     GROUP BY month ORDER BY month ASC"
);

// Top products by RFQ count
$topProducts = Database::fetchAll(
    "SELECT p.name, COUNT(qi.id) as rfq_count FROM quotation_items qi
     JOIN products p ON qi.product_id=p.id GROUP BY p.id ORDER BY rfq_count DESC LIMIT 10"
);

// Quote conversion rate
$totalQuotes = (int)(Database::fetchOne("SELECT COUNT(*) as c FROM quotations")['c'] ?? 0);
$convertedQuotes = (int)(Database::fetchOne("SELECT COUNT(*) as c FROM quotations WHERE status='approved'")['c'] ?? 0);
$conversionRate = $totalQuotes > 0 ? round(($convertedQuotes / $totalQuotes) * 100, 1) : 0;

// Customer summary
$newCustomers = (int)(Database::fetchOne("SELECT COUNT(*) as c FROM customers WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)")['c'] ?? 0);
?>

<div class="grid grid-cols-1 md:grid-cols-3 gap-4 mb-8">
  <div class="card-dark p-5">
    <div class="text-xs text-gray-400 mb-1">Quote Conversion Rate</div>
    <div class="text-3xl font-bold text-orange-500"><?= $conversionRate ?>%</div>
    <div class="text-xs text-gray-500"><?= $convertedQuotes ?> of <?= $totalQuotes ?> quotes approved</div>
  </div>
  <div class="card-dark p-5">
    <div class="text-xs text-gray-400 mb-1">New Customers (30 days)</div>
    <div class="text-3xl font-bold"><?= $newCustomers ?></div>
  </div>
  <div class="card-dark p-5">
    <div class="text-xs text-gray-400 mb-1">Total Revenue (PKR)</div>
    <div class="text-3xl font-bold text-green-400">
      <?= number_format(array_sum(array_column($monthlyRevenue, 'revenue')), 0) ?>
    </div>
  </div>
</div>

<div class="grid grid-cols-1 lg:grid-cols-2 gap-6">
  <!-- Monthly Revenue -->
  <div class="card-dark p-5">
    <h3 class="font-semibold mb-4 text-sm">Monthly Revenue (Last 12 Months)</h3>
    <?php foreach ($monthlyRevenue as $m): ?>
    <?php $maxRev = max(1, max(array_column($monthlyRevenue, 'revenue'))); ?>
    <div class="flex items-center gap-3 mb-2">
      <div class="text-xs text-gray-400 w-16"><?= $m['month'] ?></div>
      <div class="flex-1 h-4 bg-navy-600 rounded overflow-hidden">
        <div class="h-full bg-orange-600 rounded" style="width:<?= round($m['revenue'] / $maxRev * 100) ?>%"></div>
      </div>
      <div class="text-xs font-semibold w-28 text-right"><?= formatPrice($m['revenue']) ?></div>
    </div>
    <?php endforeach; ?>
    <?php if (empty($monthlyRevenue)): ?><div class="text-gray-400 text-sm">No revenue data yet.</div><?php endif; ?>
  </div>

  <!-- Top Products -->
  <div class="card-dark overflow-hidden">
    <div class="p-4 border-b border-navy-500"><h3 class="font-semibold text-sm">Top Requested Products</h3></div>
    <table class="data-table">
      <thead><tr><th>Product</th><th>RFQ Count</th></tr></thead>
      <tbody>
      <?php foreach ($topProducts as $p): ?>
      <tr>
        <td class="text-sm"><?= e($p['name']) ?></td>
        <td class="text-sm font-bold text-orange-500"><?= $p['rfq_count'] ?></td>
      </tr>
      <?php endforeach; ?>
      <?php if (empty($topProducts)): ?>
      <tr><td colspan="2" class="text-gray-400 text-sm text-center py-4">No data yet.</td></tr>
      <?php endif; ?>
      </tbody>
    </table>
  </div>
</div>

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