//! 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', 'a@example.com', 'h', 's', 1, 5)",
        "INSERT INTO accounts (id, username, email, password_hash, session_salt, banned, created_ms) VALUES (9, 'bob', 'b@example.com', '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);
}