<?php
$root = dirname(__DIR__);
$version = json_decode(file_get_contents($root . '/DDB_VERSION.json'), true);
$sql = file_get_contents($root . '/database/revenue_monthly_summary_layer.sql');
$repair = file_get_contents($root . '/database/revenue_summary_oracle30_hotfix.sql');
$job = file_get_contents($root . '/database/revenue_summary_job_integration.sql');
$tool = file_get_contents($root . '/tools/revenue-summary-foundation.php');

$identifiers = [];
if (preg_match_all('/\b(?:TABLE|SEQUENCE|PROCEDURE|CONSTRAINT)\s+([A-Z0-9_]+)/i', $sql, $m)) {
    $identifiers = array_merge($identifiers, $m[1]);
}
$tooLong = [];
foreach ($identifiers as $identifier) {
    if (strlen($identifier) > 30) $tooLong[] = $identifier;
}

$checks = [
    'version' => ($version['version'] ?? '') === '11.0.11.64',
    'phase' => ($version['phase'] ?? '') === 'H11.0-P11.1-R45-P3.1',
    'short_procedure_name' => strpos($sql, 'CREATE OR REPLACE PROCEDURE DASH_REFRESH_REV_MONTH_SUM') !== false,
    'old_long_name_removed_from_foundation' => strpos($sql, 'DASH_REFRESH_REVENUE_MONTHLY_SUMMARY') === false,
    'old_long_name_removed_from_job' => strpos($job, 'DASH_REFRESH_REVENUE_MONTHLY_SUMMARY') === false,
    'old_long_name_removed_from_tool' => strpos($tool, 'DASH_REFRESH_REVENUE_MONTHLY_SUMMARY') === false,
    'repair_sql_present' => strpos($repair, 'CREATE OR REPLACE PROCEDURE DASH_REFRESH_REV_MONTH_SUM') !== false,
    'all_schema_identifiers_at_most_30' => count($tooLong) === 0,
    'php7_compatible' => strpos(file_get_contents(__FILE__), 'str_contains(') === false,
];

$failed = [];
foreach ($checks as $name => $ok) {
    if (!$ok) $failed[] = $name;
}
$result = [
    'schema' => 'R45_P3_1_QA',
    'checks' => $checks,
    'too_long' => array_values(array_unique($tooLong)),
    'passed' => count($checks) - count($failed),
    'failed' => $failed,
];
echo json_encode($result, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES), PHP_EOL;
exit(count($failed) === 0 ? 0 : 1);
