use super::{BanType, DateTime, ModAction, ModActor, PgPool, Utc, Uuid};

/// Create or update a ban/mute. Returns the ban ID.
#[tracing::instrument(skip_all)]
pub async fn create_community_ban<'e, E: sqlx::PgExecutor<'e>>(
    executor: E,
    community_id: Uuid,
    user_id: Uuid,
    banned_by: Uuid,
    ban_type: BanType,
    reason: Option<&str>,
    expires_at: Option<DateTime<Utc>>,
) -> Result<Uuid, sqlx::Error> {
    // runtime-checked: 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 the TIMESTAMPTZ bind parameter as
    // `time::OffsetDateTime`, which a `chrono::DateTime<Utc>` won't satisfy.
    // Output-column type can be overridden in the macro; a bind parameter's
    // cannot. This is the one write path that binds a chrono timestamp, so it
    // stays a runtime-checked query (chrono's Encode handles the bind directly).
    sqlx::query_scalar::<_, Uuid>(
        "INSERT INTO community_bans (community_id, user_id, banned_by, ban_type, reason, expires_at)
         VALUES ($1, $2, $3, $4, $5, $6)
         ON CONFLICT (community_id, user_id, ban_type) DO UPDATE
         SET banned_by = $3, reason = $5, expires_at = $6, created_at = now()
         RETURNING id",
    )
    .bind(community_id)
    .bind(user_id)
    .bind(banned_by)
    .bind(ban_type.as_str())
    .bind(reason)
    .bind(expires_at)
    .fetch_one(executor)
    .await
}

/// Delete all bans/mutes whose expiration has passed.
#[tracing::instrument(skip_all)]
pub async fn cleanup_expired_bans(pool: &PgPool, community_id: Uuid) -> Result<u64, sqlx::Error> {
    let result = sqlx::query!(
        "DELETE FROM community_bans
         WHERE community_id = $1 AND expires_at IS NOT NULL AND expires_at <= now()",
        community_id,
    )
    .execute(pool)
    .await?;
    Ok(result.rows_affected())
}

/// Remove a ban or mute.
#[tracing::instrument(skip_all)]
pub async fn remove_community_ban<'e, E: sqlx::PgExecutor<'e>>(
    executor: E,
    community_id: Uuid,
    user_id: Uuid,
    ban_type: BanType,
) -> Result<(), sqlx::Error> {
    sqlx::query!(
        "DELETE FROM community_bans
         WHERE community_id = $1 AND user_id = $2 AND ban_type = $3",
        community_id,
        user_id,
        ban_type.as_str(),
    )
    .execute(executor)
    .await?;
    Ok(())
}

/// Insert a mod log entry on the given executor.
///
/// Generic over the executor so the audit row can be written on the *same*
/// transaction as the mutation it records: handlers open one `tx`, run the
/// mutation and this insert on `&mut *tx`, then commit, so an auditable action
/// can never commit without its log row. A `System` actor persists as a NULL
/// `actor_id` (migration 032).
#[tracing::instrument(skip_all)]
pub async fn insert_mod_log<'e, E>(
    executor: E,
    community_id: Option<Uuid>,
    actor: ModActor,
    action: ModAction,
    target_user: Option<Uuid>,
    target_id: Option<Uuid>,
    reason: Option<&str>,
) -> Result<(), sqlx::Error>
where
    E: sqlx::PgExecutor<'e>,
{
    sqlx::query!(
        "INSERT INTO mod_log (community_id, actor_id, action, target_user, target_id, reason)
         VALUES ($1, $2, $3, $4, $5, $6)",
        community_id,
        actor.id(),
        action.as_str(),
        target_user,
        target_id,
        reason,
    )
    .execute(executor)
    .await?;
    Ok(())
}

/// Insert a flag on a post. ON CONFLICT DO NOTHING (idempotent per user+post).
#[tracing::instrument(skip_all)]
pub async fn insert_flag(
    pool: &PgPool,
    post_id: Uuid,
    flagger_id: Uuid,
    reason: &str,
    detail: Option<&str>,
) -> Result<(), sqlx::Error> {
    sqlx::query!(
        "INSERT INTO post_flags (post_id, flagger_id, reason, detail)
         VALUES ($1, $2, $3, $4)
         ON CONFLICT (post_id, flagger_id) DO NOTHING",
        post_id,
        flagger_id,
        reason,
        detail,
    )
    .execute(pool)
    .await?;
    Ok(())
}

/// Resolve a single flag.
#[tracing::instrument(skip_all)]
pub async fn resolve_flag(
    pool: &PgPool,
    flag_id: Uuid,
    resolved_by: Uuid,
    resolution: &str,
) -> Result<(), sqlx::Error> {
    sqlx::query!(
        "UPDATE post_flags SET resolved_at = now(), resolved_by = $2, resolution = $3
         WHERE id = $1 AND resolved_at IS NULL",
        flag_id,
        resolved_by,
        resolution,
    )
    .execute(pool)
    .await?;
    Ok(())
}

/// Resolve all unresolved flags for a given post.
#[tracing::instrument(skip_all)]
pub async fn resolve_all_flags_for_post<'e, E: sqlx::PgExecutor<'e>>(
    executor: E,
    post_id: Uuid,
    resolved_by: Uuid,
    resolution: &str,
) -> Result<(), sqlx::Error> {
    sqlx::query!(
        "UPDATE post_flags SET resolved_at = now(), resolved_by = $2, resolution = $3
         WHERE post_id = $1 AND resolved_at IS NULL",
        post_id,
        resolved_by,
        resolution,
    )
    .execute(executor)
    .await?;
    Ok(())
}