from __future__ import annotations

import hashlib
import mimetypes
from dataclasses import dataclass, field
from typing import Dict, Iterable, List, Optional, Sequence, Set, Tuple
from urllib.parse import unquote, urlparse

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

from db.models import (
    ManualTaxonomyAssignment,
    ManualTaxonomyAssignmentCollection,
    MasterCollectionRegistry,
    MasterProduct,
    MasterProductAttributeValue,
    MasterProductCollectionMembership,
    MasterProductImage,
    MasterProductRelation,
    MasterTaxonomyNode,
)
from db.models_v2 import (
    V2Category,
    V2ConfigurableLink,
    V2MediaAsset,
    V2Product,
    V2ProductCategory,
    V2ProductCollection,
)


@dataclass
class V2BackfillStats:
    categories_created: int = 0
    categories_updated: int = 0
    products_created: int = 0
    products_updated: int = 0
    product_categories_rebuilt: int = 0
    product_collections_rebuilt: int = 0
    media_assets_rebuilt: int = 0
    configurable_links_rebuilt: int = 0
    skipped_category_links: int = 0
    skipped_collection_links: int = 0
    skipped_media_assets: int = 0
    source_counts: Dict[str, int] = field(default_factory=dict)

    def as_dict(self) -> Dict[str, object]:
        return {
            "categories_created": self.categories_created,
            "categories_updated": self.categories_updated,
            "products_created": self.products_created,
            "products_updated": self.products_updated,
            "product_categories_rebuilt": self.product_categories_rebuilt,
            "product_collections_rebuilt": self.product_collections_rebuilt,
            "media_assets_rebuilt": self.media_assets_rebuilt,
            "configurable_links_rebuilt": self.configurable_links_rebuilt,
            "skipped_category_links": self.skipped_category_links,
            "skipped_collection_links": self.skipped_collection_links,
            "skipped_media_assets": self.skipped_media_assets,
            "source_counts": dict(self.source_counts),
        }


def run_v2_foundation_backfill(
    session: Session,
    *,
    skus: Optional[Sequence[str]] = None,
    sku_limit: Optional[int] = None,
    sku_offset: int = 0,
    reset: bool = False,
) -> Dict[str, object]:
    stats = V2BackfillStats()
    requested_skus = [str(item or "").strip() for item in (skus or []) if str(item or "").strip()]

    product_stmt = select(MasterProduct).order_by(MasterProduct.id)
    if requested_skus:
        product_stmt = product_stmt.where(MasterProduct.sku.in_(requested_skus))
    else:
        if sku_offset and sku_offset > 0:
            product_stmt = product_stmt.offset(sku_offset)
        if sku_limit and sku_limit > 0:
            product_stmt = product_stmt.limit(sku_limit)
    source_products = list(session.scalars(product_stmt))
    selected_skus = {str(row.sku or "").strip() for row in source_products if str(row.sku or "").strip()}

    if not selected_skus:
        source_attrs: List[Tuple[str, str, Optional[str]]] = []
        source_manual: List[ManualTaxonomyAssignment] = []
        source_manual_collections: List[ManualTaxonomyAssignmentCollection] = []
        source_memberships: List[MasterProductCollectionMembership] = []
        source_images: List[MasterProductImage] = []
        source_relations: List[MasterProductRelation] = []
    else:
        source_attrs = list(
            session.execute(
                select(
                    MasterProductAttributeValue.sku,
                    MasterProductAttributeValue.attribute_code,
                    MasterProductAttributeValue.value,
                )
                .where(MasterProductAttributeValue.sku.in_(selected_skus))
                .order_by(MasterProductAttributeValue.id)
            )
        )
        source_manual = list(
            session.scalars(
                select(ManualTaxonomyAssignment)
                .where(ManualTaxonomyAssignment.source_sku.in_(selected_skus))
                .order_by(ManualTaxonomyAssignment.id)
            )
        )
        source_manual_collections = list(
            session.scalars(
                select(ManualTaxonomyAssignmentCollection)
                .where(
                    ManualTaxonomyAssignmentCollection.is_active.is_(True),
                    ManualTaxonomyAssignmentCollection.master_sku.in_(selected_skus),
                )
            )
        )
        source_memberships = list(
            session.scalars(
                select(MasterProductCollectionMembership).where(
                    MasterProductCollectionMembership.is_active.is_(True),
                    MasterProductCollectionMembership.master_sku.in_(selected_skus),
                )
            )
        )
        source_images = list(
            session.scalars(
                select(MasterProductImage)
                .where(MasterProductImage.sku.in_(selected_skus))
                .order_by(MasterProductImage.id)
            )
        )
        source_relations = list(
            session.scalars(
                select(MasterProductRelation).where(
                    MasterProductRelation.parent_sku.in_(selected_skus)
                    | MasterProductRelation.child_sku.in_(selected_skus)
                )
            )
        )

    source_taxonomy = list(
        session.scalars(
            select(MasterTaxonomyNode)
            .where(MasterTaxonomyNode.is_active.is_(True))
            .order_by(MasterTaxonomyNode.id)
        )
    )
    active_taxonomy_ids = {int(row.id) for row in source_taxonomy}
    orphaned_taxonomy = [
        row for row in source_taxonomy
        if row.parent_id is not None and int(row.parent_id) not in active_taxonomy_ids
    ]
    if orphaned_taxonomy:
        source_taxonomy = [
            row for row in source_taxonomy
            if row.parent_id is None or int(row.parent_id) in active_taxonomy_ids
        ]

    stats.source_counts = {
        "master_product": len(source_products),
        "master_product_attribute_value": len(source_attrs),
        "master_taxonomy_node": len(source_taxonomy),
        "orphaned_active_taxonomy_nodes_skipped": len(orphaned_taxonomy),
        "manual_taxonomy_assignment": len(source_manual),
        "manual_taxonomy_assignment_collection": len(source_manual_collections),
        "master_product_collection_membership": len(source_memberships),
        "master_product_image": len(source_images),
        "master_product_relation": len(source_relations),
        "selected_skus": len(selected_skus),
    }

    if reset:
        _clear_foundation_tables(session)
    attr_map = _group_attributes(source_attrs)
    manual_by_sku = _group_manual_assignments(source_manual)
    category_indexes = _upsert_categories(session, source_taxonomy, stats, reset=reset)
    _clear_selected_products(session, selected_skus)
    product_index = _upsert_products(session, source_products, attr_map, source_relations, stats)
    session.flush()

    _rebuild_product_categories(
        session=session,
        source_products=source_products,
        manual_by_sku=manual_by_sku,
        category_indexes=category_indexes,
        product_index=product_index,
        stats=stats,
    )
    _rebuild_product_collections(
        session=session,
        source_memberships=source_memberships,
        source_manual_collections=source_manual_collections,
        category_indexes=category_indexes,
        product_index=product_index,
        stats=stats,
    )
    _rebuild_media_assets(
        session=session,
        source_images=source_images,
        product_index=product_index,
        stats=stats,
    )
    _rebuild_configurable_links(
        session=session,
        source_relations=source_relations,
        product_index=product_index,
        attr_map=attr_map,
        stats=stats,
    )

    return stats.as_dict()


def _clear_foundation_tables(session: Session) -> None:
    session.execute(delete(V2ProductCategory))
    session.execute(delete(V2ProductCollection))
    session.execute(delete(V2MediaAsset))
    session.execute(delete(V2ConfigurableLink))
    session.execute(delete(V2Product))
    session.execute(delete(V2Category))


def _group_attributes(
    rows: Sequence[Tuple[str, str, Optional[str]]],
) -> Dict[str, Dict[str, str]]:
    grouped: Dict[str, Dict[str, str]] = {}
    for sku, attribute_code, value in rows:
        if not sku or not attribute_code:
            continue
        grouped.setdefault(str(sku).strip(), {})[str(attribute_code).strip()] = str(value or "").strip()
    return grouped


def _group_manual_assignments(
    rows: Sequence[ManualTaxonomyAssignment],
) -> Dict[str, List[ManualTaxonomyAssignment]]:
    grouped: Dict[str, List[ManualTaxonomyAssignment]] = {}
    for row in rows:
        sku = str(row.source_sku or "").strip()
        if not sku:
            continue
        grouped.setdefault(sku, []).append(row)
    return grouped


def _upsert_categories(
    session: Session,
    source_taxonomy: Sequence[MasterTaxonomyNode],
    stats: V2BackfillStats,
    *,
    reset: bool = False,
) -> Dict[str, Dict[object, int]]:
    existing_by_key = {
        (row.parent_id, row.node_type, _normalize_name(row.name)): row
        for row in session.scalars(select(V2Category))
    }
    by_legacy_id: Dict[int, int] = {}
    by_path_slug: Dict[str, int] = {}
    by_name: Dict[Tuple[Optional[int], str], int] = {}
    pending = list(source_taxonomy)
    unresolved = {row.id for row in pending}

    while pending:
        progressed = False
        next_pending: List[MasterTaxonomyNode] = []
        for row in pending:
            if row.parent_id is not None and row.parent_id not in by_legacy_id:
                next_pending.append(row)
                continue

            parent_id = by_legacy_id.get(row.parent_id) if row.parent_id is not None else None
            node_type = _map_node_type(row.node_kind)
            name = _leaf_name(row.name)
            key = (parent_id, node_type, _normalize_name(name))
            current = existing_by_key.get(key)
            if current is None:
                current = V2Category(
                    name=name,
                    parent_id=parent_id,
                    level=_calculate_level(row.path_slug),
                    node_type=node_type,
                    created_by="v2_backfill",
                )
                session.add(current)
                session.flush()
                existing_by_key[key] = current
                stats.categories_created += 1
            elif reset:
                current.level = _calculate_level(row.path_slug)
                stats.categories_updated += 1
            by_legacy_id[row.id] = current.id
            if row.path_slug:
                by_path_slug[str(row.path_slug).strip()] = current.id
            by_name[(parent_id, _normalize_name(name))] = current.id
            unresolved.discard(row.id)
            progressed = True
        if not progressed:
            unresolved_preview = sorted(unresolved)[:10]
            raise RuntimeError(f"Unable to resolve taxonomy parent chain for legacy ids: {unresolved_preview}")
        pending = next_pending

    return {
        "by_legacy_id": by_legacy_id,
        "by_path_slug": by_path_slug,
        "by_name": by_name,
        "by_collection_name": {
            _normalize_name(row.name): row.id
            for row in existing_by_key.values()
            if row.node_type == "collection" and _normalize_name(row.name)
        },
    }


def _upsert_products(
    session: Session,
    source_products: Sequence[MasterProduct],
    attr_map: Dict[str, Dict[str, str]],
    source_relations: Sequence[MasterProductRelation],
    stats: V2BackfillStats,
) -> Dict[str, int]:
    parent_skus = {str(row.parent_sku).strip() for row in source_relations if str(row.parent_sku or "").strip()}
    child_skus = {str(row.child_sku).strip() for row in source_relations if str(row.child_sku or "").strip()}
    rows_to_insert: List[Dict[str, object]] = []

    for row in source_products:
        sku = str(row.sku or "").strip()
        if not sku:
            continue
        attrs = dict(attr_map.get(sku) or {})
        title = str(row.name or "").strip() or sku
        slug = _first_nonempty(
            attrs.get("url_key"),
            attrs.get("slug"),
            attrs.get("handle"),
            _slugify(title),
        )
        lifecycle_status = _map_lifecycle_status(row)
        product_type = _map_product_type(sku, parent_skus, child_skus)
        description = _first_nonempty(attrs.get("description"), attrs.get("short_description"))
        seo_title = _first_nonempty(attrs.get("meta_title"), title)
        seo_description = _first_nonempty(attrs.get("meta_description"), attrs.get("meta_keywords"))

        payload_attributes = {
            "source_item": row.source_item,
            "product_family": row.product_family,
            "category_l1": row.category_l1,
            "category_l2": row.category_l2,
            "category_l3": row.category_l3,
            "brand": row.brand,
            "manufacturer": row.manufacturer,
            "collection": row.collection,
            "membership_mode": row.membership_mode,
            "base_sku": row.base_sku,
            "variant_group_code": row.variant_group_code,
            "item_size": row.item_size,
            "item_style": row.item_style,
            "assembly_type": row.assembly_type,
            "stocked": row.stocked,
            "legacy_status": row.status,
            "legacy_is_active": row.is_active,
            "legacy_normalization_status": row.normalization_status,
            **attrs,
        }
        payload_attributes = {
            key: value
            for key, value in payload_attributes.items()
            if value not in (None, "")
        }

        rows_to_insert.append(
            {
                "sku": sku,
                "product_type": product_type,
                "lifecycle_status": lifecycle_status,
                "title": title,
                "slug": slug,
                "seo_title": seo_title,
                "seo_description": seo_description,
                "description": description,
                "capabilities": {},
                "attributes": payload_attributes,
                "source": "master_backfill",
            }
        )

    if rows_to_insert:
        session.bulk_insert_mappings(V2Product, rows_to_insert)
    stats.products_created += len(rows_to_insert)
    session.flush()
    return {
        str(sku): int(product_id)
        for sku, product_id in session.execute(select(V2Product.sku, V2Product.id)).all()
    }


def _clear_selected_products(session: Session, selected_skus: Set[str]) -> None:
    if not selected_skus:
        return
    product_ids = [
        int(product_id)
        for (product_id,) in session.execute(
            select(V2Product.id).where(V2Product.sku.in_(selected_skus))
        ).all()
    ]
    if not product_ids:
        return
    session.execute(delete(V2ProductCategory).where(V2ProductCategory.product_id.in_(product_ids)))
    session.execute(delete(V2ProductCollection).where(V2ProductCollection.product_id.in_(product_ids)))
    session.execute(delete(V2MediaAsset).where(V2MediaAsset.product_id.in_(product_ids)))
    session.execute(
        delete(V2ConfigurableLink).where(
            V2ConfigurableLink.parent_id.in_(product_ids) | V2ConfigurableLink.child_id.in_(product_ids)
        )
    )
    session.execute(delete(V2Product).where(V2Product.id.in_(product_ids)))


def _rebuild_product_categories(
    *,
    session: Session,
    source_products: Sequence[MasterProduct],
    manual_by_sku: Dict[str, List[ManualTaxonomyAssignment]],
    category_indexes: Dict[str, Dict[object, int]],
    product_index: Dict[str, int],
    stats: V2BackfillStats,
) -> None:
    inserted: Set[Tuple[int, int]] = set()
    rows_to_insert: List[Dict[str, int]] = []
    primary_updates: List[Dict[str, Optional[int]]] = []

    for source in source_products:
        sku = str(source.sku or "").strip()
        if not sku:
            continue
        product_id = product_index.get(sku)
        if product_id is None:
            continue

        direct_category_ids, shopping_l1_id, shopping_l2_id, primary_category_id = _resolve_product_category_ids(
            source=source,
            manual_rows=manual_by_sku.get(sku) or [],
            category_indexes=category_indexes,
        )

        primary_updates.append(
            {
                "id": product_id,
                "primary_category_id": primary_category_id,
                "shopping_l1_category_id": shopping_l1_id,
                "shopping_l2_category_id": shopping_l2_id,
            }
        )

        for category_id in sorted(direct_category_ids):
            key = (product_id, category_id)
            if key in inserted:
                continue
            rows_to_insert.append({"product_id": product_id, "category_id": category_id})
            inserted.add(key)
            stats.product_categories_rebuilt += 1

    if primary_updates:
        session.bulk_update_mappings(V2Product, primary_updates)
    if rows_to_insert:
        session.bulk_insert_mappings(V2ProductCategory, rows_to_insert)


def _rebuild_product_collections(
    *,
    session: Session,
    source_memberships: Sequence[MasterProductCollectionMembership],
    source_manual_collections: Sequence[ManualTaxonomyAssignmentCollection],
    category_indexes: Dict[str, Dict[object, int]],
    product_index: Dict[str, int],
    stats: V2BackfillStats,
) -> None:
    existing: Set[Tuple[int, int]] = set()
    by_path_slug = category_indexes["by_path_slug"]
    by_collection_name = category_indexes.get("by_collection_name", {})
    collection_slug_by_code = _collection_slug_by_code(session)
    collection_slug_by_name = _collection_slug_by_name(session)
    rows_to_insert: List[Dict[str, object]] = []

    for row in source_memberships:
        sku = str(row.master_sku or "").strip()
        product_id = product_index.get(sku)
        category_id = _resolve_collection_category_id(
            by_path_slug=by_path_slug,
            raw_path_slug=row.path_slug,
            collection_code=None,
            collection_name=row.collection_name,
            collection_slug_by_code=collection_slug_by_code,
            collection_slug_by_name=collection_slug_by_name,
            by_collection_name=by_collection_name,
        )
        if not product_id or not category_id:
            stats.skipped_collection_links += 1
            continue
        key = (product_id, category_id)
        if key in existing:
            continue
        rows_to_insert.append({"product_id": product_id, "collection_id": category_id, "is_primary": False})
        existing.add(key)
        stats.product_collections_rebuilt += 1

    for row in source_manual_collections:
        sku = str(row.master_sku or "").strip()
        product_id = product_index.get(sku)
        category_id = _resolve_collection_category_id(
            by_path_slug=by_path_slug,
            raw_path_slug=row.collection_path_slug,
            collection_code=row.collection_code,
            collection_name=row.collection_name,
            collection_slug_by_code=collection_slug_by_code,
            collection_slug_by_name=collection_slug_by_name,
            by_collection_name=by_collection_name,
        )
        if not product_id or not category_id:
            stats.skipped_collection_links += 1
            continue
        key = (product_id, category_id)
        if key in existing:
            continue
        rows_to_insert.append({"product_id": product_id, "collection_id": category_id, "is_primary": False})
        existing.add(key)
        stats.product_collections_rebuilt += 1

    if rows_to_insert:
        session.bulk_insert_mappings(V2ProductCollection, rows_to_insert)


def _rebuild_media_assets(
    *,
    session: Session,
    source_images: Sequence[MasterProductImage],
    product_index: Dict[str, int],
    stats: V2BackfillStats,
) -> None:
    existing: Set[Tuple[int, str]] = set()
    rows_to_insert: List[Dict[str, object]] = []

    for row in source_images:
        sku = str(row.sku or "").strip()
        product_id = product_index.get(sku)
        if not product_id:
            stats.skipped_media_assets += 1
            continue
        urls = _split_image_urls(row.image_url)
        if not urls:
            stats.skipped_media_assets += 1
            continue
        for index, public_url in enumerate(urls):
            r2_key = _derive_r2_key(public_url, row.file_name)
            if not r2_key:
                stats.skipped_media_assets += 1
                continue
            key = (product_id, r2_key)
            if key in existing:
                continue
            mime_type = mimetypes.guess_type(r2_key)[0] or "application/octet-stream"
            sort_order = int(row.sort_order or 0) + index
            rows_to_insert.append(
                {
                    "product_id": product_id,
                    "r2_key": r2_key,
                    "public_url": public_url,
                    "sha256": hashlib.sha256(public_url.encode("utf-8")).hexdigest(),
                    "mime_type": mime_type,
                    "alt_text": None,
                    "sort_order": sort_order,
                    "role_flags": [row.image_role] if row.image_role else [],
                    "is_active": True,
                }
            )
            existing.add(key)
            stats.media_assets_rebuilt += 1

    if rows_to_insert:
        session.bulk_insert_mappings(V2MediaAsset, rows_to_insert)


def _rebuild_configurable_links(
    *,
    session: Session,
    source_relations: Sequence[MasterProductRelation],
    product_index: Dict[str, int],
    attr_map: Dict[str, Dict[str, str]],
    stats: V2BackfillStats,
) -> None:
    existing: Set[Tuple[int, int]] = set()
    rows_to_insert: List[Dict[str, object]] = []
    for row in source_relations:
        parent_sku = str(row.parent_sku or "").strip()
        child_sku = str(row.child_sku or "").strip()
        parent_id = product_index.get(parent_sku)
        child_id = product_index.get(child_sku)
        if not parent_id or not child_id:
            continue
        key = (parent_id, child_id)
        if key in existing:
            continue
        axis_payload = {}
        if isinstance(row.option_mapping, dict):
            axis_payload.update(row.option_mapping)
        child_attrs = attr_map.get(child_sku) or {}
        if "variation_axis" in child_attrs:
            axis_payload.setdefault("variation_axis", child_attrs["variation_axis"])
        rows_to_insert.append(
            {
                "parent_id": parent_id,
                "child_id": child_id,
                "axis_attributes": axis_payload,
            }
        )
        existing.add(key)
        stats.configurable_links_rebuilt += 1

    if rows_to_insert:
        session.bulk_insert_mappings(V2ConfigurableLink, rows_to_insert)


def _resolve_product_category_ids(
    *,
    source: MasterProduct,
    manual_rows: Sequence[ManualTaxonomyAssignment],
    category_indexes: Dict[str, Dict[object, int]],
) -> Tuple[Set[int], Optional[int], Optional[int], Optional[int]]:
    by_path_slug = category_indexes["by_path_slug"]
    by_name = category_indexes["by_name"]
    category_ids: Set[int] = set()

    primary_category_id: Optional[int] = None
    shopping_l1_id: Optional[int] = None
    shopping_l2_id: Optional[int] = None

    active_manual = [row for row in manual_rows if str(row.assignment_status or "active").strip() == "active"]
    for row in active_manual:
        primary_category_id = primary_category_id or by_path_slug.get(str(row.canonical_taxonomy_path_slug or "").strip())
        shopping_l1_id = shopping_l1_id or _find_category_by_name(by_name, None, row.shopping_l1)
        shopping_l2_id = shopping_l2_id or _find_category_by_name(by_name, shopping_l1_id, row.shopping_l2)

    if primary_category_id is None:
        primary_category_id = _resolve_fallback_primary_category(source, by_name)

    if shopping_l1_id is None:
        shopping_l1_id = _find_category_by_name(by_name, None, source.category_l1)

    if shopping_l2_id is None:
        shopping_l2_id = _find_category_by_name(by_name, shopping_l1_id, source.category_l2)

    if primary_category_id is not None:
        category_ids.add(primary_category_id)
    if shopping_l1_id is not None:
        category_ids.add(shopping_l1_id)
    if shopping_l2_id is not None:
        category_ids.add(shopping_l2_id)

    return category_ids, shopping_l1_id, shopping_l2_id, primary_category_id


def _resolve_fallback_primary_category(
    source: MasterProduct,
    by_name: Dict[Tuple[Optional[int], str], int],
) -> Optional[int]:
    l1_id = _find_category_by_name(by_name, None, source.category_l1)
    l2_id = _find_category_by_name(by_name, l1_id, source.category_l2)
    l3_id = _find_category_by_name(by_name, l2_id, source.category_l3)
    return l3_id or l2_id or l1_id


def _find_category_by_name(
    by_name: Dict[Tuple[Optional[int], str], int],
    parent_id: Optional[int],
    value: Optional[str],
) -> Optional[int]:
    normalized = _normalize_name(value)
    if not normalized:
        return None
    return by_name.get((parent_id, normalized))


def _map_node_type(value: Optional[str]) -> str:
    normalized = str(value or "category").strip().lower()
    if normalized in {"hub", "category", "collection"}:
        return normalized
    return "category"


def _map_lifecycle_status(product: MasterProduct) -> str:
    status = str(product.status or "").strip().lower()
    if not product.is_active:
        return "archived"
    if status in {"disabled", "inactive", "draft"}:
        return "disabled"
    return "active"


def _map_product_type(
    sku: str,
    parent_skus: Set[str],
    child_skus: Set[str],
) -> str:
    if sku in parent_skus:
        return "configurable"
    if sku in child_skus:
        return "simple"
    return "simple"


def _first_nonempty(*values: Optional[str]) -> Optional[str]:
    for value in values:
        text = str(value or "").strip()
        if text:
            return text
    return None


def _leaf_name(value: Optional[str]) -> str:
    text = str(value or "").strip()
    if " / " in text:
        return text.split(" / ")[-1].strip()
    return text


def _normalize_name(value: Optional[str]) -> str:
    return " ".join(str(value or "").strip().lower().split())


def _calculate_level(path_slug: Optional[str]) -> int:
    parts = [part for part in str(path_slug or "").strip("/").split("/") if part]
    return max(0, len(parts) - 1)


def _slugify(value: str) -> str:
    return "-".join(
        chunk
        for chunk in "".join(ch.lower() if ch.isalnum() else "-" for ch in value).split("-")
        if chunk
    )


def _split_image_urls(value: Optional[str]) -> List[str]:
    raw = str(value or "").strip()
    if not raw:
        return []
    return [
        item.strip()
        for item in raw.split(";")
        if item.strip()
    ]


def _derive_r2_key(public_url: str, fallback_name: Optional[str]) -> Optional[str]:
    parsed = urlparse(public_url)
    path = unquote(parsed.path or "").strip("/")
    if path:
        return path
    text = str(fallback_name or "").strip()
    if text:
        return text
    return None


def _collection_slug_by_code(session: Session) -> Dict[str, str]:
    rows = session.execute(select(MasterCollectionRegistry.code, MasterCollectionRegistry.path_slug)).all()
    return {
        str(code or "").strip().upper(): str(path_slug or "").strip()
        for code, path_slug in rows
        if str(code or "").strip() and str(path_slug or "").strip()
    }


def _collection_slug_by_name(session: Session) -> Dict[str, str]:
    rows = session.execute(select(MasterCollectionRegistry.name, MasterCollectionRegistry.path_slug)).all()
    return {
        _normalize_name(name): str(path_slug or "").strip()
        for name, path_slug in rows
        if _normalize_name(name) and str(path_slug or "").strip()
    }


def _resolve_collection_category_id(
    *,
    by_path_slug: Dict[str, int],
    raw_path_slug: Optional[str],
    collection_code: Optional[str],
    collection_name: Optional[str],
    collection_slug_by_code: Dict[str, str],
    collection_slug_by_name: Dict[str, str],
    by_collection_name: Dict[str, int],
) -> Optional[int]:
    direct_slug = str(raw_path_slug or "").strip()
    if direct_slug and direct_slug in by_path_slug:
        return by_path_slug[direct_slug]

    code = str(collection_code or "").strip().upper()
    if code:
        resolved_slug = collection_slug_by_code.get(code)
        if resolved_slug and resolved_slug in by_path_slug:
            return by_path_slug[resolved_slug]

    normalized_name = _normalize_name(collection_name)
    if normalized_name:
        resolved_slug = collection_slug_by_name.get(normalized_name)
        if resolved_slug and resolved_slug in by_path_slug:
            return by_path_slug[resolved_slug]
        if normalized_name in by_collection_name:
            return by_collection_name[normalized_name]

    return None