"""Real-database tests for scoped persistence.

The crawler, a Shopify store and an HTTP API all write to one table. An unscoped
delete here erases another producer's products, so it is proven against Postgres.
"""
from datetime import datetime, timedelta, timezone

import pytest

from app.services.catalog import products as products
from app.services.catalog.normalise import normalise
from app.services.catalog.sync import delete_stale, persist
from app.services.infra.database import bootstrap_tenant, get_db_connection

NOW = datetime(2026, 8, 10, 12, 0, 0, tzinfo=timezone.utc)
EARLIER = NOW - timedelta(hours=1)


def product(key, name="A Product"):
    return {
        "product_key": key, "name": name, "description": None, "brand": None,
        "image_url": None, "product_url": f"https://x.example/{key}",
        "category": None, "taxonomy_path": [], "taxonomy_source": "none",
        "raw_category": None, "tags": [], "attributes": [],
        "price_cents": 1000, "price_max_cents": 1000, "compare_at_cents": None,
        "currency": "INR", "on_sale": False, "in_stock": True,
        "status": "ACTIVE", "external_id": key.split(":", 1)[1],
        "source_kind": key.split(":", 1)[0], "source_ref": "ref",
        "tenant_relations": {}, "rating": None, "review_count": None,
        "featured_rank": None, "missing_fields": [],
        "price_reference_cents": 1000, "fx_rate_used": 1.0,
    }


def rows(schema, where=""):
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(
                f'SELECT product_key FROM "{schema}".strategist_products {where} '
                "ORDER BY product_key")
            return [r[0] for r in cur.fetchall()]
    finally:
        conn.close()


@pytest.fixture
def populated(temp_tenant):
    bootstrap_tenant(temp_tenant)
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(
                f'INSERT INTO "{temp_tenant}".strategist_products '
                "(product_key, name, product_url, source_kind, source_ref) VALUES "
                "('crawl:a', 'Crawled A', 'https://x.example/a', 'crawl', 'site'),"
                "('crawl:b', 'Crawled B', 'https://x.example/b', 'crawl', 'site')")
        conn.commit()
    finally:
        conn.close()
    return temp_tenant


def test_a_shopify_sync_does_not_delete_crawled_products(populated):
    # THE hazard. products.save_products' unscoped delete would remove both
    # crawled rows here.
    persist(populated, [product("shopify:1")], "shopify", "shop.myshopify.com", NOW)
    delete_stale(populated, "shopify", "shop.myshopify.com", NOW)

    assert rows(populated) == ["crawl:a", "crawl:b", "shopify:1"]


def test_a_website_crawl_does_not_delete_synced_products(populated):
    # The mirror of the hazard above. Every synced product has a URL the
    # crawler never visits, so an unscoped delete reaps the whole catalog.
    persist(populated, [product("shopify:1")], "shopify", "shop.myshopify.com", NOW)

    products.save_products(populated, [], crawled_urls=["https://x.example/a"],
                            crawl_complete=True)

    assert rows(populated) == ["crawl:a", "shopify:1"]


def test_stale_rows_of_the_same_source_are_deleted(populated):
    persist(populated, [product("shopify:1"), product("shopify:2")],
            "shopify", "shop.myshopify.com", EARLIER)
    persist(populated, [product("shopify:1")],
            "shopify", "shop.myshopify.com", NOW)

    removed = delete_stale(populated, "shopify", "shop.myshopify.com", NOW)

    assert removed == 1
    assert rows(populated) == ["crawl:a", "crawl:b", "shopify:1"]


def test_a_second_source_of_the_same_kind_is_untouched(populated):
    persist(populated, [product("shopify:1")], "shopify", "shop-a.myshopify.com", NOW)
    persist(populated, [product("shopify:2")], "shopify", "shop-b.myshopify.com", EARLIER)

    delete_stale(populated, "shopify", "shop-a.myshopify.com", NOW)

    assert "shopify:2" in rows(populated)


def test_upsert_does_not_duplicate(populated):
    persist(populated, [product("shopify:1", "First")], "shopify", "s", EARLIER)
    persist(populated, [product("shopify:1", "Second")], "shopify", "s", NOW)

    assert rows(populated, "WHERE product_key = 'shopify:1'") == ["shopify:1"]

    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(f'SELECT name FROM "{populated}".strategist_products '
                        "WHERE product_key = 'shopify:1'")
            assert cur.fetchone()[0] == "Second"
    finally:
        conn.close()


def test_persist_writes_the_commercial_fields(populated):
    persist(populated, [product("shopify:1")], "shopify", "s", NOW)
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(
                f'SELECT price_cents, currency, in_stock, status, content_hash '
                f'FROM "{populated}".strategist_products '
                "WHERE product_key = 'shopify:1'")
            price, currency, in_stock, status, chash = cur.fetchone()
    finally:
        conn.close()

    assert price == 1000
    assert currency == "INR"
    assert in_stock is True
    assert status == "ACTIVE"
    assert chash


def test_persist_accepts_exactly_what_normalise_produces(populated):
    # A hand-built product dict drifts from the normaliser over time. This
    # pins them together: persist must accept real normaliser output.
    source_product = {
        "external_id": "np-1", "title": "Normalised Product", "description": None,
        "brand": None, "handle": "np-1", "product_url": None, "image_url": None,
        "raw_category": None, "product_type": "snowboard", "tags": [],
        "collections": [], "status": "ACTIVE", "tracks_inventory": True,
        "variants": [{"price": "10.00", "compare_at_price": None,
                      "available": True, "options": {}}],
        "attributes_raw": [], "source_kind": "shopify", "source_ref": "ref",
    }
    context = {"shop_name": "S", "primary_domain": "https://x.example",
               "url_template": None, "currency": "INR",
               "reference_currency": "INR", "known_brands": []}

    product = normalise(source_product, context)
    assert product is not None
    persist(populated, [product], "shopify", "ref", NOW)  # must not raise