systemprompt-analytics 0.2.2

use anyhow::Result;
use systemprompt_identifiers::UserId;

use super::FingerprintRepository;
use crate::models::{FingerprintReputation, FlagReason};

impl FingerprintRepository {
    pub async fn upsert_fingerprint(
        &self,
        fingerprint_hash: &str,
        ip_address: Option<&str>,
        user_agent: Option<&str>,
        user_id: Option<&UserId>,
    ) -> Result<FingerprintReputation> {
        let user_ids = user_id.map_or_else(Vec::new, |u| vec![u.as_str().to_string()]);

        let row = sqlx::query_as!(
            FingerprintReputation,
            r#"
            INSERT INTO fingerprint_reputation (
                fingerprint_hash, last_ip_address, last_user_agent,
                associated_user_ids, total_session_count
            )
            VALUES ($1, $2, $3, $4, 1)
            ON CONFLICT (fingerprint_hash) DO UPDATE SET
                last_seen_at = CURRENT_TIMESTAMP,
                last_ip_address = COALESCE($2, fingerprint_reputation.last_ip_address),
                last_user_agent = COALESCE($3, fingerprint_reputation.last_user_agent),
                total_session_count = fingerprint_reputation.total_session_count + 1,
                associated_user_ids = CASE
                    WHEN array_length($4, 1) > 0 AND NOT ($4[1] = ANY(fingerprint_reputation.associated_user_ids))
                    THEN array_cat(fingerprint_reputation.associated_user_ids, $4)
                    ELSE fingerprint_reputation.associated_user_ids
                END,
                updated_at = CURRENT_TIMESTAMP
            RETURNING
                fingerprint_hash,
                first_seen_at,
                last_seen_at,
                total_session_count,
                active_session_count,
                total_request_count,
                requests_last_hour,
                peak_requests_per_minute,
                sustained_high_velocity_minutes,
                is_flagged,
                flag_reason,
                flagged_at,
                reputation_score,
                abuse_incidents,
                last_abuse_at,
                last_ip_address,
                last_user_agent,
                associated_user_ids,
                updated_at
            "#,
            fingerprint_hash,
            ip_address,
            user_agent,
            &user_ids[..],
        )
        .fetch_one(&*self.write_pool)
        .await?;

        Ok(row)
    }

    pub async fn flag_fingerprint(
        &self,
        fingerprint_hash: &str,
        reason: FlagReason,
        new_score: i32,
    ) -> Result<()> {
        sqlx::query!(
            r#"
            UPDATE fingerprint_reputation
            SET is_flagged = TRUE,
                flag_reason = $2,
                flagged_at = CURRENT_TIMESTAMP,
                reputation_score = $3,
                abuse_incidents = abuse_incidents + 1,
                last_abuse_at = CURRENT_TIMESTAMP,
                updated_at = CURRENT_TIMESTAMP
            WHERE fingerprint_hash = $1
            "#,
            fingerprint_hash,
            reason.as_str(),
            new_score,
        )
        .execute(&*self.write_pool)
        .await?;

        Ok(())
    }

    pub async fn update_velocity_metrics(
        &self,
        fingerprint_hash: &str,
        requests_last_hour: i32,
        peak_requests_per_minute: f32,
        sustained_high_velocity_minutes: i32,
    ) -> Result<()> {
        sqlx::query!(
            r#"
            UPDATE fingerprint_reputation
            SET requests_last_hour = $2,
                peak_requests_per_minute = $3,
                sustained_high_velocity_minutes = $4,
                total_request_count = total_request_count + 1,
                updated_at = CURRENT_TIMESTAMP
            WHERE fingerprint_hash = $1
            "#,
            fingerprint_hash,
            requests_last_hour,
            peak_requests_per_minute,
            sustained_high_velocity_minutes,
        )
        .execute(&*self.write_pool)
        .await?;

        Ok(())
    }

    pub async fn update_active_session_count(
        &self,
        fingerprint_hash: &str,
        active_count: i32,
    ) -> Result<()> {
        sqlx::query!(
            r#"
            UPDATE fingerprint_reputation
            SET active_session_count = $2,
                updated_at = CURRENT_TIMESTAMP
            WHERE fingerprint_hash = $1
            "#,
            fingerprint_hash,
            active_count,
        )
        .execute(&*self.write_pool)
        .await?;

        Ok(())
    }

    pub async fn increment_request_count(&self, fingerprint_hash: &str) -> Result<()> {
        sqlx::query!(
            r#"
            UPDATE fingerprint_reputation
            SET total_request_count = total_request_count + 1,
                updated_at = CURRENT_TIMESTAMP
            WHERE fingerprint_hash = $1
            "#,
            fingerprint_hash,
        )
        .execute(&*self.write_pool)
        .await?;

        Ok(())
    }

    pub async fn clear_flag(&self, fingerprint_hash: &str) -> Result<()> {
        sqlx::query!(
            r#"
            UPDATE fingerprint_reputation
            SET is_flagged = FALSE,
                flag_reason = NULL,
                flagged_at = NULL,
                updated_at = CURRENT_TIMESTAMP
            WHERE fingerprint_hash = $1
            "#,
            fingerprint_hash,
        )
        .execute(&*self.write_pool)
        .await?;

        Ok(())
    }

    pub async fn adjust_reputation_score(&self, fingerprint_hash: &str, delta: i32) -> Result<i32> {
        let row = sqlx::query_scalar!(
            r#"
            UPDATE fingerprint_reputation
            SET reputation_score = GREATEST(0, LEAST(100, reputation_score + $2)),
                updated_at = CURRENT_TIMESTAMP
            WHERE fingerprint_hash = $1
            RETURNING reputation_score as "reputation_score!"
            "#,
            fingerprint_hash,
            delta,
        )
        .fetch_one(&*self.write_pool)
        .await?;

        Ok(row)
    }
}