from __future__ import annotations

from datetime import datetime, timezone
import re

import pytest
from fastapi.testclient import TestClient
from sqlalchemy import create_engine, event, select, text
from sqlalchemy.orm import Session, sessionmaker
from sqlalchemy.pool import StaticPool

from db.base import Base
from db.models import MasterBrand
from db.models_v2 import V2Attribute, V2AttributeValue, V2Category, V2Product
from test_v2_event_queueing import LEGACY_TABLES, _create_v2_test_schema
from v2_api import get_db


@pytest.fixture()
def v2_session() -> Session:
    engine = create_engine(
        "sqlite://",
        connect_args={"check_same_thread": False},
        poolclass=StaticPool,
    )

    @event.listens_for(engine, "connect")
    def _sqlite_pragma(dbapi_connection, connection_record):
        cursor = dbapi_connection.cursor()
        cursor.execute("PRAGMA foreign_keys=ON")
        cursor.close()
        dbapi_connection.create_function(
            "regexp_replace",
            4,
            lambda value, pattern, replacement, flags: re.sub(pattern, replacement, value or ""),
            deterministic=True,
        )
        dbapi_connection.create_function("now", 0, lambda: datetime.now(timezone.utc).isoformat())

    Base.metadata.create_all(engine, tables=LEGACY_TABLES)
    session = sessionmaker(bind=engine, expire_on_commit=False)()
    _create_v2_test_schema(session)
    try:
        yield session
    finally:
        session.close()
        engine.dispose()


@pytest.fixture()
def v2_client(v2_session: Session):
    from main_v2 import app

    def _override_db():
        yield v2_session

    app.dependency_overrides[get_db] = _override_db
    try:
        yield TestClient(app)
    finally:
        app.dependency_overrides.clear()


def _csv(body: str) -> dict:
    return {"file": ("products.csv", body.encode("utf-8"), "text/csv")}


def _seed_locked_catalog(session: Session) -> dict[str, int]:
    brand = MasterBrand(code="castle-craft", name="Castle Craft", is_active=True, aliases={"items": []})
    session.add(brand)
    hub = V2Category(name="Kitchen Cabinets", node_type="hub", parent_id=None, level=1, normalized_name="kitchen cabinets")
    session.add(hub)
    session.flush()
    categories = {}
    for name in ("Anna Snow White", "Maya Snow White", "Grace Snow White"):
        row = V2Category(
            name=name,
            node_type="category",
            parent_id=int(hub.id),
            level=2,
            normalized_name=name.lower(),
        )
        session.add(row)
        session.flush()
        categories[name] = int(row.id)
    collection = V2Category(
        name="Anna Natural Rift White Oak",
        node_type="collection",
        parent_id=int(hub.id),
        level=2,
        normalized_name="anna natural rift white oak",
    )
    session.add(collection)
    attribute = V2Attribute(code="door_width", label="Door Width", data_type="select")
    session.add(attribute)
    session.flush()
    for label, code in (("White", "white"), ("Black", "black"), ("Natural Oak", "natural_oak")):
        session.add(V2AttributeValue(attribute_id=int(attribute.id), value_code=code, display_label=label))
    session.commit()
    return {
        "brand_id": int(brand.id),
        "hub_id": int(hub.id),
        "collection_id": int(collection.id),
        "attribute_id": int(attribute.id),
        **{f"category_{name}": entity_id for name, entity_id in categories.items()},
    }


def test_unknown_brand_does_not_create_and_blocks_import(v2_client: TestClient, v2_session: Session):
    _seed_locked_catalog(v2_session)
    csv = "sku,title,brand\nSKU-1,Door,NewBrand\n"
    response = v2_client.post("/api/v2/import/sessions", files=_csv(csv))
    assert response.status_code == 200
    body = response.json()
    assert body["status"] == "needs_mapping"
    assert body["summary"]["unresolved_mappings"] >= 1
    assert v2_session.execute(select(MasterBrand).where(MasterBrand.name == "NewBrand")).scalar_one_or_none() is None
    assert v2_session.execute(select(V2Product)).scalars().all() == []

    commit = v2_client.post(f"/api/v2/import/sessions/{body['id']}/commit", json={"confirmed": True})
    assert commit.status_code == 400
    mappings = v2_client.get(
        f"/api/v2/import/sessions/{body['id']}/mappings",
        params={"entity_type": "brand", "unresolved_only": True},
    ).json()["items"]
    assert mappings[0]["source_value"] == "NewBrand"
    assert mappings[0]["status"] in {"unmatched", "suggested"}


def test_unknown_hub_category_collection_attribute_and_option_are_blocked(
    v2_client: TestClient, v2_session: Session
):
    _seed_locked_catalog(v2_session)
    csv = (
        "sku,title,hub,category,collection,Door Style,DoorWidth\n"
        "SKU-1,Door,Unknown Hub,Unknown Category,Unknown Collection,Shaker,72\n"
    )
    body = v2_client.post("/api/v2/import/sessions", files=_csv(csv)).json()
    assert body["status"] == "needs_mapping"
    unresolved = body["summary"]["unresolved_by_type"]
    assert unresolved.get("hub") == 1
    assert unresolved.get("category") == 1
    assert unresolved.get("collection") == 1
    assert unresolved.get("attribute") >= 1
    assert v2_session.execute(select(V2Category).where(V2Category.name == "Unknown Hub")).scalar_one_or_none() is None
    assert v2_session.execute(select(V2Attribute).where(V2Attribute.label == "Door Style")).scalar_one_or_none() is None


def test_ambiguous_snow_white_requires_user_choice(v2_client: TestClient, v2_session: Session):
    _seed_locked_catalog(v2_session)
    csv = "sku,title,category\nSKU-1,Door,Snow White\nSKU-2,Panel,Snow White\n"
    body = v2_client.post("/api/v2/import/sessions", files=_csv(csv)).json()
    mappings = v2_client.get(
        f"/api/v2/import/sessions/{body['id']}/mappings",
        params={"entity_type": "category"},
    ).json()["items"]
    snow = next(item for item in mappings if item["source_value"] == "Snow White")
    assert snow["occurrence_count"] == 2
    assert snow["status"] == "ambiguous"
    assert snow["matched_target_id"] is None
    assert len(snow["suggested_targets"]) >= 2
    commit = v2_client.post(f"/api/v2/import/sessions/{body['id']}/commit", json={"confirmed": True})
    assert commit.status_code == 400


def test_known_mapping_is_reused_on_later_import(v2_client: TestClient, v2_session: Session):
    ids = _seed_locked_catalog(v2_session)
    csv = "sku,title,brand\nSKU-1,Door,CastleCraft\n"
    first = v2_client.post("/api/v2/import/sessions", files=_csv(csv)).json()
    mapping = v2_client.get(
        f"/api/v2/import/sessions/{first['id']}/mappings",
        params={"entity_type": "brand", "unresolved_only": True},
    ).json()["items"][0]
    resolved = v2_client.post(
        f"/api/v2/import/sessions/{first['id']}/mappings/{mapping['id']}/resolve",
        json={"action": "map", "target_id": ids["brand_id"], "resolved_by": "tester"},
    )
    assert resolved.status_code == 200
    commit = v2_client.post(
        f"/api/v2/import/sessions/{first['id']}/commit",
        json={"confirmed": True, "committed_by": "tester"},
    )
    assert commit.status_code == 200

    second = v2_client.post("/api/v2/import/sessions", files=_csv("sku,title,brand\nSKU-2,Drawer,CastleCraft\n")).json()
    mappings = v2_client.get(
        f"/api/v2/import/sessions/{second['id']}/mappings",
        params={"entity_type": "brand"},
    ).json()["items"]
    castle = next(item for item in mappings if item["source_value"] == "CastleCraft")
    assert castle["status"] == "known_mapping"
    assert castle["matched_target_id"] == ids["brand_id"]
    assert second["summary"]["unresolved_by_type"].get("brand", 0) == 0


def test_create_collection_requires_hub_and_cannot_nest(v2_client: TestClient, v2_session: Session):
    ids = _seed_locked_catalog(v2_session)
    csv = "sku,title,collection\nSKU-1,Door,New Oak Line\n"
    body = v2_client.post("/api/v2/import/sessions", files=_csv(csv)).json()
    mapping = v2_client.get(
        f"/api/v2/import/sessions/{body['id']}/mappings",
        params={"entity_type": "collection", "unresolved_only": True},
    ).json()["items"][0]
    missing_hub = v2_client.post(
        f"/api/v2/import/sessions/{body['id']}/mappings/{mapping['id']}/resolve",
        json={"action": "create", "create_payload": {"name": "New Oak Line"}, "confirm_similar": True},
    )
    assert missing_hub.status_code == 400

    created = v2_client.post(
        f"/api/v2/import/sessions/{body['id']}/mappings/{mapping['id']}/resolve",
        json={
            "action": "create",
            "create_payload": {"name": "New Oak Line", "hub_id": ids["hub_id"]},
            "confirm_similar": True,
        },
    )
    assert created.status_code == 200
    row = v2_session.get(V2Category, created.json()["mapping"]["matched_target_id"])
    assert row is not None
    assert row.node_type == "collection"
    assert int(row.parent_id) == ids["hub_id"]
    parent = v2_session.get(V2Category, int(row.parent_id))
    assert parent.node_type == "hub"


def test_hub_without_collection_is_valid(v2_client: TestClient, v2_session: Session):
    ids = _seed_locked_catalog(v2_session)
    csv = "sku,title,hub\nSKU-1,Door,Kitchen Cabinets\n"
    body = v2_client.post("/api/v2/import/sessions", files=_csv(csv)).json()
    assert body["summary"]["unresolved_by_type"].get("collection", 0) == 0
    commit = v2_client.post(
        f"/api/v2/import/sessions/{body['id']}/commit",
        json={"confirmed": True, "committed_by": "tester"},
    )
    assert commit.status_code == 200
    product = v2_session.execute(select(V2Product).where(V2Product.sku == "SKU-1")).scalar_one()
    assert int(product.shopping_l1_category_id) == ids["hub_id"]
    assert product.primary_category_id == ids["hub_id"]


def test_unknown_attribute_requires_explicit_type(v2_client: TestClient, v2_session: Session):
    _seed_locked_catalog(v2_session)
    csv = "sku,title,Door Style\nSKU-1,Door,Shaker\n"
    body = v2_client.post("/api/v2/import/sessions", files=_csv(csv)).json()
    mapping = v2_client.get(
        f"/api/v2/import/sessions/{body['id']}/mappings",
        params={"entity_type": "attribute", "unresolved_only": True},
    ).json()["items"][0]
    assert mapping["source_value"] == "Door Style"
    no_type = v2_client.post(
        f"/api/v2/import/sessions/{body['id']}/mappings/{mapping['id']}/resolve",
        json={"action": "create", "create_payload": {"name": "Door Style"}, "confirm_similar": True},
    )
    assert no_type.status_code == 400
    created = v2_client.post(
        f"/api/v2/import/sessions/{body['id']}/mappings/{mapping['id']}/resolve",
        json={
            "action": "create",
            "create_payload": {"name": "Door Style", "data_type": "text"},
            "confirm_similar": True,
        },
    )
    assert created.status_code == 200
    assert v2_session.execute(select(V2Attribute).where(V2Attribute.label == "Door Style")).scalar_one() is not None


def test_attribute_option_is_not_silently_appended(v2_client: TestClient, v2_session: Session):
    ids = _seed_locked_catalog(v2_session)
    csv = "sku,title,Door Width\nSKU-1,Door,Natural\n"
    body = v2_client.post("/api/v2/import/sessions", files=_csv(csv)).json()
    options = v2_client.get(
        f"/api/v2/import/sessions/{body['id']}/mappings",
        params={"entity_type": "attribute_option", "unresolved_only": True},
    ).json()["items"]
    assert options
    assert options[0]["source_value"] == "Natural"
    assert v2_session.execute(
        select(V2AttributeValue).where(V2AttributeValue.display_label == "Natural")
    ).scalar_one_or_none() is None
    mapped = v2_client.post(
        f"/api/v2/import/sessions/{body['id']}/mappings/{options[0]['id']}/resolve",
        json={"action": "map", "target_id": None},
    )
    assert mapped.status_code == 400
    oak = v2_session.execute(
        select(V2AttributeValue).where(
            V2AttributeValue.attribute_id == ids["attribute_id"],
            V2AttributeValue.display_label == "Natural Oak",
        )
    ).scalar_one()
    ok = v2_client.post(
        f"/api/v2/import/sessions/{body['id']}/mappings/{options[0]['id']}/resolve",
        json={"action": "map", "target_id": int(oak.id)},
    )
    assert ok.status_code == 200


def test_exact_brand_match_auto_resolves(v2_client: TestClient, v2_session: Session):
    ids = _seed_locked_catalog(v2_session)
    csv = "sku,title,brand\nSKU-1,Door,Castle Craft\n"
    body = v2_client.post("/api/v2/import/sessions", files=_csv(csv)).json()
    mappings = v2_client.get(
        f"/api/v2/import/sessions/{body['id']}/mappings",
        params={"entity_type": "brand"},
    ).json()["items"]
    assert mappings[0]["status"] == "exact_match"
    assert mappings[0]["matched_target_id"] == ids["brand_id"]
    assert body["status"] == "ready"