<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use Throwable;

/**
 * Phase 10.6.6 - Executive Review Governance, Ownership & Follow-up Management.
 *
 * Current accountability state is stored once per hospital + action key so ownership,
 * due dates and follow-up commitments survive operational-day rollover. Every change is
 * also written to an immutable governance audit trail. JSON fallback remains available
 * when the Oracle governance tables are not installed.
 */
final class ExecutiveActionGovernanceRepository
{
    /** @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
    {
        $all = $this->all();
        foreach ($actions as $i => $action) {
            if (!is_array($action)) { continue; }
            $key = $this->key((string)($action['rule_id'] ?? ''));
            $row = isset($all[$key]) && is_array($all[$key])
                ? $all[$key]
                : $this->defaultGovernance($key, $operationalDate);
            $row = $this->withDerivedState($row, $operationalDate);
            $actions[$i]['governance'] = $row;
            $actions[$i]['accountability_status'] = (string)($row['accountability_status'] ?? 'unassigned');
        }
        return $actions;
    }

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

    /** @return array<string,mixed> */
    public function find(string $ruleId, string $operationalDate = ''): array
    {
        $key = $this->key($ruleId);
        $all = $this->all();
        $row = isset($all[$key]) && is_array($all[$key])
            ? $all[$key]
            : $this->defaultGovernance($key, $operationalDate !== '' ? $operationalDate : date('Y-m-d'));
        return $this->withDerivedState($row, $operationalDate !== '' ? $operationalDate : date('Y-m-d'));
    }

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

        if ($payload['resolution_status'] === 'resolved' && trim((string)$payload['resolution_note']) === '') {
            throw new \RuntimeException('Resolution note is required when an executive action is marked resolved.');
        }

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

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

    /** @param array<int,array<string,mixed>> $actions @return array<string,mixed> */
    public function managementSummary(array $actions, string $operationalDate): array
    {
        $summary = [
            'phase' => '10.6.6',
            'total_actions' => count($actions),
            'active' => 0,
            'resolved' => 0,
            'assigned' => 0,
            'unassigned' => 0,
            'overdue' => 0,
            'due_soon' => 0,
            'follow_up_due' => 0,
            'escalated' => 0,
            'critical_escalations' => 0,
            'owners' => [],
            'owner_workload' => [],
            'accountability_queue' => [],
            'recent_activity' => $this->latestAuditRows(20, false),
            'storage' => $this->storageMode(),
            'operational_date' => $this->dateOnly($operationalDate),
        ];

        $ownerLoad = [];
        $queue = [];
        foreach ($actions as $action) {
            if (!is_array($action)) { continue; }
            $gov = is_array($action['governance'] ?? null)
                ? $this->withDerivedState($action['governance'], $operationalDate)
                : $this->defaultGovernance((string)($action['rule_id'] ?? ''), $operationalDate);

            $resolved = (string)($gov['resolution_status'] ?? 'active') === 'resolved';
            if ($resolved) { $summary['resolved']++; } else { $summary['active']++; }

            $owner = trim((string)($gov['owner_name'] ?? ''));
            $assignee = trim((string)($gov['assigned_to'] ?? ''));
            if ($owner !== '' || $assignee !== '') { $summary['assigned']++; }
            else { $summary['unassigned']++; }

            $accountability = (string)($gov['accountability_status'] ?? 'unassigned');
            if ($accountability === 'overdue') { $summary['overdue']++; }
            if ($accountability === 'due_soon') { $summary['due_soon']++; }
            if (!empty($gov['follow_up_due'])) { $summary['follow_up_due']++; }

            $esc = (string)($gov['escalation_level'] ?? 'none');
            if (!$resolved && $esc !== 'none') {
                $summary['escalated']++;
                if ($esc === 'critical') { $summary['critical_escalations']++; }
            }

            $ownerKey = $owner !== '' ? $owner : ($assignee !== '' ? $assignee : 'Unassigned');
            if (!isset($ownerLoad[$ownerKey])) {
                $ownerLoad[$ownerKey] = ['owner' => $ownerKey, 'total' => 0, 'active' => 0, 'overdue' => 0, 'escalated' => 0, 'resolved' => 0];
            }
            $ownerLoad[$ownerKey]['total']++;
            if ($resolved) { $ownerLoad[$ownerKey]['resolved']++; }
            else { $ownerLoad[$ownerKey]['active']++; }
            if ($accountability === 'overdue') { $ownerLoad[$ownerKey]['overdue']++; }
            if (!$resolved && $esc !== 'none') { $ownerLoad[$ownerKey]['escalated']++; }

            if (!$resolved) {
                $severity = strtolower(trim((string)($action['severity'] ?? 'routine')));
                $severityScore = ['critical' => 500, 'watch' => 350, 'routine' => 200, 'opportunity' => 100][$severity] ?? 200;
                $statusScore = [
                    'overdue' => 1000,
                    'follow_up_due' => 850,
                    'due_soon' => 700,
                    'escalated' => 650,
                    'unassigned' => 550,
                    'assigned' => 200,
                    'on_track' => 100,
                ][$accountability] ?? 100;
                $escScore = ['critical' => 500, 'high' => 350, 'watch' => 200, 'none' => 0][$esc] ?? 0;
                $queue[] = [
                    'action_key' => (string)($action['rule_id'] ?? ''),
                    'title' => (string)($action['title'] ?? $action['rule_id'] ?? 'Action'),
                    'title_ar' => (string)($action['title_ar'] ?? ''),
                    'severity' => $severity,
                    'owner_name' => $owner,
                    'assigned_to' => $assignee,
                    'due_date' => (string)($gov['due_date'] ?? ''),
                    'follow_up_date' => (string)($gov['follow_up_date'] ?? ''),
                    'escalation_level' => $esc,
                    'accountability_status' => $accountability,
                    'overdue_days' => (int)($gov['overdue_days'] ?? 0),
                    'due_in_days' => isset($gov['due_in_days']) ? (int)$gov['due_in_days'] : null,
                    '_score' => $statusScore + $severityScore + $escScore + min(365, (int)($gov['overdue_days'] ?? 0)),
                ];
            }
        }

        uasort($ownerLoad, function ($a, $b) {
            $aScore = ((int)$a['overdue'] * 100) + ((int)$a['escalated'] * 50) + (int)$a['active'];
            $bScore = ((int)$b['overdue'] * 100) + ((int)$b['escalated'] * 50) + (int)$b['active'];
            return $bScore <=> $aScore;
        });
        usort($queue, function ($a, $b) { return ((int)$b['_score']) <=> ((int)$a['_score']); });
        foreach ($queue as &$row) { unset($row['_score']); }
        unset($row);

        $summary['owner_workload'] = array_values($ownerLoad);
        $summary['owners'] = array_values(array_filter(array_keys($ownerLoad), function ($v) { return $v !== 'Unassigned'; }));
        $summary['accountability_queue'] = $queue;
        return $summary;
    }

    /** @return array<int,array<string,mixed>> */
    public function auditHistory(string $ruleId, int $limit = 30): array
    {
        $limit = max(1, min(100, $limit));
        $key = $this->key($ruleId);
        if ($this->shouldUseOracle()) {
            try { return $this->readOracleAuditHistory($key, $limit); }
            catch (Throwable $e) {
                $this->lastStorageError = $e->getMessage();
                $this->log('audit-history-oracle', $e);
            }
        }
        return $this->readJsonAuditHistory($key, $limit);
    }

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

        $stateCount = 0;
        $auditCount = 0;
        if ($mainExists && $auditExists) {
            try {
                $row = Database::fetchOne($this->hospitalCode,
                    "SELECT COUNT(1) AS cnt FROM " . $this->qualifiedGovernanceTable() . " WHERE LOWER(hospital_code) = :p_hospital",
                    ['p_hospital' => $this->hospitalCode]
                );
                $stateCount = (int)($row['cnt'] ?? 0);
                $row = Database::fetchOne($this->hospitalCode,
                    "SELECT COUNT(1) AS cnt FROM " . $this->qualifiedAuditTable() . " WHERE LOWER(hospital_code) = :p_hospital",
                    ['p_hospital' => $this->hospitalCode]
                );
                $auditCount = (int)($row['cnt'] ?? 0);
            } catch (Throwable $e) {
                $error = $e->getMessage();
            }
        }

        return [
            'phase' => '10.6.6',
            'preferred_storage' => 'oracle',
            'storage_mode' => $this->storageMode(),
            'main_table' => $this->qualifiedGovernanceTable(),
            'audit_table' => $this->qualifiedAuditTable(),
            'main_table_exists' => $mainExists,
            'audit_table_exists' => $auditExists,
            'state_count' => $stateCount,
            'audit_count' => $auditCount,
            'latest_audit_rows' => $this->latestAuditRows(20, true),
            'last_storage_error' => $error !== '' ? $error : $this->lastStorageError,
            'operational_date' => $this->dateOnly($operationalDate),
            'json_fallback_path' => $this->jsonFile(),
        ];
    }

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

    /** @return array<string,mixed> */
    private function normalizePayload(array $input): array
    {
        $escalation = strtolower(trim((string)($input['escalation_level'] ?? 'none')));
        if (!in_array($escalation, ['none', 'watch', 'high', 'critical'], true)) { $escalation = 'none'; }
        $resolution = strtolower(trim((string)($input['resolution_status'] ?? 'active')));
        if (!in_array($resolution, ['active', 'resolved'], true)) { $resolution = 'active'; }

        return [
            'owner_name' => $this->oracleText((string)($input['owner_name'] ?? ''), 140),
            'assigned_to' => $this->oracleText((string)($input['assigned_to'] ?? ''), 140),
            'due_date' => $this->nullableDate((string)($input['due_date'] ?? '')),
            'follow_up_date' => $this->nullableDate((string)($input['follow_up_date'] ?? '')),
            'escalation_level' => $escalation,
            'resolution_status' => $resolution,
            'resolution_note' => $this->oracleText((string)($input['resolution_note'] ?? ''), 1800),
        ];
    }

    private function shouldUseOracle(): bool
    {
        $forced = strtolower(trim((string)\env('EXEC_ACTION_GOVERNANCE_STORAGE', 'oracle')));
        if ($forced === 'json' || $forced === 'file') { return false; }
        if ($this->oracleAvailable !== null) { return $this->oracleAvailable; }
        try {
            $this->oracleAvailable = $this->tableExists($this->baseGovernanceTable()) && $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(): ?array
    {
        try {
            $sql = "SELECT action_key,
                           TO_CHAR(first_operational_date, 'YYYY-MM-DD') AS first_operational_date,
                           TO_CHAR(last_operational_date, 'YYYY-MM-DD') AS last_operational_date,
                           owner_name, assigned_to,
                           TO_CHAR(due_date, 'YYYY-MM-DD') AS due_date,
                           TO_CHAR(follow_up_date, 'YYYY-MM-DD') AS follow_up_date,
                           escalation_level, resolution_status, resolution_note,
                           TO_CHAR(resolved_at, 'YYYY-MM-DD HH24:MI:SS') AS resolved_at,
                           resolved_by, 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,
                           action_title, action_severity, action_category, action_metric, update_count
                      FROM " . $this->qualifiedGovernanceTable() . "
                     WHERE LOWER(hospital_code) = :p_hospital";
            $rows = Database::fetchAll($this->hospitalCode, $sql, ['p_hospital' => $this->hospitalCode]);
            $out = [];
            foreach ($rows as $row) {
                $key = $this->key((string)($row['action_key'] ?? ''));
                if ($key === '') { continue; }
                $out[$key] = $this->rowToGovernance($row);
            }
            return $out;
        } catch (Throwable $e) {
            $this->lastStorageError = $e->getMessage();
            $this->log('read-oracle', $e);
            return null;
        }
    }

    /** @param array<string,mixed> $payload @param array<string,mixed> $action @return array<string,mixed>|null */
    private function saveOracle(string $ruleId, string $operationalDate, array $payload, string $updatedBy, array $action): ?array
    {
        $key = $this->key($ruleId);
        $existing = $this->findOracle($key);
        $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);
        $opDate = $this->dateOnly($operationalDate);

        try {
            if (is_array($existing)) {
                // Each placeholder is intentionally unique. Native PDO_OCI does not reliably
                // support reusing one named bind more than once in a prepared statement.
                $mainSql = "UPDATE " . $this->qualifiedGovernanceTable() . "
                               SET last_operational_date = TO_DATE(:p_last_operational_date, 'YYYY-MM-DD'),
                                   owner_name = :p_owner_name,
                                   assigned_to = :p_assigned_to,
                                   due_date = TO_DATE(:p_due_date, 'YYYY-MM-DD'),
                                   follow_up_date = TO_DATE(:p_follow_up_date, 'YYYY-MM-DD'),
                                   escalation_level = :p_escalation_level,
                                   resolution_status = :p_resolution_status,
                                   resolution_note = :p_resolution_note,
                                   resolved_at = CASE WHEN :p_resolution_for_at = 'resolved' THEN NVL(resolved_at, SYSDATE) ELSE NULL END,
                                   resolved_by = CASE WHEN :p_resolution_for_by = 'resolved' THEN :p_resolved_by ELSE NULL END,
                                   updated_by = :p_updated_by,
                                   updated_at = SYSDATE,
                                   action_title = :p_title,
                                   action_severity = :p_severity,
                                   action_category = :p_category,
                                   action_metric = :p_metric,
                                   update_count = NVL(update_count,0) + 1
                             WHERE LOWER(hospital_code) = :p_hospital
                               AND action_key = :p_action_key";
                $params = [
                    'p_last_operational_date' => $opDate,
                    'p_owner_name' => (string)$payload['owner_name'],
                    'p_assigned_to' => (string)$payload['assigned_to'],
                    'p_due_date' => (string)$payload['due_date'],
                    'p_follow_up_date' => (string)$payload['follow_up_date'],
                    'p_escalation_level' => (string)$payload['escalation_level'],
                    'p_resolution_status' => (string)$payload['resolution_status'],
                    'p_resolution_note' => (string)$payload['resolution_note'],
                    'p_resolution_for_at' => (string)$payload['resolution_status'],
                    'p_resolution_for_by' => (string)$payload['resolution_status'],
                    'p_resolved_by' => $updatedBy,
                    'p_updated_by' => $updatedBy,
                    'p_title' => $title,
                    'p_severity' => $severity,
                    'p_category' => $category,
                    'p_metric' => $metric,
                    'p_hospital' => $this->hospitalCode,
                    'p_action_key' => $key,
                ];
            } else {
                $mainSql = "INSERT INTO " . $this->qualifiedGovernanceTable() . "
                            (hospital_code, action_key, first_operational_date, last_operational_date,
                             owner_name, assigned_to, due_date, follow_up_date,
                             escalation_level, resolution_status, resolution_note,
                             resolved_at, resolved_by, updated_by, updated_at, created_at,
                             action_title, action_severity, action_category, action_metric, update_count)
                        VALUES
                            (:p_hospital, :p_action_key,
                             TO_DATE(:p_first_operational_date, 'YYYY-MM-DD'),
                             TO_DATE(:p_last_operational_date, 'YYYY-MM-DD'),
                             :p_owner_name, :p_assigned_to,
                             TO_DATE(:p_due_date, 'YYYY-MM-DD'),
                             TO_DATE(:p_follow_up_date, 'YYYY-MM-DD'),
                             :p_escalation_level, :p_resolution_status, :p_resolution_note,
                             CASE WHEN :p_resolution_for_at = 'resolved' THEN SYSDATE ELSE NULL END,
                             CASE WHEN :p_resolution_for_by = 'resolved' THEN :p_resolved_by ELSE NULL END,
                             :p_updated_by, SYSDATE, SYSDATE,
                             :p_title, :p_severity, :p_category, :p_metric, 1)";
                $params = [
                    'p_hospital' => $this->hospitalCode,
                    'p_action_key' => $key,
                    'p_first_operational_date' => $opDate,
                    'p_last_operational_date' => $opDate,
                    'p_owner_name' => (string)$payload['owner_name'],
                    'p_assigned_to' => (string)$payload['assigned_to'],
                    'p_due_date' => (string)$payload['due_date'],
                    'p_follow_up_date' => (string)$payload['follow_up_date'],
                    'p_escalation_level' => (string)$payload['escalation_level'],
                    'p_resolution_status' => (string)$payload['resolution_status'],
                    'p_resolution_note' => (string)$payload['resolution_note'],
                    'p_resolution_for_at' => (string)$payload['resolution_status'],
                    'p_resolution_for_by' => (string)$payload['resolution_status'],
                    'p_resolved_by' => $updatedBy,
                    'p_updated_by' => $updatedBy,
                    'p_title' => $title,
                    'p_severity' => $severity,
                    'p_category' => $category,
                    'p_metric' => $metric,
                ];
            }

            $audit = $this->oracleAuditStatement($key, $opDate, $existing, $payload, $updatedBy, $action);
            Database::executeTransaction($this->hospitalCode, [
                ['sql' => $mainSql, 'params' => $params],
                $audit,
            ]);
            $saved = $this->findOracle($key);
            return is_array($saved) ? $this->withDerivedState($saved, $opDate) : null;
        } catch (Throwable $e) {
            $this->lastStorageError = $e->getMessage();
            $this->log('save-oracle', $e);
            return null;
        }
    }

    /** @param array<string,mixed>|null $existing @param array<string,mixed> $payload @param array<string,mixed> $action @return array{sql:string,params:array<string,mixed>} */
    private function oracleAuditStatement(string $key, string $operationalDate, ?array $existing, array $payload, string $updatedBy, array $action): array
    {
        $sql = "INSERT INTO " . $this->qualifiedAuditTable() . "
                    (audit_id, hospital_code, operational_date, action_key,
                     old_owner_name, new_owner_name, old_assigned_to, new_assigned_to,
                     old_due_date, new_due_date, old_follow_up_date, new_follow_up_date,
                     old_esc_level, new_esc_level, old_res_status, new_res_status,
                     old_res_note, new_res_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_owner_name, :p_new_owner_name, :p_old_assigned_to, :p_new_assigned_to,
                     TO_DATE(:p_old_due_date, 'YYYY-MM-DD'),
                     TO_DATE(:p_new_due_date, 'YYYY-MM-DD'),
                     TO_DATE(:p_old_follow_up_date, 'YYYY-MM-DD'),
                     TO_DATE(:p_new_follow_up_date, 'YYYY-MM-DD'),
                     :p_old_esc_level, :p_new_esc_level, :p_old_res_status, :p_new_res_status,
                     :p_old_res_note, :p_new_res_note, :p_changed_by, SYSDATE, :p_snapshot)";
        return [
            'sql' => $sql,
            'params' => [
                'p_hospital' => $this->hospitalCode,
                'p_operational_date' => $operationalDate,
                'p_action_key' => $key,
                'p_old_owner_name' => $this->oracleText((string)($existing['owner_name'] ?? ''), 140),
                'p_new_owner_name' => (string)$payload['owner_name'],
                'p_old_assigned_to' => $this->oracleText((string)($existing['assigned_to'] ?? ''), 140),
                'p_new_assigned_to' => (string)$payload['assigned_to'],
                'p_old_due_date' => (string)($existing['due_date'] ?? ''),
                'p_new_due_date' => (string)$payload['due_date'],
                'p_old_follow_up_date' => (string)($existing['follow_up_date'] ?? ''),
                'p_new_follow_up_date' => (string)$payload['follow_up_date'],
                'p_old_esc_level' => (string)($existing['escalation_level'] ?? 'none'),
                'p_new_esc_level' => (string)$payload['escalation_level'],
                'p_old_res_status' => (string)($existing['resolution_status'] ?? 'active'),
                'p_new_res_status' => (string)$payload['resolution_status'],
                'p_old_res_note' => $this->oracleText((string)($existing['resolution_note'] ?? ''), 1800),
                'p_new_res_note' => (string)$payload['resolution_note'],
                'p_changed_by' => $updatedBy,
                'p_snapshot' => $this->actionSnapshot($action),
            ],
        ];
    }

    /** @return array<string,mixed>|null */
    private function findOracle(string $key): ?array
    {
        try {
            $sql = "SELECT action_key,
                           TO_CHAR(first_operational_date, 'YYYY-MM-DD') AS first_operational_date,
                           TO_CHAR(last_operational_date, 'YYYY-MM-DD') AS last_operational_date,
                           owner_name, assigned_to,
                           TO_CHAR(due_date, 'YYYY-MM-DD') AS due_date,
                           TO_CHAR(follow_up_date, 'YYYY-MM-DD') AS follow_up_date,
                           escalation_level, resolution_status, resolution_note,
                           TO_CHAR(resolved_at, 'YYYY-MM-DD HH24:MI:SS') AS resolved_at,
                           resolved_by, 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,
                           action_title, action_severity, action_category, action_metric, update_count
                      FROM " . $this->qualifiedGovernanceTable() . "
                     WHERE LOWER(hospital_code) = :p_hospital
                       AND action_key = :p_action_key";
            $row = Database::fetchOne($this->hospitalCode, $sql, [
                'p_hospital' => $this->hospitalCode,
                'p_action_key' => $key,
            ]);
            return is_array($row) ? $this->rowToGovernance($row) : null;
        } catch (Throwable $e) {
            $this->lastStorageError = $e->getMessage();
            $this->log('find-oracle', $e);
            return null;
        }
    }

    /** @return array<int,array<string,mixed>> */
    private function readOracleAuditHistory(string $key, int $limit): array
    {
        $limit = max(1, min(100, $limit));
        $sql = "SELECT * FROM (
                    SELECT action_key, TO_CHAR(operational_date, 'YYYY-MM-DD') AS operational_date,
                           old_owner_name, new_owner_name, old_assigned_to, new_assigned_to,
                           TO_CHAR(old_due_date, 'YYYY-MM-DD') AS old_due_date,
                           TO_CHAR(new_due_date, 'YYYY-MM-DD') AS new_due_date,
                           TO_CHAR(old_follow_up_date, 'YYYY-MM-DD') AS old_follow_up_date,
                           TO_CHAR(new_follow_up_date, 'YYYY-MM-DD') AS new_follow_up_date,
                           old_esc_level, new_esc_level, old_res_status, new_res_status,
                           old_res_note, new_res_note, 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 action_key = :p_action_key
                     ORDER BY changed_at DESC, audit_id DESC
                ) WHERE ROWNUM <= " . $limit;
        $rows = Database::fetchAll($this->hospitalCode, $sql, [
            'p_hospital' => $this->hospitalCode,
            'p_action_key' => $key,
        ]);
        foreach ($rows as &$row) { $row['storage'] = 'oracle'; }
        unset($row);
        return $rows;
    }

    /** @return array<int,array<string,mixed>> */
    private function latestAuditRows(int $limit = 20, bool $includeQaActivity = false): array
    {
        $limit = max(1, min(100, $limit));
        $fetchLimit = min(300, max($limit, $limit * 3));
        $rows = [];
        if ($this->shouldUseOracle()) {
            try {
                $sql = "SELECT * FROM (
                            SELECT action_key, TO_CHAR(operational_date, 'YYYY-MM-DD') AS operational_date,
                                   old_owner_name, new_owner_name, old_assigned_to, new_assigned_to,
                                   TO_CHAR(old_due_date, 'YYYY-MM-DD') AS old_due_date,
                                   TO_CHAR(new_due_date, 'YYYY-MM-DD') AS new_due_date,
                                   TO_CHAR(old_follow_up_date, 'YYYY-MM-DD') AS old_follow_up_date,
                                   TO_CHAR(new_follow_up_date, 'YYYY-MM-DD') AS new_follow_up_date,
                                   old_esc_level, new_esc_level, old_res_status, new_res_status,
                                   old_res_note, new_res_note, changed_by,
                                   TO_CHAR(changed_at, 'YYYY-MM-DD HH24:MI:SS') AS changed_at
                              FROM " . $this->qualifiedAuditTable() . "
                             WHERE LOWER(hospital_code) = :p_hospital
                             ORDER BY changed_at DESC, audit_id DESC
                        ) WHERE ROWNUM <= " . $fetchLimit;
                $rows = Database::fetchAll($this->hospitalCode, $sql, ['p_hospital' => $this->hospitalCode]);
                foreach ($rows as &$row) { $row['storage'] = 'oracle'; }
                unset($row);
            } catch (Throwable $e) {
                $this->lastStorageError = $e->getMessage();
                $this->log('latest-audit-oracle', $e);
                $rows = [];
            }
        } else {
            $rows = $this->readJsonAuditHistory('', $fetchLimit, true);
        }

        if (!$includeQaActivity) {
            $rows = array_values(array_filter($rows, function ($row) {
                return is_array($row) && !$this->isQaActor((string)($row['changed_by'] ?? ''));
            }));
        }
        return array_slice($rows, 0, $limit);
    }

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

    /** @param array<string,mixed> $payload @param array<string,mixed> $action @return array<string,mixed> */
    private function saveJson(string $ruleId, string $operationalDate, array $payload, string $updatedBy, array $action): array
    {
        $key = $this->key($ruleId);
        $all = $this->readJson();
        $existing = isset($all[$key]) && is_array($all[$key]) ? $all[$key] : [];
        $now = date('Y-m-d H:i:s');
        $record = [
            'action_key' => $key,
            'first_operational_date' => (string)($existing['first_operational_date'] ?? $this->dateOnly($operationalDate)),
            'last_operational_date' => $this->dateOnly($operationalDate),
            'owner_name' => (string)$payload['owner_name'],
            'assigned_to' => (string)$payload['assigned_to'],
            'due_date' => (string)$payload['due_date'],
            'follow_up_date' => (string)$payload['follow_up_date'],
            'escalation_level' => (string)$payload['escalation_level'],
            'resolution_status' => (string)$payload['resolution_status'],
            'resolution_note' => (string)$payload['resolution_note'],
            'resolved_at' => (string)$payload['resolution_status'] === 'resolved' ? ((string)($existing['resolved_at'] ?? '') ?: $now) : '',
            'resolved_by' => (string)$payload['resolution_status'] === 'resolved' ? $updatedBy : '',
            'updated_by' => $updatedBy,
            'updated_at' => $now,
            'created_at' => (string)($existing['created_at'] ?? $now),
            'action_title' => (string)($action['title'] ?? $existing['action_title'] ?? ''),
            'action_severity' => (string)($action['severity'] ?? $existing['action_severity'] ?? ''),
            'action_category' => (string)($action['category'] ?? $existing['action_category'] ?? ''),
            'action_metric' => (string)($action['metric'] ?? $existing['action_metric'] ?? ''),
            'update_count' => (int)($existing['update_count'] ?? 0) + 1,
            'storage' => 'json_fallback',
        ];
        $all[$key] = $record;
        $this->writeJson($all);
        $this->writeJsonAudit($key, $operationalDate, $existing, $record, $action);
        return $this->withDerivedState($record, $operationalDate);
    }

    /** @param array<string,array<string,mixed>> $data */
    private function writeJson(array $data): void
    {
        $file = $this->jsonFile();
        $dir = dirname($file);
        if (!is_dir($dir)) { @mkdir($dir, 0775, true); }
        $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> $existing @param array<string,mixed> $record @param array<string,mixed> $action */
    private function writeJsonAudit(string $key, string $operationalDate, array $existing, array $record, array $action): void
    {
        $file = $this->jsonAuditFile();
        $dir = dirname($file);
        if (!is_dir($dir)) { @mkdir($dir, 0775, true); }
        $line = [
            'hospital_code' => $this->hospitalCode,
            'operational_date' => $this->dateOnly($operationalDate),
            'action_key' => $key,
            'old_owner_name' => (string)($existing['owner_name'] ?? ''),
            'new_owner_name' => (string)($record['owner_name'] ?? ''),
            'old_assigned_to' => (string)($existing['assigned_to'] ?? ''),
            'new_assigned_to' => (string)($record['assigned_to'] ?? ''),
            'old_due_date' => (string)($existing['due_date'] ?? ''),
            'new_due_date' => (string)($record['due_date'] ?? ''),
            'old_follow_up_date' => (string)($existing['follow_up_date'] ?? ''),
            'new_follow_up_date' => (string)($record['follow_up_date'] ?? ''),
            'old_esc_level' => (string)($existing['escalation_level'] ?? 'none'),
            'new_esc_level' => (string)($record['escalation_level'] ?? 'none'),
            'old_res_status' => (string)($existing['resolution_status'] ?? 'active'),
            'new_res_status' => (string)($record['resolution_status'] ?? 'active'),
            'old_res_note' => (string)($existing['resolution_note'] ?? ''),
            'new_res_note' => (string)($record['resolution_note'] ?? ''),
            'changed_by' => (string)($record['updated_by'] ?? ''),
            'changed_at' => date('Y-m-d H:i:s'),
            'action_title' => (string)($action['title'] ?? $record['action_title'] ?? ''),
            'storage' => 'json_fallback',
        ];
        @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, int $limit, bool $allActions = false): array
    {
        $file = $this->jsonAuditFile();
        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)) { continue; }
            if (!$allActions && (string)($row['action_key'] ?? '') !== $key) { continue; }
            $row['storage'] = 'json_fallback';
            $out[] = $row;
            if (count($out) >= $limit) { break; }
        }
        return $out;
    }

    /** @param array<string,mixed> $row @return array<string,mixed> */
    private function rowToGovernance(array $row): array
    {
        return [
            'action_key' => $this->key((string)($row['action_key'] ?? '')),
            'first_operational_date' => $this->dateOnly((string)($row['first_operational_date'] ?? date('Y-m-d'))),
            'last_operational_date' => $this->dateOnly((string)($row['last_operational_date'] ?? date('Y-m-d'))),
            'owner_name' => (string)($row['owner_name'] ?? ''),
            'assigned_to' => (string)($row['assigned_to'] ?? ''),
            'due_date' => $this->nullableDate((string)($row['due_date'] ?? '')),
            'follow_up_date' => $this->nullableDate((string)($row['follow_up_date'] ?? '')),
            'escalation_level' => $this->normalizeEscalation((string)($row['escalation_level'] ?? 'none')),
            'resolution_status' => $this->normalizeResolution((string)($row['resolution_status'] ?? 'active')),
            'resolution_note' => (string)($row['resolution_note'] ?? ''),
            'resolved_at' => (string)($row['resolved_at'] ?? ''),
            'resolved_by' => (string)($row['resolved_by'] ?? ''),
            'updated_by' => (string)($row['updated_by'] ?? ''),
            'updated_at' => (string)($row['updated_at'] ?? ''),
            'created_at' => (string)($row['created_at'] ?? ''),
            'action_title' => (string)($row['action_title'] ?? ''),
            'action_severity' => (string)($row['action_severity'] ?? ''),
            'action_category' => (string)($row['action_category'] ?? ''),
            'action_metric' => (string)($row['action_metric'] ?? ''),
            'update_count' => (int)($row['update_count'] ?? 0),
            'storage' => (string)($row['storage'] ?? 'oracle'),
        ];
    }

    /** @param array<string,mixed> $row @return array<string,mixed> */
    private function withDerivedState(array $row, string $operationalDate): array
    {
        $date = $this->dateOnly($operationalDate);
        $todayTs = strtotime($date . ' 00:00:00');
        if ($todayTs === false) { $todayTs = strtotime(date('Y-m-d') . ' 00:00:00'); }
        $dueDate = $this->nullableDate((string)($row['due_date'] ?? ''));
        $followDate = $this->nullableDate((string)($row['follow_up_date'] ?? ''));
        $resolved = $this->normalizeResolution((string)($row['resolution_status'] ?? 'active')) === 'resolved';
        $owner = trim((string)($row['owner_name'] ?? ''));
        $assignee = trim((string)($row['assigned_to'] ?? ''));
        $esc = $this->normalizeEscalation((string)($row['escalation_level'] ?? 'none'));
        $dueInDays = null;
        $overdueDays = 0;
        if ($dueDate !== '') {
            $dueTs = strtotime($dueDate . ' 00:00:00');
            if ($dueTs !== false && $todayTs !== false) {
                $dueInDays = (int)floor(($dueTs - $todayTs) / 86400);
                if ($dueInDays < 0) { $overdueDays = abs($dueInDays); }
            }
        }
        $followDue = false;
        if ($followDate !== '' && !$resolved) {
            $followTs = strtotime($followDate . ' 00:00:00');
            $followDue = $followTs !== false && $todayTs !== false && $followTs <= $todayTs;
        }

        if ($resolved) { $status = 'resolved'; }
        elseif ($dueInDays !== null && $dueInDays < 0) { $status = 'overdue'; }
        elseif ($followDue) { $status = 'follow_up_due'; }
        elseif ($dueInDays !== null && $dueInDays >= 0 && $dueInDays <= 3) { $status = 'due_soon'; }
        elseif ($esc !== 'none') { $status = 'escalated'; }
        elseif ($owner === '' && $assignee === '') { $status = 'unassigned'; }
        elseif ($owner !== '' || $assignee !== '') { $status = 'assigned'; }
        else { $status = 'on_track'; }

        $row['due_date'] = $dueDate;
        $row['follow_up_date'] = $followDate;
        $row['escalation_level'] = $esc;
        $row['resolution_status'] = $resolved ? 'resolved' : 'active';
        $row['accountability_status'] = $status;
        $row['due_in_days'] = $dueInDays;
        $row['overdue_days'] = $overdueDays;
        $row['follow_up_due'] = $followDue;
        return $row;
    }

    /** @return array<string,mixed> */
    private function defaultGovernance(string $ruleId, string $operationalDate): array
    {
        return $this->withDerivedState([
            'action_key' => $this->key($ruleId),
            'first_operational_date' => $this->dateOnly($operationalDate),
            'last_operational_date' => $this->dateOnly($operationalDate),
            'owner_name' => '',
            'assigned_to' => '',
            'due_date' => '',
            'follow_up_date' => '',
            'escalation_level' => 'none',
            'resolution_status' => 'active',
            'resolution_note' => '',
            'resolved_at' => '',
            'resolved_by' => '',
            'updated_by' => '',
            'updated_at' => '',
            'created_at' => '',
            'action_title' => '',
            'action_severity' => '',
            'action_category' => '',
            'action_metric' => '',
            'update_count' => 0,
            'storage' => $this->storageMode(),
        ], $operationalDate);
    }

    private function isQaActor(string $actor): bool
    {
        $actor = strtolower(trim($actor));
        if ($actor === '') { return false; }
        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; }
        $configured = trim((string)\env('EXEC_ACTION_GOVERNANCE_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; }
            }
        }
        return false;
    }

    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 baseGovernanceTable(): string
    {
        return $this->safeIdentifier((string)\env('EXEC_ACTION_GOVERNANCE_TABLE', 'DASH_EXEC_ACTION_GOV'), 'DASH_EXEC_ACTION_GOV');
    }

    private function baseAuditTable(): string
    {
        return $this->safeIdentifier((string)\env('EXEC_ACTION_GOVERNANCE_AUDIT_TABLE', 'DASH_EXEC_ACTION_GOV_AUDIT'), 'DASH_EXEC_ACTION_GOV_AUDIT');
    }

    private function auditSequence(): string
    {
        return $this->safeIdentifier((string)\env('EXEC_ACTION_GOVERNANCE_AUDIT_SEQUENCE', 'DASH_EXEC_ACT_GOV_AUD_S'), 'DASH_EXEC_ACT_GOV_AUD_S');
    }

    private function schemaName(): string
    {
        $schema = trim((string)\env('EXEC_ACTION_GOVERNANCE_SCHEMA', ''));
        if ($schema === '') { $schema = trim((string)\env('EXEC_ACTION_REVIEW_SCHEMA', '')); }
        return $schema !== '' ? $this->safeIdentifier($schema, '') : '';
    }

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

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

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

    private function safeIdentifier(string $value, string $fallback): string
    {
        $value = strtoupper(trim($value));
        return preg_match('/^[A-Z][A-Z0-9_$#]*$/', $value) ? $value : $fallback;
    }

    private function normalizeEscalation(string $value): string
    {
        $value = strtolower(trim($value));
        return in_array($value, ['none', 'watch', 'high', 'critical'], true) ? $value : 'none';
    }

    private function normalizeResolution(string $value): string
    {
        $value = strtolower(trim($value));
        return in_array($value, ['active', 'resolved'], true) ? $value : 'active';
    }

    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 nullableDate(string $date): string
    {
        $date = trim($date);
        if ($date === '') { return ''; }
        if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) {
            throw new \RuntimeException('Invalid date format. Use YYYY-MM-DD.');
        }
        $parts = array_map('intval', explode('-', $date));
        if (count($parts) !== 3 || !checkdate($parts[1], $parts[2], $parts[0])) {
            throw new \RuntimeException('Invalid calendar date: ' . $date);
        }
        return $date;
    }

    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'] ?? ''),
            'metric' => (string)($action['metric'] ?? ''),
            '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 jsonFile(): string
    {
        return \storage_path('action_governance/' . $this->hospitalCode . '/governance.json');
    }

    private function jsonAuditFile(): string
    {
        return \storage_path('action_governance/' . $this->hospitalCode . '/governance.audit.jsonl');
    }

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