from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker, DeclarativeBase
from app.core.config import settings


class Base(DeclarativeBase):
    """Single base for ALL models."""
    pass


def get_engine():
    return create_engine(settings.database_url, pool_pre_ping=True, pool_size=10, max_overflow=20, pool_timeout=30)


engine = get_engine()
SessionLocal = sessionmaker(bind=engine)


def init_db() -> None:
    """Create all tables. Safe to call multiple times."""
    # Import all models to register with Base.metadata
    from app.models.catalog import UserAccount, Session, ProjectRegistry, ProjectMember  # noqa: F401
    from app.models.project import (  # noqa: F401
        Episode, EntityCanon, EntityAlias, RelationFact, RelationParticipant,
        SceneStill, EntityEpisodeLink, ImageAsset, ProjectSettings, WorldGuide,
        WebbookPackage, GenerationTrace, OperationLog, PipelineProgress,
        LLMCallLog, CharacterOutlook, ScenePlan,
    )
    from app.logging.models import ActivityLog  # noqa: F401
    Base.metadata.create_all(engine, checkfirst=True)

    # 스키마 마이그레이션 — 기존 테이블에 새 컬럼 추가 (없으면)
    _migrations = [
        "ALTER TABLE entity_canon ADD COLUMN IF NOT EXISTS t2i_prompt TEXT",
        "ALTER TABLE project_settings ADD COLUMN IF NOT EXISTS style_rules_json TEXT",
        "ALTER TABLE project_settings ADD COLUMN IF NOT EXISTS world_summary TEXT",
        "ALTER TABLE scene_still ADD COLUMN IF NOT EXISTS dependent_scene_id TEXT",
        "ALTER TABLE image_asset ADD COLUMN IF NOT EXISTS theme_label TEXT",
        # Indexes for common query patterns
        "CREATE INDEX IF NOT EXISTS idx_scene_still_project_episode ON scene_still (project_id, episode_id)",
        "CREATE INDEX IF NOT EXISTS idx_image_asset_entity_type ON image_asset (entity_id, asset_type)",
        "CREATE INDEX IF NOT EXISTS idx_image_asset_still ON image_asset (still_id)",
        "CREATE INDEX IF NOT EXISTS idx_image_asset_project_episode ON image_asset (project_id, episode_id, asset_type)",
        "CREATE INDEX IF NOT EXISTS idx_entity_episode_link_canon ON entity_episode_link (canon_id, episode_id)",
        "CREATE INDEX IF NOT EXISTS idx_pipeline_progress_project ON pipeline_progress (project_id, episode_id, operation)",
        "CREATE INDEX IF NOT EXISTS idx_llm_call_log_project ON llm_call_log (project_id, created_at)",
        "CREATE INDEX IF NOT EXISTS idx_activity_log_project ON activity_log (project_id, created_at)",
        "CREATE INDEX IF NOT EXISTS idx_character_outlook_pair ON character_outlook (character_id, outlook_id)",
        "CREATE INDEX IF NOT EXISTS idx_image_asset_variant ON image_asset (still_id, asset_type, variant_type)",
        "ALTER TABLE scene_still ADD COLUMN IF NOT EXISTS scene_type TEXT DEFAULT 'normal'",
        "ALTER TABLE world_guide ADD COLUMN IF NOT EXISTS source_hash TEXT",
    ]
    with engine.connect() as conn:
        for sql in _migrations:
            try:
                conn.execute(text(sql))
                conn.commit()
            except Exception:
                conn.rollback()