<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use RuntimeException;

final class DiarRuleSettingsRepository
{
    /** @var string */
    private $controlBranch;

    public function __construct()
    {
        $this->controlBranch = strtolower(trim((string)\config('access_control.control_branch', 'njch')));
        if ($this->controlBranch === '') {
            $this->controlBranch = 'njch';
        }
    }

    public function controlBranch(): string
    {
        return $this->controlBranch;
    }

    /** @return array<string,mixed> */
    public function controlDatabaseIdentity(): array
    {
        $row = Database::fetchOne($this->controlBranch, "
            SELECT
                SYS_CONTEXT('USERENV','DB_NAME') db_name,
                SYS_CONTEXT('USERENV','CURRENT_SCHEMA') current_schema,
                SYS_CONTEXT('USERENV','SESSION_USER') session_user
            FROM dual
        ");
        return is_array($row) ? $row : [];
    }

    public function schemaReady(): bool
    {
        $row = Database::fetchOne($this->controlBranch, "
            SELECT COUNT(*) object_count
            FROM USER_TABLES
            WHERE TABLE_NAME IN (
                'DASH_BRANCH_REGISTRY',
                'DASH_SHARED_SETTINGS',
                'DASH_SHARED_SET_AUDIT',
                'DASH_DIAR_RULESETS',
                'DASH_DIAR_RULE_VERSIONS',
                'DASH_DIAR_RULE_PARAMS',
                'DASH_DIAR_RULE_SCOPES',
                'DASH_DIAR_DRY_RUNS',
                'DASH_DIAR_DRY_RESULTS',
                'DASH_DIAR_AUDIT'
            )
        ");
        return (int)($row['object_count'] ?? 0) === 10;
    }

    /** @return array<string,mixed> */
    public function dashboardData(?int $selectedVersionId = null): array
    {
        if (!$this->schemaReady()) {
            throw new RuntimeException('DIAR4A_SCHEMA_NOT_READY');
        }

        $identity = $this->controlDatabaseIdentity();
        $active = Database::fetchOne($this->controlBranch, "
            SELECT
                v.VERSION_ID version_id,
                v.VERSION_NO version_no,
                v.STATUS_CODE status_code,
                v.CONFIG_HASH config_hash,
                v.CALIBRATION_STATUS calibration_status,
                v.CHANGE_REASON change_reason,
                v.CREATED_BY created_by,
                TO_CHAR(v.CREATED_AT, 'YYYY-MM-DD HH24:MI:SS') created_at,
                TO_CHAR(v.ACTIVATED_AT, 'YYYY-MM-DD HH24:MI:SS') activated_at,
                r.RULESET_CODE ruleset_code,
                r.NAME_EN ruleset_name_en,
                r.NAME_AR ruleset_name_ar
            FROM DASH_DIAR_RULE_VERSIONS v
            JOIN DASH_DIAR_RULESETS r ON r.RULESET_ID = v.RULESET_ID
            WHERE v.STATUS_CODE = 'ACTIVE'
        ");

        $versions = Database::fetchAll($this->controlBranch, "
            SELECT * FROM (
                SELECT
                    v.VERSION_ID version_id,
                    v.VERSION_NO version_no,
                    v.STATUS_CODE status_code,
                    v.CONFIG_HASH config_hash,
                    v.CALIBRATION_STATUS calibration_status,
                    v.CHANGE_REASON change_reason,
                    v.CREATED_BY created_by,
                    TO_CHAR(v.CREATED_AT, 'YYYY-MM-DD HH24:MI:SS') created_at,
                    v.APPROVED_BY approved_by,
                    TO_CHAR(v.APPROVED_AT, 'YYYY-MM-DD HH24:MI:SS') approved_at,
                    v.ACTIVATED_BY activated_by,
                    TO_CHAR(v.ACTIVATED_AT, 'YYYY-MM-DD HH24:MI:SS') activated_at,
                    (SELECT COUNT(*) FROM DASH_DIAR_RULE_PARAMS p
                     WHERE p.VERSION_ID = v.VERSION_ID) param_count,
                    (SELECT COUNT(*) FROM DASH_DIAR_RULE_SCOPES s
                     WHERE s.VERSION_ID = v.VERSION_ID AND s.IS_ACTIVE = 1) scope_count,
                    (SELECT MAX(d.STATUS_CODE) KEEP (
                        DENSE_RANK LAST ORDER BY d.REQUESTED_AT
                     ) FROM DASH_DIAR_DRY_RUNS d
                     WHERE d.VERSION_ID = v.VERSION_ID) latest_dry_status
                FROM DASH_DIAR_RULE_VERSIONS v
                ORDER BY v.VERSION_NO DESC
            ) WHERE ROWNUM <= 30
        ");

        if ($selectedVersionId === null || $selectedVersionId <= 0) {
            $selectedVersionId = (int)($active['version_id'] ?? 0);
            foreach ($versions as $version) {
                if ((string)($version['status_code'] ?? '') === 'DRAFT') {
                    $selectedVersionId = (int)$version['version_id'];
                    break;
                }
            }
        }

        $selected = null;
        foreach ($versions as $version) {
            if ((int)($version['version_id'] ?? 0) === $selectedVersionId) {
                $selected = $version;
                break;
            }
        }
        if ($selected === null && $active) {
            $selected = $active;
            $selectedVersionId = (int)($active['version_id'] ?? 0);
        }

        $params = $selectedVersionId > 0 ? Database::fetchAll($this->controlBranch, "
            SELECT
                PARAM_ID param_id,
                VERSION_ID version_id,
                GROUP_CODE group_code,
                PARAM_CODE param_code,
                NAME_EN name_en,
                NAME_AR name_ar,
                DESCRIPTION_EN description_en,
                DESCRIPTION_AR description_ar,
                VALUE_TYPE value_type,
                NUM_VALUE num_value,
                TEXT_VALUE text_value,
                BOOL_VALUE bool_value,
                MIN_NUM min_num,
                MAX_NUM max_num,
                UNIT_CODE unit_code,
                SORT_ORDER sort_order,
                IS_EDITABLE is_editable
            FROM DASH_DIAR_RULE_PARAMS
            WHERE VERSION_ID = :version_id
            ORDER BY GROUP_CODE, SORT_ORDER, PARAM_CODE
        ", ['version_id' => $selectedVersionId]) : [];

        $scopes = $selectedVersionId > 0 ? Database::fetchAll($this->controlBranch, "
            SELECT
                SCOPE_ID scope_id,
                SCOPE_TYPE scope_type,
                SCOPE_CODE scope_code,
                EFFECT_CODE effect_code,
                IS_ACTIVE is_active,
                NOTES notes
            FROM DASH_DIAR_RULE_SCOPES
            WHERE VERSION_ID = :version_id
              AND (SCOPE_TYPE <> 'BRANCH' OR SCOPE_CODE IN ('NJCH', 'MNH'))
            ORDER BY SCOPE_TYPE, SCOPE_CODE
        ", ['version_id' => $selectedVersionId]) : [];

        $branches = Database::fetchAll($this->controlBranch, "
            SELECT
                HOSPITAL_CODE hospital_code,
                NAME_EN name_en,
                NAME_AR name_ar,
                SOURCE_DB_NAME source_db_name,
                SOURCE_SCHEMA source_schema,
                IS_ENABLED is_enabled,
                IS_READ_ONLY is_read_only,
                CONTROL_DATA_FLAG control_data_flag,
                STATUS_CODE status_code
            FROM DASH_BRANCH_REGISTRY
            WHERE HOSPITAL_CODE IN ('NJCH', 'MNH')
            ORDER BY SORT_ORDER, HOSPITAL_CODE
        ");

        $dryRuns = $selectedVersionId > 0 ? Database::fetchAll($this->controlBranch, "
            SELECT * FROM (
                SELECT
                    DRY_RUN_ID dry_run_id,
                    STATUS_CODE status_code,
                    REQUESTED_BY requested_by,
                    TO_CHAR(REQUESTED_AT, 'YYYY-MM-DD HH24:MI:SS') requested_at,
                    TO_CHAR(COMPLETED_AT, 'YYYY-MM-DD HH24:MI:SS') completed_at,
                    BEFORE_CASES before_cases,
                    AFTER_CASES after_cases,
                    DELTA_CASES delta_cases,
                    ERROR_MESSAGE error_message
                FROM DASH_DIAR_DRY_RUNS
                WHERE VERSION_ID = :version_id
                ORDER BY REQUESTED_AT DESC
            ) WHERE ROWNUM <= 10
        ", ['version_id' => $selectedVersionId]) : [];

        $audit = Database::fetchAll($this->controlBranch, "
            SELECT * FROM (
                SELECT
                    AUDIT_ID audit_id,
                    ACTOR_USERNAME actor_username,
                    ACTION_CODE action_code,
                    TARGET_TYPE target_type,
                    TARGET_KEY target_key,
                    TO_CHAR(CREATED_AT, 'YYYY-MM-DD HH24:MI:SS') created_at
                FROM DASH_DIAR_AUDIT
                ORDER BY AUDIT_ID DESC
            ) WHERE ROWNUM <= 20
        ");

        return [
            'identity' => $identity,
            'active' => $active ?: [],
            'versions' => $versions,
            'selected' => $selected ?: [],
            'params' => $params,
            'scopes' => $scopes,
            'branches' => $branches,
            'dry_runs' => $dryRuns,
            'audit' => $audit,
        ];
    }

    public function createDraft(string $actor, string $reason): int
    {
        $actor = $this->requiredText($actor, 'Actor', 128);
        $reason = $this->requiredText($reason, 'Change reason', 1000);
        $existing = Database::fetchOne($this->controlBranch, "
            SELECT VERSION_ID version_id
            FROM DASH_DIAR_RULE_VERSIONS
            WHERE STATUS_CODE = 'DRAFT'
              AND ROWNUM = 1
        ");
        if ($existing) {
            throw new RuntimeException('DRAFT_VERSION_ALREADY_EXISTS');
        }
        $active = Database::fetchOne($this->controlBranch, "
            SELECT VERSION_ID version_id, RULESET_ID ruleset_id
            FROM DASH_DIAR_RULE_VERSIONS
            WHERE STATUS_CODE = 'ACTIVE'
        ");
        if (!$active) {
            throw new RuntimeException('ACTIVE_RULE_VERSION_NOT_FOUND');
        }
        $nextId = Database::fetchOne($this->controlBranch, "
            SELECT DASH_DIAR_RVER_SEQ.NEXTVAL version_id FROM dual
        ");
        $nextNo = Database::fetchOne($this->controlBranch, "
            SELECT NVL(MAX(VERSION_NO), 0) + 1 version_no
            FROM DASH_DIAR_RULE_VERSIONS
        ");
        $versionId = (int)($nextId['version_id'] ?? 0);
        $versionNo = (int)($nextNo['version_no'] ?? 0);
        if ($versionId <= 0 || $versionNo <= 0) {
            throw new RuntimeException('UNABLE_TO_ALLOCATE_RULE_VERSION');
        }
        $statements = [
            [
                'sql' => "INSERT INTO DASH_DIAR_RULE_VERSIONS (
                    VERSION_ID, RULESET_ID, VERSION_NO, STATUS_CODE,
                    PARENT_VERSION_ID, CONFIG_HASH, CHANGE_REASON,
                    CALIBRATION_STATUS, CREATED_BY, CREATED_AT
                )
                SELECT :new_id, RULESET_ID, :version_no, 'DRAFT',
                       VERSION_ID, CONFIG_HASH, :reason,
                       'PENDING_OPERATIONAL_FEEDBACK', :actor, SYSDATE
                FROM DASH_DIAR_RULE_VERSIONS
                WHERE VERSION_ID = :active_id",
                'params' => [
                    'new_id' => $versionId,
                    'version_no' => $versionNo,
                    'reason' => $reason,
                    'actor' => $actor,
                    'active_id' => (int)$active['version_id'],
                ],
            ],
            [
                'sql' => "INSERT INTO DASH_DIAR_RULE_PARAMS (
                    PARAM_ID, VERSION_ID, GROUP_CODE, PARAM_CODE,
                    NAME_EN, NAME_AR, DESCRIPTION_EN, DESCRIPTION_AR,
                    VALUE_TYPE, NUM_VALUE, TEXT_VALUE, BOOL_VALUE,
                    MIN_NUM, MAX_NUM, UNIT_CODE, SORT_ORDER, IS_EDITABLE
                )
                SELECT DASH_DIAR_PARAM_SEQ.NEXTVAL, :new_id, GROUP_CODE, PARAM_CODE,
                       NAME_EN, NAME_AR, DESCRIPTION_EN, DESCRIPTION_AR,
                       VALUE_TYPE, NUM_VALUE, TEXT_VALUE, BOOL_VALUE,
                       MIN_NUM, MAX_NUM, UNIT_CODE, SORT_ORDER, IS_EDITABLE
                FROM DASH_DIAR_RULE_PARAMS
                WHERE VERSION_ID = :active_id",
                'params' => ['new_id' => $versionId, 'active_id' => (int)$active['version_id']],
            ],
            [
                'sql' => "INSERT INTO DASH_DIAR_RULE_SCOPES (
                    SCOPE_ID, VERSION_ID, SCOPE_TYPE, SCOPE_CODE,
                    EFFECT_CODE, IS_ACTIVE, NOTES
                )
                SELECT DASH_DIAR_SCOPE_SEQ.NEXTVAL, :new_id, SCOPE_TYPE, SCOPE_CODE,
                       EFFECT_CODE, IS_ACTIVE, NOTES
                FROM DASH_DIAR_RULE_SCOPES
                WHERE VERSION_ID = :active_id",
                'params' => ['new_id' => $versionId, 'active_id' => (int)$active['version_id']],
            ],
            $this->auditStatement($actor, 'RULE_VERSION_CREATED', 'RULE_VERSION', (string)$versionId, '', $reason),
        ];
        Database::executeTransaction($this->controlBranch, $statements);
        return $versionId;
    }

    /** @param array<string,mixed> $paramInput @param array<string,mixed> $scopeInput */
    public function saveDraft(int $versionId, array $paramInput, array $scopeInput, string $actor, string $reason): void
    {
        $actor = $this->requiredText($actor, 'Actor', 128);
        $reason = $this->requiredText($reason, 'Change reason', 1000);
        $this->assertVersionStatus($versionId, 'DRAFT');
        $rows = Database::fetchAll($this->controlBranch, "
            SELECT PARAM_CODE param_code, VALUE_TYPE value_type,
                   MIN_NUM min_num, MAX_NUM max_num, IS_EDITABLE is_editable
            FROM DASH_DIAR_RULE_PARAMS
            WHERE VERSION_ID = :version_id
        ", ['version_id' => $versionId]);
        $statements = [];
        $canonical = [];
        foreach ($rows as $row) {
            $code = (string)$row['param_code'];
            if ((int)($row['is_editable'] ?? 0) !== 1) {
                continue;
            }
            if (!array_key_exists($code, $paramInput)) {
                throw new RuntimeException('MISSING_PARAMETER_' . $code);
            }
            $type = (string)$row['value_type'];
            $raw = $paramInput[$code];
            if ($type === 'NUMBER') {
                if (!is_numeric($raw)) {
                    throw new RuntimeException('INVALID_NUMBER_' . $code);
                }
                $value = (float)$raw;
                if ($row['min_num'] !== null && $value < (float)$row['min_num']) {
                    throw new RuntimeException('VALUE_BELOW_MIN_' . $code);
                }
                if ($row['max_num'] !== null && $value > (float)$row['max_num']) {
                    throw new RuntimeException('VALUE_ABOVE_MAX_' . $code);
                }
                $canonical[$code] = $value;
                $statements[] = [
                    'sql' => "UPDATE DASH_DIAR_RULE_PARAMS
                              SET NUM_VALUE = :num_value,
                                  TEXT_VALUE = NULL,
                                  BOOL_VALUE = NULL
                              WHERE VERSION_ID = :version_id
                                AND PARAM_CODE = :param_code",
                    'params' => ['num_value' => (string)$value, 'version_id' => $versionId, 'param_code' => $code],
                ];
            } elseif ($type === 'BOOLEAN') {
                $value = in_array((string)$raw, ['1','true','on','yes'], true) ? 1 : 0;
                $canonical[$code] = $value;
                $statements[] = [
                    'sql' => "UPDATE DASH_DIAR_RULE_PARAMS
                              SET BOOL_VALUE = :bool_value,
                                  NUM_VALUE = NULL,
                                  TEXT_VALUE = NULL
                              WHERE VERSION_ID = :version_id
                                AND PARAM_CODE = :param_code",
                    'params' => ['bool_value' => $value, 'version_id' => $versionId, 'param_code' => $code],
                ];
            } else {
                $value = trim((string)$raw);
                if (strlen($value) > 2000) {
                    throw new RuntimeException('TEXT_TOO_LONG_' . $code);
                }
                $canonical[$code] = $value;
                $statements[] = [
                    'sql' => "UPDATE DASH_DIAR_RULE_PARAMS
                              SET TEXT_VALUE = :text_value,
                                  NUM_VALUE = NULL,
                                  BOOL_VALUE = NULL
                              WHERE VERSION_ID = :version_id
                                AND PARAM_CODE = :param_code",
                    'params' => ['text_value' => $value, 'version_id' => $versionId, 'param_code' => $code],
                ];
            }
        }

        $statements[] = [
            'sql' => "UPDATE DASH_DIAR_RULE_SCOPES
                      SET EFFECT_CODE = 'EXCLUDE',
                          IS_ACTIVE = 0,
                          NOTES = 'Outside approved DIAR local-engine scope'
                      WHERE VERSION_ID = :version_id
                        AND SCOPE_TYPE = 'BRANCH'
                        AND SCOPE_CODE NOT IN ('NJCH', 'MNH')",
            'params' => ['version_id' => $versionId],
        ];
        $canonical['SCOPE_POLICY'] = 'NJCH_MNH_ONLY';
        $branches = Database::fetchAll($this->controlBranch, "
            SELECT HOSPITAL_CODE hospital_code
            FROM DASH_BRANCH_REGISTRY
            WHERE HOSPITAL_CODE IN ('NJCH', 'MNH')
            ORDER BY SORT_ORDER
        ");
        foreach ($branches as $branch) {
            $code = strtoupper((string)$branch['hospital_code']);
            $effect = isset($scopeInput[$code]) && (string)$scopeInput[$code] === 'INCLUDE'
                ? 'INCLUDE' : 'EXCLUDE';
            $canonical['SCOPE_' . $code] = $effect;
            $statements[] = [
                'sql' => "MERGE INTO DASH_DIAR_RULE_SCOPES target_scope
                    USING (SELECT :version_id version_id,
                                  'BRANCH' scope_type,
                                  :scope_code scope_code FROM dual) source_scope
                    ON (target_scope.VERSION_ID = source_scope.version_id
                        AND target_scope.SCOPE_TYPE = source_scope.scope_type
                        AND target_scope.SCOPE_CODE = source_scope.scope_code)
                    WHEN MATCHED THEN UPDATE SET
                        EFFECT_CODE = :effect_code,
                        IS_ACTIVE = 1
                    WHEN NOT MATCHED THEN INSERT (
                        SCOPE_ID, VERSION_ID, SCOPE_TYPE, SCOPE_CODE,
                        EFFECT_CODE, IS_ACTIVE, NOTES
                    ) VALUES (
                        DASH_DIAR_SCOPE_SEQ.NEXTVAL, :version_id,
                        'BRANCH', :scope_code, :effect_code, 1,
                        'Managed from DIAR rules page'
                    )",
                'params' => [
                    'version_id' => $versionId,
                    'scope_code' => $code,
                    'effect_code' => $effect,
                ],
            ];
        }
        ksort($canonical);
        $hash = hash('sha256', json_encode($canonical, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES));
        $statements[] = [
            'sql' => "UPDATE DASH_DIAR_RULE_VERSIONS
                      SET CONFIG_HASH = :config_hash,
                          CHANGE_REASON = :reason
                      WHERE VERSION_ID = :version_id
                        AND STATUS_CODE = 'DRAFT'",
            'params' => ['config_hash' => $hash, 'reason' => $reason, 'version_id' => $versionId],
        ];
        $statements[] = $this->auditStatement(
            $actor,
            'RULE_VERSION_UPDATED',
            'RULE_VERSION',
            (string)$versionId,
            '',
            json_encode(['reason' => $reason, 'config_hash' => $hash], JSON_UNESCAPED_UNICODE)
        );
        Database::executeTransaction($this->controlBranch, $statements);
    }

    public function requestDryRun(int $versionId, string $actor): int
    {
        $actor = $this->requiredText($actor, 'Actor', 128);
        $this->assertVersionStatus($versionId, 'DRAFT');
        $version = Database::fetchOne($this->controlBranch, "
            SELECT CONFIG_HASH config_hash
            FROM DASH_DIAR_RULE_VERSIONS
            WHERE VERSION_ID = :version_id
        ", ['version_id' => $versionId]);
        if (trim((string)($version['config_hash'] ?? '')) === '') {
            throw new RuntimeException('SAVE_DRAFT_BEFORE_DRY_RUN');
        }
        $active = Database::fetchOne($this->controlBranch, "
            SELECT VERSION_ID version_id
            FROM DASH_DIAR_RULE_VERSIONS
            WHERE STATUS_CODE = 'ACTIVE'
        ");
        $next = Database::fetchOne($this->controlBranch, "
            SELECT DASH_DIAR_DRY_SEQ.NEXTVAL dry_run_id FROM dual
        ");
        $dryRunId = (int)($next['dry_run_id'] ?? 0);
        Database::executeTransaction($this->controlBranch, [
            [
                'sql' => "INSERT INTO DASH_DIAR_DRY_RUNS (
                    DRY_RUN_ID, VERSION_ID, BASE_VERSION_ID, STATUS_CODE,
                    SOURCE_START_DATE, SOURCE_END_DATE,
                    REQUESTED_BY, REQUESTED_AT, RESULT_HASH
                ) VALUES (
                    :dry_run_id, :version_id, :base_version_id, 'PENDING',
                    TRUNC(SYSDATE) - 180, TRUNC(SYSDATE),
                    :actor, SYSDATE, :config_hash
                )",
                'params' => [
                    'dry_run_id' => $dryRunId,
                    'version_id' => $versionId,
                    'base_version_id' => (int)($active['version_id'] ?? 0),
                    'actor' => $actor,
                    'config_hash' => (string)$version['config_hash'],
                ],
            ],
            $this->auditStatement($actor, 'DRY_RUN_REQUESTED', 'DRY_RUN', (string)$dryRunId, '', (string)$versionId),
        ]);
        return $dryRunId;
    }

    public function approve(int $versionId, string $actor): void
    {
        $actor = $this->requiredText($actor, 'Actor', 128);
        $this->assertVersionStatus($versionId, 'DRAFT');
        $run = Database::fetchOne($this->controlBranch, "
            SELECT status_code, result_hash, config_hash
            FROM (
                SELECT d.STATUS_CODE status_code,
                       d.RESULT_HASH result_hash,
                       v.CONFIG_HASH config_hash,
                       d.REQUESTED_AT
                FROM DASH_DIAR_DRY_RUNS d
                JOIN DASH_DIAR_RULE_VERSIONS v
                  ON v.VERSION_ID = d.VERSION_ID
                WHERE d.VERSION_ID = :version_id
                ORDER BY d.REQUESTED_AT DESC
            ) WHERE ROWNUM = 1
        ", ['version_id' => $versionId]);
        if ((string)($run['status_code'] ?? '') !== 'PASS') {
            throw new RuntimeException('PASS_DRY_RUN_REQUIRED');
        }
        if ((string)($run['result_hash'] ?? '') === ''
            || !hash_equals((string)$run['config_hash'], (string)$run['result_hash'])) {
            throw new RuntimeException('DRY_RUN_CONFIGURATION_HASH_MISMATCH');
        }
        Database::executeTransaction($this->controlBranch, [
            [
                'sql' => "UPDATE DASH_DIAR_RULE_VERSIONS
                          SET STATUS_CODE = 'APPROVED',
                              APPROVED_BY = :actor,
                              APPROVED_AT = SYSDATE
                          WHERE VERSION_ID = :version_id
                            AND STATUS_CODE = 'DRAFT'",
                'params' => ['actor' => $actor, 'version_id' => $versionId],
            ],
            $this->auditStatement($actor, 'RULE_VERSION_APPROVED', 'RULE_VERSION', (string)$versionId, 'DRAFT', 'APPROVED'),
        ]);
    }

    public function activate(int $versionId, string $actor): void
    {
        $actor = $this->requiredText($actor, 'Actor', 128);
        $this->assertVersionStatus($versionId, 'APPROVED');
        Database::executeTransaction($this->controlBranch, [
            [
                'sql' => "UPDATE DASH_DIAR_RULE_VERSIONS
                          SET STATUS_CODE = 'RETIRED',
                              RETIRED_AT = SYSDATE
                          WHERE STATUS_CODE = 'ACTIVE'",
                'params' => [],
            ],
            [
                'sql' => "UPDATE DASH_DIAR_RULE_VERSIONS
                          SET STATUS_CODE = 'ACTIVE',
                              ACTIVATED_BY = :actor,
                              ACTIVATED_AT = SYSDATE,
                              RETIRED_AT = NULL
                          WHERE VERSION_ID = :version_id
                            AND STATUS_CODE = 'APPROVED'",
                'params' => ['actor' => $actor, 'version_id' => $versionId],
            ],
            $this->auditStatement($actor, 'RULE_VERSION_ACTIVATED', 'RULE_VERSION', (string)$versionId, 'APPROVED', 'ACTIVE'),
        ]);
    }

    private function assertVersionStatus(int $versionId, string $expected): void
    {
        $row = Database::fetchOne($this->controlBranch, "
            SELECT STATUS_CODE status_code
            FROM DASH_DIAR_RULE_VERSIONS
            WHERE VERSION_ID = :version_id
        ", ['version_id' => $versionId]);
        if (!$row) {
            throw new RuntimeException('RULE_VERSION_NOT_FOUND');
        }
        if ((string)$row['status_code'] !== $expected) {
            throw new RuntimeException('RULE_VERSION_STATUS_MUST_BE_' . $expected);
        }
    }

    /** @return array{sql:string,params:array<string,mixed>} */
    private function auditStatement(string $actor, string $action, string $type, string $key, string $old, string $new): array
    {
        return [
            'sql' => "INSERT INTO DASH_DIAR_AUDIT (
                AUDIT_ID, ACTOR_USERNAME, ACTION_CODE, TARGET_TYPE,
                TARGET_KEY, OLD_VALUE, NEW_VALUE, CREATED_AT
            ) VALUES (
                DASH_DIAR_AUDIT_SEQ.NEXTVAL, :actor, :action_code,
                :target_type, :target_key, :old_value, :new_value, SYSDATE
            )",
            'params' => [
                'actor' => substr($actor, 0, 128),
                'action_code' => substr($action, 0, 80),
                'target_type' => substr($type, 0, 40),
                'target_key' => substr($key, 0, 180),
                'old_value' => substr($old, 0, 4000),
                'new_value' => substr($new, 0, 4000),
            ],
        ];
    }

    private function requiredText(string $value, string $label, int $max): string
    {
        $value = trim($value);
        if ($value === '') {
            throw new RuntimeException(strtoupper(str_replace(' ', '_', $label)) . '_REQUIRED');
        }
        return substr($value, 0, $max);
    }
}
