--- Creates the user verification table used for email link/code activation
CREATE TABLE IF NOT EXISTS `verificacion_de_usuarios` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_de_usuario` int(11) NOT NULL,
  `codigo` varchar(10) DEFAULT NULL,
  `token` varchar(128) DEFAULT NULL,
  `enlace_verificacion` varchar(512) DEFAULT NULL,
  `creado_el` timestamp NULL DEFAULT current_timestamp(),
  `expira_el` datetime NOT NULL,
  `consumido_el` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`id_de_usuario`),
  KEY `idx_code` (`codigo`),
  KEY `idx_token` (`token`),
  KEY `idx_expira` (`expira_el`),
  CONSTRAINT `verificacion_de_usuarios_ibfk_1` FOREIGN KEY (`id_de_usuario`) REFERENCES `usuarios` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `verificacion_de_usuarios`
  ADD COLUMN IF NOT EXISTS `token` varchar(128) DEFAULT NULL AFTER `codigo`,
  ADD COLUMN IF NOT EXISTS `enlace_verificacion` varchar(512) DEFAULT NULL AFTER `token`;

ALTER TABLE `verificacion_de_usuarios`
  ADD INDEX IF NOT EXISTS `idx_token` (`token`);
