//! 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?;

    // Mirror into the unified `lists` table, which is what sends read from now
    // (see db::lists). Errors propagate: a list that exists only in the legacy
    // table is one no send can reach.
    super::lists::mirror_legacy_list(pool, project_id.into(), list_type.into(), name).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?;

    super::lists::mirror_legacy_subscribe(
        pool,
        list_id.into(),
        &super::lists::Subscriber::User(user_id),
        super::SubscriptionState::Confirmed,
        super::SubscriptionSource::ProjectPage,
        Some("Subscribed through the product (project page, follow, or purchase)."),
    )
    .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?;

    super::lists::mirror_legacy_unsubscribe_user(pool, list_id.into(), user_id).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?;

    super::lists::mirror_legacy_unsubscribe_project(pool, project_id.into(), user_id).await?;

    Ok(result.rows_affected())
}

/// 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?;

    super::lists::mirror_legacy_unsubscribe_email(pool, list_id.into(), email).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?;

    // Mirror as `imported`, not `confirmed`. This is a creator's CSV upload:
    // the addresses did not opt in here and we hold no evidence that they
    // opted in anywhere, so the state and the event say import rather than
    // manufacturing consent the row cannot support.
    for email in &lowered {
        super::lists::mirror_legacy_subscribe(
            pool,
            list_id.into(),
            &super::lists::Subscriber::Email(email.clone()),
            super::SubscriptionState::Imported,
            super::SubscriptionSource::Import,
            Some("Bulk import from a creator-supplied subscriber list."),
        )
        .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());
    }
}