1use sqlx::{Postgres, QueryBuilder};
2
3use crate::{LibResult, Error};
4
5const TABLE_NAME: &str = "npc_factions";
6
7#[derive(Debug, serde::Serialize, serde::Deserialize, sqlx::FromRow)]
8pub struct FactionDb {
9 #[serde(rename = "factionId")]
10 pub faction_id: i32,
11 #[serde(rename = "factionName")]
12 pub faction_name: String,
13 pub description: String,
14 #[serde(rename = "raceId")]
15 pub race_id: i32,
16 #[serde(rename = "solarSystemId")]
17 pub system_id: i32,
18 #[serde(rename = "corporationId", skip_serializing_if = "Option::is_none")]
19 pub corporation_id: Option<i32>,
20 #[serde(rename = "sizeFactor")]
21 pub size_factor: f64,
22 #[serde(rename = "stationCount", skip_serializing_if = "Option::is_none")]
23 pub station_count: Option<i32>,
24 #[serde(rename = "stationSystemCount", skip_serializing_if = "Option::is_none")]
25 pub station_system_count: Option<i32>,
26 #[serde(
27 rename = "militiaCorporationId",
28 skip_serializing_if = "Option::is_none"
29 )]
30 pub militia_corporation_id: Option<i32>,
31 #[serde(rename = "iconId")]
32 pub icon_id: i32,
33}
34
35#[derive(Debug, serde::Serialize, serde::Deserialize)]
36pub struct FactionFilter {
37 #[serde(rename = "factionId")]
38 pub faction_id: Option<Vec<i32>>,
39 pub page: Option<i64>,
40 pub limit: Option<i64>,
41}
42
43impl FactionDb {
44 pub async fn get_by_id(faction_id: i32) -> LibResult<Self> {
45 let pool = crate::pool();
46 let faction = sqlx::query_as::<_, Self>(&format!(
47 r#"
48 SELECT * FROM {}
49 WHERE faction_id = $1
50 "#,
51 TABLE_NAME
52 ))
53 .bind(faction_id)
54 .fetch_one(pool)
55 .await?;
56
57 Ok(faction)
58 }
59
60 pub async fn count(filter: &FactionFilter) -> LibResult<i64> {
61 let pool = crate::pool();
62 let mut query = QueryBuilder::new(format!("SELECT COUNT(*) FROM {}", TABLE_NAME));
63 query = Self::build_query(query, filter);
64 let query = query.build_query_scalar();
65
66 match query.fetch_one(pool).await {
67 Ok(count) => Ok(count),
68 Err(err) => {
69 log::error!("Error counting items: {}", err);
70 return Err(Error::new(500, format!("Error counting items: {}", err)));
71 }
72 }
73 }
74
75 pub async fn get_multiple(filter: &FactionFilter) -> 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 faction_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 match query.fetch_all(pool).await {
89 Ok(factions) => Ok(factions),
90 Err(err) => {
91 log::error!("Error getting items: {}", err);
92 return Err(Error::new(500, format!("Error getting items: {}", err)));
93 }
94 }
95 }
96
97 pub async fn insert(&self) -> LibResult<()> {
98 let pool = crate::pool();
99 let query = format!(
100 r#"
101 INSERT INTO {} (
102 faction_id,
103 faction_name,
104 description,
105 race_id,
106 system_id,
107 corporation_id,
108 size_factor,
109 station_count,
110 station_system_count,
111 militia_corporation_id,
112 icon_id
113 ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
114 "#,
115 TABLE_NAME
116 );
117 sqlx::query(&query)
118 .bind(&self.faction_id)
119 .bind(&self.faction_name)
120 .bind(&self.description)
121 .bind(&self.race_id)
122 .bind(&self.system_id)
123 .bind(&self.corporation_id)
124 .bind(&self.size_factor)
125 .bind(&self.station_count)
126 .bind(&self.station_system_count)
127 .bind(&self.militia_corporation_id)
128 .bind(&self.icon_id)
129 .execute(pool)
130 .await?;
131 Ok(())
132 }
133
134 fn build_query<'a>(
135 mut query: QueryBuilder<'a, Postgres>,
136 filter: &'a FactionFilter,
137 ) -> QueryBuilder<'a, Postgres> {
138 let has_where = false;
139
140 if let Some(faction_ids) = &filter.faction_id {
142 if !has_where {
143 query.push(" WHERE");
144 } else {
145 query.push(" AND");
146 }
147 query.push(" (");
148 for (i, faction_id) in faction_ids.iter().enumerate() {
149 if i > 0 {
150 query.push(" OR");
151 }
152 query.push(" faction_id = ");
153 query.push_bind(format!("%{}%", faction_id));
154 }
155 query.push(" )");
156 }
157 query
158 }
159
160 pub async fn insert_multiple(factions: &Vec<Self>) -> LibResult<()> {
161 let pool = crate::pool();
162 let step = 1000;
163 for i in (0..factions.len()).step_by(step) {
164 let mut query = sqlx::QueryBuilder::new(format!(
165 r#"
166 INSERT INTO {} (faction_id, faction_name, description, race_id, system_id, corporation_id,
167 size_factor, station_count, station_system_count, militia_corporation_id, icon_id)
168 VALUES
169 "#,
170 TABLE_NAME
171 ));
172
173 for j in 0..step {
174 if i + j >= factions.len() {
175 break;
176 }
177 if j > 0 {
178 query.push(", ");
179 }
180 let faction = &factions[i + j];
181 query
182 .push(" (")
183 .push_bind(faction.faction_id)
184 .push(", ")
185 .push_bind(&faction.faction_name)
186 .push(", ")
187 .push_bind(&faction.description)
188 .push(", ")
189 .push_bind(faction.race_id)
190 .push(", ")
191 .push_bind(faction.system_id)
192 .push(", ")
193 .push_bind(faction.corporation_id)
194 .push(", ")
195 .push_bind(faction.size_factor)
196 .push(", ")
197 .push_bind(faction.station_count)
198 .push(", ")
199 .push_bind(faction.station_system_count)
200 .push(", ")
201 .push_bind(faction.militia_corporation_id)
202 .push(", ")
203 .push_bind(faction.icon_id)
204 .push(") ");
205 }
206 query
207 .push("ON CONFLICT (faction_id) DO UPDATE SET ")
208 .push("faction_name = EXCLUDED.faction_name, ")
209 .push("description = EXCLUDED.description, ")
210 .push("race_id = EXCLUDED.race_id, ")
211 .push("system_id = EXCLUDED.system_id, ")
212 .push("corporation_id = EXCLUDED.corporation_id, ")
213 .push("size_factor = EXCLUDED.size_factor, ")
214 .push("station_count = EXCLUDED.station_count, ")
215 .push("station_system_count = EXCLUDED.station_system_count, ")
216 .push("militia_corporation_id = EXCLUDED.militia_corporation_id, ")
217 .push("icon_id = EXCLUDED.icon_id");
218 query.build().execute(pool).await?;
219 }
220 Ok(())
221 }
222
223 pub async fn update(&self) -> LibResult<()> {
224 let pool = crate::pool();
225 let query = format!(
226 r#"
227 UPDATE {} SET
228 faction_name = $2,
229 description = $3,
230 race_id = $4,
231 system_id = $5,
232 corporation_id = $6,
233 size_factor = $7,
234 station_count = $8,
235 station_system_count = $9,
236 militia_corporation_id = $10,
237 icon_id = $11
238 WHERE faction_id = $1
239 "#,
240 TABLE_NAME
241 );
242 sqlx::query(&query)
243 .bind(&self.faction_id)
244 .bind(&self.faction_name)
245 .bind(&self.description)
246 .bind(&self.race_id)
247 .bind(&self.system_id)
248 .bind(&self.corporation_id)
249 .bind(&self.size_factor)
250 .bind(&self.station_count)
251 .bind(&self.station_system_count)
252 .bind(&self.militia_corporation_id)
253 .bind(&self.icon_id)
254 .execute(pool)
255 .await?;
256 Ok(())
257 }
258
259 pub async fn delete(faction_id: i32) -> LibResult<()> {
260 let pool = crate::pool();
261 let query = format!(
262 r#"
263 DELETE FROM {}
264 WHERE faction_id = $1
265 "#,
266 TABLE_NAME
267 );
268 sqlx::query(&query).bind(faction_id).execute(pool).await?;
269 Ok(())
270 }
271}