-- =========================================================
-- 063_reminder_tts.sql
-- Reminder TTS opt-in per reminder + log per occurrence
-- =========================================================

SET @sql := IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'reminders'
      AND COLUMN_NAME = 'enable_tts'
  ),
  'SELECT 1',
  'ALTER TABLE reminders ADD COLUMN enable_tts TINYINT(1) NOT NULL DEFAULT 0 AFTER note'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'reminders'
      AND COLUMN_NAME = 'tts_trigger_type'
  ),
  'SELECT 1',
  'ALTER TABLE reminders ADD COLUMN tts_trigger_type ENUM(''on_time'',''before_10_minutes'',''before_1_hour'') NOT NULL DEFAULT ''on_time'' AFTER enable_tts'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'reminders'
      AND COLUMN_NAME = 'tts_enabled_at'
  ),
  'SELECT 1',
  'ALTER TABLE reminders ADD COLUMN tts_enabled_at DATETIME NULL AFTER tts_trigger_type'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS reminder_tts_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  reminder_id BIGINT UNSIGNED NOT NULL,
  occurrence_date DATE NOT NULL,
  trigger_type ENUM('on_time','before_10_minutes','before_1_hour') NOT NULL,
  trigger_at DATETIME NOT NULL,
  played_at DATETIME NULL,
  played_by_user_id BIGINT UNSIGNED NULL,
  delivery_channel VARCHAR(40) NOT NULL DEFAULT 'browser_tts',
  message_text VARCHAR(255) NOT NULL,
  status ENUM('queued','played','failed','skipped') NOT NULL DEFAULT 'queued',
  error_message VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_reminder_tts_once (reminder_id, occurrence_date, trigger_type),
  KEY idx_reminder_tts_trigger (trigger_at, status),
  KEY idx_reminder_tts_scope (reminder_id, occurrence_date, trigger_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;