import pytest

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

TABLES = {"strategist_product_neighbors", "strategist_pairing_decisions",
          "strategist_product_embeddings"}


def _tables(schema):
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute("SELECT table_name FROM information_schema.tables "
                        "WHERE table_schema = %s", (schema,))
            return {r[0] for r in cur.fetchall()}
    finally:
        conn.close()


def _exec(schema, statement, params=None):
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(statement.replace("{S}", f'"{schema}"'), params or ())
        conn.commit()
    finally:
        conn.close()


def test_bootstrap_creates_all_three(temp_tenant):
    bootstrap_tenant(temp_tenant)
    assert TABLES <= _tables(temp_tenant)


def test_migration_is_idempotent(temp_tenant):
    bootstrap_tenant(temp_tenant)
    assert migrate_pairing_tables(temp_tenant)["created"] == []


def test_migration_creates_tables_on_a_tenant_that_predates_them(temp_tenant):
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(f'CREATE SCHEMA IF NOT EXISTS "{temp_tenant}"')
        conn.commit()
    finally:
        conn.close()

    assert set(migrate_pairing_tables(temp_tenant)["created"]) == TABLES
    assert TABLES <= _tables(temp_tenant)


def test_a_pair_is_unique_per_type(temp_tenant):
    # The same two products can be both similar and an upsell; they cannot be
    # similar twice. Without pair_type in the key, one type would overwrite
    # another and the graph would silently lose edges.
    bootstrap_tenant(temp_tenant)
    ins = ("INSERT INTO {S}.strategist_product_neighbors "
           "(anchor_key, neighbor_key, pair_type, score, confidence, source) "
           "VALUES ('a','b',%s,0.5,0.5,'embedding')")
    _exec(temp_tenant, ins, ("similar",))
    _exec(temp_tenant, ins, ("upsell",))

    with pytest.raises(Exception):
        _exec(temp_tenant, ins, ("similar",))


def test_a_decision_is_unique_per_pair_and_type(temp_tenant):
    bootstrap_tenant(temp_tenant)
    ins = ("INSERT INTO {S}.strategist_pairing_decisions "
           "(anchor_key, neighbor_key, pair_type, decision) "
           "VALUES ('a','b','similar',%s)")
    _exec(temp_tenant, ins, ("approved",))

    with pytest.raises(Exception):
        _exec(temp_tenant, ins, ("rejected",))


def test_an_embedding_row_is_keyed_by_product(temp_tenant):
    bootstrap_tenant(temp_tenant)
    ins = ("INSERT INTO {S}.strategist_product_embeddings "
           "(product_key, content_hash, vector) VALUES ('a','h',%s)")
    _exec(temp_tenant, ins, ([0.1, 0.2],))

    with pytest.raises(Exception):
        _exec(temp_tenant, ins, ([0.3, 0.4],))