tdm_server_rust/repository/
member_repo.rs

1//! 组员数据访问层 (Member Repository)
2//!
3//! 封装 `membertb`、`memberpost`、`post` 等表的查询（SeaORM）。
4
5use crate::db::DbConn;
6use crate::entity::member::{InvitationCode, Member, MemberCache, MemberEpisode, Post};
7use crate::repository::{member_episode_assemble, member_workbench_assemble};
8use crate::sea_entity::{
9    episode_role_taker, invitationcode, mangaepisodedetail, mangaepisodetb, mangastationed,
10    mangatb, memberpost, membertb, workreminder,
11};
12use sea_orm::{
13    sea_query::{Expr, Func, OnConflict},
14    ActiveModelTrait, ColumnTrait, Condition, ConnectionTrait, DatabaseConnection, DbBackend,
15    EntityTrait, PaginatorTrait, QueryFilter, QueryOrder, QuerySelect, Set, Statement,
16    TransactionTrait, Value,
17};
18use sqlx::postgres::PgPool;
19use std::collections::HashMap;
20
21/// 百合动画分类 ID，动画工序第三棒使用时轴。
22const YURI_ANIMATION_CATEGORY: i32 = 4;
23
24/// 组员仓储
25pub struct MemberRepository {
26    /// SeaORM 数据库连接
27    db: DbConn,
28}
29
30impl MemberRepository {
31    /// 从 `PgPool` 构造
32    pub fn new(pool: PgPool) -> Self {
33        Self {
34            db: crate::db::from_sqlx_pool(pool),
35        }
36    }
37
38    /// 从 `DatabaseConnection` 构造
39    pub fn from_db(db: DatabaseConnection) -> Self {
40        Self { db }
41    }
42
43    /// 底层 SeaORM 连接
44    pub fn db(&self) -> &DbConn {
45        &self.db
46    }
47
48    /// 工作台总览
49    pub async fn get_workbench_overview(
50        &self,
51        member_id: i32,
52        stationed_count: i32,
53    ) -> crate::error::ApiResult<crate::entity::member::MemberWorkbenchOverview> {
54        member_workbench_assemble::get_workbench_overview(&self.db, member_id, stationed_count)
55            .await
56    }
57
58    /// 按 ID 查询组员（含岗位列表）
59    #[tracing::instrument(skip_all, level = "debug")]
60    pub async fn get_by_id(&self, id: i32) -> crate::error::ApiResult<Member> {
61        self.get_auth_snapshot(id).await
62    }
63
64    /// 鉴权用组员快照（含岗位 ID，单条 JOIN 查询）
65    #[tracing::instrument(skip_all, level = "debug")]
66    pub async fn get_auth_snapshot(&self, id: i32) -> crate::error::ApiResult<Member> {
67        query_auth_snapshot_member(&self.db, id).await
68    }
69
70    /// 登录查询
71    #[tracing::instrument(skip_all, level = "debug")]
72    pub async fn find_for_login(
73        &self,
74        username: &str,
75    ) -> crate::error::ApiResult<Option<(Member, String)>> {
76        let row = membertb::Entity::find()
77            .filter(
78                Condition::any()
79                    .add(
80                        Expr::expr(Func::lower(Expr::col(membertb::Column::Username)))
81                            .eq(Func::lower(Expr::value(username))),
82                    )
83                    .add(membertb::Column::Email.eq(username)),
84            )
85            .one(&self.db)
86            .await?;
87        Ok(row.map(|m| (model_to_member(m.clone()), m.password)))
88    }
89
90    /// 分页查询组员
91    #[tracing::instrument(skip_all, level = "debug")]
92    pub async fn page_list(
93        &self,
94        username: Option<&str>,
95        post: Option<i16>,
96        intern: Option<i16>,
97        email: Option<&str>,
98        page: i32,
99        page_size: i32,
100    ) -> crate::error::ApiResult<(i64, Vec<Member>)> {
101        let page = page.max(1);
102        let page_size = page_size.max(1);
103        let offset = ((page - 1) * page_size) as u64;
104
105        let (total, rows) = tokio::try_join!(
106            count_list_members(&self.db, username, post, intern, email),
107            list_members_page(
108                &self.db,
109                username,
110                post,
111                intern,
112                email,
113                page_size as u64,
114                offset,
115            ),
116        )?;
117        if total == 0 {
118            return Ok((0, vec![]));
119        }
120
121        let ids: Vec<i32> = rows.iter().map(|m| m.id).collect();
122        let (posts_map, station_map) = tokio::try_join!(
123            batch_post_ids_via_post(&self.db, &ids),
124            batch_station_counts(&self.db, &ids),
125        )?;
126
127        let mut members = Vec::with_capacity(rows.len());
128        for m in rows {
129            let mut member = model_to_member(m);
130            member.post_ids = posts_map.get(&member.id).cloned().unwrap_or_default();
131            member.posts = member.post_ids.iter().map(|p| Post { post: *p }).collect();
132            member.station_count = Some(station_map.get(&member.id).copied().unwrap_or(0));
133            members.push(member);
134        }
135        Ok((total, members))
136    }
137
138    /// 全量组员缓存列表
139    #[tracing::instrument(skip_all, level = "debug")]
140    pub async fn all_cache(&self) -> crate::error::ApiResult<Vec<MemberCache>> {
141        let rows = membertb::Entity::find()
142            .order_by_asc(membertb::Column::Id)
143            .all(&self.db)
144            .await?;
145        let ids: Vec<i32> = rows.iter().map(|m| m.id).collect();
146        let posts_map = batch_post_ids_via_post(&self.db, &ids).await?;
147        Ok(rows
148            .into_iter()
149            .map(|m| MemberCache {
150                id: m.id,
151                username: Some(m.username),
152                intern: m.intern as i16,
153                email: m.email,
154                avatar_url: m.avatar_url,
155                posts: posts_map.get(&m.id).cloned().unwrap_or_default(),
156            })
157            .collect())
158    }
159
160    /// 删除组员
161    #[tracing::instrument(skip_all, level = "debug")]
162    pub async fn delete_members(&self, ids: &[i32]) -> crate::error::ApiResult<()> {
163        if ids.is_empty() {
164            return Ok(());
165        }
166        use mangaepisodetb::Column as EC;
167        let related = mangaepisodetb::Entity::find()
168            .filter(
169                Condition::any()
170                    .add(EC::ProviderId.is_in(ids.to_vec()))
171                    .add(EC::TranslatorId.is_in(ids.to_vec()))
172                    .add(EC::ProofreaderId.is_in(ids.to_vec()))
173                    .add(EC::LettererId.is_in(ids.to_vec()))
174                    .add(EC::ReviewerId.is_in(ids.to_vec())),
175            )
176            .one(&self.db)
177            .await?;
178        if related.is_some() {
179            return Err(crate::error::AppError::business("该组员已经绑定漫画了喵！"));
180        }
181        for id in ids {
182            memberpost::Entity::delete_many()
183                .filter(memberpost::Column::MemberId.eq(*id))
184                .exec(&self.db)
185                .await?;
186            membertb::Entity::delete_by_id(*id).exec(&self.db).await?;
187        }
188        Ok(())
189    }
190
191    /// 新增组员
192    #[tracing::instrument(skip_all, level = "debug")]
193    pub async fn insert_member(&self, m: &Member) -> crate::error::ApiResult<i32> {
194        let model = membertb::ActiveModel {
195            username: Set(m.username.clone().unwrap_or_default()),
196            password: Set(m.password.clone().unwrap_or_default()),
197            intern: Set(m.intern as i32),
198            email: Set(m.email.clone()),
199            avatar_url: Set(m.avatar_url.clone()),
200            registration_time: Set(chrono::Utc::now().naive_utc()),
201            ..Default::default()
202        };
203        let inserted = model.insert(&self.db).await?;
204        Ok(inserted.id)
205    }
206
207    /// 更新组员（COALESCE 语义）
208    #[tracing::instrument(skip_all, level = "debug")]
209    pub async fn update_member(&self, m: &Member) -> crate::error::ApiResult<()> {
210        Self::update_member_on(&self.db, m).await
211    }
212
213    async fn update_member_on<C: ConnectionTrait>(
214        db: &C,
215        m: &Member,
216    ) -> crate::error::ApiResult<()> {
217        let existing = membertb::Entity::find_by_id(m.id)
218            .one(db)
219            .await?
220            .ok_or_else(|| crate::error::AppError::business("组员不存在"))?;
221        let mut am: membertb::ActiveModel = existing.into();
222        if let Some(ref u) = m.username {
223            am.username = Set(u.clone());
224        }
225        if let Some(ref p) = m.password {
226            am.password = Set(p.clone());
227        }
228        if let Some(ref e) = m.email {
229            am.email = Set(Some(e.clone()));
230        }
231        am.avatar_url = Set(m.avatar_url.clone());
232        am.intern = Set(m.intern as i32);
233        am.update(db).await?;
234        Ok(())
235    }
236
237    /// 在同一事务中更新组员信息和岗位，避免职阶与权限出现半完成状态。
238    #[tracing::instrument(skip_all, level = "debug")]
239    pub async fn update_member_and_posts(
240        &self,
241        m: &Member,
242        post_ids: &[i32],
243    ) -> crate::error::ApiResult<()> {
244        let txn = self.db.begin().await?;
245        Self::update_member_on(&txn, m).await?;
246        Self::replace_posts_on(&txn, m.id, post_ids).await?;
247        txn.commit().await?;
248        Ok(())
249    }
250
251    /// 更新岗位
252    #[tracing::instrument(skip_all, level = "debug")]
253    pub async fn replace_posts(
254        &self,
255        member_id: i32,
256        post_ids: &[i32],
257    ) -> crate::error::ApiResult<()> {
258        Self::replace_posts_on(&self.db, member_id, post_ids).await
259    }
260
261    async fn replace_posts_on<C: ConnectionTrait>(
262        db: &C,
263        member_id: i32,
264        post_ids: &[i32],
265    ) -> crate::error::ApiResult<()> {
266        memberpost::Entity::delete_many()
267            .filter(memberpost::Column::MemberId.eq(member_id))
268            .exec(db)
269            .await?;
270        for pid in post_ids {
271            memberpost::ActiveModel {
272                member_id: Set(member_id),
273                post_id: Set(*pid),
274                ..Default::default()
275            }
276            .insert(db)
277            .await?;
278        }
279        Ok(())
280    }
281
282    /// 改密
283    #[tracing::instrument(skip_all, level = "debug")]
284    pub async fn update_password(&self, id: i32, new_pwd: &str) -> crate::error::ApiResult<()> {
285        let model = membertb::ActiveModel {
286            id: Set(id),
287            password: Set(new_pwd.to_string()),
288            ..Default::default()
289        };
290        model.update(&self.db).await?;
291        Ok(())
292    }
293
294    /// 取密码哈希
295    #[tracing::instrument(skip_all, level = "debug")]
296    pub async fn get_password(&self, id: i32) -> crate::error::ApiResult<String> {
297        let row = membertb::Entity::find_by_id(id)
298            .one(&self.db)
299            .await?
300            .ok_or_else(|| crate::error::AppError::business("组员不存在"))?;
301        Ok(row.password)
302    }
303
304    /// 用户名重复检查
305    #[tracing::instrument(skip_all, level = "debug")]
306    pub async fn exists_username(&self, username: &str) -> crate::error::ApiResult<bool> {
307        let n = membertb::Entity::find()
308            .filter(membertb::Column::Username.eq(username))
309            .count(&self.db)
310            .await?;
311        Ok(n > 0)
312    }
313
314    /// 邮箱重复检查
315    #[tracing::instrument(skip_all, level = "debug")]
316    pub async fn exists_email(&self, email: &str) -> crate::error::ApiResult<bool> {
317        let n = membertb::Entity::find()
318            .filter(membertb::Column::Email.eq(email))
319            .count(&self.db)
320            .await?;
321        Ok(n > 0)
322    }
323
324    /// 邀请码有效
325    #[tracing::instrument(skip_all, level = "debug")]
326    pub async fn is_valid_invitation(&self, code: i32) -> crate::error::ApiResult<bool> {
327        let n = invitationcode::Entity::find()
328            .filter(invitationcode::Column::Code.eq(code))
329            .count(&self.db)
330            .await?;
331        Ok(n > 0)
332    }
333
334    /// 邀请码列表
335    #[tracing::instrument(skip_all, level = "debug")]
336    pub async fn list_invitation_codes(&self) -> crate::error::ApiResult<Vec<InvitationCode>> {
337        let rows = invitationcode::Entity::find().all(&self.db).await?;
338        Ok(rows
339            .into_iter()
340            .map(|r| InvitationCode {
341                id: Some(r.id),
342                code: r.code,
343            })
344            .collect())
345    }
346
347    /// 删除邀请码
348    #[tracing::instrument(skip_all, level = "debug")]
349    pub async fn delete_invitation(&self, id: i32) -> crate::error::ApiResult<()> {
350        invitationcode::Entity::delete_by_id(id)
351            .exec(&self.db)
352            .await?;
353        Ok(())
354    }
355
356    /// 新增邀请码
357    #[tracing::instrument(skip_all, level = "debug")]
358    pub async fn add_invitation(&self, code: i32) -> crate::error::ApiResult<()> {
359        invitationcode::ActiveModel {
360            code: Set(code),
361            ..Default::default()
362        }
363        .insert(&self.db)
364        .await?;
365        Ok(())
366    }
367
368    /// 组员话数分页（COUNT 与列表并行）
369    #[tracing::instrument(skip_all, level = "debug")]
370    pub async fn page_member_episodes(
371        &self,
372        member_id: i32,
373        page: i32,
374        page_size: i32,
375    ) -> crate::error::ApiResult<(i64, Vec<crate::entity::member::MemberEpisodeVo>)> {
376        let (total, list) = tokio::try_join!(
377            member_episode_assemble::count_member_episodes(&self.db, member_id),
378            member_episode_assemble::page_member_episode_vos(&self.db, member_id, page, page_size),
379        )?;
380        Ok((total, list))
381    }
382
383    /// 组员话数列表（全量）
384    #[tracing::instrument(skip_all, level = "debug")]
385    pub async fn list_member_episodes(
386        &self,
387        member_id: i32,
388    ) -> crate::error::ApiResult<Vec<crate::entity::member::MemberEpisodeVo>> {
389        member_episode_assemble::list_member_episode_vos(&self.db, member_id).await
390    }
391
392    /// 工作台在做任务按漫画组分页
393    #[tracing::instrument(skip_all, level = "debug")]
394    pub async fn page_workbench_tasks(
395        &self,
396        member_id: i32,
397        tab: &str,
398        view: &str,
399        keyword: Option<&str>,
400        page: i32,
401        page_size: i32,
402    ) -> crate::error::ApiResult<(i64, Vec<crate::entity::member::MemberWorkbenchTaskGroup>)> {
403        let (total, manga_ids) = tokio::try_join!(
404            member_workbench_assemble::count_workbench_tasks(
405                &self.db, member_id, tab, view, keyword
406            ),
407            member_workbench_assemble::page_workbench_manga_ids(
408                &self.db, member_id, tab, view, keyword, page, page_size
409            ),
410        )?;
411        let ordered = member_workbench_assemble::list_workbench_task_rows_by_mangas(
412            &self.db, member_id, tab, view, keyword, &manga_ids,
413        )
414        .await?;
415        let rows =
416            member_workbench_assemble::assemble_workbench_task_groups(&self.db, manga_ids, ordered)
417                .await?;
418        Ok((total, rows))
419    }
420
421    /// 接稿
422    ///
423    /// `enforce_vacancy` 为真时，岗位若已被他人占用则拒绝接稿（防止覆盖）；
424    /// 管理员重新分派可传 `false` 跳过空位校验。
425    #[tracing::instrument(skip_all, level = "debug")]
426    pub async fn take_episode(
427        &self,
428        ep: &MemberEpisode,
429        enforce_vacancy: bool,
430    ) -> crate::error::ApiResult<()> {
431        let col = post_episode_column(&ep.my_name)?;
432        let detail_col = post_detail_setup_column(&ep.my_name)?;
433        let model = mangaepisodetb::Entity::find_by_id(ep.episode_id)
434            .one(&self.db)
435            .await?
436            .ok_or_else(|| crate::error::AppError::business("话数不存在"))?;
437        if enforce_vacancy {
438            ensure_episode_upstream_ready(&self.db, &model, &ep.my_name).await?;
439            if let Some(existing) = get_episode_member_col(&model, col) {
440                if existing != ep.member_id {
441                    if is_co_taker_role(&ep.my_name) {
442                        insert_episode_role_taker(
443                            &self.db,
444                            ep.episode_id,
445                            &ep.my_name,
446                            ep.member_id,
447                        )
448                        .await?;
449                        touch_manga_by_episode(&self.db, ep.episode_id).await?;
450                        return Ok(());
451                    }
452                    return Err(crate::error::AppError::business(
453                        "该岗位已被接走，刷新看看喵",
454                    ));
455                }
456            }
457        }
458        let mut am: mangaepisodetb::ActiveModel = model.into();
459        set_episode_member_col(&mut am, col, ep.member_id)?;
460        am.update_time = Set(chrono::Utc::now().naive_utc());
461        am.update(&self.db).await?;
462
463        update_episode_detail_time(&self.db, ep.episode_id, detail_col).await?;
464        touch_manga_by_episode(&self.db, ep.episode_id).await?;
465        Ok(())
466    }
467
468    /// 是否为话数主接稿人。
469    async fn is_primary_role_member(&self, ep: &MemberEpisode) -> crate::error::ApiResult<bool> {
470        let col = post_episode_column(&ep.my_name)?;
471        let model = mangaepisodetb::Entity::find_by_id(ep.episode_id)
472            .one(&self.db)
473            .await?
474            .ok_or_else(|| crate::error::AppError::business("话数不存在"))?;
475        Ok(get_episode_member_col(&model, col) == Some(ep.member_id))
476    }
477
478    /// 交稿
479    #[tracing::instrument(skip_all, level = "debug")]
480    pub async fn submit_episode(&self, ep: &MemberEpisode) -> crate::error::ApiResult<bool> {
481        if is_co_taker_role(&ep.my_name) && !self.is_primary_role_member(ep).await? {
482            submit_episode_role_taker(&self.db, ep.episode_id, &ep.my_name, ep.member_id).await?;
483            update_member_last_submit_time(&self.db, ep.member_id).await?;
484            touch_episode(&self.db, ep.episode_id).await?;
485            touch_manga_by_episode(&self.db, ep.episode_id).await?;
486            return Ok(false);
487        }
488        let detail_col = post_detail_update_column(&ep.my_name)?;
489        update_episode_detail_time(&self.db, ep.episode_id, detail_col).await?;
490
491        update_member_last_submit_time(&self.db, ep.member_id).await?;
492
493        touch_episode(&self.db, ep.episode_id).await?;
494
495        touch_manga_by_episode(&self.db, ep.episode_id).await?;
496
497        // RSS 提醒的业务身份与 GUID 一致：同一话同一岗位只允许写入一次。
498        let inserted = workreminder::Entity::insert(workreminder::ActiveModel {
499            episode_id: Set(Some(ep.episode_id)),
500            member_id: Set(Some(ep.member_id)),
501            my_name: Set(Some(ep.my_name.clone())),
502            ..Default::default()
503        })
504        .on_conflict(
505            OnConflict::columns([
506                workreminder::Column::EpisodeId,
507                workreminder::Column::MyName,
508            ])
509            .do_nothing()
510            .to_owned(),
511        )
512        .exec_without_returning(&self.db)
513        .await?;
514        Ok(inserted == 1)
515    }
516
517    /// 三月未交稿/未转正组员 RSS
518    ///
519    /// 正式组员按 `lastSubmitTime` 判断，实习组员按 `registrationTime` 判断。
520    #[tracing::instrument(skip_all, level = "debug")]
521    pub async fn get_member_reminder_rss(
522        &self,
523    ) -> crate::error::ApiResult<Vec<crate::entity::rss::MemberReminderRssRow>> {
524        let three_months_ago = Expr::cust("CURRENT_TIMESTAMP - INTERVAL '3 months'");
525        let rows = membertb::Entity::find()
526            .filter(
527                Condition::any()
528                    .add(
529                        Condition::all()
530                            .add(Expr::col(membertb::Column::Intern).eq(0))
531                            .add(Expr::col(membertb::Column::LastSubmitTime).lte(three_months_ago.clone())),
532                    )
533                    .add(
534                        Condition::all()
535                            .add(Expr::col(membertb::Column::Intern).eq(1))
536                            .add(Expr::col(membertb::Column::RegistrationTime).lte(three_months_ago)),
537                    ),
538            )
539            .order_by_asc(membertb::Column::RegistrationTime)
540            .all(&self.db)
541            .await?;
542        Ok(rows
543            .into_iter()
544            .map(|m| crate::entity::rss::MemberReminderRssRow {
545                id: m.id,
546                username: Some(m.username),
547                intern: m.intern as i16,
548                email: m.email,
549                last_submit_time: Some(crate::utils::legacy_time::naive_db_to_api_utc(
550                    m.last_submit_time,
551                )),
552                registration_time: Some(crate::utils::legacy_time::naive_db_to_api_utc(
553                    m.registration_time,
554                )),
555            })
556            .collect())
557    }
558
559    /// 查询组员岗位 ID 列表
560    #[tracing::instrument(skip_all, level = "debug")]
561    pub async fn get_post_ids_by_member(
562        &self,
563        member_id: i32,
564    ) -> crate::error::ApiResult<Vec<i32>> {
565        load_raw_post_ids(&self.db, member_id).await
566    }
567
568    /// 常驻漫画岗位分组（INNER JOIN mangatb，排除已删漫画）
569    #[tracing::instrument(skip_all, level = "debug")]
570    pub async fn stationed_manga_post_groups(
571        &self,
572        member_id: i32,
573    ) -> crate::error::ApiResult<HashMap<i32, Vec<i32>>> {
574        let sql = r#"SELECT ms."mangaId", ms.post
575            FROM mangastationed ms
576            INNER JOIN mangatb m ON m."Id" = ms."mangaId"
577            WHERE ms."memberId" = $1 AND ms.status = 1"#;
578        let rows = self
579            .db
580            .query_all(Statement::from_sql_and_values(
581                DbBackend::Postgres,
582                sql,
583                [Value::from(member_id)],
584            ))
585            .await?;
586        let mut map: HashMap<i32, Vec<i32>> = HashMap::new();
587        for row in rows {
588            let manga_id: i32 = row.try_get("", "mangaId")?;
589            let post: i32 = row.try_get("", "post")?;
590            map.entry(manga_id).or_default().push(post);
591        }
592        Ok(map)
593    }
594
595    /// 批量查询组员岗位 ID
596    #[tracing::instrument(skip_all, level = "debug")]
597    pub async fn get_posts_map(
598        &self,
599        member_ids: &[i32],
600    ) -> crate::error::ApiResult<HashMap<i32, Vec<i32>>> {
601        batch_posts_map_inner(&self.db, member_ids).await
602    }
603}
604
605/// 应用组员列表筛选条件
606fn apply_list_filters(
607    mut q: sea_orm::Select<membertb::Entity>,
608    username: Option<&str>,
609    post: Option<i16>,
610    intern: Option<i16>,
611    email: Option<&str>,
612) -> sea_orm::Select<membertb::Entity> {
613    q = q.filter(has_memberpost_condition(post));
614    if let Some(u) = username {
615        q = q.filter(membertb::Column::Username.contains(u));
616    }
617    if let Some(p) = post {
618        let _ = p;
619    }
620    if let Some(i) = intern {
621        q = q.filter(membertb::Column::Intern.eq(i as i32));
622    }
623    if let Some(e) = email {
624        q = q.filter(membertb::Column::Email.eq(e));
625    }
626    q
627}
628
629/// 必须有岗位记录（对齐 INNER JOIN memberpost）
630fn has_memberpost_condition(post: Option<i16>) -> Condition {
631    let mut sub = sea_orm::sea_query::Query::select()
632        .column(memberpost::Column::MemberId)
633        .from(memberpost::Entity)
634        .to_owned();
635    if let Some(p) = post {
636        sub = sub
637            .and_where(Expr::col(memberpost::Column::PostId).eq(p as i32))
638            .to_owned();
639    }
640    Condition::all().add(Expr::col(membertb::Column::Id).in_subquery(sub))
641}
642
643/// 统计列表组员数
644async fn count_list_members(
645    db: &DatabaseConnection,
646    username: Option<&str>,
647    post: Option<i16>,
648    intern: Option<i16>,
649    email: Option<&str>,
650) -> crate::error::ApiResult<i64> {
651    let q = apply_list_filters(membertb::Entity::find(), username, post, intern, email);
652    let n = q.count(db).await?;
653    Ok(n as i64)
654}
655
656/// 分页查询组员行
657async fn list_members_page(
658    db: &DatabaseConnection,
659    username: Option<&str>,
660    post: Option<i16>,
661    intern: Option<i16>,
662    email: Option<&str>,
663    limit: u64,
664    offset: u64,
665) -> crate::error::ApiResult<Vec<membertb::Model>> {
666    let q = apply_list_filters(membertb::Entity::find(), username, post, intern, email);
667    let rows = q
668        .order_by_desc(membertb::Column::LastSubmitTime)
669        .order_by_desc(membertb::Column::Id)
670        .limit(limit)
671        .offset(offset)
672        .all(db)
673        .await?;
674    Ok(rows)
675}
676
677/// 直接读取 memberpost.postId（不经 post 表）
678async fn load_raw_post_ids(
679    db: &DatabaseConnection,
680    member_id: i32,
681) -> crate::error::ApiResult<Vec<i32>> {
682    let rows = memberpost::Entity::find()
683        .filter(memberpost::Column::MemberId.eq(member_id))
684        .all(db)
685        .await?;
686    Ok(rows.into_iter().map(|r| r.post_id).collect())
687}
688
689/// 批量经 post 表左连接加载岗位 ID
690async fn batch_post_ids_via_post(
691    db: &DatabaseConnection,
692    member_ids: &[i32],
693) -> crate::error::ApiResult<HashMap<i32, Vec<i32>>> {
694    if member_ids.is_empty() {
695        return Ok(HashMap::new());
696    }
697    let mut ids = member_ids.to_vec();
698    ids.sort_unstable();
699    ids.dedup();
700    let placeholders = (1..=ids.len())
701        .map(|i| format!("${i}"))
702        .collect::<Vec<_>>()
703        .join(", ");
704    let sql = format!(
705        r#"SELECT mp."memberId" AS member_id, p.post AS post
706        FROM memberpost mp
707        INNER JOIN post p ON p.post = mp."postId"
708        WHERE mp."memberId" IN ({placeholders})
709        ORDER BY mp."memberId", p.post"#
710    );
711    let values: Vec<Value> = ids.into_iter().map(Value::from).collect();
712    let rows = db
713        .query_all(Statement::from_sql_and_values(
714            DbBackend::Postgres,
715            &sql,
716            values,
717        ))
718        .await?;
719    let mut map: HashMap<i32, Vec<i32>> = HashMap::new();
720    for r in rows {
721        let member_id: i32 = r.try_get("", "member_id")?;
722        let post: i32 = r.try_get("", "post")?;
723        map.entry(member_id).or_default().push(post);
724    }
725    for v in map.values_mut() {
726        v.sort_unstable();
727    }
728    Ok(map)
729}
730
731/// 批量经 post 表内连接加载岗位 ID
732async fn batch_posts_map_inner(
733    db: &DatabaseConnection,
734    member_ids: &[i32],
735) -> crate::error::ApiResult<HashMap<i32, Vec<i32>>> {
736    batch_post_ids_via_post(db, member_ids).await
737}
738
739/// 批量统计常驻数
740async fn batch_station_counts(
741    db: &DatabaseConnection,
742    member_ids: &[i32],
743) -> crate::error::ApiResult<HashMap<i32, i32>> {
744    if member_ids.is_empty() {
745        return Ok(HashMap::new());
746    }
747    let rows = mangastationed::Entity::find()
748        .filter(mangastationed::Column::MemberId.is_in(member_ids.to_vec()))
749        .all(db)
750        .await?;
751    let mut map: HashMap<i32, i32> = HashMap::new();
752    for r in rows {
753        *map.entry(r.member_id).or_insert(0) += 1;
754    }
755    Ok(map)
756}
757
758/// 单条 SQL 加载鉴权快照（membertb + memberpost + post）
759async fn query_auth_snapshot_member(
760    db: &DatabaseConnection,
761    id: i32,
762) -> crate::error::ApiResult<Member> {
763    let sql = r#"SELECT m."Id", m.username, m.password, m.intern, m.email, m."avatarUrl",
764         m."registrationTime", m."lastSubmitTime",
765         string_agg(p.post::text, ',' ORDER BY p.post) AS "postIdList"
766         FROM membertb m
767         LEFT JOIN memberpost mp ON m."Id" = mp."memberId"
768         LEFT JOIN post p ON p.post = mp."postId"
769         WHERE m."Id" = $1
770         GROUP BY m."Id", m.username, m.password, m.intern, m.email, m."avatarUrl",
771                  m."registrationTime", m."lastSubmitTime""#;
772    let row = db
773        .query_one(Statement::from_sql_and_values(
774            DbBackend::Postgres,
775            sql,
776            [Value::from(id)],
777        ))
778        .await?
779        .ok_or_else(|| crate::error::AppError::business("组员不存在喵"))?;
780    let mut post_ids = parse_post_id_list(row.try_get("", "postIdList").ok());
781    post_ids.sort_unstable();
782    Ok(Member {
783        id: row.try_get("", "Id")?,
784        username: row.try_get("", "username").ok(),
785        password: row.try_get("", "password").ok(),
786        intern: row
787            .try_get::<i32>("", "intern")
788            .ok()
789            .map(|v| v as i16)
790            .unwrap_or(0),
791        email: row.try_get("", "email").ok(),
792        avatar_url: row.try_get("", "avatarUrl").ok(),
793        registration_time: row
794            .try_get::<chrono::NaiveDateTime>("", "registrationTime")
795            .ok()
796            .map(crate::utils::legacy_time::naive_db_to_api_utc),
797        last_submit_time: row
798            .try_get::<chrono::NaiveDateTime>("", "lastSubmitTime")
799            .ok()
800            .map(crate::utils::legacy_time::naive_db_to_api_utc),
801        posts: post_ids.iter().map(|p| Post { post: *p }).collect(),
802        post_ids,
803        station_count: None,
804        station_id: None,
805        post: None,
806        status: None,
807        manga_id: None,
808        manga_name: None,
809    })
810}
811
812/// 解析 string_agg 岗位 ID 列表
813fn parse_post_id_list(raw: Option<String>) -> Vec<i32> {
814    raw.map(|s| s.split(',').filter_map(|p| p.trim().parse().ok()).collect())
815        .unwrap_or_default()
816}
817
818/// Model 转 Member
819fn model_to_member(m: membertb::Model) -> Member {
820    Member {
821        id: m.id,
822        username: Some(m.username),
823        password: Some(m.password),
824        intern: m.intern as i16,
825        email: m.email,
826        avatar_url: m.avatar_url,
827        registration_time: Some(crate::utils::legacy_time::naive_db_to_api_utc(
828            m.registration_time,
829        )),
830        last_submit_time: Some(crate::utils::legacy_time::naive_db_to_api_utc(
831            m.last_submit_time,
832        )),
833        posts: vec![],
834        post_ids: vec![],
835        station_count: None,
836        station_id: None,
837        post: None,
838        status: None,
839        manga_id: None,
840        manga_name: None,
841    }
842}
843
844/// 话数岗位列枚举
845#[derive(Clone, Copy)]
846enum EpisodeMemberCol {
847    ProviderId,
848    TranslatorId,
849    ProofreaderId,
850    LettererId,
851    TimerId,
852    ReviewerId,
853}
854
855fn post_episode_column(name: &str) -> crate::error::ApiResult<EpisodeMemberCol> {
856    match name {
857        "translator" => Ok(EpisodeMemberCol::TranslatorId),
858        "proofreader" => Ok(EpisodeMemberCol::ProofreaderId),
859        "letterer" => Ok(EpisodeMemberCol::LettererId),
860        "timer" => Ok(EpisodeMemberCol::TimerId),
861        "reviewer" => Ok(EpisodeMemberCol::ReviewerId),
862        "provider" => Ok(EpisodeMemberCol::ProviderId),
863        _ => Err(crate::error::AppError::business("未知岗位喵")),
864    }
865}
866
867/// 读取话数指定岗位列当前的组员 ID（用于空位/防覆盖校验）
868fn get_episode_member_col(model: &mangaepisodetb::Model, col: EpisodeMemberCol) -> Option<i32> {
869    match col {
870        EpisodeMemberCol::ProviderId => model.provider_id,
871        EpisodeMemberCol::TranslatorId => model.translator_id,
872        EpisodeMemberCol::ProofreaderId => model.proofreader_id,
873        EpisodeMemberCol::LettererId => model.letterer_id,
874        EpisodeMemberCol::TimerId => model.timer_id,
875        EpisodeMemberCol::ReviewerId => model.reviewer_id,
876    }
877}
878
879/// 翻译 / 校对支持多人共同接稿。
880fn is_co_taker_role(role: &str) -> bool {
881    matches!(role, "translator" | "proofreader")
882}
883
884/// 发布链接非空时视为历史话数前置工序已补齐。
885fn has_publish_link(model: &mangaepisodetb::Model) -> bool {
886    model
887        .publish_link
888        .as_deref()
889        .map(str::trim)
890        .map(|link| !link.is_empty())
891        .unwrap_or(false)
892}
893
894/// 非管理员接稿前校验上游工序是否已完成。
895async fn ensure_episode_upstream_ready(
896    db: &DatabaseConnection,
897    model: &mangaepisodetb::Model,
898    role: &str,
899) -> crate::error::ApiResult<()> {
900    if matches!(role, "provider" | "translator") || has_publish_link(model) {
901        return Ok(());
902    }
903    let detail = mangaepisodedetail::Entity::find()
904        .filter(mangaepisodedetail::Column::EpisodeId.eq(model.id))
905        .one(db)
906        .await?
907        .ok_or_else(|| crate::error::AppError::business("话数流程记录不存在喵"))?;
908    let is_animation = mangatb::Entity::find_by_id(model.manga_id)
909        .one(db)
910        .await?
911        .map(|manga| manga.category == YURI_ANIMATION_CATEGORY)
912        .unwrap_or(false);
913    let ready = match role {
914        "proofreader" => detail.translator_update_time.is_some(),
915        "letterer" => !is_animation && detail.proofreader_update_time.is_some(),
916        "timer" => is_animation && detail.proofreader_update_time.is_some(),
917        "reviewer" if is_animation => detail.timer_update_time.is_some(),
918        "reviewer" => detail.letterer_update_time.is_some(),
919        _ => return Err(crate::error::AppError::business("未知岗位喵")),
920    };
921    if ready {
922        Ok(())
923    } else {
924        Err(crate::error::AppError::business(
925            "上游工序未完成，暂不能接稿喵",
926        ))
927    }
928}
929
930/// 插入共同接稿记录（主接稿人仍保留在 mangaepisodetb 单列）。
931async fn insert_episode_role_taker(
932    db: &DatabaseConnection,
933    episode_id: i32,
934    role: &str,
935    member_id: i32,
936) -> crate::error::ApiResult<()> {
937    let existed = episode_role_taker::Entity::find()
938        .filter(episode_role_taker::Column::EpisodeId.eq(episode_id))
939        .filter(episode_role_taker::Column::Role.eq(role))
940        .filter(episode_role_taker::Column::MemberId.eq(member_id))
941        .one(db)
942        .await?;
943    if existed.is_some() {
944        return Err(crate::error::AppError::business("你已经共同接稿了喵"));
945    }
946    episode_role_taker::ActiveModel {
947        episode_id: Set(episode_id),
948        role: Set(role.to_string()),
949        member_id: Set(member_id),
950        setup_time: Set(chrono::Utc::now().naive_utc()),
951        ..Default::default()
952    }
953    .insert(db)
954    .await?;
955    Ok(())
956}
957
958/// 标记共同接稿人独立交稿。
959async fn submit_episode_role_taker(
960    db: &DatabaseConnection,
961    episode_id: i32,
962    role: &str,
963    member_id: i32,
964) -> crate::error::ApiResult<()> {
965    let row = episode_role_taker::Entity::find()
966        .filter(episode_role_taker::Column::EpisodeId.eq(episode_id))
967        .filter(episode_role_taker::Column::Role.eq(role))
968        .filter(episode_role_taker::Column::MemberId.eq(member_id))
969        .one(db)
970        .await?
971        .ok_or_else(|| crate::error::AppError::business("你还没有共同接这份稿喵"))?;
972    let mut am: episode_role_taker::ActiveModel = row.into();
973    am.update_time = Set(Some(chrono::Utc::now().naive_utc()));
974    am.update(db).await?;
975    Ok(())
976}
977
978fn set_episode_member_col(
979    am: &mut mangaepisodetb::ActiveModel,
980    col: EpisodeMemberCol,
981    member_id: i32,
982) -> crate::error::ApiResult<()> {
983    use mangaepisodetb::Column as C;
984    match col {
985        EpisodeMemberCol::ProviderId => am.provider_id = Set(Some(member_id)),
986        EpisodeMemberCol::TranslatorId => am.translator_id = Set(Some(member_id)),
987        EpisodeMemberCol::ProofreaderId => am.proofreader_id = Set(Some(member_id)),
988        EpisodeMemberCol::LettererId => am.letterer_id = Set(Some(member_id)),
989        EpisodeMemberCol::TimerId => am.timer_id = Set(Some(member_id)),
990        EpisodeMemberCol::ReviewerId => am.reviewer_id = Set(Some(member_id)),
991    }
992    let _ = C::Id;
993    Ok(())
994}
995
996enum DetailTimeCol {
997    TranslatorSetup,
998    ProofreaderSetup,
999    LettererSetup,
1000    TimerSetup,
1001    ReviewerSetup,
1002    ProviderSetup,
1003    TranslatorUpdate,
1004    ProofreaderUpdate,
1005    LettererUpdate,
1006    TimerUpdate,
1007    ReviewerUpdate,
1008    ProviderUpdate,
1009}
1010
1011fn post_detail_setup_column(name: &str) -> crate::error::ApiResult<DetailTimeCol> {
1012    match name {
1013        "translator" => Ok(DetailTimeCol::TranslatorSetup),
1014        "proofreader" => Ok(DetailTimeCol::ProofreaderSetup),
1015        "letterer" => Ok(DetailTimeCol::LettererSetup),
1016        "timer" => Ok(DetailTimeCol::TimerSetup),
1017        "reviewer" => Ok(DetailTimeCol::ReviewerSetup),
1018        "provider" => Ok(DetailTimeCol::ProviderSetup),
1019        _ => Err(crate::error::AppError::business("未知岗位喵")),
1020    }
1021}
1022
1023fn post_detail_update_column(name: &str) -> crate::error::ApiResult<DetailTimeCol> {
1024    match name {
1025        "translator" => Ok(DetailTimeCol::TranslatorUpdate),
1026        "proofreader" => Ok(DetailTimeCol::ProofreaderUpdate),
1027        "letterer" => Ok(DetailTimeCol::LettererUpdate),
1028        "timer" => Ok(DetailTimeCol::TimerUpdate),
1029        "reviewer" => Ok(DetailTimeCol::ReviewerUpdate),
1030        "provider" => Ok(DetailTimeCol::ProviderUpdate),
1031        _ => Err(crate::error::AppError::business("未知岗位喵")),
1032    }
1033}
1034
1035/// 更新话数 detail 时间列为当前时间
1036async fn update_episode_detail_time(
1037    db: &DatabaseConnection,
1038    episode_id: i32,
1039    col: DetailTimeCol,
1040) -> crate::error::ApiResult<()> {
1041    use crate::sea_entity::mangaepisodedetail;
1042    let now = chrono::Utc::now().naive_utc();
1043    let existing = mangaepisodedetail::Entity::find()
1044        .filter(mangaepisodedetail::Column::EpisodeId.eq(episode_id))
1045        .one(db)
1046        .await?;
1047    let Some(row) = existing else {
1048        return Ok(());
1049    };
1050    let mut am: mangaepisodedetail::ActiveModel = row.into();
1051    match col {
1052        DetailTimeCol::TranslatorSetup => am.translator_setup_time = Set(Some(now)),
1053        DetailTimeCol::ProofreaderSetup => am.proofreader_setup_time = Set(Some(now)),
1054        DetailTimeCol::LettererSetup => am.letterer_setup_time = Set(Some(now)),
1055        DetailTimeCol::TimerSetup => am.timer_setup_time = Set(Some(now)),
1056        DetailTimeCol::ReviewerSetup => am.reviewer_setup_time = Set(Some(now)),
1057        DetailTimeCol::ProviderSetup => {}
1058        DetailTimeCol::TranslatorUpdate => am.translator_update_time = Set(Some(now)),
1059        DetailTimeCol::ProofreaderUpdate => am.proofreader_update_time = Set(Some(now)),
1060        DetailTimeCol::LettererUpdate => am.letterer_update_time = Set(Some(now)),
1061        DetailTimeCol::TimerUpdate => am.timer_update_time = Set(Some(now)),
1062        DetailTimeCol::ReviewerUpdate => am.reviewer_update_time = Set(Some(now)),
1063        DetailTimeCol::ProviderUpdate => {}
1064    }
1065    am.update(db).await?;
1066    Ok(())
1067}
1068
1069/// 更新组员最近交稿时间。
1070async fn update_member_last_submit_time(
1071    db: &DatabaseConnection,
1072    member_id: i32,
1073) -> crate::error::ApiResult<()> {
1074    membertb::Entity::update_many()
1075        .col_expr(
1076            membertb::Column::LastSubmitTime,
1077            Expr::current_timestamp().into(),
1078        )
1079        .filter(membertb::Column::Id.eq(member_id))
1080        .exec(db)
1081        .await?;
1082    Ok(())
1083}
1084
1085/// 更新话数最近修改时间。
1086async fn touch_episode(db: &DatabaseConnection, episode_id: i32) -> crate::error::ApiResult<()> {
1087    mangaepisodetb::Entity::update_many()
1088        .col_expr(
1089            mangaepisodetb::Column::UpdateTime,
1090            Expr::current_timestamp().into(),
1091        )
1092        .filter(mangaepisodetb::Column::Id.eq(episode_id))
1093        .exec(db)
1094        .await?;
1095    Ok(())
1096}
1097
1098/// 刷新漫画 updateTime
1099async fn touch_manga_by_episode(
1100    db: &DatabaseConnection,
1101    episode_id: i32,
1102) -> crate::error::ApiResult<()> {
1103    let ep = mangaepisodetb::Entity::find_by_id(episode_id)
1104        .one(db)
1105        .await?
1106        .ok_or_else(|| crate::error::AppError::business("话数不存在"))?;
1107    mangatb::Entity::update_many()
1108        .col_expr(
1109            mangatb::Column::UpdateTime,
1110            Expr::current_timestamp().into(),
1111        )
1112        .filter(mangatb::Column::Id.eq(ep.manga_id))
1113        .exec(db)
1114        .await?;
1115    Ok(())
1116}
tdm_server_rust/repository/member_repo.rs

tdm_server_rust/repository/
member_repo.rs
