134 lines
5.8 KiB
SQL
134 lines
5.8 KiB
SQL
-- Monaco USA Portal 2026 - Event Reminder Emails
|
|
-- Automated reminders sent 24 hours before events to RSVPed members
|
|
|
|
-- ============================================
|
|
-- EVENT REMINDER LOGS TABLE
|
|
-- ============================================
|
|
CREATE TABLE public.event_reminder_logs (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
event_id UUID NOT NULL REFERENCES public.events(id) ON DELETE CASCADE,
|
|
rsvp_id UUID NOT NULL REFERENCES public.event_rsvps(id) ON DELETE CASCADE,
|
|
member_id UUID NOT NULL REFERENCES public.members(id) ON DELETE CASCADE,
|
|
reminder_type TEXT NOT NULL DEFAULT '24hr' CHECK (reminder_type IN ('24hr', '1hr', 'day_of')),
|
|
sent_at TIMESTAMPTZ DEFAULT NOW(),
|
|
email_log_id UUID REFERENCES public.email_logs(id),
|
|
-- Prevent duplicate reminders for same event/member/type
|
|
UNIQUE(event_id, member_id, reminder_type)
|
|
);
|
|
|
|
-- Enable RLS
|
|
ALTER TABLE public.event_reminder_logs ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Board and admin can view reminder logs
|
|
CREATE POLICY "Board/admin can view event reminder logs" ON public.event_reminder_logs
|
|
FOR SELECT USING (
|
|
EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role IN ('board', 'admin'))
|
|
);
|
|
|
|
-- Service role can manage reminder logs (from cron/server)
|
|
CREATE POLICY "Service role can manage event reminder logs" ON public.event_reminder_logs
|
|
FOR ALL USING (true)
|
|
WITH CHECK (true);
|
|
|
|
-- Indexes for fast lookups
|
|
CREATE INDEX idx_event_reminder_logs_event ON public.event_reminder_logs(event_id);
|
|
CREATE INDEX idx_event_reminder_logs_member ON public.event_reminder_logs(member_id);
|
|
CREATE INDEX idx_event_reminder_logs_sent ON public.event_reminder_logs(sent_at);
|
|
|
|
-- ============================================
|
|
-- ADD APP SETTINGS FOR EVENT REMINDERS
|
|
-- ============================================
|
|
INSERT INTO public.app_settings (category, setting_key, setting_value, display_name, description, is_public)
|
|
VALUES
|
|
('events', 'event_reminders_enabled', 'true', 'Event Reminders Enabled', 'Enable automated event reminder emails', false),
|
|
('events', 'event_reminder_hours_before', '24', 'Reminder Hours Before', 'Hours before event to send reminder', false)
|
|
ON CONFLICT (category, setting_key) DO NOTHING;
|
|
|
|
-- ============================================
|
|
-- ADD EMAIL TEMPLATE FOR EVENT REMINDER
|
|
-- ============================================
|
|
INSERT INTO public.email_templates (template_key, template_name, category, subject, body_html, body_text, is_active, is_system, variables_schema) VALUES
|
|
(
|
|
'event_reminder_24hr',
|
|
'Event Reminder - 24 Hours',
|
|
'events',
|
|
'Reminder: {{event_title}} is Tomorrow!',
|
|
'<p style="margin: 0 0 16px 0; color: #334155; line-height: 1.6;">Hi {{first_name}},</p>
|
|
<p style="margin: 0 0 16px 0; color: #334155; line-height: 1.6;">This is a friendly reminder that <strong>{{event_title}}</strong> is happening tomorrow!</p>
|
|
<div style="background: #f8fafc; border: 1px solid #e2e8f0; border-radius: 12px; padding: 20px; margin: 0 0 20px 0;">
|
|
<p style="margin: 0 0 8px 0; color: #334155; font-size: 14px; font-weight: 600;">Event Details:</p>
|
|
<p style="margin: 0 0 4px 0; color: #334155; font-size: 14px;"><strong>Date:</strong> {{event_date}}</p>
|
|
<p style="margin: 0 0 4px 0; color: #334155; font-size: 14px;"><strong>Time:</strong> {{event_time}}</p>
|
|
<p style="margin: 0 0 4px 0; color: #334155; font-size: 14px;"><strong>Location:</strong> {{event_location}}</p>
|
|
{{#if guest_count}}
|
|
<p style="margin: 8px 0 0 0; color: #64748b; font-size: 14px;"><em>You''re bringing {{guest_count}} guest(s)</em></p>
|
|
{{/if}}
|
|
</div>
|
|
<p style="margin: 0 0 16px 0; color: #334155; line-height: 1.6;">We look forward to seeing you there!</p>
|
|
<p style="margin: 0 0 20px 0;">
|
|
<a href="{{portal_url}}" style="display: inline-block; background: #CE1126; color: white; padding: 12px 24px; border-radius: 8px; text-decoration: none; font-weight: 600;">View Event Details</a>
|
|
</p>
|
|
<p style="margin: 0; color: #64748b; font-size: 12px;">Can''t make it? Please update your RSVP so we can offer your spot to someone on the waitlist.</p>',
|
|
'Hi {{first_name}},
|
|
|
|
This is a friendly reminder that {{event_title}} is happening tomorrow!
|
|
|
|
Event Details:
|
|
- Date: {{event_date}}
|
|
- Time: {{event_time}}
|
|
- Location: {{event_location}}
|
|
{{#if guest_count}}
|
|
- You''re bringing {{guest_count}} guest(s)
|
|
{{/if}}
|
|
|
|
We look forward to seeing you there!
|
|
|
|
View event: {{portal_url}}
|
|
|
|
Can''t make it? Please update your RSVP so we can offer your spot to someone on the waitlist.',
|
|
true,
|
|
true,
|
|
'{"first_name": "Member first name", "event_title": "Event title", "event_date": "Event date", "event_time": "Event start time", "event_location": "Event location", "guest_count": "Number of guests", "portal_url": "Event URL in portal"}'
|
|
)
|
|
ON CONFLICT (template_key) DO NOTHING;
|
|
|
|
-- ============================================
|
|
-- VIEW: Events needing reminders
|
|
-- ============================================
|
|
CREATE OR REPLACE VIEW public.events_needing_reminders AS
|
|
SELECT
|
|
e.id AS event_id,
|
|
e.title AS event_title,
|
|
e.start_datetime,
|
|
e.end_datetime,
|
|
e.location,
|
|
e.timezone,
|
|
r.id AS rsvp_id,
|
|
r.member_id,
|
|
r.guest_count,
|
|
r.status AS rsvp_status,
|
|
m.first_name,
|
|
m.last_name,
|
|
m.email
|
|
FROM public.events e
|
|
JOIN public.event_rsvps r ON r.event_id = e.id
|
|
JOIN public.members m ON m.id = r.member_id
|
|
WHERE
|
|
-- Event is published
|
|
e.status = 'published'
|
|
-- Event starts within 24-25 hours from now (hourly cron window)
|
|
AND e.start_datetime > NOW()
|
|
AND e.start_datetime <= NOW() + INTERVAL '25 hours'
|
|
AND e.start_datetime > NOW() + INTERVAL '23 hours'
|
|
-- Member has confirmed RSVP
|
|
AND r.status = 'confirmed'
|
|
-- No reminder already sent for this event/member
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM public.event_reminder_logs erl
|
|
WHERE erl.event_id = e.id
|
|
AND erl.member_id = r.member_id
|
|
AND erl.reminder_type = '24hr'
|
|
)
|
|
-- Member has email
|
|
AND m.email IS NOT NULL;
|