"""Real-database tests for the products table migration.

Asserted against Postgres rather than source text: the migration runs against
live tenant data, so "the words appear in the function" is not evidence.
"""
import pytest

from app.services.infra.database import (
    bootstrap_tenant, get_db_connection, migrate_products_table,
)

NEW_COLUMNS = {
    "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",
    "tenant_relations", "rating", "review_count", "featured_rank",
}

MATCHING_COLUMNS = {"tenant_relations", "rating", "review_count", "featured_rank",
                    "missing_fields", "price_reference_cents", "fx_rate_used"}

OLD_SHAPE = """
    CREATE TABLE "{s}".strategist_products (
        id           SERIAL PRIMARY KEY,
        product_key  TEXT NOT NULL UNIQUE,
        name         TEXT NOT NULL,
        description  TEXT,
        image_url    TEXT,
        product_url  TEXT NOT NULL,
        category     TEXT,
        ctas         JSONB NOT NULL DEFAULT '[]',
        options      JSONB NOT NULL DEFAULT '[]',
        raw          JSONB NOT NULL DEFAULT '{{}}',
        related_keys TEXT[] NOT NULL DEFAULT '{{}}',
        extracted_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
    )
"""


def _exec(sql_text):
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(sql_text)
        conn.commit()
    finally:
        conn.close()


def _query(sql_text):
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(sql_text)
            return cur.fetchall()
    finally:
        conn.close()


def _columns(schema):
    return {r[0] for r in _query(
        "SELECT column_name FROM information_schema.columns "
        f"WHERE table_schema = '{schema}' AND table_name = 'strategist_products'")}


@pytest.fixture
def old_table(temp_tenant):
    _exec(OLD_SHAPE.format(s=temp_tenant))
    _exec(f"""INSERT INTO "{temp_tenant}".strategist_products
              (product_key, name, product_url) VALUES
              ('crawl:a', 'Crawled A', 'https://x.example/a'),
              ('crawl:b', 'Crawled B', 'https://x.example/b')""")
    return temp_tenant


def test_migration_adds_every_new_column(old_table):
    assert not (NEW_COLUMNS & _columns(old_table))
    migrate_products_table(old_table)
    assert NEW_COLUMNS <= _columns(old_table)


def test_migration_loses_no_rows(old_table):
    before = _query(f'SELECT count(*) FROM "{old_table}".strategist_products')[0][0]
    migrate_products_table(old_table)
    after = _query(f'SELECT count(*) FROM "{old_table}".strategist_products')[0][0]
    assert after == before == 2


def test_pre_existing_rows_are_labelled_crawl(old_table):
    # Any other default would let a Shopify sync delete them as stale.
    migrate_products_table(old_table)
    kinds = _query(
        f'SELECT DISTINCT source_kind FROM "{old_table}".strategist_products')
    assert kinds == [("crawl",)]


def test_migration_is_idempotent(old_table):
    migrate_products_table(old_table)
    first = _columns(old_table)
    migrate_products_table(old_table)
    assert _columns(old_table) == first
    rows = _query(f'SELECT count(*) FROM "{old_table}".strategist_products')[0][0]
    assert rows == 2


def test_bootstrap_creates_the_full_shape_for_a_new_tenant(temp_tenant):
    bootstrap_tenant(temp_tenant)
    assert NEW_COLUMNS <= _columns(temp_tenant)


def test_the_source_index_exists(old_table):
    migrate_products_table(old_table)
    idx = {r[0] for r in _query(
        f"SELECT indexname FROM pg_indexes WHERE schemaname = '{old_table}'")}
    assert "strategist_products_source_idx" in idx


def test_matching_spec_columns_are_added(old_table):
    assert not (MATCHING_COLUMNS & _columns(old_table))
    migrate_products_table(old_table)
    assert MATCHING_COLUMNS <= _columns(old_table)


def test_product_url_becomes_nullable(old_table):
    # A source with no product URLs would otherwise have every row rejected, and
    # the merchant would see an empty catalog with no explanation.
    migrate_products_table(old_table)
    _exec(f"""INSERT INTO "{old_table}".strategist_products
              (product_key, name, product_url) VALUES ('h:1','No Link',NULL)""")
    rows = _query(
        f"SELECT product_url FROM \"{old_table}\".strategist_products "
        "WHERE product_key = 'h:1'")
    assert rows[0][0] is None


def test_tenant_relations_defaults_to_empty_object(old_table):
    # Never null: downstream code reads .get("related") without a guard, and a
    # null would force every caller to branch.
    migrate_products_table(old_table)
    _exec(f"""INSERT INTO "{old_table}".strategist_products
              (product_key, name, product_url) VALUES ('c:x','X','https://x/1')""")
    rows = _query(
        f"SELECT tenant_relations FROM \"{old_table}\".strategist_products "
        "WHERE product_key = 'c:x'")
    assert rows[0][0] == {}


EXTRACTION_COLUMNS = {"is_accessory", "price_tier", "enriched_hash"}


def test_extraction_columns_are_added(old_table):
    assert not (EXTRACTION_COLUMNS & _columns(old_table))
    migrate_products_table(old_table)
    assert EXTRACTION_COLUMNS <= _columns(old_table)


def test_is_accessory_defaults_to_null_not_false(old_table):
    # Null means "not yet extracted"; false means "extracted, and it is not an
    # accessory". Defaulting to false would make an unextracted catalog look
    # fully processed.
    migrate_products_table(old_table)
    _exec(f"""INSERT INTO "{old_table}".strategist_products
              (product_key, name, product_url) VALUES ('c:z','Z','https://x/z')""")
    rows = _query(f"SELECT is_accessory FROM \"{old_table}\".strategist_products "
                  "WHERE product_key = 'c:z'")
    assert rows[0][0] is None