//! The two schemas agree (#211): every table and column SQLite's
//! migrations make, `migrations-pg` makes too, so a change written for one
//! engine and not the other fails here. Postgres builds only, as the
//! Postgres tests run (`OXYHUB_DATABASE_URL`, `OXYHUB_DATABASE_SCHEMA=*`).
#![cfg(feature = "postgres")]

use oxygenhub::config::Config;
use std::collections::{BTreeMap, BTreeSet};

type Tables = BTreeMap<String, BTreeSet<String>>;

/// The full-text index is each engine's own (FTS5's shadow tables, a
/// generated tsvector), and the migrations' ledger is sqlx's.
fn compared(table: &str) -> bool {
    !table.starts_with("search") && !table.starts_with("_sqlx")
}

async fn sqlite() -> Tables {
    use sqlx::Connection;
    let mut conn = sqlx::SqliteConnection::connect("sqlite::memory:").await.unwrap();
    sqlx::migrate!("./migrations").run(&mut conn).await.unwrap();
    let names: Vec<String> =
        sqlx::query_scalar("SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%'").fetch_all(&mut conn).await.unwrap();
    let mut out = Tables::new();
    for t in names.into_iter().filter(|t| compared(t)) {
        let cols: Vec<String> =
            sqlx::query_scalar(&format!("SELECT name FROM pragma_table_info('{t}')")).fetch_all(&mut conn).await.unwrap();
        out.insert(t, cols.into_iter().collect());
    }
    out
}

async fn postgres() -> Tables {
    let dir = tempfile::tempdir().unwrap();
    let config = Config::load(dir.path()).unwrap();
    let pool = oxygenhub::db::open(&config).await.unwrap();
    let rows: Vec<(String, String)> = sqlx::query_as(
        "SELECT CAST(t.relname AS TEXT), CAST(a.attname AS TEXT) FROM pg_attribute a JOIN pg_class t ON t.oid = a.attrelid \
         WHERE t.relkind = 'r' AND t.relnamespace = CAST(current_schema() AS regnamespace) AND a.attnum > 0 AND NOT a.attisdropped",
    )
    .fetch_all(&pool)
    .await
    .unwrap();
    let mut out = Tables::new();
    for (t, c) in rows.into_iter().filter(|(t, _)| compared(t)) {
        out.entry(t).or_default().insert(c);
    }
    out
}

#[tokio::test]
async fn the_postgres_schema_has_every_table_and_column_sqlite_has() {
    let (lite, pg) = (sqlite().await, postgres().await);
    let only = |a: &Tables, b: &Tables| -> Vec<String> {
        a.iter()
            .flat_map(|(t, cols)| match b.get(t) {
                None => vec![format!("table {t}")],
                Some(other) => cols.difference(other).map(|c| format!("{t}.{c}")).collect(),
            })
            .collect()
    };
    assert_eq!(only(&lite, &pg), Vec::<String>::new(), "in SQLite's migrations but not migrations-pg");
    assert_eq!(only(&pg, &lite), Vec::<String>::new(), "in migrations-pg but not SQLite's migrations");
}