-- ===========================================
-- 多租户迁移: conversation-service 表
-- 为 conversations, messages, token_usages 添加 tenant_id
-- 创建 agent_executions 表
-- 日期: 2026-02-06
-- ===========================================

-- ========== 1. 确保 tenants 表和默认租户存在 ==========
CREATE TABLE IF NOT EXISTS tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) NOT NULL UNIQUE,
    slug VARCHAR(50) NOT NULL UNIQUE,
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    plan VARCHAR(30) NOT NULL DEFAULT 'STANDARD',
    max_users INT NOT NULL DEFAULT 100,
    max_conversations_per_month INT NOT NULL DEFAULT 10000,
    max_storage_mb INT NOT NULL DEFAULT 5120,
    current_user_count INT NOT NULL DEFAULT 0,
    current_conversation_count INT NOT NULL DEFAULT 0,
    current_storage_bytes BIGINT NOT NULL DEFAULT 0,
    config JSONB NOT NULL DEFAULT '{}',
    billing_email VARCHAR(255),
    billing_name VARCHAR(100),
    billing_phone VARCHAR(20),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    suspended_at TIMESTAMPTZ
);

INSERT INTO tenants (id, name, slug, status, plan)
VALUES ('00000000-0000-0000-0000-000000000001', 'Default Tenant', 'default', 'ACTIVE', 'ENTERPRISE')
ON CONFLICT (id) DO NOTHING;

-- ========== 2. conversations 表 ==========
ALTER TABLE conversations ADD COLUMN IF NOT EXISTS tenant_id UUID;
UPDATE conversations SET tenant_id = '00000000-0000-0000-0000-000000000001' WHERE tenant_id IS NULL;
ALTER TABLE conversations ALTER COLUMN tenant_id SET NOT NULL;
ALTER TABLE conversations ALTER COLUMN tenant_id SET DEFAULT '00000000-0000-0000-0000-000000000001';
CREATE INDEX IF NOT EXISTS idx_conversations_tenant ON conversations(tenant_id);
CREATE INDEX IF NOT EXISTS idx_conversations_tenant_user ON conversations(tenant_id, user_id);
CREATE INDEX IF NOT EXISTS idx_conversations_tenant_status ON conversations(tenant_id, status);

-- ========== 3. messages 表 ==========
ALTER TABLE messages ADD COLUMN IF NOT EXISTS tenant_id UUID;
UPDATE messages SET tenant_id = '00000000-0000-0000-0000-000000000001' WHERE tenant_id IS NULL;
ALTER TABLE messages ALTER COLUMN tenant_id SET NOT NULL;
ALTER TABLE messages ALTER COLUMN tenant_id SET DEFAULT '00000000-0000-0000-0000-000000000001';
CREATE INDEX IF NOT EXISTS idx_messages_tenant ON messages(tenant_id);

-- ========== 4. token_usages 表 ==========
ALTER TABLE token_usages ADD COLUMN IF NOT EXISTS tenant_id UUID;
UPDATE token_usages SET tenant_id = '00000000-0000-0000-0000-000000000001' WHERE tenant_id IS NULL;
ALTER TABLE token_usages ALTER COLUMN tenant_id SET NOT NULL;
ALTER TABLE token_usages ALTER COLUMN tenant_id SET DEFAULT '00000000-0000-0000-0000-000000000001';
CREATE INDEX IF NOT EXISTS idx_token_usages_tenant ON token_usages(tenant_id);

-- ========== 5. agent_executions 表 ==========
CREATE TABLE IF NOT EXISTS agent_executions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    conversation_id UUID NOT NULL,
    message_id UUID,
    user_id UUID,
    agent_type VARCHAR(30) NOT NULL,
    agent_name VARCHAR(50) NOT NULL,
    duration_ms INT NOT NULL DEFAULT 0,
    success BOOLEAN NOT NULL DEFAULT TRUE,
    error_message TEXT,
    tool_calls_count INT NOT NULL DEFAULT 0,
    input_tokens INT NOT NULL DEFAULT 0,
    output_tokens INT NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_agent_executions_tenant ON agent_executions(tenant_id);
CREATE INDEX IF NOT EXISTS idx_agent_executions_conversation ON agent_executions(conversation_id);
CREATE INDEX IF NOT EXISTS idx_agent_executions_agent_type ON agent_executions(agent_type);
CREATE INDEX IF NOT EXISTS idx_agent_executions_created ON agent_executions(created_at);
CREATE INDEX IF NOT EXISTS idx_agent_executions_tenant_date ON agent_executions(tenant_id, created_at);