tdm_server_rust/repository/
manga_repo.rs

1//! 漫画数据访问层 (Manga Repository)
2//!
3//! 封装 `mangatb`、`mangacollect`、`glossary` 等表的查询。
4
5use crate::entity::{
6    manga::{
7        AddStationRequest, CollectedMembersVo, GlossaryRequest, GlossaryVo, Manga, MangaCardVo,
8        MangaCollect, MangaDetailVo, MangaSimpleVo,
9    },
10    member::Member,
11};
12use chrono::{DateTime, Utc};
13use sqlx::{MySql, Pool, Row};
14use crate::error::AppError;
15
16/// 漫画仓储
17pub struct MangaRepository {
18    /// 连接池
19    pool: Pool<MySql>,
20}
21
22impl MangaRepository {
23    /// 构造仓储
24    pub fn new(pool: Pool<MySql>) -> Self {
25        Self { pool }
26    }
27
28    /// 条件分页查询漫画卡片。
29    ///
30    /// 返回全量结果（内部用 `i32::MAX` 作为 limit），由 Service 层做内存分页。
31    ///
32    /// # 返回值
33    ///
34    /// 返回符合条件的全量 [`MangaCardVo`] 列表。
35    #[tracing::instrument(skip_all, level = "debug")]
36    pub async fn list(
37        &self,
38        manga_tran_name: Option<&str>,
39        manga_ori_name: Option<&str>,
40        category: Option<i16>,
41        manga_status: Option<i16>,
42        author_name: Option<&str>,
43        magazine_name: Option<&str>,
44    ) -> crate::error::ApiResult<Vec<MangaCardVo>> {
45        self.list_page(
46            manga_tran_name,
47            manga_ori_name,
48            category,
49            manga_status,
50            author_name,
51            magazine_name,
52            i32::MAX,
53            0,
54        )
55        .await
56    }
57
58    /// 统计符合条件的漫画总数。
59    ///
60    /// # 返回值
61    ///
62    /// 返回 `COUNT(DISTINCT m.Id)` 结果。
63    #[tracing::instrument(skip_all, level = "debug")]
64    pub async fn count_list(
65        &self,
66        manga_tran_name: Option<&str>,
67        manga_ori_name: Option<&str>,
68        category: Option<i16>,
69        manga_status: Option<i16>,
70        author_name: Option<&str>,
71        magazine_name: Option<&str>,
72    ) -> crate::error::ApiResult<i64> {
73        let mut sql = format!("SELECT COUNT(DISTINCT m.Id) AS cnt {MANGA_LIST_FROM} WHERE 1=1");
74        append_manga_list_filters(
75            &mut sql,
76            manga_tran_name,
77            manga_ori_name,
78            category,
79            manga_status,
80            author_name,
81            magazine_name,
82        );
83        let mut q = sqlx::query(&sql);
84        q = bind_manga_list_filters(
85            q,
86            manga_tran_name,
87            manga_ori_name,
88            category,
89            manga_status,
90            author_name,
91            magazine_name,
92        );
93        let row = q.fetch_one(&self.pool).await?;
94        Ok(row.get("cnt"))
95    }
96
97    /// 条件分页查询漫画卡片（SQL LIMIT/OFFSET）
98    #[tracing::instrument(skip_all, level = "debug")]
99    pub async fn list_page(
100        &self,
101        manga_tran_name: Option<&str>,
102        manga_ori_name: Option<&str>,
103        category: Option<i16>,
104        manga_status: Option<i16>,
105        author_name: Option<&str>,
106        magazine_name: Option<&str>,
107        limit: i32,
108        offset: i32,
109    ) -> crate::error::ApiResult<Vec<MangaCardVo>> {
110        let mut sql = format!("{MANGA_LIST_SELECT} {MANGA_LIST_FROM} WHERE 1=1");
111        append_manga_list_filters(
112            &mut sql,
113            manga_tran_name,
114            manga_ori_name,
115            category,
116            manga_status,
117            author_name,
118            magazine_name,
119        );
120        sql.push_str(" GROUP BY m.Id ORDER BY m.updateTime DESC LIMIT ? OFFSET ?");
121        let mut q = sqlx::query(&sql);
122        q = bind_manga_list_filters(
123            q,
124            manga_tran_name,
125            manga_ori_name,
126            category,
127            manga_status,
128            author_name,
129            magazine_name,
130        );
131        q = q.bind(limit).bind(offset);
132        let rows = q.fetch_all(&self.pool).await?;
133        Ok(rows.iter().map(row_to_manga_card).collect())
134    }
135
136    /// 按作者 ID 查询漫画卡片
137    #[tracing::instrument(skip_all, level = "debug")]
138    pub async fn list_by_author(&self, author_id: i32) -> crate::error::ApiResult<Vec<MangaCardVo>> {
139        let sql = format!(
140            "{MANGA_LIST_SELECT} {MANGA_LIST_FROM} \
141             WHERE a.Id = ? GROUP BY m.Id ORDER BY m.updateTime DESC"
142        );
143        let rows = sqlx::query(&sql).bind(author_id).fetch_all(&self.pool).await?;
144        Ok(rows.iter().map(row_to_manga_card).collect())
145    }
146
147    /// 按杂志 ID 查询漫画卡片
148    #[tracing::instrument(skip_all, level = "debug")]
149    pub async fn list_by_magazine(&self, magazine_id: i32) -> crate::error::ApiResult<Vec<MangaCardVo>> {
150        let sql = format!(
151            "{MANGA_LIST_SELECT} {MANGA_LIST_FROM} \
152             WHERE maga.Id = ? GROUP BY m.Id ORDER BY m.updateTime DESC"
153        );
154        let rows = sqlx::query(&sql).bind(magazine_id).fetch_all(&self.pool).await?;
155        Ok(rows.iter().map(row_to_manga_card).collect())
156    }
157
158    /// 按 ID 删除漫画主表
159    #[tracing::instrument(skip_all, level = "debug")]
160    pub async fn delete_by_id(&self, id: i32) -> crate::error::ApiResult<()> {
161        sqlx::query("DELETE FROM mangatb WHERE Id = ?")
162            .bind(id)
163            .execute(&self.pool)
164            .await?;
165        Ok(())
166    }
167
168    /// 删除漫画作者关联
169    #[tracing::instrument(skip_all, level = "debug")]
170    pub async fn delete_manga_author(&self, id: i32) -> crate::error::ApiResult<()> {
171        sqlx::query("DELETE FROM mangaauthor WHERE mangaId = ?")
172            .bind(id)
173            .execute(&self.pool)
174            .await?;
175        Ok(())
176    }
177
178    /// 删除漫画作者关联（作画作者 type=2）
179    #[tracing::instrument(skip_all, level = "debug")]
180    pub async fn delete_manga_author2(&self, id: i32) -> crate::error::ApiResult<()> {
181        sqlx::query("DELETE FROM mangaauthor WHERE mangaId = ? AND type = 2")
182            .bind(id)
183            .execute(&self.pool)
184            .await?;
185        Ok(())
186    }
187
188    /// 删除漫画话数
189    #[tracing::instrument(skip_all, level = "debug")]
190    pub async fn delete_manga_episode(&self, id: i32) -> crate::error::ApiResult<()> {
191        sqlx::query("DELETE FROM mangaepisodetb WHERE mangaId = ?")
192            .bind(id)
193            .execute(&self.pool)
194            .await?;
195        Ok(())
196    }
197
198    /// 删除漫画杂志关联
199    #[tracing::instrument(skip_all, level = "debug")]
200    pub async fn delete_manga_magazine(&self, id: i32) -> crate::error::ApiResult<()> {
201        sqlx::query("DELETE FROM mangamagazine WHERE mangaId = ?")
202            .bind(id)
203            .execute(&self.pool)
204            .await?;
205        Ok(())
206    }
207
208    /// 新增漫画
209    #[tracing::instrument(skip_all, level = "debug")]
210    pub async fn insert(&self, manga: &Manga) -> crate::error::ApiResult<i32> {
211        let r = sqlx::query(
212            "INSERT INTO mangatb(mangaTranName, mangaOriName, category, mangaStatus, image, link, introduction, setupTime, updateTime) \
213             VALUES (?, ?, ?, ?, ?, ?, ?, NOW(), NOW())",
214        )
215        .bind(&manga.manga_tran_name)
216        .bind(&manga.manga_ori_name)
217        .bind(manga.category)
218        .bind(manga.manga_status)
219        .bind(&manga.img_url)
220        .bind(&manga.link)
221        .bind(&manga.introduction)
222        .execute(&self.pool)
223        .await?;
224        Ok(r.last_insert_id() as i32)
225    }
226
227    /// 新增漫画作者（type=1）
228    #[tracing::instrument(skip_all, level = "debug")]
229    pub async fn insert_manga_author(&self, manga_id: i32, author_id: i32) -> crate::error::ApiResult<()> {
230        sqlx::query("INSERT INTO mangaauthor(mangaId, authorId) VALUES (?, ?)")
231            .bind(manga_id)
232            .bind(author_id)
233            .execute(&self.pool)
234            .await?;
235        Ok(())
236    }
237
238    /// 新增漫画作者（type=2）
239    #[tracing::instrument(skip_all, level = "debug")]
240    pub async fn insert_manga_author2(&self, manga_id: i32, author_id: i32) -> crate::error::ApiResult<()> {
241        sqlx::query("INSERT INTO mangaauthor(mangaId, authorId, type) VALUES (?, ?, 2)")
242            .bind(manga_id)
243            .bind(author_id)
244            .execute(&self.pool)
245            .await?;
246        Ok(())
247    }
248
249    /// 新增漫画杂志关联
250    #[tracing::instrument(skip_all, level = "debug")]
251    pub async fn insert_manga_magazine(&self, manga_id: i32, magazine_id: i32) -> crate::error::ApiResult<()> {
252        sqlx::query("INSERT INTO mangamagazine(mangaId, magazineId) VALUES (?, ?)")
253            .bind(manga_id)
254            .bind(magazine_id)
255            .execute(&self.pool)
256            .await?;
257        Ok(())
258    }
259
260    /// 按 ID 查询漫画详情
261    #[tracing::instrument(skip_all, level = "debug")]
262    pub async fn get_manga_detail_by_id(
263        &self,
264        id: i32,
265    ) -> crate::error::ApiResult<Option<MangaDetailVo>> {
266        let row = sqlx::query(GET_MANGA_DETAIL_BY_ID_SQL)
267            .bind(id)
268            .fetch_optional(&self.pool)
269            .await?;
270        Ok(row.map(|r| row_to_manga_detail(&r)))
271    }
272
273    /// 按 ID 查询漫画（对齐 Java getMangaById）
274    #[tracing::instrument(skip_all, level = "debug")]
275    pub async fn get_manga_response_by_id(
276        &self,
277        id: i32,
278    ) -> crate::error::ApiResult<Option<crate::entity::manga::MangaResponse>> {
279        let row = sqlx::query(GET_MANGA_BY_ID_SQL)
280            .bind(id)
281            .fetch_optional(&self.pool)
282            .await?;
283        Ok(row.map(|r| row_to_manga_response(&r)))
284    }
285
286    /// 批量按 ID 查询漫画
287    #[tracing::instrument(skip_all, level = "debug")]
288    pub async fn get_manga_responses_by_ids(
289        &self,
290        ids: &[i32],
291    ) -> crate::error::ApiResult<std::collections::HashMap<i32, crate::entity::manga::MangaResponse>>
292    {
293        use std::collections::HashMap;
294        if ids.is_empty() {
295            return Ok(HashMap::new());
296        }
297        let placeholders = ids.iter().map(|_| "?").collect::<Vec<_>>().join(",");
298        let sql = GET_MANGA_BY_ID_SQL.replace(
299            "WHERE m.Id = ?",
300            &format!("WHERE m.Id IN ({placeholders})"),
301        );
302        let mut q = sqlx::query(&sql);
303        for id in ids {
304            q = q.bind(id);
305        }
306        let rows = q.fetch_all(&self.pool).await?;
307        Ok(rows
308            .into_iter()
309            .filter_map(|r| {
310                let manga = row_to_manga_response(&r);
311                manga.id.map(|id| (id, manga))
312            })
313            .collect())
314    }
315
316    /// 按 ID 查询漫画卡片详情
317    #[tracing::instrument(skip_all, level = "debug")]
318    pub async fn get_manga_card_by_id(&self, id: i32) -> crate::error::ApiResult<Option<MangaCardVo>> {
319        let row = sqlx::query(GET_MANGA_CARD_BY_ID_SQL)
320            .bind(id)
321            .fetch_optional(&self.pool)
322            .await?;
323        Ok(row.map(|r| row_to_manga_card(&r)))
324    }
325
326    /// 按 ID 查询漫画
327    #[tracing::instrument(skip_all, level = "debug")]
328    pub async fn get_manga_by_id(&self, id: i32) -> crate::error::ApiResult<Option<Manga>> {
329        let row = sqlx::query(
330            "SELECT Id, mangaTranName, mangaOriName, category, mangaStatus, image AS imgUrl, updateTime \
331             FROM mangatb WHERE Id = ?",
332        )
333        .bind(id)
334        .fetch_optional(&self.pool)
335        .await?;
336        Ok(row.map(|r| row_to_manga(&r)))
337    }
338
339    /// 更新漫画
340    #[tracing::instrument(skip_all, level = "debug")]
341    pub async fn update_manga(&self, manga: &Manga) -> crate::error::ApiResult<()> {
342        sqlx::query(
343            "UPDATE mangatb SET mangaTranName = COALESCE(?, mangaTranName), mangaOriName = COALESCE(?, mangaOriName), \
344             category = COALESCE(?, category), mangaStatus = COALESCE(?, mangaStatus), \
345             image = COALESCE(?, image), link = COALESCE(?, link), introduction = COALESCE(?, introduction), \
346             updateTime = NOW() WHERE Id = ?",
347        )
348        .bind(&manga.manga_tran_name)
349        .bind(&manga.manga_ori_name)
350        .bind(manga.category)
351        .bind(manga.manga_status)
352        .bind(&manga.img_url)
353        .bind(&manga.link)
354        .bind(&manga.introduction)
355        .bind(manga.id)
356        .execute(&self.pool)
357        .await?;
358        Ok(())
359    }
360
361    /// 更新漫画作者（type=1）
362    #[tracing::instrument(skip_all, level = "debug")]
363    pub async fn update_manga_author(&self, manga_id: i32, author_id: i32) -> crate::error::ApiResult<()> {
364        sqlx::query("UPDATE mangaauthor SET authorId = ? WHERE mangaId = ? AND type = 1")
365            .bind(author_id)
366            .bind(manga_id)
367            .execute(&self.pool)
368            .await?;
369        Ok(())
370    }
371
372    /// 更新漫画作者（type=2）
373    #[tracing::instrument(skip_all, level = "debug")]
374    pub async fn update_manga_author2(&self, manga_id: i32, author_id: i32) -> crate::error::ApiResult<()> {
375        sqlx::query("UPDATE mangaauthor SET authorId = ? WHERE mangaId = ? AND type = 2")
376            .bind(author_id)
377            .bind(manga_id)
378            .execute(&self.pool)
379            .await?;
380        Ok(())
381    }
382
383    /// 更新漫画杂志关联
384    #[tracing::instrument(skip_all, level = "debug")]
385    pub async fn update_manga_magazine(&self, manga_id: i32, magazine_id: i32) -> crate::error::ApiResult<()> {
386        sqlx::query("UPDATE mangamagazine SET magazineId = ? WHERE mangaId = ?")
387            .bind(magazine_id)
388            .bind(manga_id)
389            .execute(&self.pool)
390            .await?;
391        Ok(())
392    }
393
394    /// 检测作画作者是否存在
395    #[tracing::instrument(skip_all, level = "debug")]
396    pub async fn test_author2(&self, manga_id: i32) -> crate::error::ApiResult<bool> {
397        let row = sqlx::query("SELECT authorId FROM mangaauthor WHERE mangaId = ? AND type = 2 LIMIT 1")
398            .bind(manga_id)
399            .fetch_optional(&self.pool)
400            .await?;
401        Ok(row.is_some())
402    }
403
404    /// 检测原名是否重复
405    #[tracing::instrument(skip_all, level = "debug")]
406    pub async fn test_ori_name(&self, manga_ori_name: &str) -> crate::error::ApiResult<bool> {
407        self.exists_ori_name_for_other(manga_ori_name, None).await
408    }
409
410    /// 检测译名是否与其他漫画重复
411    #[tracing::instrument(skip_all, level = "debug")]
412    pub async fn exists_tran_name_for_other(
413        &self,
414        manga_tran_name: &str,
415        exclude_id: Option<i32>,
416    ) -> crate::error::ApiResult<bool> {
417        let row = match exclude_id {
418            Some(id) => {
419                sqlx::query("SELECT Id FROM mangatb WHERE mangaTranName = ? AND Id <> ?")
420                    .bind(manga_tran_name)
421                    .bind(id)
422                    .fetch_optional(&self.pool)
423                    .await?
424            }
425            None => {
426                sqlx::query("SELECT Id FROM mangatb WHERE mangaTranName = ?")
427                    .bind(manga_tran_name)
428                    .fetch_optional(&self.pool)
429                    .await?
430            }
431        };
432        Ok(row.is_some())
433    }
434
435    /// 检测原名是否与其他漫画重复
436    #[tracing::instrument(skip_all, level = "debug")]
437    pub async fn exists_ori_name_for_other(
438        &self,
439        manga_ori_name: &str,
440        exclude_id: Option<i32>,
441    ) -> crate::error::ApiResult<bool> {
442        let row = match exclude_id {
443            Some(id) => {
444                sqlx::query("SELECT Id FROM mangatb WHERE mangaOriName = ? AND Id <> ?")
445                    .bind(manga_ori_name)
446                    .bind(id)
447                    .fetch_optional(&self.pool)
448                    .await?
449            }
450            None => {
451                sqlx::query("SELECT Id FROM mangatb WHERE mangaOriName = ?")
452                    .bind(manga_ori_name)
453                    .fetch_optional(&self.pool)
454                    .await?
455            }
456        };
457        Ok(row.is_some())
458    }
459
460    /// 检测漫画是否已有杂志关联
461    #[tracing::instrument(skip_all, level = "debug")]
462    pub async fn test_manga_magazine(&self, manga_id: i32) -> crate::error::ApiResult<bool> {
463        let row = sqlx::query("SELECT magazineId FROM mangamagazine WHERE mangaId = ? LIMIT 1")
464            .bind(manga_id)
465            .fetch_optional(&self.pool)
466            .await?;
467        Ok(row.is_some())
468    }
469
470    /// 检测漫画是否已有原作作者关联（type=1）
471    #[tracing::instrument(skip_all, level = "debug")]
472    pub async fn test_manga_author1(&self, manga_id: i32) -> crate::error::ApiResult<bool> {
473        let row = sqlx::query(
474            "SELECT authorId FROM mangaauthor WHERE mangaId = ? AND type = 1 LIMIT 1",
475        )
476        .bind(manga_id)
477        .fetch_optional(&self.pool)
478        .await?;
479        Ok(row.is_some())
480    }
481
482    /// 查询全部译名
483    #[tracing::instrument(skip_all, level = "debug")]
484    pub async fn get_manga_tran_names(&self) -> crate::error::ApiResult<Vec<MangaSimpleVo>> {
485        let rows = sqlx::query("SELECT Id, mangaTranName FROM mangatb ORDER BY Id")
486            .fetch_all(&self.pool)
487            .await?;
488        Ok(rows
489            .into_iter()
490            .map(|r| MangaSimpleVo {
491                id: r.get("Id"),
492                manga_tran_name: r.try_get("mangaTranName").ok(),
493                ..Default::default()
494            })
495            .collect())
496    }
497
498    /// 获取最大漫画 ID
499    #[tracing::instrument(skip_all, level = "debug")]
500    pub async fn get_max_manga_id(&self) -> crate::error::ApiResult<Option<i32>> {
501        let row = sqlx::query("SELECT MAX(Id) AS maxId FROM mangatb")
502            .fetch_one(&self.pool)
503            .await?;
504        Ok(row.try_get("maxId").ok())
505    }
506
507    /// 查询收藏详情
508    #[tracing::instrument(skip_all, level = "debug")]
509    pub async fn get_collect_detail(
510        &self,
511        manga_id: i32,
512        member_id: i32,
513    ) -> crate::error::ApiResult<Option<MangaCollect>> {
514        let row = sqlx::query("SELECT Id, mangaId, memberId FROM mangacollect WHERE mangaId = ? AND memberId = ?")
515            .bind(manga_id)
516            .bind(member_id)
517            .fetch_optional(&self.pool)
518            .await?;
519        Ok(row.map(|r| MangaCollect {
520            id: r.try_get("Id").ok(),
521            manga_id: r.get("mangaId"),
522            member_id: r.get("memberId"),
523        }))
524    }
525
526    /// 查询组员收藏列表
527    #[tracing::instrument(skip_all, level = "debug")]
528    pub async fn get_collect_list(&self, member_id: i32) -> crate::error::ApiResult<Vec<MangaCardVo>> {
529        let rows = sqlx::query(GET_COLLECT_LIST_SQL)
530            .bind(member_id)
531            .fetch_all(&self.pool)
532            .await?;
533        Ok(rows.into_iter().map(|r| row_to_manga_card(&r)).collect())
534    }
535
536    /// 查询收藏该漫画的组员
537    #[tracing::instrument(skip_all, level = "debug")]
538    pub async fn get_collected_members(&self, manga_id: i32) -> crate::error::ApiResult<Vec<CollectedMembersVo>> {
539        let rows = sqlx::query(
540            "SELECT DISTINCT m.Id, m.username, m.intern, m.registrationTime, m.lastSubmitTime, \
541             (SELECT COUNT(*) FROM mangastationed ms WHERE ms.memberId = m.Id) AS stationCount \
542             FROM membertb m \
543             JOIN memberpost mp ON m.Id = mp.memberId \
544             JOIN post p ON mp.postId = p.post \
545             LEFT JOIN mangacollect mc ON mc.memberId = m.Id \
546             WHERE mc.mangaId = ?",
547        )
548        .bind(manga_id)
549        .fetch_all(&self.pool)
550        .await?;
551        Ok(rows.iter().map(row_to_collected_member).collect())
552    }
553
554    /// 删除收藏
555    #[tracing::instrument(skip_all, level = "debug")]
556    pub async fn del_collect(&self, manga_id: i32, member_id: i32) -> crate::error::ApiResult<()> {
557        sqlx::query("DELETE FROM mangacollect WHERE mangaId = ? AND memberId = ?")
558            .bind(manga_id)
559            .bind(member_id)
560            .execute(&self.pool)
561            .await?;
562        Ok(())
563    }
564
565    /// 新增收藏
566    #[tracing::instrument(skip_all, level = "debug")]
567    pub async fn add_collect(&self, manga_id: i32, member_id: i32) -> crate::error::ApiResult<()> {
568        sqlx::query("INSERT INTO mangacollect(memberId, mangaId) VALUES (?, ?)")
569            .bind(member_id)
570            .bind(manga_id)
571            .execute(&self.pool)
572            .await?;
573        Ok(())
574    }
575
576    /// 查询术语列表
577    #[tracing::instrument(skip_all, level = "debug")]
578    pub async fn list_glossary(
579        &self,
580        manga_id: i32,
581        type_: Option<i16>,
582    ) -> crate::error::ApiResult<Vec<GlossaryVo>> {
583        let mut sql = String::from(
584            "SELECT g.*, m.username AS updateMemberName FROM glossary g \
585             LEFT JOIN membertb m ON g.updateBy = m.Id WHERE g.mangaId = ?",
586        );
587        if type_.is_some() {
588            sql.push_str(" AND g.type = ?");
589        }
590        let mut q = sqlx::query(&sql).bind(manga_id);
591        if let Some(t) = type_ {
592            q = q.bind(t);
593        }
594        let rows = q.fetch_all(&self.pool).await?;
595        Ok(rows.into_iter().map(|r| row_to_glossary(&r)).collect())
596    }
597
598    /// 按 ID 删除术语
599    #[tracing::instrument(skip_all, level = "debug")]
600    pub async fn delete_glossary_by_id(&self, id: i32) -> crate::error::ApiResult<()> {
601        sqlx::query("DELETE FROM glossary WHERE Id = ?")
602            .bind(id)
603            .execute(&self.pool)
604            .await?;
605        Ok(())
606    }
607
608    /// 新增术语
609    #[tracing::instrument(skip_all, level = "debug")]
610    pub async fn insert_glossary(&self, req: &GlossaryRequest, update_by: i32) -> crate::error::ApiResult<i32> {
611        let r = sqlx::query(
612            "INSERT INTO glossary(type, image, title, content, mangaId, first, updateTime, updateBy) \
613             VALUES (?, ?, ?, ?, ?, ?, NOW(), ?)",
614        )
615        .bind(req.r#type)
616        .bind(&req.image)
617        .bind(&req.title)
618        .bind(&req.content)
619        .bind(req.manga_id)
620        .bind(&req.first)
621        .bind(update_by)
622        .execute(&self.pool)
623        .await?;
624        Ok(r.last_insert_id() as i32)
625    }
626
627    /// 按 ID 查询术语
628    #[tracing::instrument(skip_all, level = "debug")]
629    pub async fn get_glossary_by_id(&self, id: i32) -> crate::error::ApiResult<Option<GlossaryVo>> {
630        let row = sqlx::query(
631            "SELECT g.*, m.username AS updateMemberName FROM glossary g \
632             LEFT JOIN membertb m ON g.updateBy = m.Id WHERE g.Id = ?",
633        )
634        .bind(id)
635        .fetch_optional(&self.pool)
636        .await?;
637        Ok(row.map(|r| row_to_glossary(&r)))
638    }
639
640    /// 更新术语
641    #[tracing::instrument(skip_all, level = "debug")]
642    pub async fn update_glossary(&self, req: &GlossaryRequest, update_by: i32) -> crate::error::ApiResult<()> {
643        sqlx::query(
644            "UPDATE glossary SET type = COALESCE(?, type), title = COALESCE(?, title), \
645             content = COALESCE(?, content), image = COALESCE(?, image), first = COALESCE(?, first), \
646             updateTime = NOW(), updateBy = ? WHERE Id = ?",
647        )
648        .bind(req.r#type)
649        .bind(&req.title)
650        .bind(&req.content)
651        .bind(&req.image)
652        .bind(&req.first)
653        .bind(update_by)
654        .bind(req.id)
655        .execute(&self.pool)
656        .await?;
657        Ok(())
658    }
659
660    /// 漫画 RSS 列表（对齐 Java MangaMapper.getMangaRss）
661    #[tracing::instrument(skip_all, level = "debug")]
662    pub async fn get_manga_rss(&self) -> crate::error::ApiResult<Vec<crate::entity::rss::RssMangaRow>> {
663        let rows = sqlx::query(
664            "SELECT m.Id, m.mangaTranName, m.mangaOriName, m.category, m.mangaStatus, m.image, \
665             m.setupTime, \
666             MAX(CASE WHEN ma.type = 1 THEN a.Id END) AS authorId, \
667             MAX(CASE WHEN ma.type = 1 THEN a.authorName END) AS authorName, \
668             MAX(CASE WHEN ma.type = 2 THEN a.Id END) AS authorId2, \
669             MAX(CASE WHEN ma.type = 2 THEN a.authorName END) AS authorName2 \
670             FROM mangatb m \
671             LEFT JOIN mangaauthor ma ON m.Id = ma.mangaId \
672             LEFT JOIN authortb a ON ma.authorId = a.Id \
673             GROUP BY m.Id, m.setupTime \
674             ORDER BY m.setupTime DESC LIMIT 2",
675        )
676        .fetch_all(&self.pool)
677        .await?;
678        Ok(rows.iter().map(row_to_rss_manga).collect())
679    }
680
681    /// 最近更新漫画 RSS（按 updateTime 降序）
682    #[tracing::instrument(skip_all, level = "debug")]
683    pub async fn get_manga_updated_rss(
684        &self,
685    ) -> crate::error::ApiResult<Vec<crate::entity::rss::RssMangaRow>> {
686        let rows = sqlx::query(
687            "SELECT m.Id, m.mangaTranName, m.mangaOriName, m.category, m.mangaStatus, m.image, \
688             m.setupTime, m.updateTime, \
689             MAX(CASE WHEN ma.type = 1 THEN a.Id END) AS authorId, \
690             MAX(CASE WHEN ma.type = 1 THEN a.authorName END) AS authorName, \
691             MAX(CASE WHEN ma.type = 2 THEN a.Id END) AS authorId2, \
692             MAX(CASE WHEN ma.type = 2 THEN a.authorName END) AS authorName2 \
693             FROM mangatb m \
694             LEFT JOIN mangaauthor ma ON m.Id = ma.mangaId \
695             LEFT JOIN authortb a ON ma.authorId = a.Id \
696             GROUP BY m.Id, m.setupTime, m.updateTime \
697             ORDER BY m.updateTime DESC LIMIT 3",
698        )
699        .fetch_all(&self.pool)
700        .await?;
701        Ok(rows.iter().map(row_to_rss_manga).collect())
702    }
703
704    /// 话数 RSS 列表（对齐 Java MangaMapper.getEpisodeRss）
705    #[tracing::instrument(skip_all, level = "debug")]
706    pub async fn get_episode_rss(&self) -> crate::error::ApiResult<Vec<crate::entity::rss::EpisodeRssRow>> {
707        let rows = sqlx::query(
708            "SELECT me.Id, me.mangaId, me.mangaEpisode, me.mangaEpisodeName, me.setupTime, me.publishLink, \
709             m.mangaTranName AS mangaName, m.mangaOriName, m.category, m.mangaStatus, \
710             m.setupTime AS mangaSetupTime, m.image, \
711             mem.username AS providerName, mem2.username AS translatorName, \
712             mem2.intern AS intern, mem2.email AS email \
713             FROM mangaepisodetb me \
714             LEFT JOIN mangatb m ON m.Id = me.mangaId \
715             LEFT JOIN membertb mem ON mem.Id = me.providerId \
716             LEFT JOIN membertb mem2 ON mem2.Id = me.translatorId \
717             ORDER BY me.Id DESC LIMIT 5",
718        )
719        .fetch_all(&self.pool)
720        .await?;
721        Ok(rows.iter().map(row_to_episode_rss).collect())
722    }
723
724    /// 交稿提醒 RSS（对齐 Java MangaMapper.getWorkReminderRss）
725    #[tracing::instrument(skip_all, level = "debug")]
726    pub async fn get_work_reminder_rss(
727        &self,
728        post_name: &str,
729    ) -> crate::error::ApiResult<Vec<crate::entity::rss::WorkReminderRssRow>> {
730        let rows = sqlx::query(
731            "SELECT subquery.*, mem.username, mem.intern, mem.email \
732             FROM ( \
733               SELECT w.episodeId, w.memberId, w.myName, \
734                 m.mangaTranName AS mangaName, m.mangaOriName, m.Id AS mangaId, \
735                 m.category, m.mangaStatus, m.image, m.setupTime AS mangaSetupTime, \
736                 me.updateTime, me.setupTime, me.mangaEpisode, me.mangaEpisodeName, me.publishLink, \
737                 mem.username AS usernameNow, mem.intern AS internNow, mem.email AS emailNow, \
738                 CASE \
739                   WHEN w.myName = 'translator' THEN me.proofreaderId \
740                   WHEN w.myName = 'proofreader' THEN me.lettererId \
741                   WHEN w.myName = 'letterer' THEN me.reviewerId \
742                   WHEN w.myName = 'timer' THEN me.timerId \
743                 END AS myId \
744               FROM workreminder w \
745               LEFT JOIN mangaepisodetb me ON w.episodeId = me.Id \
746               LEFT JOIN mangatb m ON m.Id = me.mangaId \
747               LEFT JOIN membertb mem ON mem.Id = w.memberId \
748               WHERE w.myName = ? \
749               ORDER BY w.id DESC LIMIT 5 \
750             ) AS subquery \
751             LEFT JOIN membertb mem ON mem.Id = subquery.myId",
752        )
753        .bind(post_name)
754        .fetch_all(&self.pool)
755        .await?;
756        Ok(rows.iter().map(row_to_work_reminder_rss).collect())
757    }
758
759    /// 查询漫画常驻组员
760    #[tracing::instrument(skip_all, level = "debug")]
761    pub async fn get_stationed_members(&self, manga_id: i32) -> crate::error::ApiResult<Vec<Member>> {
762        if manga_id == 0 {
763            return self.get_pending_stationed_members().await;
764        }
765        let rows = sqlx::query(
766            "SELECT ms.Id AS stationId, ms.mangaId, ms.post, ms.status, m.Id, m.username, \
767             m.email, m.lastSubmitTime, m.registrationTime, m.intern \
768             FROM mangastationed ms LEFT JOIN membertb m ON m.Id = ms.memberId \
769             WHERE ms.mangaId = ?",
770        )
771        .bind(manga_id)
772        .fetch_all(&self.pool)
773        .await?;
774        Ok(rows.iter().map(row_to_station_member).collect())
775    }
776
777    /// 查询待审批常驻组员（mangaId=0）
778    #[tracing::instrument(skip_all, level = "debug")]
779    pub async fn get_pending_stationed_members(&self) -> crate::error::ApiResult<Vec<Member>> {
780        let rows = sqlx::query(
781            "SELECT ms.Id AS stationId, ms.mangaId, ms.post, ms.status, m.Id, m.username, \
782             m.email, m.lastSubmitTime, m.registrationTime, m.intern, m2.mangaTranName AS mangaName \
783             FROM mangastationed ms \
784             LEFT JOIN membertb m ON m.Id = ms.memberId \
785             LEFT JOIN mangatb m2 ON m2.Id = ms.mangaId \
786             WHERE ms.status = 0 OR ms.status = 2",
787        )
788        .fetch_all(&self.pool)
789        .await?;
790        Ok(rows.iter().map(row_to_station_member).collect())
791    }
792
793    /// 删除常驻记录
794    #[tracing::instrument(skip_all, level = "debug")]
795    pub async fn del_station(&self, station_id: i32) -> crate::error::ApiResult<()> {
796        sqlx::query("DELETE FROM mangastationed WHERE Id = ?")
797            .bind(station_id)
798            .execute(&self.pool)
799            .await?;
800        Ok(())
801    }
802
803    /// 按 ID 查询常驻记录（漫画、组员、岗位）
804    #[tracing::instrument(skip_all, level = "debug")]
805    pub async fn get_station_by_id(
806        &self,
807        station_id: i32,
808    ) -> crate::error::ApiResult<Option<StationRecord>> {
809        Self::get_station_by_id_with(&self.pool, station_id).await
810    }
811
812    /// 填充漫画内该岗位为空且未交稿的单话（指派组员）
813    #[tracing::instrument(skip_all, level = "debug")]
814    pub async fn fill_empty_episodes(&self, req: &AddStationRequest) -> crate::error::ApiResult<()> {
815        Self::fill_empty_episodes_with(&self.pool, req).await
816    }
817
818    /// 填充后写入岗位指派时间（图源岗位跳过）
819    #[tracing::instrument(skip_all, level = "debug")]
820    pub async fn fill_episode_detail(&self, req: &AddStationRequest) -> crate::error::ApiResult<()> {
821        Self::fill_episode_detail_with(&self.pool, req).await
822    }
823
824    /// 清空组员在该漫画、该岗位上的未交稿单话分配
825    #[tracing::instrument(skip_all, level = "debug")]
826    pub async fn clear_member_unsubmitted_episodes(
827        &self,
828        manga_id: i32,
829        member_id: i32,
830        post: i32,
831    ) -> crate::error::ApiResult<()> {
832        Self::clear_member_unsubmitted_episodes_with(&self.pool, manga_id, member_id, post).await
833    }
834
835    /// 组员申请常驻
836    #[tracing::instrument(skip_all, level = "debug")]
837    pub async fn add_station(&self, manga_id: i32, member_id: i32, post: i32) -> crate::error::ApiResult<()> {
838        sqlx::query("INSERT INTO mangastationed(mangaId, memberId, post, status) VALUES (?, ?, ?, 0)")
839            .bind(manga_id)
840            .bind(member_id)
841            .bind(post)
842            .execute(&self.pool)
843            .await?;
844        Ok(())
845    }
846
847    /// 管理员添加常驻
848    #[tracing::instrument(skip_all, level = "debug")]
849    pub async fn add_station_by_admin(&self, req: &AddStationRequest) -> crate::error::ApiResult<()> {
850        Self::add_station_by_admin_with(&self.pool, req).await
851    }
852
853    /// 事务内：按 ID 查询常驻记录
854    pub(crate) async fn get_station_by_id_with<'e, E>(
855        executor: E,
856        station_id: i32,
857    ) -> crate::error::ApiResult<Option<StationRecord>>
858    where
859        E: sqlx::Executor<'e, Database = MySql>,
860    {
861        let row = sqlx::query(
862            "SELECT mangaId, memberId, post FROM mangastationed WHERE Id = ?",
863        )
864        .bind(station_id)
865        .fetch_optional(executor)
866        .await?;
867        Ok(row.map(|r| StationRecord {
868            manga_id: r.get("mangaId"),
869            member_id: r.get("memberId"),
870            post: r.get("post"),
871        }))
872    }
873
874    /// 事务内：插入已通过常驻记录
875    pub(crate) async fn add_station_by_admin_with<'e, E>(
876        executor: E,
877        req: &AddStationRequest,
878    ) -> crate::error::ApiResult<()>
879    where
880        E: sqlx::Executor<'e, Database = MySql>,
881    {
882        sqlx::query(
883            "INSERT INTO mangastationed(mangaId, memberId, post, status) VALUES (?, ?, ?, 1)",
884        )
885        .bind(req.manga_id)
886        .bind(req.member_id)
887        .bind(req.post)
888        .execute(executor)
889        .await?;
890        Ok(())
891    }
892
893    /// 事务内：填充空且未交稿的单话岗位
894    pub(crate) async fn fill_empty_episodes_with<'e, E>(
895        executor: E,
896        req: &AddStationRequest,
897    ) -> crate::error::ApiResult<()>
898    where
899        E: sqlx::Executor<'e, Database = MySql>,
900    {
901        let fields = post_episode_fields(req.post)?;
902        let sql = if let Some(update_col) = fields.update_time_col {
903            format!(
904                "UPDATE mangaepisodetb me \
905                 JOIN mangaepisodedetail med ON med.episodeId = me.Id \
906                 SET me.{member_col} = ? \
907                 WHERE me.mangaId = ? AND me.{member_col} IS NULL AND med.{update_col} IS NULL",
908                member_col = fields.member_col,
909                update_col = update_col,
910            )
911        } else {
912            format!(
913                "UPDATE mangaepisodetb SET {member_col} = ? \
914                 WHERE mangaId = ? AND {member_col} IS NULL",
915                member_col = fields.member_col,
916            )
917        };
918        sqlx::query(&sql)
919            .bind(req.member_id)
920            .bind(req.manga_id)
921            .execute(executor)
922            .await?;
923        Ok(())
924    }
925
926    /// 事务内：为刚填充的单话写入岗位指派时间
927    pub(crate) async fn fill_episode_detail_with<'e, E>(
928        executor: E,
929        req: &AddStationRequest,
930    ) -> crate::error::ApiResult<()>
931    where
932        E: sqlx::Executor<'e, Database = MySql>,
933    {
934        let fields = post_episode_fields(req.post)?;
935        let Some(setup_col) = fields.setup_time_col else {
936            return Ok(());
937        };
938        let sql = format!(
939            "UPDATE mangaepisodedetail med \
940             JOIN mangaepisodetb me ON med.episodeId = me.Id \
941             SET med.{setup_col} = NOW() \
942             WHERE me.mangaId = ? AND me.{member_col} = ? AND med.{setup_col} IS NULL",
943            setup_col = setup_col,
944            member_col = fields.member_col,
945        );
946        sqlx::query(&sql)
947            .bind(req.manga_id)
948            .bind(req.member_id)
949            .execute(executor)
950            .await?;
951        Ok(())
952    }
953
954    /// 事务内：清空组员未交稿单话上的岗位分配
955    pub(crate) async fn clear_member_unsubmitted_episodes_with<'e, E>(
956        executor: E,
957        manga_id: i32,
958        member_id: i32,
959        post: i32,
960    ) -> crate::error::ApiResult<()>
961    where
962        E: sqlx::Executor<'e, Database = MySql>,
963    {
964        let fields = post_episode_fields(post)?;
965        let sql = if let Some(update_col) = fields.update_time_col {
966            format!(
967                "UPDATE mangaepisodetb me \
968                 JOIN mangaepisodedetail med ON med.episodeId = me.Id \
969                 SET me.{member_col} = NULL \
970                 WHERE me.mangaId = ? AND me.{member_col} = ? AND med.{update_col} IS NULL",
971                member_col = fields.member_col,
972                update_col = update_col,
973            )
974        } else {
975            format!(
976                "UPDATE mangaepisodetb SET {member_col} = NULL \
977                 WHERE mangaId = ? AND {member_col} = ?",
978                member_col = fields.member_col,
979            )
980        };
981        sqlx::query(&sql)
982            .bind(manga_id)
983            .bind(member_id)
984            .execute(executor)
985            .await?;
986        Ok(())
987    }
988
989    /// 事务内：删除常驻记录
990    pub(crate) async fn del_station_with<'e, E>(
991        executor: E,
992        station_id: i32,
993    ) -> crate::error::ApiResult<()>
994    where
995        E: sqlx::Executor<'e, Database = MySql>,
996    {
997        sqlx::query("DELETE FROM mangastationed WHERE Id = ?")
998            .bind(station_id)
999            .execute(executor)
1000            .await?;
1001        Ok(())
1002    }
1003
1004    /// 更新常驻状态
1005    #[tracing::instrument(skip_all, level = "debug")]
1006    pub async fn update_station_status(&self, station_id: i32, status: i16) -> crate::error::ApiResult<()> {
1007        sqlx::query("UPDATE mangastationed SET status = ? WHERE Id = ?")
1008            .bind(status)
1009            .bind(station_id)
1010            .execute(&self.pool)
1011            .await?;
1012        Ok(())
1013    }
1014}
1015
1016/// 漫画列表 SELECT（对齐 Java MangaMapper.list）
1017const MANGA_LIST_SELECT: &str = r#"
1018SELECT DISTINCT m.*,
1019    MAX(CASE WHEN ma.type = 1 THEN a.Id END) AS authorId,
1020    MAX(CASE WHEN ma.type = 1 THEN a.authorName END) AS authorName,
1021    MAX(CASE WHEN ma.type = 2 THEN a.Id END) AS authorId2,
1022    MAX(CASE WHEN ma.type = 2 THEN a.authorName END) AS authorName2,
1023    MAX(maga.magazineName) AS magazineName,
1024    MAX(med.translatorSetupTime) AS translatorSetupTime,
1025    MAX(med.translatorUpdateTime) AS translatorUpdateTime,
1026    MAX(med.proofreaderSetupTime) AS proofreaderSetupTime,
1027    MAX(med.proofreaderUpdateTime) AS proofreaderUpdateTime,
1028    MAX(med.lettererSetupTime) AS lettererSetupTime,
1029    MAX(med.lettererUpdateTime) AS lettererUpdateTime,
1030    MAX(med.reviewerSetupTime) AS reviewerSetupTime,
1031    MAX(med.reviewerUpdateTime) AS reviewerUpdateTime,
1032    MAX(med.timerSetupTime) AS timerSetupTime,
1033    MAX(med.timerUpdateTime) AS timerUpdateTime
1034"#;
1035
1036/// 漫画列表 JOIN 片段
1037const MANGA_LIST_FROM: &str = r#"
1038FROM mangatb m
1039LEFT JOIN mangaauthor ma ON m.Id = ma.mangaId
1040LEFT JOIN authortb a ON ma.authorId = a.Id
1041LEFT JOIN mangamagazine mmaga ON m.Id = mmaga.mangaId
1042LEFT JOIN magazine maga ON mmaga.magazineId = maga.Id
1043LEFT JOIN (
1044    SELECT me2.mangaId, MAX(me2.Id) AS maxEpisodeId
1045    FROM mangaepisodetb me2
1046    GROUP BY me2.mangaId
1047) maxEp ON maxEp.mangaId = m.Id
1048LEFT JOIN mangaepisodedetail med ON med.episodeId = maxEp.maxEpisodeId
1049"#;
1050
1051/// 追加漫画列表筛选条件
1052fn append_manga_list_filters(
1053    sql: &mut String,
1054    manga_tran_name: Option<&str>,
1055    manga_ori_name: Option<&str>,
1056    category: Option<i16>,
1057    manga_status: Option<i16>,
1058    author_name: Option<&str>,
1059    magazine_name: Option<&str>,
1060) {
1061    if manga_tran_name.is_some() {
1062        sql.push_str(" AND m.mangaTranName LIKE CONCAT('%', ?, '%')");
1063    }
1064    if manga_ori_name.is_some() {
1065        sql.push_str(" AND m.mangaOriName = ?");
1066    }
1067    if category.is_some() {
1068        sql.push_str(" AND m.category = ?");
1069    }
1070    if manga_status.is_some() {
1071        sql.push_str(" AND m.mangaStatus = ?");
1072    }
1073    if author_name.is_some() {
1074        sql.push_str(" AND a.authorName = ?");
1075    }
1076    if magazine_name.is_some() {
1077        sql.push_str(" AND maga.magazineName = ?");
1078    }
1079}
1080
1081/// 绑定漫画列表筛选参数
1082fn bind_manga_list_filters<'q>(
1083    mut q: sqlx::query::Query<'q, MySql, sqlx::mysql::MySqlArguments>,
1084    manga_tran_name: Option<&'q str>,
1085    manga_ori_name: Option<&'q str>,
1086    category: Option<i16>,
1087    manga_status: Option<i16>,
1088    author_name: Option<&'q str>,
1089    magazine_name: Option<&'q str>,
1090) -> sqlx::query::Query<'q, MySql, sqlx::mysql::MySqlArguments> {
1091    if let Some(v) = manga_tran_name {
1092        q = q.bind(v);
1093    }
1094    if let Some(v) = manga_ori_name {
1095        q = q.bind(v);
1096    }
1097    if let Some(v) = category {
1098        q = q.bind(v);
1099    }
1100    if let Some(v) = manga_status {
1101        q = q.bind(v);
1102    }
1103    if let Some(v) = author_name {
1104        q = q.bind(v);
1105    }
1106    if let Some(v) = magazine_name {
1107        q = q.bind(v);
1108    }
1109    q
1110}
1111
1112/// 单本漫画 SQL（对齐 Java getMangaById，不含时轴进度字段）
1113const GET_MANGA_BY_ID_SQL: &str = r#"
1114SELECT
1115    m.*,
1116    (SELECT a.Id FROM mangaauthor ma JOIN authortb a ON ma.authorId = a.Id
1117     WHERE ma.mangaId = m.Id AND ma.type = 1 LIMIT 1) AS authorId,
1118    (SELECT a.authorName FROM mangaauthor ma JOIN authortb a ON ma.authorId = a.Id
1119     WHERE ma.mangaId = m.Id AND ma.type = 1 LIMIT 1) AS authorName,
1120    (SELECT a.Id FROM mangaauthor ma JOIN authortb a ON ma.authorId = a.Id
1121     WHERE ma.mangaId = m.Id AND ma.type = 2 LIMIT 1) AS authorId2,
1122    (SELECT a.authorName FROM mangaauthor ma JOIN authortb a ON ma.authorId = a.Id
1123     WHERE ma.mangaId = m.Id AND ma.type = 2 LIMIT 1) AS authorName2,
1124    (SELECT maga.Id FROM mangamagazine mm JOIN magazine maga ON maga.Id = mm.magazineId
1125     WHERE mm.mangaId = m.Id LIMIT 1) AS magazineId,
1126    (SELECT maga.magazineName FROM mangamagazine mm JOIN magazine maga ON maga.Id = mm.magazineId
1127     WHERE mm.mangaId = m.Id LIMIT 1) AS magazineName,
1128    (SELECT med.translatorSetupTime FROM mangaepisodetb me
1129     JOIN mangaepisodedetail med ON med.episodeId = me.Id
1130     WHERE me.mangaId = m.Id ORDER BY me.Id DESC LIMIT 1) AS translatorSetupTime,
1131    (SELECT med.translatorUpdateTime FROM mangaepisodetb me
1132     JOIN mangaepisodedetail med ON med.episodeId = me.Id
1133     WHERE me.mangaId = m.Id ORDER BY me.Id DESC LIMIT 1) AS translatorUpdateTime,
1134    (SELECT med.proofreaderSetupTime FROM mangaepisodetb me
1135     JOIN mangaepisodedetail med ON med.episodeId = me.Id
1136     WHERE me.mangaId = m.Id ORDER BY me.Id DESC LIMIT 1) AS proofreaderSetupTime,
1137    (SELECT med.proofreaderUpdateTime FROM mangaepisodetb me
1138     JOIN mangaepisodedetail med ON med.episodeId = me.Id
1139     WHERE me.mangaId = m.Id ORDER BY me.Id DESC LIMIT 1) AS proofreaderUpdateTime,
1140    (SELECT med.lettererSetupTime FROM mangaepisodetb me
1141     JOIN mangaepisodedetail med ON med.episodeId = me.Id
1142     WHERE me.mangaId = m.Id ORDER BY me.Id DESC LIMIT 1) AS lettererSetupTime,
1143    (SELECT med.lettererUpdateTime FROM mangaepisodetb me
1144     JOIN mangaepisodedetail med ON med.episodeId = me.Id
1145     WHERE me.mangaId = m.Id ORDER BY me.Id DESC LIMIT 1) AS lettererUpdateTime,
1146    (SELECT med.reviewerSetupTime FROM mangaepisodetb me
1147     JOIN mangaepisodedetail med ON med.episodeId = me.Id
1148     WHERE me.mangaId = m.Id ORDER BY me.Id DESC LIMIT 1) AS reviewerSetupTime,
1149    (SELECT med.reviewerUpdateTime FROM mangaepisodetb me
1150     JOIN mangaepisodedetail med ON med.episodeId = me.Id
1151     WHERE me.mangaId = m.Id ORDER BY me.Id DESC LIMIT 1) AS reviewerUpdateTime
1152FROM mangatb m
1153WHERE m.Id = ?
1154"#;
1155
1156/// 单本漫画卡片 SQL（对齐 Java getMangaById）
1157const GET_MANGA_CARD_BY_ID_SQL: &str = r#"
1158SELECT
1159    m.*,
1160    (SELECT a.Id FROM mangaauthor ma JOIN authortb a ON ma.authorId = a.Id
1161     WHERE ma.mangaId = m.Id AND ma.type = 1 LIMIT 1) AS authorId,
1162    (SELECT a.authorName FROM mangaauthor ma JOIN authortb a ON ma.authorId = a.Id
1163     WHERE ma.mangaId = m.Id AND ma.type = 1 LIMIT 1) AS authorName,
1164    (SELECT a.Id FROM mangaauthor ma JOIN authortb a ON ma.authorId = a.Id
1165     WHERE ma.mangaId = m.Id AND ma.type = 2 LIMIT 1) AS authorId2,
1166    (SELECT a.authorName FROM mangaauthor ma JOIN authortb a ON ma.authorId = a.Id
1167     WHERE ma.mangaId = m.Id AND ma.type = 2 LIMIT 1) AS authorName2,
1168    (SELECT maga.Id FROM mangamagazine mm JOIN magazine maga ON maga.Id = mm.magazineId
1169     WHERE mm.mangaId = m.Id LIMIT 1) AS magazineId,
1170    (SELECT maga.magazineName FROM mangamagazine mm JOIN magazine maga ON maga.Id = mm.magazineId
1171     WHERE mm.mangaId = m.Id LIMIT 1) AS magazineName,
1172    (SELECT med.translatorSetupTime FROM mangaepisodetb me
1173     JOIN mangaepisodedetail med ON med.episodeId = me.Id
1174     WHERE me.mangaId = m.Id ORDER BY me.Id DESC LIMIT 1) AS translatorSetupTime,
1175    (SELECT med.translatorUpdateTime FROM mangaepisodetb me
1176     JOIN mangaepisodedetail med ON med.episodeId = me.Id
1177     WHERE me.mangaId = m.Id ORDER BY me.Id DESC LIMIT 1) AS translatorUpdateTime,
1178    (SELECT med.proofreaderSetupTime FROM mangaepisodetb me
1179     JOIN mangaepisodedetail med ON med.episodeId = me.Id
1180     WHERE me.mangaId = m.Id ORDER BY me.Id DESC LIMIT 1) AS proofreaderSetupTime,
1181    (SELECT med.proofreaderUpdateTime FROM mangaepisodetb me
1182     JOIN mangaepisodedetail med ON med.episodeId = me.Id
1183     WHERE me.mangaId = m.Id ORDER BY me.Id DESC LIMIT 1) AS proofreaderUpdateTime,
1184    (SELECT med.lettererSetupTime FROM mangaepisodetb me
1185     JOIN mangaepisodedetail med ON med.episodeId = me.Id
1186     WHERE me.mangaId = m.Id ORDER BY me.Id DESC LIMIT 1) AS lettererSetupTime,
1187    (SELECT med.lettererUpdateTime FROM mangaepisodetb me
1188     JOIN mangaepisodedetail med ON med.episodeId = me.Id
1189     WHERE me.mangaId = m.Id ORDER BY me.Id DESC LIMIT 1) AS lettererUpdateTime,
1190    (SELECT med.reviewerSetupTime FROM mangaepisodetb me
1191     JOIN mangaepisodedetail med ON med.episodeId = me.Id
1192     WHERE me.mangaId = m.Id ORDER BY me.Id DESC LIMIT 1) AS reviewerSetupTime,
1193    (SELECT med.reviewerUpdateTime FROM mangaepisodetb me
1194     JOIN mangaepisodedetail med ON med.episodeId = me.Id
1195     WHERE me.mangaId = m.Id ORDER BY me.Id DESC LIMIT 1) AS reviewerUpdateTime,
1196    (SELECT med.timerSetupTime FROM mangaepisodetb me
1197     JOIN mangaepisodedetail med ON med.episodeId = me.Id
1198     WHERE me.mangaId = m.Id ORDER BY me.Id DESC LIMIT 1) AS timerSetupTime,
1199    (SELECT med.timerUpdateTime FROM mangaepisodetb me
1200     JOIN mangaepisodedetail med ON med.episodeId = me.Id
1201     WHERE me.mangaId = m.Id ORDER BY me.Id DESC LIMIT 1) AS timerUpdateTime
1202FROM mangatb m
1203WHERE m.Id = ?
1204"#;
1205
1206/// 漫画详情 SQL（对齐 Java getMangaById，不含单话进度字段）
1207const GET_MANGA_DETAIL_BY_ID_SQL: &str = r#"
1208SELECT
1209    m.*,
1210    (SELECT a.Id FROM mangaauthor ma JOIN authortb a ON ma.authorId = a.Id
1211     WHERE ma.mangaId = m.Id AND ma.type = 1 LIMIT 1) AS authorId,
1212    (SELECT a.authorName FROM mangaauthor ma JOIN authortb a ON ma.authorId = a.Id
1213     WHERE ma.mangaId = m.Id AND ma.type = 1 LIMIT 1) AS authorName,
1214    (SELECT a.Id FROM mangaauthor ma JOIN authortb a ON ma.authorId = a.Id
1215     WHERE ma.mangaId = m.Id AND ma.type = 2 LIMIT 1) AS authorId2,
1216    (SELECT a.authorName FROM mangaauthor ma JOIN authortb a ON ma.authorId = a.Id
1217     WHERE ma.mangaId = m.Id AND ma.type = 2 LIMIT 1) AS authorName2,
1218    (SELECT maga.Id FROM mangamagazine mm JOIN magazine maga ON maga.Id = mm.magazineId
1219     WHERE mm.mangaId = m.Id LIMIT 1) AS magazineId,
1220    (SELECT maga.magazineName FROM mangamagazine mm JOIN magazine maga ON maga.Id = mm.magazineId
1221     WHERE mm.mangaId = m.Id LIMIT 1) AS magazineName
1222FROM mangatb m
1223WHERE m.Id = ?
1224"#;
1225
1226/// 组员收藏列表 SQL（对齐 Java getCollectList）
1227const GET_COLLECT_LIST_SQL: &str = r#"
1228SELECT DISTINCT m.*,
1229    MAX(CASE WHEN ma.type = 1 THEN a.Id END) AS authorId,
1230    MAX(CASE WHEN ma.type = 1 THEN a.authorName END) AS authorName,
1231    MAX(CASE WHEN ma.type = 2 THEN a.Id END) AS authorId2,
1232    MAX(CASE WHEN ma.type = 2 THEN a.authorName END) AS authorName2,
1233    MAX(med.translatorSetupTime) AS translatorSetupTime,
1234    MAX(med.translatorUpdateTime) AS translatorUpdateTime,
1235    MAX(med.proofreaderSetupTime) AS proofreaderSetupTime,
1236    MAX(med.proofreaderUpdateTime) AS proofreaderUpdateTime,
1237    MAX(med.lettererSetupTime) AS lettererSetupTime,
1238    MAX(med.lettererUpdateTime) AS lettererUpdateTime,
1239    MAX(med.reviewerSetupTime) AS reviewerSetupTime,
1240    MAX(med.reviewerUpdateTime) AS reviewerUpdateTime,
1241    MAX(med.timerSetupTime) AS timerSetupTime,
1242    MAX(med.timerUpdateTime) AS timerUpdateTime
1243FROM mangatb m
1244LEFT JOIN mangaauthor ma ON m.Id = ma.mangaId
1245LEFT JOIN authortb a ON ma.authorId = a.Id
1246LEFT JOIN mangaepisodetb me ON me.mangaId = m.Id
1247INNER JOIN mangacollect mc ON m.Id = mc.mangaId
1248LEFT JOIN (
1249    SELECT me2.mangaId, MAX(me2.Id) AS maxEpisodeId
1250    FROM mangaepisodetb me2
1251    GROUP BY me2.mangaId
1252) maxEp ON maxEp.mangaId = m.Id
1253LEFT JOIN mangaepisodedetail med ON med.episodeId = maxEp.maxEpisodeId
1254WHERE mc.memberId = ?
1255GROUP BY m.Id, m.updateTime
1256ORDER BY m.updateTime DESC
1257"#;
1258
1259/// 行映射为术语
1260fn row_to_glossary(row: &sqlx::mysql::MySqlRow) -> GlossaryVo {
1261    GlossaryVo {
1262        id: row.try_get("Id").unwrap_or(0),
1263        title: row.try_get("title").ok(),
1264        content: row.try_get("content").ok(),
1265        image: row.try_get("image").ok(),
1266        r#type: row.try_get("type").ok(),
1267        manga_id: row.try_get("mangaId").ok(),
1268        first: row.try_get("first").ok(),
1269        update_time: row.try_get("updateTime").ok(),
1270        update_by: row.try_get("updateBy").ok(),
1271        update_member_name: row.try_get("updateMemberName").ok(),
1272    }
1273}
1274
1275/// 行映射为 RSS 漫画
1276fn row_to_rss_manga(row: &sqlx::mysql::MySqlRow) -> crate::entity::rss::RssMangaRow {
1277    crate::entity::rss::RssMangaRow {
1278        id: row.get("Id"),
1279        manga_tran_name: row.try_get("mangaTranName").ok(),
1280        manga_ori_name: row.try_get("mangaOriName").ok(),
1281        category: row.try_get("category").ok(),
1282        manga_status: row.try_get("mangaStatus").ok(),
1283        image: row.try_get("image").ok(),
1284        setup_time: row.try_get("setupTime").ok(),
1285        update_time: row.try_get("updateTime").ok(),
1286        author_id: row.try_get("authorId").ok(),
1287        author_name: row.try_get("authorName").ok(),
1288        author_id2: row.try_get("authorId2").ok(),
1289        author_name2: row.try_get("authorName2").ok(),
1290    }
1291}
1292
1293/// 行映射为 RSS 话数
1294fn row_to_episode_rss(row: &sqlx::mysql::MySqlRow) -> crate::entity::rss::EpisodeRssRow {
1295    crate::entity::rss::EpisodeRssRow {
1296        id: row.get("Id"),
1297        manga_id: row.get("mangaId"),
1298        manga_episode: row.try_get("mangaEpisode").ok(),
1299        manga_episode_name: row.try_get("mangaEpisodeName").ok(),
1300        setup_time: row.try_get("setupTime").ok(),
1301        manga_name: row.try_get("mangaName").ok(),
1302        manga_ori_name: row.try_get("mangaOriName").ok(),
1303        category: row.try_get("category").ok(),
1304        manga_status: row.try_get("mangaStatus").ok(),
1305        manga_setup_time: row.try_get("mangaSetupTime").ok(),
1306        image: row.try_get("image").ok(),
1307        provider_name: row.try_get("providerName").ok(),
1308        translator_name: row.try_get("translatorName").ok(),
1309        intern: row.try_get("intern").ok(),
1310        email: row.try_get("email").ok(),
1311        publish_link: row.try_get("publishLink").ok(),
1312    }
1313}
1314
1315/// 行映射为交稿提醒 RSS
1316fn row_to_work_reminder_rss(row: &sqlx::mysql::MySqlRow) -> crate::entity::rss::WorkReminderRssRow {
1317    crate::entity::rss::WorkReminderRssRow {
1318        episode_id: row.try_get("episodeId").unwrap_or(0),
1319        manga_id: row.try_get("mangaId").unwrap_or(0),
1320        manga_episode: row.try_get("mangaEpisode").ok(),
1321        manga_episode_name: row.try_get("mangaEpisodeName").ok(),
1322        setup_time: row.try_get("setupTime").ok(),
1323        manga_name: row.try_get("mangaName").ok(),
1324        manga_ori_name: row.try_get("mangaOriName").ok(),
1325        category: row.try_get("category").ok(),
1326        manga_status: row.try_get("mangaStatus").ok(),
1327        manga_setup_time: row.try_get("mangaSetupTime").ok(),
1328        image: row.try_get("image").ok(),
1329        my_name: row.try_get("myName").unwrap_or_default(),
1330        username_now: row.try_get("usernameNow").ok(),
1331        intern_now: row.try_get("internNow").ok(),
1332        email_now: row.try_get("emailNow").ok(),
1333        username: row.try_get("username").ok(),
1334        intern: row.try_get("intern").ok(),
1335        email: row.try_get("email").ok(),
1336    }
1337}
1338
1339/// 行映射为漫画
1340fn row_to_manga(row: &sqlx::mysql::MySqlRow) -> Manga {
1341    Manga {
1342        id: row.try_get("Id").ok(),
1343        manga_tran_name: row.try_get("mangaTranName").ok(),
1344        manga_ori_name: row.try_get("mangaOriName").ok(),
1345        category: row.try_get("category").ok(),
1346        manga_status: row.try_get("mangaStatus").ok(),
1347        img_url: row.try_get("imgUrl").ok(),
1348        link: row.try_get("link").ok(),
1349        introduction: row.try_get("introduction").ok(),
1350        update_time: row.try_get::<DateTime<Utc>, _>("updateTime").ok(),
1351    }
1352}
1353
1354/// 行映射为漫画详情
1355fn row_to_manga_detail(row: &sqlx::mysql::MySqlRow) -> MangaDetailVo {
1356    MangaDetailVo {
1357        id: row.try_get("Id").unwrap_or(0),
1358        manga_tran_name: row.try_get("mangaTranName").ok(),
1359        manga_ori_name: row.try_get("mangaOriName").ok(),
1360        image: row.try_get("image").ok(),
1361        category: row.try_get("category").ok(),
1362        manga_status: row.try_get("mangaStatus").ok(),
1363        setup_time: row.try_get("setupTime").ok(),
1364        update_time: row.try_get("updateTime").ok(),
1365        link: row.try_get("link").ok(),
1366        introduction: row.try_get("introduction").ok(),
1367        author_name: row.try_get("authorName").ok(),
1368        author_id: row.try_get("authorId").ok(),
1369        author_name2: row.try_get("authorName2").ok(),
1370        author_id2: row.try_get("authorId2").ok(),
1371        magazine_name: row.try_get("magazineName").ok(),
1372        magazine_id: row.try_get("magazineId").ok(),
1373    }
1374}
1375
1376/// 行映射为收藏组员
1377fn row_to_collected_member(row: &sqlx::mysql::MySqlRow) -> CollectedMembersVo {
1378    CollectedMembersVo {
1379        id: row.try_get("Id").unwrap_or(0),
1380        username: row.try_get("username").ok(),
1381        intern: row.try_get("intern").unwrap_or(0),
1382        posts: vec![],
1383        station_count: row.try_get("stationCount").ok(),
1384        registration_time: row.try_get("registrationTime").ok(),
1385        last_submit_time: row.try_get("lastSubmitTime").ok(),
1386    }
1387}
1388
1389/// 行映射为漫画响应（对齐 Java Manga）
1390fn row_to_manga_response(row: &sqlx::mysql::MySqlRow) -> crate::entity::manga::MangaResponse {
1391    use crate::entity::manga::MangaResponse;
1392    MangaResponse {
1393        id: row.try_get("Id").ok(),
1394        manga_tran_name: row.try_get("mangaTranName").ok(),
1395        manga_ori_name: row.try_get("mangaOriName").ok(),
1396        image: row.try_get("image").ok(),
1397        category: row.try_get("category").ok(),
1398        manga_status: row.try_get("mangaStatus").ok(),
1399        setup_time: row.try_get("setupTime").ok(),
1400        update_time: row.try_get("updateTime").ok(),
1401        link: row.try_get("link").ok(),
1402        introduction: row.try_get("introduction").ok(),
1403        author_name: row.try_get("authorName").ok(),
1404        author_id: row.try_get("authorId").ok(),
1405        author_name2: row.try_get("authorName2").ok(),
1406        author_id2: row.try_get("authorId2").ok(),
1407        magazine_name: row.try_get("magazineName").ok(),
1408        magazine_id: row.try_get("magazineId").ok(),
1409        translator_setup_time: row.try_get("translatorSetupTime").ok(),
1410        translator_update_time: row.try_get("translatorUpdateTime").ok(),
1411        proofreader_setup_time: row.try_get("proofreaderSetupTime").ok(),
1412        proofreader_update_time: row.try_get("proofreaderUpdateTime").ok(),
1413        letterer_setup_time: row.try_get("lettererSetupTime").ok(),
1414        letterer_update_time: row.try_get("lettererUpdateTime").ok(),
1415        reviewer_setup_time: row.try_get("reviewerSetupTime").ok(),
1416        reviewer_update_time: row.try_get("reviewerUpdateTime").ok(),
1417    }
1418}
1419
1420/// 行映射为漫画卡片
1421fn row_to_manga_card(row: &sqlx::mysql::MySqlRow) -> MangaCardVo {
1422    MangaCardVo {
1423        id: row.try_get("Id").unwrap_or(0),
1424        manga_tran_name: row.try_get("mangaTranName").ok(),
1425        manga_ori_name: row.try_get("mangaOriName").ok(),
1426        image: row.try_get("image").ok(),
1427        category: row.try_get("category").ok(),
1428        manga_status: row.try_get("mangaStatus").ok(),
1429        update_time: row.try_get::<DateTime<Utc>, _>("updateTime").ok(),
1430        author_name: row.try_get("authorName").ok(),
1431        author_name2: row.try_get("authorName2").ok(),
1432        author_id2: row.try_get("authorId2").ok(),
1433        magazine_name: row.try_get("magazineName").ok(),
1434        translator_setup_time: row.try_get("translatorSetupTime").ok(),
1435        translator_update_time: row.try_get("translatorUpdateTime").ok(),
1436        proofreader_setup_time: row.try_get("proofreaderSetupTime").ok(),
1437        proofreader_update_time: row.try_get("proofreaderUpdateTime").ok(),
1438        letterer_setup_time: row.try_get("lettererSetupTime").ok(),
1439        letterer_update_time: row.try_get("lettererUpdateTime").ok(),
1440        reviewer_setup_time: row.try_get("reviewerSetupTime").ok(),
1441        reviewer_update_time: row.try_get("reviewerUpdateTime").ok(),
1442        timer_setup_time: row.try_get("timerSetupTime").ok(),
1443        timer_update_time: row.try_get("timerUpdateTime").ok(),
1444    }
1445}
1446
1447/// 行映射为常驻组员
1448fn row_to_station_member(row: &sqlx::mysql::MySqlRow) -> Member {
1449    Member {
1450        id: row.get("Id"),
1451        username: row.try_get("username").ok(),
1452        password: None,
1453        intern: row.get("intern"),
1454        email: row.try_get("email").ok(),
1455        registration_time: row.try_get("registrationTime").ok(),
1456        last_submit_time: row.try_get("lastSubmitTime").ok(),
1457        posts: vec![],
1458        post_ids: vec![],
1459        station_count: None,
1460        station_id: row.try_get("stationId").ok(),
1461        post: row.try_get("post").ok(),
1462        status: row.try_get("status").ok(),
1463        manga_id: row.try_get("mangaId").ok(),
1464        manga_name: row.try_get("mangaName").ok(),
1465    }
1466}
1467
1468/// 常驻记录摘要（漫画、组员、岗位）
1469pub struct StationRecord {
1470    /// 漫画 ID
1471    pub manga_id: i32,
1472    /// 组员 ID
1473    pub member_id: i32,
1474    /// 岗位 ID
1475    pub post: i32,
1476}
1477
1478/// 岗位对应的话数主表/详情表字段名
1479struct PostEpisodeFields {
1480    /// mangaepisodetb 岗位组员 ID 列
1481    member_col: &'static str,
1482    /// mangaepisodedetail 交稿时间列（图源无此列）
1483    update_time_col: Option<&'static str>,
1484    /// mangaepisodedetail 指派时间列（图源无此列）
1485    setup_time_col: Option<&'static str>,
1486}
1487
1488/// 岗位 ID 映射为话数表字段（支持 0–4、6）
1489fn post_episode_fields(post: i32) -> crate::error::ApiResult<PostEpisodeFields> {
1490    match post {
1491        0 => Ok(PostEpisodeFields {
1492            member_col: "providerId",
1493            update_time_col: None,
1494            setup_time_col: None,
1495        }),
1496        1 => Ok(PostEpisodeFields {
1497            member_col: "translatorId",
1498            update_time_col: Some("translatorUpdateTime"),
1499            setup_time_col: Some("translatorSetupTime"),
1500        }),
1501        2 => Ok(PostEpisodeFields {
1502            member_col: "proofreaderId",
1503            update_time_col: Some("proofreaderUpdateTime"),
1504            setup_time_col: Some("proofreaderSetupTime"),
1505        }),
1506        3 => Ok(PostEpisodeFields {
1507            member_col: "lettererId",
1508            update_time_col: Some("lettererUpdateTime"),
1509            setup_time_col: Some("lettererSetupTime"),
1510        }),
1511        4 => Ok(PostEpisodeFields {
1512            member_col: "reviewerId",
1513            update_time_col: Some("reviewerUpdateTime"),
1514            setup_time_col: Some("reviewerSetupTime"),
1515        }),
1516        6 => Ok(PostEpisodeFields {
1517            member_col: "timerId",
1518            update_time_col: Some("timerUpdateTime"),
1519            setup_time_col: Some("timerSetupTime"),
1520        }),
1521        _ => Err(AppError::business("不支持的常驻岗位喵")),
1522    }
1523}
tdm_server_rust/repository/manga_repo.rs

tdm_server_rust/repository/
manga_repo.rs
