22

✏️ Editing: DashboardController.php

<?php
declare(strict_types=1);
require __DIR__ . '/BaseController.php';

class DashboardController extends BaseController
{
  public function index(): void
  {
    $u = require_login($this->pdo, $this->config);

$range = $_GET['range'] ?? ($_SESSION['dash_range'] ?? 'this_month');
    $_SESSION['dash_range'] = $range;

[$fromSql, $toSql] = $this->dateRangeSql($range);

if (has_role($u, ['MARKETING'])) {
      $data = $this->marketingDashboard($fromSql, $toSql);
      $view = 'dashboard/marketing.php';
    } elseif (has_role($u, ['CC_SUP'])) {
      $data = $this->ccSupervisorDashboard((int)$u['id'], $fromSql, $toSql);
      $view = 'dashboard/cc_sup.php';
    } elseif (has_role($u, ['CC_AGENT'])) {
      $data = $this->ccAgentDashboard((int)$u['id'], $fromSql, $toSql);
      $view = 'dashboard/cc_agent.php';
    } elseif (has_role($u, ['BR_MGR'])) {
      $data = $this->branchManagerDashboard((int)($u['branch_id'] ?? 0), $fromSql, $toSql);
      $view = 'dashboard/br_mgr.php';
    } elseif (has_role($u, ['BR_AGENT'])) {
      $data = $this->branchAgentDashboard((int)$u['id'], $fromSql, $toSql);
      $view = 'dashboard/br_agent.php';
    } elseif (has_role($u, ['SALES_MGR','GM_SALES','ADMIN'])) {
      $data = $this->salesManagerDashboard($fromSql, $toSql);
      $view = 'dashboard/sales_mgr.php';
    } else {
      $data = ['kpis'=>[]];
      $view = 'dashboard/basic.php';
    }

$this->render($view, [
      'u' => $u,
      'range' => $range,
      'data' => $data
    ]);
  }

// ---------------- Date range helper ----------------
  private function dateRangeSql(string $range): array
  {
    return match ($range) {
      'today' => ["CURDATE()", "DATE_ADD(CURDATE(), INTERVAL 1 DAY)"],
      'yesterday' => ["DATE_SUB(CURDATE(), INTERVAL 1 DAY)", "CURDATE()"],
      'this_week' => ["DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY)", "DATE_ADD(DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 7 DAY)"],
      'prev_month' => ["DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),'%Y-%m-01')", "DATE_FORMAT(CURDATE(),'%Y-%m-01')"],
      'this_month' => ["DATE_FORMAT(CURDATE(),'%Y-%m-01')", "DATE_ADD(DATE_FORMAT(CURDATE(),'%Y-%m-01'), INTERVAL 1 MONTH)"],
      default => ["DATE_SUB(NOW(), INTERVAL 30 DAY)", "NOW()"],
    };
  }

// ---------------- Common helpers ----------------
  private function fetchOne(string $sql, array $params = []): array
  {
    $st = $this->pdo->prepare($sql);
    $st->execute($params);
    $row = $st->fetch(PDO::FETCH_ASSOC);
    return $row ?: [];
  }

private function fetchAll(string $sql, array $params = []): array
  {
    $st = $this->pdo->prepare($sql);
    $st->execute($params);
    return $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
  }

private function safeRate(int $num, int $den): float
  {
    if ($den <= 0) return 0.0;
    return round(($num / $den) * 100, 2);
  }

private function trendLeads(string $from, string $to, string $extraWhere = "1=1", array $params = []): array
  {
    return $this->fetchAll("
      SELECT DATE(created_at) d, COUNT(*) v
      FROM leads
      WHERE created_at >= $from AND created_at < $to
        AND $extraWhere
      GROUP BY d
      ORDER BY d ASC
      LIMIT 60
    ", $params);
  }

private function trendOpps(string $from, string $to, string $extraWhere = "1=1", array $params = []): array
  {
    return $this->fetchAll("
      SELECT DATE(created_at) d, COUNT(*) v
      FROM opportunities
      WHERE created_at >= $from AND created_at < $to
        AND $extraWhere
      GROUP BY d
      ORDER BY d ASC
      LIMIT 60
    ", $params);
  }

// ======================= MARKETING =======================
  private function marketingDashboard(string $from, string $to): array
  {
    $k = $this->fetchOne("
      SELECT
        (SELECT COUNT(*) FROM leads l WHERE l.created_at >= $from AND l.created_at < $to) AS leads_total,
        (SELECT COUNT(*) FROM leads l WHERE l.created_at >= $from AND l.created_at < $to AND l.qualification='qualified') AS leads_qualified,
        (SELECT COUNT(*) FROM leads l WHERE l.created_at >= $from AND l.created_at < $to AND l.qualification='disqualified') AS leads_disqualified,
        (SELECT COUNT(*) FROM opportunities o WHERE o.created_at >= $from AND o.created_at < $to) AS opp_total,
        (SELECT COUNT(*) FROM opportunities o WHERE o.created_at >= $from AND o.created_at < $to AND o.status='won') AS opp_won,
        (SELECT COUNT(*) FROM opportunities o WHERE o.created_at >= $from AND o.created_at < $to AND o.status='lost') AS opp_lost
    ");

$k['qualification_rate'] = $this->safeRate((int)$k['leads_qualified'], (int)$k['leads_total']);
    $k['win_rate'] = $this->safeRate((int)$k['opp_won'], max(1, ((int)$k['opp_won'] + (int)$k['opp_lost'])));

$funnel = [
      ['label'=>'Leads', 'val'=>(int)$k['leads_total']],
      ['label'=>'Qualified', 'val'=>(int)$k['leads_qualified']],
      ['label'=>'Opportunities', 'val'=>(int)$k['opp_total']],
      ['label'=>'Won', 'val'=>(int)$k['opp_won']],
      ['label'=>'Lost', 'val'=>(int)$k['opp_lost']],
      ['label'=>'Disqualified', 'val'=>(int)$k['leads_disqualified']],
    ];

return [
  'kpis' => $k,

// Funnel data (used to draw a funnel-like chart)
  'chart_funnel' => [
    ['label'=>'Leads', 'val'=>(int)$k['leads_total']],
    ['label'=>'Qualified', 'val'=>(int)$k['leads_qualified']],
    ['label'=>'Opportunities', 'val'=>(int)$k['opp_total']],
    ['label'=>'Won', 'val'=>(int)$k['opp_won']],
    ['label'=>'Lost', 'val'=>(int)$k['opp_lost']],
    ['label'=>'Disqualified', 'val'=>(int)$k['leads_disqualified']],
  ],

// ✅ New charts requested
  'chart_source' => $this->fetchAll("
    SELECT COALESCE(source,'(blank)') label, COUNT(*) val
    FROM leads
    WHERE created_at >= $from AND created_at < $to
    GROUP BY label ORDER BY val DESC LIMIT 12
  "),
  'chart_salary_range' => $this->fetchAll("
    SELECT COALESCE(salary_range,'(blank)') label, COUNT(*) val
    FROM leads
    WHERE created_at >= $from AND created_at < $to
    GROUP BY label ORDER BY val DESC
  "),
  'chart_pref_time' => $this->fetchAll("
    SELECT COALESCE(pref_time,'(blank)') label, COUNT(*) val
    FROM leads
    WHERE created_at >= $from AND created_at < $to
    GROUP BY label ORDER BY val DESC
  "),
  'chart_purchase_time' => $this->fetchAll("
    SELECT COALESCE(purchase_time,'(blank)') label, COUNT(*) val
    FROM leads
    WHERE created_at >= $from AND created_at < $to
    GROUP BY label ORDER BY val DESC
  "),
  'chart_obligation' => $this->fetchAll("
    SELECT COALESCE(obligation,'unknown') label, COUNT(*) val
    FROM leads
    WHERE created_at >= $from AND created_at < $to
    GROUP BY label ORDER BY val DESC
  "),
  'chart_vehicle' => $this->fetchAll("
    SELECT COALESCE(vehicle_of_interest,'(blank)') label, COUNT(*) val
    FROM leads
    WHERE created_at >= $from AND created_at < $to
    GROUP BY label ORDER BY val DESC
  "),

// ✅ Table 1: Qualification rate by Source + Won leads by source
  // won leads by source = opportunities that became won, grouped by leads.source
  'table_source_perf' => $this->fetchAll("
    SELECT
      COALESCE(l.source,'(blank)') source,
      COUNT(*) leads,
      SUM(CASE WHEN l.qualification='qualified' THEN 1 ELSE 0 END) qualified,
      ROUND((SUM(CASE WHEN l.qualification='qualified' THEN 1 ELSE 0 END) / NULLIF(COUNT(*),0))*100,2) AS q_rate,
      SUM(CASE WHEN o.status='won' THEN 1 ELSE 0 END) won_opps
    FROM leads l
    LEFT JOIN opportunities o ON o.id = l.converted_opportunity_id
    WHERE l.created_at >= $from AND l.created_at < $to
    GROUP BY source
    ORDER BY leads DESC
    LIMIT 15
  "),

// ✅ Table 2: Qualification rate by Vehicle of Interest
  'table_vehicle_perf' => $this->fetchAll("
    SELECT
      COALESCE(vehicle_of_interest,'(blank)') vehicle,
      COUNT(*) leads,
      SUM(CASE WHEN qualification='qualified' THEN 1 ELSE 0 END) qualified,
      ROUND((SUM(CASE WHEN qualification='qualified' THEN 1 ELSE 0 END) / NULLIF(COUNT(*),0))*100,2) AS q_rate
    FROM leads
    WHERE created_at >= $from AND created_at < $to
    GROUP BY vehicle
    ORDER BY leads DESC
    LIMIT 15
  "),
];
  }

// ======================= CC SUPERVISOR =======================
  private function ccSupervisorDashboard(int $supId, string $from, string $to): array
  {
    $agentRows = $this->fetchAll("SELECT id, name FROM users WHERE supervisor_user_id=?", [$supId]);
    $ids = array_map(fn($r)=>(int)$r['id'], $agentRows);
    if (!$ids) $ids = [-1];
    $in = implode(',', array_fill(0, count($ids), '?'));

$k = $this->fetchOne("
      SELECT
        COUNT(*) leads_total,
        SUM(CASE WHEN first_success_contact_at IS NOT NULL THEN 1 ELSE 0 END) contacted_success,
        SUM(CASE WHEN qualification='qualified' THEN 1 ELSE 0 END) qualified,
        SUM(CASE WHEN qualification='disqualified' THEN 1 ELSE 0 END) disqualified
      FROM leads
      WHERE assigned_to_user_id IN ($in)
        AND created_at >= $from AND created_at < $to
    ", $ids);

$k['contact_rate'] = $this->safeRate((int)$k['contacted_success'], (int)$k['leads_total']);
    $k['qualification_rate'] = $this->safeRate((int)$k['qualified'], (int)$k['leads_total']);

$byAgent = $this->fetchAll("
      SELECT u.name label,
             COUNT(l.id) leads,
             SUM(CASE WHEN l.first_success_contact_at IS NOT NULL THEN 1 ELSE 0 END) contacted,
             SUM(CASE WHEN l.qualification='qualified' THEN 1 ELSE 0 END) qualified,
             SUM(CASE WHEN l.qualification='disqualified' THEN 1 ELSE 0 END) disqualified
      FROM users u
      LEFT JOIN leads l ON l.assigned_to_user_id=u.id AND l.created_at >= $from AND l.created_at < $to
      WHERE u.id IN ($in)
      GROUP BY u.id
      ORDER BY leads DESC
    ", $ids);

return [
      'kpis'=>$k,

// 6 charts
      'chart_by_agent' => $byAgent,
      'chart_contact_rate' => array_map(function($r){
        $leads=(int)$r['leads']; $contacted=(int)$r['contacted'];
        return ['label'=>$r['label'], 'val'=> $leads>0 ? round(($contacted/$leads)*100,2) : 0];
      }, $byAgent),
      'chart_q_rate' => array_map(function($r){
        $leads=(int)$r['leads']; $q=(int)$r['qualified'];
        return ['label'=>$r['label'], 'val'=> $leads>0 ? round(($q/$leads)*100,2) : 0];
      }, $byAgent),
      'chart_status' => $this->fetchAll("
        SELECT COALESCE(status,'(blank)') label, COUNT(*) val
        FROM leads
        WHERE assigned_to_user_id IN ($in)
          AND created_at >= $from AND created_at < $to
        GROUP BY label ORDER BY val DESC
      ", $ids),
      'chart_disq' => $this->fetchAll("
        SELECT COALESCE(disqualify_reason,'(blank)') label, COUNT(*) val
        FROM leads
        WHERE assigned_to_user_id IN ($in)
          AND created_at >= $from AND created_at < $to
          AND qualification='disqualified'
        GROUP BY label ORDER BY val DESC LIMIT 12
      ", $ids),
      'chart_vehicle' => $this->fetchAll("
        SELECT COALESCE(vehicle_of_interest,'(blank)') label, COUNT(*) val
        FROM leads
        WHERE assigned_to_user_id IN ($in)
          AND created_at >= $from AND created_at < $to
        GROUP BY label ORDER BY val DESC
      ", $ids),

// 2 tables
      'table_agents' => $byAgent,
      'table_recent' => $this->fetchAll("
        SELECT l.id, l.status, l.vehicle_of_interest, l.lead_city, l.created_at, u.name agent_name
        FROM leads l
        LEFT JOIN users u ON u.id=l.assigned_to_user_id
        WHERE l.assigned_to_user_id IN ($in)
        ORDER BY l.id DESC
        LIMIT 12
      ", $ids),
    ];
  }

// ======================= CC AGENT =======================
  private function ccAgentDashboard(int $uid, string $from, string $to): array
  {
    $k = $this->fetchOne("
      SELECT
        COUNT(*) leads_total,
        SUM(CASE WHEN first_success_contact_at IS NOT NULL THEN 1 ELSE 0 END) contacted_success,
        SUM(CASE WHEN qualification='qualified' THEN 1 ELSE 0 END) qualified,
        SUM(CASE WHEN qualification='disqualified' THEN 1 ELSE 0 END) disqualified
      FROM leads
      WHERE assigned_to_user_id=?
        AND created_at >= $from AND created_at < $to
    ", [$uid]);

$k['contact_rate'] = $this->safeRate((int)$k['contacted_success'], (int)$k['leads_total']);
    $k['qualification_rate'] = $this->safeRate((int)$k['qualified'], (int)$k['leads_total']);

$opp = $this->fetchOne("
      SELECT COUNT(*) opp_total
      FROM opportunities o
      JOIN leads l ON l.id=o.lead_id
      WHERE l.assigned_to_user_id=?
        AND o.created_at >= $from AND o.created_at < $to
    ", [$uid]);

$k = array_merge($k, $opp);

$outcomes = [
      ['label'=>'Qualified', 'val'=>(int)$k['qualified']],
      ['label'=>'Disqualified', 'val'=>(int)$k['disqualified']],
      ['label'=>'Pending', 'val'=> max(0, (int)$k['leads_total'] - ((int)$k['qualified'] + (int)$k['disqualified']))],
    ];

return [
      'kpis'=>$k,

// 6 charts
      'chart_status' => $this->fetchAll("
        SELECT COALESCE(status,'(blank)') label, COUNT(*) val
        FROM leads
        WHERE assigned_to_user_id=?
          AND created_at >= $from AND created_at < $to
        GROUP BY label ORDER BY val DESC
      ", [$uid]),
      'chart_outcomes' => $outcomes,
      'chart_vehicle' => $this->fetchAll("
        SELECT COALESCE(vehicle_of_interest,'(blank)') label, COUNT(*) val
        FROM leads
        WHERE assigned_to_user_id=?
          AND created_at >= $from AND created_at < $to
        GROUP BY label ORDER BY val DESC
      ", [$uid]),
      'chart_pref_time' => $this->fetchAll("
        SELECT COALESCE(pref_time,'(blank)') label, COUNT(*) val
        FROM leads
        WHERE assigned_to_user_id=?
          AND created_at >= $from AND created_at < $to
        GROUP BY label ORDER BY val DESC
      ", [$uid]),
      'chart_purchase_time' => $this->fetchAll("
        SELECT COALESCE(purchase_time,'(blank)') label, COUNT(*) val
        FROM leads
        WHERE assigned_to_user_id=?
          AND created_at >= $from AND created_at < $to
        GROUP BY label ORDER BY val DESC
      ", [$uid]),
      'chart_city' => $this->fetchAll("
        SELECT COALESCE(lead_city,'(blank)') label, COUNT(*) val
        FROM leads
        WHERE assigned_to_user_id=?
          AND created_at >= $from AND created_at < $to
        GROUP BY label ORDER BY val DESC LIMIT 12
      ", [$uid]),

// 2 tables
      'table_recent_leads' => $this->fetchAll("
        SELECT id, status, vehicle_of_interest, lead_city, created_at
        FROM leads
        WHERE assigned_to_user_id=?
        ORDER BY id DESC
        LIMIT 12
      ", [$uid]),
      'table_recent_opps' => $this->fetchAll("
        SELECT o.id, o.status, o.stage, o.created_at
        FROM opportunities o
        JOIN leads l ON l.id=o.lead_id
        WHERE l.assigned_to_user_id=?
        ORDER BY o.id DESC
        LIMIT 12
      ", [$uid]),
    ];
  }

// ======================= BRANCH MANAGER =======================
  private function branchManagerDashboard(int $branchId, string $from, string $to): array
  {
    if ($branchId <= 0) return ['kpis'=>[]];

$k = $this->fetchOne("
      SELECT
        COUNT(*) opp_total,
        SUM(CASE WHEN status='open' THEN 1 ELSE 0 END) open_cnt,
        SUM(CASE WHEN status='on_hold' THEN 1 ELSE 0 END) hold_cnt,
        SUM(CASE WHEN status='won' THEN 1 ELSE 0 END) won_cnt,
        SUM(CASE WHEN status='lost' THEN 1 ELSE 0 END) lost_cnt
      FROM opportunities
      WHERE branch_id=?
        AND created_at >= $from AND created_at < $to
    ", [$branchId]);

$k['win_rate'] = $this->safeRate((int)$k['won_cnt'], max(1, ((int)$k['won_cnt']+(int)$k['lost_cnt'])));

$byAgent = $this->fetchAll("
      SELECT u.name label,
             COUNT(o.id) opps,
             SUM(CASE WHEN o.status='open' THEN 1 ELSE 0 END) open_cnt,
             SUM(CASE WHEN o.status='won' THEN 1 ELSE 0 END) won_cnt,
             SUM(CASE WHEN o.status='lost' THEN 1 ELSE 0 END) lost_cnt,
             SUM(CASE WHEN o.status='on_hold' THEN 1 ELSE 0 END) hold_cnt
      FROM users u
      JOIN roles r ON r.id=u.role_id AND r.code='BR_AGENT'
      LEFT JOIN opportunities o ON o.assigned_to_user_id=u.id AND o.created_at >= $from AND o.created_at < $to
      WHERE u.branch_id=?
      GROUP BY u.id
      ORDER BY opps DESC
    ", [$branchId]);

return [
      'kpis'=>$k,

// 6 charts
      'chart_status' => $this->fetchAll("
        SELECT COALESCE(status,'(blank)') label, COUNT(*) val
        FROM opportunities
        WHERE branch_id=? AND created_at >= $from AND created_at < $to
        GROUP BY label ORDER BY val DESC
      ", [$branchId]),
      'chart_stage' => $this->fetchAll("
        SELECT COALESCE(stage,'(blank)') label, COUNT(*) val
        FROM opportunities
        WHERE branch_id=? AND created_at >= $from AND created_at < $to
        GROUP BY label ORDER BY val DESC
      ", [$branchId]),
      'chart_stage_status' => $this->fetchAll("
        SELECT COALESCE(stage_status,'(blank)') label, COUNT(*) val
        FROM opportunities
        WHERE branch_id=? AND created_at >= $from AND created_at < $to
        GROUP BY label ORDER BY val DESC LIMIT 12
      ", [$branchId]),
      'chart_by_agent' => $byAgent,
      'chart_won_by_agent' => array_map(fn($r)=>['label'=>$r['label'],'val'=>(int)$r['won_cnt']], $byAgent),
      'chart_trend' => $this->trendOpps($from, $to, "branch_id=?", [$branchId]),

// 2 tables
      'table_agents' => $byAgent,
      'table_recent' => $this->fetchAll("
        SELECT id, status, stage, stage_status, assigned_to_user_id, created_at
        FROM opportunities
        WHERE branch_id=? AND created_at >= $from AND created_at < $to
        ORDER BY id DESC
        LIMIT 12
      ", [$branchId]),
    ];
  }

// ======================= BRANCH AGENT =======================
  private function branchAgentDashboard(int $uid, string $from, string $to): array
  {
    $k = $this->fetchOne("
      SELECT
        COUNT(*) opp_total,
        SUM(CASE WHEN status='open' THEN 1 ELSE 0 END) open_cnt,
        SUM(CASE WHEN status='on_hold' THEN 1 ELSE 0 END) hold_cnt,
        SUM(CASE WHEN status='won' THEN 1 ELSE 0 END) won_cnt,
        SUM(CASE WHEN status='lost' THEN 1 ELSE 0 END) lost_cnt
      FROM opportunities
      WHERE assigned_to_user_id=?
        AND created_at >= $from AND created_at < $to
    ", [$uid]);

$k['win_rate'] = $this->safeRate((int)$k['won_cnt'], max(1, ((int)$k['won_cnt']+(int)$k['lost_cnt'])));

return [
      'kpis'=>$k,

// 6 charts
      'chart_status' => $this->fetchAll("
        SELECT COALESCE(status,'(blank)') label, COUNT(*) val
        FROM opportunities
        WHERE assigned_to_user_id=? AND created_at >= $from AND created_at < $to
        GROUP BY label ORDER BY val DESC
      ", [$uid]),
      'chart_stage' => $this->fetchAll("
        SELECT COALESCE(stage,'(blank)') label, COUNT(*) val
        FROM opportunities
        WHERE assigned_to_user_id=? AND created_at >= $from AND created_at < $to
        GROUP BY label ORDER BY val DESC
      ", [$uid]),
      'chart_stage_status' => $this->fetchAll("
        SELECT COALESCE(stage_status,'(blank)') label, COUNT(*) val
        FROM opportunities
        WHERE assigned_to_user_id=? AND created_at >= $from AND created_at < $to
        GROUP BY label ORDER BY val DESC LIMIT 12
      ", [$uid]),
      'chart_trend' => $this->trendOpps($from, $to, "assigned_to_user_id=?", [$uid]),
      'chart_vehicle' => $this->fetchAll("
        SELECT COALESCE(l.vehicle_of_interest,'(blank)') label, COUNT(*) val
        FROM opportunities o
        JOIN leads l ON l.id=o.lead_id
        WHERE o.assigned_to_user_id=? AND o.created_at >= $from AND o.created_at < $to
        GROUP BY label ORDER BY val DESC
      ", [$uid]),
      'chart_lost_reasons' => $this->fetchAll("
        SELECT COALESCE(stage_status,'(blank)') label, COUNT(*) val
        FROM opportunities
        WHERE assigned_to_user_id=? AND created_at >= $from AND created_at < $to
          AND status='lost'
        GROUP BY label ORDER BY val DESC LIMIT 12
      ", [$uid]),

// 2 tables
      'table_recent' => $this->fetchAll("
        SELECT id, status, stage, stage_status, created_at
        FROM opportunities
        WHERE assigned_to_user_id=? AND created_at >= $from AND created_at < $to
        ORDER BY id DESC
        LIMIT 12
      ", [$uid]),
      'table_recent_contacts' => $this->fetchAll("
        SELECT o.id, c.mobile, c.full_name, o.status, o.created_at
        FROM opportunities o
        JOIN contacts c ON c.id=o.contact_id
        WHERE o.assigned_to_user_id=? AND o.created_at >= $from AND o.created_at < $to
        ORDER BY o.id DESC
        LIMIT 12
      ", [$uid]),
    ];
  }

// ======================= SALES MANAGER / ADMIN =======================
  private function salesManagerDashboard(string $from, string $to): array
  {
    $k = $this->fetchOne("
      SELECT
        (SELECT COUNT(*) FROM leads l WHERE l.created_at >= $from AND l.created_at < $to) AS leads_total,
        (SELECT COUNT(*) FROM leads l WHERE l.created_at >= $from AND l.created_at < $to AND l.qualification='qualified') AS leads_qualified,
        (SELECT COUNT(*) FROM opportunities o WHERE o.created_at >= $from AND o.created_at < $to) AS opp_total,
        (SELECT COUNT(*) FROM opportunities o WHERE o.created_at >= $from AND o.created_at < $to AND o.status='won') AS opp_won,
        (SELECT COUNT(*) FROM opportunities o WHERE o.created_at >= $from AND o.created_at < $to AND o.status='lost') AS opp_lost,
        (SELECT COUNT(*) FROM leads l WHERE l.created_at >= $from AND l.created_at < $to AND l.qualification='disqualified') AS leads_disqualified
    ");

return [
      'kpis'=>$k,

// 6 charts
      'chart_funnel' => [
        ['label'=>'Leads','val'=>(int)$k['leads_total']],
        ['label'=>'Qualified','val'=>(int)$k['leads_qualified']],
        ['label'=>'Opportunities','val'=>(int)$k['opp_total']],
        ['label'=>'Won','val'=>(int)$k['opp_won']],
        ['label'=>'Lost','val'=>(int)$k['opp_lost']],
        ['label'=>'Disqualified','val'=>(int)$k['leads_disqualified']],
      ],
      'chart_opp_status' => $this->fetchAll("
        SELECT COALESCE(status,'(blank)') label, COUNT(*) val
        FROM opportunities
        WHERE created_at >= $from AND created_at < $to
        GROUP BY label ORDER BY val DESC
      "),
      'chart_opp_stage' => $this->fetchAll("
        SELECT COALESCE(stage,'(blank)') label, COUNT(*) val
        FROM opportunities
        WHERE created_at >= $from AND created_at < $to
        GROUP BY label ORDER BY val DESC
      "),
      'chart_branch_won' => $this->fetchAll("
        SELECT b.name_en label,
               SUM(CASE WHEN o.status='won' THEN 1 ELSE 0 END) val
        FROM opportunities o
        JOIN branches b ON b.id=o.branch_id
        WHERE o.created_at >= $from AND o.created_at < $to
        GROUP BY b.id
        ORDER BY val DESC
        LIMIT 12
      "),
      'chart_vehicle' => $this->fetchAll("
        SELECT COALESCE(l.vehicle_of_interest,'(blank)') label, COUNT(*) val
        FROM leads l
        WHERE l.created_at >= $from AND l.created_at < $to
        GROUP BY label ORDER BY val DESC
      "),
      'chart_source' => $this->fetchAll("
        SELECT COALESCE(source,'(blank)') label, COUNT(*) val
        FROM leads
        WHERE created_at >= $from AND created_at < $to
        GROUP BY label ORDER BY val DESC
        LIMIT 12
      "),

// 2 tables
      'table_branch_leaderboard' => $this->fetchAll("
        SELECT b.name_en branch,
               COUNT(o.id) opps,
               SUM(CASE WHEN o.status='won' THEN 1 ELSE 0 END) won,
               SUM(CASE WHEN o.status='lost' THEN 1 ELSE 0 END) lost
        FROM branches b
        LEFT JOIN opportunities o ON o.branch_id=b.id AND o.created_at >= $from AND o.created_at < $to
        GROUP BY b.id
        ORDER BY won DESC, opps DESC
        LIMIT 12
      "),
      'table_agent_leaderboard' => $this->fetchAll("
        SELECT COALESCE(u.name,'(unassigned)') agent,
               COUNT(o.id) opps,
               SUM(CASE WHEN o.status='won' THEN 1 ELSE 0 END) won,
               SUM(CASE WHEN o.status='lost' THEN 1 ELSE 0 END) lost
        FROM opportunities o
        LEFT JOIN users u ON u.id=o.assigned_to_user_id
        WHERE o.created_at >= $from AND o.created_at < $to
        GROUP BY o.assigned_to_user_id
        ORDER BY won DESC, opps DESC
        LIMIT 12
      "),
    ];
  }
}

MR SKB