Skip to content

armgit / macro

Public Imported from GitHub
0

Macro is a unified workspace for teams: email, chat, docs, tasks, agents, calls, and CRM — @-linked together with shared AI memory.

15.6 KB
Raw Blame
#[cfg(test)]
mod test;
pub mod upload;
pub mod upload_filters;

use crate::parse::db_to_service::map_db_attachment_to_service;
use crate::parse::service_to_db::map_service_attachments_to_db;
use models_email::db::attachment;
use models_email::{db, service};
use sqlx::types::Uuid;
use sqlx::{Executor, PgPool, Pool, Postgres};
use std::collections::HashMap;

/// inserts the metadata for attachments of an email into the database in a batch
#[tracing::instrument(skip(tx, attachments, message_id), err)]
#[allow(clippy::disallowed_methods, reason = "legacy code. fix later")]
pub async fn insert_attachments(
    tx: &mut sqlx::PgConnection,
    message_id: Uuid,
    attachments: &mut [service::attachment::Attachment],
) -> anyhow::Result<()> {
    let stable_ids=sqlx::query_scalar!(r#"SELECT l.provider='OUTLOOK' AS "stable!" FROM email_messages m JOIN email_links l ON l.id=m.link_id WHERE m.id=$1"#,message_id)
        .fetch_one(&mut *tx).await?;
    if attachments.is_empty() && !stable_ids {
        return Ok(());
    }
    if stable_ids
        && attachments
            .iter()
            .any(|attachment| attachment.provider_id.is_none())
    {
        anyhow::bail!("Outlook attachment snapshot is missing a provider identifier");
    }

    let db_attachments = map_service_attachments_to_db(attachments, message_id);

    let existing_attachments = sqlx::query_as!(
        db::attachment::Attachment,
        r#"
        SELECT ea.id, ea.message_id, ea.provider_attachment_id, ea.filename, ea.mime_type, ea.size_bytes, ea.content_id, eas.sfs_id as "sfs_id?", ea.created_at, ea.reference_url
        FROM email_attachments ea LEFT JOIN email_attachments_sfs eas on ea.id = eas.attachment_id
        WHERE message_id = $1
        "#,
        message_id
    ).fetch_all(&mut *tx).await?;

    // Outlook attachment IDs remain stable until replacement. Gmail retains its
    // metadata fallback because its attachment fetch IDs may rotate.

    let orphaned_attachments: Vec<_> = existing_attachments
        .iter()
        .filter(|existing| {
            !db_attachments.iter().any(|attachment| {
                if stable_ids {
                    existing.provider_attachment_id == attachment.provider_attachment_id
                } else {
                    existing.filename == attachment.filename
                        && existing.mime_type == attachment.mime_type
                        && existing.size_bytes == attachment.size_bytes
                        && existing.content_id == attachment.content_id
                        && existing.reference_url == attachment.reference_url
                }
            })
        })
        .cloned()
        .collect();

    // Refresh Outlook metadata without changing the Macro attachment identity.
    let new_attachments: Vec<_> = db_attachments
        .into_iter()
        .filter(|attachment| {
            stable_ids
                || !existing_attachments.iter().any(|existing| {
                    existing.filename == attachment.filename
                        && existing.mime_type == attachment.mime_type
                        && existing.size_bytes == attachment.size_bytes
                        && existing.content_id == attachment.content_id
                        && existing.reference_url == attachment.reference_url
                })
        })
        .collect();

    // delete orphaned attachments
    if !orphaned_attachments.is_empty() {
        let orphaned_ids: Vec<Uuid> = orphaned_attachments
            .iter()
            .map(|orphan| orphan.id)
            .collect();
        sqlx::query!(
            r#"
            DELETE FROM email_attachments
            WHERE id = ANY($1::uuid[])
            "#,
            &orphaned_ids
        )
        .execute(&mut *tx)
        .await?;
    }

    // The thread-level flag only moves when a calendar attachment enters or
    // leaves this message's attachment set.
    let calendar_set_changed = orphaned_attachments
        .iter()
        .chain(new_attachments.iter())
        .any(|a| is_calendar_attachment(a.filename.as_deref(), a.mime_type.as_deref()));

    if new_attachments.is_empty() {
        if calendar_set_changed {
            sync_thread_calendar_flag(&mut *tx, message_id).await?;
        }
        return Ok(());
    }

    let n = new_attachments.len();
    let mut attachment_ids_repeated: Vec<Uuid> = Vec::with_capacity(n);
    let mut message_ids_repeated: Vec<Uuid> = Vec::with_capacity(n);
    let mut provider_attachment_ids: Vec<Option<String>> = Vec::with_capacity(n);
    let mut filenames: Vec<Option<String>> = Vec::with_capacity(n);
    let mut mime_types: Vec<Option<String>> = Vec::with_capacity(n);
    let mut size_bytes_vec: Vec<Option<i64>> = Vec::with_capacity(n);
    let mut content_ids: Vec<Option<String>> = Vec::with_capacity(n);

    let mut reference_urls: Vec<Option<String>> = Vec::with_capacity(n);
    for attachment in new_attachments.into_iter() {
        reference_urls.push(attachment.reference_url);
        attachment_ids_repeated.push(attachment.id);
        message_ids_repeated.push(attachment.message_id);
        provider_attachment_ids.push(attachment.provider_attachment_id);
        filenames.push(attachment.filename);
        mime_types.push(attachment.mime_type);
        size_bytes_vec.push(attachment.size_bytes);
        content_ids.push(attachment.content_id);
    }

    // Use query() for unnest as query! has trouble inferring all Option types
    sqlx::query(
        r#"
        WITH input_rows (
            id, message_id, provider_attachment_id, filename, mime_type, size_bytes, content_id, reference_url
        ) AS (
           SELECT * FROM unnest(
               $1::uuid[], $2::uuid[], $3::text[], $4::varchar[], $5::varchar[], $6::bigint[], $7::varchar[], $8::text[]
           )
        )
        INSERT INTO email_attachments (
            id, message_id, provider_attachment_id, filename, mime_type, size_bytes, content_id, reference_url
        )
        SELECT id, message_id, provider_attachment_id, filename, mime_type, size_bytes, content_id, reference_url
        FROM input_rows
        ON CONFLICT (message_id, provider_attachment_id)
        DO UPDATE SET filename=EXCLUDED.filename,mime_type=EXCLUDED.mime_type,
            size_bytes=EXCLUDED.size_bytes,content_id=EXCLUDED.content_id,reference_url=EXCLUDED.reference_url
        "#,
    )
        .bind(&attachment_ids_repeated)
        .bind(&message_ids_repeated)
        .bind(&provider_attachment_ids)
        .bind(&filenames)
        .bind(&mime_types)
        .bind(&size_bytes_vec)
        .bind(&content_ids)
        .bind(&reference_urls)
        .execute(&mut *tx)
        .await?;

    if calendar_set_changed {
        sync_thread_calendar_flag(&mut *tx, message_id).await?;
    }

    Ok(())
}

/// Mirrors the SQL calendar-attachment predicate behind the CalendarOnly
/// email filter: `filename ILIKE '%.ics' OR mime_type IN ('text/calendar',
/// 'application/ics')`.
fn is_calendar_attachment(filename: Option<&str>, mime_type: Option<&str>) -> bool {
    filename.is_some_and(|f| f.to_ascii_lowercase().ends_with(".ics"))
        || mime_type.is_some_and(|m| m == "text/calendar" || m == "application/ics")
}

/// Resolves the message's thread and recomputes its denormalized
/// `has_calendar_attachment` flag.
async fn sync_thread_calendar_flag(
    tx: &mut sqlx::PgConnection,
    message_id: Uuid,
) -> anyhow::Result<()> {
    let thread_id: Option<Uuid> = sqlx::query_scalar!(
        "SELECT thread_id FROM email_messages WHERE id = $1",
        message_id
    )
    .fetch_optional(&mut *tx)
    .await?;

    if let Some(thread_id) = thread_id {
        crate::threads::update::sync_thread_calendar_flag(tx, thread_id).await?;
    }

    Ok(())
}

/// Fetches attachments for multiple messages and returns a map keyed by message_id
#[tracing::instrument(skip(pool), err)]
pub async fn fetch_db_attachments_in_bulk(
    pool: &PgPool,
    message_ids: &[Uuid],
) -> anyhow::Result<HashMap<Uuid, Vec<attachment::Attachment>>> {
    if message_ids.is_empty() {
        return Ok(HashMap::new());
    }

    let results = sqlx::query_as!(
        db::attachment::Attachment,
        r#"
        SELECT ea.id, ea.message_id, ea.provider_attachment_id, ea.filename, ea.mime_type, ea.size_bytes, ea.content_id, eas.sfs_id as "sfs_id?", ea.created_at, ea.reference_url
        FROM email_attachments ea
        LEFT JOIN email_attachments_sfs eas ON ea.id = eas.attachment_id
        WHERE ea.message_id = ANY($1)
        ORDER BY ea.message_id, ea.filename NULLS LAST
        "#,
        message_ids
    )
    .fetch_all(pool)
    .await?;

    let mut attachments_map: HashMap<Uuid, Vec<attachment::Attachment>> = HashMap::new();
    for attachment in results {
        attachments_map
            .entry(attachment.message_id)
            .or_default()
            .push(attachment);
    }

    Ok(attachments_map)
}

// deletes all attachments of a given message
#[tracing::instrument(skip(executor), err)]
pub async fn delete_message_attachments<'e, E>(executor: E, message_id: Uuid) -> anyhow::Result<()>
where
    E: Executor<'e, Database = Postgres>,
{
    sqlx::query!(
        r#"DELETE FROM email_attachments WHERE message_id = $1"#,
        message_id
    )
    .execute(executor)
    .await?;

    Ok(())
}

#[tracing::instrument(skip(pool), err)]
pub async fn fetch_attachment_by_id(
    pool: &PgPool,
    attachment_id: Uuid,
    link_id: Uuid,
) -> anyhow::Result<Option<(service::attachment::Attachment, String)>> {
    // Modified query to include message.provider_id and sfs_id
    let result = sqlx::query!(
        r#"
        SELECT 
            a.id, a.message_id, a.provider_attachment_id, 
            a.filename, a.mime_type, a.size_bytes, 
            a.content_id, eas.sfs_id as "sfs_id?", a.created_at, a.reference_url, m.provider_id as "message_provider_id!"
        FROM email_attachments a
        JOIN email_messages m ON a.message_id = m.id
        LEFT JOIN email_attachments_sfs eas ON a.id = eas.attachment_id
        WHERE a.id = $1 AND m.link_id = $2
        "#,
        attachment_id,
        link_id
    )
    .fetch_optional(pool)
    .await?;

    match result {
        Some(record) => {
            // Construct the attachment record
            let db_att = db::attachment::Attachment {
                id: record.id,
                message_id: record.message_id,
                provider_attachment_id: record.provider_attachment_id,
                filename: record.filename,
                mime_type: record.mime_type,
                size_bytes: record.size_bytes,
                content_id: record.content_id,
                sfs_id: record.sfs_id,
                created_at: record.created_at,
                reference_url: record.reference_url,
            };

            // Map to service attachment and include the message provider ID
            let service_att = map_db_attachment_to_service(db_att);
            Ok(Some((service_att, record.message_provider_id)))
        }
        None => Ok(None),
    }
}

#[tracing::instrument(skip(db), err)]
pub async fn get_attachments_by_thread_ids(
    db: &Pool<Postgres>,
    thread_ids: &[Uuid],
) -> anyhow::Result<HashMap<Uuid, Vec<service::attachment::Attachment>>> {
    if thread_ids.is_empty() {
        return Ok(HashMap::new());
    }

    // Query all attachments for messages in the provided threads
    // Include thread_id in the result set
    let attachments = sqlx::query!(
        r#"
        SELECT 
            a.id,
            a.message_id,
            a.provider_attachment_id,
            a.filename,
            a.mime_type,
            a.size_bytes,
            a.content_id,
            eas.sfs_id as "sfs_id?",
            a.created_at, a.reference_url,
            m.thread_id
        FROM 
            email_attachments a
        JOIN
            email_messages m ON a.message_id = m.id
        LEFT JOIN
            email_attachments_sfs eas ON a.id = eas.attachment_id
        WHERE
            m.thread_id = ANY($1)
        ORDER BY 
            a.created_at ASC
        "#,
        thread_ids
    )
    .fetch_all(db)
    .await?;

    // Group attachments by thread_id
    let mut result: HashMap<Uuid, Vec<service::attachment::Attachment>> = HashMap::new();

    for record in attachments {
        // Construct db::attachment::Attachment from the query result
        let db_attachment = db::attachment::Attachment {
            id: record.id,
            message_id: record.message_id,
            provider_attachment_id: record.provider_attachment_id,
            filename: record.filename,
            mime_type: record.mime_type,
            size_bytes: record.size_bytes,
            content_id: record.content_id,
            sfs_id: record.sfs_id,
            created_at: record.created_at,
            reference_url: record.reference_url,
        };

        // Map the db attachment to a service attachment
        let service_attachment = map_db_attachment_to_service(db_attachment);

        // Add to the result map
        result
            .entry(record.thread_id)
            .or_default()
            .push(service_attachment);
    }

    Ok(result)
}

/// Get document_id for email attachment if record exists
#[tracing::instrument(skip(db), err)]
pub async fn get_document_id_by_att_id_and_link(
    db: &Pool<Postgres>,
    link_id: Uuid,
    email_attachment_id: Uuid,
) -> anyhow::Result<Option<String>> {
    let result = sqlx::query!(
        r#"
        SELECT document_id
        FROM document_email de
        INNER JOIN "Document" d on de.document_id = d.id
        INNER JOIN email_attachments ea on de.email_attachment_id = ea.id
        INNER JOIN email_messages em on ea.message_id = em.id
        WHERE em.link_id = $1 AND email_attachment_id = $2 AND d."deletedAt" IS NULL
        "#,
        link_id,
        email_attachment_id,
    )
    .fetch_optional(db)
    .await?;

    Ok(result.map(|record| record.document_id))
}

/// The live document linked to an email attachment.
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct LinkedAttachmentDocument {
    /// The document id.
    pub document_id: String,
    /// The document's bytes have not finished reaching storage. DOCX is left
    /// out: it stays not uploaded until its conversion finishes.
    pub awaiting_upload: bool,
}

/// Get the document for email attachment if record exists
#[tracing::instrument(skip(db), err)]
pub async fn get_document_by_att_id(
    db: &Pool<Postgres>,
    email_attachment_id: Uuid,
) -> anyhow::Result<Option<LinkedAttachmentDocument>> {
    let result = sqlx::query!(
        r#"
        SELECT
            document_id,
            (NOT d.uploaded AND d."fileType" IS DISTINCT FROM 'docx') AS "awaiting_upload!"
        FROM document_email de
        INNER JOIN "Document" d on de.document_id = d.id
        INNER JOIN email_attachments ea on de.email_attachment_id = ea.id
        INNER JOIN email_messages em on ea.message_id = em.id
        WHERE email_attachment_id = $1 AND d."deletedAt" IS NULL
        "#,
        email_attachment_id,
    )
    .fetch_optional(db)
    .await?;

    Ok(result.map(|record| LinkedAttachmentDocument {
        document_id: record.document_id,
        awaiting_upload: record.awaiting_upload,
    }))
}

/// Get thread_id and link_id for an email attachment
#[tracing::instrument(skip(db), err)]
pub async fn get_thread_id_for_attachment(
    db: &Pool<Postgres>,
    attachment_id: Uuid,
) -> anyhow::Result<Option<(Uuid, Uuid)>> {
    let result = sqlx::query!(
        r#"
        SELECT et.id as thread_id, et.link_id
        FROM email_attachments ea
        INNER JOIN email_messages em ON ea.message_id = em.id
        INNER JOIN email_threads et ON em.thread_id = et.id
        WHERE ea.id = $1
        "#,
        attachment_id,
    )
    .fetch_optional(db)
    .await?;

    Ok(result.map(|record| (record.thread_id, record.link_id)))
}