from __future__ import annotations

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

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

from db.channel_exports import CORE_CANONICAL_FIELDS
from db.master_catalog import _attribute_data_type, _attribute_purpose
from db.master_filter_virtual_attrs import virtual_master_filter_attributes
from db.models import MasterAttributeDefinition, MasterProduct, MasterProductAttributeValue
from db.source_imports import normalize_column_key


# master_product columns exposed as canonical attribute codes in the mapping UI
MASTER_PRODUCT_CORE_MAP: Dict[str, str] = {
    "name": "title",
    "brand": "brand",
    "manufacturer": "manufacturer",
    "product_family": "product_family",
    "category_l1": "category_l1",
    "category_l2": "category_l2",
    "category_l3": "category_l3",
    "collection": "collection",
    "assembly_type": "assembly_type",
    "item_size": "item_size",
    "item_style": "item_style",
    "status": "status",
}


def list_master_attributes(session: Session) -> List[Dict[str, Any]]:
    """All canonical master attribute codes for mapping UI (definitions + values + core)."""
    by_code: Dict[str, Dict[str, Any]] = {}

    for definition in session.scalars(
        select(MasterAttributeDefinition)
        .where(MasterAttributeDefinition.is_active.is_(True))
        .order_by(MasterAttributeDefinition.attribute_code)
    ).all():
        code = normalize_column_key(definition.attribute_code)
        if not code:
            continue
        by_code[code] = {
            "attribute_code": code,
            "label": definition.label or code,
            "purpose": definition.purpose or _attribute_purpose(code),
            "data_type": definition.data_type or _attribute_data_type(code),
            "source": "master_attribute_definition",
            "product_count": 0,
        }

    value_counts = {
        normalize_column_key(code): int(count or 0)
        for code, count in session.execute(
            select(MasterProductAttributeValue.attribute_code, func.count(distinct(MasterProductAttributeValue.product_id)))
            .group_by(MasterProductAttributeValue.attribute_code)
        ).all()
        if code
    }
    for code, count in value_counts.items():
        row = by_code.get(code)
        if row is None:
            by_code[code] = {
                "attribute_code": code,
                "label": code.replace("_", " ").title(),
                "purpose": _attribute_purpose(code),
                "data_type": _attribute_data_type(code),
                "source": "master_product_attribute_value",
                "product_count": count,
            }
        else:
            row["product_count"] = count
            if row["source"] == "master_attribute_definition":
                row["source"] = "definition+values"

    for column, canonical in MASTER_PRODUCT_CORE_MAP.items():
        if canonical in by_code:
            by_code[canonical]["source"] = "master_product_core"
            continue
        by_code[canonical] = {
            "attribute_code": canonical,
            "label": canonical.replace("_", " ").title(),
            "purpose": _attribute_purpose(canonical),
            "data_type": _attribute_data_type(canonical),
            "source": "master_product_core",
            "product_count": session.scalar(
                select(func.count()).select_from(MasterProduct).where(MasterProduct.is_active.is_(True))
            )
            or 0,
        }

    for code in CORE_CANONICAL_FIELDS:
        code = normalize_column_key(code)
        if code not in by_code:
            by_code[code] = {
                "attribute_code": code,
                "label": code.replace("_", " ").title(),
                "purpose": _attribute_purpose(code),
                "data_type": _attribute_data_type(code),
                "source": "core_default",
                "product_count": 0,
            }

    for row in virtual_master_filter_attributes():
        by_code[row["attribute_code"]] = row

    return sorted(by_code.values(), key=lambda row: (row["purpose"], row["attribute_code"]))


def export_master_attributes_csv_rows(session: Session) -> List[Dict[str, Any]]:
    """CSV rows for manual mapping reference (left column / master codes)."""
    return list_master_attributes(session)


def search_master_skus(
    session: Session,
    *,
    query: Optional[str] = None,
    limit: int = 25,
) -> List[str]:
    stmt = select(MasterProduct.sku).where(MasterProduct.is_active.is_(True)).order_by(MasterProduct.sku)
    if query:
        q = f"%{str(query).strip()}%"
        stmt = stmt.where(MasterProduct.sku.ilike(q))
    if limit:
        stmt = stmt.limit(limit)
    return [sku for (sku,) in session.execute(stmt).all()]