-- Agent Subtasks for Multi-Agent Orchestration (Phase 5.8)

CREATE TABLE IF NOT EXISTS agent_subtasks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    parent_agent_id UUID NOT NULL REFERENCES automation_agent_definitions(id) ON DELETE CASCADE,
    child_agent_id UUID NOT NULL REFERENCES automation_agent_definitions(id) ON DELETE CASCADE,
    task_description TEXT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',  -- pending, running, completed, failed, cancelled
    result JSONB NOT NULL DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    completed_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS ix_agent_subtasks_parent ON agent_subtasks (tenant_id, parent_agent_id);
CREATE INDEX IF NOT EXISTS ix_agent_subtasks_child ON agent_subtasks (tenant_id, child_agent_id);
CREATE INDEX IF NOT EXISTS ix_agent_subtasks_status ON agent_subtasks (tenant_id, status);