-- Enhanced supervisor state persistence for restoration after crashes.
-- Adds additional fields to supervisor_states to track detailed state for recovery.

-- Add state tracking field (matches SupervisorStateEnum: initializing, idle, working,
-- waiting_for_user, waiting_for_tasks, blocked, completed, failed, interrupted)
ALTER TABLE supervisor_states
    ADD COLUMN IF NOT EXISTS state VARCHAR(50) NOT NULL DEFAULT 'initializing';

-- Add current activity description for monitoring
ALTER TABLE supervisor_states
    ADD COLUMN IF NOT EXISTS current_activity TEXT;

-- Add progress percentage (0-100)
ALTER TABLE supervisor_states
    ADD COLUMN IF NOT EXISTS progress INTEGER DEFAULT 0
    CHECK (progress >= 0 AND progress <= 100);

-- Add error message for failed states
ALTER TABLE supervisor_states
    ADD COLUMN IF NOT EXISTS error_message TEXT;

-- Add spawned task IDs (tasks this supervisor has created)
ALTER TABLE supervisor_states
    ADD COLUMN IF NOT EXISTS spawned_task_ids UUID[] DEFAULT ARRAY[]::UUID[];

-- Add pending questions (questions waiting for user response)
ALTER TABLE supervisor_states
    ADD COLUMN IF NOT EXISTS pending_questions JSONB DEFAULT '[]';

-- Add restoration metadata
ALTER TABLE supervisor_states
    ADD COLUMN IF NOT EXISTS restoration_count INTEGER DEFAULT 0;

ALTER TABLE supervisor_states
    ADD COLUMN IF NOT EXISTS last_restored_at TIMESTAMPTZ;

ALTER TABLE supervisor_states
    ADD COLUMN IF NOT EXISTS restoration_source VARCHAR(50);

-- Index for finding supervisors by state (useful for finding blocked/failed supervisors)
CREATE INDEX IF NOT EXISTS idx_supervisor_states_state ON supervisor_states(state);

-- Index for finding supervisors with pending questions
CREATE INDEX IF NOT EXISTS idx_supervisor_states_pending_questions
    ON supervisor_states USING gin(pending_questions)
    WHERE pending_questions != '[]'::jsonb;

COMMENT ON COLUMN supervisor_states.state IS 'Current supervisor state: initializing, idle, working, waiting_for_user, waiting_for_tasks, blocked, completed, failed, interrupted';
COMMENT ON COLUMN supervisor_states.current_activity IS 'Human-readable description of current activity';
COMMENT ON COLUMN supervisor_states.progress IS 'Progress percentage (0-100)';
COMMENT ON COLUMN supervisor_states.error_message IS 'Error message when state is failed or blocked';
COMMENT ON COLUMN supervisor_states.spawned_task_ids IS 'Array of task UUIDs spawned by this supervisor';
COMMENT ON COLUMN supervisor_states.pending_questions IS 'Array of questions awaiting user response: [{id, question, choices, context, asked_at}]';
COMMENT ON COLUMN supervisor_states.restoration_count IS 'Number of times this supervisor has been restored after interruption';
COMMENT ON COLUMN supervisor_states.last_restored_at IS 'Timestamp of last restoration';
COMMENT ON COLUMN supervisor_states.restoration_source IS 'Source of last restoration: daemon_restart, task_reassignment, manual';