-- Create supervisor_heartbeats table for tracking supervisor state over time.
-- This enables detection of dead/stale supervisors and provides audit trail.

CREATE TABLE IF NOT EXISTS supervisor_heartbeats (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    supervisor_task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    contract_id UUID NOT NULL REFERENCES contracts(id) ON DELETE CASCADE,
    state VARCHAR(50) NOT NULL,
    phase VARCHAR(50) NOT NULL,
    current_activity TEXT,
    progress INTEGER DEFAULT 0 CHECK (progress >= 0 AND progress <= 100),
    pending_task_ids UUID[] DEFAULT ARRAY[]::UUID[],
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Index for finding heartbeats by supervisor task
CREATE INDEX idx_heartbeats_supervisor ON supervisor_heartbeats(supervisor_task_id);

-- Index for finding heartbeats by timestamp (for cleanup and monitoring)
CREATE INDEX idx_heartbeats_timestamp ON supervisor_heartbeats(timestamp);

-- Index for finding heartbeats by contract
CREATE INDEX idx_heartbeats_contract ON supervisor_heartbeats(contract_id);

-- Composite index for finding latest heartbeat per supervisor
CREATE INDEX idx_heartbeats_supervisor_timestamp ON supervisor_heartbeats(supervisor_task_id, timestamp DESC);

COMMENT ON TABLE supervisor_heartbeats IS 'Historical record of supervisor heartbeats for monitoring and dead supervisor detection';
COMMENT ON COLUMN supervisor_heartbeats.state IS 'Supervisor state: initializing, idle, working, waiting_for_user, waiting_for_tasks, blocked, completed, failed, interrupted';
COMMENT ON COLUMN supervisor_heartbeats.phase IS 'Current contract phase when heartbeat was sent';
COMMENT ON COLUMN supervisor_heartbeats.current_activity IS 'Human-readable description of what the supervisor is doing';
COMMENT ON COLUMN supervisor_heartbeats.progress IS 'Progress percentage (0-100)';
COMMENT ON COLUMN supervisor_heartbeats.pending_task_ids IS 'Array of task IDs the supervisor is waiting on';

-- Note: Cleanup of old heartbeats (24 hour TTL) should be done by a scheduled job
-- or application-level cleanup, not a CHECK constraint (which can't reference NOW())