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

use crate::error::Result;

/// Insert a new email signup, ignoring duplicates.
/// Returns the signup ID (new or existing).
#[tracing::instrument(skip_all)]
pub 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
        RETURNING id
        "#,
        email,
        source,
    )
    .fetch_one(pool)
    .await?;
    Ok(id)
}

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

/// Get email signups ordered by newest first (capped at 500).
#[tracing::instrument(skip_all)]
pub 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
        ORDER BY created_at DESC
        LIMIT 500
        "#,
    )
    .fetch_all(pool)
    .await?;
    Ok(rows)
}

/// Count total email signups.
#[tracing::instrument(skip_all)]
pub async fn count_email_signups(pool: &PgPool) -> Result<i64> {
    let count = sqlx::query_scalar!("SELECT COUNT(*) FROM email_signups")
        .fetch_one(pool)
        .await?
        .unwrap_or(0);
    Ok(count)
}