//! Shopping cart queries: fans batch items for combined checkout.

use chrono::{DateTime, Duration, Utc};
use sqlx::PgPool;

use super::{ItemId, ProjectId, UserId};
use crate::error::Result;

/// Check if an item is in the user's cart.
#[tracing::instrument(skip_all)]
pub async fn is_in_cart(pool: &PgPool, user_id: UserId, item_id: ItemId) -> Result<bool> {
    let exists: bool = sqlx::query_scalar(
        "SELECT EXISTS(SELECT 1 FROM cart_items WHERE user_id = $1 AND item_id = $2)",
    )
    .bind(user_id)
    .bind(item_id)
    .fetch_one(pool)
    .await?;

    Ok(exists)
}

/// Add an item to the user's cart (idempotent).
#[tracing::instrument(skip_all)]
pub async fn add_to_cart(pool: &PgPool, user_id: UserId, item_id: ItemId) -> Result<()> {
    sqlx::query(
        "INSERT INTO cart_items (user_id, item_id) VALUES ($1, $2) ON CONFLICT DO NOTHING",
    )
    .bind(user_id)
    .bind(item_id)
    .execute(pool)
    .await?;

    Ok(())
}

/// Remove an item from the user's cart.
#[tracing::instrument(skip_all)]
pub async fn remove_from_cart(pool: &PgPool, user_id: UserId, item_id: ItemId) -> Result<()> {
    sqlx::query("DELETE FROM cart_items WHERE user_id = $1 AND item_id = $2")
        .bind(user_id)
        .bind(item_id)
        .execute(pool)
        .await?;

    Ok(())
}

/// Bulk-remove items from the user's cart in a single roundtrip. Used by the
/// cart checkout free-claim loop to replace N per-item DELETEs (Run #8 perf
/// MED). No-op on empty slice.
#[tracing::instrument(skip_all)]
pub async fn remove_from_cart_bulk(pool: &PgPool, user_id: UserId, item_ids: &[ItemId]) -> Result<()> {
    if item_ids.is_empty() {
        return Ok(());
    }
    sqlx::query("DELETE FROM cart_items WHERE user_id = $1 AND item_id = ANY($2)")
        .bind(user_id)
        .bind(item_ids)
        .execute(pool)
        .await?;
    Ok(())
}

/// Update the PWYW amount for a cart item.
#[tracing::instrument(skip_all)]
pub async fn update_cart_amount(
    pool: &PgPool,
    user_id: UserId,
    item_id: ItemId,
    amount_cents: Option<i32>,
) -> Result<bool> {
    let result = sqlx::query(
        "UPDATE cart_items SET amount_cents = $3 WHERE user_id = $1 AND item_id = $2",
    )
    .bind(user_id)
    .bind(item_id)
    .bind(amount_cents)
    .execute(pool)
    .await?;

    Ok(result.rows_affected() > 0)
}

/// Get the number of items in the user's cart.
#[tracing::instrument(skip_all)]
pub async fn get_cart_count(pool: &PgPool, user_id: UserId) -> Result<i64> {
    let count: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM cart_items WHERE user_id = $1",
    )
    .bind(user_id)
    .fetch_one(pool)
    .await?;

    Ok(count)
}

/// Pre-flight check for cart toggle: fetches item existence, visibility, ownership,
/// purchase status, and cart membership in a single query.
#[derive(Debug, sqlx::FromRow)]
pub struct CartTogglePreflight {
    pub is_public: bool,
    pub is_owner: bool,
    pub has_purchased: bool,
    pub in_cart: bool,
    pub listed: bool,
}

/// Single-query pre-flight for cart toggle. Returns `None` if item does not exist.
#[tracing::instrument(skip_all)]
pub async fn toggle_cart_preflight(
    pool: &PgPool,
    user_id: UserId,
    item_id: ItemId,
) -> Result<Option<CartTogglePreflight>> {
    let row = sqlx::query_as::<_, CartTogglePreflight>(
        r#"
        SELECT
            i.is_public,
            (p.user_id = $2) AS is_owner,
            EXISTS(
                SELECT 1 FROM transactions t
                WHERE t.item_id = $1 AND t.buyer_id = $2 AND t.status = 'completed'
            ) AS has_purchased,
            EXISTS(
                SELECT 1 FROM cart_items c
                WHERE c.item_id = $1 AND c.user_id = $2
            ) AS in_cart,
            i.listed AS listed
        FROM items i
        JOIN projects p ON i.project_id = p.id
        WHERE i.id = $1 AND i.deleted_at IS NULL
        "#,
    )
    .bind(item_id)
    .bind(user_id)
    .fetch_optional(pool)
    .await?;

    Ok(row)
}

/// A cart item with joined display and checkout data.
#[derive(Debug, Clone, sqlx::FromRow)]
pub struct CartItem {
    pub item_id: ItemId,
    /// Owning project. Pulled through so project-scoped promo checks don't need
    /// a `get_item_by_id` per item at checkout.
    pub project_id: ProjectId,
    pub title: String,
    pub item_type: String,
    pub price_cents: i32,
    pub pwyw_enabled: bool,
    pub pwyw_min_cents: Option<i32>,
    /// Buyer's chosen PWYW amount (None = use item minimum).
    pub amount_cents: Option<i32>,
    pub creator_username: String,
    pub seller_id: UserId,
    pub seller_stripe_account_id: Option<String>,
    pub seller_charges_enabled: bool,
    pub project_slug: String,
    pub added_at: DateTime<Utc>,
    /// Pulled through so the free-claim path can decide bundle/license issuance
    /// without an extra `get_item_by_id` per item (Run #8 cart N+1 fix).
    pub enable_license_keys: bool,
    pub default_max_activations: Option<i32>,
}

impl CartItem {
    /// Effective price for cart checkout. For PWYW: buyer's chosen amount if set,
    /// otherwise the item minimum. For fixed: the item price.
    pub fn effective_price_cents(&self) -> i32 {
        if self.pwyw_enabled {
            let min = self.pwyw_min_cents.unwrap_or(0);
            self.amount_cents
                .unwrap_or(min)
                .max(min)
                .max(0)
        } else {
            self.price_cents
        }
    }

    /// Whether this item is free at its effective cart price.
    pub fn is_free(&self) -> bool {
        self.effective_price_cents() == 0
    }

    /// Minimum price in dollars for PWYW display.
    pub fn pwyw_min_dollars(&self) -> String {
        let min = self.pwyw_min_cents.unwrap_or(0).max(0);
        format!("{}.{:02}", min / 100, min % 100)
    }

    /// Bare "X.YY" dollars string for use as a numeric `<input>` value.
    pub fn effective_price_input_value(&self) -> String {
        let cents = self.effective_price_cents().max(0);
        format!("{}.{:02}", cents / 100, cents % 100)
    }

    /// "$X.YY" display string. Always shows decimals; caller branches on `is_free()`.
    pub fn effective_price_display(&self) -> String {
        crate::formatting::format_revenue(self.effective_price_cents() as i64)
    }
}

/// Get all cart items for a user with joined item, project, and seller data.
/// Only returns items that are still public and not deleted.
#[tracing::instrument(skip_all)]
pub async fn get_cart_items(pool: &PgPool, user_id: UserId) -> Result<Vec<CartItem>> {
    let items = sqlx::query_as::<_, CartItem>(
        r#"
        SELECT c.item_id, i.project_id, i.title, i.item_type::TEXT as item_type,
               i.price_cents, i.pwyw_enabled, i.pwyw_min_cents,
               c.amount_cents,
               u.username AS creator_username, p.user_id AS seller_id,
               u.stripe_account_id AS seller_stripe_account_id,
               u.stripe_charges_enabled AS seller_charges_enabled,
               p.slug AS project_slug,
               c.created_at AS added_at,
               i.enable_license_keys,
               i.default_max_activations
        FROM cart_items c
        JOIN items i ON i.id = c.item_id
        JOIN projects p ON p.id = i.project_id
        JOIN users u ON u.id = p.user_id
        WHERE c.user_id = $1
          AND i.is_public = true
          AND i.listed = true
          AND i.deleted_at IS NULL
        ORDER BY u.username, c.created_at DESC
        "#,
    )
    .bind(user_id)
    .fetch_all(pool)
    .await?;

    Ok(items)
}

/// Get cart items for a user filtered to a specific seller.
#[tracing::instrument(skip_all)]
pub async fn get_cart_items_for_seller(
    pool: &PgPool,
    user_id: UserId,
    seller_id: UserId,
) -> Result<Vec<CartItem>> {
    let items = sqlx::query_as::<_, CartItem>(
        r#"
        SELECT c.item_id, i.project_id, i.title, i.item_type::TEXT as item_type,
               i.price_cents, i.pwyw_enabled, i.pwyw_min_cents,
               c.amount_cents,
               u.username AS creator_username, p.user_id AS seller_id,
               u.stripe_account_id AS seller_stripe_account_id,
               u.stripe_charges_enabled AS seller_charges_enabled,
               p.slug AS project_slug,
               c.created_at AS added_at,
               i.enable_license_keys,
               i.default_max_activations
        FROM cart_items c
        JOIN items i ON i.id = c.item_id
        JOIN projects p ON p.id = i.project_id
        JOIN users u ON u.id = p.user_id
        WHERE c.user_id = $1
          AND p.user_id = $2
          AND i.is_public = true
          AND i.listed = true
          AND i.deleted_at IS NULL
        ORDER BY c.created_at DESC
        "#,
    )
    .bind(user_id)
    .bind(seller_id)
    .fetch_all(pool)
    .await?;

    Ok(items)
}

/// Remove all cart items belonging to a specific seller (after checkout).
#[tracing::instrument(skip_all)]
pub async fn remove_seller_items_from_cart(
    pool: &PgPool,
    user_id: UserId,
    seller_id: UserId,
) -> Result<u64> {
    let result = sqlx::query(
        r#"
        DELETE FROM cart_items c
        USING items i
        JOIN projects p ON p.id = i.project_id
        WHERE c.user_id = $1
          AND c.item_id = i.id
          AND p.user_id = $2
        "#,
    )
    .bind(user_id)
    .bind(seller_id)
    .execute(pool)
    .await?;

    Ok(result.rows_affected())
}

/// Remove stale cart items older than the given duration.
#[tracing::instrument(skip_all)]
pub async fn cleanup_stale_cart_items(pool: &PgPool, older_than: Duration) -> Result<u64> {
    let cutoff = Utc::now() - older_than;
    let result = sqlx::query("DELETE FROM cart_items WHERE created_at < $1")
        .bind(cutoff)
        .execute(pool)
        .await?;

    Ok(result.rows_affected())
}

/// Remove cart items for items that are no longer available (unpublished, deleted).
#[tracing::instrument(skip_all)]
pub async fn cleanup_unavailable_cart_items(pool: &PgPool) -> Result<u64> {
    let result = sqlx::query(
        r#"
        DELETE FROM cart_items c
        USING items i
        WHERE c.item_id = i.id
          AND (i.is_public = false OR i.listed = false OR i.deleted_at IS NOT NULL)
        "#,
    )
    .execute(pool)
    .await?;

    Ok(result.rows_affected())
}

#[cfg(test)]
mod tests {
    use super::*;
    use chrono::Utc;

    fn make_cart_item(
        price_cents: i32,
        pwyw_enabled: bool,
        pwyw_min_cents: Option<i32>,
        amount_cents: Option<i32>,
    ) -> CartItem {
        CartItem {
            item_id: ItemId::nil(),
            project_id: ProjectId::nil(),
            title: String::new(),
            item_type: String::from("audio"),
            price_cents,
            pwyw_enabled,
            pwyw_min_cents,
            amount_cents,
            creator_username: String::new(),
            seller_id: UserId::nil(),
            seller_stripe_account_id: None,
            seller_charges_enabled: false,
            project_slug: String::new(),
            added_at: Utc::now(),
            enable_license_keys: false,
            default_max_activations: None,
        }
    }

    // ---- effective_price_cents ----

    #[test]
    fn fixed_price_returns_price_cents() {
        let item = make_cart_item(500, false, None, None);
        assert_eq!(item.effective_price_cents(), 500);
    }

    #[test]
    fn pwyw_amount_above_min_returns_amount() {
        let item = make_cart_item(0, true, Some(300), Some(500));
        assert_eq!(item.effective_price_cents(), 500);
    }

    #[test]
    fn pwyw_amount_below_min_clamps_to_min() {
        let item = make_cart_item(0, true, Some(500), Some(200));
        assert_eq!(item.effective_price_cents(), 500);
    }

    #[test]
    fn pwyw_no_amount_uses_min() {
        let item = make_cart_item(0, true, Some(400), None);
        assert_eq!(item.effective_price_cents(), 400);
    }

    #[test]
    fn pwyw_no_min_defaults_to_zero() {
        let item = make_cart_item(0, true, None, Some(700));
        assert_eq!(item.effective_price_cents(), 700);
    }

    #[test]
    fn pwyw_negative_amount_clamps_to_zero() {
        let item = make_cart_item(0, true, Some(0), Some(-100));
        assert_eq!(item.effective_price_cents(), 0);
    }

    #[test]
    fn pwyw_both_none_returns_zero() {
        let item = make_cart_item(0, true, None, None);
        assert_eq!(item.effective_price_cents(), 0);
    }

    // ---- is_free ----

    #[test]
    fn fixed_price_zero_is_free() {
        let item = make_cart_item(0, false, None, None);
        assert!(item.is_free());
    }

    #[test]
    fn fixed_price_one_is_not_free() {
        let item = make_cart_item(1, false, None, None);
        assert!(!item.is_free());
    }

    #[test]
    fn pwyw_zero_min_none_amount_is_free() {
        let item = make_cart_item(0, true, Some(0), None);
        assert!(item.is_free());
    }

    // ---- pwyw_min_dollars ----

    #[test]
    fn pwyw_min_dollars_zero() {
        let item = make_cart_item(0, true, Some(0), None);
        assert_eq!(item.pwyw_min_dollars(), "0.00");
    }

    #[test]
    fn pwyw_min_dollars_one_dollar() {
        let item = make_cart_item(0, true, Some(100), None);
        assert_eq!(item.pwyw_min_dollars(), "1.00");
    }

    #[test]
    fn pwyw_min_dollars_one_fifty() {
        let item = make_cart_item(0, true, Some(150), None);
        assert_eq!(item.pwyw_min_dollars(), "1.50");
    }

    #[test]
    fn pwyw_min_dollars_ninety_nine_cents() {
        let item = make_cart_item(0, true, Some(99), None);
        assert_eq!(item.pwyw_min_dollars(), "0.99");
    }

    #[test]
    fn pwyw_min_dollars_none_returns_zero() {
        let item = make_cart_item(0, true, None, None);
        assert_eq!(item.pwyw_min_dollars(), "0.00");
    }
}