//! DB-layer contract tests for the payment cold-spot modules (`db::tips`,
//! `db::license_keys`, `db::pending_refunds`).
//!
//! Ultra-fuzz Run 7 flagged these three modules as the Payments cold spot:
//! correct, but with their DB-layer contracts asserted only indirectly through
//! HTTP/webhook flows. These tests call the `db::` functions directly so the
//! claim/unclaim/complete lifecycle, the activation cap, and the
//! finalize-idempotency are pinned at the layer they live in.

use crate::harness::TestHarness;
use makenotwork::db::{self, KeyCode};

// ── db::pending_refunds, claim/unclaim/complete lifecycle (PAY-S1) ──

#[tokio::test]
async fn pending_refund_claim_is_single_shot_and_completes() {
    let h = TestHarness::new().await;
    let pi = "pi_dbpl_single_001";

    db::pending_refunds::insert_pending_refund(&h.db, pi, 999, 999)
        .await
        .unwrap();

    // First claim matches the row; a second claim finds nothing (matched_at set).
    let first = db::pending_refunds::claim_pending_refund(&h.db, pi)
        .await
        .unwrap();
    let claimed = first.expect("first claim must match the unclaimed row");
    assert_eq!(claimed.payment_intent_id, pi);
    let second = db::pending_refunds::claim_pending_refund(&h.db, pi)
        .await
        .unwrap();
    assert!(
        second.is_none(),
        "a claimed refund must not be claimable twice"
    );

    // Completing it removes it from the stale sweep.
    db::pending_refunds::mark_refund_completed(&h.db, claimed.id)
        .await
        .unwrap();
    let stale = db::pending_refunds::get_stale_refunds(&h.db, chrono::Duration::zero())
        .await
        .unwrap();
    assert!(
        !stale.iter().any(|r| r.payment_intent_id == pi),
        "a completed refund must not surface in the stale sweep"
    );
}

#[tokio::test]
async fn pending_refund_unclaim_reopens_for_retry() {
    let h = TestHarness::new().await;
    let pi = "pi_dbpl_reopen_001";

    db::pending_refunds::insert_pending_refund(&h.db, pi, 500, 500)
        .await
        .unwrap();
    let claimed = db::pending_refunds::claim_pending_refund(&h.db, pi)
        .await
        .unwrap()
        .expect("claim");

    // A graceful failure unclaims; the row is then re-claimable.
    db::pending_refunds::unclaim_pending_refund(&h.db, claimed.id)
        .await
        .unwrap();
    let reclaim = db::pending_refunds::claim_pending_refund(&h.db, pi)
        .await
        .unwrap();
    assert!(
        reclaim.is_some(),
        "an unclaimed refund must be re-claimable"
    );
}

#[tokio::test]
async fn pending_refund_claimed_but_uncompleted_is_swept() {
    let h = TestHarness::new().await;
    let pi = "pi_dbpl_crash_001";

    // Claim but never complete, the crash window. Backdate created_at so the
    // age filter surfaces it (insert_pending_refund stamps created_at = NOW()).
    db::pending_refunds::insert_pending_refund(&h.db, pi, 700, 700)
        .await
        .unwrap();
    let claimed = db::pending_refunds::claim_pending_refund(&h.db, pi)
        .await
        .unwrap()
        .expect("claim");
    sqlx::query(
        "UPDATE pending_refunds SET created_at = NOW() - INTERVAL '25 hours' WHERE id = $1",
    )
    .bind(claimed.id)
    .execute(&h.db)
    .await
    .unwrap();

    let stale = db::pending_refunds::get_stale_refunds(&h.db, chrono::Duration::hours(24))
        .await
        .unwrap();
    assert!(
        stale.iter().any(|r| r.payment_intent_id == pi),
        "a claimed-but-uncompleted refund must surface for human reconciliation (PAY-S1)"
    );
}

// ── db::transactions, self-service refund claim (Pay-S1, Run 9) ──

#[tokio::test]
async fn refund_claim_blocks_double_submit_and_releases() {
    let h = TestHarness::new().await;
    let (buyer, seller) = two_users(&h, "refclaim").await;

    // A bare completed transaction with a payment intent, the only state the
    // refund claim cares about is status = 'completed'.
    let tx_id: db::TransactionId = sqlx::query_scalar(
        "INSERT INTO transactions (buyer_id, seller_id, amount_cents, status, stripe_payment_intent_id) \
         VALUES ($1, $2, 500, 'completed', $3) RETURNING id",
    )
    .bind(buyer)
    .bind(seller)
    .bind("pi_refclaim_001")
    .fetch_one(&h.db)
    .await
    .unwrap();

    // First claim wins the completed -> refunding transition; a rapid second
    // submit (the bug: the row stays completed until the async webhook) finds
    // nothing and is rejected before any Stripe call.
    let first = db::transactions::claim_transaction_for_refund(&h.db, tx_id)
        .await
        .unwrap();
    assert_eq!(
        first,
        Some(tx_id),
        "first refund claim must win completed->refunding"
    );
    let second = db::transactions::claim_transaction_for_refund(&h.db, tx_id)
        .await
        .unwrap();
    assert!(
        second.is_none(),
        "a second concurrent refund claim must be blocked (double-submit)"
    );

    // A Stripe failure releases the claim back to completed so the creator can retry.
    db::transactions::release_refund_claim(&h.db, tx_id)
        .await
        .unwrap();
    let reclaim = db::transactions::claim_transaction_for_refund(&h.db, tx_id)
        .await
        .unwrap();
    assert_eq!(
        reclaim,
        Some(tx_id),
        "a released claim must be re-claimable"
    );

    // Once the webhook finalizes the row to refunded, it is no longer claimable.
    sqlx::query("UPDATE transactions SET status = 'refunded' WHERE id = $1")
        .bind(tx_id)
        .execute(&h.db)
        .await
        .unwrap();
    let after = db::transactions::claim_transaction_for_refund(&h.db, tx_id)
        .await
        .unwrap();
    assert!(
        after.is_none(),
        "a refunded transaction must not be refund-claimable"
    );
}

// ── db::tips, create guard + complete/refund idempotency ──

/// Two bare users (tipper, recipient) for tip tests. Raw SQL keeps the fixture
/// minimal, tips need only valid user FKs.
async fn two_users(h: &TestHarness, tag: &str) -> (db::UserId, db::UserId) {
    let tipper: db::UserId = sqlx::query_scalar(
        "INSERT INTO users (username, email, password_hash, email_verified) \
         VALUES ($1, $2, 'x', true) RETURNING id",
    )
    .bind(format!("tipper_{tag}"))
    .bind(format!("tipper_{tag}@test.com"))
    .fetch_one(&h.db)
    .await
    .unwrap();
    let recipient: db::UserId = sqlx::query_scalar(
        "INSERT INTO users (username, email, password_hash, email_verified) \
         VALUES ($1, $2, 'x', true) RETURNING id",
    )
    .bind(format!("recip_{tag}"))
    .bind(format!("recip_{tag}@test.com"))
    .fetch_one(&h.db)
    .await
    .unwrap();
    (tipper, recipient)
}

#[tokio::test]
async fn create_tip_rejects_nonpositive_amount() {
    let h = TestHarness::new().await;
    let (tipper, recipient) = two_users(&h, "guard").await;

    for amount in [0, -100] {
        let res =
            db::tips::create_tip(&h.db, tipper, recipient, None, amount, None, "cs_guard").await;
        assert!(
            res.is_err(),
            "tip amount {amount} must be rejected by the positivity guard"
        );
    }

    // A positive amount goes through.
    let ok = db::tips::create_tip(
        &h.db,
        tipper,
        recipient,
        None,
        500,
        Some("thanks"),
        "cs_guard_ok",
    )
    .await;
    assert!(ok.is_ok(), "a positive tip must be accepted");
}

#[tokio::test]
async fn complete_tip_is_idempotent() {
    let h = TestHarness::new().await;
    let (tipper, recipient) = two_users(&h, "complete").await;
    let session = "cs_dbpl_complete_001";

    db::tips::create_tip(&h.db, tipper, recipient, None, 1500, None, session)
        .await
        .unwrap();

    // First completion transitions pending → completed and returns the row.
    let first = db::tips::complete_tip(&h.db, session, Some("pi_tip_complete_001"))
        .await
        .unwrap();
    assert!(
        first.is_some(),
        "first completion must update the pending tip"
    );

    // A redelivered webhook completing the same session is a no-op.
    let second = db::tips::complete_tip(&h.db, session, Some("pi_tip_complete_001"))
        .await
        .unwrap();
    assert!(
        second.is_none(),
        "completing an already-completed tip must be idempotent"
    );

    // The completed tip counts toward the recipient's totals.
    assert_eq!(
        db::tips::count_tips_received(&h.db, recipient)
            .await
            .unwrap(),
        1
    );
    assert_eq!(
        db::tips::total_tips_received(&h.db, recipient)
            .await
            .unwrap(),
        1500
    );
}

#[tokio::test]
async fn refund_tip_is_idempotent_and_scoped() {
    let h = TestHarness::new().await;
    let (tipper, recipient) = two_users(&h, "refund").await;
    let session = "cs_dbpl_refund_001";
    let pi = "pi_tip_refund_001";

    db::tips::create_tip(&h.db, tipper, recipient, None, 800, None, session)
        .await
        .unwrap();
    db::tips::complete_tip(&h.db, session, Some(pi))
        .await
        .unwrap();

    // First refund flips completed → refunded.
    assert!(
        db::tips::refund_tip_by_payment_intent(&h.db, pi)
            .await
            .unwrap()
    );
    // Second refund of the same PI is a no-op (already refunded).
    assert!(
        !db::tips::refund_tip_by_payment_intent(&h.db, pi)
            .await
            .unwrap()
    );
    // An unknown PI never matches.
    assert!(
        !db::tips::refund_tip_by_payment_intent(&h.db, "pi_unknown")
            .await
            .unwrap()
    );

    // A refunded tip drops out of the received totals.
    assert_eq!(
        db::tips::count_tips_received(&h.db, recipient)
            .await
            .unwrap(),
        0
    );
}

// ── db::license_keys, finalize idempotency + activation cap ──

/// A user/project/item plus a completed transaction. License-key tests need the
/// full FK chain (license_keys.transaction_id → transactions).
async fn keyed_item(h: &TestHarness, tag: &str) -> (db::UserId, db::ItemId, db::TransactionId) {
    let owner: db::UserId = sqlx::query_scalar(
        "INSERT INTO users (username, email, password_hash, email_verified) \
         VALUES ($1, $2, 'x', true) RETURNING id",
    )
    .bind(format!("lkowner_{tag}"))
    .bind(format!("lkowner_{tag}@test.com"))
    .fetch_one(&h.db)
    .await
    .unwrap();
    let project: db::ProjectId = sqlx::query_scalar(
        "INSERT INTO projects (user_id, slug, title) VALUES ($1, $2, 'P') RETURNING id",
    )
    .bind(owner)
    .bind(format!("lkproj_{tag}"))
    .fetch_one(&h.db)
    .await
    .unwrap();
    let item: db::ItemId = sqlx::query_scalar(
        "INSERT INTO items (project_id, title, item_type, price_cents, slug) \
         VALUES ($1, 'Plugin', 'plugin', 0, $2) RETURNING id",
    )
    .bind(project)
    .bind(format!("lkitem_{tag}"))
    .fetch_one(&h.db)
    .await
    .unwrap();
    let tx: db::TransactionId = sqlx::query_scalar(
        "INSERT INTO transactions (buyer_id, seller_id, item_id, amount_cents, status) \
         VALUES ($1, $1, $2, 0, 'completed') RETURNING id",
    )
    .bind(owner)
    .bind(item)
    .fetch_one(&h.db)
    .await
    .unwrap();
    (owner, item, tx)
}

#[tokio::test]
async fn create_license_key_is_idempotent_on_transaction_id() {
    let h = TestHarness::new().await;
    let (owner, item, tx) = keyed_item(&h, "idem").await;

    let code_a = KeyCode::from_trusted("aaaa-bbbb-cccc-dddd-eeee".to_string());
    let code_b = KeyCode::from_trusted("ffff-gggg-hhhh-iiii-jjjj".to_string());

    // First mint for the transaction.
    let first =
        db::license_keys::create_license_key(&h.db, item, owner, Some(tx), &code_a, Some(3))
            .await
            .unwrap();

    // A redelivered finalize for the SAME transaction must return the existing
    // key (Pay-M1), not error and not mint a second, even with a different code.
    let second =
        db::license_keys::create_license_key(&h.db, item, owner, Some(tx), &code_b, Some(3))
            .await
            .unwrap();

    assert_eq!(
        first.id, second.id,
        "a duplicate finalize must return the existing key"
    );
    let count = db::license_keys::count_keys_by_item(&h.db, item)
        .await
        .unwrap();
    assert_eq!(
        count, 1,
        "no second key may be minted for the same transaction"
    );
}

#[tokio::test]
async fn try_create_activation_enforces_max_activations() {
    let h = TestHarness::new().await;
    let (owner, item, tx) = keyed_item(&h, "cap").await;

    let code = KeyCode::from_trusted("kkkk-llll-mmmm-nnnn-oooo".to_string());
    let key = db::license_keys::create_license_key(&h.db, item, owner, Some(tx), &code, Some(2))
        .await
        .unwrap();

    // Two distinct machines fit under the cap of 2.
    assert!(
        db::license_keys::try_create_activation(&h.db, key.id, "machine-a", None)
            .await
            .unwrap()
            .is_some()
    );
    assert!(
        db::license_keys::try_create_activation(&h.db, key.id, "machine-b", None)
            .await
            .unwrap()
            .is_some()
    );
    // A third distinct machine is refused.
    assert!(
        db::license_keys::try_create_activation(&h.db, key.id, "machine-c", None)
            .await
            .unwrap()
            .is_none(),
        "activation past max_activations must be refused"
    );
    // Re-activating an existing machine is always allowed (no new slot consumed).
    assert!(
        db::license_keys::try_create_activation(&h.db, key.id, "machine-a", None)
            .await
            .unwrap()
            .is_some(),
        "re-activation of a known machine must always succeed"
    );

    // A revoked key activates nothing.
    db::license_keys::revoke_license_key(&h.db, key.id)
        .await
        .unwrap();
    assert!(
        db::license_keys::try_create_activation(&h.db, key.id, "machine-d", None)
            .await
            .unwrap()
            .is_none(),
        "a revoked key must not activate"
    );
}

// ── db::creator_tiers, storage-cap compare-and-set (Run 13 Test) ──
// The storage-cap enforcement was "illusory coverage": the atomic
// conditional-UPDATE was exercised only indirectly through upload flows. Pin the
// CAS contract at the DB layer: it must never let the cap be exceeded, including
// under a concurrent race.

#[tokio::test]
async fn storage_increment_succeeds_under_cap() {
    let mut h = TestHarness::new().await;
    let user = h.create_creator("storagecap_under").await;

    db::creator_tiers::try_increment_storage(&h.db, user, 400, 1000)
        .await
        .expect("an increment within the cap must succeed");

    let used: i64 = sqlx::query_scalar("SELECT storage_used_bytes FROM users WHERE id = $1")
        .bind(user)
        .fetch_one(&h.db)
        .await
        .unwrap();
    assert_eq!(used, 400);
}

#[tokio::test]
async fn storage_increment_past_cap_is_rejected_and_leaves_count_unchanged() {
    let mut h = TestHarness::new().await;
    let user = h.create_creator("storagecap_reject").await;

    db::creator_tiers::try_increment_storage(&h.db, user, 800, 1000)
        .await
        .unwrap();
    // 800 + 300 = 1100 > 1000 → rejected.
    assert!(
        db::creator_tiers::try_increment_storage(&h.db, user, 300, 1000)
            .await
            .is_err(),
        "an increment past the cap must fail"
    );

    let used: i64 = sqlx::query_scalar("SELECT storage_used_bytes FROM users WHERE id = $1")
        .bind(user)
        .fetch_one(&h.db)
        .await
        .unwrap();
    assert_eq!(
        used, 800,
        "a rejected increment must not change the stored count"
    );
}

#[tokio::test]
async fn storage_increment_concurrent_never_exceeds_cap() {
    let mut h = TestHarness::new().await;
    let user = h.create_creator("storagecap_race").await;

    // Cap 900, starting at 0. Two concurrent +500 increments: only one fits
    // (0+500 ok → 500; the other then sees 500, 500+500=1000 > 900 → rejected).
    let (p1, p2) = (h.db.clone(), h.db.clone());
    let a =
        tokio::spawn(
            async move { db::creator_tiers::try_increment_storage(&p1, user, 500, 900).await },
        );
    let b =
        tokio::spawn(
            async move { db::creator_tiers::try_increment_storage(&p2, user, 500, 900).await },
        );
    let (ra, rb) = (a.await.unwrap(), b.await.unwrap());

    let succeeded = [ra.is_ok(), rb.is_ok()].iter().filter(|x| **x).count();
    assert_eq!(
        succeeded, 1,
        "exactly one of two racing over-cap increments may succeed"
    );

    let used: i64 = sqlx::query_scalar("SELECT storage_used_bytes FROM users WHERE id = $1")
        .bind(user)
        .fetch_one(&h.db)
        .await
        .unwrap();
    assert_eq!(
        used, 500,
        "the cap holds under a race: total is 500, never 1000"
    );
}