#!/usr/bin/env python3
"""
Test script to validate database structure before applying production fixes
This script checks what needs to be fixed without making any changes
"""

import os
import sys
import django
import logging

# Setup Django
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings')
django.setup()

from django.db import connection

# Setup logging
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger(__name__)

def check_table_exists(table_name):
    """Check if a table exists in the database"""
    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 check_column_exists(table_name, column_name):
    """Check if a column exists in a table"""
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT COUNT(*) 
            FROM information_schema.columns 
            WHERE table_schema = DATABASE() 
            AND table_name = %s 
            AND column_name = %s
        """, [table_name, column_name])
        return cursor.fetchone()[0] > 0

def get_table_info(table_name):
    """Get detailed information about a table"""
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT column_name, data_type, is_nullable, column_default, extra
            FROM information_schema.columns 
            WHERE table_schema = DATABASE() 
            AND table_name = %s
            ORDER BY ordinal_position
        """, [table_name])
        return cursor.fetchall()

def main():
    """Main test function"""
    logger.info("Testing database structure...")
    
    issues_found = []
    
    # Check penalty_charges table
    logger.info("\n=== Checking penalty_charges table ===")
    if check_table_exists('penalty_charges'):
        logger.info("✓ penalty_charges table exists")
        columns = get_table_info('penalty_charges')
        logger.info("Columns:")
        for col in columns:
            logger.info(f"  - {col[0]} ({col[1]}) {'NULL' if col[2] == 'YES' else 'NOT NULL'}")
    else:
        logger.error("✗ penalty_charges table is MISSING")
        issues_found.append("Missing penalty_charges table")
    
    # Check utils_notification table and loan_app_id column
    logger.info("\n=== Checking utils_notification table ===")
    if check_table_exists('utils_notification'):
        logger.info("✓ utils_notification table exists")
        
        if check_column_exists('utils_notification', 'loan_app_id'):
            logger.info("✓ loan_app_id column exists")
        else:
            logger.error("✗ loan_app_id column is MISSING")
            issues_found.append("Missing loan_app_id column in utils_notification")
            
        # Show current columns
        columns = get_table_info('utils_notification')
        logger.info("Current columns:")
        for col in columns:
            logger.info(f"  - {col[0]} ({col[1]}) {'NULL' if col[2] == 'YES' else 'NOT NULL'}")
    else:
        logger.error("✗ utils_notification table is MISSING")
        issues_found.append("Missing utils_notification table")
    
    # Check loan_applications table
    logger.info("\n=== Checking loan_applications table ===")
    if check_table_exists('loan_applications'):
        logger.info("✓ loan_applications table exists")
        
        if check_column_exists('loan_applications', 'registration_fee_amount'):
            logger.info("✓ registration_fee_amount column exists")
        else:
            logger.error("✗ registration_fee_amount column is MISSING")
            issues_found.append("Missing registration_fee_amount column in loan_applications")
    else:
        logger.error("✗ loan_applications table is MISSING")
        issues_found.append("Missing loan_applications table")
    
    # Check loans table
    logger.info("\n=== Checking loans table ===")
    if check_table_exists('loans'):
        logger.info("✓ loans table exists")
        
        if check_column_exists('loans', 'registration_fee'):
            logger.info("✓ registration_fee column exists")
        else:
            logger.error("✗ registration_fee column is MISSING")
            issues_found.append("Missing registration_fee column in loans")
    else:
        logger.error("✗ loans table is MISSING")
        issues_found.append("Missing loans table")
    
    # Summary
    logger.info("\n=== SUMMARY ===")
    if issues_found:
        logger.error(f"Found {len(issues_found)} issues that need to be fixed:")
        for i, issue in enumerate(issues_found, 1):
            logger.error(f"{i}. {issue}")
        logger.info("\nRun fix_production_database_complete.py to fix these issues.")
        return False
    else:
        logger.info("✓ All database structures are correct!")
        return True

if __name__ == "__main__":
    try:
        success = main()
        sys.exit(0 if success else 1)
    except Exception as e:
        logger.error(f"Error during test: {str(e)}")
        sys.exit(1)