#!/usr/bin/env python3
"""
Script to set up local MySQL database with sample data.
Run this script to create tables and populate with test data.
"""

import pymysql
import os
from dotenv import load_dotenv

# Load environment variables
load_dotenv()

def get_db_connection():
    """Get connection to local MySQL database."""
    return pymysql.connect(
        host=os.getenv('DB_HOST', 'localhost'),
        user=os.getenv('DB_USER', 'root'),
        password=os.getenv('DB_PASSWORD', ''),
        database=os.getenv('DB_NAME', 'BigPond'),
        port=int(os.getenv('DB_PORT', 3306)),
        autocommit=True
    )

def execute_sql_file(connection, filename):
    """Execute SQL commands from a file."""
    print(f"Executing {filename}...")
    
    with open(filename, 'r') as file:
        sql_content = file.read()
    
    # Split by semicolon and execute each statement
    statements = sql_content.split(';')
    
    with connection.cursor() as cursor:
        for statement in statements:
            statement = statement.strip()
            if statement:  # Skip empty statements
                try:
                    cursor.execute(statement)
                    print(f"✅ Executed: {statement[:50]}...")
                except Exception as e:
                    print(f"❌ Error executing statement: {e}")
                    print(f"   Statement: {statement[:100]}...")

def main():
    """Main function to set up the database."""
    try:
        print("🔗 Connecting to local MySQL database...")
        connection = get_db_connection()
        
        print("📋 Creating tables...")
        execute_sql_file(connection, 'create_tables.sql')
        
        print("📊 Inserting sample data...")
        execute_sql_file(connection, 'sample_data.sql')
        
        print("✅ Database setup complete!")
        print(f"📈 Database: {os.getenv('DB_NAME', 'BigPond')}")
        print(f"🏠 Host: {os.getenv('DB_HOST', 'localhost')}")
        
    except Exception as e:
        print(f"❌ Error setting up database: {e}")
        print("💡 Make sure your .env file has correct database credentials")
        
    finally:
        if 'connection' in locals():
            connection.close()

if __name__ == "__main__":
    main()