-- WSS Veritas - Web System Solution Veritas
-- Desenvolvido por Robson Fontoura
-- Ano: 2025
-- Arquivo de Estrutura e Dados Iniciais do Banco

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

--
-- Estrutura da tabela `languages`
--
CREATE TABLE `languages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(5) NOT NULL,
  `name` varchar(50) NOT NULL,
  `flag` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `is_default` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Estrutura da tabela `user_roles`
--
CREATE TABLE `user_roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `description` text DEFAULT NULL,
  `permissions` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Estrutura da tabela `users`
--
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `profile_image` varchar(255) DEFAULT NULL,
  `last_login` datetime DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `reset_token` varchar(255) DEFAULT NULL,
  `token_expiry` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `role_id` (`role_id`),
  CONSTRAINT `users_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `user_roles` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Estrutura da tabela `clients`
--
CREATE TABLE `clients` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `cpf_cnpj` varchar(20) NOT NULL,
  `type` enum('fisica','juridica') NOT NULL,
  `email` varchar(100) NOT NULL,
  `client_password` varchar(255) DEFAULT NULL COMMENT 'Senha hash para login do cliente na área do cliente',
  `last_login_client_area` datetime DEFAULT NULL COMMENT 'Data do último login do cliente na sua área',
  `phone` varchar(20) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state` varchar(2) DEFAULT NULL,
  `zip` varchar(10) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `cpf_cnpj` (`cpf_cnpj`),
  KEY `user_id` (`user_id`),
  KEY `idx_client_email` (`email`),
  CONSTRAINT `clients_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Estrutura da tabela `processes`
--
CREATE TABLE `processes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_id` int(11) NOT NULL,
  `number` varchar(30) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `type` varchar(50) NOT NULL,
  `court` varchar(100) DEFAULT NULL,
  `jurisdiction` varchar(100) DEFAULT NULL,
  `judge` varchar(100) DEFAULT NULL,
  `status` varchar(50) NOT NULL DEFAULT 'Em andamento',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `number` (`number`),
  KEY `client_id` (`client_id`),
  CONSTRAINT `processes_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Estrutura da tabela `document_templates`
--
CREATE TABLE `document_templates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `file_path` varchar(255) NOT NULL,
  `variables` text DEFAULT NULL,
  `category` varchar(50) DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `document_templates_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Estrutura da tabela `documents`
--
CREATE TABLE `documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `process_id` int(11) DEFAULT NULL,
  `client_id` int(11) DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `template_id` int(11) DEFAULT NULL,
  `file_path` varchar(255) NOT NULL,
  `file_type` enum('docx','pdf','txt','jpg','png','xls','xlsx','other') NOT NULL DEFAULT 'docx',
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `process_id` (`process_id`),
  KEY `client_id` (`client_id`),
  KEY `created_by` (`created_by`),
  KEY `template_id` (`template_id`),
  CONSTRAINT `documents_ibfk_1` FOREIGN KEY (`process_id`) REFERENCES `processes` (`id`) ON DELETE SET NULL,
  CONSTRAINT `documents_ibfk_2` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE SET NULL,
  CONSTRAINT `documents_ibfk_3` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `documents_ibfk_4` FOREIGN KEY (`template_id`) REFERENCES `document_templates` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Estrutura da tabela `custom_links`
--
CREATE TABLE `custom_links` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `subtitle` varchar(255) DEFAULT NULL,
  `url` varchar(255) NOT NULL,
  `background_color` varchar(7) NOT NULL DEFAULT '#3498db',
  `hover_color` varchar(7) NOT NULL DEFAULT '#2980b9',
  `text_color` varchar(7) NOT NULL DEFAULT '#ffffff',
  `order_index` int(11) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `custom_links_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Estrutura da tabela `ai_activities`
--
CREATE TABLE `ai_activities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `process_id` int(11) DEFAULT NULL,
  `document_id` int(11) DEFAULT NULL,
  `action_type` varchar(50) NOT NULL,
  `input_data` text DEFAULT NULL,
  `output_data` text DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `process_id` (`process_id`),
  KEY `document_id` (`document_id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `ai_activities_ibfk_1` FOREIGN KEY (`process_id`) REFERENCES `processes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ai_activities_ibfk_2` FOREIGN KEY (`document_id`) REFERENCES `documents` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ai_activities_ibfk_3` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Estrutura da tabela `appointments`
--
CREATE TABLE `appointments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `process_id` int(11) DEFAULT NULL,
  `client_id` int(11) DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `start_date` datetime NOT NULL,
  `end_date` datetime DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `reminder` tinyint(1) NOT NULL DEFAULT 0,
  `reminder_time` int(11) DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `process_id` (`process_id`),
  KEY `client_id` (`client_id`),
  KEY `created_by` (`created_by`),
  KEY `idx_start_date` (`start_date`),
  CONSTRAINT `appointments_ibfk_1` FOREIGN KEY (`process_id`) REFERENCES `processes` (`id`) ON DELETE SET NULL,
  CONSTRAINT `appointments_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `appointments_ibfk_3` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Estrutura da tabela `jurisprudences`
--
CREATE TABLE `jurisprudences` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `source` varchar(100) NOT NULL,
  `process_number_related` varchar(50) DEFAULT NULL,
  `court_jurisprudence` varchar(100) DEFAULT NULL,
  `judge_reporter` varchar(100) DEFAULT NULL,
  `publication_date` date DEFAULT NULL,
  `summary` text DEFAULT NULL,
  `full_content_link` varchar(512) DEFAULT NULL,
  `tags` varchar(255) DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `jurisprudences_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Estrutura da tabela `reports`
--
CREATE TABLE `reports` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `query_definition` text NOT NULL,
  `parameters_json` text DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `reports_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Estrutura da tabela `config`
--
CREATE TABLE `config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `site_name` varchar(100) NOT NULL,
  `site_url` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `default_language` varchar(5) NOT NULL DEFAULT 'pt-BR',
  `logo` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Estrutura da tabela `translations`
--
CREATE TABLE `translations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `language_code` varchar(5) NOT NULL,
  `translation_key` varchar(255) NOT NULL,
  `translation_value` text NOT NULL,
  `module` varchar(100) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_lang_key_module` (`language_code`,`translation_key`,`module`),
  CONSTRAINT `fk_translations_language_code` FOREIGN KEY (`language_code`) REFERENCES `languages` (`code`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- DADOS INICIAIS
--
INSERT INTO `user_roles` (`id`, `name`, `description`, `permissions`) VALUES
(1, 'Administrador', 'Acesso total ao sistema.', '{\"users_manage\": true, \"clients_manage\": true, \"processes_manage\": true, \"documents_manage\": true, \"templates_manage\": true, \"links_manage\": true, \"settings_manage\": true, \"reports_view\": true, \"ai_features\": true, \"client_area_config\": true}'),
(2, 'Advogado', 'Acesso a funcionalidades de gestão de casos e clientes.', '{\"clients_manage\": true, \"processes_manage\": true, \"documents_manage\": true, \"templates_use\": true, \"reports_view\": true, \"ai_features\": true}'),
(3, 'Assistente', 'Acesso limitado para auxiliar advogados.', '{\"clients_view\": true, \"processes_view\": true, \"documents_create\": true, \"templates_use\": true}'),
(4, 'Cliente', 'Acesso restrito à área do cliente.', '{\"client_area_access\": true}');

INSERT INTO `languages` (`code`, `name`, `is_active`, `is_default`) VALUES
('pt-BR', 'Português (Brasil)', 1, 1),
('en-US', 'English (US)', 1, 0)
ON DUPLICATE KEY UPDATE name=VALUES(name), is_active=VALUES(is_active), is_default=VALUES(is_default);

-- Usuário Administrador Padrão (Senha: password123)
INSERT INTO `users` (`role_id`, `name`, `email`, `password`, `is_active`) VALUES
(1, 'Admin Master', 'admin@wssveritas.pro', '$2y$10$K0T.h0N8XQ3Yq2Q2o9L/1uJgetThS93vX3P.DSd.t4aY1l3mQWlO.', 1)
ON DUPLICATE KEY UPDATE name=VALUES(name);

-- Configuração Padrão
INSERT INTO `config` (`id`, `site_name`, `site_url`, `email`, `default_language`, `logo`) VALUES
(1, 'WSS Veritas', 'http://localhost:8080', 'contato@robsonfontoura.dev', 'pt-BR', NULL)
ON DUPLICATE KEY UPDATE site_name=VALUES(site_name);

-- Traduções Iniciais
-- (Incluindo todas as chaves que definimos anteriormente)
INSERT INTO `translations` (`language_code`, `translation_key`, `translation_value`, `module`) VALUES
('pt-BR', 'app.name', 'WSS Veritas', 'common'),
('pt-BR', 'app.subtitle_law_system', 'Sistema de Advocacia', 'common'),
('pt-BR', 'footer.copyright', '© %app_name% %year%. Desenvolvido por Robson Fontoura.', 'common'),
-- ... (todas as outras chaves 'pt-BR' que forneci na resposta anterior) ...
('en-US', 'app.name', 'WSS Veritas', 'common'),
('en-US', 'app.subtitle_law_system', 'Law Office System', 'common'),
('en-US', 'footer.copyright', '© %app_name% %year%. Developed by Robson Fontoura.', 'common')
-- ... (todas as outras chaves 'en-US' que forneci na resposta anterior) ...
ON DUPLICATE KEY UPDATE translation_value=VALUES(translation_value);


COMMIT;