<?php
declare(strict_types=1);

require_once __DIR__ . '/db/config.php';

if (PHP_SAPI !== 'cli') {
    header('Content-Type: text/plain; charset=utf-8');
    header('X-Robots-Tag: noindex, nofollow');
}

@set_time_limit(0);

function snapshotOutput(string $message = ''): void
{
    echo $message . PHP_EOL;
}

function configureSnapshotSession(): void
{
    if (PHP_SAPI === 'cli') {
        return;
    }

    $sessionsDir = __DIR__ . '/sessions';
    if (!is_dir($sessionsDir)) {
        @mkdir($sessionsDir, 0777, true);
    }

    if (is_writable($sessionsDir)) {
        session_save_path("0;0660;" . $sessionsDir);
    }

    if (
        (isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] === 'on')
        || (isset($_SERVER['HTTP_X_FORWARDED_PROTO']) && $_SERVER['HTTP_X_FORWARDED_PROTO'] === 'https')
    ) {
        session_set_cookie_params([
            'lifetime' => 0,
            'path' => '/',
            'secure' => true,
            'httponly' => true,
            'samesite' => 'None',
        ]);
    }
}

function canRefreshSchemaSnapshot(): bool
{
    if (PHP_SAPI === 'cli') {
        return true;
    }

    configureSnapshotSession();

    if (session_status() === PHP_SESSION_NONE) {
        @session_start();
    }

    $roleName = (string) ($_SESSION['user_role_name'] ?? '');
    if (strcasecmp($roleName, 'Administrator') === 0 || (int) ($_SESSION['user_id'] ?? 0) === 1) {
        return true;
    }

    $remoteAddress = $_SERVER['REMOTE_ADDR'] ?? '';
    return in_array($remoteAddress, ['127.0.0.1', '::1'], true);
}

function quoteIdentifier(string $identifier): string
{
    return '`' . str_replace('`', '``', $identifier) . '`';
}

function normalizeCreateTableSql(string $sql): string
{
    $sql = str_replace(["\r\n", "\r"], "\n", trim($sql));

    if (!preg_match('/^CREATE\s+TABLE\s+IF\s+NOT\s+EXISTS\b/i', $sql)) {
        $sql = preg_replace('/^CREATE\s+TABLE\b/i', 'CREATE TABLE IF NOT EXISTS', $sql, 1) ?? $sql;
    }

    $sql = preg_replace('/\s+AUTO_INCREMENT=\d+\b/i', '', $sql) ?? $sql;

    return rtrim($sql, ';') . ';';
}

function schemaSnapshotMigrationSortKey(string $filePath): string
{
    $basename = basename($filePath);

    return match ($basename) {
        '20260318_add_outlet_id_to_purchases.sql' => '20260318_10_add_outlet_id_to_purchases.sql',
        '20260318_create_outlets_table.sql' => '20260318_20_create_outlets_table.sql',
        '20260318_multi_outlet_schema.sql' => '20260318_30_multi_outlet_schema.sql',
        '20260318_local_definitions.sql' => '20260318_40_local_definitions.sql',
        '20260318_user_outlets_table.sql' => '20260318_50_user_outlets_table.sql',
        default => $basename,
    };
}

function isLegacyNumberedSnapshotMigrationFile(string $filePath): bool
{
    // Skip obsolete pre-date-based migrations from older packaged builds.
    return preg_match('/^\d{1,7}_/', basename($filePath)) === 1;
}

function isInstallBaselineExcludedMigrationFile(string $filePath): bool
{
    // These migrations include required default data that is not embedded in complete_schema.sql.
    // Keep them out of the install baseline so fresh installs still execute them.
    return in_array(basename($filePath), [
        '20260318_create_outlets_table.sql',
        '20260502_zzzz_default_outlet_guard.sql',
    ], true);
}

function fetchInstallBaselineMigrationNames(): array
{
    $files = array_merge(
        glob(__DIR__ . '/db/migrations/*.sql') ?: [],
        glob(__DIR__ . '/db/migrations/*.php') ?: []
    );

    $files = array_values(array_filter($files, static function (string $filePath): bool {
        return !isLegacyNumberedSnapshotMigrationFile($filePath)
            && !isInstallBaselineExcludedMigrationFile($filePath);
    }));

    usort($files, static function (string $left, string $right): int {
        return strnatcasecmp(schemaSnapshotMigrationSortKey($left), schemaSnapshotMigrationSortKey($right));
    });

    $names = [];
    foreach ($files as $filePath) {
        $migrationName = basename($filePath);
        if ($migrationName === '') {
            continue;
        }

        $names[$migrationName] = true;
    }

    return array_keys($names);
}

function buildInstallBaselineContent(array $migrationNames): string
{
    $lines = [
        '<?php',
        'declare(strict_types=1);',
        '',
        '// Auto-generated install baseline for fresh installs.',
        '// Refreshed together with complete_schema.sql by refresh_complete_schema.php.',
        '',
        'return [',
    ];

    foreach ($migrationNames as $migrationName) {
        $lines[] = '    ' . var_export((string) $migrationName, true) . ',';
    }

    $lines[] = '];';
    $lines[] = '';

    return implode(PHP_EOL, $lines);
}

function fetchBaseTableNames(PDO $pdo): array
{
    $stmt = $pdo->query("SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'");
    $rows = $stmt ? $stmt->fetchAll(PDO::FETCH_NUM) : [];
    $tables = [];

    foreach ($rows as $row) {
        if (!isset($row[0])) {
            continue;
        }

        $tableName = (string) $row[0];
        if ($tableName === '') {
            continue;
        }

        $tables[] = $tableName;
    }

    usort($tables, 'strnatcasecmp');
    return $tables;
}

function fetchCreateTableSql(PDO $pdo, string $tableName): string
{
    $stmt = $pdo->query('SHOW CREATE TABLE ' . quoteIdentifier($tableName));
    $row = $stmt ? $stmt->fetch(PDO::FETCH_ASSOC) : false;

    if (!$row) {
        throw new RuntimeException('Unable to read CREATE TABLE statement for ' . $tableName);
    }

    $createSql = $row['Create Table'] ?? null;
    if (!is_string($createSql) || $createSql === '') {
        $values = array_values($row);
        $createSql = isset($values[1]) && is_string($values[1]) ? $values[1] : '';
    }

    if ($createSql === '') {
        throw new RuntimeException('Database did not return a CREATE TABLE statement for ' . $tableName);
    }

    return normalizeCreateTableSql($createSql);
}

function extractTableDependencies(string $tableName, string $createSql): array
{
    if (!preg_match_all('/REFERENCES\s+`?([a-zA-Z0-9_]+)`?/i', $createSql, $matches)) {
        return [];
    }

    $dependencies = [];
    $normalizedTable = strtolower($tableName);

    foreach ($matches[1] as $referencedTable) {
        $referencedTable = strtolower((string) $referencedTable);
        if ($referencedTable === '' || $referencedTable === $normalizedTable) {
            continue;
        }

        $dependencies[$referencedTable] = true;
    }

    return array_keys($dependencies);
}

function fetchTableDefinitions(PDO $pdo): array
{
    $definitions = [];

    foreach (fetchBaseTableNames($pdo) as $tableName) {
        $definitions[$tableName] = fetchCreateTableSql($pdo, $tableName);
    }

    if ($definitions === []) {
        throw new RuntimeException('No base tables were found in the current database.');
    }

    return $definitions;
}

function orderTablesForSnapshot(array $definitions): array
{
    $orderedNames = array_keys($definitions);
    usort($orderedNames, 'strnatcasecmp');

    $actualNamesByLower = [];
    foreach ($orderedNames as $tableName) {
        $actualNamesByLower[strtolower($tableName)] = $tableName;
    }

    $incoming = [];
    $graph = [];
    foreach ($orderedNames as $tableName) {
        $incoming[$tableName] = [];
        $graph[$tableName] = [];
    }

    foreach ($definitions as $tableName => $createSql) {
        foreach (extractTableDependencies($tableName, $createSql) as $dependencyLower) {
            if (!isset($actualNamesByLower[$dependencyLower])) {
                continue;
            }

            $dependency = $actualNamesByLower[$dependencyLower];
            $incoming[$tableName][$dependency] = true;
            $graph[$dependency][$tableName] = true;
        }
    }

    $queue = [];
    foreach ($orderedNames as $tableName) {
        if ($incoming[$tableName] === []) {
            $queue[] = $tableName;
        }
    }
    usort($queue, 'strnatcasecmp');

    $snapshotOrder = [];
    while ($queue !== []) {
        $tableName = array_shift($queue);
        $snapshotOrder[] = $tableName;

        foreach (array_keys($graph[$tableName]) as $child) {
            unset($incoming[$child][$tableName]);
            if ($incoming[$child] === []) {
                $queue[] = $child;
            }
        }

        usort($queue, 'strnatcasecmp');
    }

    if (count($snapshotOrder) < count($orderedNames)) {
        $remaining = array_values(array_diff($orderedNames, $snapshotOrder));
        usort($remaining, 'strnatcasecmp');
        $snapshotOrder = array_merge($snapshotOrder, $remaining);
    }

    return $snapshotOrder;
}

function buildSnapshotContent(PDO $pdo, array &$tableOrder = []): string
{
    $definitions = fetchTableDefinitions($pdo);
    $tableOrder = orderTablesForSnapshot($definitions);

    $lines = [
        '/*M!999999\\- enable the sandbox mode */ ',
        '',
        '/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;',
        '/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;',
        '/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;',
        '/*!40101 SET NAMES utf8mb4 */;',
        '/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;',
        "/*!40103 SET TIME_ZONE='+00:00' */;",
        '/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;',
        '/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;',
        "/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;",
        '/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;',
        '',
        '-- Auto-generated full schema snapshot for fresh installs.',
        '-- Re-run refresh_complete_schema.php after schema or migration changes so new installations stay current.',
        '',
    ];

    foreach ($tableOrder as $tableName) {
        $lines[] = '--';
        $lines[] = '-- Table structure for table ' . quoteIdentifier($tableName);
        $lines[] = '--';
        $lines[] = $definitions[$tableName];
        $lines[] = '';
    }

    $lines[] = '/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;';
    $lines[] = '/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;';
    $lines[] = '/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;';
    $lines[] = '/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;';
    $lines[] = '/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;';
    $lines[] = '/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;';
    $lines[] = '/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;';
    $lines[] = '/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;';

    return implode(PHP_EOL, $lines) . PHP_EOL;
}

function runSchemaSnapshotRefresh(): int
{
    if (!canRefreshSchemaSnapshot()) {
        if (PHP_SAPI !== 'cli') {
            http_response_code(403);
        }

        snapshotOutput('Forbidden: run refresh_complete_schema.php from CLI, localhost, or while logged in as an Administrator.');
        return 1;
    }

    try {
        $pdo = db();
        $tableOrder = [];
        $snapshotSql = buildSnapshotContent($pdo, $tableOrder);
        $targetFile = __DIR__ . '/complete_schema.sql';
        $bytesWritten = file_put_contents($targetFile, $snapshotSql);

        if ($bytesWritten === false) {
            throw new RuntimeException('Unable to write complete_schema.sql');
        }

        $baselineNames = fetchInstallBaselineMigrationNames();
        $baselineTargetFile = __DIR__ . '/db/install_baseline_migrations.php';
        $baselineBytesWritten = file_put_contents($baselineTargetFile, buildInstallBaselineContent($baselineNames));

        if ($baselineBytesWritten === false) {
            throw new RuntimeException('Unable to write db/install_baseline_migrations.php');
        }

        snapshotOutput('OK: refreshed complete_schema.sql');
        snapshotOutput('OK: refreshed db/install_baseline_migrations.php');
        snapshotOutput('Tables: ' . count($tableOrder));
        snapshotOutput('Baseline migrations: ' . count($baselineNames));
        snapshotOutput('Path: complete_schema.sql');
        snapshotOutput('Path: db/install_baseline_migrations.php');
        return 0;
    } catch (Throwable $throwable) {
        if (PHP_SAPI !== 'cli') {
            http_response_code(500);
        }

        snapshotOutput('ERROR: ' . $throwable->getMessage());
        return 1;
    }
}

exit(runSchemaSnapshotRefresh());