-- =====================================================
-- Segunor Portal de Dados — Schema SQL
-- Importar via phpMyAdmin
-- =====================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Migrations table
CREATE TABLE IF NOT EXISTS `migrations` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `migration` VARCHAR(255) NOT NULL,
    `batch` INT NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Admin Users
CREATE TABLE IF NOT EXISTS `admin_users` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `role` ENUM('admin','rh','contabilidade') NOT NULL,
    `must_change_password` TINYINT(1) NOT NULL DEFAULT 1,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `two_factor_method` VARCHAR(255) NULL,
    `two_factor_secret` TEXT NULL,
    `two_factor_recovery_codes` TEXT NULL,
    `two_factor_confirmed` TINYINT(1) NOT NULL DEFAULT 0,
    `last_login_at` TIMESTAMP NULL,
    `last_login_ip` VARCHAR(45) NULL,
    `remember_token` VARCHAR(100) NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `admin_users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Employees Snapshot
CREATE TABLE IF NOT EXISTS `employees_snapshot` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `nome_completo` VARCHAR(255) NOT NULL,
    `nif` VARCHAR(20) NOT NULL,
    `data_nascimento` DATE NULL,
    `email` VARCHAR(255) NULL,
    `telemovel` VARCHAR(20) NULL,
    `contacto_emergencia_nome` VARCHAR(255) NULL,
    `contacto_emergencia_relacao` VARCHAR(255) NULL,
    `contacto_emergencia_telefone` VARCHAR(20) NULL,
    `morada` TEXT NULL,
    `estado_civil` VARCHAR(50) NULL,
    `nr_dependentes` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `habilitacoes_literarias` VARCHAR(100) NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `employees_snapshot_nif_unique` (`nif`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Snapshot Imports
CREATE TABLE IF NOT EXISTS `snapshot_imports` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `imported_by` BIGINT UNSIGNED NOT NULL,
    `total_records` INT UNSIGNED NOT NULL,
    `success_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `error_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `errors_log` TEXT NULL,
    `filename` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`imported_by`) REFERENCES `admin_users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Submissions
CREATE TABLE IF NOT EXISTS `submissions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `type` ENUM('update','new_admission') NOT NULL,
    `nome_completo` VARCHAR(255) NOT NULL,
    `nif` VARCHAR(20) NOT NULL,
    `data_nascimento` DATE NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `telemovel` VARCHAR(20) NOT NULL,
    `contacto_emergencia_nome` VARCHAR(255) NOT NULL,
    `contacto_emergencia_relacao` VARCHAR(255) NOT NULL,
    `contacto_emergencia_telefone` VARCHAR(20) NOT NULL,
    `morada_original` TEXT NOT NULL,
    `morada_corrigida` TEXT NULL,
    `mudou_morada` TINYINT(1) NOT NULL DEFAULT 0,
    `estado_civil` VARCHAR(50) NOT NULL,
    `nr_dependentes` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `habilitacoes_literarias` VARCHAR(100) NOT NULL,
    `comprovativo_path` VARCHAR(255) NULL,
    `comprovativo_original_name` VARCHAR(255) NULL,
    `comprovativo_mime` VARCHAR(255) NULL,
    `comprovativo_size` INT UNSIGNED NULL,
    `status` ENUM('novo','pendente','sem_alteracoes_auto','aprovado','rejeitado','concluido','bloqueado') NOT NULL DEFAULT 'novo',
    `flag_sem_correspondencia` TINYINT(1) NOT NULL DEFAULT 0,
    `flag_ip_fora_pt` TINYINT(1) NOT NULL DEFAULT 0,
    `flag_inconsistencia` TINYINT(1) NOT NULL DEFAULT 0,
    `flag_suspeito` TINYINT(1) NOT NULL DEFAULT 0,
    `suspicion_score` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `snapshot_match_id` BIGINT UNSIGNED NULL,
    `diff_data` JSON NULL,
    `is_latest` TINYINT(1) NOT NULL DEFAULT 1,
    `ip_address` VARCHAR(45) NULL,
    `ip_hash` VARCHAR(64) NULL,
    `user_agent` VARCHAR(255) NULL,
    `rejection_reason` TEXT NULL,
    `declaracao_verdade` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    KEY `submissions_nif_index` (`nif`),
    KEY `submissions_nif_is_latest_index` (`nif`, `is_latest`),
    KEY `submissions_status_index` (`status`),
    KEY `submissions_created_at_index` (`created_at`),
    FOREIGN KEY (`snapshot_match_id`) REFERENCES `employees_snapshot`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Audit Logs
CREATE TABLE IF NOT EXISTS `audit_logs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `admin_user_id` BIGINT UNSIGNED NULL,
    `action` VARCHAR(255) NOT NULL,
    `entity_type` VARCHAR(255) NULL,
    `entity_id` BIGINT UNSIGNED NULL,
    `old_values` JSON NULL,
    `new_values` JSON NULL,
    `reason` TEXT NULL,
    `ip_address` VARCHAR(45) NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    KEY `audit_logs_entity_index` (`entity_type`, `entity_id`),
    KEY `audit_logs_action_index` (`action`),
    FOREIGN KEY (`admin_user_id`) REFERENCES `admin_users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Blocked IPs
CREATE TABLE IF NOT EXISTS `blocked_ips` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ip_address` VARCHAR(45) NOT NULL,
    `level` ENUM('degraded','blocked') NOT NULL DEFAULT 'degraded',
    `reason` TEXT NULL,
    `blocked_by` BIGINT UNSIGNED NOT NULL,
    `expires_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `blocked_ips_ip_unique` (`ip_address`),
    FOREIGN KEY (`blocked_by`) REFERENCES `admin_users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- RGPD Requests
CREATE TABLE IF NOT EXISTS `rgpd_requests` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `nome_completo` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `nif` VARCHAR(20) NULL,
    `tipo_pedido` ENUM('acesso','retificacao','apagamento','portabilidade','oposicao') NOT NULL,
    `descricao` TEXT NOT NULL,
    `status` ENUM('pendente','em_tratamento','concluido') NOT NULL DEFAULT 'pendente',
    `resposta` TEXT NULL,
    `treated_by` BIGINT UNSIGNED NULL,
    `treated_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`treated_by`) REFERENCES `admin_users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Settings
CREATE TABLE IF NOT EXISTS `settings` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `key` VARCHAR(255) NOT NULL,
    `value` TEXT NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `settings_key_unique` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sessions
CREATE TABLE IF NOT EXISTS `sessions` (
    `id` VARCHAR(255) NOT NULL,
    `user_id` BIGINT UNSIGNED NULL,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` TEXT NULL,
    `payload` LONGTEXT NOT NULL,
    `last_activity` INT NOT NULL,
    PRIMARY KEY (`id`),
    KEY `sessions_user_id_index` (`user_id`),
    KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Cache
CREATE TABLE IF NOT EXISTS `cache` (
    `key` VARCHAR(255) NOT NULL,
    `value` MEDIUMTEXT NOT NULL,
    `expiration` INT NOT NULL,
    PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cache_locks` (
    `key` VARCHAR(255) NOT NULL,
    `owner` VARCHAR(255) NOT NULL,
    `expiration` INT NOT NULL,
    PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================
-- SEED DATA — Utilizadores iniciais
-- Password: Segunor2025! (bcrypt hash)
-- ALTERAR NO PRIMEIRO LOGIN
-- =====================================================

INSERT INTO `admin_users` (`name`, `email`, `password`, `role`, `must_change_password`, `is_active`, `created_at`, `updated_at`) VALUES
('Administrador', 'admin@segunor.pt', '$2y$12$LJ3kN0gVqZkVvPx8wHqC4eG1TmYxZj3OvCjK9cRxF0pQd5nS7kW2y', 'admin', 1, 1, NOW(), NOW()),
('Recursos Humanos', 'rh@segunor.pt', '$2y$12$LJ3kN0gVqZkVvPx8wHqC4eG1TmYxZj3OvCjK9cRxF0pQd5nS7kW2y', 'rh', 1, 1, NOW(), NOW()),
('Contabilidade', 'contabilidade@segunor.pt', '$2y$12$LJ3kN0gVqZkVvPx8wHqC4eG1TmYxZj3OvCjK9cRxF0pQd5nS7kW2y', 'contabilidade', 1, 1, NOW(), NOW());

INSERT INTO `settings` (`key`, `value`, `created_at`, `updated_at`) VALUES
('campaign_enabled', '1', NOW(), NOW()),
('campaign_start', '', NOW(), NOW()),
('campaign_end', '', NOW(), NOW()),
('maintenance_mode', '0', NOW(), NOW());
