<?php
/**
 * User Orders Report = Leads Report (User Orders = Customer Leads in this system)
 */
$reportActive = 'user_orders_report';
$pageTitle = 'User Orders Report (Leads)';
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';

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

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

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

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

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

$stmt = $pdo->prepare("SELECT COUNT(DISTINCT customer_phone) FROM leads WHERE created_at BETWEEN ? AND ? AND customer_phone IS NOT NULL AND customer_phone != ''");
$stmt->execute([$dtFrom, $dtTo]);
$uniqueCustomers = (int)$stmt->fetchColumn();

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

// By status (available vs converted)
$stmt = $pdo->prepare("SELECT COALESCE(SUM(lead_price),0) FROM leads WHERE is_available=1 AND created_at BETWEEN ? AND ?");
$stmt->execute([$dtFrom, $dtTo]);
$availableValue = (int)$stmt->fetchColumn();
$byStatus = [
  ['status' => 'Converted', 'cnt' => $convertedOrders, 'amt' => $convertedRevenue],
  ['status' => 'Available', 'cnt' => $availableOrders, 'amt' => $availableValue],
];

// By category
$stmt = $pdo->prepare("
  SELECT c.name, COUNT(l.id) cnt, COALESCE(SUM(l.lead_price),0) amt
  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 breakdown
$stmt = $pdo->prepare("
  SELECT DATE(created_at) d, COUNT(*) cnt, COALESCE(SUM(lead_price),0) amt
  FROM leads WHERE created_at BETWEEN ? AND ?
  GROUP BY DATE(created_at) ORDER BY d
");
$stmt->execute([$dtFrom, $dtTo]);
$dailyData = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Order list (leads)
$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 100
");
$stmt->execute([$dtFrom, $dtTo]);
$orders = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<div class="page-header">
  <h1>User Orders Report</h1>
  <p class="subtitle">Customer leads & bookings — User Orders = Leads in this system</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 Orders (Leads)</div><div class="value"><?php echo $totalOrders; ?></div></div>
  <div class="summary-item"><div class="label">Converted</div><div class="value text-success"><?php echo $convertedOrders; ?></div></div>
  <div class="summary-item"><div class="label">Available</div><div class="value"><?php echo $availableOrders; ?></div></div>
  <div class="summary-item"><div class="label">Total Lead Value</div><div class="value">₹<?php echo number_format($totalRevenue); ?></div></div>
  <div class="summary-item"><div class="label">Converted Revenue</div><div class="value text-success">₹<?php echo number_format($convertedRevenue); ?></div></div>
  <div class="summary-item"><div class="label">Unique Customers</div><div class="value"><?php echo $uniqueCustomers; ?></div></div>
  <div class="summary-item"><div class="label">Conversion Rate</div><div class="value"><?php echo $conversionRate; ?>%</div></div>
</div>

<div class="row g-4">
  <div class="col-lg-6">
    <div class="card">
      <div class="card-header">Orders by Status</div>
      <div class="card-body p-0">
        <div class="table-responsive">
          <table class="table mb-0">
            <thead><tr><th>Status</th><th>Count</th><th>Value</th></tr></thead>
            <tbody>
              <?php foreach ($byStatus as $s): ?>
              <tr>
                <td><span class="badge bg-<?php echo $s['status']==='Converted'?'success':'warning'; ?>"><?php echo htmlspecialchars($s['status']); ?></span></td>
                <td><?php echo $s['cnt']; ?></td>
                <td>₹<?php echo number_format($s['amt']); ?></td>
              </tr>
              <?php endforeach; ?>
              <?php if (empty($byStatus)): ?><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 Breakdown</div>
      <div class="card-body p-0">
        <div class="table-responsive" style="max-height: 300px; overflow-y: auto;">
          <table class="table mb-0">
            <thead><tr><th>Date</th><th>Orders</th><th>Value</th></tr></thead>
            <tbody>
              <?php foreach ($dailyData as $d): ?>
              <tr>
                <td><?php echo htmlspecialchars($d['d']); ?></td>
                <td><?php echo $d['cnt']; ?></td>
                <td>₹<?php echo number_format($d['amt']); ?></td>
              </tr>
              <?php endforeach; ?>
              <?php if (empty($dailyData)): ?><tr><td colspan="3" class="text-muted">No data</td></tr><?php endif; ?>
            </tbody>
          </table>
        </div>
      </div>
    </div>
  </div>
</div>

<div class="row g-4 mt-2">
  <div class="col-12">
    <div class="card">
      <div class="card-header">By Category</div>
      <div class="card-body p-0">
        <div class="table-responsive">
          <table class="table mb-0">
            <thead><tr><th>Category</th><th>Count</th><th>Value</th></tr></thead>
            <tbody>
              <?php foreach ($byCategory as $c): ?>
              <tr>
                <td><?php echo htmlspecialchars($c['name'] ?: 'Uncategorized'); ?></td>
                <td><?php echo $c['cnt']; ?></td>
                <td>₹<?php echo number_format($c['amt']); ?></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>

<div class="card mt-4">
  <div class="card-header">Recent Orders (Leads) — Last 100</div>
  <div class="card-body p-0">
    <div class="table-responsive">
      <table class="table mb-0">
        <thead><tr><th>Customer</th><th>Service</th><th>Category</th><th>Price</th><th>Status</th><th>Date</th><th></th></tr></thead>
        <tbody>
          <?php foreach ($orders as $o): ?>
          <tr>
            <td>
              <strong><?php echo htmlspecialchars($o['customer_name'] ?? '-'); ?></strong>
              <br><small class="text-muted"><?php echo htmlspecialchars($o['customer_phone'] ?? '-'); ?></small>
            </td>
            <td><?php echo htmlspecialchars($o['title']); ?></td>
            <td><?php echo htmlspecialchars($o['category_name'] ?? '-'); ?></td>
            <td>₹<?php echo number_format($o['lead_price']); ?></td>
            <td><span class="badge bg-<?php echo $o['is_available'] ? 'warning' : 'success'; ?>"><?php echo $o['is_available'] ? 'Available' : 'Converted'; ?></span></td>
            <td class="small"><?php echo date('M d, H:i', strtotime($o['created_at'])); ?></td>
            <td><a class="btn btn-sm btn-outline-dark" href="../leads.php">View</a></td>
          </tr>
          <?php endforeach; ?>
          <?php if (empty($orders)): ?><tr><td colspan="7" class="text-muted">No orders in period</td></tr><?php endif; ?>
        </tbody>
      </table>
    </div>
  </div>
</div>
<?php require_once __DIR__ . '/../partials/bottom.php'; ?>
