<?php

function normalize_column_definition($col_def_raw) {
    // Remove extra spaces, convert to lowercase, remove backticks
    $normalized = strtolower(trim(str_replace('`', '', $col_def_raw)));
    // Further normalization can be added here, e.g., for default values, nullability, etc.
    return $normalized;
}

function parse_create_table_sql($sql_content)
{
    $tables = [];
    // Regex to capture table name and the entire content within parentheses
    preg_match_all('/CREATE TABLE `?(\w+)`?\s*\(([^;]+?)\)\s*ENGINE=.*?;/is', $sql_content, $matches, PREG_SET_ORDER);

    foreach ($matches as $match) {
        $table_name = $match[1];
        $columns_str = trim($match[2]);

        $columns = [];
        $normalized_columns = [];

        echo "--- Debugging Table: {$table_name} ---\n";

        // Split the content by lines
        $lines = preg_split("/,\n/", $columns_str);
        echo "--- Debugging  Lines:  ".$columns_str."---\n";
        foreach ($lines as $index => $line) {
            $line = trim($line);
            // Remove trailing comma if present
            $line = rtrim($line, ',');


            // Skip empty lines or lines that are SQL comments
            if (empty($line) || substr($line, 0, 2) === '--' || substr($line, 0, 1) === '#') {
                echo "  Skipping empty or comment line.\n";
                continue; // Skip key/constraint definitions
            }

            // Check if it's a key or constraint definition
            if (preg_match('/^(PRIMARY KEY|UNIQUE KEY|KEY|CONSTRAINT|FOREIGN KEY)/i', $line)) {
                echo "  Is Key/Constraint: Yes\n";
                continue; // Skip key/constraint definitions
            }
            echo "  Processing Line [{$index}]: '{$line}'\n";
            // Attempt to extract column name from the beginning of the line
            // A column definition typically starts with a backticked name or a simple name
            if (preg_match('/^`?(\w+)`?/i', $line, $name_match)) {
                $col_name = $name_match[1];
                echo "  Extracted Column Name: '{$col_name}'\n";
                $columns[$col_name] = $line; // Store the whole line as the definition
                $normalized_columns[$col_name] = normalize_column_definition($line);
            } else {
                echo "  Failed to extract column name from line: '{$line}'\n";
            }
        }
        echo "--- End Debugging Table: {$table_name} ---\n";

        $tables[$table_name] = [
            'create_sql' => $match[0],
            'columns'    => $columns,
            'normalized_columns' => $normalized_columns,
        ];
    }
    return $tables;
}

$rebuild_sql_content = file_get_contents('/media/msbfox/SeagateBackup2/remoteproject/rebaseweb/rebuild.sql');
$webdb_sql_content = file_get_contents('/media/msbfox/SeagateBackup2/remoteproject/rebaseweb/webdb.sql');

$rebuild_tables = parse_create_table_sql($rebuild_sql_content);
$webdb_tables = parse_create_table_sql($webdb_sql_content);

echo "\n--- Rebuild.sql Parsed Tables ---\n";
foreach ($rebuild_tables as $table_name => $table_info) {
    echo "Table: {$table_name}, Columns: " . count($table_info['columns']) . "\n";
}

echo "Total tables parsed from rebuild.sql: " . count($rebuild_tables) . "\n";

echo "\n--- Webdb.sql Parsed Tables ---\n";
foreach ($webdb_tables as $table_name => $table_info) {
    echo "Table: {$table_name}, Columns: " . count($table_info['columns']) . "\n";
}

echo "Total tables parsed from webdb.sql: " . count($webdb_tables) . "\n";

$temp_sql_content = [];

foreach ($rebuild_tables as $table_name => $rebuild_table_info) {
    if (!isset($webdb_tables[$table_name])) {
        // Table is missing in webdb.sql
        $temp_sql_content[] = $rebuild_table_info['create_sql'];
    } else {
        // Table exists, compare column definitions
        $webdb_table_info = $webdb_tables[$table_name];

        // Compare normalized column definitions. Order matters for CREATE TABLE.
        // array_values to compare just the definitions, not keys (column names)
        $rebuild_normalized_cols = array_values($rebuild_table_info['normalized_columns']);
        $webdb_normalized_cols = array_values($webdb_table_info['normalized_columns']);

        // Check if the arrays of normalized column definitions are identical
        // This will check both content and order
        if ($rebuild_normalized_cols !== $webdb_normalized_cols) {
            // Columns differ, or their order differs. Recreate the table.
            $temp_sql_content[] = "DROP TABLE IF EXISTS `{$table_name}`;";
            $temp_sql_content[] = $rebuild_table_info['create_sql'];
        }
        // If they are identical, do nothing, as the table schema matches rebuild.sql
    }
}

file_put_contents('/media/msbfox/SeagateBackup2/remoteproject/rebaseweb/temp.sql', implode("\n\n", $temp_sql_content));

echo "\ntemp.sql 파일이 생성되었습니다.\n";

?>