//! The pending half of the lifecycle: write a row before the buyer leaves for
//! Stripe, flip it on their return, and delete it if they never come back.
//!
//! Every delete path here returns the row's `promo_code_id` so the caller
//! releases the reservation with the row.

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

/// Parameters for creating a pending Stripe checkout transaction.
pub struct CreateTransactionParams<'a> {
    pub buyer_id: Option<UserId>,
    pub seller_id: UserId,
    /// `None` for project-level purchases (no specific item).
    pub item_id: Option<ItemId>,
    pub amount_cents: Cents,
    pub platform_fee_cents: Cents,
    pub stripe_checkout_session_id: &'a str,
    pub item_title: &'a str,
    pub seller_username: &'a str,
    pub share_contact: bool,
    /// Set for project-level purchases; `None` for item purchases.
    pub project_id: Option<ProjectId>,
    /// Promo code used for this checkout (for releasing reservations on cleanup).
    pub promo_code_id: Option<PromoCodeId>,
    /// Guest buyer's email (set for guest checkouts, None for logged-in).
    pub guest_email: Option<&'a str>,
    /// Cents MNW owes the seller as reimbursement for a platform-funded credit
    /// (the Fan+ renewal credit) applied to this sale; `0` for ordinary sales. The
    /// scheduler settles it via a platform -> connected transfer once the
    /// transaction completes (see `db::platform_credits`).
    pub platform_credit_cents: i64,
}

/// Record a new pending transaction for a Stripe checkout session.
#[tracing::instrument(skip_all)]
pub async fn create_transaction<'e>(
    executor: impl sqlx::PgExecutor<'e>,
    params: &CreateTransactionParams<'_>,
) -> Result<DbTransaction> {
    let tx = sqlx::query_as!(
        DbTransaction,
        r#"
        INSERT INTO transactions (buyer_id, seller_id, item_id, amount_cents, platform_fee_cents, stripe_checkout_session_id, item_title, seller_username, share_contact, project_id, promo_code_id, guest_email, platform_credit_cents)
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
        RETURNING
            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",
            presentment_amount_cents, presentment_currency
        "#,
        params.buyer_id as Option<UserId>,
        params.seller_id as UserId,
        params.item_id as Option<ItemId>,
        params.amount_cents as Cents,
        params.platform_fee_cents as Cents,
        params.stripe_checkout_session_id,
        params.item_title,
        params.seller_username,
        params.share_contact,
        params.project_id as Option<ProjectId>,
        params.promo_code_id as Option<PromoCodeId>,
        params.guest_email,
        params.platform_credit_cents,
    )
    .fetch_one(executor)
    .await?;

    Ok(tx)
}

/// Mark a pending transaction as completed (idempotent; returns `None` if already completed).
///
/// Accepts any sqlx executor (`&PgPool`, `&mut Transaction`, etc.) so callers
/// can include this in a larger transaction when needed.
#[tracing::instrument(skip_all)]
pub async fn complete_transaction<'e>(
    executor: impl sqlx::PgExecutor<'e>,
    stripe_checkout_session_id: &str,
    stripe_payment_intent_id: Option<&str>,
    presentment: Option<(i64, &str)>,
) -> Result<Option<DbTransaction>> {
    // Only update if status is 'pending' for idempotency
    // Returns None if transaction was already completed (duplicate webhook)
    let tx = sqlx::query_as!(
        DbTransaction,
        r#"
        UPDATE transactions
        SET status = 'completed',
            stripe_payment_intent_id = $2,
            presentment_amount_cents = $3,
            presentment_currency = $4,
            completed_at = NOW()
        WHERE stripe_checkout_session_id = $1
          AND status = 'pending'
        RETURNING
            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",
            presentment_amount_cents, presentment_currency
        "#,
        stripe_checkout_session_id,
        stripe_payment_intent_id,
        presentment.map(|(cents, _)| cents),
        presentment.map(|(_, currency)| currency),
    )
    .fetch_optional(executor)
    .await?;

    Ok(tx)
}

/// Complete ALL pending transactions for a cart checkout session.
/// Returns the list of completed transactions (empty if already processed).
#[tracing::instrument(skip_all)]
pub async fn complete_cart_transactions<'e>(
    executor: impl sqlx::PgExecutor<'e>,
    stripe_checkout_session_id: &str,
    stripe_payment_intent_id: Option<&str>,
) -> Result<Vec<DbTransaction>> {
    let txs = sqlx::query_as!(
        DbTransaction,
        r#"
        UPDATE transactions
        SET status = 'completed',
            stripe_payment_intent_id = $2,
            completed_at = NOW()
        WHERE stripe_checkout_session_id = $1
          AND status = 'pending'
        RETURNING
            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",
            presentment_amount_cents, presentment_currency
        "#,
        stripe_checkout_session_id,
        stripe_payment_intent_id,
    )
    .fetch_all(executor)
    .await?;

    Ok(txs)
}

/// Fetch all completed transactions for a checkout session.
///
/// Used on the crash-recovery branch of the purchase/cart webhook handlers: when
/// `complete_transaction` / `complete_cart_transactions` return nothing (the
/// rows were already flipped to completed by a first attempt that crashed before
/// running finalize), this re-reads those completed rows so finalize can re-run
/// idempotently. Covers single and cart purchases since both key on the session.
#[tracing::instrument(skip_all)]
pub async fn get_completed_transactions_for_session<'e>(
    executor: impl sqlx::PgExecutor<'e>,
    stripe_checkout_session_id: &str,
) -> Result<Vec<DbTransaction>> {
    let txs = 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",
            presentment_amount_cents, presentment_currency
        FROM transactions
        WHERE stripe_checkout_session_id = $1 AND status = 'completed'
        "#,
        stripe_checkout_session_id,
    )
    .fetch_all(executor)
    .await?;

    Ok(txs)
}

/// Parameters for creating a pending project purchase transaction.
pub struct CreateProjectTransactionParams<'a> {
    pub buyer_id: UserId,
    pub seller_id: UserId,
    pub project_id: ProjectId,
    pub amount_cents: i32,
    pub stripe_checkout_session_id: &'a str,
    pub project_title: &'a str,
    pub seller_username: &'a str,
    pub share_contact: bool,
}

/// Record a new pending transaction for a project purchase.
#[tracing::instrument(skip_all)]
pub async fn create_project_transaction(
    pool: &PgPool,
    params: &CreateProjectTransactionParams<'_>,
) -> Result<DbTransaction> {
    let tx = sqlx::query_as!(
        DbTransaction,
        r#"
        INSERT INTO transactions (buyer_id, seller_id, project_id, amount_cents, platform_fee_cents, stripe_checkout_session_id, item_title, seller_username, share_contact)
        VALUES ($1, $2, $3, $4, 0, $5, $6, $7, $8)
        RETURNING
            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",
            presentment_amount_cents, presentment_currency
        "#,
        params.buyer_id as UserId,
        params.seller_id as UserId,
        params.project_id as ProjectId,
        params.amount_cents,
        params.stripe_checkout_session_id,
        params.project_title,
        params.seller_username,
        params.share_contact,
    )
    .fetch_one(pool)
    .await?;

    Ok(tx)
}

/// Create a pending "placeholder" transaction for a subscription checkout that
/// used a promo code. This row exists solely so `cleanup_stale_pending` can
/// release the promo code reservation if the buyer abandons the Stripe session.
/// It is deleted (not completed) when the subscription webhook fires.
#[tracing::instrument(skip_all)]
pub async fn create_subscription_pending_transaction(
    pool: &PgPool,
    buyer_id: UserId,
    seller_id: UserId,
    project_id: ProjectId,
    stripe_checkout_session_id: &str,
    promo_code_id: PromoCodeId,
) -> Result<()> {
    sqlx::query!(
        r#"
        INSERT INTO transactions (buyer_id, seller_id, project_id, amount_cents, platform_fee_cents,
                                  stripe_checkout_session_id, item_title, seller_username, share_contact, promo_code_id)
        VALUES ($1, $2, $3, 0, 0, $4, 'subscription-promo-hold', '', false, $5)
        "#,
        buyer_id as UserId,
        seller_id as UserId,
        project_id as ProjectId,
        stripe_checkout_session_id,
        promo_code_id as PromoCodeId,
    )
    .execute(pool)
    .await?;

    Ok(())
}

/// Delete a pending subscription promo-hold transaction by checkout session ID.
/// Called from the subscription webhook after the subscription is created.
#[tracing::instrument(skip_all)]
pub async fn delete_subscription_pending_transaction<'e>(
    executor: impl sqlx::PgExecutor<'e>,
    stripe_checkout_session_id: &str,
) -> Result<()> {
    sqlx::query!(
        "DELETE FROM transactions WHERE stripe_checkout_session_id = $1 AND status = 'pending'",
        stripe_checkout_session_id,
    )
    .execute(executor)
    .await?;

    Ok(())
}

/// Delete stale pending transactions (older than the given threshold) and return
/// the promo_code_ids that need their use_count decremented.
///
/// Stripe checkout sessions expire after 24 hours, so pending transactions older
/// than that will never complete. This releases the pending purchase uniqueness
/// slot and any reserved promo code use_count.
#[tracing::instrument(skip_all)]
pub async fn cleanup_stale_pending(
    pool: &PgPool,
    older_than: chrono::Duration,
) -> Result<Vec<Option<crate::db::PromoCodeId>>> {
    let cutoff = chrono::Utc::now() - older_than;
    // runtime-checked: binds a chrono DateTime<Utc> param; a bind param's type can't be overridden in the macro when sqlx time+chrono features are unified.
    let rows: Vec<(Option<crate::db::PromoCodeId>,)> = sqlx::query_as(
        r"
        DELETE FROM transactions
        WHERE status = 'pending'
          AND created_at < $1
        RETURNING promo_code_id
        ",
    )
    .bind(cutoff)
    .fetch_all(pool)
    .await?;

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

/// Bulk variant of `get_pending_item_purchase`. Returns the subset of `item_ids`
/// for which the buyer already has a `pending` transaction. Used by cart
/// checkout to abort early when any line item would collide with the partial
/// unique index on `(buyer_id, item_id) WHERE status = 'pending'`.
#[tracing::instrument(skip_all)]
pub async fn pending_subset(
    pool: &PgPool,
    buyer_id: UserId,
    item_ids: &[ItemId],
) -> Result<std::collections::HashSet<ItemId>> {
    if item_ids.is_empty() {
        return Ok(std::collections::HashSet::new());
    }
    let rows = sqlx::query_scalar!(
        r#"SELECT DISTINCT item_id AS "item_id!: ItemId" FROM transactions
         WHERE buyer_id = $1 AND status = 'pending' AND item_id = ANY($2)"#,
        buyer_id as UserId,
        item_ids as &[ItemId],
    )
    .fetch_all(pool)
    .await?;
    Ok(rows.into_iter().collect())
}

/// Returns the buyer's pending transaction for a specific item, if any.
/// Used to surface in-progress checkouts on the purchase page.
#[tracing::instrument(skip_all)]
pub async fn get_pending_item_purchase(
    pool: &PgPool,
    buyer_id: UserId,
    item_id: ItemId,
) -> Result<Option<(TransactionId, chrono::DateTime<chrono::Utc>)>> {
    let row = sqlx::query!(
        r#"
        SELECT id AS "id: TransactionId", created_at AS "created_at: chrono::DateTime<chrono::Utc>"
        FROM transactions
        WHERE buyer_id = $1 AND item_id = $2 AND status = 'pending'
        LIMIT 1
        "#,
        buyer_id as UserId,
        item_id as ItemId,
    )
    .fetch_optional(pool)
    .await?;

    Ok(row.map(|r| (r.id, r.created_at)))
}

/// Delete the buyer's pending transaction for a specific item.
/// Returns any released `promo_code_id` so the caller can release its
/// reservation.
#[tracing::instrument(skip_all)]
pub async fn delete_pending_item_purchase(
    pool: &PgPool,
    buyer_id: UserId,
    item_id: ItemId,
) -> Result<Option<crate::db::PromoCodeId>> {
    let row: Option<Option<crate::db::PromoCodeId>> = sqlx::query_scalar!(
        r#"
        DELETE FROM transactions
        WHERE buyer_id = $1 AND item_id = $2 AND status = 'pending'
        RETURNING promo_code_id AS "promo_code_id: crate::db::PromoCodeId"
        "#,
        buyer_id as UserId,
        item_id as ItemId,
    )
    .fetch_optional(pool)
    .await?;

    Ok(row.flatten())
}