"""short_id 발급 — 엔티티 타입별 접두어 + 프로젝트 내 순번.

C01=character, L01=location, P01=prop, O01=outlook.
복합: C01O02 = 인물 C01이 아웃룩 O02 착용.
"""

from typing import Any, Dict, List, Optional

from sqlalchemy import text
from sqlalchemy.orm import Session

_PREFIX_MAP = {
    "character": "C",
    "location": "L",
    "prop": "P",
    "outlook": "O",
}


def generate_short_id(db: Session, project_id: str, entity_type: str) -> str:
    """프로젝트 내 해당 타입의 다음 short_id 발급."""
    prefix = _PREFIX_MAP.get(entity_type)
    if not prefix:
        raise ValueError(f"Unknown entity_type for short_id: {entity_type}")

    row = db.execute(text(
        "SELECT MAX(CAST(SUBSTRING(short_id FROM :start) AS INTEGER)) "
        "FROM entity_canon WHERE project_id = :pid AND short_id LIKE :pattern"
    ), {"pid": project_id, "pattern": f"{prefix}%", "start": len(prefix) + 1}).fetchone()

    num = (row[0] or 0) + 1 if row else 1
    return f"{prefix}{num:02d}"


def build_short_id_map(db: Session, project_id: str, episode_id: str = None) -> Dict[str, str]:
    """DB에서 {short_id: uuid} 매핑 조회."""
    if episode_id:
        query = (
            "SELECT ec.id, ec.short_id FROM entity_canon ec "
            "JOIN entity_episode_link eel ON ec.id = eel.canon_id "
            "WHERE ec.project_id = :pid AND eel.episode_id = :eid AND ec.short_id IS NOT NULL"
        )
        rows = db.execute(text(query), {"pid": project_id, "eid": episode_id}).fetchall()
    else:
        rows = db.execute(text(
            "SELECT id, short_id FROM entity_canon WHERE project_id = :pid AND short_id IS NOT NULL"
        ), {"pid": project_id}).fetchall()
    return {r[1]: r[0] for r in rows}


def build_short_id_info(db: Session, project_id: str, episode_id: str) -> Dict[str, Dict[str, Any]]:
    """DB에서 {short_id: {uuid, name, type, description}} 매핑 조회."""
    rows = db.execute(text(
        "SELECT ec.id, ec.short_id, ec.name, ec.entity_type, ec.description "
        "FROM entity_canon ec "
        "JOIN entity_episode_link eel ON ec.id = eel.canon_id "
        "WHERE ec.project_id = :pid AND eel.episode_id = :eid AND ec.short_id IS NOT NULL"
    ), {"pid": project_id, "eid": episode_id}).fetchall()
    return {
        r[1]: {"uuid": r[0], "name": r[2], "type": r[3], "description": (r[4] or "")[:80]}
        for r in rows
    }


def build_uuid_to_short(db: Session, project_id: str) -> Dict[str, str]:
    """DB에서 {uuid: short_id} 역매핑."""
    rows = db.execute(text(
        "SELECT id, short_id FROM entity_canon WHERE project_id = :pid AND short_id IS NOT NULL"
    ), {"pid": project_id}).fetchall()
    return {r[0]: r[1] for r in rows}