-- DDL Script para inicialização do banco de dados PostgreSQL
-- Para para.AI - Sistema de análise jurídica com IA

-- Criar extensões necessárias
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

-- Tabela: Tribunais
CREATE TABLE IF NOT EXISTS tribunais (
    id VARCHAR(36) PRIMARY KEY,
    nome VARCHAR(255) NOT NULL UNIQUE,
    sigla VARCHAR(10) NOT NULL UNIQUE,
    uf VARCHAR(2) NOT NULL,
    tipo VARCHAR(50) NOT NULL,
    url_base VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT ck_tribunal_sigla_len CHECK (LENGTH(sigla) > 0),
    CONSTRAINT ck_tribunal_uf_len CHECK (LENGTH(uf) = 2)
);

CREATE INDEX IF NOT EXISTS idx_tribunal_sigla_uf ON tribunais(sigla, uf);

-- Tabela: Acórdãos
CREATE TABLE IF NOT EXISTS acordaos (
    id VARCHAR(36) PRIMARY KEY,
    tribunal_id VARCHAR(36) NOT NULL REFERENCES tribunais(id) ON DELETE CASCADE,
    numero VARCHAR(50) NOT NULL,
    ano INTEGER NOT NULL,
    data_julgamento TIMESTAMP NOT NULL,
    ementa TEXT NOT NULL,
    relator VARCHAR(255),
    orgao_julgador VARCHAR(255),
    url_original VARCHAR(500) UNIQUE,
    hash_conteudo VARCHAR(64) NOT NULL UNIQUE,
    status_processamento VARCHAR(20) DEFAULT 'pendente',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_acordao_tribunal_numero_ano UNIQUE(tribunal_id, numero, ano)
);

CREATE INDEX IF NOT EXISTS idx_acordao_tribunal_id ON acordaos(tribunal_id);
CREATE INDEX IF NOT EXISTS idx_acordao_ano ON acordaos(ano);
CREATE INDEX IF NOT EXISTS idx_acordao_data_julgamento ON acordaos(data_julgamento);
CREATE INDEX IF NOT EXISTS idx_acordao_hash_conteudo ON acordaos(hash_conteudo);
CREATE INDEX IF NOT EXISTS idx_acordao_status_processamento ON acordaos(status_processamento);
CREATE INDEX IF NOT EXISTS idx_acordao_data_tribunal ON acordaos(data_julgamento, tribunal_id);
CREATE INDEX IF NOT EXISTS idx_acordao_ementa_trgm ON acordaos USING gin(ementa gin_trgm_ops);

-- Tabela: Decisões
CREATE TABLE IF NOT EXISTS decisoes (
    id VARCHAR(36) PRIMARY KEY,
    acordao_id VARCHAR(36) NOT NULL REFERENCES acordaos(id) ON DELETE CASCADE,
    tipo VARCHAR(50) NOT NULL,
    texto TEXT NOT NULL,
    confianca FLOAT DEFAULT 0.0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_decisao_acordao_id ON decisoes(acordao_id);
CREATE INDEX IF NOT EXISTS idx_decisao_tipo ON decisoes(tipo);
CREATE INDEX IF NOT EXISTS idx_decisao_acordo_tipo ON decisoes(acordao_id, tipo);

-- Tabela: Palavras-chave
CREATE TABLE IF NOT EXISTS palavras_chave (
    id VARCHAR(36) PRIMARY KEY,
    termo VARCHAR(255) NOT NULL UNIQUE,
    categoria VARCHAR(100) NOT NULL,
    frequencia INTEGER DEFAULT 0,
    ativo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_palavra_chave_termo ON palavras_chave(termo);
CREATE INDEX IF NOT EXISTS idx_palavra_chave_categoria ON palavras_chave(categoria);

-- Tabela: Associação Acórdão-PalavraChave
CREATE TABLE IF NOT EXISTS acordao_palavras_chave (
    id VARCHAR(36) PRIMARY KEY,
    acordao_id VARCHAR(36) NOT NULL REFERENCES acordaos(id) ON DELETE CASCADE,
    palavra_chave_id VARCHAR(36) NOT NULL REFERENCES palavras_chave(id) ON DELETE CASCADE,
    relevancia FLOAT DEFAULT 0.0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_acordao_palavra UNIQUE(acordao_id, palavra_chave_id)
);

CREATE INDEX IF NOT EXISTS idx_acordao_palavras_chave_acordao ON acordao_palavras_chave(acordao_id);
CREATE INDEX IF NOT EXISTS idx_acordao_palavras_chave_palavra ON acordao_palavras_chave(palavra_chave_id);

-- Tabela: Embeddings
CREATE TABLE IF NOT EXISTS embeddings (
    id VARCHAR(36) PRIMARY KEY,
    acordao_id VARCHAR(36) NOT NULL REFERENCES acordaos(id) ON DELETE CASCADE,
    modelo VARCHAR(100) NOT NULL,
    tipo VARCHAR(50) NOT NULL,
    vetor TEXT NOT NULL,
    dimensoes INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_embedding_acordao_id ON embeddings(acordao_id);
CREATE INDEX IF NOT EXISTS idx_embedding_modelo ON embeddings(modelo);
CREATE INDEX IF NOT EXISTS idx_embedding_acordo_modelo ON embeddings(acordao_id, modelo);

-- Tabela: Usuários
CREATE TABLE IF NOT EXISTS usuarios (
    id VARCHAR(36) PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    nome VARCHAR(255) NOT NULL,
    senha_hash VARCHAR(255) NOT NULL,
    ativo BOOLEAN DEFAULT TRUE,
    role VARCHAR(50) DEFAULT 'user',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_usuario_email ON usuarios(email);

-- Tabela: Buscas
CREATE TABLE IF NOT EXISTS buscas (
    id VARCHAR(36) PRIMARY KEY,
    usuario_id VARCHAR(36) NOT NULL REFERENCES usuarios(id) ON DELETE CASCADE,
    termo VARCHAR(500) NOT NULL,
    filtros TEXT,
    resultado_count INTEGER DEFAULT 0,
    tempo_execucao FLOAT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_busca_usuario_id ON buscas(usuario_id);
CREATE INDEX IF NOT EXISTS idx_busca_usuario_data ON buscas(usuario_id, created_at);
CREATE INDEX IF NOT EXISTS idx_busca_created_at ON buscas(created_at);

-- Tabela: Processos Judiciais
CREATE TABLE IF NOT EXISTS processos_judiciais (
    id VARCHAR(36) PRIMARY KEY,
    numero_processo VARCHAR(50) NOT NULL UNIQUE,
    tribunal_id VARCHAR(36) NOT NULL REFERENCES tribunais(id) ON DELETE CASCADE,
    data_distribuicao TIMESTAMP NOT NULL,
    status VARCHAR(50) DEFAULT 'ativo',
    parte_ativa VARCHAR(255),
    parte_passiva VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_processo_numero ON processos_judiciais(numero_processo);
CREATE INDEX IF NOT EXISTS idx_processo_tribunal_id ON processos_judiciais(tribunal_id);
CREATE INDEX IF NOT EXISTS idx_processo_status ON processos_judiciais(status);

-- Tabela: Auditoria de Acessos
CREATE TABLE IF NOT EXISTS auditoria_acessos (
    id VARCHAR(36) PRIMARY KEY,
    usuario_id VARCHAR(36) REFERENCES usuarios(id) ON DELETE SET NULL,
    acao VARCHAR(100) NOT NULL,
    recurso VARCHAR(255),
    ip_address VARCHAR(45),
    status VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_auditoria_usuario_id ON auditoria_acessos(usuario_id);
CREATE INDEX IF NOT EXISTS idx_auditoria_created_at ON auditoria_acessos(created_at);
CREATE INDEX IF NOT EXISTS idx_auditoria_usuario_data ON auditoria_acessos(usuario_id, created_at);

-- Views úteis
CREATE OR REPLACE VIEW v_acordaos_por_tribunal AS
SELECT 
    t.sigla,
    t.nome,
    COUNT(a.id) as total_acordaos,
    COUNT(CASE WHEN a.status_processamento = 'processado' THEN 1 END) as processados,
    COUNT(CASE WHEN a.status_processamento = 'pendente' THEN 1 END) as pendentes
FROM tribunais t
LEFT JOIN acordaos a ON t.id = a.tribunal_id
GROUP BY t.id, t.sigla, t.nome;

CREATE OR REPLACE VIEW v_estatisticas_buscas AS
SELECT 
    DATE_TRUNC('day', b.created_at) as data,
    COUNT(*) as total_buscas,
    COUNT(DISTINCT b.usuario_id) as usuarios_unicos,
    AVG(b.tempo_execucao) as tempo_medio_ms
FROM buscas b
GROUP BY DATE_TRUNC('day', b.created_at);

-- Função para atualizar updated_at automaticamente
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Triggers para updated_at
CREATE TRIGGER tg_tribunais_updated_at BEFORE UPDATE ON tribunais
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER tg_acordaos_updated_at BEFORE UPDATE ON acordaos
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER tg_usuarios_updated_at BEFORE UPDATE ON usuarios
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER tg_processos_judiciais_updated_at BEFORE UPDATE ON processos_judiciais
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();