308 lines
17 KiB
PL/PgSQL
308 lines
17 KiB
PL/PgSQL
-- Monaco USA Portal 2026 - Dues Reminders Enhancement
|
|
-- Track sent reminders to avoid duplicates and enable analytics
|
|
|
|
-- ============================================
|
|
-- DUES REMINDER LOGS TABLE
|
|
-- ============================================
|
|
CREATE TABLE public.dues_reminder_logs (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
member_id UUID NOT NULL REFERENCES public.members(id) ON DELETE CASCADE,
|
|
reminder_type TEXT NOT NULL CHECK (reminder_type IN ('due_soon_30', 'due_soon_7', 'due_soon_1', 'overdue', 'grace_period', 'inactive_notice')),
|
|
due_date DATE NOT NULL,
|
|
sent_at TIMESTAMPTZ DEFAULT NOW(),
|
|
email_log_id UUID REFERENCES public.email_logs(id),
|
|
-- Prevent duplicate reminders for same member/type/period
|
|
UNIQUE(member_id, reminder_type, due_date)
|
|
);
|
|
|
|
-- Enable RLS
|
|
ALTER TABLE public.dues_reminder_logs ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Board and admin can view reminder logs
|
|
CREATE POLICY "Board/admin can view reminder logs" ON public.dues_reminder_logs
|
|
FOR SELECT USING (
|
|
EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role IN ('board', 'admin'))
|
|
);
|
|
|
|
-- Only service role can insert reminder logs (from cron/server)
|
|
CREATE POLICY "Service role can manage reminder logs" ON public.dues_reminder_logs
|
|
FOR ALL USING (true)
|
|
WITH CHECK (true);
|
|
|
|
-- Index for fast lookups
|
|
CREATE INDEX idx_reminder_logs_member_date ON public.dues_reminder_logs(member_id, due_date);
|
|
CREATE INDEX idx_reminder_logs_type_sent ON public.dues_reminder_logs(reminder_type, sent_at);
|
|
|
|
-- ============================================
|
|
-- ADD EMAIL TEMPLATES FOR DUES REMINDERS
|
|
-- ============================================
|
|
|
|
-- 30 days before due reminder
|
|
INSERT INTO public.email_templates (template_key, template_name, category, subject, body_html, body_text, is_active, is_system, variables_schema) VALUES
|
|
(
|
|
'dues_reminder_30',
|
|
'Dues Reminder - 30 Days',
|
|
'payment',
|
|
'Your Monaco USA Membership Dues Are Coming Up',
|
|
'<p style="margin: 0 0 16px 0; color: #334155; line-height: 1.6;">Dear {{first_name}},</p>
|
|
<p style="margin: 0 0 16px 0; color: #334155; line-height: 1.6;">This is a friendly reminder that your Monaco USA membership dues will be due on <strong>{{due_date}}</strong>.</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;">Payment Details:</p>
|
|
<p style="margin: 0 0 4px 0; color: #334155; font-size: 14px;"><strong>Amount Due:</strong> {{amount}}</p>
|
|
<p style="margin: 0 0 4px 0; color: #334155; font-size: 14px;"><strong>Due Date:</strong> {{due_date}}</p>
|
|
<p style="margin: 0 0 4px 0; color: #334155; font-size: 14px;"><strong>Member ID:</strong> {{member_id}}</p>
|
|
</div>
|
|
<div style="background: #fef3c7; border: 1px solid #fcd34d; border-radius: 12px; padding: 20px; margin: 0 0 20px 0;">
|
|
<p style="margin: 0 0 8px 0; color: #92400e; font-size: 14px; font-weight: 600;">Bank Transfer Details:</p>
|
|
<p style="margin: 0 0 4px 0; color: #78350f; font-size: 14px;"><strong>Account Holder:</strong> {{account_holder}}</p>
|
|
<p style="margin: 0 0 4px 0; color: #78350f; font-size: 14px;"><strong>Bank:</strong> {{bank_name}}</p>
|
|
<p style="margin: 0 0 4px 0; color: #78350f; font-size: 14px;"><strong>IBAN:</strong> {{iban}}</p>
|
|
<p style="margin: 0 0 4px 0; color: #78350f; font-size: 14px;"><strong>Reference:</strong> {{member_id}}</p>
|
|
</div>
|
|
<p style="margin: 0 0 16px 0; color: #334155; line-height: 1.6;">You can also view your payment status and history in the member portal:</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 My Account</a>
|
|
</p>
|
|
<p style="margin: 0; color: #64748b; font-size: 14px;">Thank you for being a valued member of Monaco USA!</p>',
|
|
'Dear {{first_name}},
|
|
|
|
This is a friendly reminder that your Monaco USA membership dues will be due on {{due_date}}.
|
|
|
|
Payment Details:
|
|
- Amount Due: {{amount}}
|
|
- Due Date: {{due_date}}
|
|
- Member ID: {{member_id}}
|
|
|
|
Bank Transfer Details:
|
|
- Account Holder: {{account_holder}}
|
|
- Bank: {{bank_name}}
|
|
- IBAN: {{iban}}
|
|
- Reference: {{member_id}}
|
|
|
|
Visit the member portal to view your payment status: {{portal_url}}
|
|
|
|
Thank you for being a valued member of Monaco USA!',
|
|
true,
|
|
true,
|
|
'{"first_name": "Member first name", "due_date": "Dues due date", "amount": "Amount due", "member_id": "Member ID for reference", "account_holder": "Bank account holder", "bank_name": "Bank name", "iban": "IBAN number", "portal_url": "Portal URL"}'
|
|
),
|
|
(
|
|
'dues_reminder_7',
|
|
'Dues Reminder - 7 Days',
|
|
'payment',
|
|
'Reminder: Monaco USA Dues Due in 7 Days',
|
|
'<p style="margin: 0 0 16px 0; color: #334155; line-height: 1.6;">Dear {{first_name}},</p>
|
|
<p style="margin: 0 0 16px 0; color: #334155; line-height: 1.6;">Your Monaco USA membership dues will be due in <strong>7 days</strong> on {{due_date}}.</p>
|
|
<div style="background: #fef3c7; border: 1px solid #fcd34d; border-radius: 12px; padding: 20px; margin: 0 0 20px 0;">
|
|
<p style="margin: 0 0 8px 0; color: #92400e; font-size: 14px; font-weight: 600;">Payment Information:</p>
|
|
<p style="margin: 0 0 4px 0; color: #78350f; font-size: 14px;"><strong>Amount:</strong> {{amount}}</p>
|
|
<p style="margin: 0 0 4px 0; color: #78350f; font-size: 14px;"><strong>Due Date:</strong> {{due_date}}</p>
|
|
<p style="margin: 0 0 4px 0; color: #78350f; font-size: 14px;"><strong>IBAN:</strong> {{iban}}</p>
|
|
<p style="margin: 0 0 4px 0; color: #78350f; font-size: 14px;"><strong>Reference:</strong> {{member_id}}</p>
|
|
</div>
|
|
<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;">Pay Now</a>
|
|
</p>
|
|
<p style="margin: 0; color: #64748b; font-size: 14px;">Questions? Contact us at contact@monacousa.org</p>',
|
|
'Dear {{first_name}},
|
|
|
|
Your Monaco USA membership dues will be due in 7 days on {{due_date}}.
|
|
|
|
Amount: {{amount}}
|
|
IBAN: {{iban}}
|
|
Reference: {{member_id}}
|
|
|
|
Visit the portal to pay: {{portal_url}}',
|
|
true,
|
|
true,
|
|
'{"first_name": "Member first name", "due_date": "Dues due date", "amount": "Amount due", "member_id": "Member ID", "iban": "IBAN number", "portal_url": "Portal URL"}'
|
|
),
|
|
(
|
|
'dues_reminder_1',
|
|
'Dues Reminder - 1 Day',
|
|
'payment',
|
|
'URGENT: Monaco USA Dues Due Tomorrow',
|
|
'<p style="margin: 0 0 16px 0; color: #334155; line-height: 1.6;">Dear {{first_name}},</p>
|
|
<p style="margin: 0 0 16px 0; color: #334155; line-height: 1.6;"><strong style="color: #dc2626;">Your Monaco USA membership dues are due tomorrow ({{due_date}}).</strong></p>
|
|
<div style="background: #fef2f2; border: 1px solid #fecaca; border-radius: 12px; padding: 20px; margin: 0 0 20px 0;">
|
|
<p style="margin: 0 0 8px 0; color: #991b1b; font-size: 14px; font-weight: 600;">Payment Required:</p>
|
|
<p style="margin: 0 0 4px 0; color: #7f1d1d; font-size: 14px;"><strong>Amount:</strong> {{amount}}</p>
|
|
<p style="margin: 0 0 4px 0; color: #7f1d1d; font-size: 14px;"><strong>IBAN:</strong> {{iban}}</p>
|
|
<p style="margin: 0 0 4px 0; color: #7f1d1d; font-size: 14px;"><strong>Reference:</strong> {{member_id}}</p>
|
|
</div>
|
|
<p style="margin: 0 0 16px 0; color: #334155; line-height: 1.6;">To maintain your active membership status and continued access to member benefits, please ensure payment is made by the due date.</p>
|
|
<p style="margin: 0 0 20px 0;">
|
|
<a href="{{portal_url}}" style="display: inline-block; background: #dc2626; color: white; padding: 12px 24px; border-radius: 8px; text-decoration: none; font-weight: 600;">Pay Now</a>
|
|
</p>',
|
|
'Dear {{first_name}},
|
|
|
|
URGENT: Your Monaco USA membership dues are due tomorrow ({{due_date}}).
|
|
|
|
Amount: {{amount}}
|
|
IBAN: {{iban}}
|
|
Reference: {{member_id}}
|
|
|
|
To maintain your active membership, please pay by the due date.
|
|
|
|
Pay now: {{portal_url}}',
|
|
true,
|
|
true,
|
|
'{"first_name": "Member first name", "due_date": "Dues due date", "amount": "Amount due", "member_id": "Member ID", "iban": "IBAN number", "portal_url": "Portal URL"}'
|
|
),
|
|
(
|
|
'dues_overdue',
|
|
'Dues Overdue Notice',
|
|
'payment',
|
|
'ACTION REQUIRED: Monaco USA Dues Are Now Overdue',
|
|
'<p style="margin: 0 0 16px 0; color: #334155; line-height: 1.6;">Dear {{first_name}},</p>
|
|
<p style="margin: 0 0 16px 0; color: #334155; line-height: 1.6;">Your Monaco USA membership dues are now <strong style="color: #dc2626;">{{days_overdue}} days overdue</strong>.</p>
|
|
<div style="background: #fef2f2; border: 1px solid #fecaca; border-radius: 12px; padding: 20px; margin: 0 0 20px 0;">
|
|
<p style="margin: 0 0 8px 0; color: #991b1b; font-size: 14px; font-weight: 600;">Overdue Payment:</p>
|
|
<p style="margin: 0 0 4px 0; color: #7f1d1d; font-size: 14px;"><strong>Amount:</strong> {{amount}}</p>
|
|
<p style="margin: 0 0 4px 0; color: #7f1d1d; font-size: 14px;"><strong>Original Due Date:</strong> {{due_date}}</p>
|
|
<p style="margin: 0 0 4px 0; color: #7f1d1d; font-size: 14px;"><strong>Days Overdue:</strong> {{days_overdue}}</p>
|
|
</div>
|
|
<div style="background: #fffbeb; border: 1px solid #fcd34d; border-radius: 12px; padding: 16px; margin: 0 0 20px 0;">
|
|
<p style="margin: 0; color: #92400e; font-size: 14px;"><strong>Grace Period:</strong> You have {{grace_days_remaining}} days remaining in your grace period. After this, your membership status will be changed to inactive.</p>
|
|
</div>
|
|
<p style="margin: 0 0 16px 0; color: #334155; line-height: 1.6;">Please remit payment as soon as possible to maintain your membership benefits.</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;">Payment Details:</p>
|
|
<p style="margin: 0 0 4px 0; color: #334155; font-size: 14px;"><strong>Account:</strong> {{account_holder}}</p>
|
|
<p style="margin: 0 0 4px 0; color: #334155; font-size: 14px;"><strong>IBAN:</strong> {{iban}}</p>
|
|
<p style="margin: 0 0 4px 0; color: #334155; font-size: 14px;"><strong>Reference:</strong> {{member_id}}</p>
|
|
</div>
|
|
<p style="margin: 0 0 20px 0;">
|
|
<a href="{{portal_url}}" style="display: inline-block; background: #dc2626; color: white; padding: 12px 24px; border-radius: 8px; text-decoration: none; font-weight: 600;">Pay Now</a>
|
|
</p>',
|
|
'Dear {{first_name}},
|
|
|
|
Your Monaco USA membership dues are now {{days_overdue}} days overdue.
|
|
|
|
Amount: {{amount}}
|
|
Original Due Date: {{due_date}}
|
|
Days Overdue: {{days_overdue}}
|
|
|
|
Grace Period: You have {{grace_days_remaining}} days remaining. After this, your membership will be marked inactive.
|
|
|
|
Payment Details:
|
|
- Account: {{account_holder}}
|
|
- IBAN: {{iban}}
|
|
- Reference: {{member_id}}
|
|
|
|
Pay now: {{portal_url}}',
|
|
true,
|
|
true,
|
|
'{"first_name": "Member first name", "due_date": "Original due date", "amount": "Amount due", "days_overdue": "Number of days overdue", "grace_days_remaining": "Days left in grace period", "member_id": "Member ID", "account_holder": "Account holder", "iban": "IBAN", "portal_url": "Portal URL"}'
|
|
),
|
|
(
|
|
'dues_grace_warning',
|
|
'Grace Period Ending Warning',
|
|
'payment',
|
|
'WARNING: Monaco USA Grace Period Ending Soon',
|
|
'<p style="margin: 0 0 16px 0; color: #334155; line-height: 1.6;">Dear {{first_name}},</p>
|
|
<p style="margin: 0 0 16px 0; color: #334155; line-height: 1.6;"><strong style="color: #dc2626;">Your grace period ends in {{grace_days_remaining}} days.</strong></p>
|
|
<p style="margin: 0 0 16px 0; color: #334155; line-height: 1.6;">Your membership dues of <strong>{{amount}}</strong> were due on {{due_date}} and are now {{days_overdue}} days overdue.</p>
|
|
<div style="background: #fef2f2; border: 1px solid #fecaca; border-radius: 12px; padding: 20px; margin: 0 0 20px 0;">
|
|
<p style="margin: 0; color: #991b1b; font-size: 14px;"><strong>If payment is not received by {{grace_end_date}}, your membership status will automatically change to INACTIVE and you will lose access to member benefits.</strong></p>
|
|
</div>
|
|
<p style="margin: 0 0 16px 0; color: #334155; line-height: 1.6;">Please make your payment immediately to avoid interruption:</p>
|
|
<div style="background: #f8fafc; border: 1px solid #e2e8f0; border-radius: 12px; padding: 20px; margin: 0 0 20px 0;">
|
|
<p style="margin: 0 0 4px 0; color: #334155; font-size: 14px;"><strong>IBAN:</strong> {{iban}}</p>
|
|
<p style="margin: 0 0 4px 0; color: #334155; font-size: 14px;"><strong>Reference:</strong> {{member_id}}</p>
|
|
</div>
|
|
<p style="margin: 0 0 20px 0;">
|
|
<a href="{{portal_url}}" style="display: inline-block; background: #dc2626; color: white; padding: 12px 24px; border-radius: 8px; text-decoration: none; font-weight: 600;">Pay Now - Urgent</a>
|
|
</p>',
|
|
'Dear {{first_name}},
|
|
|
|
WARNING: Your grace period ends in {{grace_days_remaining}} days.
|
|
|
|
Your dues of {{amount}} were due on {{due_date}} and are now {{days_overdue}} days overdue.
|
|
|
|
If payment is not received by {{grace_end_date}}, your membership will become INACTIVE.
|
|
|
|
IBAN: {{iban}}
|
|
Reference: {{member_id}}
|
|
|
|
Pay now: {{portal_url}}',
|
|
true,
|
|
true,
|
|
'{"first_name": "Member first name", "due_date": "Original due date", "amount": "Amount due", "days_overdue": "Days overdue", "grace_days_remaining": "Days until grace period ends", "grace_end_date": "Date grace period ends", "member_id": "Member ID", "iban": "IBAN", "portal_url": "Portal URL"}'
|
|
),
|
|
(
|
|
'dues_inactive_notice',
|
|
'Membership Marked Inactive',
|
|
'payment',
|
|
'Notice: Your Monaco USA Membership Is Now Inactive',
|
|
'<p style="margin: 0 0 16px 0; color: #334155; line-height: 1.6;">Dear {{first_name}},</p>
|
|
<p style="margin: 0 0 16px 0; color: #334155; line-height: 1.6;">Due to non-payment of membership dues, your Monaco USA membership has been marked as <strong style="color: #dc2626;">INACTIVE</strong>.</p>
|
|
<div style="background: #fef2f2; border: 1px solid #fecaca; border-radius: 12px; padding: 20px; margin: 0 0 20px 0;">
|
|
<p style="margin: 0 0 8px 0; color: #991b1b; font-size: 14px; font-weight: 600;">Status Change:</p>
|
|
<p style="margin: 0 0 4px 0; color: #7f1d1d; font-size: 14px;"><strong>Previous Status:</strong> Active</p>
|
|
<p style="margin: 0 0 4px 0; color: #7f1d1d; font-size: 14px;"><strong>New Status:</strong> Inactive</p>
|
|
<p style="margin: 0 0 4px 0; color: #7f1d1d; font-size: 14px;"><strong>Outstanding Amount:</strong> {{amount}}</p>
|
|
</div>
|
|
<p style="margin: 0 0 16px 0; color: #334155; line-height: 1.6;">As an inactive member, you will no longer have access to:</p>
|
|
<ul style="margin: 0 0 16px 0; padding-left: 20px; color: #334155;">
|
|
<li>Member-only events</li>
|
|
<li>Member directory</li>
|
|
<li>Member communications</li>
|
|
<li>Voting rights</li>
|
|
</ul>
|
|
<p style="margin: 0 0 16px 0; color: #334155; line-height: 1.6;">To reactivate your membership, please pay your outstanding dues:</p>
|
|
<div style="background: #f8fafc; border: 1px solid #e2e8f0; border-radius: 12px; padding: 20px; margin: 0 0 20px 0;">
|
|
<p style="margin: 0 0 4px 0; color: #334155; font-size: 14px;"><strong>Account:</strong> {{account_holder}}</p>
|
|
<p style="margin: 0 0 4px 0; color: #334155; font-size: 14px;"><strong>IBAN:</strong> {{iban}}</p>
|
|
<p style="margin: 0 0 4px 0; color: #334155; font-size: 14px;"><strong>Reference:</strong> {{member_id}}</p>
|
|
</div>
|
|
<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;">Reactivate My Membership</a>
|
|
</p>
|
|
<p style="margin: 0; color: #64748b; font-size: 14px;">If you believe this is an error or have questions, please contact us at contact@monacousa.org</p>',
|
|
'Dear {{first_name}},
|
|
|
|
Due to non-payment of membership dues, your Monaco USA membership has been marked as INACTIVE.
|
|
|
|
Outstanding Amount: {{amount}}
|
|
|
|
As an inactive member, you no longer have access to member-only events, directory, communications, or voting rights.
|
|
|
|
To reactivate, please pay your dues:
|
|
- Account: {{account_holder}}
|
|
- IBAN: {{iban}}
|
|
- Reference: {{member_id}}
|
|
|
|
Reactivate: {{portal_url}}
|
|
|
|
Questions? Contact contact@monacousa.org',
|
|
true,
|
|
true,
|
|
'{"first_name": "Member first name", "amount": "Outstanding amount", "member_id": "Member ID", "account_holder": "Account holder", "iban": "IBAN", "portal_url": "Portal URL"}'
|
|
)
|
|
ON CONFLICT (template_key) DO NOTHING;
|
|
|
|
-- ============================================
|
|
-- HELPER FUNCTION: Get dues settings
|
|
-- ============================================
|
|
CREATE OR REPLACE FUNCTION get_dues_settings()
|
|
RETURNS TABLE (
|
|
reminder_days_before INTEGER[],
|
|
grace_period_days INTEGER,
|
|
auto_inactive_enabled BOOLEAN,
|
|
payment_iban TEXT,
|
|
payment_account_holder TEXT,
|
|
payment_bank_name TEXT
|
|
) AS $$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT
|
|
COALESCE((SELECT (setting_value)::INTEGER[] FROM app_settings WHERE category = 'dues' AND setting_key = 'reminder_days_before'), ARRAY[30, 7, 1])::INTEGER[],
|
|
COALESCE((SELECT (setting_value)::INTEGER FROM app_settings WHERE category = 'dues' AND setting_key = 'grace_period_days'), 30)::INTEGER,
|
|
COALESCE((SELECT (setting_value)::BOOLEAN FROM app_settings WHERE category = 'dues' AND setting_key = 'auto_inactive_enabled'), true)::BOOLEAN,
|
|
COALESCE((SELECT setting_value::TEXT FROM app_settings WHERE category = 'dues' AND setting_key = 'payment_iban'), '')::TEXT,
|
|
COALESCE((SELECT setting_value::TEXT FROM app_settings WHERE category = 'dues' AND setting_key = 'payment_account_holder'), '')::TEXT,
|
|
COALESCE((SELECT setting_value::TEXT FROM app_settings WHERE category = 'dues' AND setting_key = 'payment_bank_name'), '')::TEXT;
|
|
END;
|
|
$$ LANGUAGE plpgsql STABLE;
|