-- Phase 4: web push notifications, event reminders, CareMeet live roundtables
SET NAMES utf8mb4;
ALTER TABLE comm_queue MODIFY channel ENUM('email','whatsapp','push') NOT NULL;

CREATE TABLE push_subscriptions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  endpoint VARCHAR(700) NOT NULL,
  endpoint_hash CHAR(64) NOT NULL UNIQUE,
  user_agent VARCHAR(200) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_success_at DATETIME NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  KEY idx_push_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE events
  ADD COLUMN meeting_provider ENUM('link','caremeet') NOT NULL DEFAULT 'link',
  ADD COLUMN meeting_room_id VARCHAR(120) NOT NULL DEFAULT '',
  ADD COLUMN recording_url VARCHAR(500) NOT NULL DEFAULT '',
  ADD COLUMN recording_ready_at DATETIME NULL;
ALTER TABLE event_rsvps ADD COLUMN reminded VARCHAR(20) NOT NULL DEFAULT '', ADD COLUMN joined_live_at DATETIME NULL;

CREATE TABLE meeting_webhook_events (
  id VARCHAR(120) PRIMARY KEY,
  type VARCHAR(60) NOT NULL,
  received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO comm_templates (code, channel, lang, subject, body) VALUES
('event_reminder','email','en','Tomorrow: {{title}}','Hello {{name}},\n\nA reminder that {{title}} starts on {{when}}.\n{{link}}\n\n{{site}}'),
('event_reminder','email','ar','غداً: {{title}}','مرحباً {{name}}،\n\nتذكير بأن {{title}} تبدأ في {{when}}.\n{{link}}\n\n{{site}}'),
('event_reminder','email','fr','Demain : {{title}}','Bonjour {{name}},\n\nRappel : {{title}} commence le {{when}}.\n{{link}}\n\n{{site}}'),
('event_reminder','email','es','Mañana: {{title}}','Hola {{name}}:\n\nLe recordamos que {{title}} empieza el {{when}}.\n{{link}}\n\n{{site}}'),
('recording_ready','email','en','Recording available: {{title}}','Hello {{name}},\n\nThe recording of {{title}} is now available to registered members:\n{{link}}\n\n{{site}}');
INSERT INTO comm_templates (code, channel, lang, subject, body, wa_template_name) VALUES
('event_reminder','whatsapp','en','','Hello {{1}}, {{2}} starts at {{3}}. Join from the event page: {{4}}','event_reminder');