<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use Throwable;

/**
 * Phase 10.6.3
 * Production-ready review workflow storage for Executive Action Center actions.
 *
 * Preferred storage:
 *   Oracle tables DASH_EXEC_ACTION_REVIEW and DASH_EXEC_ACTION_REVIEW_AUDIT with 30-character-safe constraints and sequence DASH_EXEC_ACT_REV_AUD_S.
 *
 * Safe fallback:
 *   storage/action_reviews/<hospital>/<operational_date>.json
 *
 * Phase 10.6.3 adds transaction-safe Oracle save and per-action audit history readback.
 * Phase 10.6.4 completes the management workflow: controlled status transitions,
 * previous-day review context, lifetime audit visibility, and management summary data.
 */
final class ExecutiveActionReviewRepository
{
    /** @var string */
    private $hospitalCode;

    /** @var bool|null */
    private $oracleAvailable = null;

    /** @var string */
    private $lastStorageError = '';

    public function __construct(string $hospitalCode)
    {
        $this->hospitalCode = strtolower(trim($hospitalCode)) ?: 'njch';
    }

    /** @param array<int,array<string,mixed>> $actions @return array<int,array<string,mixed>> */
    public function applyToActions(array $actions, string $operationalDate): array
    {
        $reviews = $this->allForDate($operationalDate);
        $keys = [];
        foreach ($actions as $action) {
            if (!is_array($action)) { continue; }
            $key = $this->key((string)($action['rule_id'] ?? ''));
            if ($key !== '') { $keys[] = $key; }
        }
        $previousReviews = $this->previousReviewMap($keys, $operationalDate);

        foreach ($actions as $i => $action) {
            if (!is_array($action)) { continue; }
            $ruleId = (string)($action['rule_id'] ?? '');
            $key = $this->key($ruleId);
            $review = $reviews[$key] ?? null;
            if (!is_array($review)) {
                $review = $this->defaultReview($ruleId, $operationalDate);
            }
            $previous = $previousReviews[$key] ?? null;
            $actions[$i]['review'] = $review;
            $actions[$i]['review_status'] = (string)($review['status'] ?? 'open');
            $actions[$i]['previous_review'] = is_array($previous) ? $previous : null;
            $actions[$i]['review_history_available'] = is_array($previous) || (int)($review['review_count'] ?? 0) > 0;
        }
        return $actions;
    }

    /** @return array<string,array<string,mixed>> */
    public function allForDate(string $operationalDate): array
    {
        if ($this->shouldUseOracle()) {
            $rows = $this->readOracle($operationalDate);
            if (is_array($rows)) { return $rows; }
        }
        return $this->readJson($operationalDate);
    }

    /** @return array<string,mixed>|null */
    public function find(string $ruleId, string $operationalDate): ?array
    {
        $all = $this->allForDate($operationalDate);
        $key = $this->key($ruleId);
        return isset($all[$key]) && is_array($all[$key]) ? $all[$key] : null;
    }

    /** @param array<string,mixed> $action @return array<string,mixed> */
    public function save(string $ruleId, string $operationalDate, string $status, string $note, string $updatedBy, array $action = []): array
    {
        $status = $this->normalizeStatus($status);
        $note = $this->oracleText(trim($note), 1800);
        $updatedBy = $this->oracleText($updatedBy !== '' ? $updatedBy : 'system', 120);

        if ($this->shouldUseOracle()) {
            $saved = $this->saveOracle($ruleId, $operationalDate, $status, $note, $updatedBy, $action);
            if (is_array($saved)) { return $saved; }
            throw new \RuntimeException('Oracle review save failed: ' . ($this->lastStorageError !== '' ? $this->lastStorageError : 'unknown error'));
        }

        return $this->saveJson($ruleId, $operationalDate, $status, $note, $updatedBy, $action);
    }

    /** @param array<int,array<string,mixed>> $actions @return array<string,int> */
    public function summary(array $actions): array
    {
        $counts = ['open' => 0, 'reviewed' => 0, 'deferred' => 0];
        foreach ($actions as $action) {
            $review = is_array($action['review'] ?? null) ? $action['review'] : [];
            $status = $this->normalizeStatus((string)($review['status'] ?? 'open'));
            if (!isset($counts[$status])) { $status = 'open'; }
            $counts[$status]++;
        }
        return $counts;
    }

    /** @param array<int,array<string,mixed>> $actions @return array<string,mixed> */
    public function managementSummary(array $actions, string $operationalDate): array
    {
        $counts = $this->summary($actions);
        $total = count($actions);
        $reviewed = (int)($counts['reviewed'] ?? 0);
        $deferred = (int)($counts['deferred'] ?? 0);
        $open = (int)($counts['open'] ?? 0);
        $completion = $total > 0 ? (int)round(($reviewed / $total) * 100) : 0;
        $lastActivity = $this->latestAuditActivity();

        return [
            'phase' => '10.6.4',
            'total_actions' => $total,
            'open' => $open,
            'reviewed' => $reviewed,
            'deferred' => $deferred,
            'pending_attention' => $open + $deferred,
            'completion_percent' => $completion,
            'lifetime_audit_count' => $this->lifetimeAuditCount(),
            'last_activity' => $lastActivity,
            'operational_date' => $this->dateOnly($operationalDate),
            'history_scope' => 'all_operational_days',
        ];
    }

    /**
     * Phase 10.6.5.1 — Production Analytics Purity & QA Activity Exclusion.
     * Read-only analytics built from the current action worklist plus the immutable
     * Oracle/JSON audit trail. Automated QA actors are excluded from management
     * analytics by default while every raw audit row remains stored and available.
     *
     * @param array<int,array<string,mixed>> $actions
     * @return array<string,mixed>
     */
    public function managementDashboard(array $actions, string $operationalDate, int $days = 30, bool $includeQaActivity = false): array
    {
        $days = max(7, min(90, $days));
        $endDate = $this->dateOnly($operationalDate);
        $endTs = strtotime($endDate . ' 23:59:59');
        if ($endTs === false) { $endTs = time(); }
        $startDate = date('Y-m-d', strtotime('-' . ($days - 1) . ' days', $endTs));
        $rawAuditRows = $this->dashboardAuditRows($startDate, $endDate, 1000);
        $qaAuditRows = [];
        $managementAuditRows = [];
        $qaActors = [];
        foreach ($rawAuditRows as $auditRow) {
            if (!is_array($auditRow)) { continue; }
            if ($this->isQaAuditRow($auditRow)) {
                $qaAuditRows[] = $auditRow;
                $actor = trim((string)($auditRow['changed_by'] ?? ''));
                if ($actor !== '') { $qaActors[strtolower($actor)] = $actor; }
                if (!$includeQaActivity) { continue; }
            }
            $managementAuditRows[] = $auditRow;
        }
        $auditRows = $managementAuditRows;

        $statusCounts = $this->summary($actions);
        $totalActions = count($actions);
        $reviewedCurrent = (int)($statusCounts['reviewed'] ?? 0);
        $openCurrent = (int)($statusCounts['open'] ?? 0);
        $deferredCurrent = (int)($statusCounts['deferred'] ?? 0);
        $completion = $totalActions > 0 ? (int)round(($reviewedCurrent / $totalActions) * 100) : 0;

        $severityOrder = ['critical' => 1, 'watch' => 2, 'routine' => 3, 'opportunity' => 4];
        $severityMatrix = [];
        foreach (array_keys($severityOrder) as $severity) {
            $severityMatrix[$severity] = ['open' => 0, 'reviewed' => 0, 'deferred' => 0, 'total' => 0];
        }

        $actionMeta = [];
        $attentionQueue = [];
        $oldestPendingDays = 0;
        foreach ($actions as $action) {
            if (!is_array($action)) { continue; }
            $key = $this->key((string)($action['rule_id'] ?? ''));
            if ($key === '') { continue; }

            $review = is_array($action['review'] ?? null) ? $action['review'] : [];
            $previous = is_array($action['previous_review'] ?? null) ? $action['previous_review'] : [];
            $status = $this->normalizeStatus((string)($review['status'] ?? 'open'));
            $severity = strtolower(trim((string)($action['severity'] ?? 'routine')));
            if (!isset($severityOrder[$severity])) { $severity = 'routine'; }

            $severityMatrix[$severity][$status]++;
            $severityMatrix[$severity]['total']++;

            $displayReview = (int)($review['review_count'] ?? 0) > 0 ? $review : $previous;
            $referenceDate = (string)($displayReview['operational_date'] ?? '');
            if ($referenceDate === '' && !empty($displayReview['updated_at'])) {
                $referenceDate = substr((string)$displayReview['updated_at'], 0, 10);
            }
            if ($referenceDate === '') { $referenceDate = $endDate; }
            $referenceTs = strtotime($this->dateOnly($referenceDate));
            $waitingDays = $referenceTs !== false ? max(0, (int)floor(($endTs - $referenceTs) / 86400)) : 0;

            $meta = [
                'action_key' => $key,
                'title' => (string)($action['title'] ?? $key),
                'title_ar' => (string)($action['title_ar'] ?? ''),
                'severity' => $severity,
                'category' => (string)($action['category'] ?? ''),
                'metric' => (string)($action['metric'] ?? ''),
                'status' => $status,
                'review_count' => (int)($review['review_count'] ?? 0),
                'last_review_at' => (string)($displayReview['updated_at'] ?? ''),
                'last_review_by' => (string)($displayReview['updated_by'] ?? ''),
                'last_review_note' => (string)($displayReview['note'] ?? ''),
                'waiting_days' => $waitingDays,
                'current' => true,
            ];
            $actionMeta[$key] = $meta;

            if ($status !== 'reviewed') {
                $queueRow = $meta;
                $queueRow['_severity_rank'] = (int)$severityOrder[$severity];
                $attentionQueue[] = $queueRow;
                $oldestPendingDays = max($oldestPendingDays, $waitingDays);
            }
        }

        usort($attentionQueue, function (array $a, array $b): int {
            $rankA = (int)($a['_severity_rank'] ?? 99);
            $rankB = (int)($b['_severity_rank'] ?? 99);
            if ($rankA !== $rankB) { return $rankA <=> $rankB; }
            $daysA = (int)($a['waiting_days'] ?? 0);
            $daysB = (int)($b['waiting_days'] ?? 0);
            if ($daysA !== $daysB) { return $daysB <=> $daysA; }
            return strcmp((string)($a['title'] ?? ''), (string)($b['title'] ?? ''));
        });
        foreach ($attentionQueue as &$queueItem) { unset($queueItem['_severity_rank']); }
        unset($queueItem);
        $attentionQueue = array_slice($attentionQueue, 0, 10);

        $trend = [];
        for ($i = 0; $i < $days; $i++) {
            $date = date('Y-m-d', strtotime('+' . $i . ' days', strtotime($startDate)));
            $trend[$date] = [
                'date' => $date,
                'audit_count' => 0,
                'reviewed' => 0,
                'deferred' => 0,
                'reopened' => 0,
                'other' => 0,
            ];
        }

        $reviewers = [];
        $topActions = [];
        $recentActivity = [];
        $reviewedInWindow = 0;
        $deferredInWindow = 0;
        $reopenedInWindow = 0;
        $current7Start = date('Y-m-d', strtotime('-6 days', $endTs));
        $previous7Start = date('Y-m-d', strtotime('-13 days', $endTs));
        $previous7End = date('Y-m-d', strtotime('-7 days', $endTs));
        $activity7 = 0;
        $activityPrevious7 = 0;

        foreach ($auditRows as $row) {
            if (!is_array($row)) { continue; }
            $key = $this->key((string)($row['action_key'] ?? ''));
            $changedAt = (string)($row['changed_at'] ?? '');
            $rowDate = $changedAt !== '' ? substr($changedAt, 0, 10) : $this->dateOnly((string)($row['operational_date'] ?? $endDate));
            $oldStatusRaw = strtolower(trim((string)($row['old_status'] ?? '')));
            $newStatus = $this->normalizeStatus((string)($row['new_status'] ?? 'open'));

            if (isset($trend[$rowDate])) {
                $trend[$rowDate]['audit_count']++;
                if ($newStatus === 'reviewed') {
                    $trend[$rowDate]['reviewed']++;
                    $reviewedInWindow++;
                } elseif ($newStatus === 'deferred') {
                    $trend[$rowDate]['deferred']++;
                    $deferredInWindow++;
                } elseif ($newStatus === 'open' && $oldStatusRaw !== '' && $oldStatusRaw !== 'open') {
                    $trend[$rowDate]['reopened']++;
                    $reopenedInWindow++;
                } else {
                    $trend[$rowDate]['other']++;
                }
            }

            if ($rowDate >= $current7Start && $rowDate <= $endDate) { $activity7++; }
            if ($rowDate >= $previous7Start && $rowDate <= $previous7End) { $activityPrevious7++; }

            $reviewer = trim((string)($row['changed_by'] ?? ''));
            if ($reviewer === '') { $reviewer = 'system'; }
            if (!isset($reviewers[$reviewer])) {
                $reviewers[$reviewer] = ['reviewer' => $reviewer, 'activity_count' => 0, 'reviewed' => 0, 'deferred' => 0, 'reopened' => 0, 'last_activity' => ''];
            }
            $reviewers[$reviewer]['activity_count']++;
            if ($newStatus === 'reviewed') { $reviewers[$reviewer]['reviewed']++; }
            if ($newStatus === 'deferred') { $reviewers[$reviewer]['deferred']++; }
            if ($newStatus === 'open' && $oldStatusRaw !== '' && $oldStatusRaw !== 'open') { $reviewers[$reviewer]['reopened']++; }
            if ((string)$reviewers[$reviewer]['last_activity'] < $changedAt) { $reviewers[$reviewer]['last_activity'] = $changedAt; }

            $snapshotMeta = $this->dashboardSnapshotMeta((string)($row['action_snapshot'] ?? ''));
            $knownMeta = isset($actionMeta[$key]) && is_array($actionMeta[$key]) ? $actionMeta[$key] : [];
            $title = (string)($knownMeta['title'] ?? $snapshotMeta['title'] ?? $key);
            $titleAr = (string)($knownMeta['title_ar'] ?? $snapshotMeta['title_ar'] ?? '');
            $severity = (string)($knownMeta['severity'] ?? $snapshotMeta['severity'] ?? 'routine');
            $category = (string)($knownMeta['category'] ?? $snapshotMeta['category'] ?? '');

            if (!isset($topActions[$key])) {
                $topActions[$key] = [
                    'action_key' => $key,
                    'title' => $title,
                    'title_ar' => $titleAr,
                    'severity' => $severity,
                    'category' => $category,
                    'activity_count' => 0,
                    'reviewed' => 0,
                    'deferred' => 0,
                    'reopened' => 0,
                    'last_activity' => '',
                    'current_status' => (string)($knownMeta['status'] ?? ''),
                ];
            }
            $topActions[$key]['activity_count']++;
            if ($newStatus === 'reviewed') { $topActions[$key]['reviewed']++; }
            if ($newStatus === 'deferred') { $topActions[$key]['deferred']++; }
            if ($newStatus === 'open' && $oldStatusRaw !== '' && $oldStatusRaw !== 'open') { $topActions[$key]['reopened']++; }
            if ((string)$topActions[$key]['last_activity'] < $changedAt) { $topActions[$key]['last_activity'] = $changedAt; }

            if (count($recentActivity) < 12) {
                $recentActivity[] = [
                    'action_key' => $key,
                    'title' => $title,
                    'title_ar' => $titleAr,
                    'severity' => $severity,
                    'category' => $category,
                    'old_status' => $oldStatusRaw,
                    'new_status' => $newStatus,
                    'changed_by' => $reviewer,
                    'changed_at' => $changedAt,
                    'operational_date' => (string)($row['operational_date'] ?? ''),
                    'storage' => (string)($row['storage'] ?? $this->storageMode()),
                ];
            }
        }

        $reviewerRows = array_values($reviewers);
        usort($reviewerRows, function (array $a, array $b): int {
            $cmp = (int)($b['activity_count'] ?? 0) <=> (int)($a['activity_count'] ?? 0);
            if ($cmp !== 0) { return $cmp; }
            return strcmp((string)($b['last_activity'] ?? ''), (string)($a['last_activity'] ?? ''));
        });
        $reviewerRows = array_slice($reviewerRows, 0, 10);

        $topActionRows = array_values($topActions);
        usort($topActionRows, function (array $a, array $b): int {
            $cmp = (int)($b['activity_count'] ?? 0) <=> (int)($a['activity_count'] ?? 0);
            if ($cmp !== 0) { return $cmp; }
            return strcmp((string)($b['last_activity'] ?? ''), (string)($a['last_activity'] ?? ''));
        });
        $topActionRows = array_slice($topActionRows, 0, 10);

        $velocityTrendPercent = 0;
        if ($activityPrevious7 > 0) {
            $velocityTrendPercent = (int)round((($activity7 - $activityPrevious7) / $activityPrevious7) * 100);
        } elseif ($activity7 > 0) {
            $velocityTrendPercent = 100;
        }

        $criticalPending = 0;
        foreach ($actions as $action) {
            if (!is_array($action)) { continue; }
            $review = is_array($action['review'] ?? null) ? $action['review'] : [];
            $status = $this->normalizeStatus((string)($review['status'] ?? 'open'));
            if ((string)($action['severity'] ?? '') === 'critical' && $status !== 'reviewed') { $criticalPending++; }
        }
        $riskLevel = $criticalPending > 0 || $oldestPendingDays >= 7 ? 'high' : (($openCurrent + $deferredCurrent) > 0 ? 'watch' : 'good');

        return [
            'phase' => '10.6.5.1',
            'period_days' => $days,
            'period_start' => $startDate,
            'period_end' => $endDate,
            'storage' => $this->storageMode(),
            'current' => [
                'total_actions' => $totalActions,
                'open' => $openCurrent,
                'reviewed' => $reviewedCurrent,
                'deferred' => $deferredCurrent,
                'pending_attention' => $openCurrent + $deferredCurrent,
                'completion_percent' => $completion,
                'critical_pending' => $criticalPending,
                'oldest_pending_days' => $oldestPendingDays,
                'risk_level' => $riskLevel,
            ],
            'window' => [
                'audit_count' => count($auditRows),
                'raw_audit_count' => count($rawAuditRows),
                'excluded_qa_count' => $includeQaActivity ? 0 : count($qaAuditRows),
                'reviewed' => $reviewedInWindow,
                'deferred' => $deferredInWindow,
                'reopened' => $reopenedInWindow,
                'active_reviewers' => count($reviewers),
                'activity_7d' => $activity7,
                'activity_previous_7d' => $activityPrevious7,
                'velocity_trend_percent' => $velocityTrendPercent,
            ],
            'trend' => array_values($trend),
            'severity_matrix' => $severityMatrix,
            'attention_queue' => $attentionQueue,
            'top_actions' => $topActionRows,
            'reviewers' => $reviewerRows,
            'recent_activity' => $recentActivity,
            'purity' => [
                'mode' => $includeQaActivity ? 'technical_with_qa' : 'production_only',
                'include_qa_activity' => $includeQaActivity,
                'raw_audit_count' => count($rawAuditRows),
                'management_audit_count' => count($auditRows),
                'qa_rows_detected' => count($qaAuditRows),
                'excluded_qa_count' => $includeQaActivity ? 0 : count($qaAuditRows),
                'qa_actors_detected' => array_values($qaActors),
                'filter_version' => '10.6.5.1',
                'rows_deleted' => 0,
            ],
            'history_scope' => 'selected_window_plus_current_state',
        ];
    }

    /** @return array<int,string> */
    public function allowedTransitions(string $currentStatus): array
    {
        $currentStatus = $this->normalizeStatus($currentStatus);
        $map = [
            'open' => ['open', 'reviewed', 'deferred'],
            'reviewed' => ['reviewed', 'open'],
            'deferred' => ['deferred', 'reviewed', 'open'],
        ];
        return $map[$currentStatus] ?? $map['open'];
    }

    /** @return array<int,array<string,mixed>> */
    public function auditHistoryAllDates(string $ruleId, int $limit = 30): array
    {
        $limit = max(1, min(100, $limit));
        $key = $this->key($ruleId);

        if ($this->shouldUseOracle()) {
            try { return $this->readOracleAuditHistoryAllDates($key, $limit); }
            catch (Throwable $e) {
                $this->lastStorageError = $e->getMessage();
                $this->log('audit-history-all-dates-oracle', $e);
            }
        }

        return $this->readJsonAuditHistoryAllDates($key, $limit);
    }

    /** @return array<string,mixed>|null */
    public function latestReviewAcrossDates(string $ruleId, string $beforeDate = ''): ?array
    {
        $key = $this->key($ruleId);
        $date = $beforeDate !== '' ? $this->dateOnly($beforeDate) : date('Y-m-d', strtotime('+1 day'));
        $map = $this->previousReviewMap([$key], $date);
        return isset($map[$key]) && is_array($map[$key]) ? $map[$key] : null;
    }

    /** @return array<string,mixed>|null */
    public function historicalAction(string $ruleId): ?array
    {
        $key = $this->key($ruleId);
        if ($key === '') { return null; }

        if ($this->shouldUseOracle()) {
            try { return $this->findHistoricalOracleAction($key); }
            catch (Throwable $e) {
                $this->lastStorageError = $e->getMessage();
                $this->log('historical-action-oracle', $e);
            }
        }

        return $this->findHistoricalJsonAction($key);
    }

    /** @return array<int,array<string,mixed>> */
    public function auditHistory(string $ruleId, string $operationalDate, int $limit = 15): array
    {
        $limit = max(1, min(50, $limit));
        $key = $this->key($ruleId);

        if ($this->shouldUseOracle()) {
            try { return $this->readOracleAuditHistory($key, $operationalDate, $limit); }
            catch (Throwable $e) {
                $this->lastStorageError = $e->getMessage();
                $this->log('audit-history-oracle', $e);
            }
        }

        return $this->readJsonAuditHistory($key, $operationalDate, $limit);
    }

    /** @return array<string,mixed> */
    public function diagnostics(string $operationalDate): array
    {
        $mainExists = false;
        $auditExists = false;
        $oracleError = '';
        try {
            $mainExists = $this->tableExists($this->baseReviewTable());
            $auditExists = $this->tableExists($this->baseAuditTable());
        } catch (Throwable $e) {
            $oracleError = $e->getMessage();
        }

        $reviews = $this->allForDate($operationalDate);
        $auditRows = [];
        $auditCount = 0;
        if ($mainExists && $auditExists) {
            try { $auditRows = $this->latestAuditRows($operationalDate, 20); } catch (Throwable $e) { $oracleError = $e->getMessage(); }
            try { $auditCount = $this->auditCountForDate($operationalDate); } catch (Throwable $e) { $oracleError = $e->getMessage(); }
        }

        return [
            'phase' => '10.6.4',
            'preferred_storage' => 'oracle',
            'storage_mode' => $this->storageMode(),
            'main_table' => $this->qualifiedReviewTable(),
            'audit_table' => $this->qualifiedAuditTable(),
            'main_table_exists' => $mainExists,
            'audit_table_exists' => $auditExists,
            'json_fallback_path' => $this->file($operationalDate),
            'last_storage_error' => $oracleError !== '' ? $oracleError : $this->lastStorageError,
            'review_count_for_date' => count($reviews),
            'audit_count_for_date' => $auditCount,
            'latest_audit_rows' => $auditRows,
            'lifetime_audit_count' => $this->lifetimeAuditCount(),
            'last_audit_activity' => $this->latestAuditActivity(),
            'history_scope' => 'all_operational_days',
        ];
    }

    public function storageMode(): string
    {
        return $this->shouldUseOracle() ? 'oracle' : 'json_fallback';
    }

    private function shouldUseOracle(): bool
    {
        $forced = strtolower(trim((string)\env('EXEC_ACTION_REVIEW_STORAGE', 'oracle')));
        if ($forced === 'json' || $forced === 'file') { return false; }
        if ($this->oracleAvailable !== null) { return $this->oracleAvailable; }
        try {
            $this->oracleAvailable = $this->tableExists($this->baseReviewTable()) && $this->tableExists($this->baseAuditTable());
        } catch (Throwable $e) {
            $this->lastStorageError = $e->getMessage();
            $this->oracleAvailable = false;
        }
        return (bool)$this->oracleAvailable;
    }

    /** @return array<string,array<string,mixed>>|null */
    private function readOracle(string $operationalDate): ?array
    {
        try {
            $sql = "SELECT action_key, action_title, action_severity, action_category, action_metric,
                           status, review_notes, updated_by,
                           TO_CHAR(updated_at, 'YYYY-MM-DD HH24:MI:SS') AS updated_at,
                           TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI:SS') AS created_at,
                           review_count, action_snapshot
                      FROM " . $this->qualifiedReviewTable() . "
                     WHERE LOWER(hospital_code) = :p_hospital
                       AND TRUNC(operational_date) = TO_DATE(:p_operational_date, 'YYYY-MM-DD')";
            $rows = Database::fetchAll($this->hospitalCode, $sql, [
                'p_hospital' => $this->hospitalCode,
                'p_operational_date' => $this->dateOnly($operationalDate),
            ]);
            $out = [];
            foreach ($rows as $row) {
                $key = $this->key((string)($row['action_key'] ?? ''));
                if ($key === '') { continue; }
                $out[$key] = $this->rowToReview($row, $operationalDate);
            }
            return $out;
        } catch (Throwable $e) {
            $this->lastStorageError = $e->getMessage();
            $this->log('read-oracle', $e);
            return null;
        }
    }

    /** @param array<string,mixed> $action @return array<string,mixed>|null */
    private function saveOracle(string $ruleId, string $operationalDate, string $status, string $note, string $updatedBy, array $action): ?array
    {
        $key = $this->key($ruleId);
        $existing = $this->findOracle($key, $operationalDate);
        $fromStatus = is_array($existing) ? (string)($existing['status'] ?? 'open') : 'open';
        $this->ensureTransition($fromStatus, $status);
        $title = $this->oracleText((string)($action['title'] ?? ''), 480);
        $severity = $this->oracleText((string)($action['severity'] ?? ''), 30);
        $category = $this->oracleText((string)($action['category'] ?? ''), 70);
        $metric = $this->oracleText((string)($action['metric'] ?? ''), 100);
        $snapshot = $this->actionSnapshot($action);

        try {
            if (is_array($existing)) {
                $mainSql = "UPDATE " . $this->qualifiedReviewTable() . "
                           SET status = :p_status,
                               review_notes = :p_note,
                               updated_by = :p_updated_by,
                               updated_at = SYSDATE,
                               action_title = :p_title,
                               action_severity = :p_severity,
                               action_category = :p_category,
                               action_metric = :p_metric,
                               action_snapshot = :p_snapshot,
                               review_count = NVL(review_count,0) + 1
                         WHERE LOWER(hospital_code) = :p_hospital
                           AND TRUNC(operational_date) = TO_DATE(:p_operational_date, 'YYYY-MM-DD')
                           AND action_key = :p_action_key";
                $mainParams = [
                    'p_status' => $status,
                    'p_note' => $note,
                    'p_updated_by' => $updatedBy,
                    'p_title' => $title,
                    'p_severity' => $severity,
                    'p_category' => $category,
                    'p_metric' => $metric,
                    'p_snapshot' => $snapshot,
                    'p_hospital' => $this->hospitalCode,
                    'p_operational_date' => $this->dateOnly($operationalDate),
                    'p_action_key' => $key,
                ];
            } else {
                $mainSql = "INSERT INTO " . $this->qualifiedReviewTable() . "
                            (hospital_code, operational_date, action_key, status, review_notes, updated_by, updated_at, created_at,
                             action_title, action_severity, action_category, action_metric, action_snapshot, review_count)
                        VALUES
                            (:p_hospital, TO_DATE(:p_operational_date, 'YYYY-MM-DD'), :p_action_key, :p_status, :p_note, :p_updated_by, SYSDATE, SYSDATE,
                             :p_title, :p_severity, :p_category, :p_metric, :p_snapshot, 1)";
                $mainParams = [
                    'p_hospital' => $this->hospitalCode,
                    'p_operational_date' => $this->dateOnly($operationalDate),
                    'p_action_key' => $key,
                    'p_status' => $status,
                    'p_note' => $note,
                    'p_updated_by' => $updatedBy,
                    'p_title' => $title,
                    'p_severity' => $severity,
                    'p_category' => $category,
                    'p_metric' => $metric,
                    'p_snapshot' => $snapshot,
                ];
            }

            $audit = $this->oracleAuditStatement($key, $operationalDate, $existing, $status, $note, $updatedBy, $action);
            Database::executeTransaction($this->hospitalCode, [
                ['sql' => $mainSql, 'params' => $mainParams],
                $audit,
            ]);

            $saved = $this->findOracle($key, $operationalDate);
            return is_array($saved) ? $saved : $this->defaultReview($ruleId, $operationalDate);
        } catch (Throwable $e) {
            $this->lastStorageError = $e->getMessage();
            $this->log('save-oracle', $e);
            return null;
        }
    }

    /** @param array<string,mixed>|null $existing @param array<string,mixed> $action @return array{sql:string,params:array<string,mixed>} */
    private function oracleAuditStatement(string $key, string $operationalDate, ?array $existing, string $status, string $note, string $updatedBy, array $action): array
    {
        $oldStatus = is_array($existing) ? (string)($existing['status'] ?? '') : '';
        $oldNote = is_array($existing) ? (string)($existing['note'] ?? '') : '';
        $snapshot = $this->actionSnapshot($action);
        $sql = "INSERT INTO " . $this->qualifiedAuditTable() . "
                    (audit_id, hospital_code, operational_date, action_key, old_status, new_status, old_note, new_note, changed_by, changed_at, action_snapshot)
                VALUES
                    (" . $this->auditSequenceNextVal() . ", :p_hospital, TO_DATE(:p_operational_date, 'YYYY-MM-DD'), :p_action_key,
                     :p_old_status, :p_new_status, :p_old_note, :p_new_note, :p_changed_by, SYSDATE, :p_snapshot)";
        return [
            'sql' => $sql,
            'params' => [
                'p_hospital' => $this->hospitalCode,
                'p_operational_date' => $this->dateOnly($operationalDate),
                'p_action_key' => $key,
                'p_old_status' => $oldStatus,
                'p_new_status' => $status,
                'p_old_note' => $this->oracleText($oldNote, 1800),
                'p_new_note' => $this->oracleText($note, 1800),
                'p_changed_by' => $this->oracleText($updatedBy, 120),
                'p_snapshot' => $snapshot,
            ],
        ];
    }

    /** @return array<string,mixed>|null */
    private function findOracle(string $ruleId, string $operationalDate): ?array
    {
        try {
            $sql = "SELECT action_key, action_title, action_severity, action_category, action_metric,
                           status, review_notes, updated_by,
                           TO_CHAR(updated_at, 'YYYY-MM-DD HH24:MI:SS') AS updated_at,
                           TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI:SS') AS created_at,
                           review_count, action_snapshot
                      FROM " . $this->qualifiedReviewTable() . "
                     WHERE LOWER(hospital_code) = :p_hospital
                       AND TRUNC(operational_date) = TO_DATE(:p_operational_date, 'YYYY-MM-DD')
                       AND action_key = :p_action_key";
            $row = Database::fetchOne($this->hospitalCode, $sql, [
                'p_hospital' => $this->hospitalCode,
                'p_operational_date' => $this->dateOnly($operationalDate),
                'p_action_key' => $this->key($ruleId),
            ]);
            return is_array($row) ? $this->rowToReview($row, $operationalDate) : null;
        } catch (Throwable $e) {
            $this->lastStorageError = $e->getMessage();
            $this->log('find-oracle', $e);
            return null;
        }
    }

    /** @return array<string,mixed>|null */
    private function findHistoricalOracleAction(string $key): ?array
    {
        $sql = "SELECT * FROM (
                    SELECT action_key, action_title, action_severity, action_category, action_metric,
                           action_snapshot, status, review_notes, updated_by,
                           TO_CHAR(updated_at, 'YYYY-MM-DD HH24:MI:SS') AS updated_at,
                           TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI:SS') AS created_at,
                           TO_CHAR(operational_date, 'YYYY-MM-DD') AS operational_date,
                           review_count
                      FROM " . $this->qualifiedReviewTable() . "
                     WHERE LOWER(hospital_code) = :p_hospital
                       AND action_key = :p_action_key
                     ORDER BY operational_date DESC, updated_at DESC
                ) WHERE ROWNUM <= 1";
        $row = Database::fetchOne($this->hospitalCode, $sql, [
            'p_hospital' => $this->hospitalCode,
            'p_action_key' => $key,
        ]);
        return is_array($row) ? $this->historicalActionFromReviewRow($row, 'oracle') : null;
    }

    /** @return array<string,mixed>|null */
    private function findHistoricalJsonAction(string $key): ?array
    {
        $dir = \storage_path('action_reviews/' . $this->hospitalCode);
        $files = is_dir($dir) ? glob($dir . DIRECTORY_SEPARATOR . '*.json') : [];
        if (!is_array($files)) { return null; }
        rsort($files, SORT_STRING);
        foreach ($files as $file) {
            $raw = @file_get_contents((string)$file);
            $data = is_string($raw) ? json_decode($raw, true) : null;
            if (!is_array($data) || !isset($data[$key]) || !is_array($data[$key])) { continue; }
            $row = $data[$key];
            $row['action_key'] = (string)($row['rule_id'] ?? $key);
            $row['review_notes'] = (string)($row['note'] ?? '');
            $row['operational_date'] = (string)($row['operational_date'] ?? basename((string)$file, '.json'));
            return $this->historicalActionFromReviewRow($row, 'json_fallback');
        }
        return null;
    }

    /** @param array<string,mixed> $row @return array<string,mixed> */
    private function historicalActionFromReviewRow(array $row, string $storage): array
    {
        $snapshot = [];
        $rawSnapshot = (string)($row['action_snapshot'] ?? '');
        if ($rawSnapshot !== '') {
            $decoded = json_decode($rawSnapshot, true);
            if (is_array($decoded)) { $snapshot = $decoded; }
        }

        $operationalDate = (string)($row['operational_date'] ?? '');
        $action = $snapshot;
        $action['rule_id'] = (string)($row['action_key'] ?? $snapshot['rule_id'] ?? '');
        $action['title'] = (string)($snapshot['title'] ?? $row['action_title'] ?? 'Historical executive action');
        $action['severity'] = (string)($snapshot['severity'] ?? $row['action_severity'] ?? 'routine');
        $action['category'] = (string)($snapshot['category'] ?? $row['action_category'] ?? 'action');
        $action['metric'] = (string)($snapshot['metric'] ?? $row['action_metric'] ?? '');
        $action['historical_only'] = true;
        $action['historical_operational_date'] = $operationalDate;
        $action['review'] = [
            'rule_id' => $action['rule_id'],
            'operational_date' => $operationalDate,
            'status' => $this->normalizeStatus((string)($row['status'] ?? 'open')),
            'note' => (string)($row['review_notes'] ?? $row['note'] ?? ''),
            'updated_at' => (string)($row['updated_at'] ?? ''),
            'updated_by' => (string)($row['updated_by'] ?? ''),
            'review_count' => (int)($row['review_count'] ?? 0),
            'storage' => $storage,
        ];
        $action['review_status'] = (string)$action['review']['status'];
        return $action;
    }

    /** @return array<int,array<string,mixed>> */
    private function latestAuditRows(string $operationalDate, int $limit): array
    {
        $limit = max(1, min(100, $limit));
        $sql = "SELECT * FROM (
                    SELECT action_key, old_status, new_status, changed_by,
                           TO_CHAR(changed_at, 'YYYY-MM-DD HH24:MI:SS') AS changed_at
                      FROM " . $this->qualifiedAuditTable() . "
                     WHERE LOWER(hospital_code) = :p_hospital
                       AND TRUNC(operational_date) = TO_DATE(:p_operational_date, 'YYYY-MM-DD')
                     ORDER BY changed_at DESC, audit_id DESC
                ) WHERE ROWNUM <= " . $limit;
        return Database::fetchAll($this->hospitalCode, $sql, [
            'p_hospital' => $this->hospitalCode,
            'p_operational_date' => $this->dateOnly($operationalDate),
        ]);
    }

    /** @return array<int,array<string,mixed>> */
    private function readOracleAuditHistory(string $key, string $operationalDate, int $limit): array
    {
        $limit = max(1, min(50, $limit));
        $sql = "SELECT * FROM (
                    SELECT action_key, old_status, new_status, old_note, new_note, changed_by,
                           TO_CHAR(operational_date, 'YYYY-MM-DD') AS operational_date,
                           TO_CHAR(changed_at, 'YYYY-MM-DD HH24:MI:SS') AS changed_at,
                           'oracle' AS storage
                      FROM " . $this->qualifiedAuditTable() . "
                     WHERE LOWER(hospital_code) = :p_hospital
                       AND TRUNC(operational_date) = TO_DATE(:p_operational_date, 'YYYY-MM-DD')
                       AND action_key = :p_action_key
                     ORDER BY changed_at DESC, audit_id DESC
                ) WHERE ROWNUM <= " . $limit;
        return Database::fetchAll($this->hospitalCode, $sql, [
            'p_hospital' => $this->hospitalCode,
            'p_operational_date' => $this->dateOnly($operationalDate),
            'p_action_key' => $key,
        ]);
    }

    /** @return array<int,array<string,mixed>> */
    private function readOracleAuditHistoryAllDates(string $key, int $limit): array
    {
        $limit = max(1, min(100, $limit));
        $sql = "SELECT * FROM (
                    SELECT action_key, old_status, new_status, old_note, new_note, changed_by,
                           TO_CHAR(operational_date, 'YYYY-MM-DD') AS operational_date,
                           TO_CHAR(changed_at, 'YYYY-MM-DD HH24:MI:SS') AS changed_at,
                           'oracle' AS storage
                      FROM " . $this->qualifiedAuditTable() . "
                     WHERE LOWER(hospital_code) = :p_hospital
                       AND action_key = :p_action_key
                     ORDER BY changed_at DESC, audit_id DESC
                ) WHERE ROWNUM <= " . $limit;
        return Database::fetchAll($this->hospitalCode, $sql, [
            'p_hospital' => $this->hospitalCode,
            'p_action_key' => $key,
        ]);
    }

    private function auditCountForDate(string $operationalDate): int
    {
        $sql = "SELECT COUNT(1) AS cnt
                  FROM " . $this->qualifiedAuditTable() . "
                 WHERE LOWER(hospital_code) = :p_hospital
                   AND TRUNC(operational_date) = TO_DATE(:p_operational_date, 'YYYY-MM-DD')";
        $row = Database::fetchOne($this->hospitalCode, $sql, [
            'p_hospital' => $this->hospitalCode,
            'p_operational_date' => $this->dateOnly($operationalDate),
        ]);
        return (int)($row['cnt'] ?? 0);
    }

    /** @param array<string,mixed> $row @return array<string,mixed> */
    private function rowToReview(array $row, string $operationalDate): array
    {
        return [
            'rule_id' => (string)($row['action_key'] ?? ''),
            'operational_date' => $operationalDate,
            'status' => $this->normalizeStatus((string)($row['status'] ?? 'open')),
            'note' => (string)($row['review_notes'] ?? ''),
            'updated_at' => (string)($row['updated_at'] ?? ''),
            'updated_by' => (string)($row['updated_by'] ?? ''),
            'action_title' => (string)($row['action_title'] ?? ''),
            'action_severity' => (string)($row['action_severity'] ?? ''),
            'action_category' => (string)($row['action_category'] ?? ''),
            'action_metric' => (string)($row['action_metric'] ?? ''),
            'review_count' => (int)($row['review_count'] ?? 0),
            'storage' => 'oracle',
        ];
    }

    /** @param array<string,mixed> $action @return array<string,mixed> */
    private function saveJson(string $ruleId, string $operationalDate, string $status, string $note, string $updatedBy, array $action = []): array
    {
        $all = $this->readJson($operationalDate);
        $key = $this->key($ruleId);
        $previous = isset($all[$key]) && is_array($all[$key]) ? $all[$key] : [];
        $fromStatus = $previous ? (string)($previous['status'] ?? 'open') : 'open';
        $this->ensureTransition($fromStatus, $status);
        $record = [
            'rule_id' => $ruleId,
            'operational_date' => $operationalDate,
            'status' => $status,
            'note' => $note,
            'updated_at' => date('Y-m-d H:i:s'),
            'updated_by' => $updatedBy,
            'action_title' => (string)($action['title'] ?? ''),
            'action_severity' => (string)($action['severity'] ?? ''),
            'action_category' => (string)($action['category'] ?? ''),
            'action_metric' => (string)($action['metric'] ?? ''),
            'action_snapshot' => $this->actionSnapshot($action),
            'review_count' => (int)($previous['review_count'] ?? 0) + 1,
            'storage' => 'json_fallback',
        ];
        $all[$key] = $record;
        $this->writeJson($operationalDate, $all);
        $this->writeJsonAudit($operationalDate, $key, $previous, $record, $action);
        return $record;
    }

    /** @return array<string,array<string,mixed>> */
    private function readJson(string $operationalDate): array
    {
        $file = $this->file($operationalDate);
        if (!is_file($file)) { return []; }
        $raw = @file_get_contents($file);
        if (!is_string($raw) || trim($raw) === '') { return []; }
        $data = json_decode($raw, true);
        return is_array($data) ? $data : [];
    }

    /** @param array<string,array<string,mixed>> $data */
    private function writeJson(string $operationalDate, array $data): void
    {
        $file = $this->file($operationalDate);
        $tmp = $file . '.tmp';
        @file_put_contents($tmp, json_encode($data, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES), LOCK_EX);
        @rename($tmp, $file);
    }

    /** @param array<string,mixed> $previous @param array<string,mixed> $record @param array<string,mixed> $action */
    private function writeJsonAudit(string $operationalDate, string $key, array $previous, array $record, array $action): void
    {
        $dir = \storage_path('action_reviews/' . $this->hospitalCode);
        if (!is_dir($dir)) { @mkdir($dir, 0775, true); }
        $file = $dir . DIRECTORY_SEPARATOR . $this->dateOnly($operationalDate) . '.audit.jsonl';
        $line = [
            'changed_at' => date('Y-m-d H:i:s'),
            'hospital_code' => $this->hospitalCode,
            'operational_date' => $this->dateOnly($operationalDate),
            'action_key' => $key,
            'old_status' => (string)($previous['status'] ?? ''),
            'new_status' => (string)($record['status'] ?? ''),
            'old_note' => (string)($previous['note'] ?? ''),
            'new_note' => (string)($record['note'] ?? ''),
            'changed_by' => (string)($record['updated_by'] ?? ''),
            'storage' => 'json_fallback',
            'action_title' => (string)($action['title'] ?? ''),
        ];
        @file_put_contents($file, json_encode($line, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) . PHP_EOL, FILE_APPEND | LOCK_EX);
    }

    /** @return array<int,array<string,mixed>> */
    private function readJsonAuditHistory(string $key, string $operationalDate, int $limit): array
    {
        $file = \storage_path('action_reviews/' . $this->hospitalCode . DIRECTORY_SEPARATOR . $this->dateOnly($operationalDate) . '.audit.jsonl');
        if (!is_file($file)) { return []; }
        $lines = @file($file, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
        if (!is_array($lines)) { return []; }
        $out = [];
        for ($i = count($lines) - 1; $i >= 0; $i--) {
            $row = json_decode((string)$lines[$i], true);
            if (!is_array($row) || (string)($row['action_key'] ?? '') !== $key) { continue; }
            $out[] = [
                'action_key' => (string)($row['action_key'] ?? ''),
                'operational_date' => (string)($row['operational_date'] ?? $this->dateOnly($operationalDate)),
                'old_status' => (string)($row['old_status'] ?? ''),
                'new_status' => (string)($row['new_status'] ?? ''),
                'old_note' => (string)($row['old_note'] ?? ''),
                'new_note' => (string)($row['new_note'] ?? ''),
                'changed_by' => (string)($row['changed_by'] ?? ''),
                'changed_at' => (string)($row['changed_at'] ?? ''),
                'storage' => 'json_fallback',
            ];
            if (count($out) >= $limit) { break; }
        }
        return $out;
    }

    /** @param array<int,string> $keys @return array<string,array<string,mixed>> */
    private function previousReviewMap(array $keys, string $operationalDate): array
    {
        $keys = array_values(array_unique(array_filter(array_map([$this, 'key'], $keys))));
        if (!$keys) { return []; }

        if ($this->shouldUseOracle()) {
            try { return $this->previousOracleReviewMap($keys, $operationalDate); }
            catch (Throwable $e) {
                $this->lastStorageError = $e->getMessage();
                $this->log('previous-review-map-oracle', $e);
            }
        }

        return $this->previousJsonReviewMap($keys, $operationalDate);
    }

    /** @param array<int,string> $keys @return array<string,array<string,mixed>> */
    private function previousOracleReviewMap(array $keys, string $operationalDate): array
    {
        $binds = [];
        $params = [
            'p_hospital' => $this->hospitalCode,
            'p_operational_date' => $this->dateOnly($operationalDate),
        ];
        foreach ($keys as $i => $key) {
            $name = 'p_action_key_' . $i;
            $binds[] = ':' . $name;
            $params[$name] = $key;
        }

        $sql = "SELECT action_key, status, review_notes, updated_by, updated_at, operational_date, review_count
                  FROM (
                        SELECT action_key, status, review_notes, updated_by,
                               TO_CHAR(updated_at, 'YYYY-MM-DD HH24:MI:SS') AS updated_at,
                               TO_CHAR(operational_date, 'YYYY-MM-DD') AS operational_date,
                               review_count,
                               ROW_NUMBER() OVER (PARTITION BY action_key ORDER BY operational_date DESC, updated_at DESC) AS rn
                          FROM " . $this->qualifiedReviewTable() . "
                         WHERE LOWER(hospital_code) = :p_hospital
                           AND TRUNC(operational_date) < TO_DATE(:p_operational_date, 'YYYY-MM-DD')
                           AND action_key IN (" . implode(', ', $binds) . ")
                  )
                 WHERE rn = 1";
        $rows = Database::fetchAll($this->hospitalCode, $sql, $params);
        $out = [];
        foreach ($rows as $row) {
            $key = $this->key((string)($row['action_key'] ?? ''));
            if ($key === '') { continue; }
            $out[$key] = [
                'rule_id' => $key,
                'operational_date' => (string)($row['operational_date'] ?? ''),
                'status' => $this->normalizeStatus((string)($row['status'] ?? 'open')),
                'note' => (string)($row['review_notes'] ?? ''),
                'updated_at' => (string)($row['updated_at'] ?? ''),
                'updated_by' => (string)($row['updated_by'] ?? ''),
                'review_count' => (int)($row['review_count'] ?? 0),
                'storage' => 'oracle',
            ];
        }
        return $out;
    }

    /** @param array<int,string> $keys @return array<string,array<string,mixed>> */
    private function previousJsonReviewMap(array $keys, string $operationalDate): array
    {
        $wanted = array_fill_keys($keys, true);
        $out = [];
        $dir = \storage_path('action_reviews/' . $this->hospitalCode);
        $files = is_dir($dir) ? glob($dir . DIRECTORY_SEPARATOR . '*.json') : [];
        if (!is_array($files)) { return []; }
        rsort($files, SORT_STRING);
        $before = $this->dateOnly($operationalDate);
        foreach ($files as $file) {
            $date = basename((string)$file, '.json');
            if ($date >= $before) { continue; }
            $raw = @file_get_contents((string)$file);
            $data = is_string($raw) ? json_decode($raw, true) : null;
            if (!is_array($data)) { continue; }
            foreach ($wanted as $key => $_) {
                if (isset($out[$key]) || !isset($data[$key]) || !is_array($data[$key])) { continue; }
                $row = $data[$key];
                $row['operational_date'] = (string)($row['operational_date'] ?? $date);
                $row['storage'] = 'json_fallback';
                $out[$key] = $row;
            }
            if (count($out) >= count($wanted)) { break; }
        }
        return $out;
    }

    /** @return array<int,array<string,mixed>> */
    private function readJsonAuditHistoryAllDates(string $key, int $limit): array
    {
        $dir = \storage_path('action_reviews/' . $this->hospitalCode);
        $files = is_dir($dir) ? glob($dir . DIRECTORY_SEPARATOR . '*.audit.jsonl') : [];
        if (!is_array($files)) { return []; }
        rsort($files, SORT_STRING);
        $out = [];
        foreach ($files as $file) {
            $lines = @file((string)$file, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
            if (!is_array($lines)) { continue; }
            for ($i = count($lines) - 1; $i >= 0; $i--) {
                $row = json_decode((string)$lines[$i], true);
                if (!is_array($row) || (string)($row['action_key'] ?? '') !== $key) { continue; }
                $out[] = [
                    'action_key' => (string)($row['action_key'] ?? ''),
                    'operational_date' => (string)($row['operational_date'] ?? basename((string)$file, '.audit.jsonl')),
                    'old_status' => (string)($row['old_status'] ?? ''),
                    'new_status' => (string)($row['new_status'] ?? ''),
                    'old_note' => (string)($row['old_note'] ?? ''),
                    'new_note' => (string)($row['new_note'] ?? ''),
                    'changed_by' => (string)($row['changed_by'] ?? ''),
                    'changed_at' => (string)($row['changed_at'] ?? ''),
                    'storage' => 'json_fallback',
                ];
                if (count($out) >= $limit) { return $out; }
            }
        }
        return $out;
    }

    /** @param array<string,mixed> $row */
    private function isQaAuditRow(array $row): bool
    {
        return $this->isQaAuditActor((string)($row['changed_by'] ?? ''));
    }

    private function isQaAuditActor(string $actor): bool
    {
        $actor = strtolower(trim($actor));
        if ($actor === '') { return false; }

        $configured = trim((string)\env('EXEC_ACTION_REVIEW_QA_ACTORS', ''));
        if ($configured !== '') {
            foreach (preg_split('/[,;\r\n]+/', $configured) ?: [] as $pattern) {
                $pattern = strtolower(trim((string)$pattern));
                if ($pattern === '') { continue; }
                $quoted = preg_quote($pattern, '/');
                $regex = '/^' . str_replace('\*', '.*', $quoted) . '$/i';
                if (preg_match($regex, $actor) === 1) { return true; }
            }
        }

        // Built-in identities used by automated review workflow QA gates.
        // Exact human usernames are intentionally not filtered merely because they contain "qa".
        if (preg_match('/^phase[0-9]+(?:_[0-9]+)*(?:_[a-z0-9]+)*_qa(?:_[a-z0-9]+)*$/i', $actor) === 1) { return true; }
        if (preg_match('/^qa(?:[_\-.][a-z0-9]+)*$/i', $actor) === 1) { return true; }
        if (preg_match('/^(?:e[0-9]+_)?h[0-9_]+_qa(?:_[a-z0-9]+)*$/i', $actor) === 1) { return true; }

        return false;
    }

    /** @return array<int,array<string,mixed>> */
    private function dashboardAuditRows(string $startDate, string $endDate, int $limit): array
    {
        $limit = max(1, min(2000, $limit));
        $startDate = $this->dateOnly($startDate);
        $endDate = $this->dateOnly($endDate);

        if ($this->shouldUseOracle()) {
            try {
                $sql = "SELECT * FROM (
                            SELECT audit_id, action_key, old_status, new_status,
                                   old_note, new_note, changed_by, action_snapshot,
                                   TO_CHAR(operational_date, 'YYYY-MM-DD') AS operational_date,
                                   TO_CHAR(changed_at, 'YYYY-MM-DD HH24:MI:SS') AS changed_at,
                                   'oracle' AS storage
                              FROM " . $this->qualifiedAuditTable() . "
                             WHERE LOWER(hospital_code) = :p_hospital
                               AND TRUNC(changed_at) BETWEEN TO_DATE(:p_start_date, 'YYYY-MM-DD')
                                                         AND TO_DATE(:p_end_date, 'YYYY-MM-DD')
                             ORDER BY changed_at DESC, audit_id DESC
                        ) WHERE ROWNUM <= " . $limit;
                $rows = Database::fetchAll($this->hospitalCode, $sql, [
                    'p_hospital' => $this->hospitalCode,
                    'p_start_date' => $startDate,
                    'p_end_date' => $endDate,
                ]);
                return is_array($rows) ? $rows : [];
            } catch (Throwable $e) {
                $this->lastStorageError = $e->getMessage();
                $this->log('dashboard-audit-rows-oracle', $e);
            }
        }

        $dir = \storage_path('action_reviews/' . $this->hospitalCode);
        $files = is_dir($dir) ? glob($dir . DIRECTORY_SEPARATOR . '*.audit.jsonl') : [];
        if (!is_array($files) || !$files) { return []; }

        $rows = [];
        foreach ($files as $file) {
            $base = basename((string)$file, '.audit.jsonl');
            if ($base < $startDate || $base > $endDate) { continue; }
            $lines = @file((string)$file, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
            if (!is_array($lines)) { continue; }
            foreach ($lines as $line) {
                $row = json_decode((string)$line, true);
                if (!is_array($row)) { continue; }
                $row['operational_date'] = (string)($row['operational_date'] ?? $base);
                $row['storage'] = 'json_fallback';
                $rows[] = $row;
            }
        }

        usort($rows, function (array $a, array $b): int {
            $timeCmp = strcmp((string)($b['changed_at'] ?? ''), (string)($a['changed_at'] ?? ''));
            if ($timeCmp !== 0) { return $timeCmp; }
            return strcmp((string)($b['action_key'] ?? ''), (string)($a['action_key'] ?? ''));
        });
        return array_slice($rows, 0, $limit);
    }

    /** @return array<string,string> */
    private function dashboardSnapshotMeta(string $snapshot): array
    {
        if ($snapshot === '') { return []; }
        $decoded = json_decode($snapshot, true);
        if (!is_array($decoded)) { return []; }
        return [
            'title' => (string)($decoded['title'] ?? ''),
            'title_ar' => (string)($decoded['title_ar'] ?? ''),
            'severity' => (string)($decoded['severity'] ?? ''),
            'category' => (string)($decoded['category'] ?? ''),
        ];
    }

    /** @return array<string,mixed> */
    private function latestAuditActivity(): array
    {
        if ($this->shouldUseOracle()) {
            try {
                $sql = "SELECT * FROM (
                            SELECT action_key, old_status, new_status, changed_by,
                                   TO_CHAR(operational_date, 'YYYY-MM-DD') AS operational_date,
                                   TO_CHAR(changed_at, 'YYYY-MM-DD HH24:MI:SS') AS changed_at,
                                   'oracle' AS storage
                              FROM " . $this->qualifiedAuditTable() . "
                             WHERE LOWER(hospital_code) = :p_hospital
                             ORDER BY changed_at DESC, audit_id DESC
                        ) WHERE ROWNUM <= 1";
                $row = Database::fetchOne($this->hospitalCode, $sql, ['p_hospital' => $this->hospitalCode]);
                return is_array($row) ? $row : [];
            } catch (Throwable $e) {
                $this->lastStorageError = $e->getMessage();
                $this->log('latest-audit-activity-oracle', $e);
            }
        }

        $dir = \storage_path('action_reviews/' . $this->hospitalCode);
        $files = is_dir($dir) ? glob($dir . DIRECTORY_SEPARATOR . '*.audit.jsonl') : [];
        if (!is_array($files) || !$files) { return []; }
        rsort($files, SORT_STRING);
        foreach ($files as $file) {
            $lines = @file((string)$file, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
            if (!is_array($lines) || !$lines) { continue; }
            $row = json_decode((string)$lines[count($lines) - 1], true);
            if (is_array($row)) { $row['storage'] = 'json_fallback'; return $row; }
        }
        return [];
    }

    private function lifetimeAuditCount(): int
    {
        if ($this->shouldUseOracle()) {
            try {
                $sql = "SELECT COUNT(1) AS cnt FROM " . $this->qualifiedAuditTable() . " WHERE LOWER(hospital_code) = :p_hospital";
                $row = Database::fetchOne($this->hospitalCode, $sql, ['p_hospital' => $this->hospitalCode]);
                return (int)($row['cnt'] ?? 0);
            } catch (Throwable $e) {
                $this->lastStorageError = $e->getMessage();
                $this->log('lifetime-audit-count-oracle', $e);
            }
        }

        $dir = \storage_path('action_reviews/' . $this->hospitalCode);
        $files = is_dir($dir) ? glob($dir . DIRECTORY_SEPARATOR . '*.audit.jsonl') : [];
        if (!is_array($files)) { return 0; }
        $count = 0;
        foreach ($files as $file) {
            $lines = @file((string)$file, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
            if (is_array($lines)) { $count += count($lines); }
        }
        return $count;
    }

    private function ensureTransition(string $fromStatus, string $toStatus): void
    {
        $fromStatus = $this->normalizeStatus($fromStatus);
        $toStatus = $this->normalizeStatus($toStatus);
        if (!in_array($toStatus, $this->allowedTransitions($fromStatus), true)) {
            throw new \RuntimeException('Unsupported review status transition: ' . $fromStatus . ' -> ' . $toStatus);
        }
    }

    private function defaultReview(string $ruleId, string $operationalDate): array
    {
        return [
            'rule_id' => $ruleId,
            'operational_date' => $operationalDate,
            'status' => 'open',
            'note' => '',
            'updated_at' => '',
            'updated_by' => '',
            'review_count' => 0,
            'storage' => $this->storageMode(),
        ];
    }

    private function tableExists(string $baseTableName): bool
    {
        $owner = $this->schemaName();
        $sql = "SELECT COUNT(1) AS cnt FROM ALL_TABLES WHERE TABLE_NAME = :p_table_name";
        $params = ['p_table_name' => strtoupper($baseTableName)];
        if ($owner !== '') {
            $sql .= " AND OWNER = :p_owner";
            $params['p_owner'] = strtoupper($owner);
        }
        $row = Database::fetchOne($this->hospitalCode, $sql, $params);
        return (int)($row['cnt'] ?? 0) > 0;
    }

    private function normalizeStatus(string $status): string
    {
        $status = strtolower(trim($status));
        return in_array($status, ['open', 'reviewed', 'deferred'], true) ? $status : 'open';
    }

    private function key(string $ruleId): string
    {
        $ruleId = strtolower(trim($ruleId));
        $ruleId = preg_replace('/[^a-z0-9_\-.]+/', '_', $ruleId) ?: 'action';
        return $this->oracleText($ruleId, 140);
    }

    private function dateOnly(string $date): string
    {
        if (preg_match('/^\d{4}-\d{2}-\d{2}/', $date, $m)) { return $m[0]; }
        return date('Y-m-d');
    }

    private function limitText(string $text, int $limit): string
    {
        $text = trim($text);
        if (function_exists('mb_substr')) { return mb_substr($text, 0, $limit); }
        return substr($text, 0, $limit);
    }


    /**
     * Oracle VARCHAR2 limits are byte-sensitive in many installations.  This guard
     * keeps every bound text value below the column byte ceiling so pdo_oci does not
     * promote it to LONG and raise ORA-01461 during INSERT/UPDATE.
     */
    private function oracleText(string $text, int $byteLimit): string
    {
        $text = trim($text);
        $byteLimit = max(1, $byteLimit);
        if (strlen($text) <= $byteLimit) { return $text; }

        if (function_exists('mb_strcut')) {
            return rtrim((string)mb_strcut($text, 0, $byteLimit, 'UTF-8'));
        }

        return rtrim(substr($text, 0, $byteLimit));
    }

    /** @param array<string,mixed> $action */
    private function actionSnapshot(array $action): string
    {
        $snapshot = [
            'rule_id' => (string)($action['rule_id'] ?? ''),
            'severity' => (string)($action['severity'] ?? ''),
            'category' => (string)($action['category'] ?? ''),
            'title' => (string)($action['title'] ?? ''),
            'title_ar' => (string)($action['title_ar'] ?? ''),
            'text' => (string)($action['text'] ?? ''),
            'text_ar' => (string)($action['text_ar'] ?? ''),
            'metric' => (string)($action['metric'] ?? ''),
            'observed' => (string)($action['observed'] ?? ''),
            'threshold' => (string)($action['threshold'] ?? ''),
            'why' => (string)($action['why'] ?? ''),
            'why_ar' => (string)($action['why_ar'] ?? ''),
            'recommendation' => (string)($action['recommendation'] ?? ''),
            'recommendation_ar' => (string)($action['recommendation_ar'] ?? ''),
            'review_source' => (string)($action['review_source'] ?? ''),
            'link_type' => (string)($action['link_type'] ?? ''),
            'target' => (string)($action['target'] ?? ''),
            'link_label' => (string)($action['link_label'] ?? ''),
            'generated_at' => date('Y-m-d H:i:s'),
        ];
        $json = json_encode($snapshot, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        return $this->oracleText(is_string($json) ? $json : '', 3000);
    }

    private function file(string $operationalDate): string
    {
        $date = $this->dateOnly($operationalDate);
        $dir = \storage_path('action_reviews/' . $this->hospitalCode);
        if (!is_dir($dir)) { @mkdir($dir, 0775, true); }
        return $dir . DIRECTORY_SEPARATOR . $date . '.json';
    }

    private function schemaName(): string
    {
        $schema = strtoupper(trim((string)\env('EXEC_ACTION_REVIEW_SCHEMA', '')));
        if ($schema !== '' && preg_match('/^[A-Z][A-Z0-9_]*$/', $schema)) { return $schema; }
        return '';
    }

    private function baseReviewTable(): string { return 'DASH_EXEC_ACTION_REVIEW'; }
    private function baseAuditTable(): string { return 'DASH_EXEC_ACTION_REVIEW_AUDIT'; }
    private function baseAuditSequence(): string { return 'DASH_EXEC_ACT_REV_AUD_S'; }

    private function qualifiedReviewTable(): string { return $this->qualify($this->baseReviewTable()); }
    private function qualifiedAuditTable(): string { return $this->qualify($this->baseAuditTable()); }
    private function auditSequenceNextVal(): string { return $this->qualify($this->baseAuditSequence()) . '.NEXTVAL'; }

    private function qualify(string $name): string
    {
        $schema = $this->schemaName();
        return $schema !== '' ? $schema . '.' . $name : $name;
    }

    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') . '] ExecutiveActionReviewRepository ' . $context . ': ' . $e->getMessage() . PHP_EOL, FILE_APPEND);
    }
}
