tdm_server_rust/repository/

manga_list_assemble.rs

//! 漫画列表/卡片/RSS/收藏组装（SeaORM 多表批量加载，对齐 MangaMapper 联查）

use crate::entity::episode::EpisodeType;
use crate::entity::manga::{CollectedMembersVo, GlossaryVo, MangaCardVo, MangaDetailVo};
use crate::entity::rss::{EpisodeRssRow, RssMangaRow, WorkReminderRssRow, RSS_MAX_ITEMS};
use crate::sea_entity::{
    authortb, glossary, magazine, mangaauthor, mangaepisodedetail, mangaepisodetb, mangamagazine,
    mangatb, membertb, workreminder,
};
use chrono::{DateTime, Utc};
use sea_orm::{
    ColumnTrait, Condition, ConnectionTrait, DatabaseConnection, DbBackend, EntityTrait,
    PaginatorTrait, QueryFilter, QueryOrder, QuerySelect, Statement, Value,
};
use std::collections::HashMap;

/// 漫画列表排序字段
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum MangaListSortField {
    /// 最近更新时间
    UpdateTime,
    /// 添加/开坑时间
    SetupTime,
}

/// 漫画列表排序方向
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum MangaListSortOrder {
    /// 升序
    Asc,
    /// 降序
    Desc,
}

/// 漫画列表排序配置
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub struct MangaListSort {
    field: MangaListSortField,
    order: MangaListSortOrder,
}

impl Default for MangaListSort {
    fn default() -> Self {
        Self {
            field: MangaListSortField::UpdateTime,
            order: MangaListSortOrder::Desc,
        }
    }
}

impl MangaListSort {
    /// 从前端查询参数构造排序；未知字段/方向回退到更新时间倒序。
    pub fn from_query(sort_prop: Option<&str>, sort_order: Option<&str>) -> Self {
        let field = match sort_prop.map(normalize_sort_token).as_deref() {
            Some(
                "setuptime" | "createtime" | "createdat" | "addtime" | "addedtime" | "created"
                | "添加时间",
            ) => MangaListSortField::SetupTime,
            Some("updatetime" | "updatedat" | "updated" | "更新时间") | None => {
                MangaListSortField::UpdateTime
            }
            Some(_) => MangaListSortField::UpdateTime,
        };
        let order = match sort_order.map(normalize_sort_token).as_deref() {
            Some("ascending" | "asc") => MangaListSortOrder::Asc,
            Some("descending" | "desc") | None => MangaListSortOrder::Desc,
            Some(_) => MangaListSortOrder::Desc,
        };
        Self { field, order }
    }

    /// 规范化后的缓存字段名。
    pub fn cache_field(self) -> &'static str {
        match self.field {
            MangaListSortField::UpdateTime => "updateTime",
            MangaListSortField::SetupTime => "setupTime",
        }
    }

    /// 规范化后的缓存方向名。
    pub fn cache_order(self) -> &'static str {
        match self.order {
            MangaListSortOrder::Asc => "ascending",
            MangaListSortOrder::Desc => "descending",
        }
    }
}

fn normalize_sort_token(value: &str) -> String {
    value
        .trim()
        .replace(['_', '-', ' '], "")
        .to_ascii_lowercase()
}

/// 列表关联数据缓存
struct MangaListContext {
    /// mangaId -> (type1 authorId, authorName)
    author1: HashMap<i32, (i32, String)>,
    /// mangaId -> (type2 authorId, authorName)
    author2: HashMap<i32, (i32, String)>,
    /// mangaId -> (magazineId, magazineName)
    magazines: HashMap<i32, (i32, String)>,
    /// mangaId -> 最新话详情
    latest_details: HashMap<i32, mangaepisodedetail::Model>,
}

/// 统计符合条件的漫画总数
pub async fn count_list(
    db: &DatabaseConnection,
    manga_tran_name: Option<&str>,
    manga_ori_name: Option<&str>,
    category: Option<i16>,
    manga_status: Option<i16>,
    author_name: Option<&str>,
    magazine_name: Option<&str>,
) -> crate::error::ApiResult<i64> {
    let filter = list_filter(
        db,
        manga_tran_name,
        manga_ori_name,
        category,
        manga_status,
        author_name,
        magazine_name,
    )
    .await?;
    let n = mangatb::Entity::find().filter(filter).count(db).await?;
    Ok(n as i64)
}

/// 条件分页查询漫画卡片
pub async fn list_page(
    db: &DatabaseConnection,
    manga_tran_name: Option<&str>,
    manga_ori_name: Option<&str>,
    category: Option<i16>,
    manga_status: Option<i16>,
    author_name: Option<&str>,
    magazine_name: Option<&str>,
    limit: i32,
    offset: i32,
) -> crate::error::ApiResult<Vec<MangaCardVo>> {
    list_page_sorted(
        db,
        manga_tran_name,
        manga_ori_name,
        category,
        manga_status,
        author_name,
        magazine_name,
        MangaListSort::default(),
        limit,
        offset,
    )
    .await
}

/// 条件分页查询漫画卡片（支持后端排序，避免前端只排当前页）
pub async fn list_page_sorted(
    db: &DatabaseConnection,
    manga_tran_name: Option<&str>,
    manga_ori_name: Option<&str>,
    category: Option<i16>,
    manga_status: Option<i16>,
    author_name: Option<&str>,
    magazine_name: Option<&str>,
    sort: MangaListSort,
    limit: i32,
    offset: i32,
) -> crate::error::ApiResult<Vec<MangaCardVo>> {
    let filter = list_filter(
        db,
        manga_tran_name,
        manga_ori_name,
        category,
        manga_status,
        author_name,
        magazine_name,
    )
    .await?;
    let query = mangatb::Entity::find().filter(filter);
    let query = match (sort.field, sort.order) {
        (MangaListSortField::UpdateTime, MangaListSortOrder::Desc) => query
            .order_by_desc(mangatb::Column::UpdateTime)
            .order_by_desc(mangatb::Column::Id),
        (MangaListSortField::UpdateTime, MangaListSortOrder::Asc) => query
            .order_by_asc(mangatb::Column::UpdateTime)
            .order_by_asc(mangatb::Column::Id),
        (MangaListSortField::SetupTime, MangaListSortOrder::Desc) => query
            .order_by_desc(mangatb::Column::SetupTime)
            .order_by_desc(mangatb::Column::Id),
        (MangaListSortField::SetupTime, MangaListSortOrder::Asc) => query
            .order_by_asc(mangatb::Column::SetupTime)
            .order_by_asc(mangatb::Column::Id),
    };
    let rows = query
        .limit(limit.max(0) as u64)
        .offset(offset.max(0) as u64)
        .all(db)
        .await?;
    assemble_manga_cards(db, &rows, true).await
}

/// 按作者 ID 查询漫画卡片
pub async fn list_by_author(
    db: &DatabaseConnection,
    author_id: i32,
) -> crate::error::ApiResult<Vec<MangaCardVo>> {
    let (_, cards) = list_by_author_page(db, author_id, 1, i32::MAX).await?;
    Ok(cards)
}

/// 按作者 ID SQL 真分页（COUNT 与当前页并行）
pub async fn list_by_author_page(
    db: &DatabaseConnection,
    author_id: i32,
    page: i32,
    page_size: i32,
) -> crate::error::ApiResult<(i64, Vec<MangaCardVo>)> {
    let page = page.max(1);
    let page_size = page_size.max(1);
    let offset = ((page - 1) * page_size) as i64;
    let (total, rows) = tokio::try_join!(
        count_by_author(db, author_id),
        fetch_by_author_page(db, author_id, page_size, offset),
    )?;
    if rows.is_empty() {
        return Ok((total, vec![]));
    }
    let cards = assemble_manga_cards(db, &rows, true).await?;
    Ok((total, cards))
}

/// 统计作者关联漫画总数
async fn count_by_author(db: &DatabaseConnection, author_id: i32) -> crate::error::ApiResult<i64> {
    let sql = r#"SELECT COUNT(DISTINCT m."Id")::bigint AS cnt
        FROM mangaauthor ma
        INNER JOIN mangatb m ON m."Id" = ma."mangaId"
        WHERE ma."authorId" = $1"#;
    let row = db
        .query_one(Statement::from_sql_and_values(
            DbBackend::Postgres,
            sql,
            [Value::from(author_id)],
        ))
        .await?
        .ok_or_else(|| crate::error::AppError::business("统计作者漫画失败喵"))?;
    Ok(row.try_get("", "cnt")?)
}

/// 按 updateTime 倒序拉取作者漫画当前页
async fn fetch_by_author_page(
    db: &DatabaseConnection,
    author_id: i32,
    page_size: i32,
    offset: i64,
) -> crate::error::ApiResult<Vec<mangatb::Model>> {
    let sql = r#"SELECT DISTINCT m."Id", m."mangaTranName", m."mangaOriName", m.image, m.category,
        m."mangaStatus", m."setupTime", m."updateTime", m.link, m.introduction
        FROM mangaauthor ma
        INNER JOIN mangatb m ON m."Id" = ma."mangaId"
        WHERE ma."authorId" = $1
        ORDER BY m."updateTime" DESC
        LIMIT $2 OFFSET $3"#;
    let rows = db
        .query_all(Statement::from_sql_and_values(
            DbBackend::Postgres,
            sql,
            [
                Value::from(author_id),
                Value::from(page_size),
                Value::from(offset),
            ],
        ))
        .await?;
    rows.into_iter()
        .map(row_to_mangatb_model)
        .collect::<crate::error::ApiResult<Vec<_>>>()
}

/// 按杂志 ID 查询漫画卡片
pub async fn list_by_magazine(
    db: &DatabaseConnection,
    magazine_id: i32,
) -> crate::error::ApiResult<Vec<MangaCardVo>> {
    let (_, cards) = list_by_magazine_page(db, magazine_id, 1, i32::MAX).await?;
    Ok(cards)
}

/// 按杂志 ID SQL 真分页（COUNT 与当前页并行）
pub async fn list_by_magazine_page(
    db: &DatabaseConnection,
    magazine_id: i32,
    page: i32,
    page_size: i32,
) -> crate::error::ApiResult<(i64, Vec<MangaCardVo>)> {
    let page = page.max(1);
    let page_size = page_size.max(1);
    let offset = ((page - 1) * page_size) as i64;
    let (total, rows) = tokio::try_join!(
        count_by_magazine(db, magazine_id),
        fetch_by_magazine_page(db, magazine_id, page_size, offset),
    )?;
    if rows.is_empty() {
        return Ok((total, vec![]));
    }
    let cards = assemble_manga_cards(db, &rows, true).await?;
    Ok((total, cards))
}

/// 统计杂志关联漫画总数
async fn count_by_magazine(
    db: &DatabaseConnection,
    magazine_id: i32,
) -> crate::error::ApiResult<i64> {
    let sql = r#"SELECT COUNT(DISTINCT m."Id")::bigint AS cnt
        FROM mangamagazine mm
        INNER JOIN mangatb m ON m."Id" = mm."mangaId"
        WHERE mm."magazineId" = $1 AND mm."mangaId" IS NOT NULL"#;
    let row = db
        .query_one(Statement::from_sql_and_values(
            DbBackend::Postgres,
            sql,
            [Value::from(magazine_id)],
        ))
        .await?
        .ok_or_else(|| crate::error::AppError::business("统计杂志漫画失败喵"))?;
    Ok(row.try_get("", "cnt")?)
}

/// 按 updateTime 倒序拉取杂志漫画当前页
async fn fetch_by_magazine_page(
    db: &DatabaseConnection,
    magazine_id: i32,
    page_size: i32,
    offset: i64,
) -> crate::error::ApiResult<Vec<mangatb::Model>> {
    let sql = r#"SELECT DISTINCT m."Id", m."mangaTranName", m."mangaOriName", m.image, m.category,
        m."mangaStatus", m."setupTime", m."updateTime", m.link, m.introduction
        FROM mangamagazine mm
        INNER JOIN mangatb m ON m."Id" = mm."mangaId"
        WHERE mm."magazineId" = $1 AND mm."mangaId" IS NOT NULL
        ORDER BY m."updateTime" DESC
        LIMIT $2 OFFSET $3"#;
    let rows = db
        .query_all(Statement::from_sql_and_values(
            DbBackend::Postgres,
            sql,
            [
                Value::from(magazine_id),
                Value::from(page_size),
                Value::from(offset),
            ],
        ))
        .await?;
    rows.into_iter()
        .map(row_to_mangatb_model)
        .collect::<crate::error::ApiResult<Vec<_>>>()
}

/// 按 ID 查询漫画卡片
pub async fn get_manga_card_by_id(
    db: &DatabaseConnection,
    id: i32,
) -> crate::error::ApiResult<Option<MangaCardVo>> {
    let row = mangatb::Entity::find_by_id(id).one(db).await?;
    let Some(m) = row else {
        return Ok(None);
    };
    let cards = assemble_manga_cards(db, std::slice::from_ref(&m), true).await?;
    Ok(cards.into_iter().next())
}

/// 按 ID 查询漫画详情
pub async fn get_manga_detail_by_id(
    db: &DatabaseConnection,
    id: i32,
) -> crate::error::ApiResult<Option<MangaDetailVo>> {
    let row = mangatb::Entity::find_by_id(id).one(db).await?;
    let Some(m) = row else {
        return Ok(None);
    };
    let ctx = load_list_context(db, std::slice::from_ref(&m), true, false).await?;
    Ok(Some(to_manga_detail(&m, &ctx)))
}

/// 按 ID 查询漫画响应
pub async fn get_manga_response_by_id(
    db: &DatabaseConnection,
    id: i32,
) -> crate::error::ApiResult<Option<crate::entity::manga::MangaResponse>> {
    let row = mangatb::Entity::find_by_id(id).one(db).await?;
    let Some(m) = row else {
        return Ok(None);
    };
    let ctx = load_list_context(db, std::slice::from_ref(&m), true, true).await?;
    Ok(Some(to_manga_response(&m, &ctx)))
}

/// 批量按 ID 查询漫画响应
pub async fn get_manga_responses_by_ids(
    db: &DatabaseConnection,
    ids: &[i32],
) -> crate::error::ApiResult<HashMap<i32, crate::entity::manga::MangaResponse>> {
    if ids.is_empty() {
        return Ok(HashMap::new());
    }
    let rows = mangatb::Entity::find()
        .filter(mangatb::Column::Id.is_in(ids.to_vec()))
        .all(db)
        .await?;
    let ctx = load_list_context(db, &rows, true, true).await?;
    Ok(rows
        .into_iter()
        .map(|m| (m.id, to_manga_response(&m, &ctx)))
        .collect())
}

/// 组员收藏列表（全量，供 parity 对照）
pub async fn get_collect_list(
    db: &DatabaseConnection,
    member_id: i32,
) -> crate::error::ApiResult<Vec<MangaCardVo>> {
    let (_, cards) = get_collect_list_page(db, member_id, 1, i32::MAX).await?;
    Ok(cards)
}

/// 组员收藏列表 SQL 真分页
///
/// COUNT 与当前页漫画行并行查询，仅对当前页调用 `assemble_manga_cards`。
pub async fn get_collect_list_page(
    db: &DatabaseConnection,
    member_id: i32,
    page: i32,
    page_size: i32,
) -> crate::error::ApiResult<(i64, Vec<MangaCardVo>)> {
    let page = page.max(1);
    let page_size = page_size.max(1);
    let offset = ((page - 1) * page_size) as i64;
    let (total, rows) = tokio::try_join!(
        count_collect_list(db, member_id),
        fetch_collect_list_page(db, member_id, page_size, offset),
    )?;
    if rows.is_empty() {
        return Ok((total, vec![]));
    }
    let cards = assemble_manga_cards(db, &rows, false).await?;
    Ok((total, cards))
}

/// 统计组员收藏漫画总数
async fn count_collect_list(
    db: &DatabaseConnection,
    member_id: i32,
) -> crate::error::ApiResult<i64> {
    let sql = r#"SELECT COUNT(*)::bigint AS cnt
        FROM mangacollect c
        INNER JOIN mangatb m ON m."Id" = c."mangaId"
        WHERE c."memberId" = $1"#;
    let row = db
        .query_one(Statement::from_sql_and_values(
            DbBackend::Postgres,
            sql,
            [Value::from(member_id)],
        ))
        .await?
        .ok_or_else(|| crate::error::AppError::business("统计收藏失败喵"))?;
    Ok(row.try_get("", "cnt")?)
}

/// 按 updateTime 倒序拉取收藏漫画当前页行
async fn fetch_collect_list_page(
    db: &DatabaseConnection,
    member_id: i32,
    page_size: i32,
    offset: i64,
) -> crate::error::ApiResult<Vec<mangatb::Model>> {
    let sql = r#"SELECT m."Id", m."mangaTranName", m."mangaOriName", m.image, m.category,
        m."mangaStatus", m."setupTime", m."updateTime", m.link, m.introduction
        FROM mangacollect c
        INNER JOIN mangatb m ON m."Id" = c."mangaId"
        WHERE c."memberId" = $1
        ORDER BY m."updateTime" DESC
        LIMIT $2 OFFSET $3"#;
    let rows = db
        .query_all(Statement::from_sql_and_values(
            DbBackend::Postgres,
            sql,
            [
                Value::from(member_id),
                Value::from(page_size),
                Value::from(offset),
            ],
        ))
        .await?;
    rows.into_iter().map(row_to_mangatb_model).collect()
}

/// 将收藏分页查询行映射为 mangatb::Model
fn row_to_mangatb_model(row: sea_orm::QueryResult) -> crate::error::ApiResult<mangatb::Model> {
    Ok(mangatb::Model {
        id: row.try_get("", "Id")?,
        manga_tran_name: row.try_get("", "mangaTranName")?,
        manga_ori_name: row.try_get("", "mangaOriName")?,
        image: row.try_get("", "image")?,
        category: row.try_get("", "category")?,
        manga_status: row.try_get("", "mangaStatus")?,
        setup_time: row.try_get("", "setupTime")?,
        update_time: row.try_get("", "updateTime")?,
        link: row.try_get("", "link").ok(),
        introduction: row.try_get("", "introduction").ok(),
    })
}

/// 查询收藏该漫画的组员
pub async fn get_collected_members(
    db: &DatabaseConnection,
    manga_id: i32,
) -> crate::error::ApiResult<Vec<CollectedMembersVo>> {
    let sql = r#"SELECT m."Id" AS id,
            m.username,
            m.intern,
            m."registrationTime" AS registration_time,
            m."lastSubmitTime" AS last_submit_time,
            COUNT(ms."Id")::int AS station_count,
            MIN(c."Id") AS first_collect_id
        FROM mangacollect c
        INNER JOIN membertb m ON m."Id" = c."memberId"
        LEFT JOIN mangastationed ms ON ms."memberId" = m."Id"
        WHERE c."mangaId" = $1
        GROUP BY m."Id", m.username, m.intern, m."registrationTime", m."lastSubmitTime"
        ORDER BY first_collect_id"#;
    let rows = db
        .query_all(Statement::from_sql_and_values(
            DbBackend::Postgres,
            sql,
            [Value::from(manga_id)],
        ))
        .await?;
    rows.into_iter()
        .map(|row| {
            let id: i32 = row.try_get("", "id")?;
            let username: String = row.try_get("", "username")?;
            let intern: i32 = row.try_get("", "intern")?;
            let registration_time: chrono::NaiveDateTime = row.try_get("", "registration_time")?;
            let last_submit_time: chrono::NaiveDateTime = row.try_get("", "last_submit_time")?;
            let station_count: i32 = row.try_get("", "station_count")?;
            Ok(CollectedMembersVo {
                id,
                username: Some(username),
                intern: intern as i16,
                posts: vec![],
                station_count: (station_count > 0).then_some(station_count),
                registration_time: dt_legacy(registration_time),
                last_submit_time: dt_legacy(last_submit_time),
            })
        })
        .collect()
}

/// 最新开坑漫画 RSS
pub async fn get_manga_rss(db: &DatabaseConnection) -> crate::error::ApiResult<Vec<RssMangaRow>> {
    let rows = mangatb::Entity::find()
        .order_by_desc(mangatb::Column::SetupTime)
        .limit(RSS_MAX_ITEMS)
        .all(db)
        .await?;
    assemble_rss_manga(db, &rows, false).await
}

/// 最近更新漫画 RSS
pub async fn get_manga_updated_rss(
    db: &DatabaseConnection,
) -> crate::error::ApiResult<Vec<RssMangaRow>> {
    let rows = mangatb::Entity::find()
        .order_by_desc(mangatb::Column::UpdateTime)
        .limit(RSS_MAX_ITEMS)
        .all(db)
        .await?;
    assemble_rss_manga(db, &rows, true).await
}

/// 将数据库话数分类转换为 RSS 强类型；未知旧值按正篇兼容处理。
fn rss_episode_type(value: &str) -> EpisodeType {
    if value == EpisodeType::Extra.as_str() {
        EpisodeType::Extra
    } else {
        EpisodeType::Main
    }
}

/// 话数 RSS
pub async fn get_episode_rss(
    db: &DatabaseConnection,
) -> crate::error::ApiResult<Vec<EpisodeRssRow>> {
    let episodes = mangaepisodetb::Entity::find()
        .order_by_desc(mangaepisodetb::Column::Id)
        .limit(RSS_MAX_ITEMS)
        .all(db)
        .await?;
    if episodes.is_empty() {
        return Ok(vec![]);
    }
    let manga_ids: Vec<i32> = episodes.iter().map(|e| e.manga_id).collect();
    let mangas: HashMap<i32, mangatb::Model> = mangatb::Entity::find()
        .filter(mangatb::Column::Id.is_in(manga_ids))
        .all(db)
        .await?
        .into_iter()
        .map(|m| (m.id, m))
        .collect();
    let mut member_ids = Vec::new();
    for ep in &episodes {
        if let Some(id) = ep.provider_id {
            member_ids.push(id);
        }
        if let Some(id) = ep.translator_id {
            member_ids.push(id);
        }
    }
    member_ids.sort_unstable();
    member_ids.dedup();
    let members: HashMap<i32, membertb::Model> = if member_ids.is_empty() {
        HashMap::new()
    } else {
        membertb::Entity::find()
            .filter(membertb::Column::Id.is_in(member_ids))
            .all(db)
            .await?
            .into_iter()
            .map(|m| (m.id, m))
            .collect()
    };
    Ok(episodes
        .iter()
        .map(|ep| {
            let manga = mangas.get(&ep.manga_id);
            let provider = ep.provider_id.and_then(|id| members.get(&id));
            let translator = ep.translator_id.and_then(|id| members.get(&id));
            EpisodeRssRow {
                id: ep.id,
                manga_id: ep.manga_id,
                manga_episode: Some(ep.manga_episode.clone()),
                manga_episode_name: ep.manga_episode_name.clone(),
                episode_type: rss_episode_type(&ep.episode_type),
                setup_time: dt_legacy(ep.setup_time),
                manga_name: manga.map(|m| m.manga_tran_name.clone()),
                manga_ori_name: manga.map(|m| m.manga_ori_name.clone()),
                category: manga.map(|m| m.category as i16),
                manga_status: manga.map(|m| m.manga_status as i16),
                manga_setup_time: manga.and_then(|m| dt_legacy(m.setup_time)),
                image: manga.map(|m| m.image.clone()),
                provider_name: provider.map(|m| m.username.clone()),
                translator_name: translator.map(|m| m.username.clone()),
                intern: translator.map(|m| m.intern as i16),
                email: translator.and_then(|m| m.email.clone()),
                publish_link: ep.publish_link.clone(),
            }
        })
        .collect())
}

/// 交稿提醒 RSS
pub async fn get_work_reminder_rss(
    db: &DatabaseConnection,
    post_name: &str,
) -> crate::error::ApiResult<Vec<WorkReminderRssRow>> {
    let reminders = workreminder::Entity::find()
        .filter(workreminder::Column::MyName.eq(post_name))
        .order_by_desc(workreminder::Column::Id)
        .limit(RSS_MAX_ITEMS)
        .all(db)
        .await?;
    if reminders.is_empty() {
        return Ok(vec![]);
    }
    let ep_ids: Vec<i32> = reminders.iter().filter_map(|r| r.episode_id).collect();
    let episodes: HashMap<i32, mangaepisodetb::Model> = if ep_ids.is_empty() {
        HashMap::new()
    } else {
        mangaepisodetb::Entity::find()
            .filter(mangaepisodetb::Column::Id.is_in(ep_ids))
            .all(db)
            .await?
            .into_iter()
            .map(|e| (e.id, e))
            .collect()
    };
    let manga_ids: Vec<i32> = episodes.values().map(|e| e.manga_id).collect();
    let mangas: HashMap<i32, mangatb::Model> = if manga_ids.is_empty() {
        HashMap::new()
    } else {
        mangatb::Entity::find()
            .filter(mangatb::Column::Id.is_in(manga_ids))
            .all(db)
            .await?
            .into_iter()
            .map(|m| (m.id, m))
            .collect()
    };
    let mut member_ids = Vec::new();
    for r in &reminders {
        if let Some(id) = r.member_id {
            member_ids.push(id);
        }
        if let Some(ep_id) = r.episode_id {
            if let Some(ep) = episodes.get(&ep_id) {
                if let Some(my_name) = r.my_name.as_deref() {
                    if let Some(id) = next_post_member_id(ep, my_name) {
                        member_ids.push(id);
                    }
                }
            }
        }
    }
    member_ids.sort_unstable();
    member_ids.dedup();
    let members: HashMap<i32, membertb::Model> = if member_ids.is_empty() {
        HashMap::new()
    } else {
        membertb::Entity::find()
            .filter(membertb::Column::Id.is_in(member_ids))
            .all(db)
            .await?
            .into_iter()
            .map(|m| (m.id, m))
            .collect()
    };
    Ok(reminders
        .into_iter()
        .filter_map(|r| {
            let ep_id = r.episode_id?;
            let ep = episodes.get(&ep_id);
            let manga = ep.and_then(|e| mangas.get(&e.manga_id));
            let mem_now = r.member_id.and_then(|id| members.get(&id));
            let my_name = r.my_name.clone().unwrap_or_default();
            let next_id = ep.and_then(|e| next_post_member_id(e, &my_name));
            let mem_next = next_id.and_then(|id| members.get(&id));
            Some(WorkReminderRssRow {
                episode_id: ep_id,
                manga_id: manga.map(|m| m.id).unwrap_or(0),
                manga_episode: ep.map(|e| e.manga_episode.clone()),
                manga_episode_name: ep.and_then(|e| e.manga_episode_name.clone()),
                episode_type: ep
                    .map(|e| rss_episode_type(&e.episode_type))
                    .unwrap_or_default(),
                setup_time: ep.and_then(|e| dt_legacy(e.setup_time)),
                manga_name: manga.map(|m| m.manga_tran_name.clone()),
                manga_ori_name: manga.map(|m| m.manga_ori_name.clone()),
                category: manga.map(|m| m.category as i16),
                manga_status: manga.map(|m| m.manga_status as i16),
                manga_setup_time: manga.and_then(|m| dt_legacy(m.setup_time)),
                image: manga.map(|m| m.image.clone()),
                my_name,
                username_now: mem_now.map(|m| m.username.clone()),
                intern_now: mem_now.map(|m| m.intern as i16),
                email_now: mem_now.and_then(|m| m.email.clone()),
                username: mem_next.map(|m| m.username.clone()),
                intern: mem_next.map(|m| m.intern as i16),
                email: mem_next.and_then(|m| m.email.clone()),
            })
        })
        .collect())
}

/// 术语行映射（含更新人名）
pub fn glossary_to_vo(g: glossary::Model, update_member_name: Option<String>) -> GlossaryVo {
    GlossaryVo {
        id: g.id,
        title: g.title,
        content: g.content,
        image: g.image,
        r#type: g.r#type.map(|v| v as i16),
        manga_id: g.manga_id.map(|v| v as i16),
        first: g.first,
        update_time: dt_legacy_opt(g.update_time),
        update_by: g.update_by,
        update_member_name,
    }
}

/// 构建列表筛选条件
async fn list_filter(
    db: &DatabaseConnection,
    manga_tran_name: Option<&str>,
    manga_ori_name: Option<&str>,
    category: Option<i16>,
    manga_status: Option<i16>,
    author_name: Option<&str>,
    magazine_name: Option<&str>,
) -> crate::error::ApiResult<Condition> {
    let mut cond = Condition::all();
    if let Some(v) = manga_tran_name {
        cond = cond.add(mangatb::Column::MangaTranName.like(format!("%{v}%")));
    }
    if let Some(v) = manga_ori_name {
        cond = cond.add(mangatb::Column::MangaOriName.eq(v));
    }
    if let Some(v) = category {
        cond = cond.add(mangatb::Column::Category.eq(v as i32));
    }
    if let Some(v) = manga_status {
        cond = cond.add(mangatb::Column::MangaStatus.eq(v as i32));
    }
    if let Some(name) = author_name {
        let authors = authortb::Entity::find()
            .filter(authortb::Column::AuthorName.eq(name))
            .all(db)
            .await?;
        if authors.is_empty() {
            return Ok(Condition::all().add(mangatb::Column::Id.eq(-1)));
        }
        let author_ids: Vec<i32> = authors.iter().map(|a| a.id).collect();
        let links = mangaauthor::Entity::find()
            .filter(mangaauthor::Column::AuthorId.is_in(author_ids))
            .all(db)
            .await?;
        if links.is_empty() {
            return Ok(Condition::all().add(mangatb::Column::Id.eq(-1)));
        }
        let manga_ids: Vec<i32> = links.iter().map(|l| l.manga_id).collect();
        cond = cond.add(mangatb::Column::Id.is_in(manga_ids));
    }
    if let Some(name) = magazine_name {
        let mags = magazine::Entity::find()
            .filter(magazine::Column::MagazineName.eq(name))
            .all(db)
            .await?;
        if mags.is_empty() {
            return Ok(Condition::all().add(mangatb::Column::Id.eq(-1)));
        }
        let mag_ids: Vec<i32> = mags.iter().map(|m| m.id).collect();
        let links = mangamagazine::Entity::find()
            .filter(mangamagazine::Column::MagazineId.is_in(mag_ids))
            .all(db)
            .await?;
        if links.is_empty() {
            return Ok(Condition::all().add(mangatb::Column::Id.eq(-1)));
        }
        let manga_ids: Vec<i32> = links.iter().filter_map(|l| l.manga_id).collect();
        cond = cond.add(mangatb::Column::Id.is_in(manga_ids));
    }
    Ok(cond)
}

/// 批量组装漫画卡片
async fn assemble_manga_cards(
    db: &DatabaseConnection,
    mangas: &[mangatb::Model],
    with_magazine: bool,
) -> crate::error::ApiResult<Vec<MangaCardVo>> {
    if mangas.is_empty() {
        return Ok(vec![]);
    }
    let ctx = load_list_context(db, mangas, with_magazine, true).await?;
    Ok(mangas.iter().map(|m| to_manga_card(m, &ctx)).collect())
}

/// 批量组装 RSS 漫画行
async fn assemble_rss_manga(
    db: &DatabaseConnection,
    mangas: &[mangatb::Model],
    include_update_time: bool,
) -> crate::error::ApiResult<Vec<RssMangaRow>> {
    if mangas.is_empty() {
        return Ok(vec![]);
    }
    let ctx = load_list_context(db, mangas, false, false).await?;
    Ok(mangas
        .iter()
        .map(|m| to_rss_manga(m, &ctx, include_update_time))
        .collect())
}

/// 加载列表关联数据
async fn load_list_context(
    db: &DatabaseConnection,
    mangas: &[mangatb::Model],
    with_magazine: bool,
    with_latest_detail: bool,
) -> crate::error::ApiResult<MangaListContext> {
    if mangas.is_empty() {
        return Ok(MangaListContext {
            author1: HashMap::new(),
            author2: HashMap::new(),
            magazines: HashMap::new(),
            latest_details: HashMap::new(),
        });
    }
    let manga_ids: Vec<i32> = mangas.iter().map(|m| m.id).collect();
    let author_links = mangaauthor::Entity::find()
        .filter(mangaauthor::Column::MangaId.is_in(manga_ids.clone()))
        .all(db)
        .await?;
    let author_ids: Vec<i32> = author_links.iter().map(|l| l.author_id).collect();
    let authors: HashMap<i32, authortb::Model> = if author_ids.is_empty() {
        HashMap::new()
    } else {
        authortb::Entity::find()
            .filter(authortb::Column::Id.is_in(author_ids))
            .all(db)
            .await?
            .into_iter()
            .map(|a| (a.id, a))
            .collect()
    };
    let mut author1 = HashMap::new();
    let mut author2 = HashMap::new();
    for link in author_links {
        let Some(author) = authors.get(&link.author_id) else {
            continue;
        };
        let entry = (author.id, author.author_name.clone());
        if link.r#type == 1 {
            author1.insert(link.manga_id, entry);
        } else if link.r#type == 2 {
            author2.insert(link.manga_id, entry);
        }
    }
    let magazines = if with_magazine {
        load_magazines(db, &manga_ids).await?
    } else {
        HashMap::new()
    };
    let latest_details = if with_latest_detail {
        load_latest_details(db, &manga_ids).await?
    } else {
        HashMap::new()
    };
    Ok(MangaListContext {
        author1,
        author2,
        magazines,
        latest_details,
    })
}

/// 批量加载杂志关联
async fn load_magazines(
    db: &DatabaseConnection,
    manga_ids: &[i32],
) -> crate::error::ApiResult<HashMap<i32, (i32, String)>> {
    let links = mangamagazine::Entity::find()
        .filter(mangamagazine::Column::MangaId.is_in(manga_ids.to_vec()))
        .all(db)
        .await?;
    let mag_ids: Vec<i32> = links.iter().filter_map(|l| l.magazine_id).collect();
    let mags: HashMap<i32, magazine::Model> = if mag_ids.is_empty() {
        HashMap::new()
    } else {
        magazine::Entity::find()
            .filter(magazine::Column::Id.is_in(mag_ids))
            .all(db)
            .await?
            .into_iter()
            .map(|m| (m.id, m))
            .collect()
    };
    let mut out = HashMap::new();
    for link in links {
        let (Some(manga_id), Some(mag_id)) = (link.manga_id, link.magazine_id) else {
            continue;
        };
        if let Some(mag) = mags.get(&mag_id) {
            out.insert(manga_id, (mag.id, mag.magazine_name.clone()));
        }
    }
    Ok(out)
}

/// 批量加载各漫画最新话详情（DISTINCT ON 每漫画一条）
async fn load_latest_details(
    db: &DatabaseConnection,
    manga_ids: &[i32],
) -> crate::error::ApiResult<HashMap<i32, mangaepisodedetail::Model>> {
    if manga_ids.is_empty() {
        return Ok(HashMap::new());
    }
    let placeholders: String = (1..=manga_ids.len())
        .map(|i| format!("${i}"))
        .collect::<Vec<_>>()
        .join(", ");
    let sql = format!(
        r#"SELECT DISTINCT ON (me."mangaId") me."mangaId", me."Id" AS "episodeId"
        FROM mangaepisodetb me
        WHERE me."mangaId" IN ({placeholders})
        ORDER BY me."mangaId", me."Id" DESC"#
    );
    let values: Vec<Value> = manga_ids.iter().copied().map(Value::from).collect();
    let rows = db
        .query_all(Statement::from_sql_and_values(
            DbBackend::Postgres,
            &sql,
            values,
        ))
        .await?;
    let mut latest_ep: HashMap<i32, i32> = HashMap::new();
    for row in rows {
        let manga_id: i32 = row.try_get("", "mangaId")?;
        let episode_id: i32 = row.try_get("", "episodeId")?;
        latest_ep.insert(manga_id, episode_id);
    }
    let ep_ids: Vec<i32> = latest_ep.values().copied().collect();
    if ep_ids.is_empty() {
        return Ok(HashMap::new());
    }
    let details: HashMap<i32, mangaepisodedetail::Model> = mangaepisodedetail::Entity::find()
        .filter(mangaepisodedetail::Column::EpisodeId.is_in(ep_ids))
        .all(db)
        .await?
        .into_iter()
        .map(|d| (d.episode_id, d))
        .collect();
    let mut out = HashMap::new();
    for (manga_id, ep_id) in latest_ep {
        if let Some(d) = details.get(&ep_id) {
            out.insert(manga_id, d.clone());
        }
    }
    Ok(out)
}

/// 组装 MangaCardVo
fn to_manga_card(m: &mangatb::Model, ctx: &MangaListContext) -> MangaCardVo {
    let med = ctx.latest_details.get(&m.id);
    let a1 = ctx.author1.get(&m.id);
    let a2 = ctx.author2.get(&m.id);
    MangaCardVo {
        id: m.id,
        manga_tran_name: Some(m.manga_tran_name.clone()),
        manga_ori_name: Some(m.manga_ori_name.clone()),
        image: Some(m.image.clone()),
        category: Some(m.category as i16),
        manga_status: Some(m.manga_status as i16),
        setup_time: dt_legacy(m.setup_time),
        update_time: dt_legacy(m.update_time),
        author_name: a1.map(|(_, n)| n.clone()),
        author_name2: a2.map(|(_, n)| n.clone()),
        author_id2: a2.map(|(id, _)| *id),
        magazine_name: ctx.magazines.get(&m.id).map(|(_, n)| n.clone()),
        translator_setup_time: med.and_then(|d| dt_legacy_opt(d.translator_setup_time)),
        translator_update_time: med.and_then(|d| dt_legacy_opt(d.translator_update_time)),
        proofreader_setup_time: med.and_then(|d| dt_legacy_opt(d.proofreader_setup_time)),
        proofreader_update_time: med.and_then(|d| dt_legacy_opt(d.proofreader_update_time)),
        letterer_setup_time: med.and_then(|d| dt_legacy_opt(d.letterer_setup_time)),
        letterer_update_time: med.and_then(|d| dt_legacy_opt(d.letterer_update_time)),
        reviewer_setup_time: med.and_then(|d| dt_legacy_opt(d.reviewer_setup_time)),
        reviewer_update_time: med.and_then(|d| dt_legacy_opt(d.reviewer_update_time)),
        timer_setup_time: med.and_then(|d| dt_legacy_opt(d.timer_setup_time)),
        timer_update_time: med.and_then(|d| dt_legacy_opt(d.timer_update_time)),
    }
}

/// 组装 MangaDetailVo
fn to_manga_detail(m: &mangatb::Model, ctx: &MangaListContext) -> MangaDetailVo {
    let a1 = ctx.author1.get(&m.id);
    let a2 = ctx.author2.get(&m.id);
    let mag = ctx.magazines.get(&m.id);
    MangaDetailVo {
        id: m.id,
        manga_tran_name: Some(m.manga_tran_name.clone()),
        manga_ori_name: Some(m.manga_ori_name.clone()),
        image: Some(m.image.clone()),
        category: Some(m.category as i16),
        manga_status: Some(m.manga_status as i16),
        setup_time: dt_legacy(m.setup_time),
        update_time: dt_legacy(m.update_time),
        link: m.link.clone(),
        introduction: m.introduction.clone(),
        author_name: a1.map(|(_, n)| n.clone()),
        author_id: a1.map(|(id, _)| *id),
        author_name2: a2.map(|(_, n)| n.clone()),
        author_id2: a2.map(|(id, _)| *id),
        magazine_name: mag.map(|(_, n)| n.clone()),
        magazine_id: mag.map(|(id, _)| *id),
    }
}

/// 组装 MangaResponse
fn to_manga_response(
    m: &mangatb::Model,
    ctx: &MangaListContext,
) -> crate::entity::manga::MangaResponse {
    let med = ctx.latest_details.get(&m.id);
    let a1 = ctx.author1.get(&m.id);
    let a2 = ctx.author2.get(&m.id);
    let mag = ctx.magazines.get(&m.id);
    crate::entity::manga::MangaResponse {
        id: Some(m.id),
        manga_tran_name: Some(m.manga_tran_name.clone()),
        manga_ori_name: Some(m.manga_ori_name.clone()),
        image: Some(m.image.clone()),
        category: Some(m.category as i16),
        manga_status: Some(m.manga_status as i16),
        setup_time: dt_legacy(m.setup_time),
        update_time: dt_legacy(m.update_time),
        link: m.link.clone(),
        introduction: m.introduction.clone(),
        author_name: a1.map(|(_, n)| n.clone()),
        author_id: a1.map(|(id, _)| *id),
        author_name2: a2.map(|(_, n)| n.clone()),
        author_id2: a2.map(|(id, _)| *id),
        magazine_name: mag.map(|(_, n)| n.clone()),
        magazine_id: mag.map(|(id, _)| *id),
        translator_setup_time: med.and_then(|d| dt_legacy_opt(d.translator_setup_time)),
        translator_update_time: med.and_then(|d| dt_legacy_opt(d.translator_update_time)),
        proofreader_setup_time: med.and_then(|d| dt_legacy_opt(d.proofreader_setup_time)),
        proofreader_update_time: med.and_then(|d| dt_legacy_opt(d.proofreader_update_time)),
        letterer_setup_time: med.and_then(|d| dt_legacy_opt(d.letterer_setup_time)),
        letterer_update_time: med.and_then(|d| dt_legacy_opt(d.letterer_update_time)),
        reviewer_setup_time: med.and_then(|d| dt_legacy_opt(d.reviewer_setup_time)),
        reviewer_update_time: med.and_then(|d| dt_legacy_opt(d.reviewer_update_time)),
    }
}

/// 组装 RssMangaRow
fn to_rss_manga(m: &mangatb::Model, ctx: &MangaListContext, include_update: bool) -> RssMangaRow {
    let a1 = ctx.author1.get(&m.id);
    let a2 = ctx.author2.get(&m.id);
    RssMangaRow {
        id: m.id,
        manga_tran_name: Some(m.manga_tran_name.clone()),
        manga_ori_name: Some(m.manga_ori_name.clone()),
        category: Some(m.category as i16),
        manga_status: Some(m.manga_status as i16),
        image: Some(m.image.clone()),
        setup_time: dt_legacy(m.setup_time),
        update_time: if include_update {
            dt_legacy(m.update_time)
        } else {
            None
        },
        author_id: a1.map(|(id, _)| *id),
        author_name: a1.map(|(_, n)| n.clone()),
        author_id2: a2.map(|(id, _)| *id),
        author_name2: a2.map(|(_, n)| n.clone()),
    }
}

/// 工序提醒下一岗位组员 ID
fn next_post_member_id(ep: &mangaepisodetb::Model, my_name: &str) -> Option<i32> {
    match my_name {
        "translator" => ep.proofreader_id,
        "proofreader" => ep.letterer_id,
        "letterer" => ep.reviewer_id,
        "timer" => ep.timer_id,
        _ => None,
    }
}

/// naive 时间转 API UTC
fn dt_legacy(n: chrono::NaiveDateTime) -> Option<DateTime<Utc>> {
    Some(crate::utils::legacy_time::naive_db_to_api_utc(n))
}

/// 可空 naive 时间转 API UTC
fn dt_legacy_opt(n: Option<chrono::NaiveDateTime>) -> Option<DateTime<Utc>> {
    n.map(crate::utils::legacy_time::naive_db_to_api_utc)
}

#[cfg(test)]
mod tests {
    use super::{rss_episode_type, MangaListSort, MangaListSortField, MangaListSortOrder};
    use crate::entity::episode::EpisodeType;

    /// RSS 话数分类映射应识别番外，并将未知旧值兼容为正篇。
    #[test]
    fn rss_episode_type_defaults_unknown_values_to_main() {
        assert_eq!(rss_episode_type("EXTRA"), EpisodeType::Extra);
        assert_eq!(rss_episode_type("MAIN"), EpisodeType::Main);
        assert_eq!(rss_episode_type(""), EpisodeType::Main);
    }

    #[test]
    fn manga_list_sort_accepts_time_aliases() {
        let sort = MangaListSort::from_query(Some("createTime"), Some("ascending"));
        assert_eq!(sort.field, MangaListSortField::SetupTime);
        assert_eq!(sort.order, MangaListSortOrder::Asc);
        assert_eq!(sort.cache_field(), "setupTime");
        assert_eq!(sort.cache_order(), "ascending");

        let sort = MangaListSort::from_query(Some("updated_at"), Some("desc"));
        assert_eq!(sort.field, MangaListSortField::UpdateTime);
        assert_eq!(sort.order, MangaListSortOrder::Desc);
    }

    #[test]
    fn manga_list_sort_defaults_to_update_time_desc() {
        let sort = MangaListSort::from_query(Some("unknown"), Some("sideways"));
        assert_eq!(sort.field, MangaListSortField::UpdateTime);
        assert_eq!(sort.order, MangaListSortOrder::Desc);
        assert_eq!(sort.cache_field(), "updateTime");
        assert_eq!(sort.cache_order(), "descending");
    }
}