"""SQLAlchemy catalog lookups for the import mapper (dependency inversion)."""

from __future__ import annotations

from datetime import datetime, timezone

from sqlalchemy import or_, select
from sqlalchemy.orm import Session

from db.master_taxonomy_registry import aliases_from_json
from db.models import MasterBrand
from db.models_v2 import (
    V2Attribute,
    V2AttributeValue,
    V2Category,
    V2ImportMappingDictionary,
    V2Product,
)
from db.v2_import.matching import CatalogEntity


def _utcnow() -> datetime:
    return datetime.now(timezone.utc)


class SqlAlchemyCatalogLookup:
    """Read-only catalog port used by matching and search."""

    def __init__(self, session: Session) -> None:
        self.session = session

    def list_brands(self) -> list[CatalogEntity]:
        rows = self.session.execute(
            select(MasterBrand).where(MasterBrand.is_active.is_(True)).order_by(MasterBrand.name.asc())
        ).scalars().all()
        return [
            CatalogEntity(
                entity_type="brand",
                id=int(row.id),
                name=row.name,
                code=row.code,
                aliases=tuple(aliases_from_json(row.aliases)),
            )
            for row in rows
        ]

    def get_brand(self, entity_id: int) -> CatalogEntity | None:
        row = self.session.get(MasterBrand, int(entity_id))
        if row is None:
            return None
        return CatalogEntity(
            entity_type="brand",
            id=int(row.id),
            name=row.name,
            code=row.code,
            aliases=tuple(aliases_from_json(row.aliases)),
        )

    def list_nodes(self, node_type: str, *, parent_id: int | None = None) -> list[CatalogEntity]:
        stmt = (
            select(V2Category)
            .where(V2Category.merged_into_id.is_(None), V2Category.node_type == node_type)
            .order_by(V2Category.name.asc())
        )
        if parent_id is not None:
            stmt = stmt.where(V2Category.parent_id == parent_id)
        rows = self.session.execute(stmt).scalars().all()
        parents = self._parent_names({int(row.parent_id) for row in rows if row.parent_id is not None})
        return [
            CatalogEntity(
                entity_type=node_type,
                id=int(row.id),
                name=row.name,
                parent_id=int(row.parent_id) if row.parent_id is not None else None,
                parent_name=parents.get(int(row.parent_id)) if row.parent_id is not None else None,
            )
            for row in rows
        ]

    def get_node(self, entity_id: int, *, node_type: str | None = None) -> CatalogEntity | None:
        row = self.session.get(V2Category, int(entity_id))
        if row is None or row.merged_into_id is not None:
            return None
        if node_type and row.node_type != node_type:
            return None
        parent_name = None
        if row.parent_id is not None:
            parent = self.session.get(V2Category, int(row.parent_id))
            parent_name = parent.name if parent is not None else None
        return CatalogEntity(
            entity_type=row.node_type,
            id=int(row.id),
            name=row.name,
            parent_id=int(row.parent_id) if row.parent_id is not None else None,
            parent_name=parent_name,
        )

    def list_attributes(self) -> list[CatalogEntity]:
        rows = self.session.execute(select(V2Attribute).order_by(V2Attribute.label.asc())).scalars().all()
        return [
            CatalogEntity(
                entity_type="attribute",
                id=int(row.id),
                name=row.label,
                code=row.code,
                extra={"data_type": row.data_type},
            )
            for row in rows
        ]

    def get_attribute(self, entity_id: int) -> CatalogEntity | None:
        row = self.session.get(V2Attribute, int(entity_id))
        if row is None:
            return None
        return CatalogEntity(
            entity_type="attribute",
            id=int(row.id),
            name=row.label,
            code=row.code,
            extra={"data_type": row.data_type},
        )

    def list_attribute_options(self, attribute_id: int) -> list[CatalogEntity]:
        rows = self.session.execute(
            select(V2AttributeValue)
            .where(V2AttributeValue.attribute_id == int(attribute_id))
            .order_by(V2AttributeValue.display_label.asc())
        ).scalars().all()
        attribute = self.get_attribute(attribute_id)
        return [
            CatalogEntity(
                entity_type="attribute_option",
                id=int(row.id),
                name=row.display_label,
                code=row.value_code,
                parent_id=int(attribute_id),
                parent_name=attribute.name if attribute else None,
            )
            for row in rows
        ]

    def get_attribute_option(self, entity_id: int) -> CatalogEntity | None:
        row = self.session.get(V2AttributeValue, int(entity_id))
        if row is None:
            return None
        attribute = self.get_attribute(int(row.attribute_id))
        return CatalogEntity(
            entity_type="attribute_option",
            id=int(row.id),
            name=row.display_label,
            code=row.value_code,
            parent_id=int(row.attribute_id),
            parent_name=attribute.name if attribute else None,
        )

    def existing_skus(self, skus: list[str]) -> set[str]:
        if not skus:
            return set()
        rows = self.session.execute(select(V2Product.sku).where(V2Product.sku.in_(skus))).all()
        return {str(sku) for (sku,) in rows}

    def search(
        self,
        entity_type: str,
        query: str,
        *,
        hub_id: int | None = None,
        attribute_id: int | None = None,
        limit: int = 25,
    ) -> list[CatalogEntity]:
        term = (query or "").strip()
        if entity_type == "brand":
            stmt = select(MasterBrand).where(MasterBrand.is_active.is_(True))
            if term:
                like = f"%{term}%"
                stmt = stmt.where(or_(MasterBrand.name.ilike(like), MasterBrand.code.ilike(like)))
            rows = self.session.execute(stmt.order_by(MasterBrand.name.asc()).limit(limit)).scalars().all()
            return [
                CatalogEntity(entity_type="brand", id=int(row.id), name=row.name, code=row.code)
                for row in rows
            ]
        if entity_type in {"hub", "category", "collection"}:
            stmt = select(V2Category).where(
                V2Category.merged_into_id.is_(None),
                V2Category.node_type == entity_type,
            )
            if hub_id is not None and entity_type in {"category", "collection"}:
                stmt = stmt.where(V2Category.parent_id == int(hub_id))
            if term:
                stmt = stmt.where(V2Category.name.ilike(f"%{term}%"))
            rows = self.session.execute(stmt.order_by(V2Category.name.asc()).limit(limit)).scalars().all()
            parents = self._parent_names({int(row.parent_id) for row in rows if row.parent_id is not None})
            return [
                CatalogEntity(
                    entity_type=entity_type,
                    id=int(row.id),
                    name=row.name,
                    parent_id=int(row.parent_id) if row.parent_id is not None else None,
                    parent_name=parents.get(int(row.parent_id)) if row.parent_id is not None else None,
                )
                for row in rows
            ]
        if entity_type == "attribute":
            stmt = select(V2Attribute)
            if term:
                like = f"%{term}%"
                stmt = stmt.where(or_(V2Attribute.label.ilike(like), V2Attribute.code.ilike(like)))
            rows = self.session.execute(stmt.order_by(V2Attribute.label.asc()).limit(limit)).scalars().all()
            return [
                CatalogEntity(
                    entity_type="attribute",
                    id=int(row.id),
                    name=row.label,
                    code=row.code,
                    extra={"data_type": row.data_type},
                )
                for row in rows
            ]
        if entity_type == "attribute_option":
            if attribute_id is None:
                return []
            stmt = select(V2AttributeValue).where(V2AttributeValue.attribute_id == int(attribute_id))
            if term:
                like = f"%{term}%"
                stmt = stmt.where(
                    or_(V2AttributeValue.display_label.ilike(like), V2AttributeValue.value_code.ilike(like))
                )
            rows = self.session.execute(
                stmt.order_by(V2AttributeValue.display_label.asc()).limit(limit)
            ).scalars().all()
            attribute = self.get_attribute(int(attribute_id))
            return [
                CatalogEntity(
                    entity_type="attribute_option",
                    id=int(row.id),
                    name=row.display_label,
                    code=row.value_code,
                    parent_id=int(attribute_id),
                    parent_name=attribute.name if attribute else None,
                )
                for row in rows
            ]
        return []

    def find_dictionary(
        self,
        *,
        entity_type: str,
        normalized_value: str,
        source_profile: str,
        parent_key: str = "",
    ) -> V2ImportMappingDictionary | None:
        stmt = select(V2ImportMappingDictionary).where(
            V2ImportMappingDictionary.entity_type == entity_type,
            V2ImportMappingDictionary.normalized_value == normalized_value,
            V2ImportMappingDictionary.parent_key == (parent_key or ""),
        )
        if source_profile:
            scoped = self.session.execute(
                stmt.where(V2ImportMappingDictionary.source_profile == source_profile)
            ).scalar_one_or_none()
            if scoped is not None:
                return scoped
        return self.session.execute(
            stmt.where(V2ImportMappingDictionary.source_profile == "")
        ).scalar_one_or_none()

    def remember_mapping(
        self,
        *,
        entity_type: str,
        source_profile: str,
        source_value: str,
        normalized_value: str,
        parent_key: str,
        target_id: int,
        target_name: str,
        created_by: str | None,
    ) -> None:
        row = self.find_dictionary(
            entity_type=entity_type,
            normalized_value=normalized_value,
            source_profile=source_profile or "",
            parent_key=parent_key or "",
        )
        now = _utcnow()
        if row is None:
            row = V2ImportMappingDictionary(
                entity_type=entity_type,
                source_profile=source_profile or "",
                source_value=source_value,
                normalized_value=normalized_value,
                parent_key=parent_key or "",
                target_id=int(target_id),
                target_name=target_name,
                created_by=created_by,
                created_at=now,
                last_used_at=now,
            )
            self.session.add(row)
            return
        row.target_id = int(target_id)
        row.target_name = target_name
        row.source_value = source_value
        row.last_used_at = now

    def touch_dictionary(self, row: V2ImportMappingDictionary) -> None:
        row.last_used_at = _utcnow()

    def _parent_names(self, ids: set[int]) -> dict[int, str]:
        if not ids:
            return {}
        rows = self.session.execute(select(V2Category).where(V2Category.id.in_(ids))).scalars().all()
        return {int(row.id): row.name for row in rows}


def known_target_for(
    catalog: SqlAlchemyCatalogLookup,
    *,
    entity_type: str,
    source_value: str,
    source_profile: str,
    parent_key: str = "",
) -> CatalogEntity | None:
    from db.v2_import.matching import normalize_lookup_value as _norm

    row = catalog.find_dictionary(
        entity_type=entity_type,
        normalized_value=_norm(source_value),
        source_profile=source_profile,
        parent_key=parent_key,
    )
    if row is None:
        return None
    catalog.touch_dictionary(row)
    if entity_type == "brand":
        return catalog.get_brand(int(row.target_id))
    if entity_type in {"hub", "category", "collection"}:
        return catalog.get_node(int(row.target_id), node_type=entity_type)
    if entity_type == "attribute":
        return catalog.get_attribute(int(row.target_id))
    if entity_type == "attribute_option":
        return catalog.get_attribute_option(int(row.target_id))
    return None