//! Email signup capture (launch waitlist / newsletter): insert a signup with
//! its `source`, and read the full list + count for the admin view.

use sqlx::PgPool;
use uuid::Uuid;

use crate::error::Result;

/// Insert a new email signup, ignoring duplicates.
/// Returns the signup ID (new or existing).
///
/// Re-signing up clears a previous unsubscribe: submitting the form again is a
/// fresh act of consent, and refusing to honour it would leave someone unable
/// to opt back in through the only interface that exists.
#[tracing::instrument(skip_all)]
pub(crate) async fn insert_email_signup(pool: &PgPool, email: &str, source: &str) -> Result<Uuid> {
    let id = sqlx::query_scalar!(
        r#"
        INSERT INTO email_signups (email, source)
        VALUES ($1, $2)
        ON CONFLICT (email) DO UPDATE SET email = EXCLUDED.email, unsubscribed_at = NULL
        RETURNING id
        "#,
        email,
        source,
    )
    .fetch_one(pool)
    .await?;
    Ok(id)
}

/// Mark an address unsubscribed. Returns whether a subscribed row was found.
///
/// The row is marked, not deleted. A deleted address is one the next import
/// would happily re-add, so the record of the opt-out is what honours it.
/// Idempotent: unsubscribing twice reports `false` the second time and is not
/// an error, which matters because RFC 8058 one-click POSTs get retried.
#[tracing::instrument(skip_all)]
pub(crate) async fn unsubscribe_email_signup(pool: &PgPool, email: &str) -> Result<bool> {
    let affected = sqlx::query!(
        "UPDATE email_signups SET unsubscribed_at = NOW() \
         WHERE LOWER(email) = LOWER($1) AND unsubscribed_at IS NULL",
        email,
    )
    .execute(pool)
    .await?
    .rows_affected();
    Ok(affected > 0)
}

/// Row returned by the admin email signups query.
pub(crate) struct DbEmailSignup {
    pub email: String,
    pub source: String,
    pub created_at: chrono::DateTime<chrono::Utc>,
}

/// Get mailable email signups, newest first (capped at 500).
///
/// Unsubscribed rows are excluded here rather than filtered by the caller. This
/// is the query a send would draw its recipients from, so the opt-out belongs
/// inside it: a caller that has to remember to filter is a caller that will
/// eventually forget.
#[tracing::instrument(skip_all)]
pub(crate) async fn get_all_email_signups(pool: &PgPool) -> Result<Vec<DbEmailSignup>> {
    let rows = sqlx::query_as!(
        DbEmailSignup,
        r#"
        SELECT email, source,
               created_at as "created_at: chrono::DateTime<chrono::Utc>"
        FROM email_signups
        WHERE unsubscribed_at IS NULL
        ORDER BY created_at DESC
        LIMIT 500
        "#,
    )
    .fetch_all(pool)
    .await?;
    Ok(rows)
}

/// Count mailable email signups. Excludes unsubscribed, matching
/// [`get_all_email_signups`], so the admin count is the size of the list that
/// would actually receive a send.
#[tracing::instrument(skip_all)]
pub(crate) async fn count_email_signups(pool: &PgPool) -> Result<i64> {
    let count =
        sqlx::query_scalar!("SELECT COUNT(*) FROM email_signups WHERE unsubscribed_at IS NULL")
            .fetch_one(pool)
            .await?
            .unwrap_or(0);
    Ok(count)
}