"""Normalize legacy master_product_location_availability location codes to canonical registry codes."""

from __future__ import annotations

from typing import Any, Dict, Optional

from sqlalchemy import select
from sqlalchemy.orm import Session

from db.channel_exports import _canonical_location_code, _location_code_lookup
from db.models import MasterProductLocationAvailability


def normalize_location_availability_codes(session: Session, *, dry_run: bool = True) -> Dict[str, Any]:
    rows = session.scalars(select(MasterProductLocationAvailability).order_by(MasterProductLocationAvailability.product_id)).all()
    lookup = _location_code_lookup(session)
    by_key = {
        (int(row.product_id), str(row.location_code or "").strip()): row
        for row in rows
    }
    updated = 0
    merged = 0
    skipped = 0
    sample: list[Dict[str, Any]] = []

    for row in rows:
        current = str(row.location_code or "").strip()
        target = _canonical_location_code(current, lookup)
        if not current or not target or current == target:
            skipped += 1
            continue
        target_row = by_key.get((int(row.product_id), target))
        item = {"sku": row.sku, "from": current, "to": target}
        if target_row is not None and target_row.id != row.id:
            item["action"] = "merge"
            if not dry_run:
                target_row.is_available = bool(target_row.is_available or row.is_available)
                if (not str(target_row.source_value or "").strip()) and str(row.source_value or "").strip():
                    target_row.source_value = row.source_value
                if (not str(target_row.source_label or "").strip()) and str(row.source_label or "").strip():
                    target_row.source_label = row.source_label
                session.delete(row)
            merged += 1
        else:
            item["action"] = "update"
            if not dry_run:
                row.location_code = target
            updated += 1
            by_key[(int(row.product_id), target)] = row
            by_key.pop((int(row.product_id), current), None)
        if len(sample) < 50:
            sample.append(item)

    return {
        "status": "ok",
        "dry_run": dry_run,
        "updated": 0 if dry_run else updated,
        "merged": 0 if dry_run else merged,
        "would_update": updated if dry_run else 0,
        "would_merge": merged if dry_run else 0,
        "skipped": skipped,
        "sample": sample,
    }