//! chat message inserts, removals and the retention sweep
//!
//! Expiry is computed in SQL as `now() + make_interval(hours => …)` rather than
//! bound as a `chrono::DateTime`. That is not a style choice: the full server
//! build unifies sqlx's `time` and `chrono` features (the session store pulls
//! in `time`), and with both on, the compile-time macro infers a TIMESTAMPTZ
//! bind parameter as `time::OffsetDateTime`. An output column's type can be
//! overridden in the macro; a bind parameter's cannot. Computing the timestamp
//! server-side sidesteps the bind entirely and keeps every statement here
//! compile-time checked, which is what `mutations/moderation.rs` had to give up
//! to bind a ban's `expires_at`.

use super::{PgPool, Uuid};

/// A freshly inserted message, as the database assigned it.
pub struct InsertedChatMessage {
    pub id: i64,
    /// Unix seconds.
    pub created_at: i64,
}

/// Store one message, stamping its expiry from the room's retention.
///
/// `body_html` must already be rendered through docengine's chat preset. This
/// layer does not sanitize and must never be handed raw user input.
#[tracing::instrument(skip_all)]
pub async fn insert_chat_message(
    pool: &PgPool,
    community_id: Uuid,
    author_id: Uuid,
    body_html: &str,
    retention_hours: i32,
) -> Result<InsertedChatMessage, sqlx::Error> {
    sqlx::query_as!(
        InsertedChatMessage,
        r#"INSERT INTO chat_messages (community_id, author_id, body_html, expires_at)
           VALUES ($1, $2, $3, now() + make_interval(hours => $4))
           RETURNING id, EXTRACT(EPOCH FROM created_at)::BIGINT AS "created_at!""#,
        community_id,
        author_id,
        body_html,
        retention_hours,
    )
    .fetch_one(pool)
    .await
}

/// Remove one message from a room. Returns how many rows went, so a caller can
/// tell a real deletion from a message that had already expired.
#[tracing::instrument(skip_all)]
pub async fn delete_chat_message<'e, E: sqlx::PgExecutor<'e>>(
    executor: E,
    community_id: Uuid,
    message_id: i64,
) -> Result<u64, sqlx::Error> {
    let result = sqlx::query!(
        "DELETE FROM chat_messages WHERE community_id = $1 AND id = $2",
        community_id,
        message_id,
    )
    .execute(executor)
    .await?;
    Ok(result.rows_affected())
}

/// Remove every message one author wrote in a room, in one statement.
///
/// This is the ban purge. It is a single statement keyed by (room, author)
/// because a ban is issued on a hot path, at the moment the room is busiest,
/// and the caller broadcasts one `Purge` event rather than one `Delete` per
/// row.
#[tracing::instrument(skip_all)]
pub async fn purge_author_messages<'e, E: sqlx::PgExecutor<'e>>(
    executor: E,
    community_id: Uuid,
    author_id: Uuid,
) -> Result<u64, sqlx::Error> {
    let result = sqlx::query!(
        "DELETE FROM chat_messages WHERE community_id = $1 AND author_id = $2",
        community_id,
        author_id,
    )
    .execute(executor)
    .await?;
    Ok(result.rows_affected())
}

/// Delete every expired message across every room.
///
/// One indexed statement on `idx_chat_messages_expiry`, deliberately not keyed
/// by community: per-room would turn a single sweep into one statement per
/// community, and expiry is already stamped on the row.
#[tracing::instrument(skip_all)]
pub async fn sweep_expired_chat_messages(pool: &PgPool) -> Result<u64, sqlx::Error> {
    let result = sqlx::query!("DELETE FROM chat_messages WHERE expires_at < now()")
        .execute(pool)
        .await?;
    Ok(result.rows_affected())
}

/// Trim every room back to its `chat_max_messages` cap.
///
/// The count half of retention: age expires a quiet room, this bounds a busy
/// one that would otherwise reach its age limit holding far more than the cap.
///
/// One statement for the whole table rather than one per community. It does
/// walk every live chat row, which is affordable precisely because the other
/// half of retention already bounds that set, and because this runs on the
/// scheduler and never on a request. Per-community would be a query per
/// community on every tick, most of them finding nothing to do.
#[tracing::instrument(skip_all)]
pub async fn trim_chat_rooms_to_cap(pool: &PgPool) -> Result<u64, sqlx::Error> {
    let result = sqlx::query!(
        "DELETE FROM chat_messages c
         USING (
             SELECT ranked.id
             FROM (
                 SELECT id,
                        community_id,
                        row_number() OVER (
                            PARTITION BY community_id ORDER BY id DESC
                        ) AS rn
                 FROM chat_messages
             ) ranked
             JOIN communities co ON co.id = ranked.community_id
             WHERE ranked.rn > co.chat_max_messages
         ) over_cap
         WHERE c.id = over_cap.id"
    )
    .execute(pool)
    .await?;
    Ok(result.rows_affected())
}

/// Restamp a room's messages after its retention policy changed.
///
/// Expiry is stored on the row at insert, which is what makes the sweep one
/// indexed delete. The cost of that choice is exactly this: shortening a
/// window has to reach back over messages already sent, or the change would
/// only apply to future ones and an owner who just cut retention from 30 days
/// to 1 would still be holding 30 days of chat.
///
/// Recomputed from `created_at`, not from `now()`, so the new window means the
/// same thing for an old message as for a new one.
#[tracing::instrument(skip_all)]
pub async fn recompute_chat_expiry(
    pool: &PgPool,
    community_id: Uuid,
    retention_hours: i32,
) -> Result<u64, sqlx::Error> {
    let result = sqlx::query!(
        "UPDATE chat_messages
         SET expires_at = created_at + make_interval(hours => $2)
         WHERE community_id = $1",
        community_id,
        retention_hours,
    )
    .execute(pool)
    .await?;
    Ok(result.rows_affected())
}