<?php
declare(strict_types=1);

namespace App\Lib;

use Core\DB;

/**
 * Executive dashboard data — mirrors the legacy OrchView Executive layout.
 * Shared by HomeController (admin/exec/PM view) and ClientPortalController.
 */
final class DashboardStats
{
    /**
     * @param int         $tenantId
     * @param string|null $from  YYYY-MM-DD (inclusive). Null → 12 months before anchor.
     * @param string|null $to    YYYY-MM-DD (inclusive). Null → anchor (latest incident or today).
     * @return array<string,mixed>
     */
    public static function compute(int $tenantId, ?string $from = null, ?string $to = null): array
    {
        // Anchor: newest incident-date OR today. Lets a fresh install still land
        // on today's date and lets a fully-loaded system land on the latest event.
        $latestIncident = DB::value(
            'SELECT COALESCE(MAX(start_at), MAX(created_at))
               FROM incidents WHERE tenant_id = :t AND deleted_at IS NULL',
            ['t' => $tenantId]
        ) ?: date('Y-m-d H:i:s');
        $anchorDate = date('Y-m-d', strtotime((string) $latestIncident));

        // Window resolution — either use caller-provided range or default 12 mo.
        $toDate   = $to   !== null && $to   !== '' ? $to   : $anchorDate;
        $fromDate = $from !== null && $from !== '' ? $from : date('Y-m-d', strtotime($toDate . ' -12 month'));
        $toSql    = $toDate   . ' 23:59:59';
        $fromSql  = $fromDate . ' 00:00:00';

        // Sub-windows for KPI matrix. "Today" = the last day of the range so the
        // filter can also serve as a period picker (Today → today's row).
        $todayFrom = $toDate;
        $todayTo   = $toDate;
        $weekFrom  = date('Y-m-d', strtotime('monday this week', strtotime($toDate)));
        $monthFrom = date('Y-m-01', strtotime($toDate));
        $yearFrom  = date('Y-01-01', strtotime($toDate));

        $kpi = [
            'today' => self::incidentBucket($tenantId, $todayFrom, $todayTo),
            'week'  => self::incidentBucket($tenantId, $weekFrom,  $toDate),
            'month' => self::incidentBucket($tenantId, $monthFrom, $toDate),
        ];

        $dailyIncidents = DB::all(
            "SELECT DATE(start_at) AS d, COUNT(*) AS n
               FROM incidents
              WHERE tenant_id = :t AND deleted_at IS NULL
                AND start_at IS NOT NULL
                AND start_at BETWEEN :from AND :to
              GROUP BY d ORDER BY d",
            ['t' => $tenantId, 'from' => $fromSql, 'to' => $toSql]
        );

        $monthlyIncidents = DB::all(
            "SELECT DATE_FORMAT(start_at, '%Y-%m') AS m, COUNT(*) AS n
               FROM incidents
              WHERE tenant_id = :t AND deleted_at IS NULL
                AND start_at IS NOT NULL
                AND start_at BETWEEN :from AND :to
              GROUP BY m ORDER BY m",
            ['t' => $tenantId, 'from' => $fromSql, 'to' => $toSql]
        );

        $monthCategoryContribution = self::categoryContribution($tenantId, $monthFrom, $toDate);
        $yearCategoryContribution  = self::categoryContribution($tenantId, $yearFrom,  $toDate);

        $monthTop5 = self::topFailedEquipment($tenantId, $monthFrom, $toDate);
        $yearTop5  = self::topFailedEquipment($tenantId, $yearFrom,  $toDate);

        // Plant readings — grouped by parameter_group → parameter_key → months.
        $readings = [];
        $rRows = DB::all(
            "SELECT parameter_key, parameter_group,
                    DATE_FORMAT(taken_at, '%Y-%m') AS m,
                    ROUND(AVG(value_number), 2) AS v
               FROM plant_readings
              WHERE tenant_id = :t
                AND value_number IS NOT NULL
                AND taken_at BETWEEN :from AND :to
              GROUP BY parameter_key, parameter_group, m
              ORDER BY m, parameter_key",
            ['t' => $tenantId, 'from' => $fromSql, 'to' => $toSql]
        );
        foreach ($rRows as $r) {
            $readings[$r['parameter_group']][$r['parameter_key']][] = [
                'm' => $r['m'],
                'v' => (float) $r['v'],
            ];
        }

        // Monthly generator hours — from completed PM WOs (actual_hours).
        $genHours = DB::all(
            "SELECT DATE_FORMAT(completed_at, '%Y-%m') AS m,
                    MAX(actual_hours) AS hrs
               FROM work_orders
              WHERE tenant_id = :t
                AND wo_type = 'preventive'
                AND status  = 'completed'
                AND actual_hours IS NOT NULL
                AND completed_at IS NOT NULL
                AND completed_at BETWEEN :from AND :to
              GROUP BY m ORDER BY m",
            ['t' => $tenantId, 'from' => $fromSql, 'to' => $toSql]
        );

        // Reliability roll-up over the same window — MTBF / MTTR / top failure codes.
        $reliability = self::reliability($tenantId, $fromDate, $toDate);

        // Rounds compliance today — pulls the same computation the Rounds
        // index shows, so the dashboard tile matches what the PM sees when
        // they click through.
        $roundsDue = \App\Models\Round::dueTodayStatus($tenantId);
        $roundsToday = [
            'total'     => count($roundsDue),
            'overdue'   => 0,
            'never_run' => 0,
            'on_time'   => 0,
        ];
        foreach ($roundsDue as $r) { $roundsToday[$r['status']]++; }

        // HSE tile — training compliance snapshot for the dashboard. Same
        // roll-up the Training and HSE hub pages use.
        $hseTraining = \App\Models\Training::countsForTenant($tenantId);
        $hseSds      = \App\Models\Sds::countsForTenant($tenantId);

        return [
            'anchorDate'                => $anchorDate,
            'from'                      => $fromDate,
            'to'                        => $toDate,
            'kpi'                       => $kpi,
            'dailyIncidents'            => $dailyIncidents,
            'monthlyIncidents'          => $monthlyIncidents,
            'monthCategoryContribution' => $monthCategoryContribution,
            'yearCategoryContribution'  => $yearCategoryContribution,
            'monthTop5'                 => $monthTop5,
            'yearTop5'                  => $yearTop5,
            'readings'                  => $readings,
            'genHours'                  => $genHours,
            'reliability'               => $reliability,
            'roundsToday'               => $roundsToday,
            'hseTraining'               => $hseTraining,
            'hseSds'                    => $hseSds,
        ];
    }

    /**
     * MTBF ≈ window_hours ÷ equipment failures in window
     * MTTR = mean downtime_minutes on incidents in window
     * Availability = (window_hours − total_downtime_hours) / window_hours
     */
    private static function reliability(int $tenantId, string $from, string $to): array
    {
        $windowHours = max(1, (int) ((strtotime($to) - strtotime($from)) / 3600) + 24);

        $agg = DB::one(
            "SELECT COUNT(*) AS failures,
                    ROUND(AVG(downtime_minutes), 1) AS mttr_minutes,
                    COALESCE(SUM(downtime_minutes), 0) AS total_downtime_minutes
               FROM incidents
              WHERE tenant_id = :t AND deleted_at IS NULL
                AND category = 'equipment'
                AND start_at BETWEEN :from AND :to",
            ['t' => $tenantId, 'from' => $from . ' 00:00:00', 'to' => $to . ' 23:59:59']
        ) ?: ['failures' => 0, 'mttr_minutes' => null, 'total_downtime_minutes' => 0];

        $failures    = (int) $agg['failures'];
        $mtbfHours   = $failures > 0 ? round($windowHours / $failures, 1) : null;
        $downHours   = ((int) ($agg['total_downtime_minutes'] ?? 0)) / 60;
        $availability = round(max(0, ($windowHours - $downHours) / $windowHours) * 100, 2);

        $topCodes = DB::all(
            "SELECT fc.code, fc.mode, COUNT(*) AS n, ROUND(AVG(i.downtime_minutes), 1) AS avg_dt
               FROM incidents i
               JOIN failure_codes fc ON fc.failure_code_id = i.failure_code_id
              WHERE i.tenant_id = :t AND i.deleted_at IS NULL
                AND i.start_at BETWEEN :from AND :to
              GROUP BY fc.failure_code_id
              ORDER BY n DESC, avg_dt DESC
              LIMIT 5",
            ['t' => $tenantId, 'from' => $from . ' 00:00:00', 'to' => $to . ' 23:59:59']
        );

        return [
            'window_hours' => $windowHours,
            'failures'     => $failures,
            'mtbf_hours'   => $mtbfHours,
            'mttr_minutes' => $agg['mttr_minutes'] !== null ? (float) $agg['mttr_minutes'] : null,
            'availability' => $availability,
            'top_codes'    => $topCodes,
        ];
    }

    private static function incidentBucket(int $tenantId, string $from, string $to): array
    {
        $rows = DB::all(
            "SELECT category, COUNT(*) AS n
               FROM incidents
              WHERE tenant_id = :t AND deleted_at IS NULL
                AND start_at IS NOT NULL
                AND DATE(start_at) BETWEEN :from AND :to
              GROUP BY category",
            ['t' => $tenantId, 'from' => $from, 'to' => $to]
        );
        $out = ['total' => 0, 'equipment' => 0, 'external' => 0, 'health_safety' => 0];
        foreach ($rows as $r) {
            $out['total'] += (int) $r['n'];
            $out[$r['category']] = (int) $r['n'];
        }
        return $out;
    }

    private static function categoryContribution(int $tenantId, string $from, string $to): array
    {
        return DB::all(
            "SELECT category, COUNT(*) AS n
               FROM incidents
              WHERE tenant_id = :t AND deleted_at IS NULL
                AND start_at IS NOT NULL
                AND DATE(start_at) BETWEEN :from AND :to
              GROUP BY category
              ORDER BY n DESC",
            ['t' => $tenantId, 'from' => $from, 'to' => $to]
        );
    }

    private static function topFailedEquipment(int $tenantId, string $from, string $to): array
    {
        return DB::all(
            "SELECT a.name, COUNT(*) AS n
               FROM incidents i
               JOIN assets a ON a.asset_id = i.asset_id
              WHERE i.tenant_id = :t AND i.deleted_at IS NULL
                AND i.asset_id IS NOT NULL
                AND i.start_at IS NOT NULL
                AND DATE(i.start_at) BETWEEN :from AND :to
              GROUP BY a.asset_id, a.name
              ORDER BY n DESC
              LIMIT 5",
            ['t' => $tenantId, 'from' => $from, 'to' => $to]
        );
    }
}
