from __future__ import annotations

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

import sqlalchemy as sa
from sqlalchemy import select
from sqlalchemy.dialects.postgresql import insert
from sqlalchemy.orm import Session

from db.models import MasterProductRelation
from db.source_imports import normalize_column_key


def extract_relations_from_records(
    records: Iterable[Dict[str, Any]],
    *,
    source_label: str = "master_sku",
) -> List[Dict[str, Any]]:
    """Build relation rows from master SKU import payloads (variant_of / variant_list / CV)."""
    rows: List[Dict[str, Any]] = []
    seen: Set[tuple[str, str]] = set()

    for record in records:
        sku = str(record.get("sku") or "").strip()
        payload = record.get("payload") or {}
        if not sku:
            continue
        normalized = {normalize_column_key(k): _clean(v) for k, v in payload.items() if _clean(v) is not None}

        parent_sku = _first_text(normalized, "variant_of", "plytix_variant_of")
        if parent_sku and parent_sku != sku:
            _append_relation(rows, seen, parent_sku=parent_sku, child_sku=sku, source_label=source_label)

        product_type = str(_first_text(normalized, "product_type") or "").strip().lower()
        children = _child_skus_from_payload(normalized)
        if children and ("configurable" in product_type or children):
            for order, child_sku in enumerate(children):
                if child_sku == sku:
                    continue
                _append_relation(
                    rows,
                    seen,
                    parent_sku=sku,
                    child_sku=child_sku,
                    sort_order=order,
                    source_label=source_label,
                )

        cv = _first_text(normalized, "configurable_variations", "plytix_configurable_variations") or ""
        if cv and "sku=" in cv.lower():
            for child_sku, option_mapping in _parse_configurable_variations(cv):
                if not child_sku:
                    continue
                parent_for_child = sku if child_sku in children or sku not in children else sku
                _append_relation(
                    rows,
                    seen,
                    parent_sku=parent_for_child,
                    child_sku=child_sku,
                    option_mapping=option_mapping,
                    source_label=source_label,
                )

    return rows


def upsert_relations(session: Session, rows: List[Dict[str, Any]]) -> int:
    if not rows:
        return 0
    stmt = insert(MasterProductRelation).values(rows)
    stmt = stmt.on_conflict_do_update(
        constraint="uq_master_product_relation_parent_child",
        set_={
            "option_mapping": stmt.excluded.option_mapping,
            "sort_order": stmt.excluded.sort_order,
            "source_label": stmt.excluded.source_label,
            "updated_at": sa.func.now(),
        },
    )
    session.execute(stmt)
    return len(rows)


def delete_relations_touching_skus(session: Session, skus: Iterable[str]) -> int:
    sku_list = sorted({str(s).strip() for s in skus if str(s).strip()})
    if not sku_list:
        return 0
    result = session.execute(
        sa.delete(MasterProductRelation).where(
            sa.or_(
                MasterProductRelation.parent_sku.in_(sku_list),
                MasterProductRelation.child_sku.in_(sku_list),
            )
        )
    )
    return int(result.rowcount or 0)


def children_by_parent(session: Session, parent_skus: Optional[Iterable[str]] = None) -> Dict[str, List[Dict[str, Any]]]:
    stmt = select(MasterProductRelation).order_by(
        MasterProductRelation.parent_sku,
        MasterProductRelation.sort_order,
        MasterProductRelation.child_sku,
    )
    if parent_skus is not None:
        parents = sorted({str(s).strip() for s in parent_skus if str(s).strip()})
        if not parents:
            return {}
        stmt = stmt.where(MasterProductRelation.parent_sku.in_(parents))
    grouped: Dict[str, List[Dict[str, Any]]] = {}
    for row in session.scalars(stmt).all():
        grouped.setdefault(row.parent_sku, []).append(_relation_dict(row))
    return grouped


def parent_by_child(session: Session, child_skus: Optional[Iterable[str]] = None) -> Dict[str, str]:
    stmt = select(MasterProductRelation.child_sku, MasterProductRelation.parent_sku)
    if child_skus is not None:
        children = sorted({str(s).strip() for s in child_skus if str(s).strip()})
        if not children:
            return {}
        stmt = stmt.where(MasterProductRelation.child_sku.in_(children))
    return {child: parent for child, parent in session.execute(stmt).all()}


def parent_skus_with_children(session: Session, skus: Iterable[str]) -> Set[str]:
    sku_list = sorted({str(s).strip() for s in skus if str(s).strip()})
    if not sku_list:
        return set()
    parents = {
        parent
        for (parent,) in session.execute(
            select(MasterProductRelation.parent_sku)
            .where(MasterProductRelation.parent_sku.in_(sku_list))
            .distinct()
        ).all()
    }
    return parents


def expand_skus_with_children(session: Session, skus: Set[str]) -> Set[str]:
    if not skus:
        return set()
    expanded = set(skus)
    grouped = children_by_parent(session, skus)
    for children in grouped.values():
        for child in children:
            expanded.add(child["child_sku"])
    return expanded


def expand_skus_with_parents(session: Session, skus: Set[str]) -> Set[str]:
    if not skus:
        return set()
    expanded = set(skus)
    mapping = parent_by_child(session, skus)
    expanded.update(mapping.values())
    return expanded


_VARIATION_AXIS_MAGENTO_DEFAULTS = {
    "variation_width_in": "width",
    "variation_height_in": "height",
    "variation_depth_in": "depth",
    "variation_angle": "angle",
    "width_in": "width",
    "height_in": "height",
    "depth_in": "depth",
    "angle": "angle",
}

_MAGENTO_AXIS_LABELS = {
    "width": "Width",
    "height": "Height",
    "depth": "Depth",
    "angle": "Angle",
}


def _resolve_magento_axis_code(
    session: Session,
    master_axis: str,
    *,
    channel_code: str = "magento",
) -> str:
    """Map master-catalog variation axis codes to channel configurable attribute codes."""
    from db.channel_variation_axes import ChannelVariationAxisResolver

    return ChannelVariationAxisResolver(session, channel_code).resolve_canonical_axis(master_axis)


def build_parent_relation_fields(
    session: Session,
    parent_skus: Iterable[str],
    *,
    channel_code: str = "magento",
    connection_id: Optional[int] = None,
) -> Dict[str, Dict[str, str]]:
    """Channel-oriented relation fields keyed by parent SKU."""
    from db.channel_variation_axes import build_parent_relation_fields_for_channel

    return build_parent_relation_fields_for_channel(
        session,
        parent_skus,
        channel_code=channel_code,
        connection_id=connection_id,
    )


def sync_relations_from_product_snapshots(session: Session, *, source_label: str = "product_snapshot") -> Dict[str, int]:
    """Bootstrap master_product_relation from current Plytix product_snapshot rows."""
    from db.models import ProductSnapshot

    snapshots = session.scalars(
        select(ProductSnapshot)
        .where(ProductSnapshot.valid_to.is_(None))
        .order_by(ProductSnapshot.sku)
    ).all()
    records = []
    for snap in snapshots:
        attrs = snap.attrs or {}
        base = {
            "sku": snap.sku,
            "product_type": snap.product_type,
            "variant_of": snap.variant_of,
            "variant_list": snap.variant_list,
        }
        payload = {**base, **attrs}
        records.append({"sku": snap.sku, "payload": payload})
    skus = [r["sku"] for r in records]
    delete_relations_touching_skus(session, skus)
    rows = extract_relations_from_records(records, source_label=source_label)
    upserted = upsert_relations(session, rows)
    return {"snapshot_rows": len(records), "relations_upserted": upserted}


def _relation_dict(row: MasterProductRelation) -> Dict[str, Any]:
    return {
        "parent_sku": row.parent_sku,
        "child_sku": row.child_sku,
        "option_mapping": dict(row.option_mapping or {}),
        "sort_order": row.sort_order,
        "source_label": row.source_label,
    }


def _append_relation(
    rows: List[Dict[str, Any]],
    seen: Set[tuple[str, str]],
    *,
    parent_sku: str,
    child_sku: str,
    option_mapping: Optional[Dict[str, str]] = None,
    sort_order: int = 0,
    source_label: str,
) -> None:
    parent_sku = str(parent_sku).strip()
    child_sku = str(child_sku).strip()
    if not parent_sku or not child_sku or parent_sku == child_sku:
        return
    key = (parent_sku, child_sku)
    if key in seen:
        for row in rows:
            if row["parent_sku"] == parent_sku and row["child_sku"] == child_sku:
                if option_mapping:
                    row["option_mapping"] = {**dict(row.get("option_mapping") or {}), **option_mapping}
                return
        return
    seen.add(key)
    rows.append(
        {
            "parent_sku": parent_sku,
            "child_sku": child_sku,
            "option_mapping": option_mapping or {},
            "sort_order": sort_order,
            "source_label": source_label,
        }
    )


def _child_skus_from_payload(payload: Dict[str, Any]) -> List[str]:
    variant_list = _first_text(payload, "variant_list", "plytix_variant_list") or ""
    if not variant_list:
        cv = _first_text(payload, "configurable_variations", "plytix_configurable_variations") or ""
        if cv and "sku=" not in cv.lower() and "=" not in cv:
            variant_list = cv
    children: List[str] = []
    seen: Set[str] = set()
    for part in variant_list.replace("|", ",").split(","):
        sku = part.strip()
        if sku and sku.lower().startswith("sku="):
            continue
        if sku and sku not in seen:
            seen.add(sku)
            children.append(sku)
    return children


def _parse_configurable_variations(cv: str) -> List[tuple[str, Dict[str, str]]]:
    parsed: List[tuple[str, Dict[str, str]]] = []
    for seg in cv.split("|"):
        seg = seg.strip()
        if not seg:
            continue
        child_sku = ""
        mapping: Dict[str, str] = {}
        for pair in seg.split(","):
            pair = pair.strip()
            if "=" not in pair:
                continue
            key, value = pair.split("=", 1)
            key_norm = normalize_column_key(key)
            value = value.strip()
            if key_norm == "sku":
                child_sku = value
            elif value:
                mapping[key_norm] = value
        if child_sku:
            parsed.append((child_sku, mapping))
    return parsed


def _first_text(payload: Dict[str, Any], *keys: str) -> Optional[str]:
    for key in keys:
        value = payload.get(normalize_column_key(key))
        if value is None:
            continue
        text = str(value).strip()
        if text and text.lower() not in {"nan", "none", "null"}:
            return text
    return None


def _clean(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