tickethub/backend/app/routers/tickets.py

from fastapi import APIRouter, HTTPException, Query
from typing import List, Optional
from app.models import Ticket, TicketCreate, TicketUpdate, Comment, CommentCreate, TicketStatus
from app.services.database import get_db
from app.services.webhook import trigger_webhook
import json
from datetime import datetime

router = APIRouter()

@router.get("", response_model=List[Ticket])
async def list_tickets(
    project_id: Optional[int] = None,
    status: Optional[TicketStatus] = None,
    limit: int = Query(default=50, le=100)
):
    db = await get_db()
    query = "SELECT * FROM tickets WHERE 1=1"
    params = []
    
    if project_id:
        query += " AND project_id = ?"
        params.append(project_id)
    if status:
        query += " AND status = ?"
        params.append(status.value)
    
    query += " ORDER BY created_at DESC LIMIT ?"
    params.append(limit)
    
    cursor = await db.execute(query, params)
    rows = await cursor.fetchall()
    await db.close()
    
    tickets = []
    for row in rows:
        ticket = dict(row)
        ticket["labels"] = json.loads(ticket.get("labels", "[]"))
        tickets.append(ticket)
    return tickets

@router.post("", response_model=Ticket)
async def create_ticket(ticket: TicketCreate):
    db = await get_db()
    
    # Get project and increment sequence
    cursor = await db.execute("SELECT * FROM projects WHERE id = ?", (ticket.project_id,))
    project = await cursor.fetchone()
    if not project:
        await db.close()
        raise HTTPException(status_code=404, detail="Project not found")
    
    project = dict(project)
    new_seq = project["ticket_sequence"] + 1
    ticket_key = f"{project['key']}-{new_seq}"
    
    await db.execute("UPDATE projects SET ticket_sequence = ? WHERE id = ?", (new_seq, ticket.project_id))
    
    now = datetime.utcnow().isoformat()
    cursor = await db.execute(
        """INSERT INTO tickets (project_id, key, title, description, priority, labels, created_at, updated_at) 
           VALUES (?, ?, ?, ?, ?, ?, ?, ?)""",
        (ticket.project_id, ticket_key, ticket.title, ticket.description, 
         ticket.priority.value, json.dumps(ticket.labels), now, now)
    )
    await db.commit()
    
    ticket_id = cursor.lastrowid
    cursor = await db.execute("SELECT * FROM tickets WHERE id = ?", (ticket_id,))
    row = await cursor.fetchone()
    await db.close()
    
    result = dict(row)
    result["labels"] = json.loads(result.get("labels", "[]"))
    
    # Trigger webhook
    await trigger_webhook(ticket.project_id, "ticket.created", result)
    
    return result

@router.get("/{ticket_id}", response_model=Ticket)
async def get_ticket(ticket_id: int):
    db = await get_db()
    cursor = await db.execute("SELECT * FROM tickets WHERE id = ?", (ticket_id,))
    row = await cursor.fetchone()
    await db.close()
    if not row:
        raise HTTPException(status_code=404, detail="Ticket not found")
    result = dict(row)
    result["labels"] = json.loads(result.get("labels", "[]"))
    return result

@router.get("/key/{ticket_key}", response_model=Ticket)
async def get_ticket_by_key(ticket_key: str):
    db = await get_db()
    cursor = await db.execute("SELECT * FROM tickets WHERE key = ?", (ticket_key.upper(),))
    row = await cursor.fetchone()
    await db.close()
    if not row:
        raise HTTPException(status_code=404, detail="Ticket not found")
    result = dict(row)
    result["labels"] = json.loads(result.get("labels", "[]"))
    return result

@router.patch("/{ticket_id}", response_model=Ticket)
async def update_ticket(ticket_id: int, update: TicketUpdate):
    db = await get_db()
    
    cursor = await db.execute("SELECT * FROM tickets WHERE id = ?", (ticket_id,))
    existing = await cursor.fetchone()
    if not existing:
        await db.close()
        raise HTTPException(status_code=404, detail="Ticket not found")
    
    existing = dict(existing)
    updates = []
    params = []
    
    if update.title is not None:
        updates.append("title = ?")
        params.append(update.title)
    if update.description is not None:
        updates.append("description = ?")
        params.append(update.description)
    if update.status is not None:
        updates.append("status = ?")
        params.append(update.status.value)
    if update.priority is not None:
        updates.append("priority = ?")
        params.append(update.priority.value)
    if update.labels is not None:
        updates.append("labels = ?")
        params.append(json.dumps(update.labels))
    
    if updates:
        updates.append("updated_at = ?")
        params.append(datetime.utcnow().isoformat())
        params.append(ticket_id)
        
        await db.execute(f"UPDATE tickets SET {', '.join(updates)} WHERE id = ?", params)
        await db.commit()
    
    cursor = await db.execute("SELECT * FROM tickets WHERE id = ?", (ticket_id,))
    row = await cursor.fetchone()
    await db.close()
    
    result = dict(row)
    result["labels"] = json.loads(result.get("labels", "[]"))
    
    # Trigger webhook
    await trigger_webhook(existing["project_id"], "ticket.updated", result)
    
    return result

@router.delete("/{ticket_id}")
async def delete_ticket(ticket_id: int):
    db = await get_db()
    await db.execute("DELETE FROM comments WHERE ticket_id = ?", (ticket_id,))
    await db.execute("DELETE FROM tickets WHERE id = ?", (ticket_id,))
    await db.commit()
    await db.close()
    return {"status": "deleted"}

# Comments
@router.get("/{ticket_id}/comments", response_model=List[Comment])
async def list_comments(ticket_id: int):
    db = await get_db()
    cursor = await db.execute(
        "SELECT * FROM comments WHERE ticket_id = ? ORDER BY created_at ASC",
        (ticket_id,)
    )
    rows = await cursor.fetchall()
    await db.close()
    return [dict(row) for row in rows]

@router.post("/{ticket_id}/comments", response_model=Comment)
async def add_comment(ticket_id: int, comment: CommentCreate):
    db = await get_db()
    
    cursor = await db.execute("SELECT project_id FROM tickets WHERE id = ?", (ticket_id,))
    ticket = await cursor.fetchone()
    if not ticket:
        await db.close()
        raise HTTPException(status_code=404, detail="Ticket not found")
    
    cursor = await db.execute(
        "INSERT INTO comments (ticket_id, author, content) VALUES (?, ?, ?)",
        (ticket_id, comment.author, comment.content)
    )
    await db.commit()
    
    comment_id = cursor.lastrowid
    cursor = await db.execute("SELECT * FROM comments WHERE id = ?", (comment_id,))
    row = await cursor.fetchone()
    await db.close()
    
    result = dict(row)
    
    # Trigger webhook
    await trigger_webhook(ticket["project_id"], "comment.added", {
        "ticket_id": ticket_id,
        "comment": result
    })
    
    return result