<?php
declare(strict_types=1);

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

use App\Core\Database;

function step(string $name, callable $callback): array
{
    $started = microtime(true);
    try {
        return [
            'name' => $name,
            'status' => 'PASS',
            'elapsed_ms' => round((microtime(true) - $started) * 1000, 2),
            'rows' => $callback(),
            'error' => null,
        ];
    } catch (Throwable $e) {
        return [
            'name' => $name,
            'status' => 'FAIL',
            'elapsed_ms' => round((microtime(true) - $started) * 1000, 2),
            'rows' => [],
            'error' => $e->getMessage(),
        ];
    }
}

$hospital = strtolower((string)($argv[1] ?? 'njch'));
$table = 'KPI_DOCTOR_PERF_DETAIL_JOB';

$checks = [];

$checks[] = step('table_owner_and_type', function () use ($hospital, $table): array {
    return Database::fetchAll($hospital, "
        SELECT owner, object_name, object_type, status, created, last_ddl_time
        FROM all_objects
        WHERE object_name = :object_name
        ORDER BY owner, object_type
    ", ['object_name' => $table]);
});

$checks[] = step('direct_dependencies_on_table', function () use ($hospital, $table): array {
    return Database::fetchAll($hospital, "
        SELECT owner, name, type, referenced_owner, referenced_name, referenced_type
        FROM all_dependencies
        WHERE referenced_name = :referenced_name
        ORDER BY owner, type, name
    ", ['referenced_name' => $table]);
});

$checks[] = step('source_references', function () use ($hospital, $table): array {
    return Database::fetchAll($hospital, "
        SELECT owner, name, type, line, text
        FROM all_source
        WHERE UPPER(text) LIKE '%' || :needle || '%'
        ORDER BY owner, name, type, line
    ", ['needle' => $table]);
});

$checks[] = step('scheduler_jobs', function () use ($hospital, $table): array {
    return Database::fetchAll($hospital, "
        SELECT
            owner,
            job_name,
            enabled,
            state,
            job_type,
            job_action,
            repeat_interval,
            start_date,
            last_start_date,
            last_run_duration,
            next_run_date,
            failure_count
        FROM all_scheduler_jobs
        WHERE UPPER(NVL(job_action, ' ')) LIKE '%' || :needle || '%'
           OR UPPER(job_name) LIKE '%DOCTOR%'
           OR UPPER(job_name) LIKE '%KPI%'
           OR UPPER(job_name) LIKE '%PERF%'
        ORDER BY owner, job_name
    ", ['needle' => $table]);
});

$checks[] = step('legacy_jobs', function () use ($hospital, $table): array {
    return Database::fetchAll($hospital, "
        SELECT schema_user, job, what, last_date, next_date, broken, failures, interval
        FROM all_jobs
        WHERE UPPER(NVL(what, ' ')) LIKE '%' || :needle || '%'
           OR UPPER(NVL(what, ' ')) LIKE '%DOCTOR%'
           OR UPPER(NVL(what, ' ')) LIKE '%KPI%'
           OR UPPER(NVL(what, ' ')) LIKE '%PERF%'
        ORDER BY schema_user, job
    ", ['needle' => $table]);
});

$checks[] = step('triggers', function () use ($hospital, $table): array {
    return Database::fetchAll($hospital, "
        SELECT owner, trigger_name, trigger_type, triggering_event, status, table_owner, table_name
        FROM all_triggers
        WHERE table_name = :table_name
           OR UPPER(trigger_body) LIKE '%' || :needle || '%'
        ORDER BY owner, trigger_name
    ", [
        'table_name' => $table,
        'needle' => $table,
    ]);
});

$checks[] = step('materialized_views', function () use ($hospital, $table): array {
    return Database::fetchAll($hospital, "
        SELECT owner, mview_name, container_name, refresh_mode, refresh_method, last_refresh_type, last_refresh_date, staleness
        FROM all_mviews
        WHERE mview_name = :name OR container_name = :name
        ORDER BY owner, mview_name
    ", ['name' => $table]);
});

$checks[] = step('synonyms', function () use ($hospital, $table): array {
    return Database::fetchAll($hospital, "
        SELECT owner, synonym_name, table_owner, table_name, db_link
        FROM all_synonyms
        WHERE synonym_name = :name OR table_name = :name
        ORDER BY owner, synonym_name
    ", ['name' => $table]);
});

$checks[] = step('year_coverage', function () use ($hospital): array {
    return Database::fetchAll($hospital, "
        SELECT YEARID, COUNT(*) row_count,
               MIN(MOH) min_moh, MAX(MOH) max_moh,
               NVL(SUM(MOH), 0) total_moh,
               NVL(SUM(DRUG_REV), 0) total_drug,
               NVL(SUM(NET_REV_AFTER_DRUG_MOH), 0) total_net
        FROM KPI_DOCTOR_PERF_DETAIL_JOB
        GROUP BY YEARID
        ORDER BY YEARID DESC
    ");
});

$candidates = [];
foreach ($checks as $check) {
    foreach ($check['rows'] as $row) {
        $blob = strtoupper(json_encode($row, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE));
        if (
            strpos($blob, $table) !== false
            || strpos($blob, 'DOCTOR') !== false
            || strpos($blob, 'KPI') !== false
            || strpos($blob, 'PERF') !== false
        ) {
            $candidates[] = [
                'source' => $check['name'],
                'row' => $row,
            ];
        }
    }
}

$status = count($candidates) > 0 ? 'CANDIDATES_FOUND' : 'NO_PRODUCER_FOUND';

$result = [
    'schema' => 'DDB_SPECIALTY_FINANCE_SOURCE_REFRESH_DISCOVERY_V1',
    'captured_at' => date(DATE_ATOM),
    'hospital' => $hospital,
    'target_table' => $table,
    'status' => $status,
    'checks' => $checks,
    'producer_candidates' => $candidates,
    'decision' => [
        'can_auto_fix_now' => false,
        'reason' => $status === 'CANDIDATES_FOUND'
            ? 'One or more Oracle producer candidates were found; inspect exact procedure/job before any write action.'
            : 'No producer was found from accessible Oracle metadata. The historical producer source must be supplied or recreated from transaction tables.',
    ],
    'oracle_writes' => 'NONE',
];

$dir = dirname(__DIR__) . '/storage/evidence/specialty-finance';
if (!is_dir($dir) && !mkdir($dir, 0775, true) && !is_dir($dir)) {
    throw new RuntimeException('Cannot create Specialty Finance evidence directory.');
}
$stamp = date('Ymd_His');
$file = $dir . '/SPECIALTY_FINANCE_SOURCE_REFRESH_DISCOVERY_' . $stamp . '.json';
file_put_contents(
    $file,
    json_encode($result, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE),
    LOCK_EX
);

file_put_contents(
    $dir . '/LATEST_P6_0_2.json',
    json_encode([
        'json' => basename($file),
        'captured_at' => $result['captured_at'],
        'status' => $status,
        'candidate_count' => count($candidates),
    ], JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES),
    LOCK_EX
);

echo json_encode([
    'status' => 'PASS',
    'discovery_status' => $status,
    'candidate_count' => count($candidates),
    'evidence_file' => $file,
    'producer_candidates' => $candidates,
], JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE), PHP_EOL;
