import os
import db
import sys

# Ensure backend dir is in path
sys.path.append(os.path.dirname(os.path.abspath(__file__)))

MIGRATIONS_DIR = os.path.join(os.path.dirname(os.path.abspath(__file__)), "migrations")

def init_migrations_table():
    query = """
    CREATE TABLE IF NOT EXISTS `schema_migrations` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `migration_name` varchar(255) NOT NULL,
        `applied_at` timestamp DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `migration_name` (`migration_name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    """
    db.execute_commit(query)

def get_applied_migrations():
    rows = db.execute_query("SELECT migration_name FROM schema_migrations")
    return [r['migration_name'] for r in rows]

def run_migrations():
    print(f"Checking for migrations in {MIGRATIONS_DIR}...")
    init_migrations_table()
    applied = get_applied_migrations()
    
    # Get all .sql files from migrations dir
    files = [f for f in os.listdir(MIGRATIONS_DIR) if f.endswith(".sql")]
    files.sort() # Ensure they run in order (001, 002...)

    count = 0
    for filename in files:
        if filename not in applied:
            print(f"Applying migration: {filename}...")
            filepath = os.path.join(MIGRATIONS_DIR, filename)
            with open(filepath, "r", encoding="utf-8") as f:
                sql = f.read()
                
            # Execute the migration (handling potential multiple statements)
            # Simple splitter by semicolon (works for basic schemas)
            statements = [s.strip() for s in sql.split(";") if s.strip()]
            for stmt in statements:
                db.execute_commit(stmt)
            
            # Mark as applied
            db.execute_commit("INSERT INTO schema_migrations (migration_name) VALUES (%s)", (filename,))
            print(f"Successfully applied {filename}")
            count += 1
            
    if count == 0:
        print("No new migrations to apply.")
    else:
        print(f"Applied {count} migrations.")

if __name__ == "__main__":
    try:
        run_migrations()
    except Exception as e:
        print(f"MIGRATION ERROR: {e}")
        sys.exit(1)