from __future__ import annotations

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

from sqlalchemy import select
from sqlalchemy.orm import Session

from db.models import (
    MagentoProductSourceSnapshot,
    PlytixProductSourceSnapshot,
    ShopifyProductSourceSnapshot,
)
from db.repositories import SqlAlchemyIngestRepository
from db.schemas import (
    IngestRunCreate,
    MagentoProductSourceSnapshotCreate,
    PlytixProductSourceSnapshotCreate,
    ShopifyProductSourceSnapshotCreate,
)
from db.source_imports import stable_payload_hash


ENRICHMENT_PROFILES: Dict[str, str] = {
    "images": "Product media, thumbnails, and gallery URLs",
    "magento_custom_attributes": "Magento custom_attributes (select option IDs preserved)",
    "shopify_media": "Shopify featured image and media gallery",
    "plytix_assets": "Plytix thumbnail and asset URLs",
}


def list_enrichment_profiles() -> List[Dict[str, str]]:
    return [{"code": code, "description": text} for code, text in ENRICHMENT_PROFILES.items()]


def merge_payload(base: Dict[str, Any], patch: Dict[str, Any]) -> Dict[str, Any]:
    """Deep-merge channel snapshot payloads; custom_attributes merge by attribute_code."""
    out = dict(base or {})
    for key, value in (patch or {}).items():
        if key == "custom_attributes" and isinstance(value, list):
            merged = {str(item.get("attribute_code") or "").strip(): item for item in out.get("custom_attributes") or [] if isinstance(item, dict)}
            for item in value:
                if not isinstance(item, dict):
                    continue
                code = str(item.get("attribute_code") or "").strip()
                if code:
                    merged[code] = item
            out["custom_attributes"] = list(merged.values())
            continue
        if isinstance(value, dict) and isinstance(out.get(key), dict):
            out[key] = merge_payload(out[key], value)
            continue
        out[key] = value
    return out


def apply_snapshot_patches(
    session: Session,
    channel: str,
    patches: Dict[str, Dict[str, Any]],
    *,
    connection_id: Optional[int] = None,
    source_label: str = "enrichment_pull",
    dry_run: bool = False,
) -> Dict[str, Any]:
    """Merge explicit field patches into current source snapshots (versioned rows)."""
    channel = (channel or "").strip().lower()
    if channel not in {"magento", "shopify", "plytix"}:
        raise ValueError(f"Unsupported enrichment channel: {channel}")
    if not patches:
        return {"status": "ok", "dry_run": dry_run, "patched": 0, "unchanged": 0, "missing": 0}

    current = _load_current_payloads(session, channel, list(patches), connection_id=connection_id)
    repo = SqlAlchemyIngestRepository(session)
    started_at = datetime.utcnow()
    ingest_id = None
    if not dry_run:
        ingest_id = repo.create_ingest_run(
            IngestRunCreate(
                source=f"{channel}_enrichment",
                file_name=f"{source_label}.json",
                file_hash=stable_payload_hash({"profiles": source_label, "count": len(patches)}),
                started_at=started_at,
                status="started",
                row_count=len(patches),
            )
        )

    close_ids: List[int] = []
    inserts: List[Any] = []
    patched = 0
    unchanged = 0
    missing = 0

    for sku, patch in patches.items():
        existing = current.get(sku)
        if not existing:
            missing += 1
            continue
        merged = merge_payload(existing["payload"], patch)
        row_hash = stable_payload_hash(merged)
        if existing.get("row_hash") == row_hash:
            unchanged += 1
            continue
        patched += 1
        if dry_run:
            continue
        close_ids.append(int(existing["id"]))
        inserts.append(_snapshot_create(channel, sku, merged, ingest_id, started_at, connection_id, existing))

    if dry_run:
        return {
            "status": "ok",
            "dry_run": True,
            "channel": channel,
            "connection_id": connection_id,
            "candidate_skus": len(patches),
            "patched": patched,
            "unchanged": unchanged,
            "missing": missing,
        }

    if channel == "magento":
        repo.close_magento_source_snapshots(close_ids, valid_to=started_at)
        repo.add_magento_source_snapshots(inserts)
    elif channel == "shopify":
        repo.close_shopify_source_snapshots(close_ids, valid_to=started_at)
        repo.add_shopify_source_snapshots(inserts)
    else:
        repo.close_plytix_source_snapshots(close_ids, valid_to=started_at)
        repo.add_plytix_source_snapshots(inserts)
    if ingest_id is not None:
        repo.update_ingest_run(ingest_id, status="success", row_count=len(patches))
    return {
        "status": "ok",
        "dry_run": False,
        "channel": channel,
        "connection_id": connection_id,
        "ingest_id": ingest_id,
        "patched": patched,
        "unchanged": unchanged,
        "missing": missing,
        "closed_previous": len(close_ids),
        "inserted": len(inserts),
    }


def _snapshot_create(
    channel: str,
    sku: str,
    payload: Dict[str, Any],
    ingest_id: int,
    started_at: datetime,
    connection_id: Optional[int],
    existing: Dict[str, Any],
) -> Any:
    row_hash = stable_payload_hash(payload)
    if channel == "magento":
        return MagentoProductSourceSnapshotCreate(
            connection_id=connection_id,
            load_id=ingest_id,
            sku=sku,
            valid_from=started_at,
            magento_product_id=payload.get("id") or existing.get("magento_product_id"),
            product_type=str(payload.get("type_id") or payload.get("product_type") or existing.get("product_type") or ""),
            attribute_set_id=payload.get("attribute_set_id") or existing.get("attribute_set_id"),
            status=str(payload.get("status") or existing.get("status") or ""),
            visibility=str(payload.get("visibility") or existing.get("visibility") or ""),
            price=payload.get("price") or existing.get("price"),
            qty=payload.get("qty") or existing.get("qty"),
            is_in_stock=payload.get("is_in_stock") or existing.get("is_in_stock"),
            payload=payload,
            column_names={"columns": sorted(payload.keys())},
            row_hash=row_hash,
        )
    if channel == "shopify":
        return ShopifyProductSourceSnapshotCreate(
            connection_id=connection_id,
            load_id=ingest_id,
            sku=sku,
            valid_from=started_at,
            shopify_product_id=str(payload.get("shopify_product_id") or existing.get("shopify_product_id") or ""),
            shopify_variant_id=str(payload.get("shopify_variant_id") or existing.get("shopify_variant_id") or ""),
            product_type=str(payload.get("product_type") or existing.get("product_type") or ""),
            status=str(payload.get("status") or existing.get("status") or ""),
            price=payload.get("price") or existing.get("price"),
            payload=payload,
            column_names={"columns": sorted(payload.keys())},
            row_hash=row_hash,
        )
    return PlytixProductSourceSnapshotCreate(
        load_id=ingest_id,
        sku=sku,
        valid_from=started_at,
        product_type=str(payload.get("product_type") or existing.get("product_type") or ""),
        status=str(payload.get("status") or existing.get("status") or ""),
        price=payload.get("price") or existing.get("price"),
        payload=payload,
        column_names={"columns": sorted(payload.keys())},
        row_hash=row_hash,
    )


def _load_current_payloads(
    session: Session,
    channel: str,
    skus: Sequence[str],
    *,
    connection_id: Optional[int],
) -> Dict[str, Dict[str, Any]]:
    if not skus:
        return {}
    if channel == "magento":
        model = MagentoProductSourceSnapshot
    elif channel == "shopify":
        model = ShopifyProductSourceSnapshot
    else:
        model = PlytixProductSourceSnapshot
    stmt = (
        select(model.id, model.sku, model.payload, model.row_hash)
        .where(model.valid_to.is_(None))
        .where(model.sku.in_(list(skus)))
    )
    if connection_id is not None and channel in {"magento", "shopify"}:
        stmt = stmt.where(model.connection_id == connection_id)
    out: Dict[str, Dict[str, Any]] = {}
    for row_id, sku, payload, row_hash in session.execute(stmt).all():
        if not sku or not isinstance(payload, dict):
            continue
        out[str(sku)] = {"id": row_id, "payload": payload, "row_hash": row_hash}
    return out


def extract_image_urls(payload: Dict[str, Any], *, channel: str) -> List[str]:
    """Best-effort image URL list for matrix preview."""
    channel = (channel or "").strip().lower()
    urls: List[str] = []
    if channel == "magento":
        for entry in payload.get("media_gallery_entries") or []:
            if not isinstance(entry, dict):
                continue
            file_path = str(entry.get("file") or "").strip()
            if file_path:
                urls.append(file_path)
        if payload.get("primary_image_url"):
            urls.insert(0, str(payload["primary_image_url"]))
    elif channel == "shopify":
        for key in ("featured_image_url", "thumbnail_url", "primary_image_url"):
            if payload.get(key):
                urls.append(str(payload[key]))
        raw = payload.get("image_urls")
        if isinstance(raw, list):
            urls.extend(str(item) for item in raw if item)
        elif isinstance(raw, str) and raw.startswith("["):
            try:
                parsed = json.loads(raw)
                if isinstance(parsed, list):
                    urls.extend(str(item) for item in parsed if item)
            except json.JSONDecodeError:
                pass
    else:
        for key in ("thumbnail", "Thumbnail", "thumbnail_url", "base_image", "image", "primary_image_url"):
            value = payload.get(key)
            if value:
                urls.append(str(value))
    seen: set[str] = set()
    ordered: List[str] = []
    for url in urls:
        if url and url not in seen:
            seen.add(url)
            ordered.append(url)
    return ordered