byop-engine/dbstore/tickets.go

package dbstore

import (
	"context"
	"database/sql"
	"fmt"

	"git.linuxforward.com/byop/byop-engine/models"
	"github.com/pkg/errors"
)

// CreateTicket creates a new ticket
func (s *SQLiteStore) CreateTicket(ctx context.Context, ticket *models.Ticket) error {
	query := `INSERT INTO tickets (client_id, title, description, status, priority, assigned_to) VALUES (?, ?, ?, ?, ?, ?)`
	result, err := s.db.ExecContext(ctx, query, ticket.ClientID, ticket.Title, ticket.Description, ticket.Status, ticket.Priority, ticket.AssignedTo)
	if err != nil {
		return models.NewErrInternalServer("failed to create ticket", err)
	}
	id, err := result.LastInsertId()
	if err != nil {
		return models.NewErrInternalServer("failed to get last insert ID for ticket", err)
	}
	ticket.ID = int(id)
	return nil
}

// GetTicketByID retrieves a ticket by its ID
func (s *SQLiteStore) GetTicketByID(ctx context.Context, id int) (*models.Ticket, error) {
	query := `SELECT id, client_id, title, description, status, priority, assigned_to, created_at, updated_at FROM tickets WHERE id = ?`
	row := s.db.QueryRowContext(ctx, query, id)
	ticket := &models.Ticket{}
	err := row.Scan(&ticket.ID, &ticket.ClientID, &ticket.Title, &ticket.Description, &ticket.Status, &ticket.Priority, &ticket.AssignedTo, &ticket.CreatedAt, &ticket.UpdatedAt)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, models.NewErrNotFound(fmt.Sprintf("ticket with ID %d not found", id), err)
		}
		return nil, models.NewErrInternalServer(fmt.Sprintf("failed to get ticket with ID %d", id), err)
	}
	return ticket, nil
}

// GetTickets retrieves all tickets
func (s *SQLiteStore) GetTickets(ctx context.Context) ([]models.Ticket, error) {
	query := `SELECT id, client_id, title, description, status, priority, assigned_to, created_at, updated_at FROM tickets`
	rows, err := s.db.QueryContext(ctx, query)
	if err != nil {
		return nil, models.NewErrInternalServer("failed to query tickets", err)
	}
	defer rows.Close()

	var tickets []models.Ticket
	for rows.Next() {
		var ticket models.Ticket
		err := rows.Scan(&ticket.ID, &ticket.ClientID, &ticket.Title, &ticket.Description, &ticket.Status, &ticket.Priority, &ticket.AssignedTo, &ticket.CreatedAt, &ticket.UpdatedAt)
		if err != nil {
			return nil, models.NewErrInternalServer("failed to scan ticket row", err)
		}
		tickets = append(tickets, ticket)
	}
	if err = rows.Err(); err != nil {
		return nil, models.NewErrInternalServer("error iterating ticket rows", err)
	}
	return tickets, nil
}

// UpdateTicket updates an existing ticket
func (s *SQLiteStore) UpdateTicket(ctx context.Context, ticket *models.Ticket) error {
	query := `UPDATE tickets SET client_id = ?, title = ?, description = ?, status = ?, priority = ?, assigned_to = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?`
	result, err := s.db.ExecContext(ctx, query, ticket.ClientID, ticket.Title, ticket.Description, ticket.Status, ticket.Priority, ticket.AssignedTo, ticket.ID)
	if err != nil {
		return models.NewErrInternalServer(fmt.Sprintf("failed to update ticket with ID %d", ticket.ID), err)
	}
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return models.NewErrInternalServer(fmt.Sprintf("failed to get rows affected for ticket update ID %d", ticket.ID), err)
	}
	if rowsAffected == 0 {
		return models.NewErrNotFound(fmt.Sprintf("ticket with ID %d not found for update", ticket.ID), nil)
	}
	return nil
}

// CreateTicketComment creates a new comment for a ticket
func (s *SQLiteStore) CreateTicketComment(ctx context.Context, comment *models.TicketComment) error {
	query := `INSERT INTO ticket_comments (ticket_id, user_id, comment) VALUES (?, ?, ?)`
	result, err := s.db.ExecContext(ctx, query, comment.TicketID, comment.UserID, comment.Content)
	if err != nil {
		return models.NewErrInternalServer("failed to create ticket comment", err)
	}
	id, err := result.LastInsertId()
	if err != nil {
		return models.NewErrInternalServer("failed to get last insert ID for ticket comment", err)
	}
	comment.ID = int(id)
	return nil
}

// GetTicketComments retrieves all comments for a given ticket ID
func (s *SQLiteStore) GetTicketComments(ctx context.Context, ticketID int) ([]models.TicketComment, error) {
	query := `SELECT id, ticket_id, user_id, comment, created_at FROM ticket_comments WHERE ticket_id = ? ORDER BY created_at ASC`
	rows, err := s.db.QueryContext(ctx, query, ticketID)
	if err != nil {
		return nil, models.NewErrInternalServer(fmt.Sprintf("failed to query ticket comments for ticket ID %d", ticketID), err)
	}
	defer rows.Close()

	var comments []models.TicketComment
	for rows.Next() {
		var comment models.TicketComment
		err := rows.Scan(&comment.ID, &comment.TicketID, &comment.UserID, &comment.Content, &comment.CreatedAt)
		if err != nil {
			return nil, models.NewErrInternalServer("failed to scan ticket comment row", err)
		}
		comments = append(comments, comment)
	}
	if err = rows.Err(); err != nil {
		return nil, models.NewErrInternalServer(fmt.Sprintf("error iterating ticket comment rows for ticket ID %d", ticketID), err)
	}
	return comments, nil
}