-- V008 Enterprise AI, Private RAG, Knowledge Base and Prompt Governance
CREATE TABLE IF NOT EXISTS ai_providers_v008 (id varchar(36) primary key,name varchar(180) unique not null,provider_type varchar(40) not null,base_url varchar(800) not null default '',model_name varchar(180) not null default '',encrypted_config text not null default '',is_local boolean not null default false,enabled boolean not null default true,created_by varchar(36) not null references users(id),created_at timestamptz not null default now());
CREATE TABLE IF NOT EXISTS ai_policies_v008 (id varchar(36) primary key,name varchar(180) unique not null,department_id varchar(36) references departments(id),allowed_provider_ids jsonb not null default '[]',allowed_roles jsonb not null default '[]',max_context_chars integer not null default 30000,max_output_tokens integer not null default 1200,allow_external boolean not null default false,require_approved_prompt boolean not null default true,redact_patterns jsonb not null default '[]',system_rules 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 TABLE IF NOT EXISTS knowledge_bases_v008 (id varchar(36) primary key,name varchar(180) not null,description text not null default '',owner_id varchar(36) not null references users(id),department_id varchar(36) references departments(id),classification varchar(40) not null default 'internal',allowed_roles jsonb not null default '[]',enabled boolean not null default true,created_at timestamptz not null default now());
CREATE TABLE IF NOT EXISTS knowledge_documents_v008 (id varchar(36) primary key,knowledge_base_id varchar(36) not null references knowledge_bases_v008(id) on delete cascade,title varchar(300) not null,source_type varchar(40) not null default 'text',source_uri varchar(1000) not null default '',content_hash varchar(128) not null,status varchar(30) not null default 'ready',metadata_json jsonb not null default '{}',created_by varchar(36) not null references users(id),created_at timestamptz not null default now());
CREATE TABLE IF NOT EXISTS knowledge_chunks_v008 (id varchar(36) primary key,document_id varchar(36) not null references knowledge_documents_v008(id) on delete cascade,chunk_index integer not null,content text not null,embedding jsonb not null default '[]',token_estimate integer not null default 0,metadata_json jsonb not null default '{}');
CREATE TABLE IF NOT EXISTS prompt_templates_v008 (id varchar(36) primary key,name varchar(180) not null,description text not null default '',template text not null,variables jsonb not null default '[]',owner_id varchar(36) not null references users(id),department_id varchar(36) references departments(id),status varchar(30) not null default 'draft',version integer not null default 1,approved_by varchar(36) references users(id),approved_at timestamptz,created_at timestamptz not null default now());
CREATE TABLE IF NOT EXISTS ai_conversations_v008 (id varchar(36) primary key,title varchar(240) not null default 'Nova conversa',user_id varchar(36) not null references users(id),department_id varchar(36) references departments(id),provider_id varchar(36) not null references ai_providers_v008(id),knowledge_base_ids jsonb not null default '[]',prompt_template_id varchar(36) references prompt_templates_v008(id),created_at timestamptz not null default now(),updated_at timestamptz not null default now());
CREATE TABLE IF NOT EXISTS ai_messages_v008 (id varchar(36) primary key,conversation_id varchar(36) not null references ai_conversations_v008(id) on delete cascade,role varchar(30) not null,content text not null,citations jsonb not null default '[]',safety_flags jsonb not null default '[]',prompt_tokens integer not null default 0,completion_tokens integer not null default 0,latency_ms integer not null default 0,created_at timestamptz not null default now());
CREATE TABLE IF NOT EXISTS ai_audit_events_v008 (id varchar(36) primary key,user_id varchar(36) not null references users(id),conversation_id varchar(36) references ai_conversations_v008(id),action varchar(80) not null,provider_id varchar(36) references ai_providers_v008(id),external_transfer boolean not null default false,context_chars integer not null default 0,details jsonb not null default '{}',created_at timestamptz not null default now());
CREATE INDEX IF NOT EXISTS ix_ai_audit_created ON ai_audit_events_v008(created_at);
CREATE INDEX IF NOT EXISTS ix_chunks_document ON knowledge_chunks_v008(document_id);