from fastapi import APIRouter, Depends, HTTPException, UploadFile, File, Form, status from sqlalchemy.ext.asyncio import AsyncSession from sqlalchemy.future import select from sqlalchemy.orm import selectinload from sqlalchemy import update, delete from typing import List, Optional from datetime import datetime, timedelta import uuid import os from database import get_db from models import Ticket, TicketComment, User, SystemSetting, Task, Project, Notification, Role from schemas import TicketCreate, TicketResponse, TicketUpdate, TicketCommentCreate, TicketCommentResponse, TicketAttachmentResponse, TicketMerge from auth import get_current_user, get_current_active_user, require_roles, require_helpdesk_access router = APIRouter(prefix="/api/v1/tickets", tags=["Tickets"], dependencies=[Depends(require_helpdesk_access)]) def get_sla_deadline(priority: str, db: AsyncSession) -> Optional[datetime]: # TODO: Load from SystemSetting in the future hours = 48 if priority == "Crítica": hours = 4 elif priority == "Alta": hours = 12 elif priority == "Media": hours = 48 elif priority == "Baja": hours = 72 return datetime.utcnow() + timedelta(hours=hours) async def eval_automations(ticket: Ticket, db: AsyncSession, event_type: str = "onCreate"): from models import TicketAutomation import json stmt = select(TicketAutomation).where(TicketAutomation.company_id == ticket.company_id).where(TicketAutomation.is_active == True) res = await db.execute(stmt) autos = res.scalars().all() for auto in autos: try: conds = json.loads(auto.conditions_json) actions = json.loads(auto.actions_json) # Simple condition eval match = True for c_key, c_val in conds.items(): if c_key == "event" and c_val != event_type: match = False elif c_key == "priority" and ticket.priority != c_val: match = False elif c_key == "category" and ticket.category != c_val: match = False if match: # Apply actions if "set_priority" in actions: ticket.priority = actions["set_priority"] if "set_assignee_id" in actions: ticket.assignee_id = actions["set_assignee_id"] except: pass async def auto_assign_ticket(db: AsyncSession, company_id: int) -> Optional[int]: # Obtener usuarios administradores stmt = select(User).where(User.company_id == company_id).join(User.roles).where(Role.name == "Administrador").order_by(User.id) res = await db.execute(stmt) admins = res.scalars().all() if not admins: return None # Obtener último asignado stmt_setting = select(SystemSetting).where(SystemSetting.company_id == company_id).where(SystemSetting.key == "last_ticket_assignee_id") res_setting = await db.execute(stmt_setting) setting = res_setting.scalar_one_or_none() last_id = int(setting.value) if setting and setting.value else 0 # Encontrar el índice del último asignado idx = -1 for i, admin in enumerate(admins): if admin.id == last_id: idx = i break next_idx = (idx + 1) % len(admins) next_admin_id = admins[next_idx].id # Actualizar setting if setting: setting.value = str(next_admin_id) else: new_setting = SystemSetting(key="last_ticket_assignee_id", value=str(next_admin_id), company_id=company_id) db.add(new_setting) return next_admin_id async def create_notification(db: AsyncSession, user_id: int, message: str, link: str = None): if user_id: notif = Notification(user_id=user_id, message=message, link=link) db.add(notif) @router.get("", response_model=List[TicketResponse]) async def get_tickets(db: AsyncSession = Depends(get_db), current_user: User = Depends(get_current_active_user)): stmt = select(Ticket).where(Ticket.company_id == current_user.company_id).options( selectinload(Ticket.created_by), selectinload(Ticket.assignee), selectinload(Ticket.team), selectinload(Ticket.comments).selectinload(TicketComment.user), selectinload(Ticket.comments).selectinload(TicketComment.attachments), selectinload(Ticket.attachments) ).order_by(Ticket.created_at.desc()) result = await db.execute(stmt) tickets = result.scalars().all() # Check if user is "Agente", only show their tickets user_roles = [r.name for r in current_user.roles] if "Agente" in user_roles and "Administrador" not in user_roles: tickets = [t for t in tickets if t.created_by_id == current_user.id] return tickets @router.post("", response_model=TicketResponse) async def create_ticket(ticket: TicketCreate, db: AsyncSession = Depends(get_db), current_user: User = Depends(get_current_active_user)): db_ticket = Ticket( title=ticket.title, description=ticket.description, status=ticket.status or "Abierto", priority=ticket.priority or "Media", category=ticket.category, company=ticket.company, tags=ticket.tags, email_reporter=ticket.email_reporter or current_user.email, created_by_id=current_user.id, sla_deadline=get_sla_deadline(ticket.priority or "Media", db) ) # Evaluate automations (e.g. override priority or assignee) await eval_automations(db_ticket, db, "onCreate") # Auto-assign if not assigned by automation if not db_ticket.assignee_id: assignee_id = await auto_assign_ticket(db, db_ticket.company_id) if assignee_id: db_ticket.assignee_id = assignee_id db.add(db_ticket) await db.commit() await db.refresh(db_ticket) if assignee_id: await create_notification(db, assignee_id, f"Se te ha asignado el nuevo ticket: {db_ticket.title}", link=f"/tickets.html?id={db_ticket.id}") await db.commit() # Reload with relations stmt = select(Ticket).where(Ticket.company_id == current_user.company_id).options( selectinload(Ticket.created_by), selectinload(Ticket.assignee), selectinload(Ticket.team), selectinload(Ticket.comments).selectinload(TicketComment.user), selectinload(Ticket.comments).selectinload(TicketComment.attachments), selectinload(Ticket.attachments) ).where(Ticket.id == db_ticket.id) result = await db.execute(stmt) return result.scalar_one() @router.post("/public", response_model=TicketResponse) async def create_public_ticket(ticket: TicketCreate, db: AsyncSession = Depends(get_db)): # Verify if public portal is enabled stmt_settings = select(SystemSetting).where(SystemSetting.company_id == current_user.company_id).where(SystemSetting.key.in_(["helpdesk_enabled", "helpdesk_config"])) res_settings = await db.execute(stmt_settings) settings = res_settings.scalars().all() enabled = False portal_publico = False for s in settings: if s.key == "helpdesk_enabled" and s.value == "true": enabled = True elif s.key == "helpdesk_config": try: import json config = json.loads(s.value) portal_publico = config.get("portal_publico", False) except: pass if not enabled or not portal_publico: raise HTTPException(status_code=403, detail="El portal público de tickets está deshabilitado.") db_ticket = Ticket( title=ticket.title, description=ticket.description, status="Abierto", priority=ticket.priority or "Media", category=ticket.category, email_reporter=ticket.email_reporter, # Obligatorio para públicos created_by_id=None, sla_deadline=get_sla_deadline(ticket.priority or "Media", db) ) # Evaluate automations (e.g. override priority or assignee) await eval_automations(db_ticket, db, "onCreate") # Auto-assign if not assigned by automation if not db_ticket.assignee_id: assignee_id = await auto_assign_ticket(db, db_ticket.company_id) if assignee_id: db_ticket.assignee_id = assignee_id db.add(db_ticket) await db.commit() await db.refresh(db_ticket) if assignee_id: await create_notification(db, assignee_id, f"Se te ha asignado un nuevo ticket público: {db_ticket.title}", link=f"/tickets.html?id={db_ticket.id}") await db.commit() # Reload with relations stmt = select(Ticket).where(Ticket.company_id == current_user.company_id).options( selectinload(Ticket.created_by), selectinload(Ticket.assignee), selectinload(Ticket.team), selectinload(Ticket.comments).selectinload(TicketComment.user), selectinload(Ticket.comments).selectinload(TicketComment.attachments), selectinload(Ticket.attachments) ).where(Ticket.id == db_ticket.id) result = await db.execute(stmt) db_ticket = result.scalar_one() # Send confirmation email if db_ticket.email_reporter: stmt_set = select(SystemSetting).where(SystemSetting.company_id == current_user.company_id).where(SystemSetting.key == "mfa_config") res_set = await db.execute(stmt_set) conf_set = res_set.scalar_one_or_none() if conf_set: try: import json import asyncio from mfa_utils import send_generic_email c = json.loads(conf_set.value) subj = f"[Recibido] Ticket #{db_ticket.id}: {db_ticket.title}" body = f"Hola,\n\nHemos recibido tu ticket #{db_ticket.id}.\nNuestro equipo de soporte lo revisará a la brevedad.\n\nSaludos,\nEquipo de Soporte" asyncio.create_task(send_generic_email( c.get('smtp_host'), c.get('smtp_port'), c.get('smtp_user'), c.get('smtp_password'), db_ticket.email_reporter, subj, body )) except: pass return db_ticket @router.get("/{ticket_id}", response_model=TicketResponse) async def get_ticket(ticket_id: int, db: AsyncSession = Depends(get_db), current_user: User = Depends(get_current_active_user)): stmt = select(Ticket).where(Ticket.company_id == current_user.company_id).options( selectinload(Ticket.created_by), selectinload(Ticket.assignee), selectinload(Ticket.team), selectinload(Ticket.comments).selectinload(TicketComment.user), selectinload(Ticket.comments).selectinload(TicketComment.attachments), selectinload(Ticket.attachments) ).where(Ticket.id == ticket_id) result = await db.execute(stmt) ticket = result.scalar_one_or_none() if not ticket: raise HTTPException(status_code=404, detail="Ticket not found") user_roles = [r.name for r in current_user.roles] if "Cliente" in user_roles and "Administrador" not in user_roles: if ticket.created_by_id != current_user.id: raise HTTPException(status_code=403, detail="Not authorized") return ticket @router.put("/{ticket_id}", response_model=TicketResponse) async def update_ticket(ticket_id: int, ticket_data: TicketUpdate, db: AsyncSession = Depends(get_db), current_user: User = Depends(get_current_active_user)): stmt = select(Ticket).where(Ticket.company_id == current_user.company_id).where(Ticket.id == ticket_id) result = await db.execute(stmt) ticket = result.scalar_one_or_none() if not ticket: raise HTTPException(status_code=404, detail="Ticket not found") import asyncio from mfa_utils import send_generic_email old_status = ticket.status if ticket_data.title is not None: ticket.title = ticket_data.title if ticket_data.description is not None: ticket.description = ticket_data.description if ticket_data.status is not None: ticket.status = ticket_data.status if ticket_data.priority is not None: ticket.priority = ticket_data.priority if ticket_data.category is not None: ticket.category = ticket_data.category if ticket_data.company is not None: ticket.company = ticket_data.company if ticket_data.tags is not None: ticket.tags = ticket_data.tags old_assignee = ticket.assignee_id if ticket_data.assignee_id is not None: ticket.assignee_id = ticket_data.assignee_id if ticket_data.team_id is not None: ticket.team_id = ticket_data.team_id if ticket_data.sla_deadline is not None: # Strip timezone info if present to avoid SQLAlchemy/PostgreSQL naive datetime mixing issues ticket.sla_deadline = ticket_data.sla_deadline.replace(tzinfo=None) if ticket_data.email_reporter is not None: ticket.email_reporter = ticket_data.email_reporter if old_assignee != ticket.assignee_id and ticket.assignee_id is not None and ticket.assignee_id != current_user.id: await create_notification(db, ticket.assignee_id, f"Se te ha asignado el ticket #{ticket.id}: {ticket.title}", link=f"/tickets.html?id={ticket.id}") await db.commit() # Notify by email if status changed if ticket.status != old_status and ticket.email_reporter: # Load email config stmt_set = select(SystemSetting).where(SystemSetting.company_id == current_user.company_id).where(SystemSetting.key == "mfa_config") res_set = await db.execute(stmt_set) conf_set = res_set.scalar_one_or_none() if conf_set: try: import json c = json.loads(conf_set.value) subj = f"[{ticket.status}] Ticket #{ticket.id}: {ticket.title}" body = f"Hola,\n\nTu ticket #{ticket.id} ha cambiado de estado a: {ticket.status}.\n\nSaludos,\nEquipo de Soporte" asyncio.create_task(send_generic_email( c.get('smtp_host'), c.get('smtp_port'), c.get('smtp_user'), c.get('smtp_password'), ticket.email_reporter, subj, body )) except: pass # Reload return await get_ticket(ticket_id, db, current_user) UPLOAD_DIR = os.getenv("UPLOAD_DIR", os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), "uploads")) os.makedirs(UPLOAD_DIR, exist_ok=True) from models import TicketAttachment @router.post("/{ticket_id}/attachments", response_model=TicketAttachmentResponse) async def upload_ticket_attachment( ticket_id: int, file: UploadFile = File(...), comment_id: Optional[int] = Form(None), db: AsyncSession = Depends(get_db), current_user: User = Depends(get_current_active_user) ): stmt = select(Ticket).where(Ticket.company_id == current_user.company_id).where(Ticket.id == ticket_id) res = await db.execute(stmt) if not res.scalar_one_or_none(): raise HTTPException(status_code=404, detail="Ticket not found") ext = file.filename.split('.')[-1] if '.' in file.filename else '' filename = f"{uuid.uuid4().hex}.{ext}" filepath = os.path.join(UPLOAD_DIR, filename) with open(filepath, "wb") as f: content = await file.read() f.write(content) attachment = TicketAttachment( ticket_id=ticket_id, comment_id=comment_id, filename=file.filename, file_path=filepath ) db.add(attachment) await db.commit() await db.refresh(attachment) return attachment @router.post("/{ticket_id}/comments", response_model=TicketCommentResponse) async def add_comment(ticket_id: int, comment_data: TicketCommentCreate, db: AsyncSession = Depends(get_db), current_user: User = Depends(get_current_active_user)): stmt = select(Ticket).where(Ticket.company_id == current_user.company_id).where(Ticket.id == ticket_id) result = await db.execute(stmt) ticket = result.scalar_one_or_none() if not ticket: raise HTTPException(status_code=404, detail="Ticket not found") new_comment = TicketComment( ticket_id=ticket.id, user_id=current_user.id, content=comment_data.content, is_internal=comment_data.is_internal ) db.add(new_comment) if ticket.assignee_id and ticket.assignee_id != current_user.id: await create_notification(db, ticket.assignee_id, f"Nuevo comentario en ticket #{ticket.id}", link=f"/tickets.html?id={ticket.id}") if not comment_data.is_internal and ticket.created_by_id and ticket.created_by_id != current_user.id: await create_notification(db, ticket.created_by_id, f"Respuesta a tu ticket #{ticket.id}", link=f"/tickets.html?id={ticket.id}") await db.commit() await db.refresh(new_comment) stmt_load = select(TicketComment).options( selectinload(TicketComment.user), selectinload(TicketComment.attachments) ).where(TicketComment.id == new_comment.id) res_load = await db.execute(stmt_load) return res_load.scalar_one() # Conversions @router.post("/{ticket_id}/convert/task") async def convert_to_task(ticket_id: int, project_id: int, column_id: Optional[int] = None, db: AsyncSession = Depends(get_db), current_user: User = Depends(get_current_active_user)): ticket = await get_ticket(ticket_id, db, current_user) new_task = Task( title=f"[{ticket.id}] {ticket.title}", description=f"Generado a partir del Ticket #{ticket.id}\n\n{ticket.description}", project_id=project_id, column_id=column_id, priority="high" if ticket.priority in ["Crítica", "Alta"] else "low", assignee_id=ticket.assignee_id ) db.add(new_task) await db.commit() await db.refresh(new_task) # Update ticket stmt_upd = update(Ticket).where(Ticket.id == ticket_id).values( status="Cerrado", linked_task_id=new_task.id ) await db.execute(stmt_upd) await db.commit() return {"message": "Convertido a tarea", "task_id": new_task.id} @router.post("/{ticket_id}/convert/project") async def convert_to_project(ticket_id: int, db: AsyncSession = Depends(get_db), current_user: User = Depends(require_roles(["Administrador", "Project Manager"]))): ticket = await get_ticket(ticket_id, db, current_user) new_proj = Project( name=f"[{ticket.id}] {ticket.title}", description=f"Proyecto generado desde Ticket #{ticket.id}\n\n{ticket.description}", creator_id=current_user.id, assignee_id=ticket.assignee_id ) db.add(new_proj) await db.commit() await db.refresh(new_proj) # Update ticket stmt_upd = update(Ticket).where(Ticket.id == ticket_id).values( status="Cerrado", linked_project_id=new_proj.id ) await db.execute(stmt_upd) await db.commit() return {"message": "Convertido a proyecto", "project_id": new_proj.id} # Merge @router.post("/{ticket_id}/merge") async def merge_tickets(ticket_id: int, merge_req: TicketMerge, db: AsyncSession = Depends(get_db), current_user: User = Depends(require_roles(["Administrador", "Project Manager"]))): if ticket_id == merge_req.target_ticket_id: raise HTTPException(status_code=400, detail="Cannot merge ticket into itself") source = await get_ticket(ticket_id, db, current_user) target = await get_ticket(merge_req.target_ticket_id, db, current_user) if not source or not target: raise HTTPException(status_code=404, detail="One or both tickets not found") # Move comments stmt_comments = update(TicketComment).where(TicketComment.ticket_id == ticket_id).values(ticket_id=merge_req.target_ticket_id) await db.execute(stmt_comments) # Close source ticket stmt_upd = update(Ticket).where(Ticket.id == ticket_id).values(status="Cerrado") await db.execute(stmt_upd) # Add system comment to target sys_comment = TicketComment( ticket_id=merge_req.target_ticket_id, user_id=current_user.id, content=f"Los comentarios del Ticket #{ticket_id} han sido fusionados a este ticket.", is_internal=True ) db.add(sys_comment) await db.commit() return {"message": "Tickets fusionados exitosamente"} # Analytics @router.get("/reports/analytics") async def get_analytics(db: AsyncSession = Depends(get_db), current_user: User = Depends(require_roles(["Administrador", "Project Manager"]))): stmt = select(Ticket).where(Ticket.company_id == current_user.company_id) res = await db.execute(stmt) tickets = res.scalars().all() total = len(tickets) abiertos = sum(1 for t in tickets if t.status == "Abierto") resueltos = sum(1 for t in tickets if t.status in ["Resuelto", "Cerrado"]) return { "total": total, "abiertos": abiertos, "resueltos": resueltos, "tickets_por_prioridad": { "Critica": sum(1 for t in tickets if t.priority == "Crítica"), "Alta": sum(1 for t in tickets if t.priority == "Alta"), "Media": sum(1 for t in tickets if t.priority == "Media"), "Baja": sum(1 for t in tickets if t.priority == "Baja") } } # CSAT from schemas import TicketRatingCreate from models import TicketRating @router.post("/{ticket_id}/csat") async def submit_csat(ticket_id: int, rating: TicketRatingCreate, db: AsyncSession = Depends(get_db)): # Check if CSAT is enabled stmt_settings = select(SystemSetting).where(SystemSetting.company_id == current_user.company_id).where(SystemSetting.key == "helpdesk_config") res_settings = await db.execute(stmt_settings) s = res_settings.scalar_one_or_none() if s: import json config = json.loads(s.value) if not config.get("csat_enabled", False): raise HTTPException(status_code=403, detail="CSAT is disabled") stmt = select(Ticket).where(Ticket.company_id == current_user.company_id).where(Ticket.id == ticket_id) res = await db.execute(stmt) ticket = res.scalar_one_or_none() if not ticket: raise HTTPException(status_code=404, detail="Ticket not found") db_rating = TicketRating( ticket_id=ticket_id, rating=rating.rating, feedback=rating.feedback ) db.add(db_rating) await db.commit() return {"message": "Rating submitted"} @router.post("/ai/draft_response") async def ai_draft_response( payload: dict, db: AsyncSession = Depends(get_db), current_user: User = Depends(get_current_active_user) ): try: # Check if AI is enabled stmt = select(SystemSetting).where( SystemSetting.company_id == current_user.company_id ).where(SystemSetting.key == "ai_copilot_enabled") res = await db.execute(stmt) setting = res.scalar_one_or_none() if not setting or setting.value != "true": raise HTTPException(status_code=400, detail="El asistente de IA no está habilitado.") ticket_id = payload.get("ticket_id") if not ticket_id: raise HTTPException(status_code=400, detail="El ID del ticket es requerido.") # Fetch ticket details t_stmt = select(Ticket).where(Ticket.id == ticket_id) t_res = await db.execute(t_stmt) ticket = t_res.scalar_one_or_none() if not ticket: raise HTTPException(status_code=404, detail="Ticket no encontrado.") # Get Gemini key key_stmt = select(SystemSetting).where( SystemSetting.company_id == current_user.company_id ).where(SystemSetting.key == "gemini_api_key") key_res = await db.execute(key_stmt) key_setting = key_res.scalar_one_or_none() api_key = key_setting.value.strip() if key_setting and key_setting.value else None # Try calling real AI if key is present if api_key: try: prompt = ( f"Eres un agente de soporte técnico. Genera una respuesta formal, amable y resolutiva en español para el siguiente ticket de soporte:\n" f"Título: {ticket.title}\n" f"Descripción: {ticket.description}\n" f"Estado Actual: {ticket.status}\n" f"Prioridad: {ticket.priority}\n\n" f"La respuesta debe saludar al cliente, indicar que estamos trabajando en ello o darle una solución estimada según el caso, y despedirse formalmente. El JSON de salida debe ser estrictamente del formato: {{\"draft\": \"contenido de la respuesta en texto plano (puedes usar saltos de línea \\\\n)\"}}" ) import urllib.request import json import asyncio def run_api(): url = f"https://generativelanguage.googleapis.com/v1beta/models/gemini-1.5-flash:generateContent?key={api_key}" p_data = { "contents": [{"parts": [{"text": prompt}]}], "generationConfig": {"responseMimeType": "application/json"} } req = urllib.request.Request( url, data=json.dumps(p_data).encode("utf-8"), headers={"Content-Type": "application/json"}, method="POST" ) with urllib.request.urlopen(req, timeout=10) as response: return json.loads(response.read().decode("utf-8")) res_json = await asyncio.to_thread(run_api) raw_text = res_json["candidates"][0]["content"]["parts"][0]["text"] parsed_res = json.loads(raw_text) if "draft" in parsed_res: return parsed_res except Exception as e: print("Gemini API call failed, falling back to local template:", e) # Local fallback draft = ( f"Estimado cliente,\n\n" f"Agradecemos su contacto con nuestro equipo de soporte. Hemos registrado su solicitud bajo el ticket #{ticket.id}: \"{ticket.title}\" con prioridad {ticket.priority}.\n\n" f"Nuestros agentes ya están revisando el caso para darle una solución a la brevedad. Le mantendremos informado por esta vía.\n\n" f"Atentamente,\n" f"El equipo de soporte técnico." ) return {"draft": draft} except Exception as e: raise HTTPException(status_code=500, detail=str(e))