//! Email writes that create or remove rows: plain create, backup restore, the
//! two IMAP-tracking inserts, and delete.

use chrono::Utc;
use goingson_core::{
    CoreError, DbValue, Email, EmailId, NewEmail, NewEmailWithTracking, Result, UserId,
};
use rusqlite::{Connection, params};

use crate::utils::{execute, format_datetime, format_datetime_opt};

use super::query;

/// Insert a hand-composed email and return it as stored.
pub(super) fn create(conn: &Connection, user_id: UserId, email: &NewEmail) -> Result<Email> {
    let id = EmailId::new();
    let received_at = format_datetime(&email.received_at.unwrap_or_else(Utc::now));
    execute(
        conn,
        "INSERT INTO emails (id, user_id, project_id, from_address, to_address, subject, body, is_read, received_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
        params![
            id.to_string(),
            user_id.to_string(),
            email.project_id.as_ref().map(ToString::to_string),
            &email.from_address,
            &email.to_address,
            &email.subject,
            &email.body,
            i32::from(email.is_read),
            &received_at
        ],
    )?;
    query::get_by_id(conn, id, user_id)?
        .ok_or_else(|| CoreError::internal("Failed to retrieve created email"))
}

/// Re-insert an email from a backup, preserving its original id.
pub(super) fn restore(conn: &Connection, user_id: UserId, email: &Email) -> Result<()> {
    // Durable content fields are round-tripped; account-linked and
    // sync-transient state (email_account_id, imap_uid, source_folder,
    // attachment_meta, draft_account_id) is intentionally omitted, those
    // FK into email_accounts (not part of a backup) or are re-derived on the
    // next IMAP sync. Preserving the original id + message_id makes a second
    // restore a no-op.
    let labels_json = serde_json::to_string(&email.labels).unwrap_or_else(|_| "[]".to_string());
    execute(
        conn,
        "INSERT OR IGNORE INTO emails (id, user_id, project_id, from_address, to_address, subject, body, body_format, html_body, is_read, is_archived, received_at, message_id, in_reply_to, thread_id, is_outgoing, labels, is_draft, cc_address, bcc_address, snoozed_until, waiting_for_response, waiting_since, expected_response_date) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
        params![
            email.id.to_string(),
            user_id.to_string(),
            email.project_id.map(|p| p.to_string()),
            &email.from,
            &email.to,
            &email.subject,
            &email.body,
            email.body_format.db_value(),
            &email.html_body,
            i32::from(email.is_read),
            i32::from(email.is_archived),
            format_datetime(&email.received_at),
            &email.message_id,
            &email.in_reply_to,
            &email.thread_id,
            i32::from(email.is_outgoing),
            &labels_json,
            i32::from(email.is_draft),
            &email.cc_address,
            &email.bcc_address,
            format_datetime_opt(email.snoozed_until),
            i32::from(email.waiting_for_response),
            format_datetime_opt(email.waiting_since),
            format_datetime_opt(email.expected_response_date)
        ],
    )?;
    Ok(())
}

/// Insert one synced message, keyed by a deterministic id from its message-id.
pub(super) fn create_with_tracking(
    conn: &Connection,
    user_id: UserId,
    email: &NewEmailWithTracking,
) -> Result<Email> {
    let id = goingson_core::deterministic_email_id(email.message_id.as_deref());
    let received_at = format_datetime(&email.received_at.unwrap_or_else(Utc::now));
    execute(
        conn,
        "INSERT INTO emails (id, user_id, project_id, from_address, to_address, subject, body, body_format, html_body, is_read, is_archived, received_at, message_id, in_reply_to, thread_id, email_account_id, is_outgoing, imap_uid, source_folder, attachment_meta, body_truncated, jmap_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
        params![
            id.to_string(),
            user_id.to_string(),
            email.project_id.as_ref().map(ToString::to_string),
            &email.from_address,
            &email.to_address,
            &email.subject,
            &email.body,
            email.body_format.db_value(),
            &email.html_body,
            i32::from(email.is_read),
            i32::from(email.is_archived),
            &received_at,
            &email.message_id,
            &email.in_reply_to,
            &email.thread_id,
            email.email_account_id.as_ref().map(ToString::to_string),
            i32::from(email.is_outgoing),
            email.imap_uid,
            &email.source_folder,
            &email.attachment_meta,
            i32::from(email.body_truncated),
            &email.jmap_id
        ],
    )?;
    query::get_by_id(conn, id, user_id)?
        .ok_or_else(|| CoreError::internal("Failed to retrieve created email"))
}

/// Insert a batch of synced messages in one transaction, skipping duplicates.
pub(super) fn create_with_tracking_batch(
    conn: &mut Connection,
    user_id: UserId,
    emails: Vec<NewEmailWithTracking>,
) -> Result<usize> {
    if emails.is_empty() {
        return Ok(0);
    }

    let mut count = 0usize;
    let uid = user_id.to_string();

    let tx = conn.transaction().map_err(CoreError::database)?;

    for email in emails {
        let id = goingson_core::deterministic_email_id(email.message_id.as_deref());
        let received_at = format_datetime(&email.received_at.unwrap_or_else(Utc::now));
        let result = execute(
            &tx,
            "INSERT OR IGNORE INTO emails (id, user_id, project_id, from_address, to_address, subject, body, body_format, html_body, is_read, is_archived, received_at, message_id, in_reply_to, thread_id, email_account_id, is_outgoing, imap_uid, source_folder, attachment_meta, body_truncated, jmap_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
            params![
                id.to_string(),
                &uid,
                email.project_id.map(|p| p.to_string()),
                &email.from_address,
                &email.to_address,
                &email.subject,
                &email.body,
                email.body_format.db_value(),
                &email.html_body,
                i32::from(email.is_read),
                i32::from(email.is_archived),
                &received_at,
                &email.message_id,
                &email.in_reply_to,
                &email.thread_id,
                email.email_account_id.map(|a| a.to_string()),
                i32::from(email.is_outgoing),
                email.imap_uid,
                &email.source_folder,
                &email.attachment_meta,
                i32::from(email.body_truncated),
                &email.jmap_id
            ],
        )?;
        if result > 0 {
            count += 1;
        }
    }

    tx.commit().map_err(CoreError::database)?;
    Ok(count)
}

/// Delete one email. Returns whether a row was removed.
pub(super) fn delete(conn: &Connection, id: EmailId, user_id: UserId) -> Result<bool> {
    let result = execute(
        conn,
        "DELETE FROM emails WHERE id = ? AND user_id = ?",
        params![id.to_string(), user_id.to_string()],
    )?;
    Ok(result > 0)
}