tdm_server_rust/repository/

oss_repo.rs

//! OSS 对象存储数据访问层 (OSS Repository)
//!
//! 封装 `oss` 表记录管理及 COS STS 凭证生成（SeaORM）。

use crate::config::AppConfig;
use crate::db::DbConn;
use crate::entity::oss::{OssCredential, OssDto};
use crate::sea_entity::{episode_role_taker, mangaepisodetb, oss};
use crate::utils::{agent_debug, cos_cdn, cos_presign, cos_sts};
use sea_orm::{ActiveModelTrait, ColumnTrait, DatabaseConnection, EntityTrait, QueryFilter, Set};
use sqlx::postgres::PgPool;
use std::time::Duration;
use uuid::Uuid;

/// OSS 仓储
pub struct OssRepository {
    /// SeaORM 数据库连接
    db: DbConn,
    /// 应用配置
    config: AppConfig,
}

impl OssRepository {
    /// 从 `PgPool` 构造
    pub fn new(pool: PgPool, config: AppConfig) -> Self {
        Self {
            db: crate::db::from_sqlx_pool(pool),
            config,
        }
    }

    /// 从 `DatabaseConnection` 构造
    pub fn from_db(db: DatabaseConnection, config: AppConfig) -> Self {
        Self { db, config }
    }

    /// 新增或更新 OSS 记录，绑定到话数岗位列
    #[tracing::instrument(skip_all, level = "debug")]
    pub async fn upsert_oss(&self, dto: &OssDto, member_id: i32) -> crate::error::ApiResult<i32> {
        let bucket = &self.config.tencent.bucket;
        let region = &self.config.tencent.region;
        let manga_id = self.get_episode_manga_id(dto.episode_id).await?;
        let object_key = dto.object_key.clone().unwrap_or_else(|| {
            self.build_object_key(manga_id, dto.episode_id, &dto.post_name, &dto.filename)
        });
        self.verify_object_exists_if_configured(bucket, region, &object_key)
            .await?;
        let url = format!("https://{bucket}.cos.{region}.myqcloud.com/{object_key}");
        let now = chrono::Utc::now().naive_utc();
        let ext = file_ext(&dto.filename).unwrap_or_default();

        let existing_oss_id = self
            .get_episode_oss_id(dto.episode_id, &dto.post_name, Some(member_id))
            .await?;
        let oss_id = if let Some(id) = existing_oss_id {
            let model = oss::ActiveModel {
                id: Set(id),
                filename: Set(dto.filename.clone()),
                object_key: Set(object_key.clone()),
                url: Set(url.clone()),
                bucket_name: Set(Some(bucket.clone())),
                region: Set(Some(region.clone())),
                update_time: Set(Some(now)),
                update_by: Set(Some(member_id)),
                ..Default::default()
            };
            model.update(&self.db).await?;
            id
        } else {
            let model = oss::ActiveModel {
                filename: Set(dto.filename.clone()),
                file_ext: Set(ext),
                object_key: Set(object_key.clone()),
                url: Set(url.clone()),
                bucket_name: Set(Some(bucket.clone())),
                region: Set(Some(region.clone())),
                create_time: Set(Some(now)),
                create_by: Set(Some(member_id)),
                update_time: Set(Some(now)),
                update_by: Set(Some(member_id)),
                ..Default::default()
            };
            let inserted = model.insert(&self.db).await?;
            self.bind_episode_oss_id(dto.episode_id, &dto.post_name, member_id, inserted.id)
                .await?;
            inserted.id
        };
        mangaepisodetb::Entity::update_many()
            .col_expr(
                mangaepisodetb::Column::UpdateTime,
                sea_orm::sea_query::Expr::current_timestamp().into(),
            )
            .filter(mangaepisodetb::Column::Id.eq(dto.episode_id))
            .exec(&self.db)
            .await?;
        Ok(oss_id)
    }

    /// 删除 OSS 记录
    #[tracing::instrument(skip_all, level = "debug")]
    pub async fn delete_oss(&self, oss_id: i32) -> crate::error::ApiResult<Option<String>> {
        let row = oss::Entity::find_by_id(oss_id).one(&self.db).await?;
        let key = row.map(|r| r.object_key);
        oss::Entity::delete_by_id(oss_id).exec(&self.db).await?;
        Ok(key)
    }

    /// 获取上传凭证
    #[tracing::instrument(skip_all, level = "debug")]
    pub async fn get_upload_credential(
        &self,
        episode_id: i32,
        post_name: &str,
        filename: &str,
    ) -> crate::error::ApiResult<OssCredential> {
        self.ensure_episode_exists(episode_id).await?;
        self.ensure_ext_allowed(filename, &self.config.tencent.ext_whitelist)?;
        let manga_id = self.get_episode_manga_id(episode_id).await?;
        let object_key = self.build_object_key(manga_id, episode_id, post_name, filename);
        let max_bytes = self.config.tencent.max_file_size * 1024 * 1024;
        agent_debug::log(
            "H5",
            "oss_repo.rs:get_upload_credential",
            "upload_credential_request",
            serde_json::json!({
                "episodeId": episode_id,
                "postName": post_name,
                "objectKeyLen": object_key.len(),
                "hasNonAscii": !object_key.is_ascii(),
                "maxBytes": max_bytes
            }),
        );
        let presigned_url = self
            .presigned_put_url(&self.config.tencent.bucket, &object_key, max_bytes)
            .await?;
        Ok(OssCredential {
            presigned_url,
            object_key: Some(object_key),
            original_filename: Some(filename.to_string()),
        })
    }

    /// 获取下载凭证
    #[tracing::instrument(skip_all, level = "debug")]
    pub async fn get_download_credential(
        &self,
        episode_id: i32,
        post_name: &str,
    ) -> crate::error::ApiResult<OssCredential> {
        let oss_id: i32 = self
            .get_episode_oss_id(episode_id, post_name, None)
            .await?
            .ok_or_else(|| {
                agent_debug::log(
                    "H2",
                    "oss_repo.rs:get_download_credential",
                    "oss_id_missing",
                    serde_json::json!({
                        "episodeId": episode_id,
                        "postName": post_name
                    }),
                );
                crate::error::AppError::Oss {
                    code: None,
                    msg: format!("当前单话尚未上传 [{post_name}] 文件"),
                }
            })?;
        let row = oss::Entity::find_by_id(oss_id)
            .one(&self.db)
            .await?
            .ok_or_else(|| crate::error::AppError::Oss {
                code: None,
                msg: format!("OSS 记录 {oss_id} 不存在"),
            })?;
        let object_key = row.object_key.clone();
        let original_filename = Some(row.filename.clone());
        let bucket = resolve_download_bucket(&row, &self.config.tencent.bucket);
        let region = row
            .region
            .as_deref()
            .filter(|s| !s.trim().is_empty())
            .unwrap_or(&self.config.tencent.region);
        let cdn_domain = self.resolve_download_cdn_domain(&row);
        let presigned_url = if let Some(cdn_url) = cdn_domain.as_deref().and_then(|domain| {
            cos_cdn::generate_cdn_url(domain, &self.config.tencent.cdn_key, &object_key)
        }) {
            agent_debug::log(
                "H1",
                "oss_repo.rs:get_download_credential",
                "cdn_signed_url",
                serde_json::json!({
                    "hasSign": cdn_url.contains("sign="),
                    "hasT": cdn_url.contains("&t="),
                    "objectKeyLen": object_key.len(),
                    "bucket": bucket,
                    "cdnDomain": cdn_domain
                }),
            );
            cdn_url
        } else {
            agent_debug::log(
                "H1",
                "oss_repo.rs:get_download_credential",
                "fallback_cos_presign",
                serde_json::json!({
                    "cdnDomainEmpty": self.config.tencent.cdn_domain.is_empty(),
                    "cdnKeyEmpty": self.config.tencent.cdn_key.is_empty(),
                    "bucket": bucket,
                    "region": region
                }),
            );
            self.presigned_get_url(&bucket, region, &object_key).await?
        };
        Ok(OssCredential {
            presigned_url,
            object_key: Some(object_key),
            original_filename,
        })
    }

    /// 获取图片上传凭证
    #[tracing::instrument(skip_all, level = "debug")]
    pub async fn get_image_upload_credential(
        &self,
        image_type: &str,
        filename: &str,
    ) -> crate::error::ApiResult<OssCredential> {
        let image_bucket = self.config.tencent.image_bucket.trim();
        if image_bucket.is_empty() {
            return Err(crate::error::AppError::Oss {
                code: None,
                msg: "图片存储桶未配置".into(),
            });
        }
        self.ensure_ext_allowed(filename, &self.config.tencent.image_ext_whitelist)?;
        let object_key = self.build_image_object_key(image_type, filename);
        let max_bytes = self.config.tencent.image_max_file_size * 1024 * 1024;
        let presigned_url = self
            .presigned_put_url(image_bucket, &object_key, max_bytes)
            .await?;
        Ok(OssCredential {
            presigned_url,
            object_key: Some(object_key),
            original_filename: Some(filename.to_string()),
        })
    }

    /// 生成图片桶的预签名 PUT URL（服务端导入图源时上传用）
    ///
    /// 与 `get_image_upload_credential` 不同：此处不校验图片后缀白名单，
    /// 且使用更大的大小上限，用于服务端批量上传解压后的页面图片。
    #[tracing::instrument(skip_all, level = "debug")]
    pub async fn presigned_image_put_url(
        &self,
        object_key: &str,
        max_bytes: u64,
    ) -> crate::error::ApiResult<String> {
        let image_bucket = self.config.tencent.image_bucket.trim();
        if image_bucket.is_empty() {
            return Err(crate::error::AppError::Oss {
                code: None,
                msg: "图片存储桶未配置".into(),
            });
        }
        self.presigned_put_url(image_bucket, object_key, max_bytes)
            .await
    }

    /// 批量生成图片桶预签名 PUT URL（前端图源解压后逐张直传用）
    ///
    /// 以 `{prefix}*` 前缀策略**仅申请一次** STS 联合身份临时凭证，
    /// 再用同一份临时密钥对每个对象键分别签名，避免逐张调用 STS 拖慢上传。
    ///
    /// ## 参数
    /// - `prefix`: 对象键公共前缀（如 `editor/episode_123/`），策略按 `{prefix}*` 授权
    /// - `object_keys`: 全部待签名对象键（必须都落在 `prefix` 下）
    /// - `max_bytes`: 单个对象允许的最大字节数
    #[tracing::instrument(skip_all, level = "debug")]
    pub async fn presigned_image_put_urls(
        &self,
        prefix: &str,
        object_keys: &[String],
        max_bytes: u64,
    ) -> crate::error::ApiResult<Vec<String>> {
        let image_bucket = self.config.tencent.image_bucket.trim();
        if image_bucket.is_empty() {
            return Err(crate::error::AppError::Oss {
                code: None,
                msg: "图片存储桶未配置".into(),
            });
        }
        if object_keys.is_empty() {
            return Ok(Vec::new());
        }
        let region = &self.config.tencent.region;
        // 通配前缀策略，单次 STS 覆盖本话数全部页面对象键
        let policy = cos_sts::build_put_policy(
            image_bucket,
            region,
            &format!("{}*", prefix.trim_start_matches('/')),
            max_bytes,
        )?;
        let creds = cos_sts::get_federation_token(
            &self.config.tencent.secret_id,
            &self.config.tencent.secret_key,
            region,
            policy,
            self.config.tencent.duration_seconds,
        )
        .await?;
        let mut urls = Vec::with_capacity(object_keys.len());
        for key in object_keys {
            let url = cos_presign::presigned_url(
                &creds.tmp_secret_id,
                &creds.tmp_secret_key,
                image_bucket,
                region,
                key,
                "PUT",
                self.config.tencent.duration_seconds,
                &creds.session_token,
            )?;
            urls.push(url);
        }
        Ok(urls)
    }

    /// 构造图片访问 URL（优先图片 CDN 域名，否则 COS 直链）
    pub fn image_display_url(&self, object_key: &str) -> String {
        let cdn = self.config.tencent.image_cdn_domain.trim();
        if !cdn.is_empty() {
            let domain = cdn.trim_end_matches('/');
            let proto = if domain.starts_with("http://") || domain.starts_with("https://") {
                ""
            } else {
                "https://"
            };
            return format!("{proto}{domain}/{object_key}");
        }
        format!(
            "https://{}.cos.{}.myqcloud.com/{}",
            self.config.tencent.image_bucket, self.config.tencent.region, object_key
        )
    }

    /// 图片桶名
    pub fn image_bucket_name(&self) -> String {
        self.config.tencent.image_bucket.clone()
    }

    /// STS + 预签名 PUT URL
    #[tracing::instrument(skip_all, level = "debug")]
    async fn presigned_put_url(
        &self,
        bucket: &str,
        object_key: &str,
        max_bytes: u64,
    ) -> crate::error::ApiResult<String> {
        let policy =
            cos_sts::build_put_policy(bucket, &self.config.tencent.region, object_key, max_bytes)?;
        let creds = cos_sts::get_federation_token(
            &self.config.tencent.secret_id,
            &self.config.tencent.secret_key,
            &self.config.tencent.region,
            policy,
            self.config.tencent.duration_seconds,
        )
        .await?;
        cos_presign::presigned_url(
            &creds.tmp_secret_id,
            &creds.tmp_secret_key,
            bucket,
            &self.config.tencent.region,
            object_key,
            "PUT",
            self.config.tencent.duration_seconds,
            &creds.session_token,
        )
    }

    /// STS + 预签名 GET URL
    #[tracing::instrument(skip_all, level = "debug")]
    async fn presigned_get_url(
        &self,
        bucket: &str,
        region: &str,
        object_key: &str,
    ) -> crate::error::ApiResult<String> {
        if self.config.tencent.secret_id.trim().is_empty()
            || self.config.tencent.secret_key.trim().is_empty()
        {
            return Err(crate::error::AppError::Oss {
                code: None,
                msg: "当前环境缺少 COS 下载密钥，无法生成对象存储直链".into(),
            });
        }

        let policy = cos_sts::build_get_policy(bucket, region, object_key)?;
        let creds = cos_sts::get_federation_token(
            &self.config.tencent.secret_id,
            &self.config.tencent.secret_key,
            region,
            policy,
            self.config.tencent.duration_seconds,
        )
        .await?;
        cos_presign::presigned_url(
            &creds.tmp_secret_id,
            &creds.tmp_secret_key,
            bucket,
            region,
            object_key,
            "GET",
            self.config.tencent.duration_seconds,
            &creds.session_token,
        )
    }

    /// 按 OSS 行内信息选择下载 CDN 域名。
    ///
    /// 只在 OSS 记录属于当前 profile 桶时使用当前 profile 的 CDN key。
    /// 如果记录来自其他 bucket，不能拿当前环境 key 去签别的 CDN 域名，
    /// 应降级为对应 bucket/region 的 COS 预签名直链。
    fn resolve_download_cdn_domain(&self, row: &oss::Model) -> Option<String> {
        if self.config.tencent.cdn_key.trim().is_empty() {
            return None;
        }

        if should_use_config_cdn(
            row.bucket_name.as_deref(),
            &row.url,
            &self.config.tencent.bucket,
            &self.config.tencent.cdn_domain,
        ) {
            return non_empty_trimmed(&self.config.tencent.cdn_domain);
        }

        None
    }

    /// 在密钥可用时校验对象已经真实存在于 COS。
    ///
    /// 对齐 Java 旧服务在 upsert 前调用 `getObjectMetadata` 的保护思路。
    /// 本地开发/测试经常没有腾讯云 SecretKey，此时跳过网络校验，避免阻断
    /// 不依赖 OSS 的开发流程。
    async fn verify_object_exists_if_configured(
        &self,
        bucket: &str,
        region: &str,
        object_key: &str,
    ) -> crate::error::ApiResult<()> {
        if self.config.tencent.secret_id.trim().is_empty()
            || self.config.tencent.secret_key.trim().is_empty()
        {
            agent_debug::log(
                "H2",
                "oss_repo.rs:verify_object_exists",
                "skip_missing_secret",
                serde_json::json!({
                    "bucket": bucket,
                    "objectKeyLen": object_key.len()
                }),
            );
            return Ok(());
        }

        let url = self.presigned_get_url(bucket, region, object_key).await?;
        let client = reqwest::Client::builder()
            .timeout(Duration::from_secs(10))
            .build()
            .map_err(|e| {
                crate::error::AppError::Internal(format!("构建 COS 校验客户端失败: {e}"))
            })?;
        let resp = client
            .get(&url)
            .header(reqwest::header::RANGE, "bytes=0-0")
            .send()
            .await
            .map_err(|e| crate::error::AppError::Oss {
                code: None,
                msg: format!("校验 COS 对象存在失败: {e}"),
            })?;
        if resp.status().is_success() || resp.status() == reqwest::StatusCode::PARTIAL_CONTENT {
            return Ok(());
        }
        Err(crate::error::AppError::Oss {
            code: None,
            msg: format!(
                "COS 对象不存在或无法访问: {object_key} (HTTP {})",
                resp.status()
            ),
        })
    }

    /// 按 ID 查询 objectKey
    #[tracing::instrument(skip_all, level = "debug")]
    pub async fn get_object_key(&self, oss_id: i32) -> crate::error::ApiResult<Option<String>> {
        let row = oss::Entity::find_by_id(oss_id).one(&self.db).await?;
        Ok(row.map(|r| r.object_key))
    }

    /// 查询话数某岗位是否已绑定 OSS 记录（对外只读）
    ///
    /// 用于「下载图源」判断：provider 未绑定压缩包时改走编辑器页面现打包。
    #[tracing::instrument(skip_all, level = "debug")]
    pub async fn episode_post_oss_id(
        &self,
        episode_id: i32,
        post_name: &str,
    ) -> crate::error::ApiResult<Option<i32>> {
        self.get_episode_oss_id(episode_id, post_name, None).await
    }

    /// 检测话数是否存在
    #[tracing::instrument(skip_all, level = "debug")]
    async fn ensure_episode_exists(&self, episode_id: i32) -> crate::error::ApiResult<()> {
        let exists = mangaepisodetb::Entity::find_by_id(episode_id)
            .one(&self.db)
            .await?
            .is_some();
        if !exists {
            return Err(crate::error::AppError::business(format!(
                "漫画单话 {episode_id} 不存在"
            )));
        }
        Ok(())
    }

    /// 查询话数岗位绑定的 OSS ID
    #[tracing::instrument(skip_all, level = "debug")]
    async fn get_episode_oss_id(
        &self,
        episode_id: i32,
        post_name: &str,
        member_id: Option<i32>,
    ) -> crate::error::ApiResult<Option<i32>> {
        validate_post_name(post_name)?;
        if let Some(member_id) = member_id {
            if let Some(taker) =
                find_episode_role_taker(&self.db, episode_id, post_name, member_id).await?
            {
                return Ok(taker.file_oss_id);
            }
        }
        let row = mangaepisodetb::Entity::find_by_id(episode_id)
            .one(&self.db)
            .await?;
        Ok(row.and_then(|ep| read_post_oss_id(&ep, post_name)))
    }

    /// 绑定话数岗位 OSS ID
    #[tracing::instrument(skip_all, level = "debug")]
    async fn bind_episode_oss_id(
        &self,
        episode_id: i32,
        post_name: &str,
        member_id: i32,
        oss_id: i32,
    ) -> crate::error::ApiResult<()> {
        validate_post_name(post_name)?;
        if let Some(taker) =
            find_episode_role_taker(&self.db, episode_id, post_name, member_id).await?
        {
            let mut am: episode_role_taker::ActiveModel = taker.into();
            am.file_oss_id = Set(Some(oss_id));
            am.update(&self.db).await?;
            return Ok(());
        }
        let Some(ep) = mangaepisodetb::Entity::find_by_id(episode_id)
            .one(&self.db)
            .await?
        else {
            return Err(crate::error::AppError::business("话数不存在喵"));
        };
        let mut am: mangaepisodetb::ActiveModel = ep.into();
        write_post_oss_id(&mut am, post_name, oss_id)?;
        am.update_time = Set(chrono::Utc::now().naive_utc());
        am.update(&self.db).await?;
        Ok(())
    }

    /// 查询话数所属漫画 ID
    #[tracing::instrument(skip_all, level = "debug")]
    async fn get_episode_manga_id(&self, episode_id: i32) -> crate::error::ApiResult<i32> {
        let row = mangaepisodetb::Entity::find_by_id(episode_id)
            .one(&self.db)
            .await?;
        row.map(|r| r.manga_id).ok_or_else(|| {
            crate::error::AppError::business(format!("漫画单话 {episode_id} 不存在"))
        })
    }

    /// 生成话数稿件 objectKey
    fn build_object_key(
        &self,
        manga_id: i32,
        episode_id: i32,
        post_name: &str,
        filename: &str,
    ) -> String {
        let file_type_dir = match post_name.to_lowercase().as_str() {
            "provider" => "manga-raw",
            "translator" => "translation",
            "proofreader" => "proofread",
            "letterer" => "manga-cooked",
            "timer" => "timing",
            _ => "other",
        };
        format!("manga_{manga_id}/episode_{episode_id}/{file_type_dir}/{filename}")
    }

    /// 生成图片 objectKey
    fn build_image_object_key(&self, image_type: &str, filename: &str) -> String {
        let mut normalized = image_type.trim().trim_matches('/').to_string();
        if normalized.is_empty() {
            normalized = "misc".into();
        }
        let ext = file_ext(filename).unwrap_or_else(|| "jpg".into());
        format!("{normalized}/{}.{}", Uuid::new_v4(), ext)
    }

    /// 校验后缀白名单
    fn ensure_ext_allowed(
        &self,
        filename: &str,
        whitelist: &[String],
    ) -> crate::error::ApiResult<()> {
        let ext = file_ext(filename).ok_or_else(|| crate::error::AppError::Oss {
            code: None,
            msg: format!("无法识别文件后缀: {filename}"),
        })?;
        if !whitelist.iter().any(|w| w.eq_ignore_ascii_case(&ext)) {
            return Err(crate::error::AppError::Oss {
                code: None,
                msg: format!("非法文件后缀: {ext}"),
            });
        }
        Ok(())
    }
}

/// 校验岗位名
fn validate_post_name(post_name: &str) -> crate::error::ApiResult<()> {
    match post_name.to_lowercase().as_str() {
        "provider" | "translator" | "proofreader" | "letterer" | "timer" => Ok(()),
        _ => Err(crate::error::AppError::Oss {
            code: None,
            msg: format!("不支持的岗位类型：{post_name}"),
        }),
    }
}

fn non_empty_trimmed(value: &str) -> Option<String> {
    let trimmed = value.trim();
    (!trimmed.is_empty()).then(|| trimmed.to_string())
}

fn resolve_download_bucket(row: &oss::Model, config_bucket: &str) -> String {
    row.bucket_name
        .as_deref()
        .and_then(non_empty_trimmed)
        .or_else(|| cos_bucket_from_url(&row.url))
        .unwrap_or_else(|| config_bucket.to_string())
}

fn should_use_config_cdn(
    row_bucket: Option<&str>,
    row_url: &str,
    config_bucket: &str,
    config_cdn_domain: &str,
) -> bool {
    let row_bucket_matches = row_bucket.is_none_or(|bucket| {
        let bucket = bucket.trim();
        bucket.is_empty() || bucket == config_bucket
    });
    row_bucket_matches
        && stored_url_matches_config_resource(row_url, config_bucket, config_cdn_domain)
}

fn stored_url_matches_config_resource(
    row_url: &str,
    config_bucket: &str,
    config_cdn_domain: &str,
) -> bool {
    let Some(host) = url_host(row_url) else {
        return true;
    };

    if let Some(bucket) = cos_bucket_from_host(&host) {
        return bucket == config_bucket;
    }

    let config_cdn_host = normalize_domain_host(config_cdn_domain);
    config_cdn_host.as_deref() == Some(host.as_str())
}

fn cos_bucket_from_url(url: &str) -> Option<String> {
    url_host(url).and_then(|host| cos_bucket_from_host(&host))
}

fn cos_bucket_from_host(host: &str) -> Option<String> {
    host.split_once(".cos.").and_then(|(bucket, _)| {
        let bucket = bucket.trim();
        (!bucket.is_empty()).then(|| bucket.to_string())
    })
}

fn normalize_domain_host(domain: &str) -> Option<String> {
    let trimmed = domain.trim().trim_end_matches('/');
    if trimmed.is_empty() {
        return None;
    }
    let without_scheme = trimmed
        .strip_prefix("https://")
        .or_else(|| trimmed.strip_prefix("http://"))
        .unwrap_or(trimmed);
    without_scheme
        .split('/')
        .next()
        .map(str::trim)
        .filter(|host| !host.is_empty())
        .map(ToString::to_string)
}

fn url_host(url: &str) -> Option<String> {
    let trimmed = url.trim();
    if trimmed.is_empty() {
        return None;
    }
    let without_scheme = trimmed
        .strip_prefix("https://")
        .or_else(|| trimmed.strip_prefix("http://"))?;
    without_scheme
        .split('/')
        .next()
        .map(str::trim)
        .filter(|host| !host.is_empty())
        .map(ToString::to_string)
}

/// 读取岗位 OSS ID 列
fn read_post_oss_id(ep: &mangaepisodetb::Model, post_name: &str) -> Option<i32> {
    match post_name.to_lowercase().as_str() {
        "provider" => ep.provider_file_oss_id,
        "translator" => ep.translator_file_oss_id,
        "proofreader" => ep.proofreader_file_oss_id,
        "letterer" => ep.letterer_file_oss_id,
        "timer" => ep.timer_file_oss_id,
        _ => None,
    }
}

/// 查询当前上传人是否为翻译 / 校对共同接稿人。
async fn find_episode_role_taker(
    db: &DatabaseConnection,
    episode_id: i32,
    post_name: &str,
    member_id: i32,
) -> crate::error::ApiResult<Option<episode_role_taker::Model>> {
    let role = post_name.to_lowercase();
    if !matches!(role.as_str(), "translator" | "proofreader") {
        return Ok(None);
    }
    let row = episode_role_taker::Entity::find()
        .filter(episode_role_taker::Column::EpisodeId.eq(episode_id))
        .filter(episode_role_taker::Column::Role.eq(role))
        .filter(episode_role_taker::Column::MemberId.eq(member_id))
        .one(db)
        .await?;
    Ok(row)
}

/// 写入岗位 OSS ID 列
fn write_post_oss_id(
    am: &mut mangaepisodetb::ActiveModel,
    post_name: &str,
    oss_id: i32,
) -> crate::error::ApiResult<()> {
    match post_name.to_lowercase().as_str() {
        "provider" => am.provider_file_oss_id = Set(Some(oss_id)),
        "translator" => am.translator_file_oss_id = Set(Some(oss_id)),
        "proofreader" => am.proofreader_file_oss_id = Set(Some(oss_id)),
        "letterer" => am.letterer_file_oss_id = Set(Some(oss_id)),
        "timer" => am.timer_file_oss_id = Set(Some(oss_id)),
        _ => {
            return Err(crate::error::AppError::Oss {
                code: None,
                msg: format!("不支持的岗位类型：{post_name}"),
            });
        }
    }
    Ok(())
}

/// 取小写扩展名
fn file_ext(filename: &str) -> Option<String> {
    filename.rsplit('.').next().map(|s| s.to_lowercase())
}

#[cfg(test)]
mod tests {
    use super::*;

    /// 岗位列读取与写入映射
    #[test]
    fn post_oss_column_mapping() {
        let ep = mangaepisodetb::Model {
            id: 1,
            manga_id: 1,
            manga_episode: "1".into(),
            episode_type: "MAIN".into(),
            manga_episode_name: None,
            provider_id: None,
            translator_id: None,
            proofreader_id: None,
            letterer_id: None,
            timer_id: None,
            reviewer_id: None,
            setup_time: chrono::Utc::now().naive_utc(),
            update_time: chrono::Utc::now().naive_utc(),
            translator_file: None,
            proofreader_file: None,
            timer_file: None,
            publish_link: None,
            provider_file_oss_id: Some(10),
            translator_file_oss_id: None,
            proofreader_file_oss_id: None,
            letterer_file_oss_id: None,
            timer_file_oss_id: None,
        };
        assert_eq!(read_post_oss_id(&ep, "provider"), Some(10));
        assert_eq!(read_post_oss_id(&ep, "unknown"), None);
    }

    #[test]
    fn uses_config_cdn_only_for_current_or_missing_bucket() {
        assert!(should_use_config_cdn(
            None,
            "",
            "manga-trans-1317356496",
            "ossdev.yuriful.top"
        ));
        assert!(should_use_config_cdn(
            Some(""),
            "https://manga-trans-1317356496.cos.ap-guangzhou.myqcloud.com/manga_1/file.txt",
            "manga-trans-1317356496",
            "ossdev.yuriful.top"
        ));
        assert!(should_use_config_cdn(
            Some("manga-trans-1317356496"),
            "https://ossdev.yuriful.top/manga_1/file.txt",
            "manga-trans-1317356496",
            "ossdev.yuriful.top"
        ));
    }

    #[test]
    fn rejects_config_cdn_for_different_bucket_or_domain() {
        assert!(!should_use_config_cdn(
            Some("manga-trans-prod-1317356496"),
            "https://oss.yuriful.top/manga_1/file.txt",
            "manga-trans-1317356496",
            "ossdev.yuriful.top"
        ));
        assert!(!should_use_config_cdn(
            None,
            "https://manga-trans-prod-1317356496.cos.ap-guangzhou.myqcloud.com/manga_1/file.txt",
            "manga-trans-1317356496",
            "ossdev.yuriful.top"
        ));
        assert!(!should_use_config_cdn(
            None,
            "https://oss.yuriful.top/manga_1/file.txt",
            "manga-trans-1317356496",
            "ossdev.yuriful.top"
        ));
        assert!(!should_use_config_cdn(
            None,
            "https://unknown.example.com/manga_1/file.txt",
            "manga-trans-1317356496",
            "ossdev.yuriful.top"
        ));
    }

    #[test]
    fn extracts_bucket_from_cos_url() {
        assert_eq!(
            cos_bucket_from_url(
                "https://manga-trans-prod-1317356496.cos.ap-guangzhou.myqcloud.com/manga_1/file.txt"
            )
            .as_deref(),
            Some("manga-trans-prod-1317356496")
        );
    }
}