-- Monaco USA Portal 2026
-- Migration 003: Storage Buckets and Audit Logging
-- ================================================

-- ============================================
-- STORAGE BUCKETS
-- ============================================

-- Documents bucket (public for direct URL access - visibility controlled at app level)
INSERT INTO storage.buckets (id, name, public, file_size_limit, allowed_mime_types)
VALUES (
  'documents',
  'documents',
  true,
  52428800, -- 50MB
  ARRAY['application/pdf', 'application/msword', 'application/vnd.openxmlformats-officedocument.wordprocessingml.document', 'application/vnd.ms-excel', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', 'application/vnd.ms-powerpoint', 'application/vnd.openxmlformats-officedocument.presentationml.presentation', 'text/plain', 'text/csv', 'application/json', 'image/jpeg', 'image/png', 'image/webp', 'image/gif']
)
ON CONFLICT (id) DO UPDATE SET
  public = true,
  file_size_limit = EXCLUDED.file_size_limit,
  allowed_mime_types = EXCLUDED.allowed_mime_types;

-- Avatars bucket (public for display)
INSERT INTO storage.buckets (id, name, public, file_size_limit, allowed_mime_types)
VALUES (
  'avatars',
  'avatars',
  true,
  5242880, -- 5MB
  ARRAY['image/jpeg', 'image/png', 'image/webp', 'image/gif']
)
ON CONFLICT (id) DO UPDATE SET
  public = true,
  file_size_limit = EXCLUDED.file_size_limit,
  allowed_mime_types = EXCLUDED.allowed_mime_types;

-- Event images bucket (public for display)
INSERT INTO storage.buckets (id, name, public, file_size_limit, allowed_mime_types)
VALUES (
  'event-images',
  'event-images',
  true,
  10485760, -- 10MB
  ARRAY['image/jpeg', 'image/png', 'image/webp']
)
ON CONFLICT (id) DO UPDATE SET
  public = true,
  file_size_limit = EXCLUDED.file_size_limit,
  allowed_mime_types = EXCLUDED.allowed_mime_types;

-- ============================================
-- STORAGE POLICIES
-- ============================================

-- Documents bucket policies
DROP POLICY IF EXISTS "documents_read_policy" ON storage.objects;
CREATE POLICY "documents_read_policy" ON storage.objects FOR SELECT
USING (bucket_id = 'documents' AND auth.role() = 'authenticated');

DROP POLICY IF EXISTS "documents_insert_policy" ON storage.objects;
CREATE POLICY "documents_insert_policy" ON storage.objects FOR INSERT
WITH CHECK (
  bucket_id = 'documents'
  AND auth.role() = 'authenticated'
  AND EXISTS (
    SELECT 1 FROM public.members
    WHERE id = auth.uid()
    AND role IN ('board', 'admin')
  )
);

DROP POLICY IF EXISTS "documents_delete_policy" ON storage.objects;
CREATE POLICY "documents_delete_policy" ON storage.objects FOR DELETE
USING (
  bucket_id = 'documents'
  AND EXISTS (
    SELECT 1 FROM public.members
    WHERE id = auth.uid()
    AND role = 'admin'
  )
);

-- Avatars bucket policies (public read, user-specific write)
DROP POLICY IF EXISTS "avatars_read_policy" ON storage.objects;
CREATE POLICY "avatars_read_policy" ON storage.objects FOR SELECT
USING (bucket_id = 'avatars');

DROP POLICY IF EXISTS "avatars_insert_policy" ON storage.objects;
CREATE POLICY "avatars_insert_policy" ON storage.objects FOR INSERT
WITH CHECK (
  bucket_id = 'avatars'
  AND auth.role() = 'authenticated'
  AND (storage.foldername(name))[1] = auth.uid()::text
);

DROP POLICY IF EXISTS "avatars_update_policy" ON storage.objects;
CREATE POLICY "avatars_update_policy" ON storage.objects FOR UPDATE
USING (
  bucket_id = 'avatars'
  AND auth.role() = 'authenticated'
  AND (storage.foldername(name))[1] = auth.uid()::text
);

DROP POLICY IF EXISTS "avatars_delete_policy" ON storage.objects;
CREATE POLICY "avatars_delete_policy" ON storage.objects FOR DELETE
USING (
  bucket_id = 'avatars'
  AND auth.role() = 'authenticated'
  AND (storage.foldername(name))[1] = auth.uid()::text
);

-- Event images bucket policies
DROP POLICY IF EXISTS "event_images_read_policy" ON storage.objects;
CREATE POLICY "event_images_read_policy" ON storage.objects FOR SELECT
USING (bucket_id = 'event-images');

DROP POLICY IF EXISTS "event_images_insert_policy" ON storage.objects;
CREATE POLICY "event_images_insert_policy" ON storage.objects FOR INSERT
WITH CHECK (
  bucket_id = 'event-images'
  AND auth.role() = 'authenticated'
  AND EXISTS (
    SELECT 1 FROM public.members
    WHERE id = auth.uid()
    AND role IN ('board', 'admin')
  )
);

DROP POLICY IF EXISTS "event_images_delete_policy" ON storage.objects;
CREATE POLICY "event_images_delete_policy" ON storage.objects FOR DELETE
USING (
  bucket_id = 'event-images'
  AND EXISTS (
    SELECT 1 FROM public.members
    WHERE id = auth.uid()
    AND role IN ('board', 'admin')
  )
);

-- ============================================
-- AUDIT LOGS TABLE
-- ============================================

CREATE TABLE IF NOT EXISTS audit_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  user_email TEXT,
  action TEXT NOT NULL,
  resource_type TEXT,
  resource_id TEXT,
  details JSONB DEFAULT '{}',
  ip_address TEXT,
  user_agent TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index for querying audit logs
CREATE INDEX IF NOT EXISTS idx_audit_logs_user_id ON audit_logs(user_id);
CREATE INDEX IF NOT EXISTS idx_audit_logs_action ON audit_logs(action);
CREATE INDEX IF NOT EXISTS idx_audit_logs_resource_type ON audit_logs(resource_type);
CREATE INDEX IF NOT EXISTS idx_audit_logs_created_at ON audit_logs(created_at DESC);

-- RLS for audit logs (only admins can read, service role can write)
ALTER TABLE audit_logs ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "audit_logs_read_admin" ON audit_logs;
CREATE POLICY "audit_logs_read_admin" ON audit_logs FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM public.members
    WHERE id = auth.uid()
    AND role = 'admin'
  )
);

-- ============================================
-- DEFAULT EMAIL TEMPLATES
-- ============================================

-- Insert default email templates if they don't exist
-- Using Monaco-branded design matching the login screen
INSERT INTO email_templates (template_key, template_name, category, subject, body_html, body_text, is_system)
VALUES
  (
    'welcome',
    'Welcome Email',
    'member',
    'Welcome to Monaco USA, {{first_name}}!',
    '<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
</head>
<body style="margin: 0; padding: 0; font-family: Arial, sans-serif; background-color: #0f172a;">
  <table role="presentation" width="100%" cellspacing="0" cellpadding="0" style="background: linear-gradient(135deg, rgba(15, 23, 42, 0.9) 0%, rgba(30, 41, 59, 0.85) 50%, rgba(127, 29, 29, 0.8) 100%); background-color: #0f172a;">
    <tr>
      <td align="center" style="padding: 40px 20px;">
        <!-- Logo Section -->
        <table role="presentation" width="100%" cellspacing="0" cellpadding="0" style="max-width: 480px;">
          <tr>
            <td align="center" style="padding-bottom: 30px;">
              <div style="background: rgba(255, 255, 255, 0.95); border-radius: 16px; padding: 12px; display: inline-block; box-shadow: 0 10px 40px rgba(0, 0, 0, 0.3);">
                <img src="{{logo_url}}" alt="Monaco USA" width="80" height="80" style="display: block; border-radius: 8px;">
              </div>
              <h1 style="margin: 16px 0 4px 0; font-size: 24px; font-weight: bold; color: #ffffff; text-shadow: 0 2px 4px rgba(0, 0, 0, 0.3);">Monaco <span style="color: #fca5a5;">USA</span></h1>
              <p style="margin: 0; font-size: 14px; color: rgba(255, 255, 255, 0.8);">Americans in Monaco</p>
            </td>
          </tr>
        </table>

        <!-- Main Content Card -->
        <table role="presentation" cellspacing="0" cellpadding="0" style="max-width: 480px; width: 100%; background: #ffffff; border-radius: 16px; box-shadow: 0 20px 60px rgba(0, 0, 0, 0.3);">
          <tr>
            <td style="padding: 40px;">
              <h2 style="margin: 0 0 20px 0; color: #CE1126; font-size: 24px;">Welcome to Monaco USA!</h2>
              <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;">We are thrilled to welcome you to the Monaco USA community! Your membership has been created and you can now access all member features.</p>
              <p style="margin: 0 0 12px 0; color: #334155; font-weight: 600;">To get started:</p>
              <ol style="margin: 0 0 20px 20px; padding: 0; color: #334155; line-height: 1.8;">
                <li>Set up your password using the separate email we sent</li>
                <li>Complete your profile with your details</li>
                <li>Explore upcoming events and connect with fellow members</li>
              </ol>
              <p style="margin: 0 0 20px 0; color: #334155; line-height: 1.6;">If you have any questions, please don''t hesitate to reach out to our board members.</p>
              <p style="margin: 0; color: #334155;">Best regards,<br><strong style="color: #CE1126;">The Monaco USA Team</strong></p>
            </td>
          </tr>
        </table>

        <!-- Footer -->
        <table role="presentation" width="100%" cellspacing="0" cellpadding="0" style="max-width: 480px;">
          <tr>
            <td align="center" style="padding-top: 24px;">
              <p style="margin: 0; font-size: 12px; color: rgba(255, 255, 255, 0.5);">&copy; 2026 Monaco USA. All rights reserved.</p>
            </td>
          </tr>
        </table>
      </td>
    </tr>
  </table>
</body>
</html>',
    'Welcome to Monaco USA, {{first_name}}! Your membership has been created. Please set up your password and complete your profile.',
    true
  ),
  (
    'waitlist_promotion',
    'Waitlist Promotion',
    'event',
    'Great news! You''re confirmed for {{event_title}}',
    '<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
</head>
<body style="margin: 0; padding: 0; font-family: Arial, sans-serif; background-color: #0f172a;">
  <table role="presentation" width="100%" cellspacing="0" cellpadding="0" style="background: linear-gradient(135deg, rgba(15, 23, 42, 0.9) 0%, rgba(30, 41, 59, 0.85) 50%, rgba(127, 29, 29, 0.8) 100%); background-color: #0f172a;">
    <tr>
      <td align="center" style="padding: 40px 20px;">
        <!-- Logo Section -->
        <table role="presentation" width="100%" cellspacing="0" cellpadding="0" style="max-width: 480px;">
          <tr>
            <td align="center" style="padding-bottom: 30px;">
              <div style="background: rgba(255, 255, 255, 0.95); border-radius: 16px; padding: 12px; display: inline-block; box-shadow: 0 10px 40px rgba(0, 0, 0, 0.3);">
                <img src="{{logo_url}}" alt="Monaco USA" width="80" height="80" style="display: block; border-radius: 8px;">
              </div>
              <h1 style="margin: 16px 0 4px 0; font-size: 24px; font-weight: bold; color: #ffffff; text-shadow: 0 2px 4px rgba(0, 0, 0, 0.3);">Monaco <span style="color: #fca5a5;">USA</span></h1>
              <p style="margin: 0; font-size: 14px; color: rgba(255, 255, 255, 0.8);">Americans in Monaco</p>
            </td>
          </tr>
        </table>

        <!-- Main Content Card -->
        <table role="presentation" cellspacing="0" cellpadding="0" style="max-width: 480px; width: 100%; background: #ffffff; border-radius: 16px; box-shadow: 0 20px 60px rgba(0, 0, 0, 0.3);">
          <tr>
            <td style="padding: 40px;">
              <h2 style="margin: 0 0 20px 0; color: #CE1126; font-size: 24px;">You''re In!</h2>
              <p style="margin: 0 0 16px 0; color: #334155; line-height: 1.6;">Dear {{first_name}},</p>
              <p style="margin: 0 0 20px 0; color: #334155; line-height: 1.6;">Great news! A spot has opened up for <strong>{{event_title}}</strong> and you have been moved from the waitlist to confirmed!</p>
              <div style="background: #f8fafc; border-radius: 12px; padding: 20px; margin: 0 0 20px 0;">
                <p style="margin: 0 0 8px 0; color: #64748b; font-size: 12px; text-transform: uppercase; letter-spacing: 0.5px;">Event Details</p>
                <p style="margin: 0 0 8px 0; color: #334155;"><strong>Date:</strong> {{event_date}}</p>
                <p style="margin: 0; color: #334155;"><strong>Location:</strong> {{event_location}}</p>
              </div>
              <p style="margin: 0 0 20px 0; color: #334155; line-height: 1.6;">We look forward to seeing you there!</p>
              <p style="margin: 0; color: #334155;">Best regards,<br><strong style="color: #CE1126;">The Monaco USA Team</strong></p>
            </td>
          </tr>
        </table>

        <!-- Footer -->
        <table role="presentation" width="100%" cellspacing="0" cellpadding="0" style="max-width: 480px;">
          <tr>
            <td align="center" style="padding-top: 24px;">
              <p style="margin: 0; font-size: 12px; color: rgba(255, 255, 255, 0.5);">&copy; 2026 Monaco USA. All rights reserved.</p>
            </td>
          </tr>
        </table>
      </td>
    </tr>
  </table>
</body>
</html>',
    'Great news! A spot has opened up for {{event_title}} and you''ve been confirmed. See you on {{event_date}} at {{event_location}}!',
    true
  ),
  (
    'rsvp_confirmation',
    'RSVP Confirmation',
    'event',
    'RSVP Confirmed: {{event_title}}',
    '<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
</head>
<body style="margin: 0; padding: 0; font-family: Arial, sans-serif; background-color: #0f172a;">
  <table role="presentation" width="100%" cellspacing="0" cellpadding="0" style="background: linear-gradient(135deg, rgba(15, 23, 42, 0.9) 0%, rgba(30, 41, 59, 0.85) 50%, rgba(127, 29, 29, 0.8) 100%); background-color: #0f172a;">
    <tr>
      <td align="center" style="padding: 40px 20px;">
        <!-- Logo Section -->
        <table role="presentation" width="100%" cellspacing="0" cellpadding="0" style="max-width: 480px;">
          <tr>
            <td align="center" style="padding-bottom: 30px;">
              <div style="background: rgba(255, 255, 255, 0.95); border-radius: 16px; padding: 12px; display: inline-block; box-shadow: 0 10px 40px rgba(0, 0, 0, 0.3);">
                <img src="{{logo_url}}" alt="Monaco USA" width="80" height="80" style="display: block; border-radius: 8px;">
              </div>
              <h1 style="margin: 16px 0 4px 0; font-size: 24px; font-weight: bold; color: #ffffff; text-shadow: 0 2px 4px rgba(0, 0, 0, 0.3);">Monaco <span style="color: #fca5a5;">USA</span></h1>
              <p style="margin: 0; font-size: 14px; color: rgba(255, 255, 255, 0.8);">Americans in Monaco</p>
            </td>
          </tr>
        </table>

        <!-- Main Content Card -->
        <table role="presentation" cellspacing="0" cellpadding="0" style="max-width: 480px; width: 100%; background: #ffffff; border-radius: 16px; box-shadow: 0 20px 60px rgba(0, 0, 0, 0.3);">
          <tr>
            <td style="padding: 40px;">
              <h2 style="margin: 0 0 20px 0; color: #CE1126; font-size: 24px;">RSVP Confirmed!</h2>
              <p style="margin: 0 0 16px 0; color: #334155; line-height: 1.6;">Dear {{first_name}},</p>
              <p style="margin: 0 0 20px 0; color: #334155; line-height: 1.6;">Your RSVP for <strong>{{event_title}}</strong> has been confirmed.</p>
              <div style="background: #f8fafc; border-radius: 12px; padding: 20px; margin: 0 0 20px 0;">
                <p style="margin: 0 0 8px 0; color: #64748b; font-size: 12px; text-transform: uppercase; letter-spacing: 0.5px;">Event Details</p>
                <p style="margin: 0 0 8px 0; color: #334155;"><strong>Date:</strong> {{event_date}}</p>
                <p style="margin: 0 0 8px 0; color: #334155;"><strong>Time:</strong> {{event_time}}</p>
                <p style="margin: 0 0 8px 0; color: #334155;"><strong>Location:</strong> {{event_location}}</p>
                <p style="margin: 0; color: #334155;"><strong>Guests:</strong> {{guest_count}}</p>
              </div>
              <p style="margin: 0 0 20px 0; color: #334155; line-height: 1.6;">We look forward to seeing you!</p>
              <p style="margin: 0; color: #334155;">Best regards,<br><strong style="color: #CE1126;">The Monaco USA Team</strong></p>
            </td>
          </tr>
        </table>

        <!-- Footer -->
        <table role="presentation" width="100%" cellspacing="0" cellpadding="0" style="max-width: 480px;">
          <tr>
            <td align="center" style="padding-top: 24px;">
              <p style="margin: 0; font-size: 12px; color: rgba(255, 255, 255, 0.5);">&copy; 2026 Monaco USA. All rights reserved.</p>
            </td>
          </tr>
        </table>
      </td>
    </tr>
  </table>
</body>
</html>',
    'Your RSVP for {{event_title}} is confirmed! See you on {{event_date}} at {{event_location}}.',
    true
  ),
  (
    'payment_received',
    'Payment Received',
    'dues',
    'Payment Received - Monaco USA',
    '<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
</head>
<body style="margin: 0; padding: 0; font-family: Arial, sans-serif; background-color: #0f172a;">
  <table role="presentation" width="100%" cellspacing="0" cellpadding="0" style="background: linear-gradient(135deg, rgba(15, 23, 42, 0.9) 0%, rgba(30, 41, 59, 0.85) 50%, rgba(127, 29, 29, 0.8) 100%); background-color: #0f172a;">
    <tr>
      <td align="center" style="padding: 40px 20px;">
        <!-- Logo Section -->
        <table role="presentation" width="100%" cellspacing="0" cellpadding="0" style="max-width: 480px;">
          <tr>
            <td align="center" style="padding-bottom: 30px;">
              <div style="background: rgba(255, 255, 255, 0.95); border-radius: 16px; padding: 12px; display: inline-block; box-shadow: 0 10px 40px rgba(0, 0, 0, 0.3);">
                <img src="{{logo_url}}" alt="Monaco USA" width="80" height="80" style="display: block; border-radius: 8px;">
              </div>
              <h1 style="margin: 16px 0 4px 0; font-size: 24px; font-weight: bold; color: #ffffff; text-shadow: 0 2px 4px rgba(0, 0, 0, 0.3);">Monaco <span style="color: #fca5a5;">USA</span></h1>
              <p style="margin: 0; font-size: 14px; color: rgba(255, 255, 255, 0.8);">Americans in Monaco</p>
            </td>
          </tr>
        </table>

        <!-- Main Content Card -->
        <table role="presentation" cellspacing="0" cellpadding="0" style="max-width: 480px; width: 100%; background: #ffffff; border-radius: 16px; box-shadow: 0 20px 60px rgba(0, 0, 0, 0.3);">
          <tr>
            <td style="padding: 40px;">
              <h2 style="margin: 0 0 20px 0; color: #CE1126; font-size: 24px;">Payment Received</h2>
              <p style="margin: 0 0 16px 0; color: #334155; line-height: 1.6;">Dear {{first_name}},</p>
              <p style="margin: 0 0 20px 0; color: #334155; line-height: 1.6;">We have received your payment. Thank you!</p>
              <div style="background: #f0fdf4; border: 1px solid #bbf7d0; border-radius: 12px; padding: 20px; margin: 0 0 20px 0;">
                <p style="margin: 0 0 8px 0; color: #166534; font-size: 12px; text-transform: uppercase; letter-spacing: 0.5px;">Payment Details</p>
                <p style="margin: 0 0 8px 0; color: #334155;"><strong>Amount:</strong> ${{amount}}</p>
                <p style="margin: 0 0 8px 0; color: #334155;"><strong>Date:</strong> {{payment_date}}</p>
                <p style="margin: 0; color: #334155;"><strong>Reference:</strong> {{reference}}</p>
              </div>
              <p style="margin: 0 0 20px 0; color: #334155; line-height: 1.6;">Your membership dues are now paid through <strong>{{due_date}}</strong>.</p>
              <p style="margin: 0 0 20px 0; color: #334155; line-height: 1.6;">Thank you for your continued support of Monaco USA!</p>
              <p style="margin: 0; color: #334155;">Best regards,<br><strong style="color: #CE1126;">The Monaco USA Team</strong></p>
            </td>
          </tr>
        </table>

        <!-- Footer -->
        <table role="presentation" width="100%" cellspacing="0" cellpadding="0" style="max-width: 480px;">
          <tr>
            <td align="center" style="padding-top: 24px;">
              <p style="margin: 0; font-size: 12px; color: rgba(255, 255, 255, 0.5);">&copy; 2026 Monaco USA. All rights reserved.</p>
            </td>
          </tr>
        </table>
      </td>
    </tr>
  </table>
</body>
</html>',
    'Payment of ${{amount}} received on {{payment_date}}. Your dues are paid through {{due_date}}. Thank you!',
    true
  ),
  (
    'dues_reminder',
    'Dues Reminder',
    'dues',
    'Monaco USA Membership Dues Reminder',
    '<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
</head>
<body style="margin: 0; padding: 0; font-family: Arial, sans-serif; background-color: #0f172a;">
  <table role="presentation" width="100%" cellspacing="0" cellpadding="0" style="background: linear-gradient(135deg, rgba(15, 23, 42, 0.9) 0%, rgba(30, 41, 59, 0.85) 50%, rgba(127, 29, 29, 0.8) 100%); background-color: #0f172a;">
    <tr>
      <td align="center" style="padding: 40px 20px;">
        <!-- Logo Section -->
        <table role="presentation" width="100%" cellspacing="0" cellpadding="0" style="max-width: 480px;">
          <tr>
            <td align="center" style="padding-bottom: 30px;">
              <div style="background: rgba(255, 255, 255, 0.95); border-radius: 16px; padding: 12px; display: inline-block; box-shadow: 0 10px 40px rgba(0, 0, 0, 0.3);">
                <img src="{{logo_url}}" alt="Monaco USA" width="80" height="80" style="display: block; border-radius: 8px;">
              </div>
              <h1 style="margin: 16px 0 4px 0; font-size: 24px; font-weight: bold; color: #ffffff; text-shadow: 0 2px 4px rgba(0, 0, 0, 0.3);">Monaco <span style="color: #fca5a5;">USA</span></h1>
              <p style="margin: 0; font-size: 14px; color: rgba(255, 255, 255, 0.8);">Americans in Monaco</p>
            </td>
          </tr>
        </table>

        <!-- Main Content Card -->
        <table role="presentation" cellspacing="0" cellpadding="0" style="max-width: 480px; width: 100%; background: #ffffff; border-radius: 16px; box-shadow: 0 20px 60px rgba(0, 0, 0, 0.3);">
          <tr>
            <td style="padding: 40px;">
              <h2 style="margin: 0 0 20px 0; color: #CE1126; font-size: 24px;">Membership Dues Reminder</h2>
              <p style="margin: 0 0 16px 0; color: #334155; line-height: 1.6;">Dear {{first_name}},</p>
              <p style="margin: 0 0 20px 0; color: #334155; line-height: 1.6;">This is a friendly reminder that your Monaco USA membership dues {{status}}.</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: 12px; text-transform: uppercase; letter-spacing: 0.5px;">Dues Details</p>
                <p style="margin: 0 0 8px 0; color: #334155;"><strong>Amount Due:</strong> ${{amount}}</p>
                <p style="margin: 0; color: #334155;"><strong>Due Date:</strong> {{due_date}}</p>
              </div>
              <p style="margin: 0 0 20px 0; color: #334155; line-height: 1.6;">Please log in to your member portal to view payment instructions or contact the treasurer for assistance.</p>
              <p style="margin: 0 0 20px 0; color: #334155; line-height: 1.6;">Thank you for your continued membership!</p>
              <p style="margin: 0; color: #334155;">Best regards,<br><strong style="color: #CE1126;">The Monaco USA Team</strong></p>
            </td>
          </tr>
        </table>

        <!-- Footer -->
        <table role="presentation" width="100%" cellspacing="0" cellpadding="0" style="max-width: 480px;">
          <tr>
            <td align="center" style="padding-top: 24px;">
              <p style="margin: 0; font-size: 12px; color: rgba(255, 255, 255, 0.5);">&copy; 2026 Monaco USA. All rights reserved.</p>
            </td>
          </tr>
        </table>
      </td>
    </tr>
  </table>
</body>
</html>',
    'Reminder: Your Monaco USA membership dues ({{amount}}) {{status}}. Due date: {{due_date}}. Please log in to your portal for payment instructions.',
    true
  )
ON CONFLICT (template_key) DO NOTHING;

-- Grant permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON audit_logs TO authenticated;
GRANT ALL ON audit_logs TO service_role;