<?php
declare(strict_types=1);

if ($argc < 5) {
    fwrite(STDERR, "Usage: php merge_company_sql.php <base.sql> <source.sql> <company_id> <output.sql>\n");
    exit(1);
}

[$script, $basePath, $sourcePath, $companyIdRaw, $outputPath] = $argv;
$companyId = (int) $companyIdRaw;
if ($companyId <= 0) {
    fwrite(STDERR, "Invalid company id.\n");
    exit(1);
}

$baseSql = file_get_contents($basePath);
$sourceSql = file_get_contents($sourcePath);
if ($baseSql === false || $sourceSql === false) {
    fwrite(STDERR, "Could not read one of the SQL files.\n");
    exit(1);
}

function sql_tables_with_create(string $sql): array
{
    preg_match_all('/CREATE TABLE `([^`]+)`/u', $sql, $matches);
    return array_fill_keys($matches[1] ?? [], true);
}

function sql_parse_columns(string $columnsSql): array
{
    preg_match_all('/`([^`]+)`/u', $columnsSql, $matches);
    return $matches[1] ?? [];
}

function sql_split_tuples(string $valuesSql): array
{
    $tuples = [];
    $length = strlen($valuesSql);
    $inString = false;
    $escape = false;
    $depth = 0;
    $start = null;

    for ($i = 0; $i < $length; $i++) {
        $ch = $valuesSql[$i];
        if ($inString) {
            if ($escape) {
                $escape = false;
                continue;
            }
            if ($ch === '\\') {
                $escape = true;
                continue;
            }
            if ($ch === "'") {
                $inString = false;
            }
            continue;
        }

        if ($ch === "'") {
            $inString = true;
            continue;
        }
        if ($ch === '(') {
            if ($depth === 0) {
                $start = $i;
            }
            $depth++;
            continue;
        }
        if ($ch === ')') {
            $depth--;
            if ($depth === 0 && $start !== null) {
                $tuples[] = trim(substr($valuesSql, $start, $i - $start + 1));
                $start = null;
            }
        }
    }

    return $tuples;
}

function sql_parse_tuple_values(string $tuple): array
{
    $body = trim($tuple);
    if (str_starts_with($body, '(') && str_ends_with($body, ')')) {
        $body = substr($body, 1, -1);
    }

    $values = [];
    $length = strlen($body);
    $inString = false;
    $escape = false;
    $current = '';

    for ($i = 0; $i < $length; $i++) {
        $ch = $body[$i];
        if ($inString) {
            $current .= $ch;
            if ($escape) {
                $escape = false;
                continue;
            }
            if ($ch === '\\') {
                $escape = true;
                continue;
            }
            if ($ch === "'") {
                $inString = false;
            }
            continue;
        }

        if ($ch === "'") {
            $inString = true;
            $current .= $ch;
            continue;
        }
        if ($ch === ',') {
            $values[] = trim($current);
            $current = '';
            continue;
        }
        $current .= $ch;
    }
    $values[] = trim($current);

    return $values;
}

function sql_token_int(string $token): ?int
{
    $token = trim($token);
    if (preg_match('/^-?\d+$/', $token)) {
        return (int) $token;
    }
    if (preg_match("/^'(-?\d+)'$/", $token, $match)) {
        return (int) $match[1];
    }
    return null;
}

$localTables = sql_tables_with_create($baseSql);
$selected = [];
$skippedTables = [];
$sourceTableCount = 0;
$selectedRowCount = 0;
$idMax = [];

preg_match_all('/INSERT INTO `([^`]+)`\s*\((.*?)\)\s*VALUES\s*(.*?);/us', $baseSql, $baseInsertMatches, PREG_SET_ORDER);
foreach ($baseInsertMatches as $insert) {
    $table = $insert[1];
    $columns = sql_parse_columns($insert[2]);
    $columnIndex = array_flip($columns);
    $idColumn = $columnIndex['id'] ?? null;
    if ($idColumn === null) {
        continue;
    }
    foreach (sql_split_tuples($insert[3]) as $tuple) {
        $values = sql_parse_tuple_values($tuple);
        $idValue = sql_token_int($values[$idColumn] ?? '');
        if ($idValue !== null) {
            $idMax[$table] = max($idMax[$table] ?? 0, $idValue);
        }
    }
}

preg_match_all('/INSERT INTO `([^`]+)`\s*\((.*?)\)\s*VALUES\s*(.*?);/us', $sourceSql, $insertMatches, PREG_SET_ORDER);
foreach ($insertMatches as $insert) {
    $sourceTableCount++;
    $table = $insert[1];
    if (!isset($localTables[$table])) {
        $skippedTables[$table] = true;
        continue;
    }

    $columns = sql_parse_columns($insert[2]);
    $columnIndex = array_flip($columns);
    $companyColumn = $columnIndex['company_id'] ?? null;
    $idColumn = $columnIndex['id'] ?? null;
    $isCompanyTable = $table === 'companies' && $idColumn !== null;
    if ($companyColumn === null && !$isCompanyTable) {
        continue;
    }

    foreach (sql_split_tuples($insert[3]) as $tuple) {
        $values = sql_parse_tuple_values($tuple);
        $include = false;
        if ($companyColumn !== null) {
            $include = sql_token_int($values[$companyColumn] ?? '') === $companyId;
        } elseif ($isCompanyTable) {
            $include = sql_token_int($values[$idColumn] ?? '') === $companyId;
        }
        if (!$include) {
            continue;
        }

        if (!isset($selected[$table])) {
            $selected[$table] = ['columns' => $columns, 'rows' => []];
        }
        $selected[$table]['rows'][] = $values;
        $selectedRowCount++;
    }
}

$tableOrder = array_keys($selected);
usort($tableOrder, static function (string $a, string $b): int {
    if ($a === 'companies') {
        return -1;
    }
    if ($b === 'companies') {
        return 1;
    }
    return strcmp($a, $b);
});

$idMaps = [];
foreach ($tableOrder as $table) {
    if ($table === 'companies') {
        continue;
    }
    $columns = $selected[$table]['columns'];
    $columnIndex = array_flip($columns);
    $idColumn = $columnIndex['id'] ?? null;
    if ($idColumn === null) {
        continue;
    }

    $nextId = ($idMax[$table] ?? 0) + 1;
    foreach ($selected[$table]['rows'] as $rowIndex => $values) {
        $oldId = sql_token_int($values[$idColumn] ?? '');
        if ($oldId === null) {
            continue;
        }
        $newId = $nextId++;
        $idMaps[$table][$oldId] = $newId;
        $selected[$table]['rows'][$rowIndex][$idColumn] = (string) $newId;
    }
    $idMax[$table] = $nextId - 1;
}

$setMappedValue = static function (array &$selected, string $table, int $rowIndex, array $columnIndex, string $columnName, string $mapTable) use ($idMaps): void {
    if (!isset($columnIndex[$columnName], $idMaps[$mapTable])) {
        return;
    }
    $oldValue = sql_token_int($selected[$table]['rows'][$rowIndex][$columnIndex[$columnName]] ?? '');
    if ($oldValue !== null && isset($idMaps[$mapTable][$oldValue])) {
        $selected[$table]['rows'][$rowIndex][$columnIndex[$columnName]] = (string) $idMaps[$mapTable][$oldValue];
    }
};

$sourceTypeToTable = [
    'expenses' => 'expenses',
    'payments' => 'payments',
    'prepaid_expenses' => 'prepaid_expenses',
    'prepaid_amortization_runs' => 'prepaid_amortization_runs',
];

foreach ($tableOrder as $table) {
    $columns = $selected[$table]['columns'];
    $columnIndex = array_flip($columns);
    foreach ($selected[$table]['rows'] as $rowIndex => $values) {
        if ($table === 'expense_lines') {
            $setMappedValue($selected, $table, $rowIndex, $columnIndex, 'expense_id', 'expenses');
        } elseif ($table === 'journal_entry_lines') {
            $setMappedValue($selected, $table, $rowIndex, $columnIndex, 'journal_entry_id', 'journal_entries');
        } elseif ($table === 'payments') {
            $setMappedValue($selected, $table, $rowIndex, $columnIndex, 'expense_id', 'expenses');
        } elseif ($table === 'prepaid_amortization_runs') {
            $setMappedValue($selected, $table, $rowIndex, $columnIndex, 'prepaid_expense_id', 'prepaid_expenses');
            $setMappedValue($selected, $table, $rowIndex, $columnIndex, 'journal_entry_id', 'journal_entries');
        } elseif ($table === 'user_company_access') {
            $setMappedValue($selected, $table, $rowIndex, $columnIndex, 'user_id', 'users');
        }

        if (isset($columnIndex['source_type'], $columnIndex['source_id'])) {
            $sourceTypeToken = trim((string) ($selected[$table]['rows'][$rowIndex][$columnIndex['source_type']] ?? ''));
            $sourceType = trim($sourceTypeToken, "'");
            $sourceType = str_replace(["\\\\", "\\'"], ["\\", "'"], $sourceType);
            if (isset($sourceTypeToTable[$sourceType])) {
                $setMappedValue($selected, $table, $rowIndex, $columnIndex, 'source_id', $sourceTypeToTable[$sourceType]);
            }
        }
    }
}

$rowToTuple = static function (array $values): string {
    return '(' . implode(', ', $values) . ')';
};

$merge = [];
$merge[] = '';
$merge[] = '-- --------------------------------------------------------';
$merge[] = '-- Company 9 data merged from atgerpst_orion.sql';
$merge[] = '-- Base file: orion_accounts_erp local.sql';
$merge[] = '-- Generated: ' . date('Y-m-d H:i:s');
$merge[] = '-- --------------------------------------------------------';
$merge[] = 'SET FOREIGN_KEY_CHECKS=0;';

$deleteOrder = array_reverse($tableOrder);
usort($deleteOrder, static function (string $a, string $b): int {
    if ($a === 'user_company_access') {
        return -1;
    }
    if ($b === 'user_company_access') {
        return 1;
    }
    if ($a === 'companies') {
        return 1;
    }
    if ($b === 'companies') {
        return -1;
    }
    return 0;
});
foreach ($deleteOrder as $table) {
    if ($table === 'companies') {
        $merge[] = "DELETE FROM `companies` WHERE `id` = {$companyId};";
    } elseif ($table === 'user_company_access') {
        $merge[] = "DELETE FROM `user_company_access` WHERE `company_id` = {$companyId} OR `user_id` IN (SELECT `id` FROM `users` WHERE `company_id` = {$companyId});";
    } else {
        $merge[] = "DELETE FROM `{$table}` WHERE `company_id` = {$companyId};";
    }
}

foreach ($tableOrder as $table) {
    $columns = array_map(static fn (string $column): string => '`' . str_replace('`', '``', $column) . '`', $selected[$table]['columns']);
    $rows = $selected[$table]['rows'];
    $merge[] = '';
    $merge[] = "-- Data for table `{$table}`";
    $merge[] = 'INSERT INTO `' . $table . '` (' . implode(', ', $columns) . ') VALUES';
    $last = count($rows) - 1;
    foreach ($rows as $index => $values) {
        $merge[] = $rowToTuple($values) . ($index === $last ? ';' : ',');
    }
}

foreach ($tableOrder as $table) {
    if (($idMax[$table] ?? 0) > 0) {
        $merge[] = 'ALTER TABLE `' . $table . '` AUTO_INCREMENT = ' . ((int) $idMax[$table] + 1) . ';';
    }
}
$merge[] = 'SET FOREIGN_KEY_CHECKS=1;';
$merge[] = '';

$mergeSection = implode("\n", $merge);
$position = strripos($baseSql, "\nCOMMIT;");
if ($position === false) {
    $outputSql = rtrim($baseSql) . "\n" . $mergeSection . "\n";
} else {
    $outputSql = substr($baseSql, 0, $position) . "\n" . $mergeSection . substr($baseSql, $position);
}

if (file_put_contents($outputPath, $outputSql) === false) {
    fwrite(STDERR, "Could not write output file.\n");
    exit(1);
}

echo json_encode([
    'output' => $outputPath,
    'selected_tables' => count($selected),
    'selected_rows' => $selectedRowCount,
    'skipped_source_tables_not_in_base' => array_keys($skippedTables),
    'tables' => array_map(
        static fn (string $table): array => ['table' => $table, 'rows' => count($selected[$table]['rows'])],
        $tableOrder
    ),
], JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT) . PHP_EOL;
