"""Channel SKU prefix rules — master catalog is the single source of truth.

Normal push flow: master SKU → channel SKU via ``resolve_channel_sku_from_prefixes``.
Upload special case: channel/Tribeca reference → master via ``resolve_master_sku_from_prefixes``
or ``tribeca_sku_parse.expand_upload_reference_to_master_keys``.

Same Tribeca catalog as ``db.tribeca_sku_parse`` and ``build_plytix_csv.py``.
"""
from __future__ import annotations

from typing import Any, Dict, List, Optional, Sequence, Tuple

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.channel_exports import resolve_pipeline_channel_code
from db.compat_connections import pipeline_channel_for_connection
from db.models import ChannelSkuPrefixMapping
from db.source_imports import normalize_column_key


ACTIVE_STATUS = "active"

# Unassembled mirror SKUs: RTA-{assembled_master_sku} (see db.master_product_images).
RTA_ASSEMBLY_PREFIX = "RTA-"

# Shopify prefix rules: master SKU prefix (right) → channel/Shopify prefix (left).
DEFAULT_SHOPIFY_PREFIX_RULES: List[Dict[str, str]] = [
    {"channel_prefix": "HD-CW", "master_prefix": "HPW", "notes": "default shopify prefix map"},
    {"channel_prefix": "HD-SNW", "master_prefix": "ASW", "notes": "default shopify prefix map"},
    {"channel_prefix": "HD-HS", "master_prefix": "ASG", "notes": "default shopify prefix map"},
    {"channel_prefix": "HD-WRO", "master_prefix": "ANRO", "notes": "default shopify prefix map"},
    {"channel_prefix": "HD-CA", "master_prefix": "ACH", "notes": "default shopify prefix map"},
    {"channel_prefix": "SH-SNW", "master_prefix": "ESW", "notes": "default shopify prefix map"},
    {"channel_prefix": "SH-EB", "master_prefix": "EOB", "notes": "default shopify prefix map"},
    {"channel_prefix": "STH-SNW", "master_prefix": "MSW", "notes": "default shopify prefix map"},
    {"channel_prefix": "STH-WRO", "master_prefix": "MNRO", "notes": "default shopify prefix map"},
    {"channel_prefix": "STH-CBO", "master_prefix": "MBRO", "notes": "default shopify prefix map"},
    {"channel_prefix": "AM-SNW", "master_prefix": "GSW", "notes": "default shopify prefix map"},
]


def default_shopify_prefix_rule_pairs() -> List[Tuple[str, str]]:
    """(master_prefix, channel_prefix) pairs for default Shopify Tribeca prefix rules."""
    return [(row["master_prefix"], row["channel_prefix"]) for row in DEFAULT_SHOPIFY_PREFIX_RULES]


def load_all_active_prefix_rules(session: Session) -> List[Tuple[str, str]]:
    """All active channel prefix rules across connections/channels, deduped longest-first.

    Used by manifest/image imports where the source file may contain mixed channel prefixes
    and we need a global reverse lookup back to canonical master SKUs.
    """
    rows = session.scalars(
        select(ChannelSkuPrefixMapping)
        .where(ChannelSkuPrefixMapping.mapping_status == ACTIVE_STATUS)
        .order_by(
            ChannelSkuPrefixMapping.connection_id.is_(None),
            ChannelSkuPrefixMapping.sort_order,
            ChannelSkuPrefixMapping.master_prefix,
        )
    ).all()
    rules = _rules_from_rows(rows)
    if not rules:
        rules = default_shopify_prefix_rule_pairs()
    else:
        # Keep bundled defaults as a safety net for any historical filenames not yet seeded in DB.
        rules.extend(default_shopify_prefix_rule_pairs())
    return _dedupe_longest_first(rules)


def _peel_rta_assembly_prefix(sku: str) -> tuple[str, str]:
    """Split RTA-HPW-B15 → ('RTA-', 'HPW-B15'); identity when not an RTA mirror SKU."""
    text = str(sku or "").strip()
    prefix_len = len(RTA_ASSEMBLY_PREFIX)
    if len(text) > prefix_len and text.upper().startswith(RTA_ASSEMBLY_PREFIX):
        return RTA_ASSEMBLY_PREFIX, text[prefix_len:]
    return "", text


def apply_prefix_rules(sku: str, rules: Sequence[Tuple[str, str]], *, from_master: bool = True) -> str:
    """Replace leading style prefix. Rules are (master_prefix, channel_prefix) longest-first.

    RTA assembly mirrors keep the RTA- shell and only transform the inner style segment, e.g.
    RTA-HPW123 → RTA-HD-CW123 when HPW → HD-CW.
    """
    text = str(sku or "").strip()
    if not text or not rules:
        return text
    rta_shell, target = _peel_rta_assembly_prefix(text)
    if from_master:
        for master_prefix, channel_prefix in rules:
            if target.startswith(master_prefix):
                transformed = channel_prefix + target[len(master_prefix) :]
                return f"{rta_shell}{transformed}" if rta_shell else transformed
        return text
    for master_prefix, channel_prefix in rules:
        if target.startswith(channel_prefix):
            transformed = master_prefix + target[len(channel_prefix) :]
            return f"{rta_shell}{transformed}" if rta_shell else transformed
    return text


def apply_prefix_rules_reverse(sku: str, rules: Sequence[Tuple[str, str]]) -> str:
    """Channel SKU → master SKU; longest channel_prefix wins; RTA- shell preserved."""
    ordered = sorted(rules, key=lambda item: len(item[1]), reverse=True)
    return apply_prefix_rules(sku, ordered, from_master=False)


def load_prefix_rules(
    session: Session,
    channel_code: str,
    *,
    connection_id: Optional[int] = None,
) -> List[Tuple[str, str]]:
    """Active prefix rules sorted longest master_prefix first (connection-specific wins over channel-wide)."""
    channel = resolve_pipeline_channel_code(channel_code)
    rules: List[Tuple[str, str]] = []
    if connection_id:
        rules.extend(_rules_from_rows(_query_rules(session, channel, connection_id=connection_id)))
    rules.extend(_rules_from_rows(_query_rules(session, channel, connection_id=None)))
    return _dedupe_longest_first(rules)


def resolve_channel_sku_from_prefixes(
    session: Session,
    master_sku: str,
    channel_code: str,
    *,
    connection_id: Optional[int] = None,
    rules: Optional[Sequence[Tuple[str, str]]] = None,
) -> str:
    rule_set = list(rules) if rules is not None else load_prefix_rules(session, channel_code, connection_id=connection_id)
    return apply_prefix_rules(master_sku, rule_set, from_master=True)


def resolve_master_sku_from_prefixes(
    session: Session,
    channel_sku: str,
    channel_code: str,
    *,
    connection_id: Optional[int] = None,
) -> str:
    rules = load_prefix_rules(session, channel_code, connection_id=connection_id)
    return apply_prefix_rules(channel_sku, rules, from_master=False)


def list_prefix_mappings(
    session: Session,
    *,
    channel_code: Optional[str] = None,
    connection_id: Optional[int] = None,
    status: Optional[str] = ACTIVE_STATUS,
) -> List[Dict[str, Any]]:
    stmt = select(ChannelSkuPrefixMapping).order_by(
        ChannelSkuPrefixMapping.sort_order,
        ChannelSkuPrefixMapping.master_prefix,
    )
    if channel_code:
        stmt = stmt.where(ChannelSkuPrefixMapping.channel_code == resolve_pipeline_channel_code(channel_code))
    if connection_id is not None:
        stmt = stmt.where(ChannelSkuPrefixMapping.connection_id == connection_id)
    if status:
        stmt = stmt.where(ChannelSkuPrefixMapping.mapping_status == status)
    return [
        {
            "channel_code": row.channel_code,
            "connection_id": row.connection_id,
            "master_prefix": row.master_prefix,
            "channel_prefix": row.channel_prefix,
            "mapping_status": row.mapping_status,
            "sort_order": row.sort_order,
            "notes": row.notes or "",
            "updated_at": row.updated_at.isoformat() if row.updated_at else "",
        }
        for row in session.scalars(stmt).all()
    ]


def upsert_prefix_mappings(session: Session, rows: List[Dict[str, Any]]) -> int:
    if not rows:
        return 0
    channel_rows = [row for row in rows if not row.get("connection_id")]
    connection_rows = [row for row in rows if row.get("connection_id")]
    total = 0
    if channel_rows:
        stmt = insert(ChannelSkuPrefixMapping).values(channel_rows)
        stmt = stmt.on_conflict_do_update(
            index_elements=["channel_code", "master_prefix"],
            index_where=ChannelSkuPrefixMapping.connection_id.is_(None),
            set_={
                "channel_prefix": stmt.excluded.channel_prefix,
                "mapping_status": stmt.excluded.mapping_status,
                "sort_order": stmt.excluded.sort_order,
                "notes": stmt.excluded.notes,
                "updated_at": sa.func.now(),
            },
        )
        session.execute(stmt)
        total += len(channel_rows)
    if connection_rows:
        stmt = insert(ChannelSkuPrefixMapping).values(connection_rows)
        stmt = stmt.on_conflict_do_update(
            index_elements=["master_prefix", "connection_id"],
            index_where=ChannelSkuPrefixMapping.connection_id.is_not(None),
            set_={
                "channel_code": stmt.excluded.channel_code,
                "channel_prefix": stmt.excluded.channel_prefix,
                "mapping_status": stmt.excluded.mapping_status,
                "sort_order": stmt.excluded.sort_order,
                "notes": stmt.excluded.notes,
                "updated_at": sa.func.now(),
            },
        )
        session.execute(stmt)
        total += len(connection_rows)
    return total


def import_prefix_mapping_dataframe(
    session: Session,
    df: pd.DataFrame,
    *,
    default_channel_code: Optional[str] = None,
) -> Dict[str, 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()}
        master_prefix = _clean(
            record.get("master_prefix")
            or record.get("master_sku_prefix")
            or record.get("sku_prefix")
            or record.get("master")
            or record.get("master_code")
        )
        channel_prefix = _clean(
            record.get("channel_prefix")
            or record.get("channel_sku_prefix")
            or record.get("shopify_prefix")
            or record.get("shopify")
        )
        if not master_prefix or not channel_prefix:
            skipped += 1
            continue
        connection_id = _to_int(record.get("connection_id") or record.get("channel_id"))
        channel_code = _clean(record.get("channel_code") or record.get("pipeline_channel")) or default_channel_code
        if connection_id and not channel_code:
            channel_code = pipeline_channel_for_connection(session, connection_id)
        if not channel_code:
            channel_code = "shopify"
        rows.append(
            {
                "channel_code": resolve_pipeline_channel_code(channel_code),
                "connection_id": connection_id,
                "master_prefix": master_prefix,
                "channel_prefix": channel_prefix,
                "mapping_status": ACTIVE_STATUS,
                "sort_order": _to_int(record.get("sort_order"), default=0),
                "notes": _clean(record.get("notes")),
            }
        )
    upserted = upsert_prefix_mappings(session, rows)
    return {"input_rows": len(df), "upserted": upserted, "skipped": skipped}


def bootstrap_shopify_prefix_defaults(session: Session, *, connection_id: Optional[int] = None) -> Dict[str, int]:
    existing = {
        row.master_prefix
        for row in session.scalars(
            select(ChannelSkuPrefixMapping)
            .where(ChannelSkuPrefixMapping.channel_code == "shopify")
            .where(
                ChannelSkuPrefixMapping.connection_id == connection_id
                if connection_id
                else ChannelSkuPrefixMapping.connection_id.is_(None)
            )
        ).all()
    }
    rows = []
    for index, item in enumerate(DEFAULT_SHOPIFY_PREFIX_RULES, start=1):
        if item["master_prefix"] in existing:
            continue
        rows.append(
            {
                "channel_code": "shopify",
                "connection_id": connection_id,
                "master_prefix": item["master_prefix"],
                "channel_prefix": item["channel_prefix"],
                "mapping_status": ACTIVE_STATUS,
                "sort_order": index * 10,
                "notes": item.get("notes"),
            }
        )
    return {"candidates": len(DEFAULT_SHOPIFY_PREFIX_RULES), "upserted": upsert_prefix_mappings(session, rows)}


def preview_prefix_resolution(
    session: Session,
    master_sku: str,
    channel_code: str,
    *,
    connection_id: Optional[int] = None,
) -> Dict[str, Any]:
    rules = load_prefix_rules(session, channel_code, connection_id=connection_id)
    resolved = apply_prefix_rules(master_sku, rules, from_master=True)
    matched = None
    for master_prefix, channel_prefix in rules:
        if str(master_sku or "").startswith(master_prefix):
            matched = {"master_prefix": master_prefix, "channel_prefix": channel_prefix}
            break
    return {
        "master_sku": master_sku,
        "channel_code": resolve_pipeline_channel_code(channel_code),
        "channel_sku": resolved,
        "matched_rule": matched,
        "changed": resolved != str(master_sku or "").strip(),
    }


def _query_rules(
    session: Session,
    channel_code: str,
    *,
    connection_id: Optional[int],
):
    stmt = (
        select(ChannelSkuPrefixMapping)
        .where(ChannelSkuPrefixMapping.channel_code == channel_code)
        .where(ChannelSkuPrefixMapping.mapping_status == ACTIVE_STATUS)
        .order_by(ChannelSkuPrefixMapping.sort_order, ChannelSkuPrefixMapping.master_prefix)
    )
    if connection_id is None:
        stmt = stmt.where(ChannelSkuPrefixMapping.connection_id.is_(None))
    else:
        stmt = stmt.where(ChannelSkuPrefixMapping.connection_id == connection_id)
    return session.scalars(stmt).all()


def _rules_from_rows(rows) -> List[Tuple[str, str]]:
    return [(str(row.master_prefix), str(row.channel_prefix)) for row in rows]


def _dedupe_longest_first(rules: Sequence[Tuple[str, str]]) -> List[Tuple[str, str]]:
    seen: set[str] = set()
    ordered = sorted(rules, key=lambda item: len(item[0]), reverse=True)
    out: List[Tuple[str, str]] = []
    for master_prefix, channel_prefix in ordered:
        if master_prefix in seen:
            continue
        seen.add(master_prefix)
        out.append((master_prefix, channel_prefix))
    return out


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_int(value: Any, *, default: int = 0) -> int:
    text = _clean(value)
    if text is None:
        return default
    try:
        return int(float(text))
    except ValueError:
        return default