from __future__ import annotations

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

from sqlalchemy import select
from sqlalchemy.orm import Session

from db.channel_assignments import active_skus_for_channel, remove_skus
from db.channel_exports import resolve_pipeline_channel_code
from db.channel_sku_mapping import reconcile_connection_id_for_channel
from db.models import ChannelSkuMapping, MagentoCatalogState, ProductSnapshot


ACTIVE_STATUS = "active"
INACTIVE_STATUS = "inactive"
IDENTITY_MATCH_SOURCES = frozenset({"auto_exact", "identity bootstrap"})


def remote_sku_set_from_db(
    session: Session,
    channel_code: str,
    *,
    connection_id: Optional[int] = None,
) -> Set[str]:
    """Channel SKUs present in pulled DB catalogs (no live Shopify API)."""
    channel = resolve_pipeline_channel_code(channel_code)
    if channel == "magento":
        if not connection_id:
            return set()
        return {
            str(sku).strip()
            for (sku,) in session.execute(
                select(MagentoCatalogState.sku).where(MagentoCatalogState.connection_id == connection_id)
            ).all()
            if str(sku).strip()
        }
    if channel == "plytix":
        return {
            str(sku).strip()
            for (sku,) in session.execute(
                select(ProductSnapshot.sku).where(ProductSnapshot.valid_to.is_(None))
            ).all()
            if str(sku).strip()
        }
    return set()


def _mapping_scope_filter(stmt, connection_id: Optional[int]):
    if connection_id is not None:
        return stmt.where(
            (ChannelSkuMapping.connection_id == connection_id)
            | (ChannelSkuMapping.connection_id.is_(None))
        )
    return stmt.where(ChannelSkuMapping.connection_id.is_(None))


def masters_with_verified_remote_link(
    session: Session,
    channel_code: str,
    *,
    connection_id: Optional[int] = None,
) -> Set[str]:
    """Master SKUs with an active mapping whose channel SKU exists on the remote catalog."""
    channel = resolve_pipeline_channel_code(channel_code)
    catalog_conn_id = connection_id if channel == "magento" else None
    remote_skus = remote_sku_set_from_db(session, channel, connection_id=catalog_conn_id)

    stmt = (
        select(ChannelSkuMapping.master_sku, ChannelSkuMapping.channel_sku, ChannelSkuMapping.remote_id)
        .where(ChannelSkuMapping.channel_code == channel)
        .where(ChannelSkuMapping.mapping_status == ACTIVE_STATUS)
    )
    stmt = _mapping_scope_filter(stmt, connection_id)

    matched: Set[str] = set()
    for master_sku, channel_sku, remote_id in session.execute(stmt).all():
        master = str(master_sku or "").strip()
        if not master:
            continue
        channel_sku_text = str(channel_sku or "").strip()
        if channel_sku_text and channel_sku_text in remote_skus:
            matched.add(master)
            continue
        if str(remote_id or "").strip():
            matched.add(master)
    return matched


def bogus_identity_mappings(
    session: Session,
    channel_code: str,
    *,
    connection_id: Optional[int] = None,
) -> List[ChannelSkuMapping]:
    """Active 1:1 identity mappings whose channel SKU is not on the pulled remote catalog."""
    channel = resolve_pipeline_channel_code(channel_code)
    catalog_conn_id = connection_id if channel == "magento" else None
    remote_skus = remote_sku_set_from_db(session, channel, connection_id=catalog_conn_id)

    stmt = select(ChannelSkuMapping).where(
        ChannelSkuMapping.channel_code == channel,
        ChannelSkuMapping.mapping_status == ACTIVE_STATUS,
    )
    stmt = _mapping_scope_filter(stmt, connection_id)

    bogus: List[ChannelSkuMapping] = []
    for row in session.scalars(stmt).all():
        match_source = str(row.match_source or "").strip()
        notes = str(row.notes or "").strip().lower()
        is_identity = match_source in IDENTITY_MATCH_SOURCES or notes == "identity bootstrap"
        if not is_identity:
            continue
        channel_sku = str(row.channel_sku or "").strip()
        if channel_sku in remote_skus or str(row.remote_id or "").strip():
            continue
        bogus.append(row)
    return bogus


def prune_unmatched_channel_assignments(
    session: Session,
    channel_code: str,
    *,
    connection_id: Optional[int] = None,
    native_connection_id: Optional[int] = None,
    compat_connection_id: Optional[int] = None,
    prune_bogus_mappings: bool = False,
    dry_run: bool = True,
    reason: str = "prune_unmatched_assignments",
) -> Dict[str, Any]:
    """Remove channel assignments for masters not linked to the pulled remote catalog."""
    channel = resolve_pipeline_channel_code(channel_code)
    reconcile_conn_id = connection_id or reconcile_connection_id_for_channel(
        channel,
        compat_connection_id=compat_connection_id,
        native_connection_id=native_connection_id,
    )
    catalog_conn_id = native_connection_id if channel == "magento" else None

    assigned = active_skus_for_channel(session, channel)
    matched = masters_with_verified_remote_link(
        session,
        channel,
        connection_id=reconcile_conn_id,
    )
    to_remove = sorted(assigned - matched)
    to_keep = sorted(assigned & matched)

    bogus_rows: List[ChannelSkuMapping] = []
    if prune_bogus_mappings:
        bogus_rows = bogus_identity_mappings(
            session,
            channel,
            connection_id=reconcile_conn_id,
        )

    result: Dict[str, Any] = {
        "channel_code": channel,
        "connection_id": reconcile_conn_id,
        "catalog_connection_id": catalog_conn_id,
        "remote_catalog_sku_count": len(
            remote_sku_set_from_db(session, channel, connection_id=catalog_conn_id)
        ),
        "assigned_before": len(assigned),
        "verified_linked": len(to_keep),
        "would_remove": len(to_remove),
        "removed": 0,
        "samples_keep": to_keep[:25],
        "samples_remove": to_remove[:25],
        "bogus_identity_mappings": len(bogus_rows),
        "bogus_mappings_deactivated": 0,
        "dry_run": dry_run,
    }

    if dry_run:
        return result

    if to_remove:
        remove_stats = remove_skus(session, skus=to_remove, channel_code=channel, reason=reason)
        result["removed"] = remove_stats.get("upserted", 0)
        result["remove_stats"] = remove_stats

    if bogus_rows:
        for row in bogus_rows:
            row.mapping_status = INACTIVE_STATUS
            row.notes = (row.notes or "").strip() or "deactivated: identity mapping not on remote catalog"
        result["bogus_mappings_deactivated"] = len(bogus_rows)

    return result


def prune_channels(
    session: Session,
    *,
    channels: Iterable[str],
    magento_connection_id: Optional[int] = None,
    shopify_connection_id: Optional[int] = None,
    prune_bogus_mappings: bool = False,
    dry_run: bool = True,
) -> Dict[str, Any]:
    from db.channel_sku_link import _active_connections

    connections = _active_connections(session)
    summary: Dict[str, Any] = {
        "status": "ok",
        "dry_run": dry_run,
        "prune_bogus_mappings": prune_bogus_mappings,
        "channels": {},
    }

    for channel in channels:
        channel_code = resolve_pipeline_channel_code(channel)
        conn = connections.get(channel_code, {})
        native_id = magento_connection_id if channel_code == "magento" else None
        if channel_code == "magento" and native_id is None:
            native_id = conn.get("native_id")
        compat_id = shopify_connection_id if channel_code == "shopify" else None
        if channel_code == "shopify" and compat_id is None:
            compat_id = conn.get("connection_id")
        if channel_code == "plytix":
            compat_id = conn.get("connection_id")

        try:
            with session.begin_nested():
                summary["channels"][channel_code] = prune_unmatched_channel_assignments(
                    session,
                    channel_code,
                    native_connection_id=native_id,
                    compat_connection_id=compat_id,
                    prune_bogus_mappings=prune_bogus_mappings,
                    dry_run=dry_run,
                )
        except Exception as exc:
            summary["status"] = "partial"
            summary["channels"][channel_code] = {"status": "failed", "error": str(exc)}

    return summary