"""Inspect one SKU's taxonomy and Magento category derivation end to end.

Examples:
    python -m app.jobs.audit_single_sku_taxonomy --sku ACH-W1230
    python -m app.jobs.audit_single_sku_taxonomy --sku ACH-W1230 --connection-id 4
"""

from __future__ import annotations

import argparse
import json
from typing import Any, Dict, Optional

from sqlalchemy import select

from db.channel_exports import build_channel_product_payloads
from db.channel_listing_path import expand_listing_path_targets, resolve_listing_paths_for_sku
from db.master_taxonomy_product_paths import canonical_master_taxonomy_path_slugs, canonical_taxonomy_leaf_label
from db.models import MasterProduct, MasterProductAttributeValue, ProductChannelTaxonomyAssignment
from db.product_channel_taxonomy import resolve_magento_category_targets, resolve_taxonomy_targets_for_push
from db.session import get_session
from db.source_imports import normalize_column_key


def audit_single_sku_taxonomy(
    *,
    sku: str,
    connection_id: Optional[int] = None,
) -> Dict[str, Any]:
    with get_session() as session:
        product = session.scalar(
            select(MasterProduct).where(MasterProduct.sku == sku).limit(1)
        )
        if product is None:
            return {"status": "not_found", "sku": sku}

        attr_rows = list(
            session.scalars(
                select(MasterProductAttributeValue)
                .where(MasterProductAttributeValue.product_id == product.id)
                .order_by(MasterProductAttributeValue.attribute_code)
            ).all()
        )
        attrs = {
            normalize_column_key(str(row.attribute_code or "")): str(row.value or "")
            for row in attr_rows
            if str(row.attribute_code or "").strip()
        }
        raw_attributes = {
            str(row.attribute_code or ""): str(row.value or "")
            for row in attr_rows
            if str(row.attribute_code or "").strip()
        }

        canonical_paths = canonical_master_taxonomy_path_slugs(session, product, attrs)
        taxonomy_leaf = canonical_taxonomy_leaf_label(session, product, attrs)
        listing_paths = resolve_listing_paths_for_sku(session, product.sku)
        expanded_targets, expanded_path_slugs = expand_listing_path_targets(
            session,
            master_sku=product.sku,
            channel_code="magento",
            connection_id=connection_id,
        )

        payloads = build_channel_product_payloads(
            session,
            "magento",
            skus=[product.sku],
            only_assigned=False,
            connection_id=connection_id,
            skip_taxonomy=False,
        )
        payload = payloads[0] if payloads else {}
        canonical_fields = payload.get("canonical_fields") or {}
        channel_fields = payload.get("fields") or {}
        taxonomy_targets = payload.get("taxonomy_targets") or {}

        magento_category_ids, magento_category_paths = resolve_magento_category_targets(
            session,
            master_sku=product.sku,
            canonical_fields=canonical_fields,
            connection_id=connection_id,
        )

        assignment_rows = list(
            session.scalars(
                select(ProductChannelTaxonomyAssignment)
                .where(ProductChannelTaxonomyAssignment.master_sku == product.sku)
                .where(ProductChannelTaxonomyAssignment.channel_code == "magento")
                .order_by(
                    ProductChannelTaxonomyAssignment.assignment_status.desc(),
                    ProductChannelTaxonomyAssignment.sort_order,
                    ProductChannelTaxonomyAssignment.id,
                )
            ).all()
        )

        return {
            "status": "ok",
            "sku": product.sku,
            "connection_id": connection_id,
            "product": {
                "id": product.id,
                "name": product.name,
                "category_l1": product.category_l1,
                "category_l2": product.category_l2,
                "category_l3": product.category_l3,
                "collection": product.collection,
                "product_family": product.product_family,
                "brand": product.brand,
                "product_type_id": product.product_type_id,
                "is_active": product.is_active,
            },
            "raw_attributes": raw_attributes,
            "focus_attributes": {
                key: attrs.get(key)
                for key in (
                    "cabinet_type_sub_category_1",
                    "cabinet_type_sub_category_2",
                    "cabinet_type",
                    "sub_type",
                    "product_type",
                    "taxonomy_leaf_label",
                    "base_cabinets",
                    "wall_cabinets",
                    "panels_and_fillers",
                    "shopping_collection",
                    "shopping_l1",
                    "shopping_l2",
                )
                if attrs.get(key) is not None
            },
            "canonical_taxonomy": {
                "taxonomy_leaf_label": taxonomy_leaf,
                "canonical_master_taxonomy_path_slugs": canonical_paths,
            },
            "listing_paths": listing_paths,
            "expanded_magento_targets": {
                "targets": expanded_targets,
                "plp_path_slugs": expanded_path_slugs,
            },
            "magento_assignment_rows": [
                {
                    "id": row.id,
                    "remote_id": row.remote_id,
                    "remote_path": row.remote_path,
                    "taxonomy_kind": row.taxonomy_kind,
                    "assignment_status": row.assignment_status,
                    "match_source": row.match_source,
                    "sort_order": row.sort_order,
                    "connection_id": row.connection_id,
                }
                for row in assignment_rows
            ],
            "resolved_magento_targets": {
                "category_ids": magento_category_ids,
                "category_paths": magento_category_paths,
            },
            "payload_snapshot": {
                "canonical_fields": canonical_fields,
                "channel_fields": channel_fields,
                "taxonomy_targets": taxonomy_targets,
                "push_taxonomy_targets": resolve_taxonomy_targets_for_push(
                    session,
                    master_sku=product.sku,
                    channel_code="magento",
                    canonical_fields=canonical_fields,
                    connection_id=connection_id,
                ),
            },
        }


def main() -> int:
    parser = argparse.ArgumentParser(description="Audit one SKU taxonomy + Magento category derivation")
    parser.add_argument("--sku", required=True, help="Master SKU to inspect")
    parser.add_argument("--connection-id", type=int, default=None, help="Native Magento connection id")
    args = parser.parse_args()

    result = audit_single_sku_taxonomy(
        sku=str(args.sku).strip(),
        connection_id=args.connection_id,
    )
    print(json.dumps(result, indent=2, default=str))
    return 0 if result.get("status") == "ok" else 1


if __name__ == "__main__":
    raise SystemExit(main())