<?php
$active = 'dashboard';
$pageTitle = 'Overview Dashboard';
require_once __DIR__ . '/partials/top.php';

$pdo = db();

/** Helpers */
function qCount(PDO $pdo, string $sql): int {
  return (int)$pdo->query($sql)->fetchColumn();
}
function qSum(PDO $pdo, string $sql): int {
  return (int)$pdo->query($sql)->fetchColumn();
}
function safeDate($dt, $format = 'M d, H:i') {
  if (!$dt) return '—';
  $t = strtotime($dt);
  return $t ? date($format, $t) : '—';
}

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

// Core KPIs
$stats = [
  'vendors_total'     => qCount($pdo, "SELECT COUNT(*) FROM vendors"),
  'vendors_approved'  => qCount($pdo, "SELECT COUNT(*) FROM vendors WHERE approval_status='approved' AND is_active=1"),
  'vendors_pending'   => qCount($pdo, "SELECT COUNT(*) FROM vendors WHERE approval_status='pending'"),
  'leads_total'       => qCount($pdo, "SELECT COUNT(*) FROM leads"),
  'leads_available'   => qCount($pdo, "SELECT COUNT(*) FROM leads WHERE is_available=1"),
  'leads_converted'   => qCount($pdo, "SELECT COUNT(*) FROM leads WHERE is_available=0"),
  'jobs_total'        => qCount($pdo, "SELECT COUNT(*) FROM jobs"),
  'jobs_completed'    => qCount($pdo, "SELECT COUNT(*) FROM jobs WHERE status='completed'"),
  'jobs_ongoing'      => qCount($pdo, "SELECT COUNT(*) FROM jobs WHERE status IN ('accepted','in_progress')"),
  'tx_total'          => qCount($pdo, "SELECT COUNT(*) FROM wallet_transactions"),
  'categories_count'  => qCount($pdo, "SELECT COUNT(*) FROM categories WHERE is_active=1"),
  'services_count'    => qCount($pdo, "SELECT COUNT(*) FROM services WHERE is_active=1"),
  'users_total'       => $userTablesExist ? qCount($pdo, "SELECT COUNT(*) FROM users") : 0,

  // User Orders = Leads
  'user_orders_total'     => 0,
  'user_orders_converted' => 0,
  'user_orders_revenue'   => qSum($pdo, "SELECT COALESCE(SUM(lead_price),0) FROM leads WHERE is_available=0"),
  'unique_customers'      => qCount($pdo, "SELECT COUNT(DISTINCT customer_phone) FROM leads WHERE customer_phone IS NOT NULL AND customer_phone != ''"),
];

$stats['user_orders_total'] = $stats['leads_total'];
$stats['user_orders_converted'] = $stats['leads_converted'];

// Revenue metrics
$totalRevenue = qSum($pdo, "SELECT COALESCE(SUM(total_earnings),0) FROM vendors");
$todayRevenue = qSum($pdo, "SELECT COALESCE(SUM(j.earning_amount),0) FROM jobs j WHERE j.status='completed' AND DATE(j.completed_at)=CURDATE()");
$weekRevenue  = qSum($pdo, "SELECT COALESCE(SUM(j.earning_amount),0) FROM jobs j WHERE j.status='completed' AND j.completed_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)");
$monthRevenue = qSum($pdo, "SELECT COALESCE(SUM(j.earning_amount),0) FROM jobs j WHERE j.status='completed' AND j.completed_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)");

// Pending withdrawals
$pendingWithdrawals = qSum($pdo, "SELECT COALESCE(SUM(amount),0) FROM withdrawal_requests WHERE status='pending'");
$withdrawalCount    = qCount($pdo, "SELECT COUNT(*) FROM withdrawal_requests WHERE status='pending'");

// Chart data - Revenue last 14 days
$revenueChart = $pdo->query("
  SELECT DATE(j.completed_at) d, COALESCE(SUM(j.earning_amount),0) amt, COUNT(*) cnt
  FROM jobs j
  WHERE j.status='completed' AND j.completed_at >= DATE_SUB(CURDATE(), INTERVAL 14 DAY)
  GROUP BY DATE(j.completed_at)
  ORDER BY d
")->fetchAll(PDO::FETCH_ASSOC);

// Chart data - Leads by category
$leadsByCat = $pdo->query("
  SELECT c.name, COUNT(l.id) cnt
  FROM leads l
  LEFT JOIN categories c ON c.id = l.category_id
  GROUP BY l.category_id, c.name
  ORDER BY cnt DESC
  LIMIT 8
")->fetchAll(PDO::FETCH_ASSOC);

// Chart data - Jobs by status
$jobsByStatus = $pdo->query("SELECT status, COUNT(*) cnt FROM jobs GROUP BY status")->fetchAll(PDO::FETCH_ASSOC);

// Recent activity
$recentJobs = $pdo->query("
  SELECT j.id, j.status, j.earning_amount, j.completed_at, j.created_at,
         v.vendor_code, v.name vendor_name, l.title lead_title
  FROM jobs j
  JOIN vendors v ON v.id=j.vendor_id
  JOIN leads l ON l.id=j.lead_id
  ORDER BY j.created_at DESC
  LIMIT 8
")->fetchAll(PDO::FETCH_ASSOC);

$recentUserOrders = $pdo->query("
  SELECT l.id, l.title, l.lead_price, l.is_available, l.created_at,
         l.customer_name, l.customer_phone, c.name category_name
  FROM leads l
  LEFT JOIN categories c ON c.id=l.category_id
  ORDER BY l.created_at DESC
  LIMIT 8
")->fetchAll(PDO::FETCH_ASSOC);
?>

<style>
/* ---- Premium Dashboard Styling ---- */
.page-wrap { padding: 6px 0 18px; }
.dash-header {
  display:flex; justify-content:space-between; align-items:flex-end; gap:16px;
  margin: 4px 0 18px;
}
.dash-title h1 { margin:0; font-size: 28px; letter-spacing: -0.4px; }
.dash-title .meta { color:#6b7280; font-size: 13px; margin-top: 4px; display:flex; gap:10px; flex-wrap:wrap; }
.badge-soft { background:#f3f4f6; color:#111827; border:1px solid #e5e7eb; padding:6px 10px; border-radius: 999px; font-size:12px; }
.btn-pill { border-radius: 999px; padding: 10px 14px; }

.kpi-grid { display:grid; grid-template-columns: repeat(12,1fr); gap:14px; margin-bottom: 14px; }
.kpi {
  grid-column: span 3;
  background: #fff;
  border: 1px solid #e5e7eb;
  border-radius: 16px;
  padding: 16px;
  box-shadow: 0 10px 24px rgba(0,0,0,0.04);
  position: relative;
  overflow: hidden;
}
.kpi:before{
  content:""; position:absolute; inset:-40px -60px auto auto; width:160px; height:160px;
  background: radial-gradient(circle at 30% 30%, rgba(0,0,0,0.06), transparent 60%);
  transform: rotate(12deg);
}
.kpi .label { color:#6b7280; font-size: 12px; letter-spacing: .3px; text-transform: uppercase; }
.kpi .value { font-size: 28px; font-weight: 800; margin-top: 6px; }
.kpi .sub { margin-top: 6px; font-size: 13px; color:#6b7280; display:flex; justify-content:space-between; gap:10px; flex-wrap:wrap; }
.kpi .icon {
  width:44px; height:44px; border-radius: 12px;
  display:grid; place-items:center; position:absolute; right:14px; top:14px;
  background:#111827; color:#fff; font-size: 18px;
}

.kpi.accent .icon{ background: linear-gradient(135deg, #ff6b35, #f97316); }
.kpi.success .icon{ background: linear-gradient(135deg, #10b981, #059669); }
.kpi.info .icon{ background: linear-gradient(135deg, #3b82f6, #2563eb); }
.kpi.warning .icon{ background: linear-gradient(135deg, #f59e0b, #d97706); }

.stat-grid { display:grid; grid-template-columns: repeat(12,1fr); gap:12px; margin: 12px 0 18px; }
.stat {
  grid-column: span 2;
  background:#fff;
  border:1px solid #e5e7eb;
  border-radius: 14px;
  padding: 14px;
}
.stat .t { color:#6b7280; font-size: 12px; }
.stat .v { font-size: 20px; font-weight: 800; margin-top: 4px; }
.stat .hint { font-size: 12px; margin-top: 6px; color:#6b7280; }

.panel {
  background:#fff; border:1px solid #e5e7eb; border-radius: 16px;
  box-shadow: 0 10px 24px rgba(0,0,0,0.04);
}
.panel .ph {
  padding: 14px 16px;
  border-bottom: 1px solid #e5e7eb;
  display:flex; align-items:center; justify-content:space-between; gap:10px;
  font-weight: 700;
}
.panel .pb { padding: 14px 16px; }
.chart-box { height: 320px; }
.chart-box.small { height: 320px; }

.list { max-height: 340px; overflow:auto; }
.list-item {
  display:flex; gap:12px; padding: 12px 16px;
  border-bottom: 1px solid #f3f4f6;
}
.list-item:last-child{ border-bottom:0; }
.avatar {
  width:38px; height:38px; border-radius: 12px;
  background:#f3f4f6; border:1px solid #e5e7eb;
  display:grid; place-items:center; font-weight: 800; color:#111827;
  flex:0 0 auto;
}
.li-main { flex:1; min-width: 0; }
.li-top { display:flex; justify-content:space-between; gap:10px; }
.li-title { font-weight: 700; white-space:nowrap; overflow:hidden; text-overflow:ellipsis; }
.li-meta { font-size: 12px; color:#6b7280; margin-top: 4px; display:flex; gap:10px; flex-wrap:wrap; }

.empty {
  padding: 22px; text-align:center; color:#6b7280;
}
.quick-actions { display:flex; flex-wrap:wrap; gap:10px; }

@media (max-width: 1200px){
  .kpi{ grid-column: span 6; }
  .stat{ grid-column: span 3; }
}
@media (max-width: 768px){
  .kpi{ grid-column: span 12; }
  .stat{ grid-column: span 6; }
}
</style>

<div class="page-wrap">

  <div class="dash-header">
    <div class="dash-title">
      <h1>Overview Dashboard</h1>
      <div class="meta">
        <span class="badge-soft">Real-time KPIs • Sales • Ops • Withdrawals</span>
        <span class="badge-soft">Last updated: <?php echo date('M d, Y H:i'); ?></span>
      </div>
    </div>
    <div class="d-flex gap-2">
      <a class="btn btn-dark btn-pill" href="reports/business_overview.php">Business Eagle View</a>
      <a class="btn btn-outline-dark btn-pill" href="reports/management_report.php">Management Report</a>
    </div>
  </div>

  <!-- KPI GRID -->
  <div class="kpi-grid">
    <div class="kpi accent">
      <div class="icon">₹</div>
      <div class="label">Total Revenue</div>
      <div class="value">₹<?php echo number_format($totalRevenue); ?></div>
      <div class="sub">
        <span>Today: <strong>₹<?php echo number_format($todayRevenue); ?></strong></span>
        <span>30D: <strong>₹<?php echo number_format($monthRevenue); ?></strong></span>
      </div>
    </div>

    <div class="kpi success">
      <div class="icon">👷</div>
      <div class="label">Active Vendors</div>
      <div class="value"><?php echo number_format($stats['vendors_approved']); ?></div>
      <div class="sub">
        <span>Pending: <strong><?php echo number_format($stats['vendors_pending']); ?></strong></span>
        <span>Total: <strong><?php echo number_format($stats['vendors_total']); ?></strong></span>
      </div>
    </div>

    <div class="kpi info">
      <div class="icon">✅</div>
      <div class="label">Completed Jobs</div>
      <div class="value"><?php echo number_format($stats['jobs_completed']); ?></div>
      <div class="sub">
        <span>Ongoing: <strong><?php echo number_format($stats['jobs_ongoing']); ?></strong></span>
        <span>Total: <strong><?php echo number_format($stats['jobs_total']); ?></strong></span>
      </div>
    </div>

    <div class="kpi warning">
      <div class="icon">📌</div>
      <div class="label">Available Leads</div>
      <div class="value"><?php echo number_format($stats['leads_available']); ?></div>
      <div class="sub">
        <span>Converted: <strong><?php echo number_format($stats['leads_converted']); ?></strong></span>
        <span>Total: <strong><?php echo number_format($stats['leads_total']); ?></strong></span>
      </div>
    </div>
  </div>

  <!-- SECONDARY STATS -->
  <div class="stat-grid">
    <div class="stat">
      <div class="t">Categories</div>
      <div class="v"><?php echo number_format($stats['categories_count']); ?></div>
      <div class="hint">Active categories</div>
    </div>
    <div class="stat">
      <div class="t">Services</div>
      <div class="v"><?php echo number_format($stats['services_count']); ?></div>
      <div class="hint">Active services</div>
    </div>
    <div class="stat">
      <div class="t">Wallet Transactions</div>
      <div class="v"><?php echo number_format($stats['tx_total']); ?></div>
      <div class="hint">All-time</div>
    </div>
    <div class="stat">
      <div class="t">Unique Customers</div>
      <div class="v"><?php echo number_format($stats['unique_customers']); ?></div>
      <div class="hint">Distinct phones</div>
    </div>
    <div class="stat">
      <div class="t">User Orders (Leads)</div>
      <div class="v"><?php echo number_format($stats['user_orders_total']); ?></div>
      <div class="hint"><?php echo number_format($stats['user_orders_converted']); ?> converted</div>
    </div>
    <div class="stat">
      <div class="t">Leads Revenue</div>
      <div class="v">₹<?php echo number_format($stats['user_orders_revenue']); ?></div>
      <div class="hint">Converted lead value</div>
    </div>
    <div class="stat">
      <div class="t">Pending Withdrawals</div>
      <div class="v">₹<?php echo number_format($pendingWithdrawals); ?></div>
      <div class="hint"><?php echo number_format($withdrawalCount); ?> requests</div>
    </div>
    <?php if ($userTablesExist): ?>
    <div class="stat">
      <div class="t">App Users</div>
      <div class="v"><?php echo number_format($stats['users_total']); ?></div>
      <div class="hint">Registered users</div>
    </div>
    <?php endif; ?>
    <div class="stat">
      <div class="t">Revenue (7 Days)</div>
      <div class="v">₹<?php echo number_format($weekRevenue); ?></div>
      <div class="hint">Completed jobs</div>
    </div>
  </div>

  <!-- CHARTS -->
  <div class="row g-3 mb-3">
    <div class="col-lg-8">
      <div class="panel">
        <div class="ph">
          <span>Revenue Trend (Last 14 Days)</span>
          <span class="badge-soft">bar chart</span>
        </div>
        <div class="pb">
          <div class="chart-box">
            <canvas id="revenueChart"></canvas>
          </div>
        </div>
      </div>
    </div>
    <div class="col-lg-4">
      <div class="panel">
        <div class="ph">
          <span>Jobs by Status</span>
          <span class="badge-soft">doughnut</span>
        </div>
        <div class="pb">
          <div class="chart-box small">
            <canvas id="jobsStatusChart"></canvas>
          </div>
        </div>
      </div>
    </div>
  </div>

  <div class="row g-3">
    <div class="col-lg-4">
      <div class="panel">
        <div class="ph">
          <span>Leads by Category</span>
          <a class="btn btn-sm btn-outline-dark" href="leads.php">View Leads</a>
        </div>
        <div class="pb">
          <div class="chart-box small">
            <canvas id="leadsCategoryChart"></canvas>
          </div>
        </div>
      </div>
    </div>

    <div class="col-lg-4">
      <div class="panel">
        <div class="ph">
          <span>Recent User Orders (Leads)</span>
          <a class="btn btn-sm btn-outline-dark" href="leads.php">View All</a>
        </div>
        <div class="list">
          <?php if (empty($recentUserOrders)): ?>
            <div class="empty">No recent user orders.</div>
          <?php else: ?>
            <?php foreach ($recentUserOrders as $o): ?>
              <?php
                $name = trim($o['customer_name'] ?? '');
                $initials = $name ? strtoupper(mb_substr($name, 0, 1)) : 'C';
                $badgeClass = $o['is_available'] ? 'warning' : 'success';
                $badgeText = $o['is_available'] ? 'Available' : 'Converted';
              ?>
              <div class="list-item">
                <div class="avatar"><?php echo htmlspecialchars($initials); ?></div>
                <div class="li-main">
                  <div class="li-top">
                    <div class="li-title">
                      <?php echo htmlspecialchars($name ?: 'Customer'); ?>
                      <span class="text-muted fw-normal">— <?php echo htmlspecialchars($o['title']); ?></span>
                    </div>
                    <div class="text-muted small"><?php echo safeDate($o['created_at']); ?></div>
                  </div>
                  <div class="li-meta">
                    <span class="badge bg-<?php echo $badgeClass; ?>"><?php echo $badgeText; ?></span>
                    <span><strong>₹<?php echo number_format((int)$o['lead_price']); ?></strong></span>
                    <?php if (!empty($o['category_name'])): ?>
                      <span class="badge-soft"><?php echo htmlspecialchars($o['category_name']); ?></span>
                    <?php endif; ?>
                    <?php if (!empty($o['customer_phone'])): ?>
                      <span class="text-muted"><?php echo htmlspecialchars($o['customer_phone']); ?></span>
                    <?php endif; ?>
                  </div>
                </div>
              </div>
            <?php endforeach; ?>
          <?php endif; ?>
        </div>
      </div>
    </div>

    <div class="col-lg-4">
      <div class="panel">
        <div class="ph">
          <span>Recent Activity (Jobs)</span>
          <a class="btn btn-sm btn-outline-dark" href="jobs.php">View All</a>
        </div>
        <div class="list">
          <?php if (empty($recentJobs)): ?>
            <div class="empty">No recent job activity.</div>
          <?php else: ?>
            <?php foreach ($recentJobs as $j): ?>
              <?php
                $status = $j['status'] ?? '—';
                $badge = 'secondary';
                if ($status === 'completed') $badge = 'success';
                else if ($status === 'in_progress') $badge = 'primary';
                else if ($status === 'accepted') $badge = 'warning';
              ?>
              <div class="list-item">
                <div class="avatar"><?php echo htmlspecialchars(strtoupper(mb_substr($j['vendor_code'] ?? 'V', 0, 1))); ?></div>
                <div class="li-main">
                  <div class="li-top">
                    <div class="li-title">
                      <strong><?php echo htmlspecialchars($j['vendor_code'] ?? 'VENDOR'); ?></strong>
                      <span class="text-muted fw-normal">— <?php echo htmlspecialchars($j['lead_title'] ?? 'Lead'); ?></span>
                    </div>
                    <div class="text-muted small"><?php echo safeDate($j['completed_at'] ?? $j['created_at']); ?></div>
                  </div>
                  <div class="li-meta">
                    <span class="badge bg-<?php echo $badge; ?>"><?php echo htmlspecialchars($status); ?></span>
                    <?php if (!empty($j['earning_amount'])): ?>
                      <span><strong>₹<?php echo number_format((int)$j['earning_amount']); ?></strong></span>
                    <?php endif; ?>
                    <?php if (!empty($j['vendor_name'])): ?>
                      <span class="text-muted"><?php echo htmlspecialchars($j['vendor_name']); ?></span>
                    <?php endif; ?>
                  </div>
                </div>
              </div>
            <?php endforeach; ?>
          <?php endif; ?>
        </div>
      </div>
    </div>
  </div>

  <!-- QUICK ACTIONS -->
  <div class="panel mt-3">
    <div class="ph">
      <span>Quick Actions</span>
      <span class="badge-soft">Admin shortcuts</span>
    </div>
    <div class="pb">
      <div class="quick-actions">
        <a class="btn btn-dark btn-pill" href="vendors.php">Manage Vendors</a>
        <a class="btn btn-outline-dark btn-pill" href="leads.php">User Orders (Leads)</a>
        <?php if ($userTablesExist): ?>
          <a class="btn btn-outline-dark btn-pill" href="users.php">Manage Users</a>
          <a class="btn btn-outline-dark btn-pill" href="user_orders.php">App Orders</a>
        <?php endif; ?>
        <a class="btn btn-outline-dark btn-pill" href="jobs.php">View Jobs</a>
        <a class="btn btn-outline-dark btn-pill" href="transactions.php">Transactions</a>
        <a class="btn btn-outline-dark btn-pill" href="withdrawals.php">Withdrawals</a>
        <a class="btn btn-outline-dark btn-pill" href="reports/payment_report.php">Payment & Commission</a>
        <a class="btn btn-outline-dark btn-pill" href="reports/transaction_report.php">Transaction Report</a>
        <a class="btn btn-outline-dark btn-pill" href="reports/user_orders_report.php">User Orders Report</a>
      </div>
    </div>
  </div>

</div>

<script>
document.addEventListener('DOMContentLoaded', function() {
  if (typeof Chart === 'undefined') return;

  const revenueData = <?php echo json_encode($revenueChart); ?>;
  const jobsStatusData = <?php echo json_encode($jobsByStatus); ?>;
  const leadsCatData = <?php echo json_encode($leadsByCat); ?>;

  // Global defaults (clean admin vibe)
  Chart.defaults.font.family = "system-ui, -apple-system, Segoe UI, Roboto, Arial";
  Chart.defaults.color = "#374151";

  const revEl = document.getElementById('revenueChart');
  if (revEl) new Chart(revEl, {
    type: 'bar',
    data: {
      labels: revenueData.map(r => r.d),
      datasets: [{
        label: 'Revenue (₹)',
        data: revenueData.map(r => Number(r.amt || 0)),
        backgroundColor: 'rgba(255,107,53,0.85)',
        borderColor: 'rgba(255,107,53,1)',
        borderWidth: 1,
        borderRadius: 10,
        borderSkipped: false
      }]
    },
    options: {
      responsive: true,
      maintainAspectRatio: false,
      plugins: { legend: { display: false }, tooltip: { intersect: false } },
      scales: {
        y: {
          beginAtZero: true,
          grid: { color: 'rgba(0,0,0,0.06)' },
          ticks: { callback: (v) => '₹' + v }
        },
        x: { grid: { display: false }, ticks: { maxRotation: 45 } }
      }
    }
  });

  const jobsEl = document.getElementById('jobsStatusChart');
  if (jobsEl) new Chart(jobsEl, {
    type: 'doughnut',
    data: {
      labels: jobsStatusData.map(r => r.status),
      datasets: [{
        data: jobsStatusData.map(r => Number(r.cnt || 0)),
        backgroundColor: ['#10B981','#3B82F6','#F59E0B','#94a3b8','#8B5CF6'],
        borderWidth: 2,
        borderColor: '#fff',
        hoverOffset: 10
      }]
    },
    options: {
      responsive: true,
      maintainAspectRatio: false,
      plugins: {
        legend: { position: 'bottom', labels: { padding: 16 } }
      },
      cutout: '62%'
    }
  });

  const leadsEl = document.getElementById('leadsCategoryChart');
  if (leadsEl) new Chart(leadsEl, {
    type: 'bar',
    data: {
      labels: leadsCatData.map(r => r.name || 'Uncategorized'),
      datasets: [{
        label: 'Leads',
        data: leadsCatData.map(r => Number(r.cnt || 0)),
        backgroundColor: 'rgba(59,130,246,0.85)',
        borderColor: 'rgba(59,130,246,1)',
        borderWidth: 1,
        borderRadius: 8,
        borderSkipped: false
      }]
    },
    options: {
      indexAxis: 'y',
      responsive: true,
      maintainAspectRatio: false,
      plugins: { legend: { display: false } },
      scales: {
        x: { beginAtZero: true, grid: { color: 'rgba(0,0,0,0.06)' } },
        y: { grid: { display: false } }
      }
    }
  });

});
</script>

<?php require_once __DIR__ . '/partials/bottom.php'; ?>
