from __future__ import annotations

from typing import Any, Dict, List, Optional

import pandas as pd
import sqlalchemy as sa
from sqlalchemy import select
from sqlalchemy.dialects.postgresql import insert
from sqlalchemy.orm import Session

from db.models import MasterProductOverride
from db.source_imports import normalize_column_key


GLOBAL_CHANNEL = ""

LONG_FORMAT_COLUMNS = {"attribute_code", "field", "canonical_code"}

# Wide-format columns that are metadata, not override attributes.
NON_ATTRIBUTE_KEYS = {"sku", "item", "channel_code", "channel", "source", "source_label", "notes", "is_active"}


def import_master_override_dataframe(
    session: Session,
    df: pd.DataFrame,
    *,
    source_label: str = "override",
    default_channel_code: str = GLOBAL_CHANNEL,
) -> Dict[str, int]:
    """Import overrides from CSV in either long format (sku, attribute_code, value[, channel_code])
    or wide format (sku + one column per attribute, e.g. "SKU, SEO Title, SEO Description").
    """
    normalized_columns = {normalize_column_key(c) for c in df.columns}
    if normalized_columns & LONG_FORMAT_COLUMNS:
        rows, skipped = _long_rows(df, source_label, default_channel_code)
    else:
        rows, skipped = _wide_rows(df, source_label, default_channel_code)
    _upsert_overrides(session, rows)
    return {"input_rows": len(df), "upserted": len(rows), "skipped": skipped}


def set_master_override(
    session: Session,
    *,
    sku: str,
    attribute_code: str,
    value: Optional[str],
    channel_code: str = GLOBAL_CHANNEL,
    source_label: str = "manual",
    is_active: bool = True,
    notes: Optional[str] = None,
) -> Dict[str, Any]:
    sku_text = str(sku or "").strip()
    code = normalize_column_key(attribute_code)
    if not sku_text or not code:
        raise ValueError("sku and attribute_code are required")
    row = {
        "sku": sku_text,
        "channel_code": normalize_column_key(channel_code) if channel_code else GLOBAL_CHANNEL,
        "attribute_code": code,
        "value": _clean(value),
        "source_label": source_label,
        "is_active": is_active,
        "notes": notes,
    }
    _upsert_overrides(session, [row])
    return row


def export_master_overrides(session: Session) -> List[Dict[str, Any]]:
    rows = []
    for override in session.scalars(
        select(MasterProductOverride).order_by(
            MasterProductOverride.sku,
            MasterProductOverride.channel_code,
            MasterProductOverride.attribute_code,
        )
    ).all():
        rows.append(
            {
                "sku": override.sku,
                "channel_code": override.channel_code,
                "attribute_code": override.attribute_code,
                "value": override.value or "",
                "source_label": override.source_label or "",
                "is_active": override.is_active,
                "notes": override.notes or "",
            }
        )
    return rows


def overrides_for_channel(session: Session, channel_code: str) -> Dict[str, Dict[str, str]]:
    """Return {sku: {attribute_code: value}} for a channel: global rows first, channel rows win."""
    channel = normalize_column_key(channel_code)
    stmt = (
        select(MasterProductOverride)
        .where(MasterProductOverride.is_active.is_(True))
        .where(MasterProductOverride.channel_code.in_([GLOBAL_CHANNEL, channel]))
        .order_by(MasterProductOverride.sku)
    )
    rows = list(session.scalars(stmt).all())
    return merge_override_rows(
        [
            {
                "sku": r.sku,
                "channel_code": r.channel_code,
                "attribute_code": r.attribute_code,
                "value": r.value,
            }
            for r in rows
        ],
        channel,
    )


def merge_override_rows(rows: List[Dict[str, Any]], channel_code: str) -> Dict[str, Dict[str, str]]:
    """Pure merge: global ('') overrides apply first, channel-specific rows replace them."""
    merged: Dict[str, Dict[str, str]] = {}
    for wanted_channel in (GLOBAL_CHANNEL, channel_code):
        for row in rows:
            if (row.get("channel_code") or GLOBAL_CHANNEL) != wanted_channel:
                continue
            value = row.get("value")
            if value is None:
                continue
            merged.setdefault(row["sku"], {})[row["attribute_code"]] = value
    return merged


def _long_rows(df: pd.DataFrame, source_label: str, default_channel: str) -> tuple[List[Dict[str, Any]], int]:
    rows: List[Dict[str, Any]] = []
    skipped = 0
    for raw in df.to_dict(orient="records"):
        record = {normalize_column_key(k): v for k, v in raw.items()}
        sku = _clean(record.get("sku") or record.get("item"))
        code = normalize_column_key(
            str(record.get("attribute_code") or record.get("field") or record.get("canonical_code") or "")
        )
        if not sku or not code:
            skipped += 1
            continue
        channel = normalize_column_key(str(record.get("channel_code") or record.get("channel") or "")) or default_channel
        rows.append(
            {
                "sku": sku,
                "channel_code": channel,
                "attribute_code": code,
                "value": _clean(record.get("value")),
                "source_label": _clean(record.get("source_label") or record.get("source")) or source_label,
                "is_active": _to_bool(record.get("is_active"), default=True),
                "notes": _clean(record.get("notes")),
            }
        )
    return _dedupe(rows), skipped


def _wide_rows(df: pd.DataFrame, source_label: str, default_channel: str) -> tuple[List[Dict[str, Any]], int]:
    rows: List[Dict[str, Any]] = []
    skipped = 0
    for raw in df.to_dict(orient="records"):
        record = {str(k): v for k, v in raw.items()}
        keyed = {normalize_column_key(k): v for k, v in record.items()}
        sku = _clean(keyed.get("sku") or keyed.get("item"))
        if not sku:
            skipped += 1
            continue
        channel = normalize_column_key(str(keyed.get("channel_code") or keyed.get("channel") or "")) or default_channel
        for column_name, value in record.items():
            code = normalize_column_key(column_name)
            if not code or code in NON_ATTRIBUTE_KEYS:
                continue
            value_text = _clean(value)
            if value_text is None:
                continue
            rows.append(
                {
                    "sku": sku,
                    "channel_code": channel,
                    "attribute_code": code,
                    "value": value_text,
                    "source_label": source_label,
                    "is_active": True,
                    "notes": None,
                }
            )
    return _dedupe(rows), skipped


def _dedupe(rows: List[Dict[str, Any]]) -> List[Dict[str, Any]]:
    by_key: Dict[tuple, Dict[str, Any]] = {}
    for row in rows:
        by_key[(row["sku"], row["channel_code"], row["attribute_code"])] = row
    return list(by_key.values())


def _upsert_overrides(session: Session, rows: List[Dict[str, Any]]) -> None:
    if not rows:
        return
    stmt = insert(MasterProductOverride).values(rows)
    stmt = stmt.on_conflict_do_update(
        constraint="uq_master_product_override_sku_channel_attr",
        set_={
            "value": stmt.excluded.value,
            "source_label": stmt.excluded.source_label,
            "is_active": stmt.excluded.is_active,
            "notes": stmt.excluded.notes,
            "updated_at": sa.func.now(),
        },
    )
    session.execute(stmt)


def _clean(value: Any) -> Optional[str]:
    if value is None:
        return None
    text = str(value).strip()
    if not text or text.lower() in {"nan", "none", "null"}:
        return None
    return text


def _to_bool(value: Any, *, default: bool) -> bool:
    text = _clean(value)
    if text is None:
        return default
    return text.lower() in {"1", "true", "yes", "y", "on", "active"}