tickets.js

Go to the documentation of this file.

/**
 * @file tickets.js
 * @brief Ticket Management API Routes
 * @description Comprehensive ticketing system supporting ITIL-style ticket management
 * for MSP/PSA operations. Includes ticket lifecycle management, SLA tracking, time
 * tracking, note management, and multi-tenant isolation.
 * 
 * **Core Features:**
 * - Full CRUD operations for tickets
 * - Status workflow: open → in-progress → closed
 * - Priority levels: low, medium, high, critical
 * - SLA breach tracking (default 24h, configurable per customer)
 * - Multi-tenant isolation (customer-level and MSP-level)
 * - Assignment to technicians/users
 * - Time entry tracking (billable/non-billable)
 * - Internal and customer-facing notes
 * - Advanced filtering and search
 * - Notification system integration
 * 
 * **Database Schema:**
 * - tickets: Main ticket records
 * - ticket_notes: Comments and updates
 * - ticket_time_entries: Time tracking for billing
 * - Foreign keys: customers, users, tenants
 * @module routes/tickets
 * @requires express
 * @requires services/db
 * @requires middleware/auth
 * @requires middleware/adminOnly
 * @requires middleware/tenant
 * @requires utils/notificationHelper
 * @author RMM-PSA Platform
 * @date 2026-02-10
 * @since 2.0.0
 */
const express = require('express');
const router = express.Router();
const pool = require('../services/db');
const authenticateToken = require('../middleware/auth');
const requireAdmin = require('../middleware/adminOnly');
const { getTenantFilter, setTenantContext } = require('../middleware/tenant');
const { notifyTicketCreated } = require('../utils/notificationHelper');
 
// Apply authentication and tenant context to all routes
router.use(authenticateToken, setTenantContext);
 
/**
 * @api {get} /tickets/count Get open ticket count
 * @apiName GetTicketCount
 * @apiGroup Tickets
 * @apiDescription Returns the count of tickets with status='open' for the current tenant.
 * Useful for dashboard widgets and notification badges. Fast query optimized with
 * indexed fields.
 * 
 * **Performance:**
 * - Uses COUNT(*) with status index
 * - Returns integer count only
 * - Multi-tenant filtered automatically
 * @apiHeader {string} Authorization Bearer JWT token with tenant context
 * @apiSuccess {number} activeTickets Count of open tickets
 * @apiExample {curl} Example Request:
 *   curl -H "Authorization: Bearer eyJhbGc..." \
 *        "https://api.everydaytech.au/tickets/count"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "activeTickets": 17
 *   }
 * @since 2.0.0
 * @see {@link module:routes/tickets.listTickets} for full ticket list
 */
router.get('/count', async (req, res) => {
  try {
    const { clause, params } = getTenantFilter(req);
    const whereClause = clause ? `WHERE ${clause} AND status = $${params.length + 1}` : 'WHERE status = $1';
    const queryParams = clause ? [...params, 'open'] : ['open'];
    
    const result = await pool.query(
      `SELECT COUNT(*)::int AS open_count FROM tickets ${whereClause}`,
      queryParams
    );
    res.json({ activeTickets: result.rows[0].open_count });
  } catch (err) {
    console.error('Error fetching ticket count:', err);
    res.status(500).json({ error: 'Server error', details: err.message });
  }
});
 
/**
 * @api {get} /tickets/:id Get ticket details
 * @apiName GetTicket
 * @apiGroup Tickets
 * @apiDescription Retrieves complete ticket information including all notes and time
 * entries. Includes SLA calculations, assignment details, customer information, and
 * related data. Returns 404 if ticket not found or not accessible by current tenant.
 * 
 * **Included Data:**
 * - Full ticket details (title, description, status, priority)
 * - SLA information (breach time, remaining seconds, status)
 * - Assigned user and customer names
 * - All ticket notes with author information
 * - Complete time entry history
 * 
 * **SLA Calculations:**
 * - sla_hours: 24 (default, can be overridden per customer)
 * - sla_breach_at: Timestamp when ticket breaches SLA
 * - sla_remaining_seconds: Seconds until breach (null if closed)
 * - sla_status: "ok" | "due_soon" | "overdue" | "closed"
 * @apiHeader {string} Authorization Bearer JWT token with tenant context
 * @apiParam {number} id Ticket ID (URL parameter)
 * @apiSuccess {object} ticket Complete ticket object with SLA info
 * @apiSuccess {Array} notes All ticket notes ordered by created_at DESC
 * @apiSuccess {Array} timeEntries All time entries ordered by start_time DESC
 * @apiError {number} 404 Ticket not found or access denied
 * @apiError {number} 500 Server error
 * @apiExample {curl} Example Request:
 *   curl -H "Authorization: Bearer eyJhbGc..." \
 *        "https://api.everydaytech.au/tickets/123"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "ticket": {
 *       "ticket_id": 123,
 *       "title": "Email server down",
 *       "description": "Exchange server not responding",
 *       "status": "open",
 *       "priority": "high",
 *       "assigned_to": 5,
 *       "assigned_to_name": "John Technician",
 *       "customer_id": 42,
 *       "customer_name": "Acme Corp",
 *       "sla_hours": 24,
 *       "sla_breach_at": "2026-02-11T10:30:00Z",
 *       "sla_remaining_seconds": 43200,
 *       "sla_status": "ok"
 *     },
 *     "notes": [
 *       {
 *         "note_id": 1,
 *         "content": "Checking server logs...",
 *         "author_name": "John Technician",
 *         "is_private": false,
 *         "created_at": "2026-02-10T11:00:00Z"
 *       }
 *     ],
 *     "timeEntries": [
 *       {
 *         "entry_id": 1,
 *         "user_name": "John Technician",
 *         "duration_minutes": 45,
 *         "billable": true,
 *         "start_time": "2026-02-10T10:30:00Z"
 *       }
 *     ]
 *   }
 * @since 2.0.0
 * @see {@link module:routes/tickets.updateTicket} for updating ticket
 * @see {@link module:routes/tickets.addNote} for adding notes
 */
router.get('/:id', async (req, res) => {
  const { id } = req.params;
  try {
    const { clause, params, nextParamIndex } = getTenantFilter(req, 't');
    const whereClauses = [`t.ticket_id = $${nextParamIndex}`];
    if (clause) whereClauses.push(clause);
    const queryParams = clause ? [...params, id] : [id];
    
    const ticketRes = await pool.query(`
      SELECT 
        t.*, u.name as assigned_to_name, c.name as customer_name,
        24 AS sla_hours,
        (t.created_at + (24 * INTERVAL '1 hour')) AS sla_breach_at,
        CASE 
          WHEN t.status = 'closed' THEN NULL
          ELSE EXTRACT(EPOCH FROM ((t.created_at + (24 * INTERVAL '1 hour')) - NOW()))::bigint
        END AS sla_remaining_seconds,
        CASE 
          WHEN t.status = 'closed' THEN 'closed'
          WHEN (t.created_at + (24 * INTERVAL '1 hour')) < NOW() THEN 'overdue'
          WHEN (t.created_at + (24 * INTERVAL '1 hour')) < NOW() + INTERVAL '2 hours' THEN 'due_soon'
          ELSE 'ok'
        END AS sla_status
      FROM tickets t 
      LEFT JOIN users u ON t.assigned_to = u.user_id
      LEFT JOIN customers c ON t.customer_id = c.customer_id
      WHERE ${whereClauses.join(' AND ')}
    `, queryParams);
 
    if (ticketRes.rows.length === 0) {
      return res.status(404).send('Ticket not found');
    }
 
    const notesRes = await pool.query(`
      SELECT n.*, u.name as author_name
      FROM ticket_notes n
      LEFT JOIN users u ON n.user_id = u.user_id
      WHERE n.ticket_id = $1
      ORDER BY n.created_at DESC
    `, [id]);
 
    const timeRes = await pool.query(`
      SELECT t.*, u.name as user_name
      FROM ticket_time_entries t
      LEFT JOIN users u ON t.user_id = u.user_id
      WHERE t.ticket_id = $1
      ORDER BY t.start_time DESC
    `, [id]);
 
    res.json({
      ticket: ticketRes.rows[0],
      notes: notesRes.rows,
      timeEntries: timeRes.rows
    });
  } catch (err) {
    console.error('Error fetching ticket:', err);
    res.status(500).json({ error: 'Server error', details: err.message });
  }
});
 
/**
 * @api {get} /tickets List all tickets
 * @apiName ListTickets
 * @apiGroup Tickets
 * @apiDescription Retrieves paginated list of tickets with advanced filtering capabilities.
 * Supports search, status filtering, priority filtering, SLA filtering, and date range
 * queries. Includes computed SLA fields for dashboard views. Results are tenant-isolated
 * and ordered by ticket_id DESC (newest first).
 * 
 * **Query Parameters:**
 * - page (default: 1): Page number for pagination
 * - limit (default: 10): Results per page
 * - search: Search in title, description, or customer name
 * - status: Filter by status (open, in-progress, closed, all)
 * - priority: Filter by priority (low, medium, high, critical, all)
 * - sla_filter: Filter by SLA status (overdue, due_today, due_this_week, all)
 * - date_from: Filter tickets created after this date
 * - date_to: Filter tickets created before this date
 * 
 * **SLA Filtering:**
 * - overdue: SLA breach time passed and status != closed
 * - due_today: SLA breach date is today
 * - due_this_week: SLA breach is within current week
 * @apiHeader {string} Authorization Bearer JWT token with tenant context
 * @apiParam {number} [page=1] Page number
 * @apiParam {number} [limit=10] Results per page
 * @apiParam {string} [search] Search query
 * @apiParam {string} [status] Status filter
 * @apiParam {string} [priority] Priority filter
 * @apiParam {string} [sla_filter] SLA status filter
 * @apiParam {string} [date_from] Start date (ISO 8601)
 * @apiParam {string} [date_to] End date (ISO 8601)
 * @apiSuccess {Array} tickets Array of ticket objects with SLA info
 * @apiSuccess {number} total Total count of matching tickets
 * @apiExample {curl} List Open High Priority Tickets:
 *   curl -H "Authorization: Bearer eyJhbGc..." \
 *        "https://api.everydaytech.au/tickets?status=open&priority=high&page=1&limit=20"
 * @apiExample {curl} Search Overdue Tickets:
 *   curl -H "Authorization: Bearer eyJhbGc..." \
 *        "https://api.everydaytech.au/tickets?search=email&sla_filter=overdue"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "tickets": [
 *       {
 *         "ticket_id": 125,
 *         "title": "Printer offline",
 *         "status": "open",
 *         "priority": "medium",
 *         "customer_name": "Acme Corp",
 *         "assigned_to": "Jane Tech",
 *         "sla_status": "ok",
 *         "sla_remaining_seconds": 72000,
 *         "created_at": "2026-02-10T08:00:00Z"
 *       }
 *     ],
 *     "total": 48
 *   }
 * @since 2.0.0
 * @see {@link module:routes/tickets.getTicket} for single ticket details
 */
router.get('/', async (req, res) => {
  const page = parseInt(req.query.page) || 1;
  const limit = parseInt(req.query.limit) || 10;
  const offset = (page - 1) * limit;
  const search = req.query.search;
  const status = req.query.status;
  const priority = req.query.priority;
  const slaFilter = req.query.sla_filter;
  const dateFrom = req.query.date_from;
  const dateTo = req.query.date_to;
 
  try {
    const { clause: tenantClause, params: tenantParams, nextParamIndex } = getTenantFilter(req, 't');
    const conditions = [];
    const params = [...tenantParams];
    
    if (tenantClause) conditions.push(tenantClause);
    let paramIdx = nextParamIndex;
 
    if (search) {
      params.push(`%${search}%`);
      conditions.push(`(t.title ILIKE $${paramIdx} OR t.description ILIKE $${paramIdx} OR c.name ILIKE $${paramIdx})`);
      paramIdx++;
    }
 
    if (status && status !== 'all') {
      params.push(status);
      conditions.push(`t.status = $${paramIdx}`);
      paramIdx++;
    }
 
    if (priority && priority !== 'all') {
      params.push(priority);
      conditions.push(`t.priority = $${paramIdx}`);
      paramIdx++;
    }
 
    if (dateFrom) {
      params.push(dateFrom);
      conditions.push(`t.created_at >= $${paramIdx}`);
      paramIdx++;
    }
    if (dateTo) {
      params.push(dateTo);
      conditions.push(`t.created_at <= $${paramIdx}`);
      paramIdx++;
    }
 
    // SLA filter using computed SLA breach datetime (default 24h). Once contracts.sla_hours exists, this can be tied per customer.
    if (slaFilter && slaFilter !== 'all') {
      // Reuse the same expression we compute in SELECT for SLA breach time
      const breachExpr = `(t.created_at + (24 * INTERVAL '1 hour'))`;
      if (slaFilter === 'overdue') {
        conditions.push(`${breachExpr} < NOW() AND t.status != 'closed'`);
      } else if (slaFilter === 'due_today') {
        conditions.push(`DATE(${breachExpr}) = CURRENT_DATE`);
      } else if (slaFilter === 'due_this_week') {
        conditions.push(`${breachExpr} >= date_trunc('week', NOW()) AND ${breachExpr} < date_trunc('week', NOW()) + INTERVAL '7 days'`);
      }
    }
 
    const whereClause = conditions.length > 0 ? ' WHERE ' + conditions.join(' AND ') : '';
    
    const query = `
      SELECT 
        t.ticket_id, t.title, t.status, t.priority, t.created_at, t.updated_at,
        t.customer_id, t.tenant_id, tn.name AS tenant_name,
        u.name AS assigned_to, c.name AS customer_name,
        24 AS sla_hours,
        (t.created_at + (24 * INTERVAL '1 hour')) AS sla_breach_at,
        CASE 
          WHEN t.status = 'closed' THEN NULL
          ELSE EXTRACT(EPOCH FROM ((t.created_at + (24 * INTERVAL '1 hour')) - NOW()))::bigint
        END AS sla_remaining_seconds,
        CASE 
          WHEN t.status = 'closed' THEN 'closed'
          WHEN (t.created_at + (24 * INTERVAL '1 hour')) < NOW() THEN 'overdue'
          WHEN (t.created_at + (24 * INTERVAL '1 hour')) < NOW() + INTERVAL '2 hours' THEN 'due_soon'
          ELSE 'ok'
        END AS sla_status
      FROM tickets t
      LEFT JOIN users u ON t.assigned_to = u.user_id
      LEFT JOIN customers c ON t.customer_id = c.customer_id
      LEFT JOIN tenants tn ON t.tenant_id = tn.tenant_id
      ${whereClause}
      ORDER BY t.ticket_id DESC LIMIT $${paramIdx} OFFSET $${paramIdx + 1}
    `;
    
    const countQuery = `
      SELECT COUNT(*) FROM tickets t 
      LEFT JOIN customers c ON t.customer_id = c.customer_id
      ${whereClause}
    `;
    
    const queryParams = [...params, limit, offset];
    
    const [ticketsResult, countResult] = await Promise.all([
      pool.query(query, queryParams),
      pool.query(countQuery, params)
    ]);
 
    res.json({
      tickets: ticketsResult.rows,
      total: parseInt(countResult.rows[0].count)
    });
  } catch (err) {
    console.error('Error fetching tickets:', err);
    res.status(500).send('Server error');
  }
});
 
/**
 * @api {post} /tickets Create new ticket
 * @apiName CreateTicket
 * @apiGroup Tickets
 * @apiDescription Creates a new support ticket in the system. Automatically sets tenant
 * context, generates creation timestamp, and triggers notification to assigned user.
 * Returns the complete newly created ticket object.
 * 
 * **Default Values:**
 * - status: 'open' (if not provided)
 * - priority: 'medium' (if not provided)
 * - tenant_id: Extracted from JWT token
 * - created_at: Current timestamp
 * 
 * **Notifications:**
 * Automatically sends notification to assigned user (if specified) via the
 * notification helper system.
 * @apiHeader {string} Authorization Bearer JWT token with tenant context
 * @apiHeader {string} Content-Type application/json
 * @apiParam {string} title Ticket title/subject (required)
 * @apiParam {string} description Full ticket description (required)
 * @apiParam {string} [status=open] Ticket status (open, in-progress, closed)
 * @apiParam {string} [priority=medium] Priority level (low, medium, high, critical)
 * @apiParam {number} [assigned_to] User ID to assign ticket to
 * @apiParam {number} customer_id Customer ID associated with ticket (required)
 * @apiSuccess {Object} ticket Complete newly created ticket object
 * @apiSuccess {number} ticket.ticket_id Auto-generated ticket ID
 * @apiError {number} 400 Missing required fields
 * @apiError {number} 500 Failed to create ticket
 * @apiExample {curl} Example Request:
 *   curl -X POST \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        -H "Content-Type: application/json" \
 *        -d '{
 *          "title": "Network connectivity issues",
 *          "description": "Users unable to access file shares",
 *          "priority": "high",
 *          "customer_id": 42,
 *          "assigned_to": 5
 *        }' \
 *        "https://api.everydaytech.au/tickets"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 201 Created
 *   {
 *     "ticket_id": 126,
 *     "title": "Network connectivity issues",
 *     "description": "Users unable to access file shares",
 *     "status": "open",
 *     "priority": "high",
 *     "assigned_to": 5,
 *     "customer_id": 42,
 *     "tenant_id": 1,
 *     "created_at": "2026-02-10T14:30:00Z",
 *     "updated_at": "2026-02-10T14:30:00Z"
 *   }
 * @since 2.0.0
 * @see {@link module:routes/tickets.updateTicket} for updating tickets
 * @see {@link module:utils/notificationHelper.notifyTicketCreated} for notification logic
 */
router.post('/', async (req, res) => {
  const { title, description, status, priority, assigned_to, customer_id } = req.body;
 
  try {
    const tenant_id = req.tenant && req.tenant.id ? req.tenant.id : null;
    
    const result = await pool.query(
      `INSERT INTO tickets (title, description, status, priority, assigned_to, customer_id, tenant_id)
       VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING *`,
      [title, description, status || 'open', priority || 'medium', assigned_to, customer_id, tenant_id]
    );
 
    const ticket = result.rows[0];
 
    // Create notification for ticket creation
    try {
      await notifyTicketCreated(ticket);
    } catch (notifError) {
      console.error('Error creating ticket notification:', notifError);
      // Don't fail ticket creation if notification fails
    }
 
    res.status(201).json(ticket);
  } catch (err) {
    console.error('Error creating ticket:', err);
    res.status(500).send('Failed to create ticket');
  }
});
 
/**
 * @api {put} /tickets/:id Update ticket
 * @apiName UpdateTicket
 * @apiGroup Tickets
 * @apiDescription Updates an existing ticket's details. Sets updated_at timestamp
 * automatically. All fields are required in request body - provide existing values
 * for fields that should not change. Tenant isolation enforced automatically.
 * 
 * **Updatable Fields:**
 * - title: Ticket subject
 * - description: Full description
 * - status: Workflow status (open, in-progress, closed)
 * - priority: Priority level
 * - assigned_to: Assigned user ID
 * - customer_id: Associated customer
 * 
 * **Status Workflow:**
 * Typical flow: open → in-progress → closed
 * Can be changed to any status at any time (no enforced workflow constraints)
 * @apiHeader {string} Authorization Bearer JWT token with tenant context
 * @apiHeader {string} Content-Type application/json
 * @apiParam {number} id Ticket ID (URL parameter)
 * @apiParam {string} title Updated ticket title
 * @apiParam {string} description Updated description
 * @apiParam {string} status Updated status
 * @apiParam {string} priority Updated priority
 * @apiParam {number} assigned_to Updated assigned user ID
 * @apiParam {number} customer_id Updated customer ID
 * @apiSuccess {Object} ticket Updated ticket object with new updated_at timestamp
 * @apiError {number} 404 Ticket not found or access denied
 * @apiError {number} 500 Failed to update ticket
 * @apiExample {curl} Example Request:
 *   curl -X PUT \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        -H "Content-Type: application/json" \
 *        -d '{
 *          "title": "Network connectivity issues - RESOLVED",
 *          "description": "Users unable to access file shares. Fixed by restarting switch.",
 *          "status": "closed",
 *          "priority": "high",
 *          "assigned_to": 5,
 *          "customer_id": 42
 *        }' \
 *        "https://api.everydaytech.au/tickets/126"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "ticket_id": 126,
 *     "title": "Network connectivity issues - RESOLVED",
 *     "status": "closed",
 *     "updated_at": "2026-02-10T16:45:00Z",
 *     ...
 *   }
 * @since 2.0.0
 * @see {@link module:routes/tickets.getTicket} for retrieving current ticket state
 */
router.put('/:id', async (req, res) => {
  const { id } = req.params;
  const { title, description, status, priority, assigned_to, customer_id } = req.body;
 
  try {
    const { clause, params, nextParamIndex } = getTenantFilter(req);
    
    // Build the query with proper parameter indexing
    // SET clause uses $1 through $6
    // Tenant filter starts at $7 (nextParamIndex would be 7 if tenant exists)
    // Ticket ID comes after tenant params
    
    const updateParams = [title, description, status, priority, assigned_to, customer_id];
    
    // Add tenant filter params (if any)
    const tenantParamStart = 7; // After the 6 SET params
    let ticketIdParamIndex;
    
    if (clause) {
      updateParams.push(...params);
      ticketIdParamIndex = tenantParamStart + params.length;
    } else {
      ticketIdParamIndex = 7;
    }
    
    updateParams.push(id);
    
    // Build WHERE clause with proper parameter indices
    const whereClauses = [`ticket_id = $${ticketIdParamIndex}`];
    if (clause) {
      // Replace $1 in tenant clause with correct param index
      const adjustedClause = clause.replace(/\$(\d+)/g, (match, num) => {
        return `$${tenantParamStart + parseInt(num) - 1}`;
      });
      whereClauses.push(adjustedClause);
    }
    
    const result = await pool.query(
      `UPDATE tickets 
       SET title = $1, description = $2, status = $3, priority = $4, 
           assigned_to = $5, customer_id = $6, updated_at = CURRENT_TIMESTAMP
       WHERE ${whereClauses.join(' AND ')}
       RETURNING *`,
      updateParams
    );
 
    if (result.rows.length === 0) {
      return res.status(404).send('Ticket not found');
    }
 
    res.json(result.rows[0]);
  } catch (err) {
    console.error('Error updating ticket:', err);
    res.status(500).send('Failed to update ticket');
  }
});
 
/**
 * @api {post} /tickets/:id/notes Add note to ticket
 * @apiName AddTicketNote
 * @apiGroup Tickets
 * @apiDescription Adds a note (comment) to a ticket with optional time tracking. If time
 * tracking parameters are provided, creates both a note and a time entry in a single
 * transaction. Useful for documenting work performed while tracking billable hours.
 * 
 * **Note Types:**
 * - Public notes: Visible to customers (is_private = false)
 * - Private notes: Internal only, hidden from customers (is_private = true)
 * 
 * **Time Tracking Options:**
 * 1. Provide start_time and end_time - calculates duration automatically
 * 2. Provide end_time and duration_minutes - calculates start_time
 * 3. Provide only duration_minutes - uses current time as end, calculates start
 * 
 * **Transaction Safety:**
 * Note and time entry created in a database transaction - both succeed or both fail.
 * @apiHeader {string} Authorization Bearer JWT token with tenant context
 * @apiHeader {string} Content-Type application/json
 * @apiParam {number} id Ticket ID (URL parameter)
 * @apiParam {string} content Note content/message (required)
 * @apiParam {boolean} [is_private=false] Whether note is internal only
 * @apiParam {string} [start_time] ISO timestamp for time entry start
 * @apiParam {string} [end_time] ISO timestamp for time entry end
 * @apiParam {number} [duration_minutes] Duration in minutes for time entry
 * @apiParam {boolean} [billable=false] Whether time entry is billable
 * @apiSuccess {Object} note Newly created note object
 * @apiError {number} 500 Failed to add note or time entry
 * @apiExample {curl} Simple Note:
 *   curl -X POST \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        -H "Content-Type: application/json" \
 *        -d '{"content": "Rebooted server, issue resolved", "is_private": false}' \
 *        "https://api.everydaytech.au/tickets/126/notes"
 * @apiExample {curl} Note with Time Tracking:
 *   curl -X POST \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        -H "Content-Type: application/json" \
 *        -d '{
 *          "content": "Troubleshooting network connectivity",
 *          "is_private": false,
 *          "duration_minutes": 45,
 *          "billable": true
 *        }' \
 *        "https://api.everydaytech.au/tickets/126/notes"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "note_id": 89,
 *     "ticket_id": 126,
 *     "user_id": 5,
 *     "content": "Rebooted server, issue resolved",
 *     "is_private": false,
 *     "created_at": "2026-02-10T16:30:00Z"
 *   }
 * @since 2.0.0
 * @see {@link module:routes/tickets.addTimeEntry} for standalone time tracking
 */
router.post('/:id/notes', async (req, res) => {
  const { id } = req.params;
  const { content, is_private, start_time, end_time, billable, duration_minutes } = req.body;
  const user_id = req.user.user_id;
 
  try {
    const client = await pool.connect();
    
    try {
      await client.query('BEGIN');
      
      const noteResult = await client.query(
        `INSERT INTO ticket_notes (ticket_id, user_id, content, is_private)
         VALUES ($1, $2, $3, $4)
         RETURNING *`,
        [id, user_id, content, is_private]
      );
      
      if ((start_time && end_time) || (duration_minutes && Number(duration_minutes) > 0)) {
        let startISO = start_time;
        let endISO = end_time;
        let mins = 0;
 
        if (startISO && endISO) {
          const start = new Date(startISO);
          const end = new Date(endISO);
          mins = Math.max(1, Math.round((end - start) / 60000));
        } else {
          const end = endISO ? new Date(endISO) : new Date();
          const minsIn = parseInt(duration_minutes, 10);
          const start = new Date(end.getTime() - Math.max(1, minsIn) * 60000);
          startISO = start.toISOString();
          endISO = end.toISOString();
          mins = Math.max(1, minsIn);
        }
 
        await client.query(
          `INSERT INTO ticket_time_entries (ticket_id, user_id, start_time, end_time, duration_minutes, description, billable)
           VALUES ($1, $2, $3, $4, $5, $6, $7)`,
          [id, user_id, startISO, endISO, mins, content, billable || false]
        );
      }
      
      await client.query('COMMIT');
      res.json(noteResult.rows[0]);
    } catch (err) {
      await client.query('ROLLBACK');
      throw err;
    } finally {
      client.release();
    }
  } catch (err) {
    console.error('Error adding note:', err);
    res.status(500).send('Failed to add note');
  }
});
 
/**
 * @api {post} /tickets/:id/time Add time entry to ticket
 * @apiName AddTimeEntry
 * @apiGroup Tickets
 * @apiDescription Adds a time tracking entry to a ticket for billing and reporting.
 * Records work performed with start/end times, duration, and billable status.
 * Used for accurate time tracking and invoice generation.
 * 
 * **Time Entry Fields:**
 * - start_time: When work began (ISO timestamp)
 * - end_time: When work ended (ISO timestamp)
 * - duration_minutes: Total minutes worked
 * - description: What work was performed
 * - billable: Whether to bill customer for this time
 * 
 * **Billing Integration:**
 * Billable time entries are aggregated for invoice generation and contract hour
 * consumption tracking.
 * @apiHeader {string} Authorization Bearer JWT token with tenant context
 * @apiHeader {string} Content-Type application/json
 * @apiParam {number} id Ticket ID (URL parameter)
 * @apiParam {string} start_time ISO timestamp when work started (required)
 * @apiParam {string} end_time ISO timestamp when work ended (required)
 * @apiParam {number} duration_minutes Duration in minutes (required)
 * @apiParam {string} description Work performed description (required)
 * @apiParam {boolean} billable Whether time is billable (required)
 * @apiSuccess {Object} entry Newly created time entry object
 * @apiError {number} 500 Failed to add time entry
 * @apiExample {curl} Example Request:
 *   curl -X POST \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        -H "Content-Type: application/json" \
 *        -d '{
 *          "start_time": "2026-02-10T14:00:00Z",
 *          "end_time": "2026-02-10T15:30:00Z",
 *          "duration_minutes": 90,
 *          "description": "Network troubleshooting and switch configuration",
 *          "billable": true
 *        }' \
 *        "https://api.everydaytech.au/tickets/126/time"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 201 Created
 *   {
 *     "entry_id": 234,
 *     "ticket_id": 126,
 *     "user_id": 5,
 *     "start_time": "2026-02-10T14:00:00Z",
 *     "end_time": "2026-02-10T15:30:00Z",
 *     "duration_minutes": 90,
 *     "description": "Network troubleshooting and switch configuration",
 *     "billable": true,
 *     "created_at": "2026-02-10T15:30:00Z"
 *   }
 * @since 2.0.0
 * @see {@link module:routes/tickets.addNote} for adding note with time tracking
 */
router.post('/:id/time', async (req, res) => {
  const { id } = req.params;
  const { start_time, end_time, duration_minutes, description, billable } = req.body;
  const user_id = req.user.user_id;
 
  try {
    const result = await pool.query(
      `INSERT INTO ticket_time_entries (
        ticket_id, user_id, start_time, end_time, 
        duration_minutes, description, billable
      )
      VALUES ($1, $2, $3, $4, $5, $6, $7)
      RETURNING *`,
      [id, user_id, start_time, end_time, duration_minutes, description, billable]
    );
 
    res.status(201).json(result.rows[0]);
  } catch (err) {
    console.error('Error adding time entry:', err);
    res.status(500).send('Failed to add time entry');
  }
});
 
/**
 * @api {delete} /tickets/:id Delete ticket
 * @apiName DeleteTicket
 * @apiGroup Tickets
 * @apiDescription Permanently deletes a ticket and all associated data (notes and time
 * entries). Admin-only operation. Use with caution as this action cannot be undone.
 * Typically used for data cleanup, spam removal, or test ticket deletion.
 * 
 * **Admin Only:**
 * Requires admin role via requireAdmin middleware. Regular users cannot delete tickets.
 * 
 * **Cascade Deletion:**
 * Automatically deletes:
 * - All ticket notes
 * - All time entries
 * - The ticket record itself
 * 
 * **Best Practice:**
 * Consider closing tickets instead of deleting them to preserve historical data
 * and audit trails. Deletion should be reserved for cleanup operations.
 * @apiHeader {string} Authorization Bearer JWT token with admin permissions
 * @apiParam {number} id Ticket ID to delete (URL parameter)
 * @apiSuccess {string} message Success confirmation message
 * @apiSuccess {object} deleted_ticket The deleted ticket object
 * @apiError {number} 403 Forbidden - Admin role required
 * @apiError {number} 404 Ticket not found or access denied
 * @apiError {number} 500 Failed to delete ticket
 * @apiExample {curl} Example Request:
 *   curl -X DELETE \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        "https://api.everydaytech.au/tickets/126"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "message": "Ticket deleted successfully",
 *     "deleted_ticket": {
 *       "ticket_id": 126,
 *       "title": "Test ticket for deletion",
 *       "status": "closed"
 *     }
 *   }
 * @apiExample {json} Error Response (Not Admin):
 *   HTTP/1.1 403 Forbidden
 *   {
 *     "error": "Admin access required"
 *   }
 * @since 2.0.0
 * @see {@link module:routes/tickets.updateTicket} for closing tickets (preferred)
 */
router.delete('/:id', requireAdmin, async (req, res) => {
  const ticketId = req.params.id;
  
  try {
    const { clause: tenantClause, params: tenantParams } = getTenantFilter(req, 't');
    
    // First check if ticket exists and user has access
    let checkQuery = 'SELECT ticket_id, title, status FROM tickets t WHERE t.ticket_id = $1';
    const checkParams = [ticketId];
    
    if (tenantClause) {
      const adjustedClause = tenantClause.replace(/\$(\d+)/g, (m, n) => `$${parseInt(n, 10) + 1}`);
      checkQuery += ` AND ${adjustedClause}`;
      checkParams.push(...tenantParams);
    }
    
    const checkResult = await pool.query(checkQuery, checkParams);
    
    if (checkResult.rows.length === 0) {
      return res.status(404).json({ error: 'Ticket not found' });
    }
    
    const ticket = checkResult.rows[0];
    
    // Allow deletion of tickets (note: can delete any status for data cleanup)
    // In production, you might want to restrict this based on business rules
    
    // Delete associated ticket data first (due to foreign key constraints)
    try {
      await pool.query('DELETE FROM ticket_time_entries WHERE ticket_id = $1', [ticketId]);
      await pool.query('DELETE FROM ticket_notes WHERE ticket_id = $1', [ticketId]);
    } catch (relatedErr) {
      console.log('No ticket notes or time entries to delete or tables do not exist');
    }
    
    // Delete the ticket
    const result = await pool.query(
      'DELETE FROM tickets WHERE ticket_id = $1 RETURNING *',
      [ticketId]
    );
    
    console.log(`[Tickets] Ticket deleted: ID ${ticketId}, Title: "${ticket.title}", User: ${req.user?.user_id}, Tenant: ${req.tenant?.id}`);
    
    res.json({ 
      message: 'Ticket deleted successfully', 
      deleted_ticket: result.rows[0] 
    });
  } catch (err) {
    console.error('Error deleting ticket:', err);
    res.status(500).json({ error: 'Failed to delete ticket', details: err.message });
  }
});
 
module.exports = router;