from __future__ import annotations

from typing import Optional, Tuple

from sqlalchemy.orm import Session

from db.channel_exports import resolve_pipeline_channel_code
from db.models import ChannelConnection, MagentoConnection, ShopifyConnection


SHOPIFY_COMPAT_ID_OFFSET = 1_000_000
GENERIC_COMPAT_ID_OFFSET = 2_000_000


def default_native_connection_id(session: Session, channel_type: str) -> Optional[int]:
    """First active native connection for a channel (used when readiness scope omits connection ids)."""
    from sqlalchemy import select

    channel = (channel_type or "").strip().lower()
    if channel == "magento":
        native_id = session.scalar(
            select(MagentoConnection.id)
            .where(MagentoConnection.status == "active")
            .order_by(MagentoConnection.id)
            .limit(1)
        )
        return int(native_id) if native_id is not None else None
    if channel == "shopify":
        native_id = session.scalar(
            select(ShopifyConnection.id)
            .where(ShopifyConnection.status == "active")
            .order_by(ShopifyConnection.id)
            .limit(1)
        )
        return int(native_id) if native_id is not None else None
    return None


def compat_connection_id(channel_type: str, native_id: int) -> int:
    if channel_type == "shopify":
        return SHOPIFY_COMPAT_ID_OFFSET + int(native_id)
    if channel_type == "generic":
        return GENERIC_COMPAT_ID_OFFSET + int(native_id)
    return int(native_id)


def decode_compat_connection_id(connection_id: int) -> Tuple[str, int]:
    if connection_id >= GENERIC_COMPAT_ID_OFFSET:
        return "generic", connection_id - GENERIC_COMPAT_ID_OFFSET
    if connection_id >= SHOPIFY_COMPAT_ID_OFFSET:
        return "shopify", connection_id - SHOPIFY_COMPAT_ID_OFFSET
    return "magento", connection_id


def resolve_native_connection_id(channel_type: str, connection_id: Optional[int]) -> Optional[int]:
    """Decode a compat or native connection id to the channel's native table id."""
    if connection_id is None:
        return None
    channel = (channel_type or "").strip().lower()
    decoded_type, native_id = decode_compat_connection_id(int(connection_id))
    if channel == "shopify":
        return native_id if decoded_type == "shopify" else int(connection_id)
    if channel == "magento":
        return native_id if decoded_type == "magento" else int(connection_id)
    if channel == "generic":
        return native_id if decoded_type == "generic" else int(connection_id)
    return native_id


def resolve_compat_connection_id(channel_type: str, connection_id: Optional[int]) -> Optional[int]:
    """Normalize a compat or native connection id to the compat id used in mapping tables."""
    if connection_id is None:
        return None
    channel = (channel_type or "").strip().lower()
    decoded_type, native_id = decode_compat_connection_id(int(connection_id))
    if channel == "shopify":
        if decoded_type == "shopify":
            return int(connection_id)
        return compat_connection_id("shopify", int(connection_id))
    if channel == "magento":
        return native_id if decoded_type == "magento" else int(connection_id)
    if channel == "generic":
        if decoded_type == "generic":
            return int(connection_id)
        return compat_connection_id("generic", int(connection_id))
    return int(connection_id)


def pipeline_channel_for_connection(session: Session, connection_id: int) -> Optional[str]:
    channel_type, native_id = decode_compat_connection_id(connection_id)
    if channel_type == "magento":
        row = session.get(MagentoConnection, native_id)
        code = row.store_code if row and row.store_code else f"magento-{native_id}"
    elif channel_type == "shopify":
        from sqlalchemy import select

        code = session.scalar(
            select(ShopifyConnection.shop_code).where(ShopifyConnection.id == native_id)
        ) or f"shopify-{native_id}"
    else:
        row = session.get(ChannelConnection, native_id)
        code = row.channel_code if row else ""
    if not code:
        return channel_type if channel_type in {"magento", "shopify", "plytix"} else None
    try:
        return resolve_pipeline_channel_code(code, channel_type=channel_type)
    except ValueError:
        return channel_type if channel_type in {"magento", "shopify", "plytix"} else None