1use sqlx::{Postgres, QueryBuilder};
2
3use crate::{LibResult, Error};
4
5const TABLE_NAME: &str = "map_systems";
6
7#[derive(Debug, serde::Serialize, serde::Deserialize, sqlx::FromRow)]
8pub struct SystemDb {
9 #[serde(rename = "regionId")]
10 pub region_id: i32,
11 #[serde(rename = "constellationId")]
12 pub constellation_id: i32,
13 #[serde(rename = "solarSystemId")]
14 pub system_id: i32,
15 #[serde(rename = "solarSystemName")]
16 pub system_name: String,
17 pub x: f32,
18 pub y: f32,
19 pub z: f32,
20 #[serde(rename = "xMin")]
21 pub x_min: f32,
22 #[serde(rename = "xMax")]
23 pub x_max: f32,
24 #[serde(rename = "yMin")]
25 pub y_min: f32,
26 #[serde(rename = "yMax")]
27 pub y_max: f32,
28 #[serde(rename = "zMin")]
29 pub z_min: f32,
30 #[serde(rename = "zMax")]
31 pub z_max: f32,
32 pub luminosity: f32,
33 pub border: bool,
34 pub fringe: bool,
35 pub corridor: bool,
36 pub hub: bool,
37 pub international: bool,
38 pub regional: bool,
39 #[serde(skip_serializing_if = "Option::is_none")]
40 pub constellation: Option<bool>,
41 pub security: f32,
42 #[serde(rename = "factionId", skip_serializing_if = "Option::is_none")]
43 pub faction_id: Option<i32>,
44 pub radius: f32,
45 #[serde(rename = "sunTypeId", skip_serializing_if = "Option::is_none")]
46 pub sun_type_id: Option<i32>,
47 #[serde(rename = "securityClass", skip_serializing_if = "Option::is_none")]
48 pub security_class: Option<String>,
49}
50
51#[derive(Debug, serde::Serialize, serde::Deserialize)]
52pub struct SystemFilter {
53 #[serde(rename = "systemID")]
54 pub system_id: Option<Vec<i32>>,
55 #[serde(rename = "systemName")]
56 pub system_name: Option<Vec<String>>,
57 #[serde(rename = "regionId")]
58 pub region_id: Option<Vec<i32>>,
59 #[serde(rename = "constellationId")]
60 pub constellation_id: Option<Vec<i32>>,
61 pub page: Option<i64>,
62 pub limit: Option<i64>,
63}
64
65impl SystemDb {
66 pub async fn get_by_id(system_id: i32) -> LibResult<Option<Self>> {
67 let pool = crate::pool();
68 let item = sqlx::query_as::<_, Self>(&format!(
69 "SELECT * FROM {} WHERE system_id = $1",
70 TABLE_NAME
71 ))
72 .bind(system_id)
73 .fetch_optional(pool)
74 .await?;
75 Ok(item)
76 }
77
78 pub async fn count(filter: &SystemFilter) -> LibResult<i64> {
79 let pool = crate::pool();
80 let mut query = QueryBuilder::new(format!("SELECT COUNT(*) FROM {}", TABLE_NAME));
81 query = Self::build_query(query, filter);
82 let query = query.build_query_scalar();
83
84 match query.fetch_one(pool).await {
85 Ok(count) => Ok(count),
86 Err(err) => {
87 log::error!("Error counting items: {}", err);
88 return Err(Error::new(500, format!("Error counting items: {}", err)));
89 }
90 }
91 }
92
93 pub async fn get(filter: &SystemFilter) -> LibResult<Vec<Self>> {
94 let page = filter.page.unwrap_or(1);
95 let limit = filter.limit.unwrap_or(100);
96
97 let pool = crate::pool();
98 let mut query = QueryBuilder::new(format!("SELECT * FROM {}", TABLE_NAME));
99 query = Self::build_query(query, filter);
100 query.push(" ORDER BY system_id LIMIT ");
101 query.push_bind(limit);
102 query.push(" OFFSET ");
103 query.push_bind((page - 1) * limit);
104 let query = query.build_query_as();
105 match query.fetch_all(pool).await {
106 Ok(items) => Ok(items),
107 Err(err) => {
108 log::error!("Error fetching items: {}", err);
109 return Err(Error::new(500, format!("Error fetching items: {}", err)));
110 }
111 }
112 }
113
114 fn build_query<'a>(
115 mut query: QueryBuilder<'a, Postgres>,
116 filter: &'a SystemFilter,
117 ) -> QueryBuilder<'a, Postgres> {
118 let mut has_where = false;
119
120 if let Some(system_names) = &filter.system_name {
122 if !has_where {
123 query.push(" WHERE");
124 has_where = true;
125 } else {
126 query.push(" AND");
127 }
128 query.push(" (");
129 for (i, system_name) in system_names.iter().enumerate() {
130 if i > 0 {
131 query.push(" OR");
132 }
133 query.push(" system_name ILIKE ");
134 query.push_bind(format!("%{}%", system_name));
135 }
136 query.push(" )");
137 }
138
139 if let Some(system_ids) = &filter.system_id {
141 if !has_where {
142 query.push(" WHERE");
143 has_where = true;
144 } else {
145 query.push(" AND");
146 }
147 query.push(" (");
148 for (i, system_id) in system_ids.iter().enumerate() {
149 if i > 0 {
150 query.push(" OR");
151 }
152 query.push(" system_id = ");
153 query.push_bind(system_id);
154 }
155 query.push(" )");
156 }
157
158 if let Some(region_ids) = &filter.region_id {
160 if !has_where {
161 query.push(" WHERE");
162 } else {
163 query.push(" AND");
164 }
165 query.push(" (");
166 for (i, region_id) in region_ids.iter().enumerate() {
167 if i > 0 {
168 query.push(" OR");
169 }
170 query.push(" region_id = ");
171 query.push_bind(region_id);
172 }
173 query.push(" )");
174 }
175
176 query
177 }
178
179 pub async fn insert(&self) -> LibResult<()> {
180 let pool = crate::pool();
181 sqlx::query(&format!(
182 "INSERT INTO {} (
183 region_id,
184 constellation_id,
185 system_id,
186 system_name,
187 x,
188 y,
189 z,
190 x_min,
191 x_max,
192 y_min,
193 y_max,
194 z_min,
195 z_max,
196 luminosity,
197 border,
198 fringe,
199 corridor,
200 hub,
201 international,
202 regional,
203 constellation,
204 security,
205 faction_id,
206 radius,
207 sun_type_id,
208 security_class
209 ) VALUES (
210 $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26
211 )",
212 TABLE_NAME
213 ))
214 .bind(self.region_id)
215 .bind(self.constellation_id)
216 .bind(&self.system_id)
217 .bind(&self.system_name)
218 .bind(self.x)
219 .bind(self.y)
220 .bind(self.z)
221 .bind(self.x_min)
222 .bind(self.x_max)
223 .bind(self.y_min)
224 .bind(self.y_max)
225 .bind(self.z_min)
226 .bind(self.z_max)
227 .bind(self.luminosity)
228 .bind(self.border)
229 .bind(self.fringe)
230 .bind(self.corridor)
231 .bind(self.hub)
232 .bind(self.international)
233 .bind(self.regional)
234 .bind(self.constellation)
235 .bind(self.security)
236 .bind(self.faction_id)
237 .bind(self.radius)
238 .bind(&self.sun_type_id)
239 .bind(&self.security_class)
240 .execute(pool)
241 .await?;
242 Ok(())
243 }
244
245 pub async fn insert_multiple(systems: &Vec<Self>) -> LibResult<()> {
246 let pool = crate::pool();
247 let step = 1000;
248 for i in (0..systems.len()).step_by(step) {
249 let mut query = sqlx::QueryBuilder::new(format!(
250 r#"
251 INSERT INTO {} (region_id, constellation_id, system_id, system_name, x, y, z, x_min, x_max, y_min, y_max, z_min, z_max, luminosity, border, fringe, corridor, hub, international, regional, constellation, security, faction_id, radius, sun_type_id, security_class)
252 VALUES
253 "#,
254 TABLE_NAME
255 ));
256
257 for j in 0..step {
258 if i + j >= systems.len() {
259 break;
260 }
261 if j > 0 {
262 query.push(", ");
263 }
264 let system = &systems[i + j];
265 query
266 .push(" (")
267 .push_bind(system.region_id)
268 .push(", ")
269 .push_bind(system.constellation_id)
270 .push(", ")
271 .push_bind(system.system_id)
272 .push(", ")
273 .push_bind(&system.system_name)
274 .push(", ")
275 .push_bind(system.x)
276 .push(", ")
277 .push_bind(system.y)
278 .push(", ")
279 .push_bind(system.z)
280 .push(", ")
281 .push_bind(system.x_min)
282 .push(", ")
283 .push_bind(system.x_max)
284 .push(", ")
285 .push_bind(system.y_min)
286 .push(", ")
287 .push_bind(system.y_max)
288 .push(", ")
289 .push_bind(system.z_min)
290 .push(", ")
291 .push_bind(system.z_max)
292 .push(", ")
293 .push_bind(system.luminosity)
294 .push(", ")
295 .push_bind(system.border)
296 .push(", ")
297 .push_bind(system.fringe)
298 .push(", ")
299 .push_bind(system.corridor)
300 .push(", ")
301 .push_bind(system.hub)
302 .push(", ")
303 .push_bind(system.international)
304 .push(", ")
305 .push_bind(system.regional)
306 .push(", ")
307 .push_bind(system.constellation)
308 .push(", ")
309 .push_bind(system.security)
310 .push(", ")
311 .push_bind(system.faction_id)
312 .push(", ")
313 .push_bind(system.radius)
314 .push(", ")
315 .push_bind(system.sun_type_id)
316 .push(", ")
317 .push_bind(&system.security_class)
318 .push(") ");
319 }
320 query
321 .push("ON CONFLICT (system_id) DO UPDATE SET ")
322 .push("region_id = EXCLUDED.region_id, ")
323 .push("constellation_id = EXCLUDED.constellation_id, ")
324 .push("system_name = EXCLUDED.system_name, ")
325 .push("x = EXCLUDED.x, ")
326 .push("y = EXCLUDED.y, ")
327 .push("z = EXCLUDED.z, ")
328 .push("x_min = EXCLUDED.x_min, ")
329 .push("x_max = EXCLUDED.x_max, ")
330 .push("y_min = EXCLUDED.y_min, ")
331 .push("y_max = EXCLUDED.y_max, ")
332 .push("z_min = EXCLUDED.z_min, ")
333 .push("z_max = EXCLUDED.z_max, ")
334 .push("luminosity = EXCLUDED.luminosity, ")
335 .push("border = EXCLUDED.border, ")
336 .push("fringe = EXCLUDED.fringe, ")
337 .push("corridor = EXCLUDED.corridor, ")
338 .push("hub = EXCLUDED.hub, ")
339 .push("international = EXCLUDED.international, ")
340 .push("regional = EXCLUDED.regional, ")
341 .push("constellation = EXCLUDED.constellation, ")
342 .push("security = EXCLUDED.security, ")
343 .push("faction_id = EXCLUDED.faction_id, ")
344 .push("radius = EXCLUDED.radius, ")
345 .push("sun_type_id = EXCLUDED.sun_type_id, ")
346 .push("security_class = EXCLUDED.security_class");
347 query.build().execute(pool).await?;
348 }
349 Ok(())
350 }
351
352 pub async fn update(&self) -> LibResult<()> {
353 let pool = crate::pool();
354 sqlx::query(&format!(
355 "UPDATE {} SET
356 region_id = $2,
357 constellation_id = $3,
358 system_name = $4,
359 x = $5,
360 y = $6,
361 z = $7,
362 x_min = $8,
363 x_max = $9,
364 y_min = $10,
365 y_max = $11,
366 z_min = $12,
367 z_max = $13,
368 luminosity = $14,
369 border = $15,
370 fringe = $16,
371 corridor = $17,
372 hub = $18,
373 international = $19,
374 regional = $20,
375 constellation = $21,
376 security = $22,
377 faction_id = $23,
378 radius = $24,
379 sun_type_id = $25,
380 security_class = $26
381 WHERE system_id = $1",
382 TABLE_NAME
383 ))
384 .bind(&self.system_id)
385 .bind(self.region_id)
386 .bind(self.constellation_id)
387 .bind(&self.system_name)
388 .bind(self.x)
389 .bind(self.y)
390 .bind(self.z)
391 .bind(self.x_min)
392 .bind(self.x_max)
393 .bind(self.y_min)
394 .bind(self.y_max)
395 .bind(self.z_min)
396 .bind(self.z_max)
397 .bind(self.luminosity)
398 .bind(self.border)
399 .bind(self.fringe)
400 .bind(self.corridor)
401 .bind(self.hub)
402 .bind(self.international)
403 .bind(self.regional)
404 .bind(self.constellation)
405 .bind(self.security)
406 .bind(self.faction_id)
407 .bind(self.radius)
408 .bind(&self.sun_type_id)
409 .bind(&self.security_class)
410 .execute(pool)
411 .await?;
412 Ok(())
413 }
414
415 pub async fn delete(system_id: i64) -> LibResult<()> {
416 let pool = crate::pool();
417 sqlx::query(&format!("DELETE FROM {} WHERE system_id = $1", TABLE_NAME))
418 .bind(system_id)
419 .execute(pool)
420 .await?;
421 Ok(())
422 }
423}