-- Migration 005b: Create notifications table
CREATE TABLE IF NOT EXISTS notifications (
  id            CHAR(36)     NOT NULL DEFAULT (UUID()),
  user_id       INT          NOT NULL,
  type          VARCHAR(50)  NOT NULL,
  title         VARCHAR(255) NOT NULL,
  body          TEXT         NOT NULL,
  is_read       TINYINT(1)   NOT NULL DEFAULT 0,
  ref_id        VARCHAR(255) NULL,
  cron_date_key VARCHAR(100) NULL COMMENT 'Dedup key for cron-based rows. NULL for event-based rows.',
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  UNIQUE KEY uq_cron_date_key (cron_date_key),
  INDEX idx_user_created (user_id, created_at),
  INDEX idx_user_is_read (user_id, is_read),
  CONSTRAINT fk_notifications_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
