# 分镜-项目素材关联服务

> **文档版本**：v1.0  
> **最后更新**：2026-02-02  
> **符合规范**：jointo-tech-stack v1.0

---

## 目录

1. [服务概述](#服务概述)
2. [核心功能](#核心功能)
3. [数据库设计](#数据库设计)
4. [服务实现](#服务实现)
5. [API 接口](#api-接口)

---

## 服务概述

分镜-项目素材关联服务负责管理分镜与项目素材（角色、场景、道具、实拍）的关联关系。该服务是 `StoryboardService` 的一部分，专门处理素材关联逻辑。

### 职责

- 将项目素材添加到分镜
- 从分镜移除项目素材
- 查询分镜使用的素材
- 维护素材的引用计数（`usage_count`）

### 设计原则

- **引用计数维护**：添加/删除关联时自动更新 `project_resources.usage_count`
- **事务安全**：关联操作和计数更新在同一事务中完成
- **删除保护**：`usage_count > 0` 的素材禁止删除（除非强制删除）
- **软删除分镜不计入**：只统计 `deleted_at IS NULL` 的分镜

---

## 核心功能

### 1. 添加素材到分镜

- 创建 `storyboard_resources` 关联记录
- 自动增加 `project_resources.usage_count` +1
- 事务安全

### 2. 从分镜移除素材

- 删除 `storyboard_resources` 关联记录
- 自动减少 `project_resources.usage_count` -1
- 事务安全

### 3. 查询分镜素材

- 按类型查询（角色/场景/道具/实拍）
- 按标签查询
- 返回完整素材信息

### 4. 批量操作

- 批量添加素材
- 批量移除素材
- 批量更新引用计数

---

## 数据库设计

### storyboard_resources 关联表

```sql
CREATE TABLE storyboard_resources (
    storyboard_resource_id UUID PRIMARY KEY,  -- 应用层生成 UUID v7
    storyboard_id UUID NOT NULL,  -- 无物理外键，应用层校验
    project_resource_id UUID NOT NULL,  -- 无物理外键，应用层校验
    resource_type SMALLINT NOT NULL,  -- 1=character, 2=scene, 3=prop, 4=footage
    display_order INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT storyboard_resources_unique UNIQUE (storyboard_id, project_resource_id) NULLS NOT DISTINCT
);

-- 索引（手动创建，确保引用完整性查询性能）
CREATE INDEX idx_storyboard_resources_storyboard_id ON storyboard_resources (storyboard_id);
CREATE INDEX idx_storyboard_resources_project_resource_id ON storyboard_resources (project_resource_id);
CREATE INDEX idx_storyboard_resources_type ON storyboard_resources (resource_type);
```

### project_resources 表（引用计数字段）

```sql
-- 在 project_resources 表中添加 usage_count 字段
ALTER TABLE project_resources ADD COLUMN usage_count INTEGER NOT NULL DEFAULT 0;

-- 添加 CHECK 约束
ALTER TABLE project_resources ADD CONSTRAINT project_resources_usage_count_check CHECK (usage_count >= 0);

-- 添加索引
CREATE INDEX idx_project_resources_usage_count ON project_resources (usage_count) WHERE deleted_at IS NULL;

-- 添加注释
COMMENT ON COLUMN project_resources.usage_count IS '引用计数（被多少个分镜使用，由 StoryboardService 维护）';
```

---

## 服务实现

### StoryboardService 类（素材关联部分）

```python
# app/services/storyboard_service.py
from typing import List, Optional, Dict, Any
from uuid import UUID as PG_UUID
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, update, delete
from app.models.storyboard import Storyboard
from app.models.project_resource import ProjectResource
from app.models.storyboard_resource import StoryboardResource
from app.repositories.storyboard_repository import StoryboardRepository
from app.repositories.project_resource_repository import ProjectResourceRepository
from app.core.exceptions import NotFoundError, ValidationError, PermissionError
from app.core.database import generate_uuid_v7
import logging

logger = logging.getLogger(__name__)

class StoryboardService:
    def __init__(
        self,
        db: AsyncSession,
        storyboard_repo: StoryboardRepository,
        project_resource_repo: ProjectResourceRepository
    ):
        self.db = db
        self.storyboard_repo = storyboard_repo
        self.project_resource_repo = project_resource_repo

    # ==================== 素材关联管理 ====================

    async def add_resource_to_storyboard(
        self,
        user_id: uuid.UUID,
        storyboard_id: uuid.UUID,
        project_resource_id: uuid.UUID,
        display_order: int = 0
    ) -> StoryboardResource:
        """
        将项目素材添加到分镜
        
        自动维护 project_resources.usage_count +1
        """
        logger.info(
            "添加素材到分镜: user_id=%s, storyboard_id=%s, project_resource_id=%s",
            user_id,
            storyboard_id,
            project_resource_id
        )
        
        # 检查分镜权限
        storyboard = await self.storyboard_repo.get_by_id(storyboard_id)
        if not storyboard:
            raise NotFoundError("分镜不存在")
        
        await self._check_project_permission(user_id, storyboard.project_id, 'editor')
        
        # 检查素材存在性和归属
        resource = await self.project_resource_repo.get_by_id(project_resource_id)
        if not resource:
            raise NotFoundError("素材不存在")
        
        if resource.project_id != storyboard.project_id:
            raise ValidationError("素材不属于当前项目")
        
        # 检查是否已关联
        existing = await self.db.execute(
            select(StoryboardResource).where(
                StoryboardResource.storyboard_id == storyboard_id,
                StoryboardResource.project_resource_id == project_resource_id
            )
        )
        if existing.scalar_one_or_none():
            raise ValidationError("素材已关联到该分镜")
        
        # 开始事务：创建关联 + 更新引用计数
        try:
            # 1. 创建关联记录
            association = StoryboardResource(
                storyboard_resource_id=generate_uuid_v7(),
                storyboard_id=storyboard_id,
                project_resource_id=project_resource_id,
                resource_type=resource.type,
                display_order=display_order
            )
            self.db.add(association)
            
            # 2. 增加引用计数
            await self.db.execute(
                update(ProjectResource)
                .where(ProjectResource.project_resource_id == project_resource_id)
                .values(usage_count=ProjectResource.usage_count + 1)
            )
            
            # 3. 提交事务
            await self.db.commit()
            await self.db.refresh(association)
            
            logger.info(
                "素材关联成功: storyboard_resource_id=%s, usage_count=%d",
                association.storyboard_resource_id,
                resource.usage_count + 1
            )
            
            return association
            
        except Exception as e:
            await self.db.rollback()
            logger.error(
                "添加素材到分镜失败: storyboard_id=%s, project_resource_id=%s",
                storyboard_id,
                project_resource_id,
                exc_info=True
            )
            raise

    async def remove_resource_from_storyboard(
        self,
        user_id: uuid.UUID,
        storyboard_id: uuid.UUID,
        project_resource_id: uuid.UUID
    ) -> None:
        """
        从分镜移除项目素材
        
        自动维护 project_resources.usage_count -1
        """
        logger.info(
            "从分镜移除素材: user_id=%s, storyboard_id=%s, project_resource_id=%s",
            user_id,
            storyboard_id,
            project_resource_id
        )
        
        # 检查分镜权限
        storyboard = await self.storyboard_repo.get_by_id(storyboard_id)
        if not storyboard:
            raise NotFoundError("分镜不存在")
        
        await self._check_project_permission(user_id, storyboard.project_id, 'editor')
        
        # 检查关联是否存在
        result = await self.db.execute(
            select(StoryboardResource).where(
                StoryboardResource.storyboard_id == storyboard_id,
                StoryboardResource.project_resource_id == project_resource_id
            )
        )
        association = result.scalar_one_or_none()
        if not association:
            raise NotFoundError("素材未关联到该分镜")
        
        # 开始事务：删除关联 + 更新引用计数
        try:
            # 1. 删除关联记录
            await self.db.execute(
                delete(StoryboardResource).where(
                    StoryboardResource.storyboard_resource_id == association.storyboard_resource_id
                )
            )
            
            # 2. 减少引用计数
            await self.db.execute(
                update(ProjectResource)
                .where(ProjectResource.project_resource_id == project_resource_id)
                .values(usage_count=ProjectResource.usage_count - 1)
            )
            
            # 3. 提交事务
            await self.db.commit()
            
            logger.info(
                "素材移除成功: storyboard_resource_id=%s",
                association.storyboard_resource_id
            )
            
        except Exception as e:
            await self.db.rollback()
            logger.error(
                "从分镜移除素材失败: storyboard_id=%s, project_resource_id=%s",
                storyboard_id,
                project_resource_id,
                exc_info=True
            )
            raise

    async def get_storyboard_resources(
        self,
        user_id: uuid.UUID,
        storyboard_id: uuid.UUID,
        resource_type: Optional[int] = None
    ) -> List[Dict[str, Any]]:
        """
        获取分镜使用的素材列表
        
        Args:
            user_id: 用户ID
            storyboard_id: 分镜ID
            resource_type: 素材类型过滤（可选）
        
        Returns:
            素材列表（包含完整素材信息）
        """
        # 检查分镜权限
        storyboard = await self.storyboard_repo.get_by_id(storyboard_id)
        if not storyboard:
            raise NotFoundError("分镜不存在")
        
        await self._check_project_permission(user_id, storyboard.project_id, 'viewer')
        
        # 查询关联素材
        query = (
            select(StoryboardResource, ProjectResource)
            .join(
                ProjectResource,
                StoryboardResource.project_resource_id == ProjectResource.project_resource_id
            )
            .where(StoryboardResource.storyboard_id == storyboard_id)
            .where(ProjectResource.deleted_at.is_(None))
        )
        
        if resource_type is not None:
            query = query.where(StoryboardResource.resource_type == resource_type)
        
        query = query.order_by(StoryboardResource.display_order)
        
        result = await self.db.execute(query)
        rows = result.all()
        
        resources = []
        for association, resource in rows:
            resources.append({
                'storyboard_resource_id': str(association.storyboard_resource_id),
                'project_resource_id': str(resource.project_resource_id),
                'name': resource.name,
                'type': resource.type,
                'file_url': resource.file_url,
                'thumbnail_url': resource.thumbnail_url,
                'element_tag_id': str(resource.element_tag_id) if resource.element_tag_id else None,
                'element_name': resource.element_name,
                'tag_label': resource.tag_label,
                'display_order': association.display_order,
                'created_at': association.created_at
            })
        
        return resources

    async def batch_add_resources(
        self,
        user_id: uuid.UUID,
        storyboard_id: uuid.UUID,
        resource_ids: List[uuid.UUID]
    ) -> List[StoryboardResource]:
        """批量添加素材到分镜"""
        associations = []
        for resource_id in resource_ids:
            association = await self.add_resource_to_storyboard(
                user_id, storyboard_id, resource_id
            )
            associations.append(association)
        return associations

    async def batch_remove_resources(
        self,
        user_id: uuid.UUID,
        storyboard_id: uuid.UUID,
        resource_ids: List[uuid.UUID]
    ) -> None:
        """批量从分镜移除素材"""
        for resource_id in resource_ids:
            await self.remove_resource_from_storyboard(
                user_id, storyboard_id, resource_id
            )

    # ==================== 辅助方法 ====================

    async def _check_project_permission(
        self,
        user_id: uuid.UUID,
        project_id: uuid.UUID,
        required_role: str = 'viewer'
    ) -> None:
        """检查项目权限（应用层引用完整性校验）"""
        from app.services.project_service import ProjectService
        from app.repositories.project_repository import ProjectRepository
        
        project_repo = ProjectRepository(self.db)
        project_service = ProjectService(self.db, project_repo)
        
        has_permission = await project_service.check_user_permission(
            user_id, project_id, required_role
        )
        if not has_permission:
            raise PermissionError("没有权限访问此项目")
```

---

## API 接口

### 1. 添加素材到分镜

```http
POST /api/v1/storyboards/{storyboard_id}/resources
```

**请求体**：

```json
{
  "project_resource_id": "019d1234-5678-7abc-def0-123456789abc",
  "display_order": 0
}
```

**响应**：

```json
{
  "success": true,
  "code": 200,
  "message": "Success",
  "data": {
    "storyboard_resource_id": "019d1234-5678-7abc-def0-999999999999",
    "storyboard_id": "019d1234-5678-7abc-def0-111111111111",
    "project_resource_id": "019d1234-5678-7abc-def0-123456789abc",
    "resource_type": 1,
    "display_order": 0,
    "created_at": "2026-02-02T10:00:00+00:00"
  },
  "timestamp": "2026-02-03T10:00:00Z"
}
```

### 2. 从分镜移除素材

```http
DELETE /api/v1/storyboards/{storyboard_id}/resources/{project_resource_id}
```

**响应**：

```json
{
  "success": true,
  "code": 200,
  "message": "素材已移除",
  "data": null,
  "timestamp": "2026-02-03T10:00:00Z"
}
```

### 3. 获取分镜素材列表

```http
GET /api/v1/storyboards/{storyboard_id}/resources?type=1
```

**查询参数**：
- `type`: 素材类型过滤（可选：1=角色, 2=场景, 3=道具, 4=实拍）

**响应**：

```json
{
  "success": true,
  "code": 200,
  "message": "Success",
  "data": [
    {
      "storyboard_resource_id": "019d1234-5678-7abc-def0-999999999999",
      "project_resource_id": "019d1234-5678-7abc-def0-123456789abc",
      "name": "主角-张三-少年形象",
      "type": 1,
      "file_url": "https://storage.jointo.ai/project_resource/character/1/abc123.png",
      "thumbnail_url": "https://storage.jointo.ai/thumbnail/character/1/thumb_abc123.png",
      "element_tag_id": "019d1234-5678-7abc-def0-666666666666",
      "element_name": "张三",
      "tag_label": "少年",
      "display_order": 0,
      "created_at": "2026-02-02T10:00:00+00:00"
    }
  ],
  "timestamp": "2026-02-03T10:00:00Z"
}
```

### 4. 批量添加素材

```http
POST /api/v1/storyboards/{storyboard_id}/resources/batch
```

**请求体**：

```json
{
  "resource_ids": [
    "019d1234-5678-7abc-def0-123456789abc",
    "019d1234-5678-7abc-def0-123456789abd"
  ]
}
```

### 5. 批量移除素材

```http
DELETE /api/v1/storyboards/{storyboard_id}/resources/batch
```

**请求体**：

```json
{
  "resource_ids": [
    "019d1234-5678-7abc-def0-123456789abc",
    "019d1234-5678-7abc-def0-123456789abd"
  ]
}
```

---

## 引用计数维护机制

### 1. 计数规则

- **添加关联**：`usage_count` +1
- **删除关联**：`usage_count` -1
- **软删除分镜**：不影响 `usage_count`（分镜恢复后关联仍有效）
- **硬删除分镜**：批量减少所有关联素材的 `usage_count`

### 2. 事务安全

所有关联操作和计数更新在同一事务中完成：

```python
try:
    # 1. 创建/删除关联
    # 2. 更新引用计数
    await self.db.commit()
except Exception:
    await self.db.rollback()
    raise
```

### 3. 删除保护

在 `ProjectResourceService.delete_resource()` 中检查 `usage_count`：

```python
if resource.usage_count > 0 and not force:
    raise ValidationError(
        f"该素材正在被 {resource.usage_count} 个分镜使用，无法删除。"
        "请先从分镜中移除该素材，或使用强制删除。"
    )
```

### 4. 数据一致性

- **初始化**：新素材 `usage_count` 默认为 0
- **约束**：CHECK 约束确保 `usage_count >= 0`
- **索引**：`idx_project_resources_usage_count` 加速查询
- **审计**：日志记录所有计数变更

---

## 相关文档

- [项目素材服务](./project-resource-service.md)
- [分镜管理服务](./storyboard-service.md)
- [ADR-008: 分镜统一关联表](../../architecture/adrs/008-storyboard-unified-association-table.md)

---

## 变更记录

**v1.1 (2026-02-03)**

- ✅ 修复 UUID v7 生成方式（移除数据库 DEFAULT，改为应用层生成）
- ✅ 修复日志格式化（统一使用 %-formatting）
- ✅ 修复异常日志（添加 exc_info=True）
- ✅ 修复 API 响应格式（统一使用 ApiResponse 格式）
- ✅ 移除数据库注释（将在迁移脚本中添加）
- ✅ 符合 jointo-tech-stack v1.0 规范

**v1.0 (2026-02-02)**

- 初始版本
- 定义分镜-项目素材关联服务
- 实现引用计数维护机制
- 添加事务安全保证
- 添加批量操作支持

---

**文档版本**：v1.0  
**最后更新**：2026-02-02