<?php
require_once __DIR__ . '/../config.php';
requireAdmin();
$db = getDB();
$admin_page = 'reports';

// Stats queries
$total_revenue = $db->query("SELECT SUM(total) FROM orders WHERE order_status='delivered' OR payment_status='paid'")->fetchColumn() ?: 0;
$total_orders = $db->query("SELECT COUNT(*) FROM orders")->fetchColumn();
$total_leads = $db->query("SELECT COUNT(*) FROM crm_leads")->fetchColumn();
$converted_leads = $db->query("SELECT COUNT(*) FROM crm_leads WHERE status='converted'")->fetchColumn();

// Monthly Revenue
$monthly = $db->query("
    SELECT DATE_FORMAT(created_at, '%b %Y') as month, SUM(total) as revenue, COUNT(id) as orders
    FROM orders 
    WHERE order_status != 'cancelled'
    GROUP BY YEAR(created_at), MONTH(created_at)
    ORDER BY YEAR(created_at) DESC, MONTH(created_at) DESC
    LIMIT 12
")->fetchAll();

// Top Selling Products
$top_products = $db->query("
    SELECT product_name, SUM(qty) as total_qty, SUM(subtotal) as total_revenue
    FROM order_items
    GROUP BY product_id, product_name
    ORDER BY total_qty DESC
    LIMIT 5
")->fetchAll();

$page_title = 'Reports & Analytics';
include __DIR__ . '/header.php';
?>

<div style="display:grid;grid-template-columns:repeat(auto-fit, minmax(200px, 1fr));gap:20px;margin-bottom:28px;">
  <div class="stat-card" style="background:#fff;padding:24px;border-radius:12px;box-shadow:0 2px 8px rgba(0,0,0,.04);">
    <div style="color:var(--text-light);font-size:.85rem;font-weight:600;margin-bottom:8px;">TOTAL REVENUE</div>
    <div style="font-family:'Outfit',sans-serif;font-size:2rem;font-weight:800;color:var(--blue-main);"><?= rupee($total_revenue) ?></div>
  </div>
  <div class="stat-card" style="background:#fff;padding:24px;border-radius:12px;box-shadow:0 2px 8px rgba(0,0,0,.04);">
    <div style="color:var(--text-light);font-size:.85rem;font-weight:600;margin-bottom:8px;">TOTAL ORDERS</div>
    <div style="font-family:'Outfit',sans-serif;font-size:2rem;font-weight:800;color:var(--blue-dark);"><?= number_format($total_orders) ?></div>
  </div>
  <div class="stat-card" style="background:#fff;padding:24px;border-radius:12px;box-shadow:0 2px 8px rgba(0,0,0,.04);">
    <div style="color:var(--text-light);font-size:.85rem;font-weight:600;margin-bottom:8px;">LEAD CONVERSION</div>
    <div style="font-family:'Outfit',sans-serif;font-size:2rem;font-weight:800;color:#10b981;">
      <?= $total_leads > 0 ? round(($converted_leads / $total_leads) * 100) : 0 ?>%
    </div>
  </div>
</div>

<div style="display:grid;grid-template-columns:1fr 1fr;gap:24px;align-items:start;">
  <div class="card">
    <div class="card-header"><h2>Monthly Revenue (Last 12 Months)</h2></div>
    <div class="table-wrap">
      <table>
        <thead><tr><th>Month</th><th>Orders</th><th>Revenue</th></tr></thead>
        <tbody>
          <?php foreach ($monthly as $m): ?>
          <tr>
            <td><strong><?= h($m['month']) ?></strong></td>
            <td><?= $m['orders'] ?></td>
            <td style="color:var(--blue-main);font-weight:700;"><?= rupee($m['revenue']) ?></td>
          </tr>
          <?php endforeach; ?>
          <?php if(!$monthly): ?><tr><td colspan="3" style="text-align:center;">No data available</td></tr><?php endif; ?>
        </tbody>
      </table>
    </div>
  </div>

  <div class="card">
    <div class="card-header"><h2>Top Selling Products</h2></div>
    <div class="table-wrap">
      <table>
        <thead><tr><th>Product Name</th><th>Qty Sold</th><th>Revenue Gen.</th></tr></thead>
        <tbody>
          <?php foreach ($top_products as $p): ?>
          <tr>
            <td><strong><?= h($p['product_name']) ?></strong></td>
            <td><span class="badge" style="background:#fef3c7;color:#d97706;"><?= $p['total_qty'] ?> units</span></td>
            <td><?= rupee($p['total_revenue']) ?></td>
          </tr>
          <?php endforeach; ?>
          <?php if(!$top_products): ?><tr><td colspan="3" style="text-align:center;">No data available</td></tr><?php endif; ?>
        </tbody>
      </table>
    </div>
  </div>
</div>

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