//! Analytics query verification: insert transactions + follows via SQL,
//! then run the same aggregate queries used by db/analytics.rs to confirm
//! they produce correct results against real PostgreSQL.

use crate::harness::TestHarness;
use makenotwork::db::{ItemId, ProjectId, UserId};
use sqlx::PgPool;
use std::sync::atomic::{AtomicU32, Ordering};

/// Monotonic counter for unique buyer usernames across all tests.
static BUYER_COUNTER: AtomicU32 = AtomicU32::new(0);

/// Returns an ISO 8601 timestamp for `days` days ago at the given `hour` (UTC).
/// Uses date-only arithmetic so two calls with the same `days` but different
/// `hour` values always land on the same calendar day.
fn days_ago_at(days: u32, hour: u32) -> String {
    let date = (chrono::Utc::now() - chrono::Duration::days(days as i64)).date_naive();
    format!("{}T{:02}:00:00Z", date, hour)
}

/// Create a unique buyer user via direct SQL. Avoids the partial unique index
/// on transactions(buyer_id, item_id) WHERE status = 'completed' by giving
/// each transaction its own buyer.
async fn create_buyer(pool: &PgPool) -> UserId {
    let n = BUYER_COUNTER.fetch_add(1, Ordering::Relaxed);
    let id = UserId::new();
    sqlx::query(
        "INSERT INTO users (id, username, email, password_hash) VALUES ($1, $2, $3, 'not-a-real-hash')",
    )
    .bind(id)
    .bind(format!("abuyer{n}"))
    .bind(format!("abuyer{n}@test.com"))
    .execute(pool)
    .await
    .expect("create buyer");
    id
}

/// Insert a completed transaction with a specific completed_at timestamp.
async fn insert_transaction(
    pool: &PgPool,
    seller_id: UserId,
    item_id: ItemId,
    amount_cents: i32,
    completed_at: &str,
) {
    let buyer_id = create_buyer(pool).await;
    sqlx::query(
        r#"
        INSERT INTO transactions
            (buyer_id, seller_id, item_id, amount_cents, platform_fee_cents,
             stripe_checkout_session_id, status, completed_at, item_title, seller_username, share_contact)
        VALUES ($1, $2, $3, $4, 0, $5, 'completed', $6::timestamptz, 'Test', 'seller', false)
        "#,
    )
    .bind(buyer_id)
    .bind(seller_id)
    .bind(item_id)
    .bind(amount_cents)
    .bind(format!("test-{}-{}", buyer_id, completed_at))
    .bind(completed_at)
    .execute(pool)
    .await
    .expect("insert transaction");
}

/// Insert a follow with a specific created_at timestamp.
async fn insert_follow(pool: &PgPool, follower_id: UserId, target_type: &str, target_id: uuid::Uuid, created_at: &str) {
    sqlx::query(
        r#"
        INSERT INTO follows (follower_id, target_type, target_id, created_at)
        VALUES ($1, $2, $3, $4::timestamptz)
        "#,
    )
    .bind(follower_id)
    .bind(target_type)
    .bind(target_id)
    .bind(created_at)
    .execute(pool)
    .await
    .expect("insert follow");
}

/// Create a seller user, project, and item via direct SQL. Returns (seller_id, project_id, item_id).
async fn setup_seller_with_item(h: &mut TestHarness, suffix: &str) -> (UserId, ProjectId, ItemId) {
    let seller_id = h.signup(
        &format!("aseller{suffix}"),
        &format!("aseller{suffix}@test.com"),
        "password123",
    ).await;
    h.grant_creator(seller_id).await;

    let project_id = ProjectId::new();
    let item_id = ItemId::new();

    sqlx::query("INSERT INTO projects (id, user_id, slug, title) VALUES ($1, $2, $3, 'Test Project')")
        .bind(project_id)
        .bind(seller_id)
        .bind(format!("proj-{suffix}"))
        .execute(&h.db)
        .await
        .unwrap();

    sqlx::query("INSERT INTO items (id, project_id, title, price_cents, item_type, slug) VALUES ($1, $2, 'Test Item', 1000, 'digital', 'test-item-' || $3)")
        .bind(item_id)
        .bind(project_id)
        .bind(suffix)
        .execute(&h.db)
        .await
        .unwrap();

    (seller_id, project_id, item_id)
}

#[tokio::test]
async fn revenue_timeseries_buckets_by_day() {
    let mut h = TestHarness::new().await;
    let (seller_id, _, item_id) = setup_seller_with_item(&mut h, "ts1").await;

    // Insert 3 transactions across 2 days (all within 30-day window)
    let day_a = days_ago_at(2, 12);
    let day_a2 = days_ago_at(2, 14);
    let day_b = days_ago_at(3, 10);
    insert_transaction(&h.db, seller_id, item_id, 1000, &day_a).await;
    insert_transaction(&h.db, seller_id, item_id, 2000, &day_a2).await;
    insert_transaction(&h.db, seller_id, item_id, 500, &day_b).await;

    // Timeseries bucketed by day, user scope, 30d range
    let rows: Vec<(chrono::DateTime<chrono::Utc>, i64, i64)> = sqlx::query_as(
        r#"
        SELECT
            date_trunc('day', completed_at) AS bucket,
            COALESCE(SUM(amount_cents), 0),
            COUNT(*)
        FROM transactions
        WHERE seller_id = $1
          AND status = 'completed'
          AND completed_at >= NOW() - INTERVAL '30 days'
        GROUP BY bucket
        ORDER BY bucket
        LIMIT 500
        "#,
    )
    .bind(seller_id)
    .fetch_all(&h.db)
    .await
    .unwrap();

    assert_eq!(rows.len(), 2, "Expected 2 daily buckets, got {}", rows.len());

    // Earlier day: 1 sale, 500 cents
    assert_eq!(rows[0].1, 500);
    assert_eq!(rows[0].2, 1);

    // Later day: 2 sales, 3000 cents
    assert_eq!(rows[1].1, 3000);
    assert_eq!(rows[1].2, 2);
}

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

    let seller_id = h.signup("aseller_scope", "aseller_scope@test.com", "password123").await;
    h.grant_creator(seller_id).await;

    let project_id = ProjectId::new();
    let item_a = ItemId::new();
    let item_b = ItemId::new();

    sqlx::query("INSERT INTO projects (id, user_id, slug, title) VALUES ($1, $2, 'scope-test', 'Scope Test')")
        .bind(project_id)
        .bind(seller_id)
        .execute(&h.db)
        .await
        .unwrap();

    sqlx::query("INSERT INTO items (id, project_id, title, price_cents, item_type, slug) VALUES ($1, $2, 'Item A', 1000, 'digital', 'item-a')")
        .bind(item_a)
        .bind(project_id)
        .execute(&h.db)
        .await
        .unwrap();

    sqlx::query("INSERT INTO items (id, project_id, title, price_cents, item_type, slug) VALUES ($1, $2, 'Item B', 2000, 'digital', 'item-b')")
        .bind(item_b)
        .bind(project_id)
        .execute(&h.db)
        .await
        .unwrap();

    // Transactions: item_a on day_b and day_a, item_b on day_a only
    let day_a = days_ago_at(2, 12);
    let day_a2 = days_ago_at(2, 13);
    let day_b = days_ago_at(3, 12);
    insert_transaction(&h.db, seller_id, item_a, 1000, &day_b).await;
    insert_transaction(&h.db, seller_id, item_a, 1000, &day_a).await;
    insert_transaction(&h.db, seller_id, item_b, 2000, &day_a2).await;

    // Item scope: only item_a
    let rows: Vec<(chrono::DateTime<chrono::Utc>, i64, i64)> = sqlx::query_as(
        r#"
        SELECT
            date_trunc('day', completed_at) AS bucket,
            COALESCE(SUM(amount_cents), 0),
            COUNT(*)
        FROM transactions
        WHERE seller_id = $1
          AND item_id = $2
          AND status = 'completed'
          AND completed_at >= NOW() - INTERVAL '30 days'
        GROUP BY bucket
        ORDER BY bucket
        LIMIT 500
        "#,
    )
    .bind(seller_id)
    .bind(item_a)
    .fetch_all(&h.db)
    .await
    .unwrap();

    assert_eq!(rows.len(), 2, "Item A should have 2 daily buckets");
    assert_eq!(rows[0].1, 1000);
    assert_eq!(rows[1].1, 1000);

    // Project scope: both items
    let rows: Vec<(chrono::DateTime<chrono::Utc>, i64, i64)> = sqlx::query_as(
        r#"
        SELECT
            date_trunc('day', t.completed_at) AS bucket,
            COALESCE(SUM(t.amount_cents), 0),
            COUNT(*)
        FROM transactions t
        WHERE t.seller_id = $1
          AND t.item_id IN (SELECT id FROM items WHERE project_id = $2)
          AND t.status = 'completed'
          AND t.completed_at >= NOW() - INTERVAL '30 days'
        GROUP BY bucket
        ORDER BY bucket
        LIMIT 500
        "#,
    )
    .bind(seller_id)
    .bind(project_id)
    .fetch_all(&h.db)
    .await
    .unwrap();

    assert_eq!(rows.len(), 2, "Project scope should have 2 daily buckets");
    assert_eq!(rows[0].1, 1000, "Earlier day: only item_a");
    assert_eq!(rows[1].1, 3000, "Later day: item_a + item_b");
    assert_eq!(rows[1].2, 2, "Later day: 2 sales total");
}

#[tokio::test]
async fn period_comparison_current_vs_previous() {
    let mut h = TestHarness::new().await;
    let (seller_id, _, item_id) = setup_seller_with_item(&mut h, "cmp1").await;

    // Current period (within last 7 days): 2 sales, 3000 cents
    insert_transaction(&h.db, seller_id, item_id, 1000, &days_ago_at(2, 12)).await;
    insert_transaction(&h.db, seller_id, item_id, 2000, &days_ago_at(3, 12)).await;

    // Previous period (8-14 days ago): 1 sale, 500 cents
    insert_transaction(&h.db, seller_id, item_id, 500, &days_ago_at(10, 12)).await;

    // Period comparison with FILTER, user scope, 7d
    let row: (i64, i64, i64, i64) = sqlx::query_as(
        r#"
        SELECT
            COALESCE(SUM(amount_cents) FILTER (WHERE completed_at >= NOW() - INTERVAL '7 days'), 0),
            COUNT(*) FILTER (WHERE completed_at >= NOW() - INTERVAL '7 days'),
            COALESCE(SUM(amount_cents) FILTER (WHERE completed_at < NOW() - INTERVAL '7 days'), 0),
            COUNT(*) FILTER (WHERE completed_at < NOW() - INTERVAL '7 days')
        FROM transactions
        WHERE seller_id = $1
          AND status = 'completed'
          AND completed_at >= NOW() - INTERVAL '7 days' * 2
        "#,
    )
    .bind(seller_id)
    .fetch_one(&h.db)
    .await
    .unwrap();

    assert_eq!(row.0, 3000, "Current revenue");
    assert_eq!(row.1, 2, "Current sales");
    assert_eq!(row.2, 500, "Previous revenue");
    assert_eq!(row.3, 1, "Previous sales");
}

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

    let seller_id = h.signup("aseller_fol", "aseller_fol@test.com", "password123").await;
    h.grant_creator(seller_id).await;

    // Create follower users via direct SQL
    let fan1 = create_buyer(&h.db).await;
    let fan2 = create_buyer(&h.db).await;
    let fan3 = create_buyer(&h.db).await;

    let seller_uuid: uuid::Uuid = seller_id.into();

    // Current period follows (within last 30 days)
    insert_follow(&h.db, fan1, "user", seller_uuid, &days_ago_at(3, 12)).await;
    insert_follow(&h.db, fan2, "user", seller_uuid, &days_ago_at(5, 12)).await;

    // Previous period follow (31-60 days ago)
    insert_follow(&h.db, fan3, "user", seller_uuid, &days_ago_at(35, 12)).await;

    // Follower comparison, user scope, 30d
    let row: (i64, i64) = sqlx::query_as(
        r#"
        SELECT
            COUNT(*) FILTER (WHERE created_at >= NOW() - INTERVAL '30 days'),
            COUNT(*) FILTER (WHERE created_at < NOW() - INTERVAL '30 days')
        FROM follows
        WHERE target_type = $1
          AND target_id = $2
          AND created_at >= NOW() - INTERVAL '30 days' * 2
        "#,
    )
    .bind("user")
    .bind(seller_uuid)
    .fetch_one(&h.db)
    .await
    .unwrap();

    assert_eq!(row.0, 2, "Current period: 2 new followers");
    assert_eq!(row.1, 1, "Previous period: 1 follower");
}

#[tokio::test]
async fn timeseries_empty_returns_no_rows() {
    let mut h = TestHarness::new().await;
    let seller_id = h.signup("aseller_empty", "aseller_empty@test.com", "password123").await;

    // No transactions — timeseries should be empty
    let rows: Vec<(chrono::DateTime<chrono::Utc>, i64, i64)> = sqlx::query_as(
        r#"
        SELECT
            date_trunc('day', completed_at) AS bucket,
            COALESCE(SUM(amount_cents), 0),
            COUNT(*)
        FROM transactions
        WHERE seller_id = $1
          AND status = 'completed'
          AND completed_at >= NOW() - INTERVAL '30 days'
        GROUP BY bucket
        ORDER BY bucket
        LIMIT 500
        "#,
    )
    .bind(seller_id)
    .fetch_all(&h.db)
    .await
    .unwrap();

    assert!(rows.is_empty(), "Empty seller should have no buckets");

    // All-time aggregate should return zeros
    let row: (i64, i64) = sqlx::query_as(
        r#"
        SELECT
            COALESCE(SUM(amount_cents), 0),
            COUNT(*)
        FROM transactions
        WHERE seller_id = $1 AND status = 'completed'
        "#,
    )
    .bind(seller_id)
    .fetch_one(&h.db)
    .await
    .unwrap();

    assert_eq!(row.0, 0, "Revenue should be 0");
    assert_eq!(row.1, 0, "Sales should be 0");
}