"""
Compare current database schema with SQL file schema
"""
import os
import django
import re
from collections import defaultdict

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings')
django.setup()

from django.db import connection

def get_current_tables():
    """Get all tables from current database"""
    with connection.cursor() as cursor:
        cursor.execute("SHOW TABLES")
        return {row[0] for row in cursor.fetchall()}

def get_current_columns(table_name):
    """Get columns for a table from current database"""
    with connection.cursor() as cursor:
        cursor.execute(f"DESCRIBE `{table_name}`")
        columns = {}
        for row in cursor.fetchall():
            field, type_, null, key, default, extra = row
            columns[field] = {
                'type': type_,
                'null': null,
                'key': key,
                'default': default,
                'extra': extra
            }
        return columns

def parse_sql_file(filepath):
    """Parse SQL file to extract table structures"""
    with open(filepath, 'r', encoding='utf-8') as f:
        content = f.read()
    
    tables = {}
    
    # Find all CREATE TABLE statements
    create_pattern = r'CREATE TABLE `([^`]+)` \((.*?)\) ENGINE='
    matches = re.finditer(create_pattern, content, re.DOTALL)
    
    for match in matches:
        table_name = match.group(1)
        table_def = match.group(2)
        
        columns = {}
        # Parse column definitions
        lines = table_def.split('\n')
        for line in lines:
            line = line.strip()
            if line.startswith('`') and not line.startswith('PRIMARY KEY') and not line.startswith('UNIQUE KEY') and not line.startswith('KEY'):
                # Extract column name
                col_match = re.match(r'`([^`]+)`\s+(.+?)(?:,|$)', line)
                if col_match:
                    col_name = col_match.group(1)
                    col_def = col_match.group(2).rstrip(',')
                    columns[col_name] = col_def
        
        tables[table_name] = columns
    
    return tables

def main():
    print("=" * 80)
    print("DATABASE SCHEMA COMPARISON")
    print("=" * 80)
    print()
    
    # Get current database info
    print("Fetching current database schema...")
    current_tables = get_current_tables()
    print(f"Current database has {len(current_tables)} tables")
    
    # Parse SQL file
    sql_file = r"C:\Users\PC\Desktop\branch-system\users\xygbfpsg_graz (1).sql"
    print(f"\nParsing SQL file: {sql_file}")
    sql_tables = parse_sql_file(sql_file)
    print(f"SQL file defines {len(sql_tables)} tables")
    
    print("\n" + "=" * 80)
    print("DIFFERENCES FOUND")
    print("=" * 80)
    
    # Tables in SQL but not in current DB
    missing_tables = set(sql_tables.keys()) - current_tables
    if missing_tables:
        print(f"\n❌ MISSING TABLES (in SQL file but not in current DB): {len(missing_tables)}")
        for table in sorted(missing_tables):
            print(f"   - {table}")
    
    # Tables in current DB but not in SQL
    extra_tables = current_tables - set(sql_tables.keys())
    if extra_tables:
        print(f"\n⚠️  EXTRA TABLES (in current DB but not in SQL file): {len(extra_tables)}")
        for table in sorted(extra_tables):
            print(f"   - {table}")
    
    # Compare columns for common tables
    common_tables = current_tables & set(sql_tables.keys())
    print(f"\n✓ COMMON TABLES: {len(common_tables)}")
    
    tables_with_differences = []
    
    for table in sorted(common_tables):
        current_cols = get_current_columns(table)
        sql_cols = sql_tables[table]
        
        current_col_names = set(current_cols.keys())
        sql_col_names = set(sql_cols.keys())
        
        missing_cols = sql_col_names - current_col_names
        extra_cols = current_col_names - sql_col_names
        
        if missing_cols or extra_cols:
            tables_with_differences.append(table)
            print(f"\n📋 Table: {table}")
            
            if missing_cols:
                print(f"   ❌ Missing columns ({len(missing_cols)}):")
                for col in sorted(missing_cols):
                    print(f"      - {col}: {sql_cols[col]}")
            
            if extra_cols:
                print(f"   ⚠️  Extra columns ({len(extra_cols)}):")
                for col in sorted(extra_cols):
                    col_info = current_cols[col]
                    print(f"      - {col}: {col_info['type']}")
    
    # Summary
    print("\n" + "=" * 80)
    print("SUMMARY")
    print("=" * 80)
    print(f"Missing tables: {len(missing_tables)}")
    print(f"Extra tables: {len(extra_tables)}")
    print(f"Tables with column differences: {len(tables_with_differences)}")
    
    if not missing_tables and not extra_tables and not tables_with_differences:
        print("\n✅ Database schemas match perfectly!")
    else:
        print("\n⚠️  Database schemas have differences that need attention.")
    
    print("\n" + "=" * 80)

if __name__ == '__main__':
    main()