-- Directive system v3: long-lived DAG-based project management
-- Directives are ongoing top-level entities (alternative to contracts) for managing
-- large, whole-repository projects via a DAG of auto-progressing tasks.

CREATE TABLE directives (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    owner_id UUID NOT NULL REFERENCES owners(id) ON DELETE CASCADE,
    title VARCHAR(500) NOT NULL,
    goal TEXT NOT NULL,
    -- Status: draft -> active <-> idle -> archived
    --   draft    = being planned, DAG not yet started
    --   active   = executing steps, orchestrator running
    --   idle     = all current steps done, waiting for new work
    --   paused   = user-paused execution
    --   archived = project finished, no longer active
    status VARCHAR(32) NOT NULL DEFAULT 'draft'
        CHECK (status IN ('draft', 'active', 'idle', 'paused', 'archived')),
    repository_url VARCHAR(512),
    local_path VARCHAR(512),
    base_branch VARCHAR(255),
    orchestrator_task_id UUID REFERENCES tasks(id) ON DELETE SET NULL,
    -- Tracks when the goal/requirements were last changed (orchestrator watches this)
    goal_updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    started_at TIMESTAMPTZ,
    version INTEGER NOT NULL DEFAULT 1,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE directive_steps (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    directive_id UUID NOT NULL REFERENCES directives(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    task_plan TEXT,
    depends_on UUID[] NOT NULL DEFAULT '{}',
    status VARCHAR(32) NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'ready', 'running', 'completed', 'failed', 'skipped')),
    task_id UUID REFERENCES tasks(id) ON DELETE SET NULL,
    order_index INTEGER NOT NULL DEFAULT 0,
    -- Which "generation" of planning created this step (for tracking re-plans)
    generation INTEGER NOT NULL DEFAULT 1,
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Link tasks back to directives
ALTER TABLE tasks ADD COLUMN directive_id UUID REFERENCES directives(id) ON DELETE SET NULL;
ALTER TABLE tasks ADD COLUMN directive_step_id UUID REFERENCES directive_steps(id) ON DELETE SET NULL;
