kasl-server 0.2.0

//! Exercises the migrations against a real PostgreSQL.
//!
//! The schema is a contract with two parties that cannot both be checked by
//! reading the SQL: kasl agents upload into it, and every dashboard reads out
//! of it. These tests assert the guarantees that upload depends on - one day
//! per person per date, a re-upload landing on the same row, a departed
//! employee taking their rows with them - by running them.
//!
//! Skipped unless `DATABASE_URL` is set, so `cargo test` stays green on a
//! machine without a database; CI runs them with a Postgres service.

use sqlx::{AssertSqlSafe, Connection, Executor, PgConnection, PgPool, postgres::PgPoolOptions};
use uuid::Uuid;

/// Connection string of a throwaway database, or `None` when the environment
/// offers no server to create one on.
struct TestDb {
    admin_url: String,
    name: String,
    pool: PgPool,
}

impl TestDb {
    /// Creates a database of its own and migrates it.
    ///
    /// A shared database would make these tests order-dependent: they assert on
    /// uniqueness and on cascade deletes, which leftover rows from a neighbour
    /// would break.
    async fn create() -> Option<Self> {
        let admin_url = std::env::var("DATABASE_URL").ok()?;
        let name = format!("kasl_test_{}", Uuid::new_v4().simple());

        let mut admin = PgConnection::connect(&admin_url).await.expect("DATABASE_URL is set but not reachable");
        // `CREATE DATABASE` takes no bind parameters, so the name is
        // interpolated. It is a literal prefix plus a generated UUID, never
        // anything from outside this test.
        admin
            .execute(AssertSqlSafe(format!(r#"CREATE DATABASE "{name}""#)))
            .await
            .expect("failed to create the test database");
        admin.close().await.ok();

        let pool = PgPoolOptions::new()
            .max_connections(2)
            .connect(&replace_database(&admin_url, &name))
            .await
            .expect("failed to connect to the test database");
        sqlx::migrate!().run(&pool).await.expect("migrations failed");

        Some(Self { admin_url, name, pool })
    }

    async fn drop(self) {
        let Self { admin_url, name, pool } = self;
        pool.close().await;
        if let Ok(mut admin) = PgConnection::connect(&admin_url).await {
            // WITH (FORCE) so a lingering connection cannot leave the database
            // behind on the developer's server.
            let _ = admin.execute(AssertSqlSafe(format!(r#"DROP DATABASE IF EXISTS "{name}" WITH (FORCE)"#))).await;
            admin.close().await.ok();
        }
    }
}

/// Swaps the database name in a connection string, keeping credentials, host
/// and query parameters (`sslmode` and friends) intact.
fn replace_database(url: &str, database: &str) -> String {
    let (prefix, rest) = url.split_once("://").expect("DATABASE_URL must be a URL");
    let (authority, path) = rest.split_once('/').unwrap_or((rest, ""));
    let query = path.split_once('?').map(|(_, q)| format!("?{q}")).unwrap_or_default();
    format!("{prefix}://{authority}/{database}{query}")
}

/// Runs `test` against a freshly migrated database, or skips when there is none.
async fn with_db<F, Fut>(test: F)
where
    F: FnOnce(PgPool) -> Fut,
    Fut: Future<Output = ()>,
{
    let Some(db) = TestDb::create().await else {
        eprintln!("skipped: DATABASE_URL is not set");
        return;
    };
    let pool = db.pool.clone();
    test(pool).await;
    db.drop().await;
}

/// Inserts a user and returns its id.
async fn insert_user(pool: &PgPool, email: &str) -> Uuid {
    sqlx::query_scalar("INSERT INTO users (email, display_name) VALUES ($1, $2) RETURNING id")
        .bind(email)
        .bind("Test Person")
        .fetch_one(pool)
        .await
        .expect("failed to insert a user")
}

async fn insert_workday(pool: &PgPool, user: Uuid, date: &str) -> Result<Uuid, sqlx::Error> {
    sqlx::query_scalar("INSERT INTO workdays (user_id, date, started_at) VALUES ($1, $2::date, $2::date + time '09:00' AT TIME ZONE 'UTC') RETURNING id")
        .bind(user)
        .bind(date)
        .fetch_one(pool)
        .await
}

#[tokio::test]
async fn migrations_apply_and_are_idempotent() {
    with_db(|pool| async move {
        // A second run must be a no-op: the server migrates on every startup.
        sqlx::migrate!().run(&pool).await.expect("re-running migrations must be a no-op");

        let tables: Vec<String> = sqlx::query_scalar("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_name")
            .fetch_all(&pool)
            .await
            .expect("failed to list tables");

        for expected in ["agents", "pauses", "reports", "tags", "task_tags", "tasks", "users", "workdays"] {
            assert!(tables.iter().any(|t| t == expected), "table `{expected}` is missing; got {tables:?}");
        }
    })
    .await;
}

#[tokio::test]
async fn a_person_has_one_workday_per_date() {
    with_db(|pool| async move {
        let user = insert_user(&pool, "one-day@example.test").await;
        insert_workday(&pool, user, "2026-08-14").await.expect("the first workday should insert");

        let duplicate = insert_workday(&pool, user, "2026-08-14").await;
        assert!(duplicate.is_err(), "a second workday on the same date must be rejected");

        // The same date for a different person is a different day.
        let colleague = insert_user(&pool, "colleague@example.test").await;
        insert_workday(&pool, colleague, "2026-08-14")
            .await
            .expect("another person's day on the same date must be allowed");
    })
    .await;
}

#[tokio::test]
async fn emails_are_unique_regardless_of_case() {
    with_db(|pool| async move {
        insert_user(&pool, "Person@Example.test").await;

        let duplicate = sqlx::query("INSERT INTO users (email, display_name) VALUES ($1, $2)")
            .bind("person@example.test")
            .bind("Impostor")
            .execute(&pool)
            .await;
        assert!(duplicate.is_err(), "logins must not differ only by letter case");
    })
    .await;
}

#[tokio::test]
async fn an_upload_of_the_same_task_updates_one_row() {
    with_db(|pool| async move {
        let user = insert_user(&pool, "tasks@example.test").await;

        // The agent identifies a task by its own row id; a re-upload of a day
        // must correct the row it already sent, not add a second one.
        for completeness in [40_i16, 100] {
            sqlx::query(
                "INSERT INTO tasks (user_id, agent_task_id, agent_group_id, date, recorded_at, name, completeness)
                 VALUES ($1, 7, 7, date '2026-08-14', now(), 'Ship the schema', $2)
                 ON CONFLICT (user_id, agent_task_id) DO UPDATE SET completeness = EXCLUDED.completeness",
            )
            .bind(user)
            .bind(completeness)
            .execute(&pool)
            .await
            .expect("upsert should succeed");
        }

        let rows: Vec<(i32,)> = sqlx::query_as("SELECT completeness::int FROM tasks WHERE user_id = $1")
            .bind(user)
            .fetch_all(&pool)
            .await
            .expect("failed to read tasks");
        assert_eq!(rows, vec![(100,)], "the re-upload should have updated the single row");
    })
    .await;
}

#[tokio::test]
async fn a_workday_owns_its_pauses() {
    with_db(|pool| async move {
        let user = insert_user(&pool, "pauses@example.test").await;
        let workday = insert_workday(&pool, user, "2026-08-14").await.expect("workday");

        sqlx::query("INSERT INTO pauses (workday_id, started_at, ended_at, duration_seconds) VALUES ($1, now(), now() + interval '20 minutes', 1200)")
            .bind(workday)
            .execute(&pool)
            .await
            .expect("failed to insert a pause");

        // Deleting the day takes its pauses; a pause without a day is not a
        // state any reader can make sense of.
        sqlx::query("DELETE FROM workdays WHERE id = $1")
            .bind(workday)
            .execute(&pool)
            .await
            .expect("failed to delete the workday");

        let left: i64 = sqlx::query_scalar("SELECT count(*) FROM pauses")
            .fetch_one(&pool)
            .await
            .expect("failed to count pauses");
        assert_eq!(left, 0, "pauses must not outlive their workday");
    })
    .await;
}

#[tokio::test]
async fn deleting_a_user_takes_their_data() {
    with_db(|pool| async move {
        let user = insert_user(&pool, "departed@example.test").await;
        let workday = insert_workday(&pool, user, "2026-08-14").await.expect("workday");
        sqlx::query("INSERT INTO pauses (workday_id, started_at) VALUES ($1, now())")
            .bind(workday)
            .execute(&pool)
            .await
            .expect("pause");
        sqlx::query("INSERT INTO agents (user_id, name, token_hash) VALUES ($1, 'laptop', 'hash')")
            .bind(user)
            .execute(&pool)
            .await
            .expect("agent");
        sqlx::query("INSERT INTO reports (user_id, kind, period_start, submitted_at) VALUES ($1, 'daily', date '2026-08-14', now())")
            .bind(user)
            .execute(&pool)
            .await
            .expect("report");

        sqlx::query("DELETE FROM users WHERE id = $1")
            .bind(user)
            .execute(&pool)
            .await
            .expect("failed to delete the user");

        for (table, sql) in [
            ("workdays", "SELECT count(*) FROM workdays"),
            ("pauses", "SELECT count(*) FROM pauses"),
            ("agents", "SELECT count(*) FROM agents"),
            ("reports", "SELECT count(*) FROM reports"),
        ] {
            let left: i64 = sqlx::query_scalar(sql).fetch_one(&pool).await.expect("failed to count rows");
            assert_eq!(left, 0, "`{table}` still holds rows of a deleted user");
        }
    })
    .await;
}

#[tokio::test]
async fn impossible_intervals_are_rejected() {
    with_db(|pool| async move {
        let user = insert_user(&pool, "checks@example.test").await;
        let workday = insert_workday(&pool, user, "2026-08-14").await.expect("workday");

        let backwards = sqlx::query("UPDATE workdays SET ended_at = started_at - interval '1 hour' WHERE id = $1")
            .bind(workday)
            .execute(&pool)
            .await;
        assert!(backwards.is_err(), "a day cannot end before it starts");

        let negative = sqlx::query("INSERT INTO pauses (workday_id, started_at, duration_seconds) VALUES ($1, now(), -1)")
            .bind(workday)
            .execute(&pool)
            .await;
        assert!(negative.is_err(), "a pause cannot last a negative time");

        let over_complete = sqlx::query(
            "INSERT INTO tasks (user_id, agent_task_id, agent_group_id, date, recorded_at, name, completeness)
             VALUES ($1, 1, 1, date '2026-08-14', now(), 'Overachieve', 101)",
        )
        .bind(user)
        .execute(&pool)
        .await;
        assert!(over_complete.is_err(), "completeness above 100 must be rejected");
    })
    .await;
}

#[tokio::test]
async fn updated_at_follows_the_row() {
    with_db(|pool| async move {
        let user = insert_user(&pool, "touched@example.test").await;

        let before: (chrono::DateTime<chrono::Utc>, chrono::DateTime<chrono::Utc>) = sqlx::query_as("SELECT created_at, updated_at FROM users WHERE id = $1")
            .bind(user)
            .fetch_one(&pool)
            .await
            .expect("failed to read timestamps");
        assert_eq!(before.0, before.1, "a fresh row should carry equal timestamps");

        sqlx::query("UPDATE users SET display_name = 'Renamed' WHERE id = $1")
            .bind(user)
            .execute(&pool)
            .await
            .expect("failed to update");

        let after: (chrono::DateTime<chrono::Utc>, chrono::DateTime<chrono::Utc>) = sqlx::query_as("SELECT created_at, updated_at FROM users WHERE id = $1")
            .bind(user)
            .fetch_one(&pool)
            .await
            .expect("failed to read timestamps");
        assert_eq!(after.0, before.0, "created_at must not move");
        assert!(after.1 > before.1, "updated_at must advance on write");
    })
    .await;
}