import pytest

from db.master_product_filters import (
    MasterAttributeFilter,
    _assembly_bucket_from_filter,
    apply_master_attribute_filters,
    parse_master_filters,
)
from db.models import MasterProduct
from sqlalchemy import select


def test_parse_master_filters_json_string():
    raw = '[{"code":"assembled or rta","value":"Assembled","match":"exact"}]'
    filters = parse_master_filters(raw)
    assert len(filters) == 1
    assert filters[0].normalized_code() == "assembled_or_rta"
    assert filters[0].normalized_value() == "Assembled"


def test_parse_master_filters_list():
    filters = parse_master_filters([{"attribute_code": "brand", "value": "Port & Bell"}])
    assert filters[0].normalized_code() == "brand"
    assert filters[0].normalized_value() == "Port & Bell"


def test_parse_master_filters_preserves_multi_value_list():
    filters = parse_master_filters([{"attribute_code": "collection", "value": "A, B\nC"}])
    assert filters[0].normalized_code() == "collection"
    assert filters[0].normalized_values() == ["A", "B", "C"]


def test_parse_master_filters_rejects_invalid_payload():
    with pytest.raises(ValueError, match="JSON array"):
        parse_master_filters({"code": "brand"})


def test_apply_master_attribute_filters_core_column_sql():
    stmt = select(MasterProduct.sku).where(MasterProduct.is_active.is_(True))
    stmt = apply_master_attribute_filters(
        stmt,
        filters=[MasterAttributeFilter(code="brand", value="Port & Bell", match="exact")],
    )
    compiled = str(stmt).lower()
    assert "master_product.brand" in compiled or "brand" in compiled


def test_apply_master_attribute_filters_multi_value_core_column_sql():
    stmt = select(MasterProduct.sku).where(MasterProduct.is_active.is_(True))
    stmt = apply_master_attribute_filters(
        stmt,
        filters=[MasterAttributeFilter(code="collection", value="Nova, Plymouth", match="contains")],
    )
    compiled = str(stmt).lower()
    assert " or " in compiled
    assert "collection" in compiled


def test_apply_master_attribute_filters_multi_value_custom_attribute_sql():
    stmt = select(MasterProduct.sku).where(MasterProduct.is_active.is_(True))
    stmt = apply_master_attribute_filters(
        stmt,
        filters=[MasterAttributeFilter(code="custom_style", value="Shaker|Raised", match="exact")],
    )
    compiled = str(stmt).lower()
    assert " or " in compiled
    assert "master_product_attribute_value" in compiled


def test_assembly_bucket_maps_unassembled_to_rta():
    assert _assembly_bucket_from_filter("Unassembled", "exact") == "rta"
    assert _assembly_bucket_from_filter("Unassem", "starts_with") == "rta"
    assert _assembly_bucket_from_filter("Assembled", "exact") == "assembled"


def test_apply_master_attribute_filters_assembled_exact_sql():
    stmt = select(MasterProduct.sku).where(MasterProduct.is_active.is_(True))
    stmt = apply_master_attribute_filters(
        stmt,
        filters=[MasterAttributeFilter(code="assembled_or_rta", value="Assembled", match="exact")],
    )
    compiled = str(stmt).lower()
    assert "assembly_type" in compiled
    assert "master_product_attribute_value" in compiled


def test_apply_master_attribute_filters_unassembled_exact_sql():
    stmt = select(MasterProduct.sku).where(MasterProduct.is_active.is_(True))
    stmt = apply_master_attribute_filters(
        stmt,
        filters=[MasterAttributeFilter(code="assembled_or_rta", value="Unassembled", match="exact")],
    )
    compiled = str(stmt).lower()
    assert "assembly_type" in compiled
    assert "master_product_attribute_value" in compiled
    assert "like" in compiled or "lower" in compiled


def test_apply_master_attribute_filters_not_equal_unassembled_sql():
    stmt = select(MasterProduct.sku).where(MasterProduct.is_active.is_(True))
    stmt = apply_master_attribute_filters(
        stmt,
        filters=[MasterAttributeFilter(code="assembled_or_rta", value="Unassembled", match="not_equal")],
    )
    compiled = str(stmt).lower()
    assert "not" in compiled
    assert "assembly_type" in compiled


def test_apply_master_attribute_filters_not_equal_assembled_sql():
    stmt = select(MasterProduct.sku).where(MasterProduct.is_active.is_(True))
    stmt = apply_master_attribute_filters(
        stmt,
        filters=[MasterAttributeFilter(code="assembled_or_rta", value="Assembled", match="not_equal")],
    )
    compiled = str(stmt).lower()
    assert "not" in compiled
    assert "assembly_type" in compiled
    assert "master_product_attribute_value" in compiled


def test_parse_master_filters_empty_match_without_value():
    filters = parse_master_filters([{"code": "assembled_or_rta", "match": "empty"}])
    assert len(filters) == 1
    assert filters[0].normalized_code() == "assembled_or_rta"
    assert filters[0].match == "empty"
    assert filters[0].is_empty_match()


def test_apply_master_attribute_filters_empty_assembly_sql():
    stmt = select(MasterProduct.sku).where(MasterProduct.is_active.is_(True))
    stmt = apply_master_attribute_filters(
        stmt,
        filters=[MasterAttributeFilter(code="assembled_or_rta", value="", match="empty")],
    )
    compiled = str(stmt).lower()
    assert "assembly_type" in compiled
    assert "master_product_attribute_value" in compiled


def test_apply_master_attribute_filters_empty_core_column_sql():
    stmt = select(MasterProduct.sku).where(MasterProduct.is_active.is_(True))
    stmt = apply_master_attribute_filters(
        stmt,
        filters=[MasterAttributeFilter(code="brand", value="", match="empty")],
    )
    compiled = str(stmt).lower()
    assert "brand" in compiled
    assert "null" in compiled or "trim" in compiled


def test_parse_master_filters_starts_with_alias():
    filters = parse_master_filters([{"code": "assembled_or_rta", "value": "Unassem", "match": "starts with"}])
    assert len(filters) == 1
    assert filters[0].match == "starts_with"


def test_apply_master_attribute_filters_starts_with_unassembled_sql():
    stmt = select(MasterProduct.sku).where(MasterProduct.is_active.is_(True))
    stmt = apply_master_attribute_filters(
        stmt,
        filters=[MasterAttributeFilter(code="assembled_or_rta", value="Unassem", match="starts_with")],
    )
    compiled = str(stmt).lower()
    assert "assembly_type" in compiled
    assert "master_product_attribute_value" in compiled


def test_parse_master_filters_not_equal_alias():
    filters = parse_master_filters([{"code": "assembled_or_rta", "value": "Assembled", "match": "not equal"}])
    assert len(filters) == 1
    assert filters[0].match == "not_equal"


def test_apply_master_attribute_filters_not_equal_custom_attribute_sql():
    stmt = select(MasterProduct.sku).where(MasterProduct.is_active.is_(True))
    stmt = apply_master_attribute_filters(
        stmt,
        filters=[MasterAttributeFilter(code="custom_attr", value="Assembled", match="not_equal")],
    )
    compiled = str(stmt).lower()
    assert "master_product_attribute_value" in compiled
    assert "not in" in compiled or "notin" in compiled.replace(" ", "")


def test_parse_master_filters_not_contains_alias():
    filters = parse_master_filters([{"code": "title", "value": "parent", "match": "does not contain"}])
    assert len(filters) == 1
    assert filters[0].match == "not_contains"
    assert filters[0].normalized_code() == "title"


def test_parse_master_filters_not_ends_with_alias():
    filters = parse_master_filters([{"code": "title", "value": "PARENT", "match": "doesn't end with"}])
    assert len(filters) == 1
    assert filters[0].match == "not_ends_with"


def test_apply_master_attribute_filters_title_not_contains_sql():
    stmt = select(MasterProduct.sku).where(MasterProduct.is_active.is_(True))
    stmt = apply_master_attribute_filters(
        stmt,
        filters=[MasterAttributeFilter(code="title", value="parent", match="not_contains")],
    )
    compiled = str(stmt).lower()
    assert "name" in compiled
    assert "not like" in compiled or "notlike" in compiled.replace(" ", "")


def test_apply_master_attribute_filters_title_not_ends_with_sql():
    stmt = select(MasterProduct.sku).where(MasterProduct.is_active.is_(True))
    stmt = apply_master_attribute_filters(
        stmt,
        filters=[MasterAttributeFilter(code="title", value="PARENT", match="not_ends_with")],
    )
    compiled = str(stmt).lower()
    assert "name" in compiled
    assert "not like" in compiled or "notlike" in compiled.replace(" ", "")


def test_apply_master_attribute_filters_is_parent_false_sql():
    stmt = select(MasterProduct.sku).where(MasterProduct.is_active.is_(True))
    stmt = apply_master_attribute_filters(
        stmt,
        filters=[MasterAttributeFilter(code="is_parent", value="0", match="exact")],
    )
    compiled = str(stmt).lower()
    assert "master_product_relation" in compiled
    assert "parent_sku" in compiled
    assert "not in" in compiled or "notin" in compiled.replace(" ", "")


def test_apply_master_attribute_filters_is_parent_true_sql():
    stmt = select(MasterProduct.sku).where(MasterProduct.is_active.is_(True))
    stmt = apply_master_attribute_filters(
        stmt,
        filters=[MasterAttributeFilter(code="is_parent", value="1", match="exact")],
    )
    compiled = str(stmt).lower()
    assert "master_product_relation" in compiled
    assert "parent_sku" in compiled
    assert "in (" in compiled or " in " in compiled


def test_list_master_attribute_values_is_parent():
    from unittest.mock import MagicMock

    from db.master_product_filters import list_master_attribute_values

    values = list_master_attribute_values(MagicMock(), "is_parent", limit=10)
    assert "0" in values
    assert "1" in values