from __future__ import annotations

from collections import defaultdict
from typing import Any, Dict, Iterable, List, Optional

from sqlalchemy import select
from sqlalchemy.orm import Session

from db.models import (
    MagentoProductSourceSnapshot,
    PlytixProductSourceSnapshot,
    ProductSupplementalAttributeValue,
)
from db.source_imports import infer_destination_hint, normalize_column_key


SHOPIFY_PREFIXES = ("shopify_", "shopify_product_", "shopify_variant_")
MAGENTO_PREFIXES = ("magento_", "mage_")
PLYTIX_PREFIXES = ("plytix_",)


def build_attribute_audit(session: Session, *, include_magento: bool = True) -> List[Dict[str, Any]]:
    buckets: Dict[tuple, Dict[str, Any]] = {}

    for row in _iter_plytix_source_values(session):
        _add_value(buckets, row)
    for row in _iter_supplemental_values(session):
        _add_value(buckets, row)
    if include_magento:
        for row in _iter_magento_source_values(session):
            _add_value(buckets, row)

    rows = []
    for item in buckets.values():
        sample_values = sorted(item.pop("_sample_values"))[:5]
        skus = item.pop("_skus")
        item["sku_count"] = len(skus)
        item["sample_values"] = " | ".join(sample_values)
        item["recommendation"] = _recommend(item)
        rows.append(item)

    rows.sort(
        key=lambda r: (
            r["family_key"],
            _source_sort(r["source_system"]),
            r["destination_hint"] or "",
            r["column_key"],
            r["source_label"] or "",
        )
    )
    return rows


def _iter_plytix_source_values(session: Session) -> Iterable[Dict[str, Any]]:
    stmt = select(PlytixProductSourceSnapshot.sku, PlytixProductSourceSnapshot.payload).where(
        PlytixProductSourceSnapshot.valid_to.is_(None)
    )
    for sku, payload in session.execute(stmt).all():
        if not isinstance(payload, dict):
            continue
        for column_name, value in payload.items():
            value_text = _value_to_text(value)
            if value_text is None:
                continue
            column_key = normalize_column_key(column_name)
            yield {
                "source_system": "plytix",
                "source_label": "plytix_source",
                "sku": sku,
                "column_name": str(column_name),
                "column_key": column_key,
                "value": value_text,
                "destination_hint": infer_destination_hint(column_key),
            }


def _iter_supplemental_values(session: Session) -> Iterable[Dict[str, Any]]:
    stmt = select(
        ProductSupplementalAttributeValue.source_label,
        ProductSupplementalAttributeValue.sku,
        ProductSupplementalAttributeValue.column_name,
        ProductSupplementalAttributeValue.column_key,
        ProductSupplementalAttributeValue.value,
        ProductSupplementalAttributeValue.destination_hint,
    )
    for source_label, sku, column_name, column_key, value, destination_hint in session.execute(stmt).all():
        value_text = _value_to_text(value)
        if value_text is None:
            continue
        yield {
            "source_system": "supplemental",
            "source_label": source_label,
            "sku": sku,
            "column_name": column_name,
            "column_key": column_key,
            "value": value_text,
            "destination_hint": destination_hint or infer_destination_hint(column_key),
        }


def _iter_magento_source_values(session: Session) -> Iterable[Dict[str, Any]]:
    stmt = select(MagentoProductSourceSnapshot.sku, MagentoProductSourceSnapshot.payload).where(
        MagentoProductSourceSnapshot.valid_to.is_(None)
    )
    for sku, payload in session.execute(stmt).all():
        if not isinstance(payload, dict):
            continue
        for column_name, value in payload.items():
            if isinstance(value, (dict, list)):
                continue
            value_text = _value_to_text(value)
            if value_text is None:
                continue
            column_key = normalize_column_key(column_name)
            yield {
                "source_system": "magento",
                "source_label": "magento_source",
                "sku": sku,
                "column_name": str(column_name),
                "column_key": column_key,
                "value": value_text,
                "destination_hint": "magento",
            }


def _add_value(buckets: Dict[tuple, Dict[str, Any]], row: Dict[str, Any]) -> None:
    column_key = normalize_column_key(row["column_key"])
    family_key = semantic_family_key(column_key)
    key = (
        family_key,
        row["source_system"],
        row.get("source_label") or "",
        column_key,
        row.get("destination_hint") or "",
    )
    if key not in buckets:
        buckets[key] = {
            "family_key": family_key,
            "column_key": column_key,
            "column_name": row.get("column_name") or column_key,
            "source_system": row["source_system"],
            "source_label": row.get("source_label") or "",
            "destination_hint": row.get("destination_hint") or "",
            "value_count": 0,
            "non_empty_value_count": 0,
            "_skus": set(),
            "_sample_values": set(),
        }
    item = buckets[key]
    item["value_count"] += 1
    value = _value_to_text(row.get("value"))
    if value is None:
        return
    item["non_empty_value_count"] += 1
    item["_skus"].add(str(row.get("sku") or "").strip())
    if len(item["_sample_values"]) < 5 and value not in {"-", "0"}:
        item["_sample_values"].add(value[:160])


def semantic_family_key(column_key: str) -> str:
    key = normalize_column_key(column_key)
    for prefix in SHOPIFY_PREFIXES + MAGENTO_PREFIXES + PLYTIX_PREFIXES:
        if key.startswith(prefix):
            key = key[len(prefix):]
            break
    if key.startswith("seo_"):
        key = key[4:]
    if key in {"body_html", "html_description", "product_description"}:
        return "description"
    if key in {"title_tag", "page_title", "product_title"}:
        return "title"
    if key in {"meta_title", "metatitle"}:
        return "title"
    if key in {"meta_description", "metadescription"}:
        return "description"
    return key


def _recommend(row: Dict[str, Any]) -> str:
    source = row.get("source_system")
    hint = row.get("destination_hint")
    column_key = row.get("column_key") or ""
    if hint == "shopify" or column_key.startswith("shopify_"):
        return "shopify_only_review"
    if hint == "seo" or column_key.startswith(("seo_", "meta_")):
        return "seo_override_review"
    if source == "plytix":
        return "candidate_plytix_canonical"
    if source == "magento":
        return "magento_compare_only"
    return "review"


def _value_to_text(value: Any) -> Optional[str]:
    if value is None:
        return None
    text = str(value).strip()
    if not text or text.lower() in {"nan", "none", "null"}:
        return None
    return text


def _source_sort(source: str) -> int:
    return {"plytix": 0, "supplemental": 1, "magento": 2}.get(source, 9)