-- Remove obsolete push 2FA tables and enums, add WebAuthn credential table

-- Drop FKs if exist (ignore errors if already gone)
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `push_2fa_challenge`;
DROP TABLE IF EXISTS `push_2fa_device`;

-- WebAuthn credential table
CREATE TABLE IF NOT EXISTS `webauthn_credential` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `userId` INT NOT NULL,
  `credentialId` VARCHAR(255) NOT NULL,
  `publicKey` LONGTEXT NOT NULL,
  `counter` INT NOT NULL DEFAULT 0,
  `transports` VARCHAR(255) NULL,
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `lastUsed` DATETIME(3) NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `credentialId_unique` (`credentialId`),
  KEY `idx_webauthn_user` (`userId`),
  CONSTRAINT `fk_webauthn_user` FOREIGN KEY (`userId`) REFERENCES `user`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Clean user table columns if any push-specific flags (optional if still present)
ALTER TABLE `user` DROP COLUMN IF EXISTS `pushTwoFactorEnabled`;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
