# tests/integration/test_products_queries.py
"""Integration suite: this talks to the real development database against the
test tenant (org_8c32bf3e-6a18-4739-9b1c-94c0cf11125f), so it is not part of
the default `tests/unit` run and requires live DB connectivity to pass.

Exists because unit tests mock at the module boundary and never reach a real
cursor, which let a `_table_exists` bug ship that made every query in
products.py return empty against a real database. These tests go through the
real connection so a regression like that fails loudly here."""
import pytest
from dotenv import load_dotenv

load_dotenv()

from psycopg2 import sql

from app.services.infra.database import bootstrap_tenant, get_db_connection
from app.services.catalog.products import find_by_url, list_products, save_products

TENANT = "org_8c32bf3e-6a18-4739-9b1c-94c0cf11125f"
PRODUCT_KEY = "integration-test-query-layer-product"
PRODUCT_URL = "https://example.com/integration-test-query-layer-product"


@pytest.fixture
def inserted_product():
    """Insert one product via save_products, then delete it afterwards so the
    tenant is left as it was found."""
    bootstrap_tenant(TENANT)
    product = {
        "product_key": PRODUCT_KEY,
        "name": "Integration Test Product",
        "description": "Inserted by tests/integration/test_products_queries.py",
        "image_url": None,
        "product_url": PRODUCT_URL,
        "category": "integration-test",
        "ctas": [],
    }
    save_products(TENANT, [product], crawled_urls=[], crawl_complete=False)
    yield product

    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(sql.SQL(
                "DELETE FROM {}.strategist_products WHERE product_key = %s"
            ).format(sql.Identifier(TENANT)), (PRODUCT_KEY,))
        conn.commit()
    finally:
        conn.close()


def test_a_saved_product_is_found_by_url_and_listed(inserted_product):
    """This is the regression test for the `_table_exists` bug: under the old
    `cur.fetchone()[0]` code this raised KeyError against a RealDictCursor and
    every query silently returned empty. Both assertions here would have
    failed loudly on that bug."""
    found = find_by_url(TENANT, PRODUCT_URL)
    assert found["product_key"] == PRODUCT_KEY
    assert found["name"] == "Integration Test Product"

    listed = list_products(TENANT, limit=1000)
    assert any(row["product_key"] == PRODUCT_KEY for row in listed)


def test_find_by_url_on_an_unknown_url_returns_empty_not_broken(inserted_product):
    """An empty result must mean "not found", not "the query layer is
    broken" -- so this checks a real miss alongside a real hit in the same run."""
    assert find_by_url(TENANT, "https://example.com/definitely-not-a-real-product") == {}