from typing import Optional, Tuple
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession


async def pick_from_workbook(
    db: AsyncSession,
    workbook_id: int,
    pick: str = "random",   # random | level_asc
) -> Tuple[Optional[int], Optional[str], Optional[int], Optional[int], Optional[int]]:
    """
    workbook_id에서 아직 보내지 않은 문제 1개 선택 + workbook_sends 기록까지 원샷.
    pick:
      - random: 기존처럼 랜덤
      - level_asc: level 낮은 것부터(쉬운 것부터). level NULL은 맨 뒤.
                  같은 level이면 랜덤으로 섞어서 뽑음.
    """
    mode = (pick or "random").lower().strip()
    if mode not in ("random", "level_asc"):
        mode = "random"

    # 정렬 기준만 분기
    if mode == "level_asc":
        order_sql = "ORDER BY (wp.level IS NULL) ASC, wp.level ASC, random()"
    else:
        order_sql = "ORDER BY random()"

    sql = f"""
    WITH candidate AS (
      SELECT
        wp.problem_id,
        COALESCE(wp.title_ko, wp.title_en, '제목 없음') AS title,
        wp.level
      FROM workbook_problems wp
      LEFT JOIN workbook_sends ws
             ON ws.workbook_id = wp.workbook_id
            AND ws.problem_id = wp.problem_id
      WHERE wp.workbook_id = :wid
        AND ws.problem_id IS NULL
      {order_sql}
      LIMIT 1
    ),
    ins AS (
      INSERT INTO workbook_sends(workbook_id, problem_id)
      SELECT :wid, problem_id
      FROM candidate
      ON CONFLICT DO NOTHING
      RETURNING problem_id
    ),
    total AS (
      SELECT COUNT(*) AS cnt FROM workbook_problems WHERE workbook_id = :wid
    ),
    sent AS (
      SELECT COUNT(*) AS cnt FROM workbook_sends WHERE workbook_id = :wid
    )
    SELECT candidate.problem_id, candidate.title, candidate.level,
           (SELECT cnt FROM sent) + 1 AS current_idx,
           (SELECT cnt FROM total) AS total_cnt
    FROM candidate;
    """

    row = (await db.execute(text(sql), {"wid": workbook_id})).first()
    if not row:
        return None, None, None, None, None

    await db.commit()
    pid = int(row[0])
    title = str(row[1])
    level = int(row[2]) if row[2] is not None else None
    current_idx = int(row[3])
    total_cnt = int(row[4])
    return pid, title, level, current_idx, total_cnt