from pathlib import Path
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker, declarative_base

# Keep data inside the backend app root so cPanel/Passenger (which may restrict the app to its folder) can read/write it.
DATA_DIR = Path(__file__).resolve().parent / "data"
DATA_DIR.mkdir(parents=True, exist_ok=True)

DB_PATH = DATA_DIR / "app.db"
DATABASE_URL = f"sqlite:///{DB_PATH.as_posix()}"

engine = create_engine(
    DATABASE_URL,
    connect_args={"check_same_thread": False},
)

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

def init_db():
    # Fix corrupted platform_posts table (exists but has no columns) before create_all
    _fix_platform_posts_if_empty()
    Base.metadata.create_all(bind=engine)
    # Run migrations for existing databases
    _migrate_toxicity_override()


def _fix_platform_posts_if_empty():
    """If platform_posts exists but has no columns (corrupt/old schema), drop it so create_all can recreate it."""
    with engine.connect() as conn:
        r = conn.execute(text("SELECT name FROM sqlite_master WHERE type='table' AND name='platform_posts'"))
        if r.fetchone() is None:
            return
        r = conn.execute(text("PRAGMA table_info(platform_posts)"))
        cols = r.fetchall()
        if cols:
            return
        conn.execute(text("DROP TABLE platform_posts"))
        conn.commit()

def _migrate_toxicity_override():
    """Add toxicity_manual_override column if it doesn't exist"""
    db = SessionLocal()
    try:
        # Check if table exists
        result = db.execute(text("SELECT name FROM sqlite_master WHERE type='table' AND name='comment_analyses'"))
        if result.fetchone() is None:
            # Table doesn't exist yet, create_all will handle it
            db.close()
            return
        
        # Check if column exists
        result = db.execute(text("PRAGMA table_info(comment_analyses)"))
        columns = [row[1] for row in result.fetchall()]
        
        if "toxicity_manual_override" not in columns:
            db.execute(text("""
                ALTER TABLE comment_analyses 
                ADD COLUMN toxicity_manual_override BOOLEAN
            """))
            db.commit()
    except Exception as e:
        db.rollback()
        # Don't fail startup if migration fails - log and continue
        print(f"Warning: Migration failed: {e}")
    finally:
        db.close()