plumber/internal/store/migrate_posts_test.go

package store

import (
	"context"
	"database/sql"
	"fmt"
	"os"
	"strings"
	"testing"

	"github.com/google/uuid"

	"plumber/internal/store/sqlc"
)

func TestPostMigrationsAndQueries(t *testing.T) {
	rawURL := strings.TrimSpace(os.Getenv("TEST_DATABASE_URL"))
	if rawURL == "" {
		t.Skip("TEST_DATABASE_URL is not set")
	}
	dsn, err := postgresDSN(rawURL)
	if err != nil {
		t.Fatal(err)
	}
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		t.Fatal(err)
	}
	defer db.Close()

	ctx := context.Background()
	conn, err := db.Conn(ctx)
	if err != nil {
		t.Fatal(err)
	}
	defer conn.Close()

	schemaName := "test_posts_" + strings.ReplaceAll(uuid.NewString(), "-", "")
	if _, err := conn.ExecContext(ctx, "CREATE SCHEMA "+schemaName); err != nil {
		t.Fatal(err)
	}
	defer func() {
		_, _ = conn.ExecContext(context.Background(), "SET search_path TO public")
		_, _ = conn.ExecContext(context.Background(), "DROP SCHEMA "+schemaName+" CASCADE")
	}()
	if _, err := conn.ExecContext(ctx, "SET search_path TO "+schemaName); err != nil {
		t.Fatal(err)
	}

	baseSchema := `
CREATE TABLE users (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  role TEXT NOT NULL
);`
	if err := applySchema(ctx, conn, baseSchema); err != nil {
		t.Fatal(err)
	}

	if err := migratePosts(ctx, conn); err != nil {
		t.Fatal(err)
	}
	if err := migratePosts(ctx, conn); err != nil {
		t.Fatalf("migration is not idempotent: %v", err)
	}
	if err := migratePostVoteIndex(ctx, conn); err != nil {
		t.Fatal(err)
	}
	if err := migratePostVoteIndex(ctx, conn); err != nil {
		t.Fatalf("post vote index migration is not idempotent: %v", err)
	}
	if err := migratePostAuthorIndex(ctx, conn); err != nil {
		t.Fatal(err)
	}
	if err := migratePostAuthorIndex(ctx, conn); err != nil {
		t.Fatalf("post author index migration is not idempotent: %v", err)
	}
	if _, err := conn.ExecContext(ctx, `
INSERT INTO users (id, name, role)
VALUES ('homeowner', 'Home Owner', 'user'), ('plumber', 'The Plumber', 'admin');
INSERT INTO posts (
  id, parent_id, author_id, title, body, city, post_date, post_state, created_at, updated_at
) VALUES
  (
    'root-1', NULL, 'homeowner', 'Leaky sink', 'It drips.', 'Oakland',
    '2026-08-26', 'visible', '2026-08-26T08:00:00Z', '2026-08-26T08:00:00Z'
  ),
  (
    'reply-1', 'root-1', 'plumber', '', 'Replace the cartridge.', '', '',
    'visible', '2026-08-26T09:00:00Z', '2026-08-26T09:05:00Z'
  );
INSERT INTO post_votes (user_id, post_id, value)
VALUES ('homeowner', 'root-1', 1);`); err != nil {
		t.Fatal(err)
	}

	var postCount, voteCount int
	if err := conn.QueryRowContext(ctx, "SELECT count(*) FROM posts").Scan(&postCount); err != nil {
		t.Fatal(err)
	}
	if err := conn.QueryRowContext(ctx, "SELECT count(*) FROM post_votes").Scan(&voteCount); err != nil {
		t.Fatal(err)
	}
	if postCount != 2 || voteCount != 1 {
		t.Fatalf("counts posts=%d votes=%d", postCount, voteCount)
	}
	var postVoteIndexCount int
	if err := conn.QueryRowContext(ctx, `
SELECT count(*)
FROM pg_indexes
WHERE schemaname = current_schema()
  AND tablename = 'post_votes'
  AND indexname = 'idx_post_votes_post_id'`).Scan(&postVoteIndexCount); err != nil {
		t.Fatal(err)
	}
	if postVoteIndexCount != 1 {
		t.Fatalf("post vote index count = %d, want 1", postVoteIndexCount)
	}
	var postAuthorIndexCount int
	if err := conn.QueryRowContext(ctx, `
SELECT count(*)
FROM pg_indexes
WHERE schemaname = current_schema()
  AND tablename = 'posts'
  AND indexname = 'idx_posts_author_created'`).Scan(&postAuthorIndexCount); err != nil {
		t.Fatal(err)
	}
	if postAuthorIndexCount != 1 {
		t.Fatalf("post author index count = %d, want 1", postAuthorIndexCount)
	}
	if _, err := conn.ExecContext(ctx, `
INSERT INTO post_votes (user_id, post_id, value)
VALUES ('homeowner', 'root-1', -1)`); err == nil {
		t.Fatal("duplicate user/post vote unexpectedly succeeded")
	}

	var rootParent sql.NullString
	var rootAuthor, title, rootBody, city, postDate, rootState, rootCreated, rootUpdated string
	if err := conn.QueryRowContext(ctx, `
SELECT parent_id, author_id, title, body, city, post_date, post_state, created_at, updated_at
FROM posts
WHERE id = 'root-1'`).Scan(
		&rootParent,
		&rootAuthor,
		&title,
		&rootBody,
		&city,
		&postDate,
		&rootState,
		&rootCreated,
		&rootUpdated,
	); err != nil {
		t.Fatal(err)
	}
	if rootParent.Valid ||
		rootAuthor != "homeowner" ||
		title != "Leaky sink" ||
		rootBody != "It drips." ||
		city != "Oakland" ||
		postDate != "2026-08-26" ||
		rootState != "visible" ||
		rootCreated != "2026-08-26T08:00:00Z" ||
		rootUpdated != rootCreated {
		t.Fatalf("unexpected root post")
	}

	var replyParent, replyAuthor, replyBody, replyState, replyCreated, replyUpdated string
	if err := conn.QueryRowContext(ctx, `
SELECT parent_id, author_id, body, post_state, created_at, updated_at
FROM posts
WHERE id = 'reply-1'`).Scan(
		&replyParent,
		&replyAuthor,
		&replyBody,
		&replyState,
		&replyCreated,
		&replyUpdated,
	); err != nil {
		t.Fatal(err)
	}
	if replyParent != "root-1" ||
		replyAuthor != "plumber" ||
		replyBody != "Replace the cartridge." ||
		replyState != "visible" ||
		replyCreated != "2026-08-26T09:00:00Z" ||
		replyUpdated != "2026-08-26T09:05:00Z" {
		t.Fatalf("unexpected reply post")
	}

	var voteValue int
	if err := conn.QueryRowContext(ctx, `
SELECT value FROM post_votes
WHERE user_id = 'homeowner' AND post_id = 'root-1'`).Scan(&voteValue); err != nil {
		t.Fatal(err)
	}
	if voteValue != 1 {
		t.Fatalf("vote value = %d, want 1", voteValue)
	}

	if _, err := conn.ExecContext(ctx, `
INSERT INTO posts (
  id, parent_id, author_id, title, body, city, post_date, post_state, created_at, updated_at
) VALUES (
  'invalid-reply', 'root-1', 'homeowner', 'Replies cannot have titles', 'Body', '', '',
  'visible', 'now', 'now'
)`); err == nil {
		t.Fatal("reply with root-only title unexpectedly succeeded")
	}

	queries := sqlc.New(conn)
	if err := queries.CreatePost(ctx, sqlc.CreatePostParams{
		ID:        "follow-up",
		ParentID:  sql.NullString{String: "reply-1", Valid: true},
		AuthorID:  "homeowner",
		Body:      "It is still dripping.",
		PostState: string(PostStateVisible),
		CreatedAt: "2026-08-26T10:00:00Z",
		UpdatedAt: "2026-08-26T10:00:00Z",
	}); err != nil {
		t.Fatal(err)
	}
	thread, err := queries.ListPostThread(ctx, "root-1")
	if err != nil {
		t.Fatal(err)
	}
	if len(thread) != 3 ||
		thread[0].ID != "root-1" ||
		thread[1].ID != "reply-1" ||
		thread[2].ID != "follow-up" {
		t.Fatalf("recursive thread = %+v", thread)
	}
	nonRootThread, err := queries.ListPostThread(ctx, "reply-1")
	if err != nil {
		t.Fatal(err)
	}
	if len(nonRootThread) != 0 {
		t.Fatalf("non-root thread lookup returned %+v", nonRootThread)
	}
	if n, err := queries.UpdatePost(ctx, sqlc.UpdatePostParams{
		ID:        "follow-up",
		Body:      "The drip continues.",
		UpdatedAt: "2026-08-26T10:05:00Z",
	}); err != nil || n != 1 {
		t.Fatalf("update rows=%d error=%v", n, err)
	}
	if n, err := queries.UpsertPostVoteOnVisibleRoot(ctx, sqlc.UpsertPostVoteOnVisibleRootParams{
		UserID:      "plumber",
		PostID:      "root-1",
		Value:       1,
		HiddenState: string(PostStateHidden),
	}); err != nil || n != 1 {
		t.Fatalf("vote rows=%d error=%v", n, err)
	}
	roots, err := queries.ListRootPosts(ctx, sqlc.ListRootPostsParams{
		ViewerID:    "plumber",
		RowLimit:    100,
		PostDate:    "2026-08-26",
		HiddenState: string(PostStateHidden),
	})
	if err != nil {
		t.Fatal(err)
	}
	if len(roots) != 1 ||
		roots[0].Score != 2 ||
		!roots[0].Answered ||
		roots[0].UserVote != 1 {
		t.Fatalf("root annotations = %+v", roots)
	}
	summary, err := queries.GetRootPostVoteSummary(ctx, sqlc.GetRootPostVoteSummaryParams{
		ViewerID: "plumber",
		RootID:   "root-1",
	})
	if err != nil || summary.Score != 2 || summary.UserVote != 1 {
		t.Fatalf("root vote summary = %+v, %v", summary, err)
	}
	byAuthor, err := queries.ListRootPostsByAuthor(ctx, sqlc.ListRootPostsByAuthorParams{
		AuthorID:    "homeowner",
		HiddenState: string(PostStateHidden),
		RowLimit:    50,
	})
	if err != nil || len(byAuthor) != 1 || byAuthor[0].ID != "root-1" {
		t.Fatalf("roots by author = %+v, %v", byAuthor, err)
	}
	answeredBy, err := queries.ListRootPostsAnsweredBy(ctx, sqlc.ListRootPostsAnsweredByParams{
		HiddenState: string(PostStateHidden),
		AdminID:     "plumber",
		RowLimit:    50,
	})
	if err != nil || len(answeredBy) != 1 || answeredBy[0].ID != "root-1" {
		t.Fatalf("roots answered by admin = %+v, %v", answeredBy, err)
	}
	for _, state := range []PostState{PostStateLocked, PostStateVisible} {
		n, err := queries.UpdateRootPostState(ctx, sqlc.UpdateRootPostStateParams{
			PostState: string(state),
			UpdatedAt: "2026-08-26T10:10:00Z",
			ID:        "root-1",
		})
		if err != nil || n != 1 {
			t.Fatalf("set root state %q rows=%d error=%v", state, n, err)
		}
	}

	if _, err := conn.ExecContext(ctx, `
DROP INDEX idx_posts_root_date;
ALTER TABLE posts RENAME COLUMN post_date TO hunt_date;
CREATE INDEX idx_posts_root_hunt
  ON posts(hunt_date, post_state)
  WHERE parent_id IS NULL;`); err != nil {
		t.Fatal(err)
	}
	if err := migratePostDate(ctx, conn); err != nil {
		t.Fatal(err)
	}
	if err := migratePostDate(ctx, conn); err != nil {
		t.Fatalf("post date migration is not idempotent: %v", err)
	}

	var postDateColumnCount, huntDateColumnCount, rootDateIndexCount, legacyIndexCount int
	if err := conn.QueryRowContext(ctx, `
SELECT
  count(*) FILTER (WHERE column_name = 'post_date'),
  count(*) FILTER (WHERE column_name = 'hunt_date')
FROM information_schema.columns
WHERE table_schema = current_schema()
  AND table_name = 'posts'`).Scan(&postDateColumnCount, &huntDateColumnCount); err != nil {
		t.Fatal(err)
	}
	if err := conn.QueryRowContext(ctx, `
SELECT
  count(*) FILTER (WHERE indexname = 'idx_posts_root_date'),
  count(*) FILTER (WHERE indexname = 'idx_posts_root_hunt')
FROM pg_indexes
WHERE schemaname = current_schema()
  AND tablename = 'posts'`).Scan(&rootDateIndexCount, &legacyIndexCount); err != nil {
		t.Fatal(err)
	}
	var migratedPostDate string
	if err := conn.QueryRowContext(ctx, `
SELECT post_date FROM posts WHERE id = 'root-1'`).Scan(&migratedPostDate); err != nil {
		t.Fatal(err)
	}
	if postDateColumnCount != 1 ||
		huntDateColumnCount != 0 ||
		rootDateIndexCount != 1 ||
		legacyIndexCount != 0 ||
		migratedPostDate != "2026-08-26" {
		t.Fatalf(
			"post date migration columns=%d legacy_columns=%d indexes=%d legacy_indexes=%d date=%q",
			postDateColumnCount,
			huntDateColumnCount,
			rootDateIndexCount,
			legacyIndexCount,
			migratedPostDate,
		)
	}

	if _, err := conn.ExecContext(ctx, `
DROP INDEX idx_posts_root_date;
ALTER TABLE posts DROP CONSTRAINT posts_shape_check;
ALTER TABLE posts ADD COLUMN hidden INTEGER NOT NULL DEFAULT 0;
UPDATE posts SET hidden = CASE WHEN id = 'root-1' THEN 1 ELSE 0 END;
ALTER TABLE posts DROP COLUMN post_state;
ALTER TABLE posts ADD CONSTRAINT posts_check CHECK (
  (parent_id IS NULL AND title <> '' AND post_date <> '')
  OR
  (parent_id IS NOT NULL AND title = '' AND city = '' AND post_date = '' AND hidden = 0)
);
CREATE INDEX idx_posts_root_date
  ON posts(post_date, hidden)
  WHERE parent_id IS NULL;`); err != nil {
		t.Fatal(err)
	}
	if err := migratePostState(ctx, conn); err != nil {
		t.Fatal(err)
	}
	if err := migratePostState(ctx, conn); err != nil {
		t.Fatalf("post state migration is not idempotent: %v", err)
	}

	var postStateColumnCount, hiddenColumnCount int
	if err := conn.QueryRowContext(ctx, `
SELECT
  count(*) FILTER (WHERE column_name = 'post_state'),
  count(*) FILTER (WHERE column_name = 'hidden')
FROM information_schema.columns
WHERE table_schema = current_schema()
  AND table_name = 'posts'`).Scan(&postStateColumnCount, &hiddenColumnCount); err != nil {
		t.Fatal(err)
	}
	var hiddenState, replyStateAfterMigration string
	if err := conn.QueryRowContext(ctx, `
SELECT post_state FROM posts WHERE id = 'root-1'`).Scan(&hiddenState); err != nil {
		t.Fatal(err)
	}
	if err := conn.QueryRowContext(ctx, `
SELECT post_state FROM posts WHERE id = 'reply-1'`).Scan(&replyStateAfterMigration); err != nil {
		t.Fatal(err)
	}
	var postStateDataType string
	if err := conn.QueryRowContext(ctx, `
SELECT data_type
FROM information_schema.columns
WHERE table_schema = current_schema()
  AND table_name = 'posts'
  AND column_name = 'post_state'`).Scan(&postStateDataType); err != nil {
		t.Fatal(err)
	}
	var stateIndexCount int
	if err := conn.QueryRowContext(ctx, `
SELECT count(*)
FROM pg_indexes
WHERE schemaname = current_schema()
  AND tablename = 'posts'
  AND indexname = 'idx_posts_root_date'
  AND indexdef LIKE '%(post_date, post_state)%'`).Scan(&stateIndexCount); err != nil {
		t.Fatal(err)
	}
	if postStateColumnCount != 1 ||
		hiddenColumnCount != 0 ||
		hiddenState != "hidden" ||
		replyStateAfterMigration != "visible" ||
		postStateDataType != "text" ||
		stateIndexCount != 1 {
		t.Fatalf(
			"post state migration columns=%d hidden_columns=%d root=%q reply=%q type=%q indexes=%d",
			postStateColumnCount,
			hiddenColumnCount,
			hiddenState,
			replyStateAfterMigration,
			postStateDataType,
			stateIndexCount,
		)
	}

	if _, err := conn.ExecContext(ctx, `
CREATE TABLE questions (id TEXT PRIMARY KEY);
CREATE TABLE votes (id TEXT PRIMARY KEY);
CREATE TABLE answers (id TEXT PRIMARY KEY);`); err != nil {
		t.Fatal(err)
	}
	if err := migrateDropLegacyPostTables(ctx, conn); err != nil {
		t.Fatal(err)
	}
	if err := migrateDropLegacyPostTables(ctx, conn); err != nil {
		t.Fatalf("legacy table cleanup is not idempotent: %v", err)
	}
	for _, table := range []string{"questions", "votes", "answers"} {
		var relation sql.NullString
		if err := conn.QueryRowContext(ctx, "SELECT to_regclass($1)", table).Scan(&relation); err != nil {
			t.Fatal(err)
		}
		if relation.Valid {
			t.Fatalf("legacy table %q still exists", table)
		}
	}
}

func TestMigratePostsReportsStep(t *testing.T) {
	t.Parallel()

	exec := &failingMigrationExec{failAt: 5}
	err := migratePosts(context.Background(), exec)
	if err == nil || !strings.Contains(err.Error(), "create post votes") {
		t.Fatalf("error = %v, want create post votes context", err)
	}
}

type failingMigrationExec struct {
	calls  int
	failAt int
}

func (f *failingMigrationExec) ExecContext(context.Context, string, ...any) (sql.Result, error) {
	f.calls++
	if f.calls == f.failAt {
		return nil, fmt.Errorf("boom")
	}
	return nil, nil
}

func (*failingMigrationExec) QueryContext(context.Context, string, ...any) (*sql.Rows, error) {
	return nil, fmt.Errorf("not implemented")
}