"""Backfill collection-shareable filter attributes onto empty collections.

Peer impute cannot help when a collection is entirely empty. This module:

1. Learns uniform values from collections that are already complete
2. Derives ``color`` / ``finish`` from the collection name
3. Propagates style attrs (door_style, construction, overlay) from series peers
4. Optionally stores the resolved profile on ``master_collection_registry.aliases``
5. Writes missing values onto member SKUs
"""

from __future__ import annotations

import re
from collections import Counter, defaultdict
from typing import Any, Dict, Iterable, List, Optional, Sequence, Tuple

from sqlalchemy import select
from sqlalchemy.orm import Session

from db.canonical_attributes import canonicalize_attribute_fields
from db.channel_exports import compose_canonical_fields
from db.collection_path_guard import is_polluted_collection_name
from db.collection_landing_pages import canonical_collection
from db.models import MasterCollectionRegistry, MasterProduct, MasterProductAttributeValue
from db.source_imports import normalize_column_key

DEFAULT_BACKFILL_ATTRIBUTES = (
    "door_style",
    "color",
    "finish",
    "cabinet_construction",
    "cabinet_door_overlay",
)

# Style attrs are uniform within a series line (Anna / Maya / …); color varies by finish name.
SERIES_PROPAGATED_ATTRIBUTES = (
    "door_style",
    "cabinet_construction",
    "cabinet_door_overlay",
)

_SERIES_LINE_PREFIXES = frozenset(
    {
        "anna",
        "maya",
        "eleanor",
        "grace",
        "harriet",
        "cooper",
        "hallmark",
        "galaxy",
        "metro",
        "merrill",
        "hurston",
        "plymouth",
        "johnson",
        "belmont",
        "quest",
        "bedford",
        "dayton",
        "chesapeake",
        "mchenry",
    }
)

# Longest-match phrases → storefront filter color buckets (audit top_values).
_COLOR_PHRASES: Tuple[Tuple[str, str], ...] = (
    ("natural rift white oak", "Natural Oak"),
    ("black rift white oak", "Black"),
    ("rift white oak", "Natural Oak"),
    ("white oak", "Natural Oak"),
    ("caramel harvest", "Brown"),
    ("midnight espresso", "Brown"),
    ("toffee pecan", "Brown"),
    ("golden oak", "Natural Oak"),
    ("ocean blue", "Blue"),
    ("pearl white", "White"),
    ("polar white", "White"),
    ("snow white", "White"),
    ("stone gray", "Grey"),
    ("slate gray", "Grey"),
    ("drift white", "White"),
    ("sandstone", "Beige"),
    ("espresso", "Brown"),
    ("horizon", "Grey"),
    ("frost", "White"),
    ("mist", "Grey"),
    ("slate", "Grey"),
    ("navy", "Blue"),
    ("black", "Black"),
    ("white", "White"),
    ("blue", "Blue"),
    ("gray", "Grey"),
    ("grey", "Grey"),
    ("brown", "Brown"),
)


def backfill_collection_filter_attributes(
    session: Session,
    *,
    attributes: Optional[Sequence[str]] = None,
    source_label: str = "collection_filter_backfill",
    update_registry: bool = True,
    dry_run: bool = True,
) -> Dict[str, Any]:
    wanted = _codes(attributes or DEFAULT_BACKFILL_ATTRIBUTES)
    products = list(
        session.scalars(
            select(MasterProduct)
            .where(MasterProduct.is_active.is_(True))
            .order_by(MasterProduct.collection, MasterProduct.sku)
        ).all()
    )
    attrs_by_product = _attrs_by_product(session, [product.id for product in products])
    field_by_sku: Dict[str, Dict[str, Any]] = {}
    products_by_collection: Dict[str, List[MasterProduct]] = defaultdict(list)
    for product in products:
        fields, _ = compose_canonical_fields(_core_fields(product), attrs_by_product.get(product.id, {}), {})
        field_by_sku[product.sku] = fields
        collection = _collection_name(fields, product)
        if collection:
            products_by_collection[collection].append(product)

    observed = _observed_profiles(session, products_by_collection, field_by_sku, wanted)
    registry_by_name = _registry_by_name(session)
    suggestions: List[Dict[str, Any]] = []
    updates: List[Dict[str, Any]] = []
    registry_updates: List[Dict[str, Any]] = []
    actionable_collection_count = 0
    polluted_skipped_count = 0

    for collection, members in sorted(products_by_collection.items()):
        if _should_skip_collection(session, collection, members):
            polluted_skipped_count += 1
            continue
        actionable_collection_count += 1
        profile: Dict[str, str] = {}
        sources: Dict[str, str] = {}
        for code in wanted:
            status = _collection_attr_status(members, field_by_sku, code)
            if status == "full":
                value = observed.get(collection, {}).get(code)
                if value:
                    profile[code] = value
                    sources[code] = "observed"
                continue
            if status != "empty":
                continue
            value, source = _resolve_empty_value(
                collection,
                code,
                observed=observed,
                registry_by_name=registry_by_name,
            )
            if not value:
                continue
            profile[code] = value
            sources[code] = source
            missing = [
                product
                for product in members
                if not str(field_by_sku.get(product.sku, {}).get(code) or "").strip()
            ]
            suggestion = {
                "collection": collection,
                "attribute": code,
                "value": value,
                "source": source,
                "missing_count": len(missing),
                "sample_missing_skus": [product.sku for product in missing[:25]],
            }
            suggestions.append(suggestion)
            for product in missing:
                updates.append(
                    {
                        "product": product,
                        "sku": product.sku,
                        "attribute": code,
                        "value": value,
                        "collection": collection,
                        "source": source,
                    }
                )

        if profile and update_registry:
            reg = registry_by_name.get(_norm_key(collection))
            if reg is not None:
                registry_updates.append(
                    {
                        "collection": collection,
                        "registry_id": reg.id,
                        "filter_attributes": dict(profile),
                        "sources": dict(sources),
                    }
                )

    if not dry_run:
        for update in updates:
            _upsert_attr(
                session,
                update["product"],
                update["attribute"],
                update["value"],
                source_label,
            )
        if update_registry:
            for item in registry_updates:
                reg = session.get(MasterCollectionRegistry, int(item["registry_id"]))
                if reg is None:
                    continue
                aliases = dict(reg.aliases or {})
                existing = dict(aliases.get("filter_attributes") or {})
                existing.update(item["filter_attributes"])
                aliases["filter_attributes"] = existing
                reg.aliases = aliases

    return {
        "status": "ok",
        "dry_run": dry_run,
        "attribute_count": len(wanted),
        "collection_count": actionable_collection_count,
        "raw_collection_count": len(products_by_collection),
        "polluted_skipped_count": polluted_skipped_count,
        "observed_full_profiles": {
            collection: values for collection, values in sorted(observed.items()) if values
        },
        "suggestion_count": len(suggestions),
        "would_update": len(updates),
        "updated": 0 if dry_run else len(updates),
        "registry_update_count": len(registry_updates),
        "suggestions": suggestions[:200],
        "registry_updates": registry_updates[:100],
    }


def derive_filter_color_from_collection(collection: str) -> Optional[str]:
    """Map a collection / series name onto a storefront filter color bucket."""
    text = canonical_collection(collection) or str(collection or "").strip()
    if not text:
        return None
    base = _strip_accessories_suffix(text)
    norm = _norm_key(base)
    for phrase, color in _COLOR_PHRASES:
        if norm == phrase or f" {phrase} " in f" {norm} " or norm.endswith(f" {phrase}"):
            return color
    # Fall back to canonicalize helpers (color_finish → coarse color).
    fields = canonicalize_attribute_fields({"collection": base})
    color_finish = str(fields.get("color_finish") or "").strip()
    if color_finish:
        for phrase, color in _COLOR_PHRASES:
            if _norm_key(color_finish) == phrase or phrase in _norm_key(color_finish):
                return color
        mapped = _coarse_color(color_finish)
        if mapped:
            return mapped
    return _coarse_color(base)


def derive_finish_from_collection(collection: str) -> Optional[str]:
    fields = canonicalize_attribute_fields({"collection": collection})
    finish_type = str(fields.get("finish_type") or "").strip()
    if finish_type:
        norm = finish_type.strip().lower()
        if "paint" in norm:
            return "Painted"
        if "stain" in norm:
            return "Stained"
        if "wire-brushed" in norm or "wire brushed" in norm:
            return "Wire-Brushed"
    color_finish = str(fields.get("color_finish") or "").strip()
    color_norm = color_finish.strip().lower()
    if "paint" in color_norm:
        return "Painted"
    if "stain" in color_norm:
        return "Stained"
    if "wire-brushed" in color_norm or "wire brushed" in color_norm:
        return "Wire-Brushed"
    return color_finish or None


def collection_series_key(collection: str) -> Optional[str]:
    text = canonical_collection(collection) or str(collection or "").strip()
    if not text:
        return None
    base = _strip_accessories_suffix(text)
    parts = base.split()
    if not parts:
        return None
    first = parts[0].lower()
    if first in _SERIES_LINE_PREFIXES:
        return parts[0].title() if first != "mchenry" else "McHenry"
    return None


def accessory_base_name(collection: str) -> Optional[str]:
    text = canonical_collection(collection) or str(collection or "").strip()
    if not text:
        return None
    match = re.match(r"^(?P<base>.+?)\s*-\s*accessories\s*$", text, flags=re.IGNORECASE)
    if not match:
        return None
    return canonical_collection(match.group("base")) or match.group("base").strip()


def _resolve_empty_value(
    collection: str,
    code: str,
    *,
    observed: Dict[str, Dict[str, str]],
    registry_by_name: Dict[str, MasterCollectionRegistry],
) -> Tuple[Optional[str], str]:
    reg = registry_by_name.get(_norm_key(collection))
    if reg and isinstance(reg.aliases, dict):
        stored = (reg.aliases or {}).get("filter_attributes") or {}
        if isinstance(stored, dict):
            value = str(stored.get(code) or "").strip()
            if value:
                return value, "registry.aliases"

    if code == "color":
        value = derive_filter_color_from_collection(collection)
        if value:
            return value, "collection_name"

    if code == "finish":
        value = derive_finish_from_collection(collection)
        if value:
            return value, "collection_name"

    if code in SERIES_PROPAGATED_ATTRIBUTES:
        donor_value, donor_name = _series_donor_value(collection, code, observed)
        if donor_value:
            return donor_value, f"series:{donor_name}"

    return None, ""


def _series_donor_value(
    collection: str,
    code: str,
    observed: Dict[str, Dict[str, str]],
) -> Tuple[Optional[str], str]:
    # 1) Exact accessory base ("Snow White - Accessories" → "Snow White")
    base = accessory_base_name(collection)
    if base and base in observed and observed[base].get(code):
        return observed[base][code], base

    # 2) Any observed collection whose name ends with the accessory base
    if base:
        suffix = _norm_key(base)
        for name, profile in observed.items():
            if profile.get(code) and _norm_key(name).endswith(suffix):
                return profile[code], name

    # 3) Same series line prefix (Anna → Anna Snow White, …)
    series = collection_series_key(collection)
    if series:
        candidates = [
            (name, profile[code])
            for name, profile in observed.items()
            if profile.get(code) and collection_series_key(name) == series
        ]
        if candidates:
            # Prefer unanimous series value; otherwise most common.
            counts = Counter(value for _, value in candidates)
            value, _ = counts.most_common(1)[0]
            donor = next(name for name, candidate in candidates if candidate == value)
            return value, donor

    return None, ""


def _observed_profiles(
    session: Session,
    products_by_collection: Dict[str, List[MasterProduct]],
    field_by_sku: Dict[str, Dict[str, Any]],
    wanted: Sequence[str],
) -> Dict[str, Dict[str, str]]:
    out: Dict[str, Dict[str, str]] = {}
    for collection, members in products_by_collection.items():
        if _should_skip_collection(session, collection, members):
            continue
        profile: Dict[str, str] = {}
        for code in wanted:
            if _collection_attr_status(members, field_by_sku, code) != "full":
                continue
            values = [
                str(field_by_sku.get(product.sku, {}).get(code) or "").strip()
                for product in members
            ]
            non_empty = [value for value in values if value]
            if not non_empty:
                continue
            value, count = Counter(non_empty).most_common(1)[0]
            if count == len(non_empty):
                profile[code] = value
        if profile:
            out[collection] = profile
    return out


def _should_skip_collection(
    session: Session,
    collection: str,
    members: Sequence[MasterProduct],
) -> bool:
    category_l1 = next(
        (
            str(product.category_l1).strip()
            for product in members
            if str(product.category_l1 or "").strip()
        ),
        None,
    )
    return is_polluted_collection_name(session, collection, category_l1=category_l1)


def _collection_attr_status(
    members: Sequence[MasterProduct],
    field_by_sku: Dict[str, Dict[str, Any]],
    code: str,
) -> str:
    filled = 0
    for product in members:
        if str(field_by_sku.get(product.sku, {}).get(code) or "").strip():
            filled += 1
    if filled == 0:
        return "empty"
    if filled == len(members):
        return "full"
    return "partial"


def _registry_by_name(session: Session) -> Dict[str, MasterCollectionRegistry]:
    rows = list(
        session.scalars(
            select(MasterCollectionRegistry).where(MasterCollectionRegistry.is_active.is_(True))
        ).all()
    )
    out: Dict[str, MasterCollectionRegistry] = {}
    for row in rows:
        name = canonical_collection(row.name) or row.name
        out[_norm_key(name)] = row
    return out


def _attrs_by_product(session: Session, product_ids: Sequence[int]) -> Dict[int, Dict[str, Any]]:
    if not product_ids:
        return {}
    rows: Dict[int, Dict[str, Any]] = defaultdict(dict)
    for row in session.scalars(
        select(MasterProductAttributeValue).where(MasterProductAttributeValue.product_id.in_(list(product_ids)))
    ).all():
        if row.value is not None and str(row.value).strip():
            rows[int(row.product_id)][normalize_column_key(row.attribute_code)] = row.value
    return rows


def _upsert_attr(
    session: Session,
    product: MasterProduct,
    attribute_code: str,
    value: str,
    source_label: str,
) -> None:
    row = session.scalar(
        select(MasterProductAttributeValue)
        .where(MasterProductAttributeValue.product_id == product.id)
        .where(MasterProductAttributeValue.attribute_code == attribute_code)
    )
    if row is None:
        row = MasterProductAttributeValue(
            product_id=product.id,
            sku=product.sku,
            attribute_code=attribute_code,
        )
        session.add(row)
    row.value = value
    row.source_label = source_label


def _core_fields(product: MasterProduct) -> Dict[str, Any]:
    return {
        "title": product.name,
        "brand": product.brand,
        "manufacturer": product.manufacturer,
        "product_family": product.product_family,
        "category_l1": product.category_l1,
        "category_l2": product.category_l2,
        "category_l3": product.category_l3,
        "collection": product.collection,
        "assembly_type": product.assembly_type,
        "item_size": product.item_size,
        "status": product.status,
        "stocked": product.stocked,
    }


def _collection_name(fields: Dict[str, Any], product: MasterProduct) -> str:
    raw = str(fields.get("collection") or product.collection or "").strip()
    return canonical_collection(raw) or raw


def _strip_accessories_suffix(name: str) -> str:
    return re.sub(r"\s*-\s*accessories\s*$", "", name, flags=re.IGNORECASE).strip()


def _coarse_color(value: str) -> Optional[str]:
    norm = _norm_key(value)
    for phrase, color in _COLOR_PHRASES:
        if phrase in norm:
            return color
    return None


def _codes(values: Iterable[str]) -> List[str]:
    out: List[str] = []
    for value in values:
        code = normalize_column_key(value)
        if code and code not in out:
            out.append(code)
    return out


def _norm_key(value: str) -> str:
    return re.sub(r"[^a-z0-9]+", " ", str(value or "").lower()).strip()