"""Enrichment for master-product workspace rows (channel IDs, taxonomy labels)."""

from __future__ import annotations

from typing import Any, Dict, List, Optional, Sequence, Set

from sqlalchemy import select
from sqlalchemy.orm import Session

from channel.url_canonical import normalize_plp_path
from db.catalog_intent_planner import ALL_PRODUCTS
from db.channel_exports import resolve_pipeline_channel_code
from db.channel_listing_path import resolve_listing_paths_for_sku
from db.channel_sku_mapping import mapping_by_master, remote_ids_by_master
from db.master_taxonomy_merge import resolve_canonical_path_slug
from db.master_taxonomy_product_paths import master_taxonomy_nodes_for_product
from db.models import (
    MagentoCatalogState,
    MagentoCategoryRegistry,
    MasterProduct,
    MasterTaxonomyNode,
    ProductChannelTaxonomyAssignment,
    ShopifyCollectionRegistry,
    ShopifyProductSourceSnapshot,
)
from db.product_channel_taxonomy import (
    SHOPIFY_COLLECTION_KIND,
    resolve_magento_category_targets,
    resolve_shopify_collection_ids,
)


def master_skus_on_channel(
    session: Session,
    channel_code: str,
    master_skus: Sequence[str],
    *,
    connection_id: Optional[int],
) -> Set[str]:
    """Master SKUs that have a remote product id on the channel (mapping or pulled catalog)."""
    channel = resolve_pipeline_channel_code(channel_code)
    wanted = sorted({str(s).strip() for s in master_skus if str(s).strip()})
    if not wanted or not connection_id:
        return set()

    on_channel: Set[str] = set(remote_ids_by_master(session, channel, wanted, connection_id=connection_id).keys())
    channel_skus = mapping_by_master(session, channel, wanted, connection_id=connection_id)
    if channel == "magento":
        on_channel |= set(_magento_catalog_product_ids(session, connection_id, channel_skus).keys())
    elif channel == "shopify":
        on_channel |= set(_shopify_catalog_product_ids(session, connection_id, channel_skus).keys())
    return on_channel


def filter_skus_without_channel_products(
    session: Session,
    master_skus: Sequence[str],
    *,
    without_magento: bool = False,
    without_shopify: bool = False,
    magento_connection_id: Optional[int] = None,
    shopify_connection_id: Optional[int] = None,
) -> List[str]:
    """Keep only SKUs that are not on the selected channel store(s)."""
    skus = [str(s).strip() for s in master_skus if str(s).strip()]
    if not skus:
        return []

    magento_on: Set[str] = set()
    shopify_on: Set[str] = set()
    if without_magento and magento_connection_id:
        magento_on = master_skus_on_channel(
            session,
            "magento",
            skus,
            connection_id=magento_connection_id,
        )
    if without_shopify and shopify_connection_id:
        shopify_on = master_skus_on_channel(
            session,
            "shopify",
            skus,
            connection_id=shopify_connection_id,
        )

    out: List[str] = []
    for sku in skus:
        if without_magento and magento_connection_id and sku in magento_on:
            continue
        if without_shopify and shopify_connection_id and sku in shopify_on:
            continue
        out.append(sku)
    return out


def enrich_workspace_sku_rows(
    session: Session,
    rows: List[Dict[str, Any]],
    *,
    magento_connection_id: Optional[int] = None,
    shopify_connection_id: Optional[int] = None,
) -> List[Dict[str, Any]]:
    """Attach channel product links and human-readable taxonomy labels to workspace rows."""
    if not rows:
        return rows

    skus = [str(row.get("master_sku") or "").strip() for row in rows]
    skus = [sku for sku in skus if sku]
    if not skus:
        return rows

    magento_channel_skus = mapping_by_master(
        session, "magento", skus, connection_id=magento_connection_id
    )
    shopify_channel_skus = mapping_by_master(
        session, "shopify", skus, connection_id=shopify_connection_id
    )
    magento_remote_ids = remote_ids_by_master(
        session, "magento", skus, connection_id=magento_connection_id
    )
    shopify_remote_ids = remote_ids_by_master(
        session, "shopify", skus, connection_id=shopify_connection_id
    )
    magento_catalog_ids = _magento_catalog_product_ids(
        session, magento_connection_id, magento_channel_skus
    )
    shopify_catalog_ids = _shopify_catalog_product_ids(
        session, shopify_connection_id, shopify_channel_skus
    )

    magento_taxonomy_ids: Set[str] = set()
    shopify_taxonomy_ids: Set[str] = set()
    for sku in skus:
        for item in _active_taxonomy_assignments(session, sku):
            if item.channel_code == "magento" and item.taxonomy_kind == "category":
                magento_taxonomy_ids.add(str(item.remote_id))
            if item.channel_code == "shopify" and item.taxonomy_kind == SHOPIFY_COLLECTION_KIND:
                shopify_taxonomy_ids.add(str(item.remote_id))

    magento_labels = _magento_category_labels(session, magento_connection_id, magento_taxonomy_ids)
    shopify_labels = _shopify_collection_labels(session, shopify_connection_id, shopify_taxonomy_ids)

    products = {
        p.sku: p
        for p in session.scalars(select(MasterProduct).where(MasterProduct.sku.in_(skus))).all()
    }
    from db.master_taxonomy_hierarchy import _attrs_by_sku

    attrs_by_sku = _attrs_by_sku(session, [p.id for p in products.values()])

    for row in rows:
        sku = str(row.get("master_sku") or "").strip()
        product = products.get(sku)
        attrs = attrs_by_sku.get(sku, {})
        row["channels"] = {
            "magento": _channel_product_link(
                channel_sku=magento_channel_skus.get(sku),
                remote_product_id=magento_remote_ids.get(sku) or magento_catalog_ids.get(sku),
                connection_id=magento_connection_id,
            ),
            "shopify": _channel_product_link(
                channel_sku=shopify_channel_skus.get(sku),
                remote_product_id=shopify_remote_ids.get(sku) or shopify_catalog_ids.get(sku),
                connection_id=shopify_connection_id,
            ),
        }
        row["master_taxonomies"] = (
            master_taxonomy_nodes_for_product(
                session,
                product,
                attrs=attrs,
            )
            if product is not None
            else []
        )
        canonical_fields = {
            "sku": sku,
            "title": product.name if product is not None else row.get("name"),
            "name": product.name if product is not None else row.get("name"),
            "category_l1": product.category_l1 if product is not None else row.get("category_l1"),
            "category_l2": product.category_l2 if product is not None else row.get("category_l2"),
            "category_l3": product.category_l3 if product is not None else row.get("category_l3"),
            "collection": product.collection if product is not None else row.get("collection"),
            "product_family": product.product_family if product is not None else row.get("product_family"),
        }
        channel_tax_all = canonical_channel_taxonomies_for_sku(
            session,
            sku,
            canonical_fields=canonical_fields,
            magento_labels=magento_labels,
            shopify_labels=shopify_labels,
            magento_connection_id=magento_connection_id,
            shopify_connection_id=shopify_connection_id,
        )
        channel_tax = collapse_to_leaf_channel_taxonomies(channel_tax_all) if channel_tax_all else []
        row["channel_taxonomies"] = channel_tax
        row["channel_taxonomies_total"] = len(channel_tax_all)
        row["direct_assignments"] = channel_taxonomies_for_sku(
            session,
            sku,
            magento_labels=magento_labels,
            shopify_labels=shopify_labels,
            magento_connection_id=magento_connection_id,
            shopify_connection_id=shopify_connection_id,
            leaf_only=True,
        )
    return rows


def master_taxonomies_for_product(
    session: Session,
    product: MasterProduct,
    *,
    attrs: Optional[Dict[str, str]] = None,
) -> List[Dict[str, Any]]:
    """Backward-compatible alias for canonical master taxonomy node list."""
    return master_taxonomy_nodes_for_product(session, product, attrs=attrs)


def _legacy_master_taxonomies_for_product(
    session: Session,
    product: MasterProduct,
    *,
    attrs: Optional[Dict[str, str]] = None,
) -> List[Dict[str, Any]]:
    """Assigned + inferred master taxonomy nodes for one product."""
    seen: Set[str] = set()
    out: List[Dict[str, Any]] = []

    for path in resolve_listing_paths_for_sku(session, product.sku):
        slug = normalize_plp_path(path.get("path_slug") or "")
        if not slug or slug in seen:
            continue
        seen.add(slug)
        node = _taxonomy_node_for_slug(session, slug)
        out.append(
            {
                "node_id": node.id if node else None,
                "path_slug": slug,
                "name": (node.name if node else None) or path.get("title") or slug,
                "source": "assigned",
            }
        )

    all_products_slug = normalize_plp_path(ALL_PRODUCTS)
    from db.catalog_intent_planner import category_targets_for_product

    for path in category_targets_for_product(product, attrs or {}):
        slug = normalize_plp_path(path)
        if not slug or slug == all_products_slug:
            continue
        canonical = resolve_canonical_path_slug(session, slug) or slug
        if canonical in seen:
            continue
        node = _taxonomy_node_for_slug(session, canonical)
        if node is None:
            continue
        seen.add(canonical)
        out.append(
            {
                "node_id": node.id,
                "path_slug": canonical,
                "name": node.name,
                "source": "inferred",
            }
        )
    return out


def _normalize_taxonomy_label_path(label: str) -> str:
    return str(label or "").strip().replace("\\", "/")


def collapse_to_leaf_channel_taxonomies(items: List[Dict[str, Any]]) -> List[Dict[str, Any]]:
    """Keep only deepest channel taxonomy paths — drop parents and duplicate chips."""
    if not items:
        return []
    if len(items) == 1:
        return [{**items[0], "is_leaf": True}]

    # Same remote category stored once per Magento connection → one chip.
    deduped: List[Dict[str, Any]] = []
    seen_remote: Set[tuple] = set()
    seen_path: Set[tuple] = set()
    for item in items:
        channel = str(item.get("channel_code") or "")
        kind = str(item.get("taxonomy_kind") or "")
        remote_id = str(item.get("remote_id") or "").strip()
        path = _normalize_taxonomy_label_path(item.get("label") or item.get("remote_path") or "")
        remote_key = (channel, kind, remote_id)
        path_key = (channel, kind, path.casefold()) if path else None
        if remote_id and remote_key in seen_remote:
            continue
        if path_key and path_key in seen_path:
            continue
        if remote_id:
            seen_remote.add(remote_key)
        if path_key:
            seen_path.add(path_key)
        deduped.append(item)

    if len(deduped) <= 1:
        return [{**item, "is_leaf": True} for item in deduped]

    grouped: Dict[tuple, List[Dict[str, Any]]] = {}
    for item in deduped:
        key = (str(item.get("channel_code") or ""), str(item.get("taxonomy_kind") or ""))
        grouped.setdefault(key, []).append(item)

    out: List[Dict[str, Any]] = []
    for group_items in grouped.values():
        if len(group_items) == 1:
            out.append({**group_items[0], "is_leaf": True})
            continue

        paths = [
            _normalize_taxonomy_label_path(item.get("label") or item.get("remote_path") or "")
            for item in group_items
        ]
        for idx, item in enumerate(group_items):
            path_a = paths[idx]
            if not path_a:
                out.append({**item, "is_leaf": True})
                continue
            is_ancestor = False
            for jdx, path_b in enumerate(paths):
                if idx == jdx or not path_b or path_b == path_a:
                    continue
                if path_b.startswith(f"{path_a}/"):
                    is_ancestor = True
                    break
            if not is_ancestor:
                out.append({**item, "is_leaf": True})
    return out


def channel_taxonomies_for_sku(
    session: Session,
    master_sku: str,
    *,
    magento_labels: Optional[Dict[str, str]] = None,
    shopify_labels: Optional[Dict[str, str]] = None,
    magento_connection_id: Optional[int] = None,
    shopify_connection_id: Optional[int] = None,
    leaf_only: bool = True,
) -> List[Dict[str, Any]]:
    """Channel taxonomy placements with display labels.

    When a Magento/Shopify connection is selected in the Products grid, only show
    assignments for that connection (plus connection-agnostic rows with NULL
    connection_id). Otherwise the same Default Category id appears once per
    Magento connection as duplicate ``M: Default Category`` chips.
    """
    out: List[Dict[str, Any]] = []
    for row in _active_taxonomy_assignments(session, master_sku):
        if not _assignment_matches_selected_connection(
            row,
            magento_connection_id=magento_connection_id,
            shopify_connection_id=shopify_connection_id,
        ):
            continue
        remote_id = str(row.remote_id)
        label = row.remote_path or ""
        if row.channel_code == "magento" and magento_labels:
            label = magento_labels.get(remote_id) or label or f"Category {remote_id}"
        elif row.channel_code == "shopify" and row.taxonomy_kind == SHOPIFY_COLLECTION_KIND and shopify_labels:
            label = shopify_labels.get(remote_id) or label or f"Collection {remote_id}"
        elif not label:
            label = f"{row.taxonomy_kind} {remote_id}"
        out.append(
            {
                "channel_code": row.channel_code,
                "taxonomy_kind": row.taxonomy_kind,
                "remote_id": remote_id,
                "remote_path": row.remote_path,
                "label": label,
                "connection_id": row.connection_id,
            }
        )
    if leaf_only and len(out) > 1:
        return collapse_to_leaf_channel_taxonomies(out)
    return out


def canonical_channel_taxonomies_for_sku(
    session: Session,
    master_sku: str,
    *,
    canonical_fields: Dict[str, Any],
    magento_labels: Optional[Dict[str, str]] = None,
    shopify_labels: Optional[Dict[str, str]] = None,
    magento_connection_id: Optional[int] = None,
    shopify_connection_id: Optional[int] = None,
) -> List[Dict[str, Any]]:
    """Resolved channel placements for the workspace grid.

    Prefer canonical listing-path-backed channel targets so stale historical
    assignment rows do not keep appearing after taxonomy reconciliation.
    """
    out: List[Dict[str, Any]] = []

    magento_ids, magento_paths = resolve_magento_category_targets(
        session,
        master_sku=master_sku,
        canonical_fields=canonical_fields,
        connection_id=magento_connection_id,
    )
    for idx, category_id in enumerate(magento_ids):
        remote_id = str(category_id)
        label = (magento_labels or {}).get(remote_id)
        remote_path = magento_paths[idx] if idx < len(magento_paths) else None
        out.append(
            {
                "channel_code": "magento",
                "taxonomy_kind": "category",
                "remote_id": remote_id,
                "remote_path": remote_path,
                "label": label or remote_path or f"Category {remote_id}",
                "connection_id": magento_connection_id,
            }
        )

    for remote_id in resolve_shopify_collection_ids(
        session,
        master_sku=master_sku,
        connection_id=shopify_connection_id,
    ):
        collection_id = str(remote_id).strip()
        if not collection_id:
            continue
        out.append(
            {
                "channel_code": "shopify",
                "taxonomy_kind": SHOPIFY_COLLECTION_KIND,
                "remote_id": collection_id,
                "remote_path": None,
                "label": (shopify_labels or {}).get(collection_id) or f"Collection {collection_id}",
                "connection_id": shopify_connection_id,
            }
        )

    return out or channel_taxonomies_for_sku(
        session,
        master_sku,
        magento_labels=magento_labels,
        shopify_labels=shopify_labels,
        magento_connection_id=magento_connection_id,
        shopify_connection_id=shopify_connection_id,
        leaf_only=False,
    )


def _assignment_matches_selected_connection(
    row: ProductChannelTaxonomyAssignment,
    *,
    magento_connection_id: Optional[int],
    shopify_connection_id: Optional[int],
) -> bool:
    if row.connection_id is None:
        return True
    if row.channel_code == "magento":
        if magento_connection_id is None:
            return True
        return int(row.connection_id) == int(magento_connection_id)
    if row.channel_code == "shopify":
        if shopify_connection_id is None:
            return True
        return int(row.connection_id) == int(shopify_connection_id)
    return True


def switch_skus_taxonomy_node(
    session: Session,
    *,
    skus: Sequence[str],
    to_node_id: int,
    from_node_id: Optional[int] = None,
    replace_existing: bool = True,
    dry_run: bool = True,
    magento_connection_id: Optional[int] = None,
    shopify_connection_id: Optional[int] = None,
) -> Dict[str, Any]:
    """Move SKUs from one taxonomy node to another (listing paths + channel taxonomy)."""
    from db.channel_listing_path import bulk_remove_listing_paths
    from db.master_taxonomy_sync import assign_skus_to_taxonomy_node

    wanted = sorted({str(s).strip() for s in skus if str(s).strip()})
    if not wanted:
        raise ValueError("skus is required")

    stats: Dict[str, Any] = {"sku_count": len(wanted), "dry_run": dry_run, "unassign": None, "assign": None}
    if from_node_id is not None and int(from_node_id) != int(to_node_id):
        from_node = session.get(MasterTaxonomyNode, int(from_node_id))
        if from_node is None or not from_node.is_active:
            raise ValueError(f"Unknown from taxonomy node: {from_node_id}")
        if not dry_run:
            bulk_remove_listing_paths(
                session,
                path_slugs=[from_node.path_slug],
                skus=wanted,
                dry_run=False,
            )
            links = _channel_links_for_node(session, from_node.id)
            from db.product_channel_taxonomy import bulk_remove_sku_taxonomy

            for channel_code, link in links.items():
                if not link.get("remote_id"):
                    continue
                bulk_remove_sku_taxonomy(
                    session,
                    channel_code=channel_code,
                    taxonomy_kind=link["taxonomy_kind"],
                    remote_ids=[link["remote_id"]],
                    connection_id=link.get("connection_id"),
                    skus=wanted,
                    dry_run=False,
                )
        stats["unassign"] = {"from_node_id": from_node.id, "path_slug": from_node.path_slug}

    stats["assign"] = assign_skus_to_taxonomy_node(
        session,
        node_id=int(to_node_id),
        skus=wanted,
        replace_existing=replace_existing,
        dry_run=dry_run,
        magento_connection_id=magento_connection_id,
        shopify_connection_id=shopify_connection_id,
    )
    return stats


def _channel_product_link(
    *,
    channel_sku: Optional[str],
    remote_product_id: Optional[str],
    connection_id: Optional[int],
) -> Dict[str, Any]:
    remote = str(remote_product_id).strip() if remote_product_id else None
    return {
        "connection_id": connection_id,
        "channel_sku": channel_sku,
        "remote_product_id": remote,
        "linked": bool(remote),
    }


def _taxonomy_node_for_slug(session: Session, path_slug: str) -> Optional[MasterTaxonomyNode]:
    from db.master_taxonomy_hierarchy import active_lineage_node_by_slug

    return active_lineage_node_by_slug(session, path_slug)


def _active_taxonomy_assignments(session: Session, master_sku: str) -> List[ProductChannelTaxonomyAssignment]:
    return list(
        session.scalars(
            select(ProductChannelTaxonomyAssignment)
            .where(ProductChannelTaxonomyAssignment.master_sku == master_sku)
            .where(ProductChannelTaxonomyAssignment.assignment_status == "active")
            .order_by(ProductChannelTaxonomyAssignment.sort_order, ProductChannelTaxonomyAssignment.id)
        ).all()
    )


def _magento_category_labels(
    session: Session,
    connection_id: Optional[int],
    category_ids: Set[str],
) -> Dict[str, str]:
    if not connection_id or not category_ids:
        return {}
    ids = {int(cid) for cid in category_ids if str(cid).isdigit()}
    if not ids:
        return {}
    rows = session.scalars(
        select(MagentoCategoryRegistry).where(
            MagentoCategoryRegistry.connection_id == int(connection_id),
            MagentoCategoryRegistry.category_id.in_(ids),
        )
    ).all()
    return {str(row.category_id): str(row.path_names or row.name or row.category_id) for row in rows}


def _magento_catalog_product_ids(
    session: Session,
    connection_id: Optional[int],
    channel_skus: Dict[str, str],
) -> Dict[str, str]:
    if not connection_id or not channel_skus:
        return {}
    wanted = {str(v).strip() for v in channel_skus.values() if str(v).strip()}
    if not wanted:
        return {}
    by_channel_sku = {
        str(row.sku).strip(): str(row.magento_product_id)
        for row in session.scalars(
            select(MagentoCatalogState).where(
                MagentoCatalogState.connection_id == int(connection_id),
                MagentoCatalogState.sku.in_(wanted),
                MagentoCatalogState.magento_product_id.is_not(None),
            )
        ).all()
        if row.magento_product_id is not None
    }
    out: Dict[str, str] = {}
    for master_sku, channel_sku in channel_skus.items():
        remote = by_channel_sku.get(str(channel_sku).strip())
        if remote:
            out[master_sku] = remote
    return out


def _shopify_catalog_product_ids(
    session: Session,
    connection_id: Optional[int],
    channel_skus: Dict[str, str],
) -> Dict[str, str]:
    if not connection_id or not channel_skus:
        return {}
    wanted = {str(v).strip() for v in channel_skus.values() if str(v).strip()}
    if not wanted:
        return {}
    rows = session.scalars(
        select(ShopifyProductSourceSnapshot).where(
            ShopifyProductSourceSnapshot.connection_id == int(connection_id),
            ShopifyProductSourceSnapshot.sku.in_(wanted),
            ShopifyProductSourceSnapshot.valid_to.is_(None),
        )
    ).all()
    by_channel_sku: Dict[str, str] = {}
    for row in rows:
        sku = str(row.sku or "").strip()
        if not sku:
            continue
        product_id = row.shopify_product_id
        if product_id:
            by_channel_sku[sku] = str(product_id)
    out: Dict[str, str] = {}
    for master_sku, channel_sku in channel_skus.items():
        remote = by_channel_sku.get(str(channel_sku).strip())
        if remote:
            out[master_sku] = remote
    return out


def _shopify_collection_labels(
    session: Session,
    connection_id: Optional[int],
    collection_ids: Set[str],
) -> Dict[str, str]:
    if not connection_id or not collection_ids:
        return {}
    gids = {gid for gid in collection_ids if gid}
    if not gids:
        return {}
    rows = session.scalars(
        select(ShopifyCollectionRegistry).where(
            ShopifyCollectionRegistry.connection_id == int(connection_id),
            ShopifyCollectionRegistry.collection_id.in_(gids),
        )
    ).all()
    out = {str(row.collection_id): str(row.title or row.handle or row.collection_id) for row in rows}
    for gid in gids:
        if gid not in out and gid.startswith("gid://"):
            numeric = gid.rsplit("/", 1)[-1]
            for row in rows:
                if str(row.collection_id or "").endswith(numeric):
                    out[gid] = str(row.title or row.handle or gid)
    return out


def _channel_links_for_node(session: Session, node_id: int) -> Dict[str, Dict[str, Any]]:
    from db.models import MasterTaxonomyChannelLink

    links: Dict[str, Dict[str, Any]] = {}
    for link in session.scalars(
        select(MasterTaxonomyChannelLink)
        .where(MasterTaxonomyChannelLink.taxonomy_node_id == node_id)
        .where(MasterTaxonomyChannelLink.is_active.is_(True))
    ).all():
        links[link.channel_code] = {
            "remote_id": link.remote_id,
            "taxonomy_kind": link.taxonomy_kind,
            "connection_id": link.connection_id,
        }
    return links