byop-engine/dbstore/clients.go

package dbstore

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

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

// Client operations
func (s *SQLiteStore) CreateClient(ctx context.Context, client models.Client) (int, error) {
	query := `INSERT INTO clients (name, description, contact_info, active) VALUES (?, ?, ?, ?)`
	result, err := s.db.ExecContext(ctx, query, client.Name, client.Description, client.ContactInfo, client.Active)
	if err != nil {
		return 0, models.NewErrInternalServer("failed to create client", err)
	}

	id, err := result.LastInsertId()
	if err != nil {
		return 0, models.NewErrInternalServer("failed to get last insert ID for client", err)
	}

	return int(id), nil
}

func (s *SQLiteStore) GetAllClients(ctx context.Context) ([]models.Client, error) {
	query := `SELECT id, name, description, contact_info, active, created_at, updated_at FROM clients`
	rows, err := s.db.QueryContext(ctx, query)
	if err != nil {
		return nil, models.NewErrInternalServer("failed to query clients", err)
	}
	defer rows.Close()

	var clients []models.Client
	for rows.Next() {
		var client models.Client
		err := rows.Scan(&client.ID, &client.Name, &client.Description, &client.ContactInfo, &client.Active, &client.CreatedAt, &client.UpdatedAt)
		if err != nil {
			return nil, models.NewErrInternalServer("failed to scan client row", err)
		}
		clients = append(clients, client)
	}
	if err = rows.Err(); err != nil {
		return nil, models.NewErrInternalServer("error iterating client rows", err)
	}

	return clients, nil
}

func (s *SQLiteStore) GetClientByID(ctx context.Context, id int) (models.Client, error) {
	var client models.Client
	query := `SELECT id, name, description, contact_info, active, created_at, updated_at FROM clients WHERE id = ?`
	err := s.db.QueryRowContext(ctx, query, id).Scan(&client.ID, &client.Name, &client.Description, &client.ContactInfo, &client.Active, &client.CreatedAt, &client.UpdatedAt)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return client, models.NewErrNotFound(fmt.Sprintf("client with ID %d not found", id), err)
		}
		return client, models.NewErrInternalServer(fmt.Sprintf("failed to get client with ID %d", id), err)
	}
	return client, nil
}

// UpdateClient updates an existing client
func (s *SQLiteStore) UpdateClient(ctx context.Context, client models.Client) error {
	query := `UPDATE clients SET name = ?, description = ?, contact_info = ?, active = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?` // Added CURRENT_TIMESTAMP for updated_at
	result, err := s.db.ExecContext(ctx, query, client.Name, client.Description, client.ContactInfo, client.Active, client.ID)
	if err != nil {
		return models.NewErrInternalServer(fmt.Sprintf("failed to update client with ID %d", client.ID), err)
	}
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return models.NewErrInternalServer(fmt.Sprintf("failed to get rows affected for client update ID %d", client.ID), err)
	}
	if rowsAffected == 0 {
		return models.NewErrNotFound(fmt.Sprintf("client with ID %d not found for update", client.ID), nil)
	}
	return nil
}

// DeleteClient deletes a client by ID
func (s *SQLiteStore) DeleteClient(ctx context.Context, id int) error {
	query := `DELETE FROM clients WHERE id = ?`
	result, err := s.db.ExecContext(ctx, query, id)
	if err != nil {
		return models.NewErrInternalServer(fmt.Sprintf("failed to delete client with ID %d", id), err)
	}
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return models.NewErrInternalServer(fmt.Sprintf("failed to get rows affected for client deletion ID %d", id), err)
	}
	if rowsAffected == 0 {
		return models.NewErrNotFound(fmt.Sprintf("client with ID %d not found for deletion", id), nil)
	}
	return nil
}