from __future__ import annotations

from datetime import datetime
from typing import Any, Dict, List, Optional

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

from db.models import ShopifyAttributeRegistry


class SqlAlchemyShopifyAttributeRegistryRepository:
    """CRUD for Shopify attributes/metafields/options pulled from Admin GraphQL."""

    def __init__(self, session: Session) -> None:
        self._session = session

    def consolidate_shop_codes(
        self,
        canonical_shop_code: str,
        alternate_shop_codes: List[str],
    ) -> Dict[str, int]:
        """Merge legacy shop_code aliases into one canonical key for the same store."""
        canonical = str(canonical_shop_code or "").strip()
        alternates = [
            str(code).strip()
            for code in alternate_shop_codes
            if str(code).strip() and str(code).strip() != canonical
        ]
        if not canonical or not alternates:
            return {"merged": 0, "deleted_conflicts": 0, "purged_alternate_shops": 0}

        merged = 0
        deleted_conflicts = 0
        for alternate in alternates:
            alt_rows = list(
                self._session.scalars(
                    select(ShopifyAttributeRegistry).where(ShopifyAttributeRegistry.shop_code == alternate)
                ).all()
            )
            for alt_row in alt_rows:
                existing = self._session.scalar(
                    select(ShopifyAttributeRegistry).where(
                        ShopifyAttributeRegistry.shop_code == canonical,
                        ShopifyAttributeRegistry.owner_type == alt_row.owner_type,
                        ShopifyAttributeRegistry.attribute_code == alt_row.attribute_code,
                    )
                )
                if existing is None:
                    alt_row.shop_code = canonical
                    merged += 1
                    continue
                if (alt_row.fetched_at or datetime.min) > (existing.fetched_at or datetime.min):
                    existing.name = alt_row.name
                    existing.namespace = alt_row.namespace
                    existing.key = alt_row.key
                    existing.data_type = alt_row.data_type
                    existing.is_standard = alt_row.is_standard
                    existing.raw_json = alt_row.raw_json
                    existing.fetched_at = alt_row.fetched_at
                self._session.delete(alt_row)
                deleted_conflicts += 1

        purge_result = self._session.execute(
            delete(ShopifyAttributeRegistry).where(ShopifyAttributeRegistry.shop_code.in_(alternates))
        )
        purged = int(purge_result.rowcount or 0)
        self._session.flush()
        return {
            "merged": merged,
            "deleted_conflicts": deleted_conflicts,
            "purged_alternate_shops": purged,
        }

    def bulk_replace(self, shop_code: str, rows: List[Dict[str, Any]], fetched_at: datetime) -> int:
        shop = str(shop_code or "").strip()
        if not shop:
            return 0

        existing_stmt = select(ShopifyAttributeRegistry).where(
            ShopifyAttributeRegistry.shop_code == shop,
        )
        existing = {
            (row.owner_type, row.attribute_code): row
            for row in self._session.execute(existing_stmt).scalars().all()
        }
        incoming_keys = set()
        count = 0
        for entry in rows:
            owner_type = str(entry.get("owner_type") or "").strip().lower()
            attribute_code = str(entry.get("attribute_code") or "").strip().lower()
            if not owner_type or not attribute_code:
                continue
            key = (owner_type, attribute_code)
            incoming_keys.add(key)
            row = existing.get(key)
            if row is None:
                row = ShopifyAttributeRegistry(
                    shop_code=shop,
                    owner_type=owner_type,
                    attribute_code=attribute_code,
                )
                self._session.add(row)
            row.name = entry.get("name")
            row.namespace = entry.get("namespace")
            row.key = entry.get("key")
            row.data_type = entry.get("data_type")
            row.is_standard = bool(entry.get("is_standard", False))
            row.raw_json = entry.get("raw_json")
            row.fetched_at = fetched_at
            count += 1

        for key, row in existing.items():
            if key not in incoming_keys:
                self._session.delete(row)

        self._session.flush()
        return count

    def list_attributes(self, shop_code: Optional[str] = None) -> List[ShopifyAttributeRegistry]:
        stmt = select(ShopifyAttributeRegistry)
        if shop_code:
            stmt = stmt.where(ShopifyAttributeRegistry.shop_code == shop_code)
        stmt = stmt.order_by(
            ShopifyAttributeRegistry.shop_code,
            ShopifyAttributeRegistry.owner_type,
            ShopifyAttributeRegistry.attribute_code,
        )
        return self._session.execute(stmt).scalars().all()