from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, DeclarativeBase

from backend.core.config import settings


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

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


class Base(DeclarativeBase):
    pass


def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


def upgrade_shop_tables(eng):
    """Add new columns to existing shop tables (SQLite-safe: try/except per column)."""
    from sqlalchemy import text
    new_account_cols = [
        ("avg_engagement_rate", "FLOAT DEFAULT 0.0"),
        ("avg_hook_strength", "FLOAT DEFAULT 0.0"),
        ("avg_shareability", "FLOAT DEFAULT 0.0"),
        ("total_shares", "INTEGER DEFAULT 0"),
        ("posts_per_week", "FLOAT DEFAULT 0.0"),
        ("best_posting_days", "JSON"),
        ("best_posting_hours", "JSON"),
        ("pct_short_videos", "FLOAT DEFAULT 0.0"),
        ("pct_medium_videos", "FLOAT DEFAULT 0.0"),
        ("pct_long_videos", "FLOAT DEFAULT 0.0"),
        ("avg_duration", "FLOAT DEFAULT 0.0"),
        ("commerce_video_count", "INTEGER DEFAULT 0"),
        ("commerce_ratio", "FLOAT DEFAULT 0.0"),
        ("commission_eligible_count", "INTEGER DEFAULT 0"),
        ("top_hashtags", "JSON"),
        ("avg_hashtags_per_post", "FLOAT DEFAULT 0.0"),
        ("full_videos", "JSON"),
        ("revenue_per_video", "FLOAT DEFAULT 0.0"),
        ("follower_to_sale_ratio", "FLOAT DEFAULT 0.0"),
        ("ai_report", "JSON"),
        ("ai_report_generated_at", "DATETIME"),
        ("insights_cache", "JSON"),
        ("insights_generated_at", "DATETIME"),
        ("video_ideas_cache", "JSON"),
        ("video_ideas_generated_at", "DATETIME"),
        ("product_video_map", "JSON"),
    ]
    new_snapshot_cols = [
        ("avg_engagement_rate", "FLOAT DEFAULT 0.0"),
        ("commerce_video_count", "INTEGER DEFAULT 0"),
        ("total_shares", "INTEGER DEFAULT 0"),
        ("avg_hook_strength", "FLOAT DEFAULT 0.0"),
        ("avg_likes", "FLOAT DEFAULT 0.0"),
        ("avg_shares", "FLOAT DEFAULT 0.0"),
        ("shop_followers", "INTEGER DEFAULT 0"),
        ("avg_product_price", "FLOAT DEFAULT 0.0"),
        ("posts_per_week", "FLOAT DEFAULT 0.0"),
    ]
    with eng.connect() as conn:
        for col, dtype in new_account_cols:
            try:
                conn.execute(text(f"ALTER TABLE tiktok_shop_accounts ADD COLUMN {col} {dtype}"))
                conn.commit()
            except Exception:
                conn.rollback()
        for col, dtype in new_snapshot_cols:
            try:
                conn.execute(text(f"ALTER TABLE tiktok_shop_account_snapshots ADD COLUMN {col} {dtype}"))
                conn.commit()
            except Exception:
                conn.rollback()


def init_db():
    from backend.models import video, creator, trend, collection, idea, product, product_snapshot, settings, competitor, saved_video, echotik_cache, script, academy, youtube_upload, tiktok_shop_account  # noqa: F401
    from backend.services import api_usage_tracker  # noqa: F401
    Base.metadata.create_all(bind=engine)
    upgrade_shop_tables(engine)
