PROJECT-CONTORL/backend/app/api/projects/router.py

import uuid
from typing import List
from fastapi import APIRouter, Depends, HTTPException, status, Request
from sqlalchemy.orm import Session, joinedload, selectinload

from app.core.database import get_db
from app.models import User, Space, Project, TaskStatus, AuditAction, ProjectMember, ProjectTemplate, CustomField
from app.models.task_status import DEFAULT_STATUSES
from app.schemas.project import ProjectCreate, ProjectUpdate, ProjectResponse, ProjectWithDetails
from app.schemas.task_status import TaskStatusResponse
from app.schemas.project_member import (
    ProjectMemberCreate,
    ProjectMemberUpdate,
    ProjectMemberResponse,
    ProjectMemberWithDetails,
    ProjectMemberListResponse,
)
from app.middleware.auth import (
    get_current_user, check_space_access, check_space_edit_access,
    check_project_access, check_project_edit_access
)
from app.middleware.audit import get_audit_metadata
from app.services.audit_service import AuditService

router = APIRouter(tags=["projects"])


def create_default_statuses(db: Session, project_id: str):
    """Create default task statuses for a new project."""
    for status_data in DEFAULT_STATUSES:
        status = TaskStatus(
            id=str(uuid.uuid4()),
            project_id=project_id,
            **status_data
        )
        db.add(status)


def can_view_template(user: User, template: ProjectTemplate) -> bool:
    """Check if a user can view a template."""
    if template.is_public:
        return True
    if template.owner_id == user.id:
        return True
    if user.is_system_admin:
        return True
    return False


@router.get("/api/spaces/{space_id}/projects", response_model=List[ProjectWithDetails])
async def list_projects_in_space(
    space_id: str,
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_user),
):
    """
    List all projects in a space that the user can access.

    Optimized to avoid N+1 queries by using joinedload/selectinload for relationships.
    """
    space = db.query(Space).filter(Space.id == space_id, Space.is_active == True).first()

    if not space:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Space not found",
        )

    if not check_space_access(current_user, space):
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="Access denied",
        )

    # Use joinedload to eagerly load owner, space, and department
    # Use selectinload for tasks (one-to-many) to avoid cartesian product issues
    projects = db.query(Project).options(
        joinedload(Project.owner),
        joinedload(Project.space),
        joinedload(Project.department),
        selectinload(Project.tasks),
    ).filter(Project.space_id == space_id, Project.is_active == True).all()

    # Filter by project access
    accessible_projects = [p for p in projects if check_project_access(current_user, p)]

    result = []
    for project in accessible_projects:
        # Access pre-loaded relationships - no additional queries needed
        task_count = len(project.tasks) if project.tasks else 0
        result.append(ProjectWithDetails(
            id=project.id,
            space_id=project.space_id,
            title=project.title,
            description=project.description,
            owner_id=project.owner_id,
            budget=project.budget,
            start_date=project.start_date,
            end_date=project.end_date,
            security_level=project.security_level,
            status=project.status,
            department_id=project.department_id,
            created_at=project.created_at,
            updated_at=project.updated_at,
            owner_name=project.owner.name if project.owner else None,
            space_name=project.space.name if project.space else None,
            department_name=project.department.name if project.department else None,
            task_count=task_count,
        ))

    return result


@router.post("/api/spaces/{space_id}/projects", response_model=ProjectResponse, status_code=status.HTTP_201_CREATED)
async def create_project(
    space_id: str,
    project_data: ProjectCreate,
    request: Request,
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_user),
):
    """
    Create a new project in a space.
    """
    space = db.query(Space).filter(Space.id == space_id, Space.is_active == True).first()

    if not space:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Space not found",
        )

    if not check_space_access(current_user, space):
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="Access denied",
        )

    template = None
    if project_data.template_id:
        template = db.query(ProjectTemplate).filter(
            ProjectTemplate.id == project_data.template_id,
            ProjectTemplate.is_active == True,
        ).first()
        if not template:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail="Template not found",
            )
        if not can_view_template(current_user, template):
            raise HTTPException(
                status_code=status.HTTP_403_FORBIDDEN,
                detail="Access denied to template",
            )

    security_level = project_data.security_level.value if project_data.security_level else "department"
    if template and template.default_security_level:
        security_level = template.default_security_level

    project = Project(
        id=str(uuid.uuid4()),
        space_id=space_id,
        title=project_data.title,
        description=project_data.description,
        owner_id=current_user.id,
        budget=project_data.budget,
        start_date=project_data.start_date,
        end_date=project_data.end_date,
        security_level=security_level,
        department_id=project_data.department_id or current_user.department_id,
    )

    db.add(project)
    db.flush()  # Get the project ID

    # Create task statuses (from template if provided, otherwise defaults)
    if template and template.task_statuses:
        for status_data in template.task_statuses:
            status = TaskStatus(
                id=str(uuid.uuid4()),
                project_id=project.id,
                name=status_data.get("name", "Unnamed"),
                color=status_data.get("color", "#808080"),
                position=status_data.get("position", 0),
                is_done=status_data.get("is_done", False),
            )
            db.add(status)
    else:
        create_default_statuses(db, project.id)

    # Create custom fields from template if provided
    if template and template.custom_fields:
        for field_data in template.custom_fields:
            custom_field = CustomField(
                id=str(uuid.uuid4()),
                project_id=project.id,
                name=field_data.get("name", "Unnamed"),
                field_type=field_data.get("field_type", "text"),
                options=field_data.get("options"),
                formula=field_data.get("formula"),
                is_required=field_data.get("is_required", False),
                position=field_data.get("position", 0),
            )
            db.add(custom_field)

    # Audit log
    changes = [{"field": "title", "old_value": None, "new_value": project.title}]
    if template:
        changes.append({"field": "template_id", "old_value": None, "new_value": template.id})
    AuditService.log_event(
        db=db,
        event_type="project.create",
        resource_type="project",
        action=AuditAction.CREATE,
        user_id=current_user.id,
        resource_id=project.id,
        changes=changes,
        request_metadata=get_audit_metadata(request),
    )

    db.commit()
    db.refresh(project)

    return project


@router.get("/api/projects/{project_id}", response_model=ProjectWithDetails)
async def get_project(
    project_id: str,
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_user),
):
    """
    Get a project by ID.
    """
    project = db.query(Project).filter(Project.id == project_id, Project.is_active == True).first()

    if not project:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Project not found",
        )

    if not check_project_access(current_user, project):
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="Access denied",
        )

    task_count = len(project.tasks) if project.tasks else 0

    return ProjectWithDetails(
        id=project.id,
        space_id=project.space_id,
        title=project.title,
        description=project.description,
        owner_id=project.owner_id,
        budget=project.budget,
        start_date=project.start_date,
        end_date=project.end_date,
        security_level=project.security_level,
        status=project.status,
        department_id=project.department_id,
        created_at=project.created_at,
        updated_at=project.updated_at,
        owner_name=project.owner.name if project.owner else None,
        space_name=project.space.name if project.space else None,
        department_name=project.department.name if project.department else None,
        task_count=task_count,
    )


@router.patch("/api/projects/{project_id}", response_model=ProjectResponse)
async def update_project(
    project_id: str,
    project_data: ProjectUpdate,
    request: Request,
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_user),
):
    """
    Update a project.
    """
    project = db.query(Project).filter(Project.id == project_id, Project.is_active == True).first()

    if not project:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Project not found",
        )

    if not check_project_edit_access(current_user, project):
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="Only project owner can update",
        )

    # Capture old values for audit
    old_values = {
        "title": project.title,
        "description": project.description,
        "budget": project.budget,
        "start_date": project.start_date,
        "end_date": project.end_date,
        "security_level": project.security_level,
        "status": project.status,
    }

    # Update fields
    update_data = project_data.model_dump(exclude_unset=True)
    for field, value in update_data.items():
        if field == "security_level" and value:
            setattr(project, field, value.value)
        else:
            setattr(project, field, value)

    # Capture new values and log changes
    new_values = {
        "title": project.title,
        "description": project.description,
        "budget": project.budget,
        "start_date": project.start_date,
        "end_date": project.end_date,
        "security_level": project.security_level,
        "status": project.status,
    }

    changes = AuditService.detect_changes(old_values, new_values)
    if changes:
        AuditService.log_event(
            db=db,
            event_type="project.update",
            resource_type="project",
            action=AuditAction.UPDATE,
            user_id=current_user.id,
            resource_id=project.id,
            changes=changes,
            request_metadata=get_audit_metadata(request),
        )

    db.commit()
    db.refresh(project)

    return project


@router.delete("/api/projects/{project_id}", status_code=status.HTTP_204_NO_CONTENT)
async def delete_project(
    project_id: str,
    request: Request,
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_user),
):
    """
    Delete a project (soft delete - sets is_active to False).
    """
    project = db.query(Project).filter(Project.id == project_id, Project.is_active == True).first()

    if not project:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Project not found",
        )

    if not check_project_edit_access(current_user, project):
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="Only project owner can delete",
        )

    # Audit log before soft deletion (this is a high-sensitivity event that triggers alert)
    AuditService.log_event(
        db=db,
        event_type="project.delete",
        resource_type="project",
        action=AuditAction.DELETE,
        user_id=current_user.id,
        resource_id=project.id,
        changes=[{"field": "is_active", "old_value": True, "new_value": False}],
        request_metadata=get_audit_metadata(request),
    )

    # Soft delete - set is_active to False
    project.is_active = False
    db.commit()

    return None


@router.get("/api/projects/{project_id}/statuses", response_model=List[TaskStatusResponse])
async def list_project_statuses(
    project_id: str,
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_user),
):
    """
    List all task statuses for a project.
    """
    project = db.query(Project).filter(Project.id == project_id, Project.is_active == True).first()

    if not project:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Project not found",
        )

    if not check_project_access(current_user, project):
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="Access denied",
        )

    statuses = db.query(TaskStatus).filter(
        TaskStatus.project_id == project_id
    ).order_by(TaskStatus.position).all()

    return statuses


# ============================================================================
# Project Members API - Cross-Department Collaboration
# ============================================================================


@router.get("/api/projects/{project_id}/members", response_model=ProjectMemberListResponse)
async def list_project_members(
    project_id: str,
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_user),
):
    """
    List all members of a project.

    Only users with project access can view the member list.

    Optimized to avoid N+1 queries by using joinedload for user relationships.
    This loads all members and their related users in at most 2 queries instead of
    one query per member.
    """
    project = db.query(Project).filter(Project.id == project_id, Project.is_active == True).first()

    if not project:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Project not found",
        )

    if not check_project_access(current_user, project):
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="Access denied",
        )

    # Use joinedload to eagerly load user and added_by_user relationships
    # This avoids N+1 queries when accessing member.user and member.added_by_user
    members = db.query(ProjectMember).options(
        joinedload(ProjectMember.user).joinedload(User.department),
        joinedload(ProjectMember.added_by_user),
    ).filter(
        ProjectMember.project_id == project_id
    ).all()

    member_list = []
    for member in members:
        # Access pre-loaded relationships - no additional queries needed
        user = member.user
        added_by_user = member.added_by_user

        member_list.append(ProjectMemberWithDetails(
            id=member.id,
            project_id=member.project_id,
            user_id=member.user_id,
            role=member.role,
            added_by=member.added_by,
            created_at=member.created_at,
            user_name=user.name if user else None,
            user_email=user.email if user else None,
            user_department_id=user.department_id if user else None,
            user_department_name=user.department.name if user and user.department else None,
            added_by_name=added_by_user.name if added_by_user else None,
        ))

    return ProjectMemberListResponse(
        members=member_list,
        total=len(member_list),
    )


@router.post("/api/projects/{project_id}/members", response_model=ProjectMemberResponse, status_code=status.HTTP_201_CREATED)
async def add_project_member(
    project_id: str,
    member_data: ProjectMemberCreate,
    request: Request,
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_user),
):
    """
    Add a user as a project member for cross-department collaboration.

    Only project owners and members with 'admin' role can add new members.
    """
    project = db.query(Project).filter(Project.id == project_id, Project.is_active == True).first()

    if not project:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Project not found",
        )

    # Check if user has permission to add members (owner or admin member)
    if not check_project_edit_access(current_user, project):
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="Only project owner or admin members can add new members",
        )

    # Check if user exists
    user_to_add = db.query(User).filter(User.id == member_data.user_id, User.is_active == True).first()
    if not user_to_add:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="User not found",
        )

    # Check if user is already a member
    existing_member = db.query(ProjectMember).filter(
        ProjectMember.project_id == project_id,
        ProjectMember.user_id == member_data.user_id,
    ).first()

    if existing_member:
        raise HTTPException(
            status_code=status.HTTP_409_CONFLICT,
            detail="User is already a member of this project",
        )

    # Don't add the owner as a member (they already have access)
    if member_data.user_id == project.owner_id:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Project owner cannot be added as a member",
        )

    # Create the membership
    member = ProjectMember(
        id=str(uuid.uuid4()),
        project_id=project_id,
        user_id=member_data.user_id,
        role=member_data.role.value,
        added_by=current_user.id,
    )

    db.add(member)

    # Audit log
    AuditService.log_event(
        db=db,
        event_type="project_member.add",
        resource_type="project_member",
        action=AuditAction.CREATE,
        user_id=current_user.id,
        resource_id=member.id,
        changes=[
            {"field": "user_id", "old_value": None, "new_value": member_data.user_id},
            {"field": "role", "old_value": None, "new_value": member_data.role.value},
        ],
        request_metadata=get_audit_metadata(request),
    )

    db.commit()
    db.refresh(member)

    return member


@router.patch("/api/projects/{project_id}/members/{member_id}", response_model=ProjectMemberResponse)
async def update_project_member(
    project_id: str,
    member_id: str,
    member_data: ProjectMemberUpdate,
    request: Request,
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_user),
):
    """
    Update a project member's role.

    Only project owners and members with 'admin' role can update member roles.
    """
    project = db.query(Project).filter(Project.id == project_id, Project.is_active == True).first()

    if not project:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Project not found",
        )

    if not check_project_edit_access(current_user, project):
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="Only project owner or admin members can update member roles",
        )

    member = db.query(ProjectMember).filter(
        ProjectMember.id == member_id,
        ProjectMember.project_id == project_id,
    ).first()

    if not member:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Member not found",
        )

    old_role = member.role
    member.role = member_data.role.value

    # Audit log
    AuditService.log_event(
        db=db,
        event_type="project_member.update",
        resource_type="project_member",
        action=AuditAction.UPDATE,
        user_id=current_user.id,
        resource_id=member.id,
        changes=[{"field": "role", "old_value": old_role, "new_value": member_data.role.value}],
        request_metadata=get_audit_metadata(request),
    )

    db.commit()
    db.refresh(member)

    return member


@router.delete("/api/projects/{project_id}/members/{member_id}", status_code=status.HTTP_204_NO_CONTENT)
async def remove_project_member(
    project_id: str,
    member_id: str,
    request: Request,
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_user),
):
    """
    Remove a member from a project.

    Only project owners and members with 'admin' role can remove members.
    Members can also remove themselves from a project.
    """
    project = db.query(Project).filter(Project.id == project_id, Project.is_active == True).first()

    if not project:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Project not found",
        )

    member = db.query(ProjectMember).filter(
        ProjectMember.id == member_id,
        ProjectMember.project_id == project_id,
    ).first()

    if not member:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Member not found",
        )

    # Allow self-removal or admin access
    is_self_removal = member.user_id == current_user.id
    if not is_self_removal and not check_project_edit_access(current_user, project):
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="Only project owner, admin members, or the member themselves can remove membership",
        )

    # Audit log
    AuditService.log_event(
        db=db,
        event_type="project_member.remove",
        resource_type="project_member",
        action=AuditAction.DELETE,
        user_id=current_user.id,
        resource_id=member.id,
        changes=[
            {"field": "user_id", "old_value": member.user_id, "new_value": None},
            {"field": "role", "old_value": member.role, "new_value": None},
        ],
        request_metadata=get_audit_metadata(request),
    )

    db.delete(member)
    db.commit()

    return None