<?php
/**
 * DashboardData
 * Central query class for all role-based dashboards.
 *
 * Assumes session vars: $_SESSION['user_id'], $_SESSION['team_id']
 *
 * NOTE: The `notes` table is assumed to have columns:
 *   record_id, jobcard_id, user_id, reason, note, date_time
 *   where reason = 'DRILLING BIT' | 'RIEMING BIT'  and  note = bit serial number
 */
class DashboardData
{
    private $db;

    public function __construct($db)
    {
        $this->db = $db;
    }

    /* ═══════════════════════════════════════════════════
     *  SHARED — METERS THIS MONTH
     * ═══════════════════════════════════════════════════ */

    /**
     * Sum meters for a category prefix, optionally filtered to a single user.
     * $prefix = 'RIEMING' | 'DRILLING' | 'CASING' | 'BLASTING'
     */
    public function metersThisMonth(string $prefix, int $user_id = 0): int
    {
        $user_filter = $user_id ? "AND user_id = '$user_id'" : "";

        $res = $this->db->query("jobcard_timeline",
            "SELECT COALESCE(SUM(meters), 0) AS total
             FROM jobcard_timeline
             WHERE type LIKE '{$prefix}%'
             $user_filter
             AND MONTH(date_time) = MONTH(CURDATE())
             AND YEAR(date_time)  = YEAR(CURDATE())"
        );
        return (int) ($res->fetch_assoc()['total'] ?? 0);
    }

    /**
     * Returns CSV: RIEMING,DRILLING,CASING,BLASTING totals for this month.
     */
    public function metersThisMonthCSV(int $user_id = 0): string
    {
        $categories = ['RIEMING', 'DRILLING', 'CASING', 'BLASTING'];
        $values = [];
        foreach ($categories as $cat) {
            $values[] = $this->metersThisMonth($cat, $user_id);
        }
        return implode(',', $values);
    }

    /* ═══════════════════════════════════════════════════
     *  SHARED — BIT METERS
     * ═══════════════════════════════════════════════════ */

    /**
     * Meters per bit serial number.
     * $reason = 'DRILLING BIT' | 'RIEMING BIT'
     * $user_id = 0 means all users (admin); specific ID filters to driver.
     */
    public function bitMeters(string $reason, int $user_id = 0): array
    {
        $type_prefix  = ($reason === 'DRILLING BIT') ? 'DRILLING' : 'RIEMING';
        $user_filter  = $user_id ? "AND jt.user_id = '$user_id'" : "";

        $res = $this->db->query("notes",
            "SELECT n.note AS serial_number,
                    COALESCE(SUM(jt.meters), 0) AS total_meters
             FROM notes n
             JOIN jobcard_timeline jt
               ON jt.jobcard_id = n.jobcard_id
              AND jt.type LIKE '{$type_prefix}%'
              $user_filter
             WHERE n.reason = '$reason'
             GROUP BY n.note
             ORDER BY total_meters DESC"
        );

        $bits = [];
        while ($row = $res->fetch_assoc()) {
            $bits[] = [
                'serial'  => $row['serial_number'],
                'meters'  => (int) $row['total_meters']
            ];
        }
        return $bits;
    }

    /* ═══════════════════════════════════════════════════
     *  ADMIN — LAST TEAM ACTIONS
     * ═══════════════════════════════════════════════════ */

    /**
     * Returns the most recent jobcard_timeline entry per active team.
     */
    public function lastTeamActions(): array
    {
        $teams_res = $this->db->query("teams",
            "SELECT record_id, name FROM teams WHERE status = 'ACTIVE'"
        );

        $results = [];
        while ($team = $teams_res->fetch_assoc()) {
            // Get user IDs that belong to this team
            $users_res = $this->db->query("users",
                "SELECT record_id FROM users WHERE team_id = '{$team['record_id']}'"
            );

            $user_ids = [];
            while ($u = $users_res->fetch_assoc()) {
                $user_ids[] = (int) $u['record_id'];
            }

            if (empty($user_ids)) {
                $results[] = [
                    'team'       => $team['name'],
                    'type'       => 'No members',
                    'date_time'  => '-',
                    'jobcard_id' => '-'
                ];
                continue;
            }

            $ids_sql = implode(',', $user_ids);

            $last = $this->db->query("jobcard_timeline",
                "SELECT type, date_time, jobcard_id
                 FROM jobcard_timeline
                 WHERE user_id IN ($ids_sql)
                 ORDER BY record_id DESC
                 LIMIT 1"
            );

            $row = $last->fetch_assoc();
            $results[] = [
                'team'       => $team['name'],
                'type'       => $row['type']       ?? 'No activity',
                'date_time'  => $row['date_time']  ?? '-',
                'jobcard_id' => $row['jobcard_id'] ?? '-'
            ];
        }
        return $results;
    }

    /* ═══════════════════════════════════════════════════
     *  DRIVER — JOBCARDS
     * ═══════════════════════════════════════════════════ */

    /**
     * Returns jobcards for a team.
     * $status = 'active' (status != 1) | 'completed' (status = 1)
     */
    public function teamJobcards(int $team_id, string $status = 'active'): array
    {
        $where = ($status === 'completed') ? "status = '1'" : "status != '1'";

        $res = $this->db->query("jobcards",
            "SELECT record_id, jc_no, client_name, address,
                    date_created, jc_current_status, status
             FROM jobcards
             WHERE team_assigned_id = '$team_id'
             AND $where
             ORDER BY record_id DESC"
        );

        $cards = [];
        while ($row = $res->fetch_assoc()) {
            $cards[] = $row;
        }
        return $cards;
    }

    /* ═══════════════════════════════════════════════════
     *  DEV — ALL JOBCARDS
     * ═══════════════════════════════════════════════════ */

    /**
     * Returns all jobcards grouped by status value.
     */
    public function allJobcardsGrouped(): array
    {
        $res = $this->db->query("jobcards",
            "SELECT jc.record_id, jc.jc_no, jc.client_name, jc.address,
                    jc.date_created, jc.jc_current_status, jc.status,
                    t.name AS team_name
             FROM jobcards jc
             LEFT JOIN teams t ON t.record_id = jc.team_assigned_id
             ORDER BY jc.record_id DESC"
        );

        $groups = [
            'open'      => [],
            'active'    => [],
            'completed' => [],
            'other'     => []
        ];

        while ($row = $res->fetch_assoc()) {
            $s = strtolower(trim($row['status']));
            if ($s === '0' || $s === 'open')          $groups['open'][]      = $row;
            elseif ($s === '1' || $s === 'completed')  $groups['completed'][] = $row;
            elseif (in_array($s, ['active','in progress','inprogress','2']))
                                                        $groups['active'][]    = $row;
            else                                        $groups['other'][]     = $row;
        }
        return $groups;
    }

    /* ═══════════════════════════════════════════════════
     *  DISPATCH — STOCK
     * ═══════════════════════════════════════════════════ */

    /**
     * Stock items where status <= 1 (critically low).
     */
    public function lowStock(): array
    {
        $res = $this->db->query("stock",
            "SELECT record_id, stock_no, item_name, type, size,
                    unit_of_measure, status
             FROM stock
             WHERE status <= 1
             ORDER BY status ASC, item_name ASC"
        );
        $items = [];
        while ($row = $res->fetch_assoc()) $items[] = $row;
        return $items;
    }

    /**
     * Stock transactions with status = 'BOOKED'.
     * ⚠️ Adjust 'BOOKED' to match your actual status value.
     */
    public function bookedStock(): array
    {
        $res = $this->db->query("stock_trans",
            "SELECT st.record_id, st.stock_no, st.item_name,
                    st.quantity, st.order_no,
                    st.datetime_created, st.status,
                    s.type, s.size, s.unit_of_measure
             FROM stock_trans st
             LEFT JOIN stock s ON s.stock_no = st.stock_no
             WHERE st.status = 'BOOKED'
             ORDER BY st.datetime_created DESC"
        );
        $items = [];
        while ($row = $res->fetch_assoc()) $items[] = $row;
        return $items;
    }

    /* ═══════════════════════════════════════════════════
     *  DRIVER — TIME SPENT PER ACTIVITY
     * ═══════════════════════════════════════════════════ */

    /**
     * Calculates total hours spent on each activity for a user by
     * pairing START/STOP events in jobcard_timeline.
     *
     * Returns:
     * [
     *   'RIEMING'  => ['seconds' => 3600, 'hours' => 1.0,  'label' => '1h 00m'],
     *   'DRILLING' => [...],
     *   'CASING'   => [...],
     *   'BLASTING' => [...],
     * ]
     */
    public function timeSpentByActivity(int $user_id): array
    {
        $activities = [
            'RIEMING'  => ['start' => ['RIEMING_START',  'RIEM_START'],  'stop' => ['RIEMING_STOP',  'RIEM_STOP']],
            'DRILLING' => ['start' => ['DRILLING_START'],                 'stop' => ['DRILLING_STOP']],
            'CASING'   => ['start' => ['CASING_START'],                   'stop' => ['CASING_STOP']],
            'BLASTING' => ['start' => ['BLASTING_START'],                 'stop' => ['BLASTING_STOP']],
        ];

        // Fetch all timeline rows for this user, ordered by record_id
        $res = $this->db->query("jobcard_timeline",
            "SELECT jobcard_id, type, date_time
             FROM jobcard_timeline
             WHERE user_id = '$user_id'
             ORDER BY record_id ASC"
        );

        // Group rows by jobcard
        $by_jobcard = [];
        while ($row = $res->fetch_assoc()) {
            $by_jobcard[$row['jobcard_id']][] = $row;
        }

        // Accumulate seconds per activity
        $totals = ['RIEMING' => 0, 'DRILLING' => 0, 'CASING' => 0, 'BLASTING' => 0];

        foreach ($by_jobcard as $rows) {
            foreach ($activities as $name => $events) {
                $start_ts = null;
                foreach ($rows as $row) {
                    $type = strtoupper(trim($row['type']));
                    if (in_array($type, $events['start'])) {
                        $start_ts = strtotime($row['date_time']);
                    } elseif (in_array($type, $events['stop'])) {
                        if ($start_ts !== null) {
                            $diff = strtotime($row['date_time']) - $start_ts;
                            if ($diff > 0) $totals[$name] += $diff;
                            $start_ts = null;
                        }
                    }
                }
            }
        }

        // Format results
        $result = [];
        foreach ($totals as $name => $seconds) {
            $h = floor($seconds / 3600);
            $m = floor(($seconds % 3600) / 60);
            $result[$name] = [
                'seconds' => $seconds,
                'hours'   => round($seconds / 3600, 2),
                'label'   => "{$h}h " . str_pad($m, 2, '0', STR_PAD_LEFT) . "m",
            ];
        }
        return $result;
    }

    /* ═══════════════════════════════════════════════════
     *  INSTALL TEAM — CABLE & HDPE METERS THIS MONTH
     * ═══════════════════════════════════════════════════ */

    /**
     * Totals cable_meters and hdpe_meters from pump_installation
     * for the current month, filtered by team.
     * Returns: ['cable' => int, 'hdpe' => int]
     */
    public function installMetersThisMonth(int $team_id): array
    {
        $team_filter = $team_id ? "AND drilling_team_id = '$team_id'" : "";

        $res = $this->db->query("pump_installation",
            "SELECT
                COALESCE(SUM(cable_meters), 0) AS cable,
                COALESCE(SUM(hdpe_meters),  0) AS hdpe
             FROM pump_installation
             WHERE MONTH(date_time_created) = MONTH(CURDATE())
             AND YEAR(date_time_created)  = YEAR(CURDATE())
             $team_filter"
        );
        $row = $res->fetch_assoc();
        return [
            'cable' => (int) ($row['cable'] ?? 0),
            'hdpe'  => (int) ($row['hdpe']  ?? 0),
        ];
    }

    /* ═══════════════════════════════════════════════════
     *  ADMIN — ALL REPAIRS
     * ═══════════════════════════════════════════════════ */
    public function allRepairs(): array
    {
        $res = $this->db->query("pump_repair",
            "SELECT pr.record_id, pr.jobcard_no, pr.repaired_by,
                    pr.problem_description, pr.problem_solution,
                    pr.client_type, pr.contact_number, pr.address,
                    pr.jc_current_status, pr.status,
                    pr.date_time_created, pr.date_time_finished,
                    t.name AS team_name
             FROM pump_repair pr
             LEFT JOIN teams t ON t.record_id = pr.drilling_team_id
             ORDER BY pr.record_id DESC"
        );
        $rows = [];
        while ($row = $res->fetch_assoc()) $rows[] = $row;
        return $rows;
    }

    /* ═══════════════════════════════════════════════════
     *  ADMIN — ALL JOBCARDS
     * ═══════════════════════════════════════════════════ */
    public function allJobcards(): array
    {
        $res = $this->db->query("jobcards",
            "SELECT j.record_id, j.jc_no, j.client_name, j.address,
                    j.date_created, j.date_time_closed,
                    j.jc_current_status, j.status,
                    j.water_strike, j.water_flow,
                    j.compressor_hours,
                    t.name AS team_name
             FROM jobcards j
             LEFT JOIN teams t ON t.record_id = j.team_assigned_id
             ORDER BY j.record_id DESC"
        );
        $rows = [];
        while ($row = $res->fetch_assoc()) $rows[] = $row;
        return $rows;
    }

    /**
     * Returns all pump_installation records across all teams,
     * with team name joined in. Used by admin install tab.
     */
    public function allInstallations(): array
    {
        $res = $this->db->query("pump_installation",
            "SELECT pi.record_id, pi.jobcard_no, pi.client_name, pi.area,
                    pi.contact_number, pi.installed_by, pi.attended_by,
                    pi.pump_depth, pi.cable_meters, pi.hdpe_meters,
                    pi.meters_drilled, pi.borehole_meters,
                    pi.pack_no,
                    pi.jc_current_status, pi.status,
                    pi.date_time_created, pi.date_time_finished,
                    pi.date_attended,
                    t.name AS team_name
             FROM pump_installation pi
             LEFT JOIN teams t ON t.record_id = pi.drilling_team_id
             ORDER BY pi.record_id DESC"
        );
        $rows = [];
        while ($row = $res->fetch_assoc()) $rows[] = $row;
        return $rows;
    }

    /**
     * Returns open pump_installation jobs for the team (status = 0).
     */
    public function activeInstallations(int $team_id): array
    {
        $team_filter = $team_id ? "AND drilling_team_id = '$team_id'" : "";

        $res = $this->db->query("pump_installation",
            "SELECT record_id, jobcard_no, client_name, area,
                    contact_number, installed_by, pump_depth,
                    cable_meters, hdpe_meters, jc_current_status,
                    date_time_created, pack_no
             FROM pump_installation
             WHERE status = 0
             $team_filter
             ORDER BY date_time_created DESC"
        );
        $rows = [];
        while ($row = $res->fetch_assoc()) $rows[] = $row;
        return $rows;
    }

    /* ═══════════════════════════════════════════════════
     *  INSTALL TEAM — ACTIVE REPAIRS
     * ═══════════════════════════════════════════════════ */

    /**
     * Returns open pump_repair jobs for the team (status = 0).
     */
    public function activeRepairs(int $team_id): array
    {
        $team_filter = $team_id ? "AND pr.drilling_team_id = '$team_id'" : "";

        $res = $this->db->query("pump_repair",
            "SELECT pr.record_id, pr.jobcard_no, pr.installation_id,
                    pr.repaired_by, pr.problem_description,
                    pr.client_type, pr.contact_number, pr.address,
                    pr.jc_current_status, pr.date_time_created,
                    pr.date_time_started,
                    t.name AS team_name
             FROM pump_repair pr
             LEFT JOIN teams t ON t.record_id = pr.drilling_team_id
             WHERE pr.status = 0
             $team_filter
             ORDER BY pr.date_time_created DESC"
        );
        $rows = [];
        while ($row = $res->fetch_assoc()) $rows[] = $row;
        return $rows;
    }

    /* ═══════════════════════════════════════════════════
     *  INSTALL TEAM — SUMMARY STATS
     * ═══════════════════════════════════════════════════ */

    /**
     * Returns summary stats for the install team:
     * completed_installs_month, completed_repairs_month, avg_pump_depth
     */
    public function installSummaryStats(int $team_id): array
    {
        $team_filter = $team_id ? "AND drilling_team_id = '$team_id'" : "";

        $inst = $this->db->query("pump_installation",
            "SELECT COUNT(*) AS total,
                    ROUND(AVG(pump_depth), 1) AS avg_depth
             FROM pump_installation
             WHERE status = 1
             AND MONTH(date_time_created) = MONTH(CURDATE())
             AND YEAR(date_time_created)  = YEAR(CURDATE())
             $team_filter"
        );
        $inst_row = $inst->fetch_assoc();

        $rep = $this->db->query("pump_repair",
            "SELECT COUNT(*) AS total
             FROM pump_repair
             WHERE status = 1
             AND MONTH(date_time_created) = MONTH(CURDATE())
             AND YEAR(date_time_created)  = YEAR(CURDATE())
             $team_filter"
        );
        $rep_row = $rep->fetch_assoc();

        return [
            'completed_installs' => (int)   ($inst_row['total']     ?? 0),
            'completed_repairs'  => (int)   ($rep_row['total']      ?? 0),
            'avg_pump_depth'     => (float) ($inst_row['avg_depth'] ?? 0),
        ];
    }

    /* ═══════════════════════════════════════════════════
     *  ADMIN — JOBCARD COUNTS PER TEAM
     * ═══════════════════════════════════════════════════ */

    /**
     * Returns open jobcard counts per team across all three types.
     * Each row: team_name, normal, install, repair, total
     */
    public function jobcardCountsPerTeam(): array
    {
        $teams_res = $this->db->query("teams",
            "SELECT record_id, name FROM teams WHERE status = 'ACTIVE' ORDER BY name ASC"
        );

        $results = [];
        while ($team = $teams_res->fetch_assoc()) {
            $tid = $team['record_id'];

            // Normal jobcards (open)
            $normal = $this->db->query("jobcards",
                "SELECT COUNT(*) AS cnt FROM jobcards
                 WHERE team_assigned_id = '$tid' AND status != '1'"
            );
            $normal_cnt = (int)($normal->fetch_assoc()['cnt'] ?? 0);

            // Normal completed
            $normal_done = $this->db->query("jobcards",
                "SELECT COUNT(*) AS cnt FROM jobcards
                 WHERE team_assigned_id = '$tid' AND status = '1'"
            );
            $normal_done_cnt = (int)($normal_done->fetch_assoc()['cnt'] ?? 0);

            // Install open
            $install = $this->db->query("pump_installation",
                "SELECT COUNT(*) AS cnt FROM pump_installation
                 WHERE drilling_team_id = '$tid' AND status = 0"
            );
            $install_cnt = (int)($install->fetch_assoc()['cnt'] ?? 0);

            // Install completed
            $install_done = $this->db->query("pump_installation",
                "SELECT COUNT(*) AS cnt FROM pump_installation
                 WHERE drilling_team_id = '$tid' AND status = 1"
            );
            $install_done_cnt = (int)($install_done->fetch_assoc()['cnt'] ?? 0);

            // Repair open
            $repair = $this->db->query("pump_repair",
                "SELECT COUNT(*) AS cnt FROM pump_repair
                 WHERE drilling_team_id = '$tid' AND status = 0"
            );
            $repair_cnt = (int)($repair->fetch_assoc()['cnt'] ?? 0);

            // Repair completed
            $repair_done = $this->db->query("pump_repair",
                "SELECT COUNT(*) AS cnt FROM pump_repair
                 WHERE drilling_team_id = '$tid' AND status = 1"
            );
            $repair_done_cnt = (int)($repair_done->fetch_assoc()['cnt'] ?? 0);

            $results[] = [
                'team'         => $team['name'],
                'team_id'      => $tid,
                'normal_open'  => $normal_cnt,
                'normal_done'  => $normal_done_cnt,
                'install_open' => $install_cnt,
                'install_done' => $install_done_cnt,
                'repair_open'  => $repair_cnt,
                'repair_done'  => $repair_done_cnt,
                'total_open'   => $normal_cnt + $install_cnt + $repair_cnt,
            ];
        }

        // Sort by most open first
        usort($results, fn($a, $b) => $b['total_open'] - $a['total_open']);
        return $results;
    }

    /* ═══════════════════════════════════════════════════
     *  ADMIN — OVERDUE JOBCARDS (5+ DAYS NO COMPLETION)
     * ═══════════════════════════════════════════════════ */

    /**
     * Returns all open jobcards (all types) created more than 5 days ago,
     * with the team name and days outstanding.
     */
    public function overdueJobcards(int $days = 5): array
    {
        $results = [];

        // Normal jobcards overdue — last status from jobcard_timeline
        $res = $this->db->query("jobcards",
            "SELECT j.record_id, j.jc_no, j.client_name, j.address,
                    j.date_created, j.action_date,
                    t.name AS team_name,
                    DATEDIFF(NOW(), STR_TO_DATE(j.date_created, '%Y-%m-%d')) AS days_open,
                    'Normal' AS job_type,
                    (
                        SELECT jt.type
                        FROM jobcard_timeline jt
                        WHERE jt.jobcard_id = j.record_id
                        ORDER BY jt.record_id DESC
                        LIMIT 1
                    ) AS last_status
             FROM jobcards j
             LEFT JOIN teams t ON t.record_id = j.team_assigned_id
             WHERE j.status != '1'
             AND DATEDIFF(NOW(), STR_TO_DATE(j.date_created, '%Y-%m-%d')) >= $days
             ORDER BY days_open DESC"
        );
        while ($r = $res->fetch_assoc()) $results[] = $r;

        // Install jobcards overdue — use jc_current_status (no timeline entries)
        $res = $this->db->query("pump_installation",
            "SELECT pi.record_id, pi.jobcard_no AS jc_no, pi.client_name,
                    pi.area AS address,
                    DATE(pi.date_time_created) AS date_created,
                    NULL AS action_date,
                    t.name AS team_name,
                    DATEDIFF(NOW(), DATE(pi.date_time_created)) AS days_open,
                    'Install' AS job_type,
                    pi.jc_current_status AS last_status
             FROM pump_installation pi
             LEFT JOIN teams t ON t.record_id = pi.drilling_team_id
             WHERE pi.status = 0
             AND DATEDIFF(NOW(), DATE(pi.date_time_created)) >= $days
             ORDER BY days_open DESC"
        );
        while ($r = $res->fetch_assoc()) $results[] = $r;

        // Repair jobcards overdue — use jc_current_status (no timeline entries)
        $res = $this->db->query("pump_repair",
            "SELECT pr.record_id, pr.jobcard_no AS jc_no, NULL AS client_name,
                    pr.address,
                    DATE(pr.date_time_created) AS date_created,
                    NULL AS action_date,
                    t.name AS team_name,
                    DATEDIFF(NOW(), DATE(pr.date_time_created)) AS days_open,
                    'Repair' AS job_type,
                    pr.jc_current_status AS last_status
             FROM pump_repair pr
             LEFT JOIN teams t ON t.record_id = pr.drilling_team_id
             WHERE pr.status = 0
             AND DATEDIFF(NOW(), DATE(pr.date_time_created)) >= $days
             ORDER BY days_open DESC"
        );
        while ($r = $res->fetch_assoc()) $results[] = $r;

        // Sort all by days_open descending
        usort($results, fn($a, $b) => (int)$b['days_open'] - (int)$a['days_open']);
        return $results;
    }

    /* ═══════════════════════════════════════════════════
     *  COMBINED — TODAY'S JOBCARD STATUSES
     * ═══════════════════════════════════════════════════ */
    public function todayJobcardStatuses(int $team_id): array
    {
        $team_filter = $team_id ? "AND team_assigned_id = '$team_id'" : "";
        $res = $this->db->query("jobcards",
            "SELECT record_id, jc_no, client_name, jc_current_status, status, action_date
             FROM jobcards
             WHERE status != '1'
             $team_filter
             ORDER BY record_id DESC"
        );
        $rows = [];
        while ($r = $res->fetch_assoc()) $rows[] = $r;
        return $rows;
    }

    /* ═══════════════════════════════════════════════════
     *  COMBINED — DIESEL USAGE THIS MONTH
     * ═══════════════════════════════════════════════════ */
    public function dieselThisMonth(int $team_id): array
    {
        $team_filter = $team_id ? "AND team_assigned_id = '$team_id'" : "";
        $res = $this->db->query("jobcards",
            "SELECT COALESCE(SUM(diesel_stop - diesel_start), 0) AS total_litres,
                    COUNT(*) AS jobs
             FROM jobcards
             WHERE status = '1'
             AND diesel_stop > diesel_start
             AND date_time_closed IS NOT NULL
             AND MONTH(date_time_closed) = MONTH(CURDATE())
             AND YEAR(date_time_closed)  = YEAR(CURDATE())
             $team_filter"
        );
        $row = $res->fetch_assoc();
        return [
            'litres' => (int)   ($row['total_litres'] ?? 0),
            'jobs'   => (int)   ($row['jobs']         ?? 0),
        ];
    }

    /* ═══════════════════════════════════════════════════
     *  COMBINED — COMPRESSOR HOURS THIS MONTH
     * ═══════════════════════════════════════════════════ */
    public function compressorHoursThisMonth(int $team_id): array
    {
        $team_filter = $team_id ? "AND team_assigned_id = '$team_id'" : "";
        $res = $this->db->query("jobcards",
            "SELECT COALESCE(SUM(compressor_hours), 0) AS total_hours,
                    COUNT(*) AS jobs
             FROM jobcards
             WHERE status = '1'
             AND compressor_hours > 0
             AND date_time_closed IS NOT NULL
             AND MONTH(date_time_closed) = MONTH(CURDATE())
             AND YEAR(date_time_closed)  = YEAR(CURDATE())
             $team_filter"
        );
        $row = $res->fetch_assoc();
        return [
            'hours' => (float) ($row['total_hours'] ?? 0),
            'jobs'  => (int)   ($row['jobs']        ?? 0),
        ];
    }

    /* ═══════════════════════════════════════════════════
     *  COMBINED — WATER STRIKE & FLOW (RECENT JOBS)
     * ═══════════════════════════════════════════════════ */
    public function recentWaterData(int $team_id, int $limit = 10): array
    {
        $team_filter = $team_id ? "AND team_assigned_id = '$team_id'" : "";
        $res = $this->db->query("jobcards",
            "SELECT jc_no, client_name, address,
                    water_strike, water_flow, date_time_closed
             FROM jobcards
             WHERE status = '1'
             AND (water_strike IS NOT NULL OR water_flow IS NOT NULL)
             $team_filter
             ORDER BY record_id DESC
             LIMIT $limit"
        );
        $rows = [];
        while ($r = $res->fetch_assoc()) $rows[] = $r;
        return $rows;
    }

    /* ═══════════════════════════════════════════════════
     *  COMBINED — UPCOMING SCHEDULED JOBCARDS
     * ═══════════════════════════════════════════════════ */
    public function upcomingJobcards(int $team_id, int $limit = 8): array
    {
        $team_filter = $team_id ? "AND team_assigned_id = '$team_id'" : "";
        $res = $this->db->query("jobcards",
            "SELECT record_id, jc_no, client_name, address,
                    action_date, jc_current_status
             FROM jobcards
             WHERE status != '1'
             AND action_date IS NOT NULL
             AND action_date != ''
             $team_filter
             ORDER BY action_date ASC
             LIMIT $limit"
        );
        $rows = [];
        while ($r = $res->fetch_assoc()) $rows[] = $r;
        return $rows;
    }

    /* ═══════════════════════════════════════════════════
     *  COMBINED — STOCK ASSIGNED TO TEAM
     * ═══════════════════════════════════════════════════ */
    public function teamStock(int $team_id): array
    {
        $team_filter = $team_id ? "WHERE s.teams_id = '$team_id'" : "WHERE s.teams_id IS NOT NULL";
        $res = $this->db->query("stock",
            "SELECT s.record_id, s.stock_no, s.item_name,
                    s.type, s.size, s.serial_number,
                    s.unit_of_measure, s.status AS qty,
                    st.name AS type_name
             FROM stock s
             LEFT JOIN stock_types st ON st.record_id = s.stock_type_id
             $team_filter
             ORDER BY s.item_name ASC"
        );
        $rows = [];
        while ($r = $res->fetch_assoc()) $rows[] = $r;
        return $rows;
    }
}