"""
Simple Database Schema Comparison Script
Compares SQL dump files to identify schema differences
"""

import re
from collections import defaultdict
import json

TARGET_SQL_FILE = r'c:\Users\Teamjoint company\Desktop\branchsystem\xygbfpsg_loans.sql'
CURRENT_SQL_FILE = r'c:\Users\Teamjoint company\Desktop\branchsystem\xygbfpsg_graz.sql'


def parse_sql_file(sql_file_path):
    """Parse SQL file to extract table structures"""
    print(f"Parsing {sql_file_path}...")
    
    try:
        with open(sql_file_path, 'r', encoding='utf-8', errors='ignore') as f:
            sql_content = f.read()
    except FileNotFoundError:
        print(f"File not found: {sql_file_path}")
        return {}
    
    tables = {}
    
    # Find all CREATE TABLE statements
    create_table_pattern = r'CREATE TABLE\s+`?(\w+)`?\s*\((.*?)\)\s*ENGINE'
    matches = re.finditer(create_table_pattern, sql_content, re.DOTALL | re.IGNORECASE)
    
    for match in matches:
        table_name = match.group(1)
        table_def = match.group(2)
        
        columns = {}
        constraints = []
        
        # Parse column definitions
        lines = table_def.split('\n')
        for line in lines:
            line = line.strip().rstrip(',')
            if not line:
                continue
            
            # Skip constraints
            if line.upper().startswith(('PRIMARY KEY', 'KEY', 'UNIQUE KEY', 'CONSTRAINT', 'FOREIGN KEY', 'INDEX')):
                constraints.append(line)
                continue
            
            # Parse column definition
            parts = line.split()
            if len(parts) >= 2:
                col_name = parts[0].strip('`')
                col_type = parts[1].upper()
                
                # Extract additional attributes
                attributes = {
                    'type': col_type,
                    'nullable': 'NOT NULL' not in line.upper(),
                    'default': None,
                    'auto_increment': 'AUTO_INCREMENT' in line.upper(),
                    'definition': line
                }
                
                # Extract default value
                default_match = re.search(r"DEFAULT\s+(['\"].*?['\"]|\S+)", line, re.IGNORECASE)
                if default_match:
                    attributes['default'] = default_match.group(1)
                
                columns[col_name] = attributes
        
        tables[table_name] = {
            'columns': columns,
            'constraints': constraints
        }
    
    print(f"Found {len(tables)} tables")
    return tables


def normalize_type(type_str):
    """Normalize MySQL data types for comparison"""
    type_str = type_str.upper()
    
    # Remove size specifications for basic comparison
    base_type = re.sub(r'\(.*?\)', '', type_str)
    
    return base_type


def compare_schemas(current, target):
    """Compare current and target schemas"""
    comparison = {
        'missing_tables': [],
        'extra_tables': [],
        'missing_columns': defaultdict(list),
        'extra_columns': defaultdict(list),
        'type_differences': defaultdict(list),
        'summary': {}
    }
    
    current_tables = set(current.keys())
    target_tables = set(target.keys())
    
    # Find missing and extra tables
    comparison['missing_tables'] = sorted(list(target_tables - current_tables))
    comparison['extra_tables'] = sorted(list(current_tables - target_tables))
    
    # Compare common tables
    common_tables = current_tables & target_tables
    
    for table_name in sorted(common_tables):
        current_cols = set(current[table_name]['columns'].keys())
        target_cols = set(target[table_name]['columns'].keys())
        
        # Find missing and extra columns
        missing_cols = target_cols - current_cols
        extra_cols = current_cols - target_cols
        
        if missing_cols:
            comparison['missing_columns'][table_name] = sorted(list(missing_cols))
        
        if extra_cols:
            comparison['extra_columns'][table_name] = sorted(list(extra_cols))
        
        # Compare column types for common columns
        common_cols = current_cols & target_cols
        for col_name in sorted(common_cols):
            current_col = current[table_name]['columns'][col_name]
            target_col = target[table_name]['columns'][col_name]
            
            # Compare types
            current_type = normalize_type(current_col['type'])
            target_type = normalize_type(target_col['type'])
            
            if current_type != target_type:
                comparison['type_differences'][table_name].append({
                    'column': col_name,
                    'current_type': current_col['type'],
                    'target_type': target_col['type']
                })
    
    # Generate summary
    comparison['summary'] = {
        'total_missing_tables': len(comparison['missing_tables']),
        'total_extra_tables': len(comparison['extra_tables']),
        'total_tables_with_missing_columns': len(comparison['missing_columns']),
        'total_tables_with_extra_columns': len(comparison['extra_columns']),
        'total_tables_with_type_differences': len(comparison['type_differences'])
    }
    
    return comparison


def generate_report(comparison, target_schema, output_file):
    """Generate a detailed comparison report"""
    report_lines = []
    
    report_lines.append("=" * 80)
    report_lines.append("DATABASE SCHEMA COMPARISON REPORT")
    report_lines.append("=" * 80)
    report_lines.append("")
    report_lines.append("Current Database: xygbfpsg_graz (Django)")
    report_lines.append("Target Schema: xygbfpsg_loans.sql (Grazuri)")
    report_lines.append("")
    
    # Summary
    report_lines.append("=" * 80)
    report_lines.append("SUMMARY")
    report_lines.append("=" * 80)
    report_lines.append("")
    summary = comparison['summary']
    report_lines.append(f"Missing Tables in Current Schema: {summary['total_missing_tables']}")
    report_lines.append(f"Extra Tables in Current Schema: {summary['total_extra_tables']}")
    report_lines.append(f"Tables with Missing Columns: {summary['total_tables_with_missing_columns']}")
    report_lines.append(f"Tables with Extra Columns: {summary['total_tables_with_extra_columns']}")
    report_lines.append(f"Tables with Type Differences: {summary['total_tables_with_type_differences']}")
    report_lines.append("")
    
    # Missing Tables
    if comparison['missing_tables']:
        report_lines.append("=" * 80)
        report_lines.append("MISSING TABLES IN CURRENT SCHEMA")
        report_lines.append("=" * 80)
        report_lines.append("")
        report_lines.append("The following tables exist in the target Grazuri schema but are missing")
        report_lines.append("in the current Django database:")
        report_lines.append("")
        for i, table in enumerate(comparison['missing_tables'], 1):
            # Get column count from target schema
            col_count = len(target_schema[table]['columns'])
            report_lines.append(f"{i}. {table} ({col_count} columns)")
        report_lines.append("")
    
    # Extra Tables
    if comparison['extra_tables']:
        report_lines.append("=" * 80)
        report_lines.append("EXTRA TABLES IN CURRENT SCHEMA")
        report_lines.append("=" * 80)
        report_lines.append("")
        report_lines.append("The following tables exist in the current Django database but are not")
        report_lines.append("in the target Grazuri schema (these are Django-specific tables):")
        report_lines.append("")
        for i, table in enumerate(comparison['extra_tables'], 1):
            report_lines.append(f"{i}. {table}")
        report_lines.append("")
    
    # Missing Columns
    if comparison['missing_columns']:
        report_lines.append("=" * 80)
        report_lines.append("MISSING COLUMNS IN EXISTING TABLES")
        report_lines.append("=" * 80)
        report_lines.append("")
        report_lines.append("These columns exist in the target schema but are missing in the current")
        report_lines.append("database. Django migrations will need to add these columns.")
        report_lines.append("")
        for table_name in sorted(comparison['missing_columns'].keys()):
            columns = comparison['missing_columns'][table_name]
            report_lines.append(f"Table: {table_name}")
            report_lines.append(f"Missing Columns ({len(columns)}):")
            for col in columns:
                # Get column details from target schema
                col_info = target_schema[table_name]['columns'][col]
                report_lines.append(f"  - {col}: {col_info['type']}")
            report_lines.append("")
    
    # Extra Columns
    if comparison['extra_columns']:
        report_lines.append("=" * 80)
        report_lines.append("EXTRA COLUMNS IN EXISTING TABLES")
        report_lines.append("=" * 80)
        report_lines.append("")
        report_lines.append("These columns exist in the current database but not in the target schema.")
        report_lines.append("These are likely Django-specific fields and should be preserved.")
        report_lines.append("")
        for table_name in sorted(comparison['extra_columns'].keys()):
            columns = comparison['extra_columns'][table_name]
            report_lines.append(f"Table: {table_name}")
            report_lines.append(f"Extra Columns ({len(columns)}):")
            for col in columns:
                report_lines.append(f"  - {col}")
            report_lines.append("")
    
    # Type Differences
    if comparison['type_differences']:
        report_lines.append("=" * 80)
        report_lines.append("DATA TYPE DIFFERENCES")
        report_lines.append("=" * 80)
        report_lines.append("")
        report_lines.append("These columns have different data types between current and target schemas.")
        report_lines.append("")
        for table_name in sorted(comparison['type_differences'].keys()):
            diffs = comparison['type_differences'][table_name]
            report_lines.append(f"Table: {table_name}")
            report_lines.append(f"Type Differences ({len(diffs)}):")
            for diff in diffs:
                report_lines.append(f"  Column: {diff['column']}")
                report_lines.append(f"    Current: {diff['current_type']}")
                report_lines.append(f"    Target:  {diff['target_type']}")
            report_lines.append("")
    
    # Recommendations
    report_lines.append("=" * 80)
    report_lines.append("RECOMMENDATIONS")
    report_lines.append("=" * 80)
    report_lines.append("")
    report_lines.append("1. CREATE DJANGO MODELS FOR MISSING TABLES:")
    report_lines.append("   - Create models for the following tables:")
    for table in comparison['missing_tables'][:10]:  # Show first 10
        report_lines.append(f"     * {table}")
    if len(comparison['missing_tables']) > 10:
        report_lines.append(f"     ... and {len(comparison['missing_tables']) - 10} more")
    report_lines.append("")
    
    report_lines.append("2. ADD MISSING COLUMNS TO EXISTING MODELS:")
    report_lines.append("   - Update Django models to include missing columns")
    report_lines.append("   - Generate migrations using: python manage.py makemigrations")
    report_lines.append("")
    
    report_lines.append("3. REVIEW DATA TYPE DIFFERENCES:")
    report_lines.append("   - Ensure Django field types match target schema requirements")
    report_lines.append("   - Consider data migration if types are incompatible")
    report_lines.append("")
    
    report_lines.append("4. PRESERVE DJANGO-SPECIFIC TABLES:")
    report_lines.append("   - Keep Django admin, auth, and session tables")
    report_lines.append("   - These are required for Django functionality")
    report_lines.append("")
    
    report_lines.append("5. TEST MIGRATIONS:")
    report_lines.append("   - Test all migrations in development environment first")
    report_lines.append("   - Verify data integrity after applying migrations")
    report_lines.append("   - Create database backup before production deployment")
    report_lines.append("")
    
    # Write report
    report_content = '\n'.join(report_lines)
    with open(output_file, 'w', encoding='utf-8') as f:
        f.write(report_content)
    
    return report_content


def main():
    print("=" * 80)
    print("DATABASE SCHEMA COMPARISON TOOL")
    print("=" * 80)
    print()
    
    # Parse target SQL file
    target_schema = parse_sql_file(TARGET_SQL_FILE)
    if not target_schema:
        print("ERROR: Could not parse target schema file")
        return
    print()
    
    # Parse current SQL file
    current_schema = parse_sql_file(CURRENT_SQL_FILE)
    if not current_schema:
        print("ERROR: Could not parse current schema file")
        return
    print()
    
    # Compare schemas
    print("Comparing schemas...")
    comparison = compare_schemas(current_schema, target_schema)
    print()
    
    # Generate report
    output_file = r'c:\Users\Teamjoint company\Desktop\branchsystem\schema_comparison_report.txt'
    print(f"Generating comparison report...")
    report_content = generate_report(comparison, target_schema, output_file)
    print(f"Report saved to: {output_file}")
    print()
    
    # Save JSON for programmatic access
    json_file = r'c:\Users\Teamjoint company\Desktop\branchsystem\schema_comparison.json'
    with open(json_file, 'w', encoding='utf-8') as f:
        # Convert defaultdict to dict for JSON serialization
        json_data = {
            'missing_tables': comparison['missing_tables'],
            'extra_tables': comparison['extra_tables'],
            'missing_columns': dict(comparison['missing_columns']),
            'extra_columns': dict(comparison['extra_columns']),
            'type_differences': dict(comparison['type_differences']),
            'summary': comparison['summary']
        }
        json.dump(json_data, f, indent=2)
    print(f"JSON data saved to: {json_file}")
    print()
    
    # Print summary
    print("=" * 80)
    print("COMPARISON SUMMARY")
    print("=" * 80)
    print(f"Missing Tables: {comparison['summary']['total_missing_tables']}")
    print(f"Extra Tables: {comparison['summary']['total_extra_tables']}")
    print(f"Tables with Missing Columns: {comparison['summary']['total_tables_with_missing_columns']}")
    print(f"Tables with Extra Columns: {comparison['summary']['total_tables_with_extra_columns']}")
    print(f"Tables with Type Differences: {comparison['summary']['total_tables_with_type_differences']}")
    print()
    
    print("Schema comparison complete!")
    print(f"Review the detailed report at: {output_file}")


if __name__ == '__main__':
    main()