-- --------------------------------------------------------
-- 1. Criação da Base de Dados
-- --------------------------------------------------------
CREATE DATABASE IF NOT EXISTS sgpej
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE sgpej;

-- --------------------------------------------------------
-- 3. Tabela: clientes (Constituintes)
-- --------------------------------------------------------
CREATE TABLE clientes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tipo ENUM('particular', 'empresa') NOT NULL,
    nome VARCHAR(200) NOT NULL,
    nif VARCHAR(20) UNIQUE,
    bi_passaporte VARCHAR(30) UNIQUE,
    telefone VARCHAR(20),
    email VARCHAR(100),
    endereco TEXT,
    observacoes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);

-- --------------------------------------------------------
-- 4. Tabela: processos (Pastas Digitais)
-- --------------------------------------------------------
CREATE TABLE processos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    numero_unico VARCHAR(50) NOT NULL UNIQUE, -- Ex: SGPEJ/2026/001
    cliente_id INT NOT NULL,
    advogado_responsavel_id INT NOT NULL, -- Sócio ou Associado principal
    estagiario_id INT NULL, -- Estagiário atribuído
    secretario_id INT NULL, -- Secretariado que apoia
    estado ENUM('aberto', 'em_andamento', 'suspenso', 'arquivado') DEFAULT 'aberto',
    data_abertura DATE NOT NULL,
    data_arquivamento DATE NULL,
    observacoes TEXT,
    criado_por INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE RESTRICT,
    FOREIGN KEY (advogado_responsavel_id) REFERENCES usuarios(id) ON DELETE RESTRICT,
    FOREIGN KEY (estagiario_id) REFERENCES usuarios(id) ON DELETE SET NULL,
    FOREIGN KEY (secretario_id) REFERENCES usuarios(id) ON DELETE SET NULL,
    FOREIGN KEY (criado_por) REFERENCES usuarios(id) ON DELETE SET NULL
);

-- --------------------------------------------------------
-- 5. Tabela: processo_advogados (Equipa adicional do processo)
-- Permite associar vários advogados assistentes ao mesmo processo
-- --------------------------------------------------------
CREATE TABLE processo_advogados (
    id INT AUTO_INCREMENT PRIMARY KEY,
    processo_id INT NOT NULL,
    usuario_id INT NOT NULL, -- advogado associado ou sócio
    tipo_vinculo ENUM('responsavel', 'assistente') DEFAULT 'assistente',
    data_atribuicao DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (processo_id) REFERENCES processos(id) ON DELETE CASCADE,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE RESTRICT,
    UNIQUE KEY unique_processo_usuario (processo_id, usuario_id)
);

-- --------------------------------------------------------
-- 6. Tabela: tramitacoes (Andamentos Processuais)
-- --------------------------------------------------------
CREATE TABLE tramitacoes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    processo_id INT NOT NULL,
    data_registo DATETIME DEFAULT CURRENT_TIMESTAMP,
    tipo ENUM('despacho', 'sentenca', 'notificacao', 'citacao', 'outros') NOT NULL,
    instancia ENUM('seccao', 'comarca', 'relacao', 'supremo', 'constitucional') NULL,
    descricao TEXT NOT NULL,
    data_limite DATE NULL, -- prazo para recurso / contestação
    created_by INT NOT NULL, -- Utilizador que registou
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (processo_id) REFERENCES processos(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES usuarios(id) ON DELETE RESTRICT
);

-- --------------------------------------------------------
-- 7. Tabela: alertas (Notificações Automáticas e Manuais)
-- --------------------------------------------------------
CREATE TABLE alertas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tramitacao_id INT NULL, -- se o alerta for gerado por um prazo
    usuario_id INT NOT NULL, -- destinatário do alerta
    tipo ENUM('email', 'sms', 'interna') NOT NULL,
    mensagem TEXT NOT NULL,
    data_envio DATETIME NULL, -- preenchido quando enviado
    lido BOOLEAN DEFAULT FALSE,
    disparado BOOLEAN DEFAULT FALSE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (tramitacao_id) REFERENCES tramitacoes(id) ON DELETE SET NULL,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE RESTRICT
);

-- --------------------------------------------------------
-- 8. Tabela: honorarios_propostas (Propostas de Honorários)
-- --------------------------------------------------------
CREATE TABLE honorarios_propostas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    processo_id INT NOT NULL,
    cliente_id INT NOT NULL,
    valor DECIMAL(15,2) NOT NULL,
    condicoes TEXT, -- Ex: "50% à entrada, 50% no final"
    estado ENUM('rascunho', 'aguardando_aprovacao', 'aprovada', 'rejeitada', 'enviada') DEFAULT 'rascunho',
    criado_por INT NOT NULL, -- Advogado Associado
    aprovado_por INT NULL, -- Sócio
    data_criacao DATETIME DEFAULT CURRENT_TIMESTAMP,
    data_aprovacao DATETIME NULL,
    data_envio DATETIME NULL,
    bservacoes TEXT NULL,
    
    FOREIGN KEY (processo_id) REFERENCES processos(id) ON DELETE CASCADE,
    FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE RESTRICT,
    FOREIGN KEY (criado_por) REFERENCES usuarios(id) ON DELETE RESTRICT,
    FOREIGN KEY (aprovado_por) REFERENCES usuarios(id) ON DELETE SET NULL
);



-- --------------------------------------------------------
-- 9. Tabela: pagamentos (Recebimentos de Clientes)
-- --------------------------------------------------------
CREATE TABLE pagamentos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    processo_id INT NOT NULL,
    cliente_id INT NOT NULL,
    tipo ENUM('honorarios', 'custas_judiciais') NOT NULL,
    valor DECIMAL(15,2) NOT NULL,
    data_pagamento DATE NOT NULL,
    metodo ENUM('express', 'tpa', 'transferencia', 'numerario') NOT NULL,
    comprovativo_path VARCHAR(255) NULL, -- caminho do ficheiro anexado
    estado ENUM('registado', 'pendente_comprovativo') DEFAULT 'registado',
    registrado_por INT NOT NULL, -- Secretariado
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (processo_id) REFERENCES processos(id) ON DELETE CASCADE,
    FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE RESTRICT,
    FOREIGN KEY (registrado_por) REFERENCES usuarios(id) ON DELETE RESTRICT
);

-- --------------------------------------------------------
-- 10. Tabela: recibos (Gerados automaticamente a partir de pagamentos)
-- --------------------------------------------------------
CREATE TABLE recibos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    pagamento_id INT NOT NULL,
    numero_sequencial VARCHAR(30) NOT NULL UNIQUE, -- Ex: REC-2026-0001
    data_emissao DATETIME DEFAULT CURRENT_TIMESTAMP,
    pdf_path VARCHAR(255) NULL, -- caminho do PDF gerado
    enviado_email BOOLEAN DEFAULT FALSE,
    data_envio_email DATETIME NULL,
    
    FOREIGN KEY (pagamento_id) REFERENCES pagamentos(id) ON DELETE CASCADE
);

-- --------------------------------------------------------
-- 11. Tabela: documentos (Arquivo Digital do Processo)
-- --------------------------------------------------------
CREATE TABLE documentos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    processo_id INT NOT NULL,
    nome_ficheiro VARCHAR(255) NOT NULL,
    caminho VARCHAR(255) NOT NULL, -- local onde o ficheiro está armazenado
    descricao TEXT,
    tipo_documento ENUM('peca_processual', 'procuração', 'prova', 'digitalizacao', 'comprovativo', 'outros') NOT NULL,
    uploaded_by INT NOT NULL,
    tamanho INT NULL, -- tamanho em bytes
    data_upload DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (processo_id) REFERENCES processos(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES usuarios(id) ON DELETE RESTRICT
);

-- --------------------------------------------------------
-- 12. Tabela: agenda_eventos (Audiências e Reuniões)
-- --------------------------------------------------------
CREATE TABLE agenda_eventos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    processo_id INT NULL, -- pode não estar ligado a um processo
    titulo VARCHAR(200) NOT NULL,
    descricao TEXT,
    tipo ENUM('audiencia', 'reuniao', 'prazo_interno') NOT NULL,
    data_inicio DATETIME NOT NULL,
    data_fim DATETIME NULL,
    local VARCHAR(200),
    criado_por INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (processo_id) REFERENCES processos(id) ON DELETE CASCADE,
    FOREIGN KEY (criado_por) REFERENCES usuarios(id) ON DELETE RESTRICT
);

-- --------------------------------------------------------
-- 13. Tabela: agenda_participantes (Participantes do evento)
-- --------------------------------------------------------
CREATE TABLE agenda_participantes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    evento_id INT NOT NULL,
    usuario_id INT NOT NULL,
    confirmado BOOLEAN DEFAULT FALSE,
    
    FOREIGN KEY (evento_id) REFERENCES agenda_eventos(id) ON DELETE CASCADE,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    UNIQUE KEY unique_evento_usuario (evento_id, usuario_id)
);

-- --------------------------------------------------------
-- 14. Tabela: expediente (Recepção de Documentos Físicos/Externos)
-- --------------------------------------------------------
CREATE TABLE expediente (
    id INT AUTO_INCREMENT PRIMARY KEY,
    processo_id INT NOT NULL,
    data_rececao DATETIME DEFAULT CURRENT_TIMESTAMP,
    remetente VARCHAR(200) NOT NULL,
    descricao TEXT NOT NULL,
    anexo_path VARCHAR(255) NULL,
    registrado_por INT NOT NULL, -- Secretariado
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (processo_id) REFERENCES processos(id) ON DELETE CASCADE,
    FOREIGN KEY (registrado_por) REFERENCES usuarios(id) ON DELETE RESTRICT
);