-- Run this once in your MySQL database to create the subscribers table
-- phpMyAdmin → SQL tab → paste and run

CREATE TABLE IF NOT EXISTS `push_subscribers` (
  `id`                          INT AUTO_INCREMENT PRIMARY KEY,
  `token`                       VARCHAR(512) NOT NULL,
  `state`                       ENUM('non_installed','installed','non_launched') DEFAULT 'non_installed',

  -- Timestamps for event tracking (stored in UTC)
  `clicked_at`                  DATETIME NULL DEFAULT NULL,
  `installed_at`                DATETIME NULL DEFAULT NULL,
  `last_open_at`                DATETIME NULL DEFAULT NULL,

  -- One-time notification sent flags
  `first_install_sent`          TINYINT(1) DEFAULT 0,
  `non_installed_event_sent`    TINYINT(1) DEFAULT 0,
  `installed_event_sent`        TINYINT(1) DEFAULT 0,
  `non_launched_event_sent`     TINYINT(1) DEFAULT 0,

  -- Recurring daily/every-2-days tracking
  `non_installed_last_daily`    DATETIME NULL DEFAULT NULL,
  `installed_last_daily`        DATETIME NULL DEFAULT NULL,
  `non_launched_last_daily`     DATETIME NULL DEFAULT NULL,

  -- Daily schedule (8 PM BD) tracking
  `schedule_last_sent`          DATE NULL DEFAULT NULL,

  `created_at`                  DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at`                  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  UNIQUE KEY `uq_token` (`token`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;