<?php
// =============================================
// SHARK CRM - Dashboard
// =============================================

// Stats
$stageResultExpr = columnExists($conn, 'stage', 'result_type') ? "COALESCE(s.result_type,'normal')" : "CASE WHEN LOWER(s.name)='won' THEN 'won' WHEN LOWER(s.name)='lost' THEN 'lost' ELSE 'normal' END";
$stats_q = $conn->query("
  SELECT
    COUNT(l.id) as total,
    SUM(CASE WHEN $stageResultExpr='won' OR l.status='won' THEN 1 ELSE 0 END) as won,
    SUM(CASE WHEN $stageResultExpr='lost' OR l.status='lost' THEN 1 ELSE 0 END) as lost,
    SUM(CASE WHEN $stageResultExpr NOT IN ('won','lost') AND l.status NOT IN ('won','lost') THEN 1 ELSE 0 END) as open_leads,
    SUM(l.estimated_price) as total_value,
    SUM(CASE WHEN $stageResultExpr='won' OR l.status='won' THEN l.estimated_price ELSE 0 END) as won_value
  FROM leads l LEFT JOIN stage s ON s.id=l.stage_id
  WHERE l.account_id = $account_id
");
$stats = $stats_q->fetch_assoc();

$cust_q  = $conn->query("SELECT COUNT(*) as cnt FROM customers WHERE account_id = $account_id");
$cust_cnt = $cust_q->fetch_assoc()['cnt'];

$items_q  = $conn->query("SELECT COUNT(*) as cnt FROM items WHERE account_id = $account_id");
$items_cnt = $items_q->fetch_assoc()['cnt'];

$rem_q  = $conn->query("SELECT COUNT(*) as cnt FROM reminder WHERE account_id = $account_id AND status='pending' AND date >= CURDATE()");
$rem_cnt = $rem_q->fetch_assoc()['cnt'];

// Reminder counters for homepage alerts
$today_rem_q = $conn->query("SELECT COUNT(*) cnt FROM reminder WHERE account_id=$account_id AND status='pending' AND date=CURDATE()");
$today_rem_cnt = $today_rem_q ? (int)$today_rem_q->fetch_assoc()['cnt'] : 0;
$overdue_rem_q = $conn->query("SELECT COUNT(*) cnt FROM reminder WHERE account_id=$account_id AND status='pending' AND date<CURDATE()");
$overdue_rem_cnt = $overdue_rem_q ? (int)$overdue_rem_q->fetch_assoc()['cnt'] : 0;

// Connection health cards
$email_ok = tableExists($conn,'accounts_email') && ($conn->query("SELECT id FROM accounts_email WHERE account_id=$account_id LIMIT 1")->num_rows > 0);
$wa_ok = tableExists($conn,'contact_phone') && ($conn->query("SELECT id FROM contact_phone WHERE account_id=$account_id LIMIT 1")->num_rows > 0);
$sms_ok = tableExists($conn,'communications');


// Recent leads
$recent_leads = $conn->query("
  SELECT l.*, c.customer_name, c.business_name, s.name as stage_name, s.color as stage_color
  FROM leads l
  LEFT JOIN customers c ON l.customer_id = c.id
  LEFT JOIN stage s ON l.stage_id = s.id
  WHERE l.account_id = $account_id
  ORDER BY l.datetime_created DESC LIMIT 8
");

// Upcoming reminders
$upcoming_rems = $conn->query("
  SELECT r.*, c.customer_name, at.name as activity_name
  FROM reminder r
  LEFT JOIN customers c ON r.customer_id = c.id
  LEFT JOIN activity_type at ON r.activity_type_id = at.id
  WHERE r.account_id = $account_id AND r.status = 'pending'
  ORDER BY r.date ASC, r.time ASC LIMIT 6
");

// Stage stats
$stage_stats = $conn->query("
  SELECT s.name, s.color, COUNT(l.id) as cnt, SUM(l.estimated_price) as val
  FROM stage s
  LEFT JOIN leads l ON l.stage_id = s.id AND l.account_id = $account_id
  WHERE s.account_id = $account_id
  GROUP BY s.id ORDER BY s.position
");
?>

<div class="fade-in">
<!-- ===================== STAT CARDS ===================== -->
<div class="stats-grid">
  <div class="stat-card teal">
    <div class="stat-icon teal">📊</div>
    <div class="stat-value"><?= $stats['total'] ?></div>
    <div class="stat-label">Total Leads</div>
  </div>
  <div class="stat-card green">
    <div class="stat-icon green">✅</div>
    <div class="stat-value"><?= $stats['won'] ?></div>
    <div class="stat-label">Won Deals</div>
  </div>
  <div class="stat-card red">
    <div class="stat-icon red">❌</div>
    <div class="stat-value"><?= $stats['lost'] ?></div>
    <div class="stat-label">Lost Deals</div>
  </div>
  <div class="stat-card blue">
    <div class="stat-icon blue">👥</div>
    <div class="stat-value"><?= $cust_cnt ?></div>
    <div class="stat-label">Customers</div>
  </div>
  <div class="stat-card orange">
    <div class="stat-icon orange">💰</div>
    <div class="stat-value" style="font-size:22px"><?= crmMoney($stats['won_value'] ?? 0, 'USD') ?></div>
    <div class="stat-label">Revenue Won</div>
  </div>
  <div class="stat-card purple">
    <div class="stat-icon purple">🔔</div>
    <div class="stat-value"><?= $rem_cnt ?></div>
    <div class="stat-label">Pending Reminders</div>
  </div>
  <div class="stat-card orange">
    <div class="stat-icon orange">⏰</div>
    <div class="stat-value"><?= $today_rem_cnt ?></div>
    <div class="stat-label">Today's Reminders</div>
  </div>
  <div class="stat-card red">
    <div class="stat-icon red">⚠️</div>
    <div class="stat-value"><?= $overdue_rem_cnt ?></div>
    <div class="stat-label">Overdue Reminders</div>
  </div>
</div>

<div class="card" style="margin-bottom:20px">
  <div class="card-header"><span class="card-title">Connection Health</span></div>
  <div class="card-body" style="display:grid;grid-template-columns:repeat(auto-fit,minmax(180px,1fr));gap:12px">
    <div class="reminder-card <?= $wa_ok ? 'upcoming' : 'overdue' ?>"><div class="reminder-icon">💬</div><div class="reminder-body"><div class="reminder-title">WhatsApp</div><div class="reminder-meta"><?= $wa_ok ? 'Connected' : 'Not connected / check Settings' ?></div></div><span class="badge badge-<?= $wa_ok ? 'green':'red' ?>"><?= $wa_ok ? 'OK':'Issue' ?></span></div>
    <div class="reminder-card <?= $sms_ok ? 'upcoming' : 'overdue' ?>"><div class="reminder-icon">📱</div><div class="reminder-body"><div class="reminder-title">SMS</div><div class="reminder-meta"><?= $sms_ok ? 'Log table ready' : 'Missing communications table' ?></div></div><span class="badge badge-<?= $sms_ok ? 'green':'red' ?>"><?= $sms_ok ? 'OK':'Issue' ?></span></div>
    <div class="reminder-card <?= $email_ok ? 'upcoming' : 'overdue' ?>"><div class="reminder-icon">📧</div><div class="reminder-body"><div class="reminder-title">Email</div><div class="reminder-meta"><?= $email_ok ? 'Sender email configured' : 'No sender email in Settings' ?></div></div><span class="badge badge-<?= $email_ok ? 'green':'red' ?>"><?= $email_ok ? 'OK':'Issue' ?></span></div>
  </div>
</div>

<!-- ===================== MAIN GRID ===================== -->
<div class="dashboard-grid">
  <!-- Recent Leads -->
  <div class="card">
    <div class="card-header">
      <span class="card-title">Recent Leads</span>
      <a href="index.php?page=leads" class="btn btn-secondary btn-sm">View All</a>
    </div>
    <div class="table-wrapper">
      <table class="crm-table">
        <thead>
          <tr>
            <th>Lead</th>
            <th>Stage</th>
            <th>Value</th>
            <th>%</th>
          </tr>
        </thead>
        <tbody>
          <?php if ($recent_leads->num_rows === 0): ?>
          <tr><td colspan="4" class="text-center text-muted" style="padding:30px">No leads yet</td></tr>
          <?php else: ?>
          <?php while ($lead = $recent_leads->fetch_assoc()): ?>
          <tr>
            <td>
              <div class="font-bold"><?= htmlspecialchars($lead['title'] ?: 'Lead') ?></div>
              <div class="text-xs text-muted"><?= htmlspecialchars($lead['customer_name']) ?></div>
            </td>
            <td>
              <span class="stage-pill" style="color:<?= $lead['stage_color'] ?>;background:<?= $lead['stage_color'] ?>22;border-color:<?= $lead['stage_color'] ?>44">
                <?= htmlspecialchars($lead['stage_name'] ?? 'N/A') ?>
              </span>
            </td>
            <td class="text-accent"><?= crmMoney($lead['estimated_price'], $lead['item_currency'] ?? 'USD') ?></td>
            <td>
              <div class="progress-wrap" style="min-width:60px">
                <div class="progress-bar" style="width:<?= $lead['opportunity'] ?>%"></div>
              </div>
              <div class="text-xs text-muted"><?= $lead['opportunity'] ?>%</div>
            </td>
          </tr>
          <?php endwhile; ?>
          <?php endif; ?>
        </tbody>
      </table>
    </div>
  </div>

  <!-- Upcoming Reminders -->
  <div class="card">
    <div class="card-header">
      <span class="card-title">Upcoming Reminders</span>
      <a href="index.php?page=reminders" class="btn btn-secondary btn-sm">View All</a>
    </div>
    <div class="card-body" style="padding:14px">
      <?php if ($upcoming_rems->num_rows === 0): ?>
        <div class="empty-state"><div class="empty-state-icon">🔔</div><p>No upcoming reminders</p></div>
      <?php else: ?>
      <?php while ($rem = $upcoming_rems->fetch_assoc()):
        $today = date('Y-m-d');
        $cls = $rem['date'] < $today ? 'overdue' : ($rem['date'] == $today ? 'today' : 'upcoming');
      ?>
      <div class="reminder-card <?= $cls ?>">
        <div class="reminder-icon" style="background:var(--accent-dim);color:var(--accent)">📌</div>
        <div class="reminder-body">
          <div class="reminder-title"><?= htmlspecialchars($rem['comment'] ?: 'Reminder') ?></div>
          <div class="reminder-meta">
            <?= htmlspecialchars($rem['customer_name'] ?? '') ?>
            · <?= date('M d', strtotime($rem['date'])) ?>
            <?= $rem['time'] ? date('H:i', strtotime($rem['time'])) : '' ?>
          </div>
        </div>
        <span class="badge badge-<?= $cls === 'overdue' ? 'red' : ($cls === 'today' ? 'orange' : 'teal') ?>"><?= ucfirst($cls) ?></span>
      </div>
      <?php endwhile; ?>
      <?php endif; ?>
    </div>
  </div>

  <!-- Pipeline Overview -->
  <div class="card">
    <div class="card-header">
      <span class="card-title">Pipeline Overview</span>
      <a href="index.php?page=pipeline" class="btn btn-secondary btn-sm">Kanban View</a>
    </div>
    <div class="card-body">
      <?php while ($s = $stage_stats->fetch_assoc()): ?>
      <div style="margin-bottom:14px">
        <div class="d-flex justify-between align-center mb-0" style="margin-bottom:5px">
          <span style="font-size:13px;font-weight:600"><?= htmlspecialchars($s['name']) ?></span>
          <span style="font-size:12px;color:var(--text-muted)"><?= $s['cnt'] ?> leads · $<?= number_format($s['val'] ?? 0, 0) ?></span>
        </div>
        <?php
          $total_leads = max($stats['total'], 1);
          $pct = round(($s['cnt'] / $total_leads) * 100);
        ?>
        <div class="progress-wrap">
          <div class="progress-bar" style="width:<?= $pct ?>%;background:<?= $s['color'] ?>"></div>
        </div>
      </div>
      <?php endwhile; ?>
    </div>
  </div>

  <!-- Quick Stats -->
  <div class="card">
    <div class="card-header"><span class="card-title">Quick Stats</span></div>
    <div class="card-body">
      <?php
        $win_rate = $stats['total'] > 0 ? round(($stats['won'] / $stats['total']) * 100) : 0;
        $pipeline_val = $stats['total_value'] ?? 0;
      ?>
      <div style="display:grid;grid-template-columns:1fr 1fr;gap:16px">
        <div style="text-align:center;padding:16px;background:var(--bg-base);border-radius:10px;border:1px solid var(--border)">
          <div style="font-family:var(--font-head);font-size:36px;color:var(--green)"><?= $win_rate ?>%</div>
          <div style="font-size:12px;color:var(--text-muted);margin-top:4px">Win Rate</div>
        </div>
        <div style="text-align:center;padding:16px;background:var(--bg-base);border-radius:10px;border:1px solid var(--border)">
          <div style="font-family:var(--font-head);font-size:28px;color:var(--accent)">$<?= number_format($pipeline_val, 0) ?></div>
          <div style="font-size:12px;color:var(--text-muted);margin-top:4px">Pipeline Value</div>
        </div>
        <div style="text-align:center;padding:16px;background:var(--bg-base);border-radius:10px;border:1px solid var(--border)">
          <div style="font-family:var(--font-head);font-size:36px;color:var(--blue)"><?= $stats['open_leads'] ?></div>
          <div style="font-size:12px;color:var(--text-muted);margin-top:4px">Active Leads</div>
        </div>
        <div style="text-align:center;padding:16px;background:var(--bg-base);border-radius:10px;border:1px solid var(--border)">
          <div style="font-family:var(--font-head);font-size:36px;color:var(--purple)"><?= $items_cnt ?></div>
          <div style="font-size:12px;color:var(--text-muted);margin-top:4px">Items Listed</div>
        </div>
      </div>
    </div>
  </div>
</div>
</div>
