softplayer-backend/internal/repository/projects.go

package repository

import (
	"context"
	"database/sql"
	"errors"
	"time"

	"github.com/jackc/pgerrcode"
	"github.com/jackc/pgx/v5/pgconn"
)

type ProjectData struct {
	UUID        string
	Name        string
	Slug        string
	Description string
	CreatedBy   string
	CreatedAt   time.Time
	ClosedAt    sql.NullTime
	Blocked     bool
	UpdatedAt   time.Time
	UpdatedBy   string
}

// CreateProject adds a new projects to the database
func CreateProject(ctx context.Context, db *sql.DB, data *ProjectData) error {
	tx, err := StartTransaction(ctx, db)
	if err != nil {
		return err
	}
	defer tx.Rollback()
	queryProject := `
	INSERT INTO projects
	(uuid, name, slug, description, owner_user_id, billing_account_id, created_by, created_at, updated_by, updated_at)
	VALUES
	($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) 
	`

	if _, err := tx.ExecContext(ctx, queryProject,
		data.UUID, data.Name, data.Slug, data.Description, data.CreatedBy,
		data.CreatedBy, data.CreatedBy, data.CreatedAt, data.CreatedBy, data.CreatedAt); err != nil {
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) {
			switch pgErr.Code {
			case pgerrcode.UniqueViolation:
				return ErrAlreadyExists
			case pgerrcode.CheckViolation:
				return ErrCheckNotPassed
			}
			return err
		}
		return err
	}

	// When a project is created, we need to insert the default owner project membership
	queryMembership := `
	INSERT INTO project_membership
	(project_uuid, user_uuid, role, status, invited_by, joined_at)
	VALUES
	($1, $2, $3, $4, $5, $6);
	`
	if _, err := tx.ExecContext(ctx, queryMembership,
		data.UUID, data.CreatedBy, "owner", "active", data.CreatedBy, data.CreatedAt,
	); err != nil {
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) {
			switch pgErr.Code {
			case pgerrcode.UniqueViolation:
				return ErrAlreadyExists
			case pgerrcode.CheckViolation:
				return ErrCheckNotPassed
			}
			return err
		}
		return err
	}

	if err := tx.Commit(); err != nil {
		return err
	}
	return nil
}

// GetProjectByID returns a project from the database
func GetProjectByID(ctx context.Context, db *sql.DB, projectID string) (*ProjectData, error) {
	data := &ProjectData{}
	query := `
	SELECT uuid, name, slug, description, owner_user_id, closed_at, created_at
	FROM projects
	WHERE uuid=$1`

	if err := db.QueryRowContext(ctx, query, projectID).Scan(
		&data.UUID,
		&data.Name,
		&data.Slug,
		&data.Description,
		&data.CreatedBy,
		&data.ClosedAt,
		&data.CreatedAt,
	); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, ErrNotFound
		}
		return nil, err
	}
	return data, nil
}

// UpdateProject change editable project data
func UpdateProject(ctx context.Context, db *sql.DB, data *ProjectData) error {
	query := `
	UPDATE projects
	SET 
		name = $2,
		description = $3,
		updated_at = $4,
		updated_by = $5
	WHERE uuid = $1;`
	if _, err := db.Query(query, data.UUID, data.Name, data.Description, data.UpdatedAt, data.UpdatedBy); err != nil {
		return err
	}
	return nil
}

// ListProjects get all projects that are available for the user from the database
func ListProjects(ctx context.Context, db *sql.DB, userID string) ([]*ProjectData, error) {
	query := `
	  SELECT p.uuid, p.name
    FROM projects p
    JOIN project_membership pm ON pm.project_uuid = p.uuid
    WHERE pm.user_uuid = $1`
	rows, err := db.QueryContext(ctx, query, userID)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, ErrNotFound
		}
		return nil, err
	}
	result := []*ProjectData{}

	for rows.Next() {
		pd := &ProjectData{}
		err := rows.Scan(&pd.UUID, &pd.Name)
		if err != nil {
			return nil, err
		}
		result = append(result, pd)
	}
	return result, nil
}

// GetProjectOwner should return an owner if a project
func GetProjectOwner(ctx context.Context, db *sql.DB, projectID string) (userID string, err error) {
	query := `
	  SELECT user_uuid
    FROM project_membership
    WHERE project_uuid = $1 AND role = 'owner'`

	if err := db.QueryRowContext(ctx, query, projectID).Scan(
		&userID,
	); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return "", ErrNotFound
		}
		return "", err
	}
	return
}