<?php
$reportActive = 'leads';
$pageTitle = 'Leads Report';
require_once __DIR__ . '/../partials/top.php';

$pdo = db();
$dateFrom = $_GET['date_from'] ?? date('Y-m-d', strtotime('-30 days'));
$dateTo = $_GET['date_to'] ?? date('Y-m-d');
$dtFrom = $dateFrom . ' 00:00:00';
$dtTo = $dateTo . ' 23:59:59';

// Summary
$stmt = $pdo->prepare("SELECT COUNT(*) FROM leads WHERE created_at BETWEEN ? AND ?");
$stmt->execute([$dtFrom, $dtTo]);
$totalLeads = (int)$stmt->fetchColumn();

$stmt = $pdo->prepare("SELECT COUNT(*) FROM leads WHERE is_available=0 AND created_at BETWEEN ? AND ?");
$stmt->execute([$dtFrom, $dtTo]);
$convertedLeads = (int)$stmt->fetchColumn();

$stmt = $pdo->prepare("SELECT COUNT(*) FROM leads WHERE is_available=1 AND created_at BETWEEN ? AND ?");
$stmt->execute([$dtFrom, $dtTo]);
$availableLeads = (int)$stmt->fetchColumn();

$stmt = $pdo->prepare("SELECT COALESCE(SUM(lead_price),0) FROM leads WHERE created_at BETWEEN ? AND ?");
$stmt->execute([$dtFrom, $dtTo]);
$totalLeadValue = (int)$stmt->fetchColumn();

$conversionRate = $totalLeads > 0 ? round(($convertedLeads / $totalLeads) * 100, 1) : 0;

// By category
$stmt = $pdo->prepare("
  SELECT c.name, COUNT(l.id) cnt, COALESCE(SUM(l.lead_price),0) value
  FROM leads l LEFT JOIN categories c ON c.id=l.category_id
  WHERE l.created_at BETWEEN ? AND ?
  GROUP BY l.category_id, c.name ORDER BY cnt DESC
");
$stmt->execute([$dtFrom, $dtTo]);
$byCategory = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Daily trend
$stmt = $pdo->prepare("
  SELECT DATE(created_at) d, COUNT(*) cnt, COALESCE(SUM(lead_price),0) value
  FROM leads WHERE created_at BETWEEN ? AND ?
  GROUP BY DATE(created_at) ORDER BY d
");
$stmt->execute([$dtFrom, $dtTo]);
$dailyLeads = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Recent leads list
$stmt = $pdo->prepare("
  SELECT l.*, c.name category_name
  FROM leads l LEFT JOIN categories c ON c.id=l.category_id
  WHERE l.created_at BETWEEN ? AND ?
  ORDER BY l.created_at DESC LIMIT 50
");
$stmt->execute([$dtFrom, $dtTo]);
$recentLeads = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<div class="page-header">
  <h1>Leads Report</h1>
  <p class="subtitle">Lead generation and conversion analytics</p>
</div>

<div class="report-filters">
  <form method="get" class="row g-3 align-items-end">
    <div class="col-md-3"><label class="form-label">From Date</label><input type="date" name="date_from" class="form-control" value="<?php echo htmlspecialchars($dateFrom); ?>"></div>
    <div class="col-md-3"><label class="form-label">To Date</label><input type="date" name="date_to" class="form-control" value="<?php echo htmlspecialchars($dateTo); ?>"></div>
    <div class="col-md-4"><button type="submit" class="btn btn-dark">Apply</button></div>
  </form>
</div>

<div class="report-summary">
  <div class="summary-item"><div class="label">Total Leads</div><div class="value"><?php echo $totalLeads; ?></div></div>
  <div class="summary-item"><div class="label">Converted</div><div class="value text-success"><?php echo $convertedLeads; ?></div></div>
  <div class="summary-item"><div class="label">Available</div><div class="value"><?php echo $availableLeads; ?></div></div>
  <div class="summary-item"><div class="label">Conversion Rate</div><div class="value"><?php echo $conversionRate; ?>%</div></div>
  <div class="summary-item"><div class="label">Total Lead Value</div><div class="value">₹<?php echo number_format($totalLeadValue); ?></div></div>
</div>

<div class="row g-4">
  <div class="col-lg-6">
    <div class="card">
      <div class="card-header">Leads by Category</div>
      <div class="card-body">
        <div class="table-responsive">
          <table class="table">
            <thead><tr><th>Category</th><th>Count</th><th>Value</th></tr></thead>
            <tbody>
              <?php foreach ($byCategory as $r): ?>
              <tr><td><?php echo htmlspecialchars($r['name'] ?: 'Uncategorized'); ?></td><td><?php echo $r['cnt']; ?></td><td>₹<?php echo number_format($r['value']); ?></td></tr>
              <?php endforeach; ?>
              <?php if (empty($byCategory)): ?><tr><td colspan="3" class="text-muted">No data</td></tr><?php endif; ?>
            </tbody>
          </table>
        </div>
      </div>
    </div>
  </div>
  <div class="col-lg-6">
    <div class="card">
      <div class="card-header">Daily Lead Trend</div>
      <div class="card-body">
        <div class="table-responsive">
          <table class="table">
            <thead><tr><th>Date</th><th>Leads</th><th>Value</th></tr></thead>
            <tbody>
              <?php foreach ($dailyLeads as $r): ?>
              <tr><td><?php echo $r['d']; ?></td><td><?php echo $r['cnt']; ?></td><td>₹<?php echo number_format($r['value']); ?></td></tr>
              <?php endforeach; ?>
              <?php if (empty($dailyLeads)): ?><tr><td colspan="3" class="text-muted">No data</td></tr><?php endif; ?>
            </tbody>
          </table>
        </div>
      </div>
    </div>
  </div>
</div>

<div class="card mt-4">
  <div class="card-header">Recent Leads (Last 50)</div>
  <div class="card-body p-0">
    <div class="table-responsive">
      <table class="table mb-0">
        <thead><tr><th>ID</th><th>Title</th><th>Customer</th><th>Category</th><th>Price</th><th>Status</th><th>Created</th></tr></thead>
        <tbody>
          <?php foreach ($recentLeads as $l): ?>
          <tr>
            <td>#<?php echo $l['id']; ?></td>
            <td><?php echo htmlspecialchars($l['title']); ?></td>
            <td><?php echo htmlspecialchars($l['customer_name']); ?></td>
            <td><?php echo htmlspecialchars($l['category_name'] ?? '-'); ?></td>
            <td>₹<?php echo number_format($l['lead_price']); ?></td>
            <td><span class="badge bg-<?php echo $l['is_available'] ? 'success' : 'secondary'; ?>"><?php echo $l['is_available'] ? 'Available' : 'Converted'; ?></span></td>
            <td class="small"><?php echo date('M d, H:i', strtotime($l['created_at'])); ?></td>
          </tr>
          <?php endforeach; ?>
          <?php if (empty($recentLeads)): ?><tr><td colspan="7" class="text-muted">No leads in period</td></tr><?php endif; ?>
        </tbody>
      </table>
    </div>
  </div>
</div>
<?php require_once __DIR__ . '/../partials/bottom.php'; ?>
