# tests/integration/test_products_table.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.

The table is created by bootstrap_tenant, which is how every table in this
codebase is created. Asserting against the real schema rather than the source
text means a typo in the DDL fails here rather than at runtime."""
import pytest
from dotenv import load_dotenv

load_dotenv()

from app.services.infra.database import bootstrap_tenant, get_db_connection

TENANT = "org_8c32bf3e-6a18-4739-9b1c-94c0cf11125f"

EXPECTED_COLUMNS = {
    "id", "product_key", "name", "description",
    "image_url", "product_url", "category", "ctas", "options",
    # The page's structured data, kept verbatim. Nothing reads it yet -- it
    # exists so that adding a field later is a query rather than a re-crawl of
    # every tenant's site.
    "raw",
    "related_keys", "extracted_at",
    # Catalog sync: the table now serves the crawler, Shopify and HTTP APIs, so
    # every row records which producer wrote it and every delete is scoped to one.
    "source_kind", "source_ref", "external_id", "brand",
    "taxonomy_path", "taxonomy_source", "raw_category",
    "price_cents", "price_max_cents", "compare_at_cents", "currency",
    "on_sale", "in_stock", "status", "attributes",
    "quality_score", "content_hash", "record_hash", "synced_at",
    # Recommendation matching specification: tenant-scoped related products,
    # merchandising signals, and the missing_fields exclusion flag.
    "tenant_relations", "rating", "review_count", "featured_rank",
    "missing_fields", "price_reference_cents", "fx_rate_used",
    # Attribute extraction (Phase 1): LLM-derived, filtered on by the
    # complement materialiser and the online ranker.
    "is_accessory", "price_tier",
    # Records the content_hash a product was last extracted at, so a re-run
    # after a sync only re-extracts products whose content actually changed.
    "enriched_hash",
}


@pytest.fixture(scope="module")
def columns():
    bootstrap_tenant(TENANT)
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute("""SELECT column_name, data_type, is_nullable, column_default
                           FROM information_schema.columns
                           WHERE table_schema = %s AND table_name = 'strategist_products'""",
                        (TENANT,))
            return {r[0]: {"type": r[1], "nullable": r[2], "default": r[3]}
                    for r in cur.fetchall()}
    finally:
        conn.close()


def test_the_table_has_exactly_the_expected_columns(columns):
    assert set(columns) == EXPECTED_COLUMNS


def test_ctas_is_jsonb_defaulting_to_an_empty_array(columns):
    assert columns["ctas"]["type"] == "jsonb"
    assert "[]" in (columns["ctas"]["default"] or "")
    assert columns["ctas"]["nullable"] == "NO"


def test_options_is_jsonb_defaulting_to_an_empty_array(columns):
    assert columns["options"]["type"] == "jsonb"
    assert "[]" in (columns["options"]["default"] or "")
    assert columns["options"]["nullable"] == "NO"


def test_related_keys_is_a_text_array_defaulting_to_empty(columns):
    assert columns["related_keys"]["type"] == "ARRAY"
    assert columns["related_keys"]["nullable"] == "NO"


def test_product_key_is_unique():
    """Re-ingestion must update a product, not duplicate it."""
    bootstrap_tenant(TENANT)
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute("""SELECT COUNT(*) FROM information_schema.table_constraints tc
                           JOIN information_schema.key_column_usage k
                             ON tc.constraint_name = k.constraint_name
                            AND tc.table_schema = k.table_schema
                           WHERE tc.table_schema = %s
                             AND tc.table_name = 'strategist_products'
                             AND tc.constraint_type = 'UNIQUE'
                             AND k.column_name = 'product_key'""", (TENANT,))
            assert cur.fetchone()[0] == 1
    finally:
        conn.close()


def test_the_url_index_exists(columns):
    """Every proactive event looks a product up by URL; without the index that
    is a sequential scan on the hot path."""
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute("""SELECT indexdef FROM pg_indexes
                           WHERE schemaname = %s AND tablename = 'strategist_products'""",
                        (TENANT,))
            defs = " ".join(r[0] for r in cur.fetchall())
            assert "product_url" in defs
    finally:
        conn.close()