-- History tables for Resume and History System
-- Enables conversation rewind, snapshots, and unified event timeline

-- 1. Conversation Snapshots table
-- Stores conversation state at specific points for rewind capability
CREATE TABLE IF NOT EXISTS conversation_snapshots (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    checkpoint_id UUID REFERENCES task_checkpoints(id) ON DELETE SET NULL,
    snapshot_type VARCHAR(50) NOT NULL,  -- 'auto', 'manual', 'checkpoint'
    message_count INTEGER NOT NULL,
    conversation_state JSONB NOT NULL,  -- Full conversation at this point
    metadata JSONB,  -- Additional context (token count, cost, etc.)
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_conversation_snapshots_task ON conversation_snapshots(task_id);
CREATE INDEX idx_conversation_snapshots_checkpoint ON conversation_snapshots(checkpoint_id);
CREATE INDEX idx_conversation_snapshots_created ON conversation_snapshots(created_at DESC);

-- 2. History Events table
-- Unified event stream for timeline views
CREATE TABLE IF NOT EXISTS history_events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    owner_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    contract_id UUID REFERENCES contracts(id) ON DELETE CASCADE,
    task_id UUID REFERENCES tasks(id) ON DELETE CASCADE,
    event_type VARCHAR(50) NOT NULL,  -- 'task', 'chat', 'checkpoint', 'phase', 'file'
    event_subtype VARCHAR(50),  -- Specific event: 'created', 'completed', 'message', etc.
    phase VARCHAR(50),  -- Contract phase when event occurred
    event_data JSONB NOT NULL,  -- Event-specific data
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_history_events_contract ON history_events(contract_id, created_at DESC);
CREATE INDEX idx_history_events_task ON history_events(task_id, created_at DESC);
CREATE INDEX idx_history_events_owner ON history_events(owner_id, created_at DESC);
CREATE INDEX idx_history_events_type ON history_events(event_type, created_at DESC);

-- 3. Alter task_checkpoints - add conversation snapshot reference
ALTER TABLE task_checkpoints
    ADD COLUMN IF NOT EXISTS conversation_snapshot_id UUID REFERENCES conversation_snapshots(id) ON DELETE SET NULL;

-- 4. Alter tasks - add forking fields
ALTER TABLE tasks
    ADD COLUMN IF NOT EXISTS forked_from_task_id UUID REFERENCES tasks(id) ON DELETE SET NULL,
    ADD COLUMN IF NOT EXISTS forked_at_checkpoint_id UUID REFERENCES task_checkpoints(id) ON DELETE SET NULL;

CREATE INDEX IF NOT EXISTS idx_tasks_forked_from ON tasks(forked_from_task_id) WHERE forked_from_task_id IS NOT NULL;

-- Comments for documentation
COMMENT ON TABLE conversation_snapshots IS 'Stores conversation state at specific points for rewind/resume capability';
COMMENT ON TABLE history_events IS 'Unified event stream for timeline views across contracts and tasks';
COMMENT ON COLUMN conversation_snapshots.snapshot_type IS 'Type: auto (periodic), manual (user-triggered), checkpoint (at git checkpoint)';
COMMENT ON COLUMN history_events.event_type IS 'Category: task, chat, checkpoint, phase, file';