-- Tmp directive system: every task must belong to a directive going forward.
--
-- Background: the unified-surface UI previously surfaced "orphan" tasks
-- (tasks with directive_id NULL) under a synthetic /tmp/ folder. That
-- accumulated stale junk over time and made the UI noisy. The new model:
--
--   * Add `is_tmp` to directives — at most one per owner, marks the
--     special "scratchpad" directive that holds otherwise-orphan tasks.
--   * Delete every existing orphan task. The user explicitly asked for
--     a clean slate: "ALSO there are TOO MANY old tasks in tmp, we need
--     to remove all of them as well."
--   * Going forward, ephemeral / standalone task creation paths attach
--     to the caller's tmp directive (auto-created on first use by the
--     repository helper, not by this migration — owners may not exist
--     yet at migration time, but every owner gets one as soon as a
--     standalone task is requested).
--   * A 30-day expiry sweep in the directive reconciler deletes tasks
--     in tmp directives once they age out.

-- 1. New flag column on directives. Default false; only set true on the
--    auto-created scratchpad directive.
ALTER TABLE directives
    ADD COLUMN is_tmp BOOLEAN NOT NULL DEFAULT false;

-- Partial unique index — at most ONE tmp directive per owner.
CREATE UNIQUE INDEX idx_directives_owner_tmp_unique
    ON directives(owner_id)
    WHERE is_tmp;

-- 2. Clear out every existing orphan task. Per the user's spec these are
--    discardable scratch work; pre-existing valuable tasks are already
--    attached to a directive and will not be touched.
--
--    Cascades: task_events delete via FK; daemon links go to NULL; nothing
--    in the contracts/directive_steps tables references orphan tasks (a
--    contract-backed step always has a directive_id by construction).
DELETE FROM tasks
 WHERE directive_id IS NULL
   AND parent_task_id IS NULL;