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

from __future__ import annotations

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

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

from channel.url_canonical import normalize_plp_path
from db.master_taxonomy_registry import alias_codes_from_json
from db.models import (
    ManualTaxonomyAssignment,
    ManualTaxonomyAssignmentCollection,
    MasterCollectionRegistry,
)


def _collection_row_dict(row: ManualTaxonomyAssignmentCollection) -> Dict[str, Any]:
    alias_codes = row.alias_codes if isinstance(row.alias_codes, list) else []
    return {
        "collection_code": row.collection_code,
        "source_collection_code": row.source_collection_code,
        "sku_prefix": row.sku_prefix,
        "collection_name": row.collection_name,
        "collection_path_slug": row.collection_path_slug,
        "shopping_collection": row.shopping_collection,
        "master_sku": row.master_sku,
        "alias_codes": alias_codes,
    }


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)


def _path_label_part(slug: str) -> str:
    text = str(slug or "").strip().replace("-", " ")
    return text.title() if text else ""


def _manual_assignment_path_bundle(canonical_path_slug: str, collection_path_slug: str) -> Dict[str, List[str]]:
    canonical_slug = normalize_plp_path(canonical_path_slug or "")
    collection_slug = normalize_plp_path(collection_path_slug or "")
    plp_paths: List[str] = []
    membership_paths: List[str] = []

    if collection_slug:
        plp_paths.append(collection_slug)
        membership_paths.append(collection_slug)
    if canonical_slug:
        parts = [part for part in canonical_slug.split("/") if part]
        if len(parts) >= 2:
            plp_paths.append("/".join(parts[:2]))
        plp_paths.append(canonical_slug)
        membership_paths.append(canonical_slug)

    seen = set()
    deduped_plp: List[str] = []
    for item in plp_paths:
        if item and item not in seen:
            seen.add(item)
            deduped_plp.append(item)
    seen_membership = set()
    deduped_membership: List[str] = []
    for item in membership_paths:
        if item and item not in seen_membership:
            seen_membership.add(item)
            deduped_membership.append(item)
    return {
        "plp_path_slugs": deduped_plp,
        "membership_path_slugs": deduped_membership,
    }


def _assignment_row_dict(
    row: ManualTaxonomyAssignment,
    collection_rows: List[ManualTaxonomyAssignmentCollection],
) -> Dict[str, Any]:
    raw_payload = row.raw_payload if isinstance(row.raw_payload, dict) else {}
    return {
        "source_sku": row.source_sku,
        "canonical_taxonomy_path_slug": row.canonical_taxonomy_path_slug,
        "canonical_taxonomy_path_label": row.canonical_taxonomy_path_label,
        "shopping_l1": row.shopping_l1,
        "shopping_l2": row.shopping_l2,
        "l2_category": row.l2_category,
        "l3_category": row.l3_category,
        "scope_kind": str(raw_payload.get("scope_kind") or "selected").strip() or "selected",
        "scope_key": str(raw_payload.get("scope_key") or "").strip() or None,
        "collections": [_collection_row_dict(item) for item in collection_rows if item.is_active],
    }


def _build_collection_lookup(session: Session) -> Dict[str, Dict[str, Any]]:
    """Map codes/aliases → active master collections only.

    Inactive registry rows are ignored so Save to DB cannot expand polluted /
    deactivated collections into ``manual_taxonomy_assignment_collection``.
    """
    lookup: Dict[str, Dict[str, Any]] = {}
    rows = session.scalars(
        select(MasterCollectionRegistry)
        .where(MasterCollectionRegistry.is_active.is_(True))
        .order_by(MasterCollectionRegistry.id)
    ).all()
    for row in rows:
        payload = {
            "id": row.id,
            "code": row.code,
            "name": row.name,
            "path_slug": row.path_slug,
            "is_active": True,
            "source_collection_code": str((row.aliases or {}).get("source_collection_code") or "").strip()
            if isinstance(row.aliases, dict)
            else "",
            "alias_codes": alias_codes_from_json(row.aliases),
        }
        sku_prefix = payload["source_collection_code"] or payload["code"]
        payload["sku_prefix"] = sku_prefix
        keys = {str(row.code or "").strip().upper(), str(sku_prefix or "").strip().upper()}
        keys.update(str(code).strip().upper() for code in payload["alias_codes"] if code)
        for key in keys:
            if key:
                # First active wins; do not let later aliases clobber an earlier active row.
                lookup.setdefault(key, payload)
    return lookup


def list_manual_taxonomy_assignments(session: Session) -> Dict[str, Any]:
    rows = session.scalars(
        select(ManualTaxonomyAssignment)
        .where(ManualTaxonomyAssignment.assignment_status == "active")
        .order_by(ManualTaxonomyAssignment.source_sku, ManualTaxonomyAssignment.canonical_taxonomy_path_slug)
    ).all()
    if not rows:
        return {
            "generated_at_utc": None,
            "source": "manual_taxonomy_assignment_table",
            "templates": [],
            "assignments": [],
        }
    assignment_ids = [row.id for row in rows]
    child_rows = session.scalars(
        select(ManualTaxonomyAssignmentCollection)
        .where(ManualTaxonomyAssignmentCollection.assignment_id.in_(assignment_ids))
        .order_by(
            ManualTaxonomyAssignmentCollection.assignment_id,
            ManualTaxonomyAssignmentCollection.sort_order,
            ManualTaxonomyAssignmentCollection.collection_code,
        )
    ).all()
    children_by_assignment: Dict[int, List[ManualTaxonomyAssignmentCollection]] = {}
    for child in child_rows:
        children_by_assignment.setdefault(child.assignment_id, []).append(child)
    templates = [_assignment_row_dict(row, children_by_assignment.get(row.id, [])) for row in rows]
    assignments = [
        {
            "source_sku": template["source_sku"],
            "master_sku": item["master_sku"],
            "collection_code": item["collection_code"],
            "source_collection_code": item["source_collection_code"],
            "sku_prefix": item["sku_prefix"],
            "collection_name": item["collection_name"],
            "collection_path_slug": item["collection_path_slug"],
            "shopping_collection": item["shopping_collection"],
            "alias_codes": item["alias_codes"],
            "canonical_taxonomy_path_slug": template["canonical_taxonomy_path_slug"],
            "canonical_taxonomy_path_label": template["canonical_taxonomy_path_label"],
            "shopping_l1": template["shopping_l1"],
            "shopping_l2": template["shopping_l2"],
            "l2_category": template["l2_category"],
            "l3_category": template["l3_category"],
        }
        for template in templates
        for item in template["collections"]
    ]
    latest_updated_at = max(
        (_normalize_timestamp(row.updated_at) for row in rows),
        default=None,
    )
    return {
        "generated_at_utc": latest_updated_at.isoformat() if latest_updated_at else None,
        "source": "manual_taxonomy_assignment_table",
        "templates": templates,
        "assignments": assignments,
    }


def replace_manual_taxonomy_assignments(session: Session, payload: Dict[str, Any]) -> Dict[str, Any]:
    templates = payload.get("templates")
    if not isinstance(templates, list):
        raise ValueError("templates must be a list")
    normalized_templates: List[Dict[str, Any]] = []
    template_index_by_source: Dict[str, int] = {}
    for item in templates:
        if not isinstance(item, dict):
            continue
        source_sku = str(item.get("source_sku") or "").strip().upper()
        if not source_sku:
            continue
        existing_index = template_index_by_source.get(source_sku)
        if existing_index is None:
            template_index_by_source[source_sku] = len(normalized_templates)
            normalized_templates.append(item)
        else:
            normalized_templates[existing_index] = item

    collection_lookup = _build_collection_lookup(session)
    created_rules = 0
    created_assignments = 0
    skipped_inactive_or_unknown = 0
    skipped_duplicate = 0
    changed_master_skus: set[str] = set()

    for row in normalized_templates:
        source_sku = str(row.get("source_sku") or "").strip().upper()
        path_slug = str(row.get("canonical_taxonomy_path_slug") or "").strip()
        if not source_sku or not path_slug:
            continue
        existing_assignments = session.scalars(
            select(ManualTaxonomyAssignment)
            .where(ManualTaxonomyAssignment.source_sku == source_sku)
            .order_by(ManualTaxonomyAssignment.id)
        ).all()
        assignment = existing_assignments[0] if existing_assignments else None
        if assignment is None:
            assignment = ManualTaxonomyAssignment(
                source_sku=source_sku,
                canonical_taxonomy_path_slug=path_slug,
            )
            session.add(assignment)
            session.flush()
        else:
            existing_assignment_ids = [row.id for row in existing_assignments]
            existing_child_skus = session.scalars(
                select(ManualTaxonomyAssignmentCollection.master_sku).where(
                    ManualTaxonomyAssignmentCollection.assignment_id.in_(existing_assignment_ids)
                )
            ).all()
            changed_master_skus.update(
                str(sku).strip().upper() for sku in existing_child_skus if str(sku).strip()
            )
            session.execute(
                delete(ManualTaxonomyAssignmentCollection).where(
                    ManualTaxonomyAssignmentCollection.assignment_id.in_(existing_assignment_ids)
                )
            )
            if len(existing_assignments) > 1:
                duplicate_assignment_ids = existing_assignment_ids[1:]
                session.execute(
                    delete(ManualTaxonomyAssignment).where(
                        ManualTaxonomyAssignment.id.in_(duplicate_assignment_ids)
                    )
                )

        assignment.canonical_taxonomy_path_slug = path_slug
        assignment.canonical_taxonomy_path_label = str(row.get("canonical_taxonomy_path_label") or "").strip() or None
        assignment.shopping_l1 = str(row.get("shopping_l1") or "").strip() or None
        assignment.shopping_l2 = str(row.get("shopping_l2") or "").strip() or None
        assignment.l2_category = str(row.get("l2_category") or "").strip() or None
        assignment.l3_category = str(row.get("l3_category") or "").strip() or None
        assignment.assignment_status = "active"
        assignment.raw_payload = row
        created_rules += 1

        seen_codes: set[str] = set()
        sort_index = 0
        for item in row.get("collections") or []:
            if not isinstance(item, dict):
                skipped_inactive_or_unknown += 1
                continue
            canonical_code = str(item.get("collection_code") or "").strip().upper()
            source_code = str(item.get("source_collection_code") or "").strip().upper()
            sku_prefix = str(item.get("sku_prefix") or source_code or canonical_code).strip().upper()
            lookup = (
                collection_lookup.get(canonical_code)
                or collection_lookup.get(source_code)
                or collection_lookup.get(sku_prefix)
            )
            # Only persist rows that resolve to an active master collection.
            if not lookup:
                skipped_inactive_or_unknown += 1
                continue

            canonical_code = str(lookup.get("code") or canonical_code).strip().upper()
            if not canonical_code or canonical_code in seen_codes:
                skipped_duplicate += 1
                continue
            seen_codes.add(canonical_code)

            source_code = str(source_code or lookup.get("source_collection_code") or "").strip().upper()
            sku_prefix = str(sku_prefix or lookup.get("sku_prefix") or canonical_code).strip().upper()
            collection_name = str(item.get("collection_name") or lookup.get("name") or "").strip()
            collection_path_slug = str(item.get("collection_path_slug") or lookup.get("path_slug") or "").strip()
            alias_codes = lookup.get("alias_codes") or []
            collection_registry_id = lookup.get("id")
            shopping_collection = str(item.get("shopping_collection") or "").strip()
            master_sku = str(item.get("master_sku") or f"{sku_prefix}-{source_sku}").strip().upper()
            child = ManualTaxonomyAssignmentCollection(
                assignment_id=assignment.id,
                collection_registry_id=collection_registry_id,
                collection_code=canonical_code,
                source_collection_code=source_code or None,
                sku_prefix=sku_prefix,
                collection_name=collection_name or None,
                collection_path_slug=collection_path_slug or None,
                shopping_collection=shopping_collection or None,
                master_sku=master_sku,
                alias_codes=alias_codes or None,
                sort_order=sort_index,
                is_active=True,
                raw_payload=item,
            )
            session.add(child)
            changed_master_skus.add(master_sku)
            created_assignments += 1
            sort_index += 1

    session.flush()
    result = list_manual_taxonomy_assignments(session)
    result["saved_rule_count"] = created_rules
    result["saved_assignment_count"] = created_assignments
    result["skipped_inactive_or_unknown_count"] = skipped_inactive_or_unknown
    result["skipped_duplicate_count"] = skipped_duplicate
    result["changed_master_skus"] = sorted(changed_master_skus)
    return result


def resolve_manual_taxonomy_overrides(
    session: Session,
    *,
    master_skus: List[str],
) -> Dict[str, Dict[str, Any]]:
    wanted = sorted({str(item or "").strip().upper() for item in master_skus if str(item or "").strip()})
    if not wanted:
        return {}
    stmt = (
        select(ManualTaxonomyAssignment, ManualTaxonomyAssignmentCollection)
        .join(
            ManualTaxonomyAssignmentCollection,
            ManualTaxonomyAssignmentCollection.assignment_id == ManualTaxonomyAssignment.id,
        )
        .where(ManualTaxonomyAssignment.assignment_status == "active")
        .where(ManualTaxonomyAssignmentCollection.is_active.is_(True))
        .where(ManualTaxonomyAssignmentCollection.master_sku.in_(wanted))
    )
    rows = session.execute(stmt).all()
    overrides: Dict[str, Dict[str, Any]] = {}
    for assignment, child in rows:
        master_sku = str(child.master_sku or "").strip().upper()
        if not master_sku:
            continue
        current = overrides.get(master_sku)
        candidate_updated = _normalize_timestamp(child.updated_at) or _normalize_timestamp(assignment.updated_at) or datetime.min.replace(tzinfo=timezone.utc)
        current_updated = current.get("_updated_at") if current else None
        if current and current_updated and current_updated >= candidate_updated:
            continue
        canonical_slug = normalize_plp_path(assignment.canonical_taxonomy_path_slug or "")
        bundle = _manual_assignment_path_bundle(canonical_slug, str(child.collection_path_slug or ""))
        parts = [part for part in canonical_slug.split("/") if part]
        category_l1 = _path_label_part(parts[0]) if parts else ""
        overrides[master_sku] = {
            "source_sku": str(assignment.source_sku or "").strip().upper(),
            "master_sku": master_sku,
            "canonical_taxonomy_path_slug": canonical_slug,
            "canonical_taxonomy_path_label": str(assignment.canonical_taxonomy_path_label or "").strip(),
            "category_l1": category_l1,
            "category_l2": str(assignment.l2_category or "").strip(),
            "category_l3": str(assignment.l3_category or "").strip(),
            "shopping_l1": str(assignment.shopping_l1 or "").strip(),
            "shopping_l2": str(assignment.shopping_l2 or "").strip(),
            "collection": str(child.collection_name or "").strip(),
            "collection_code": str(child.collection_code or "").strip().upper(),
            "collection_registry_id": child.collection_registry_id,
            "collection_path_slug": normalize_plp_path(child.collection_path_slug or ""),
            "shopping_collection": str(child.shopping_collection or "").strip(),
            "plp_path_slugs": bundle["plp_path_slugs"],
            "membership_path_slugs": bundle["membership_path_slugs"],
            "_updated_at": candidate_updated,
        }
    for item in overrides.values():
        item.pop("_updated_at", None)
    return overrides


def resolve_manual_taxonomy_import_override(
    session: Session,
    *,
    master_sku: str,
    source_sku: Optional[str] = None,
) -> Optional[Dict[str, Any]]:
    exact = resolve_manual_taxonomy_overrides(session, master_skus=[master_sku])
    exact_item = exact.get(str(master_sku or "").strip().upper())
    if exact_item:
        return {**exact_item, "match_scope": "exact"}

    normalized_master_sku = str(master_sku or "").strip().upper()
    candidate_source_skus = {
        str(source_sku or "").strip().upper(),
        normalized_master_sku.split("-", 1)[1].strip().upper() if "-" in normalized_master_sku else "",
    }
    candidate_source_skus = {item for item in candidate_source_skus if item}
    if not candidate_source_skus:
        return None

    stmt = (
        select(ManualTaxonomyAssignment, ManualTaxonomyAssignmentCollection)
        .join(
            ManualTaxonomyAssignmentCollection,
            ManualTaxonomyAssignmentCollection.assignment_id == ManualTaxonomyAssignment.id,
        )
        .where(ManualTaxonomyAssignment.assignment_status == "active")
        .where(ManualTaxonomyAssignmentCollection.is_active.is_(True))
        .where(ManualTaxonomyAssignment.source_sku.in_(sorted(candidate_source_skus)))
    )
    rows = session.execute(stmt).all()
    best: Optional[Dict[str, Any]] = None
    best_score = -1
    for assignment, child in rows:
        score = 0
        if str(assignment.source_sku or "").strip().upper() in candidate_source_skus:
            score += 10
        if score <= 0 or score < best_score:
            continue
        canonical_slug = normalize_plp_path(assignment.canonical_taxonomy_path_slug or "")
        bundle = _manual_assignment_path_bundle(canonical_slug, "")
        parts = [part for part in canonical_slug.split("/") if part]
        category_l1 = _path_label_part(parts[0]) if parts else ""
        best = {
            "source_sku": str(assignment.source_sku or "").strip().upper(),
            "master_sku": normalized_master_sku,
            "canonical_taxonomy_path_slug": canonical_slug,
            "canonical_taxonomy_path_label": str(assignment.canonical_taxonomy_path_label or "").strip(),
            "category_l1": category_l1,
            "category_l2": str(assignment.l2_category or "").strip(),
            "category_l3": str(assignment.l3_category or "").strip(),
            "shopping_l1": str(assignment.shopping_l1 or "").strip(),
            "shopping_l2": str(assignment.shopping_l2 or "").strip(),
            "collection": None,
            "collection_code": None,
            "collection_registry_id": None,
            "collection_path_slug": "",
            "shopping_collection": None,
            "plp_path_slugs": bundle["plp_path_slugs"],
            "membership_path_slugs": bundle["membership_path_slugs"],
            "match_scope": "source_sku",
        }
        best_score = score
    return best