<?php
declare(strict_types=1);

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

use App\Core\Database;

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

function safeIdentifier(string $name): string
{
    $name = strtoupper(trim($name));
    if (!preg_match('/^[A-Z][A-Z0-9_$#]{0,29}$/', $name)) {
        throw new RuntimeException('Unsafe Oracle identifier: ' . $name);
    }
    return $name;
}

$hospital = strtolower((string)($argv[1] ?? 'njch'));
$table = 'KPI_DOCTOR_PERF_DETAIL_JOB';
$schema = strtoupper((string)config('hospitals.' . $hospital . '.database.schema', ''));
$currentYear = (int)($argv[2] ?? date('Y'));
$compareYear = (int)($argv[3] ?? ($currentYear - 1));

$columnsStep = timed('column_inventory', function () use ($hospital, $table, $schema): array {
    return Database::fetchAll($hospital, "
        SELECT
            column_id,
            column_name,
            data_type,
            data_length,
            data_precision,
            data_scale,
            nullable
        FROM all_tab_columns
        WHERE table_name = :table_name
          AND (:owner_name IS NULL OR owner = :owner_name)
        ORDER BY column_id
    ", [
        'table_name' => $table,
        'owner_name' => $schema !== '' ? $schema : null,
    ]);
});

$columns = is_array($columnsStep['value']) ? $columnsStep['value'] : [];
$candidates = [];
foreach ($columns as $column) {
    $name = strtoupper((string)($column['column_name'] ?? ''));
    $type = strtoupper((string)($column['data_type'] ?? ''));
    $score = 0;
    $reasons = [];

    if (preg_match('/(^|_)(MONTH|MON|PERIOD|DATE|DAY|TRANS|VISIT|SERVICE)(_|$)/', $name)) {
        $score += 4;
        $reasons[] = 'name_match';
    }
    if (in_array($type, ['DATE', 'TIMESTAMP', 'TIMESTAMP(6)', 'TIMESTAMP WITH TIME ZONE'], true)) {
        $score += 5;
        $reasons[] = 'date_type';
    }
    if (preg_match('/MONTH(ID|_ID|NO|_NO|NUM|_NUM)?$/', $name)) {
        $score += 8;
        $reasons[] = 'month_identifier';
    }
    if ($name === 'YEARID') {
        continue;
    }
    if ($score > 0) {
        $candidates[] = [
            'column_name' => $name,
            'data_type' => $type,
            'score' => $score,
            'reasons' => $reasons,
        ];
    }
}
usort($candidates, static function (array $a, array $b): int {
    return $b['score'] <=> $a['score'];
});

$yearCoverageStep = timed('year_coverage', function () use ($hospital, $table, $currentYear, $compareYear): array {
    return Database::fetchAll($hospital, "
        SELECT
            YEARID,
            COUNT(*) row_count,
            COUNT(DISTINCT SPECIAL_SPEC_ID) specialty_count,
            COUNT(DISTINCT EMP_ID) doctor_count,
            NVL(SUM(MOH), 0) moh_sum,
            NVL(SUM(DRUG_REV), 0) drug_sum,
            NVL(SUM(NET_REV_AFTER_DRUG_MOH), 0) net_sum
        FROM KPI_DOCTOR_PERF_DETAIL_JOB
        WHERE YEARID IN (:current_year, :compare_year)
        GROUP BY YEARID
        ORDER BY YEARID
    ", [
        'current_year' => $currentYear,
        'compare_year' => $compareYear,
    ]);
});

$candidateProfiles = [];
foreach (array_slice($candidates, 0, 12) as $candidate) {
    $column = safeIdentifier($candidate['column_name']);
    $type = strtoupper($candidate['data_type']);

    $profile = timed('candidate_' . $column, function () use (
        $hospital, $column, $type, $currentYear, $compareYear
    ): array {
        if (strpos($type, 'DATE') !== false || strpos($type, 'TIMESTAMP') !== false) {
            $rows = Database::fetchAll($hospital, "
                SELECT
                    YEARID,
                    MIN({$column}) min_value,
                    MAX({$column}) max_value,
                    COUNT(DISTINCT TO_CHAR({$column}, 'YYYY-MM')) distinct_months,
                    COUNT(*) row_count,
                    SUM(CASE WHEN {$column} IS NULL THEN 1 ELSE 0 END) null_count
                FROM KPI_DOCTOR_PERF_DETAIL_JOB
                WHERE YEARID IN (:current_year, :compare_year)
                GROUP BY YEARID
                ORDER BY YEARID
            ", [
                'current_year' => $currentYear,
                'compare_year' => $compareYear,
            ]);
            return ['mode' => 'date', 'rows' => $rows];
        }

        $rows = Database::fetchAll($hospital, "
            SELECT *
            FROM (
                SELECT
                    YEARID,
                    TO_CHAR({$column}) candidate_value,
                    COUNT(*) row_count
                FROM KPI_DOCTOR_PERF_DETAIL_JOB
                WHERE YEARID IN (:current_year, :compare_year)
                GROUP BY YEARID, TO_CHAR({$column})
                ORDER BY YEARID, TO_CHAR({$column})
            )
            WHERE ROWNUM <= 100
        ", [
            'current_year' => $currentYear,
            'compare_year' => $compareYear,
        ]);
        return ['mode' => 'value', 'rows' => $rows];
    });

    $candidateProfiles[] = [
        'column' => $column,
        'data_type' => $type,
        'score' => $candidate['score'],
        'reasons' => $candidate['reasons'],
        'status' => $profile['status'],
        'elapsed_ms' => $profile['elapsed_ms'],
        'profile' => $profile['value'],
        'error' => $profile['error'],
    ];
}

$readiness = [
    'status' => 'NOT_READY',
    'recommended_column' => null,
    'recommended_mode' => null,
    'reason' => 'No proven monthly grain column was found.',
];

foreach ($candidateProfiles as $profile) {
    if ($profile['status'] !== 'PASS' || !is_array($profile['profile'])) {
        continue;
    }

    if (($profile['profile']['mode'] ?? '') === 'date') {
        $rows = $profile['profile']['rows'] ?? [];
        $hasMonths = false;
        foreach ($rows as $row) {
            if ((int)($row['distinct_months'] ?? 0) > 1) {
                $hasMonths = true;
            }
        }
        if ($hasMonths) {
            $readiness = [
                'status' => 'READY',
                'recommended_column' => $profile['column'],
                'recommended_mode' => 'DATE_RANGE',
                'reason' => 'A date/timestamp column with multiple distinct months was proven.',
            ];
            break;
        }
    }

    if (($profile['profile']['mode'] ?? '') === 'value') {
        $values = [];
        foreach (($profile['profile']['rows'] ?? []) as $row) {
            $v = trim((string)($row['candidate_value'] ?? ''));
            if ($v !== '') $values[$v] = true;
        }
        $numericMonths = array_filter(array_keys($values), static function (string $v): bool {
            return ctype_digit($v) && (int)$v >= 1 && (int)$v <= 12;
        });
        if (count($numericMonths) >= 2) {
            $readiness = [
                'status' => 'READY',
                'recommended_column' => $profile['column'],
                'recommended_mode' => 'MONTH_ID',
                'reason' => 'A numeric month-grain column with values 1..12 was proven.',
            ];
            break;
        }
    }
}

$result = [
    'schema' => 'DDB_SPECIALTY_FINANCE_MONTHLY_GRAIN_DISCOVERY_V1',
    'captured_at' => date(DATE_ATOM),
    'hospital' => $hospital,
    'schema_owner' => $schema,
    'source_table' => $table,
    'current_year' => $currentYear,
    'compare_year' => $compareYear,
    'column_inventory' => [
        'status' => $columnsStep['status'],
        'elapsed_ms' => $columnsStep['elapsed_ms'],
        'column_count' => count($columns),
        'columns' => $columns,
        'error' => $columnsStep['error'],
    ],
    'candidate_columns' => $candidates,
    'candidate_profiles' => $candidateProfiles,
    'year_coverage' => [
        'status' => $yearCoverageStep['status'],
        'elapsed_ms' => $yearCoverageStep['elapsed_ms'],
        'rows' => $yearCoverageStep['value'],
        'error' => $yearCoverageStep['error'],
    ],
    'fair_comparison_readiness' => $readiness,
    'source_changes' => 'NONE',
    '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');
$jsonFile = $dir . '/SPECIALTY_FINANCE_MONTHLY_GRAIN_DISCOVERY_' . $stamp . '.json';
$csvFile = $dir . '/SPECIALTY_FINANCE_COLUMN_CANDIDATES_' . $stamp . '.csv';

file_put_contents(
    $jsonFile,
    json_encode($result, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE),
    LOCK_EX
);

$csv = fopen($csvFile, 'wb');
fputcsv($csv, ['column_name', 'data_type', 'score', 'reasons', 'profile_status']);
foreach ($candidateProfiles as $profile) {
    fputcsv($csv, [
        $profile['column'],
        $profile['data_type'],
        $profile['score'],
        implode('|', $profile['reasons']),
        $profile['status'],
    ]);
}
fclose($csv);

file_put_contents(
    $dir . '/LATEST_P6_0.json',
    json_encode([
        'json' => basename($jsonFile),
        'csv' => basename($csvFile),
        'captured_at' => $result['captured_at'],
        'readiness' => $readiness,
    ], JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES),
    LOCK_EX
);

echo json_encode([
    'status' => 'PASS',
    'json_file' => $jsonFile,
    'csv_file' => $csvFile,
    'fair_comparison_readiness' => $readiness,
    'candidate_profiles' => $candidateProfiles,
], JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE), PHP_EOL;
