<?php
$reportActive = 'user';
$pageTitle = 'User & Customer 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';

$userTablesExist = false;
try { $pdo->query("SELECT 1 FROM users LIMIT 1"); $userTablesExist = true; } catch (Exception $e) {}

// App users (from users table)
$appUsersNew = 0;
$appUsersTotal = 0;
$userOrdersCount = 0;
$userOrdersRevenue = 0;
$topAppUsers = [];
$recentUserOrders = [];
if ($userTablesExist) {
  $stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE created_at BETWEEN ? AND ?");
  $stmt->execute([$dtFrom, $dtTo]);
  $appUsersNew = (int)$stmt->fetchColumn();
  $appUsersTotal = (int)$pdo->query("SELECT COUNT(*) FROM users")->fetchColumn();
  $stmt = $pdo->prepare("SELECT COUNT(*), COALESCE(SUM(total_amount),0) FROM user_orders WHERE created_at BETWEEN ? AND ? AND payment_status='paid'");
  $stmt->execute([$dtFrom, $dtTo]);
  $row = $stmt->fetch();
  $userOrdersCount = (int)$row[0];
  $userOrdersRevenue = (int)$row[1];
  $stmt = $pdo->prepare("
    SELECT u.name, u.phone, u.email, COUNT(o.id) order_count, COALESCE(SUM(o.total_amount),0) total_spent
    FROM user_orders o
    JOIN users u ON u.id=o.user_id
    WHERE o.created_at BETWEEN ? AND ?
    GROUP BY o.user_id
    ORDER BY order_count DESC
    LIMIT 20
  ");
  $stmt->execute([$dtFrom, $dtTo]);
  $topAppUsers = $stmt->fetchAll(PDO::FETCH_ASSOC);
  $stmt = $pdo->prepare("
    SELECT o.id, o.order_number, o.status, o.payment_status, o.total_amount, o.created_at, u.name user_name, u.phone user_phone
    FROM user_orders o JOIN users u ON u.id=o.user_id
    WHERE o.created_at BETWEEN ? AND ?
    ORDER BY o.created_at DESC LIMIT 50
  ");
  $stmt->execute([$dtFrom, $dtTo]);
  $recentUserOrders = $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Customers from leads (unique by phone)
$stmt = $pdo->prepare("
  SELECT COUNT(DISTINCT customer_phone) cnt, COUNT(*) total_leads, COALESCE(SUM(lead_price),0) value
  FROM leads WHERE created_at BETWEEN ? AND ? AND (customer_phone IS NOT NULL AND customer_phone != '')
");
$stmt->execute([$dtFrom, $dtTo]);
$leadCustomers = $stmt->fetch(PDO::FETCH_ASSOC);

// Contact form submissions
$stmt = $pdo->prepare("SELECT COUNT(*) FROM contact_messages WHERE created_at BETWEEN ? AND ?");
$stmt->execute([$dtFrom, $dtTo]);
$contactCount = (int)$stmt->fetchColumn();

// Top customers by lead count (from leads)
$stmt = $pdo->prepare("
  SELECT customer_name, customer_phone, COUNT(*) lead_count, COALESCE(SUM(lead_price),0) total_value
  FROM leads
  WHERE created_at BETWEEN ? AND ? AND customer_phone IS NOT NULL AND customer_phone != ''
  GROUP BY customer_phone, customer_name
  ORDER BY lead_count DESC
  LIMIT 20
");
$stmt->execute([$dtFrom, $dtTo]);
$topCustomers = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Contact messages list
$stmt = $pdo->prepare("
  SELECT * FROM contact_messages
  WHERE created_at BETWEEN ? AND ?
  ORDER BY created_at DESC
  LIMIT 50
");
$stmt->execute([$dtFrom, $dtTo]);
$contactMessages = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Customer leads detail (recent)
$stmt = $pdo->prepare("
  SELECT l.id, l.customer_name, l.customer_phone, l.title, l.lead_price, l.is_available, l.created_at, 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]);
$customerLeads = $stmt->fetchAll(PDO::FETCH_ASSOC);

$uniqueCustomers = (int)($leadCustomers['cnt'] ?? 0);
$totalLeads = (int)($leadCustomers['total_leads'] ?? 0);
$totalValue = (int)($leadCustomers['value'] ?? 0);
$avgPerCustomer = $uniqueCustomers > 0 ? round($totalLeads / $uniqueCustomers, 1) : 0;
?>
<div class="page-header">
  <h1>User & Customer Report</h1>
  <p class="subtitle">Customer analytics and contact enquiries</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">Unique Customers (Leads)</div><div class="value"><?php echo $uniqueCustomers; ?></div></div>
  <div class="summary-item"><div class="label">Total Leads (Bookings)</div><div class="value"><?php echo $totalLeads; ?></div></div>
  <div class="summary-item"><div class="label">Total Lead Value</div><div class="value">₹<?php echo number_format($totalValue); ?></div></div>
  <div class="summary-item"><div class="label">Avg Leads per Customer</div><div class="value"><?php echo $avgPerCustomer; ?></div></div>
  <div class="summary-item"><div class="label">Contact Enquiries</div><div class="value"><?php echo $contactCount; ?></div></div>
  <?php if ($userTablesExist): ?>
  <div class="summary-item"><div class="label">New App Users</div><div class="value"><?php echo $appUsersNew; ?></div></div>
  <div class="summary-item"><div class="label">Total App Users</div><div class="value"><?php echo $appUsersTotal; ?></div></div>
  <div class="summary-item"><div class="label">User Orders (Paid)</div><div class="value"><?php echo $userOrdersCount; ?></div></div>
  <div class="summary-item"><div class="label">User Orders Revenue</div><div class="value text-success">₹<?php echo number_format($userOrdersRevenue); ?></div></div>
  <?php endif; ?>
</div>

<div class="row g-4">
  <div class="col-lg-6">
    <div class="card">
      <div class="card-header">Top Customers by Lead Count</div>
      <div class="card-body p-0">
        <div class="table-responsive">
          <table class="table mb-0">
            <thead><tr><th>Customer</th><th>Phone</th><th>Leads</th><th>Total Value</th></tr></thead>
            <tbody>
              <?php foreach ($topCustomers as $c): ?>
              <tr>
                <td><?php echo htmlspecialchars($c['customer_name']); ?></td>
                <td><?php echo htmlspecialchars($c['customer_phone']); ?></td>
                <td><?php echo $c['lead_count']; ?></td>
                <td>₹<?php echo number_format($c['total_value']); ?></td>
              </tr>
              <?php endforeach; ?>
              <?php if (empty($topCustomers)): ?><tr><td colspan="4" 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">Contact Form Enquiries</div>
      <div class="card-body p-0">
        <div class="table-responsive">
          <table class="table mb-0">
            <thead><tr><th>Name</th><th>Email</th><th>Phone</th><th>Date</th></tr></thead>
            <tbody>
              <?php foreach ($contactMessages as $m): ?>
              <tr>
                <td><?php echo htmlspecialchars($m['name']); ?></td>
                <td><?php echo htmlspecialchars($m['email']); ?></td>
                <td><?php echo htmlspecialchars($m['phone'] ?? '-'); ?></td>
                <td class="small"><?php echo date('M d, H:i', strtotime($m['created_at'])); ?></td>
              </tr>
              <?php endforeach; ?>
              <?php if (empty($contactMessages)): ?><tr><td colspan="4" class="text-muted">No enquiries in period</td></tr><?php endif; ?>
            </tbody>
          </table>
        </div>
      </div>
    </div>
  </div>
</div>

<?php if ($userTablesExist): ?>
<div class="row g-4 mt-2">
  <?php if (!empty($topAppUsers)): ?>
  <div class="col-lg-6">
    <div class="card">
      <div class="card-header">Top App Users by Orders (Period)</div>
      <div class="card-body p-0">
        <div class="table-responsive">
          <table class="table mb-0">
            <thead><tr><th>User</th><th>Phone</th><th>Orders</th><th>Total Spent</th></tr></thead>
            <tbody>
              <?php foreach ($topAppUsers as $u): ?>
              <tr>
                <td><?php echo htmlspecialchars($u['name']); ?></td>
                <td><?php echo htmlspecialchars($u['phone']); ?></td>
                <td><?php echo (int)$u['order_count']; ?></td>
                <td>₹<?php echo number_format($u['total_spent']); ?></td>
              </tr>
              <?php endforeach; ?>
            </tbody>
          </table>
        </div>
      </div>
    </div>
  </div>
  <?php endif; ?>
</div>
<?php endif; ?>

<?php if ($userTablesExist && !empty($recentUserOrders)): ?>
<div class="card mt-4">
  <div class="card-header">Recent User Orders (App)</div>
  <div class="card-body p-0">
    <div class="table-responsive">
      <table class="table mb-0">
        <thead><tr><th>Order #</th><th>Customer</th><th>Phone</th><th>Amount</th><th>Status</th><th>Date</th></tr></thead>
        <tbody>
          <?php foreach ($recentUserOrders as $o): ?>
          <tr>
            <td><a href="../user_order_view.php?id=<?php echo (int)$o['id']; ?>"><?php echo htmlspecialchars($o['order_number']); ?></a></td>
            <td><?php echo htmlspecialchars($o['user_name']); ?></td>
            <td><?php echo htmlspecialchars($o['user_phone']); ?></td>
            <td>₹<?php echo number_format($o['total_amount']); ?></td>
            <td><span class="badge bg-<?php echo $o['payment_status']==='paid'?'success':'warning'; ?>"><?php echo $o['payment_status']; ?></span></td>
            <td class="small"><?php echo date('M d, H:i', strtotime($o['created_at'])); ?></td>
          </tr>
          <?php endforeach; ?>
        </tbody>
      </table>
    </div>
  </div>
</div>
<?php endif; ?>

<div class="card mt-4">
  <div class="card-header">Recent Customer Leads</div>
  <div class="card-body p-0">
    <div class="table-responsive">
      <table class="table mb-0">
        <thead><tr><th>Customer</th><th>Phone</th><th>Service</th><th>Category</th><th>Price</th><th>Status</th><th>Date</th></tr></thead>
        <tbody>
          <?php foreach ($customerLeads as $l): ?>
          <tr>
            <td><?php echo htmlspecialchars($l['customer_name']); ?></td>
            <td><?php echo htmlspecialchars($l['customer_phone'] ?? '-'); ?></td>
            <td><?php echo htmlspecialchars($l['title']); ?></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($customerLeads)): ?><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'; ?>
