db/migrations/003_add_metadata.sql

-- Migracao 003: Adicionar coluna de metadata JSONB
-- Data: 2026-01-23
-- Descricao: Adiciona suporte a metadados extensiveis para filtros avancados

-- Adicionar coluna metadata
ALTER TABLE documents
ADD COLUMN IF NOT EXISTS metadata JSONB DEFAULT '{}'::jsonb;

-- Indice GIN para busca eficiente em JSONB
CREATE INDEX IF NOT EXISTS idx_documents_metadata
ON documents USING GIN (metadata);

-- Indices especificos para campos comuns
-- Indice para tags (array JSONB)
CREATE INDEX IF NOT EXISTS idx_documents_tags
ON documents USING GIN ((metadata->'tags'));

-- Indice para document_type
CREATE INDEX IF NOT EXISTS idx_documents_type
ON documents ((metadata->>'document_type'));

-- Indice para security_level
CREATE INDEX IF NOT EXISTS idx_documents_security
ON documents ((metadata->>'security_level'));

-- Indice para department
CREATE INDEX IF NOT EXISTS idx_documents_department
ON documents ((metadata->>'department'));

-- Indice para author
CREATE INDEX IF NOT EXISTS idx_documents_author
ON documents ((metadata->>'author'));

-- Indice para upload_date (com cast para timestamp)
CREATE INDEX IF NOT EXISTS idx_documents_upload_date
ON documents (((metadata->>'upload_date')::timestamp));

-- Comentarios
COMMENT ON COLUMN documents.metadata IS 'Metadados extensiveis em formato JSONB para filtros avancados';

-- Exemplo de uso:
-- INSERT INTO documents (title, content, embedding, metadata)
-- VALUES ('Doc 1', 'Conteudo...', '[...]', '{"document_type": "PDF", "tags": ["tech", "ai"]}'::jsonb);

-- SELECT * FROM documents WHERE metadata->>'document_type' = 'PDF';
-- SELECT * FROM documents WHERE metadata->'tags' ?| array['tech', 'ai'];