-- Genesis Command Bridge Schema
-- Voice-to-Terminal directive relay between Kinan (via AIVA) and Claude Code
-- Deployed on Elestio PostgreSQL (postgresql-genesis-u50607.vm.elestio.app:25432)

CREATE SCHEMA IF NOT EXISTS genesis_bridge;

-- Command queue: bidirectional message relay
CREATE TABLE IF NOT EXISTS genesis_bridge.command_queue (
    id SERIAL PRIMARY KEY,
    direction TEXT NOT NULL CHECK (direction IN ('kinan_to_claude', 'claude_to_kinan')),
    message TEXT NOT NULL,
    priority TEXT DEFAULT 'normal' CHECK (priority IN ('low', 'normal', 'high', 'urgent')),
    status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'read', 'executed', 'acknowledged')),
    source TEXT DEFAULT 'aiva_voice',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    read_at TIMESTAMPTZ,
    executed_at TIMESTAMPTZ,
    response TEXT,
    metadata JSONB DEFAULT '{}'
);

-- Fast lookup for pending messages by direction
CREATE INDEX IF NOT EXISTS idx_command_queue_pending
    ON genesis_bridge.command_queue(direction, status)
    WHERE status = 'pending';

-- Time-based ordering index
CREATE INDEX IF NOT EXISTS idx_command_queue_created
    ON genesis_bridge.command_queue(created_at DESC);

-- Priority-based ordering for urgent directives
CREATE INDEX IF NOT EXISTS idx_command_queue_priority
    ON genesis_bridge.command_queue(priority, created_at)
    WHERE status = 'pending';

COMMENT ON TABLE genesis_bridge.command_queue IS 'Bidirectional command relay between Kinan (via AIVA voice) and Claude Code terminal';
COMMENT ON COLUMN genesis_bridge.command_queue.direction IS 'kinan_to_claude = directive from Kinan, claude_to_kinan = status update from Claude';
COMMENT ON COLUMN genesis_bridge.command_queue.priority IS 'urgent = immediate attention, high = important, normal = standard, low = when free';
COMMENT ON COLUMN genesis_bridge.command_queue.source IS 'Origin: aiva_voice, claude_terminal, manual';
