//! 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");

}

/// A SQLite hub with a few of each kind of value, copied into Postgres.

#[tokio::test]

async fn a_sqlite_hub_copies_into_postgres_ids_flags_forks_and_all() {

    use sqlx::Connection;

    let old = tempfile::tempdir().unwrap();

    let path = old.path().join("hub.db");

    let options = sqlx::sqlite::SqliteConnectOptions::new().filename(&path).create_if_missing(true);

    let mut lite = sqlx::SqliteConnection::connect_with(&options).await.unwrap();

    sqlx::migrate!("./migrations").run(&mut lite).await.unwrap();

    for sql in [

        "INSERT INTO accounts (id, username, email, password_hash, session_salt, is_admin, created_ms) VALUES (7, 'Andy', '[email protected]', 'h', 's', 1, 5)",

        "INSERT INTO accounts (id, username, email, password_hash, session_salt, banned, created_ms) VALUES (9, 'bob', '[email protected]', 'h', 's', 1, 6)",

        // The fork comes first: its parent is filled in after.

        "INSERT INTO repositories (id, owner, name, created_ms, updated_ms) VALUES (4, 'andy', 'game', 1, 1)",

        "INSERT INTO repositories (id, owner, name, created_ms, updated_ms, archived) VALUES (3, 'bob', 'engine', 1, 1, 1)",

        "UPDATE repositories SET forked_from = 3 WHERE id = 4",

        "INSERT INTO flow_secrets (scope, scope_id, name, value, updated_ms) VALUES ('repo', 3, 'KEY', X'00FF10', 1)",

        "INSERT INTO search (kind, repo_id, target_id, number, title, body) VALUES ('issue', 3, 1, 1, 'Sprites flicker', 'the sprites flicker on level two')",

    ] {

        sqlx::query(sql).execute(&mut lite).await.unwrap();

    }

    lite.close().await.unwrap();

    let dir = tempfile::tempdir().unwrap();

    let config = Config::load(dir.path()).unwrap();

    let pool = oxygenhub::db::open(&config).await.unwrap();

    let copied = oxygenhub::db::copy::from_sqlite(&pool, &path, false).await.map_err(|e| format!("{e:#}")).unwrap();

    let count = |t: &str| copied.iter().find(|(c, _)| c == t).map(|(_, n)| *n);

    assert_eq!((count("accounts"), count("repositories"), count("search")), (Some(2), Some(2), Some(1)));

    let flags: Vec<(String, bool, bool)> =

        sqlx::query_as("SELECT username, is_admin, banned FROM accounts WHERE username = 'andy' OR username = 'BOB' ORDER BY id")

            .fetch_all(&pool)

            .await

            .unwrap();

    assert_eq!(flags, [("Andy".to_string(), true, false), ("bob".to_string(), false, true)], "flags, and names without case");

    let game = oxygenhub::db::repos::get(&pool, "andy", "game").await.unwrap().unwrap();

    assert_eq!((game.id, game.forked_from), (4, Some(3)));

    let secret: Vec<u8> = sqlx::query_scalar("SELECT value FROM flow_secrets WHERE name = 'KEY'").fetch_one(&pool).await.unwrap();

    assert_eq!(secret, [0x00, 0xff, 0x10]);

    let hits = oxygenhub::db::notify::search(&pool, "flickering sprite", None, 5).await.unwrap();

    assert_eq!(hits.iter().map(|h| h.title.as_str()).collect::<Vec<_>>(), ["Sprites flicker"]);

    // New rows take ids after the copied ones.

    let next = oxygenhub::db::repos::create(&pool, "andy", "next", "", hub_api::Visibility::Public).await.unwrap();

    assert_eq!(next.id, 5);

    // Never into a database with rows.

    let again = oxygenhub::db::copy::from_sqlite(&pool, &path, false).await.unwrap_err().to_string();

    assert!(again.contains("already has rows"), "{again}");

    // With --replace: the rows here (the "next" repository) are emptied and

    // the copy is whole again, ids from the start.

    let again = oxygenhub::db::copy::from_sqlite(&pool, &path, true).await.map_err(|e| format!("{e:#}")).unwrap();

    assert_eq!(again.iter().find(|(t, _)| t == "repositories").map(|(_, n)| *n), Some(2));

    assert!(oxygenhub::db::repos::get(&pool, "andy", "next").await.unwrap().is_none());

    let after = oxygenhub::db::repos::create(&pool, "andy", "after", "", hub_api::Visibility::Public).await.unwrap();

    assert_eq!(after.id, 5);

}