projax/db/migrate_test.go

package db_test

import (
	"context"
	"fmt"
	"os"
	"testing"
	"time"

	"github.com/jackc/pgx/v5/pgxpool"

	"github.com/m/projax/db"
)

// skipMigrate reports whether the test process should skip ApplyMigrations.
// Useful when the live deploy or another process is concurrently migrating
// against the same DB — Postgres serialises CREATE / ALTER OWNER and the
// loser deadlocks. Set PROJAX_SKIP_MIGRATE=1 to opt out.
func skipMigrate() bool { return os.Getenv("PROJAX_SKIP_MIGRATE") == "1" }

// connect returns a pool or skips the test if no DB is configured.
// Honours PROJAX_DB_URL first, then SUPABASE_DATABASE_URL.
func connect(t *testing.T) *pgxpool.Pool {
	t.Helper()
	url := os.Getenv("PROJAX_DB_URL")
	if url == "" {
		url = os.Getenv("SUPABASE_DATABASE_URL")
	}
	if url == "" {
		t.Skip("no PROJAX_DB_URL / SUPABASE_DATABASE_URL set — skipping integration test")
	}
	ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancel()
	pool, err := pgxpool.New(ctx, url)
	if err != nil {
		t.Fatalf("pool: %v", err)
	}
	if err := pool.Ping(ctx); err != nil {
		t.Skipf("DB unreachable: %v", err)
	}
	return pool
}

func TestMigrationsAreIdempotent(t *testing.T) {
	if skipMigrate() {
		t.Skip("PROJAX_SKIP_MIGRATE=1 — schema assumed already applied")
	}
	pool := connect(t)
	defer pool.Close()
	ctx := context.Background()

	// Apply twice; second run must not fail.
	if err := db.ApplyMigrations(ctx, pool); err != nil {
		t.Fatalf("first apply: %v", err)
	}
	if err := db.ApplyMigrations(ctx, pool); err != nil {
		t.Fatalf("second apply: %v", err)
	}

	var n int
	if err := pool.QueryRow(ctx,
		`select count(*) from projax.items where cardinality(parent_ids) = 0`,
	).Scan(&n); err != nil {
		t.Fatalf("count roots: %v", err)
	}
	if n < 7 {
		t.Fatalf("expected at least 7 seeded roots, got %d", n)
	}
}

func TestPathTriggerNestAndRename(t *testing.T) {
	pool := connect(t)
	defer pool.Close()
	ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
	defer cancel()

	tx, err := pool.Begin(ctx)
	if err != nil {
		t.Fatalf("begin: %v", err)
	}
	defer tx.Rollback(ctx)

	var homeID string
	if err := tx.QueryRow(ctx, `select id from projax.items where slug='home' and cardinality(parent_ids)=0`).Scan(&homeID); err != nil {
		t.Fatalf("read home: %v", err)
	}

	var parentPaths []string
	if err := tx.QueryRow(ctx,
		`insert into projax.items (kind, title, slug, parent_ids) values (array['project']::text[], $1, $2, ARRAY[$3]::uuid[]) returning paths`,
		"Spring clean", "spring-clean", homeID,
	).Scan(&parentPaths); err != nil {
		t.Fatalf("insert spring-clean: %v", err)
	}
	if len(parentPaths) != 1 || parentPaths[0] != "home.spring-clean" {
		t.Fatalf("expected paths ['home.spring-clean'], got %v", parentPaths)
	}

	var childPaths []string
	if err := tx.QueryRow(ctx,
		`insert into projax.items (kind, title, slug, parent_ids)
		 select array['project']::text[], 'Bathroom', 'bathroom', ARRAY[id]::uuid[]
		 from projax.items where 'home.spring-clean' = any(paths)
		 returning paths`).Scan(&childPaths); err != nil {
		t.Fatalf("insert bathroom: %v", err)
	}
	if len(childPaths) != 1 || childPaths[0] != "home.spring-clean.bathroom" {
		t.Fatalf("expected paths ['home.spring-clean.bathroom'], got %v", childPaths)
	}

	// Rename middle: descendants must be rewritten.
	if _, err := tx.Exec(ctx,
		`update projax.items set slug='big-clean' where 'home.spring-clean' = any(paths)`,
	); err != nil {
		t.Fatalf("rename: %v", err)
	}
	var renamedChildPaths []string
	if err := tx.QueryRow(ctx,
		`select paths from projax.items where slug='bathroom'`,
	).Scan(&renamedChildPaths); err != nil {
		t.Fatalf("read child after rename: %v", err)
	}
	if len(renamedChildPaths) != 1 || renamedChildPaths[0] != "home.big-clean.bathroom" {
		t.Fatalf("expected ['home.big-clean.bathroom'], got %v", renamedChildPaths)
	}
}

func TestPathTriggerReparent(t *testing.T) {
	pool := connect(t)
	defer pool.Close()
	ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
	defer cancel()

	tx, err := pool.Begin(ctx)
	if err != nil {
		t.Fatalf("begin: %v", err)
	}
	defer tx.Rollback(ctx)

	var homeID, devID string
	if err := tx.QueryRow(ctx, `select id from projax.items where slug='home' and cardinality(parent_ids)=0`).Scan(&homeID); err != nil {
		t.Fatalf("home: %v", err)
	}
	if err := tx.QueryRow(ctx, `select id from projax.items where slug='dev' and cardinality(parent_ids)=0`).Scan(&devID); err != nil {
		t.Fatalf("dev: %v", err)
	}

	var pid string
	if err := tx.QueryRow(ctx,
		`insert into projax.items (kind, title, slug, parent_ids) values (array['project']::text[], 'X', 'mover', ARRAY[$1]::uuid[]) returning id`,
		homeID,
	).Scan(&pid); err != nil {
		t.Fatalf("insert mover: %v", err)
	}
	if _, err := tx.Exec(ctx,
		`insert into projax.items (kind, title, slug, parent_ids) values (array['project']::text[], 'X.child', 'child', ARRAY[$1]::uuid[])`,
		pid,
	); err != nil {
		t.Fatalf("insert child: %v", err)
	}

	if _, err := tx.Exec(ctx,
		`update projax.items set parent_ids=ARRAY[$1]::uuid[] where id=$2`, devID, pid,
	); err != nil {
		t.Fatalf("reparent: %v", err)
	}

	var moverPaths, childPaths []string
	if err := tx.QueryRow(ctx, `select paths from projax.items where id=$1`, pid).Scan(&moverPaths); err != nil {
		t.Fatalf("read mover paths: %v", err)
	}
	if len(moverPaths) != 1 || moverPaths[0] != "dev.mover" {
		t.Fatalf("mover paths = %v, want [dev.mover]", moverPaths)
	}
	if err := tx.QueryRow(ctx,
		`select paths from projax.items where $1::uuid = any(parent_ids)`, pid,
	).Scan(&childPaths); err != nil {
		t.Fatalf("read child paths: %v", err)
	}
	if len(childPaths) != 1 || childPaths[0] != "dev.mover.child" {
		t.Fatalf("child paths = %v, want [dev.mover.child]", childPaths)
	}
}

// Phase 1.5 collapsed the area/project distinction. Both shapes are now
// allowed: projects at root (former areas) and any-kind under any-parent.
// The only structural rule left is "no cycles", covered by TestCycleRejected.
func TestRootAndChildBothAllowed(t *testing.T) {
	pool := connect(t)
	defer pool.Close()
	ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
	defer cancel()

	tx, err := pool.Begin(ctx)
	if err != nil {
		t.Fatalf("begin: %v", err)
	}
	defer tx.Rollback(ctx)

	rootSlug := fmt.Sprintf("root-proj-%d", time.Now().UnixNano())
	if _, err := tx.Exec(ctx,
		`insert into projax.items (kind, title, slug) values (array['project']::text[], 'Root proj', $1)`,
		rootSlug,
	); err != nil {
		t.Fatalf("root project should be allowed: %v", err)
	}

	var rootID string
	if err := tx.QueryRow(ctx, `select id from projax.items where slug=$1`, rootSlug).Scan(&rootID); err != nil {
		t.Fatalf("root id: %v", err)
	}
	if _, err := tx.Exec(ctx,
		`insert into projax.items (kind, title, slug, parent_ids) values (array['project']::text[], 'Child', $1, ARRAY[$2]::uuid[])`,
		fmt.Sprintf("child-%d", time.Now().UnixNano()), rootID,
	); err != nil {
		t.Fatalf("child of root project should be allowed: %v", err)
	}
}

func TestCycleRejected(t *testing.T) {
	pool := connect(t)
	defer pool.Close()
	ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
	defer cancel()

	tx, err := pool.Begin(ctx)
	if err != nil {
		t.Fatalf("begin: %v", err)
	}
	defer tx.Rollback(ctx)

	var homeID string
	if err := tx.QueryRow(ctx, `select id from projax.items where slug='home' and cardinality(parent_ids)=0`).Scan(&homeID); err != nil {
		t.Fatalf("home: %v", err)
	}
	var aID, bID string
	if err := tx.QueryRow(ctx,
		`insert into projax.items (kind, title, slug, parent_ids) values (array['project']::text[], 'A', 'cyc-a', ARRAY[$1]::uuid[]) returning id`, homeID,
	).Scan(&aID); err != nil {
		t.Fatalf("a: %v", err)
	}
	if err := tx.QueryRow(ctx,
		`insert into projax.items (kind, title, slug, parent_ids) values (array['project']::text[], 'B', 'cyc-b', ARRAY[$1]::uuid[]) returning id`, aID,
	).Scan(&bID); err != nil {
		t.Fatalf("b: %v", err)
	}
	// Now try to make A a child of B -> cycle.
	if _, err := tx.Exec(ctx, `update projax.items set parent_ids=ARRAY[$1]::uuid[] where id=$2`, bID, aID); err == nil {
		t.Fatalf("expected cycle rejection, got nil error")
	}

	// Self-parent.
	if _, err := tx.Exec(ctx, `update projax.items set parent_ids=ARRAY[$1]::uuid[] where id=$1`, aID); err == nil {
		t.Fatalf("expected self-parent rejection, got nil error")
	}
}

// Multi-parent specific tests.

func TestMultiParentResolvesBothPaths(t *testing.T) {
	pool := connect(t)
	defer pool.Close()
	ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
	defer cancel()
	tx, err := pool.Begin(ctx)
	if err != nil {
		t.Fatalf("begin: %v", err)
	}
	defer tx.Rollback(ctx)

	var dev, work string
	if err := tx.QueryRow(ctx, `select id from projax.items where slug='dev' and cardinality(parent_ids)=0`).Scan(&dev); err != nil {
		t.Fatalf("dev: %v", err)
	}
	if err := tx.QueryRow(ctx, `select id from projax.items where slug='work' and cardinality(parent_ids)=0`).Scan(&work); err != nil {
		t.Fatalf("work: %v", err)
	}
	slug := fmt.Sprintf("multi-%d", time.Now().UnixNano())
	if _, err := tx.Exec(ctx,
		`insert into projax.items (kind, title, slug, parent_ids) values (array['project']::text[], 'Multi', $1, ARRAY[$2,$3]::uuid[])`,
		slug, dev, work,
	); err != nil {
		t.Fatalf("insert multi-parent: %v", err)
	}
	var paths []string
	if err := tx.QueryRow(ctx, `select paths from projax.items where slug=$1`, slug).Scan(&paths); err != nil {
		t.Fatalf("read paths: %v", err)
	}
	want := map[string]bool{"dev." + slug: true, "work." + slug: true}
	got := map[string]bool{}
	for _, p := range paths {
		got[p] = true
	}
	for k := range want {
		if !got[k] {
			t.Fatalf("expected path %q in %v", k, paths)
		}
	}
}

// TestBackfillTagsFromArea verifies migration 0012's logic both directly (the
// query as written, applied to a transaction-local fixture) and in the
// applied state (every live child item now carries its area slug). We
// re-execute the migration body inside a transaction so we can assert on a
// freshly inserted untagged row without polluting the live DB.
func TestBackfillTagsFromArea(t *testing.T) {
	pool := connect(t)
	defer pool.Close()
	ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
	defer cancel()

	// First: every existing live child item already carries area tags after
	// 0012 was applied on boot. This catches regressions where a future
	// migration clears the column or the backfill stops covering some path.
	var untagged int
	if err := pool.QueryRow(ctx,
		`select count(*) from projax.items
		  where deleted_at is null
		    and cardinality(parent_ids) > 0
		    and tags = '{}'`).Scan(&untagged); err != nil {
		t.Fatalf("count untagged children: %v", err)
	}
	if untagged != 0 {
		t.Fatalf("expected every live child to have area tags after 0012, got %d still empty", untagged)
	}

	// Second: idempotency + multi-parent. Insert a fresh multi-parent row
	// with empty tags, re-run the backfill query, observe both area slugs in
	// tags. Re-run a second time and check nothing changes.
	tx, err := pool.Begin(ctx)
	if err != nil {
		t.Fatalf("begin: %v", err)
	}
	defer tx.Rollback(ctx)

	var dev, work string
	if err := tx.QueryRow(ctx, `select id from projax.items where slug='dev' and cardinality(parent_ids)=0`).Scan(&dev); err != nil {
		t.Fatalf("dev: %v", err)
	}
	if err := tx.QueryRow(ctx, `select id from projax.items where slug='work' and cardinality(parent_ids)=0`).Scan(&work); err != nil {
		t.Fatalf("work: %v", err)
	}
	slug := fmt.Sprintf("tag-backfill-%d", time.Now().UnixNano())
	if _, err := tx.Exec(ctx,
		`insert into projax.items (kind, title, slug, parent_ids, tags)
		  values (array['project']::text[], 'TagBackfill', $1, ARRAY[$2,$3]::uuid[], '{}')`,
		slug, dev, work,
	); err != nil {
		t.Fatalf("insert multi-parent: %v", err)
	}

	backfill := `UPDATE projax.items i
	   SET tags = sub.area_tags
	  FROM (
	    SELECT id, ARRAY(SELECT DISTINCT split_part(p, '.', 1)
	                       FROM unnest(paths) p ORDER BY 1) AS area_tags
	      FROM projax.items
	     WHERE deleted_at IS NULL AND tags = '{}'
	       AND cardinality(parent_ids) > 0
	  ) sub
	 WHERE i.id = sub.id AND i.tags = '{}'
	   AND cardinality(sub.area_tags) > 0`
	if _, err := tx.Exec(ctx, backfill); err != nil {
		t.Fatalf("first backfill apply: %v", err)
	}

	var tags []string
	if err := tx.QueryRow(ctx, `select tags from projax.items where slug=$1`, slug).Scan(&tags); err != nil {
		t.Fatalf("read tags after backfill: %v", err)
	}
	got := map[string]bool{}
	for _, t := range tags {
		got[t] = true
	}
	if !got["dev"] || !got["work"] || len(tags) != 2 {
		t.Fatalf("expected tags = {dev, work}, got %v", tags)
	}

	// Idempotent: second run is a no-op on this row (tags no longer = '{}').
	if _, err := tx.Exec(ctx, backfill); err != nil {
		t.Fatalf("second backfill apply: %v", err)
	}
	var tags2 []string
	if err := tx.QueryRow(ctx, `select tags from projax.items where slug=$1`, slug).Scan(&tags2); err != nil {
		t.Fatalf("read tags after second backfill: %v", err)
	}
	if len(tags2) != 2 {
		t.Fatalf("expected idempotent backfill, got %v after second run", tags2)
	}
}

func TestSlugCollisionUnderCommonParent(t *testing.T) {
	pool := connect(t)
	defer pool.Close()
	ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
	defer cancel()
	tx, err := pool.Begin(ctx)
	if err != nil {
		t.Fatalf("begin: %v", err)
	}
	defer tx.Rollback(ctx)

	var dev string
	if err := tx.QueryRow(ctx, `select id from projax.items where slug='dev' and cardinality(parent_ids)=0`).Scan(&dev); err != nil {
		t.Fatalf("dev: %v", err)
	}
	slug := fmt.Sprintf("collide-%d", time.Now().UnixNano())
	if _, err := tx.Exec(ctx,
		`insert into projax.items (kind, title, slug, parent_ids) values (array['project']::text[], 'A', $1, ARRAY[$2]::uuid[])`,
		slug, dev,
	); err != nil {
		t.Fatalf("first insert: %v", err)
	}
	if _, err := tx.Exec(ctx,
		`insert into projax.items (kind, title, slug, parent_ids) values (array['project']::text[], 'B', $1, ARRAY[$2]::uuid[])`,
		slug, dev,
	); err == nil {
		t.Fatalf("expected slug-collision rejection under shared parent dev")
	}
}