-- Owner alerts (Telegram, with quiet hours) and "is this a new device?" memory for sign-in alerts.
CREATE TABLE alert_queue (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  kind       VARCHAR(20)  NOT NULL,
  message    VARCHAR(1000) NOT NULL,
  status     ENUM('queued','sent','failed') NOT NULL DEFAULT 'queued',
  attempts   TINYINT UNSIGNED NOT NULL DEFAULT 0,
  error      VARCHAR(300) NULL,
  send_after DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  sent_at    DATETIME NULL,
  KEY idx_due (status, send_after)
) ENGINE=InnoDB;

CREATE TABLE known_devices (
  user_id    INT UNSIGNED NOT NULL,
  device     CHAR(64)     NOT NULL,
  label      VARCHAR(100) NULL,
  first_seen DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, device),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;
