//! Read-only email queries: the flat list views and the single-row fetch by id.
//!
//! `get_by_id` is the shared read the write paths in `crud`, `flags`, `state`
//! and `draft` call to return the row they just changed.

use goingson_core::{Email, EmailId, ProjectId, Result, UserId};
use rusqlite::{Connection, params, params_from_iter};

use crate::utils::{bind_placeholders, query_all, query_opt};

use super::row::{EMAIL_LIST_CAP, EMAIL_LIST_COLUMNS, EMAIL_SELECT_COLUMNS, EmailRow};

/// Every email for the user, bodies included, for a backup export.
pub(super) fn list_all_for_backup(conn: &Connection, user_id: UserId) -> Result<Vec<Email>> {
    let query = format!(
        "SELECT {EMAIL_SELECT_COLUMNS} FROM emails e LEFT JOIN projects p ON e.project_id = p.id AND p.user_id = ? WHERE e.user_id = ? ORDER BY e.received_at DESC"
    );
    let rows = query_all(
        conn,
        &query,
        params![user_id.to_string(), user_id.to_string()],
        EmailRow::from_row,
    )?;
    rows.into_iter().map(Email::try_from).collect()
}

/// Every non-draft email, bodies included.
pub(super) fn list_all(
    conn: &Connection,
    user_id: UserId,
    include_archived: bool,
) -> Result<Vec<Email>> {
    let archived_filter = if include_archived {
        ""
    } else {
        "AND e.is_archived = 0"
    };
    let query = format!(
        "SELECT {EMAIL_SELECT_COLUMNS} FROM emails e LEFT JOIN projects p ON e.project_id = p.id AND p.user_id = ? WHERE e.user_id = ? AND e.is_draft = 0 {archived_filter} ORDER BY e.received_at DESC"
    );
    let rows = query_all(
        conn,
        &query,
        params![user_id.to_string(), user_id.to_string()],
        EmailRow::from_row,
    )?;
    rows.into_iter().map(Email::try_from).collect()
}

/// Body-less, capped flat list for the metadata-only list view.
pub(super) fn list_metadata(
    conn: &Connection,
    user_id: UserId,
    include_archived: bool,
) -> Result<Vec<Email>> {
    let archived_filter = if include_archived {
        ""
    } else {
        "AND e.is_archived = 0"
    };
    let query = format!(
        "SELECT {EMAIL_LIST_COLUMNS} FROM emails e LEFT JOIN projects p ON e.project_id = p.id AND p.user_id = ? WHERE e.user_id = ? AND e.is_draft = 0 {archived_filter} ORDER BY e.received_at DESC LIMIT {EMAIL_LIST_CAP}"
    );
    let rows = query_all(
        conn,
        &query,
        params![user_id.to_string(), user_id.to_string()],
        EmailRow::from_row,
    )?;
    rows.into_iter().map(Email::try_from).collect()
}

/// Emails linked to one project.
pub(super) fn list_by_project(
    conn: &Connection,
    user_id: UserId,
    project_id: ProjectId,
) -> Result<Vec<Email>> {
    // Body-less + capped: the project dashboard renders only subject/from/date
    // and opens the reader (which re-fetches the full body via `get_by_id`) on
    // click, so this must not materialize every body into RAM (Perf S3, same
    // rule as `list_metadata`). `EMAIL_LIST_COLUMNS` forces `body_truncated=1`.
    let query = format!(
        "SELECT {EMAIL_LIST_COLUMNS} FROM emails e LEFT JOIN projects p ON e.project_id = p.id AND p.user_id = ? WHERE e.user_id = ? AND e.project_id = ? ORDER BY e.received_at DESC LIMIT {EMAIL_LIST_CAP}"
    );
    let rows = query_all(
        conn,
        &query,
        params![
            user_id.to_string(),
            user_id.to_string(),
            project_id.to_string()
        ],
        EmailRow::from_row,
    )?;
    rows.into_iter().map(Email::try_from).collect()
}

/// Emails sent to or from any of the given addresses.
pub(super) fn list_by_addresses(
    conn: &Connection,
    user_id: UserId,
    addresses: &[&str],
) -> Result<Vec<Email>> {
    if addresses.is_empty() {
        return Ok(Vec::new());
    }
    let placeholders = bind_placeholders(addresses.len());
    let query = format!(
        "SELECT {EMAIL_SELECT_COLUMNS} FROM emails e LEFT JOIN projects p ON e.project_id = p.id AND p.user_id = ? WHERE e.user_id = ? AND (LOWER(e.from_address) IN ({placeholders}) OR LOWER(e.to_address) IN ({placeholders})) ORDER BY e.received_at DESC LIMIT 200"
    );
    let mut binds: Vec<String> = Vec::with_capacity(addresses.len() * 2 + 2);
    binds.push(user_id.to_string());
    binds.push(user_id.to_string());
    // Bind addresses twice (once for from_address IN, once for to_address IN)
    for _ in 0..2 {
        binds.extend(addresses.iter().map(|a| a.to_lowercase()));
    }
    let rows = query_all(conn, &query, params_from_iter(binds), EmailRow::from_row)?;
    rows.into_iter().map(Email::try_from).collect()
}

/// Unarchived emails with no project, for the link-to-project picker.
pub(super) fn list_unlinked(conn: &Connection, user_id: UserId) -> Result<Vec<Email>> {
    // Body-less + capped: the sole caller is the "link email to project" picker,
    // which shows only subject/from. No body needed, and an unbounded mailbox
    // must not load every body into RAM (Perf S3). `EMAIL_LIST_COLUMNS` forces
    // `body_truncated=1` so any later reader re-fetches the full body.
    let query = format!(
        "SELECT {EMAIL_LIST_COLUMNS} FROM emails e LEFT JOIN projects p ON e.project_id = p.id AND p.user_id = ? WHERE e.user_id = ? AND e.project_id IS NULL AND e.is_archived = 0 ORDER BY e.received_at DESC LIMIT {EMAIL_LIST_CAP}"
    );
    let rows = query_all(
        conn,
        &query,
        params![user_id.to_string(), user_id.to_string()],
        EmailRow::from_row,
    )?;
    rows.into_iter().map(Email::try_from).collect()
}

/// One email by id, body included.
pub(super) fn get_by_id(conn: &Connection, id: EmailId, user_id: UserId) -> Result<Option<Email>> {
    let query = format!(
        "SELECT {EMAIL_SELECT_COLUMNS} FROM emails e LEFT JOIN projects p ON e.project_id = p.id AND p.user_id = ? WHERE e.id = ? AND e.user_id = ?"
    );
    let row = query_opt(
        conn,
        &query,
        params![user_id.to_string(), id.to_string(), user_id.to_string()],
        EmailRow::from_row,
    )?;
    row.map(Email::try_from).transpose()
}