"""Read-only diagnostics for Magento SKU re-push / duplicate-risk investigation.

Dependency-inverted over SQLAlchemy session + existing mapping/assignment helpers.
Does not call Magento; pairs with optional live GET in the API layer.
"""

from __future__ import annotations

from typing import Any, Dict, List, Optional

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

from db.channel_sku_mapping import channel_sku_for_master, master_sku_for_channel_sku
from db.channel_sku_prefix_mapping import RTA_ASSEMBLY_PREFIX
from db.models import (
    MagentoCatalogState,
    MagentoSyncState,
    MasterProduct,
    MasterProductImage,
    ProductChannelAssignment,
)


def _peel_rta(sku: str) -> tuple[bool, str]:
    text = str(sku or "").strip()
    prefix_len = len(RTA_ASSEMBLY_PREFIX)
    if len(text) > prefix_len and text.upper().startswith(RTA_ASSEMBLY_PREFIX):
        return True, text[prefix_len:]
    return False, text


def _state_row(session: Session, connection_id: int, sku: str) -> Optional[Dict[str, Any]]:
    row = session.scalar(
        select(MagentoSyncState)
        .where(MagentoSyncState.connection_id == connection_id)
        .where(MagentoSyncState.sku == sku)
    )
    if row is None:
        return None
    return {
        "sku": row.sku,
        "data_hash": row.data_hash,
        "images_hash": row.images_hash,
        "relations_hash": row.relations_hash,
        "associations_hash": getattr(row, "associations_hash", None),
        "last_pushed_at": row.last_pushed_at.isoformat() if row.last_pushed_at else None,
        "last_error": row.last_error,
        "updated_at": row.updated_at.isoformat() if row.updated_at else None,
    }


def _catalog_row(session: Session, connection_id: int, sku: str) -> Optional[Dict[str, Any]]:
    row = session.scalar(
        select(MagentoCatalogState)
        .where(MagentoCatalogState.connection_id == connection_id)
        .where(MagentoCatalogState.sku == sku)
    )
    if row is None:
        return None
    return {
        "sku": row.sku,
        "magento_product_id": row.magento_product_id,
        "price": float(row.price) if row.price is not None else None,
        "media_hash": row.media_hash,
        "last_pulled_at": row.last_pulled_at.isoformat() if row.last_pulled_at else None,
    }


def _master_summary(session: Session, sku: str) -> Optional[Dict[str, Any]]:
    row = session.scalar(select(MasterProduct).where(MasterProduct.sku == sku))
    if row is None:
        return None
    image_count = session.scalar(
        select(func.count())
        .select_from(MasterProductImage)
        .where(MasterProductImage.sku == sku)
    ) or 0
    return {
        "sku": row.sku,
        "name": row.name,
        "assembly_type": row.assembly_type,
        "status": row.status,
        "is_active": row.is_active,
        "image_count": int(image_count),
    }


def _assignment(session: Session, sku: str, channel_code: str = "magento") -> Optional[Dict[str, Any]]:
    row = session.scalar(
        select(ProductChannelAssignment)
        .where(ProductChannelAssignment.sku == sku)
        .where(ProductChannelAssignment.channel_code == channel_code)
    )
    if row is None:
        return None
    return {
        "sku": row.sku,
        "channel_code": row.channel_code,
        "assignment_status": row.assignment_status,
        "reason": row.reason,
    }


def diagnose_magento_sku_push(
    session: Session,
    connection_id: int,
    sku: str,
    *,
    channel_code: str = "magento",
) -> Dict[str, Any]:
    """Explain sync-state / catalog-state / RTA mapping for a SKU that keeps pushing."""
    input_sku = str(sku or "").strip()
    if not input_sku:
        return {"error": "sku required"}

    is_rta, assembled_inner = _peel_rta(input_sku)
    rta_mirror = input_sku if is_rta else f"{RTA_ASSEMBLY_PREFIX}{input_sku}"
    assembled_sku = assembled_inner if is_rta else input_sku

    master_from_channel = master_sku_for_channel_sku(
        session, input_sku, channel_code, connection_id=connection_id
    )
    master_sku = master_from_channel or input_sku
    channel_sku = channel_sku_for_master(
        session, master_sku, channel_code, connection_id=connection_id
    )

    keys: List[str] = []
    for candidate in (input_sku, master_sku, channel_sku, assembled_sku, rta_mirror):
        text = str(candidate or "").strip()
        if text and text not in keys:
            keys.append(text)

    sync_states = {k: _state_row(session, connection_id, k) for k in keys}
    catalog_states = {k: _catalog_row(session, connection_id, k) for k in keys}

    planner_key = channel_sku or input_sku
    sync_for_planner = sync_states.get(planner_key)
    catalog_for_planner = catalog_states.get(planner_key)
    master_for_planner = _master_summary(session, master_sku)
    assignment = _assignment(session, master_sku, channel_code) or _assignment(
        session, input_sku, channel_code
    )

    risks: List[str] = []
    if sync_for_planner is None:
        risks.append("missing_sync_state_for_channel_sku")
        if master_sku != planner_key and sync_states.get(master_sku):
            risks.append("sync_state_keyed_by_master_sku_not_channel_sku")
    if catalog_for_planner is None or not catalog_for_planner.get("magento_product_id"):
        risks.append("missing_catalog_state_product_id")
        if master_for_planner and int(master_for_planner.get("image_count") or 0) > 0:
            risks.append("media_will_force_product_upsert")
    if is_rta:
        risks.append("is_rta_mirror_sku")
    if master_sku != channel_sku:
        risks.append("master_channel_sku_differ")
    if assignment is None:
        risks.append("not_assigned_to_channel")
    elif str(assignment.get("assignment_status") or "").lower() != "active":
        risks.append("assignment_not_active")

    recommendations: List[str] = []
    if "sync_state_keyed_by_master_sku_not_channel_sku" in risks:
        recommendations.append(
            "POST /api/magento/sync-state/align with dry_run=true then dry_run=false"
        )
    if "missing_catalog_state_product_id" in risks:
        recommendations.append(
            "Run Magento pull for this connection so magento_catalog_state gets magento_product_id"
        )
    if "is_rta_mirror_sku" in risks:
        recommendations.append(
            "RTA SKUs are intentional separate catalog products; unassign from Magento if they should not publish"
        )
    if not recommendations and not risks:
        recommendations.append(
            "State looks aligned; check recent data_hash churn (depth/attrs/price) or force_upsert jobs"
        )

    return {
        "connection_id": connection_id,
        "channel_code": channel_code,
        "input_sku": input_sku,
        "is_rta_mirror": is_rta,
        "assembled_sku": assembled_sku,
        "rta_mirror_sku": rta_mirror,
        "master_sku": master_sku,
        "channel_sku": channel_sku,
        "planner_sku": planner_key,
        "master_product": master_for_planner,
        "assembled_master_product": _master_summary(session, assembled_sku),
        "rta_master_product": _master_summary(session, rta_mirror),
        "assignment": assignment,
        "sync_states": sync_states,
        "catalog_states": catalog_states,
        "risks": risks,
        "recommendations": recommendations,
    }