"""Import prefix-based color assignments as a safe master-data overlay.

CLI:
    python -m app.jobs.import_prefix_colors --file /path/to/prefix_colors.csv --dry-run
    python -m app.jobs.import_prefix_colors --file /path/to/prefix_colors.csv --apply
"""

from __future__ import annotations

import argparse
import csv
import hashlib
import json
import logging
import sys
from pathlib import Path
from typing import Any, Dict, List, Optional, Tuple

from sqlalchemy import select

from db.manual_attributes import replace_manual_attributes
from db.models import MasterProduct, MasterProductAttributeValue
from db.session import get_session

logger = logging.getLogger(__name__)

SOURCE_LABEL_DEFAULT = "prefix_color_import"
VOCAB_ATTRIBUTE_CODE = "color"
CSV_PREFIX_FIELD = "SKU Prefix"
CSV_COLOR_FIELD = "Color"


def _clean(value: Any) -> str:
    return str(value or "").strip()


def _normalize_prefix(value: Any) -> str:
    return _clean(value).upper()


def _normalize_color(value: Any) -> str:
    return _clean(value)


def _load_prefix_color_rows(file_path: str) -> List[Dict[str, str]]:
    path = Path(file_path)
    with path.open("r", newline="", encoding="utf-8-sig") as handle:
        reader = csv.DictReader(handle)
        fieldnames = set(reader.fieldnames or [])
        missing = {CSV_PREFIX_FIELD, CSV_COLOR_FIELD} - fieldnames
        if missing:
            raise ValueError(f"Missing required CSV column(s): {', '.join(sorted(missing))}")
        rows: List[Dict[str, str]] = []
        seen_prefixes: set[str] = set()
        for raw in reader:
            prefix = _normalize_prefix(raw.get(CSV_PREFIX_FIELD))
            color = _normalize_color(raw.get(CSV_COLOR_FIELD))
            if not prefix or not color:
                continue
            if prefix in seen_prefixes:
                raise ValueError(f"Duplicate prefix in CSV: {prefix}")
            seen_prefixes.add(prefix)
            rows.append({"prefix": prefix, "color": color})
        return rows


def _build_locked_color_vocabulary(colors: List[str]) -> Dict[str, Any]:
    return {
        "attributes": [
            {
                "attribute_code": VOCAB_ATTRIBUTE_CODE,
                "label": "Color",
                "is_locked": True,
                "unknown_policy": "drop",
                "covered_codes": [VOCAB_ATTRIBUTE_CODE],
                "sort_order": 100,
                "notes": "Locked by prefix color import",
                "values": [
                    {
                        "value": color,
                        "aliases": [color],
                        "sort_order": (index + 1) * 10,
                    }
                    for index, color in enumerate(colors)
                ],
            }
        ],
        "replace_all": False,
    }


def _style_match_for_sku(sku: str, prefix_rows: List[Dict[str, str]]) -> Optional[Tuple[str, str]]:
    text = _clean(sku).upper()
    for row in prefix_rows:
        prefix = row["prefix"]
        if text.startswith(prefix):
            return prefix, row["color"]
    return None


def _upsert_attribute_value(
    session,
    *,
    product: MasterProduct,
    attribute_code: str,
    value: str,
    source_label: str,
) -> None:
    row = session.scalar(
        select(MasterProductAttributeValue)
        .where(MasterProductAttributeValue.product_id == product.id)
        .where(MasterProductAttributeValue.attribute_code == attribute_code)
        .limit(1)
    )
    if row is None:
        row = MasterProductAttributeValue(
            product_id=product.id,
            sku=product.sku,
            attribute_code=attribute_code,
            value=value,
            source_label=source_label,
        )
        session.add(row)
    else:
        row.value = value
        row.source_label = source_label


def import_prefix_colors_rows(
    session,
    prefix_rows: List[Dict[str, str]],
    *,
    source_label: str = SOURCE_LABEL_DEFAULT,
    dry_run: bool = False,
    category_l1: str = "Kitchen Cabinets",
) -> Dict[str, Any]:
    if not prefix_rows:
        raise ValueError("No valid prefix/color rows found")

    colors = sorted({_normalize_color(row["color"]) for row in prefix_rows}, key=str.lower)
    ordered_prefix_rows = sorted(prefix_rows, key=lambda row: (-len(row["prefix"]), row["prefix"]))

    vocab_result = replace_manual_attributes(session, _build_locked_color_vocabulary(colors))

    products = session.scalars(
        select(MasterProduct)
        .where(MasterProduct.category_l1 == category_l1)
        .where(MasterProduct.is_active.is_(True))
        .order_by(MasterProduct.sku)
    ).all()

    matched = 0
    updated = 0
    unmatched = 0
    sample_updates: List[Dict[str, Any]] = []

    for product in products:
        match = _style_match_for_sku(product.sku, ordered_prefix_rows)
        if match is None:
            unmatched += 1
            continue
        prefix, color = match
        matched += 1
        current_payload = dict(product.raw_payload or {})
        current_value = _clean(current_payload.get(VOCAB_ATTRIBUTE_CODE))
        attr_value = session.scalar(
            select(MasterProductAttributeValue.value)
            .where(MasterProductAttributeValue.product_id == product.id)
            .where(MasterProductAttributeValue.attribute_code == VOCAB_ATTRIBUTE_CODE)
            .limit(1)
        )
        current_attr_value = _clean(attr_value)
        if current_value == color and current_attr_value == color:
            continue

        sample_updates.append(
            {
                "sku": product.sku,
                "prefix": prefix,
                "from_payload_color": current_value or None,
                "from_attribute_color": current_attr_value or None,
                "to_color": color,
            }
        )
        updated += 1

        if dry_run:
            continue

        current_payload[VOCAB_ATTRIBUTE_CODE] = color
        product.raw_payload = current_payload
        product.row_hash = hashlib.sha256(
            json.dumps(product.raw_payload or {}, sort_keys=True, default=str).encode("utf-8")
        ).hexdigest()
        product.is_active = True
        _upsert_attribute_value(
            session,
            product=product,
            attribute_code=VOCAB_ATTRIBUTE_CODE,
            value=color,
            source_label=source_label,
        )

    return {
        "status": "ok",
        "source_label": source_label,
        "category_l1": category_l1,
        "dry_run": dry_run,
        "prefix_count": len(ordered_prefix_rows),
        "distinct_colors": colors,
        "matched_products": matched,
        "updated_products": updated if not dry_run else 0,
        "would_update_products": updated if dry_run else 0,
        "unmatched_products": unmatched,
        "sample_updates": sample_updates[:50],
        "vocabulary": {
            "attribute_code": VOCAB_ATTRIBUTE_CODE,
            "value_count": len(colors),
            "saved_count": vocab_result.get("saved_count"),
        },
    }


def run_import_prefix_colors(
    file_path: str,
    *,
    source_label: str = SOURCE_LABEL_DEFAULT,
    dry_run: bool = False,
    category_l1: str = "Kitchen Cabinets",
) -> Dict[str, Any]:
    path = Path(file_path)
    if not path.exists() or not path.is_file():
        return {"status": "failed", "error": f"File not found: {file_path}"}
    if path.suffix.lower() != ".csv":
        return {"status": "failed", "error": "Only CSV files are supported"}

    try:
        prefix_rows = _load_prefix_color_rows(file_path)
    except Exception as exc:
        return {"status": "failed", "error": str(exc)}

    try:
        with get_session() as session:
            result = import_prefix_colors_rows(
                session,
                prefix_rows,
                source_label=source_label,
                dry_run=dry_run,
                category_l1=category_l1,
            )
            if dry_run:
                session.rollback()
            else:
                session.commit()
    except Exception as exc:
        return {"status": "failed", "error": str(exc)}

    result["file"] = str(path)
    return result


def main() -> int:
    parser = argparse.ArgumentParser(description="Import prefix-based product color assignments")
    parser.add_argument("--file", required=True, help="Path to the prefix color CSV on the server")
    parser.add_argument("--source-label", default=SOURCE_LABEL_DEFAULT, help="Import source label")
    parser.add_argument("--category-l1", default="Kitchen Cabinets", help="Limit import to one family/category")
    parser.add_argument("--dry-run", dest="dry_run", action="store_true", default=True)
    parser.add_argument("--apply", dest="dry_run", action="store_false", help="Write changes to DB")
    args = parser.parse_args()

    result = run_import_prefix_colors(
        args.file,
        source_label=args.source_label,
        dry_run=args.dry_run,
        category_l1=args.category_l1,
    )
    if result.get("status") != "ok":
        print(result.get("error", "unknown error"), file=sys.stderr)
        return 1
    print(json.dumps(result, indent=2, sort_keys=True))
    return 0


if __name__ == "__main__":
    logging.basicConfig(level=logging.INFO)
    sys.exit(main())