"""SQL-backed MarketService implementation.

Queries ``shared.models.market.*`` and ``shared.models.market_place.*`` —
only this module may read market-domain tables on behalf of other domains.
"""
from __future__ import annotations

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

from shared.models.market import Product
from shared.models.market_place import MarketPlace
from shared.browser.app.utils import utcnow
from shared.contracts.dtos import MarketPlaceDTO, ProductDTO
from shared.infrastructure.actions import call_action


def _mp_to_dto(mp: MarketPlace) -> MarketPlaceDTO:
    return MarketPlaceDTO(
        id=mp.id,
        container_type=mp.container_type,
        container_id=mp.container_id,
        name=mp.name,
        slug=mp.slug,
        description=mp.description,
    )


def _product_to_dto(p: Product) -> ProductDTO:
    return ProductDTO(
        id=p.id,
        slug=p.slug,
        title=p.title,
        image=p.image,
        description_short=p.description_short,
        rrp=p.rrp,
        regular_price=p.regular_price,
        special_price=p.special_price,
    )


class SqlMarketService:
    async def marketplaces_for_container(
        self, session: AsyncSession, container_type: str, container_id: int,
    ) -> list[MarketPlaceDTO]:
        result = await session.execute(
            select(MarketPlace).where(
                MarketPlace.container_type == container_type,
                MarketPlace.container_id == container_id,
                MarketPlace.deleted_at.is_(None),
            ).order_by(MarketPlace.name.asc())
        )
        return [_mp_to_dto(mp) for mp in result.scalars().all()]

    async def list_marketplaces(
        self, session: AsyncSession,
        container_type: str | None = None, container_id: int | None = None,
        *, page: int = 1, per_page: int = 20,
    ) -> tuple[list[MarketPlaceDTO], bool]:
        stmt = select(MarketPlace).where(MarketPlace.deleted_at.is_(None))
        if container_type is not None and container_id is not None:
            stmt = stmt.where(
                MarketPlace.container_type == container_type,
                MarketPlace.container_id == container_id,
            )
        stmt = stmt.order_by(MarketPlace.name.asc())
        stmt = stmt.offset((page - 1) * per_page).limit(per_page + 1)
        rows = (await session.execute(stmt)).scalars().all()
        has_more = len(rows) > per_page
        return [_mp_to_dto(mp) for mp in rows[:per_page]], has_more

    async def product_by_id(self, session: AsyncSession, product_id: int) -> ProductDTO | None:
        product = (
            await session.execute(select(Product).where(Product.id == product_id))
        ).scalar_one_or_none()
        return _product_to_dto(product) if product else None

    async def product_by_slug(self, session: AsyncSession, slug: str) -> ProductDTO | None:
        product = (
            await session.execute(select(Product).where(Product.slug == slug))
        ).scalar_one_or_none()
        return _product_to_dto(product) if product else None

    async def create_marketplace(
        self, session: AsyncSession, container_type: str, container_id: int,
        name: str, slug: str,
    ) -> MarketPlaceDTO:
        # Look for existing (including soft-deleted)
        existing = (await session.execute(
            select(MarketPlace).where(
                MarketPlace.container_type == container_type,
                MarketPlace.container_id == container_id,
                MarketPlace.slug == slug,
            )
        )).scalar_one_or_none()

        if existing:
            if existing.deleted_at is not None:
                existing.deleted_at = None  # revive
                existing.name = name
                await session.flush()
                await call_action("relations", "relate", payload={
                    "relation_type": f"{container_type}->market",
                    "from_id": container_id, "to_id": existing.id,
                    "label": existing.name,
                    "metadata": {"slug": existing.slug},
                })
                return _mp_to_dto(existing)
            raise ValueError(f'Market with slug "{slug}" already exists for this container.')

        market = MarketPlace(
            container_type=container_type, container_id=container_id,
            name=name, slug=slug,
        )
        session.add(market)
        await session.flush()
        await call_action("relations", "relate", payload={
            "relation_type": f"{container_type}->market",
            "from_id": container_id, "to_id": market.id,
            "label": market.name,
            "metadata": {"slug": market.slug},
        })
        return _mp_to_dto(market)

    async def soft_delete_marketplace(
        self, session: AsyncSession, container_type: str, container_id: int,
        slug: str,
    ) -> bool:
        market = (await session.execute(
            select(MarketPlace).where(
                MarketPlace.container_type == container_type,
                MarketPlace.container_id == container_id,
                MarketPlace.slug == slug,
                MarketPlace.deleted_at.is_(None),
            )
        )).scalar_one_or_none()

        if not market:
            return False

        market.deleted_at = utcnow()
        await session.flush()
        await call_action("relations", "unrelate", payload={
            "relation_type": f"{container_type}->market",
            "from_id": container_id, "to_id": market.id,
        })
        return True
