#!/usr/bin/env python3
"""
Script to fix database lock issues by updating SQLite configuration.
This script sets proper SQLite pragmas to handle concurrent access better.
"""

import sqlite3
import sys
import os
from pathlib import Path

def find_database_file():
    """Find the database file in common locations"""
    possible_paths = [
        'test.db',
        'server_api/test.db',
        '../test.db',
        'api.timetabling.mutabletech.co.ke/test.db',
        '/home/mutablet/api.timetabling.mutabletech.co.ke/test.db'
    ]
    
    for path in possible_paths:
        if os.path.exists(path):
            return path
    
    return None

def optimize_database_for_concurrent_access(db_path):
    """Apply SQLite optimizations for better concurrent access"""
    print(f"🔧 Optimizing database for concurrent access: {db_path}")
    
    try:
        conn = sqlite3.connect(db_path, timeout=30.0)  # 30 second timeout
        cursor = conn.cursor()
        
        # Set SQLite pragmas for better concurrent access
        pragmas = [
            "PRAGMA journal_mode=WAL",  # Write-Ahead Logging for better concurrency
            "PRAGMA synchronous=NORMAL",  # Balance between safety and speed
            "PRAGMA cache_size=10000",  # Increase cache size
            "PRAGMA temp_store=MEMORY",  # Store temp tables in memory
            "PRAGMA mmap_size=268435456",  # 256MB memory-mapped I/O
            "PRAGMA busy_timeout=30000",  # 30 second busy timeout
            "PRAGMA wal_autocheckpoint=1000",  # Checkpoint WAL every 1000 pages
        ]
        
        for pragma in pragmas:
            try:
                cursor.execute(pragma)
                print(f"✅ Applied: {pragma}")
            except Exception as e:
                print(f"⚠️  Warning applying {pragma}: {e}")
        
        # Check current settings
        print("\n📊 Current database settings:")
        settings_to_check = [
            ("journal_mode", "PRAGMA journal_mode"),
            ("synchronous", "PRAGMA synchronous"),
            ("cache_size", "PRAGMA cache_size"),
            ("busy_timeout", "PRAGMA busy_timeout"),
            ("wal_autocheckpoint", "PRAGMA wal_autocheckpoint"),
        ]
        
        for setting_name, pragma in settings_to_check:
            try:
                cursor.execute(pragma)
                value = cursor.fetchone()[0]
                print(f"  - {setting_name}: {value}")
            except Exception as e:
                print(f"  - {setting_name}: Error reading - {e}")
        
        conn.commit()
        conn.close()
        
        print("✅ Database optimization completed successfully!")
        return True
        
    except Exception as e:
        print(f"❌ Error optimizing database: {e}")
        return False

def main():
    """Main function"""
    print("🚀 Database Lock Fix Script")
    print("=" * 40)
    
    # Find database file
    db_path = find_database_file()
    if not db_path:
        print("❌ Database file not found!")
        print("Please ensure the database file exists in one of these locations:")
        print("- test.db (current directory)")
        print("- server_api/test.db")
        print("- ../test.db")
        print("- api.timetabling.mutabletech.co.ke/test.db")
        print("- /home/mutablet/api.timetabling.mutabletech.co.ke/test.db")
        sys.exit(1)
    
    print(f"📁 Found database at: {db_path}")
    
    # Optimize database
    success = optimize_database_for_concurrent_access(db_path)
    
    if success:
        print("\n🎉 Database optimization completed!")
        print("The following optimizations were applied:")
        print("  - WAL (Write-Ahead Logging) mode for better concurrency")
        print("  - Increased cache size for better performance")
        print("  - Memory-mapped I/O for faster access")
        print("  - 30-second busy timeout to handle locks gracefully")
        print("  - Optimized checkpoint settings")
        print("\n✅ You can now restart your application.")
        print("The database lock errors should be resolved.")
    else:
        print("\n💥 Database optimization failed!")
        print("Please check the error messages above and try again.")
        sys.exit(1)

if __name__ == "__main__":
    main()