# app/db/repositories/menu_slots.py
from typing import List, Optional, Sequence

from asyncpg import Connection, Record

from app.db.repositories.base import BaseRepository

DEFAULT_MENU_SLOTS = [
    {"slot_key": "news", "label": "资讯广场"},
    {"slot_key": "tutorial", "label": "使用教程"},
    {"slot_key": "community", "label": "社区"},
]


class MenuSlotsRepository(BaseRepository):
    def __init__(self, conn: Connection) -> None:
        super().__init__(conn)

    async def _ensure_table(self) -> None:
        await self.connection.execute(
            """
            CREATE TABLE IF NOT EXISTS menu_slots (
              slot_key TEXT PRIMARY KEY,
              label TEXT NOT NULL,
              tags TEXT[] NOT NULL DEFAULT '{}'::text[],
              created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
              updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
            );
            """,
        )

    async def _ensure_default_slots(self) -> None:
        await self._ensure_table()
        for slot in DEFAULT_MENU_SLOTS:
            await self.connection.execute(
                """
                INSERT INTO menu_slots (slot_key, label)
                VALUES ($1, $2)
                ON CONFLICT (slot_key) DO NOTHING;
                """,
                slot["slot_key"],
                slot["label"],
            )

    async def list_slots(self) -> List[Record]:
        await self._ensure_default_slots()
        rows = await self.connection.fetch(
            """
            SELECT slot_key, label, tags, created_at, updated_at
            FROM menu_slots
            ORDER BY slot_key ASC;
            """,
        )
        return list(rows)

    async def get_slot(self, slot_key: str) -> Optional[Record]:
        await self._ensure_default_slots()
        return await self.connection.fetchrow(
            """
            SELECT slot_key, label, tags, created_at, updated_at
            FROM menu_slots
            WHERE slot_key = $1
            LIMIT 1;
            """,
            slot_key,
        )

    async def upsert_slot_tags(
        self,
        *,
        slot_key: str,
        tags: Sequence[str],
        label: Optional[str] = None,
    ) -> Record:
        await self._ensure_default_slots()
        clean_tags = [t.strip() for t in tags if str(t).strip()]
        return await self.connection.fetchrow(
            """
            INSERT INTO menu_slots (slot_key, label, tags)
            VALUES ($1, COALESCE($2, $1), $3::text[])
            ON CONFLICT (slot_key) DO UPDATE
              SET tags = EXCLUDED.tags,
                  label = COALESCE(EXCLUDED.label, menu_slots.label),
                  updated_at = NOW()
            RETURNING slot_key, label, tags, created_at, updated_at;
            """,
            slot_key,
            label or slot_key,
            clean_tags,
        )