-- Virtual Banker Database Schema
-- This file contains the complete schema for reference

-- Sessions
CREATE TABLE IF NOT EXISTS sessions (
    id VARCHAR(255) PRIMARY KEY,
    tenant_id VARCHAR(255) NOT NULL,
    user_id VARCHAR(255) NOT NULL,
    ephemeral_token VARCHAR(512) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    expires_at TIMESTAMP NOT NULL,
    last_activity_at TIMESTAMP NOT NULL DEFAULT NOW(),
    ended_at TIMESTAMP,
    INDEX idx_tenant_user (tenant_id, user_id),
    INDEX idx_expires_at (expires_at),
    INDEX idx_ended_at (ended_at)
);

-- Tenants
CREATE TABLE IF NOT EXISTS tenants (
    id VARCHAR(255) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    theme JSONB,
    avatar_enabled BOOLEAN DEFAULT true,
    greeting TEXT,
    allowed_tools JSONB DEFAULT '[]'::jsonb,
    policy JSONB,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Conversations
CREATE TABLE IF NOT EXISTS conversations (
    id VARCHAR(255) PRIMARY KEY,
    session_id VARCHAR(255) NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    user_id VARCHAR(255) NOT NULL,
    tenant_id VARCHAR(255) NOT NULL,
    started_at TIMESTAMP NOT NULL DEFAULT NOW(),
    ended_at TIMESTAMP,
    metadata JSONB,
    INDEX idx_session (session_id),
    INDEX idx_user (user_id),
    INDEX idx_tenant (tenant_id)
);

-- Conversation Messages
CREATE TABLE IF NOT EXISTS conversation_messages (
    id VARCHAR(255) PRIMARY KEY,
    conversation_id VARCHAR(255) NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
    role VARCHAR(50) NOT NULL,
    content TEXT NOT NULL,
    audio_url TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    metadata JSONB,
    INDEX idx_conversation (conversation_id),
    INDEX idx_created_at (created_at)
);

-- Conversation State
CREATE TABLE IF NOT EXISTS conversation_state (
    session_id VARCHAR(255) PRIMARY KEY REFERENCES sessions(id) ON DELETE CASCADE,
    workflow VARCHAR(255),
    step VARCHAR(255),
    context JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    expires_at TIMESTAMP
);

-- Knowledge Base (requires pgvector extension)
CREATE TABLE IF NOT EXISTS knowledge_base (
    id VARCHAR(255) PRIMARY KEY,
    tenant_id VARCHAR(255) NOT NULL,
    title VARCHAR(500),
    content TEXT NOT NULL,
    embedding vector(1536),
    metadata JSONB,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    INDEX idx_tenant (tenant_id)
);