-- migrate:up

CREATE TABLE bot_message_templates (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  template_key VARCHAR(120) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  locale VARCHAR(16) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT 'bn',
  body_html TEXT NOT NULL,
  button_config JSON NULL,
  allowed_placeholders JSON NOT NULL,
  status ENUM('draft', 'active', 'archived') NOT NULL DEFAULT 'draft',
  version INT UNSIGNED NOT NULL DEFAULT 1,
  updated_by_admin_id BIGINT UNSIGNED NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_bot_message_templates_key_locale (template_key, locale),
  CONSTRAINT fk_bot_message_templates_updater FOREIGN KEY (updated_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL,
  CONSTRAINT chk_bot_message_templates_version CHECK (version > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE notifications (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  notification_type VARCHAR(80) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  title VARCHAR(191) NOT NULL,
  body VARCHAR(2000) NOT NULL,
  action JSON NULL,
  deduplication_key VARCHAR(191) CHARACTER SET ascii COLLATE ascii_bin NULL,
  read_at DATETIME(6) NULL,
  expires_at DATETIME(6) NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_notifications_public_id (public_id),
  UNIQUE KEY uq_notifications_deduplication (deduplication_key),
  KEY idx_notifications_user_time (user_id, created_at, id),
  CONSTRAINT fk_notifications_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE reminder_schedules (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  name VARCHAR(191) NOT NULL,
  template_id BIGINT UNSIGNED NOT NULL,
  timezone VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  cron_expression VARCHAR(120) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  active_days JSON NOT NULL,
  target_rules JSON NOT NULL,
  status ENUM('draft', 'active', 'paused', 'archived') NOT NULL DEFAULT 'draft',
  next_run_at DATETIME(6) NULL,
  created_by_admin_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_reminder_schedules_public_id (public_id),
  KEY idx_reminder_schedules_due (status, next_run_at),
  CONSTRAINT fk_reminder_schedules_template FOREIGN KEY (template_id) REFERENCES bot_message_templates (id) ON DELETE RESTRICT,
  CONSTRAINT fk_reminder_schedules_creator FOREIGN KEY (created_by_admin_id) REFERENCES admins (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE reminder_deliveries (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  reminder_schedule_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  delivery_key VARCHAR(191) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  scheduled_for DATETIME(6) NOT NULL,
  status ENUM('queued', 'sent', 'failed', 'skipped') NOT NULL DEFAULT 'queued',
  attempt_count SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  telegram_message_id BIGINT NULL,
  last_error_code VARCHAR(120) CHARACTER SET ascii COLLATE ascii_bin NULL,
  sent_at DATETIME(6) NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_reminder_deliveries_key (delivery_key),
  KEY idx_reminder_deliveries_queue (status, scheduled_for, id),
  KEY idx_reminder_deliveries_user (user_id, created_at),
  CONSTRAINT fk_reminder_deliveries_schedule FOREIGN KEY (reminder_schedule_id) REFERENCES reminder_schedules (id) ON DELETE RESTRICT,
  CONSTRAINT fk_reminder_deliveries_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE telegram_updates (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  update_id BIGINT UNSIGNED NOT NULL,
  update_type VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  payload_hash BINARY(32) NOT NULL,
  processing_status ENUM('received', 'processing', 'processed', 'ignored', 'failed') NOT NULL DEFAULT 'received',
  attempt_count SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  received_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  processed_at DATETIME(6) NULL,
  last_error_code VARCHAR(120) CHARACTER SET ascii COLLATE ascii_bin NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_telegram_updates_update_id (update_id),
  KEY idx_telegram_updates_status_time (processing_status, received_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE broadcasts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  name VARCHAR(191) NOT NULL,
  template_id BIGINT UNSIGNED NOT NULL,
  target_rules JSON NOT NULL,
  status ENUM('draft', 'approved', 'queued', 'sending', 'completed', 'cancelled', 'failed') NOT NULL DEFAULT 'draft',
  approved_by_admin_id BIGINT UNSIGNED NULL,
  approved_at DATETIME(6) NULL,
  scheduled_at DATETIME(6) NULL,
  created_by_admin_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_broadcasts_public_id (public_id),
  KEY idx_broadcasts_queue (status, scheduled_at),
  CONSTRAINT fk_broadcasts_template FOREIGN KEY (template_id) REFERENCES bot_message_templates (id) ON DELETE RESTRICT,
  CONSTRAINT fk_broadcasts_approver FOREIGN KEY (approved_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL,
  CONSTRAINT fk_broadcasts_creator FOREIGN KEY (created_by_admin_id) REFERENCES admins (id) ON DELETE RESTRICT,
  CONSTRAINT chk_broadcasts_separation CHECK (approved_by_admin_id IS NULL OR approved_by_admin_id <> created_by_admin_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE broadcast_deliveries (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  broadcast_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  status ENUM('queued', 'sent', 'failed', 'skipped') NOT NULL DEFAULT 'queued',
  attempt_count SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  telegram_message_id BIGINT NULL,
  last_error_code VARCHAR(120) CHARACTER SET ascii COLLATE ascii_bin NULL,
  sent_at DATETIME(6) NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_broadcast_deliveries_recipient (broadcast_id, user_id),
  KEY idx_broadcast_deliveries_queue (status, id),
  CONSTRAINT fk_broadcast_deliveries_broadcast FOREIGN KEY (broadcast_id) REFERENCES broadcasts (id) ON DELETE RESTRICT,
  CONSTRAINT fk_broadcast_deliveries_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- migrate:down

DROP TABLE IF EXISTS broadcast_deliveries;
DROP TABLE IF EXISTS broadcasts;
DROP TABLE IF EXISTS telegram_updates;
DROP TABLE IF EXISTS reminder_deliveries;
DROP TABLE IF EXISTS reminder_schedules;
DROP TABLE IF EXISTS notifications;
DROP TABLE IF EXISTS bot_message_templates;