import sys
import os

# Add the current directory to sys.path to allow importing 'app'
sys.path.append(os.getcwd())

from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool
from app.db.base import Base, settings
from app.api.seed import seed_database
from sqlalchemy.orm import sessionmaker
import json

def run():
    print(">>> Starting Product Import from extra_products_vortex.json...")
    
    # 1. Create a fresh engine with NullPool and use port 6543 (Transaction Mode)
    # to avoid "MaxClientsInSessionMode" errors on port 5432.
    db_url = settings.DATABASE_URL.replace(":5432/", ":6543/")
    temp_engine = create_engine(db_url, poolclass=NullPool)
    TempSessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=temp_engine)
    
    # 2. Ensure tables exist
    Base.metadata.create_all(bind=temp_engine)
    
    # 3. Setup DB Session
    db = TempSessionLocal()
    try:
        # Step 1: Initialize Categories and wipe data
        print(">>> Wiping data and creating categories...")
        result = seed_database(clear=True, db=db)
        if not result.isSuccess:
            print(f"FAILED: {result.error}")
            return

        print(f"SUCCESS: {result.value.get('message')}")
        print(f"Stats: Inserted {result.value.get('inserted_new')} products.")
        
    except Exception as e:
        print(f"CRITICAL ERROR: {str(e)}")
        db.rollback()
    finally:
        db.close()

if __name__ == "__main__":
    run()