LabelingSystem/backend/routers/project.py

"""
Project API router.
Provides CRUD endpoints for project management.
"""
from logging import Logger


import logging
import uuid
from datetime import datetime
from typing import List, Optional
from fastapi import APIRouter, HTTPException, status, Request, Query
from database import get_db_connection
from schemas.project import (
    ProjectCreate,
    ProjectUpdate,
    ProjectResponse,
    ProjectStatus,
    ProjectSource,
    ProjectStatusUpdate,
    ProjectConfigUpdate,
    ProjectResponseExtended,
    ProjectListPaginationResponse,
)
from models import Project

router = APIRouter(
    prefix="/api/projects",
    tags=["projects"]
)
logger: Logger = logging.getLogger(__name__)

# 定义合法的状态转换
VALID_STATUS_TRANSITIONS = {
    ProjectStatus.DRAFT: [ProjectStatus.CONFIGURING],
    ProjectStatus.CONFIGURING: [ProjectStatus.READY, ProjectStatus.DRAFT],
    ProjectStatus.READY: [ProjectStatus.IN_PROGRESS, ProjectStatus.CONFIGURING],
    ProjectStatus.IN_PROGRESS: [ProjectStatus.COMPLETED, ProjectStatus.READY],
    ProjectStatus.COMPLETED: [ProjectStatus.IN_PROGRESS],  # 允许重新开放
}


@router.get("", response_model=ProjectListPaginationResponse)
async def list_projects(
    request: Request,
    status_filter: Optional[ProjectStatus] = Query(None, alias="status", description="按状态筛选"),
    source_filter: Optional[ProjectSource] = Query(None, alias="source", description="按来源筛选"),
    page: int = Query(1, ge=1, description="页码"),
    page_size: int = Query(20, ge=1, le=100, description="每页数量"),
):
    """
    List projects with extended information.

    For admin users: Returns all projects with their total task counts.
    For annotator users: Returns only projects that have tasks assigned to them,
                         with task counts reflecting only their assigned tasks.

    Query Parameters:
        status: Filter by project status (draft, configuring, ready, in_progress, completed)
        source: Filter by project source (internal, external)
        page: Page number (default: 1)
        page_size: Number of items per page (default: 20, max: 100)

    Requires authentication.
    """
    user = request.state.user
    user_id = user["id"]
    user_role = user["role"]

    with get_db_connection() as conn:
        cursor = conn.cursor()

        # Build base query
        base_from = """
            FROM projects p
            LEFT JOIN tasks t ON p.id = t.project_id
        """

        if user_role == "admin":
            # 管理员：返回所有项目及其全部任务统计
            select_cols = """
                p.id,
                p.name,
                p.description,
                p.config,
                p.task_type,
                p.status,
                p.source,
                p.external_id,
                p.created_at,
                p.updated_at,
                COUNT(t.id) as task_count,
                SUM(CASE WHEN t.status = 'completed' THEN 1 ELSE 0 END) as completed_task_count,
                SUM(CASE WHEN t.assigned_to IS NOT NULL THEN 1 ELSE 0 END) as assigned_task_count
            """

            conditions = []
            params = []

            if status_filter:
                conditions.append("p.status = ?")
                params.append(status_filter.value)

            if source_filter:
                conditions.append("p.source = ?")
                params.append(source_filter.value)

            where_clause = " WHERE " + " AND ".join(conditions) if conditions else ""

            # Count query for pagination
            count_query = f"""
                SELECT COUNT(DISTINCT p.id) as total
                {base_from}
                {where_clause}
            """

            cursor.execute(count_query, params)
            total = cursor.fetchone()["total"]

            # Calculate pagination
            total_pages = (total + page_size - 1) // page_size
            offset = (page - 1) * page_size

            # Main query with pagination
            query = f"""
                SELECT {select_cols}
                {base_from}
                {where_clause}
                GROUP BY p.id
                ORDER BY p.created_at DESC
                LIMIT ? OFFSET ?
            """
            params.extend([page_size, offset])
        else:
            # 标注员：只返回有分配给他们任务的项目，任务数量只统计分配给他们的任务
            select_cols = """
                p.id,
                p.name,
                p.description,
                p.config,
                p.task_type,
                p.status,
                p.source,
                p.external_id,
                p.created_at,
                p.updated_at,
                COUNT(t.id) as task_count,
                SUM(CASE WHEN t.status = 'completed' THEN 1 ELSE 0 END) as completed_task_count,
                COUNT(t.id) as assigned_task_count
            """

            conditions = ["t.assigned_to = ?"]
            params = [user_id]

            if status_filter:
                conditions.append("p.status = ?")
                params.append(status_filter.value)

            if source_filter:
                conditions.append("p.source = ?")
                params.append(source_filter.value)

            # 为标注员构建 where 子句（用于主查询）
            # 从 conditions 中移除 t.assigned_to = ?，因为它已经在 JOIN 中了
            main_query_conditions = [cond for cond in conditions if cond != "t.assigned_to = ?"]
            where_clause = " WHERE " + " AND ".join(main_query_conditions) if main_query_conditions else ""

            # 为 count 查询构建 where 子句（移除 t.assigned_to = ? 条件）
            count_where_clause = where_clause

            # Count query for pagination
            count_query = f"""
                SELECT COUNT(DISTINCT p.id) as total
                FROM projects p
                INNER JOIN tasks t ON p.id = t.project_id AND t.assigned_to = ?
                {count_where_clause}
            """
            count_params = [user_id]
            if status_filter:
                count_params.append(status_filter.value)
            if source_filter:
                count_params.append(source_filter.value)

            cursor.execute(count_query, count_params)
            total = cursor.fetchone()["total"]

            # Calculate pagination
            total_pages = (total + page_size - 1) // page_size
            offset = (page - 1) * page_size

            # Main query with pagination
            query = f"""
                SELECT {select_cols}
                FROM projects p
                INNER JOIN tasks t ON p.id = t.project_id AND t.assigned_to = ?
                {where_clause}
                GROUP BY p.id
                HAVING COUNT(t.id) > 0
                ORDER BY p.created_at DESC
                LIMIT ? OFFSET ?
            """
            params.extend([page_size, offset])

        cursor.execute(query, params)
        rows = cursor.fetchall()

        projects = []
        for row in rows:
            projects.append(ProjectResponseExtended(
                id=row["id"],
                name=row["name"],
                description=row["description"] or "",
                config=row["config"],
                task_type=row["task_type"],
                status=ProjectStatus(row["status"]) if row["status"] else ProjectStatus.DRAFT,
                source=ProjectSource(row["source"]) if row["source"] else ProjectSource.INTERNAL,
                external_id=row["external_id"],
                created_at=row["created_at"],
                updated_at=row["updated_at"],
                task_count=row["task_count"] or 0,
                completed_task_count=row["completed_task_count"] or 0,
                assigned_task_count=row["assigned_task_count"] or 0,
            ))

        return ProjectListPaginationResponse(
            projects=projects,
            total=total,
            page=page,
            page_size=page_size,
            total_pages=total_pages,
            has_next=page < total_pages,
            has_prev=page > 1
        )


@router.post("", response_model=ProjectResponse, status_code=status.HTTP_201_CREATED)
async def create_project(request: Request, project: ProjectCreate):
    """
    Create a new project.
    
    Args:
        request: FastAPI Request object (contains user info)
        project: Project creation data
    
    Returns:
        Created project with generated ID
        
    Requires authentication.
    """
    # Generate unique ID
    project_id = f"proj_{uuid.uuid4().hex[:12]}"
    
    with get_db_connection() as conn:
        cursor = conn.cursor()
        
        # Insert new project with task_type
        cursor.execute("""
            INSERT INTO projects (id, name, description, config, task_type)
            VALUES (?, ?, ?, ?, ?)
        """, (
            project_id,
            project.name,
            project.description,
            project.config,
            project.task_type
        ))
        
        # Fetch the created project
        cursor.execute("""
            SELECT id, name, description, config, created_at
            FROM projects
            WHERE id = ?
        """, (project_id,))
        
        row = cursor.fetchone()
        
        return ProjectResponse(
            id=row["id"],
            name=row["name"],
            description=row["description"] or "",
            config=row["config"],
            created_at=row["created_at"],
            task_count=0
        )


@router.get("/{project_id}", response_model=ProjectResponse)
async def get_project(request: Request, project_id: str):
    """
    Get project by ID.
    
    Args:
        request: FastAPI Request object (contains user info)
        project_id: Project unique identifier
    
    Returns:
        Project details with task count
    
    Raises:
        HTTPException: 404 if project not found
        
    Requires authentication.
    """
    with get_db_connection() as conn:
        cursor = conn.cursor()
        
        # Get project with task count
        cursor.execute("""
            SELECT 
                p.id,
                p.name,
                p.description,
                p.config,
                p.created_at,
                COUNT(t.id) as task_count
            FROM projects p
            LEFT JOIN tasks t ON p.id = t.project_id
            WHERE p.id = ?
            GROUP BY p.id
        """, (project_id,))
        
        row = cursor.fetchone()
        
        if not row:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail=f"Project with id '{project_id}' not found"
            )
        
        return ProjectResponse(
            id=row["id"],
            name=row["name"],
            description=row["description"] or "",
            config=row["config"],
            created_at=row["created_at"],
            task_count=row["task_count"]
        )


@router.put("/{project_id}", response_model=ProjectResponse)
async def update_project(request: Request, project_id: str, project: ProjectUpdate):
    """
    Update an existing project.
    
    Args:
        request: FastAPI Request object (contains user info)
        project_id: Project unique identifier
        project: Project update data
    
    Returns:
        Updated project details
    
    Raises:
        HTTPException: 404 if project not found
        
    Requires authentication.
    """
    with get_db_connection() as conn:
        cursor = conn.cursor()
        
        # Check if project exists
        cursor.execute("SELECT id FROM projects WHERE id = ?", (project_id,))
        if not cursor.fetchone():
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail=f"Project with id '{project_id}' not found"
            )
        
        # Build update query dynamically based on provided fields
        update_fields = []
        update_values = []
        
        if project.name is not None:
            update_fields.append("name = ?")
            update_values.append(project.name)
        
        if project.description is not None:
            update_fields.append("description = ?")
            update_values.append(project.description)
        
        if project.config is not None:
            update_fields.append("config = ?")
            update_values.append(project.config)
        
        if not update_fields:
            # No fields to update, just return current project
            cursor.execute("""
                SELECT 
                    p.id,
                    p.name,
                    p.description,
                    p.config,
                    p.created_at,
                    COUNT(t.id) as task_count
                FROM projects p
                LEFT JOIN tasks t ON p.id = t.project_id
                WHERE p.id = ?
                GROUP BY p.id
            """, (project_id,))
            row = cursor.fetchone()
            return ProjectResponse(
                id=row["id"],
                name=row["name"],
                description=row["description"] or "",
                config=row["config"],
                created_at=row["created_at"],
                task_count=row["task_count"]
            )
        
        # Execute update
        update_values.append(project_id)
        cursor.execute(f"""
            UPDATE projects
            SET {', '.join(update_fields)}
            WHERE id = ?
        """, update_values)
        
        # Fetch and return updated project
        cursor.execute("""
            SELECT 
                p.id,
                p.name,
                p.description,
                p.config,
                p.created_at,
                COUNT(t.id) as task_count
            FROM projects p
            LEFT JOIN tasks t ON p.id = t.project_id
            WHERE p.id = ?
            GROUP BY p.id
        """, (project_id,))
        
        row = cursor.fetchone()
        return ProjectResponse(
            id=row["id"],
            name=row["name"],
            description=row["description"] or "",
            config=row["config"],
            created_at=row["created_at"],
            task_count=row["task_count"]
        )


@router.delete("/{project_id}", status_code=status.HTTP_204_NO_CONTENT)
async def delete_project(request: Request, project_id: str):
    """
    Delete a project and all associated tasks.
    
    Args:
        request: FastAPI Request object (contains user info)
        project_id: Project unique identifier
    
    Raises:
        HTTPException: 404 if project not found
        HTTPException: 403 if user is not admin
        
    Requires authentication and admin role.
    """
    # Check if user has admin role
    user = request.state.user
    if user["role"] != "admin":
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="只有管理员可以删除项目"
        )
    
    with get_db_connection() as conn:
        cursor = conn.cursor()
        
        # Check if project exists
        cursor.execute("SELECT id FROM projects WHERE id = ?", (project_id,))
        if not cursor.fetchone():
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail=f"Project with id '{project_id}' not found"
            )
        
        # Delete project (cascade will delete tasks and annotations)
        cursor.execute("DELETE FROM projects WHERE id = ?", (project_id,))
        
        return None


@router.put("/{project_id}/status", response_model=ProjectResponseExtended)
async def update_project_status(request: Request, project_id: str, status_update: ProjectStatusUpdate):
    """
    Update project status with validation.
    
    Only allows valid status transitions:
    - draft → configuring
    - configuring → ready, draft
    - ready → in_progress, configuring
    - in_progress → completed, ready
    - completed → in_progress (reopen)
    
    Args:
        request: FastAPI Request object (contains user info)
        project_id: Project unique identifier
        status_update: New status
    
    Returns:
        Updated project details
    
    Raises:
        HTTPException: 404 if project not found
        HTTPException: 400 if status transition is invalid
        HTTPException: 403 if user is not admin
    """
    # Check if user has admin role
    user = request.state.user
    if user["role"] != "admin":
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="只有管理员可以更新项目状态"
        )
    
    with get_db_connection() as conn:
        cursor = conn.cursor()
        
        # Get current project status
        cursor.execute("SELECT id, status FROM projects WHERE id = ?", (project_id,))
        row = cursor.fetchone()
        
        if not row:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail=f"项目 '{project_id}' 不存在"
            )
        
        current_status = ProjectStatus(row["status"]) if row["status"] else ProjectStatus.DRAFT
        new_status = status_update.status
        
        # Validate status transition
        valid_transitions = VALID_STATUS_TRANSITIONS.get(current_status, [])
        if new_status not in valid_transitions:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail=f"无效的状态转换: {current_status.value} → {new_status.value}。允许的转换: {[s.value for s in valid_transitions]}"
            )
        
        # Update status
        cursor.execute("""
            UPDATE projects 
            SET status = ?, updated_at = ?
            WHERE id = ?
        """, (new_status.value, datetime.now(), project_id))
        
        # Fetch and return updated project
        cursor.execute("""
            SELECT 
                p.id,
                p.name,
                p.description,
                p.config,
                p.task_type,
                p.status,
                p.source,
                p.external_id,
                p.created_at,
                p.updated_at,
                COUNT(t.id) as task_count,
                SUM(CASE WHEN t.status = 'completed' THEN 1 ELSE 0 END) as completed_task_count,
                SUM(CASE WHEN t.assigned_to IS NOT NULL THEN 1 ELSE 0 END) as assigned_task_count
            FROM projects p
            LEFT JOIN tasks t ON p.id = t.project_id
            WHERE p.id = ?
            GROUP BY p.id
        """, (project_id,))
        
        row = cursor.fetchone()
        return ProjectResponseExtended(
            id=row["id"],
            name=row["name"],
            description=row["description"] or "",
            config=row["config"],
            task_type=row["task_type"],
            status=ProjectStatus(row["status"]) if row["status"] else ProjectStatus.DRAFT,
            source=ProjectSource(row["source"]) if row["source"] else ProjectSource.INTERNAL,
            external_id=row["external_id"],
            created_at=row["created_at"],
            updated_at=row["updated_at"],
            task_count=row["task_count"] or 0,
            completed_task_count=row["completed_task_count"] or 0,
            assigned_task_count=row["assigned_task_count"] or 0,
        )


@router.patch("/{project_id}/mark-completed", response_model=ProjectResponseExtended)
async def mark_project_completed(request: Request, project_id: str):
    """
    Mark a project as completed.
    
    This endpoint is specifically for marking a project as completed when
    all tasks are done (100% completion rate). It validates that the project
    has 100% completion before allowing the status change.
    
    Args:
        request: FastAPI Request object (contains user info)
        project_id: Project unique identifier
    
    Returns:
        Updated project details with completed status
    
    Raises:
        HTTPException: 404 if project not found
        HTTPException: 400 if project is not 100% complete
        HTTPException: 403 if user is not admin
    """
    # Check if user has admin role
    user = request.state.user
    if user["role"] != "admin":
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="只有管理员可以标记项目为已完成"
        )
    
    with get_db_connection() as conn:
        cursor = conn.cursor()
        
        # Get project with task statistics
        cursor.execute("""
            SELECT 
                p.id,
                p.name,
                p.description,
                p.config,
                p.task_type,
                p.status,
                p.source,
                p.external_id,
                p.created_at,
                p.updated_at,
                COUNT(t.id) as task_count,
                SUM(CASE WHEN t.status = 'completed' THEN 1 ELSE 0 END) as completed_task_count,
                SUM(CASE WHEN t.assigned_to IS NOT NULL THEN 1 ELSE 0 END) as assigned_task_count
            FROM projects p
            LEFT JOIN tasks t ON p.id = t.project_id
            WHERE p.id = ?
            GROUP BY p.id
        """, (project_id,))
        
        row = cursor.fetchone()
        
        if not row:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail=f"项目 '{project_id}' 不存在"
            )
        
        task_count = row["task_count"] or 0
        completed_task_count = row["completed_task_count"] or 0
        current_status = ProjectStatus(row["status"]) if row["status"] else ProjectStatus.DRAFT
        
        # Check if project has tasks
        if task_count == 0:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail="项目没有任务，无法标记为已完成"
            )
        
        # Check if all tasks are completed (100% completion rate)
        completion_rate = (completed_task_count / task_count) * 100
        if completion_rate < 100:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail=f"项目未完成，当前完成率: {completion_rate:.1f}%。只有 100% 完成的项目才能标记为已完成"
            )
        
        # Check if project is in a valid state for completion
        if current_status == ProjectStatus.COMPLETED:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail="项目已经是已完成状态"
            )
        
        if current_status not in [ProjectStatus.IN_PROGRESS, ProjectStatus.READY]:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail=f"只有进行中或待分发状态的项目才能标记为已完成，当前状态: {current_status.value}"
            )
        
        # Update status to completed with completion timestamp
        completed_at = datetime.now()
        cursor.execute("""
            UPDATE projects 
            SET status = ?, updated_at = ?
            WHERE id = ?
        """, (ProjectStatus.COMPLETED.value, completed_at, project_id))
        
        return ProjectResponseExtended(
            id=row["id"],
            name=row["name"],
            description=row["description"] or "",
            config=row["config"],
            task_type=row["task_type"],
            status=ProjectStatus.COMPLETED,
            source=ProjectSource(row["source"]) if row["source"] else ProjectSource.INTERNAL,
            external_id=row["external_id"],
            created_at=row["created_at"],
            updated_at=completed_at,
            task_count=task_count,
            completed_task_count=completed_task_count,
            assigned_task_count=row["assigned_task_count"] or 0,
        )


@router.put("/{project_id}/config", response_model=ProjectResponseExtended)
async def update_project_config(request: Request, project_id: str, config_update: ProjectConfigUpdate):
    """
    Update project configuration (XML config and labels).
    
    This endpoint is used by admins to configure the labeling interface
    for projects created by external systems.
    
    Args:
        request: FastAPI Request object (contains user info)
        project_id: Project unique identifier
        config_update: New configuration
    
    Returns:
        Updated project details
    
    Raises:
        HTTPException: 404 if project not found
        HTTPException: 400 if config is invalid
        HTTPException: 403 if user is not admin
    """
    # Check if user has admin role
    user = request.state.user
    if user["role"] != "admin":
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="只有管理员可以更新项目配置"
        )
    
    with get_db_connection() as conn:
        cursor = conn.cursor()
        
        # Check if project exists
        cursor.execute("SELECT id, status FROM projects WHERE id = ?", (project_id,))
        row = cursor.fetchone()
        
        if not row:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail=f"项目 '{project_id}' 不存在"
            )
        
        current_status = ProjectStatus(row["status"]) if row["status"] else ProjectStatus.DRAFT
        
        # Only allow config updates in draft or configuring status
        if current_status not in [ProjectStatus.DRAFT, ProjectStatus.CONFIGURING]:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail=f"只能在 draft 或 configuring 状态下更新配置，当前状态: {current_status.value}"
            )
        
        # Validate XML config (basic check)
        config = config_update.config.strip()
        if not config.startswith("<") or not config.endswith(">"):
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail="无效的XML配置格式"
            )
        
        # Update config and set status to configuring if it was draft
        new_status = ProjectStatus.CONFIGURING if current_status == ProjectStatus.DRAFT else current_status
        
        # Build update query based on provided fields
        if config_update.task_type:
            cursor.execute("""
                UPDATE projects 
                SET config = ?, task_type = ?, status = ?, updated_at = ?
                WHERE id = ?
            """, (config, config_update.task_type, new_status.value, datetime.now(), project_id))
        else:
            cursor.execute("""
                UPDATE projects 
                SET config = ?, status = ?, updated_at = ?
                WHERE id = ?
            """, (config, new_status.value, datetime.now(), project_id))
        
        # Fetch and return updated project
        cursor.execute("""
            SELECT 
                p.id,
                p.name,
                p.description,
                p.config,
                p.task_type,
                p.status,
                p.source,
                p.external_id,
                p.created_at,
                p.updated_at,
                COUNT(t.id) as task_count,
                SUM(CASE WHEN t.status = 'completed' THEN 1 ELSE 0 END) as completed_task_count,
                SUM(CASE WHEN t.assigned_to IS NOT NULL THEN 1 ELSE 0 END) as assigned_task_count
            FROM projects p
            LEFT JOIN tasks t ON p.id = t.project_id
            WHERE p.id = ?
            GROUP BY p.id
        """, (project_id,))
        
        row = cursor.fetchone()
        return ProjectResponseExtended(
            id=row["id"],
            name=row["name"],
            description=row["description"] or "",
            config=row["config"],
            task_type=row["task_type"],
            status=ProjectStatus(row["status"]) if row["status"] else ProjectStatus.DRAFT,
            source=ProjectSource(row["source"]) if row["source"] else ProjectSource.INTERNAL,
            external_id=row["external_id"],
            created_at=row["created_at"],
            updated_at=row["updated_at"],
            task_count=row["task_count"] or 0,
            completed_task_count=row["completed_task_count"] or 0,
            assigned_task_count=row["assigned_task_count"] or 0,
        )