-- Task decomposition + dependency graph (autonomy roadmap §10).
-- Additive only (follows the existing convention where 0003/0015 patched issues).

-- ============ issues: parent_id + priority ============
-- parent_id: self-FK for subtask hierarchy. ON DELETE SET NULL so deleting a
-- parent orphans (not cascades) its subtasks. CHECK forbids self-parent.
ALTER TABLE issues
  ADD COLUMN parent_id UUID REFERENCES issues(id) ON DELETE SET NULL;
ALTER TABLE issues
  ADD CONSTRAINT issues_parent_not_self CHECK (parent_id IS NULL OR parent_id <> id);

-- priority: higher = scheduled sooner. 0..3 (0 default = normal).
ALTER TABLE issues
  ADD COLUMN priority INT NOT NULL DEFAULT 0;
ALTER TABLE issues
  ADD CONSTRAINT issues_priority_check CHECK (priority BETWEEN 0 AND 3);

CREATE INDEX idx_issues_parent ON issues(parent_id) WHERE parent_id IS NOT NULL;

-- ============ issue_dependencies ============
-- Semantics: blocked_id is blocked-by blocker_id; blocked_id becomes ready only
-- when blocker_id.status='closed'. project_id denormalized for scope/cycle scoping.
CREATE TABLE issue_dependencies (
  id          UUID PRIMARY KEY,
  project_id  UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  blocked_id  UUID NOT NULL REFERENCES issues(id)   ON DELETE CASCADE,
  blocker_id  UUID NOT NULL REFERENCES issues(id)   ON DELETE CASCADE,
  created_by  UUID NOT NULL REFERENCES users(id)    ON DELETE RESTRICT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (blocked_id, blocker_id),
  CONSTRAINT issue_deps_not_self CHECK (blocked_id <> blocker_id)
);
CREATE INDEX idx_issue_deps_blocked ON issue_dependencies(blocked_id);
CREATE INDEX idx_issue_deps_blocker ON issue_dependencies(blocker_id);