Tolan/backend/services/scenario_gift_service.py

"""
特殊情景对话（?scene=window|reading|eating）首句成功发送后，赠送对应商店物品到 user_assets。

商品以 item_code 为准（兼容不同库 shop_items.id 自增差异）。
参考：window→coffee_mug(常 id=11)、reading→books(常 id=8)、eating→snacks(常 id=24)
"""

import logging
from typing import Union

from sqlalchemy import select, text
from sqlalchemy.ext.asyncio import AsyncSession

from config import settings
from database import db_manager
from models_db import User
from scenario_config import SCENE_EATING, SCENE_READING, SCENE_WINDOW

logger = logging.getLogger(__name__)

SCENE_GIFT_ITEM_CODE_BY_SCENE: dict[str, str] = {
    SCENE_WINDOW: "coffee_mug",
    SCENE_READING: "books",
    SCENE_EATING: "snacks",
}


async def _mysql_table_exists(session: AsyncSession, table_name: str) -> bool:
    try:
        r = await session.execute(
            text(
                """
                SELECT COUNT(*) AS c
                FROM information_schema.tables
                WHERE table_schema = :db AND table_name = :table
                """
            ),
            {"db": settings.MYSQL_DATABASE, "table": table_name},
        )
        row = r.mappings().first()
        return bool(row and int(row.get("c") or 0) > 0)
    except Exception:
        return False


async def _mysql_column_data_type(
    session: AsyncSession, table_name: str, column_name: str
) -> str:
    try:
        r = await session.execute(
            text(
                """
                SELECT DATA_TYPE
                FROM information_schema.columns
                WHERE table_schema = :db
                  AND table_name = :table
                  AND column_name = :column
                """
            ),
            {"db": settings.MYSQL_DATABASE, "table": table_name, "column": column_name},
        )
        row = r.mappings().first()
        return str((row or {}).get("DATA_TYPE") or "")
    except Exception:
        return ""


async def _user_assets_uid_value(
    session: AsyncSession, user_id_uuid: str
) -> Union[str, int, None]:
    dt = await _mysql_column_data_type(session, "user_assets", "user_id")
    if not dt:
        return None
    if "bigint" in dt.lower():
        r = await session.execute(select(User.id).where(User.user_id == user_id_uuid))
        rid = r.scalar_one_or_none()
        return int(rid) if rid is not None else None
    return user_id_uuid


async def grant_scene_gift_if_needed(user_id_uuid: str, scene: str) -> None:
    """
    若本次为特殊情景且用户尚无该场景对应商品，则插入 user_assets 一行（owned=1, used=0）。
    失败仅打日志，不抛异常。
    """
    item_code = SCENE_GIFT_ITEM_CODE_BY_SCENE.get(scene)
    if not item_code or not user_id_uuid or user_id_uuid == "guest_user":
        return

    try:
        async with db_manager.get_session() as session:
            if not await _mysql_table_exists(session, "shop_items") or not await _mysql_table_exists(
                session, "user_assets"
            ):
                return

            ua_uid = await _user_assets_uid_value(session, user_id_uuid)
            if ua_uid is None:
                return

            item_res = await session.execute(
                text(
                    """
                    SELECT id FROM shop_items
                    WHERE item_code = :code AND is_active = 1
                    LIMIT 1
                    """
                ),
                {"code": item_code},
            )
            item_row = item_res.mappings().first()
            if not item_row or item_row.get("id") is None:
                logger.warning(
                    "⚠️ 情景赠送：shop_items 无商品 item_code=%s，跳过 scene=%s",
                    item_code,
                    scene,
                )
                return
            item_id = int(item_row["id"])

            exists = await session.execute(
                text(
                    """
                    SELECT id FROM user_assets
                    WHERE user_id = :uid AND item_id = :iid
                    LIMIT 1
                    """
                ),
                {"uid": ua_uid, "iid": item_id},
            )
            if exists.scalar_one_or_none() is not None:
                return

            await session.execute(
                text(
                    """
                    INSERT INTO user_assets
                      (user_id, item_id, owned_qty, used_qty, last_acquired_at, created_at, updated_at)
                    VALUES
                      (:uid, :iid, 1, 0, NOW(), NOW(), NOW())
                    """
                ),
                {"uid": ua_uid, "iid": item_id},
            )
            await session.commit()
            logger.info(
                "✅ 情景赠送商品：user_id=%s scene=%s item_code=%s item_id=%s",
                user_id_uuid,
                scene,
                item_code,
                item_id,
            )
    except Exception as exc:  # noqa: BLE001
        logger.warning(
            "⚠️ 情景赠送失败：user_id=%s scene=%s error=%s",
            user_id_uuid,
            scene,
            exc,
        )