1pub mod analytics;
2
3pub use analytics::LinkAnalyticsRepository;
4
5use crate::error::ContentError;
6use crate::models::{CampaignLink, CreateLinkParams};
7use chrono::Utc;
8use sqlx::PgPool;
9use std::sync::Arc;
10use systemprompt_database::DbPool;
11use systemprompt_identifiers::{CampaignId, ContentId, LinkId};
12
13#[derive(Debug)]
14pub struct LinkRepository {
15 pool: Arc<PgPool>,
16 write_pool: Arc<PgPool>,
17}
18
19impl LinkRepository {
20 pub fn new(db: &DbPool) -> Result<Self, ContentError> {
21 let pool = db
22 .pool_arc()
23 .map_err(|e| ContentError::InvalidRequest(format!("Database pool error: {e}")))?;
24 let write_pool = db
25 .write_pool_arc()
26 .map_err(|e| ContentError::InvalidRequest(format!("Database write pool error: {e}")))?;
27 Ok(Self { pool, write_pool })
28 }
29
30 pub async fn create_link(
31 &self,
32 params: &CreateLinkParams,
33 ) -> Result<CampaignLink, sqlx::Error> {
34 let id = LinkId::generate();
35 let now = Utc::now();
36 sqlx::query_as!(
37 CampaignLink,
38 r#"
39 INSERT INTO campaign_links (
40 id, short_code, target_url, link_type, source_content_id, source_page,
41 campaign_id, campaign_name, utm_params, link_text, link_position,
42 destination_type, is_active, expires_at, created_at, updated_at
43 )
44 VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $15)
45 ON CONFLICT (short_code) DO UPDATE SET
46 target_url = EXCLUDED.target_url,
47 link_type = EXCLUDED.link_type,
48 source_content_id = EXCLUDED.source_content_id,
49 source_page = EXCLUDED.source_page,
50 campaign_id = EXCLUDED.campaign_id,
51 campaign_name = EXCLUDED.campaign_name,
52 utm_params = EXCLUDED.utm_params,
53 link_text = EXCLUDED.link_text,
54 link_position = EXCLUDED.link_position,
55 destination_type = EXCLUDED.destination_type,
56 is_active = EXCLUDED.is_active,
57 expires_at = EXCLUDED.expires_at,
58 updated_at = EXCLUDED.updated_at
59 RETURNING id as "id: LinkId", short_code, target_url, link_type,
60 campaign_id as "campaign_id: CampaignId", campaign_name,
61 source_content_id as "source_content_id: ContentId", source_page,
62 utm_params, link_text, link_position, destination_type,
63 click_count, unique_click_count, conversion_count,
64 is_active, expires_at, created_at, updated_at
65 "#,
66 id.as_str(),
67 params.short_code,
68 params.target_url,
69 params.link_type,
70 params.source_content_id.as_ref().map(ContentId::as_str),
71 params.source_page,
72 params.campaign_id.as_ref().map(CampaignId::as_str),
73 params.campaign_name,
74 params.utm_params,
75 params.link_text,
76 params.link_position,
77 params.destination_type,
78 params.is_active,
79 params.expires_at,
80 now
81 )
82 .fetch_one(&*self.write_pool)
83 .await
84 }
85
86 pub async fn get_link_by_short_code(
87 &self,
88 short_code: &str,
89 ) -> Result<Option<CampaignLink>, sqlx::Error> {
90 sqlx::query_as!(
91 CampaignLink,
92 r#"
93 SELECT id as "id: LinkId", short_code, target_url, link_type,
94 campaign_id as "campaign_id: CampaignId", campaign_name,
95 source_content_id as "source_content_id: ContentId", source_page,
96 utm_params, link_text, link_position, destination_type,
97 click_count, unique_click_count, conversion_count,
98 is_active, expires_at, created_at, updated_at
99 FROM campaign_links
100 WHERE short_code = $1 AND is_active = true
101 "#,
102 short_code
103 )
104 .fetch_optional(&*self.pool)
105 .await
106 }
107
108 pub async fn list_links_by_campaign(
109 &self,
110 campaign_id: &CampaignId,
111 ) -> Result<Vec<CampaignLink>, sqlx::Error> {
112 sqlx::query_as!(
113 CampaignLink,
114 r#"
115 SELECT id as "id: LinkId", short_code, target_url, link_type,
116 campaign_id as "campaign_id: CampaignId", campaign_name,
117 source_content_id as "source_content_id: ContentId", source_page,
118 utm_params, link_text, link_position, destination_type,
119 click_count, unique_click_count, conversion_count,
120 is_active, expires_at, created_at, updated_at
121 FROM campaign_links
122 WHERE campaign_id = $1
123 ORDER BY created_at DESC
124 "#,
125 campaign_id.as_str()
126 )
127 .fetch_all(&*self.pool)
128 .await
129 }
130
131 pub async fn list_links_by_source_content(
132 &self,
133 content_id: &ContentId,
134 ) -> Result<Vec<CampaignLink>, sqlx::Error> {
135 sqlx::query_as!(
136 CampaignLink,
137 r#"
138 SELECT id as "id: LinkId", short_code, target_url, link_type,
139 campaign_id as "campaign_id: CampaignId", campaign_name,
140 source_content_id as "source_content_id: ContentId", source_page,
141 utm_params, link_text, link_position, destination_type,
142 click_count, unique_click_count, conversion_count,
143 is_active, expires_at, created_at, updated_at
144 FROM campaign_links
145 WHERE source_content_id = $1
146 ORDER BY created_at DESC
147 "#,
148 content_id.as_str()
149 )
150 .fetch_all(&*self.pool)
151 .await
152 }
153
154 pub async fn get_link_by_id(&self, id: &LinkId) -> Result<Option<CampaignLink>, sqlx::Error> {
155 sqlx::query_as!(
156 CampaignLink,
157 r#"
158 SELECT id as "id: LinkId", short_code, target_url, link_type,
159 campaign_id as "campaign_id: CampaignId", campaign_name,
160 source_content_id as "source_content_id: ContentId", source_page,
161 utm_params, link_text, link_position, destination_type,
162 click_count, unique_click_count, conversion_count,
163 is_active, expires_at, created_at, updated_at
164 FROM campaign_links
165 WHERE id = $1
166 "#,
167 id.as_str()
168 )
169 .fetch_optional(&*self.pool)
170 .await
171 }
172
173 pub async fn find_link_by_source_and_target(
174 &self,
175 source_page: &str,
176 target_url: &str,
177 ) -> Result<Option<CampaignLink>, sqlx::Error> {
178 sqlx::query_as!(
179 CampaignLink,
180 r#"
181 SELECT id as "id: LinkId", short_code, target_url, link_type,
182 campaign_id as "campaign_id: CampaignId", campaign_name,
183 source_content_id as "source_content_id: ContentId", source_page,
184 utm_params, link_text, link_position, destination_type,
185 click_count, unique_click_count, conversion_count,
186 is_active, expires_at, created_at, updated_at
187 FROM campaign_links
188 WHERE source_page = $1 AND target_url = $2 AND is_active = true
189 ORDER BY created_at DESC
190 LIMIT 1
191 "#,
192 source_page,
193 target_url
194 )
195 .fetch_optional(&*self.pool)
196 .await
197 }
198
199 pub async fn delete_link(&self, id: &LinkId) -> Result<bool, sqlx::Error> {
200 let result = sqlx::query!("DELETE FROM campaign_links WHERE id = $1", id.as_str())
201 .execute(&*self.write_pool)
202 .await?;
203 Ok(result.rows_affected() > 0)
204 }
205}