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_exports import resolve_pipeline_channel_code
from db.compat_connections import decode_compat_connection_id, pipeline_channel_for_connection
from db.models import AttributeDef, MagentoAttributeRegistry, ShopifyAttributeRegistry, ShopifyConnection


SHOPIFY_VIRTUAL_PRODUCT_FIELDS = {
    "sku",
    "title",
    "description",
    "description_html",
    "body_html",
    "vendor",
    "product_type",
    "handle",
    "status",
    "tags",
    "seo_title",
    "seo_description",
    # Legacy static codes still accepted by productSet mapping.
    "meta_title",
    "meta_description",
    "short_description",
    # Variant price in productSet — not a pulled metafield/registry row.
    "price",
    # ProductSetInput can write this typed metafield even before a definition is pulled.
    "custom_short_description",
}


def channel_attribute_options(
    session: Session,
    channel_code: str,
    *,
    connection_id: Optional[int] = None,
) -> List[Dict[str, Any]]:
    """Attribute/field codes available on a channel for mapping dropdowns."""
    channel = resolve_pipeline_channel_code(channel_code)
    if connection_id:
        channel = pipeline_channel_for_connection(session, connection_id) or channel

    if channel == "magento":
        return _magento_options(session, connection_id)
    if channel == "shopify":
        return _shopify_options(session, connection_id)
    if channel == "plytix":
        return _plytix_options(session)
    return []


def channel_registry_codes_for(
    session: Session,
    channel_code: str,
    codes: Iterable[str],
    *,
    connection_id: Optional[int] = None,
) -> Set[str]:
    """Return which of the requested codes exist in a channel attribute registry."""
    channel = resolve_pipeline_channel_code(channel_code)
    if connection_id:
        channel = pipeline_channel_for_connection(session, connection_id) or channel
    wanted = {str(code or "").strip() for code in codes if str(code or "").strip()}
    if not wanted:
        return set()

    if channel == "magento":
        native_id = None
        if connection_id:
            channel_type, native_id = decode_compat_connection_id(connection_id)
            if channel_type != "magento":
                native_id = None
        stmt = select(MagentoAttributeRegistry.attribute_code).where(
            MagentoAttributeRegistry.attribute_code.in_(sorted(wanted))
        )
        if native_id:
            stmt = stmt.where(MagentoAttributeRegistry.connection_id == native_id)
        return {str(code).strip() for code in session.scalars(stmt).all() if str(code).strip()}

    if channel == "shopify":
        shop_code = None
        if connection_id:
            channel_type, native_id = decode_compat_connection_id(connection_id)
            if channel_type == "shopify":
                shop_code = session.scalar(
                    select(ShopifyConnection.shop_code).where(ShopifyConnection.id == native_id)
                )
        stmt = select(ShopifyAttributeRegistry.attribute_code).where(
            ShopifyAttributeRegistry.attribute_code.in_(sorted(wanted))
        )
        if shop_code:
            stmt = stmt.where(ShopifyAttributeRegistry.shop_code == shop_code)
        found = {str(code).strip() for code in session.scalars(stmt).all() if str(code).strip()}
        return found | (wanted & SHOPIFY_VIRTUAL_PRODUCT_FIELDS)

    if channel == "plytix":
        return {
            str(code).strip()
            for code in session.scalars(
                select(AttributeDef.attribute_code).where(AttributeDef.attribute_code.in_(sorted(wanted)))
            ).all()
            if str(code).strip()
        }
    return set()


def channel_attribute_options_bundle(
    session: Session,
    *,
    magento_connection_id: Optional[int] = None,
    shopify_connection_id: Optional[int] = None,
) -> Dict[str, List[Dict[str, Any]]]:
    return {
        "magento": channel_attribute_options(session, "magento", connection_id=magento_connection_id),
        "shopify": channel_attribute_options(session, "shopify", connection_id=shopify_connection_id),
        "plytix": channel_attribute_options(session, "plytix"),
    }


def _magento_options(session: Session, connection_id: Optional[int]) -> List[Dict[str, Any]]:
    native_id = None
    if connection_id:
        channel_type, native_id = decode_compat_connection_id(connection_id)
        if channel_type != "magento":
            native_id = None
    stmt = select(MagentoAttributeRegistry).order_by(MagentoAttributeRegistry.attribute_code)
    if native_id:
        stmt = stmt.where(MagentoAttributeRegistry.connection_id == native_id)
    seen: set[str] = set()
    options: List[Dict[str, Any]] = []
    for row in session.scalars(stmt).all():
        code = str(row.attribute_code or "").strip()
        if not code or code in seen:
            continue
        seen.add(code)
        label = row.frontend_label or code
        field_group = "custom" if row.is_user_defined else "core"
        options.append(
            {
                "code": code,
                "label": label,
                "display_label": f"{label} ({field_group})",
                "field_group": field_group,
                "data_type": row.backend_type or "",
                "attribute_id": row.attribute_id,
                "frontend_input": row.frontend_input or "",
                "source": "magento_attribute_registry",
            }
        )
    options.sort(
        key=lambda opt: (
            str(opt.get("label") or "").lower(),
            str(opt.get("field_group") or "").lower(),
            str(opt.get("code") or "").lower(),
        )
    )
    return options


def _shopify_field_group(owner_type: Optional[str], attribute_code: str) -> str:
    owner = str(owner_type or "").strip().lower()
    code = str(attribute_code or "").strip().lower()
    if owner in {"product", "variant"}:
        return "core"
    if owner == "product_option" or code.startswith("option_"):
        return "option"
    if "metafield" in owner:
        return "custom"
    return "core"


def _shopify_options(session: Session, connection_id: Optional[int]) -> List[Dict[str, Any]]:
    shop_code = None
    if connection_id:
        channel_type, native_id = decode_compat_connection_id(connection_id)
        if channel_type == "shopify":
            shop_code = session.scalar(
                select(ShopifyConnection.shop_code).where(ShopifyConnection.id == native_id)
            )
    stmt = select(ShopifyAttributeRegistry)
    if shop_code:
        stmt = stmt.where(ShopifyAttributeRegistry.shop_code == shop_code)
    seen: set[str] = set()
    options: List[Dict[str, Any]] = []
    for row in session.scalars(stmt).all():
        code = str(row.attribute_code or "").strip()
        if not code or code in seen:
            continue
        seen.add(code)
        label = str(row.name or "").strip() or code.replace("_", " ").title()
        field_group = _shopify_field_group(row.owner_type, code)
        options.append(
            {
                "code": code,
                "label": label,
                "display_label": f"{label} ({field_group})",
                "field_group": field_group,
                "data_type": row.data_type or "",
                "owner_type": row.owner_type,
                "source": "shopify_attribute_registry",
            }
        )
    options.sort(
        key=lambda opt: (
            str(opt.get("label") or "").lower(),
            str(opt.get("field_group") or "").lower(),
            str(opt.get("code") or "").lower(),
        )
    )
    return options


def export_channel_attribute_options_csv_rows(
    session: Session,
    channel_code: str,
    *,
    connection_id: Optional[int] = None,
) -> List[Dict[str, Any]]:
    """CSV rows for manual mapping reference (channel attribute codes)."""
    channel = resolve_pipeline_channel_code(channel_code)
    options = channel_attribute_options(session, channel, connection_id=connection_id)
    rows: List[Dict[str, Any]] = []
    for opt in options:
        rows.append(
            {
                "channel_code": channel,
                "connection_id": connection_id or "",
                "attribute_code": opt.get("code") or "",
                "attribute_id": opt.get("attribute_id") or "",
                "label": opt.get("label") or "",
                "data_type": opt.get("data_type") or "",
                "frontend_input": opt.get("frontend_input") or "",
                "owner_type": opt.get("owner_type") or "",
                "source": opt.get("source") or "",
            }
        )
    return rows


def _plytix_options(session: Session) -> List[Dict[str, Any]]:
    options: List[Dict[str, Any]] = []
    for row in session.scalars(select(AttributeDef).order_by(AttributeDef.attribute_code)).all():
        code = str(row.attribute_code or "").strip()
        if not code:
            continue
        options.append(
            {
                "code": code,
                "label": code,
                "data_type": row.type or "text",
                "source": "attribute_def",
            }
        )
    return options