//! Mailing list CRUD: per-project lists and subscriber management.

use sqlx::PgPool;

use super::enums::MailingListType;
use super::id_types::{MailingListId, ProjectId, UserId};
use super::models::DbMailingList;
use crate::error::Result;

/// Create a mailing list for a project. Idempotent via ON CONFLICT.
#[tracing::instrument(skip_all)]
pub async fn create_list(
    pool: &PgPool,
    project_id: ProjectId,
    list_type: MailingListType,
    name: &str,
    description: Option<&str>,
) -> Result<DbMailingList> {
    let row = sqlx::query_as::<_, DbMailingList>(
        r"
        INSERT INTO mailing_lists (project_id, list_type, name, description)
        VALUES ($1, $2, $3, $4)
        ON CONFLICT (project_id, list_type) DO UPDATE SET name = EXCLUDED.name
        RETURNING *
        ",
    )
    .bind(project_id)
    .bind(list_type)
    .bind(name)
    .bind(description)
    .fetch_one(pool)
    .await?;

    Ok(row)
}

/// Look up a list by project and type.
#[tracing::instrument(skip_all)]
pub async fn get_list_by_project_and_type(
    pool: &PgPool,
    project_id: ProjectId,
    list_type: MailingListType,
) -> Result<Option<DbMailingList>> {
    let row = sqlx::query_as::<_, DbMailingList>(
        "SELECT * FROM mailing_lists WHERE project_id = $1 AND list_type = $2",
    )
    .bind(project_id)
    .bind(list_type)
    .fetch_optional(pool)
    .await?;

    Ok(row)
}

/// Subscribe a user to a list. Idempotent via ON CONFLICT DO NOTHING.
#[tracing::instrument(skip_all)]
pub async fn subscribe(pool: &PgPool, list_id: MailingListId, user_id: UserId) -> Result<()> {
    sqlx::query(
        r"
        INSERT INTO mailing_list_subscribers (list_id, user_id)
        VALUES ($1, $2)
        ON CONFLICT (list_id, user_id) DO NOTHING
        ",
    )
    .bind(list_id)
    .bind(user_id)
    .execute(pool)
    .await?;

    Ok(())
}

/// Unsubscribe a user from a specific list. Returns true if a row was deleted.
#[tracing::instrument(skip_all)]
pub async fn unsubscribe(pool: &PgPool, list_id: MailingListId, user_id: UserId) -> Result<bool> {
    let result =
        sqlx::query("DELETE FROM mailing_list_subscribers WHERE list_id = $1 AND user_id = $2")
            .bind(list_id)
            .bind(user_id)
            .execute(pool)
            .await?;

    Ok(result.rows_affected() > 0)
}

/// Unsubscribe a user from ALL mailing lists on a project (used on unfollow).
#[tracing::instrument(skip_all)]
pub async fn unsubscribe_from_project(
    pool: &PgPool,
    project_id: ProjectId,
    user_id: UserId,
) -> Result<u64> {
    let result = sqlx::query(
        r"
        DELETE FROM mailing_list_subscribers
        WHERE user_id = $1
          AND list_id IN (SELECT id FROM mailing_lists WHERE project_id = $2)
        ",
    )
    .bind(user_id)
    .bind(project_id)
    .execute(pool)
    .await?;

    Ok(result.rows_affected())
}

/// A mailing-list recipient: either an MNW user (has `user_id`) or an
/// email-only subscriber imported from another platform (`user_id` is `None`).
/// The two need different unsubscribe links, user-keyed vs email-keyed.
#[derive(Debug, Clone, sqlx::FromRow)]
pub struct MailingSubscriber {
    pub user_id: Option<UserId>,
    pub email: String,
    pub display_name: Option<String>,
}

/// Get all deliverable subscribers on a list, both MNW users AND email-only
/// imported subscribers. Capped at 10,000.
///
/// User rows require a verified, non-suspended account; email-only rows
/// (`user_id IS NULL`) come straight from the import. Both branches exclude
/// suppressed addresses. Previously this INNER JOINed `users`, so imported
/// email-only subscribers were silently never emailed (Run 21 data).
#[tracing::instrument(skip_all)]
pub async fn get_subscribers(
    pool: &PgPool,
    list_id: MailingListId,
) -> Result<Vec<MailingSubscriber>> {
    let rows = sqlx::query_as::<_, MailingSubscriber>(
        r"
        SELECT u.id AS user_id, u.email, u.display_name
        FROM mailing_list_subscribers s
        JOIN users u ON u.id = s.user_id
        WHERE s.list_id = $1
          AND u.email_verified = true
          AND u.suspended_at IS NULL
          AND LOWER(u.email) NOT IN (SELECT LOWER(email) FROM email_suppressions)
        UNION ALL
        SELECT NULL::uuid AS user_id, s.email, NULL AS display_name
        FROM mailing_list_subscribers s
        WHERE s.list_id = $1
          AND s.user_id IS NULL
          AND s.email IS NOT NULL
          AND LOWER(s.email) NOT IN (SELECT LOWER(email) FROM email_suppressions)
        LIMIT 10000
        ",
    )
    .bind(list_id)
    .fetch_all(pool)
    .await?;

    Ok(rows)
}

/// Unsubscribe an email-only subscriber (no MNW account) from a list. Removes
/// the `(list_id, email)` row. Idempotent. Backs the email-keyed unsubscribe
/// link carried in emails to imported subscribers.
#[tracing::instrument(skip_all)]
pub async fn unsubscribe_by_email(
    pool: &PgPool,
    list_id: MailingListId,
    email: &str,
) -> Result<bool> {
    let result = sqlx::query(
        "DELETE FROM mailing_list_subscribers WHERE list_id = $1 AND LOWER(email) = LOWER($2) AND user_id IS NULL",
    )
    .bind(list_id)
    .bind(email)
    .execute(pool)
    .await?;

    Ok(result.rows_affected() > 0)
}

/// Auto-create the default content + devlog lists for a new project.
#[tracing::instrument(skip_all)]
pub async fn create_default_lists(
    pool: &PgPool,
    project_id: ProjectId,
    project_title: &str,
) -> Result<()> {
    create_list(
        pool,
        project_id,
        MailingListType::Content,
        &format!("{project_title}: Content"),
        Some("New releases and content updates"),
    )
    .await?;

    create_list(
        pool,
        project_id,
        MailingListType::Devlog,
        &format!("{project_title}: Devlog"),
        Some("Development updates and behind-the-scenes"),
    )
    .await?;

    Ok(())
}

/// Batch-subscribe many emails to a list in a single statement.
///
/// Used by the import pipeline, where subscribing one email per query is an
/// N+1 storm (a 100k-subscriber import would issue 100k sequential INSERTs on
/// the shared pool). Emails are lowercased and deduplicated within the batch;
/// `ON CONFLICT DO NOTHING` skips ones already subscribed. Returns the number of
/// rows actually inserted (new subscribers).
#[tracing::instrument(skip_all, fields(count = emails.len()))]
pub async fn subscribe_many_by_email(
    pool: &PgPool,
    list_id: MailingListId,
    emails: &[String],
) -> Result<u64> {
    if emails.is_empty() {
        return Ok(0);
    }
    let lowered: Vec<String> = emails.iter().map(|e| e.to_lowercase()).collect();
    let result = sqlx::query(
        r"
        INSERT INTO mailing_list_subscribers (list_id, email)
        SELECT $1, sub_email FROM UNNEST($2::text[]) AS sub_email
        ON CONFLICT DO NOTHING
        ",
    )
    .bind(list_id)
    .bind(&lowered)
    .execute(pool)
    .await?;

    Ok(result.rows_affected())
}

/// Convenience: find the content list for a project and subscribe a user.
/// No-op if the content list doesn't exist yet.
#[tracing::instrument(skip_all)]
pub async fn subscribe_to_content_list(
    pool: &PgPool,
    project_id: ProjectId,
    user_id: UserId,
) -> Result<()> {
    if let Some(list) =
        get_list_by_project_and_type(pool, project_id, MailingListType::Content).await?
    {
        subscribe(pool, list.id, user_id).await?;
    }

    Ok(())
}

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

    #[test]
    fn mailing_list_type_content_roundtrip() {
        assert_eq!(MailingListType::Content.to_string(), "content");
        assert_eq!(
            "content".parse::<MailingListType>().unwrap(),
            MailingListType::Content
        );
    }

    #[test]
    fn mailing_list_type_devlog_roundtrip() {
        assert_eq!(MailingListType::Devlog.to_string(), "devlog");
        assert_eq!(
            "devlog".parse::<MailingListType>().unwrap(),
            MailingListType::Devlog
        );
    }

    #[test]
    fn mailing_list_type_patches_roundtrip() {
        assert_eq!(MailingListType::Patches.to_string(), "patches");
        assert_eq!(
            "patches".parse::<MailingListType>().unwrap(),
            MailingListType::Patches
        );
    }

    #[test]
    fn mailing_list_type_invalid_parse_fails() {
        assert!("newsletter".parse::<MailingListType>().is_err());
        assert!("".parse::<MailingListType>().is_err());
        assert!("CONTENT".parse::<MailingListType>().is_err());
    }

    #[test]
    fn mailing_list_type_serde_json_roundtrip() {
        let val = MailingListType::Content;
        let json = serde_json::to_string(&val).unwrap();
        assert_eq!(json, "\"content\"");
        let parsed: MailingListType = serde_json::from_str(&json).unwrap();
        assert_eq!(parsed, val);
    }

    #[test]
    fn default_list_name_format_content() {
        let name = format!("{}: Content", "My Project");
        assert_eq!(name, "My Project: Content");
    }

    #[test]
    fn default_list_name_format_devlog() {
        let name = format!("{}: Devlog", "My Project");
        assert_eq!(name, "My Project: Devlog");
    }

    #[test]
    fn default_list_names_with_special_chars() {
        let title = "Héllo & World <3>";
        assert_eq!(format!("{title}: Content"), "Héllo & World <3>: Content");
        assert_eq!(format!("{title}: Devlog"), "Héllo & World <3>: Devlog");
    }

    #[test]
    fn subscribe_by_email_lowercases() {
        // The function lowercases via email.to_lowercase() before binding.
        // Verify the stdlib behaviour our code relies on.
        assert_eq!("FOO@BAR.COM".to_lowercase(), "foo@bar.com");
        assert_eq!("MiXeD@CaSe.Org".to_lowercase(), "mixed@case.org");
    }

    #[test]
    fn id_types_are_distinct() {
        let ml_id = MailingListId::new();
        let proj_id = ProjectId::new();
        // They wrap different UUIDs and are different types, this is a compile-time check.
        assert_ne!(ml_id.as_uuid(), proj_id.as_uuid());
    }
}