from __future__ import annotations

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

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

from db.channel_sku_prefix_mapping import resolve_channel_sku_from_prefixes
from db.models import (
    ChannelSkuMapping,
    ChannelSkuPrefixMapping,
    MasterProduct,
    MasterProductImage,
    MasterProductRelation,
    ProductChannelAssignment,
)
from db.source_imports import normalize_column_key


CHANNELS = ("magento", "shopify")
ACTIVE = "active"


def build_catalog_operations_summary(session: Session) -> Dict[str, Any]:
    active_total = _count_active_products(session)
    prefix_connection_ids = _default_prefix_connection_ids(session)
    channels = {
        channel: _channel_summary(
            session,
            channel,
            connection_id=prefix_connection_ids.get(channel),
        )
        for channel in CHANNELS
    }
    image_summary = _image_summary(session, active_total)
    relation_summary = _relation_summary(session)
    families = _counts_by(session, MasterProduct.product_family)
    assembly = _counts_by(session, MasterProduct.assembly_type)
    return {
        "active_master_skus": active_total,
        "families": families,
        "assembly_types": assembly,
        "images": image_summary,
        "relations": relation_summary,
        "channels": channels,
        "prefix_connection_ids": prefix_connection_ids,
    }


def build_catalog_operations_registry(
    session: Session,
    *,
    channel: Optional[str] = None,
    q: Optional[str] = None,
    issue: Optional[str] = None,
    page: int = 1,
    page_size: int = 50,
) -> Dict[str, Any]:
    selected_channel = _channel_or_none(channel)
    page = max(int(page or 1), 1)
    page_size = max(1, min(int(page_size or 50), 200))

    product_rows = _active_products(session, q=q)
    product_ids = [p.id for p in product_rows]
    skus = [p.sku for p in product_rows]
    prefix_connection_ids = _default_prefix_connection_ids(session)

    assignments = _assignments_by_sku(session, skus)
    mappings = _mappings_by_sku(session, skus, connection_ids=prefix_connection_ids)
    image_counts = _image_counts_by_sku(session, product_ids)
    parent_counts = _parent_child_counts(session, skus)
    child_parents = _child_parent_map(session, skus)

    rows = []
    for product in product_rows:
        row = _registry_row(
            session,
            product,
            selected_channel=selected_channel,
            prefix_connection_ids=prefix_connection_ids,
            assignments=assignments.get(product.sku, set()),
            mappings=mappings.get(product.sku, {}),
            image_count=image_counts.get(product.id, 0),
            child_count=parent_counts.get(product.sku, 0),
            parent_sku=child_parents.get(product.sku),
        )
        if issue and issue != "all" and issue not in row["issues"]:
            continue
        rows.append(row)

    total = len(rows)
    start = (page - 1) * page_size
    return {
        "page": page,
        "page_size": page_size,
        "total": total,
        "pages": (total + page_size - 1) // page_size if total else 0,
        "channel": selected_channel or "all",
        "issue": issue or "all",
        "rows": rows[start : start + page_size],
        "issue_counts": _issue_counts(rows),
        "prefix_connection_ids": prefix_connection_ids,
    }


def _registry_row(
    session: Session,
    product: MasterProduct,
    *,
    selected_channel: Optional[str],
    prefix_connection_ids: Dict[str, Optional[int]],
    assignments: Set[str],
    mappings: Dict[str, ChannelSkuMapping],
    image_count: int,
    child_count: int,
    parent_sku: Optional[str],
) -> Dict[str, Any]:
    channels = [selected_channel] if selected_channel else list(CHANNELS)
    channel_rows = {}
    issues: Set[str] = set()

    recommended = _recommended_channels(product)
    if image_count <= 0:
        issues.add("missing_image")
    if not product.name:
        issues.add("missing_name")
    if not product.product_family:
        issues.add("missing_family")
    if not product.assembly_type:
        issues.add("assembly_unknown")

    for channel in channels:
        mapping = mappings.get(channel)
        assigned = channel in assignments
        prefix_connection_id = prefix_connection_ids.get(channel)
        target_sku = resolve_channel_sku_from_prefixes(
            session,
            product.sku,
            channel,
            connection_id=prefix_connection_id,
        )
        current_sku = mapping.channel_sku if mapping else target_sku
        mapped = bool(mapping)
        rename_needed = bool(mapping and target_sku and mapping.channel_sku != target_sku)
        eligible = channel in recommended

        if assigned and not mapped:
            issues.add(f"{channel}_unmapped")
            issues.add("unmapped")
        if rename_needed:
            issues.add(f"{channel}_rename_needed")
            issues.add("rename_needed")
        if assigned and not eligible:
            issues.add(f"{channel}_eligibility_review")
            issues.add("eligibility_review")
        if eligible and not assigned:
            issues.add(f"{channel}_unassigned")

        channel_rows[channel] = {
            "assigned": assigned,
            "eligible": eligible,
            "mapped": mapped,
            "channel_sku": current_sku,
            "target_sku": target_sku,
            "rename_needed": rename_needed,
            "match_source": mapping.match_source if mapping else "prefix_or_identity",
            "remote_id": mapping.remote_id if mapping else None,
            "connection_id": mapping.connection_id if mapping else prefix_connection_id,
            "prefix_connection_id": prefix_connection_id,
        }

    role = "parent" if child_count else "child" if parent_sku else "standalone"
    if selected_channel == "magento" and role == "standalone" and product.product_family == "Cabinets":
        issues.add("variation_review")

    return {
        "sku": product.sku,
        "name": product.name,
        "family": product.product_family,
        "collection": product.collection,
        "assembly_type": product.assembly_type,
        "status": product.status,
        "recommended_channels": sorted(recommended),
        "channels": channel_rows,
        "image_count": image_count,
        "relation_role": role,
        "child_count": child_count,
        "parent_sku": parent_sku,
        "issues": sorted(issues),
    }


def _recommended_channels(product: MasterProduct) -> Set[str]:
    assembly = normalize_column_key(product.assembly_type or "")
    family = normalize_column_key(product.product_family or "")
    out = {"shopify"}
    if assembly == "assembled":
        out.add("magento")
    if family in {"accessories", "accessory"} and assembly in {"", "assembled"}:
        out.add("magento")
    return out


def _channel_summary(
    session: Session,
    channel: str,
    *,
    connection_id: Optional[int] = None,
) -> Dict[str, Any]:
    assigned = {
        sku
        for (sku,) in session.execute(
            select(ProductChannelAssignment.sku)
            .where(ProductChannelAssignment.channel_code == channel)
            .where(ProductChannelAssignment.assignment_status == ACTIVE)
        ).all()
    }
    mapped_rows = session.scalars(
        select(ChannelSkuMapping)
        .where(ChannelSkuMapping.channel_code == channel)
        .where(ChannelSkuMapping.mapping_status == ACTIVE)
    ).all()
    mapped = {row.master_sku for row in mapped_rows}
    rename_needed = 0
    for row in mapped_rows:
        target = resolve_channel_sku_from_prefixes(
            session,
            row.master_sku,
            channel,
            connection_id=row.connection_id or connection_id,
        )
        if target and target != row.channel_sku:
            rename_needed += 1
    prefix_rules = session.scalar(
        select(func.count())
        .select_from(ChannelSkuPrefixMapping)
        .where(ChannelSkuPrefixMapping.channel_code == channel)
        .where(ChannelSkuPrefixMapping.mapping_status == ACTIVE)
    ) or 0
    return {
        "assigned": len(assigned),
        "mapped": len(mapped),
        "assigned_unmapped": len(assigned - mapped),
        "mapped_unassigned": len(mapped - assigned),
        "rename_needed": rename_needed,
        "prefix_rules": int(prefix_rules),
        "prefix_connection_id": connection_id,
    }


def _image_summary(session: Session, active_total: int) -> Dict[str, int]:
    skus_with_images = session.scalar(select(func.count(func.distinct(MasterProductImage.sku)))) or 0
    total_images = session.scalar(select(func.count()).select_from(MasterProductImage)) or 0
    return {
        "total_images": int(total_images),
        "skus_with_images": int(skus_with_images),
        "skus_without_images": max(0, int(active_total) - int(skus_with_images)),
    }


def _relation_summary(session: Session) -> Dict[str, int]:
    parent_count = session.scalar(select(func.count(func.distinct(MasterProductRelation.parent_sku)))) or 0
    child_count = session.scalar(select(func.count(func.distinct(MasterProductRelation.child_sku)))) or 0
    link_count = session.scalar(select(func.count()).select_from(MasterProductRelation)) or 0
    return {"parents": int(parent_count), "children": int(child_count), "links": int(link_count)}


def _active_products(session: Session, *, q: Optional[str]) -> List[MasterProduct]:
    stmt = select(MasterProduct).where(MasterProduct.is_active.is_(True))
    text = str(q or "").strip()
    if text:
        like = f"%{text}%"
        stmt = stmt.where(
            sa.or_(
                MasterProduct.sku.ilike(like),
                MasterProduct.name.ilike(like),
                MasterProduct.collection.ilike(like),
                MasterProduct.product_family.ilike(like),
            )
        )
    return list(session.scalars(stmt.order_by(MasterProduct.sku)).all())


def _assignments_by_sku(session: Session, skus: Sequence[str]) -> Dict[str, Set[str]]:
    grouped: Dict[str, Set[str]] = {}
    if not skus:
        return grouped
    for sku, channel in session.execute(
        select(ProductChannelAssignment.sku, ProductChannelAssignment.channel_code)
        .where(ProductChannelAssignment.sku.in_(list(skus)))
        .where(ProductChannelAssignment.assignment_status == ACTIVE)
    ).all():
        grouped.setdefault(sku, set()).add(channel)
    return grouped


def _mappings_by_sku(
    session: Session,
    skus: Sequence[str],
    *,
    connection_ids: Dict[str, Optional[int]],
) -> Dict[str, Dict[str, ChannelSkuMapping]]:
    grouped: Dict[str, Dict[str, ChannelSkuMapping]] = {}
    if not skus:
        return grouped
    for row in session.scalars(
        select(ChannelSkuMapping)
        .where(ChannelSkuMapping.master_sku.in_(list(skus)))
        .where(ChannelSkuMapping.mapping_status == ACTIVE)
    ).all():
        current = grouped.setdefault(row.master_sku, {}).get(row.channel_code)
        if current is None or _mapping_priority(row, connection_ids) < _mapping_priority(current, connection_ids):
            grouped.setdefault(row.master_sku, {})[row.channel_code] = row
    return grouped


def _mapping_priority(row: ChannelSkuMapping, connection_ids: Dict[str, Optional[int]]) -> int:
    preferred = connection_ids.get(row.channel_code)
    if preferred is not None and row.connection_id == preferred:
        return 0
    if row.connection_id is None:
        return 1
    return 2


def _default_prefix_connection_ids(session: Session) -> Dict[str, Optional[int]]:
    out: Dict[str, Optional[int]] = {}
    for channel in CHANNELS:
        out[channel] = session.scalar(
            select(ChannelSkuPrefixMapping.connection_id)
            .where(ChannelSkuPrefixMapping.channel_code == channel)
            .where(ChannelSkuPrefixMapping.mapping_status == ACTIVE)
            .where(ChannelSkuPrefixMapping.connection_id.is_not(None))
            .group_by(ChannelSkuPrefixMapping.connection_id)
            .order_by(func.count().desc(), ChannelSkuPrefixMapping.connection_id)
            .limit(1)
        )
    return out


def _image_counts_by_sku(session: Session, product_ids: Sequence[int]) -> Dict[int, int]:
    if not product_ids:
        return {}
    return {
        int(product_id): int(count)
        for product_id, count in session.execute(
            select(MasterProductImage.product_id, func.count())
            .where(MasterProductImage.product_id.in_(list(product_ids)))
            .group_by(MasterProductImage.product_id)
        ).all()
    }


def _parent_child_counts(session: Session, skus: Iterable[str]) -> Dict[str, int]:
    sku_list = list(skus)
    if not sku_list:
        return {}
    return {
        parent: int(count)
        for parent, count in session.execute(
            select(MasterProductRelation.parent_sku, func.count())
            .where(MasterProductRelation.parent_sku.in_(sku_list))
            .group_by(MasterProductRelation.parent_sku)
        ).all()
    }


def _child_parent_map(session: Session, skus: Iterable[str]) -> Dict[str, str]:
    sku_list = list(skus)
    if not sku_list:
        return {}
    return {
        child: parent
        for child, parent in session.execute(
            select(MasterProductRelation.child_sku, MasterProductRelation.parent_sku)
            .where(MasterProductRelation.child_sku.in_(sku_list))
        ).all()
    }


def _counts_by(session: Session, column: Any) -> Dict[str, int]:
    return {
        str(key or "(blank)"): int(count)
        for key, count in session.execute(
            select(column, func.count())
            .where(MasterProduct.is_active.is_(True))
            .group_by(column)
            .order_by(func.count().desc())
        ).all()
    }


def _count_active_products(session: Session) -> int:
    return int(
        session.scalar(
            select(func.count()).select_from(MasterProduct).where(MasterProduct.is_active.is_(True))
        )
        or 0
    )


def _issue_counts(rows: List[Dict[str, Any]]) -> Dict[str, int]:
    counts: Dict[str, int] = {}
    for row in rows:
        for issue in row.get("issues") or []:
            counts[issue] = counts.get(issue, 0) + 1
    return dict(sorted(counts.items(), key=lambda item: (-item[1], item[0])))


def _channel_or_none(channel: Optional[str]) -> Optional[str]:
    text = normalize_column_key(channel or "")
    return text if text in CHANNELS else None