#!/usr/bin/env python3
"""
Fix duplicate column names in MySQL schema (case-insensitive duplicates)
"""

import re

def fix_duplicate_columns(input_file, output_file):
    with open(input_file, 'r', encoding='utf-8') as f:
        lines = f.readlines()
    
    output_lines = []
    i = 0
    tables_fixed = 0
    
    while i < len(lines):
        line = lines[i]
        output_lines.append(line)
        
        # Check for CREATE TABLE
        if 'CREATE TABLE' in line:
            table_lines = [line]
            j = i + 1
            seen_columns = set()
            in_table = True
            
            # Collect table definition
            while j < len(lines) and in_table:
                next_line = lines[j]
                
                # Check for closing );
                if ');' in next_line:
                    table_lines.append(next_line)
                    in_table = False
                    break
                
                # Check for duplicate columns (case-insensitive)
                col_match = re.search(r'`([^`]+)`', next_line)
                if col_match:
                    col_name = col_match.group(1)
                    col_name_lower = col_name.lower()
                    
                    if col_name_lower in seen_columns:
                        # Skip this duplicate
                        print(f"  Removing duplicate column '{col_name}' at line {j+1}")
                        j += 1
                        continue
                    else:
                        seen_columns.add(col_name_lower)
                
                table_lines.append(next_line)
                j += 1
            
            # Fix trailing commas before );
            if len(table_lines) > 1:
                closing_line = table_lines[-1]
                if ');' in closing_line:
                    # Check second-to-last line for trailing comma
                    if len(table_lines) > 2:
                        prev_line = table_lines[-2].rstrip()
                        if prev_line.endswith(','):
                            table_lines[-2] = prev_line[:-1] + '\n'
                            tables_fixed += 1
            
            # Write table lines (skip first, already written)
            output_lines.extend(table_lines[1:])
            i = j
        else:
            i += 1
    
    with open(output_file, 'w', encoding='utf-8') as f:
        f.writelines(output_lines)
    
    print(f"\n✅ Fixed duplicate columns in {tables_fixed} tables")

if __name__ == "__main__":
    fix_duplicate_columns("mysql/schema_mysql_20251113_185155.sql", "mysql/schema_mysql_20251113_185155.sql")