-- Migration 008: Saga State Store
-- Stores saga orchestration state for long-running business processes

CREATE TABLE IF NOT EXISTS saga_states (
    saga_id TEXT NOT NULL,
    correlation_id TEXT NOT NULL,
    saga_name TEXT NOT NULL,
    state INT NOT NULL,  -- 0=NotStarted, 1=Running, 2=Completed, 3=Compensating, 4=Compensated, 5=Failed, 6=Paused
    current_step INT NOT NULL DEFAULT 0,
    total_steps INT NOT NULL DEFAULT 0,
    completed_steps JSONB NOT NULL DEFAULT '[]'::jsonb,
    started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_updated TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    completed_at TIMESTAMPTZ NULL,
    error_message TEXT NULL,
    data JSONB NOT NULL DEFAULT '{}'::jsonb,

    CONSTRAINT pk_saga_states PRIMARY KEY (saga_id)
);

-- Index for querying by correlation ID
CREATE INDEX IF NOT EXISTS idx_saga_states_correlation_id
    ON saga_states (correlation_id);

-- Index for querying by saga name
CREATE INDEX IF NOT EXISTS idx_saga_states_saga_name
    ON saga_states (saga_name);

-- Index for querying by state
CREATE INDEX IF NOT EXISTS idx_saga_states_state
    ON saga_states (state);

-- Index for querying active sagas (Running, Paused, Compensating)
CREATE INDEX IF NOT EXISTS idx_saga_states_active
    ON saga_states (state)
    WHERE state IN (1, 3, 6);