1use sqlx::{Postgres, QueryBuilder};
2
3use crate::{LibResult, Error};
4
5const TABLE_NAME: &str = "npc_stations";
6
7#[derive(Debug, serde::Serialize, serde::Deserialize, sqlx::FromRow)]
8pub struct StationDb {
9 #[serde(rename = "stationId")]
10 pub station_id: i64,
11 pub security: f32,
12 #[serde(rename = "operationType")]
13 pub operation_type: String,
14 #[serde(rename = "services")]
15 pub services: Vec<String>,
16 #[serde(rename = "corporationId")]
17 pub corporation_id: i32,
18 #[serde(rename = "solarSystemId")]
19 pub system_id: i32,
20 #[serde(rename = "constellationId")]
21 pub constellation_id: i32,
22 #[serde(rename = "regionId")]
23 pub region_id: i32,
24 #[serde(rename = "stationName")]
25 pub station_name: String,
26 pub x: f32,
27 pub y: f32,
28 pub z: f32,
29 #[serde(rename = "reprocessingEfficiency")]
30 pub reprocessing_efficiency: f32,
31 #[serde(rename = "reprocessingStationsTake")]
32 pub reprocessing_stations_take: f32,
33 #[serde(rename = "reprocessingHangarFlag")]
34 pub reprocessing_hangar_flag: i32,
35}
36
37#[derive(Debug, serde::Serialize, serde::Deserialize)]
38pub struct StationFilter {
39 #[serde(rename = "stationId")]
40 pub station_id: Option<Vec<i64>>,
41 #[serde(rename = "stationName")]
42 pub station_name: Option<Vec<String>>,
43 #[serde(rename = "systemId")]
44 pub system_id: Option<Vec<i32>>,
45 pub page: Option<i64>,
46 pub limit: Option<i64>,
47}
48
49impl StationDb {
50 pub async fn get_by_id(station_id: i64) -> LibResult<Option<Self>> {
51 let pool = crate::pool();
52 let station = sqlx::query_as::<_, Self>(&format!(
53 r#"
54 SELECT * FROM {} WHERE station_id = $1
55 "#,
56 TABLE_NAME
57 ))
58 .bind(station_id)
59 .fetch_optional(pool)
60 .await?;
61
62 Ok(station)
63 }
64
65 pub async fn count(filter: &StationFilter) -> 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 match query.fetch_one(pool).await {
72 Ok(count) => Ok(count),
73 Err(err) => {
74 log::error!("Error counting items: {}", err);
75 return Err(Error::new(500, format!("Error counting items: {}", err)));
76 }
77 }
78 }
79
80 pub async fn get(filter: &StationFilter) -> LibResult<Vec<Self>> {
81 let page = filter.page.unwrap_or(1);
82 let limit = filter.limit.unwrap_or(100);
83
84 let pool = crate::pool();
85 let mut query = QueryBuilder::new(format!("SELECT * FROM {}", TABLE_NAME));
86 query = Self::build_query(query, filter);
87 query.push(" ORDER BY station_id LIMIT ");
88 query.push_bind(limit);
89 query.push(" OFFSET ");
90 query.push_bind((page - 1) * limit);
91 let query = query.build_query_as();
92
93 match query.fetch_all(pool).await {
94 Ok(items) => Ok(items),
95 Err(err) => {
96 log::error!("Error fetching items: {}", err);
97 return Err(Error::new(500, format!("Error fetching items: {}", err)));
98 }
99 }
100 }
101
102 fn build_query<'a>(
103 mut query: QueryBuilder<'a, Postgres>,
104 filter: &'a StationFilter,
105 ) -> QueryBuilder<'a, Postgres> {
106 let mut has_where = false;
107
108 if let Some(names) = &filter.station_name {
110 if !has_where {
111 query.push(" WHERE");
112 has_where = true;
113 } else {
114 query.push(" AND");
115 }
116 query.push(" (");
117 for (i, name) in names.iter().enumerate() {
118 if i > 0 {
119 query.push(" OR");
120 }
121 query.push(" name ILIKE ");
122 query.push_bind(format!("%{}%", name));
123 }
124 query.push(" )");
125 }
126
127 if let Some(station_ids) = &filter.station_id {
129 if !has_where {
130 query.push(" WHERE");
131 has_where = true;
132 } else {
133 query.push(" AND");
134 }
135 query.push(" (");
136 for (i, station_id) in station_ids.iter().enumerate() {
137 if i > 0 {
138 query.push(" OR");
139 }
140 query.push(" station_id = ");
141 query.push_bind(station_id);
142 }
143 query.push(" )");
144 }
145
146 if let Some(system_ids) = &filter.system_id {
148 if !has_where {
149 query.push(" WHERE");
150 } else {
151 query.push(" AND");
152 }
153 query.push(" (");
154 for (i, system_id) in system_ids.iter().enumerate() {
155 if i > 0 {
156 query.push(" OR");
157 }
158 query.push(" system_id = ");
159 query.push_bind(system_id);
160 }
161 query.push(" )");
162 }
163
164 query
165 }
166
167 pub async fn insert(&self) -> LibResult<()> {
168 let pool = crate::pool();
169 sqlx::query(&format!(
170 r#"
171 INSERT INTO {} (
172 station_id,
173 security,
174 operation_type,
175 services,
176 corporation_id,
177 system_id,
178 constellation_id,
179 region_id,
180 station_name,
181 x: f32,
182 y: f32,
183 z: f32,
184 reprocessing_efficiency,
185 reprocessing_stations_take,
186 reprocessing_hangar_flag,
187 ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18)
188 "#,
189 TABLE_NAME
190 ))
191 .bind(self.station_id)
192 .bind(self.security)
193 .bind(&self.operation_type)
194 .bind(&self.services)
195 .bind(self.corporation_id)
196 .bind(self.system_id)
197 .bind(self.constellation_id)
198 .bind(self.region_id)
199 .bind(&self.station_name)
200 .bind(self.x)
201 .bind(self.y)
202 .bind(self.z)
203 .bind(self.reprocessing_efficiency)
204 .bind(self.reprocessing_stations_take)
205 .bind(self.reprocessing_hangar_flag)
206 .execute(pool)
207 .await?;
208
209 Ok(())
210 }
211
212 pub async fn insert_multiple(stations: &Vec<Self>) -> LibResult<()> {
213 let pool = crate::pool();
214 let step = 1000;
215 for i in (0..stations.len()).step_by(step) {
216 let mut query = sqlx::QueryBuilder::new(format!(
217 r#"
218 INSERT INTO {} (
219 station_id,
220 security,
221 operation_type,
222 services,
223 corporation_id,
224 system_id,
225 constellation_id,
226 region_id,
227 station_name,
228 x,
229 y,
230 z,
231 reprocessing_efficiency,
232 reprocessing_stations_take,
233 reprocessing_hangar_flag
234 )
235 VALUES
236 "#,
237 TABLE_NAME
238 ));
239
240 for j in 0..step {
241 if i + j >= stations.len() {
242 break;
243 }
244 if j > 0 {
245 query.push(", ");
246 }
247 let station = &stations[i + j];
248 query
249 .push(" (")
250 .push_bind(station.station_id)
251 .push(", ")
252 .push_bind(station.security)
253 .push(", ")
254 .push_bind(&station.operation_type)
255 .push(", ")
256 .push_bind(&station.services)
257 .push(", ")
258 .push_bind(station.corporation_id)
259 .push(", ")
260 .push_bind(station.system_id)
261 .push(", ")
262 .push_bind(station.constellation_id)
263 .push(", ")
264 .push_bind(station.region_id)
265 .push(", ")
266 .push_bind(&station.station_name)
267 .push(", ")
268 .push_bind(station.x)
269 .push(", ")
270 .push_bind(station.y)
271 .push(", ")
272 .push_bind(station.z)
273 .push(", ")
274 .push_bind(station.reprocessing_efficiency)
275 .push(", ")
276 .push_bind(station.reprocessing_stations_take)
277 .push(", ")
278 .push_bind(station.reprocessing_hangar_flag)
279 .push(") ");
280 }
281 query
282 .push(" ON CONFLICT (station_id) DO UPDATE SET ")
283 .push("security = EXCLUDED.security, ")
284 .push("operation_type = EXCLUDED.operation_type, ")
285 .push("services = EXCLUDED.services, ")
286 .push("corporation_id = EXCLUDED.corporation_id, ")
287 .push("system_id = EXCLUDED.system_id, ")
288 .push("constellation_id = EXCLUDED.constellation_id, ")
289 .push("region_id = EXCLUDED.region_id, ")
290 .push("station_name = EXCLUDED.station_name, ")
291 .push("x = EXCLUDED.x, ")
292 .push("y = EXCLUDED.y, ")
293 .push("z = EXCLUDED.z, ")
294 .push("reprocessing_efficiency = EXCLUDED.reprocessing_efficiency, ")
295 .push("reprocessing_stations_take = EXCLUDED.reprocessing_stations_take, ")
296 .push("reprocessing_hangar_flag = EXCLUDED.reprocessing_hangar_flag");
297 query.build().execute(pool).await?;
298 }
299
300 Ok(())
301 }
302}