-- Migration: 001_initial_schema.sql
-- Description: Initial schema for AIVA Voice Command Bridge System

CREATE SCHEMA IF NOT EXISTS genesis_bridge;

-- Command Queue: Core messaging table between Kinan and Claude
CREATE TABLE IF NOT EXISTS genesis_bridge.command_queue (
    id BIGSERIAL PRIMARY KEY,
    direction VARCHAR(10) NOT NULL CHECK (direction IN ('inbound', 'outbound')),
    priority INTEGER DEFAULT 5 CHECK (priority BETWEEN 1 AND 10),
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'failed', 'expired')),
    command_type VARCHAR(50) NOT NULL,
    payload JSONB NOT NULL,
    source VARCHAR(50) NOT NULL,
    target VARCHAR(50) NOT NULL,
    metadata JSONB DEFAULT '{}',
    error_message TEXT,
    retry_count INTEGER DEFAULT 0,
    max_retries INTEGER DEFAULT 3,
    expires_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    completed_at TIMESTAMPTZ,
    
    CONSTRAINT valid_direction CHECK (direction IN ('inbound', 'outbound'))
);

-- Performance indexes
CREATE INDEX IF NOT EXISTS idx_command_queue_status_direction ON genesis_bridge.command_queue (status, direction, priority DESC, created_at);
CREATE INDEX IF NOT EXISTS idx_command_queue_source ON genesis_bridge.command_queue (source);
CREATE INDEX IF NOT EXISTS idx_command_queue_target ON genesis_bridge.command_queue (target);
CREATE INDEX IF NOT EXISTS idx_command_queue_created_at ON genesis_bridge.command_queue (created_at);
CREATE INDEX IF NOT EXISTS idx_command_queue_expires_pending ON genesis_bridge.command_queue (expires_at) WHERE status = 'pending';

-- Trigger function for auto-updating updated_at
CREATE OR REPLACE FUNCTION genesis_bridge.update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply trigger to command_queue
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_trigger 
        WHERE tgname = 'trg_command_queue_updated_at'
        AND tgrelid = 'genesis_bridge.command_queue'::regclass
    ) THEN
        CREATE TRIGGER trg_command_queue_updated_at
        BEFORE UPDATE ON genesis_bridge.command_queue
        FOR EACH ROW
        EXECUTE FUNCTION genesis_bridge.update_updated_at_column();
    END IF;
END $$;

-- Bridge Sessions: Track active voice sessions
CREATE TABLE IF NOT EXISTS genesis_bridge.bridge_sessions (
    id BIGSERIAL PRIMARY KEY,
    session_id VARCHAR(100) UNIQUE NOT NULL,
    phone_number VARCHAR(20),
    telnyx_call_control_id VARCHAR(100),
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'closed')),
    started_at TIMESTAMPTZ DEFAULT NOW(),
    last_activity_at TIMESTAMPTZ DEFAULT NOW(),
    ended_at TIMESTAMPTZ,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_bridge_sessions_status ON genesis_bridge.bridge_sessions (status);
CREATE INDEX IF NOT EXISTS idx_bridge_sessions_session_id ON genesis_bridge.bridge_sessions (session_id);
CREATE INDEX IF NOT EXISTS idx_bridge_sessions_phone ON genesis_bridge.bridge_sessions (phone_number);

-- Trigger for bridge_sessions updated_at
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_trigger 
        WHERE tgname = 'trg_bridge_sessions_updated_at'
        AND tgrelid = 'genesis_bridge.bridge_sessions'::regclass
    ) THEN
        CREATE TRIGGER trg_bridge_sessions_updated_at
        BEFORE UPDATE ON genesis_bridge.bridge_sessions
        FOR EACH ROW
        EXECUTE FUNCTION genesis_bridge.update_updated_at_column();
    END IF;
END $$;

-- Audit Log: Track all operations
CREATE TABLE IF NOT EXISTS genesis_bridge.audit_log (
    id BIGSERIAL PRIMARY KEY,
    table_name VARCHAR(50) NOT NULL,
    record_id BIGINT,
    operation VARCHAR(10) NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    old_data JSONB,
    new_data JSONB,
    performed_by VARCHAR(100),
    session_id VARCHAR(100),
    performed_at TIMESTAMPTZ DEFAULT NOW(),
    ip_address INET
);

CREATE INDEX IF NOT EXISTS idx_audit_log_table_record ON genesis_bridge.audit_log (table_name, record_id);
CREATE INDEX IF NOT EXISTS idx_audit_log_performed_at ON genesis_bridge.audit_log (performed_at DESC);
CREATE INDEX IF NOT EXISTS idx_audit_log_operation ON genesis_bridge.audit_log (operation);

-- Schema migrations tracking
CREATE TABLE IF NOT EXISTS genesis_bridge.schema_migrations (
    id SERIAL PRIMARY KEY,
    version VARCHAR(50) UNIQUE NOT NULL,
    applied_at TIMESTAMPTZ DEFAULT NOW(),
    checksum VARCHAR(64),
    execution_time_ms INTEGER
);