<?php

declare(strict_types=1);

use App\Core\Database;
use App\Repositories\DiarInvestigationRepository;

const DIAR_6B_4_R2_RELEASE = 'DIAR-6B.4-R2';
const DIAR_6B_4_R2_BRANCH = 'njch';
const DIAR_6B_4_R2_QA_PREFIX = 'QA6B4R2_';

/** @param array<string,mixed> $options */
function diarR2Bootstrap(array $options = []): string
{
    $candidate = trim((string)($options['target'] ?? ''));
    if ($candidate === '') {
        $candidate = dirname(__DIR__);
    }
    $root = rtrim($candidate, "\\/");
    $bootstrap = $root . DIRECTORY_SEPARATOR . 'app' . DIRECTORY_SEPARATOR . 'bootstrap.php';
    if (!is_file($bootstrap)) {
        $root = rtrim('C:\\xampp\\htdocs\\ddb-new', "\\/");
        $bootstrap = $root . DIRECTORY_SEPARATOR . 'app' . DIRECTORY_SEPARATOR . 'bootstrap.php';
    }
    if (!is_file($bootstrap)) {
        throw new RuntimeException('TARGET_BOOTSTRAP_NOT_FOUND|' . $bootstrap);
    }
    require_once $bootstrap;
    return $root;
}

function diarR2Path(string ...$parts): string
{
    return preg_replace('~[\\\\/]+~', DIRECTORY_SEPARATOR, implode(DIRECTORY_SEPARATOR, $parts)) ?? '';
}

function diarR2EnsureDir(string $directory): void
{
    if (!is_dir($directory) && !mkdir($directory, 0777, true) && !is_dir($directory)) {
        throw new RuntimeException('MKDIR_FAILED|' . $directory);
    }
}

function diarR2StatePath(string $root): string
{
    return diarR2Path($root, 'storage', 'diar', 'DIAR_6B_4_R2_QA_STATE.json');
}

/** @param array<string,mixed> $state */
function diarR2SaveState(string $root, array $state): void
{
    $path = diarR2StatePath($root);
    diarR2EnsureDir(dirname($path));
    $json = json_encode($state, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
    if (!is_string($json) || file_put_contents($path, $json) === false) {
        throw new RuntimeException('QA_STATE_WRITE_FAILED|' . $path);
    }
}

/** @return array<string,mixed> */
function diarR2LoadState(string $root): array
{
    $path = diarR2StatePath($root);
    if (!is_file($path)) {
        throw new RuntimeException('QA_STATE_NOT_FOUND|' . $path);
    }
    $state = json_decode((string)file_get_contents($path), true);
    if (!is_array($state) || trim((string)($state['case_code'] ?? '')) === '') {
        throw new RuntimeException('QA_STATE_INVALID|' . $path);
    }
    return $state;
}

function diarR2DeleteState(string $root): void
{
    $path = diarR2StatePath($root);
    if (is_file($path)) {
        @unlink($path);
    }
}

/** @return array<string,mixed> */
function diarR2VerifyControlDatabase(): array
{
    $identity = Database::fetchOne(DIAR_6B_4_R2_BRANCH, "SELECT
        UPPER(SYS_CONTEXT('USERENV','DB_NAME')) DB_NAME,
        UPPER(SYS_CONTEXT('USERENV','CURRENT_SCHEMA')) CURRENT_SCHEMA,
        TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') SERVER_TIME
      FROM dual");
    if (strtoupper((string)($identity['db_name'] ?? '')) !== 'ORANJC'
        || strtoupper((string)($identity['current_schema'] ?? '')) !== 'WATANY') {
        throw new RuntimeException('CONTROL_DB_IDENTITY_REQUIRED_ORANJC_WATANY');
    }

    $objects = Database::fetchOne(DIAR_6B_4_R2_BRANCH, "SELECT
        (SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME IN (
            'DASH_DIAR_CASES','DASH_DIAR_CASE_ASSIGNMENTS','DASH_DIAR_CASE_HISTORY',
            'DASH_DIAR_CASE_NOTES','DASH_DIAR_CASE_EVIDENCE',
            'DASH_DIAR_CASE_INFO_REQUESTS','DASH_DIAR_CASE_FINDINGS',
            'DASH_DIAR_CASE_APPROVALS','DASH_DIAR_CORRECTIVE_ACTIONS'
        )) TABLE_COUNT,
        (SELECT COUNT(*) FROM USER_SEQUENCES WHERE SEQUENCE_NAME IN (
            'DASH_DIAR_CASE_SEQ','DASH_DIAR_ASSIGN_SEQ','DASH_DIAR_HISTORY_SEQ',
            'DASH_DIAR_NOTE_SEQ','DASH_DIAR_EVID_SEQ','DASH_DIAR_INFO_REQ_SEQ',
            'DASH_DIAR_FINDING_SEQ','DASH_DIAR_APPROVAL_SEQ','DASH_DIAR_CORR_ACTION_SEQ'
        )) SEQUENCE_COUNT,
        (SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME='DASH_DIAR_CASES'
          AND COLUMN_NAME IN ('WORKFLOW_STAGE_CODE','SUPERVISOR_USERNAME','MANAGEMENT_APPROVER','REOPEN_COUNT','LAST_ACTION_AT')) WORKFLOW_COLUMN_COUNT
      FROM dual");
    if ((int)($objects['table_count'] ?? 0) !== 9) {
        throw new RuntimeException('WORKFLOW_TABLES_REQUIRED_9');
    }
    if ((int)($objects['sequence_count'] ?? 0) !== 9) {
        throw new RuntimeException('WORKFLOW_SEQUENCES_REQUIRED_9');
    }
    if ((int)($objects['workflow_column_count'] ?? 0) !== 5) {
        throw new RuntimeException('WORKFLOW_COLUMNS_REQUIRED_5');
    }
    return ['identity' => $identity, 'objects' => $objects];
}

/** @return array<string,int> */
function diarR2ProtectedCounts(): array
{
    $wanted = [
        'DASH_DIAR_MONTH_SNAPSHOTS',
        'DASH_DIAR_MONTH_CURRENT',
        'DASH_DIAR_MONTH_DAYS',
        'DASH_DIAR_MONTH_CASES',
        'DASH_DIAR_SOURCE_CASES',
        'DASH_DIAR_CASE_MOVE_REFS',
        'DASH_DIAR_CASE_STAT_EVID',
        'DASH_DIAR_CASE_SEVID',
    ];
    $existing = Database::fetchAll(DIAR_6B_4_R2_BRANCH,
        "SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME IN ('" . implode("','", $wanted) . "') ORDER BY TABLE_NAME");
    $counts = [];
    foreach ($existing as $row) {
        $table = strtoupper((string)($row['table_name'] ?? ''));
        if (!preg_match('/^DASH_DIAR_[A-Z0-9_]+$/', $table)) {
            continue;
        }
        $count = Database::fetchOne(DIAR_6B_4_R2_BRANCH, 'SELECT COUNT(*) ROW_COUNT FROM ' . $table);
        $counts[$table] = (int)($count['row_count'] ?? 0);
    }
    ksort($counts);
    return $counts;
}

/** @param array<string,int> $before @param array<string,int> $after */
function diarR2AssertProtectedCounts(array $before, array $after): void
{
    if ($before !== $after) {
        throw new RuntimeException('FROZEN_SOURCE_COUNTS_CHANGED|before=' . json_encode($before) . '|after=' . json_encode($after));
    }
}

/** @return array<string,array<string,mixed>> */
function diarR2CaseColumns(): array
{
    $rows = Database::fetchAll(DIAR_6B_4_R2_BRANCH, "SELECT
        COLUMN_NAME,DATA_TYPE,DATA_LENGTH,NULLABLE,COLUMN_ID,VIRTUAL_COLUMN,HIDDEN_COLUMN
      FROM USER_TAB_COLS
      WHERE TABLE_NAME='DASH_DIAR_CASES'
        AND NVL(HIDDEN_COLUMN,'NO')='NO'
        AND NVL(VIRTUAL_COLUMN,'NO')='NO'
      ORDER BY COLUMN_ID");
    $columns = [];
    foreach ($rows as $row) {
        $name = strtoupper((string)($row['column_name'] ?? ''));
        if ($name !== '') {
            $columns[$name] = $row;
        }
    }
    foreach (['CASE_ID','CASE_CODE','SITE_CODE','STATUS_CODE','WORKFLOW_STAGE_CODE'] as $required) {
        if (!isset($columns[$required])) {
            throw new RuntimeException('CASE_COLUMN_REQUIRED|' . $required);
        }
    }
    return $columns;
}

/** @return array<string,array<int,string>> */
function diarR2CaseUniqueConstraints(): array
{
    $rows = Database::fetchAll(DIAR_6B_4_R2_BRANCH, "SELECT
        c.CONSTRAINT_NAME,cc.COLUMN_NAME,cc.POSITION
      FROM USER_CONSTRAINTS c
      JOIN USER_CONS_COLUMNS cc
        ON cc.CONSTRAINT_NAME=c.CONSTRAINT_NAME AND cc.TABLE_NAME=c.TABLE_NAME
      WHERE c.TABLE_NAME='DASH_DIAR_CASES' AND c.CONSTRAINT_TYPE IN ('P','U')
      ORDER BY c.CONSTRAINT_NAME,cc.POSITION");
    $out = [];
    foreach ($rows as $row) {
        $constraint = strtoupper((string)($row['constraint_name'] ?? ''));
        $column = strtoupper((string)($row['column_name'] ?? ''));
        if ($constraint !== '' && $column !== '') {
            $out[$constraint][] = $column;
        }
    }
    return $out;
}

/** @return array<string,mixed> */
function diarR2PickSourceCase(): array
{
    $row = Database::fetchOne(DIAR_6B_4_R2_BRANCH, "SELECT * FROM (
        SELECT c.CASE_ID,c.CASE_CODE,c.SITE_CODE,c.STORE_CODE,c.ENTITY_TYPE,
               c.WORKFLOW_STAGE_CODE,c.STATUS_CODE
        FROM DASH_DIAR_CASES c
        WHERE (c.STORE_CODE IS NULL OR c.STORE_CODE NOT IN (56,57))
          AND UPPER(NVL(c.ENTITY_TYPE,'CASE'))='CASE'
          AND UPPER(c.CASE_CODE) NOT LIKE 'QA6B4R2\\_%' ESCAPE '\\'
        ORDER BY CASE WHEN c.WORKFLOW_STAGE_CODE='NEW' THEN 0 ELSE 1 END,
                 c.CASE_ID DESC
      ) WHERE ROWNUM=1");
    if (!is_array($row) || (int)($row['case_id'] ?? 0) < 1) {
        throw new RuntimeException('GOVERNED_SOURCE_CASE_NOT_FOUND');
    }
    return $row;
}

function diarR2NewCaseCode(array $columns): string
{
    $maxLength = (int)($columns['CASE_CODE']['data_length'] ?? 64);
    if ($maxLength < strlen(DIAR_6B_4_R2_QA_PREFIX) + 4) {
        throw new RuntimeException('CASE_CODE_LENGTH_TOO_SMALL_FOR_QA_PREFIX|' . $maxLength);
    }
    $code = DIAR_6B_4_R2_QA_PREFIX . date('ymdHis') . '_' . strtoupper(substr(hash('sha256', microtime(true) . '|' . random_int(1, PHP_INT_MAX)), 0, 6));
    return substr($code, 0, $maxLength);
}

/** @return array<string,mixed> */
function diarR2CloneQaCase(string $root): array
{
    diarR2VerifyControlDatabase();
    $columns = diarR2CaseColumns();
    $source = diarR2PickSourceCase();
    $next = Database::fetchOne(DIAR_6B_4_R2_BRANCH, 'SELECT DASH_DIAR_CASE_SEQ.NEXTVAL CASE_ID FROM dual');
    $qaCaseId = (int)($next['case_id'] ?? 0);
    if ($qaCaseId < 1) {
        throw new RuntimeException('QA_CASE_SEQUENCE_FAILED');
    }
    $qaCaseCode = diarR2NewCaseCode($columns);
    $qaActor = 'DIAR6B4_R2_QA';

    $overrides = [
        'CASE_ID' => ':qa_case_id',
        'CASE_CODE' => ':qa_case_code',
        'STATUS_CODE' => "'NEW'",
        'WORKFLOW_STAGE_CODE' => "'NEW'",
        'DUE_AT' => 'SYSDATE-(1/24)',
        'CREATED_BY' => ':qa_actor',
        'UPDATED_BY' => ':qa_actor',
        'CREATED_AT' => 'SYSDATE',
        'UPDATED_AT' => 'SYSDATE',
        'LAST_ACTION_AT' => 'SYSDATE',
        'CLOSED_BY' => 'NULL',
        'CLOSED_AT' => 'NULL',
        'INVESTIGATION_OUTCOME' => 'NULL',
        'RESOLUTION_SUMMARY' => 'NULL',
        'ESCALATION_LEVEL' => 'NULL',
        'SUPERVISOR_USERNAME' => 'NULL',
        'MANAGEMENT_APPROVER' => 'NULL',
        'REOPEN_COUNT' => '0',
    ];
    if (isset($columns['REVIEW_REASON'])) {
        $limit = max(20, (int)($columns['REVIEW_REASON']['data_length'] ?? 4000));
        $overrides['REVIEW_REASON'] = "SUBSTR('QA runtime workflow acceptance clone',1," . $limit . ')';
    }

    foreach (diarR2CaseUniqueConstraints() as $constraint => $uniqueColumns) {
        if (in_array('CASE_ID', $uniqueColumns, true) || in_array('CASE_CODE', $uniqueColumns, true)) {
            continue;
        }
        $nullable = null;
        foreach ($uniqueColumns as $uniqueColumn) {
            if (($columns[$uniqueColumn]['nullable'] ?? 'N') === 'Y') {
                $nullable = $uniqueColumn;
                break;
            }
        }
        if ($nullable === null) {
            throw new RuntimeException('UNSUPPORTED_NON_NULL_UNIQUE_CONSTRAINT|' . $constraint . '|' . implode(',', $uniqueColumns));
        }
        $overrides[$nullable] = 'NULL';
    }

    $insertColumns = [];
    $selectExpressions = [];
    foreach ($columns as $name => $meta) {
        $insertColumns[] = $name;
        $selectExpressions[] = $overrides[$name] ?? ('s.' . $name);
    }
    $sql = 'INSERT INTO DASH_DIAR_CASES (' . implode(',', $insertColumns) . ') '
        . 'SELECT ' . implode(',', $selectExpressions) . ' FROM DASH_DIAR_CASES s WHERE s.CASE_ID=:source_case_id';
    Database::execute(DIAR_6B_4_R2_BRANCH, $sql, [
        'qa_case_id' => $qaCaseId,
        'qa_case_code' => $qaCaseCode,
        'qa_actor' => $qaActor,
        'source_case_id' => (int)$source['case_id'],
    ]);

    $qa = Database::fetchOne(DIAR_6B_4_R2_BRANCH, "SELECT CASE_ID,CASE_CODE,SITE_CODE,STORE_CODE,
        WORKFLOW_STAGE_CODE,STATUS_CODE,TO_CHAR(DUE_AT,'YYYY-MM-DD HH24:MI:SS') DUE_AT
      FROM DASH_DIAR_CASES WHERE CASE_ID=:case_id AND CASE_CODE=:case_code", [
        'case_id' => $qaCaseId,
        'case_code' => $qaCaseCode,
    ]);
    if (!is_array($qa)) {
        throw new RuntimeException('QA_CASE_INSERT_VERIFY_FAILED');
    }
    if (in_array((int)($qa['store_code'] ?? -1), [56,57], true)) {
        throw new RuntimeException('QA_CASE_EXCLUDED_STORE_FORBIDDEN');
    }

    $state = [
        'release' => DIAR_6B_4_R2_RELEASE,
        'case_id' => $qaCaseId,
        'case_code' => $qaCaseCode,
        'site_code' => strtoupper((string)$qa['site_code']),
        'source_case_id' => (int)$source['case_id'],
        'source_case_code' => (string)$source['case_code'],
        'created_at' => date(DATE_ATOM),
        'qa_actor' => $qaActor,
        'identity_fingerprint' => diarR2CaseIdentityFingerprint($qaCaseId),
        'protected_counts' => diarR2ProtectedCounts(),
    ];
    diarR2SaveState($root, $state);
    return $state;
}

/** @return array{hash:string,values:array<string,mixed>} */
function diarR2CaseIdentityFingerprint(int $caseId): array
{
    $columns = diarR2CaseColumns();
    $wanted = [
        'CASE_ID','CASE_CODE','ENTITY_TYPE','SITE_CODE','SOURCE_DB','SOURCE_CASE_ID',
        'MONTH_SNAPSHOT_ID','SNAPSHOT_ID','BRANCH_CODE','STORE_CODE','ITEM_CODE',
        'RAW_UNIT_CODE','STORETRANSCODE','STORETRANSEFFECT','CALENDAR_DAY',
        'CANDIDATE_ID','RULE_VERSION_ID','EVENT_TYPE','EVENT_FAMILY','PARENT_EVENT_CASE_ID'
    ];
    $select = [];
    foreach ($wanted as $name) {
        if (!isset($columns[$name])) {
            continue;
        }
        $type = strtoupper((string)($columns[$name]['data_type'] ?? ''));
        if (strpos($type, 'DATE') !== false || strpos($type, 'TIMESTAMP') !== false) {
            $select[] = "TO_CHAR($name,'YYYY-MM-DD HH24:MI:SS') $name";
        } else {
            $select[] = $name;
        }
    }
    if ($select === []) {
        throw new RuntimeException('IDENTITY_COLUMNS_UNAVAILABLE');
    }
    $row = Database::fetchOne(DIAR_6B_4_R2_BRANCH,
        'SELECT ' . implode(',', $select) . ' FROM DASH_DIAR_CASES WHERE CASE_ID=:case_id',
        ['case_id' => $caseId]);
    if (!is_array($row)) {
        throw new RuntimeException('QA_CASE_IDENTITY_NOT_FOUND');
    }
    ksort($row);
    $json = json_encode($row, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
    if (!is_string($json)) {
        throw new RuntimeException('IDENTITY_JSON_FAILED');
    }
    return ['hash' => hash('sha256', $json), 'values' => $row];
}

/** @return array<string,mixed> */
function diarR2FindAssignableUser(string $siteCode): array
{
    $site = strtolower(trim($siteCode));
    $row = Database::fetchOne(DIAR_6B_4_R2_BRANCH, "SELECT * FROM (
      SELECT su.NAME USERNAME,
        CASE WHEN UPPER(su.NAME)='7124' THEN 0 ELSE 1 END SORT_ORDER
      FROM SECURITY_USERS su
      WHERE EXISTS (
        SELECT 1 FROM DASH_ACL_USER_ROLES ur
        JOIN DASH_ACL_ROLES r ON r.ROLE_CODE=ur.ROLE_CODE
        WHERE UPPER(ur.USERNAME)=UPPER(su.NAME)
          AND NVL(ur.IS_ACTIVE,1)=1 AND NVL(r.IS_ACTIVE,1)=1
          AND (UPPER(r.ROLE_CODE)='SITE_ADMIN' OR NVL(r.ALL_BRANCHES,0)=1 OR EXISTS (
            SELECT 1 FROM DASH_ACL_USER_BRANCHES ub
            WHERE UPPER(ub.USERNAME)=UPPER(ur.USERNAME)
              AND LOWER(ub.HOSPITAL_CODE)=:site_code_1 AND NVL(ub.IS_ACTIVE,1)=1
          ))
      )
      OR (
        NOT EXISTS (SELECT 1 FROM DASH_ACL_USER_ROLES ur2 WHERE UPPER(ur2.USERNAME)=UPPER(su.NAME) AND NVL(ur2.IS_ACTIVE,1)=1)
        AND NVL(su.DASHBOARD_ROLE,0)=1
      )
      ORDER BY SORT_ORDER,su.NAME
    ) WHERE ROWNUM=1", ['site_code_1' => $site]);
    if (!is_array($row) || trim((string)($row['username'] ?? '')) === '') {
        throw new RuntimeException('NO_ASSIGNABLE_USER_FOR_SITE|' . strtoupper($siteCode));
    }
    return $row;
}

function diarR2Stage(string $caseCode): string
{
    $row = Database::fetchOne(DIAR_6B_4_R2_BRANCH,
        'SELECT WORKFLOW_STAGE_CODE FROM DASH_DIAR_CASES WHERE CASE_CODE=:case_code',
        ['case_code' => $caseCode]);
    if (!is_array($row)) {
        throw new RuntimeException('QA_CASE_NOT_FOUND|' . $caseCode);
    }
    return strtoupper((string)($row['workflow_stage_code'] ?? ''));
}

function diarR2AssertStage(string $caseCode, string $expected, array &$stagePath): void
{
    $actual = diarR2Stage($caseCode);
    if ($actual !== strtoupper($expected)) {
        throw new RuntimeException('WORKFLOW_STAGE_MISMATCH|expected=' . strtoupper($expected) . '|actual=' . $actual);
    }
    $stagePath[] = $actual;
}

function diarR2ExpectException(callable $callback, string $expectedFragment, string $gate, array &$blocked): void
{
    try {
        $callback();
    } catch (Throwable $error) {
        if (strpos($error->getMessage(), $expectedFragment) === false) {
            throw new RuntimeException($gate . '|WRONG_ERROR|' . $error->getMessage());
        }
        $blocked[] = $gate . ':' . $expectedFragment;
        return;
    }
    throw new RuntimeException($gate . '|EXPECTED_BLOCK_DID_NOT_OCCUR');
}

/** @return array<string,mixed> */
function diarR2RoleMatrix(): array
{
    $tables = Database::fetchOne(DIAR_6B_4_R2_BRANCH,
        "SELECT COUNT(*) OBJECT_COUNT FROM USER_TABLES WHERE TABLE_NAME IN ('DASH_ACL_ROLES','DASH_ACL_ROLE_OVERRIDES')");
    if ((int)($tables['object_count'] ?? 0) !== 2) {
        throw new RuntimeException('ACL_ROLE_TABLES_REQUIRED');
    }
    $roles = ['DIAR_INVESTIGATOR','DIAR_INV_SUPERVISOR','DIAR_AUDIT_DIRECTOR','DIAR_READ_ONLY_AUDITOR'];
    $rows = Database::fetchAll(DIAR_6B_4_R2_BRANCH,
        "SELECT r.ROLE_CODE,o.PERMISSION_CODE,UPPER(o.EFFECT) EFFECT
         FROM DASH_ACL_ROLES r
         LEFT JOIN DASH_ACL_ROLE_OVERRIDES o ON o.ROLE_CODE=r.ROLE_CODE
         WHERE r.ROLE_CODE IN ('" . implode("','", $roles) . "')");
    $presentRoles = [];
    $effect = [];
    foreach ($rows as $row) {
        $role = strtoupper((string)($row['role_code'] ?? ''));
        if ($role === '') continue;
        $presentRoles[$role] = true;
        $permission = trim((string)($row['permission_code'] ?? ''));
        if ($permission !== '') {
            $effect[$role][$permission] = strtoupper((string)($row['effect'] ?? ''));
        }
    }
    foreach ($roles as $role) {
        if (!isset($presentRoles[$role])) {
            throw new RuntimeException('ACL_ROLE_MISSING|' . $role);
        }
    }
    $required = [
        'DIAR_INVESTIGATOR' => [
            'diar.investigation.view','diar.investigation.review','diar.investigation.evidence',
            'diar.investigation.request_information','diar.investigation.finding.propose'
        ],
        'DIAR_INV_SUPERVISOR' => [
            'diar.investigation.view','diar.investigation.assign','diar.investigation.supervisor_review',
            'diar.investigation.corrective_action','diar.investigation.close'
        ],
        'DIAR_AUDIT_DIRECTOR' => [
            'diar.investigation.view','diar.investigation.management_approve','diar.investigation.reopen'
        ],
        'DIAR_READ_ONLY_AUDITOR' => ['diar.investigation.view'],
    ];
    foreach ($required as $role => $permissions) {
        foreach ($permissions as $permission) {
            if (($effect[$role][$permission] ?? '') !== 'ALLOW') {
                throw new RuntimeException('REQUIRED_ROLE_GRANT_MISSING|' . $role . '|' . $permission);
            }
        }
    }
    $forbiddenAllows = [
        'DIAR_INVESTIGATOR' => [
            'diar.investigation.assign','diar.investigation.supervisor_review',
            'diar.investigation.management_approve','diar.investigation.corrective_action',
            'diar.investigation.close','diar.investigation.reopen'
        ],
        'DIAR_INV_SUPERVISOR' => ['diar.investigation.management_approve','diar.investigation.reopen'],
        'DIAR_READ_ONLY_AUDITOR' => [
            'diar.investigation.assign','diar.investigation.review','diar.investigation.evidence',
            'diar.investigation.request_information','diar.investigation.finding.propose',
            'diar.investigation.supervisor_review','diar.investigation.management_approve',
            'diar.investigation.corrective_action','diar.investigation.close','diar.investigation.reopen'
        ],
    ];
    foreach ($forbiddenAllows as $role => $permissions) {
        foreach ($permissions as $permission) {
            if (($effect[$role][$permission] ?? '') === 'ALLOW') {
                throw new RuntimeException('FORBIDDEN_ROLE_ALLOW|' . $role . '|' . $permission);
            }
        }
    }
    return [
        'roles' => $roles,
        'required_grants' => array_sum(array_map('count', $required)),
        'forbidden_allows' => 0,
    ];
}

/** @return array<string,mixed> */
function diarR2RunWorkflow(string $root, array $state): array
{
    $caseCode = trim((string)$state['case_code']);
    $caseId = (int)$state['case_id'];
    $site = strtoupper((string)$state['site_code']);
    if ($caseCode === '' || $caseId < 1 || !in_array($site, ['NJCH','MNH'], true)) {
        throw new RuntimeException('QA_STATE_FIELDS_INVALID');
    }
    $repo = new DiarInvestigationRepository();
    if (!$repo->workflowSchemaReady()) {
        throw new RuntimeException('WORKFLOW_SCHEMA_NOT_READY');
    }
    $allowedSites = [$site];
    $assignee = (string)(diarR2FindAssignableUser($site)['username'] ?? '');
    $investigator = $assignee;
    $supervisor = 'QA_SUPERVISOR_R2';
    $manager = 'QA_MANAGEMENT_R2';
    $stagePath = [];
    $blocked = [];
    $identityBefore = diarR2CaseIdentityFingerprint($caseId);
    $protectedBefore = is_array($state['protected_counts'] ?? null)
        ? $state['protected_counts']
        : diarR2ProtectedCounts();

    diarR2AssertStage($caseCode, 'NEW', $stagePath);
    $sla = Database::fetchOne(DIAR_6B_4_R2_BRANCH,
        "SELECT CASE WHEN DUE_AT<SYSDATE THEN 'OVERDUE' ELSE 'NOT_OVERDUE' END SLA_CODE
         FROM DASH_DIAR_CASES WHERE CASE_ID=:case_id", ['case_id' => $caseId]);
    if (($sla['sla_code'] ?? '') !== 'OVERDUE') {
        throw new RuntimeException('SLA_OVERDUE_SEED_FAILED');
    }

    diarR2ExpectException(function () use ($repo,$caseCode,$investigator,$allowedSites): void {
        $repo->startReview($caseCode, $investigator, $allowedSites);
    }, 'WORKFLOW_STAGE_NOT_ALLOWED_NEW_TO_UNDER_REVIEW', 'BLOCK_START_BEFORE_ASSIGNMENT', $blocked);
    diarR2ExpectException(function () use ($repo,$caseCode,$investigator,$allowedSites): void {
        $repo->proposeFinding($caseCode, 'POLICY_VIOLATION', 'Premature finding', 'Premature root cause', '', true, $investigator, $allowedSites);
    }, 'WORKFLOW_STAGE_NOT_ALLOWED_NEW_TO_FINDING_PROPOSED', 'BLOCK_FINDING_BEFORE_REVIEW', $blocked);

    $repo->assign($caseCode, $assignee, 'DIAR-6B.4-R2 controlled runtime acceptance.', 'QA_ASSIGNER_R2', $allowedSites);
    diarR2AssertStage($caseCode, 'ASSIGNED', $stagePath);
    $repo->startReview($caseCode, $investigator, $allowedSites);
    diarR2AssertStage($caseCode, 'UNDER_REVIEW', $stagePath);

    $repo->addNote($caseCode, 'REVIEW', 'QA review note for controlled runtime acceptance.', true, $investigator, $allowedSites);
    $repo->addEvidence($caseCode, [
        'evidence_type' => 'SYSTEM_LOG',
        'reference_no' => 'DIAR-6B.4-R2',
        'description' => 'Controlled QA evidence row. No operational movement table was modified.',
        'file_name' => 'diar6b4-r2-qa-evidence.txt',
        'storage_path' => 'qa://diar/6b4/r2/' . $caseCode,
        'file_sha256' => hash('sha256', DIAR_6B_4_R2_RELEASE . '|' . $caseCode),
    ], $investigator, $allowedSites);

    $repo->requestInformation(
        $caseCode,
        'QA Pharmacy Operations',
        'Provide the controlled operational explanation for the selected frozen case.',
        date('Y-m-d H:i:s', time() + 86400),
        $investigator,
        $allowedSites
    );
    diarR2AssertStage($caseCode, 'INFORMATION_REQUIRED', $stagePath);
    $request = Database::fetchOne(DIAR_6B_4_R2_BRANCH,
        "SELECT * FROM (SELECT REQUEST_ID FROM DASH_DIAR_CASE_INFO_REQUESTS
         WHERE CASE_ID=:case_id AND STATUS_CODE='OPEN' ORDER BY REQUEST_ID DESC) WHERE ROWNUM=1",
        ['case_id' => $caseId]);
    $requestId = (int)($request['request_id'] ?? 0);
    if ($requestId < 1) throw new RuntimeException('QA_INFORMATION_REQUEST_NOT_FOUND');
    $repo->respondInformation($caseCode, $requestId,
        'Controlled QA response confirms the workflow path only; no operational conclusion is asserted.',
        'QA_INFORMATION_PROVIDER_R2', $allowedSites);
    diarR2AssertStage($caseCode, 'UNDER_REVIEW', $stagePath);

    diarR2ExpectException(function () use ($repo,$caseCode,$manager,$allowedSites): void {
        $repo->managementDecision($caseCode, 'APPROVED', 'Premature management approval.', $manager, $allowedSites);
    }, 'MANAGEMENT_APPROVAL_STAGE_REQUIRED', 'BLOCK_MANAGEMENT_BEFORE_SUPERVISOR', $blocked);

    $repo->proposeFinding(
        $caseCode,
        'POLICY_VIOLATION',
        'QA finding proposed for runtime acceptance.',
        'Controlled test root cause; not a finding against a real transaction or person.',
        'Complete the controlled corrective action and close the QA case.',
        true,
        $investigator,
        $allowedSites
    );
    diarR2AssertStage($caseCode, 'FINDING_PROPOSED', $stagePath);
    diarR2ExpectException(function () use ($repo,$caseCode,$supervisor,$allowedSites): void {
        $repo->supervisorDecision($caseCode, 'APPROVED', 'Premature supervisor decision.', $supervisor, $allowedSites);
    }, 'SUPERVISOR_DECISION_STAGE_REQUIRED', 'BLOCK_SUPERVISOR_BEFORE_REVIEW_STAGE', $blocked);

    $repo->startSupervisorReview($caseCode, $supervisor, $allowedSites);
    diarR2AssertStage($caseCode, 'SUPERVISOR_REVIEW', $stagePath);
    diarR2ExpectException(function () use ($repo,$caseCode,$investigator,$allowedSites): void {
        $repo->supervisorDecision($caseCode, 'APPROVED', 'Self approval must be blocked.', $investigator, $allowedSites);
    }, 'SEPARATION_OF_DUTIES_PROPOSER_CANNOT_SUPERVISE', 'BLOCK_PROPOSER_SELF_SUPERVISION', $blocked);
    diarR2AssertStage($caseCode, 'SUPERVISOR_REVIEW', $stagePath);
    $repo->supervisorDecision($caseCode, 'APPROVED', 'QA supervisor approval.', $supervisor, $allowedSites);
    diarR2AssertStage($caseCode, 'MANAGEMENT_APPROVAL', $stagePath);

    diarR2ExpectException(function () use ($repo,$caseCode,$investigator,$allowedSites): void {
        $repo->managementDecision($caseCode, 'APPROVED', 'Proposer management approval must be blocked.', $investigator, $allowedSites);
    }, 'SEPARATION_OF_DUTIES_MANAGEMENT_ACTOR_CONFLICT', 'BLOCK_PROPOSER_MANAGEMENT_APPROVAL', $blocked);
    diarR2ExpectException(function () use ($repo,$caseCode,$supervisor,$allowedSites): void {
        $repo->managementDecision($caseCode, 'APPROVED', 'Supervisor management approval must be blocked.', $supervisor, $allowedSites);
    }, 'SEPARATION_OF_DUTIES_MANAGEMENT_ACTOR_CONFLICT', 'BLOCK_SUPERVISOR_MANAGEMENT_APPROVAL', $blocked);
    diarR2AssertStage($caseCode, 'MANAGEMENT_APPROVAL', $stagePath);
    $repo->managementDecision($caseCode, 'APPROVED', 'QA management approval with corrective action.', $manager, $allowedSites);
    diarR2AssertStage($caseCode, 'CORRECTIVE_ACTION', $stagePath);

    $repo->createCorrectiveAction(
        $caseCode,
        'QA controlled corrective action',
        'Controlled action created only to validate the DIAR workflow lifecycle.',
        'QA_ACTION_OWNER_R2',
        date('Y-m-d H:i:s', time() + 172800),
        $supervisor,
        $allowedSites
    );
    diarR2ExpectException(function () use ($repo,$caseCode,$supervisor,$allowedSites): void {
        $repo->closeCase($caseCode, 'Close must be blocked while the corrective action is open.', $supervisor, $allowedSites);
    }, 'OPEN_CORRECTIVE_ACTIONS_PREVENT_CLOSE', 'BLOCK_CLOSE_WITH_OPEN_CORRECTIVE_ACTION', $blocked);

    $action = Database::fetchOne(DIAR_6B_4_R2_BRANCH,
        'SELECT * FROM (SELECT ACTION_ID FROM DASH_DIAR_CORRECTIVE_ACTIONS WHERE CASE_ID=:case_id ORDER BY ACTION_ID DESC) WHERE ROWNUM=1',
        ['case_id' => $caseId]);
    $actionId = (int)($action['action_id'] ?? 0);
    if ($actionId < 1) throw new RuntimeException('QA_CORRECTIVE_ACTION_NOT_FOUND');
    $repo->updateCorrectiveAction($caseCode, $actionId, 'IN_PROGRESS', 'QA action started.', $supervisor, $allowedSites);
    $repo->updateCorrectiveAction($caseCode, $actionId, 'COMPLETED', 'QA action completed with no operational change.', $supervisor, $allowedSites);
    $repo->closeCase($caseCode, 'First QA lifecycle closed after corrective action completion.', $supervisor, $allowedSites);
    diarR2AssertStage($caseCode, 'CLOSED', $stagePath);

    $repo->reopenCase($caseCode, 'QA reopen control proof.', $manager, $allowedSites);
    diarR2AssertStage($caseCode, 'UNDER_REVIEW', $stagePath);
    $repo->proposeFinding(
        $caseCode,
        'AUTHORIZED_ACTIVITY',
        'Second QA finding confirms the no-corrective-action closure path.',
        'Controlled acceptance path.',
        'No corrective action required.',
        false,
        $investigator,
        $allowedSites
    );
    diarR2AssertStage($caseCode, 'FINDING_PROPOSED', $stagePath);
    $repo->startSupervisorReview($caseCode, $supervisor, $allowedSites);
    diarR2AssertStage($caseCode, 'SUPERVISOR_REVIEW', $stagePath);
    $repo->supervisorDecision($caseCode, 'APPROVED', 'Second QA supervisor approval.', $supervisor, $allowedSites);
    diarR2AssertStage($caseCode, 'MANAGEMENT_APPROVAL', $stagePath);
    $repo->managementDecision($caseCode, 'APPROVED', 'Second QA management approval; auto-close path.', $manager, $allowedSites);
    diarR2AssertStage($caseCode, 'CLOSED', $stagePath);

    $final = Database::fetchOne(DIAR_6B_4_R2_BRANCH, "SELECT
        WORKFLOW_STAGE_CODE,STATUS_CODE,REOPEN_COUNT,CLOSED_BY,SUPERVISOR_USERNAME,MANAGEMENT_APPROVER,
        (SELECT COUNT(*) FROM DASH_DIAR_CASE_ASSIGNMENTS a WHERE a.CASE_ID=c.CASE_ID AND a.STATUS_CODE='ACTIVE') ACTIVE_ASSIGNMENTS,
        (SELECT COUNT(*) FROM DASH_DIAR_CASE_INFO_REQUESTS r WHERE r.CASE_ID=c.CASE_ID AND r.STATUS_CODE='RESPONDED') RESPONDED_REQUESTS,
        (SELECT COUNT(*) FROM DASH_DIAR_CASE_FINDINGS f WHERE f.CASE_ID=c.CASE_ID) FINDINGS,
        (SELECT COUNT(*) FROM DASH_DIAR_CASE_APPROVALS p WHERE p.CASE_ID=c.CASE_ID) APPROVALS,
        (SELECT COUNT(*) FROM DASH_DIAR_CORRECTIVE_ACTIONS a WHERE a.CASE_ID=c.CASE_ID AND a.STATUS_CODE='COMPLETED') COMPLETED_ACTIONS,
        (SELECT COUNT(*) FROM DASH_DIAR_CASE_EVIDENCE e WHERE e.CASE_ID=c.CASE_ID) EVIDENCE_ROWS,
        (SELECT COUNT(*) FROM DASH_DIAR_CASE_NOTES n WHERE n.CASE_ID=c.CASE_ID) NOTE_ROWS,
        (SELECT COUNT(*) FROM DASH_DIAR_CASE_HISTORY h WHERE h.CASE_ID=c.CASE_ID) HISTORY_ROWS
      FROM DASH_DIAR_CASES c WHERE c.CASE_ID=:case_id", ['case_id' => $caseId]);
    if (!is_array($final)) throw new RuntimeException('QA_FINAL_CASE_NOT_FOUND');
    $expected = [
        'workflow_stage_code' => 'CLOSED',
        'status_code' => 'CLOSED',
        'reopen_count' => 1,
        'active_assignments' => 1,
        'responded_requests' => 1,
        'findings' => 2,
        'approvals' => 4,
        'completed_actions' => 1,
        'evidence_rows' => 1,
        'note_rows' => 1,
    ];
    foreach ($expected as $key => $value) {
        $actual = is_int($value) ? (int)($final[$key] ?? -1) : strtoupper((string)($final[$key] ?? ''));
        if ($actual !== $value) {
            throw new RuntimeException('QA_FINAL_CONTRACT_MISMATCH|' . $key . '|expected=' . $value . '|actual=' . $actual);
        }
    }
    if ((int)($final['history_rows'] ?? 0) < 19) {
        throw new RuntimeException('AUDIT_HISTORY_INCOMPLETE|' . (int)($final['history_rows'] ?? 0));
    }
    if (strcasecmp((string)($final['closed_by'] ?? ''), $manager) !== 0
        || strcasecmp((string)($final['supervisor_username'] ?? ''), $supervisor) !== 0
        || strcasecmp((string)($final['management_approver'] ?? ''), $manager) !== 0) {
        throw new RuntimeException('ACTOR_TRACEABILITY_MISMATCH');
    }

    $history = Database::fetchAll(DIAR_6B_4_R2_BRANCH,
        'SELECT ACTION_CODE,PERFORMED_BY,TO_CHAR(PERFORMED_AT,\'YYYY-MM-DD HH24:MI:SS\') PERFORMED_AT FROM DASH_DIAR_CASE_HISTORY WHERE CASE_ID=:case_id ORDER BY HISTORY_ID',
        ['case_id' => $caseId]);
    $actions = [];
    foreach ($history as $row) $actions[] = strtoupper((string)($row['action_code'] ?? ''));
    foreach ([
        'ASSIGNED','REVIEW_STARTED','NOTE_ADDED','EVIDENCE_ADDED','INFORMATION_REQUESTED','INFORMATION_RESPONDED',
        'FINDING_PROPOSED','SUPERVISOR_REVIEW_STARTED','SUPERVISOR_APPROVED','MANAGEMENT_APPROVED',
        'CORRECTIVE_ACTION_CREATED','CORRECTIVE_ACTION_IN_PROGRESS','CORRECTIVE_ACTION_COMPLETED','CASE_CLOSED','CASE_REOPENED'
    ] as $requiredAction) {
        if (!in_array($requiredAction, $actions, true)) {
            throw new RuntimeException('AUDIT_ACTION_MISSING|' . $requiredAction);
        }
    }

    $identityAfter = diarR2CaseIdentityFingerprint($caseId);
    if (($identityBefore['hash'] ?? '') !== ($identityAfter['hash'] ?? '')) {
        throw new RuntimeException('FROZEN_CASE_IDENTITY_CHANGED');
    }
    $protectedAfter = diarR2ProtectedCounts();
    diarR2AssertProtectedCounts($protectedBefore, $protectedAfter);
    $roleMatrix = diarR2RoleMatrix();

    return [
        'release' => DIAR_6B_4_R2_RELEASE,
        'case_code' => $caseCode,
        'case_id' => $caseId,
        'site_code' => $site,
        'assignee' => $assignee,
        'actors' => ['investigator'=>$investigator,'supervisor'=>$supervisor,'management'=>$manager],
        'stage_path' => $stagePath,
        'blocked_actions' => $blocked,
        'final' => $final,
        'history_actions' => $actions,
        'identity_hash' => $identityAfter['hash'],
        'protected_counts' => $protectedAfter,
        'role_matrix' => $roleMatrix,
    ];
}

/** @return array<int,array<string,mixed>> */
function diarR2QaCases(): array
{
    return Database::fetchAll(DIAR_6B_4_R2_BRANCH,
        "SELECT CASE_ID,CASE_CODE,SITE_CODE,WORKFLOW_STAGE_CODE,STATUS_CODE,CREATED_AT
         FROM DASH_DIAR_CASES WHERE UPPER(CASE_CODE) LIKE 'QA6B4R2\\_%' ESCAPE '\\'
         ORDER BY CASE_ID");
}

/** @return array{cases:int,child_rows:int} */
function diarR2CleanupQaCases(string $root): array
{
    diarR2VerifyControlDatabase();
    $cases = diarR2QaCases();
    $deletedChildren = 0;
    foreach ($cases as $case) {
        $caseId = (int)($case['case_id'] ?? 0);
        if ($caseId < 1) continue;
        $known = [
            ['DASH_DIAR_CASE_APPROVALS','CASE_ID'],
            ['DASH_DIAR_CORRECTIVE_ACTIONS','CASE_ID'],
            ['DASH_DIAR_CASE_FINDINGS','CASE_ID'],
            ['DASH_DIAR_CASE_INFO_REQUESTS','CASE_ID'],
            ['DASH_DIAR_CASE_EVIDENCE','CASE_ID'],
            ['DASH_DIAR_CASE_NOTES','CASE_ID'],
            ['DASH_DIAR_CASE_HISTORY','CASE_ID'],
            ['DASH_DIAR_CASE_ASSIGNMENTS','CASE_ID'],
        ];
        $fkRows = Database::fetchAll(DIAR_6B_4_R2_BRANCH, "SELECT
            c.TABLE_NAME,cc.COLUMN_NAME
          FROM USER_CONSTRAINTS c
          JOIN USER_CONS_COLUMNS cc ON cc.CONSTRAINT_NAME=c.CONSTRAINT_NAME AND cc.TABLE_NAME=c.TABLE_NAME
          JOIN USER_CONSTRAINTS p ON p.CONSTRAINT_NAME=c.R_CONSTRAINT_NAME
          JOIN USER_CONS_COLUMNS pc ON pc.CONSTRAINT_NAME=p.CONSTRAINT_NAME AND pc.POSITION=cc.POSITION
          WHERE c.CONSTRAINT_TYPE='R' AND p.TABLE_NAME='DASH_DIAR_CASES' AND pc.COLUMN_NAME='CASE_ID'");
        $targets = [];
        foreach (array_merge($known, array_map(static function (array $row): array {
            return [strtoupper((string)($row['table_name'] ?? '')), strtoupper((string)($row['column_name'] ?? ''))];
        }, $fkRows)) as $target) {
            $table = strtoupper((string)($target[0] ?? ''));
            $column = strtoupper((string)($target[1] ?? ''));
            if (!preg_match('/^DASH_DIAR_[A-Z0-9_]+$/', $table) || !preg_match('/^[A-Z0-9_]+$/', $column)) continue;
            if ($table === 'DASH_DIAR_CASES') continue;
            $targets[$table . '|' . $column] = [$table,$column];
        }
        foreach ($targets as [$table,$column]) {
            $count = Database::fetchOne(DIAR_6B_4_R2_BRANCH,
                'SELECT COUNT(*) ROW_COUNT FROM ' . $table . ' WHERE ' . $column . '=:case_id', ['case_id'=>$caseId]);
            $deletedChildren += (int)($count['row_count'] ?? 0);
            Database::execute(DIAR_6B_4_R2_BRANCH,
                'DELETE FROM ' . $table . ' WHERE ' . $column . '=:case_id', ['case_id'=>$caseId]);
        }
        Database::execute(DIAR_6B_4_R2_BRANCH,
            'DELETE FROM DASH_DIAR_CASES WHERE CASE_ID=:case_id AND UPPER(CASE_CODE) LIKE \'QA6B4R2\\_%\' ESCAPE \'\\\'',
            ['case_id'=>$caseId]);
    }
    diarR2DeleteState($root);
    $remaining = diarR2QaCases();
    if ($remaining !== []) {
        throw new RuntimeException('QA_CLEANUP_INCOMPLETE|' . count($remaining));
    }
    return ['cases'=>count($cases),'child_rows'=>$deletedChildren];
}

/** @return array<string,mixed> */
function diarR2ReadOnlyDiagnostics(): array
{
    $base = diarR2VerifyControlDatabase();
    $integrity = Database::fetchOne(DIAR_6B_4_R2_BRANCH, "SELECT
        COUNT(*) RAW_TOTAL_CASES,
        SUM(CASE WHEN STORE_CODE IN (56,57) THEN 1 ELSE 0 END) EXCLUDED_STORE_CASES,
        SUM(CASE WHEN STORE_CODE IS NULL OR STORE_CODE NOT IN (56,57) THEN 1 ELSE 0 END) GOVERNED_CASES,
        SUM(CASE WHEN WORKFLOW_STAGE_CODE IS NULL THEN 1 ELSE 0 END) NULL_STAGES,
        SUM(CASE WHEN WORKFLOW_STAGE_CODE NOT IN (
          'NEW','ASSIGNED','UNDER_REVIEW','INFORMATION_REQUIRED','FINDING_PROPOSED',
          'SUPERVISOR_REVIEW','MANAGEMENT_APPROVAL','CORRECTIVE_ACTION','CLOSED') THEN 1 ELSE 0 END) BAD_STAGES
      FROM DASH_DIAR_CASES");
    if ((int)($integrity['null_stages'] ?? -1) !== 0 || (int)($integrity['bad_stages'] ?? -1) !== 0) {
        throw new RuntimeException('WORKFLOW_CASE_INTEGRITY_FAILED');
    }
    return [
        'release' => DIAR_6B_4_R2_RELEASE,
        'database' => $base,
        'integrity' => $integrity,
        'qa_case_count' => count(diarR2QaCases()),
        'role_matrix' => diarR2RoleMatrix(),
        'protected_counts' => diarR2ProtectedCounts(),
        'writes' => 'NONE',
    ];
}
