sql differece between two files

import re

FILE1 = "erp1.sql"
FILE2 = "erp2.sql"
OUTPUT_FILE = "schema_diff.txt"


def remove_sql_comments(sql_text: str) -> str:
    # Remove block comments /* ... */
    sql_text = re.sub(r"/\*.*?\*/", "", sql_text, flags=re.S)

    # Remove full-line comments starting with --
    sql_text = re.sub(r"^\s*--.*$", "", sql_text, flags=re.M)

    # Remove full-line comments starting with #
    sql_text = re.sub(r"^\s*#.*$", "", sql_text, flags=re.M)

    return sql_text


def extract_create_tables(sql_text: str):
    """
    Returns dict:
    {
        table_name: full CREATE TABLE body text
    }
    """
    sql_text = remove_sql_comments(sql_text)

    pattern = re.compile(
        r"CREATE\s+TABLE\s+`?([a-zA-Z0-9_]+)`?\s*\((.*?)\)\s*(ENGINE|TYPE|COMMENT|DEFAULT|;)",
        re.I | re.S
    )

    tables = {}
    for match in pattern.finditer(sql_text):
        table_name = match.group(1)
        table_body = match.group(2)
        tables[table_name] = table_body

    return tables


def split_column_lines(table_body: str):
    """
    Split CREATE TABLE body into lines, but safely.
    Usually one column/index per line in MySQL dumps.
    """
    lines = []
    current = []
    paren_level = 0

    for ch in table_body:
        if ch == '(':
            paren_level += 1
        elif ch == ')':
            paren_level -= 1

        if ch == ',' and paren_level == 0:
            line = ''.join(current).strip()
            if line:
                lines.append(line)
            current = []
        else:
            current.append(ch)

    last = ''.join(current).strip()
    if last:
        lines.append(last)

    return lines


def normalize_spaces(s: str) -> str:
    return re.sub(r"\s+", " ", s.strip())


def parse_columns(table_body: str):
    """
    Returns dict:
    {
        column_name: {
            "type": "...",
            "full": "..."
        }
    }

    Ignores PRIMARY KEY, KEY, UNIQUE KEY, CONSTRAINT, INDEX, etc.
    """
    lines = split_column_lines(table_body)
    columns = {}

    skip_starts = (
        "PRIMARY KEY", "UNIQUE KEY", "KEY", "INDEX",
        "CONSTRAINT", "FULLTEXT KEY", "SPATIAL KEY"
    )

    for line in lines:
        clean = normalize_spaces(line)

        upper = clean.upper()
        if upper.startswith(skip_starts):
            continue

        # Match column definition: `colname` datatype ....
        m = re.match(r"^`([^`]+)`\s+(.*)$", clean)
        if not m:
            # fallback for non-backtick column names
            m = re.match(r"^([a-zA-Z0-9_]+)\s+(.*)$", clean)
            if not m:
                continue

        col_name = m.group(1)
        remainder = m.group(2).strip()

        # Extract type = first token, but allow types like:
        # decimal(10,2), varchar(100), enum('a','b')
        type_match = re.match(
            r"^([a-zA-Z]+(?:\s+[a-zA-Z]+)?(?:\([^\)]*\))?(?:\s+unsigned)?(?:\s+zerofill)?)\b(.*)$",
            remainder,
            flags=re.I
        )

        if type_match:
            col_type = normalize_spaces(type_match.group(1))
        else:
            col_type = remainder.split()[0]

        columns[col_name] = {
            "type": col_type,
            "full": normalize_spaces(remainder)
        }

    return columns


def parse_schema(sql_file: str):
    with open(sql_file, "r", encoding="utf-8", errors="ignore") as f:
        sql_text = f.read()

    create_tables = extract_create_tables(sql_text)

    schema = {}
    for table_name, table_body in create_tables.items():
        schema[table_name] = parse_columns(table_body)

    return schema


def compare_schemas(schema1, schema2, name1="erp1.sql", name2="erp2.sql"):
    differences = []

    tables1 = set(schema1.keys())
    tables2 = set(schema2.keys())

    only_in_1 = sorted(tables1 - tables2)
    only_in_2 = sorted(tables2 - tables1)

    if only_in_1:
        differences.append(f"Tables only in {name1}:")
        for t in only_in_1:
            differences.append(f"  - {t}")
        differences.append("")

    if only_in_2:
        differences.append(f"Tables only in {name2}:")
        for t in only_in_2:
            differences.append(f"  - {t}")
        differences.append("")

    common_tables = sorted(tables1 & tables2)

    for table in common_tables:
        cols1 = schema1[table]
        cols2 = schema2[table]

        colset1 = set(cols1.keys())
        colset2 = set(cols2.keys())

        only_cols1 = sorted(colset1 - colset2)
        only_cols2 = sorted(colset2 - colset1)

        table_diff = []

        if only_cols1:
            table_diff.append(f"  Columns only in {name1}:")
            for c in only_cols1:
                table_diff.append(f"    - {c} : {cols1[c]['full']}")

        if only_cols2:
            table_diff.append(f"  Columns only in {name2}:")
            for c in only_cols2:
                table_diff.append(f"    - {c} : {cols2[c]['full']}")

        common_cols = sorted(colset1 & colset2)

        for col in common_cols:
            type1 = cols1[col]["type"]
            type2 = cols2[col]["type"]
            full1 = cols1[col]["full"]
            full2 = cols2[col]["full"]

            if normalize_spaces(type1).lower() != normalize_spaces(type2).lower():
                table_diff.append(f"  Datatype changed for column '{col}':")
                table_diff.append(f"    {name1}: {type1}")
                table_diff.append(f"    {name2}: {type2}")

            if normalize_spaces(full1).lower() != normalize_spaces(full2).lower():
                if normalize_spaces(type1).lower() == normalize_spaces(type2).lower():
                    table_diff.append(f"  Definition changed for column '{col}':")
                    table_diff.append(f"    {name1}: {full1}")
                    table_diff.append(f"    {name2}: {full2}")

        if table_diff:
            differences.append(f"Table: {table}")
            differences.extend(table_diff)
            differences.append("")

    if not differences:
        differences.append("No schema differences found.")

    return "\n".join(differences)


def main():
    schema1 = parse_schema(FILE1)
    schema2 = parse_schema(FILE2)

    result = compare_schemas(schema1, schema2, FILE1, FILE2)

    with open(OUTPUT_FILE, "w", encoding="utf-8") as f:
        f.write(result)

    print(f"Done. Differences written to: {OUTPUT_FILE}")


if __name__ == "__main__":
    main()