<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use Throwable;

/**
 * Phase 10.1.1 Hotfix
 * --------------------
 * The elite home page must never block the whole application with many heavy
 * legacy Oracle queries. By default this repository runs in FAST mode and
 * returns a complete, renderable command-center model without executing heavy
 * operational queries.
 *
 * To test live Oracle widgets later, set EXECUTIVE_DASHBOARD_MODE=live in .env.
 * The recommended production solution is Phase 10.2/20: summary tables or
 * materialized views, not direct aggregation from large transactional tables.
 */
final class ExecutiveRepository
{
    /** @var string */
    private $hospitalCode;

    /** @var array<string,bool> */
    private $tableCache = [];

    public function __construct(string $hospitalCode)
    {
        $this->hospitalCode = strtolower($hospitalCode);
    }

    /** @return array<string,mixed> */
    public function commandCenter(): array
    {
        $mode = strtolower((string) $this->env('EXECUTIVE_DASHBOARD_MODE', 'summary'));

        if ($mode === 'summary' || $mode === 'cache') {
            try {
                $summary = $this->summaryCommandCenter();
                if ($summary !== null) {
                    return $summary;
                }
                $data = $this->fastCommandCenter('summary-empty');
                $data['runtime_warning'] = 'Executive summary table has no row for today. Run executive-summary-refresh.php or schedule it.';
                return $data;
            } catch (Throwable $e) {
                $this->log('commandCenter.summaryFallback', $e);
                $data = $this->fastCommandCenter('summary-error');
                $data['runtime_warning'] = 'Summary layer could not be read. Run database/executive_kpi_summary_layer.sql and refresh the summary.';
                return $data;
            }
        }

        if ($mode !== 'live') {
            return $this->fastCommandCenter($mode);
        }

        try {
            return $this->liveCommandCenter();
        } catch (Throwable $e) {
            $this->log('commandCenter.liveFallback', $e);
            $data = $this->fastCommandCenter('fast-fallback');
            $data['runtime_warning'] = 'Live executive KPIs were skipped because Oracle query failed or timed out. Fast fallback is active.';
            return $data;
        }
    }

    /** @return array<string,mixed> */
    private function fastCommandCenter(string $mode): array
    {
        $now = date('M d, Y H:i');
        $today = date('Y-m-d');
        $year = (int) date('Y');

        return [
            'mode' => $mode,
            'is_fast_mode' => true,
            'last_updated' => $now,
            'today' => $today,
            'year' => $year,
            'operational' => [
                'operational_date' => $today,
                'window_start' => $today . ' 00:00',
                'window_end' => date('Y-m-d H:i', strtotime($today . ' +1 day')),
                'current_time' => date('Y-m-d H:i'),
                'start_hour' => (int)$this->env('EXECUTIVE_OPERATIONAL_DAY_START_HOUR', '7'),
                'is_rollover' => false,
            ],
            'flow' => [
                'opd' => 0,
                'er' => 0,
                'admissions' => 0,
                'discharges' => 0,
                'scheduled_operations' => 0,
                'performed_operations' => 0,
            ],
            'finance' => [
                'today_total' => 0.0,
                'month_total' => 0.0,
                'cash' => 0.0,
                'insurance' => 0.0,
                'moh' => 0.0,
                'net' => 0.0,
                'trend' => [],
            ],
            'capacity' => [
                'occupancy_rate' => null,
                'total_beds' => 0,
                'occupied_beds' => 0,
                'available_beds' => 0,
                'blocked_beds' => 0,
                'expected_discharges' => 0,
                'units' => [],
            ],
            'clinical' => [
                'active_doctors' => 0,
                'top_doctor' => null,
                'top_specialties' => [],
                'op_mix' => 0,
                'ip_mix' => 0,
            ],
            'trends' => [
                'visits' => [],
                'revenue' => [],
            ],
            'alerts' => [
                [
                    'severity' => 'medium',
                    'title' => 'Fast mode active',
                    'text' => 'Live home KPIs are paused to prevent heavy Oracle queries from blocking the dashboard.',
                ],
                [
                    'severity' => 'low',
                    'title' => 'Performance protection',
                    'text' => 'Use module pages for detailed live data, or enable summary tables for the command center.',
                ],
            ],
            'actions' => [
                ['title' => 'Open Revenue Dashboard', 'text' => 'Use the optimized revenue page for detailed financial figures.', 'tone' => 'purple'],
                ['title' => 'Open Visits Workspace', 'text' => 'Use the visits page for operational visit analytics.', 'tone' => 'teal'],
                ['title' => 'Build KPI Summary Layer', 'text' => 'Create daily/monthly summary tables before enabling live home KPIs.', 'tone' => 'blue'],
            ],
            'status' => [
                'status' => 'FAST MODE',
                'tone' => 'medium',
                'text' => 'Home page protected from slow Oracle aggregations',
            ],
        ];
    }


    /** @return array<string,mixed>|null */
    private function summaryCommandCenter()
    {
        $summaryRepo = new ExecutiveSummaryRepository($this->hospitalCode);
        $summary = $summaryRepo->latestForToday();
        if (!$summary || empty($summary['rows']) || !is_array($summary['rows'])) {
            return null;
        }

        $rows = $summary['rows'];
        $operational = is_array($summary['operational'] ?? null) ? $summary['operational'] : [];
        $n = function (string $code) use ($rows) {
            $code = strtolower($code);
            if (!isset($rows[$code])) { return null; }
            $value = $rows[$code]['kpi_value'] ?? null;
            return $value === null || $value === '' ? null : (float)$value;
        };
        $t = function (string $code) use ($rows): string {
            $code = strtolower($code);
            return isset($rows[$code]) ? trim((string)($rows[$code]['kpi_text'] ?? '')) : '';
        };

        $flow = [
            'opd' => $n('opd'),
            'er' => $n('er'),
            'admissions' => $n('admissions'),
            'discharges' => $n('discharges'),
            'scheduled_operations' => $n('scheduled_operations'),
            'performed_operations' => $n('performed_operations'),
        ];

        $finance = [
            'today_total' => $n('today_total'),
            'month_total' => $n('month_total'),
            'cash' => $n('cash'),
            'insurance' => $n('insurance'),
            'moh' => $n('moh'),
            'net' => $n('net'),
            'trend' => [],
        ];

        $units = [];
        foreach (['unit_icu', 'unit_medical', 'unit_surgical'] as $unitCode) {
            $units[] = [
                'unit_label' => $t($unitCode) ?: str_replace('unit_', '', $unitCode),
                'occupancy_pct' => $n($unitCode),
            ];
        }

        $capacity = [
            'occupancy_rate' => $n('occupancy_rate'),
            'total_beds' => $n('total_beds'),
            'occupied_beds' => $n('occupied_beds'),
            'available_beds' => $n('available_beds'),
            'blocked_beds' => $n('blocked_beds'),
            'expected_discharges' => $n('expected_discharges') ?? 0,
            'units' => $units,
        ];

        $topSpecialties = [];
        for ($i = 1; $i <= 5; $i++) {
            $label = $t('top_specialty_' . $i);
            $value = $n('top_specialty_' . $i);
            if ($label !== '') {
                $topSpecialties[] = ['label' => $label, 'value' => $value ?? 0];
            }
        }

        $topDoctorLabel = $t('top_doctor');
        $clinical = [
            'active_doctors' => $n('active_doctors'),
            'top_doctor' => $topDoctorLabel !== '' ? ['label' => $topDoctorLabel, 'value' => $n('top_doctor') ?? 0] : null,
            'top_specialties' => $topSpecialties,
            'op_mix' => 0,
            'ip_mix' => 0,
        ];

        $alerts = [];
        if (!empty($summary['is_stale'])) {
            $alerts[] = [
                'severity' => 'medium',
                'title' => 'Summary data is stale',
                'text' => 'The executive summary has not been refreshed recently. Run the summary refresh job.',
            ];
        }
        if (($capacity['occupancy_rate'] ?? 0) >= 90) {
            $alerts[] = [
                'severity' => 'high',
                'title' => 'High occupancy',
                'text' => 'Hospital occupancy is above the executive threshold.',
            ];
        }
        if (!$alerts) {
            $alerts[] = [
                'severity' => 'low',
                'title' => 'Summary layer active',
                'text' => 'Home KPIs are being read from the fast executive summary table.',
            ];
        }

        $status = [
            'status' => !empty($summary['is_stale']) ? 'STALE' : 'STABLE',
            'tone' => !empty($summary['is_stale']) ? 'medium' : 'good',
            'text' => !empty($summary['is_stale']) ? 'Summary refresh required' : 'Summary KPIs loaded from cache',
        ];

        return [
            'mode' => 'summary',
            'is_fast_mode' => false,
            'last_updated' => (string)($summary['last_updated'] ?? date('Y-m-d H:i')),
            'today' => (string)($operational['operational_date'] ?? date('Y-m-d')),
            'year' => (int)($n('doctor_year') ?: date('Y')),
            'operational' => $operational,
            'flow' => $flow,
            'finance' => $finance,
            'capacity' => $capacity,
            'clinical' => $clinical,
            'trends' => ['visits' => [], 'revenue' => []],
            'alerts' => $alerts,
            'actions' => [
                ['title' => 'Refresh Executive Summary', 'text' => 'Run the scheduled refresh job to update current KPIs.', 'tone' => 'teal'],
                ['title' => 'Open Revenue Dashboard', 'text' => 'Review detailed financial performance by month.', 'tone' => 'purple'],
                ['title' => 'Review Capacity', 'text' => 'Open Beds dashboard for detailed capacity analysis.', 'tone' => 'blue'],
            ],
            'status' => $status,
        ];
    }

    /** @return array<string,mixed> */
    private function liveCommandCenter(): array
    {
        $today = date('Y-m-d');
        $year = (int) date('Y');

        // Important: compute each expensive part once only. The previous build
        // called capacitySnapshot() multiple times through status + alerts.
        $flow = $this->todayFlowLight();
        $finance = $this->financeSnapshotLight();
        $capacity = $this->capacitySnapshotLight();
        $clinical = $this->clinicalSnapshotLight($year);
        $trends = ['visits' => [], 'revenue' => []];
        $alerts = $this->alertsFromCapacity($capacity);
        $status = $this->statusFromCapacity($capacity);

        return [
            'mode' => 'live',
            'is_fast_mode' => false,
            'last_updated' => date('M d, Y H:i'),
            'flow' => $flow,
            'finance' => $finance,
            'capacity' => $capacity,
            'clinical' => $clinical,
            'trends' => $trends,
            'alerts' => $alerts,
            'actions' => [
                ['title' => 'Review Discharge Queue', 'text' => 'Check expected discharges and bed readiness.', 'tone' => 'teal'],
                ['title' => 'Capacity Review', 'text' => 'Review high utilization units and blocked beds.', 'tone' => 'purple'],
                ['title' => 'Revenue Snapshot', 'text' => 'Open Revenue Dashboard for monthly trend.', 'tone' => 'blue'],
            ],
            'status' => $status,
            'year' => $year,
            'today' => $today,
            'operational' => [
                'operational_date' => $today,
                'window_start' => $today . ' 00:00',
                'window_end' => date('Y-m-d H:i', strtotime($today . ' +1 day')),
                'current_time' => date('Y-m-d H:i'),
                'start_hour' => (int)$this->env('EXECUTIVE_OPERATIONAL_DAY_START_HOUR', '7'),
                'is_rollover' => false,
            ],
        ];
    }

    /** @return array<string,int> */
    private function todayFlowLight(): array
    {
        // Keep Phase 10.1 live mode intentionally light. Avoid RCP_SERVICE_RESERVATION
        // joins here until a summary view exists.
        return [
            'opd' => 0,
            'er' => 0,
            'admissions' => $this->safeAdmissionsTodayLight(),
            'discharges' => 0,
            'scheduled_operations' => 0,
            'performed_operations' => 0,
        ];
    }

    private function safeAdmissionsTodayLight(): int
    {
        if (!$this->tableExists('RCP_VISIT')) { return 0; }
        try {
            // Range predicate, not TRUNC(column), so Oracle has a better chance
            // to use a date index if available.
            $sql = "
                SELECT COUNT(1) value
                FROM rcp_visit v
                WHERE v.visit_start_date >= TRUNC(SYSDATE)
                  AND v.visit_start_date < TRUNC(SYSDATE) + 1
                  AND ROWNUM <= 1000000
            ";
            $row = Database::fetchOne($this->hospitalCode, $sql, []);
            return (int)($row['value'] ?? 0);
        } catch (Throwable $e) {
            $this->log('safeAdmissionsTodayLight', $e);
            return 0;
        }
    }

    /** @return array<string,mixed> */
    private function financeSnapshotLight(): array
    {
        $empty = ['today_total' => 0.0, 'month_total' => 0.0, 'cash' => 0.0, 'insurance' => 0.0, 'moh' => 0.0, 'net' => 0.0, 'trend' => []];
        if (!$this->tableExists('T_INP_OUTP_REVENUE_BATCH')) { return $empty; }

        try {
            $mohCustomer = (string)\config('hospitals.' . $this->hospitalCode . '.moh_customer_code', '');
            $sql = "
                SELECT
                    NVL(SUM(CASE WHEN visit_end_date >= TRUNC(SYSDATE) AND visit_end_date < TRUNC(SYSDATE)+1 THEN NVL(credit_value,0)+NVL(cash_value,0) ELSE 0 END), 0) today_total,
                    NVL(SUM(NVL(credit_value,0)+NVL(cash_value,0)), 0) month_total,
                    NVL(SUM(NVL(cash_value,0)), 0) cash,
                    NVL(SUM(NVL(credit_value,0)), 0) insurance,
                    NVL(SUM(CASE WHEN :moh_customer IS NOT NULL AND customer_code = :moh_customer THEN NVL(credit_value,0)+NVL(cash_value,0) ELSE 0 END), 0) moh
                FROM t_inp_outp_revenue_batch
                WHERE yearid = :year_id
                  AND visit_end_date >= TRUNC(SYSDATE, 'MM')
                  AND visit_end_date < ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1)
            ";
            $row = Database::fetchOne($this->hospitalCode, $sql, [
                'year_id' => (int)date('Y'),
                'moh_customer' => $mohCustomer !== '' ? $mohCustomer : null,
            ]);
            if (!$row) { return $empty; }
            $row = array_change_key_case($row, CASE_LOWER);
            $cash = (float)($row['cash'] ?? 0);
            $insurance = (float)($row['insurance'] ?? 0);
            $moh = (float)($row['moh'] ?? 0);
            return [
                'today_total' => (float)($row['today_total'] ?? 0),
                'month_total' => (float)($row['month_total'] ?? 0),
                'cash' => $cash,
                'insurance' => $insurance,
                'moh' => $moh,
                'net' => max(0.0, $cash + $insurance - $moh),
                'trend' => [],
            ];
        } catch (Throwable $e) {
            $this->log('financeSnapshotLight', $e);
            return $empty;
        }
    }

    /** @return array<string,mixed> */
    private function capacitySnapshotLight(): array
    {
        // Do not call BedsRepository synchronously on the home page in Phase 10.1.
        // That page can perform detailed bed calculations separately.
        return ['occupancy_rate' => null, 'total_beds' => 0, 'occupied_beds' => 0, 'available_beds' => 0, 'blocked_beds' => 0, 'expected_discharges' => 0, 'units' => []];
    }

    /** @return array<string,mixed> */
    private function clinicalSnapshotLight(int $year): array
    {
        // Do not call DoctorsRepository synchronously on the home page in Phase 10.1.
        return ['active_doctors' => 0, 'top_doctor' => null, 'top_specialties' => [], 'op_mix' => 0, 'ip_mix' => 0];
    }

    /** @param array<string,mixed> $capacity @return array<int,array<string,mixed>> */
    private function alertsFromCapacity(array $capacity): array
    {
        $rate = $capacity['occupancy_rate'] ?? null;
        if ($rate !== null && (float)$rate >= 90) {
            return [['severity' => 'high', 'title' => 'Occupancy pressure', 'text' => 'Occupancy is above 90%. Review capacity and discharge queue.']];
        }
        return [['severity' => 'good', 'title' => 'No critical alerts', 'text' => 'No high severity alert detected from fast home data.']];
    }

    /** @param array<string,mixed> $capacity @return array<string,mixed> */
    private function statusFromCapacity(array $capacity): array
    {
        $rate = $capacity['occupancy_rate'] ?? null;
        if ($rate !== null && (float)$rate >= 90) { return ['status' => 'PRESSURE', 'tone' => 'high', 'text' => 'Operational attention needed']; }
        if ($rate !== null && (float)$rate >= 80) { return ['status' => 'WATCH', 'tone' => 'medium', 'text' => 'Monitor capacity and flow']; }
        return ['status' => 'STABLE', 'tone' => 'good', 'text' => 'All systems normal'];
    }

    private function tableExists(string $table): bool
    {
        $schema = strtoupper((string)\config('hospitals.' . $this->hospitalCode . '.database.schema', ''));
        $key = $schema . '.' . strtoupper($table);
        if (array_key_exists($key, $this->tableCache)) { return $this->tableCache[$key]; }

        try {
            $rows = Database::fetchAll($this->hospitalCode, "
                SELECT 1 ok FROM all_tables
                WHERE table_name = UPPER(:table_name)
                  AND (:owner_name IS NULL OR owner = UPPER(:owner_name))
                  AND ROWNUM <= 1
            ", ['table_name' => $table, 'owner_name' => $schema !== '' ? $schema : null]);
            return $this->tableCache[$key] = !empty($rows);
        } catch (Throwable $e) {
            $this->log('tableExists.' . $table, $e);
            return $this->tableCache[$key] = false;
        }
    }

    private function env(string $key, string $default): string
    {
        if (function_exists('env')) {
            $value = \env($key, $default);
            return is_scalar($value) ? (string)$value : $default;
        }
        $value = getenv($key);
        return $value === false ? $default : (string)$value;
    }

    private function log(string $context, Throwable $e): void
    {
        $file = \storage_path('logs/app.log');
        $dir = dirname($file);
        if (!is_dir($dir)) { @mkdir($dir, 0775, true); }
        @file_put_contents($file, '[' . date('c') . '] ExecutiveRepository::' . $context . ': ' . $e->getMessage() . PHP_EOL, FILE_APPEND);
    }
}
