-- Add depth column for task hierarchy (0=top-level, 1=subtask, 2=sub-subtask)
-- Max depth is 2 (3 levels total)

ALTER TABLE tasks ADD COLUMN depth INTEGER NOT NULL DEFAULT 0;
ALTER TABLE tasks ADD CONSTRAINT tasks_depth_check CHECK (depth >= 0 AND depth < 3);

-- Backfill existing tasks based on parent chain
WITH RECURSIVE task_depth AS (
    SELECT id, parent_task_id, 0 as calculated_depth
    FROM tasks
    WHERE parent_task_id IS NULL
    UNION ALL
    SELECT t.id, t.parent_task_id, td.calculated_depth + 1
    FROM tasks t
    JOIN task_depth td ON t.parent_task_id = td.id
)
UPDATE tasks SET depth = task_depth.calculated_depth
FROM task_depth WHERE tasks.id = task_depth.id;

-- Index for depth queries
CREATE INDEX idx_tasks_depth ON tasks(depth);