//! Schema installation and seed tests for the `postgres` feature.
//!
//! Tests exercise `install_schema()` and `seed_default_node()` helpers
//! against a real Postgres instance, verifying that the DDL is idempotent
//! and that the seed creates the expected default node row.
//!
//! Requires a running Postgres instance reachable via the `DATABASE_URL`
//! environment variable. If unset, tests are skipped (printed to stderr).
//! The test suite uses `tokio::test` for async execution and `tokio-postgres`
//! with `NoTls` for the connection.
//!
//! Tests are compiled only when the `postgres` feature is enabled.

#![cfg(feature = "postgres")]

use std::env;
use tokio_postgres::NoTls;

async fn connect() -> Option<tokio_postgres::Client> {
    let url = env::var("DATABASE_URL").ok()?;
    let (client, conn) = tokio_postgres::connect(&url, NoTls).await.ok()?;
    tokio::spawn(async move {
        if let Err(e) = conn.await {
            eprintln!("postgres connection error: {e}");
        }
    });
    Some(client)
}

// ---------------------------------------------------------------------------
// Schema installation
// ---------------------------------------------------------------------------

#[tokio::test]
async fn install_schema_creates_tables() {
    let Some(client) = connect().await else {
        eprintln!("SKIP: DATABASE_URL not set; skipping live database test");
        return;
    };

    // Create an isolated schema for testing.
    let schema_name = "test_heeranjid_install";
    client
        .execute(&format!("DROP SCHEMA IF EXISTS {schema_name} CASCADE"), &[])
        .await
        .expect("drop test schema");
    client
        .execute(&format!("CREATE SCHEMA {schema_name}"), &[])
        .await
        .expect("create test schema");

    // Set search_path so subsequent DDL lands in our isolated schema.
    client
        .execute(&format!("SET search_path TO {schema_name}"), &[])
        .await
        .expect("set search_path");

    // Run install_schema.
    heeranjid::postgres_schema::install_schema(&client)
        .await
        .expect("install_schema should succeed");

    // Verify core tables exist.
    let tables: Vec<String> = client
        .query_opt(
            "SELECT tablename FROM pg_tables WHERE schemaname = $1 AND tablename = 'heer_nodes'",
            &[&schema_name],
        )
        .await
        .expect("query pg_tables")
        .iter()
        .map(|row| row.get(0))
        .collect();

    assert!(
        !tables.is_empty(),
        "heer_nodes table should exist after install"
    );

    // Re-run install_schema to verify idempotency.
    heeranjid::postgres_schema::install_schema(&client)
        .await
        .expect("install_schema should be idempotent");

    // Cleanup.
    client
        .execute(&format!("DROP SCHEMA {schema_name} CASCADE"), &[])
        .await
        .expect("drop test schema");
}

// ---------------------------------------------------------------------------
// Seed installation
// ---------------------------------------------------------------------------

#[tokio::test]
async fn seed_default_node_creates_row() {
    let Some(client) = connect().await else {
        eprintln!("SKIP: DATABASE_URL not set; skipping live database test");
        return;
    };

    // Create an isolated schema for testing.
    let schema_name = "test_heeranjid_seed";
    client
        .execute(&format!("DROP SCHEMA IF EXISTS {schema_name} CASCADE"), &[])
        .await
        .expect("drop test schema");
    client
        .execute(&format!("CREATE SCHEMA {schema_name}"), &[])
        .await
        .expect("create test schema");

    // Set search_path so subsequent DDL lands in our isolated schema.
    client
        .execute(&format!("SET search_path TO {schema_name}"), &[])
        .await
        .expect("set search_path");

    // Install schema first.
    heeranjid::postgres_schema::install_schema(&client)
        .await
        .expect("install_schema");

    // Seed default node.
    heeranjid::postgres_schema::seed_default_node(&client)
        .await
        .expect("seed_default_node should succeed");

    // Verify default node (node_id = 1) exists.
    let count: i64 = client
        .query_one("SELECT count(*) FROM heer_nodes WHERE node_id = 1", &[])
        .await
        .expect("query heer_nodes")
        .get(0);

    assert_eq!(
        count, 1,
        "default node (node_id = 1) should exist after seed"
    );

    // Re-run seed to verify idempotency.
    heeranjid::postgres_schema::seed_default_node(&client)
        .await
        .expect("seed_default_node should be idempotent");

    // Verify count is still 1 (not duplicated).
    let count_after_reseed: i64 = client
        .query_one("SELECT count(*) FROM heer_nodes WHERE node_id = 1", &[])
        .await
        .expect("query heer_nodes after reseed")
        .get(0);

    assert_eq!(
        count_after_reseed, 1,
        "default node should not be duplicated on re-seed"
    );

    // Cleanup.
    client
        .execute(&format!("DROP SCHEMA {schema_name} CASCADE"), &[])
        .await
        .expect("drop test schema");
}

// ---------------------------------------------------------------------------
// Desc flip round-trip (install_all_desc_support)
// ---------------------------------------------------------------------------

#[tokio::test]
async fn desc_flip_round_trips_inside_postgres() {
    let Some(client) = connect().await else {
        eprintln!("SKIP: DATABASE_URL not set; skipping live database test");
        return;
    };

    // Create an isolated schema for testing.
    let schema_name = "test_heeranjid_desc_flip";
    client
        .execute(&format!("DROP SCHEMA IF EXISTS {schema_name} CASCADE"), &[])
        .await
        .expect("drop test schema");
    client
        .execute(&format!("CREATE SCHEMA {schema_name}"), &[])
        .await
        .expect("create test schema");

    // Set search_path so subsequent DDL lands in our isolated schema.
    client
        .execute(&format!("SET search_path TO {schema_name}"), &[])
        .await
        .expect("set search_path");

    // Install schema, seed, and all desc support.
    heeranjid::postgres_schema::install_schema(&client)
        .await
        .expect("install_schema");
    heeranjid::postgres_schema::seed_default_node(&client)
        .await
        .expect("seed_default_node");
    heeranjid::postgres_schema::install_all_desc_support(&client)
        .await
        .expect("install_all_desc_support");

    // heerid_to_asc(heerid_to_desc(1234567)) must round-trip.
    let row = client
        .query_one("SELECT heerid_to_asc(heerid_to_desc(1234567::bigint))", &[])
        .await
        .expect("round-trip query");
    let back: i64 = row.get(0);
    assert_eq!(back, 1_234_567, "heerid_to_asc(heerid_to_desc(x)) == x");

    // heerid_flip_mask() must equal the documented constant.
    let row = client
        .query_one("SELECT heerid_flip_mask()", &[])
        .await
        .expect("flip mask query");
    let mask: i64 = row.get(0);
    assert_eq!(
        mask, 9_223_372_036_850_589_695,
        "heerid_flip_mask() == documented constant"
    );

    // Cleanup.
    client
        .execute(&format!("DROP SCHEMA {schema_name} CASCADE"), &[])
        .await
        .expect("drop test schema");
}

// ---------------------------------------------------------------------------
// ID generation post-seed
// ---------------------------------------------------------------------------

#[tokio::test]
async fn generate_id_after_seed() {
    let Some(client) = connect().await else {
        eprintln!("SKIP: DATABASE_URL not set; skipping live database test");
        return;
    };

    // Create an isolated schema for testing.
    let schema_name = "test_heeranjid_genid";
    client
        .execute(&format!("DROP SCHEMA IF EXISTS {schema_name} CASCADE"), &[])
        .await
        .expect("drop test schema");
    client
        .execute(&format!("CREATE SCHEMA {schema_name}"), &[])
        .await
        .expect("create test schema");

    // Set search_path so subsequent DDL lands in our isolated schema.
    client
        .execute(&format!("SET search_path TO {schema_name}"), &[])
        .await
        .expect("set search_path");

    // Install schema and seed.
    heeranjid::postgres_schema::install_schema(&client)
        .await
        .expect("install_schema");
    heeranjid::postgres_schema::seed_default_node(&client)
        .await
        .expect("seed_default_node");

    // Set session node_id for generation.
    client
        .execute("SELECT set_heer_node_id(1)", &[])
        .await
        .expect("set session node_id");

    // Generate an ID.
    let id: i64 = client
        .query_one("SELECT generate_id()", &[])
        .await
        .expect("generate_id")
        .get(0);

    assert!(id > 0, "generated ID should be positive");

    // Cleanup.
    client
        .execute(&format!("DROP SCHEMA {schema_name} CASCADE"), &[])
        .await
        .expect("drop test schema");
}

// ---------------------------------------------------------------------------
// Per-table autofill trigger (Task 11)
// ---------------------------------------------------------------------------

#[tokio::test]
async fn autofill_trigger_populates_desc_column_on_insert_and_update() {
    let Some(client) = connect().await else {
        eprintln!("SKIP: DATABASE_URL not set; skipping live database test");
        return;
    };

    // Create an isolated schema for testing.
    let schema_name = "test_heeranjid_autofill_trigger";
    client
        .execute(&format!("DROP SCHEMA IF EXISTS {schema_name} CASCADE"), &[])
        .await
        .expect("drop test schema");
    client
        .execute(&format!("CREATE SCHEMA {schema_name}"), &[])
        .await
        .expect("create test schema");

    // Pin search_path so all DDL and the trigger body resolve here.
    client
        .execute(&format!("SET search_path TO {schema_name}"), &[])
        .await
        .expect("set search_path");

    // Install schema + all desc support (flip fns are what the trigger calls).
    heeranjid::postgres_schema::install_schema(&client)
        .await
        .expect("install_schema");
    heeranjid::postgres_schema::seed_default_node(&client)
        .await
        .expect("seed_default_node");
    heeranjid::postgres_schema::install_all_desc_support(&client)
        .await
        .expect("install_all_desc_support");

    // Fixture table: plain int64 pk + a sibling desc column.
    client
        .batch_execute("CREATE TABLE trig_test (id bigint PRIMARY KEY, id_desc bigint)")
        .await
        .expect("create trig_test");

    // Install the per-table trigger (single pair, Heer kind).
    heeranjid::postgres_schema::install_autofill_trigger_for_table(
        &client,
        "trig_test",
        &[heeranjid::postgres_schema::ColumnPair {
            src: "id",
            dst: "id_desc",
        }],
        heeranjid::postgres_schema::IdKind::Heer,
    )
    .await
    .expect("install_autofill_trigger_for_table");

    // INSERT without populating id_desc — trigger must fill it.
    client
        .execute("INSERT INTO trig_test (id) VALUES ($1)", &[&1000_i64])
        .await
        .expect("insert row");

    let expected: i64 = client
        .query_one("SELECT heerid_to_desc($1::bigint)", &[&1000_i64])
        .await
        .expect("expected id_desc for 1000")
        .get(0);
    let got: i64 = client
        .query_one("SELECT id_desc FROM trig_test WHERE id = $1", &[&1000_i64])
        .await
        .expect("read id_desc after insert")
        .get(0);
    assert_eq!(
        got, expected,
        "INSERT trigger should populate id_desc via heerid_to_desc(id)"
    );

    // UPDATE the source — trigger must recompute id_desc.
    client
        .execute(
            "UPDATE trig_test SET id = $1 WHERE id = $2",
            &[&2000_i64, &1000_i64],
        )
        .await
        .expect("update row");

    let expected2: i64 = client
        .query_one("SELECT heerid_to_desc($1::bigint)", &[&2000_i64])
        .await
        .expect("expected id_desc for 2000")
        .get(0);
    let got2: i64 = client
        .query_one("SELECT id_desc FROM trig_test WHERE id = $1", &[&2000_i64])
        .await
        .expect("read id_desc after update")
        .get(0);
    assert_eq!(
        got2, expected2,
        "UPDATE trigger should recompute id_desc when source changes"
    );

    // Drop the trigger and confirm it's gone.
    heeranjid::postgres_schema::drop_autofill_trigger_for_table(&client, "trig_test")
        .await
        .expect("drop_autofill_trigger_for_table");

    let remaining: i64 = client
        .query_one(
            "SELECT count(*) FROM pg_trigger \
             WHERE tgname = 'zzz_trig_test_autofill_desc' AND NOT tgisinternal",
            &[],
        )
        .await
        .expect("check trigger removal")
        .get(0);
    assert_eq!(remaining, 0, "trigger should be gone after drop helper");

    // After drop, an UPDATE to id must NOT touch id_desc.
    client
        .execute(
            "UPDATE trig_test SET id = $1 WHERE id = $2",
            &[&3000_i64, &2000_i64],
        )
        .await
        .expect("update row post-drop");
    let stale: i64 = client
        .query_one("SELECT id_desc FROM trig_test WHERE id = $1", &[&3000_i64])
        .await
        .expect("read id_desc after post-drop update")
        .get(0);
    assert_eq!(
        stale, expected2,
        "after drop, id_desc must not be recomputed by a trigger"
    );

    // Cleanup.
    client
        .execute(&format!("DROP SCHEMA {schema_name} CASCADE"), &[])
        .await
        .expect("drop test schema");
}
