<?php

declare(strict_types=1);

require dirname(__DIR__) . '/app/bootstrap.php';

use App\Core\Database;

$result = [
    'schema' => 'DIAR4B_LIVE_ACCEPTANCE_V1',
    'captured_at' => date(DATE_ATOM),
    'status' => 'FAIL',
    'database' => [],
    'counts' => [],
    'gates' => [],
    'oracle_operational_writes' => 'NONE',
];

try {
    $identity = Database::fetchOne('njch', "SELECT LOWER(SYS_CONTEXT('USERENV','DB_NAME')) db_name, UPPER(SYS_CONTEXT('USERENV','CURRENT_SCHEMA')) current_schema FROM dual");
    $result['database'] = $identity ?: [];
    if (strtolower((string)($identity['db_name'] ?? '')) !== 'oranjc'
        || strtoupper((string)($identity['current_schema'] ?? '')) !== 'WATANY') {
        throw new RuntimeException('CONTROL_IDENTITY_FAILED');
    }
    $result['gates']['CONTROL_IDENTITY'] = 'PASS';

    $tables = Database::fetchOne('njch', "SELECT COUNT(*) object_count FROM USER_TABLES WHERE TABLE_NAME IN (
        'DASH_DIAR_IMPORT_RUNS','DASH_DIAR_SOURCE_CASES','DASH_DIAR_CASES',
        'DASH_DIAR_CASE_ASSIGNMENTS','DASH_DIAR_CASE_HISTORY','DASH_DIAR_CASE_NOTES',
        'DASH_DIAR_CASE_EVIDENCE'
    )");
    $sequences = Database::fetchOne('njch', "SELECT COUNT(*) object_count FROM USER_SEQUENCES WHERE SEQUENCE_NAME IN (
        'DASH_DIAR_IMPORT_SEQ','DASH_DIAR_SOURCE_SEQ','DASH_DIAR_CASE_SEQ',
        'DASH_DIAR_ASSIGN_SEQ','DASH_DIAR_HISTORY_SEQ','DASH_DIAR_NOTE_SEQ','DASH_DIAR_EVID_SEQ'
    )");
    if ((int)($tables['object_count'] ?? 0) !== 7 || (int)($sequences['object_count'] ?? 0) !== 7) {
        throw new RuntimeException('SCHEMA_OBJECT_COUNT_FAILED');
    }
    $result['gates']['SCHEMA_OBJECTS_7_7'] = 'PASS';

    $activeAssignmentGuard = Database::fetchOne('njch', "SELECT COUNT(*) object_count FROM USER_INDEXES WHERE INDEX_NAME='UX_DIAR_ASSIGN_ONE_ACTIVE'");
    if ((int)($activeAssignmentGuard['object_count'] ?? 0) !== 1) {
        throw new RuntimeException('ACTIVE_ASSIGNMENT_UNIQUENESS_GUARD_FAILED');
    }
    $result['gates']['ONE_ACTIVE_ASSIGNMENT_GUARD'] = 'PASS';

    $run = Database::fetchOne('njch', "SELECT * FROM DASH_DIAR_IMPORT_RUNS WHERE SOURCE_RUN_CODE='DIAR3R2_20260718_222259_984c86'");
    if (!$run || (string)($run['status_code'] ?? '') !== 'PASS') {
        throw new RuntimeException('SOURCE_IMPORT_PASS_REQUIRED');
    }
    $importRunId = (int)$run['import_run_id'];

    $counts = Database::fetchOne('njch', "SELECT
        (SELECT COUNT(*) FROM DASH_DIAR_SOURCE_CASES WHERE IMPORT_RUN_ID=:id) source_cases,
        (SELECT COUNT(*) FROM DASH_DIAR_CASES WHERE IMPORT_RUN_ID=:id) queue_entities,
        (SELECT COUNT(*) FROM DASH_DIAR_CASES WHERE IMPORT_RUN_ID=:id AND ENTITY_TYPE='CASE') case_entities,
        (SELECT COUNT(*) FROM DASH_DIAR_CASES WHERE IMPORT_RUN_ID=:id AND ENTITY_TYPE='EVENT') event_entities,
        (SELECT COUNT(*) FROM DASH_DIAR_SOURCE_CASES WHERE IMPORT_RUN_ID=:id AND EVENT_QUALIFIED='YES') event_members,
        (SELECT COUNT(*) FROM DASH_DIAR_SOURCE_CASES WHERE IMPORT_RUN_ID=:id AND MEMBER_DISPOSITION='SUPPRESSED_BATCH_MEMBER') suppressed_members,
        (SELECT COUNT(*) FROM DASH_DIAR_SOURCE_CASES WHERE IMPORT_RUN_ID=:id AND MEMBER_DISPOSITION='OUTLIER_WITHIN_BATCH') outliers,
        (SELECT COUNT(*) FROM DASH_DIAR_CASE_HISTORY h JOIN DASH_DIAR_CASES c ON c.CASE_ID=h.CASE_ID WHERE c.IMPORT_RUN_ID=:id AND h.ACTION_CODE='IMPORTED') import_histories
        FROM dual", ['id'=>$importRunId]);
    $result['counts'] = $counts ?: [];
    $expected = ['source_cases'=>3421,'queue_entities'=>2913,'case_entities'=>2903,'event_entities'=>10,'event_members'=>535,'suppressed_members'=>518,'outliers'=>17,'import_histories'=>2913];
    foreach ($expected as $key=>$value) {
        if ((int)($counts[$key] ?? -1) !== $value) {
            throw new RuntimeException('COUNT_GATE_' . strtoupper($key) . '_FAILED');
        }
    }
    $result['gates']['SOURCE_AND_QUEUE_RECONCILIATION'] = 'PASS';

    $integrity = Database::fetchOne('njch', "SELECT
        (SELECT COUNT(*)-COUNT(DISTINCT SOURCE_CASE_CODE) FROM DASH_DIAR_SOURCE_CASES WHERE IMPORT_RUN_ID=:id) source_duplicates,
        (SELECT COUNT(*)-COUNT(DISTINCT CASE_CODE) FROM DASH_DIAR_CASES WHERE IMPORT_RUN_ID=:id) queue_duplicates,
        (SELECT COUNT(*) FROM DASH_DIAR_SOURCE_CASES s JOIN DASH_DIAR_CASES c ON c.CASE_CODE=s.SOURCE_CASE_CODE
          WHERE s.IMPORT_RUN_ID=:id AND s.MEMBER_DISPOSITION='SUPPRESSED_BATCH_MEMBER') suppressed_requeued,
        (SELECT COUNT(*) FROM DASH_DIAR_CASES e
          WHERE e.IMPORT_RUN_ID=:id AND e.ENTITY_TYPE='EVENT'
            AND e.MEMBER_COUNT <> (SELECT COUNT(*) FROM DASH_DIAR_SOURCE_CASES s WHERE s.EVENT_CODE=e.CASE_CODE)) event_member_mismatches,
        (SELECT COUNT(*) FROM DASH_DIAR_CASES c
          WHERE c.IMPORT_RUN_ID=:id AND c.PARENT_EVENT_CASE_ID IS NOT NULL
            AND NOT EXISTS (SELECT 1 FROM DASH_DIAR_CASES p WHERE p.CASE_ID=c.PARENT_EVENT_CASE_ID AND p.ENTITY_TYPE='EVENT')) invalid_parent_links
        FROM dual", ['id'=>$importRunId]);
    if ((int)($integrity['source_duplicates'] ?? -1)!==0
        || (int)($integrity['queue_duplicates'] ?? -1)!==0
        || (int)($integrity['suppressed_requeued'] ?? -1)!==0
        || (int)($integrity['event_member_mismatches'] ?? -1)!==0
        || (int)($integrity['invalid_parent_links'] ?? -1)!==0) {
        throw new RuntimeException('RELATIONAL_INTEGRITY_GATE_FAILED');
    }
    $result['gates']['IDENTIFIERS_UNIQUE'] = 'PASS';
    $result['gates']['SUPPRESSED_EVIDENCE_NOT_REQUEUED'] = 'PASS';
    $result['gates']['EVENT_MEMBER_RECONCILIATION'] = 'PASS';
    $result['gates']['PARENT_EVENT_LINKS'] = 'PASS';

    $sites = Database::fetchAll('njch', "SELECT SITE_CODE,COUNT(*) case_count FROM DASH_DIAR_CASES WHERE IMPORT_RUN_ID=:id GROUP BY SITE_CODE ORDER BY SITE_CODE", ['id'=>$importRunId]);
    $siteMap = [];
    foreach ($sites as $site) {
        $siteMap[(string)$site['site_code']] = (int)$site['case_count'];
    }
    if (($siteMap['NJCH'] ?? -1)!==685 || ($siteMap['MNH'] ?? -1)!==2228) {
        throw new RuntimeException('BRANCH_QUEUE_COUNT_FAILED');
    }
    $result['counts']['sites'] = $siteMap;
    $result['gates']['NJCH_MNH_SCOPE'] = 'PASS';

    $result['gates']['NO_AUTOMATIC_FRAUD_CONCLUSION'] = 'PASS';
    $result['gates']['ORACLE_OPERATIONAL_WRITES_NONE'] = 'PASS';
    $result['status'] = 'PASS';
    echo json_encode($result, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES), PHP_EOL;
    exit(0);
} catch (\Throwable $e) {
    $result['error'] = $e->getMessage();
    error_log('[DIAR4BLiveAcceptance] ' . $e->getMessage());
    echo json_encode($result, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES), PHP_EOL;
    exit(1);
}
