-- Create sessions table
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)
);

-- Create tenants table
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()
);

-- Create conversations table
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)
);

-- Create conversation_messages table
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, -- 'user' or 'assistant'
    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)
);