"""
Dashboard Migration

dashboard 테이블 초기화 및 기존 데이터 마이그레이션을 담당합니다.
서버 기동 시 create_db_tables() 이후 호출됩니다.
"""

import logging

from sqlalchemy import func, select, text
from sqlalchemy.dialects.mysql import insert

from app.dashboard.models import Dashboard
from app.database.session import AsyncSessionLocal, engine
from app.social.models import SocialUpload
from app.user.models import SocialAccount

logger = logging.getLogger(__name__)


async def _dashboard_table_exists() -> bool:
    """dashboard 테이블 존재 여부 확인"""
    async with engine.connect() as conn:
        result = await conn.execute(
            text(
                "SELECT COUNT(*) FROM information_schema.tables "
                "WHERE table_schema = DATABASE() AND table_name = 'dashboard'"
            )
        )
        return result.scalar() > 0


async def _dashboard_is_empty() -> bool:
    """dashboard 테이블 데이터 존재 여부 확인"""
    async with AsyncSessionLocal() as session:
        result = await session.execute(
            select(func.count()).select_from(Dashboard)
        )
        return result.scalar() == 0


async def _migrate_existing_data() -> None:
    """
    SocialUpload(status=completed) → Dashboard 마이그레이션.
    INSERT IGNORE로 중복 안전하게 삽입.
    """
    async with AsyncSessionLocal() as session:
        result = await session.execute(
            select(
                SocialUpload.user_uuid,
                SocialUpload.platform,
                SocialUpload.platform_video_id,
                SocialUpload.platform_url,
                SocialUpload.title,
                SocialUpload.uploaded_at,
                SocialAccount.platform_user_id,
            )
            .join(SocialAccount, SocialUpload.social_account_id == SocialAccount.id)
            .where(
                SocialUpload.status == "completed",
                SocialUpload.platform_video_id.isnot(None),
                SocialUpload.uploaded_at.isnot(None),
                SocialAccount.platform_user_id.isnot(None),
            )
        )
        rows = result.all()

    if not rows:
        logger.info("[DASHBOARD_MIGRATE] 마이그레이션 대상 없음")
        return

    async with AsyncSessionLocal() as session:
        for row in rows:
            stmt = (
                insert(Dashboard)
                .values(
                    user_uuid=row.user_uuid,
                    platform=row.platform,
                    platform_user_id=row.platform_user_id,
                    platform_video_id=row.platform_video_id,
                    platform_url=row.platform_url,
                    title=row.title,
                    uploaded_at=row.uploaded_at,
                )
                .prefix_with("IGNORE")
            )
            await session.execute(stmt)
        await session.commit()

    logger.info(f"[DASHBOARD_MIGRATE] 마이그레이션 완료 - {len(rows)}건 삽입")


async def init_dashboard_table() -> None:
    """
    dashboard 테이블 초기화 진입점.

    - 테이블이 없으면 생성 후 마이그레이션
    - 테이블이 있지만 비어있으면 마이그레이션 (DEBUG 모드에서 create_db_tables()가 빈 테이블 생성한 경우)
    - 테이블이 있고 데이터도 있으면 스킵
    """
    if not await _dashboard_table_exists():
        logger.info("[DASHBOARD_MIGRATE] dashboard 테이블 없음 - 생성 및 마이그레이션 시작")
        async with engine.begin() as conn:
            await conn.run_sync(
                lambda c: Dashboard.__table__.create(c, checkfirst=True)
            )
        await _migrate_existing_data()
    elif await _dashboard_is_empty():
        logger.info("[DASHBOARD_MIGRATE] dashboard 테이블 비어있음 - 마이그레이션 시작")
        await _migrate_existing_data()
    else:
        logger.info("[DASHBOARD_MIGRATE] dashboard 테이블 이미 존재 - 스킵")