//! Integration tests for creator tier storage enforcement (Phase 11C).
//!
//! Tests use raw SQL for setup/verification and HTTP endpoints for upload flows
//! since `db::creator_tiers` is crate-private.

use crate::harness::TestHarness;
use makenotwork::db::UserId;
use serde_json::{json, Value};

// =============================================================================
// Helpers
// =============================================================================

/// Create a creator with no subscription. Returns user_id.
async fn setup_creator_no_tier(h: &mut TestHarness, username: &str) -> UserId {
    h.create_creator(username).await
}

/// Give a user an active creator subscription at the given tier.
async fn give_subscription(h: &TestHarness, user_id: UserId, tier: &str) {
    sqlx::query(
        r#"INSERT INTO creator_subscriptions (user_id, stripe_subscription_id, stripe_customer_id, tier, status)
           VALUES ($1, 'sub_fake_' || $1::text, 'cus_fake_' || $1::text, $2, 'active')
           ON CONFLICT (user_id) DO UPDATE SET tier = $2, status = 'active'"#,
    )
    .bind(user_id)
    .bind(tier)
    .execute(&h.db)
    .await
    .expect("give_subscription");

    // Sync denormalized column
    sqlx::query("UPDATE users SET creator_tier = $2 WHERE id = $1")
        .bind(user_id)
        .bind(tier)
        .execute(&h.db)
        .await
        .expect("sync creator_tier");
}

/// Set grandfathered_until for a user.
async fn set_grandfathered(h: &TestHarness, user_id: UserId, until: &str) {
    sqlx::query("UPDATE users SET grandfathered_until = $2::timestamptz WHERE id = $1")
        .bind(user_id)
        .bind(until)
        .execute(&h.db)
        .await
        .expect("set_grandfathered");
}

/// Set storage_used_bytes for a user.
async fn set_storage_used(h: &TestHarness, user_id: UserId, bytes: i64) {
    sqlx::query("UPDATE users SET storage_used_bytes = $2 WHERE id = $1")
        .bind(user_id)
        .bind(bytes)
        .execute(&h.db)
        .await
        .expect("set_storage_used");
}

/// Get storage_used_bytes for a user.
async fn get_storage_used(h: &TestHarness, user_id: UserId) -> i64 {
    sqlx::query_scalar::<_, i64>("SELECT storage_used_bytes FROM users WHERE id = $1")
        .bind(user_id)
        .fetch_one(&h.db)
        .await
        .expect("get_storage_used")
}

/// Cancel a user's subscription (set status + canceled_at).
async fn cancel_subscription(h: &TestHarness, user_id: UserId, days_ago: i32) {
    sqlx::query(
        r#"UPDATE creator_subscriptions
           SET status = 'canceled', canceled_at = NOW() - ($2 || ' days')::interval
           WHERE user_id = $1"#,
    )
    .bind(user_id)
    .bind(days_ago)
    .execute(&h.db)
    .await
    .expect("cancel_subscription");

    // Clear denormalized tier
    sqlx::query("UPDATE users SET creator_tier = NULL WHERE id = $1")
        .bind(user_id)
        .execute(&h.db)
        .await
        .expect("clear creator_tier");
}

/// Set max_file_override_bytes for a user.
async fn set_file_override(h: &TestHarness, user_id: UserId, bytes: Option<i64>) {
    sqlx::query("UPDATE users SET max_file_override_bytes = $2 WHERE id = $1")
        .bind(user_id)
        .bind(bytes)
        .execute(&h.db)
        .await
        .expect("set_file_override");
}

/// Create a creator with a project and item, trusted for uploads.
async fn setup_creator_with_item(
    h: &mut TestHarness,
    username: &str,
) -> (UserId, String, String) {
    let setup = h.create_creator_with_item(username, "audio", 0).await;
    h.trust_user(setup.user_id).await;
    (setup.user_id, setup.project_id, setup.item_id)
}

/// Presign + upload + confirm an audio file. Returns the confirm response status.
async fn presign_upload_confirm(
    h: &mut TestHarness,
    item_id: &str,
    file_bytes: &[u8],
) -> (u16, String) {
    let body = json!({
        "item_id": item_id,
        "file_type": "audio",
        "file_name": "test.mp3",
        "content_type": "audio/mpeg",
    });
    let resp = h.client.post_json("/api/upload/presign", &body.to_string()).await;
    if !resp.status.is_success() {
        return (resp.status.as_u16(), resp.text);
    }
    let data: Value = resp.json();
    let s3_key = data["s3_key"].as_str().unwrap().to_string();

    // Simulate client upload to S3
    h.storage.as_ref().unwrap().put(&s3_key, file_bytes.to_vec());

    // Confirm
    let body = json!({
        "item_id": item_id,
        "file_type": "audio",
        "s3_key": s3_key,
    });
    let resp = h.client.post_json("/api/upload/confirm", &body.to_string()).await;
    (resp.status.as_u16(), resp.text)
}

// =============================================================================
// Tests
// =============================================================================

/// No tier → file upload rejected.
#[tokio::test]
async fn upload_without_subscription_rejected() {
    let mut h = TestHarness::with_storage().await;
    let (user_id, _, item_id) = setup_creator_with_item(&mut h, "notier").await;
    let _ = user_id;

    let (status, body) = presign_upload_confirm(&mut h, &item_id, b"fake audio").await;
    assert!(status == 400 || status == 403, "Expected rejection, got {status}: {body}");
    assert!(
        body.contains("subscription is required") || body.contains("tier"),
        "Expected tier error, got: {body}"
    );
}

/// Basic tier → audio upload rejected ("text-only").
#[tokio::test]
async fn upload_with_basic_tier_file_rejected() {
    let mut h = TestHarness::with_storage().await;
    let (user_id, _, item_id) = setup_creator_with_item(&mut h, "basicup").await;
    give_subscription(&h, user_id, "basic").await;

    let (status, body) = presign_upload_confirm(&mut h, &item_id, b"fake audio").await;
    assert!(status == 400 || status == 403, "Expected rejection, got {status}: {body}");
    assert!(body.contains("text-only"), "Expected text-only error, got: {body}");
}

/// Basic tier → cover upload succeeds (covers bypass tier).
#[tokio::test]
async fn upload_with_basic_tier_cover_succeeds() {
    let mut h = TestHarness::with_storage().await;
    let (user_id, _, item_id) = setup_creator_with_item(&mut h, "basiccov").await;
    give_subscription(&h, user_id, "basic").await;

    // Presign a cover via the dedicated item-image route (covers no longer go
    // through the generic /api/upload/confirm — see storage::confirm_upload_rejects_cover).
    let body = json!({
        "item_id": item_id,
        "file_name": "cover.jpg",
        "content_type": "image/jpeg",
    });
    let resp = h.client.post_json("/api/items/image/presign", &body.to_string()).await;
    assert!(resp.status.is_success(), "Cover presign failed: {}", resp.text);
    let data: Value = resp.json();
    let s3_key = data["s3_key"].as_str().unwrap().to_string();

    // Upload and confirm
    h.storage.as_ref().unwrap().put(&s3_key, b"fake jpeg bytes".to_vec());
    let body = json!({
        "item_id": item_id,
        "s3_key": s3_key,
    });
    let resp = h.client.post_json("/api/items/image/confirm", &body.to_string()).await;
    assert!(resp.status.is_success(), "Cover confirm should succeed: {}", resp.text);
}

/// SmallFiles → upload succeeds + storage_used_bytes incremented.
#[tokio::test]
async fn upload_with_small_files_succeeds() {
    let mut h = TestHarness::with_storage().await;
    let (user_id, _, item_id) = setup_creator_with_item(&mut h, "smfile").await;
    give_subscription(&h, user_id, "small_files").await;

    let before = get_storage_used(&h, user_id).await;
    let file_bytes = vec![0u8; 1024]; // 1 KB
    let (status, body) = presign_upload_confirm(&mut h, &item_id, &file_bytes).await;
    assert!(status == 200, "SmallFiles upload should succeed: {body}");

    let after = get_storage_used(&h, user_id).await;
    assert!(after > before, "Storage should be incremented (before={before}, after={after})");
}

/// File exceeding tier per-file max rejected.
#[tokio::test]
async fn per_file_limit_enforced() {
    let mut h = TestHarness::with_storage().await;
    let (user_id, _, item_id) = setup_creator_with_item(&mut h, "bigfile").await;
    give_subscription(&h, user_id, "small_files").await;

    // SmallFiles max is 500 MB per-file. We can't fake S3 object_size to
    // be >500MB in memory, so we test the storage cap enforcement path instead.
    // SmallFiles storage cap is 250 GB.
    let near_cap = 250 * 1024 * 1024 * 1024_i64 - 100; // 250GB - 100 bytes (SmallFiles cap)
    set_storage_used(&h, user_id, near_cap).await;

    let file_bytes = vec![0u8; 1024]; // 1 KB — within per-file limit but exceeds cap
    let (status, body) = presign_upload_confirm(&mut h, &item_id, &file_bytes).await;
    assert!(status == 400 || status == 413, "Expected rejection, got {status}: {body}");
    assert!(body.contains("storage"), "Expected storage cap error, got: {body}");
}

/// storage_used near cap → upload rejected.
#[tokio::test]
async fn storage_cap_enforced() {
    let mut h = TestHarness::with_storage().await;
    let (user_id, _, item_id) = setup_creator_with_item(&mut h, "capenf").await;
    give_subscription(&h, user_id, "small_files").await;

    // SmallFiles cap is 250 GB. Set usage to just under cap.
    let near_cap = 250 * 1024 * 1024 * 1024_i64 - 1;
    set_storage_used(&h, user_id, near_cap).await;

    let file_bytes = vec![0u8; 2048]; // 2 KB — pushes over the cap
    let (status, body) = presign_upload_confirm(&mut h, &item_id, &file_bytes).await;
    assert!(status == 400, "Expected rejection for storage cap, got {status}: {body}");
    assert!(body.contains("storage"), "Expected storage cap error, got: {body}");
}

/// Upload then delete → storage decremented after purge.
///
/// Soft-delete does not immediately reclaim storage — the scheduler purges
/// items after a 7-day grace window. This test fast-forwards deleted_at to
/// simulate the grace period expiring, then runs the purge.
#[tokio::test]
async fn delete_decrements_storage() {
    let mut h = TestHarness::with_storage().await;
    let (user_id, _, item_id) = setup_creator_with_item(&mut h, "deldec").await;
    give_subscription(&h, user_id, "small_files").await;

    // Upload a file
    let file_bytes = vec![0u8; 1024];
    let (status, _) = presign_upload_confirm(&mut h, &item_id, &file_bytes).await;
    assert_eq!(status, 200, "Upload should succeed");

    let after_upload = get_storage_used(&h, user_id).await;
    assert!(after_upload > 0, "Storage should be > 0 after upload");

    // Soft-delete the item
    let resp = h.client.delete(&format!("/api/items/{item_id}")).await;
    assert!(resp.status.is_success(), "Delete failed: {}", resp.text);

    // Storage unchanged immediately after soft-delete
    let after_soft_delete = get_storage_used(&h, user_id).await;
    assert_eq!(after_soft_delete, after_upload, "Storage unchanged after soft-delete");

    // Fast-forward: backdate deleted_at past the 7-day grace window
    let item_uuid: uuid::Uuid = item_id.parse().unwrap();
    sqlx::query("UPDATE items SET deleted_at = NOW() - INTERVAL '8 days' WHERE id = $1")
        .bind(item_uuid)
        .execute(&h.db)
        .await
        .unwrap();

    // Run the purge (same function the scheduler calls)
    let purged = makenotwork::db::items::purge_expired_deleted_items(&h.db).await.unwrap();
    assert_eq!(purged, 1, "Should purge 1 item");

    // Recalculate storage (purge deletes the row but doesn't adjust the counter;
    // the weekly drift correction handles that). Simulate via direct SQL.
    sqlx::query(
        r#"
        UPDATE users SET storage_used_bytes = COALESCE((
            SELECT SUM(i.audio_file_size_bytes)::BIGINT
            FROM items i JOIN projects p ON i.project_id = p.id
            WHERE p.user_id = users.id AND i.audio_file_size_bytes IS NOT NULL
        ), 0) WHERE id = $1
        "#,
    )
    .bind(user_id)
    .execute(&h.db)
    .await
    .unwrap();

    let after_purge = get_storage_used(&h, user_id).await;
    assert!(after_purge < after_upload, "Storage should decrease after purge (before={after_upload}, after={after_purge})");
}

/// grandfathered_until in future → upload succeeds as SmallFiles-equivalent.
#[tokio::test]
async fn grandfathered_creator_can_upload() {
    let mut h = TestHarness::with_storage().await;
    let (user_id, _, item_id) = setup_creator_with_item(&mut h, "grandok").await;

    // No subscription, but grandfathered until next year
    set_grandfathered(&h, user_id, "2027-01-01T00:00:00Z").await;

    let file_bytes = vec![0u8; 1024];
    let (status, body) = presign_upload_confirm(&mut h, &item_id, &file_bytes).await;
    assert_eq!(status, 200, "Grandfathered creator should upload: {body}");
}

/// grandfathered_until in past → rejected.
#[tokio::test]
async fn expired_grandfathering_rejected() {
    let mut h = TestHarness::with_storage().await;
    let (user_id, _, item_id) = setup_creator_with_item(&mut h, "grandexp").await;

    // Grandfathering expired
    set_grandfathered(&h, user_id, "2020-01-01T00:00:00Z").await;

    let file_bytes = vec![0u8; 1024];
    let (status, body) = presign_upload_confirm(&mut h, &item_id, &file_bytes).await;
    assert!(status == 400 || status == 403, "Expected rejection, got {status}: {body}");
}

/// Admin file override allows larger files than the tier normally permits.
#[tokio::test]
async fn admin_file_override_allows_larger() {
    let mut h = TestHarness::with_storage().await;
    let (user_id, _, item_id) = setup_creator_with_item(&mut h, "oversize").await;
    give_subscription(&h, user_id, "small_files").await;

    // Set override to 2 GB
    let two_gb = 2 * 1024 * 1024 * 1024_i64;
    set_file_override(&h, user_id, Some(two_gb)).await;

    // Normal upload should still work
    let file_bytes = vec![0u8; 2048];
    let (status, body) = presign_upload_confirm(&mut h, &item_id, &file_bytes).await;
    assert_eq!(status, 200, "Upload with override should succeed: {body}");
}

/// Canceled subscription → upload rejected.
#[tokio::test]
async fn canceled_subscription_blocks_upload() {
    let mut h = TestHarness::with_storage().await;
    let (user_id, _, item_id) = setup_creator_with_item(&mut h, "cancld").await;
    give_subscription(&h, user_id, "small_files").await;

    // Cancel 5 days ago (within grace period)
    cancel_subscription(&h, user_id, 5).await;

    let file_bytes = vec![0u8; 1024];
    let (status, body) = presign_upload_confirm(&mut h, &item_id, &file_bytes).await;
    assert!(status == 400 || status == 403, "Expected rejection, got {status}: {body}");
}

/// Manually set wrong storage_used → recalculate (via SQL) fixes it.
#[tokio::test]
async fn recalculate_storage_corrects_drift() {
    let mut h = TestHarness::new().await;
    let user_id = setup_creator_no_tier(&mut h, "drift").await;

    // Set storage to wrong value
    set_storage_used(&h, user_id, 999_999_999).await;
    assert_eq!(get_storage_used(&h, user_id).await, 999_999_999);

    // Run the recalculation query directly (same as db::creator_tiers::recalculate_storage_used)
    let total: i64 = sqlx::query_scalar(
        r#"
        WITH version_bytes AS (
            SELECT COALESCE(SUM(v.file_size_bytes)::BIGINT, 0) AS total
            FROM versions v
            JOIN items i ON v.item_id = i.id
            JOIN projects p ON i.project_id = p.id
            WHERE p.user_id = $1 AND v.file_size_bytes IS NOT NULL
        ),
        insertion_bytes AS (
            SELECT COALESCE(SUM(ci.file_size)::BIGINT, 0) AS total
            FROM content_insertions ci
            WHERE ci.user_id = $1
        ),
        audio_bytes AS (
            SELECT COALESCE(SUM(i.audio_file_size_bytes)::BIGINT, 0) AS total
            FROM items i
            JOIN projects p ON i.project_id = p.id
            WHERE p.user_id = $1 AND i.audio_file_size_bytes IS NOT NULL
        ),
        cover_bytes AS (
            SELECT COALESCE(SUM(i.cover_file_size_bytes)::BIGINT, 0) AS total
            FROM items i
            JOIN projects p ON i.project_id = p.id
            WHERE p.user_id = $1 AND i.cover_file_size_bytes IS NOT NULL
        )
        SELECT ((SELECT total FROM version_bytes)
             + (SELECT total FROM insertion_bytes)
             + (SELECT total FROM audio_bytes)
             + (SELECT total FROM cover_bytes))::BIGINT AS total
        "#,
    )
    .bind(user_id)
    .fetch_one(&h.db)
    .await
    .expect("recalculate query");

    sqlx::query("UPDATE users SET storage_used_bytes = $2 WHERE id = $1")
        .bind(user_id)
        .bind(total)
        .execute(&h.db)
        .await
        .expect("update storage");

    assert_eq!(total, 0, "User has no files, should be 0");
    assert_eq!(get_storage_used(&h, user_id).await, 0);
}