-- Criação do banco de dados
CREATE DATABASE IF NOT EXISTS ged_db1;
USE ged_db1;

-- Tabela de usuários
CREATE TABLE IF NOT EXISTS user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(150),
    user_type ENUM('admin', 'user') NOT NULL DEFAULT 'user',
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_login DATETIME
);

-- Tabela de categorias de documentos
CREATE TABLE IF NOT EXISTS category (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_by INT,
    FOREIGN KEY (created_by) REFERENCES user(id)
);

-- Tabela de documentos
CREATE TABLE IF NOT EXISTS document (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(150),
    file_path VARCHAR(255) NOT NULL,
    file_type VARCHAR(50),
    file_size INT,
    category_id INT,
    status ENUM('draft', 'in_review', 'approved', 'archived') DEFAULT 'draft',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by INT,
    FOREIGN KEY (category_id) REFERENCES category(id),
    FOREIGN KEY (created_by) REFERENCES user(id)
);

-- Tabela de palavras-chave de documentos
CREATE TABLE IF NOT EXISTS document_keyword (
    id INT AUTO_INCREMENT PRIMARY KEY,
    document_id INT NOT NULL,
    keyword VARCHAR(100) NOT NULL,
    FOREIGN KEY (document_id) REFERENCES document(id) ON DELETE CASCADE
);

-- Tabela de versões de documentos
CREATE TABLE IF NOT EXISTS document_version (
    id INT AUTO_INCREMENT PRIMARY KEY,
    document_id INT NOT NULL,
    version_number INT NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    file_size INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_by INT,
    change_summary TEXT,
    FOREIGN KEY (document_id) REFERENCES document(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES user(id)
);

-- Tabela de workflow de aprovação
CREATE TABLE IF NOT EXISTS workflow (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_by INT,
    FOREIGN KEY (created_by) REFERENCES user(id)
);

-- Tabela de etapas de workflow
CREATE TABLE IF NOT EXISTS workflow_step (
    id INT AUTO_INCREMENT PRIMARY KEY,
    workflow_id INT NOT NULL,
    step_order INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    approver_id INT,
    FOREIGN KEY (workflow_id) REFERENCES workflow(id) ON DELETE CASCADE,
    FOREIGN KEY (approver_id) REFERENCES user(id)
);

-- Tabela de aprovações de documentos
CREATE TABLE IF NOT EXISTS document_approval (
    id INT AUTO_INCREMENT PRIMARY KEY,
    document_id INT NOT NULL,
    workflow_id INT NOT NULL,
    current_step INT NOT NULL DEFAULT 1,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    started_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME,
    FOREIGN KEY (document_id) REFERENCES document(id) ON DELETE CASCADE,
    FOREIGN KEY (workflow_id) REFERENCES workflow(id)
);

-- Tabela de histórico de aprovações
CREATE TABLE IF NOT EXISTS approval_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    document_approval_id INT NOT NULL,
    step_id INT NOT NULL,
    user_id INT NOT NULL,
    action ENUM('approved', 'rejected', 'commented') NOT NULL,
    comments TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (document_approval_id) REFERENCES document_approval(id) ON DELETE CASCADE,
    FOREIGN KEY (step_id) REFERENCES workflow_step(id),
    FOREIGN KEY (user_id) REFERENCES user(id)
);

-- Tabela de logs de auditoria
CREATE TABLE IF NOT EXISTS audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    action VARCHAR(50) NOT NULL,
    resource_type VARCHAR(50) NOT NULL,
    resource_id INT,
    details TEXT,
    ip_address VARCHAR(50),
    user_agent VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES user(id)
);

-- Tabela de configurações de backup
CREATE TABLE IF NOT EXISTS backup_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    frequency ENUM('daily', 'weekly', 'monthly') DEFAULT 'weekly',
    retention_days INT DEFAULT 30,
    storage_type ENUM('local', 'cloud') DEFAULT 'local',
    storage_path VARCHAR(255),
    cloud_provider VARCHAR(50),
    cloud_credentials TEXT,
    last_backup DATETIME,
    created_by INT,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES user(id)
);

-- Tabela de permissões
CREATE TABLE IF NOT EXISTS permission (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT
);

-- Tabela de permissões de usuário
CREATE TABLE IF NOT EXISTS user_permission (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    permission_id INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permission(id) ON DELETE CASCADE,
    UNIQUE KEY user_permission_unique (user_id, permission_id)
);

-- Inserir usuário administrador padrão
INSERT INTO user (username, email, password_hash, full_name, user_type, is_active)
VALUES ('admin', 'admin@sysged.com.br', '$2b$12$tPZxQRGBjQRoLMOGxKrAn.Kg.hGGh7XZfBpR/xCpbKxCV/0JGq5iK', 'Administrador', 'admin', TRUE);

-- Inserir permissões básicas
INSERT INTO permission (name, description) VALUES
('document_create', 'Criar documentos'),
('document_read', 'Visualizar documentos'),
('document_update', 'Atualizar documentos'),
('document_delete', 'Excluir documentos'),
('user_manage', 'Gerenciar usuários'),
('workflow_manage', 'Gerenciar workflows'),
('category_manage', 'Gerenciar categorias'),
('backup_manage', 'Gerenciar backups'),
('report_view', 'Visualizar relatórios');

-- Atribuir todas as permissões ao administrador
INSERT INTO user_permission (user_id, permission_id)
SELECT 1, id FROM permission;

-- Inserir categorias padrão
INSERT INTO category (name, description, created_by) VALUES
('Financeiro', 'Documentos financeiros como notas fiscais, recibos, etc.', 1),
('Jurídico', 'Documentos jurídicos como contratos, procurações, etc.', 1),
('RH', 'Documentos de recursos humanos como currículos, avaliações, etc.', 1),
('Administrativo', 'Documentos administrativos gerais', 1),
('Marketing', 'Documentos relacionados a marketing e publicidade', 1);

-- Inserir workflow padrão
INSERT INTO workflow (name, description, created_by)
VALUES ('Aprovação Padrão', 'Workflow de aprovação com duas etapas', 1);

-- Inserir etapas do workflow padrão
INSERT INTO workflow_step (workflow_id, step_order, name, description, approver_id)
VALUES 
(1, 1, 'Revisão Inicial', 'Primeira revisão do documento', 1),
(1, 2, 'Aprovação Final', 'Aprovação final do documento', 1);
