tdm_server_rust/repository/

member_workbench_assemble.rs

//! 个人工作台 SQL 聚合（总览统计 + 分页在做任务，避免前端拉全量话数）

use crate::entity::member::{
    MemberEpisodeVo, MemberWorkbenchOverview, MemberWorkbenchRoleCounts, MemberWorkbenchTaskGroup,
};
use chrono::NaiveDateTime;
use sea_orm::{ConnectionTrait, DatabaseConnection, DbBackend, Statement, Value};
use std::collections::HashMap;

/// 百合动画分类 ID，动画工序第三棒使用时轴。
const YURI_ANIMATION_CATEGORY: i32 = 4;

/// 组员在各岗位的接稿/交稿行模板（UNION LATERAL 展开）。
const ROLE_ROWS_CTE_TEMPLATE: &str = r#"
role_rows AS (
	  SELECT
	    e."Id" AS episode_id,
	    e."mangaId" AS manga_id,
	    r.role_name,
    r.setup_time,
    r.update_time,
    e."mangaEpisode" AS manga_episode,
    e."mangaEpisodeName" AS manga_episode_name,
    e."publishLink" AS publish_link,
    m."mangaTranName" AS manga_tran_name,
    m."mangaOriName" AS manga_ori_name,
    m.category AS manga_category,
    d."translatorUpdateTime" AS translator_update_time,
    d."proofreaderUpdateTime" AS proofreader_update_time,
    d."lettererUpdateTime" AS letterer_update_time,
    d."timerUpdateTime" AS timer_update_time,
    e."translatorFile" AS translator_file,
    e."proofreaderFile" AS proofreader_file,
    e."timerFile" AS timer_file,
    e.translator_file_oss_id,
    e.proofreader_file_oss_id,
    e.timer_file_oss_id
  FROM mangaepisodetb e
  INNER JOIN mangaepisodedetail d ON d."episodeId" = e."Id"
  INNER JOIN mangatb m ON m."Id" = e."mangaId"
  CROSS JOIN LATERAL (
    VALUES
      ('translator'::text, e."translatorId", d."translatorSetupTime", d."translatorUpdateTime"),
      ('proofreader', e."proofreaderId", d."proofreaderSetupTime", d."proofreaderUpdateTime"),
      ('letterer', e."lettererId", d."lettererSetupTime", d."lettererUpdateTime"),
      ('timer', e."timerId", d."timerSetupTime", d."timerUpdateTime"),
      ('reviewer', e."reviewerId", d."reviewerSetupTime", d."reviewerUpdateTime")
  ) AS r(role_name, assignee_id, setup_time, update_time)
  WHERE r.assignee_id = $1
),
doing AS (
  SELECT * FROM role_rows
  WHERE setup_time IS NOT NULL AND update_time IS NULL
),
assigned AS (
  SELECT * FROM role_rows WHERE setup_time IS NOT NULL
),
done AS (
  SELECT * FROM role_rows WHERE update_time IS NOT NULL
),
doing_with_readiness AS (
  SELECT
    d.*,
    (
      COALESCE(NULLIF(TRIM(d.publish_link), ''), '') <> ''
      OR d.role_name = 'translator'
      OR (d.role_name = 'proofreader' AND d.translator_update_time IS NOT NULL)
      OR (
        d.role_name = 'letterer'
        AND d.proofreader_update_time IS NOT NULL
        AND (
          d.manga_category IS NULL
          OR d.manga_category <> {YURI_ANIMATION_CATEGORY}
        )
      )
      OR (
        d.role_name = 'timer'
        AND d.proofreader_update_time IS NOT NULL
        AND d.manga_category = {YURI_ANIMATION_CATEGORY}
      )
      OR (
        d.role_name = 'reviewer'
        AND (
          (
            (
              d.manga_category IS NULL
              OR d.manga_category <> {YURI_ANIMATION_CATEGORY}
            )
            AND d.letterer_update_time IS NOT NULL
          )
          OR (
            d.manga_category = {YURI_ANIMATION_CATEGORY}
            AND d.timer_update_time IS NOT NULL
          )
        )
      )
    ) AS upstream_ready
  FROM doing d
)
"#;

/// 组员在各岗位的接稿/交稿行（UNION LATERAL 展开）。
fn role_rows_cte() -> String {
    ROLE_ROWS_CTE_TEMPLATE.replace(
        "{YURI_ANIMATION_CATEGORY}",
        &YURI_ANIMATION_CATEGORY.to_string(),
    )
}

/// 工作台任务分页中间行。
#[derive(Debug, Clone)]
pub struct WorkbenchTaskRow {
    /// 话数 ID
    pub episode_id: i32,
    /// 漫画 ID
    pub manga_id: i32,
    /// 当前组员在该话数上的最新接稿时间
    pub sort_time: NaiveDateTime,
}

/// 查询工作台总览统计
pub async fn get_workbench_overview(
    db: &DatabaseConnection,
    member_id: i32,
    stationed_count: i32,
) -> crate::error::ApiResult<MemberWorkbenchOverview> {
    let role_rows_cte = role_rows_cte();
    let sql = format!(
        r#"WITH {role_rows_cte},
active_episode_state AS (
  SELECT episode_id, BOOL_OR(upstream_ready) AS has_ready
  FROM doing_with_readiness
  GROUP BY episode_id
),
ready_doing AS (
  SELECT * FROM doing_with_readiness WHERE upstream_ready
),
plan_doing AS (
  SELECT * FROM doing_with_readiness WHERE NOT upstream_ready
)
SELECT
  (SELECT COUNT(DISTINCT episode_id)::int FROM ready_doing) AS pending,
  (SELECT COUNT(DISTINCT episode_id)::int FROM assigned) AS working,
  (SELECT COUNT(DISTINCT episode_id)::int FROM done) AS completed,
  (SELECT COUNT(*)::int FROM active_episode_state WHERE NOT has_ready) AS plan_count,
  (SELECT COUNT(*)::int FROM active_episode_state WHERE has_ready) AS doing_count,
  (SELECT COUNT(DISTINCT episode_id)::int FROM ready_doing
     WHERE setup_time < (NOW() AT TIME ZONE 'UTC') - INTERVAL '60 days') AS overdue,
  (SELECT COUNT(DISTINCT d.episode_id)::int FROM ready_doing d
     WHERE COALESCE(d.translator_file, '') <> ''
        OR COALESCE(d.proofreader_file, '') <> ''
        OR COALESCE(d.timer_file, '') <> ''
        OR d.translator_file_oss_id IS NOT NULL
        OR d.proofreader_file_oss_id IS NOT NULL
        OR d.timer_file_oss_id IS NOT NULL) AS downloadable,
  (SELECT COUNT(DISTINCT episode_id)::int FROM ready_doing WHERE role_name = 'translator') AS cnt_translator,
  (SELECT COUNT(DISTINCT episode_id)::int FROM ready_doing WHERE role_name = 'proofreader') AS cnt_proofreader,
  (SELECT COUNT(DISTINCT episode_id)::int FROM ready_doing WHERE role_name = 'letterer') AS cnt_letterer,
  (SELECT COUNT(DISTINCT episode_id)::int FROM ready_doing WHERE role_name = 'timer') AS cnt_timer,
  (SELECT COUNT(DISTINCT episode_id)::int FROM ready_doing WHERE role_name = 'reviewer') AS cnt_reviewer,
  (SELECT COUNT(DISTINCT episode_id)::int FROM plan_doing WHERE role_name = 'translator') AS plan_cnt_translator,
  (SELECT COUNT(DISTINCT episode_id)::int FROM plan_doing WHERE role_name = 'proofreader') AS plan_cnt_proofreader,
  (SELECT COUNT(DISTINCT episode_id)::int FROM plan_doing WHERE role_name = 'letterer') AS plan_cnt_letterer,
  (SELECT COUNT(DISTINCT episode_id)::int FROM plan_doing WHERE role_name = 'timer') AS plan_cnt_timer,
  (SELECT COUNT(DISTINCT episode_id)::int FROM plan_doing WHERE role_name = 'reviewer') AS plan_cnt_reviewer
"#
    );
    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("工作台总览查询失败喵"))?;

    let pending: i32 = row.try_get("", "pending")?;
    Ok(MemberWorkbenchOverview {
        pending,
        working: row.try_get("", "working")?,
        overdue: row.try_get("", "overdue")?,
        downloadable: row.try_get("", "downloadable")?,
        completed: row.try_get("", "completed")?,
        stationed: stationed_count,
        all_count: pending,
        plan_count: row.try_get("", "plan_count")?,
        doing_count: row.try_get("", "doing_count")?,
        role_counts: MemberWorkbenchRoleCounts {
            translator: row.try_get("", "cnt_translator")?,
            proofreader: row.try_get("", "cnt_proofreader")?,
            letterer: row.try_get("", "cnt_letterer")?,
            timer: row.try_get("", "cnt_timer")?,
            reviewer: row.try_get("", "cnt_reviewer")?,
        },
        plan_role_counts: MemberWorkbenchRoleCounts {
            translator: row.try_get("", "plan_cnt_translator")?,
            proofreader: row.try_get("", "plan_cnt_proofreader")?,
            letterer: row.try_get("", "plan_cnt_letterer")?,
            timer: row.try_get("", "plan_cnt_timer")?,
            reviewer: row.try_get("", "plan_cnt_reviewer")?,
        },
    })
}

/// 规范化 Tab：非法值视为 all
pub fn normalize_workbench_tab(tab: Option<&str>) -> &'static str {
    match tab.unwrap_or("all") {
        "translator" => "translator",
        "proofreader" => "proofreader",
        "letterer" => "letterer",
        "timer" => "timer",
        "reviewer" => "reviewer",
        _ => "all",
    }
}

/// 规范化 View：非法值视为 all
pub fn normalize_workbench_view(view: Option<&str>) -> &'static str {
    match view.unwrap_or("all") {
        "plan" => "plan",
        "doing" => "doing",
        _ => "all",
    }
}

/// 统计工作台匹配漫画组数（Tab + View + 关键词）
pub async fn count_workbench_tasks(
    db: &DatabaseConnection,
    member_id: i32,
    tab: &str,
    view: &str,
    keyword: Option<&str>,
) -> crate::error::ApiResult<i64> {
    let kw = keyword.unwrap_or("").trim();
    let kw_pattern = if kw.is_empty() {
        None
    } else {
        Some(format!("%{kw}%"))
    };
    let role_rows_cte = role_rows_cte();
    let sql = format!(
        r#"WITH {role_rows_cte},
	filtered AS (
	  SELECT d.episode_id, d.manga_id, BOOL_OR(d.upstream_ready) AS has_ready
	  FROM doing_with_readiness d
  WHERE ($2 = 'all' OR d.role_name = $2)
    AND (
      $3 IS NULL OR
      d.manga_tran_name ILIKE $3 OR
      d.manga_ori_name ILIKE $3 OR
      d.manga_episode ILIKE $3 OR
      COALESCE(d.manga_episode_name, '') ILIKE $3
    )
	  GROUP BY d.episode_id, d.manga_id
	),
	selected_mangas AS (
	  SELECT DISTINCT manga_id
	  FROM filtered
	  WHERE $4 = 'all'
	     OR ($4 = 'doing' AND has_ready)
	     OR ($4 = 'plan' AND NOT has_ready)
	)
	SELECT COUNT(*)::bigint AS cnt FROM selected_mangas"#
    );
    let row = db
        .query_one(Statement::from_sql_and_values(
            DbBackend::Postgres,
            &sql,
            [
                Value::from(member_id),
                Value::from(tab),
                kw_pattern.into(),
                Value::from(view),
            ],
        ))
        .await?
        .ok_or_else(|| crate::error::AppError::business("工作台任务计数失败喵"))?;
    Ok(row.try_get("", "cnt")?)
}

/// 分页查询工作台漫画组 ID（按组内最新接稿时间降序）
pub async fn page_workbench_manga_ids(
    db: &DatabaseConnection,
    member_id: i32,
    tab: &str,
    view: &str,
    keyword: Option<&str>,
    page: i32,
    page_size: i32,
) -> crate::error::ApiResult<Vec<i32>> {
    let page = page.max(1);
    let page_size = page_size.clamp(1, 100);
    let offset = ((page - 1) * page_size) as i64;
    let kw = keyword.unwrap_or("").trim();
    let kw_pattern = if kw.is_empty() {
        None
    } else {
        Some(format!("%{kw}%"))
    };
    let role_rows_cte = role_rows_cte();
    let sql = format!(
        r#"WITH {role_rows_cte},
	filtered AS (
	  SELECT
	    d.episode_id,
	    d.manga_id,
	    MAX(d.setup_time) AS sort_time,
	    BOOL_OR(d.upstream_ready) AS has_ready
  FROM doing_with_readiness d
  WHERE ($2 = 'all' OR d.role_name = $2)
    AND (
      $3 IS NULL OR
      d.manga_tran_name ILIKE $3 OR
      d.manga_ori_name ILIKE $3 OR
      d.manga_episode ILIKE $3 OR
      COALESCE(d.manga_episode_name, '') ILIKE $3
    )
	  GROUP BY d.episode_id, d.manga_id
	),
	selected_episodes AS (
	  SELECT episode_id, manga_id, sort_time
	  FROM filtered
	  WHERE $4 = 'all'
	     OR ($4 = 'doing' AND has_ready)
	     OR ($4 = 'plan' AND NOT has_ready)
	),
	selected_mangas AS (
	  SELECT manga_id, MAX(sort_time) AS manga_sort_time
	  FROM selected_episodes
	  GROUP BY manga_id
	)
	SELECT manga_id
	FROM selected_mangas
	ORDER BY manga_sort_time DESC NULLS LAST, manga_id DESC
	LIMIT $5 OFFSET $6"#
    );
    let rows = db
        .query_all(Statement::from_sql_and_values(
            DbBackend::Postgres,
            &sql,
            [
                Value::from(member_id),
                Value::from(tab),
                kw_pattern.into(),
                Value::from(view),
                Value::from(page_size),
                Value::from(offset),
            ],
        ))
        .await?;
    rows.into_iter()
        .map(|row| row.try_get("", "manga_id").map_err(Into::into))
        .collect()
}

/// 查询指定漫画组内的工作台任务行。
pub async fn list_workbench_task_rows_by_mangas(
    db: &DatabaseConnection,
    member_id: i32,
    tab: &str,
    view: &str,
    keyword: Option<&str>,
    manga_ids: &[i32],
) -> crate::error::ApiResult<Vec<WorkbenchTaskRow>> {
    if manga_ids.is_empty() {
        return Ok(vec![]);
    }
    let kw = keyword.unwrap_or("").trim();
    let kw_pattern = if kw.is_empty() {
        None
    } else {
        Some(format!("%{kw}%"))
    };
    let placeholders: Vec<String> = (0..manga_ids.len())
        .map(|i| format!("${}", i + 5))
        .collect();
    let manga_in = placeholders.join(", ");
    let manga_order = format!("ARRAY[{}]::int[]", manga_in);
    let role_rows_cte = role_rows_cte();
    let sql = format!(
        r#"WITH {role_rows_cte},
filtered AS (
  SELECT
    d.episode_id,
    d.manga_id,
    MAX(d.setup_time) AS sort_time,
    BOOL_OR(d.upstream_ready) AS has_ready
  FROM doing_with_readiness d
  WHERE ($2 = 'all' OR d.role_name = $2)
    AND (
      $3 IS NULL OR
      d.manga_tran_name ILIKE $3 OR
      d.manga_ori_name ILIKE $3 OR
      d.manga_episode ILIKE $3 OR
      COALESCE(d.manga_episode_name, '') ILIKE $3
    )
    AND d.manga_id IN ({manga_in})
  GROUP BY d.episode_id, d.manga_id
),
selected AS (
  SELECT episode_id, manga_id, sort_time
  FROM filtered
  WHERE $4 = 'all'
     OR ($4 = 'doing' AND has_ready)
     OR ($4 = 'plan' AND NOT has_ready)
)
SELECT episode_id, manga_id, sort_time
FROM selected
ORDER BY array_position({manga_order}, manga_id), sort_time DESC NULLS LAST, episode_id DESC"#
    );
    let mut values = vec![
        Value::from(member_id),
        Value::from(tab),
        kw_pattern.into(),
        Value::from(view),
    ];
    values.extend(manga_ids.iter().copied().map(Value::from));
    let rows = db
        .query_all(Statement::from_sql_and_values(
            DbBackend::Postgres,
            &sql,
            values,
        ))
        .await?;
    rows.into_iter()
        .map(|row| {
            Ok(WorkbenchTaskRow {
                episode_id: row.try_get("", "episode_id")?,
                manga_id: row.try_get("", "manga_id")?,
                sort_time: row.try_get("", "sort_time")?,
            })
        })
        .collect()
}

/// 按漫画组顺序组装工作台任务 VO，并填充 workbench_time
pub async fn assemble_workbench_task_groups(
    db: &DatabaseConnection,
    manga_ids: Vec<i32>,
    ordered: Vec<WorkbenchTaskRow>,
) -> crate::error::ApiResult<Vec<MemberWorkbenchTaskGroup>> {
    if ordered.is_empty() {
        return Ok(vec![]);
    }
    let ids: Vec<i32> = ordered.iter().map(|row| row.episode_id).collect();
    let episodes = super::member_episode_assemble::load_episode_models_by_ids(db, &ids).await?;
    let mut vos = super::member_episode_assemble::assemble_member_episode_vos(db, episodes).await?;
    let time_map: HashMap<i32, NaiveDateTime> = ordered
        .iter()
        .map(|row| (row.episode_id, row.sort_time))
        .collect();
    let manga_map: HashMap<i32, i32> = ordered
        .iter()
        .map(|row| (row.episode_id, row.manga_id))
        .collect();
    let order: HashMap<i32, usize> = ids.iter().enumerate().map(|(i, id)| (*id, i)).collect();
    for vo in &mut vos {
        if let Some(t) = time_map.get(&vo.id) {
            vo.workbench_time = Some(crate::utils::legacy_time::naive_db_to_api_utc(*t));
        }
    }
    vos.sort_by_key(|vo| order.get(&vo.id).copied().unwrap_or(usize::MAX));
    let mut grouped: HashMap<i32, Vec<MemberEpisodeVo>> = HashMap::new();
    for vo in vos {
        let manga_id = manga_map.get(&vo.id).copied().unwrap_or(vo.manga_id);
        grouped.entry(manga_id).or_default().push(vo);
    }
    let groups = manga_ids
        .into_iter()
        .filter_map(|manga_id| {
            let tasks = grouped.remove(&manga_id)?;
            let manga_name = tasks
                .first()
                .and_then(|task| task.manga_name.clone())
                .unwrap_or_else(|| format!("漫画#{manga_id}"));
            Some(MemberWorkbenchTaskGroup {
                manga_id,
                manga_name,
                tasks,
            })
        })
        .collect();
    Ok(groups)
}