//! Lifecycle integration tests: full state machine traversals and concurrent access.
//!
//! These tests verify multi-step workflows that span creation, mutation, and cleanup,
//! plus concurrent access scenarios that exercise database atomicity guarantees.

use crate::harness::TestHarness;
use makenotwork::db::UserId;
use serde_json::Value;

// =============================================================================
// Sandbox lifecycle: create → use features → expire → cleanup deletes account
// =============================================================================

/// Helper: create a sandbox account and return its user_id.
async fn create_sandbox_get_id(h: &mut TestHarness) -> UserId {
    let resp = h.client.get("/sandbox").await;
    assert!(resp.status.is_success());
    let resp = h.client.post_form("/sandbox", "").await;
    assert!(
        resp.status.is_redirection(),
        "POST /sandbox should redirect, got {}",
        resp.status
    );

    sqlx::query_scalar::<_, UserId>(
        "SELECT id FROM users WHERE is_sandbox = TRUE ORDER BY created_at DESC LIMIT 1",
    )
    .fetch_one(&h.db)
    .await
    .expect("No sandbox user found")
}

#[tokio::test]
async fn sandbox_lifecycle_create_use_expire_cleanup() {
    let mut h = TestHarness::new().await;

    // Step 1: Create sandbox
    let user_id = create_sandbox_get_id(&mut h).await;

    // Refresh CSRF token after sandbox creation (session rotated on login)
    h.client.fetch_csrf_token().await;

    // Step 2: Use features — create a project and item
    let resp = h
        .client
        .post_form("/api/projects", "slug=sb-life&title=Sandbox+Life")
        .await;
    assert!(resp.status.is_success(), "Create project failed: {}", resp.text);
    let project: Value = resp.json();
    let project_id = project["id"].as_str().unwrap().to_string();

    let resp = h
        .client
        .post_form(
            &format!("/api/projects/{}/items", project_id),
            "title=Sandbox+Item&item_type=digital&price_cents=0",
        )
        .await;
    assert!(resp.status.is_success(), "Create item failed: {}", resp.text);

    // Verify content exists
    let item_count: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM items WHERE project_id = $1::uuid",
    )
    .bind(&project_id)
    .fetch_one(&h.db)
    .await
    .unwrap();
    assert_eq!(item_count, 1, "Should have 1 item");

    // Step 3: Simulate expiry by backdating sandbox_expires_at
    sqlx::query("UPDATE users SET sandbox_expires_at = NOW() - INTERVAL '1 hour' WHERE id = $1")
        .bind(user_id)
        .execute(&h.db)
        .await
        .unwrap();

    // Verify user is now in expired set
    let expired_ids: Vec<UserId> = sqlx::query_scalar(
        "SELECT id FROM users WHERE is_sandbox = TRUE AND sandbox_expires_at < NOW()",
    )
    .fetch_all(&h.db)
    .await
    .unwrap();
    assert!(
        expired_ids.contains(&user_id),
        "Sandbox user should appear in expired set"
    );

    // Step 4: Simulate cleanup (direct SQL CASCADE delete, same as scheduler does)
    sqlx::query("DELETE FROM users WHERE id = $1")
        .bind(user_id)
        .execute(&h.db)
        .await
        .unwrap();

    // Verify everything is gone
    let user_exists: i64 =
        sqlx::query_scalar("SELECT COUNT(*) FROM users WHERE id = $1")
            .bind(user_id)
            .fetch_one(&h.db)
            .await
            .unwrap();
    assert_eq!(user_exists, 0, "Sandbox user should be deleted");

    let projects_left: i64 =
        sqlx::query_scalar("SELECT COUNT(*) FROM projects WHERE user_id = $1")
            .bind(user_id)
            .fetch_one(&h.db)
            .await
            .unwrap();
    assert_eq!(projects_left, 0, "Projects should be cascade-deleted");
}

// =============================================================================
// Creator tier upgrade: SmallFiles → BigFiles → verify limits change
// =============================================================================

#[tokio::test]
async fn creator_tier_upgrade_changes_limits() {
    let mut h = TestHarness::with_storage().await;
    let user_id = h.create_creator("tierup").await;

    // Start with small_files tier
    h.grant_tier(user_id, "small_files").await;

    // Verify via DB
    let tier: String =
        sqlx::query_scalar("SELECT creator_tier FROM users WHERE id = $1")
            .bind(user_id)
            .fetch_one(&h.db)
            .await
            .unwrap();
    assert_eq!(tier, "small_files");

    // Verify subscription row exists with correct tier
    let sub_tier: String =
        sqlx::query_scalar("SELECT tier FROM creator_subscriptions WHERE user_id = $1")
            .bind(user_id)
            .fetch_one(&h.db)
            .await
            .unwrap();
    assert_eq!(sub_tier, "small_files");

    // Upgrade to big_files
    h.grant_tier(user_id, "big_files").await;

    let tier: String =
        sqlx::query_scalar("SELECT creator_tier FROM users WHERE id = $1")
            .bind(user_id)
            .fetch_one(&h.db)
            .await
            .unwrap();
    assert_eq!(tier, "big_files");

    // Verify subscription row was UPDATED (not duplicated — ON CONFLICT DO UPDATE)
    let sub_count: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM creator_subscriptions WHERE user_id = $1",
    )
    .bind(user_id)
    .fetch_one(&h.db)
    .await
    .unwrap();
    assert_eq!(sub_count, 1, "Upgrade should update, not duplicate subscription");

    let sub_tier: String =
        sqlx::query_scalar("SELECT tier FROM creator_subscriptions WHERE user_id = $1")
            .bind(user_id)
            .fetch_one(&h.db)
            .await
            .unwrap();
    assert_eq!(sub_tier, "big_files");

    // Upgrade to everything
    h.grant_tier(user_id, "everything").await;

    let tier: String =
        sqlx::query_scalar("SELECT creator_tier FROM users WHERE id = $1")
            .bind(user_id)
            .fetch_one(&h.db)
            .await
            .unwrap();
    assert_eq!(tier, "everything");

    // Re-login and verify dashboard loads with new tier
    h.client.post_form("/logout", "").await;
    h.login("tierup", "password123").await;
    let resp = h.client.get("/dashboard").await;
    assert!(
        resp.status.is_success(),
        "Dashboard should load after tier upgrade, got {}",
        resp.status
    );
}

// =============================================================================
// Concurrent promo code: 2 buyers apply same max_uses=1 code → only 1 succeeds
// =============================================================================

#[tokio::test]
async fn concurrent_promo_code_max_uses_one() {
    let mut h = TestHarness::new().await;

    // Create a creator with an item
    let seller_id = h.create_creator("promosel").await;
    let resp = h
        .client
        .post_form("/api/projects", "slug=promo-race&title=Promo+Race")
        .await;
    assert!(resp.status.is_success());
    let project: Value = resp.json();
    let project_id = project["id"].as_str().unwrap().to_string();

    let resp = h
        .client
        .post_form(
            &format!("/api/projects/{}/items", project_id),
            "title=Race+Item&item_type=digital&price_cents=500",
        )
        .await;
    assert!(resp.status.is_success());

    // Create a promo code with max_uses = 1
    sqlx::query(
        "INSERT INTO promo_codes (creator_id, code, code_purpose, discount_type, discount_value, min_price_cents, max_uses)
         VALUES ($1, 'ONLYONE', 'discount', 'percentage', 100, 0, 1)",
    )
    .bind(seller_id)
    .execute(&h.db)
    .await
    .unwrap();

    // Get the promo code id
    let promo_id: uuid::Uuid = sqlx::query_scalar::<_, uuid::Uuid>(
        "SELECT id FROM promo_codes WHERE code = 'ONLYONE'",
    )
    .fetch_one(&h.db)
    .await
    .unwrap();

    // Simulate concurrent increment attempts using try_increment_use_count logic
    // (The actual SQL: UPDATE ... WHERE use_count < max_uses)
    let result1 = sqlx::query(
        "UPDATE promo_codes SET use_count = use_count + 1 WHERE id = $1 AND (max_uses IS NULL OR use_count < max_uses)",
    )
    .bind(promo_id)
    .execute(&h.db)
    .await
    .unwrap();

    let result2 = sqlx::query(
        "UPDATE promo_codes SET use_count = use_count + 1 WHERE id = $1 AND (max_uses IS NULL OR use_count < max_uses)",
    )
    .bind(promo_id)
    .execute(&h.db)
    .await
    .unwrap();

    // First should succeed, second should be a no-op (WHERE clause fails)
    let total_affected = result1.rows_affected() + result2.rows_affected();
    assert_eq!(
        total_affected, 1,
        "Only 1 of 2 concurrent increments should succeed for max_uses=1, got {}",
        total_affected
    );

    // Verify use_count is exactly 1
    let use_count: i32 = sqlx::query_scalar(
        "SELECT use_count FROM promo_codes WHERE id = $1",
    )
    .bind(promo_id)
    .fetch_one(&h.db)
    .await
    .unwrap();
    assert_eq!(use_count, 1, "use_count should be exactly 1");
}

// =============================================================================
// Concurrent sandbox creation: same IP → per-IP cap holds
// =============================================================================

#[tokio::test]
#[cfg_attr(not(feature = "fast-tests"), ignore)]
async fn concurrent_sandbox_per_ip_cap_holds() {
    let mut h = TestHarness::new().await;

    // Create sandboxes up to the cap without logging out.
    // The cap counts concurrent sessions per IP — logout deletes session rows,
    // which would break the count.
    let cap = makenotwork::constants::SANDBOX_MAX_PER_IP;
    for i in 0..cap {
        let resp = h.client.get("/sandbox").await;
        assert!(resp.status.is_success());
        let resp = h.client.post_form("/sandbox", "").await;
        assert!(
            resp.status.is_redirection(),
            "Sandbox {} should succeed, got {}",
            i + 1,
            resp.status
        );
    }

    // Count how many sandbox users exist
    let count: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM users WHERE is_sandbox = TRUE",
    )
    .fetch_one(&h.db)
    .await
    .unwrap();
    assert_eq!(count, cap, "Should have exactly {} sandbox users", cap);

    // Try to create one more — should fail (cap reached)
    h.client.get("/sandbox").await;
    let resp = h.client.post_form("/sandbox", "").await;
    assert_eq!(
        resp.status.as_u16(),
        400,
        "Sandbox beyond cap should return 400, got {}",
        resp.status
    );

    // Count should still be at cap
    let count_after: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM users WHERE is_sandbox = TRUE",
    )
    .fetch_one(&h.db)
    .await
    .unwrap();
    assert_eq!(
        count_after, cap,
        "No new sandbox should be created beyond cap"
    );
}

// =============================================================================
// Concurrent purchase: 2 buyers checkout same item → sales_count correct
// =============================================================================

#[tokio::test]
async fn concurrent_purchases_sales_count_correct() {
    let mut h = TestHarness::new().await;

    // Create a creator with a free item (so we can claim without Stripe)
    let _creator_id = h.create_creator("salescount").await;
    let resp = h
        .client
        .post_form("/api/projects", "slug=sales-race&title=Sales+Race")
        .await;
    assert!(resp.status.is_success());
    let project: Value = resp.json();
    let project_id = project["id"].as_str().unwrap().to_string();

    let resp = h
        .client
        .post_form(
            &format!("/api/projects/{}/items", project_id),
            "title=Free+Race&item_type=digital&price_cents=0",
        )
        .await;
    assert!(resp.status.is_success());
    let item: Value = resp.json();
    let item_id = item["id"].as_str().unwrap().to_string();

    // Publish
    h.publish_project_and_item(&project_id, &item_id).await;
    h.client.post_form("/logout", "").await;

    // Create 5 buyers and have them each claim the free item
    for i in 0..5 {
        let username = format!("racer{}", i);
        h.signup(&username, &format!("{}@test.com", username), "password123")
            .await;
        let resp = h
            .client
            .post_form(&format!("/api/library/add/{}", item_id), "")
            .await;
        assert!(
            resp.status.is_success(),
            "Free claim {} should succeed, got: {} {}",
            i,
            resp.status,
            resp.text
        );
        h.client.post_form("/logout", "").await;
    }

    // Verify sales_count is exactly 5
    let sales: i32 = sqlx::query_scalar("SELECT sales_count FROM items WHERE id = $1::uuid")
        .bind(&item_id)
        .fetch_one(&h.db)
        .await
        .unwrap();
    assert_eq!(sales, 5, "sales_count should be exactly 5 after 5 purchases");

    // Verify 5 completed transactions exist
    let tx_count: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM transactions WHERE item_id = $1::uuid AND status = 'completed'",
    )
    .bind(&item_id)
    .fetch_one(&h.db)
    .await
    .unwrap();
    assert_eq!(tx_count, 5, "Should have 5 completed transactions");
}

// =============================================================================
// Promo code lifecycle: create → use → exhaust → delete
// =============================================================================

#[tokio::test]
async fn promo_code_full_lifecycle() {
    let mut h = TestHarness::new().await;

    let _creator_id = h.create_creator("promolife").await;

    // Create a project (needed for promo code scoping)
    let resp = h
        .client
        .post_form("/api/projects", "slug=promo-life&title=Promo+Life")
        .await;
    assert!(resp.status.is_success());
    let project: Value = resp.json();
    let project_id = project["id"].as_str().unwrap().to_string();

    // Step 1: Create promo code via API
    let resp = h
        .client
        .post_form(
            "/api/promo-codes",
            &format!(
                "code=LIFECYCLE&code_purpose=discount&discount_type=percentage&discount_value=50&max_uses=2&project_id={}",
                project_id
            ),
        )
        .await;
    assert!(
        resp.status.is_success() || resp.status.is_redirection(),
        "Create promo code failed: {} {}",
        resp.status,
        resp.text
    );

    // Step 2: Verify it exists in the DB
    let code_id: String = sqlx::query_scalar::<_, uuid::Uuid>(
        "SELECT id FROM promo_codes WHERE code = 'LIFECYCLE'",
    )
    .fetch_one(&h.db)
    .await
    .expect("Promo code should exist")
    .to_string();

    let (use_count, max_uses): (i32, Option<i32>) = sqlx::query_as(
        "SELECT use_count, max_uses FROM promo_codes WHERE code = 'LIFECYCLE'",
    )
    .fetch_one(&h.db)
    .await
    .unwrap();
    assert_eq!(use_count, 0);
    assert_eq!(max_uses, Some(2));

    // Step 3: Simulate usage (increment use_count twice)
    sqlx::query("UPDATE promo_codes SET use_count = use_count + 1 WHERE id = $1::uuid")
        .bind(&code_id)
        .execute(&h.db)
        .await
        .unwrap();
    sqlx::query("UPDATE promo_codes SET use_count = use_count + 1 WHERE id = $1::uuid")
        .bind(&code_id)
        .execute(&h.db)
        .await
        .unwrap();

    // Step 4: Verify exhausted (use_count == max_uses)
    let (use_count, max_uses): (i32, Option<i32>) = sqlx::query_as(
        "SELECT use_count, max_uses FROM promo_codes WHERE id = $1::uuid",
    )
    .bind(&code_id)
    .fetch_one(&h.db)
    .await
    .unwrap();
    assert_eq!(use_count, 2);
    assert_eq!(max_uses, Some(2));

    // Step 5: try_increment should fail (exhausted)
    let result = sqlx::query(
        "UPDATE promo_codes SET use_count = use_count + 1 WHERE id = $1::uuid AND (max_uses IS NULL OR use_count < max_uses)",
    )
    .bind(&code_id)
    .execute(&h.db)
    .await
    .unwrap();
    assert_eq!(
        result.rows_affected(),
        0,
        "Exhausted promo code should not increment"
    );

    // Step 6: Delete promo code
    let resp = h
        .client
        .delete(&format!("/api/promo-codes/{}", code_id))
        .await;
    assert!(
        resp.status.is_success(),
        "Delete promo code failed: {} {}",
        resp.status,
        resp.text
    );

    // Step 7: Verify gone
    let count: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM promo_codes WHERE id = $1::uuid",
    )
    .bind(&code_id)
    .fetch_one(&h.db)
    .await
    .unwrap();
    assert_eq!(count, 0, "Promo code should be deleted");
}

// =============================================================================
// Account deletion with 30-day export window
// =============================================================================

#[tokio::test]
async fn account_deletion_export_window() {
    let mut h = TestHarness::new().await;

    // Create user with content
    let user_id = h.create_creator("exporter").await;
    let resp = h
        .client
        .post_form("/api/projects", "slug=export-test&title=Export+Test")
        .await;
    assert!(resp.status.is_success());
    let project: Value = resp.json();
    let project_id = project["id"].as_str().unwrap().to_string();

    let resp = h
        .client
        .post_form(
            &format!("/api/projects/{}/items", project_id),
            "title=Exportable+Item&item_type=digital&price_cents=0",
        )
        .await;
    assert!(resp.status.is_success());

    // Request deletion
    let resp = h
        .client
        .post_form("/api/account/request-deletion", "username=exporter")
        .await;
    assert!(
        resp.status.is_success(),
        "Request deletion failed: {} {}",
        resp.status,
        resp.text
    );

    // Verify user still exists (not deleted yet — waiting for confirmation link)
    let user_exists: i64 =
        sqlx::query_scalar("SELECT COUNT(*) FROM users WHERE id = $1")
            .bind(user_id)
            .fetch_one(&h.db)
            .await
            .unwrap();
    assert_eq!(user_exists, 1, "User should still exist before confirmation");

    // Verify content still accessible
    let project_exists: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM projects WHERE user_id = $1",
    )
    .bind(user_id)
    .fetch_one(&h.db)
    .await
    .unwrap();
    assert_eq!(
        project_exists, 1,
        "Projects should still exist during export window"
    );
}

// =============================================================================
// Subscription lifecycle: subscribe → active → past_due → recover → cancel
// =============================================================================

#[tokio::test]
async fn subscription_lifecycle_subscribe_cancel_access_revoked() {
    let mut h = TestHarness::new().await;

    // Step 1: Creator sets up a project with a subscription tier
    let _creator_id = h.create_creator("subhost").await;
    let resp = h
        .client
        .post_form("/api/projects", "slug=sub-life&title=Sub+Life")
        .await;
    assert!(resp.status.is_success(), "Create project failed: {}", resp.text);
    let project: Value = resp.json();
    let project_id = project["id"].as_str().unwrap().to_string();
    let project_uuid: uuid::Uuid = project_id.parse().unwrap();

    // Publish project
    h.client
        .put_json(
            &format!("/api/projects/{}", project_id),
            r#"{"is_public": true}"#,
        )
        .await;

    // Insert subscription tier via SQL (bypasses Stripe product creation)
    let tier_id: uuid::Uuid = sqlx::query_scalar(
        "INSERT INTO subscription_tiers (project_id, name, price_cents, stripe_product_id, stripe_price_id)
         VALUES ($1, 'Premium', 500, 'prod_test', 'price_test')
         RETURNING id",
    )
    .bind(project_uuid)
    .fetch_one(&h.db)
    .await
    .unwrap();

    h.client.post_form("/logout", "").await;

    // Step 2: Subscriber signs up
    let subscriber_id = h.signup("subfan", "subfan@test.com", "password123").await;
    h.client.post_form("/logout", "").await;

    // Step 3: Simulate subscription creation (what handle_subscription_checkout_completed does)
    let stripe_sub_id = "sub_lifecycle_test_001";
    let stripe_customer_id = "cus_lifecycle_test_001";
    let now = chrono::Utc::now();
    let period_end = now + chrono::Duration::days(30);

    sqlx::query(
        "INSERT INTO subscriptions (subscriber_id, tier_id, project_id, stripe_subscription_id, stripe_customer_id, status, current_period_start, current_period_end)
         VALUES ($1, $2, $3, $4, $5, 'active', $6, $7)",
    )
    .bind(subscriber_id)
    .bind(tier_id)
    .bind(project_uuid)
    .bind(stripe_sub_id)
    .bind(stripe_customer_id)
    .bind(now)
    .bind(period_end)
    .execute(&h.db)
    .await
    .unwrap();

    // Step 4: Verify subscriber has access (mirrors db::subscriptions::has_access)
    let has_access: bool = sqlx::query_scalar(
        "SELECT COUNT(*) > 0 FROM subscriptions WHERE subscriber_id = $1 AND project_id = $2 AND status = 'active' AND paused_at IS NULL",
    )
    .bind(subscriber_id)
    .bind(project_uuid)
    .fetch_one(&h.db)
    .await
    .unwrap();
    assert!(has_access, "Subscriber should have access after subscribing");

    // Verify subscriber count = 1
    let sub_count: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM subscriptions WHERE project_id = $1 AND status = 'active'",
    )
    .bind(project_uuid)
    .fetch_one(&h.db)
    .await
    .unwrap();
    assert_eq!(sub_count, 1, "Project should have 1 active subscriber");

    // Step 5: Simulate subscription status → past_due (missed payment)
    sqlx::query(
        "UPDATE subscriptions SET status = 'past_due' WHERE stripe_subscription_id = $1",
    )
    .bind(stripe_sub_id)
    .execute(&h.db)
    .await
    .unwrap();

    // past_due still counts as non-active for access check
    let has_access_past_due: bool = sqlx::query_scalar(
        "SELECT COUNT(*) > 0 FROM subscriptions WHERE subscriber_id = $1 AND project_id = $2 AND status = 'active' AND paused_at IS NULL",
    )
    .bind(subscriber_id)
    .bind(project_uuid)
    .fetch_one(&h.db)
    .await
    .unwrap();
    assert!(
        !has_access_past_due,
        "Subscriber should NOT have access when past_due"
    );

    // Step 6: Restore to active (payment recovered)
    sqlx::query(
        "UPDATE subscriptions SET status = 'active' WHERE stripe_subscription_id = $1",
    )
    .bind(stripe_sub_id)
    .execute(&h.db)
    .await
    .unwrap();

    let has_access_restored: bool = sqlx::query_scalar(
        "SELECT COUNT(*) > 0 FROM subscriptions WHERE subscriber_id = $1 AND project_id = $2 AND status = 'active' AND paused_at IS NULL",
    )
    .bind(subscriber_id)
    .bind(project_uuid)
    .fetch_one(&h.db)
    .await
    .unwrap();
    assert!(has_access_restored, "Subscriber should regain access after payment recovery");

    // Step 7: Cancel subscription (mirrors cancel_subscription DB function)
    sqlx::query(
        "UPDATE subscriptions SET status = 'canceled', canceled_at = NOW() WHERE stripe_subscription_id = $1",
    )
    .bind(stripe_sub_id)
    .execute(&h.db)
    .await
    .unwrap();

    // Step 8: Verify subscription is canceled
    let status: String = sqlx::query_scalar(
        "SELECT status FROM subscriptions WHERE stripe_subscription_id = $1",
    )
    .bind(stripe_sub_id)
    .fetch_one(&h.db)
    .await
    .unwrap();
    assert_eq!(status, "canceled");

    let canceled_at: Option<chrono::DateTime<chrono::Utc>> = sqlx::query_scalar(
        "SELECT canceled_at FROM subscriptions WHERE stripe_subscription_id = $1",
    )
    .bind(stripe_sub_id)
    .fetch_one(&h.db)
    .await
    .unwrap();
    assert!(canceled_at.is_some(), "canceled_at should be set");

    // Step 9: Verify subscriber NO LONGER has access
    let has_access_after: bool = sqlx::query_scalar(
        "SELECT COUNT(*) > 0 FROM subscriptions WHERE subscriber_id = $1 AND project_id = $2 AND status = 'active' AND paused_at IS NULL",
    )
    .bind(subscriber_id)
    .bind(project_uuid)
    .fetch_one(&h.db)
    .await
    .unwrap();
    assert!(
        !has_access_after,
        "Subscriber should NOT have access after cancellation"
    );

    // Step 10: Verify tier delete soft-deletes when subscriptions exist
    // (instead of hard-deleting, since this tier has subscription references)
    h.login("subhost", "password123").await;
    let resp = h
        .client
        .delete(&format!("/api/tiers/{}", tier_id))
        .await;
    assert_eq!(resp.status.as_u16(), 204, "Delete tier should return 204");

    // Tier should still exist in DB (soft-deleted: is_active=false)
    let (tier_exists, tier_active): (bool, bool) = sqlx::query_as(
        "SELECT EXISTS(SELECT 1 FROM subscription_tiers WHERE id = $1), COALESCE((SELECT is_active FROM subscription_tiers WHERE id = $1), false)",
    )
    .bind(tier_id)
    .fetch_one(&h.db)
    .await
    .unwrap();
    assert!(tier_exists, "Tier should still exist (soft-deleted)");
    assert!(!tier_active, "Tier should be deactivated (is_active=false)");
}

// =============================================================================
// Concurrent file upload: 2 increments → storage_used_bytes correct
// =============================================================================

#[tokio::test]
async fn concurrent_storage_increment_correct() {
    let mut h = TestHarness::new().await;
    let user_id = h.create_creator("storagerace").await;
    h.grant_tier(user_id, "small_files").await;

    // Start with 0 bytes
    let initial: i64 = sqlx::query_scalar(
        "SELECT storage_used_bytes FROM users WHERE id = $1",
    )
    .bind(user_id)
    .fetch_one(&h.db)
    .await
    .unwrap();
    assert_eq!(initial, 0);

    let cap = 50_000_000_000i64; // 50 GB (well above what we'll use)

    // Simulate two concurrent uploads: 10 MB and 20 MB
    let upload_a = 10 * 1024 * 1024i64;
    let upload_b = 20 * 1024 * 1024i64;

    // Run both increments concurrently via tokio::join
    let pool = h.db.clone();
    let pool2 = h.db.clone();
    let (result_a, result_b) = tokio::join!(
        sqlx::query(
            "UPDATE users SET storage_used_bytes = storage_used_bytes + $2 WHERE id = $1 AND storage_used_bytes + $2 <= $3",
        )
        .bind(user_id)
        .bind(upload_a)
        .bind(cap)
        .execute(&pool),
        sqlx::query(
            "UPDATE users SET storage_used_bytes = storage_used_bytes + $2 WHERE id = $1 AND storage_used_bytes + $2 <= $3",
        )
        .bind(user_id)
        .bind(upload_b)
        .bind(cap)
        .execute(&pool2),
    );

    assert!(result_a.is_ok(), "Upload A should succeed");
    assert!(result_b.is_ok(), "Upload B should succeed");
    assert_eq!(result_a.unwrap().rows_affected(), 1);
    assert_eq!(result_b.unwrap().rows_affected(), 1);

    // Verify final storage is exactly the sum
    let final_bytes: i64 = sqlx::query_scalar(
        "SELECT storage_used_bytes FROM users WHERE id = $1",
    )
    .bind(user_id)
    .fetch_one(&h.db)
    .await
    .unwrap();
    assert_eq!(
        final_bytes,
        upload_a + upload_b,
        "Concurrent increments should sum correctly: expected {}, got {}",
        upload_a + upload_b,
        final_bytes
    );
}

#[tokio::test]
async fn concurrent_storage_increment_respects_cap() {
    let mut h = TestHarness::new().await;
    let user_id = h.create_creator("storagecap").await;
    h.grant_tier(user_id, "small_files").await;

    let cap = 1_000_000i64; // 1 MB cap

    // Two uploads that individually fit but together exceed the cap
    let upload_a = 700_000i64;
    let upload_b = 700_000i64;

    let pool = h.db.clone();
    let pool2 = h.db.clone();
    let (result_a, result_b) = tokio::join!(
        sqlx::query(
            "UPDATE users SET storage_used_bytes = storage_used_bytes + $2 WHERE id = $1 AND storage_used_bytes + $2 <= $3",
        )
        .bind(user_id)
        .bind(upload_a)
        .bind(cap)
        .execute(&pool),
        sqlx::query(
            "UPDATE users SET storage_used_bytes = storage_used_bytes + $2 WHERE id = $1 AND storage_used_bytes + $2 <= $3",
        )
        .bind(user_id)
        .bind(upload_b)
        .bind(cap)
        .execute(&pool2),
    );

    // Both queries succeed at the SQL level, but only one should affect a row
    let affected_a = result_a.unwrap().rows_affected();
    let affected_b = result_b.unwrap().rows_affected();

    // Exactly one should succeed (the other's WHERE clause fails after the first commits)
    assert_eq!(
        affected_a + affected_b, 1,
        "Only 1 of 2 concurrent uploads should fit under the cap, got {} + {}",
        affected_a, affected_b
    );

    // Final storage should be exactly one upload's worth
    let final_bytes: i64 = sqlx::query_scalar(
        "SELECT storage_used_bytes FROM users WHERE id = $1",
    )
    .bind(user_id)
    .fetch_one(&h.db)
    .await
    .unwrap();
    assert_eq!(final_bytes, 700_000, "Should have exactly one upload's worth");
}