#!/usr/bin/env python3
"""
schema_diff.py — MySQL Schema Comparator
Porównuje dwa pliki SQL (dump struktury) i generuje ALTER TABLE
do zsynchronizowania TEST ze schematem DEV.

Użycie:
    py schema_diff.py dev.sql test.sql
    py schema_diff.py dev.sql test.sql --output changes.sql
"""

import re
import sys
import argparse
from collections import OrderedDict


def parse_create_tables(sql_file):
    """Wyciąga definicje tabel z pliku SQL."""
    with open(sql_file, 'r', encoding='utf-8', errors='ignore') as f:
        content = f.read()

    tables = {}
    # Znajdź wszystkie bloki CREATE TABLE
    pattern = re.compile(
        r'CREATE TABLE `?(\w+)`?\s*\((.*?)\)\s*(?:ENGINE[^;]*)?;',
        re.DOTALL | re.IGNORECASE
    )
    for match in pattern.finditer(content):
        table_name = match.group(1)
        body = match.group(2)
        tables[table_name] = parse_columns(body)

    return tables


def parse_columns(body):
    """Parsuje kolumny i indeksy z wnętrza CREATE TABLE."""
    columns = OrderedDict()
    indexes = {}
    primary_key = None

    for line in body.split('\n'):
        line = line.strip().rstrip(',').strip()
        if not line:
            continue

        # Kolumna
        col_match = re.match(r'`(\w+)`\s+(.+)', line)
        # PRIMARY KEY
        pk_match = re.match(r'PRIMARY KEY\s*\((.+)\)', line, re.IGNORECASE)
        # INDEX / UNIQUE KEY
        idx_match = re.match(r'(UNIQUE\s+)?(?:KEY|INDEX)\s+`?(\w+)`?\s*\((.+)\)', line, re.IGNORECASE)

        if pk_match and not col_match:
            primary_key = pk_match.group(1).strip()
        elif idx_match and not col_match:
            unique = bool(idx_match.group(1))
            idx_name = idx_match.group(2)
            idx_cols = idx_match.group(3)
            indexes[idx_name] = {'cols': idx_cols, 'unique': unique}
        elif col_match:
            col_name = col_match.group(1)
            col_def = col_match.group(2).strip()
            columns[col_name] = col_def

    return {
        'columns': columns,
        'indexes': indexes,
        'primary_key': primary_key
    }


def normalize_col_def(col_def):
    """Normalizuje definicję kolumny do porównania."""
    col_def = col_def.strip().rstrip(',')
    col_def = re.sub(r'\s+', ' ', col_def)
    return col_def.upper()


def generate_diff(dev_tables, test_tables):
    """Generuje listę zmian: nowe tabele, nowe kolumny, zmienione kolumny."""
    statements = []

    # 1. Nowe tabele (są w DEV, nie ma w TEST)
    new_tables = set(dev_tables.keys()) - set(test_tables.keys())
    for table in sorted(new_tables):
        cols = dev_tables[table]['columns']
        pk = dev_tables[table]['primary_key']
        indexes = dev_tables[table]['indexes']

        col_defs = []
        for col_name, col_def in cols.items():
            col_defs.append(f"  `{col_name}` {col_def}")
        if pk:
            col_defs.append(f"  PRIMARY KEY ({pk})")
        for idx_name, idx_info in indexes.items():
            unique = "UNIQUE " if idx_info['unique'] else ""
            col_defs.append(f"  {unique}KEY `{idx_name}` ({idx_info['cols']})")

        stmt = f"CREATE TABLE `{table}` (\n" + ",\n".join(col_defs) + "\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
        statements.append(('new_table', table, stmt))

    # 2. Tabele istniejące w obu — porównaj kolumny
    common_tables = set(dev_tables.keys()) & set(test_tables.keys())
    for table in sorted(common_tables):
        dev_cols = dev_tables[table]['columns']
        test_cols = test_tables[table]['columns']

        dev_col_list = list(dev_cols.keys())

        for i, col_name in enumerate(dev_col_list):
            col_def = dev_cols[col_name]

            if col_name not in test_cols:
                # Nowa kolumna — dodaj po poprzedniej
                if i == 0:
                    position = "FIRST"
                else:
                    prev_col = dev_col_list[i - 1]
                    position = f"AFTER `{prev_col}`"
                stmt = f"ALTER TABLE `{table}` ADD COLUMN `{col_name}` {col_def} {position};"
                statements.append(('add_column', f"{table}.{col_name}", stmt))

            else:
                # Kolumna istnieje — sprawdź czy typ się zmienił
                if normalize_col_def(col_def) != normalize_col_def(test_cols[col_name]):
                    stmt = f"ALTER TABLE `{table}` MODIFY COLUMN `{col_name}` {col_def};"
                    statements.append(('modify_column', f"{table}.{col_name}", stmt))

        # Kolumny usunięte z DEV (są w TEST, nie ma w DEV)
        removed_cols = set(test_cols.keys()) - set(dev_cols.keys())
        for col_name in sorted(removed_cols):
            stmt = f"-- ALTER TABLE `{table}` DROP COLUMN `{col_name}`; -- UWAGA: kolumna usunieta z DEV"
            statements.append(('drop_column', f"{table}.{col_name}", stmt))

    # 3. Tabele usunięte z DEV (są w TEST, nie ma w DEV)
    removed_tables = set(test_tables.keys()) - set(dev_tables.keys())
    for table in sorted(removed_tables):
        stmt = f"-- DROP TABLE `{table}`; -- UWAGA: tabela usunieta z DEV"
        statements.append(('drop_table', table, stmt))

    return statements


def format_output(statements):
    """Formatuje wynik jako gotowy skrypt SQL."""
    lines = []
    lines.append("-- ============================================")
    lines.append("-- Schema diff: DEV -> TEST")
    lines.append("-- Wygenerowano przez schema_diff.py")
    lines.append("-- ============================================")
    lines.append("")

    new_tables = [s for s in statements if s[0] == 'new_table']
    add_cols = [s for s in statements if s[0] == 'add_column']
    mod_cols = [s for s in statements if s[0] == 'modify_column']
    drop_cols = [s for s in statements if s[0] == 'drop_column']
    drop_tables = [s for s in statements if s[0] == 'drop_table']

    if new_tables:
        lines.append("-- --------------------------------------------")
        lines.append(f"-- NOWE TABELE ({len(new_tables)})")
        lines.append("-- --------------------------------------------")
        for _, name, stmt in new_tables:
            lines.append(f"\n-- Tabela: {name}")
            lines.append(stmt)
        lines.append("")

    if add_cols:
        lines.append("-- --------------------------------------------")
        lines.append(f"-- NOWE KOLUMNY ({len(add_cols)})")
        lines.append("-- --------------------------------------------")
        for _, name, stmt in add_cols:
            lines.append(f"\n-- {name}")
            lines.append(stmt)
        lines.append("")

    if mod_cols:
        lines.append("-- --------------------------------------------")
        lines.append(f"-- ZMIENIONE KOLUMNY ({len(mod_cols)})")
        lines.append("-- --------------------------------------------")
        for _, name, stmt in mod_cols:
            lines.append(f"\n-- {name}")
            lines.append(stmt)
        lines.append("")

    if drop_cols:
        lines.append("-- --------------------------------------------")
        lines.append(f"-- USUNIETE KOLUMNY (zakomentowane - wykonaj recznie jesli chcesz)")
        lines.append("-- --------------------------------------------")
        for _, name, stmt in drop_cols:
            lines.append(stmt)
        lines.append("")

    if drop_tables:
        lines.append("-- --------------------------------------------")
        lines.append(f"-- USUNIETE TABELE (zakomentowane - wykonaj recznie jesli chcesz)")
        lines.append("-- --------------------------------------------")
        for _, name, stmt in drop_tables:
            lines.append(stmt)
        lines.append("")

    if not any([new_tables, add_cols, mod_cols]):
        lines.append("-- Brak roznic w strukturze! DEV i TEST sa identyczne.")

    return "\n".join(lines)


def main():
    parser = argparse.ArgumentParser(description='MySQL Schema Diff — DEV vs TEST')
    parser.add_argument('dev', help='Plik SQL z bazy DEV (mysqldump --no-data)')
    parser.add_argument('test', help='Plik SQL z bazy TEST (mysqldump --no-data)')
    parser.add_argument('--output', '-o', help='Plik wyjściowy (domyslnie: changes.sql)', default='changes.sql')
    args = parser.parse_args()

    print(f"Wczytuje DEV:  {args.dev}")
    dev_tables = parse_create_tables(args.dev)
    print(f"Znaleziono tabel w DEV:  {len(dev_tables)}")

    print(f"Wczytuje TEST: {args.test}")
    test_tables = parse_create_tables(args.test)
    print(f"Znaleziono tabel w TEST: {len(test_tables)}")

    print("\nPorownuje schematy...")
    statements = generate_diff(dev_tables, test_tables)

    new_t  = sum(1 for s in statements if s[0] == 'new_table')
    add_c  = sum(1 for s in statements if s[0] == 'add_column')
    mod_c  = sum(1 for s in statements if s[0] == 'modify_column')
    drop_c = sum(1 for s in statements if s[0] == 'drop_column')
    drop_t = sum(1 for s in statements if s[0] == 'drop_table')

    print(f"\nWyniki:")
    print(f"  Nowe tabele:        {new_t}")
    print(f"  Nowe kolumny:       {add_c}")
    print(f"  Zmienione kolumny:  {mod_c}")
    print(f"  Usuniete kolumny:   {drop_c}  (zakomentowane w pliku)")
    print(f"  Usuniete tabele:    {drop_t}  (zakomentowane w pliku)")

    output = format_output(statements)
    with open(args.output, 'w', encoding='utf-8') as f:
        f.write(output)

    print(f"\nGotowy skrypt zapisany do: {args.output}")
    print("Wykonaj go w DBeaver na bazie TEST.")


if __name__ == '__main__':
    main()