projax/store/store.go

package store

import (
	"context"
	"errors"
	"fmt"
	"slices"
	"time"

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

// Item is the unified row shape served by projax.items_unified. Phase 1.5
// collapsed the area/project distinction (kind keeps the slot for future
// types but 'area' is no longer a special value) and extended the tree to
// a DAG: an item can have zero or more parents and surface under multiple
// paths simultaneously.
type Item struct {
	ID          string
	Kind        []string
	Title       string
	Slug        string
	Paths       []string // sorted, deduped — one entry per ancestor lineage
	ParentIDs   []string
	ContentMD   string
	Aliases     []string
	Metadata    map[string]any
	Status      string
	Pinned      bool
	Archived    bool
	StartTime   *time.Time
	EndTime     *time.Time
	Source      string  // always "projax" after Phase 1.5; kept for forward-compat
	SourceRefID *string // mai.projects.id when a 'mai-project' item_links row exists
	Tags        []string
	Management  []string
	// Phase 4d public-listing fields. Source of truth for the portfolio
	// flexsiebels.de + any future public consumer renders via MCP.
	Public             bool
	PublicDescription  string
	PublicLiveURL      string
	PublicSourceURL    string
	PublicScreenshots  []string
	// Phase 4f timeline-exclude. Per-item array of kinds to hide from
	// /timeline aggregation. Values: 'todos' | 'events' | 'docs' | 'creation'.
	// Empty array (default) = nothing excluded = current behaviour.
	TimelineExclude    []string
	CreatedAt   time.Time
	UpdatedAt   time.Time
}

// ExcludesTimelineKind reports whether this item's timeline_exclude array
// names the given kind. The aggregator uses the singular form ("todo",
// "event", "doc", "creation"); the persisted values use the plural form
// ("todos", "events", "docs", "creation") per the task brief and so the
// UI labels read naturally. This translates between the two so callers
// can pass either.
func (it *Item) ExcludesTimelineKind(kind string) bool {
	plural := kind
	switch kind {
	case "todo":
		plural = "todos"
	case "event":
		plural = "events"
	case "doc":
		plural = "docs"
	}
	for _, k := range it.TimelineExclude {
		if k == kind || k == plural {
			return true
		}
	}
	return false
}

// IsRoot reports whether this item sits at the top of the DAG (no parents).
func (it *Item) IsRoot() bool { return len(it.ParentIDs) == 0 }

// PrimaryPath returns the first path (alphabetically) for routing & display.
// Empty string when paths is empty (defensive — every persisted row has at
// least one path).
func (it *Item) PrimaryPath() string {
	if len(it.Paths) == 0 {
		return ""
	}
	return it.Paths[0]
}

// OtherPaths returns all paths except the primary one, for the "also at: …"
// breadcrumb on the detail page.
func (it *Item) OtherPaths() []string {
	if len(it.Paths) <= 1 {
		return nil
	}
	return it.Paths[1:]
}

// HasManagement reports whether the given mode (e.g. "mai") is set on the item.
func (it *Item) HasManagement(mode string) bool { return slices.Contains(it.Management, mode) }

// HasTag reports whether the item carries the given tag.
func (it *Item) HasTag(tag string) bool { return slices.Contains(it.Tags, tag) }

// Editable is preserved for template forward-compat. All rows are editable
// in projax after the mai.projects unification.
func (it *Item) Editable() bool { return true }

// SourceRefDeref returns the source ref id (empty string if nil) for templates.
func (it *Item) SourceRefDeref() string {
	if it.SourceRefID == nil {
		return ""
	}
	return *it.SourceRefID
}

// Store wraps a pgx pool with the queries projax needs.
type Store struct {
	Pool *pgxpool.Pool
}

func New(pool *pgxpool.Pool) *Store { return &Store{Pool: pool} }

var ErrNotFound = errors.New("projax: item not found")

const itemsUnifiedCols = `id, kind, title, slug, paths, parent_ids, content_md, aliases,
metadata, status, pinned, archived, start_time, end_time, source, source_ref_id,
tags, management, public, public_description, public_live_url, public_source_url,
public_screenshots, timeline_exclude, created_at, updated_at`

func scanItem(row pgx.Row) (*Item, error) {
	var it Item
	if err := row.Scan(
		&it.ID, &it.Kind, &it.Title, &it.Slug, &it.Paths, &it.ParentIDs, &it.ContentMD,
		&it.Aliases, &it.Metadata, &it.Status, &it.Pinned, &it.Archived,
		&it.StartTime, &it.EndTime, &it.Source, &it.SourceRefID,
		&it.Tags, &it.Management,
		&it.Public, &it.PublicDescription, &it.PublicLiveURL, &it.PublicSourceURL, &it.PublicScreenshots,
		&it.TimelineExclude,
		&it.CreatedAt, &it.UpdatedAt,
	); err != nil {
		return nil, err
	}
	return &it, nil
}

func scanItems(rows pgx.Rows) ([]*Item, error) {
	defer rows.Close()
	var out []*Item
	for rows.Next() {
		var it Item
		if err := rows.Scan(
			&it.ID, &it.Kind, &it.Title, &it.Slug, &it.Paths, &it.ParentIDs, &it.ContentMD,
			&it.Aliases, &it.Metadata, &it.Status, &it.Pinned, &it.Archived,
			&it.StartTime, &it.EndTime, &it.Source, &it.SourceRefID,
			&it.Tags, &it.Management,
			&it.Public, &it.PublicDescription, &it.PublicLiveURL, &it.PublicSourceURL, &it.PublicScreenshots,
			&it.TimelineExclude,
			&it.CreatedAt, &it.UpdatedAt,
		); err != nil {
			return nil, err
		}
		out = append(out, &it)
	}
	return out, rows.Err()
}

// ListAll returns every visible row from items_unified. Caller groups by tree.
func (s *Store) ListAll(ctx context.Context) ([]*Item, error) {
	rows, err := s.Pool.Query(ctx,
		`select `+itemsUnifiedCols+` from projax.items_unified order by paths[1]`)
	if err != nil {
		return nil, err
	}
	return scanItems(rows)
}

// GetByPath looks up a single item by any of its paths. Multi-parent items
// can be accessed via /i/work.paliad or /i/dev.paliad interchangeably.
func (s *Store) GetByPath(ctx context.Context, path string) (*Item, error) {
	row := s.Pool.QueryRow(ctx,
		`select `+itemsUnifiedCols+` from projax.items_unified where $1 = any(paths) limit 1`, path)
	it, err := scanItem(row)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return nil, ErrNotFound
		}
		return nil, err
	}
	return it, nil
}

// GetByID looks up a single projax-native item by uuid.
func (s *Store) GetByID(ctx context.Context, id string) (*Item, error) {
	row := s.Pool.QueryRow(ctx,
		`select `+itemsUnifiedCols+` from projax.items_unified where id = $1`, id)
	it, err := scanItem(row)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return nil, ErrNotFound
		}
		return nil, err
	}
	return it, nil
}

// Roots returns the top-level items (no parents), ordered by slug.
func (s *Store) Roots(ctx context.Context) ([]*Item, error) {
	rows, err := s.Pool.Query(ctx,
		`select `+itemsUnifiedCols+` from projax.items_unified
		 where cardinality(parent_ids) = 0
		 order by slug`)
	if err != nil {
		return nil, err
	}
	return scanItems(rows)
}

// MaiOrphans lists mai-managed items that landed at root and need m to
// re-parent them. This includes both backfilled items that the heuristic
// misplaced and brand-new mai.projects rows created by mai code (which the
// reverse sync trigger drops at root by design).
func (s *Store) MaiOrphans(ctx context.Context) ([]*Item, error) {
	rows, err := s.Pool.Query(ctx,
		`select `+itemsUnifiedCols+` from projax.items_unified
		 where cardinality(parent_ids) = 0 and 'mai' = any(management)
		 order by slug`)
	if err != nil {
		return nil, err
	}
	return scanItems(rows)
}

// CreateInput captures the editable surface of a projax-native item.
type CreateInput struct {
	Kind       []string
	Title      string
	Slug       string
	ParentIDs  []string
	ContentMD  string
	Status     string
	Pinned     bool
	StartTime  *time.Time
	EndTime    *time.Time
	Tags       []string
	Management []string
	Metadata   map[string]any
}

func (s *Store) Create(ctx context.Context, in CreateInput) (*Item, error) {
	if len(in.Kind) == 0 {
		return nil, errors.New("kind required")
	}
	if in.Title == "" {
		return nil, errors.New("title required")
	}
	if in.Slug == "" {
		return nil, errors.New("slug required")
	}
	if in.Status == "" {
		in.Status = "active"
	}
	if in.Tags == nil {
		in.Tags = []string{}
	}
	if in.Management == nil {
		in.Management = []string{}
	}
	if in.ParentIDs == nil {
		in.ParentIDs = []string{}
	}
	metadata := in.Metadata
	if metadata == nil {
		metadata = map[string]any{}
	}
	var id string
	err := s.Pool.QueryRow(ctx, `
		insert into projax.items
			(kind, title, slug, parent_ids, content_md, status, pinned, start_time, end_time,
			 tags, management, metadata)
		values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
		returning id`,
		in.Kind, in.Title, in.Slug, in.ParentIDs, in.ContentMD, in.Status, in.Pinned, in.StartTime, in.EndTime,
		in.Tags, in.Management, metadata,
	).Scan(&id)
	if err != nil {
		return nil, fmt.Errorf("insert: %w", err)
	}
	return s.GetByID(ctx, id)
}

// UpdateInput captures the editable surface of an existing projax-native item.
type UpdateInput struct {
	Title      string
	Slug       string
	ParentIDs  []string
	ContentMD  string
	Status     string
	Pinned     bool
	Archived   bool
	StartTime  *time.Time
	EndTime    *time.Time
	Tags       []string
	Management []string
	// Phase 4d public-listing fields. Full-replace semantics on Update —
	// callers that want partial-update use UpdatePublic instead.
	Public             bool
	PublicDescription  string
	PublicLiveURL      string
	PublicSourceURL    string
	PublicScreenshots  []string
	// Phase 4f timeline-exclude. Full-replace; values 'todos' / 'events' /
	// 'docs' / 'creation'.
	TimelineExclude    []string
}

func (s *Store) Update(ctx context.Context, id string, in UpdateInput) (*Item, error) {
	if in.Tags == nil {
		in.Tags = []string{}
	}
	if in.Management == nil {
		in.Management = []string{}
	}
	if in.ParentIDs == nil {
		in.ParentIDs = []string{}
	}
	if in.PublicScreenshots == nil {
		in.PublicScreenshots = []string{}
	}
	if in.TimelineExclude == nil {
		in.TimelineExclude = []string{}
	}
	_, err := s.Pool.Exec(ctx, `
		update projax.items
		   set title=$2, slug=$3, parent_ids=$4, content_md=$5,
		       status=$6, pinned=$7, archived=$8, start_time=$9, end_time=$10,
		       tags=$11, management=$12,
		       public=$13, public_description=$14, public_live_url=$15,
		       public_source_url=$16, public_screenshots=$17,
		       timeline_exclude=$18
		 where id=$1 and deleted_at is null`,
		id, in.Title, in.Slug, in.ParentIDs, in.ContentMD,
		in.Status, in.Pinned, in.Archived, in.StartTime, in.EndTime,
		in.Tags, in.Management,
		in.Public, in.PublicDescription, in.PublicLiveURL,
		in.PublicSourceURL, in.PublicScreenshots,
		in.TimelineExclude,
	)
	if err != nil {
		return nil, fmt.Errorf("update: %w", err)
	}
	return s.GetByID(ctx, id)
}

// SetPublic flips just the public boolean on selected items. Used by the
// /admin/bulk "Make public" / "Make private" actions so callers don't have
// to round-trip every other field.
func (s *Store) SetPublic(ctx context.Context, ids []string, public bool) error {
	if len(ids) == 0 {
		return nil
	}
	_, err := s.Pool.Exec(ctx,
		`update projax.items set public = $2 where id = any($1::uuid[]) and deleted_at is null`,
		ids, public)
	return err
}

// SetPinned flips just the pinned boolean on selected items. Used by the
// dashboard Tiles view's star toggle so a tile flip doesn't have to
// re-send every other item field. Same minimal-write shape as SetPublic.
func (s *Store) SetPinned(ctx context.Context, ids []string, pinned bool) error {
	if len(ids) == 0 {
		return nil
	}
	_, err := s.Pool.Exec(ctx,
		`update projax.items set pinned = $2 where id = any($1::uuid[]) and deleted_at is null`,
		ids, pinned)
	return err
}

// Reparent replaces parent_ids entirely with the given set. Used by the
// classify page's inline form and any "move to under X" action.
func (s *Store) Reparent(ctx context.Context, id string, parentIDs []string) (*Item, error) {
	if parentIDs == nil {
		parentIDs = []string{}
	}
	_, err := s.Pool.Exec(ctx,
		`update projax.items set parent_ids = $2 where id = $1 and deleted_at is null`,
		id, parentIDs,
	)
	if err != nil {
		return nil, fmt.Errorf("reparent: %w", err)
	}
	return s.GetByID(ctx, id)
}

// AddParent appends a parent without disturbing existing ones — used by the
// multi-parent UI to surface a project under a second branch.
func (s *Store) AddParent(ctx context.Context, id, parentID string) (*Item, error) {
	_, err := s.Pool.Exec(ctx, `
		update projax.items
		   set parent_ids = case
		     when $2::uuid = any(parent_ids) then parent_ids
		     else array_append(parent_ids, $2::uuid)
		   end
		 where id = $1 and deleted_at is null`,
		id, parentID,
	)
	if err != nil {
		return nil, fmt.Errorf("add parent: %w", err)
	}
	return s.GetByID(ctx, id)
}

// ItemLink mirrors a projax.item_links row — external pointer attached to
// an item (calendar URL, gitea repo, mai project id, …).
type ItemLink struct {
	ID        string
	ItemID    string
	RefType   string
	RefID     string
	Rel       string
	Note      *string
	Metadata  map[string]any
	CreatedAt time.Time
	// EventDate, when non-nil, anchors the link to a calendar day — the
	// backing slot for the YYMMDD segment of the PER standard. Day
	// granularity by design; time-of-day is intentionally out of scope.
	EventDate *time.Time
}

// LinksByType returns every item_link of the given ref_type for one item.
func (s *Store) LinksByType(ctx context.Context, itemID, refType string) ([]*ItemLink, error) {
	rows, err := s.Pool.Query(ctx, `
		select id, item_id, ref_type, ref_id, rel, note, metadata, created_at, event_date
		  from projax.item_links
		 where item_id = $1 and ref_type = $2
		 order by created_at`, itemID, refType)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []*ItemLink
	for rows.Next() {
		var l ItemLink
		if err := rows.Scan(&l.ID, &l.ItemID, &l.RefType, &l.RefID, &l.Rel, &l.Note, &l.Metadata, &l.CreatedAt, &l.EventDate); err != nil {
			return nil, err
		}
		out = append(out, &l)
	}
	return out, rows.Err()
}

// LinksByRefType returns every item_link of the given ref_type across the
// whole schema. Used by /admin/caldav to find already-linked calendars.
func (s *Store) LinksByRefType(ctx context.Context, refType string) ([]*ItemLink, error) {
	rows, err := s.Pool.Query(ctx, `
		select id, item_id, ref_type, ref_id, rel, note, metadata, created_at, event_date
		  from projax.item_links
		 where ref_type = $1
		 order by created_at`, refType)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []*ItemLink
	for rows.Next() {
		var l ItemLink
		if err := rows.Scan(&l.ID, &l.ItemID, &l.RefType, &l.RefID, &l.Rel, &l.Note, &l.Metadata, &l.CreatedAt, &l.EventDate); err != nil {
			return nil, err
		}
		out = append(out, &l)
	}
	return out, rows.Err()
}

// AddLink inserts an item_link. ON CONFLICT (item_id, ref_type, ref_id, rel)
// the existing row is returned untouched.
func (s *Store) AddLink(ctx context.Context, itemID, refType, refID, rel string, metadata map[string]any) (*ItemLink, error) {
	if rel == "" {
		rel = "contains"
	}
	if metadata == nil {
		metadata = map[string]any{}
	}
	var id string
	err := s.Pool.QueryRow(ctx, `
		insert into projax.item_links (item_id, ref_type, ref_id, rel, metadata)
		values ($1, $2, $3, $4, $5)
		on conflict (item_id, ref_type, ref_id, rel) do update set metadata = excluded.metadata
		returning id`,
		itemID, refType, refID, rel, metadata,
	).Scan(&id)
	if err != nil {
		return nil, fmt.Errorf("add link: %w", err)
	}
	row := s.Pool.QueryRow(ctx, `
		select id, item_id, ref_type, ref_id, rel, note, metadata, created_at, event_date
		  from projax.item_links where id = $1`, id)
	var l ItemLink
	if err := row.Scan(&l.ID, &l.ItemID, &l.RefType, &l.RefID, &l.Rel, &l.Note, &l.Metadata, &l.CreatedAt, &l.EventDate); err != nil {
		return nil, err
	}
	return &l, nil
}

// AddLinkDated is AddLink + an event_date + an explicit note. Existing
// AddLink callers leave the date unset; the new MCP add_link tool and the
// Documents UI pass it through.
func (s *Store) AddLinkDated(ctx context.Context, itemID, refType, refID, rel string, note *string, eventDate *time.Time, metadata map[string]any) (*ItemLink, error) {
	if rel == "" {
		rel = "contains"
	}
	if metadata == nil {
		metadata = map[string]any{}
	}
	var id string
	err := s.Pool.QueryRow(ctx, `
		insert into projax.item_links (item_id, ref_type, ref_id, rel, note, metadata, event_date)
		values ($1, $2, $3, $4, $5, $6, $7)
		on conflict (item_id, ref_type, ref_id, rel) do update
		  set metadata   = excluded.metadata,
		      note       = coalesce(excluded.note, projax.item_links.note),
		      event_date = coalesce(excluded.event_date, projax.item_links.event_date)
		returning id`,
		itemID, refType, refID, rel, note, metadata, eventDate,
	).Scan(&id)
	if err != nil {
		return nil, fmt.Errorf("add link (dated): %w", err)
	}
	row := s.Pool.QueryRow(ctx, `
		select id, item_id, ref_type, ref_id, rel, note, metadata, created_at, event_date
		  from projax.item_links where id = $1`, id)
	var l ItemLink
	if err := row.Scan(&l.ID, &l.ItemID, &l.RefType, &l.RefID, &l.Rel, &l.Note, &l.Metadata, &l.CreatedAt, &l.EventDate); err != nil {
		return nil, err
	}
	return &l, nil
}

// RecentDocuments returns every dated item_link across the whole schema with
// event_date in [since, now], newest-first. Used by the /dashboard "Recent
// documents" card. Soft-deleted items are excluded — 0013's cascade trigger
// removes their links, so the join against projax.items is technically
// redundant but kept defensively to match the items_unified guarantee.
func (s *Store) RecentDocuments(ctx context.Context, since time.Time, limit int) ([]*ItemLinkWithItem, error) {
	if limit <= 0 {
		limit = 30
	}
	rows, err := s.Pool.Query(ctx, `
		select l.id, l.item_id, l.ref_type, l.ref_id, l.rel, l.note, l.metadata, l.created_at, l.event_date,
		       i.slug, i.title, i.paths
		  from projax.item_links l
		  join projax.items i on i.id = l.item_id and i.deleted_at is null
		 where l.event_date is not null
		   and l.event_date >= $1
		 order by l.event_date desc, l.created_at desc
		 limit $2`, since, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := []*ItemLinkWithItem{}
	for rows.Next() {
		var x ItemLinkWithItem
		if err := rows.Scan(
			&x.Link.ID, &x.Link.ItemID, &x.Link.RefType, &x.Link.RefID, &x.Link.Rel,
			&x.Link.Note, &x.Link.Metadata, &x.Link.CreatedAt, &x.Link.EventDate,
			&x.ItemSlug, &x.ItemTitle, &x.ItemPaths,
		); err != nil {
			return nil, err
		}
		out = append(out, &x)
	}
	return out, rows.Err()
}

// ItemLinkWithItem bundles an item_link with a thin slice of its parent
// item's fields — enough for the dashboard "Recent documents" row to render
// without a second store hop.
type ItemLinkWithItem struct {
	Link       ItemLink
	ItemSlug   string
	ItemTitle  string
	ItemPaths  []string
}

// PrimaryPath returns the first path of the bundled item, mirroring Item.PrimaryPath.
func (x *ItemLinkWithItem) PrimaryPath() string {
	if len(x.ItemPaths) == 0 {
		return ""
	}
	return x.ItemPaths[0]
}

// DatedLinksRange returns every dated item_link with event_date in [from, to)
// across the whole schema, joined to its parent item — same shape as
// RecentDocuments but with explicit bounds. Used by the /timeline view so
// dated docs/letters/URLs appear under their anchor date. Ordered by event_date
// asc (timeline groups by ascending day then we reorder at render time).
func (s *Store) DatedLinksRange(ctx context.Context, from, to time.Time) ([]*ItemLinkWithItem, error) {
	rows, err := s.Pool.Query(ctx, `
		select l.id, l.item_id, l.ref_type, l.ref_id, l.rel, l.note, l.metadata, l.created_at, l.event_date,
		       i.slug, i.title, i.paths
		  from projax.item_links l
		  join projax.items i on i.id = l.item_id and i.deleted_at is null
		 where l.event_date is not null
		   and l.event_date >= $1
		   and l.event_date <  $2
		 order by l.event_date asc, l.created_at asc`, from, to)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := []*ItemLinkWithItem{}
	for rows.Next() {
		var x ItemLinkWithItem
		if err := rows.Scan(
			&x.Link.ID, &x.Link.ItemID, &x.Link.RefType, &x.Link.RefID, &x.Link.Rel,
			&x.Link.Note, &x.Link.Metadata, &x.Link.CreatedAt, &x.Link.EventDate,
			&x.ItemSlug, &x.ItemTitle, &x.ItemPaths,
		); err != nil {
			return nil, err
		}
		out = append(out, &x)
	}
	return out, rows.Err()
}

// ItemsCreatedInRange returns every live projax.item created between [from, to).
// Used by the /timeline view to render small "added X to projax" markers in
// the spine.
func (s *Store) ItemsCreatedInRange(ctx context.Context, from, to time.Time) ([]*Item, error) {
	rows, err := s.Pool.Query(ctx,
		`select `+itemsUnifiedCols+` from projax.items_unified
		 where created_at >= $1 and created_at < $2
		 order by created_at asc`, from, to)
	if err != nil {
		return nil, err
	}
	return scanItems(rows)
}

// DatedLinks returns every item_link with an event_date set, ordered
// newest-first then by insertion order. Used by the detail-page Documents
// section.
func (s *Store) DatedLinks(ctx context.Context, itemID string) ([]*ItemLink, error) {
	rows, err := s.Pool.Query(ctx, `
		select id, item_id, ref_type, ref_id, rel, note, metadata, created_at, event_date
		  from projax.item_links
		 where item_id = $1 and event_date is not null
		 order by event_date desc, created_at`, itemID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []*ItemLink
	for rows.Next() {
		var l ItemLink
		if err := rows.Scan(&l.ID, &l.ItemID, &l.RefType, &l.RefID, &l.Rel, &l.Note, &l.Metadata, &l.CreatedAt, &l.EventDate); err != nil {
			return nil, err
		}
		out = append(out, &l)
	}
	return out, rows.Err()
}

// DeleteLink removes a single item_link by id.
func (s *Store) DeleteLink(ctx context.Context, id string) error {
	_, err := s.Pool.Exec(ctx, `delete from projax.item_links where id = $1`, id)
	return err
}

// AllTags returns the deduplicated tag vocabulary in alphabetical order.
// Used by the tree page filter chips.
func (s *Store) AllTags(ctx context.Context) ([]string, error) {
	rows, err := s.Pool.Query(ctx,
		`select distinct unnest(tags) as tag from projax.items where deleted_at is null order by tag`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []string
	for rows.Next() {
		var t string
		if err := rows.Scan(&t); err != nil {
			return nil, err
		}
		out = append(out, t)
	}
	return out, rows.Err()
}

// SoftDelete marks a projax-native item deleted_at = now().
func (s *Store) SoftDelete(ctx context.Context, id string) error {
	_, err := s.Pool.Exec(ctx, `update projax.items set deleted_at = now() where id = $1`, id)
	return err
}

// ErrHasLiveChildren is returned by SoftDeleteCascade when the caller did not
// request cascade=true and the item has at least one undeleted descendant.
var ErrHasLiveChildren = errors.New("projax: item has live children — pass cascade=true to soft-delete the whole subtree")

// SoftDeleteCascade soft-deletes the item and, if cascade is true, every
// descendant (any row whose paths array contains a prefix matching this
// item's primary path). Without cascade, it refuses when there is at least
// one live descendant.
func (s *Store) SoftDeleteCascade(ctx context.Context, id string, cascade bool) error {
	it, err := s.GetByID(ctx, id)
	if err != nil {
		return err
	}
	primary := it.PrimaryPath()
	// Children are any other live row that names this id in their parent_ids
	// (direct children) or has a path with the primary path as a `.`-prefix
	// (transitive descendants).
	var childCount int
	err = s.Pool.QueryRow(ctx, `
		select count(*) from projax.items
		 where deleted_at is null
		   and id <> $1
		   and ($1::uuid = any(parent_ids)
		        or exists (select 1 from unnest(paths) p where p like $2 || '.%'))
		`, id, primary).Scan(&childCount)
	if err != nil {
		return fmt.Errorf("count children: %w", err)
	}
	if childCount > 0 && !cascade {
		return ErrHasLiveChildren
	}
	if childCount > 0 && cascade {
		_, err := s.Pool.Exec(ctx, `
			update projax.items set deleted_at = now()
			 where deleted_at is null
			   and ($1::uuid = any(parent_ids)
			        or exists (select 1 from unnest(paths) p where p like $2 || '.%'))`,
			id, primary)
		if err != nil {
			return fmt.Errorf("cascade soft-delete: %w", err)
		}
	}
	return s.SoftDelete(ctx, id)
}

// GetByPathOrSlug resolves a single item by either a dot path (any entry in
// paths) or a bare root slug. Slug-only inputs match items whose paths array
// contains exactly the slug (i.e. root items) as well as a fallback unique
// slug match — useful for MCP callers that don't know the path.
func (s *Store) GetByPathOrSlug(ctx context.Context, key string) (*Item, error) {
	key = sanitizeKey(key)
	if key == "" {
		return nil, ErrNotFound
	}
	row := s.Pool.QueryRow(ctx, `select `+itemsUnifiedCols+`
		from projax.items_unified u
		 where ($1 = any(u.paths) or u.slug = $1)
		 order by case when $1 = any(u.paths) then 0 else 1 end
		 limit 1`, key)
	it, err := scanItem(row)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return nil, ErrNotFound
		}
		return nil, err
	}
	return it, nil
}

// SearchFilters narrows ListByFilters. Each field is treated independently;
// the predicates AND together. Empty fields are no-ops.
type SearchFilters struct {
	ParentPath string   // any item whose paths array contains a path beginning with this prefix
	Tags       []string // ALL must be present on the item
	Management []string // ALL must be present on the item
	Kind       []string // ANY must be present on the item
	Status     string   // exact match (active|done|archived)
	Q          string   // ILIKE prefix-match on title/slug/aliases/content_md
	HasRepo    *bool    // if non-nil, item must (or must not) have a gitea-repo link
	HasCalDAV  *bool    // if non-nil, item must (or must not) have a caldav-list link
	Public     *bool    // if non-nil, item must (or must not) be marked public — Phase 4d
	Limit      int      // 0 → no limit
}

// ListByFilters returns items_unified rows matching all supplied predicates.
// Used by the MCP list_items tool.
func (s *Store) ListByFilters(ctx context.Context, f SearchFilters) ([]*Item, error) {
	conds := []string{"true"}
	args := []any{}
	addArg := func(v any) string {
		args = append(args, v)
		return fmt.Sprintf("$%d", len(args))
	}
	if f.ParentPath != "" {
		p := addArg(f.ParentPath)
		// Path equals or starts with `<parent>.`
		conds = append(conds, fmt.Sprintf("exists (select 1 from unnest(u.paths) pp where pp = %s or pp like %s || '.%%')", p, p))
	}
	if len(f.Tags) > 0 {
		conds = append(conds, fmt.Sprintf("u.tags @> %s::text[]", addArg(f.Tags)))
	}
	if len(f.Management) > 0 {
		conds = append(conds, fmt.Sprintf("u.management @> %s::text[]", addArg(f.Management)))
	}
	if len(f.Kind) > 0 {
		conds = append(conds, fmt.Sprintf("u.kind && %s::text[]", addArg(f.Kind)))
	}
	if f.Status != "" {
		conds = append(conds, fmt.Sprintf("u.status = %s", addArg(f.Status)))
	}
	if f.Q != "" {
		q := addArg("%" + f.Q + "%")
		conds = append(conds, fmt.Sprintf("(u.title ilike %s or u.slug ilike %s or u.content_md ilike %s or exists (select 1 from unnest(u.aliases) a where a ilike %s))", q, q, q, q))
	}
	if f.HasRepo != nil {
		op := ""
		if *f.HasRepo {
			op = "exists"
		} else {
			op = "not exists"
		}
		conds = append(conds, fmt.Sprintf("%s (select 1 from projax.item_links l where l.item_id = u.id and l.ref_type = 'gitea-repo')", op))
	}
	if f.HasCalDAV != nil {
		op := ""
		if *f.HasCalDAV {
			op = "exists"
		} else {
			op = "not exists"
		}
		conds = append(conds, fmt.Sprintf("%s (select 1 from projax.item_links l where l.item_id = u.id and l.ref_type = 'caldav-list')", op))
	}
	if f.Public != nil {
		conds = append(conds, fmt.Sprintf("u.public = %s", addArg(*f.Public)))
	}
	q := `select ` + itemsUnifiedCols + ` from projax.items_unified u where ` + joinAnd(conds) + ` order by u.paths[1] nulls last, u.slug`
	if f.Limit > 0 {
		q += fmt.Sprintf(" limit %d", f.Limit)
	}
	rows, err := s.Pool.Query(ctx, q, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanItems(rows)
}

// Search returns ranked items_unified rows matching the query. Match buckets:
// (0) exact slug, (1) title ILIKE prefix, (2) title contains, (3) alias hit,
// (4) content_md contains. Within each bucket rows are sorted by primary path.
func (s *Store) Search(ctx context.Context, q string, limit int) ([]*Item, error) {
	q = sanitizeKey(q)
	if q == "" {
		return nil, nil
	}
	if limit <= 0 || limit > 200 {
		limit = 50
	}
	// Ranking is computed in SQL with a virtual `match_rank`, then we re-select
	// just the canonical column set so scanItems handles the row.
	sql := `with ranked as (
		  select u.*,
		    case
		      when u.slug = $1 then 0
		      when u.title ilike $1 || '%' then 1
		      when u.title ilike '%' || $1 || '%' then 2
		      when exists (select 1 from unnest(u.aliases) a where a ilike '%' || $1 || '%') then 3
		      when u.content_md ilike '%' || $1 || '%' then 4
		      else 5
		    end as match_rank
		    from projax.items_unified u
		   where true
		     and (
		       u.slug = $1
		       or u.title ilike '%' || $1 || '%'
		       or u.content_md ilike '%' || $1 || '%'
		       or exists (select 1 from unnest(u.aliases) a where a ilike '%' || $1 || '%')
		     )
		)
		select ` + itemsUnifiedCols + `
		  from ranked
		 order by match_rank, paths[1] nulls last, slug
		 limit $2`
	rows, err := s.Pool.Query(ctx, sql, q, limit)
	if err != nil {
		return nil, err
	}
	return scanItems(rows)
}

// sanitizeKey trims and rejects NUL / control characters that the planner
// would otherwise have to deal with.
func sanitizeKey(s string) string {
	s = trimSpace(s)
	for _, r := range s {
		if r == 0 || (r < 0x20 && r != '\t') {
			return ""
		}
	}
	return s
}

func trimSpace(s string) string {
	for len(s) > 0 && (s[0] == ' ' || s[0] == '\t' || s[0] == '\n' || s[0] == '\r') {
		s = s[1:]
	}
	for len(s) > 0 && (s[len(s)-1] == ' ' || s[len(s)-1] == '\t' || s[len(s)-1] == '\n' || s[len(s)-1] == '\r') {
		s = s[:len(s)-1]
	}
	return s
}

func joinAnd(parts []string) string {
	out := ""
	for i, p := range parts {
		if i > 0 {
			out += " and "
		}
		out += p
	}
	return out
}