systemprompt_analytics/repository/funnel/
finders.rs

1//! Funnel lookup queries.
2//!
3//! Copyright (c) systemprompt.io — Business Source License 1.1.
4//! See <https://systemprompt.io> for licensing details.
5
6use crate::Result;
7use systemprompt_identifiers::{FunnelId, SessionId};
8
9use super::FunnelRepository;
10use super::types::{FunnelProgressRow, FunnelRow, FunnelStepRow};
11use crate::models::{Funnel, FunnelProgress, FunnelStep, FunnelWithSteps};
12
13impl FunnelRepository {
14    pub async fn find_by_id(&self, id: &FunnelId) -> Result<Option<FunnelWithSteps>> {
15        let funnel_row = sqlx::query_as!(
16            FunnelRow,
17            r#"
18            SELECT id, name, description, is_active, created_at, updated_at
19            FROM funnels WHERE id = $1
20            "#,
21            id.as_str()
22        )
23        .fetch_optional(&*self.pool)
24        .await?;
25
26        let Some(row) = funnel_row else {
27            return Ok(None);
28        };
29
30        let funnel = row.into_funnel();
31        let steps = self.get_steps_for_funnel(id).await?;
32
33        Ok(Some(FunnelWithSteps { funnel, steps }))
34    }
35
36    pub async fn find_by_name(&self, name: &str) -> Result<Option<FunnelWithSteps>> {
37        let funnel_row = sqlx::query_as!(
38            FunnelRow,
39            r#"
40            SELECT id, name, description, is_active, created_at, updated_at
41            FROM funnels WHERE name = $1
42            "#,
43            name
44        )
45        .fetch_optional(&*self.pool)
46        .await?;
47
48        let Some(row) = funnel_row else {
49            return Ok(None);
50        };
51
52        let funnel = row.into_funnel();
53        let funnel_id = FunnelId::new(funnel.id.as_str());
54        let steps = self.get_steps_for_funnel(&funnel_id).await?;
55
56        Ok(Some(FunnelWithSteps { funnel, steps }))
57    }
58
59    pub async fn list_active(&self) -> Result<Vec<Funnel>> {
60        let rows = sqlx::query_as!(
61            FunnelRow,
62            r#"
63            SELECT id, name, description, is_active, created_at, updated_at
64            FROM funnels WHERE is_active = TRUE ORDER BY name
65            "#
66        )
67        .fetch_all(&*self.pool)
68        .await?;
69
70        Ok(rows.into_iter().map(FunnelRow::into_funnel).collect())
71    }
72
73    pub async fn list_all(&self) -> Result<Vec<Funnel>> {
74        let rows = sqlx::query_as!(
75            FunnelRow,
76            r#"
77            SELECT id, name, description, is_active, created_at, updated_at
78            FROM funnels ORDER BY name
79            "#
80        )
81        .fetch_all(&*self.pool)
82        .await?;
83
84        Ok(rows.into_iter().map(FunnelRow::into_funnel).collect())
85    }
86
87    pub async fn find_progress(
88        &self,
89        funnel_id: &FunnelId,
90        session_id: &SessionId,
91    ) -> Result<Option<FunnelProgress>> {
92        let row = sqlx::query_as!(
93            FunnelProgressRow,
94            r#"
95            SELECT id, funnel_id, session_id, current_step, completed_at, dropped_at_step,
96                   step_timestamps, created_at, updated_at
97            FROM funnel_progress WHERE funnel_id = $1 AND session_id = $2
98            "#,
99            funnel_id.as_str(),
100            session_id.as_str()
101        )
102        .fetch_optional(&*self.pool)
103        .await?;
104
105        Ok(row.map(FunnelProgressRow::into_progress))
106    }
107
108    pub(super) async fn get_steps_for_funnel(
109        &self,
110        funnel_id: &FunnelId,
111    ) -> Result<Vec<FunnelStep>> {
112        let rows = sqlx::query_as!(
113            FunnelStepRow,
114            r#"
115            SELECT funnel_id, step_order, name, match_pattern, match_type
116            FROM funnel_steps WHERE funnel_id = $1 ORDER BY step_order
117            "#,
118            funnel_id.as_str()
119        )
120        .fetch_all(&*self.pool)
121        .await?;
122
123        Ok(rows.into_iter().map(FunnelStepRow::into_step).collect())
124    }
125}
systemprompt_analytics/repository/funnel/finders.rs

systemprompt_analytics/repository/funnel/
finders.rs
