<?php
// admin/ajax/get_admin_manager_dashboard.php
// Admin-wide endpoint that reuses the CSR Manager dashboard metrics logic for all teams.
require_once '../auth.php';
ensureAdminAjaxAuth();
require_once '../../config/database.php';
header('Content-Type: application/json');

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);

    // Admin uses the exact CSR Manager dashboard data engine, but without limiting to one manager.
    $managerId = 0;
    $role = 'csr_manager';

    /**
     * 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 managerTopRegions(PDO $db, int $limit = 7): array {
        if (!mgrHasTable($db, 'service_requests')) return [];
        $parts = [];
        $joins = [];

        if (mgrHasColumn($db, 'service_requests', 'region')) {
            $parts[] = "NULLIF(TRIM(sr.region),'')";
        }
        if (mgrHasTable($db, 'service_areas') && mgrHasColumn($db, 'service_requests', 'zip_code')) {
            $joins[] = "LEFT JOIN service_areas sz ON sz.zip_code = sr.zip_code AND sz.is_active = 1";
            if (mgrHasColumn($db, 'service_areas', 'region')) $parts[] = "NULLIF(TRIM(sz.region),'')";
        }
        if (mgrHasTable($db, 'cities') && mgrHasColumn($db, 'service_requests', 'city_id')) {
            $joins[] = "LEFT JOIN cities c ON c.id = sr.city_id";
            if (mgrHasColumn($db, 'cities', 'region')) $parts[] = "NULLIF(TRIM(c.region),'')";
            if (mgrHasColumn($db, 'cities', 'city_name')) $parts[] = "NULLIF(TRIM(c.city_name),'')";
        }
        if (mgrHasTable($db, 'locations') && mgrHasColumn($db, 'service_requests', 'location_id')) {
            $joins[] = "LEFT JOIN locations l ON l.id = sr.location_id";
            if (mgrHasColumn($db, 'locations', 'location_name')) $parts[] = "NULLIF(TRIM(l.location_name),'')";
        }

        if (!$parts) return [];
        $expr = "COALESCE(" . implode(',', $parts) . ", 'Unknown')";
        try {
            $sql = "SELECT $expr AS region, COUNT(sr.id) AS cnt
                      FROM service_requests sr
                      " . implode("\n", array_unique($joins)) . "
                     GROUP BY region
                     HAVING region <> 'Unknown'
                     ORDER BY cnt DESC, region ASC
                     LIMIT " . (int)$limit;
            $stmt = $db->query($sql);
            return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (Throwable $e) { return []; }
    }

    function managerTopBrands(PDO $db, int $limit = 7): array {
        if (!mgrHasTable($db, 'service_requests')) return [];
        try {
            if (mgrHasTable($db, 'brands') && mgrHasColumn($db, 'service_requests', 'brand_id')) {
                $stmt = $db->query("SELECT COALESCE(NULLIF(TRIM(b.name),''),'Unknown') AS name, COUNT(sr.id) AS cnt
                                      FROM service_requests sr
                                      LEFT JOIN brands b ON b.id = sr.brand_id
                                     GROUP BY name
                                     HAVING name <> 'Unknown'
                                     ORDER BY cnt DESC, name ASC
                                     LIMIT " . (int)$limit);
                return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
            }
            foreach (['brand_name','brand'] as $col) {
                if (mgrHasColumn($db, 'service_requests', $col)) {
                    $stmt = $db->query("SELECT COALESCE(NULLIF(TRIM($col),''),'Unknown') AS name, COUNT(id) AS cnt
                                          FROM service_requests
                                         GROUP BY name
                                         HAVING name <> 'Unknown'
                                         ORDER BY cnt DESC, name ASC
                                         LIMIT " . (int)$limit);
                    return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
                }
            }
        } catch (Throwable $e) {}
        return [];
    }

    function managerTopAppliances(PDO $db, int $limit = 7): array {
        if (!mgrHasTable($db, 'service_requests')) return [];
        try {
            if (mgrHasTable($db, 'appliance_types') && mgrHasColumn($db, 'service_requests', 'appliance_type_id')) {
                $stmt = $db->query("SELECT COALESCE(NULLIF(TRIM(a.name),''),'Unknown') AS name, COUNT(sr.id) AS cnt
                                      FROM service_requests sr
                                      LEFT JOIN appliance_types a ON a.id = sr.appliance_type_id
                                     GROUP BY name
                                     HAVING name <> 'Unknown'
                                     ORDER BY cnt DESC, name ASC
                                     LIMIT " . (int)$limit);
                return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
            }
            foreach (['appliance_name','appliance_type','appliance'] as $col) {
                if (mgrHasColumn($db, 'service_requests', $col)) {
                    $stmt = $db->query("SELECT COALESCE(NULLIF(TRIM($col),''),'Unknown') AS name, COUNT(id) AS cnt
                                          FROM service_requests
                                         GROUP BY name
                                         HAVING name <> 'Unknown'
                                         ORDER BY cnt DESC, name ASC
                                         LIMIT " . (int)$limit);
                    return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
                }
            }
        } catch (Throwable $e) {}
        return [];
    }


    function managerTopCities(PDO $db, int $limit = 7): array {
        if (!mgrHasTable($db, 'service_requests')) return [];
        $cityParts = [];
        $regionParts = [];
        $joins = [];

        foreach (['city','city_name','service_city'] as $col) {
            if (mgrHasColumn($db, 'service_requests', $col)) $cityParts[] = "NULLIF(TRIM(sr.$col),'')";
        }
        if (mgrHasColumn($db, 'service_requests', 'region')) $regionParts[] = "NULLIF(TRIM(sr.region),'')";

        if (mgrHasTable($db, 'service_areas') && mgrHasColumn($db, 'service_requests', 'zip_code')) {
            $joins[] = "LEFT JOIN service_areas sz ON sz.zip_code = sr.zip_code";
            if (mgrHasColumn($db, 'service_areas', 'city')) $cityParts[] = "NULLIF(TRIM(sz.city),'')";
            if (mgrHasColumn($db, 'service_areas', 'city_name')) $cityParts[] = "NULLIF(TRIM(sz.city_name),'')";
            if (mgrHasColumn($db, 'service_areas', 'region')) $regionParts[] = "NULLIF(TRIM(sz.region),'')";
        }
        if (mgrHasTable($db, 'cities') && mgrHasColumn($db, 'service_requests', 'city_id')) {
            $joins[] = "LEFT JOIN cities c ON c.id = sr.city_id";
            if (mgrHasColumn($db, 'cities', 'city_name')) $cityParts[] = "NULLIF(TRIM(c.city_name),'')";
            if (mgrHasColumn($db, 'cities', 'name')) $cityParts[] = "NULLIF(TRIM(c.name),'')";
            if (mgrHasColumn($db, 'cities', 'region')) $regionParts[] = "NULLIF(TRIM(c.region),'')";
        }
        if (!$cityParts) return [];
        $cityExpr = "COALESCE(" . implode(',', $cityParts) . ", 'Unknown')";
        $regionExpr = $regionParts ? "COALESCE(" . implode(',', $regionParts) . ", '')" : "''";
        try {
            $sql = "SELECT $cityExpr AS city, $regionExpr AS region, COUNT(sr.id) AS cnt
                      FROM service_requests sr
                      " . implode("\n", array_unique($joins)) . "
                     GROUP BY city, region
                     HAVING city <> 'Unknown'
                     ORDER BY cnt DESC, city ASC
                     LIMIT " . (int)$limit;
            $stmt = $db->query($sql);
            return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (Throwable $e) { return []; }
    }

    function managerUrgencyDistribution(PDO $db): array {
        if (!mgrHasTable($db, 'service_requests')) return [];
        try {
            if (mgrHasColumn($db, 'service_requests', 'urgency_level_id')) {
                if (mgrHasTable($db, 'urgency_levels')) {
                    $labelParts = [];
                    foreach (['name','level_name','urgency_name','label','title'] as $ucol) {
                        if (mgrHasColumn($db, 'urgency_levels', $ucol)) $labelParts[] = "NULLIF(TRIM(ul.$ucol),'')";
                    }
                    $labelExpr = $labelParts ? "COALESCE(" . implode(',', $labelParts) . ", CONCAT('Level ', sr.urgency_level_id))" : "CONCAT('Level ', sr.urgency_level_id)";
                    $stmt = $db->query("SELECT $labelExpr AS label, COUNT(sr.id) AS cnt
                                          FROM service_requests sr
                                          LEFT JOIN urgency_levels ul ON ul.id = sr.urgency_level_id
                                         GROUP BY label
                                         ORDER BY cnt DESC, label ASC");
                    return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
                }
                $stmt = $db->query("SELECT CASE urgency_level_id WHEN 1 THEN 'Normal' WHEN 2 THEN 'Urgent' WHEN 3 THEN 'Emergency' ELSE CONCAT('Level ', urgency_level_id) END AS label,
                                           COUNT(id) AS cnt
                                      FROM service_requests
                                     GROUP BY label
                                     ORDER BY cnt DESC, label ASC");
                return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
            }
        } catch (Throwable $e) {}
        foreach (['urgency','urgency_level','priority','priority_level'] as $col) {
            if (mgrHasColumn($db, 'service_requests', $col)) {
                try {
                    $stmt = $db->query("SELECT COALESCE(NULLIF(TRIM($col),''),'Normal') AS label, COUNT(id) AS cnt
                                          FROM service_requests
                                         GROUP BY label
                                         ORDER BY cnt DESC, label ASC");
                    return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
                } catch (Throwable $e) { return []; }
            }
        }
        return [];
    }



    function managerTaskStatus(PDO $db): array {
        if (!mgrHasTable($db, 'service_requests') || !mgrHasColumn($db, 'service_requests', 'job_status_id')) {
            return [];
        }

        $defaultColors = [
            1 => '#38bdf8', // New
            2 => '#60a5fa', // Assigned
            3 => '#a78bfa', // In Progress
            4 => '#34d399', // Completed
            5 => '#fb7185', // Cancelled
        ];
        $defaultNames = [
            1 => 'New',
            2 => 'Assigned',
            3 => 'In Progress',
            4 => 'Completed',
            5 => 'Cancelled',
        ];

        try {
            if (mgrHasTable($db, 'job_statuses')) {
                $stmt = $db->query("SELECT
                                        COALESCE(js.id, sr.job_status_id) AS status_id,
                                        COALESCE(NULLIF(TRIM(js.status_name),''), CONCAT('Status ', sr.job_status_id)) AS status_name,
                                        COALESCE(NULLIF(TRIM(js.color_code),''), '#60a5fa') AS color_code,
                                        COUNT(sr.id) AS cnt
                                      FROM service_requests sr
                                      LEFT JOIN job_statuses js ON js.id = sr.job_status_id
                                     GROUP BY status_id, status_name, color_code
                                     ORDER BY status_id ASC");
                $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
            } else {
                $stmt = $db->query("SELECT job_status_id AS status_id, COUNT(id) AS cnt
                                      FROM service_requests
                                     GROUP BY job_status_id
                                     ORDER BY job_status_id ASC");
                $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
                foreach ($rows as &$row) {
                    $sid = (int)($row['status_id'] ?? 0);
                    $row['status_name'] = $defaultNames[$sid] ?? ('Status ' . $sid);
                    $row['color_code'] = $defaultColors[$sid] ?? '#60a5fa';
                }
                unset($row);
            }

            // Keep the chart useful even when the status table has no matching labels/colors.
            foreach ($rows as &$row) {
                $sid = (int)($row['status_id'] ?? 0);
                if (empty($row['status_name']) || stripos((string)$row['status_name'], 'Status ') === 0) {
                    $row['status_name'] = $defaultNames[$sid] ?? ($row['status_name'] ?: 'Unknown');
                }
                if (empty($row['color_code']) || (string)$row['color_code'] === '#60a5fa') {
                    $row['color_code'] = $defaultColors[$sid] ?? ($row['color_code'] ?: '#60a5fa');
                }
                $row['cnt'] = (int)($row['cnt'] ?? 0);
            }
            unset($row);
            return $rows;
        } catch (Throwable $e) {
            return [];
        }
    }

    function managerPeakHours(PDO $db, int $limit = 12): array {
        if (!mgrHasTable($db, 'service_requests')) return [];
        $dateExpr = managerDateExpr($db, 'service_requests', ['requested_at','created_at','date_created','last_updated_at']);
        try {
            $stmt = $db->query("SELECT LPAD(HOUR($dateExpr),2,'0') AS hour, COUNT(id) AS cnt
                                  FROM service_requests
                                 GROUP BY HOUR($dateExpr)
                                 ORDER BY HOUR($dateExpr) ASC
                                 LIMIT " . (int)$limit);
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
            foreach ($rows as &$r) {
                $h = (int)($r['hour'] ?? 0);
                $ampm = $h >= 12 ? 'PM' : 'AM';
                $h12 = $h % 12; if ($h12 === 0) $h12 = 12;
                $r['label'] = $h12 . ' ' . $ampm;
            }
            return $rows;
        } catch (Throwable $e) { return []; }
    }

    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
                  FROM csr_discipline_records dr
                  JOIN csr_users cu ON cu.id = dr.recipient_id
                 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
                 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),
                '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
    // -------------------------------------------------------
    $teamStmt = $db->query("SELECT id, team_name, manager_id FROM csr_teams WHERE is_active = 1 ORDER BY team_name");
    $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' => [],
            'task_status'   => [],
            'top_regions'   => [],
            'top_cities'    => [],
            'top_brands'    => [],
            'top_appliances'=> [],
            'urgency_distribution' => [],
            'peak_hours' => [],
            '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 = [];

    // Admin dashboard must also include CSR Managers, because Admin can send warnings/mistakes to them.
    try {
        $managerRows = $db->query("SELECT id FROM csr_users WHERE is_active = 1 AND COALESCE(role,'csr') = 'csr_manager'")->fetchAll(PDO::FETCH_COLUMN);
        foreach ($managerRows ?: [] as $mid) { $allCsrIds[] = (int)$mid; }
        $allCsrIds = array_values(array_unique(array_filter(array_map('intval', $allCsrIds))));
    } catch (Throwable $e) {}

    // 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;

    // -------------------------------------------------------
    // 8. Top CX usage charts: region, brands, and appliances
    // -------------------------------------------------------
    $taskStatus = managerTaskStatus($db);
    $topRegions = managerTopRegions($db);
    $topCities = managerTopCities($db);
    $topBrands = managerTopBrands($db);
    $topAppliances = managerTopAppliances($db);
    $urgencyDistribution = managerUrgencyDistribution($db);
    $peakHours = managerPeakHours($db);

    // -------------------------------------------------------
    // 9. 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 admin 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,
        'task_status' => $taskStatus,
        'top_regions' => $topRegions,
        'top_cities' => $topCities,
        'top_brands' => $topBrands,
        'top_appliances' => $topAppliances,
        'urgency_distribution' => $urgencyDistribution,
        'peak_hours' => $peakHours,
        'warning_records' => $warningRecords,
        'mistakes'    => $mistakesBoard,
        'start_date'  => $startDate,
        'end_date'    => $endDate,
    ]);

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