Productier/apps/backend/internal/store/postgres.go

package store

import (
	"context"
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"log"
	"os"
	"path/filepath"
	"runtime"
	"time"

	"github.com/google/uuid"
	_ "github.com/jackc/pgx/v5/stdlib"
	"github.com/pressly/goose/v3"

	db "productier/apps/backend/internal/db/generated"
)

type PostgresStore struct {
	db      *sql.DB
	queries *db.Queries
}

func NewPostgresStore(databaseURL string, mode string) (*PostgresStore, error) {
	sqlDB, err := sql.Open("pgx", databaseURL)
	if err != nil {
		return nil, fmt.Errorf("open postgres connection: %w", err)
	}

	if err := sqlDB.Ping(); err != nil {
		return nil, fmt.Errorf("ping postgres: %w", err)
	}

	if err := goose.SetDialect("postgres"); err != nil {
		return nil, fmt.Errorf("set goose dialect: %w", err)
	}

	if err := goose.Up(sqlDB, migrationsDir()); err != nil {
		return nil, fmt.Errorf("run migrations: %w", err)
	}

	store := &PostgresStore{
		db:      sqlDB,
		queries: db.New(sqlDB),
	}

	if err := store.seedIfEmpty(mode); err != nil {
		return nil, fmt.Errorf("seed postgres store: %w", err)
	}

	return store, nil
}

func migrationsDir() string {
	if configured := filepath.Clean(os.Getenv("DB_MIGRATIONS_DIR")); configured != "" && configured != "." {
		if info, err := os.Stat(configured); err == nil && info.IsDir() {
			return configured
		}
	}

	_, file, _, _ := runtime.Caller(0)
	sourcePath := filepath.Join(filepath.Dir(file), "..", "db", "migrations")
	if info, err := os.Stat(sourcePath); err == nil && info.IsDir() {
		return sourcePath
	}

	if executablePath, err := os.Executable(); err == nil {
		executableDir := filepath.Dir(executablePath)
		executableRelative := filepath.Join(executableDir, "migrations")
		if info, statErr := os.Stat(executableRelative); statErr == nil && info.IsDir() {
			return executableRelative
		}
	}

	return sourcePath
}

func (s *PostgresStore) seedIfEmpty(mode string) error {
	ctx := context.Background()
	count, err := s.queries.CountWorkspaces(ctx)
	if err != nil {
		return err
	}
	if count > 0 {
		return nil
	}

	seed := NewSeededState(mode)
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	queries := s.queries.WithTx(tx)

	for _, workspace := range seed.Workspaces {
		err = queries.CreateWorkspace(ctx, db.CreateWorkspaceParams{
			ID:        workspace.ID,
			Slug:      workspace.Slug,
			Name:      workspace.Name,
			Role:      workspace.Role,
			CreatedAt: workspace.CreatedAt,
		})
		if err != nil {
			_ = tx.Rollback()
			return err
		}
	}

	for _, member := range seed.Members {
		err = queries.CreateMember(ctx, db.CreateMemberParams{
			ID:            member.ID,
			WorkspaceSlug: member.WorkspaceSlug,
			Name:          member.Name,
			Email:         member.Email,
			Role:          member.Role,
			Status:        member.Status,
		})
		if err != nil {
			_ = tx.Rollback()
			return err
		}
	}

	for _, invite := range seed.Invites {
		_, err = queries.CreateInvite(ctx, db.CreateInviteParams{
			ID:            invite.ID,
			WorkspaceSlug: invite.WorkspaceSlug,
			Email:         invite.Email,
			Role:          invite.Role,
			Token:         invite.Token,
			CreatedAt:     invite.CreatedAt,
			Status:        invite.Status,
		})
		if err != nil {
			_ = tx.Rollback()
			return err
		}
	}

	for _, activity := range seed.Activities {
		err = queries.CreateActivity(ctx, db.CreateActivityParams{
			ID:            activity.ID,
			WorkspaceSlug: activity.WorkspaceSlug,
			Title:         activity.Title,
			Detail:        activity.Detail,
			CreatedAt:     activity.CreatedAt,
		})
		if err != nil {
			_ = tx.Rollback()
			return err
		}
	}

	for _, group := range seed.BoardGroups {
		_, err = queries.CreateBoardGroup(ctx, db.CreateBoardGroupParams{
			ID:            group.ID,
			WorkspaceSlug: group.WorkspaceSlug,
			Name:          group.Name,
			Color:         group.Color,
			SortOrder:     int32(group.Order),
		})
		if err != nil {
			_ = tx.Rollback()
			return err
		}
	}

	for _, label := range seed.Labels {
		_, err = queries.CreateLabel(ctx, db.CreateLabelParams{
			ID:            label.ID,
			WorkspaceSlug: label.WorkspaceSlug,
			Name:          label.Name,
			Color:         label.Color,
		})
		if err != nil {
			_ = tx.Rollback()
			return err
		}
	}

	for _, task := range seed.Tasks {
		_, err = queries.CreateTask(ctx, db.CreateTaskParams{
			ID:             task.ID,
			WorkspaceSlug:  task.WorkspaceSlug,
			BoardGroupID:   task.BoardGroupID,
			Title:          task.Title,
			Description:    task.Description,
			Status:         task.Status,
			Color:          task.Color,
			DueAt:          nullableTime(task.DueAt),
			ScheduledStart: nullableTime(task.ScheduledStart),
			ScheduledEnd:   nullableTime(task.ScheduledEnd),
			AssigneeID:     nullableString(task.AssigneeID),
			LabelIds:       mustJSON(task.LabelIDs),
			Attachments:    mustJSON(task.Attachments),
			Comments:       mustJSON(task.Comments),
			CreatedAt:      task.CreatedAt,
			UpdatedAt:      task.UpdatedAt,
		})
		if err != nil {
			_ = tx.Rollback()
			return err
		}
	}

	for _, event := range seed.Events {
		_, err = queries.CreateCalendarEvent(ctx, db.CreateCalendarEventParams{
			ID:            event.ID,
			WorkspaceSlug: event.WorkspaceSlug,
			Title:         event.Title,
			Description:   event.Description,
			StartsAt:      event.StartsAt,
			EndsAt:        event.EndsAt,
			Color:         event.Color,
			LinkedTaskID:  nullableString(event.LinkedTaskID),
			Attachments:   mustJSON(event.Attachments),
		})
		if err != nil {
			_ = tx.Rollback()
			return err
		}
	}

	for _, note := range seed.Notes {
		_, err = queries.CreateNote(ctx, db.CreateNoteParams{
			ID:            note.ID,
			WorkspaceSlug: note.WorkspaceSlug,
			Title:         note.Title,
			Content:       note.Content,
			UpdatedAt:     note.UpdatedAt,
		})
		if err != nil {
			_ = tx.Rollback()
			return err
		}
	}

	for _, session := range seed.FocusSessions {
		_, err = queries.CreateFocusSession(ctx, db.CreateFocusSessionParams{
			ID:                 session.ID,
			WorkspaceSlug:      session.WorkspaceSlug,
			TaskID:             nullableString(session.TaskID),
			Mode:               session.Mode,
			StartedAt:          session.StartedAt,
			CompletedAt:        nullableTime(session.CompletedAt),
			PausedAt:           nullableTime(session.PausedAt),
			PausedTotalSeconds: int32(session.PausedTotalSeconds),
			DurationSeconds:    int32(session.DurationSeconds),
		})
		if err != nil {
			_ = tx.Rollback()
			return err
		}
	}

	return tx.Commit()
}

func (s *PostgresStore) ListWorkspaces() []Workspace {
	rows, err := s.queries.ListWorkspaces(context.Background())
	if err != nil {
		panic(err)
	}

	items := make([]Workspace, 0, len(rows))
	for _, row := range rows {
		items = append(items, Workspace{
			ID:        row.ID,
			Slug:      row.Slug,
			Name:      row.Name,
			Role:      row.Role,
			CreatedAt: row.CreatedAt,
		})
	}
	return items
}

func (s *PostgresStore) ListMembers(workspaceSlug string) []Member {
	rows, err := s.queries.ListMembers(context.Background(), workspaceSlug)
	if err != nil {
		panic(err)
	}

	items := make([]Member, 0, len(rows))
	for _, row := range rows {
		items = append(items, Member{
			ID:            row.ID,
			WorkspaceSlug: row.WorkspaceSlug,
			Name:          row.Name,
			Email:         row.Email,
			Role:          row.Role,
			Status:        row.Status,
		})
	}
	return items
}

func (s *PostgresStore) GetMemberByID(memberID string) (Member, error) {
	row := s.db.QueryRowContext(context.Background(), `
		SELECT id, workspace_slug, name, email, role, status
		FROM members
		WHERE id = $1
	`, memberID)
	var member Member
	if err := row.Scan(&member.ID, &member.WorkspaceSlug, &member.Name, &member.Email, &member.Role, &member.Status); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return Member{}, errors.New("member not found")
		}
		return Member{}, err
	}
	return member, nil
}

func (s *PostgresStore) UpdateMember(memberID string, input UpdateMemberInput) (Member, error) {
	ctx := context.Background()
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return Member{}, err
	}

	currentRow := tx.QueryRowContext(ctx, `
		SELECT id, workspace_slug, name, email, role, status
		FROM members
		WHERE id = $1
		FOR UPDATE
	`, memberID)
	var current Member
	if err := currentRow.Scan(&current.ID, &current.WorkspaceSlug, &current.Name, &current.Email, &current.Role, &current.Status); err != nil {
		_ = tx.Rollback()
		if errors.Is(err, sql.ErrNoRows) {
			return Member{}, errors.New("member not found")
		}
		return Member{}, err
	}

	nextRole := current.Role
	if input.Role != nil {
		if !isValidWorkspaceRole(*input.Role) {
			_ = tx.Rollback()
			return Member{}, errors.New("invalid member role")
		}
		nextRole = *input.Role
	}

	nextStatus := current.Status
	if input.Status != nil {
		if !isValidMemberStatus(*input.Status) {
			_ = tx.Rollback()
			return Member{}, errors.New("invalid member status")
		}
		nextStatus = *input.Status
	}

	if current.Role == "owner" && current.Status == "active" && (nextRole != "owner" || nextStatus != "active") {
		var activeOwnerCount int64
		if err := tx.QueryRowContext(ctx, `
			SELECT COUNT(*)
			FROM members
			WHERE workspace_slug = $1 AND role = 'owner' AND status = 'active'
		`, current.WorkspaceSlug).Scan(&activeOwnerCount); err != nil {
			_ = tx.Rollback()
			return Member{}, err
		}
		if activeOwnerCount <= 1 {
			_ = tx.Rollback()
			return Member{}, errors.New("workspace must have at least one active owner")
		}
	}

	updatedRow := tx.QueryRowContext(ctx, `
		UPDATE members
		SET role = $2,
		    status = $3
		WHERE id = $1
		RETURNING id, workspace_slug, name, email, role, status
	`, memberID, nextRole, nextStatus)
	var updated Member
	if err := updatedRow.Scan(&updated.ID, &updated.WorkspaceSlug, &updated.Name, &updated.Email, &updated.Role, &updated.Status); err != nil {
		_ = tx.Rollback()
		return Member{}, err
	}

	queries := s.queries.WithTx(tx)
	if err := appendActivity(ctx, queries, updated.WorkspaceSlug, "Member updated", fmt.Sprintf("%s membership settings changed.", updated.Email)); err != nil {
		_ = tx.Rollback()
		return Member{}, err
	}

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

func (s *PostgresStore) ListInvites(workspaceSlug string) []Invite {
	rows, err := s.queries.ListInvites(context.Background(), workspaceSlug)
	if err != nil {
		panic(err)
	}

	items := make([]Invite, 0, len(rows))
	for _, row := range rows {
		items = append(items, inviteFromDB(row))
	}
	return items
}

func (s *PostgresStore) GetInviteByID(inviteID string) (Invite, error) {
	row := s.db.QueryRowContext(context.Background(), `
		SELECT id, workspace_slug, email, role, token, created_at, status
		FROM invites
		WHERE id = $1
	`, inviteID)
	var invite Invite
	if err := row.Scan(&invite.ID, &invite.WorkspaceSlug, &invite.Email, &invite.Role, &invite.Token, &invite.CreatedAt, &invite.Status); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return Invite{}, errors.New("invite not found")
		}
		return Invite{}, err
	}
	return invite, nil
}

func (s *PostgresStore) GetInviteByToken(token string) (Invite, error) {
	row, err := s.queries.GetInviteByToken(context.Background(), token)
	if err != nil {
		return Invite{}, err
	}
	return inviteFromDB(row), nil
}

func (s *PostgresStore) CreateInvite(input CreateInviteInput) Invite {
	ctx := context.Background()
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		panic(err)
	}

	queries := s.queries.WithTx(tx)
	invite, err := queries.CreateInvite(ctx, db.CreateInviteParams{
		ID:            uuid.NewString(),
		WorkspaceSlug: input.WorkspaceSlug,
		Email:         input.Email,
		Role:          defaultString(input.Role, "member"),
		Token:         "join-" + uuid.NewString(),
		CreatedAt:     time.Now().UTC(),
		Status:        "pending",
	})
	if err != nil {
		_ = tx.Rollback()
		panic(err)
	}

	if err := appendActivity(ctx, queries, input.WorkspaceSlug, "Invite created", fmt.Sprintf("Shared workspace access with %s.", input.Email)); err != nil {
		_ = tx.Rollback()
		panic(err)
	}

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

	return inviteFromDB(invite)
}

func (s *PostgresStore) RevokeInvite(inviteID string) error {
	ctx := context.Background()
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}

	row := tx.QueryRowContext(ctx, `
		SELECT id, workspace_slug, email, role, token, created_at, status
		FROM invites
		WHERE id = $1
		FOR UPDATE
	`, inviteID)
	var invite Invite
	if err := row.Scan(&invite.ID, &invite.WorkspaceSlug, &invite.Email, &invite.Role, &invite.Token, &invite.CreatedAt, &invite.Status); err != nil {
		_ = tx.Rollback()
		if errors.Is(err, sql.ErrNoRows) {
			return errors.New("invite not found")
		}
		return err
	}
	if invite.Status != "pending" {
		_ = tx.Rollback()
		return errors.New("only pending invites can be revoked")
	}

	if _, err := tx.ExecContext(ctx, `DELETE FROM invites WHERE id = $1`, inviteID); err != nil {
		_ = tx.Rollback()
		return err
	}

	queries := s.queries.WithTx(tx)
	if err := appendActivity(ctx, queries, invite.WorkspaceSlug, "Invite revoked", fmt.Sprintf("Invite for %s was revoked.", invite.Email)); err != nil {
		_ = tx.Rollback()
		return err
	}

	return tx.Commit()
}

func (s *PostgresStore) AcceptInvite(token string, input AcceptInviteInput) (Invite, error) {
	ctx := context.Background()
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return Invite{}, err
	}

	queries := s.queries.WithTx(tx)
	invite, err := queries.AcceptInvite(ctx, token)
	if err != nil {
		_ = tx.Rollback()
		return Invite{}, err
	}

	_, err = queries.GetMemberByWorkspaceAndEmail(ctx, db.GetMemberByWorkspaceAndEmailParams{
		WorkspaceSlug: invite.WorkspaceSlug,
		Email:         input.Email,
	})
	if err != nil {
		if !errors.Is(err, sql.ErrNoRows) {
			_ = tx.Rollback()
			return Invite{}, err
		}
		err = queries.CreateMember(ctx, db.CreateMemberParams{
			ID:            uuid.NewString(),
			WorkspaceSlug: invite.WorkspaceSlug,
			Name:          input.Name,
			Email:         input.Email,
			Role:          invite.Role,
			Status:        "active",
		})
		if err != nil {
			_ = tx.Rollback()
			return Invite{}, err
		}
	}

	if err := appendActivity(ctx, queries, invite.WorkspaceSlug, "Invite accepted", fmt.Sprintf("%s joined the workspace.", input.Email)); err != nil {
		_ = tx.Rollback()
		return Invite{}, err
	}

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

	return inviteFromDB(invite), nil
}

func (s *PostgresStore) ListActivities(workspaceSlug string) []ActivityEntry {
	rows, err := s.queries.ListActivities(context.Background(), workspaceSlug)
	if err != nil {
		panic(err)
	}

	items := make([]ActivityEntry, 0, len(rows))
	for _, row := range rows {
		items = append(items, ActivityEntry{
			ID:            row.ID,
			WorkspaceSlug: row.WorkspaceSlug,
			Title:         row.Title,
			Detail:        row.Detail,
			CreatedAt:     row.CreatedAt,
		})
	}
	return items
}

func (s *PostgresStore) ListBoardGroups(workspaceSlug string) []BoardGroup {
	rows, err := s.queries.ListBoardGroups(context.Background(), workspaceSlug)
	if err != nil {
		panic(err)
	}

	items := make([]BoardGroup, 0, len(rows))
	for _, row := range rows {
		items = append(items, BoardGroup{
			ID:            row.ID,
			WorkspaceSlug: row.WorkspaceSlug,
			Name:          row.Name,
			Color:         row.Color,
			Order:         int(row.SortOrder),
		})
	}
	return items
}

func (s *PostgresStore) GetBoardGroupByID(groupID string) (BoardGroup, error) {
	row, err := s.queries.GetBoardGroupByID(context.Background(), groupID)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return BoardGroup{}, errors.New("board group not found")
		}
		panic(err)
	}
	return BoardGroup{
		ID:            row.ID,
		WorkspaceSlug: row.WorkspaceSlug,
		Name:          row.Name,
		Color:         row.Color,
		Order:         int(row.SortOrder),
	}, nil
}

func (s *PostgresStore) CreateBoardGroup(input CreateBoardGroupInput) BoardGroup {
	ctx := context.Background()
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		panic(err)
	}

	queries := s.queries.WithTx(tx)
	count, err := queries.CountBoardGroupsByWorkspace(ctx, input.WorkspaceSlug)
	if err != nil {
		_ = tx.Rollback()
		panic(err)
	}

	row, err := queries.CreateBoardGroup(ctx, db.CreateBoardGroupParams{
		ID:            uuid.NewString(),
		WorkspaceSlug: input.WorkspaceSlug,
		Name:          input.Name,
		Color:         defaultString(input.Color, "slate"),
		SortOrder:     int32(count),
	})
	if err != nil {
		_ = tx.Rollback()
		panic(err)
	}

	if err := appendActivity(ctx, queries, input.WorkspaceSlug, "Board group added", fmt.Sprintf("%s is ready for new tasks.", input.Name)); err != nil {
		_ = tx.Rollback()
		panic(err)
	}

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

	return BoardGroup{
		ID:            row.ID,
		WorkspaceSlug: row.WorkspaceSlug,
		Name:          row.Name,
		Color:         row.Color,
		Order:         int(row.SortOrder),
	}
}

func (s *PostgresStore) UpdateBoardGroup(groupID string, input UpdateBoardGroupInput) (BoardGroup, error) {
	ctx := context.Background()
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		panic(err)
	}

	queries := s.queries.WithTx(tx)
	current, err := queries.GetBoardGroupByID(ctx, groupID)
	if err != nil {
		_ = tx.Rollback()
		if errors.Is(err, sql.ErrNoRows) {
			return BoardGroup{}, errors.New("board group not found")
		}
		panic(err)
	}

	nextName := current.Name
	if input.Name != nil {
		nextName = *input.Name
	}

	nextColor := current.Color
	if input.Color != nil {
		nextColor = *input.Color
	}

	nextOrder := int(current.SortOrder)
	if input.Order != nil {
		nextOrder = *input.Order
		if nextOrder < 0 {
			nextOrder = 0
		}
	}

	row, err := queries.UpdateBoardGroup(ctx, db.UpdateBoardGroupParams{
		ID:        current.ID,
		Name:      nextName,
		Color:     nextColor,
		SortOrder: int32(nextOrder),
	})
	if err != nil {
		_ = tx.Rollback()
		panic(err)
	}

	if input.Order != nil {
		rows, err := queries.ListBoardGroups(ctx, row.WorkspaceSlug)
		if err != nil {
			_ = tx.Rollback()
			panic(err)
		}
		for index, group := range rows {
			if int(group.SortOrder) == index {
				continue
			}
			if _, err := queries.UpdateBoardGroup(ctx, db.UpdateBoardGroupParams{
				ID:        group.ID,
				Name:      group.Name,
				Color:     group.Color,
				SortOrder: int32(index),
			}); err != nil {
				_ = tx.Rollback()
				panic(err)
			}
			if group.ID == row.ID {
				row.SortOrder = int32(index)
			}
		}
	}

	if err := appendActivity(ctx, queries, row.WorkspaceSlug, "Board group updated", fmt.Sprintf("%s settings changed.", row.Name)); err != nil {
		_ = tx.Rollback()
		panic(err)
	}

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

	return BoardGroup{
		ID:            row.ID,
		WorkspaceSlug: row.WorkspaceSlug,
		Name:          row.Name,
		Color:         row.Color,
		Order:         int(row.SortOrder),
	}, nil
}

func (s *PostgresStore) ListLabels(workspaceSlug string) []Label {
	rows, err := s.queries.ListLabels(context.Background(), workspaceSlug)
	if err != nil {
		panic(err)
	}

	items := make([]Label, 0, len(rows))
	for _, row := range rows {
		items = append(items, Label{
			ID:            row.ID,
			WorkspaceSlug: row.WorkspaceSlug,
			Name:          row.Name,
			Color:         row.Color,
		})
	}
	return items
}

func (s *PostgresStore) CreateLabel(input CreateLabelInput) Label {
	ctx := context.Background()
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		panic(err)
	}

	queries := s.queries.WithTx(tx)
	row, err := queries.CreateLabel(ctx, db.CreateLabelParams{
		ID:            uuid.NewString(),
		WorkspaceSlug: input.WorkspaceSlug,
		Name:          input.Name,
		Color:         input.Color,
	})
	if err != nil {
		_ = tx.Rollback()
		panic(err)
	}

	if err := appendActivity(ctx, queries, input.WorkspaceSlug, "Label added", fmt.Sprintf("%s is now available across the workspace.", input.Name)); err != nil {
		_ = tx.Rollback()
		panic(err)
	}

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

	return Label{
		ID:            row.ID,
		WorkspaceSlug: row.WorkspaceSlug,
		Name:          row.Name,
		Color:         row.Color,
	}
}

func (s *PostgresStore) ListTasks(workspaceSlug string) []Task {
	rows, err := s.queries.ListTasks(context.Background(), workspaceSlug)
	if err != nil {
		panic(err)
	}

	items := make([]Task, 0, len(rows))
	for _, row := range rows {
		items = append(items, taskFromDB(row))
	}
	return items
}

func (s *PostgresStore) GetTaskByID(taskID string) (Task, error) {
	row, err := s.queries.GetTaskByID(context.Background(), taskID)
	if err != nil {
		return Task{}, err
	}
	return taskFromDB(row), nil
}

func (s *PostgresStore) CreateTask(input CreateTaskInput) Task {
	ctx := context.Background()
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		panic(err)
	}

	queries := s.queries.WithTx(tx)
	now := time.Now().UTC()
	row, err := queries.CreateTask(ctx, db.CreateTaskParams{
		ID:             uuid.NewString(),
		WorkspaceSlug:  input.WorkspaceSlug,
		BoardGroupID:   input.BoardGroupID,
		Title:          input.Title,
		Description:    input.Description,
		Status:         "todo",
		Color:          defaultString(input.Color, "slate"),
		DueAt:          nullableTime(input.DueAt),
		ScheduledStart: sql.NullTime{},
		ScheduledEnd:   sql.NullTime{},
		AssigneeID:     sql.NullString{},
		LabelIds:       mustJSON([]string{}),
		Attachments:    mustJSON([]Attachment{}),
		Comments:       mustJSON([]TaskComment{}),
		CreatedAt:      now,
		UpdatedAt:      now,
	})
	if err != nil {
		_ = tx.Rollback()
		panic(err)
	}

	if err := appendActivity(ctx, queries, input.WorkspaceSlug, "Task created", input.Title); err != nil {
		_ = tx.Rollback()
		panic(err)
	}

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

	return taskFromDB(row)
}

func (s *PostgresStore) UpdateTask(taskID string, input UpdateTaskInput) (Task, error) {
	ctx := context.Background()
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return Task{}, err
	}

	queries := s.queries.WithTx(tx)
	current, err := queries.GetTaskByID(ctx, taskID)
	if err != nil {
		_ = tx.Rollback()
		return Task{}, err
	}

	labelIDs, err := decodeJSONSlice[string](current.LabelIds)
	if err != nil {
		_ = tx.Rollback()
		return Task{}, err
	}
	attachments, err := decodeJSONSlice[Attachment](current.Attachments)
	if err != nil {
		_ = tx.Rollback()
		return Task{}, err
	}
	comments, err := decodeJSONSlice[TaskComment](current.Comments)
	if err != nil {
		_ = tx.Rollback()
		return Task{}, err
	}

	title := current.Title
	if input.Title != nil {
		title = *input.Title
	}
	description := current.Description
	if input.Description != nil {
		description = *input.Description
	}
	status := current.Status
	if input.Status != nil {
		status = *input.Status
	}
	boardGroupID := current.BoardGroupID
	if input.BoardGroupID != nil {
		boardGroupID = *input.BoardGroupID
	}
	color := current.Color
	if input.Color != nil {
		color = *input.Color
	}
	dueAt := current.DueAt
	if input.DueAt != nil {
		dueAt = nullableTime(input.DueAt)
	}
	scheduledStart := current.ScheduledStart
	if input.ScheduledStart != nil {
		scheduledStart = nullableTime(input.ScheduledStart)
	}
	scheduledEnd := current.ScheduledEnd
	if input.ScheduledEnd != nil {
		scheduledEnd = nullableTime(input.ScheduledEnd)
	}
	assigneeID := current.AssigneeID
	if input.AssigneeID != nil {
		assigneeID = nullableString(input.AssigneeID)
	}
	if input.LabelIDs != nil {
		labelIDs = input.LabelIDs
	}
	if input.Attachments != nil {
		attachments = input.Attachments
	}
	if input.Comments != nil {
		comments = input.Comments
	}

	row, err := queries.UpdateTask(ctx, db.UpdateTaskParams{
		ID:             taskID,
		Title:          title,
		Description:    description,
		Status:         status,
		BoardGroupID:   boardGroupID,
		Color:          color,
		DueAt:          dueAt,
		ScheduledStart: scheduledStart,
		ScheduledEnd:   scheduledEnd,
		AssigneeID:     assigneeID,
		LabelIds:       mustJSON(labelIDs),
		Attachments:    mustJSON(attachments),
		Comments:       mustJSON(comments),
		UpdatedAt:      time.Now().UTC(),
	})
	if err != nil {
		_ = tx.Rollback()
		return Task{}, err
	}

	if err := appendActivity(ctx, queries, row.WorkspaceSlug, "Task updated", row.Title); err != nil {
		_ = tx.Rollback()
		return Task{}, err
	}

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

	return taskFromDB(row), nil
}

func (s *PostgresStore) ListEvents(workspaceSlug string) []CalendarEvent {
	rows, err := s.queries.ListCalendarEvents(context.Background(), workspaceSlug)
	if err != nil {
		panic(err)
	}

	items := make([]CalendarEvent, 0, len(rows))
	for _, row := range rows {
		items = append(items, eventFromDB(row))
	}
	return items
}

func (s *PostgresStore) GetEventByID(eventID string) (CalendarEvent, error) {
	row, err := s.queries.GetCalendarEventByID(context.Background(), eventID)
	if err != nil {
		return CalendarEvent{}, err
	}
	return eventFromDB(row), nil
}

func (s *PostgresStore) CreateEvent(input CreateEventInput) CalendarEvent {
	ctx := context.Background()
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		panic(err)
	}

	startsAt, err := time.Parse(time.RFC3339, input.StartsAt)
	if err != nil {
		_ = tx.Rollback()
		panic(err)
	}
	endsAt, err := time.Parse(time.RFC3339, input.EndsAt)
	if err != nil {
		_ = tx.Rollback()
		panic(err)
	}

	queries := s.queries.WithTx(tx)
	row, err := queries.CreateCalendarEvent(ctx, db.CreateCalendarEventParams{
		ID:            uuid.NewString(),
		WorkspaceSlug: input.WorkspaceSlug,
		Title:         input.Title,
		Description:   input.Description,
		StartsAt:      startsAt,
		EndsAt:        endsAt,
		Color:         defaultString(input.Color, "blue"),
		LinkedTaskID:  nullableString(input.LinkedTaskID),
		Attachments:   mustJSON(input.Attachments),
	})
	if err != nil {
		_ = tx.Rollback()
		panic(err)
	}

	if err := appendActivity(ctx, queries, input.WorkspaceSlug, "Event created", input.Title); err != nil {
		_ = tx.Rollback()
		panic(err)
	}

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

	return eventFromDB(row)
}

func (s *PostgresStore) UpdateEvent(eventID string, input UpdateEventInput) (CalendarEvent, error) {
	ctx := context.Background()
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return CalendarEvent{}, err
	}

	queries := s.queries.WithTx(tx)
	current, err := queries.GetCalendarEventByID(ctx, eventID)
	if err != nil {
		_ = tx.Rollback()
		return CalendarEvent{}, err
	}

	attachments, err := decodeJSONSlice[Attachment](current.Attachments)
	if err != nil {
		_ = tx.Rollback()
		return CalendarEvent{}, err
	}

	title := current.Title
	if input.Title != nil {
		title = *input.Title
	}
	description := current.Description
	if input.Description != nil {
		description = *input.Description
	}
	startsAt := current.StartsAt
	if input.StartsAt != nil {
		startsAt = *input.StartsAt
	}
	endsAt := current.EndsAt
	if input.EndsAt != nil {
		endsAt = *input.EndsAt
	}
	color := current.Color
	if input.Color != nil {
		color = *input.Color
	}
	linkedTaskID := current.LinkedTaskID
	if input.LinkedTaskID != nil {
		linkedTaskID = nullableString(input.LinkedTaskID)
	}
	if input.Attachments != nil {
		attachments = input.Attachments
	}

	row, err := queries.UpdateCalendarEvent(ctx, db.UpdateCalendarEventParams{
		ID:           eventID,
		Title:        title,
		Description:  description,
		StartsAt:     startsAt,
		EndsAt:       endsAt,
		Color:        color,
		LinkedTaskID: linkedTaskID,
		Attachments:  mustJSON(attachments),
	})
	if err != nil {
		_ = tx.Rollback()
		return CalendarEvent{}, err
	}

	if err := appendActivity(ctx, queries, row.WorkspaceSlug, "Event updated", row.Title); err != nil {
		_ = tx.Rollback()
		return CalendarEvent{}, err
	}

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

	return eventFromDB(row), nil
}

func (s *PostgresStore) ListNotes(workspaceSlug string) []Note {
	rows, err := s.queries.ListNotes(context.Background(), workspaceSlug)
	if err != nil {
		panic(err)
	}

	items := make([]Note, 0, len(rows))
	for _, row := range rows {
		items = append(items, Note{
			ID:            row.ID,
			WorkspaceSlug: row.WorkspaceSlug,
			Title:         row.Title,
			Content:       row.Content,
			UpdatedAt:     row.UpdatedAt,
		})
	}
	return items
}

func (s *PostgresStore) GetNoteByID(noteID string) (Note, error) {
	row, err := s.queries.GetNoteByID(context.Background(), noteID)
	if err != nil {
		return Note{}, err
	}
	return Note{
		ID:            row.ID,
		WorkspaceSlug: row.WorkspaceSlug,
		Title:         row.Title,
		Content:       row.Content,
		UpdatedAt:     row.UpdatedAt,
	}, nil
}

func (s *PostgresStore) CreateNote(input CreateNoteInput) Note {
	ctx := context.Background()
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		panic(err)
	}

	queries := s.queries.WithTx(tx)
	row, err := queries.CreateNote(ctx, db.CreateNoteParams{
		ID:            uuid.NewString(),
		WorkspaceSlug: input.WorkspaceSlug,
		Title:         input.Title,
		Content:       input.Content,
		UpdatedAt:     time.Now().UTC(),
	})
	if err != nil {
		_ = tx.Rollback()
		panic(err)
	}

	if err := appendActivity(ctx, queries, input.WorkspaceSlug, "Note created", input.Title); err != nil {
		_ = tx.Rollback()
		panic(err)
	}

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

	return Note{
		ID:            row.ID,
		WorkspaceSlug: row.WorkspaceSlug,
		Title:         row.Title,
		Content:       row.Content,
		UpdatedAt:     row.UpdatedAt,
	}
}

func (s *PostgresStore) UpdateNote(noteID string, input UpdateNoteInput) (Note, error) {
	ctx := context.Background()
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return Note{}, err
	}

	queries := s.queries.WithTx(tx)
	current, err := queries.GetNoteByID(ctx, noteID)
	if err != nil {
		_ = tx.Rollback()
		return Note{}, err
	}

	title := current.Title
	if input.Title != nil {
		title = *input.Title
	}
	content := current.Content
	if input.Content != nil {
		content = *input.Content
	}

	row, err := queries.UpdateNote(ctx, db.UpdateNoteParams{
		ID:        noteID,
		Title:     title,
		Content:   content,
		UpdatedAt: time.Now().UTC(),
	})
	if err != nil {
		_ = tx.Rollback()
		return Note{}, err
	}

	if err := appendActivity(ctx, queries, row.WorkspaceSlug, "Note updated", row.Title); err != nil {
		_ = tx.Rollback()
		return Note{}, err
	}

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

	return Note{
		ID:            row.ID,
		WorkspaceSlug: row.WorkspaceSlug,
		Title:         row.Title,
		Content:       row.Content,
		UpdatedAt:     row.UpdatedAt,
	}, nil
}

func (s *PostgresStore) ListFocusSessions(workspaceSlug string) []FocusSession {
	rows, err := s.queries.ListFocusSessions(context.Background(), workspaceSlug)
	if err != nil {
		panic(err)
	}

	items := make([]FocusSession, 0, len(rows))
	for _, row := range rows {
		items = append(items, focusSessionFromDB(row))
	}
	return items
}

func (s *PostgresStore) GetFocusSessionByID(sessionID string) (FocusSession, error) {
	row, err := s.queries.GetFocusSessionByID(context.Background(), sessionID)
	if err != nil {
		return FocusSession{}, err
	}
	return focusSessionFromDB(row), nil
}

func (s *PostgresStore) CreateFocusSession(input CreateFocusSessionInput) FocusSession {
	ctx := context.Background()
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		panic(err)
	}

	queries := s.queries.WithTx(tx)
	row, err := queries.CreateFocusSession(ctx, db.CreateFocusSessionParams{
		ID:                 uuid.NewString(),
		WorkspaceSlug:      input.WorkspaceSlug,
		TaskID:             nullableString(input.TaskID),
		Mode:               input.Mode,
		StartedAt:          time.Now().UTC(),
		CompletedAt:        sql.NullTime{},
		PausedAt:           sql.NullTime{},
		PausedTotalSeconds: 0,
		DurationSeconds:    int32(input.DurationSeconds),
	})
	if err != nil {
		_ = tx.Rollback()
		panic(err)
	}

	if err := appendActivity(ctx, queries, input.WorkspaceSlug, "Focus session started", input.Mode); err != nil {
		_ = tx.Rollback()
		panic(err)
	}

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

	return focusSessionFromDB(row)
}

func (s *PostgresStore) UpdateFocusSession(sessionID string, input UpdateFocusSessionInput) (FocusSession, error) {
	ctx := context.Background()
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return FocusSession{}, err
	}

	queries := s.queries.WithTx(tx)
	current, err := queries.GetFocusSessionByID(ctx, sessionID)
	if err != nil {
		_ = tx.Rollback()
		return FocusSession{}, err
	}

	completedAt := current.CompletedAt
	if input.CompletedAt != nil {
		completedAt = nullableTime(input.CompletedAt)
	}
	pausedAt := current.PausedAt
	if input.PausedAt != nil {
		pausedAt = nullableTime(input.PausedAt)
	}
	pausedTotalSeconds := current.PausedTotalSeconds
	if input.PausedTotalSeconds != nil {
		pausedTotalSeconds = int32(*input.PausedTotalSeconds)
	}

	row, err := queries.UpdateFocusSession(ctx, db.UpdateFocusSessionParams{
		ID:                 sessionID,
		CompletedAt:        completedAt,
		PausedAt:           pausedAt,
		PausedTotalSeconds: pausedTotalSeconds,
	})
	if err != nil {
		_ = tx.Rollback()
		return FocusSession{}, err
	}

	if err := appendActivity(ctx, queries, row.WorkspaceSlug, "Focus session updated", row.Mode); err != nil {
		_ = tx.Rollback()
		return FocusSession{}, err
	}

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

	return focusSessionFromDB(row), nil
}

func appendActivity(ctx context.Context, queries *db.Queries, workspaceSlug string, title string, detail string) error {
	err := queries.CreateActivity(ctx, db.CreateActivityParams{
		ID:            uuid.NewString(),
		WorkspaceSlug: workspaceSlug,
		Title:         title,
		Detail:        detail,
		CreatedAt:     time.Now().UTC(),
	})
	if err != nil {
		return err
	}
	return queries.TrimActivities(ctx, workspaceSlug)
}

func taskFromDB(row db.Task) Task {
	labelIDs, err := decodeJSONSlice[string](row.LabelIds)
	if err != nil {
		log.Printf("decode task label ids: %v", err)
	}
	attachments, err := decodeJSONSlice[Attachment](row.Attachments)
	if err != nil {
		log.Printf("decode task attachments: %v", err)
	}
	comments, err := decodeJSONSlice[TaskComment](row.Comments)
	if err != nil {
		log.Printf("decode task comments: %v", err)
	}

	return Task{
		ID:             row.ID,
		WorkspaceSlug:  row.WorkspaceSlug,
		BoardGroupID:   row.BoardGroupID,
		Title:          row.Title,
		Description:    row.Description,
		Status:         row.Status,
		Color:          row.Color,
		DueAt:          timePtr(row.DueAt),
		ScheduledStart: timePtr(row.ScheduledStart),
		ScheduledEnd:   timePtr(row.ScheduledEnd),
		AssigneeID:     stringPtr(row.AssigneeID),
		LabelIDs:       labelIDs,
		Attachments:    attachments,
		Comments:       comments,
		CreatedAt:      row.CreatedAt,
		UpdatedAt:      row.UpdatedAt,
	}
}

func eventFromDB(row db.CalendarEvent) CalendarEvent {
	attachments, err := decodeJSONSlice[Attachment](row.Attachments)
	if err != nil {
		log.Printf("decode event attachments: %v", err)
	}

	return CalendarEvent{
		ID:            row.ID,
		WorkspaceSlug: row.WorkspaceSlug,
		Title:         row.Title,
		Description:   row.Description,
		StartsAt:      row.StartsAt,
		EndsAt:        row.EndsAt,
		Color:         row.Color,
		LinkedTaskID:  stringPtr(row.LinkedTaskID),
		Attachments:   attachments,
	}
}

func inviteFromDB(row db.Invite) Invite {
	return Invite{
		ID:            row.ID,
		WorkspaceSlug: row.WorkspaceSlug,
		Email:         row.Email,
		Role:          row.Role,
		Token:         row.Token,
		CreatedAt:     row.CreatedAt,
		Status:        row.Status,
	}
}

func focusSessionFromDB(row db.FocusSession) FocusSession {
	return FocusSession{
		ID:                 row.ID,
		WorkspaceSlug:      row.WorkspaceSlug,
		TaskID:             stringPtr(row.TaskID),
		Mode:               row.Mode,
		StartedAt:          row.StartedAt,
		CompletedAt:        timePtr(row.CompletedAt),
		PausedAt:           timePtr(row.PausedAt),
		PausedTotalSeconds: int(row.PausedTotalSeconds),
		DurationSeconds:    int(row.DurationSeconds),
	}
}

func decodeJSONSlice[T any](raw []byte) ([]T, error) {
	if len(raw) == 0 {
		return []T{}, nil
	}
	var items []T
	if err := json.Unmarshal(raw, &items); err != nil {
		return nil, err
	}
	return items, nil
}

func mustJSON(value any) []byte {
	payload, err := json.Marshal(value)
	if err != nil {
		panic(err)
	}
	return payload
}

func nullableString(value *string) sql.NullString {
	if value == nil {
		return sql.NullString{}
	}
	return sql.NullString{String: *value, Valid: true}
}

func stringPtr(value sql.NullString) *string {
	if !value.Valid {
		return nil
	}
	text := value.String
	return &text
}

func nullableTime(value *time.Time) sql.NullTime {
	if value == nil {
		return sql.NullTime{}
	}
	return sql.NullTime{Time: *value, Valid: true}
}

func timePtr(value sql.NullTime) *time.Time {
	if !value.Valid {
		return nil
	}
	timestamp := value.Time
	return &timestamp
}