#!/usr/bin/env python
"""
Script to create the missing receipts table in the database
"""
import os
import sys
import django

# Setup Django
sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings')
django.setup()

from django.db import connection

def create_receipts_table():
    """Create the receipts table if it doesn't exist"""
    
    sql = """
    CREATE TABLE IF NOT EXISTS `receipts` (
      `id` char(32) NOT NULL,
      `repayment_id` char(32) NOT NULL,
      `loan_id` char(32) NOT NULL,
      `borrower_id` int(11) NOT NULL,
      `receipt_number` varchar(20) NOT NULL,
      `amount_paid` decimal(12,2) NOT NULL,
      `payment_method` varchar(20) NOT NULL,
      `payment_date` datetime(6) NOT NULL,
      `previous_balance` decimal(12,2) NOT NULL,
      `new_balance` decimal(12,2) NOT NULL,
      `pdf_file` varchar(100) DEFAULT NULL,
      `created_at` datetime(6) NOT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `receipt_number` (`receipt_number`),
      UNIQUE KEY `repayment_id` (`repayment_id`),
      KEY `receipts_loan_id_idx` (`loan_id`),
      KEY `receipts_borrower_id_idx` (`borrower_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    """
    
    print("Creating receipts table...")
    
    try:
        with connection.cursor() as cursor:
            cursor.execute(sql)
            print("✓ Receipts table created successfully!")
            
            # Check if table exists
            cursor.execute("SHOW TABLES LIKE 'receipts'")
            result = cursor.fetchone()
            if result:
                print("✓ Verified: receipts table exists")
                
                # Show table structure
                cursor.execute("DESCRIBE receipts")
                columns = cursor.fetchall()
                print("\nTable structure:")
                for col in columns:
                    print(f"  - {col[0]}: {col[1]}")
            else:
                print("✗ Error: receipts table was not created")
                
    except Exception as e:
        print(f"✗ Error creating receipts table: {e}")
        return False
    
    return True

if __name__ == '__main__':
    print("=" * 60)
    print("Creating Missing Receipts Table")
    print("=" * 60)
    
    success = create_receipts_table()
    
    if success:
        print("\n" + "=" * 60)
        print("SUCCESS: Receipts table created!")
        print("=" * 60)
        print("\nYou can now process loan repayments.")
    else:
        print("\n" + "=" * 60)
        print("FAILED: Could not create receipts table")
        print("=" * 60)
        print("\nPlease check the error messages above.")