CREATE TABLE IF NOT EXISTS `web_push_subscriptions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `endpoint_hash` CHAR(64) NOT NULL,
    `endpoint` TEXT NOT NULL,
    `public_key` TEXT NOT NULL,
    `auth_token` VARCHAR(255) NOT NULL,
    `content_encoding` VARCHAR(32) NOT NULL DEFAULT 'aesgcm',
    `user_id` BIGINT UNSIGNED DEFAULT NULL,
    `visitor_key` CHAR(64) DEFAULT NULL,
    `user_agent` VARCHAR(255) DEFAULT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `last_error` VARCHAR(500) DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `last_seen_at` DATETIME DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_web_push_subscriptions_endpoint_hash` (`endpoint_hash`),
    KEY `idx_web_push_subscriptions_active` (`is_active`, `updated_at`),
    KEY `idx_web_push_subscriptions_user_id` (`user_id`),
    CONSTRAINT `fk_web_push_subscriptions_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
