projax/db/unified_test.go

package db_test

import (
	"context"
	"fmt"
	"testing"
	"time"
)

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

	// After Phase 1.5 the view collapses to a thin projection over projax.items.
	// Source is always 'projax'; there are no 'mai.projects' rows in the view.
	var total, projaxN, otherN int
	if err := pool.QueryRow(ctx, `select count(*) from projax.items_unified`).Scan(&total); err != nil {
		t.Fatalf("total: %v", err)
	}
	if err := pool.QueryRow(ctx, `select count(*) from projax.items_unified where source = 'projax'`).Scan(&projaxN); err != nil {
		t.Fatalf("projax count: %v", err)
	}
	if err := pool.QueryRow(ctx, `select count(*) from projax.items_unified where source <> 'projax'`).Scan(&otherN); err != nil {
		t.Fatalf("other count: %v", err)
	}
	if projaxN != total {
		t.Fatalf("expected all %d rows to have source='projax', got %d projax + %d other", total, projaxN, otherN)
	}
	if total < 7 {
		t.Fatalf("expected at least the 7 seeded roots in items_unified, got %d", total)
	}
}

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

	// Any item with a 'mai-project' item_link should surface source_ref_id.
	var linked, withRef int
	if err := pool.QueryRow(ctx,
		`select count(*) from projax.item_links where ref_type='mai-project'`,
	).Scan(&linked); err != nil {
		t.Fatalf("count links: %v", err)
	}
	if err := pool.QueryRow(ctx,
		`select count(*) from projax.items_unified where source_ref_id is not null`,
	).Scan(&withRef); err != nil {
		t.Fatalf("count source_ref_id: %v", err)
	}
	if linked != withRef {
		t.Fatalf("source_ref_id surfacing diverged from item_links: %d link rows vs %d view rows with ref", linked, withRef)
	}
}

// TestSoftDeleteCascadesToItemLinks proves migration 0013's trigger: setting
// deleted_at on a projax.items row deletes every item_links row that pointed
// at it in the same statement. Without this, the source_ref_id count in
// items_unified diverges from the raw link count (and external pointers to
// dead items linger, which silent-corrupts downstream integrations).
func TestSoftDeleteCascadesToItemLinks(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)

	// Insert a fresh root item that is NOT 'mai' managed so the sync_to_mai
	// trigger does not own this link's life cycle — we want to test the new
	// cascade trigger in isolation.
	var id string
	slug := fmt.Sprintf("linkcascade-%d", time.Now().UnixNano())
	if err := tx.QueryRow(ctx,
		`insert into projax.items (kind, title, slug, parent_ids, management)
		  values (array['project']::text[], 'cascade test', $1, '{}'::uuid[], '{}')
		  returning id`,
		slug,
	).Scan(&id); err != nil {
		t.Fatalf("insert item: %v", err)
	}
	if _, err := tx.Exec(ctx,
		`insert into projax.item_links (item_id, ref_type, ref_id, rel)
		  values ($1, 'gitea-repo', 'mgit.msbls.de/test', 'tracks')`,
		id,
	); err != nil {
		t.Fatalf("insert link: %v", err)
	}

	var linksBefore int
	if err := tx.QueryRow(ctx, `select count(*) from projax.item_links where item_id=$1`, id).Scan(&linksBefore); err != nil {
		t.Fatalf("count before: %v", err)
	}
	if linksBefore != 1 {
		t.Fatalf("expected 1 link before soft-delete, got %d", linksBefore)
	}

	if _, err := tx.Exec(ctx, `update projax.items set deleted_at = now() where id=$1`, id); err != nil {
		t.Fatalf("soft-delete: %v", err)
	}

	var linksAfter int
	if err := tx.QueryRow(ctx, `select count(*) from projax.item_links where item_id=$1`, id).Scan(&linksAfter); err != nil {
		t.Fatalf("count after: %v", err)
	}
	if linksAfter != 0 {
		t.Fatalf("expected 0 links after soft-delete (cascade trigger should have removed them), got %d", linksAfter)
	}
}

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

	// Sanity-check tags + management + the multi-parent paths/parent_ids columns.
	row := pool.QueryRow(ctx,
		`select tags, management, paths, parent_ids from projax.items_unified
		 where cardinality(parent_ids) = 0 limit 1`)
	var tags, mgmt, paths []string
	var parentIDs []string
	if err := row.Scan(&tags, &mgmt, &paths, &parentIDs); err != nil {
		t.Fatalf("scan tags/management/paths/parent_ids: %v", err)
	}
	if len(paths) == 0 {
		t.Fatalf("expected at least one path on a root item")
	}
}