-- SunaivaCore Platform Database Initialization
-- Create database schema for multi-LLM orchestration platform

-- Memory management tables
CREATE TABLE IF NOT EXISTS memory_chunks (
    id SERIAL PRIMARY KEY,
    session_id VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    metadata JSONB DEFAULT '{}',
    embedding VECTOR(1536),
    importance_score FLOAT DEFAULT 0.0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_session_id (session_id),
    INDEX idx_importance (importance_score),
    INDEX idx_created_at (created_at)
);

-- Context relationships
CREATE TABLE IF NOT EXISTS context_relationships (
    id SERIAL PRIMARY KEY,
    source_chunk_id INTEGER REFERENCES memory_chunks(id) ON DELETE CASCADE,
    target_chunk_id INTEGER REFERENCES memory_chunks(id) ON DELETE CASCADE,
    relationship_type VARCHAR(50) DEFAULT 'related',
    strength FLOAT DEFAULT 0.5,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(source_chunk_id, target_chunk_id)
);

-- LLM routing logs
CREATE TABLE IF NOT EXISTS llm_routing_logs (
    id SERIAL PRIMARY KEY,
    session_id VARCHAR(255) NOT NULL,
    model_used VARCHAR(100) NOT NULL,
    input_tokens INTEGER DEFAULT 0,
    output_tokens INTEGER DEFAULT 0,
    response_time_ms INTEGER DEFAULT 0,
    success BOOLEAN DEFAULT true,
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_session_routing (session_id),
    INDEX idx_model_used (model_used)
);

-- Compliance audit trail
CREATE TABLE IF NOT EXISTS compliance_logs (
    id SERIAL PRIMARY KEY,
    session_id VARCHAR(255) NOT NULL,
    action VARCHAR(100) NOT NULL,
    resource_type VARCHAR(50) NOT NULL,
    resource_id VARCHAR(255),
    user_context JSONB DEFAULT '{}',
    compliance_status VARCHAR(20) DEFAULT 'approved',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_session_compliance (session_id),
    INDEX idx_compliance_status (compliance_status)
);

-- System health metrics
CREATE TABLE IF NOT EXISTS health_metrics (
    id SERIAL PRIMARY KEY,
    service_name VARCHAR(100) NOT NULL,
    metric_name VARCHAR(100) NOT NULL,
    metric_value FLOAT NOT NULL,
    unit VARCHAR(20),
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_service_metric (service_name, metric_name),
    INDEX idx_timestamp (timestamp)
);

-- Voice interaction logs
CREATE TABLE IF NOT EXISTS voice_interactions (
    id SERIAL PRIMARY KEY,
    session_id VARCHAR(255) NOT NULL,
    interaction_type VARCHAR(50) NOT NULL, -- 'speech_to_text', 'text_to_speech', 'voice_command'
    input_data TEXT,
    output_data TEXT,
    processing_time_ms INTEGER DEFAULT 0,
    success BOOLEAN DEFAULT true,
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_session_voice (session_id),
    INDEX idx_interaction_type (interaction_type)
);

-- Initialize default data
INSERT INTO health_metrics (service_name, metric_name, metric_value, unit) VALUES
('memory-server', 'startup_time', 0, 'seconds'),
('context-server', 'startup_time', 0, 'seconds'),
('llm-router-server', 'startup_time', 0, 'seconds'),
('compliance-server', 'startup_time', 0, 'seconds')
ON CONFLICT DO NOTHING;

-- Create indexes for performance
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_memory_chunks_embedding ON memory_chunks USING ivfflat (embedding vector_cosine_ops);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_memory_full_text ON memory_chunks USING GIN (to_tsvector('english', content));

-- Grant permissions
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO sunaivacore;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO sunaivacore; 