"""
Create missing tables using direct SQL based on existing similar tables
"""
import os
import sys

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings')
sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))

import django
django.setup()

from django.db import connection

print("="*60)
print("  CREATE MISSING TABLES VIA SQL")
print("="*60)

def table_exists(table_name):
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT COUNT(*)
            FROM information_schema.TABLES
            WHERE TABLE_SCHEMA = DATABASE()
            AND TABLE_NAME = %s
        """, [table_name])
        return cursor.fetchone()[0] > 0

def create_users_customuser():
    """Create users_customuser table"""
    if table_exists('users_customuser'):
        print("✅ users_customuser already exists")
        return True
    
    print("Creating users_customuser table...")
    
    sql = """
    CREATE TABLE `users_customuser` (
        `id` bigint NOT NULL AUTO_INCREMENT,
        `password` varchar(128) NOT NULL,
        `last_login` datetime(6) DEFAULT NULL,
        `is_superuser` tinyint(1) NOT NULL,
        `username` varchar(150) NOT NULL,
        `first_name` varchar(150) NOT NULL,
        `last_name` varchar(150) NOT NULL,
        `email` varchar(254) NOT NULL,
        `is_staff` tinyint(1) NOT NULL,
        `is_active` tinyint(1) NOT NULL,
        `date_joined` datetime(6) NOT NULL,
        `phone_number` varchar(20) DEFAULT NULL,
        `national_id` varchar(20) DEFAULT NULL,
        `role` varchar(20) NOT NULL,
        `monthly_income` decimal(10,2) DEFAULT NULL,
        `logbook` varchar(100) DEFAULT NULL,
        `signature` varchar(100) DEFAULT NULL,
        `id_front` varchar(100) DEFAULT NULL,
        `id_back` varchar(100) DEFAULT NULL,
        `capital_invested` decimal(15,2) DEFAULT NULL,
        `county` varchar(100) DEFAULT NULL,
        `sub_county` varchar(100) DEFAULT NULL,
        `ward` varchar(100) DEFAULT NULL,
        `branch_id` char(32) DEFAULT NULL,
        `portfolio_manager_id` bigint DEFAULT NULL,
        `profile_image` varchar(100) DEFAULT NULL,
        `is_email_verified` tinyint(1) NOT NULL DEFAULT '0',
        `is_phone_verified` tinyint(1) NOT NULL DEFAULT '0',
        `email_verification_token` varchar(100) DEFAULT NULL,
        `phone_verification_code` varchar(6) DEFAULT NULL,
        `registration_fee_amount` decimal(10,2) DEFAULT NULL,
        `registration_fee_paid` tinyint(1) NOT NULL DEFAULT '0',
        `registration_fee_paid_at` datetime(6) DEFAULT NULL,
        `unassigned_date` datetime(6) DEFAULT NULL,
        `approval_status` varchar(20) NOT NULL DEFAULT 'pending',
        `approved_by_id` bigint DEFAULT NULL,
        `approved_at` datetime(6) DEFAULT NULL,
        `approval_reason` longtext,
        PRIMARY KEY (`id`),
        UNIQUE KEY `username` (`username`),
        KEY `users_customuser_branch_id` (`branch_id`),
        KEY `users_customuser_portfolio_manager_id` (`portfolio_manager_id`),
        KEY `users_customuser_approved_by_id` (`approved_by_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    """
    
    try:
        with connection.cursor() as cursor:
            cursor.execute(sql)
        print("✅ users_customuser created")
        return True
    except Exception as e:
        print(f"❌ Error: {e}")
        return False

def create_loans_loan():
    """Create loans_loan table"""
    if table_exists('loans_loan'):
        print("✅ loans_loan already exists")
        return True
    
    print("Creating loans_loan table...")
    
    sql = """
    CREATE TABLE `loans_loan` (
        `id` bigint NOT NULL AUTO_INCREMENT,
        `loan_number` varchar(20) NOT NULL,
        `borrower_id` bigint NOT NULL,
        `loan_product_id` bigint NOT NULL,
        `amount` decimal(10,2) NOT NULL,
        `interest_rate` decimal(5,2) NOT NULL,
        `duration_days` int NOT NULL,
        `status` varchar(20) NOT NULL,
        `application_date` datetime(6) NOT NULL,
        `approval_date` datetime(6) DEFAULT NULL,
        `disbursement_date` datetime(6) DEFAULT NULL,
        `due_date` datetime(6) DEFAULT NULL,
        `total_amount` decimal(10,2) NOT NULL,
        `amount_paid` decimal(10,2) NOT NULL DEFAULT '0.00',
        `balance` decimal(10,2) NOT NULL,
        `approved_by_id` bigint DEFAULT NULL,
        `disbursed_by_id` bigint DEFAULT NULL,
        `notes` longtext,
        `created_at` datetime(6) NOT NULL,
        `updated_at` datetime(6) NOT NULL,
        `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
        `deleted_at` datetime(6) DEFAULT NULL,
        `deleted_by_id` bigint DEFAULT NULL,
        `registration_fee` decimal(10,2) DEFAULT NULL,
        `processing_fee` decimal(10,2) DEFAULT NULL,
        PRIMARY KEY (`id`),
        UNIQUE KEY `loan_number` (`loan_number`),
        KEY `loans_loan_borrower_id` (`borrower_id`),
        KEY `loans_loan_loan_product_id` (`loan_product_id`),
        KEY `loans_loan_approved_by_id` (`approved_by_id`),
        KEY `loans_loan_disbursed_by_id` (`disbursed_by_id`),
        KEY `loans_loan_deleted_by_id` (`deleted_by_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    """
    
    try:
        with connection.cursor() as cursor:
            cursor.execute(sql)
        print("✅ loans_loan created")
        return True
    except Exception as e:
        print(f"❌ Error: {e}")
        return False

def create_loans_repayment():
    """Create loans_repayment table"""
    if table_exists('loans_repayment'):
        print("✅ loans_repayment already exists")
        return True
    
    print("Creating loans_repayment table...")
    
    sql = """
    CREATE TABLE `loans_repayment` (
        `id` bigint NOT NULL AUTO_INCREMENT,
        `loan_id` bigint NOT NULL,
        `amount` decimal(10,2) NOT NULL,
        `payment_date` datetime(6) NOT NULL,
        `payment_method` varchar(50) NOT NULL,
        `transaction_reference` varchar(100) DEFAULT NULL,
        `notes` longtext,
        `recorded_by_id` bigint DEFAULT NULL,
        `created_at` datetime(6) NOT NULL,
        `mpesa_transaction_id` bigint DEFAULT NULL,
        `payment_source` varchar(20) NOT NULL DEFAULT 'manual',
        PRIMARY KEY (`id`),
        KEY `loans_repayment_loan_id` (`loan_id`),
        KEY `loans_repayment_recorded_by_id` (`recorded_by_id`),
        KEY `loans_repayment_mpesa_transaction_id` (`mpesa_transaction_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    """
    
    try:
        with connection.cursor() as cursor:
            cursor.execute(sql)
        print("✅ loans_repayment created")
        return True
    except Exception as e:
        print(f"❌ Error: {e}")
        return False

print("\nStep 1: Create missing tables...")
print("-"*60)

create_users_customuser()
create_loans_loan()
create_loans_repayment()

print("\n" + "="*60)
print("Step 2: Mark migrations as applied...")
print("="*60)

# Now fake the migrations since tables exist
from django.core.management import call_command

try:
    print("Faking users migrations...")
    call_command('migrate', 'users', '--fake')
    print("✅ users migrations faked")
except Exception as e:
    print(f"⚠️  Error: {e}")

try:
    print("Faking loans migrations...")
    call_command('migrate', 'loans', '--fake')
    print("✅ loans migrations faked")
except Exception as e:
    print(f"⚠️  Error: {e}")

print("\n" + "="*60)
print("Step 3: Verify tables...")
print("="*60)

critical_tables = [
    'users_customuser',
    'users_branch',
    'loans_loan',
    'loans_loanapplication',
    'loans_repayment',
]

all_exist = True
for table in critical_tables:
    if table_exists(table):
        print(f"✅ {table}")
    else:
        print(f"❌ {table} - MISSING")
        all_exist = False

if all_exist:
    print("\n" + "="*60)
    print("  ✅ SUCCESS! All tables exist")
    print("="*60)
    print("\nYour 500 error should be fixed!")
    print("\nFinal steps:")
    print("1. Restart your application")
    print("2. Test the site")
else:
    print("\n⚠️  Some tables still missing")