xtablo-source/deprecated/internal/db/repository.go

package db

import (
	"context"
	"errors"
	"fmt"
	"strings"
	"time"

	"github.com/google/uuid"
	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgtype"
	"github.com/jackc/pgx/v5/pgxpool"
	"github.com/rs/zerolog/log"

	sqlcdb "xtablo-backend/internal/db/sqlc"
	tablomodel "xtablo-backend/internal/tablos"
	taskmodel "xtablo-backend/internal/tasks"
	"xtablo-backend/internal/web/handlers"
)

type PostgresAuthRepository struct {
	pool    *pgxpool.Pool
	queries *sqlcdb.Queries
}

const defaultTabloColor = "#3B82F6"

func NewPostgresAuthRepository(ctx context.Context, databaseURL string) (*PostgresAuthRepository, error) {
	if databaseURL == "" {
		return nil, errors.New("DATABASE_URL is required")
	}

	pool, err := pgxpool.New(ctx, databaseURL)
	if err != nil {
		return nil, fmt.Errorf("connect postgres: %w", err)
	}

	return &PostgresAuthRepository{
		pool:    pool,
		queries: sqlcdb.New(pool),
	}, nil
}

func (r *PostgresAuthRepository) Close() {
	r.pool.Close()
}

func (r *PostgresAuthRepository) CreateAuthUser(ctx context.Context, input handlers.CreateAuthUserInput) (uuid.UUID, error) {
	id := uuid.New()
	createdID, err := r.queries.CreateAuthUser(ctx, sqlcdb.CreateAuthUserParams{
		ID:                id,
		Email:             input.Email,
		EncryptedPassword: input.EncryptedPassword,
		DisplayName:       input.DisplayName,
	})
	if err != nil {
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) && pgErr.Code == "23505" {
			return uuid.Nil, handlers.ErrUserAlreadyExists
		}
		return uuid.Nil, err
	}

	log.Info().
		Str("component", "auth_store").
		Str("action", "create_user").
		Str("email", input.Email).
		Msg("auth store mutated")

	return createdID, nil
}

func (r *PostgresAuthRepository) GetAuthUserByEmail(ctx context.Context, email string) (handlers.AuthUser, error) {
	row, err := r.queries.GetAuthUserByEmail(ctx, email)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return handlers.AuthUser{}, handlers.ErrUserNotFound
		}
		return handlers.AuthUser{}, err
	}

	return handlers.AuthUser{
		ID:                row.ID,
		Email:             row.Email,
		EncryptedPassword: row.EncryptedPassword,
		CreatedAt:         row.CreatedAt.Time,
		UpdatedAt:         row.UpdatedAt.Time,
	}, nil
}

func (r *PostgresAuthRepository) GetPublicUserByID(ctx context.Context, id uuid.UUID) (handlers.PublicUser, error) {
	row, err := r.queries.GetPublicUserByID(ctx, id)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return handlers.PublicUser{}, handlers.ErrUserNotFound
		}
		return handlers.PublicUser{}, err
	}

	return handlers.PublicUser{
		ID:          row.ID,
		Email:       row.Email,
		DisplayName: row.DisplayName,
		CreatedAt:   row.CreatedAt.Time,
		UpdatedAt:   row.UpdatedAt.Time,
	}, nil
}

func (r *PostgresAuthRepository) CreateSession(ctx context.Context, token string, userID uuid.UUID, expiresAt time.Time) error {
	err := r.queries.CreateSession(ctx, sqlcdb.CreateSessionParams{
		ID:           uuid.New(),
		SessionToken: token,
		UserID:       userID,
		ExpiresAt:    pgtypeTimestamptz(expiresAt),
	})
	return err
}

func (r *PostgresAuthRepository) GetSessionByToken(ctx context.Context, token string) (handlers.Session, error) {
	row, err := r.queries.GetSessionByToken(ctx, token)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return handlers.Session{}, handlers.ErrSessionNotFound
		}
		return handlers.Session{}, err
	}

	return handlers.Session{
		Token:     row.SessionToken,
		UserID:    row.UserID,
		CreatedAt: row.CreatedAt.Time,
		UpdatedAt: row.UpdatedAt.Time,
		ExpiresAt: row.ExpiresAt.Time,
	}, nil
}

func (r *PostgresAuthRepository) DeleteSessionByToken(ctx context.Context, token string) error {
	rows, err := r.queries.DeleteSessionByToken(ctx, token)
	if err != nil {
		return err
	}
	if rows == 0 {
		return handlers.ErrSessionNotFound
	}
	return nil
}

func (r *PostgresAuthRepository) CreateTablo(ctx context.Context, input tablomodel.CreateInput) (tablomodel.Record, error) {
	row, err := r.queries.CreateTablo(ctx, sqlcdb.CreateTabloParams{
		ID:      uuid.New(),
		OwnerID: input.OwnerID,
		Name:    strings.TrimSpace(input.Name),
		Color:   storedTabloColor(input.Color),
		Status:  string(input.Status),
	})
	if err != nil {
		return tablomodel.Record{}, err
	}

	return mapTabloRecord(row), nil
}

func (r *PostgresAuthRepository) ListTablos(ctx context.Context, input tablomodel.ListInput) ([]tablomodel.Record, error) {
	params := sqlcdb.ListTablosParams{
		OwnerID: input.OwnerID,
		Status:  nullableStatus(input.Status),
	}

	rows, err := r.queries.ListTablos(ctx, params)
	if err != nil {
		return nil, err
	}

	tablos := make([]tablomodel.Record, 0, len(rows))
	for _, row := range rows {
		tablos = append(tablos, mapTabloRecord(row))
	}
	return tablos, nil
}

func (r *PostgresAuthRepository) UpdateTablo(ctx context.Context, input tablomodel.UpdateInput) error {
	rows, err := r.queries.UpdateTablo(ctx, sqlcdb.UpdateTabloParams{
		ID:      input.ID,
		OwnerID: input.OwnerID,
		Name:    strings.TrimSpace(input.Name),
		Color:   storedTabloColor(input.Color),
	})
	if err != nil {
		return err
	}
	if rows == 0 {
		return tablomodel.ErrNotFound
	}
	return nil
}

func (r *PostgresAuthRepository) SoftDeleteTablo(ctx context.Context, tabloID uuid.UUID, ownerID uuid.UUID) error {
	rows, err := r.queries.SoftDeleteTablo(ctx, sqlcdb.SoftDeleteTabloParams{
		ID:      tabloID,
		OwnerID: ownerID,
	})
	if err != nil {
		return err
	}
	if rows == 0 {
		return tablomodel.ErrNotFound
	}
	return nil
}

func (r *PostgresAuthRepository) CreateTask(ctx context.Context, input handlers.CreateTaskInput) (handlers.TaskRecord, error) {
	row, err := r.queries.CreateTask(ctx, sqlcdb.CreateTaskParams{
		ID:           uuid.New(),
		TabloID:      input.TabloID,
		OwnerID:      input.OwnerID,
		Title:        strings.TrimSpace(input.Title),
		Description:  strings.TrimSpace(input.Description),
		Status:       string(input.Status),
		AssigneeID:   nullableUUID(input.AssigneeID),
		IsEtape:      input.IsEtape,
		ParentTaskID: nullableUUID(input.ParentTaskID),
		DueDate:      nullableDate(input.DueDate),
	})
	if err != nil {
		return handlers.TaskRecord{}, err
	}
	return mapTaskRecord(row), nil
}

func (r *PostgresAuthRepository) ListTasksByOwner(ctx context.Context, ownerID uuid.UUID) ([]handlers.TaskRecord, error) {
	rows, err := r.queries.ListTasksByOwner(ctx, ownerID)
	if err != nil {
		return nil, err
	}

	records := make([]handlers.TaskRecord, 0, len(rows))
	for _, row := range rows {
		records = append(records, mapTaskRecord(row))
	}
	return records, nil
}

func (r *PostgresAuthRepository) ListTasksByTablo(ctx context.Context, input handlers.ListTasksByTabloInput) ([]handlers.TaskRecord, error) {
	rows, err := r.queries.ListTasksByTablo(ctx, sqlcdb.ListTasksByTabloParams{
		OwnerID: input.OwnerID,
		TabloID: input.TabloID,
	})
	if err != nil {
		return nil, err
	}

	records := make([]handlers.TaskRecord, 0, len(rows))
	for _, row := range rows {
		records = append(records, mapTaskRecord(row))
	}
	return records, nil
}

func (r *PostgresAuthRepository) GetTaskByID(ctx context.Context, taskID uuid.UUID, ownerID uuid.UUID) (handlers.TaskRecord, error) {
	row, err := r.queries.GetTaskByID(ctx, sqlcdb.GetTaskByIDParams{
		ID:      taskID,
		OwnerID: ownerID,
	})
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return handlers.TaskRecord{}, taskmodel.ErrNotFound
		}
		return handlers.TaskRecord{}, err
	}
	return mapTaskRecord(row), nil
}

func (r *PostgresAuthRepository) UpdateTask(ctx context.Context, input handlers.UpdateTaskInput) (handlers.TaskRecord, error) {
	row, err := r.queries.UpdateTask(ctx, sqlcdb.UpdateTaskParams{
		ID:           input.ID,
		OwnerID:      input.OwnerID,
		Title:        strings.TrimSpace(input.Title),
		Description:  strings.TrimSpace(input.Description),
		Status:       string(input.Status),
		DueDate:      nullableDate(input.DueDate),
		AssigneeID:   nullableUUID(input.AssigneeID),
		ParentTaskID: nullableUUID(input.ParentTaskID),
	})
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return handlers.TaskRecord{}, taskmodel.ErrNotFound
		}
		return handlers.TaskRecord{}, err
	}
	return mapTaskRecord(row), nil
}

func (r *PostgresAuthRepository) SoftDeleteTask(ctx context.Context, taskID uuid.UUID, ownerID uuid.UUID) error {
	tx, err := r.pool.Begin(ctx)
	if err != nil {
		return err
	}
	defer tx.Rollback(ctx)

	queries := r.queries.WithTx(tx)

	task, err := queries.GetTaskByID(ctx, sqlcdb.GetTaskByIDParams{
		ID:      taskID,
		OwnerID: ownerID,
	})
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return taskmodel.ErrNotFound
		}
		return err
	}

	if task.IsEtape {
		if _, err := queries.ClearTaskChildrenParent(ctx, sqlcdb.ClearTaskChildrenParentParams{
			ParentTaskID: pgtypeUUID(task.ID),
			OwnerID:      ownerID,
		}); err != nil {
			return err
		}
	}

	rows, err := queries.SoftDeleteTask(ctx, sqlcdb.SoftDeleteTaskParams{
		ID:      taskID,
		OwnerID: ownerID,
	})
	if err != nil {
		return err
	}
	if rows == 0 {
		return taskmodel.ErrNotFound
	}

	return tx.Commit(ctx)
}

func pgtypeTimestamptz(value time.Time) pgtype.Timestamptz {
	return pgtype.Timestamptz{Time: value, Valid: true}
}

func nullableText(value string) pgtype.Text {
	if value == "" {
		return pgtype.Text{}
	}
	return pgtype.Text{String: value, Valid: true}
}

func storedTabloColor(value string) string {
	trimmed := strings.TrimSpace(value)
	if trimmed == "" {
		return defaultTabloColor
	}
	return trimmed
}

func nullableStatus(value *tablomodel.Status) pgtype.Text {
	if value == nil {
		return pgtype.Text{}
	}
	return nullableText(string(*value))
}

func nullableUUID(value *uuid.UUID) pgtype.UUID {
	if value == nil {
		return pgtype.UUID{}
	}
	return pgtypeUUID(*value)
}

func pgtypeUUID(value uuid.UUID) pgtype.UUID {
	var bytes [16]byte
	copy(bytes[:], value[:])
	return pgtype.UUID{Bytes: bytes, Valid: true}
}

func nullableDate(value *time.Time) pgtype.Date {
	if value == nil {
		return pgtype.Date{}
	}
	return pgtype.Date{Time: *value, Valid: true}
}

func mapTabloRecord(row sqlcdb.Tablo) tablomodel.Record {
	record := tablomodel.Record{
		ID:        row.ID,
		OwnerID:   row.OwnerID,
		Name:      row.Name,
		Color:     storedTabloColor(row.Color),
		Status:    tablomodel.Status(row.Status),
		CreatedAt: row.CreatedAt.Time,
		UpdatedAt: row.UpdatedAt.Time,
	}
	if row.DeletedAt.Valid {
		deletedAt := row.DeletedAt.Time
		record.DeletedAt = &deletedAt
	}
	return record
}

func mapTaskRecord(row sqlcdb.Task) handlers.TaskRecord {
	record := handlers.TaskRecord{
		ID:          row.ID,
		OwnerID:     row.OwnerID,
		TabloID:     row.TabloID,
		Title:       row.Title,
		Description: row.Description,
		Status:      taskmodel.Status(row.Status),
		IsEtape:     row.IsEtape,
		CreatedAt:   row.CreatedAt.Time,
		UpdatedAt:   row.UpdatedAt.Time,
	}
	if row.AssigneeID.Valid {
		assigneeID := uuid.UUID(row.AssigneeID.Bytes)
		record.AssigneeID = &assigneeID
	}
	if row.ParentTaskID.Valid {
		parentID := uuid.UUID(row.ParentTaskID.Bytes)
		record.ParentTaskID = &parentID
	}
	if row.DueDate.Valid {
		dueDate := row.DueDate.Time
		record.DueDate = &dueDate
	}
	if row.DeletedAt.Valid {
		deletedAt := row.DeletedAt.Time
		record.DeletedAt = &deletedAt
	}
	return record
}