from __future__ import annotations

from typing import Any, Dict, Optional

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

from db.models import (
    ChannelAttributeAlias,
    ChannelJob,
    ChannelPublishState,
    ChannelTransformRule,
    MagentoCatalogState,
)
from db.source_snapshot_export import count_current_source_snapshots


def _latest_job_finished_at(
    session: Session,
    connection_id: int,
    *,
    job_type: str,
) -> Optional[Any]:
    return session.scalar(
        select(ChannelJob.finished_at)
        .where(ChannelJob.channel_connection_id == connection_id)
        .where(ChannelJob.job_type == job_type)
        .where(ChannelJob.finished_at.is_not(None))
        .order_by(ChannelJob.finished_at.desc())
        .limit(1)
    )


def build_connection_dashboard_row(session: Session, conn: dict) -> Dict[str, Any]:
    """Per-connection stats for dashboard summary and list views."""
    connection_id = int(conn["id"])
    channel_type = conn["channel_type"]
    native_id = conn.get("native_id")
    pipeline_code = conn.get("channel_code") or channel_type

    published = session.scalar(
        select(func.count())
        .select_from(ChannelPublishState)
        .where(ChannelPublishState.channel_code == pipeline_code)
    ) or 0
    mappings = session.scalar(
        select(func.count())
        .select_from(ChannelAttributeAlias)
        .where(ChannelAttributeAlias.channel_code == pipeline_code)
    ) or 0
    rules = session.scalar(
        select(func.count())
        .select_from(ChannelTransformRule)
        .where(ChannelTransformRule.channel_code == pipeline_code)
    ) or 0

    source_snapshot = count_current_source_snapshots(
        session,
        channel_type,
        connection_id=native_id if channel_type in {"magento", "shopify"} else None,
    )
    remote_snapshot = 0
    if channel_type == "magento" and native_id:
        remote_snapshot = session.scalar(
            select(func.count())
            .select_from(MagentoCatalogState)
            .where(MagentoCatalogState.connection_id == native_id)
        ) or 0

    return {
        "connection_id": connection_id,
        "channel_type": channel_type,
        "channel_code": pipeline_code,
        "display_name": conn.get("display_name"),
        "channel_products": published,
        "source_snapshot": source_snapshot,
        "remote_snapshot": remote_snapshot,
        "published": published,
        "attribute_mappings": mappings,
        "transform_rules": rules,
        "last_pull_at": _latest_job_finished_at(session, connection_id, job_type="pull"),
        "last_push_at": _latest_job_finished_at(session, connection_id, job_type="push"),
    }