use "../std/db/core"
use "../std/db/query"
use "../std/db/postgres"
use "../std/pg/uri"

start:
    let database_url: Text = env_get("DATABASE_URL")
    assert database_url != ""
    assert pg_uri_configure_environment(database_url) == 0

    let host: Text = env_get("PGHOST")
    let port: Number = number_parse(env_get("PGPORT"))
    let username: Text = env_get("PGUSER")
    let database: Text = env_get("PGDATABASE")
    let password: Text = env_get("PGPASSWORD")
    let connected: Result[Number, Text] = pg_connect(host, port, username, database, password, 5000)
    assert result_is_ok(connected)
    let handle: Number = result_unwrap(connected)

    # A temporary table shadows the API table, keeping this fixture isolated.
    let created: Result[PgQueryResult, Text] = pg_query(handle, "CREATE TEMP TABLE yaza_api_records (resource TEXT NOT NULL, id TEXT NOT NULL) ON COMMIT PRESERVE ROWS", 5000)
    assert result_is_ok(created)

    let first_parameters: PgParameters = pg_parameters_new()
    assert result_is_ok(pg_parameters_add_text(first_parameters, "/query-smoke"))
    assert result_is_ok(pg_parameters_add_text(first_parameters, "alpha"))
    let first_insert: Result[PgQueryResult, Text] = pg_query_with_parameters(handle, "INSERT INTO yaza_api_records(resource, id) VALUES ($1, $2)", first_parameters, 5000)
    assert result_is_ok(first_insert)

    let second_parameters: PgParameters = pg_parameters_new()
    assert result_is_ok(pg_parameters_add_text(second_parameters, "/query-smoke"))
    assert result_is_ok(pg_parameters_add_text(second_parameters, "beta"))
    let second_insert: Result[PgQueryResult, Text] = pg_query_with_parameters(handle, "INSERT INTO yaza_api_records(resource, id) VALUES ($1, $2)", second_parameters, 5000)
    assert result_is_ok(second_insert)

    let columns: TextList = text_list()
    list_push(columns, "id")
    let base: Result[DbSelect, DbError] = db_select_new("yaza_api_records", columns)
    assert result_is_ok(base)
    let filtered: Result[DbSelect, DbError] = db_select_where(result_unwrap(base), "resource", DbComparison::Equal, DbValue::TextValue("/query-smoke"))
    assert result_is_ok(filtered)
    let narrowed: Result[DbSelect, DbError] = db_select_where(result_unwrap(filtered), "id", DbComparison::GreaterThan, DbValue::TextValue("alpha"))
    assert result_is_ok(narrowed)
    let ordered: Result[DbSelect, DbError] = db_select_order_by(result_unwrap(narrowed), "id", false)
    assert result_is_ok(ordered)
    let paged: Result[DbSelect, DbError] = db_select_page(result_unwrap(ordered), 10, 0)
    assert result_is_ok(paged)

    let selected: Result[PgQueryResult, DbError] = db_postgres_select(handle, result_unwrap(paged), 5000)
    assert result_is_ok(selected)
    let result: PgQueryResult = result_unwrap(selected)
    assert result.row_count == 1
    assert pg_query_column_count(result) == 1
    assert list_get(result.column_metadata, 0) == "id"
    let first_id: Result[Option[Text], Text] = pg_query_text(result, 0, 0)
    assert result_is_ok(first_id)
    assert option_is_some(result_unwrap(first_id))
    assert option_unwrap(result_unwrap(first_id)) == "beta"

    # Injection-shaped runtime values remain data in the bound predicate.
    let hostile: Result[DbSelect, DbError] = db_select_where(result_unwrap(base), "resource", DbComparison::Equal, DbValue::TextValue("/query-smoke' OR 1=1 --"))
    assert result_is_ok(hostile)
    let hostile_selected: Result[PgQueryResult, DbError] = db_postgres_select(handle, result_unwrap(hostile), 5000)
    assert result_is_ok(hostile_selected)
    assert result_unwrap(hostile_selected).row_count == 0

    assert pg_disconnect(handle) == 0
    say "Live PostgreSQL structured-query execution passed."