#!/usr/bin/env python
"""
Directly add missing columns to production database using raw SQL
This bypasses Django migrations entirely
"""
import os
import sys
import django

# Set up Django environment
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
django.setup()

from django.db import connection

def execute_sql(sql, description):
    """Execute SQL and handle errors"""
    try:
        with connection.cursor() as cursor:
            cursor.execute(sql)
            print(f"  ✓ {description}")
            return True
    except Exception as e:
        error_msg = str(e)
        if "Duplicate column" in error_msg or "already exists" in error_msg:
            print(f"  ⊙ {description} (already exists)")
            return True
        else:
            print(f"  ✗ {description}: {error_msg}")
            return False

def main():
    print("=" * 60)
    print("Direct Database Column Addition")
    print("=" * 60)
    
    print("\nAdding columns to loan_products table...")
    print("-" * 60)
    
    success = True
    
    # Add penalty_mode
    success &= execute_sql(
        "ALTER TABLE loan_products ADD COLUMN penalty_mode VARCHAR(20) NOT NULL DEFAULT 'automatic'",
        "Add penalty_mode column"
    )
    
    # Add penalty_frequency
    success &= execute_sql(
        "ALTER TABLE loan_products ADD COLUMN penalty_frequency VARCHAR(20) NOT NULL DEFAULT 'daily'",
        "Add penalty_frequency column"
    )
    
    # Modify late_payment_penalty
    success &= execute_sql(
        "ALTER TABLE loan_products MODIFY COLUMN late_payment_penalty DECIMAL(5,2) NOT NULL DEFAULT 0.00",
        "Update late_payment_penalty type"
    )
    
    print("\nAdding columns to penalty_charges table...")
    print("-" * 60)
    
    # Add applied_by_id
    success &= execute_sql(
        "ALTER TABLE penalty_charges ADD COLUMN applied_by_id INT NULL",
        "Add applied_by_id column"
    )
    
    # Add is_automatic
    success &= execute_sql(
        "ALTER TABLE penalty_charges ADD COLUMN is_automatic TINYINT(1) NOT NULL DEFAULT 1",
        "Add is_automatic column"
    )
    
    # Add reason
    success &= execute_sql(
        "ALTER TABLE penalty_charges ADD COLUMN reason TEXT NULL",
        "Add reason column"
    )
    
    # Add foreign key
    print("\nAdding foreign key constraints...")
    print("-" * 60)
    
    execute_sql(
        "ALTER TABLE penalty_charges ADD CONSTRAINT penalty_charges_applied_by_id_fk FOREIGN KEY (applied_by_id) REFERENCES users(id) ON DELETE SET NULL",
        "Add applied_by foreign key"
    )
    
    print("\nMarking migrations as applied...")
    print("-" * 60)
    
    # Mark the penalty migration as applied
    try:
        with connection.cursor() as cursor:
            # Check if migration record exists
            cursor.execute(
                "SELECT COUNT(*) FROM django_migrations WHERE app = 'loans' AND name = '0016_add_penalty_mode'"
            )
            exists = cursor.fetchone()[0] > 0
            
            if not exists:
                cursor.execute(
                    "INSERT INTO django_migrations (app, name, applied) VALUES ('loans', '0016_add_penalty_mode', NOW())"
                )
                print("  ✓ Marked 0016_add_penalty_mode as applied")
            else:
                print("  ⊙ Migration 0016_add_penalty_mode already marked")
                
    except Exception as e:
        print(f"  ⚠ Could not mark migration: {e}")
        print("  This is OK - the columns are added")
    
    print("\n" + "=" * 60)
    if success:
        print("✓ Database columns added successfully!")
        print("✓ Your production site should now work!")
    else:
        print("⚠ Some operations had warnings, but may still work")
    print("=" * 60)

if __name__ == '__main__':
    main()