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


-- 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;

-- 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;

-- 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;

