"""
Project API router.
Provides CRUD endpoints for project management.
"""
import uuid
from typing import List
from fastapi import APIRouter, HTTPException, status
from database import get_db_connection
from schemas.project import ProjectCreate, ProjectUpdate, ProjectResponse
from models import Project

router = APIRouter(
    prefix="/api/projects",
    tags=["projects"]
)


@router.get("", response_model=List[ProjectResponse])
async def list_projects():
    """
    List all projects.
    Returns a list of all projects with their task counts.
    """
    with get_db_connection() as conn:
        cursor = conn.cursor()
        
        # Get all projects with task counts
        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
            GROUP BY p.id
            ORDER BY p.created_at DESC
        """)
        
        rows = cursor.fetchall()
        
        projects = []
        for row in rows:
            projects.append(ProjectResponse(
                id=row["id"],
                name=row["name"],
                description=row["description"] or "",
                config=row["config"],
                created_at=row["created_at"],
                task_count=row["task_count"]
            ))
        
        return projects


@router.post("", response_model=ProjectResponse, status_code=status.HTTP_201_CREATED)
async def create_project(project: ProjectCreate):
    """
    Create a new project.
    
    Args:
        project: Project creation data
    
    Returns:
        Created project with generated ID
    """
    # Generate unique ID
    project_id = f"proj_{uuid.uuid4().hex[:12]}"
    
    with get_db_connection() as conn:
        cursor = conn.cursor()
        
        # Insert new project
        cursor.execute("""
            INSERT INTO projects (id, name, description, config)
            VALUES (?, ?, ?, ?)
        """, (
            project_id,
            project.name,
            project.description,
            project.config
        ))
        
        # 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(project_id: str):
    """
    Get project by ID.
    
    Args:
        project_id: Project unique identifier
    
    Returns:
        Project details with task count
    
    Raises:
        HTTPException: 404 if project not found
    """
    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(project_id: str, project: ProjectUpdate):
    """
    Update an existing project.
    
    Args:
        project_id: Project unique identifier
        project: Project update data
    
    Returns:
        Updated project details
    
    Raises:
        HTTPException: 404 if project not found
    """
    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(project_id: str):
    """
    Delete a project and all associated tasks.
    
    Args:
        project_id: Project unique identifier
    
    Raises:
        HTTPException: 404 if project not found
    """
    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