<?php
// csr/ajax/get_manager_dashboard.php
// Returns all data needed for the CSR Manager dashboard.
require_once '../../config/database.php';
session_start();
header('Content-Type: application/json');

if (!isset($_SESSION['csr_id'])) {
    echo json_encode(['success' => false, 'message' => 'Unauthorized']);
    exit();
}

try {
    $database = new Database();
    $db = $database->getConnection();

    require_once '../../includes/app_helpers.php';
    require_once '../../includes/csr_report_helpers.php';
    require_once '../../includes/discipline_system.php';

    ensureOperationalTables($db);
    ensureCsrTeamTables($db);
    ensureCsrRoleSchema($db);
    discEnsureTables($db);

    $managerId = (int)$_SESSION['csr_id'];
    $role = getCsrRoleForReport($db, $managerId);

    // Only managers and team_leaders may access this endpoint
    if (!in_array($role, ['team_leader', 'csr_manager'], true)) {
        echo json_encode(['success' => false, 'message' => 'Access denied']);
        exit();
    }

    /**
     * CSR Manager dashboard operational metrics.
     * These are the exact cards used in the manager dashboard and team cards.
     */
    function emptyManagerOps(): array {
        return [
            'handled_today'    => 0,
            'assigned_tickets' => 0,
            'in_progress'      => 0,
            'completed_today'  => 0,
            'cancelled_today'  => 0,
            'total_calls'      => 0,
            'calls_scheduled'  => 0,
            'connected'        => 0,
            'missed_absent'    => 0,
            'updates'          => 0,
            'warnings'         => 0,
            'mistakes'         => 0,
            // Legacy aliases kept so older chart code will not break.
            'gh'               => 0,
            'schedule'         => 0,
            'attempt'          => 0,
            'update'           => 0,
        ];
    }

    function makeInClause(array $ids, string $prefix, array &$params): string {
        $holders = [];
        foreach (array_values(array_unique(array_map('intval', $ids))) as $i => $id) {
            $ph = ':' . $prefix . $i;
            $holders[] = $ph;
            $params[$ph] = $id;
        }
        return $holders ? implode(',', $holders) : 'NULL';
    }

    function mgrHasTable(PDO $db, string $table): bool {
        if (function_exists('tableExistsSafe')) return tableExistsSafe($db, $table);
        try {
            $stmt = $db->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :table");
            $stmt->execute([':table' => $table]);
            return (int)$stmt->fetchColumn() > 0;
        } catch (Exception $e) { return false; }
    }

    function mgrHasColumn(PDO $db, string $table, string $column): bool {
        if (function_exists('columnExists')) return columnExists($db, $table, $column);
        try {
            $stmt = $db->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :table AND COLUMN_NAME = :column");
            $stmt->execute([':table' => $table, ':column' => $column]);
            return (int)$stmt->fetchColumn() > 0;
        } catch (Exception $e) { return false; }
    }

    function managerDateExpr(PDO $db, string $table, array $preferred): string {
        $parts = [];
        foreach ($preferred as $col) {
            if (mgrHasColumn($db, $table, $col)) $parts[] = $col;
        }
        if (!$parts) return 'NOW()';
        return count($parts) === 1 ? $parts[0] : 'COALESCE(' . implode(',', $parts) . ')';
    }

    function managerCount(PDO $db, string $sql, array $params = []): int {
        try { $stmt = $db->prepare($sql); $stmt->execute($params); return (int)$stmt->fetchColumn(); }
        catch (Throwable $e) { return 0; }
    }

    function serviceRequestOwnerCondition(PDO $db, array $csrIds, string $prefix, array &$params): string {
        if (!mgrHasTable($db, 'service_requests')) return '1=0';
        $ors = [];
        foreach (['assigned_csr_id','handled_by_csr_id','created_by_csr_id'] as $idx => $col) {
            if (mgrHasColumn($db, 'service_requests', $col)) {
                $in = makeInClause($csrIds, $prefix . $idx . '_', $params);
                $ors[] = "$col IN ($in)";
            }
        }
        return $ors ? '(' . implode(' OR ', $ors) . ')' : '1=0';
    }

    function managerScopedRequestParts(PDO $db, array $csrIds, int $teamId, string $prefix, array &$params, ?string $startDate = null, ?string $endDate = null): array {
        $parts = [];
        $dateFilter = function(string $table, array $preferred, string $alias = '') use ($db, $startDate, $endDate, &$params, $prefix): string {
            if ($startDate === null || $endDate === null) return '';
            $expr = managerDateExpr($db, $table, $preferred);
            $fromKey = ':' . $prefix . '_from_' . count($params);
            $toKey   = ':' . $prefix . '_to_' . (count($params) + 1);
            $params[$fromKey] = $startDate;
            $params[$toKey]   = $endDate;
            $col = $alias !== '' ? str_replace(['COALESCE(', ','], ['COALESCE(' . $alias . '.', ',' . $alias . '.'], $expr) : $expr;
            // Avoid aliasing NOW().
            $col = str_replace($alias . '.NOW()', 'NOW()', $col);
            return " AND DATE($col) BETWEEN $fromKey AND $toKey";
        };

        if (mgrHasTable($db, 'service_requests')) {
            $ownerCond = serviceRequestOwnerCondition($db, $csrIds, $prefix . '_sr', $params);
            if ($ownerCond !== '1=0') {
                $parts[] = "SELECT id AS request_id FROM service_requests WHERE $ownerCond" . $dateFilter('service_requests', ['last_updated_at','completed_date','requested_at','created_at']);
            }
        }

        if (mgrHasTable($db, 'csr_activity') && mgrHasColumn($db, 'csr_activity', 'csr_id') && mgrHasColumn($db, 'csr_activity', 'request_id')) {
            $in = makeInClause($csrIds, $prefix . '_ca', $params);
            $parts[] = "SELECT request_id FROM csr_activity WHERE csr_id IN ($in) AND request_id IS NOT NULL" . $dateFilter('csr_activity', ['created_at']);
        }

        if (mgrHasTable($db, 'csr_activity_logs') && mgrHasColumn($db, 'csr_activity_logs', 'csr_id') && mgrHasColumn($db, 'csr_activity_logs', 'request_id')) {
            $in = makeInClause($csrIds, $prefix . '_cal', $params);
            $parts[] = "SELECT request_id FROM csr_activity_logs WHERE csr_id IN ($in) AND request_id IS NOT NULL" . $dateFilter('csr_activity_logs', ['created_at']);
        }

        if (mgrHasTable($db, 'request_logs') && mgrHasColumn($db, 'request_logs', 'user_id') && mgrHasColumn($db, 'request_logs', 'request_id')) {
            $in = makeInClause($csrIds, $prefix . '_rl', $params);
            $parts[] = "SELECT request_id FROM request_logs WHERE user_id IN ($in) AND user_role IN ('csr','team_leader','csr_manager') AND request_id IS NOT NULL" . $dateFilter('request_logs', ['created_at']);
        }

        if (mgrHasTable($db, 'csr_request_locks') && mgrHasColumn($db, 'csr_request_locks', 'csr_id')) {
            $in = makeInClause($csrIds, $prefix . '_lk', $params);
            $parts[] = "SELECT request_id FROM csr_request_locks WHERE csr_id IN ($in) AND request_id IS NOT NULL" . $dateFilter('csr_request_locks', ['locked_at','created_at']);
        }

        if (mgrHasTable($db, 'service_assignments') && mgrHasColumn($db, 'service_assignments', 'assigned_by')) {
            $in = makeInClause($csrIds, $prefix . '_sa', $params);
            $parts[] = "SELECT request_id FROM service_assignments WHERE assigned_by IN ($in) AND request_id IS NOT NULL" . $dateFilter('service_assignments', ['assigned_at','created_at']);
        }

        if (mgrHasTable($db, 'call_requests') && mgrHasColumn($db, 'call_requests', 'request_id')) {
            $ors = [];
            if ($teamId > 0 && mgrHasColumn($db, 'call_requests', 'team_id')) {
                $ph = ':' . $prefix . '_call_team';
                $params[$ph] = $teamId;
                $ors[] = "team_id = $ph";
            }
            if (mgrHasColumn($db, 'call_requests', 'manager_id')) {
                $in = makeInClause($csrIds, $prefix . '_call_mgr', $params);
                $ors[] = "manager_id IN ($in)";
            }
            if (mgrHasColumn($db, 'call_requests', 'assigned_csr_id')) {
                $in = makeInClause($csrIds, $prefix . '_call_assigned', $params);
                $ors[] = "assigned_csr_id IN ($in)";
            }
            if (mgrHasColumn($db, 'call_requests', 'accepted_by')) {
                $in = makeInClause($csrIds, $prefix . '_call_accepted', $params);
                $ors[] = "accepted_by IN ($in)";
            }
            if ($ors) {
                $parts[] = "SELECT request_id FROM call_requests WHERE request_id IS NOT NULL AND (" . implode(' OR ', $ors) . ")" . $dateFilter('call_requests', ['accepted_at','completed_at','queued_at','created_at']);
            }
        }

        return $parts;
    }



    function sumManagerOps(array $rows): array {
        $sum = emptyManagerOps();
        foreach ($rows as $row) {
            foreach (array_keys($sum) as $key) {
                if (isset($row[$key])) {
                    $sum[$key] += (int)$row[$key];
                }
            }
        }
        // Keep aliases exact after adding the new dashboard fields.
        $sum['gh']       = $sum['handled_today'];
        $sum['schedule'] = $sum['calls_scheduled'];
        $sum['attempt']  = $sum['total_calls'];
        $sum['update']   = $sum['updates'];
        return $sum;
    }

    function calculateManagerOpsForScope(PDO $db, array $csrIds, string $startDate, string $endDate, int $teamId = 0): array {
        $out = emptyManagerOps();
        $csrIds = array_values(array_unique(array_filter(array_map('intval', $csrIds))));
        if (!$csrIds) return $out;

        // Handled Today = distinct tickets touched/owned by these CSRs or Team Leaders.
        // Uses the real tables in your dump: request_logs, csr_activity, service_assignments,
        // and call_requests team routing. This fixes blank cards when service_requests owner columns are NULL.
        $params = [];
        $unionParts = managerScopedRequestParts($db, $csrIds, $teamId, 'handled', $params, $startDate, $endDate);
        if ($unionParts) {
            $out['handled_today'] = managerCount($db, "SELECT COUNT(DISTINCT request_id) FROM (" . implode(' UNION ', $unionParts) . ") x", $params);
        }

        // Current ticket status cards from service_requests, scoped through activity/log/team call routing.
        if (mgrHasTable($db, 'service_requests')) {
            $params = [':sr_from' => $startDate, ':sr_to' => $endDate];
            $scopeParts = managerScopedRequestParts($db, $csrIds, $teamId, 'srscope', $params, null, null);
            if ($scopeParts) {
                $dateExpr = managerDateExpr($db, 'service_requests', ['last_updated_at','completed_date','requested_at','created_at']);
                $statusCol = mgrHasColumn($db, 'service_requests', 'job_status_id') ? 'job_status_id' : null;
                if ($statusCol) {
                    $statusJoin = mgrHasTable($db, 'job_statuses') ? " LEFT JOIN job_statuses js ON js.id = sr.$statusCol " : "";
                    $statusNameExpr = mgrHasTable($db, 'job_statuses') ? "LOWER(COALESCE(js.status_name,''))" : "''";
                    $sql = "SELECT
                                COALESCE(SUM((COALESCE(sr.$statusCol,1) NOT IN (4,5)) AND $statusNameExpr NOT LIKE '%complete%' AND $statusNameExpr NOT LIKE '%cancel%'),0) AS assigned_tickets,
                                COALESCE(SUM(COALESCE(sr.$statusCol,0) = 3 OR $statusNameExpr LIKE '%progress%'),0) AS in_progress,
                                COALESCE(SUM((COALESCE(sr.$statusCol,0) = 4 OR $statusNameExpr LIKE '%complete%') AND DATE($dateExpr) BETWEEN :sr_from AND :sr_to),0) AS completed_today,
                                COALESCE(SUM((COALESCE(sr.$statusCol,0) = 5 OR $statusNameExpr LIKE '%cancel%') AND DATE($dateExpr) BETWEEN :sr_from AND :sr_to),0) AS cancelled_today
                            FROM service_requests sr
                            $statusJoin
                            WHERE sr.id IN (SELECT DISTINCT request_id FROM (" . implode(' UNION ', $scopeParts) . ") scoped_requests)";
                    try {
                        $stmt = $db->prepare($sql); $stmt->execute($params); $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
                        foreach (['assigned_tickets','in_progress','completed_today','cancelled_today'] as $k) $out[$k] = (int)($row[$k] ?? 0);
                    } catch (Throwable $e) {}

                    // Extra fallback for In Progress: count current tickets directly by CSR ownership.
                    // This catches tickets that are still In Progress even when their latest activity/log is old.
                    if ($out['in_progress'] === 0) {
                        $fallbackParams = [];
                        $fallbackOwner = serviceRequestOwnerCondition($db, $csrIds, 'ip_owner', $fallbackParams);
                        if ($fallbackOwner !== '1=0') {
                            $join = mgrHasTable($db, 'job_statuses') ? " LEFT JOIN job_statuses js ON js.id = sr.$statusCol " : "";
                            $nameExpr = mgrHasTable($db, 'job_statuses') ? "LOWER(COALESCE(js.status_name,''))" : "''";
                            $out['in_progress'] = managerCount($db, "SELECT COUNT(DISTINCT sr.id) FROM service_requests sr $join WHERE $fallbackOwner AND (COALESCE(sr.$statusCol,0)=3 OR $nameExpr LIKE '%progress%')", $fallbackParams);
                        }
                    }
                }
            }
        }

        // Completed/cancelled by logs are added as fallback when direct ownership columns are missing.
        if ($out['completed_today'] === 0 && mgrHasTable($db, 'request_logs')) {
            $params = [':sd'=>$startDate, ':ed'=>$endDate];
            $in = makeInClause($csrIds, 'comp_rl', $params);
            $out['completed_today'] = managerCount($db, "SELECT COUNT(DISTINCT request_id) FROM request_logs WHERE user_id IN ($in) AND user_role IN ('csr','team_leader','csr_manager') AND action='status_change' AND CAST(new_value AS CHAR)='4' AND DATE(created_at) BETWEEN :sd AND :ed", $params);
        }
        if ($out['cancelled_today'] === 0 && mgrHasTable($db, 'request_logs')) {
            $params = [':sd'=>$startDate, ':ed'=>$endDate];
            $in = makeInClause($csrIds, 'can_rl', $params);
            $out['cancelled_today'] = managerCount($db, "SELECT COUNT(DISTINCT request_id) FROM request_logs WHERE user_id IN ($in) AND user_role IN ('csr','team_leader','csr_manager') AND action='status_change' AND CAST(new_value AS CHAR)='5' AND DATE(created_at) BETWEEN :sd AND :ed", $params);
        }

        // Call stats from call_requests. Include team_id / manager_id routing because your call rows
        // are often assigned to a team before an individual CSR accepts them.
        if (mgrHasTable($db, 'call_requests')) {
            $params = [':cr_from' => $startDate, ':cr_to' => $endDate];
            $ors = [];
            if ($teamId > 0 && mgrHasColumn($db, 'call_requests', 'team_id')) { $params[':cr_team_id'] = $teamId; $ors[] = "team_id = :cr_team_id"; }
            if (mgrHasColumn($db, 'call_requests', 'manager_id')) { $in = makeInClause($csrIds, 'crm', $params); $ors[] = "manager_id IN ($in)"; }
            if (mgrHasColumn($db, 'call_requests', 'assigned_csr_id')) { $in = makeInClause($csrIds, 'cra', $params); $ors[] = "assigned_csr_id IN ($in)"; }
            if (mgrHasColumn($db, 'call_requests', 'accepted_by')) { $in = makeInClause($csrIds, 'crb', $params); $ors[] = "accepted_by IN ($in)"; }
            if ($ors) {
                $dateExpr = managerDateExpr($db, 'call_requests', ['accepted_at','completed_at','queued_at','created_at']);
                $sql = "SELECT
                            COUNT(*) AS total_calls,
                            COALESCE(SUM(LOWER(COALESCE(status,'')) IN ('assigned','manager_queue','scheduled','pending')),0) AS calls_scheduled,
                            COALESCE(SUM(LOWER(COALESCE(status,'')) IN ('accepted','completed','connected')),0) AS connected,
                            COALESCE(SUM(LOWER(COALESCE(status,'')) IN ('missed','cancelled','canceled','no_answer','no answer','absent')),0) AS missed_absent
                        FROM call_requests
                        WHERE (" . implode(' OR ', $ors) . ")
                          AND DATE($dateExpr) BETWEEN :cr_from AND :cr_to";
                try {
                    $stmt = $db->prepare($sql); $stmt->execute($params); $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
                    foreach (['total_calls','calls_scheduled','connected','missed_absent'] as $k) $out[$k] = (int)($row[$k] ?? 0);
                } catch (Throwable $e) {}
            }
        }

        // Activity stats from csr_activity.
        if (mgrHasTable($db, 'csr_activity') && mgrHasColumn($db, 'csr_activity', 'csr_id')) {
            $params = [':act_from' => $startDate, ':act_to' => $endDate];
            $in = makeInClause($csrIds, 'act', $params);
            $sql = "SELECT
                        COALESCE(SUM(action IN ('status_change','note_added','request_claimed','manual_ticket_created') OR action LIKE '%update%'),0) AS updates,
                        COALESCE(SUM(action LIKE '%warning%' OR details LIKE '%warning%'),0) AS warnings,
                        COALESCE(SUM(action LIKE '%mistake%' OR action LIKE '%error%' OR details LIKE '%mistake%' OR details LIKE '%error%'),0) AS mistakes
                    FROM csr_activity
                    WHERE csr_id IN ($in)
                      AND DATE(created_at) BETWEEN :act_from AND :act_to";
            try {
                $stmt = $db->prepare($sql); $stmt->execute($params); $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
                foreach (['updates','warnings','mistakes'] as $k) $out[$k] = (int)($row[$k] ?? 0);
            } catch (Throwable $e) {}
        }

        // New Mistake / Warning records from csr_discipline_records.
        if (mgrHasTable($db, 'csr_discipline_records')) {
            $params = [':disc_from' => $startDate, ':disc_to' => $endDate];
            $in = makeInClause($csrIds, 'disc', $params);
            $sql = "SELECT
                        COALESCE(SUM(record_type='warning'),0) AS warnings,
                        COALESCE(SUM(record_type='mistake'),0) AS mistakes
                    FROM csr_discipline_records
                    WHERE recipient_id IN ($in)
                      AND DATE(created_at) BETWEEN :disc_from AND :disc_to";
            try {
                $stmt = $db->prepare($sql); $stmt->execute($params); $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
                $out['warnings'] += (int)($row['warnings'] ?? 0);
                $out['mistakes'] += (int)($row['mistakes'] ?? 0);
            } catch (Throwable $e) {}
        }

        // Backward-compatible aliases for charts / old templates.
        $out['gh']       = $out['handled_today'];
        $out['schedule'] = $out['calls_scheduled'];
        $out['attempt']  = $out['total_calls'];
        $out['update']   = $out['updates'];
        return $out;
    }



    function managerTeamNameForUser(array $teamMembers, array $teams, int $csrId): string {
        foreach ($teamMembers as $tid => $memberList) {
            if (in_array($csrId, $memberList, true)) {
                foreach ($teams as $t) {
                    if ((int)($t['id'] ?? 0) === (int)$tid) return (string)($t['team_name'] ?? '');
                }
            }
        }
        return '';
    }

    function managerDisciplineBoards(PDO $db, array $scopeIds, array $teamMembers, array $teams, int $limit = 10): array {
        $empty = ['warning_records' => [], 'mistakes' => []];
        $scopeIds = array_values(array_unique(array_filter(array_map('intval', $scopeIds))));
        if (!$scopeIds || !mgrHasTable($db, 'csr_discipline_records')) return $empty;

        $params = [];
        $in = makeInClause($scopeIds, 'board_disc', $params);
        $sql = "SELECT dr.recipient_id,
                       dr.record_type,
                       COUNT(*) AS total_records,
                       MAX(dr.created_at) AS last_record_at,
                       TRIM(CONCAT(COALESCE(cu.first_name,''),' ',COALESCE(cu.last_name,''))) AS recipient_name,
                       COALESCE(cu.email,'') AS recipient_email,
                       COALESCE(cu.role,'csr') AS recipient_role,
                       COALESCE(ct.team_name,'') AS discipline_team_name
                  FROM csr_discipline_records dr
                  JOIN csr_users cu ON cu.id = dr.recipient_id
             LEFT JOIN csr_teams ct ON ct.id = dr.team_id AND ct.is_active = 1
                 WHERE dr.recipient_id IN ($in)
                   AND dr.record_type IN ('warning','mistake')
                 GROUP BY dr.recipient_id, dr.record_type, cu.first_name, cu.last_name, cu.email, cu.role, ct.team_name
                 ORDER BY total_records DESC, last_record_at DESC";
        try {
            $stmt = $db->prepare($sql);
            $stmt->execute($params);
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (Throwable $e) {
            return $empty;
        }

        $detailsByUserType = [];
        try {
            $detailSql = "SELECT dr.id, dr.recipient_id, dr.record_type, dr.title, dr.message, dr.severity,
                                 dr.sender_role, dr.created_at,
                                 CASE WHEN dr.sender_role = 'admin'
                                      THEN TRIM(CONCAT(COALESCE(au.first_name,''),' ',COALESCE(au.last_name,'')))
                                      ELSE TRIM(CONCAT(COALESCE(su.first_name,''),' ',COALESCE(su.last_name,'')))
                                 END AS sender_name,
                                 CASE WHEN dr.sender_role = 'admin' THEN au.email ELSE su.email END AS sender_email
                            FROM csr_discipline_records dr
                       LEFT JOIN csr_users su ON su.id = dr.sender_id AND dr.sender_role <> 'admin'
                       LEFT JOIN admin_users au ON au.id = dr.sender_id AND dr.sender_role = 'admin'
                           WHERE dr.recipient_id IN ($in)
                             AND dr.record_type IN ('warning','mistake')
                        ORDER BY dr.created_at DESC
                           LIMIT 250";
            $detailStmt = $db->prepare($detailSql);
            $detailStmt->execute($params);
            foreach (($detailStmt->fetchAll(PDO::FETCH_ASSOC) ?: []) as $d) {
                $key = ((int)($d['recipient_id'] ?? 0)) . ':' . (string)($d['record_type'] ?? '');
                if (!isset($detailsByUserType[$key])) $detailsByUserType[$key] = [];
                if (count($detailsByUserType[$key]) < 8) {
                    $senderName = trim((string)($d['sender_name'] ?? '')) ?: ((string)($d['sender_email'] ?? '') ?: ucfirst((string)($d['sender_role'] ?? '')));
                    $detailsByUserType[$key][] = [
                        'id' => (int)($d['id'] ?? 0),
                        'title' => (string)($d['title'] ?? ''),
                        'message' => (string)($d['message'] ?? ''),
                        'severity' => (string)($d['severity'] ?? 'normal'),
                        'sender' => $senderName,
                        'sender_role' => (string)($d['sender_role'] ?? ''),
                        'created_at' => (string)($d['created_at'] ?? ''),
                    ];
                }
            }
        } catch (Throwable $e) {
            $detailsByUserType = [];
        }

        $warningRecords = [];
        $mistakesBoard = [];
        foreach ($rows as $r) {
            $csrId = (int)($r['recipient_id'] ?? 0);
            $type = (string)($r['record_type'] ?? '');
            $name = trim((string)($r['recipient_name'] ?? '')) ?: ((string)($r['recipient_email'] ?? '') ?: ('User #' . $csrId));
            $base = [
                'csr_id' => $csrId,
                'name' => $name,
                'team' => managerTeamNameForUser($teamMembers, $teams, $csrId) ?: (string)($r['discipline_team_name'] ?? ''),
                'role' => $r['recipient_role'] ?? 'csr',
                'updates' => 0,
                'total_calls' => 0,
                'last_record_at' => $r['last_record_at'] ?? null,
                'details' => $detailsByUserType[$csrId . ':' . $type] ?? [],
            ];
            if ($type === 'warning') {
                $warningRecords[] = array_merge($base, ['warnings' => (int)($r['total_records'] ?? 0)]);
            } elseif ($type === 'mistake') {
                $mistakesBoard[] = array_merge($base, ['mistakes' => (int)($r['total_records'] ?? 0)]);
            }
        }
        return [
            'warning_records' => array_slice($warningRecords, 0, $limit),
            'mistakes' => array_slice($mistakesBoard, 0, $limit),
        ];
    }

    // Dashboard has two required scopes:
    // 1) Overall Total = all available history up to today.
    // 2) Today = only today's performance.
    // Optional GET date filters are still supported for charts/team cards; the Today cards remain today.
    $todayDate = date('Y-m-d');
    $overallStartDate = '2000-01-01';
    $overallEndDate = $todayDate;

    $endDate   = $todayDate;
    $startDate = $todayDate;

    if (!empty($_GET['start_date'])) $startDate = $_GET['start_date'];
    if (!empty($_GET['end_date']))   $endDate   = $_GET['end_date'];

    // -------------------------------------------------------
    // 1. Resolve teams this manager oversees
    // -------------------------------------------------------
    if ($role === 'csr_manager') {
        $teamStmt = $db->query("SELECT id, team_name, manager_id FROM csr_teams WHERE is_active = 1 ORDER BY team_name");
    } else {
        $teamStmt = $db->prepare("SELECT id, team_name, manager_id FROM csr_teams WHERE manager_id = :mid AND is_active = 1 ORDER BY team_name");
        $teamStmt->execute([':mid' => $managerId]);
    }
    $teams = $teamStmt->fetchAll(PDO::FETCH_ASSOC);
    $teamIds = array_column($teams, 'id');

    if (!$teamIds) {
        echo json_encode([
            'success'       => true,
            'teams'         => [],
            'summary'       => emptyManagerOps(),
            'summary_today' => emptyManagerOps(),
            'summary_overall' => emptyManagerOps(),
            'agent_count'   => 0,
            'team_stats'    => [],
            'team_stats_overall' => [],
            'trend'         => [],
            'task_status'   => [],
            'warning_records' => [],
            'mistakes'      => [],
        ]);
        exit();
    }

    // -------------------------------------------------------
    // 2. Agents per team
    // -------------------------------------------------------
    $placeholders = implode(',', array_fill(0, count($teamIds), '?'));
    $memberStmt = $db->prepare(
        "SELECT ctm.team_id, ctm.csr_id, cu.first_name, cu.last_name, cu.role,
                ct.manager_id
           FROM csr_team_members ctm
           JOIN csr_users cu ON cu.id = ctm.csr_id AND cu.is_active = 1
           JOIN csr_teams ct ON ct.id = ctm.team_id
          WHERE ctm.team_id IN ($placeholders)"
    );
    $memberStmt->execute($teamIds);
    $members = $memberStmt->fetchAll(PDO::FETCH_ASSOC);

    // Organise: team_id => [csr_ids...]
    // IMPORTANT: include BOTH the Team Leader (csr_teams.manager_id) and the team members.
    // The old dashboard only counted csr_team_members, so Team Leader work was missing from the manager totals.
    $teamMembers = [];
    foreach ($teams as $t) {
        $tid = (int)$t['id'];
        $teamMembers[$tid] = [];
        $leaderId = (int)($t['manager_id'] ?? 0);
        if ($leaderId > 0) {
            $teamMembers[$tid][] = $leaderId;
        }
    }
    foreach ($members as $m) {
        $tid = (int)$m['team_id'];
        $cid = (int)$m['csr_id'];
        if ($cid > 0) {
            $teamMembers[$tid][] = $cid;
        }
    }
    foreach ($teamMembers as $tid => $ids) {
        $teamMembers[$tid] = array_values(array_unique(array_filter(array_map('intval', $ids))));
    }

    // -------------------------------------------------------
    // 3. Per-agent report stats over the selected date range
    // -------------------------------------------------------
    // Collect all CSR + Team Leader IDs we need stats for.
    $allCsrIds = array_values(array_unique(array_merge(...array_values($teamMembers ?: [[]]))));
    if (!$allCsrIds) $allCsrIds = [];

    // CSR Manager dashboard must include the manager's own received warnings/mistakes too.
    // Team members + team leaders are counted through teamMembers; this adds the manager account itself.
    if ($managerId > 0) {
        $allCsrIds[] = $managerId;
        $allCsrIds = array_values(array_unique(array_filter(array_map('intval', $allCsrIds))));
    }

    // Build summary stats using the standard report helper
    // Mapping: gh=total_tickets_handled, schedule=total_technician_assigned,
    //          attempt=total_in_progress+total_completed, update=total_completed,
    //          warnings & mistakes require a warnings/notes table (0 fallback if unavailable)
    $csrStats = [];
    foreach ($allCsrIds as $csrId) {
        $csrStats[$csrId] = calculateManagerOpsForScope($db, [$csrId], $startDate, $endDate);
    }

    // -------------------------------------------------------
    // 4. Per-team aggregated stats
    // -------------------------------------------------------
    $teamStats = [];
    foreach ($teams as $team) {
        $tid  = (int)$team['id'];
        $ids  = $teamMembers[$tid] ?? [];
        $agentCount = count($ids);

        // Use the same per-person performance source used by the team leader/agent performance pages,
        // then add those rows together for this exact team. This makes each team container reflect
        // that team's Team Leader + CSR Agents, not unrelated global totals.
        $personRows = [];
        foreach ($ids as $cid) {
            if (isset($csrStats[$cid])) {
                $personRows[] = $csrStats[$cid];
            }
        }
        $stats = calculateManagerOpsForScope($db, $ids, $startDate, $endDate, $tid);

        $teamStats[] = array_merge([
            'team_id'   => $tid,
            'team_name' => $team['team_name'],
            'agents'    => $agentCount,
        ], $stats);
    }

    // -------------------------------------------------------
    // 5. Overall and today summary totals
    // -------------------------------------------------------
    // Today summary is the TOTAL of today's team containers below.
    // Overall summary recalculates all available history for the same teams/members.
    // Summary cards use the full dashboard scope, including users who can receive discipline records
    // but are not stored as regular team members (for example, CSR Manager accounts).
    $summaryToday = $allCsrIds ? calculateManagerOpsForScope($db, $allCsrIds, $startDate, $endDate) : emptyManagerOps();
    $summaryToday['agents'] = count($allCsrIds);

    $teamStatsOverall = [];
    foreach ($teams as $team) {
        $tid = (int)$team['id'];
        $ids = $teamMembers[$tid] ?? [];
        $overallStats = calculateManagerOpsForScope($db, $ids, $overallStartDate, $overallEndDate, $tid);
        $teamStatsOverall[] = array_merge([
            'team_id' => $tid,
            'team_name' => $team['team_name'],
            'agents' => count($ids),
        ], $overallStats);
    }
    $summaryOverall = $allCsrIds ? calculateManagerOpsForScope($db, $allCsrIds, $overallStartDate, $overallEndDate) : emptyManagerOps();
    $summaryOverall['agents'] = count($allCsrIds);

    // Backward-compatible name used by older JS.
    $summary = $summaryToday;

    // -------------------------------------------------------
    // 6. 10-day trend (daily totals across all managed agents)
    // -------------------------------------------------------
    $trend = [];
    if ($allCsrIds) {
        $trendDays = 10;
        for ($i = $trendDays - 1; $i >= 0; $i--) {
            $day = date('Y-m-d', strtotime("-{$i} days"));
            $dayStats = calculateManagerOpsForScope($db, $allCsrIds, $day, $day);
            $trend[] = [
                'date'            => $day,
                'label'           => date('n/j/y', strtotime($day)),
                'handled_today'   => $dayStats['handled_today'],
                'calls_scheduled' => $dayStats['calls_scheduled'],
                'completed_today' => $dayStats['completed_today'],
                'mistakes'        => $dayStats['mistakes'],
                // Legacy aliases
                'gh'              => $dayStats['handled_today'],
                'schedule'        => $dayStats['calls_scheduled'],
            ];
        }
    }

    // -------------------------------------------------------
    // 7. Task status distribution from service_requests
    // -------------------------------------------------------
    // Scope this to the same manager/team-leader IDs instead of showing global unrelated tickets.
    $taskStatus = [];
    try {
        if (mgrHasTable($db, 'job_statuses') && mgrHasTable($db, 'service_requests') && $allCsrIds) {
            $params = [];
            $ownerCond = serviceRequestOwnerCondition($db, $allCsrIds, 'ts_owner', $params);
            $joinExtra = $ownerCond !== '1=0' ? " AND $ownerCond" : '';
            $tsStmt = $db->prepare(
                "SELECT js.status_name, js.color_code, COUNT(sr.id) AS cnt
                   FROM job_statuses js
                   LEFT JOIN service_requests sr ON sr.job_status_id = js.id $joinExtra
                  GROUP BY js.id, js.status_name, js.color_code
                  ORDER BY cnt DESC"
            );
            $tsStmt->execute($params);
            $taskStatus = $tsStmt->fetchAll(PDO::FETCH_ASSOC);
        }
    } catch (Exception $e) {
        // table might not exist in all environments
    }

    // -------------------------------------------------------
    // 8. Recent warnings and mistakes from the actual discipline table
    // -------------------------------------------------------
    // Read directly from csr_discipline_records instead of rebuilding from today's stats.
    // This makes the dashboard show the real saved Warning/Mistake records across the manager scope.
    $disciplineBoards = managerDisciplineBoards($db, $allCsrIds, $teamMembers, $teams, 10);
    $warningRecords = $disciplineBoards['warning_records'];
    $mistakesBoard = $disciplineBoards['mistakes'];

    echo json_encode([
        'success'     => true,
        'role'        => $role,
        'summary'     => $summary,
        'summary_today' => $summaryToday,
        'summary_overall' => $summaryOverall,
        'teams'       => $teams,
        'team_stats'  => $teamStats,
        'team_stats_overall' => $teamStatsOverall,
        'trend'       => $trend,
        'task_status' => $taskStatus,
        'warning_records' => $warningRecords,
        'mistakes'    => $mistakesBoard,
        'start_date'  => $startDate,
        'end_date'    => $endDate,
    ]);

} catch (Exception $e) {
    echo json_encode(['success' => false, 'message' => $e->getMessage()]);
}
?>
