"""Persistence helpers for reusable manual variation assignments."""

from __future__ import annotations

from datetime import datetime, timezone
from typing import Any, Dict, List, Optional, Sequence

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

from db.manual_taxonomy_assignments import _build_collection_lookup
from db.models import (
    ManualVariationAssignment,
    ManualVariationAssignmentChild,
    ManualVariationAssignmentCollection,
    MasterProduct,
    MasterProductRelation,
)
from db.variation_builder import commit_variation_plan, sanitize_option_attrs


MANUAL_VARIATION_SOURCE = "manual_variation"


def list_manual_variation_assignments(session: Session) -> Dict[str, Any]:
    persisted_templates, latest_updated_at = _persisted_manual_variation_templates(session)
    persisted_by_source = {
        str(item.get("source_parent_sku") or "").strip().upper(): item
        for item in persisted_templates
        if str(item.get("source_parent_sku") or "").strip()
    }
    legacy_templates = _legacy_manual_variation_templates(
        session,
        exclude_source_parent_skus=set(persisted_by_source),
    )
    templates = sorted(
        [*persisted_templates, *legacy_templates],
        key=lambda item: str(item.get("source_parent_sku") or item.get("parent_sku") or ""),
    )
    return {
        "generated_at_utc": latest_updated_at.isoformat() if latest_updated_at else None,
        "source": "manual_variation_assignment_table" if persisted_templates else "legacy_manual_variation_parents",
        "templates": templates,
        "template_count": len(templates),
        "relation_count": sum(len(item.get("children") or []) * max(1, len(item.get("collections") or [])) for item in templates),
    }


def replace_manual_variation_assignments(session: Session, payload: Dict[str, Any]) -> Dict[str, Any]:
    templates_raw = payload.get("templates")
    if not isinstance(templates_raw, list):
        raise ValueError("templates must be a list")

    normalized_templates = _dedupe_templates(templates_raw)
    _upsert_manual_variation_templates(session, normalized_templates)

    expanded = expand_manual_variation_templates(session, normalized_templates)
    result = commit_variation_plan(
        session,
        groups=expanded["groups"],
        source_label=MANUAL_VARIATION_SOURCE,
        replace_existing=True,
        assign_channels=payload.get("assign_channels") or ["magento"],
        mapping_connection_ids=payload.get("mapping_connection_ids") or {},
        create_identity_mappings=bool(payload.get("create_identity_mappings", True)),
    )
    listed = list_manual_variation_assignments(session)
    listed.update(
        {
            "saved_template_count": expanded["template_count"],
            "saved_relation_count": expanded["relation_count"],
            "parents_upserted": result.get("parents_upserted", 0),
            "relations_upserted": result.get("relations_upserted", 0),
            "assignments_upserted": result.get("assignments_upserted", 0),
            "mappings_upserted": result.get("mappings_upserted", 0),
            "shareable_attrs_copied": result.get("shareable_attrs_copied", 0),
            "parent_skus": result.get("parent_skus", []),
        }
    )
    return listed


def expand_manual_variation_templates(session: Session, templates_raw: List[Dict[str, Any]]) -> Dict[str, Any]:
    expanded_groups: List[Dict[str, Any]] = []
    template_count = 0
    relation_count = 0
    for item in templates_raw:
        if not isinstance(item, dict):
            continue
        groups = _expand_template_groups(session, item)
        if not groups:
            continue
        expanded_groups.extend(groups)
        template_count += 1
        relation_count += sum(len(group.get("children") or []) for group in groups)
    return {
        "groups": expanded_groups,
        "template_count": template_count,
        "relation_count": relation_count,
        "parent_count": len(expanded_groups),
        "parent_skus": [
            str(group.get("parent_sku") or "").strip()
            for group in expanded_groups
            if str(group.get("parent_sku") or "").strip()
        ],
    }


def _persisted_manual_variation_templates(session: Session) -> tuple[List[Dict[str, Any]], Optional[datetime]]:
    rows = session.scalars(
        select(ManualVariationAssignment)
        .where(ManualVariationAssignment.assignment_status == "active")
        .order_by(ManualVariationAssignment.source_parent_sku)
    ).all()
    if not rows:
        return [], None

    assignment_ids = [row.id for row in rows]
    collection_rows = session.scalars(
        select(ManualVariationAssignmentCollection)
        .where(ManualVariationAssignmentCollection.assignment_id.in_(assignment_ids))
        .order_by(
            ManualVariationAssignmentCollection.assignment_id,
            ManualVariationAssignmentCollection.sort_order,
            ManualVariationAssignmentCollection.collection_code,
        )
    ).all()
    child_rows = session.scalars(
        select(ManualVariationAssignmentChild)
        .where(ManualVariationAssignmentChild.assignment_id.in_(assignment_ids))
        .order_by(
            ManualVariationAssignmentChild.assignment_id,
            ManualVariationAssignmentChild.sort_order,
            ManualVariationAssignmentChild.source_child_sku,
        )
    ).all()

    collections_by_assignment: Dict[int, List[ManualVariationAssignmentCollection]] = {}
    for row in collection_rows:
        collections_by_assignment.setdefault(row.assignment_id, []).append(row)

    children_by_assignment: Dict[int, List[ManualVariationAssignmentChild]] = {}
    for row in child_rows:
        children_by_assignment.setdefault(row.assignment_id, []).append(row)

    templates = [
        _persisted_assignment_row_dict(
            row,
            collections_by_assignment.get(row.id, []),
            children_by_assignment.get(row.id, []),
        )
        for row in rows
    ]
    latest_updated_at = max((_normalize_timestamp(row.updated_at) for row in rows), default=None)
    return templates, latest_updated_at


def _persisted_assignment_row_dict(
    row: ManualVariationAssignment,
    collection_rows: Sequence[ManualVariationAssignmentCollection],
    child_rows: Sequence[ManualVariationAssignmentChild],
) -> Dict[str, Any]:
    raw_payload = row.raw_payload if isinstance(row.raw_payload, dict) else {}
    return {
        "source_parent_sku": str(row.source_parent_sku or "").strip().upper(),
        "source_parent_name": str(row.source_parent_name or "").strip(),
        "group_values": dict(row.group_values or {}),
        "option_attrs": sanitize_option_attrs(row.option_attrs or []),
        "children": [
            {
                "source_sku": str(child.source_child_sku or "").strip().upper(),
                "name": str(child.child_name or "").strip(),
                "option_mapping": _normalize_option_mapping(child.option_mapping or {}),
            }
            for child in child_rows
            if child.is_active
        ],
        "collections": [
            {
                "collection_code": str(item.collection_code or "").strip().upper(),
                "source_collection_code": str(item.source_collection_code or "").strip().upper(),
                "sku_prefix": str(item.sku_prefix or "").strip().upper(),
                "collection_name": str(item.collection_name or "").strip(),
                "collection_path_slug": str(item.collection_path_slug or "").strip(),
                "parent_sku": str(item.parent_sku or "").strip().upper(),
            }
            for item in collection_rows
            if item.is_active
        ],
        "scope_kind": str(row.scope_kind or raw_payload.get("scope_kind") or "selected").strip() or "selected",
        "source_label": MANUAL_VARIATION_SOURCE,
        "generated_by": MANUAL_VARIATION_SOURCE,
    }


def _legacy_manual_variation_templates(
    session: Session,
    *,
    exclude_source_parent_skus: Optional[set[str]] = None,
) -> List[Dict[str, Any]]:
    grouped: Dict[str, Dict[str, Any]] = {}
    excluded = {str(item or "").strip().upper() for item in (exclude_source_parent_skus or set()) if str(item or "").strip()}
    for parent in _manual_variation_parents(session):
        raw_payload = parent.raw_payload if isinstance(parent.raw_payload, dict) else {}
        source_parent_sku = str(raw_payload.get("source_parent_sku") or parent.sku).strip().upper()
        if not source_parent_sku or source_parent_sku in excluded:
            continue
        entry = grouped.setdefault(
            source_parent_sku,
            {
                "source_parent_sku": source_parent_sku,
                "source_parent_name": str(raw_payload.get("source_parent_name") or parent.name or "").strip(),
                "group_values": dict(raw_payload.get("group_values") or {}),
                "option_attrs": sanitize_option_attrs(raw_payload.get("option_attrs") or []),
                "children": list(raw_payload.get("source_children") or []),
                "collections": [],
                "scope_kind": str(raw_payload.get("scope_kind") or "selected").strip() or "selected",
                "source_label": MANUAL_VARIATION_SOURCE,
                "generated_by": MANUAL_VARIATION_SOURCE,
            },
        )
        collection_row = {
            "collection_code": str(raw_payload.get("collection_code") or "").strip().upper(),
            "source_collection_code": str(raw_payload.get("source_collection_code") or "").strip().upper(),
            "sku_prefix": str(raw_payload.get("sku_prefix") or "").strip().upper(),
            "collection_name": str(raw_payload.get("collection_name") or "").strip(),
            "collection_path_slug": str(raw_payload.get("collection_path_slug") or "").strip(),
            "parent_sku": parent.sku,
        }
        if collection_row["collection_code"]:
            existing_codes = {str(item.get("collection_code") or "").strip().upper() for item in entry["collections"]}
            if collection_row["collection_code"] not in existing_codes:
                entry["collections"].append(collection_row)
        if not entry["option_attrs"]:
            relations = _relations_for_parent(session, parent.sku)
            for relation in relations:
                for code in (relation.option_mapping or {}).keys():
                    text = str(code or "").strip()
                    if text and text not in entry["option_attrs"]:
                        entry["option_attrs"].append(text)
        if not entry["children"]:
            relations = _relations_for_parent(session, parent.sku)
            prefix = collection_row["sku_prefix"]
            entry["children"] = [
                {
                    "source_sku": _strip_prefix(relation.child_sku, prefix),
                    "name": _product_name(session, relation.child_sku),
                    "option_mapping": dict(relation.option_mapping or {}),
                }
                for relation in relations
            ]
    return sorted(grouped.values(), key=lambda item: str(item.get("source_parent_sku") or ""))


def _upsert_manual_variation_templates(session: Session, templates_raw: List[Dict[str, Any]]) -> None:
    collection_lookup = _build_collection_lookup(session)
    for item in templates_raw:
        source_parent_sku = str(item.get("source_parent_sku") or item.get("parent_sku") or "").strip().upper()
        if not source_parent_sku:
            continue

        assignment = session.scalar(
            select(ManualVariationAssignment).where(ManualVariationAssignment.source_parent_sku == source_parent_sku)
        )
        if assignment is None:
            assignment = ManualVariationAssignment(source_parent_sku=source_parent_sku)
            session.add(assignment)
            session.flush()

        normalized_children = _normalize_source_children(item.get("children") or [])
        option_attrs = sanitize_option_attrs(item.get("option_attrs") or [])
        if not option_attrs:
            option_attrs = _option_attrs_from_source_children(normalized_children)
        normalized_collections = _collections_for_storage(
            item,
            lookup=collection_lookup,
            source_parent_sku=source_parent_sku,
        )

        assignment.source_parent_name = str(item.get("source_parent_name") or item.get("parent_name") or "").strip() or None
        assignment.scope_kind = str(item.get("scope_kind") or "selected").strip() or "selected"
        assignment.option_attrs = option_attrs
        assignment.group_values = dict(item.get("group_values") or {})
        assignment.assignment_status = "active"
        assignment.raw_payload = {
            "source_label": MANUAL_VARIATION_SOURCE,
            "generated_by": MANUAL_VARIATION_SOURCE,
        }

        session.execute(
            delete(ManualVariationAssignmentCollection).where(
                ManualVariationAssignmentCollection.assignment_id == assignment.id
            )
        )
        session.execute(
            delete(ManualVariationAssignmentChild).where(
                ManualVariationAssignmentChild.assignment_id == assignment.id
            )
        )
        session.flush()

        for order, collection in enumerate(normalized_collections):
            session.add(
                ManualVariationAssignmentCollection(
                    assignment_id=assignment.id,
                    collection_registry_id=collection.get("collection_registry_id"),
                    collection_code=str(collection.get("collection_code") or "").strip().upper(),
                    source_collection_code=str(collection.get("source_collection_code") or "").strip().upper() or None,
                    sku_prefix=str(collection.get("sku_prefix") or "").strip().upper(),
                    collection_name=str(collection.get("collection_name") or "").strip() or None,
                    collection_path_slug=str(collection.get("collection_path_slug") or "").strip() or None,
                    parent_sku=str(collection.get("parent_sku") or "").strip().upper(),
                    sort_order=order,
                    is_active=True,
                    raw_payload=None,
                )
            )

        for order, child in enumerate(normalized_children):
            session.add(
                ManualVariationAssignmentChild(
                    assignment_id=assignment.id,
                    source_child_sku=str(child.get("source_sku") or "").strip().upper(),
                    child_name=str(child.get("name") or "").strip() or None,
                    option_mapping=_normalize_option_mapping(child.get("option_mapping") or {}),
                    sort_order=order,
                    is_active=True,
                    raw_payload=None,
                )
            )


def _collections_for_storage(
    item: Dict[str, Any],
    *,
    lookup: Dict[str, Dict[str, Any]],
    source_parent_sku: str,
) -> List[Dict[str, Any]]:
    collections = _resolve_template_collections_from_lookup(lookup, item)
    out: List[Dict[str, Any]] = []
    for collection in collections:
        sku_prefix = str(collection.get("sku_prefix") or collection.get("code") or "").strip().upper()
        code = str(collection.get("code") or "").strip().upper()
        if not sku_prefix or not code:
            continue
        out.append(
            {
                "collection_registry_id": collection.get("id"),
                "collection_code": code,
                "source_collection_code": str(collection.get("source_collection_code") or "").strip().upper(),
                "sku_prefix": sku_prefix,
                "collection_name": str(collection.get("name") or "").strip(),
                "collection_path_slug": str(collection.get("path_slug") or "").strip(),
                "parent_sku": f"{sku_prefix}-{source_parent_sku}",
            }
        )
    return out


def _dedupe_templates(templates_raw: List[Dict[str, Any]]) -> List[Dict[str, Any]]:
    normalized_templates: List[Dict[str, Any]] = []
    template_index_by_source: Dict[str, int] = {}
    for item in templates_raw:
        if not isinstance(item, dict):
            continue
        source_parent_sku = str(item.get("source_parent_sku") or item.get("parent_sku") or "").strip().upper()
        if not source_parent_sku:
            continue
        normalized = dict(item)
        normalized["source_parent_sku"] = source_parent_sku
        existing_index = template_index_by_source.get(source_parent_sku)
        if existing_index is None:
            template_index_by_source[source_parent_sku] = len(normalized_templates)
            normalized_templates.append(normalized)
        else:
            normalized_templates[existing_index] = normalized
    return normalized_templates


def _expand_template_groups(session: Session, item: Dict[str, Any]) -> List[Dict[str, Any]]:
    if item.get("source_parent_sku"):
        return _expand_base_sku_template(session, item)
    return [_normalize_full_sku_template(item)]


def _normalize_full_sku_template(item: Dict[str, Any]) -> Dict[str, Any]:
    parent_sku = str(item.get("parent_sku") or "").strip().upper()
    children = _normalize_children(item.get("children") or [])
    if not parent_sku or len(children) < 2:
        return {}
    option_attrs = sanitize_option_attrs(item.get("option_attrs") or [])
    if not option_attrs:
        option_attrs = _option_attrs_from_children(children)
    if not option_attrs:
        raise ValueError(f"manual variation '{parent_sku}' must include option_attrs or child option_mapping values")
    return {
        "parent_sku": parent_sku,
        "parent_name": str(item.get("parent_name") or parent_sku).strip(),
        "group_values": dict(item.get("group_values") or {}),
        "option_attrs": option_attrs,
        "children": children,
        "source_label": MANUAL_VARIATION_SOURCE,
        "generated_by": MANUAL_VARIATION_SOURCE,
    }


def _expand_base_sku_template(session: Session, item: Dict[str, Any]) -> List[Dict[str, Any]]:
    source_parent_sku = str(item.get("source_parent_sku") or "").strip().upper()
    if not source_parent_sku:
        return []
    option_attrs = sanitize_option_attrs(item.get("option_attrs") or [])
    children = _normalize_source_children(item.get("children") or [])
    if len(children) < 2:
        raise ValueError(f"manual variation '{source_parent_sku}' needs at least two child base SKUs")
    if not option_attrs:
        option_attrs = _option_attrs_from_source_children(children)
    if not option_attrs:
        raise ValueError(f"manual variation '{source_parent_sku}' must include option_attrs or child option_mapping values")
    collections = _resolve_template_collections(session, item)
    if not collections:
        raise ValueError(f"manual variation '{source_parent_sku}' did not resolve any active collections")

    groups: List[Dict[str, Any]] = []
    for collection in collections:
        sku_prefix = str(collection.get("sku_prefix") or collection.get("collection_code") or "").strip().upper()
        if not sku_prefix:
            continue
        parent_sku = f"{sku_prefix}-{source_parent_sku}"
        groups.append(
            {
                "parent_sku": parent_sku,
                "parent_name": str(item.get("source_parent_name") or item.get("parent_name") or parent_sku).strip(),
                "group_values": {
                    **dict(item.get("group_values") or {}),
                    "collection": str(collection.get("name") or dict(item.get("group_values") or {}).get("collection") or "").strip(),
                },
                "option_attrs": option_attrs,
                "children": [
                    {
                        "sku": f"{sku_prefix}-{child['source_sku']}",
                        "name": child.get("name") or "",
                        "option_mapping": dict(child.get("option_mapping") or {}),
                    }
                    for child in children
                ],
                "source_label": MANUAL_VARIATION_SOURCE,
                "generated_by": MANUAL_VARIATION_SOURCE,
                "raw_payload_extra": {
                    "source_parent_sku": source_parent_sku,
                    "source_parent_name": str(item.get("source_parent_name") or item.get("parent_name") or "").strip(),
                    "source_children": children,
                    "scope_kind": str(item.get("scope_kind") or "selected").strip() or "selected",
                    "collection_code": str(collection.get("code") or "").strip().upper(),
                    "source_collection_code": str(collection.get("source_collection_code") or "").strip().upper(),
                    "sku_prefix": sku_prefix,
                    "collection_name": str(collection.get("name") or "").strip(),
                    "collection_path_slug": str(collection.get("path_slug") or "").strip(),
                },
            }
        )
    return groups


def _resolve_template_collections(session: Session, item: Dict[str, Any]) -> List[Dict[str, Any]]:
    return _resolve_template_collections_from_lookup(_build_collection_lookup(session), item)


def _resolve_template_collections_from_lookup(
    lookup: Dict[str, Dict[str, Any]],
    item: Dict[str, Any],
) -> List[Dict[str, Any]]:
    by_code: Dict[str, Dict[str, Any]] = {}
    if str(item.get("scope_kind") or "").strip().lower() == "all":
        for row in lookup.values():
            code = str(row.get("code") or "").strip().upper()
            if code:
                by_code.setdefault(code, row)
        return list(by_code.values())

    for row in item.get("collections") or []:
        if not isinstance(row, dict):
            continue
        canonical_code = str(row.get("collection_code") or "").strip().upper()
        source_code = str(row.get("source_collection_code") or "").strip().upper()
        sku_prefix = str(row.get("sku_prefix") or "").strip().upper()
        resolved = lookup.get(canonical_code) or lookup.get(source_code) or lookup.get(sku_prefix)
        if not resolved:
            continue
        code = str(resolved.get("code") or canonical_code).strip().upper()
        if code:
            by_code.setdefault(code, resolved)
    return list(by_code.values())


def _normalize_children(children_raw: List[Dict[str, Any]]) -> List[Dict[str, Any]]:
    seen: set[str] = set()
    children: List[Dict[str, Any]] = []
    for child in children_raw:
        if not isinstance(child, dict):
            continue
        sku = str(child.get("sku") or "").strip().upper()
        if not sku or sku in seen:
            continue
        seen.add(sku)
        children.append(
            {
                "sku": sku,
                "name": str(child.get("name") or "").strip(),
                "option_mapping": _normalize_option_mapping(child.get("option_mapping") or {}),
            }
        )
    return children


def _normalize_source_children(children_raw: List[Dict[str, Any]]) -> List[Dict[str, Any]]:
    seen: set[str] = set()
    children: List[Dict[str, Any]] = []
    for child in children_raw:
        if not isinstance(child, dict):
            continue
        source_sku = str(child.get("source_sku") or child.get("sku") or "").strip().upper()
        if not source_sku or source_sku in seen:
            continue
        seen.add(source_sku)
        children.append(
            {
                "source_sku": source_sku,
                "name": str(child.get("name") or "").strip(),
                "option_mapping": _normalize_option_mapping(child.get("option_mapping") or {}),
            }
        )
    return children


def _normalize_option_mapping(mapping: Dict[str, Any]) -> Dict[str, str]:
    return {
        str(key or "").strip(): str(value or "").strip()
        for key, value in dict(mapping or {}).items()
        if str(key or "").strip() and str(value or "").strip()
    }


def _option_attrs_from_children(children: List[Dict[str, Any]]) -> List[str]:
    out: List[str] = []
    for child in children:
        for code in (child.get("option_mapping") or {}).keys():
            text = str(code or "").strip()
            if text and text not in out:
                out.append(text)
    return sanitize_option_attrs(out)


def _option_attrs_from_source_children(children: List[Dict[str, Any]]) -> List[str]:
    return _option_attrs_from_children(children)


def _manual_variation_parents(session: Session) -> List[MasterProduct]:
    rows = session.scalars(
        select(MasterProduct)
        .where(MasterProduct.is_active.is_(True))
        .where(MasterProduct.raw_payload.is_not(None))
        .order_by(MasterProduct.sku)
    ).all()
    out: List[MasterProduct] = []
    for row in rows:
        payload = row.raw_payload if isinstance(row.raw_payload, dict) else {}
        if str(payload.get("generated_by") or "").strip() == MANUAL_VARIATION_SOURCE:
            out.append(row)
    return out


def _relations_for_parent(session: Session, parent_sku: str) -> List[MasterProductRelation]:
    return session.scalars(
        select(MasterProductRelation)
        .where(MasterProductRelation.parent_sku == parent_sku)
        .order_by(MasterProductRelation.sort_order, MasterProductRelation.child_sku)
    ).all()


def _product_name(session: Session, sku: str) -> str:
    product = session.scalar(select(MasterProduct).where(MasterProduct.sku == sku))
    return str(product.name or "").strip() if product is not None else ""


def _strip_prefix(sku: str, prefix: str) -> str:
    text = str(sku or "").strip().upper()
    wanted = str(prefix or "").strip().upper()
    if wanted and text.startswith(f"{wanted}-"):
        return text[len(wanted) + 1 :]
    return text


def _normalize_timestamp(value: Optional[datetime]) -> Optional[datetime]:
    if value is None:
        return None
    if value.tzinfo is None:
        return value.replace(tzinfo=timezone.utc)
    return value.astimezone(timezone.utc)