use super::{DateTime, PgPool, Result, UserId, Utc};

/// A buyer's email and display name for platform notifications (e.g. creator departure).
///
/// Unlike [`DbContactRow`], this includes ALL buyers regardless of contact sharing
/// preference, because platform-initiated notifications (content removal warnings)
/// are distinct from creator-initiated contact.
#[derive(sqlx::FromRow)]
pub struct BuyerNotificationRow {
    pub email: String,
    pub display_name: Option<String>,
}

/// Get unique buyers who purchased from a seller, for platform notifications.
///
/// This bypasses `share_contact` and `contact_revocations` because the notification
/// is sent by the platform (not the creator) to warn buyers about content removal.
///
/// Capped at `limit` rows to bound memory + outbound email volume on creators
/// with very large historical buyer pools. The caller is responsible for
/// noting when the cap is hit (returned slice length == limit).
#[tracing::instrument(skip_all)]
pub async fn get_all_buyers_for_seller(
    pool: &PgPool,
    seller_id: UserId,
    limit: i64,
) -> Result<Vec<BuyerNotificationRow>> {
    let rows = sqlx::query_as!(
        BuyerNotificationRow,
        r#"
        SELECT DISTINCT u.email, u.display_name
        FROM transactions t
        JOIN users u ON u.id = t.buyer_id
        WHERE t.seller_id = $1
          AND t.status = 'completed'
          AND t.buyer_id IS NOT NULL
        LIMIT $2
        "#,
        seller_id as UserId,
        limit,
    )
    .fetch_all(pool)
    .await?;

    Ok(rows)
}

/// A contact row: a buyer who opted to share their email with the seller.
#[derive(sqlx::FromRow)]
pub struct DbContactRow {
    pub username: String,
    pub email: String,
    pub total_purchases: i64,
    pub total_spent_cents: i64,
    pub last_purchase_at: DateTime<Utc>,
}

/// A creator the fan has actively shared contact info with (no revocation).
#[derive(sqlx::FromRow)]
pub struct SharedCreatorRow {
    pub seller_id: UserId,
    pub username: String,
    pub display_name: Option<String>,
}

/// Get unique contacts for a seller: buyers who opted in to share their email.
///
/// Aggregates across all completed transactions where `share_contact = true`,
/// returning one row per buyer with purchase stats. Excludes buyers who have
/// revoked contact sharing.
#[tracing::instrument(skip_all)]
pub async fn get_seller_contacts(pool: &PgPool, seller_id: UserId) -> Result<Vec<DbContactRow>> {
    let rows = sqlx::query_as!(
        DbContactRow,
        r#"
        SELECT
            u.username,
            u.email,
            COUNT(*) AS "total_purchases!",
            COALESCE(SUM(t.amount_cents), 0)::BIGINT AS "total_spent_cents!",
            MAX(t.completed_at) AS "last_purchase_at!: chrono::DateTime<chrono::Utc>"
        FROM transactions t
        JOIN users u ON u.id = t.buyer_id
        WHERE t.seller_id = $1
          AND t.status = 'completed'
          AND t.share_contact = true
          AND NOT EXISTS (
            SELECT 1 FROM contact_revocations cr
            WHERE cr.buyer_id = t.buyer_id AND cr.seller_id = t.seller_id
          )
        GROUP BY t.buyer_id, u.username, u.email
        ORDER BY MAX(t.completed_at) DESC
        LIMIT 500
        "#,
        seller_id as UserId,
    )
    .fetch_all(pool)
    .await?;

    Ok(rows)
}

/// One page of a seller's sharing-opted-in contacts for CSV export, newest
/// first. Paginated so the contacts export streams in bounded batches like its
/// sibling exports instead of buffering one capped query into a single `String`
/// (ultra-fuzz Run 6 R6-Perf-M2). `(MAX(completed_at), buyer_id)` ordering is
/// stable so OFFSET batches don't reorder.
#[tracing::instrument(skip_all)]
pub async fn get_seller_contacts_page(
    pool: &PgPool,
    seller_id: UserId,
    limit: i64,
    offset: i64,
) -> Result<Vec<DbContactRow>> {
    let rows = sqlx::query_as::<_, DbContactRow>(
        r"
        SELECT
            u.username,
            u.email,
            COUNT(*) AS total_purchases,
            COALESCE(SUM(t.amount_cents), 0)::BIGINT AS total_spent_cents,
            MAX(t.completed_at) AS last_purchase_at
        FROM transactions t
        JOIN users u ON u.id = t.buyer_id
        WHERE t.seller_id = $1
          AND t.status = 'completed'
          AND t.share_contact = true
          AND NOT EXISTS (
            SELECT 1 FROM contact_revocations cr
            WHERE cr.buyer_id = t.buyer_id AND cr.seller_id = t.seller_id
          )
        GROUP BY t.buyer_id, u.username, u.email
        ORDER BY MAX(t.completed_at) DESC, t.buyer_id
        LIMIT $2 OFFSET $3
        ",
    )
    .bind(seller_id)
    .bind(limit)
    .bind(offset)
    .fetch_all(pool)
    .await?;

    Ok(rows)
}

/// Record a contact revocation (fan withdraws email sharing from a creator).
///
/// Idempotent: does nothing if already revoked.
#[tracing::instrument(skip_all)]
pub async fn revoke_contact_sharing(
    pool: &PgPool,
    buyer_id: UserId,
    seller_id: UserId,
) -> Result<()> {
    sqlx::query!(
        "INSERT INTO contact_revocations (buyer_id, seller_id) VALUES ($1, $2) ON CONFLICT DO NOTHING",
        buyer_id as UserId,
        seller_id as UserId,
    )
    .execute(pool)
    .await?;

    Ok(())
}

/// Clear a contact revocation (fan re-shares on a new purchase).
#[tracing::instrument(skip_all)]
pub async fn clear_contact_revocation(
    pool: &PgPool,
    buyer_id: UserId,
    seller_id: UserId,
) -> Result<()> {
    sqlx::query!(
        "DELETE FROM contact_revocations WHERE buyer_id = $1 AND seller_id = $2",
        buyer_id as UserId,
        seller_id as UserId,
    )
    .execute(pool)
    .await?;

    Ok(())
}

/// Get creators the fan has actively shared contact info with (excluding revoked).
#[tracing::instrument(skip_all)]
pub async fn get_shared_creators(pool: &PgPool, buyer_id: UserId) -> Result<Vec<SharedCreatorRow>> {
    let rows = sqlx::query_as!(
        SharedCreatorRow,
        r#"
        SELECT DISTINCT t.seller_id AS "seller_id!: UserId", u.username, u.display_name
        FROM transactions t
        JOIN users u ON u.id = t.seller_id
        WHERE t.buyer_id = $1
          AND t.status = 'completed'
          AND t.share_contact = true
          AND NOT EXISTS (
            SELECT 1 FROM contact_revocations cr
            WHERE cr.buyer_id = t.buyer_id AND cr.seller_id = t.seller_id
          )
        ORDER BY u.username
        LIMIT 500
        "#,
        buyer_id as UserId,
    )
    .fetch_all(pool)
    .await?;

    Ok(rows)
}