1use sqlx::{Postgres, QueryBuilder};
2
3use crate::LibResult;
4
5const TABLE_NAME: &str = "npc_corporations";
6
7#[derive(Debug, serde::Serialize, serde::Deserialize, sqlx::FromRow)]
8pub struct CorporationDb {
9 #[serde(rename = "corporationId")]
10 pub corporation_id: i32,
11 #[serde(rename = "corporationName")]
12 pub corporation_name: String,
13 pub size: String,
14 pub extent: String,
15 #[serde(rename = "solarSystemId", skip_serializing_if = "Option::is_none")]
16 pub system_id: Option<i32>,
17 #[serde(rename = "friendId", skip_serializing_if = "Option::is_none")]
18 pub friend_id: Option<i32>,
19 #[serde(rename = "enemyId", skip_serializing_if = "Option::is_none")]
20 pub enemy_id: Option<i32>,
21 #[serde(rename = "publicShares")]
22 pub public_shares: i32,
23 #[serde(rename = "initialPrice")]
24 pub initial_price: i32,
25 #[serde(rename = "minSecurity")]
26 pub min_security: f32,
27 #[serde(rename = "factionId", skip_serializing_if = "Option::is_none")]
28 pub faction_id: Option<i32>,
29 #[serde(rename = "sizeFactor", skip_serializing_if = "Option::is_none")]
30 pub size_factor: Option<f32>,
31 #[serde(rename = "stationCount", skip_serializing_if = "Option::is_none")]
32 pub station_count: Option<i32>,
33 #[serde(rename = "stationSystemCount", skip_serializing_if = "Option::is_none")]
34 pub station_system_count: Option<i32>,
35 pub description: String,
36 #[serde(rename = "iconId", skip_serializing_if = "Option::is_none")]
37 pub icon_id: Option<i32>,
38}
39
40#[derive(Debug, serde::Serialize, serde::Deserialize)]
41pub struct CorporationFilter {
42 #[serde(rename = "corporationId")]
43 pub corporation_id: Option<String>,
44 pub page: Option<i64>,
45 pub limit: Option<i64>,
46}
47
48impl CorporationDb {
49 pub async fn get_by_id(corporation_id: i32) -> LibResult<Self> {
50 let pool = crate::pool();
51 let corporation = sqlx::query_as::<_, Self>(&format!(
52 r#"
53 SELECT * FROM {}
54 WHERE corporation_id = $1
55 "#,
56 TABLE_NAME
57 ))
58 .bind(corporation_id)
59 .fetch_one(pool)
60 .await?;
61
62 Ok(corporation)
63 }
64
65 pub async fn count(filter: &CorporationFilter) -> LibResult<i64> {
66 let pool = crate::pool();
67 let mut query = QueryBuilder::new(format!("SELECT COUNT(*) FROM {}", TABLE_NAME));
68 query = Self::build_query(query, filter);
69 let query = query.build_query_scalar();
70
71 let count = query.fetch_one(pool).await?;
72 Ok(count)
73 }
74
75 pub async fn get_multiple(filter: &CorporationFilter) -> LibResult<Vec<Self>> {
76 let page = filter.page.unwrap_or(1);
77 let limit = filter.limit.unwrap_or(100);
78
79 let pool = crate::pool();
80 let mut query = QueryBuilder::new(format!("SELECT * FROM {}", TABLE_NAME));
81 query = Self::build_query(query, filter);
82 query.push(" ORDER BY corporation_id LIMIT ");
83 query.push_bind(limit);
84 query.push(" OFFSET ");
85 query.push_bind((page - 1) * limit);
86 let query = query.build_query_as();
87
88 let corporations = query.fetch_all(pool).await?;
89 Ok(corporations)
90 }
91
92 fn build_query<'a>(
93 mut query: QueryBuilder<'a, Postgres>,
94 filter: &'a CorporationFilter,
95 ) -> QueryBuilder<'a, Postgres> {
96 let has_where = false;
97
98 if let Some(corporation_ids) = &filter.corporation_id {
100 let corporation_ids: Vec<&str> = corporation_ids.split(',').collect();
101 if !has_where {
102 query.push(" WHERE");
103 } else {
104 query.push(" AND");
105 }
106 query.push(" (");
107 for (i, corporation_id) in corporation_ids.iter().enumerate() {
108 if i > 0 {
109 query.push(" OR");
110 }
111 query.push(" corporation_id ILIKE ");
112 query.push_bind(format!("%{}%", corporation_id));
113 }
114 query.push(" )");
115 }
116
117 query
118 }
119
120 pub async fn insert(&self) -> LibResult<()> {
121 let pool = crate::pool();
122 sqlx::query(&format!(
123 r#"
124 INSERT INTO {} (
125 corporation_id,
126 corporation_name,
127 size,
128 extent,
129 system_id,
130 friend_id,
131 enemy_id,
132 public_shares,
133 initial_price,
134 min_security,
135 faction_id,
136 size_factor,
137 station_count,
138 station_system_count,
139 description,
140 icon_id
141 ) VALUES (
142 $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12,
143 $13, $14, $15, $16
144 )
145 "#,
146 TABLE_NAME
147 ))
148 .bind(&self.corporation_id)
149 .bind(&self.corporation_name)
150 .bind(&self.size)
151 .bind(&self.extent)
152 .bind(&self.system_id)
153 .bind(&self.friend_id)
154 .bind(&self.enemy_id)
155 .bind(&self.public_shares)
156 .bind(&self.initial_price)
157 .bind(&self.min_security)
158 .bind(&self.faction_id)
159 .bind(&self.size_factor)
160 .bind(&self.station_count)
161 .bind(&self.station_system_count)
162 .bind(&self.description)
163 .bind(&self.icon_id)
164 .execute(pool)
165 .await?;
166 Ok(())
167 }
168
169 pub async fn insert_multiple(corporations: &Vec<Self>) -> LibResult<()> {
170 let pool = crate::pool();
171 let step = 1000;
172 for i in (0..corporations.len()).step_by(step) {
173 let mut query = sqlx::QueryBuilder::new(format!(
174 r#"
175 INSERT INTO {} (corporation_id, corporation_name, size, extent, system_id, friend_id,
176 enemy_id, public_shares, initial_price, min_security, faction_id, size_factor,
177 station_count, station_system_count, description, icon_id)
178 VALUES
179 "#,
180 TABLE_NAME
181 ));
182
183 for j in 0..step {
184 if i + j >= corporations.len() {
185 break;
186 }
187 if j > 0 {
188 query.push(", ");
189 }
190 let corporation = &corporations[i + j];
191 query
192 .push(" (")
193 .push_bind(corporation.corporation_id)
194 .push(", ")
195 .push_bind(&corporation.corporation_name)
196 .push(", ")
197 .push_bind(&corporation.size)
198 .push(", ")
199 .push_bind(&corporation.extent)
200 .push(", ")
201 .push_bind(corporation.system_id)
202 .push(", ")
203 .push_bind(corporation.friend_id)
204 .push(", ")
205 .push_bind(corporation.enemy_id)
206 .push(", ")
207 .push_bind(corporation.public_shares)
208 .push(", ")
209 .push_bind(corporation.initial_price)
210 .push(", ")
211 .push_bind(corporation.min_security)
212 .push(", ")
213 .push_bind(corporation.faction_id)
214 .push(", ")
215 .push_bind(corporation.size_factor)
216 .push(", ")
217 .push_bind(corporation.station_count)
218 .push(", ")
219 .push_bind(corporation.station_system_count)
220 .push(", ")
221 .push_bind(&corporation.description)
222 .push(", ")
223 .push_bind(corporation.icon_id)
224 .push(") ");
225 }
226 query
227 .push("ON CONFLICT (corporation_id) DO UPDATE SET ")
228 .push("corporation_name = EXCLUDED.corporation_name, ")
229 .push("size = EXCLUDED.size, ")
230 .push("extent = EXCLUDED.extent, ")
231 .push("system_id = EXCLUDED.system_id, ")
232 .push("friend_id = EXCLUDED.friend_id, ")
233 .push("enemy_id = EXCLUDED.enemy_id, ")
234 .push("public_shares = EXCLUDED.public_shares, ")
235 .push("initial_price = EXCLUDED.initial_price, ")
236 .push("min_security = EXCLUDED.min_security, ")
237 .push("faction_id = EXCLUDED.faction_id, ")
238 .push("size_factor = EXCLUDED.size_factor, ")
239 .push("station_count = EXCLUDED.station_count, ")
240 .push("station_system_count = EXCLUDED.station_system_count, ")
241 .push("description = EXCLUDED.description, ")
242 .push("icon_id = EXCLUDED.icon_id");
243 query.build().execute(pool).await?;
244 }
245 Ok(())
246 }
247
248 pub async fn update(&self) -> LibResult<()> {
249 let pool = crate::pool();
250 sqlx::query(&format!(
251 r#"
252 UPDATE {} SET
253 corporation_name = $2,
254 size = $3,
255 extent = $4,
256 system_id = $5,
257 friend_id = $6,
258 enemy_id = $7,
259 public_shares = $8,
260 initial_price = $9,
261 min_security = $10,
262 faction_id = $11,
263 size_factor = $12,
264 station_count = $13,
265 station_system_count = $14,
266 description = $15,
267 icon_id = $16
268 WHERE corporation_id = $1
269 "#,
270 TABLE_NAME
271 ))
272 .bind(&self.corporation_id)
273 .bind(&self.corporation_name)
274 .bind(&self.size)
275 .bind(&self.extent)
276 .bind(&self.system_id)
277 .bind(&self.friend_id)
278 .bind(&self.enemy_id)
279 .bind(&self.public_shares)
280 .bind(&self.initial_price)
281 .bind(&self.min_security)
282 .bind(&self.faction_id)
283 .bind(&self.size_factor)
284 .bind(&self.station_count)
285 .bind(&self.station_system_count)
286 .bind(&self.description)
287 .bind(&self.icon_id)
288 .execute(pool)
289 .await?;
290 Ok(())
291 }
292
293 pub async fn delete(corporation_id: i32) -> LibResult<()> {
294 let pool = crate::pool();
295 sqlx::query(&format!(
296 r#"
297 DELETE FROM {}
298 WHERE corporation_id = $1
299 "#,
300 TABLE_NAME
301 ))
302 .bind(corporation_id)
303 .execute(pool)
304 .await?;
305 Ok(())
306 }
307}