from __future__ import annotations

import hashlib
import math
import json
from datetime import datetime
from typing import Dict, Iterable, List, Tuple

import pandas as pd

from db.ports import IngestRepository
from db.schemas import (
    AttributeDiscoveryCreate,
    IngestRunCreate,
    ProductSnapshotClose,
    ProductSnapshotCreate,
    RawFeedRowCreate,
)


CORE_COLUMNS = {
    "sku",
    "name",
    "description",
    "short_description",
    "product_type",
    "attribute_set_code",
    "categories",
    "categories_raw",
    "base_image",
    "additional_images",
    "variant_of",
    "variant_list",
}


def _stable_hash(payload: Dict[str, object]) -> str:
    blob = json.dumps(payload, sort_keys=True, separators=(",", ":"), default=str)
    return hashlib.sha256(blob.encode("utf-8")).hexdigest()


def _row_to_snapshot_payload(row: Dict[str, object]) -> Dict[str, object]:
    payload: Dict[str, object] = {}
    for key in CORE_COLUMNS:
        if key in row:
            payload[key] = _as_text(row.get(key))
    attrs = {k: _as_text(v) for k, v in row.items() if k not in CORE_COLUMNS}
    payload["attrs"] = attrs
    payload["row_hash"] = _stable_hash(payload)
    return payload


def ingest_plytix_dataframe(
    repo: IngestRepository,
    df: pd.DataFrame,
    *,
    file_name: str,
    file_hash: str,
    keep_cols: List[str],
    hardcoded_additional: Dict[str, str],
    allowed_attribute_codes: set[str],
) -> Tuple[int, List[str]]:
    started_at = datetime.utcnow()
    records = df.to_dict(orient="records")
    skus = [str(r.get("sku", "")).strip() for r in records]

    ingest_id = repo.create_ingest_run(
        IngestRunCreate(
            source="plytix",
            file_name=file_name,
            file_hash=file_hash,
            started_at=started_at,
            status="started",
            row_count=len(df),
        )
    )

    rows: List[RawFeedRowCreate] = []
    snapshots: List[ProductSnapshotCreate] = []
    close_updates: List[ProductSnapshotClose] = []
    discoveries: List[AttributeDiscoveryCreate] = []

    current_index = repo.load_current_snapshot_index([s for s in skus if s])
    attribute_defs = repo.load_attribute_def_index()
    attribute_options = repo.load_attribute_option_index()
    allowed_columns = _build_allowed_columns(
        keep_cols=keep_cols,
        hardcoded_additional=hardcoded_additional,
        allowed_attribute_codes=allowed_attribute_codes,
    )

    for idx, row in enumerate(records, start=1):
        sku = str(row.get("sku", "")).strip()
        rows.append(
            RawFeedRowCreate(
                ingest_run_id=ingest_id,
                row_num=idx,
                sku=sku or None,
                payload=row,
            )
        )

        if not sku:
            continue

        payload = _row_to_snapshot_payload(row)
        row_hash = payload["row_hash"]
        current = current_index.get(sku)
        if current and current.get("row_hash") == row_hash:
            continue

        if current:
            close_updates.append(
                ProductSnapshotClose(id=int(current.get("id") or 0), valid_to=started_at)
            )

        snapshots.append(
            ProductSnapshotCreate(
                sku=sku,
                source="plytix",
                load_id=ingest_id,
                valid_from=started_at,
                name=_as_text(row.get("name")),
                description=_as_text(row.get("description")),
                short_description=_as_text(row.get("short_description")),
                product_type=_as_text(row.get("product_type")),
                attribute_set_code=_as_text(row.get("attribute_set_code")),
                categories_raw=_as_text(row.get("categories_raw") or row.get("categories")),
                base_image=_as_text(row.get("base_image")),
                additional_images=_as_text(row.get("additional_images")),
                variant_of=_as_text(row.get("variant_of")),
                variant_list=_as_text(row.get("variant_list")),
                attrs=payload["attrs"],
                row_hash=row_hash,
            )
        )

        discoveries.extend(
            _detect_new_attribute_values(
                ingest_id,
                sku,
                row,
                attribute_defs,
                attribute_options,
            )
        )
        discoveries.extend(
            _detect_unknown_columns(
                ingest_id,
                sku,
                row,
                allowed_columns,
            )
        )

    if rows:
        repo.add_raw_rows(rows)
    if close_updates:
        repo.close_snapshots([c for c in close_updates if c.id])
    if snapshots:
        repo.add_product_snapshots(snapshots)
    if discoveries:
        repo.add_attribute_discoveries(_dedupe_discoveries(discoveries))

    repo.update_ingest_run(ingest_id, status="success", row_count=len(df))
    return ingest_id, [s for s in skus if s]


def expand_sku_list_with_children(
    repo: IngestRepository,
    sku_list: List[str],
) -> List[str]:
    """
    Expand sku_list to include children of any configurable parents.
    Parses children from variant_list and configurable_variations (Plytix raw "C1,C2"
    or Magento "sku=C1,attr=Y|sku=C2" format). Ensures parent+children are loaded
    together so normalization can build configurable_variations correctly.
    """
    if not sku_list:
        return []
    snapshots = repo.load_current_snapshots(sku_list)
    if not snapshots:
        return list(sku_list)
    seen: set[str] = {s.strip().lower() for s in sku_list if s}
    children: List[str] = []

    from magento.sync_hashes import _parse_children_from_row

    for snap in snapshots:
        pt = str(snap.get("product_type", "") or "").strip().lower()
        if "configurable" not in pt:
            continue
        attrs = snap.get("attrs", {}) or {}
        row = {**{k: v for k, v in snap.items() if k != "attrs"}, **(attrs or {})}
        ch, _, _ = _parse_children_from_row(row)
        for c in ch:
            if c and c.strip().lower() not in seen:
                seen.add(c.strip().lower())
                children.append(c)

    if not children:
        return list(sku_list)
    return list(sku_list) + children


def build_dataframe_from_snapshots(snapshots: Iterable[Dict[str, object]]) -> pd.DataFrame:
    rows: List[Dict[str, object]] = []
    for snapshot in snapshots:
        attrs = snapshot.get("attrs", {}) or {}
        base = {k: v for k, v in snapshot.items() if k not in {"attrs", "id"}}
        if "categories" not in base or _is_empty(base.get("categories")):
            if "categories_raw" in base and not _is_empty(base.get("categories_raw")):
                base["categories"] = base.get("categories_raw")
        row = {**attrs, **base}
        rows.append(row)
    return pd.DataFrame(rows)


def _as_text(value: object) -> str:
    if value is None:
        return ""
    return str(value).strip()


def _is_empty(value: object) -> bool:
    if value is None:
        return True
    try:
        if isinstance(value, float) and math.isnan(value):
            return True
    except Exception:
        pass
    text = str(value).strip()
    return text == "" or text.lower() in {"nan", "none", "null"}


def _detect_new_attribute_values(
    ingest_run_id: int,
    sku: str,
    row: Dict[str, object],
    attribute_defs: Dict[str, Dict[str, object]],
    attribute_options: Dict[str, List[str]],
) -> List[AttributeDiscoveryCreate]:
    discoveries: List[AttributeDiscoveryCreate] = []
    for code, meta in attribute_defs.items():
        if meta.get("allows_free_text"):
            continue
        if code not in row:
            continue
        raw_value = _as_text(row.get(code))
        if not raw_value:
            continue

        values = _split_attribute_values(raw_value, meta.get("type", ""))
        existing = set(attribute_options.get(code, []))
        for val in values:
            if not val:
                continue
            if val in existing:
                continue
            discoveries.append(
                AttributeDiscoveryCreate(
                    ingest_run_id=ingest_run_id,
                    sku=sku,
                    attribute_code=code,
                    new_value=val,
                    source="column",
                )
            )
    return discoveries


def _split_attribute_values(value: str, attr_type: object) -> List[str]:
    text = _as_text(value)
    if not text:
        return []
    type_text = _as_text(attr_type).lower()
    if "multi" in type_text:
        parts = [p.strip() for p in text.replace("|", ",").split(",")]
        return [p for p in parts if p]
    return [text]


def _dedupe_discoveries(rows: List[AttributeDiscoveryCreate]) -> List[AttributeDiscoveryCreate]:
    seen = set()
    out: List[AttributeDiscoveryCreate] = []
    for row in rows:
        key = (row.attribute_code, row.new_value)
        if key in seen:
            continue
        seen.add(key)
        out.append(row)
    return out


def _build_allowed_columns(
    *,
    keep_cols: List[str],
    hardcoded_additional: Dict[str, str],
    allowed_attribute_codes: set[str],
) -> set[str]:
    allowed = {c.strip().lower() for c in keep_cols if c}
    allowed |= {k.strip().lower() for k in (hardcoded_additional or {}).keys()}
    allowed |= {c.strip().lower() for c in allowed_attribute_codes if c}
    return allowed


def _detect_unknown_columns(
    ingest_run_id: int,
    sku: str,
    row: Dict[str, object],
    allowed_columns: set[str],
) -> List[AttributeDiscoveryCreate]:
    discoveries: List[AttributeDiscoveryCreate] = []
    for key, value in row.items():
        code = str(key).strip()
        if not code:
            continue
        if code.lower() in allowed_columns:
            continue
        if _is_empty(value):
            continue
        discoveries.append(
            AttributeDiscoveryCreate(
                ingest_run_id=ingest_run_id,
                sku=sku,
                attribute_code=code,
                new_value=_as_text(value),
                source="unknown_column",
            )
        )
    return discoveries