"""
Script to parse xygbfpsg_loans.sql and generate complete Django models for all 62 missing tables.

This script:
1. Parses the SQL file to extract table structures
2. Generates complete Django model definitions with all fields
3. Creates proper field types, constraints, and relationships
4. Outputs a complete models.py file for the grazuri app
"""

import re
from pathlib import Path


# SQL to Django field type mapping
SQL_TO_DJANGO_FIELD = {
    'int': 'models.IntegerField',
    'bigint': 'models.BigIntegerField',
    'tinyint(1)': 'models.BooleanField',
    'varchar': 'models.CharField',
    'text': 'models.TextField',
    'date': 'models.DateField',
    'datetime': 'models.DateTimeField',
    'timestamp': 'models.DateTimeField',
    'decimal': 'models.DecimalField',
    'double': 'models.DecimalField',
    'float': 'models.FloatField',
}


def parse_sql_file(sql_file_path):
    """Parse SQL file and extract table definitions"""
    with open(sql_file_path, 'r', encoding='utf-8', errors='ignore') as f:
        content = f.read()
    
    # Find all CREATE TABLE statements
    table_pattern = r'CREATE TABLE `(\w+)` \((.*?)\) ENGINE='
    tables = {}
    
    for match in re.finditer(table_pattern, content, re.DOTALL):
        table_name = match.group(1)
        table_def = match.group(2)
        
        # Parse columns
        columns = []
        for line in table_def.split('\n'):
            line = line.strip()
            if line and not line.startswith('PRIMARY KEY') and not line.startswith('KEY') and not line.startswith('UNIQUE'):
                # Extract column definition
                col_match = re.match(r'`(\w+)` (\w+)(\([\d,]+\))?(.*)', line.rstrip(','))
                if col_match:
                    col_name = col_match.group(1)
                    col_type = col_match.group(2)
                    col_size = col_match.group(3) if col_match.group(3) else ''
                    col_constraints = col_match.group(4) if col_match.group(4) else ''
                    
                    columns.append({
                        'name': col_name,
                        'type': col_type,
                        'size': col_size,
                        'constraints': col_constraints,
                        'is_auto_increment': False
                    })
        
        tables[table_name] = columns
    
    # Parse ALTER TABLE statements to find AUTO_INCREMENT fields
    alter_pattern = r'ALTER TABLE `(\w+)`\s+MODIFY `(\w+)` \w+.*?AUTO_INCREMENT'
    for match in re.finditer(alter_pattern, content, re.IGNORECASE):
        table_name = match.group(1)
        col_name = match.group(2)
        
        if table_name in tables:
            for col in tables[table_name]:
                if col['name'] == col_name:
                    col['is_auto_increment'] = True
                    break
    
    return tables


def sql_type_to_django_field(col_type, col_size, col_constraints):
    """Convert SQL column type to Django field"""
    col_type_lower = col_type.lower()
    
    # Handle special cases
    if col_type_lower == 'tinyint' and '(1)' in col_size:
        return 'models.BooleanField(default=False)'
    
    # Get base field type
    django_field = None
    for sql_type, django_type in SQL_TO_DJANGO_FIELD.items():
        if col_type_lower.startswith(sql_type):
            django_field = django_type
            break
    
    if not django_field:
        django_field = 'models.CharField'  # Default fallback
    
    # Build field parameters
    params = []
    
    # Handle varchar max_length
    if 'CharField' in django_field and col_size:
        size_match = re.search(r'\((\d+)\)', col_size)
        if size_match:
            params.append(f'max_length={size_match.group(1)}')
    
    # Handle decimal fields
    if 'DecimalField' in django_field and col_size:
        size_match = re.search(r'\((\d+),(\d+)\)', col_size)
        if size_match:
            params.append(f'max_digits={size_match.group(1)}')
            params.append(f'decimal_places={size_match.group(2)}')
        else:
            # Default decimal parameters
            params.append('max_digits=18')
            params.append('decimal_places=2')
    
    # Handle NULL constraints
    if 'NOT NULL' not in col_constraints.upper():
        if 'BooleanField' not in django_field:
            params.append('null=True')
            params.append('blank=True')
    
    # Handle DEFAULT values
    default_match = re.search(r"DEFAULT '([^']*)'", col_constraints, re.IGNORECASE)
    if not default_match:
        default_match = re.search(r'DEFAULT (\S+)', col_constraints, re.IGNORECASE)
    
    if default_match:
        default_val = default_match.group(1)
        if default_val.upper() == 'CURRENT_TIMESTAMP':
            if 'DateTimeField' in django_field:
                params.append('auto_now_add=True')
        elif default_val.upper() not in ['NULL', '0000-00-00', '0000-00-00 00:00:00']:
            if default_val.replace('.', '').replace('-', '').isdigit():
                params.append(f'default={default_val}')
            else:
                params.append(f"default='{default_val}'")
    
    # Handle AUTO_INCREMENT
    if 'AUTO_INCREMENT' in col_constraints.upper():
        return 'models.AutoField(primary_key=True)'
    
    # Build final field string
    if params:
        return f"{django_field}({', '.join(params)})"
    else:
        return f"{django_field}()"


def generate_model_class(table_name, columns):
    """Generate Django model class code"""
    # Convert table name to class name (PascalCase)
    class_name = ''.join(word.capitalize() for word in table_name.split('_'))
    
    # Handle special cases
    if class_name == 'User':
        class_name = 'GrazuriUser'
    elif class_name == 'Hiw':
        class_name = 'HIW'
    elif class_name == 'Faqs':
        class_name = 'FAQs'
    elif class_name == 'Etemplates':
        class_name = 'ETemplates'
    elif class_name == 'Glcodes':
        class_name = 'GLCodes'
    elif class_name == 'B2Cpaymentsresults':
        class_name = 'B2CPaymentsResults'
    elif class_name == 'Stkpushresults':
        class_name = 'STKPushResults'
    elif class_name == 'Sms':
        class_name = 'SMS'
    elif class_name == 'SmsMessages':
        class_name = 'SMSMessages'
    elif class_name == 'Sasapayipn':
        class_name = 'SasaPayIPN'
    
    # Generate model code
    model_code = f'\n\nclass {class_name}(models.Model):\n'
    model_code += f'    """{class_name} model for Grazuri schema compatibility"""\n'
    
    # Add fields
    for col in columns:
        field_name = col['name']
        
        # Handle AUTO_INCREMENT / PRIMARY KEY first
        if col.get('is_auto_increment', False) or 'AUTO_INCREMENT' in col['constraints'].upper():
            model_code += f'    {field_name} = models.AutoField(primary_key=True)\n'
            continue
        
        # Handle reserved Python keywords
        if field_name in ['from', 'class', 'type', 'pass']:
            field_name_py = field_name + '_field'
            db_column = f", db_column='{col['name']}'"
        else:
            field_name_py = field_name
            db_column = ''
        
        # Handle camelCase column names
        if field_name != field_name.lower() and '_' not in field_name:
            db_column = f", db_column='{col['name']}'"
            field_name_py = ''.join(['_' + c.lower() if c.isupper() else c for c in field_name]).lstrip('_')
        
        django_field = sql_type_to_django_field(col['type'], col['size'], col['constraints'])
        
        # Add db_column if needed
        if db_column and 'primary_key=True' not in django_field:
            django_field = django_field.rstrip(')') + db_column + ')'
        
        model_code += f'    {field_name_py} = {django_field}\n'
    
    # Add Meta class
    model_code += f'\n    class Meta:\n'
    model_code += f"        db_table = '{table_name}'\n"
    model_code += f"        verbose_name = '{class_name}'\n"
    model_code += f"        verbose_name_plural = '{class_name}s'\n"
    
    # Add __str__ method
    model_code += f'\n    def __str__(self):\n'
    if 'id' in [col['name'] for col in columns]:
        model_code += f"        return f'{class_name} #{{self.id}}'\n"
    else:
        primary_key = next((col['name'] for col in columns if 'AUTO_INCREMENT' in col['constraints']), None)
        if primary_key:
            model_code += f"        return f'{class_name} #{{self.{primary_key}}}'\n"
        else:
            model_code += f"        return f'{class_name} instance'\n"
    
    return model_code


def main():
    """Main function to generate models"""
    sql_file = Path(r'C:\Users\Teamjoint company\Desktop\branchsystem\xygbfpsg_loans.sql')
    output_file = Path(r'C:\Users\Teamjoint company\Desktop\branchsystem\grazuri\models_generated.py')
    
    print("Parsing SQL file...")
    tables = parse_sql_file(sql_file)
    
    print(f"Found {len(tables)} tables")
    
    # Generate models file
    models_code = '"""\nDjango models for Grazuri schema compatibility.\n\n'
    models_code += 'This file contains complete Django models for all 62 tables that exist in the\n'
    models_code += 'Grazuri schema but are missing from the current Django system.\n\n'
    models_code += 'Auto-generated from xygbfpsg_loans.sql\n'
    models_code += 'Created as part of the Haven Grazuri Investment Limited rebranding migration.\n'
    models_code += '"""\n\n'
    models_code += 'from django.db import models\n'
    models_code += 'from django.contrib.auth import get_user_model\n\n'
    models_code += 'User = get_user_model()\n'
    
    # List of 62 missing tables from the schema comparison report
    missing_tables = [
        'aboutus', 'additional_fees', 'affordability_check', 'attachment', 'b2cpaymentsresults',
        'backup', 'bank_accounts', 'banner', 'basic', 'battachment', 'borrowers', 'borrowers_salaries',
        'branches', 'bureau_records', 'bureau_submissions', 'collateral', 'comments', 'countries',
        'documents_required', 'emp_permission', 'emp_role', 'employer_details', 'etemplates', 'faqs',
        'fin_info', 'footer', 'gl_codes', 'hiw', 'journal_transactions', 'loan_additional_settings',
        'loan_disbursements', 'loan_fees', 'loan_fees_settings', 'loan_feespayments', 'loan_guarantors',
        'loan_info', 'loan_reschedule_table', 'loan_settings', 'loan_statuses', 'loanfees',
        'loanprocessingfeesx', 'message', 'mywallet', 'next_of_kin_details', 'pay_schedule',
        'payment_schedule', 'paymenterrors', 'payments', 'products', 'reversed_payments',
        'saccos_members', 'sasapayipn', 'sms', 'sms_messages', 'stkpushresults', 'system_transactions',
        'systemset', 'temp_borrowers', 'transaction', 'twallet', 'unknown_payments', 'user'
    ]
    
    # Generate models for missing tables
    for table_name in missing_tables:
        if table_name in tables:
            print(f"Generating model for {table_name}...")
            models_code += generate_model_class(table_name, tables[table_name])
        else:
            print(f"Warning: Table {table_name} not found in SQL file")
    
    # Write to file
    print(f"\nWriting models to {output_file}...")
    with open(output_file, 'w', encoding='utf-8') as f:
        f.write(models_code)
    
    print(f"\nGenerated {len(missing_tables)} models successfully!")
    print(f"Output file: {output_file}")
    print("\nNext steps:")
    print("1. Review the generated models in grazuri/models_generated.py")
    print("2. Replace grazuri/models.py with the generated file (or merge carefully)")
    print("3. Run: python manage.py makemigrations grazuri")
    print("4. Run: python manage.py migrate grazuri")


if __name__ == '__main__':
    main()