//! Tip CRUD operations.

use sqlx::PgPool;

use super::id_types::{ProjectId, TipId, UserId};
use super::models::{DbTip, DbTipWithUser};
use super::validated_types::Cents;
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> {
    // Defense in depth: the route handler enforces a $1 minimum and the column
    // carries CHECK (amount_cents > 0), but guard here too so any future caller
    // gets a clean validation error instead of a raw constraint violation.
    if amount_cents <= 0 {
        return Err(crate::error::AppError::validation(
            "Tip amount must be positive",
        ));
    }
    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
            id AS "id: TipId", tipper_id AS "tipper_id: UserId", recipient_id AS "recipient_id: UserId",
            project_id AS "project_id: ProjectId", amount_cents AS "amount_cents: Cents", message,
            status AS "status: super::TransactionStatus", stripe_payment_intent_id, stripe_checkout_session_id,
            stripe_transfer_group,
            created_at AS "created_at: chrono::DateTime<chrono::Utc>",
            completed_at AS "completed_at: chrono::DateTime<chrono::Utc>"
        "#,
        tipper_id as UserId,
        recipient_id as UserId,
        project_id as Option<ProjectId>,
        amount_cents,
        message,
        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<'e>(
    executor: impl sqlx::PgExecutor<'e>,
    stripe_checkout_session_id: &str,
    stripe_payment_intent_id: Option<&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
            id AS "id: TipId", tipper_id AS "tipper_id: UserId", recipient_id AS "recipient_id: UserId",
            project_id AS "project_id: ProjectId", amount_cents AS "amount_cents: Cents", message,
            status AS "status: super::TransactionStatus", stripe_payment_intent_id, stripe_checkout_session_id,
            stripe_transfer_group,
            created_at AS "created_at: chrono::DateTime<chrono::Utc>",
            completed_at AS "completed_at: chrono::DateTime<chrono::Utc>"
        "#,
        stripe_checkout_session_id,
        stripe_payment_intent_id,
    )
    .fetch_optional(executor)
    .await?;

    Ok(tip)
}

/// Fetch a tip by its Stripe checkout session id, regardless of status.
///
/// Used for webhook crash recovery: when `complete_tip` returns `None` (the tip
/// was already flipped to completed by an earlier delivery), the handler re-reads
/// the tip here to re-run the idempotent split write, in case the first delivery
/// crashed after completing the tip but before recording splits.
#[tracing::instrument(skip(pool))]
pub async fn get_tip_by_session(
    pool: &PgPool,
    stripe_checkout_session_id: &str,
) -> Result<Option<DbTip>> {
    let tip = sqlx::query_as!(
        DbTip,
        r#"
        SELECT
            id AS "id: TipId", tipper_id AS "tipper_id: UserId", recipient_id AS "recipient_id: UserId",
            project_id AS "project_id: ProjectId", amount_cents AS "amount_cents: Cents", message,
            status AS "status: super::TransactionStatus", stripe_payment_intent_id, stripe_checkout_session_id,
            stripe_transfer_group,
            created_at AS "created_at: chrono::DateTime<chrono::Utc>",
            completed_at AS "completed_at: chrono::DateTime<chrono::Utc>"
        FROM tips
        WHERE stripe_checkout_session_id = $1
        "#,
        stripe_checkout_session_id,
    )
    .fetch_optional(pool)
    .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 AS "id: TipId", t.tipper_id AS "tipper_id: UserId", t.recipient_id AS "recipient_id: UserId",
               t.project_id AS "project_id: ProjectId", t.amount_cents AS "amount_cents: Cents",
               t.message, t.status AS "status: super::TransactionStatus",
               t.created_at AS "created_at: chrono::DateTime<chrono::Utc>",
               t.completed_at AS "completed_at: chrono::DateTime<chrono::Utc>",
               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
        "#,
        recipient_id as UserId,
        limit,
        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 total = sqlx::query_scalar!(
        r#"SELECT COALESCE(SUM(amount_cents), 0)::BIGINT AS "total!" FROM tips WHERE recipient_id = $1 AND status = 'completed'"#,
        recipient_id as UserId,
    )
    .fetch_one(pool)
    .await?;

    Ok(total)
}

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

    Ok(count)
}

/// 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'
        "#,
        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
            id AS "id: TipId", tipper_id AS "tipper_id: UserId", recipient_id AS "recipient_id: UserId",
            project_id AS "project_id: ProjectId", amount_cents AS "amount_cents: Cents", message,
            status AS "status: super::TransactionStatus", stripe_payment_intent_id, stripe_checkout_session_id,
            stripe_transfer_group,
            created_at AS "created_at: chrono::DateTime<chrono::Utc>",
            completed_at AS "completed_at: chrono::DateTime<chrono::Utc>"
        FROM tips
        WHERE tipper_id = $1 AND status = 'completed'
        ORDER BY created_at DESC
        LIMIT $2 OFFSET $3
        "#,
        tipper_id as UserId,
        limit,
        offset,
    )
    .fetch_all(pool)
    .await?;

    Ok(tips)
}