from __future__ import annotations

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

from sqlalchemy import select
from sqlalchemy.orm import Session

from db.models import (
    MagentoProductSourceSnapshot,
    PlytixProductSourceSnapshot,
    ShopifyProductSourceSnapshot,
)
from db.source_imports import normalize_column_key


def flatten_payload_for_export(payload: Dict[str, Any], *, channel: str) -> Dict[str, Any]:
    """Turn a stored source snapshot payload into flat scalar columns for CSV export."""
    if channel == "magento":
        return _flatten_magento_payload(payload)
    if channel == "plytix":
        from plytix.product_normalize import filter_plytix_display_fields, normalize_plytix_flat_record

        return filter_plytix_display_fields(normalize_plytix_flat_record(payload))
    return _flatten_scalar_payload(payload)


def _flatten_magento_payload(payload: Dict[str, Any]) -> Dict[str, Any]:
    flat: Dict[str, Any] = {}
    for key, value in payload.items():
        if key == "custom_attributes" and isinstance(value, list):
            for attr in value:
                if not isinstance(attr, dict):
                    continue
                code = str(attr.get("attribute_code") or "").strip()
                if not code:
                    continue
                flat[f"magento_{normalize_column_key(code)}"] = _scalar_export_value(attr.get("value"))
            continue
        if key in {"media_gallery_entries", "extension_attributes", "product_links", "options"}:
            if value:
                flat[key] = json.dumps(value, sort_keys=True, default=str)
            continue
        if isinstance(value, (dict, list)):
            flat[key] = json.dumps(value, sort_keys=True, default=str) if value else None
            continue
        flat[key] = _scalar_export_value(value)
    return flat


def _flatten_scalar_payload(payload: Dict[str, Any]) -> Dict[str, Any]:
    flat: Dict[str, Any] = {}
    for key, value in payload.items():
        if isinstance(value, (dict, list)):
            flat[key] = json.dumps(value, sort_keys=True, default=str) if value else None
        else:
            flat[key] = _scalar_export_value(value)
    return flat


def _scalar_export_value(value: Any) -> Optional[str]:
    if value is None:
        return None
    text = str(value).strip()
    return text or None


def export_source_snapshot_csv_rows(
    session: Session,
    channel: str,
    *,
    connection_id: Optional[int] = None,
    limit: Optional[int] = None,
) -> List[Dict[str, Any]]:
    """Wide CSV rows: one row per current SKU with flattened payload columns."""
    rows: List[Dict[str, Any]] = []
    for sku, payload in _iter_current_payloads(session, channel, connection_id=connection_id, limit=limit):
        flat = flatten_payload_for_export(payload, channel=channel)
        flat["sku"] = sku
        rows.append(flat)
    return rows


def export_source_snapshot_columns(
    session: Session,
    channel: str,
    *,
    connection_id: Optional[int] = None,
    sample_size: int = 500,
) -> List[str]:
    """Distinct export column names discovered from current snapshots."""
    columns: set[str] = {"sku"}
    seen = 0
    for _, payload in _iter_current_payloads(
        session,
        channel,
        connection_id=connection_id,
        limit=sample_size,
    ):
        flat = flatten_payload_for_export(payload, channel=channel)
        columns.update(flat.keys())
        seen += 1
    return sorted(columns)


def _iter_current_payloads(
    session: Session,
    channel: str,
    *,
    connection_id: Optional[int] = None,
    limit: Optional[int] = None,
) -> Iterable[tuple[str, Dict[str, Any]]]:
    channel = (channel or "").strip().lower()
    if channel == "magento":
        stmt = (
            select(MagentoProductSourceSnapshot.sku, MagentoProductSourceSnapshot.payload)
            .where(MagentoProductSourceSnapshot.valid_to.is_(None))
            .order_by(MagentoProductSourceSnapshot.sku)
        )
        if connection_id is not None:
            stmt = stmt.where(MagentoProductSourceSnapshot.connection_id == connection_id)
    elif channel == "shopify":
        stmt = (
            select(ShopifyProductSourceSnapshot.sku, ShopifyProductSourceSnapshot.payload)
            .where(ShopifyProductSourceSnapshot.valid_to.is_(None))
            .order_by(ShopifyProductSourceSnapshot.sku)
        )
        if connection_id is not None:
            stmt = stmt.where(ShopifyProductSourceSnapshot.connection_id == connection_id)
    elif channel == "plytix":
        stmt = (
            select(PlytixProductSourceSnapshot.sku, PlytixProductSourceSnapshot.payload)
            .where(PlytixProductSourceSnapshot.valid_to.is_(None))
            .order_by(PlytixProductSourceSnapshot.sku)
        )
    else:
        return

    if limit is not None:
        stmt = stmt.limit(limit)
    for sku, payload in session.execute(stmt).all():
        if not sku or not isinstance(payload, dict):
            continue
        yield str(sku), payload


def count_current_source_snapshots(
    session: Session,
    channel: str,
    *,
    connection_id: Optional[int] = None,
) -> int:
    from sqlalchemy import func

    channel = (channel or "").strip().lower()
    if channel == "magento":
        stmt = select(func.count()).select_from(MagentoProductSourceSnapshot).where(
            MagentoProductSourceSnapshot.valid_to.is_(None)
        )
        if connection_id is not None:
            stmt = stmt.where(MagentoProductSourceSnapshot.connection_id == connection_id)
    elif channel == "shopify":
        stmt = select(func.count()).select_from(ShopifyProductSourceSnapshot).where(
            ShopifyProductSourceSnapshot.valid_to.is_(None)
        )
        if connection_id is not None:
            stmt = stmt.where(ShopifyProductSourceSnapshot.connection_id == connection_id)
    elif channel == "plytix":
        stmt = select(func.count()).select_from(PlytixProductSourceSnapshot).where(
            PlytixProductSourceSnapshot.valid_to.is_(None)
        )
    else:
        return 0
    return int(session.scalar(stmt) or 0)


def ordered_csv_columns(rows: Sequence[Dict[str, Any]]) -> List[str]:
    if not rows:
        return ["sku"]
    keys: set[str] = set()
    for row in rows:
        keys.update(row.keys())
    ordered = ["sku"] + sorted(k for k in keys if k != "sku")
    return ordered