from __future__ import annotations

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

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

from db.channel_exports import resolve_pipeline_channel_code
from db.channel_projection_cache import mark_projection_rows_stale
from db.models import (
    ChannelAttributeAlias,
    ChannelProjectionRow,
    ChannelProjectionRun,
    ChannelPublishState,
    ChannelSkuPrefixMapping,
    ChannelTaxonomyMapping,
    MasterProduct,
    MasterProductOverride,
    ProductChannelTaxonomyAssignment,
)


def invalidate_skus(
    session: Session,
    *,
    channel_code: str,
    connection_id: Optional[int] = None,
    skus: Sequence[str],
    reason: Optional[str] = None,
) -> Dict[str, Any]:
    channel = resolve_pipeline_channel_code(channel_code)
    wanted = sorted({str(s).strip() for s in skus if str(s).strip()})
    if not wanted:
        return {"channel_code": channel, "connection_id": connection_id, "invalidated": 0, "skus": []}
    count = mark_projection_rows_stale(
        session,
        channel_code=channel,
        connection_id=connection_id,
        skus=wanted,
        reason=reason or "sku_invalidate",
    )
    return {
        "channel_code": channel,
        "connection_id": connection_id,
        "invalidated": count,
        "skus": wanted,
        "reason": reason,
    }


def invalidate_connection(
    session: Session,
    *,
    channel_code: str,
    connection_id: Optional[int] = None,
    reason: Optional[str] = None,
) -> Dict[str, Any]:
    channel = resolve_pipeline_channel_code(channel_code)
    count = mark_projection_rows_stale(
        session,
        channel_code=channel,
        connection_id=connection_id,
        skus=None,
        reason=reason or "connection_invalidate",
    )
    return {
        "channel_code": channel,
        "connection_id": connection_id,
        "invalidated": count,
        "reason": reason,
    }


def inspect_projection_drift(
    session: Session,
    *,
    channel_code: str,
    connection_id: Optional[int] = None,
    skus: Optional[Iterable[str]] = None,
    apply: bool = False,
) -> Dict[str, Any]:
    """Inspect projection freshness signals and optionally mark drifted rows stale."""
    from db.compat_connections import resolve_native_connection_id

    channel = resolve_pipeline_channel_code(channel_code)
    if connection_id is not None and channel in {"shopify", "magento"}:
        connection_id = resolve_native_connection_id(channel, connection_id)
    wanted = sorted({str(s).strip() for s in (skus or []) if str(s).strip()})

    rows = _projection_rows(session, channel_code=channel, connection_id=connection_id, skus=wanted or None)
    if not rows:
        return {
            "channel_code": channel,
            "connection_id": connection_id,
            "row_count": 0,
            "drift_skus": [],
            "reasons": {},
            "connection_wide": [],
            "applied": False,
            "invalidated": 0,
        }

    row_skus = [row.master_sku for row in rows]
    reasons: Dict[str, List[str]] = {sku: [] for sku in row_skus}

    _collect_master_hash_drift(session, rows, reasons)
    _collect_override_drift(session, channel, rows, reasons)
    _collect_taxonomy_drift(session, channel, connection_id, rows, reasons)
    _collect_publish_failure_drift(session, channel, rows, reasons)

    connection_wide: List[str] = []
    newest_ready = _newest_ready_normalized_at(rows)
    if newest_ready is not None:
        if _aliases_newer_than(session, channel, newest_ready):
            connection_wide.append("channel_attribute_alias_updated")
        if _prefix_mappings_newer_than(session, channel, connection_id, newest_ready):
            connection_wide.append("channel_sku_prefix_mapping_updated")
        if _taxonomy_mappings_newer_than(session, channel, newest_ready):
            connection_wide.append("channel_taxonomy_mapping_updated")

    critical = _critical_attribute_gaps(session, channel, connection_id=connection_id)
    if critical:
        connection_wide.append("critical_attribute_mappings_incomplete")

    drift_skus: Set[str] = {sku for sku, items in reasons.items() if items}
    if connection_wide:
        drift_skus.update(row_skus)

    invalidated = 0
    applied = False
    if apply and (drift_skus or connection_wide):
        applied = True
        if connection_wide and not wanted:
            invalidated = invalidate_connection(
                session,
                channel_code=channel,
                connection_id=connection_id,
                reason=",".join(connection_wide),
            )["invalidated"]
        else:
            invalidated = invalidate_skus(
                session,
                channel_code=channel,
                connection_id=connection_id,
                skus=sorted(drift_skus),
                reason="inspect_projection_drift",
            )["invalidated"]

    return {
        "channel_code": channel,
        "connection_id": connection_id,
        "row_count": len(rows),
        "drift_skus": sorted(drift_skus),
        "drift_count": len(drift_skus),
        "reasons": {sku: reasons[sku] for sku in sorted(drift_skus) if reasons.get(sku)},
        "connection_wide": connection_wide,
        "critical_attribute_gaps": critical,
        "applied": applied,
        "invalidated": invalidated,
    }


def _projection_rows(
    session: Session,
    *,
    channel_code: str,
    connection_id: Optional[int],
    skus: Optional[Sequence[str]],
) -> List[ChannelProjectionRow]:
    stmt = select(ChannelProjectionRow).where(ChannelProjectionRow.channel_code == channel_code)
    if connection_id is None:
        stmt = stmt.where(ChannelProjectionRow.connection_id.is_(None))
    else:
        stmt = stmt.where(ChannelProjectionRow.connection_id == connection_id)
    if skus:
        stmt = stmt.where(ChannelProjectionRow.master_sku.in_(list(skus)))
    return list(session.scalars(stmt.order_by(ChannelProjectionRow.master_sku)).all())


def _collect_master_hash_drift(
    session: Session,
    rows: Sequence[ChannelProjectionRow],
    reasons: Dict[str, List[str]],
) -> None:
    skus = [row.master_sku for row in rows]
    hashes = {
        str(sku): str(row_hash or "")
        for sku, row_hash in session.execute(
            select(MasterProduct.sku, MasterProduct.row_hash).where(MasterProduct.sku.in_(skus))
        ).all()
    }
    for row in rows:
        master_hash = hashes.get(row.master_sku)
        if master_hash is None:
            reasons[row.master_sku].append("master_product_missing")
        elif str(row.source_row_hash or "") != master_hash:
            reasons[row.master_sku].append("master_row_hash_drift")


def _collect_override_drift(
    session: Session,
    channel: str,
    rows: Sequence[ChannelProjectionRow],
    reasons: Dict[str, List[str]],
) -> None:
    skus = [row.master_sku for row in rows]
    normalized_by_sku = {row.master_sku: row.normalized_at for row in rows}
    stmt = (
        select(MasterProductOverride.sku, func.max(MasterProductOverride.updated_at))
        .where(MasterProductOverride.sku.in_(skus))
        .where(MasterProductOverride.is_active.is_(True))
        .where(MasterProductOverride.channel_code.in_(["", channel]))
        .group_by(MasterProductOverride.sku)
    )
    for sku, updated_at in session.execute(stmt).all():
        normalized_at = normalized_by_sku.get(str(sku))
        if updated_at is not None and normalized_at is not None and updated_at > normalized_at:
            reasons[str(sku)].append("override_updated")


def _collect_taxonomy_drift(
    session: Session,
    channel: str,
    connection_id: Optional[int],
    rows: Sequence[ChannelProjectionRow],
    reasons: Dict[str, List[str]],
) -> None:
    skus = [row.master_sku for row in rows]
    normalized_by_sku = {row.master_sku: row.normalized_at for row in rows}
    stmt = select(ProductChannelTaxonomyAssignment).where(
        ProductChannelTaxonomyAssignment.master_sku.in_(skus),
        ProductChannelTaxonomyAssignment.channel_code == channel,
    )
    if connection_id is None:
        stmt = stmt.where(ProductChannelTaxonomyAssignment.connection_id.is_(None))
    else:
        stmt = stmt.where(
            (ProductChannelTaxonomyAssignment.connection_id == connection_id)
            | (ProductChannelTaxonomyAssignment.connection_id.is_(None))
        )
    for assignment in session.scalars(stmt).all():
        sku = assignment.master_sku
        if assignment.assignment_status != "active":
            reasons[sku].append("taxonomy_assignment_inactive")
            continue
        normalized_at = normalized_by_sku.get(sku)
        if (
            assignment.updated_at is not None
            and normalized_at is not None
            and assignment.updated_at > normalized_at
        ):
            reasons[sku].append("taxonomy_assignment_updated")


def _collect_publish_failure_drift(
    session: Session,
    channel: str,
    rows: Sequence[ChannelProjectionRow],
    reasons: Dict[str, List[str]],
) -> None:
    skus = [row.master_sku for row in rows]
    stmt = (
        select(ChannelPublishState)
        .where(ChannelPublishState.channel_code == channel)
        .where(ChannelPublishState.sku.in_(skus))
    )
    for state in session.scalars(stmt).all():
        status = str(state.last_status or "").lower()
        if status in {"error", "failed"} or state.last_error:
            reasons[state.sku].append("prior_publish_failure")


def _newest_ready_normalized_at(rows: Sequence[ChannelProjectionRow]) -> Optional[datetime]:
    stamps = [row.normalized_at for row in rows if row.normalized_at is not None and row.projection_status == "ready"]
    if not stamps:
        stamps = [row.normalized_at for row in rows if row.normalized_at is not None]
    return max(stamps) if stamps else None


def _aliases_newer_than(session: Session, channel: str, threshold: datetime) -> bool:
    newest = session.scalar(
        select(func.max(ChannelAttributeAlias.updated_at)).where(
            ChannelAttributeAlias.channel_code == channel,
            ChannelAttributeAlias.is_active.is_(True),
        )
    )
    return newest is not None and newest > threshold


def _prefix_mappings_newer_than(
    session: Session,
    channel: str,
    connection_id: Optional[int],
    threshold: datetime,
) -> bool:
    stmt = select(func.max(ChannelSkuPrefixMapping.updated_at)).where(
        ChannelSkuPrefixMapping.channel_code == channel,
        ChannelSkuPrefixMapping.mapping_status == "active",
    )
    if connection_id is not None:
        stmt = stmt.where(
            (ChannelSkuPrefixMapping.connection_id == connection_id)
            | (ChannelSkuPrefixMapping.connection_id.is_(None))
        )
    newest = session.scalar(stmt)
    return newest is not None and newest > threshold


def _taxonomy_mappings_newer_than(session: Session, channel: str, threshold: datetime) -> bool:
    newest = session.scalar(
        select(func.max(ChannelTaxonomyMapping.updated_at)).where(ChannelTaxonomyMapping.channel_code == channel)
    )
    return newest is not None and newest > threshold


def _critical_attribute_gaps(
    session: Session,
    channel: str,
    *,
    connection_id: Optional[int],
) -> List[str]:
    try:
        from db.channel_push_readiness import _critical_attribute_checks

        result = _critical_attribute_checks(
            session,
            channel,
            magento_connection_id=connection_id if channel == "magento" else None,
            shopify_connection_id=connection_id if channel == "shopify" else None,
        )
        return list(result.get("unmapped_critical") or [])
    except Exception:
        return []


def latest_projection_run(
    session: Session,
    *,
    channel_code: str,
    connection_id: Optional[int] = None,
) -> Optional[ChannelProjectionRun]:
    stmt = (
        select(ChannelProjectionRun)
        .where(ChannelProjectionRun.channel_code == channel_code)
        .order_by(ChannelProjectionRun.id.desc())
    )
    if connection_id is None:
        stmt = stmt.where(ChannelProjectionRun.connection_id.is_(None))
    else:
        stmt = stmt.where(ChannelProjectionRun.connection_id == connection_id)
    return session.scalars(stmt).first()