-- V027 Enterprise AI Center
CREATE TABLE IF NOT EXISTS ai_center_providers_v027 (id varchar(36) PRIMARY KEY, tenant_id varchar(36) NOT NULL REFERENCES tenants_v020(id) ON DELETE CASCADE, name varchar(160) NOT NULL, provider_type varchar(50) NOT NULL, base_url varchar(700) NOT NULL DEFAULT '', encrypted_config text NOT NULL DEFAULT '', enabled boolean NOT NULL DEFAULT true, created_by varchar(36) NOT NULL REFERENCES users(id), created_at timestamptz NOT NULL DEFAULT now());
CREATE INDEX IF NOT EXISTS ix_ai_center_providers_tenant_v027 ON ai_center_providers_v027(tenant_id);
CREATE TABLE IF NOT EXISTS ai_center_models_v027 (id varchar(36) PRIMARY KEY, tenant_id varchar(36) NOT NULL REFERENCES tenants_v020(id) ON DELETE CASCADE, provider_id varchar(36) NOT NULL REFERENCES ai_center_providers_v027(id) ON DELETE CASCADE, name varchar(180) NOT NULL, model_code varchar(220) NOT NULL, capabilities jsonb NOT NULL DEFAULT '[]', context_window integer NOT NULL DEFAULT 0, input_cost_per_million integer NOT NULL DEFAULT 0, output_cost_per_million integer NOT NULL DEFAULT 0, enabled boolean NOT NULL DEFAULT true, created_at timestamptz NOT NULL DEFAULT now(), UNIQUE(tenant_id,provider_id,model_code));
CREATE TABLE IF NOT EXISTS ai_center_tools_v027 (id varchar(36) PRIMARY KEY, tenant_id varchar(36) REFERENCES tenants_v020(id) ON DELETE CASCADE, code varchar(120) NOT NULL, name varchar(180) NOT NULL, description text NOT NULL DEFAULT '', risk_level varchar(30) NOT NULL DEFAULT 'low', requires_approval boolean NOT NULL DEFAULT false, enabled boolean NOT NULL DEFAULT true, configuration jsonb NOT NULL DEFAULT '{}', UNIQUE(tenant_id,code));
CREATE TABLE IF NOT EXISTS ai_agents_v027 (id varchar(36) PRIMARY KEY, tenant_id varchar(36) NOT NULL REFERENCES tenants_v020(id) ON DELETE CASCADE, name varchar(180) NOT NULL, description text NOT NULL DEFAULT '', department_id varchar(36) REFERENCES departments(id), model_id varchar(36) NOT NULL REFERENCES ai_center_models_v027(id), system_prompt text NOT NULL DEFAULT '', tool_codes jsonb NOT NULL DEFAULT '[]', knowledge_base_ids jsonb NOT NULL DEFAULT '[]', dataset_ids jsonb NOT NULL DEFAULT '[]', allowed_roles jsonb NOT NULL DEFAULT '[]', memory_enabled boolean NOT NULL DEFAULT true, max_context_chars integer NOT NULL DEFAULT 20000, enabled boolean NOT NULL DEFAULT true, owner_id varchar(36) NOT NULL REFERENCES users(id), created_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS ai_center_conversations_v027 (id varchar(36) PRIMARY KEY, tenant_id varchar(36) NOT NULL REFERENCES tenants_v020(id) ON DELETE CASCADE, agent_id varchar(36) NOT NULL REFERENCES ai_agents_v027(id) ON DELETE CASCADE, user_id varchar(36) NOT NULL REFERENCES users(id), title varchar(220) NOT NULL DEFAULT 'Nova conversa', status varchar(30) NOT NULL DEFAULT 'active', created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS ai_center_messages_v027 (id varchar(36) PRIMARY KEY, tenant_id varchar(36) NOT NULL REFERENCES tenants_v020(id) ON DELETE CASCADE, conversation_id varchar(36) NOT NULL REFERENCES ai_center_conversations_v027(id) ON DELETE CASCADE, role varchar(30) NOT NULL, content text NOT NULL, citations jsonb NOT NULL DEFAULT '[]', tool_calls jsonb NOT NULL DEFAULT '[]', safety_flags jsonb NOT NULL DEFAULT '[]', created_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS ai_prompt_assets_v027 (id varchar(36) PRIMARY KEY, tenant_id varchar(36) NOT NULL REFERENCES tenants_v020(id) ON DELETE CASCADE, name varchar(180) NOT NULL, template text NOT NULL, version integer NOT NULL DEFAULT 1, status varchar(30) NOT NULL DEFAULT 'draft', variables jsonb NOT NULL DEFAULT '[]', created_by varchar(36) NOT NULL REFERENCES users(id), approved_by varchar(36) REFERENCES users(id), created_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS ai_approval_requests_v027 (id varchar(36) PRIMARY KEY, tenant_id varchar(36) NOT NULL REFERENCES tenants_v020(id) ON DELETE CASCADE, conversation_id varchar(36) REFERENCES ai_center_conversations_v027(id) ON DELETE SET NULL, requested_by varchar(36) NOT NULL REFERENCES users(id), tool_code varchar(120) NOT NULL, arguments jsonb NOT NULL DEFAULT '{}', status varchar(30) NOT NULL DEFAULT 'pending', reviewer_id varchar(36) REFERENCES users(id), reason text NOT NULL DEFAULT '', created_at timestamptz NOT NULL DEFAULT now(), decided_at timestamptz);
CREATE TABLE IF NOT EXISTS ai_cost_ledger_v027 (id varchar(36) PRIMARY KEY, tenant_id varchar(36) NOT NULL REFERENCES tenants_v020(id) ON DELETE CASCADE, user_id varchar(36) NOT NULL REFERENCES users(id), agent_id varchar(36) NOT NULL REFERENCES ai_agents_v027(id), model_id varchar(36) NOT NULL REFERENCES ai_center_models_v027(id), input_tokens integer NOT NULL DEFAULT 0, output_tokens integer NOT NULL DEFAULT 0, estimated_cost_micros bigint NOT NULL DEFAULT 0, latency_ms integer NOT NULL DEFAULT 0, status varchar(30) NOT NULL DEFAULT 'success', created_at timestamptz NOT NULL DEFAULT now());
ALTER TABLE ai_center_providers_v027 ENABLE ROW LEVEL SECURITY; ALTER TABLE ai_center_providers_v027 FORCE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_ai_providers_v027 ON ai_center_providers_v027; CREATE POLICY tenant_ai_providers_v027 ON ai_center_providers_v027 USING (tenant_id::text=current_setting('app.current_tenant_id',true)) WITH CHECK (tenant_id::text=current_setting('app.current_tenant_id',true));
ALTER TABLE ai_agents_v027 ENABLE ROW LEVEL SECURITY; ALTER TABLE ai_agents_v027 FORCE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_ai_agents_v027 ON ai_agents_v027; CREATE POLICY tenant_ai_agents_v027 ON ai_agents_v027 USING (tenant_id::text=current_setting('app.current_tenant_id',true)) WITH CHECK (tenant_id::text=current_setting('app.current_tenant_id',true));
ALTER TABLE ai_center_conversations_v027 ENABLE ROW LEVEL SECURITY; ALTER TABLE ai_center_conversations_v027 FORCE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_ai_conversations_v027 ON ai_center_conversations_v027; CREATE POLICY tenant_ai_conversations_v027 ON ai_center_conversations_v027 USING (tenant_id::text=current_setting('app.current_tenant_id',true)) WITH CHECK (tenant_id::text=current_setting('app.current_tenant_id',true));