use super::{
    Cents, ClaimToken, DbTransaction, DownloadToken, ItemId, PgPool, ProjectId, PromoCodeId,
    Result, TransactionId, UserId,
};

/// Sum completed revenue and count sales for all items in a project.
///
/// Returns `(total_revenue_cents, total_sales)`. Only completed transactions
/// are counted; pending, failed, and refunded are excluded.
#[tracing::instrument(skip_all)]
pub async fn get_revenue_by_project(pool: &PgPool, project_id: ProjectId) -> Result<(i64, i64)> {
    let row = sqlx::query!(
        r#"
        SELECT
            COALESCE(SUM(t.amount_cents), 0)::BIGINT AS "total!",
            COUNT(*) AS "count!"
        FROM transactions t
        JOIN items i ON t.item_id = i.id
        WHERE i.project_id = $1
          AND t.status = 'completed'
        "#,
        project_id as ProjectId,
    )
    .fetch_one(pool)
    .await?;

    Ok((row.total, row.count))
}

/// Revenue per project for a given seller, returned as (project_id, title, revenue_cents).
/// Single query replaces N+1 loop in dashboard analytics.
#[tracing::instrument(skip_all)]
pub async fn get_revenue_by_user_projects(
    pool: &PgPool,
    user_id: UserId,
) -> Result<Vec<(ProjectId, String, i64)>> {
    let rows = sqlx::query!(
        r#"
        SELECT p.id AS "id: ProjectId", p.title, COALESCE(SUM(t.amount_cents), 0)::BIGINT AS "revenue!"
        FROM projects p
        LEFT JOIN items i ON i.project_id = p.id
        LEFT JOIN transactions t ON t.item_id = i.id AND t.status = 'completed'
        WHERE p.user_id = $1
        GROUP BY p.id, p.title
        HAVING COALESCE(SUM(t.amount_cents), 0) > 0
        ORDER BY COALESCE(SUM(t.amount_cents), 0) DESC
        "#,
        user_id as UserId,
    )
    .fetch_all(pool)
    .await?;

    Ok(rows
        .into_iter()
        .map(|r| (r.id, r.title, r.revenue))
        .collect())
}

/// Revenue and sales per project for a seller within a time range.
///
/// Used for the cross-project comparison table on the user analytics tab.
#[tracing::instrument(skip_all)]
pub async fn get_revenue_by_user_projects_in_range(
    pool: &PgPool,
    user_id: UserId,
    range: &crate::db::analytics::TimeRange,
) -> Result<Vec<(ProjectId, String, i64, i64)>> {
    let time_filter = match range.interval_sql() {
        Some(interval) => format!(" AND t.completed_at >= NOW() - INTERVAL '{interval}'"),
        None => String::new(),
    };

    let sql = format!(
        r"
        SELECT p.id, p.title,
               COALESCE(SUM(t.amount_cents), 0)::BIGINT,
               COUNT(t.id)::BIGINT
        FROM projects p
        LEFT JOIN items i ON i.project_id = p.id
        LEFT JOIN transactions t ON t.item_id = i.id AND t.status = 'completed'{time_filter}
        WHERE p.user_id = $1
        GROUP BY p.id, p.title
        ORDER BY COALESCE(SUM(t.amount_cents), 0) DESC
        "
    );

    // runtime-checked: dynamically-built SQL string, the `time_filter` clause is
    // conditionally interpolated via `format!`, so the query text isn't a literal
    // and can't be compile-checked by the macro.
    let rows: Vec<(ProjectId, String, i64, i64)> =
        sqlx::query_as(&sql).bind(user_id).fetch_all(pool).await?;

    Ok(rows)
}

/// Platform-wide revenue stats: total completed revenue, completed count, refunded count.
#[tracing::instrument(skip_all)]
pub async fn get_platform_revenue_stats(pool: &PgPool) -> Result<(i64, i64, i64)> {
    let row = sqlx::query!(
        r#"
        SELECT
            COALESCE(SUM(CASE WHEN status = 'completed' THEN amount_cents ELSE 0 END), 0)::BIGINT AS "revenue!",
            COALESCE(SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END), 0)::BIGINT AS "completed!",
            COALESCE(SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END), 0)::BIGINT AS "refunded!"
        FROM transactions
        "#,
    )
    .fetch_one(pool)
    .await?;

    Ok((row.revenue, row.completed, row.refunded))
}

/// Completed and refunded sales for a specific item, for the item dashboard Sales tab.
#[tracing::instrument(skip_all)]
pub async fn get_sales_by_item(
    pool: &PgPool,
    item_id: ItemId,
    seller_id: UserId,
) -> Result<Vec<DbTransaction>> {
    let rows = sqlx::query_as!(
        DbTransaction,
        r#"
        SELECT
            id AS "id: TransactionId", buyer_id AS "buyer_id: UserId", seller_id AS "seller_id: UserId",
            item_id AS "item_id: ItemId", amount_cents AS "amount_cents: Cents", platform_fee_cents AS "platform_fee_cents: Cents",
            currency, status AS "status: crate::db::TransactionStatus", stripe_payment_intent_id, stripe_checkout_session_id,
            created_at AS "created_at: chrono::DateTime<chrono::Utc>", completed_at AS "completed_at: chrono::DateTime<chrono::Utc>",
            item_title, seller_username, share_contact, project_id AS "project_id: ProjectId",
            parent_transaction_id AS "parent_transaction_id: TransactionId", promo_code_id AS "promo_code_id: PromoCodeId",
            guest_email, claim_token AS "claim_token: ClaimToken", claimed_by AS "claimed_by: UserId",
            download_token AS "download_token: DownloadToken"
        FROM transactions
        WHERE item_id = $1 AND seller_id = $2
          AND status IN ('completed', 'refunded')
        ORDER BY created_at DESC
        LIMIT 200
        "#,
        item_id as ItemId,
        seller_id as UserId,
    )
    .fetch_all(pool)
    .await?;

    Ok(rows)
}