//! Tip CRUD operations.

use sqlx::PgPool;

use super::id_types::*;
use super::models::*;
use crate::error::Result;

/// Create a pending tip record before redirecting to Stripe Checkout.
#[tracing::instrument(skip(pool))]
pub async fn create_tip(
    pool: &PgPool,
    tipper_id: UserId,
    recipient_id: UserId,
    project_id: Option<ProjectId>,
    amount_cents: i32,
    message: Option<&str>,
    stripe_checkout_session_id: &str,
) -> Result<DbTip> {
    let tip = sqlx::query_as::<_, DbTip>(
        r#"
        INSERT INTO tips (tipper_id, recipient_id, project_id, amount_cents, message, stripe_checkout_session_id)
        VALUES ($1, $2, $3, $4, $5, $6)
        RETURNING *
        "#,
    )
    .bind(tipper_id)
    .bind(recipient_id)
    .bind(project_id)
    .bind(amount_cents)
    .bind(message)
    .bind(stripe_checkout_session_id)
    .fetch_one(pool)
    .await?;

    Ok(tip)
}

/// Mark a tip as completed after Stripe confirms payment.
/// Returns `Some(tip)` if updated, `None` if already completed (idempotent).
#[tracing::instrument(skip(executor))]
pub async fn complete_tip(
    executor: impl sqlx::Executor<'_, Database = sqlx::Postgres>,
    stripe_checkout_session_id: &str,
    stripe_payment_intent_id: &str,
) -> Result<Option<DbTip>> {
    let tip = sqlx::query_as::<_, DbTip>(
        r#"
        UPDATE tips
        SET status = 'completed',
            stripe_payment_intent_id = $2,
            completed_at = NOW()
        WHERE stripe_checkout_session_id = $1
          AND status = 'pending'
        RETURNING *
        "#,
    )
    .bind(stripe_checkout_session_id)
    .bind(stripe_payment_intent_id)
    .fetch_optional(executor)
    .await?;

    Ok(tip)
}

/// Get tips received by a creator, most recent first.
#[tracing::instrument(skip(pool))]
pub async fn get_tips_received(
    pool: &PgPool,
    recipient_id: UserId,
    limit: i64,
    offset: i64,
) -> Result<Vec<DbTipWithUser>> {
    let tips = sqlx::query_as::<_, DbTipWithUser>(
        r#"
        SELECT t.id, t.tipper_id, t.recipient_id, t.project_id, t.amount_cents,
               t.message, t.status, t.created_at, t.completed_at,
               u.username AS tipper_username, u.display_name AS tipper_display_name
        FROM tips t
        JOIN users u ON u.id = t.tipper_id
        WHERE t.recipient_id = $1 AND t.status = 'completed'
        ORDER BY t.created_at DESC
        LIMIT $2 OFFSET $3
        "#,
    )
    .bind(recipient_id)
    .bind(limit)
    .bind(offset)
    .fetch_all(pool)
    .await?;

    Ok(tips)
}

/// Total tip revenue received by a creator (completed tips only).
#[tracing::instrument(skip(pool))]
pub async fn total_tips_received(pool: &PgPool, recipient_id: UserId) -> Result<i64> {
    let row: (Option<i64>,) = sqlx::query_as(
        "SELECT SUM(amount_cents)::BIGINT FROM tips WHERE recipient_id = $1 AND status = 'completed'",
    )
    .bind(recipient_id)
    .fetch_one(pool)
    .await?;

    Ok(row.0.unwrap_or(0))
}

/// Count of completed tips received.
#[tracing::instrument(skip(pool))]
pub async fn count_tips_received(pool: &PgPool, recipient_id: UserId) -> Result<i64> {
    let row: (i64,) = sqlx::query_as(
        "SELECT COUNT(*) FROM tips WHERE recipient_id = $1 AND status = 'completed'",
    )
    .bind(recipient_id)
    .fetch_one(pool)
    .await?;

    Ok(row.0)
}

/// Mark a tip as refunded by payment intent ID.
/// Returns true if a tip was refunded, false if not found (idempotent).
#[tracing::instrument(skip(pool))]
pub async fn refund_tip_by_payment_intent(pool: &PgPool, payment_intent_id: &str) -> Result<bool> {
    let result = sqlx::query(
        r#"
        UPDATE tips
        SET status = 'refunded'
        WHERE stripe_payment_intent_id = $1 AND status = 'completed'
        "#,
    )
    .bind(payment_intent_id)
    .execute(pool)
    .await?;

    Ok(result.rows_affected() > 0)
}

/// Get tips sent by a user, most recent first.
#[allow(dead_code)]
#[tracing::instrument(skip(pool))]
pub async fn get_tips_sent(
    pool: &PgPool,
    tipper_id: UserId,
    limit: i64,
    offset: i64,
) -> Result<Vec<DbTip>> {
    let tips = sqlx::query_as::<_, DbTip>(
        r#"
        SELECT * FROM tips
        WHERE tipper_id = $1 AND status = 'completed'
        ORDER BY created_at DESC
        LIMIT $2 OFFSET $3
        "#,
    )
    .bind(tipper_id)
    .bind(limit)
    .bind(offset)
    .fetch_all(pool)
    .await?;

    Ok(tips)
}