webstory/pkg/database/actor/actor.go

package actor

import (
	"database/sql"
	"errors"
	"fmt"
	"regexp"
	"strings"
	"time"

	v1 "git.tipsy.codes/charles/webstory/pkg/api/webstory/v1"
	"github.com/google/uuid"
	"google.golang.org/protobuf/types/known/timestamppb"
)

var (
	// ErrActorIDInvalid is returned when the actor_id format is invalid.
	ErrActorIDInvalid = errors.New("invalid actor_id format: must be 4-63 characters, matching pattern /[a-z][0-9-][0-9]$/")

	// actorIDPattern validates the actor_id format.
	// Must start with a lowercase letter, end with a number, and contain only lowercase letters, numbers, and hyphens.
	actorIDPattern = regexp.MustCompile(`^[a-z][0-9a-z-]{2,61}[0-9]$`)
)

// DBActor represents an actor in the PostgreSQL database.
// This is the database model that corresponds to the schema.sql structure.
type DBActor struct {
	// Database-generated unique identifier
	ID int64

	// Foreign key to story
	StoryID int64

	// Actor ID (used in resource name: stories/{story}/actors/{actor_id})
	ActorID string

	// Name of the actor
	NameValue string

	// Role or character name this actor plays in the story
	Role string

	// Optional notes about the actor
	Notes string

	// CreatedAt is when the actor was created
	CreatedAt time.Time

	// UpdatedAt is when the actor was last updated
	UpdatedAt time.Time

	// Etag is used for concurrency control
	Etag string
}

// ActorTranslator provides conversion methods between API and database formats.
type ActorTranslator struct{}

// NewActorTranslator creates a new ActorTranslator instance.
func NewActorTranslator() *ActorTranslator {
	return &ActorTranslator{}
}

// FromAPI converts an API Actor message to a DBActor struct.
// This handles the conversion from the protobuf-generated type to the database model.
func (t *ActorTranslator) FromAPI(apiActor *v1.Actor) (*DBActor, error) {
	if apiActor == nil {
		return nil, errors.New("api actor cannot be nil")
	}

	// Validate actor_id (required field)
	if err := validateActorID(apiActor.ActorId); err != nil {
		return nil, err
	}

	// Validate name_value (required field)
	if apiActor.NameValue == "" {
		return nil, errors.New("name_value is required")
	}

	// Convert timestamps if provided
	var createdAt, updatedAt time.Time
	if apiActor.GetCreateTime() != nil {
		createdAt = apiActor.CreateTime.AsTime()
	}
	if apiActor.GetUpdateTime() != nil {
		updatedAt = apiActor.UpdateTime.AsTime()
	}

	return &DBActor{
		ID:          0, // Will be generated by database
		StoryID:     0, // Will be provided during save
		ActorID:     apiActor.ActorId,
		NameValue:   apiActor.NameValue,
		Role:        apiActor.Role,
		Notes:       apiActor.Notes,
		CreatedAt:   createdAt,
		UpdatedAt:   updatedAt,
		Etag:        apiActor.Etag,
	}, nil
}

// ToAPI converts a DBActor struct to an API Actor message.
// This prepares the database model for response serialization.
func (t *ActorTranslator) ToAPI(dbActor *DBActor) *v1.Actor {
	if dbActor == nil {
		return nil
	}

	// Build resource name
	name := fmt.Sprintf("stories/%d/actors/%s", dbActor.StoryID, dbActor.ActorID)

	return &v1.Actor{
		Name:        name,
		ActorId:     dbActor.ActorID,
		NameValue:   dbActor.NameValue,
		Role:        dbActor.Role,
		Notes:       dbActor.Notes,
		CreateTime:  timestamppb.New(dbActor.CreatedAt),
		UpdateTime:  timestamppb.New(dbActor.UpdatedAt),
		Etag:        dbActor.Etag,
	}
}

// ToAPIDetailed converts a DBActor to an API Actor with parent resource name.
// This is used when loading actors with their parent story.
func (t *ActorTranslator) ToAPIDetailed(dbActor *DBActor, parentStoryName string) *v1.Actor {
	apiActor := t.ToAPI(dbActor)
	if parentStoryName != "" {
		apiActor.Name = parentStoryName
	}
	return apiActor
}

// Validate validates the DBActor struct.
func (a *DBActor) Validate() error {
	if a.StoryID <= 0 {
		return errors.New("story_id is required")
	}

	if a.ActorID == "" {
		return errors.New("actor_id is required")
	}

	if err := validateActorID(a.ActorID); err != nil {
		return err
	}

	if a.NameValue == "" {
		return errors.New("name_value is required")
	}

	return nil
}

// validateActorID validates the actor_id format.
func validateActorID(actorID string) error {
	if actorID == "" {
		return ErrActorIDInvalid
	}

	if len(actorID) < 4 || len(actorID) > 63 {
		return ErrActorIDInvalid
	}

	if !actorIDPattern.MatchString(actorID) {
		return ErrActorIDInvalid
	}

	return nil
}

// Save saves the DBActor to the database.
// If the actor has an ID, it performs an UPDATE; otherwise, it performs an INSERT.
func (a *DBActor) Save(db *sql.DB) error {
	if err := a.Validate(); err != nil {
		return err
	}

	// Check if this is an update (has ID from database) or insert
	if a.ID > 0 {
		return a.update(db)
	}

	return a.insert(db)
}

// insert inserts a new actor into the database.
func (a *DBActor) insert(db *sql.DB) error {
	query := `
		INSERT INTO actors (story_id, actor_id, name_value, role, notes, created_at, updated_at, etag)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
		RETURNING id, created_at, updated_at, etag
	`

	etag := a.Etag
	if etag == "" {
		etag = generateEtag()
	}

	var createdID int64
	var createdAt, updatedAt time.Time
	var returnedEtag string

	err := db.QueryRow(
		query,
		a.StoryID,
		a.ActorID,
		a.NameValue,
		a.Role,
		a.Notes,
		a.CreatedAt,
		a.UpdatedAt,
		etag,
	).Scan(&createdID, &createdAt, &updatedAt, &returnedEtag)

	if err != nil {
		return fmt.Errorf("failed to insert actor: %w", err)
	}

	a.ID = createdID
	a.CreatedAt = createdAt
	a.UpdatedAt = updatedAt
	a.Etag = returnedEtag

	return nil
}

// update updates an existing actor in the database.
func (a *DBActor) update(db *sql.DB) error {
	query := `
		UPDATE actors
		SET name_value = $1,
		    role = $2,
		    notes = $3,
		    updated_at = $4,
		    etag = $5
		WHERE id = $6
		RETURNING created_at, updated_at, etag
	`

	now := time.Now()
	if a.UpdatedAt.IsZero() {
		a.UpdatedAt = now
	}

	etag := a.Etag
	if etag == "" {
		etag = generateEtag()
	}

	var createdAt, updatedAt time.Time
	var returnedEtag string

	err := db.QueryRow(
		query,
		a.NameValue,
		a.Role,
		a.Notes,
		a.UpdatedAt,
		etag,
		a.ID,
	).Scan(&createdAt, &updatedAt, &returnedEtag)

	if err != nil {
		return fmt.Errorf("failed to update actor: %w", err)
	}

	a.CreatedAt = createdAt
	a.UpdatedAt = updatedAt
	a.Etag = returnedEtag

	return nil
}

// generateEtag generates a unique etag for the actor.
func generateEtag() string {
	return uuid.New().String()
}

// GetByID retrieves an actor by its primary key ID.
func GetByID(db *sql.DB, id int64) (*DBActor, error) {
	query := `
		SELECT id, story_id, actor_id, name_value, role, notes, created_at, updated_at, etag
		FROM actors
		WHERE id = $1
	`

	actor := &DBActor{}
	err := db.QueryRow(query, id).Scan(
		&actor.ID,
		&actor.StoryID,
		&actor.ActorID,
		&actor.NameValue,
		&actor.Role,
		&actor.Notes,
		&actor.CreatedAt,
		&actor.UpdatedAt,
		&actor.Etag,
	)

	if err != nil {
		if err == sql.ErrNoRows {
			return nil, fmt.Errorf("actor with id %d not found", id)
		}
		return nil, fmt.Errorf("failed to get actor: %w", err)
	}

	return actor, nil
}

// GetByActorID retrieves an actor by its actor_id and story_id.
func GetByActorID(db *sql.DB, storyID int64, actorID string) (*DBActor, error) {
	if err := validateActorID(actorID); err != nil {
		return nil, err
	}

	query := `
		SELECT id, story_id, actor_id, name_value, role, notes, created_at, updated_at, etag
		FROM actors
		WHERE story_id = $1 AND actor_id = $2
	`

	actor := &DBActor{}
	err := db.QueryRow(query, storyID, actorID).Scan(
		&actor.ID,
		&actor.StoryID,
		&actor.ActorID,
		&actor.NameValue,
		&actor.Role,
		&actor.Notes,
		&actor.CreatedAt,
		&actor.UpdatedAt,
		&actor.Etag,
	)

	if err != nil {
		if err == sql.ErrNoRows {
			return nil, fmt.Errorf("actor with story_id '%d' and actor_id '%s' not found", storyID, actorID)
		}
		return nil, fmt.Errorf("failed to get actor: %w", err)
	}

	return actor, nil
}

// GetByResourceName retrieves an actor by its resource name.
// Resource name format: stories/{story_id}/actors/{actor_id}
func GetByResourceName(db *sql.DB, resourceName string) (*DBActor, error) {
	prefix := "stories/"
	if !strings.HasPrefix(resourceName, prefix) {
		return nil, fmt.Errorf("invalid resource name: must start with '%s', got '%s'", prefix, resourceName)
	}

	// Extract story_id and actor_id
	rest := strings.TrimPrefix(resourceName, prefix)
	parts := strings.SplitN(rest, "/actors/", 2)
	if len(parts) != 2 {
		return nil, errors.New("invalid resource name: must contain 'actors/'")
	}

	storyIDStr, actorID := parts[0], parts[1]

	var storyID int64
	_, err := fmt.Sscanf(storyIDStr, "%d", &storyID)
	if err != nil {
		return nil, fmt.Errorf("invalid story_id in resource name: %w", err)
	}

	if err := validateActorID(actorID); err != nil {
		return nil, fmt.Errorf("invalid actor_id in resource name: %w", err)
	}

	return GetByActorID(db, storyID, actorID)
}


// ListByStoryID retrieves a list of actors for a story.
func ListByStoryID(db *sql.DB, storyID int64, pageSize int, pageToken string) ([]*DBActor, string, error) {
	if pageSize <= 0 || pageSize > 1000 {
		pageSize = 50
	}

	var query string
	var args []interface{}

	query = `
		SELECT id, story_id, actor_id, name_value, role, notes, created_at, updated_at, etag
		FROM actors
		WHERE story_id = $1
	`

	// Parse page token for pagination
	if pageToken != "" {
		query += " AND id > " + pageToken
	}

	query += " ORDER BY name_value ASC"
	query += fmt.Sprintf(" LIMIT %d", pageSize)

	args = append(args, storyID)

	rows, err := db.Query(query, args...)
	if err != nil {
		return nil, "", fmt.Errorf("failed to list actors: %w", err)
	}
	defer rows.Close()

	actors := make([]*DBActor, 0)
	for rows.Next() {
		var id int64
		var storyID int64
		var actorID, nameValue, role, notes, etag string
		var createdAt, updatedAt time.Time

		err := rows.Scan(&id, &storyID, &actorID, &nameValue, &role, &notes, &createdAt, &updatedAt, &etag)
		if err != nil {
			return nil, "", fmt.Errorf("failed to scan actor: %w", err)
		}

		actors = append(actors, &DBActor{
			ID:          id,
			StoryID:     storyID,
			ActorID:     actorID,
			NameValue:   nameValue,
			Role:        role,
			Notes:       notes,
			CreatedAt:   createdAt,
			UpdatedAt:   updatedAt,
			Etag:        etag,
		})
	}

	if err := rows.Err(); err != nil {
		return nil, "", fmt.Errorf("error iterating actors: %w", err)
	}

	var nextPageToken string
	if len(actors) == pageSize {
		// Use the last actor's ID as the next page token
		nextPageToken = fmt.Sprintf("%d", actors[len(actors)-1].ID)
	}

	return actors, nextPageToken, nil
}

// Delete removes an actor from the database.
func Delete(db *sql.DB, id int64) error {
	query := `
		DELETE FROM actors
		WHERE id = $1
	`

	result, err := db.Exec(query, id)
	if err != nil {
		return fmt.Errorf("failed to delete actor: %w", err)
	}

	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("failed to get rows affected: %w", err)
	}

	if rowsAffected == 0 {
		return fmt.Errorf("actor with id %d not found", id)
	}

	return nil
}