//! DB-layer contract tests for the `--seed-examples` flow (`seed::run`, Phases 1-2).
//!
//! These exercise the seed against real Postgres to prove the invariants the
//! example marketplace rests on:
//!
//!   1. It clears the no-real-users guard on a *freshly-migrated* DB (migration
//!      `080_remove_demo_data` leaves no non-example accounts) and creates the
//!      full roster.
//!   2. Every seeded creator+project is publicly visible, `is_sandbox = FALSE`
//!      on the user and `is_public = true` on the project, the only two gates the
//!      discover feed applies. The discover predicate returning them is the proof.
//!   3. Phase-2 content: one item of every `ItemType`, spanning every pricing
//!      kind, with tags and subscription tiers, and all items **hidden**
//!      (`scan_status='pending'`) until Phase 3 attaches media.
//!   4. Idempotency: a second `run()` wipes the prior example data first, so
//!      counts stay fixed instead of doubling.
//!   5. The prod-host guard still refuses even with the opt-in flag set.

use std::sync::Arc;

use crate::harness::db::TestDb;
use crate::harness::storage::InMemoryStorage;
use makenotwork::seed::{self, SeedMedia, SeedOptions};
use makenotwork::storage::StorageBackend;

/// The Phase-1 roster size (see `seed::creators::ROSTER`).
const SEEDED_CREATORS: i64 = 5;
/// Phase-2 item count: one per `ItemType` spread across the projects.
const SEEDED_ITEMS: i64 = 11;

/// Guard-passing options for a test run: opt-in on, approved example host.
fn testnot_opts() -> SeedOptions {
    SeedOptions {
        allow_example_seed: true,
        host_url: "https://testnot.work".to_string(),
    }
}

async fn count_example_creators(pool: &sqlx::PgPool) -> i64 {
    sqlx::query_scalar(
        "SELECT COUNT(*) FROM users \
         WHERE lower(split_part(email, '@', 2)) = 'example.test' AND is_sandbox = FALSE",
    )
    .fetch_one(pool)
    .await
    .expect("count example creators")
}

async fn count_public_example_projects(pool: &sqlx::PgPool) -> i64 {
    sqlx::query_scalar(
        "SELECT COUNT(*) FROM projects p \
         JOIN users u ON u.id = p.user_id \
         WHERE lower(split_part(u.email, '@', 2)) = 'example.test' AND p.is_public = TRUE",
    )
    .fetch_one(pool)
    .await
    .expect("count public example projects")
}

/// Slugs of seeded projects that clear the *exact* public-discover gate
/// (`p.is_public AND u.is_sandbox = FALSE`, per `db::discover::discover_projects`).
/// Mirrors that predicate in SQL because the `discover` module is `pub(crate)` and
/// so unreachable from this external test crate.
async fn discover_visible_example_slugs(pool: &sqlx::PgPool) -> Vec<String> {
    sqlx::query_scalar::<_, String>(
        "SELECT p.slug::text FROM projects p \
         JOIN users u ON u.id = p.user_id \
         WHERE p.is_public = TRUE AND u.is_sandbox = FALSE \
           AND lower(split_part(u.email, '@', 2)) = 'example.test' \
         ORDER BY p.slug",
    )
    .fetch_all(pool)
    .await
    .expect("discover-visible example slugs")
}

/// Total items under the seeded example projects.
async fn count_example_items(pool: &sqlx::PgPool) -> i64 {
    sqlx::query_scalar(
        "SELECT COUNT(*) FROM items i \
         JOIN projects p ON p.id = i.project_id \
         JOIN users u ON u.id = p.user_id \
         WHERE lower(split_part(u.email, '@', 2)) = 'example.test'",
    )
    .fetch_one(pool)
    .await
    .expect("count example items")
}

/// Example items that clear the *exact* item-discover gate. Phase 2 seeds items
/// hidden (`scan_status='pending'`), so this must be zero until Phase 3.
async fn count_discover_visible_example_items(pool: &sqlx::PgPool) -> i64 {
    sqlx::query_scalar(
        "SELECT COUNT(*) FROM items i \
         JOIN projects p ON p.id = i.project_id \
         JOIN users u ON u.id = p.user_id \
         WHERE i.is_public = TRUE AND i.listed = TRUE AND p.is_public = TRUE \
           AND i.scan_status = 'clean' AND u.is_sandbox = FALSE AND i.deleted_at IS NULL \
           AND lower(split_part(u.email, '@', 2)) = 'example.test'",
    )
    .fetch_one(pool)
    .await
    .expect("count discover-visible example items")
}

#[tokio::test]
async fn seeds_publicly_visible_creators_and_projects() {
    let db = TestDb::new().await;

    // Precondition the guard relies on: a freshly-migrated DB holds no real
    // accounts (080_remove_demo_data cleared the 003 demo seed).
    let real: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM users WHERE lower(email) NOT LIKE '%@example.test'",
    )
    .fetch_one(&db.pool)
    .await
    .expect("count real users");
    assert_eq!(real, 0, "a migrated DB should hold no non-example accounts");

    seed::run(&db.pool, &testnot_opts(), &SeedMedia::none())
        .await
        .expect("seed should run on a clean migrated DB");

    // All five creators exist and are non-sandbox (publicly visible), and each
    // owns a public project.
    assert_eq!(count_example_creators(&db.pool).await, SEEDED_CREATORS);
    assert_eq!(
        count_public_example_projects(&db.pool).await,
        SEEDED_CREATORS
    );

    // Public visibility proof: all five projects clear the discover gate.
    let slugs = discover_visible_example_slugs(&db.pool).await;
    assert_eq!(slugs.len() as i64, SEEDED_CREATORS);
    for slug in [
        "restored-reels-vol-1",
        "deskriver-suite",
        "cc0-field-library",
        "the-marginalia-reader",
        "commons-sampler",
    ] {
        assert!(
            slugs.iter().any(|s| s == slug),
            "discover-visible set missing seeded project {slug}"
        );
    }
}

#[tokio::test]
async fn seeds_items_across_types_and_pricing_but_hidden() {
    let db = TestDb::new().await;
    seed::run(&db.pool, &testnot_opts(), &SeedMedia::none())
        .await
        .expect("seed");

    // One item per ItemType (11), and every type represented exactly once.
    assert_eq!(count_example_items(&db.pool).await, SEEDED_ITEMS);
    let distinct_types: i64 = sqlx::query_scalar(
        "SELECT COUNT(DISTINCT i.item_type) FROM items i \
         JOIN projects p ON p.id = i.project_id \
         JOIN users u ON u.id = p.user_id \
         WHERE lower(split_part(u.email, '@', 2)) = 'example.test'",
    )
    .fetch_one(&db.pool)
    .await
    .expect("distinct item types");
    assert_eq!(distinct_types, SEEDED_ITEMS, "every ItemType should appear");

    // Phase-2 boundary: nothing is discover-visible yet (all pending).
    assert_eq!(
        count_discover_visible_example_items(&db.pool).await,
        0,
        "Phase 2 items must stay hidden until Phase 3 attaches media"
    );

    // Pricing kinds are represented: Pwyw (pwyw_enabled), BuyOnce (price > 0),
    // and Free (price 0) items all exist.
    let pwyw: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM items i JOIN projects p ON p.id = i.project_id \
         JOIN users u ON u.id = p.user_id \
         WHERE lower(split_part(u.email,'@',2))='example.test' AND i.pwyw_enabled = TRUE",
    )
    .fetch_one(&db.pool)
    .await
    .expect("pwyw count");
    assert!(pwyw >= 1, "expected Pwyw items");
    let buyonce: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM items i JOIN projects p ON p.id = i.project_id \
         JOIN users u ON u.id = p.user_id \
         WHERE lower(split_part(u.email,'@',2))='example.test' \
           AND i.pwyw_enabled = FALSE AND i.price_cents > 0",
    )
    .fetch_one(&db.pool)
    .await
    .expect("buyonce count");
    assert!(buyonce >= 1, "expected BuyOnce items");

    // The subscription project has its pricing_model set and >= 2 tiers.
    let sub_tiers: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM subscription_tiers st JOIN projects p ON p.id = st.project_id \
         WHERE p.slug = 'the-marginalia-reader' AND p.pricing_model = 'subscription'",
    )
    .fetch_one(&db.pool)
    .await
    .expect("subscription tiers");
    assert!(
        sub_tiers >= 2,
        "subscription project should have >= 2 tiers, got {sub_tiers}"
    );

    // Every item is tagged (for the browse/filter surfaces), no example item
    // lacks a tag, which would mean a roster slug failed to resolve.
    let untagged: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM items i \
         JOIN projects p ON p.id = i.project_id JOIN users u ON u.id = p.user_id \
         WHERE lower(split_part(u.email,'@',2))='example.test' \
           AND NOT EXISTS (SELECT 1 FROM item_tags it WHERE it.item_id = i.id)",
    )
    .fetch_one(&db.pool)
    .await
    .expect("untagged item count");
    assert_eq!(
        untagged, 0,
        "every seeded item should have at least one tag"
    );
}

#[tokio::test]
async fn seed_is_idempotent() {
    let db = TestDb::new().await;

    seed::run(&db.pool, &testnot_opts(), &SeedMedia::none())
        .await
        .expect("first seed");
    seed::run(&db.pool, &testnot_opts(), &SeedMedia::none())
        .await
        .expect("second seed");

    // Re-running wipes prior example data first (cascading to projects + items),
    // so counts stay fixed instead of doubling.
    assert_eq!(count_example_creators(&db.pool).await, SEEDED_CREATORS);
    assert_eq!(
        count_public_example_projects(&db.pool).await,
        SEEDED_CREATORS
    );
    assert_eq!(count_example_items(&db.pool).await, SEEDED_ITEMS);
}

#[tokio::test]
async fn refuses_on_prod_host() {
    let db = TestDb::new().await;

    let err = seed::run(
        &db.pool,
        &SeedOptions {
            allow_example_seed: true,
            host_url: "https://makenot.work".to_string(),
        },
        &SeedMedia::none(),
    )
    .await
    .expect_err("prod host must be refused");
    assert!(matches!(err, seed::SeedError::ProdHost(_)));

    // Nothing was created, and the demo account is untouched.
    assert_eq!(count_example_creators(&db.pool).await, 0);
}

#[tokio::test]
async fn seeds_blog_posts_and_follows() {
    let db = TestDb::new().await;
    seed::run(&db.pool, &testnot_opts(), &SeedMedia::none())
        .await
        .expect("seed");

    // Two published posts per project (10), all published.
    let posts: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM blog_posts bp JOIN projects p ON p.id = bp.project_id \
         JOIN users u ON u.id = p.user_id \
         WHERE lower(split_part(u.email,'@',2))='example.test' AND bp.published_at IS NOT NULL",
    )
    .fetch_one(&db.pool)
    .await
    .expect("published posts");
    assert_eq!(posts, 10, "expected 2 published blog posts per project");

    // Follow graph: 20 user-follows + 20 project-follows among example accounts.
    let user_follows: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM follows f JOIN users fu ON fu.id = f.follower_id \
         WHERE f.target_type = 'user' AND lower(split_part(fu.email,'@',2))='example.test'",
    )
    .fetch_one(&db.pool)
    .await
    .expect("user follows");
    assert_eq!(user_follows, 20);
    let project_follows: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM follows f JOIN users fu ON fu.id = f.follower_id \
         WHERE f.target_type = 'project' AND lower(split_part(fu.email,'@',2))='example.test'",
    )
    .fetch_one(&db.pool)
    .await
    .expect("project follows");
    assert_eq!(project_follows, 20);

    // Spot-check a rendered count: each creator has 4 followers.
    let followers_of_openreels: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM follows f \
         WHERE f.target_type = 'user' \
           AND f.target_id = (SELECT id FROM users WHERE username = 'openreels')",
    )
    .fetch_one(&db.pool)
    .await
    .expect("openreels followers");
    assert_eq!(followers_of_openreels, 4);

    // Idempotent: re-run keeps 10 posts + 40 follows, not doubled.
    seed::run(&db.pool, &testnot_opts(), &SeedMedia::none())
        .await
        .expect("re-seed");
    let posts2: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM blog_posts bp JOIN projects p ON p.id = bp.project_id \
         JOIN users u ON u.id = p.user_id \
         WHERE lower(split_part(u.email,'@',2))='example.test'",
    )
    .fetch_one(&db.pool)
    .await
    .expect("posts after re-run");
    assert_eq!(posts2, 10, "re-run should not duplicate blog posts");
    let follows2: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM follows f JOIN users fu ON fu.id = f.follower_id \
         WHERE lower(split_part(fu.email,'@',2))='example.test'",
    )
    .fetch_one(&db.pool)
    .await
    .expect("follows after re-run");
    assert_eq!(follows2, 40, "re-run should not duplicate follows");
}

/// Guard-passing options bundled with an in-memory storage backend, so the media
/// phase runs and flips items visible.
fn media_ctx() -> SeedMedia {
    let s3: Arc<dyn StorageBackend> = Arc::new(InMemoryStorage::new());
    let public: Arc<dyn StorageBackend> = Arc::new(InMemoryStorage::new());
    SeedMedia {
        s3: Some(s3),
        public_s3: Some(public),
        cdn_base_url: Some("https://cdn.example.test".to_string()),
    }
}

#[tokio::test]
async fn seeds_media_makes_items_visible() {
    let db = TestDb::new().await;
    seed::run(&db.pool, &testnot_opts(), &media_ctx())
        .await
        .expect("seed with media");

    // With media attached, every item is promoted 'clean' and now clears the
    // full item-discover gate (contrast the no-storage `..._but_hidden` test).
    assert_eq!(
        count_discover_visible_example_items(&db.pool).await,
        SEEDED_ITEMS,
        "all items should be visible once media is attached"
    );

    // The audio item has its audio key set.
    let audio_keyed: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM items i JOIN projects p ON p.id = i.project_id \
         JOIN users u ON u.id = p.user_id \
         WHERE lower(split_part(u.email,'@',2))='example.test' \
           AND i.item_type = 'audio' AND i.audio_s3_key IS NOT NULL",
    )
    .fetch_one(&db.pool)
    .await
    .expect("audio keyed");
    assert!(audio_keyed >= 1, "audio item should have an audio_s3_key");

    // Download-type items produced clean, keyed versions.
    let clean_versions: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM versions v JOIN items i ON i.id = v.item_id \
         JOIN projects p ON p.id = i.project_id JOIN users u ON u.id = p.user_id \
         WHERE lower(split_part(u.email,'@',2))='example.test' \
           AND v.s3_key IS NOT NULL AND v.scan_status = 'clean'",
    )
    .fetch_one(&db.pool)
    .await
    .expect("clean versions");
    assert!(
        clean_versions >= 1,
        "expected a clean, keyed download version"
    );

    // Covers were attached (public bucket + CDN base present).
    let covered: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM items i JOIN projects p ON p.id = i.project_id \
         JOIN users u ON u.id = p.user_id \
         WHERE lower(split_part(u.email,'@',2))='example.test' \
           AND i.cover_s3_key IS NOT NULL AND i.cover_scan_status = 'clean'",
    )
    .fetch_one(&db.pool)
    .await
    .expect("covered items");
    assert!(
        covered >= 1,
        "expected item covers with cover_scan_status clean"
    );

    // Idempotent with media too: re-running stays at the roster size.
    seed::run(&db.pool, &testnot_opts(), &media_ctx())
        .await
        .expect("re-seed with media");
    assert_eq!(count_example_items(&db.pool).await, SEEDED_ITEMS);
    assert_eq!(
        count_discover_visible_example_items(&db.pool).await,
        SEEDED_ITEMS
    );
}