acmcc/supabase/migrations/20260317035537_fa15cb34-6f3a-442f-9113-aae7af29442a.sql

-- Email Senders (SMTP sender identities)
CREATE TABLE public.email_senders (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL,
  sender_name TEXT NOT NULL,
  email_address TEXT NOT NULL,
  smtp_host TEXT,
  smtp_port INTEGER DEFAULT 587,
  smtp_username TEXT,
  smtp_password TEXT,
  use_tls BOOLEAN DEFAULT true,
  use_ssl BOOLEAN DEFAULT false,
  is_active BOOLEAN DEFAULT true,
  verified BOOLEAN DEFAULT false,
  is_default BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE public.email_senders ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users manage own senders" ON public.email_senders FOR ALL TO authenticated USING (user_id = auth.uid()) WITH CHECK (user_id = auth.uid());

-- Email History
CREATE TABLE public.email_history (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL,
  sender_email TEXT,
  recipient_email TEXT,
  subject TEXT,
  body_text TEXT,
  sent_at TIMESTAMPTZ DEFAULT now(),
  status TEXT DEFAULT 'sent',
  feature_type TEXT,
  email_headers JSONB,
  proof_url TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE public.email_history ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users view own email history" ON public.email_history FOR ALL TO authenticated USING (user_id = auth.uid()) WITH CHECK (user_id = auth.uid());

-- Email Routing Rules (inbound email addresses per association)
CREATE TABLE public.email_routing_rules (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  association_id UUID NOT NULL REFERENCES public.associations(id) ON DELETE CASCADE,
  email_address TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID
);
ALTER TABLE public.email_routing_rules ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Authenticated users manage routing" ON public.email_routing_rules FOR ALL TO authenticated USING (true) WITH CHECK (true);

-- Email Templates (reusable template library)
CREATE TABLE public.email_templates (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  subject TEXT,
  body_html TEXT,
  thumbnail_url TEXT,
  created_by UUID,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE public.email_templates ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Authenticated users manage templates" ON public.email_templates FOR ALL TO authenticated USING (true) WITH CHECK (true);

-- Notify Board Templates
CREATE TABLE public.notify_board_templates (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL,
  subject TEXT,
  body TEXT,
  attachments JSONB,
  created_by UUID,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE public.notify_board_templates ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Authenticated users manage board templates" ON public.notify_board_templates FOR ALL TO authenticated USING (true) WITH CHECK (true);

-- Owner Notification Templates
CREATE TABLE public.owner_notification_templates (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL,
  subject TEXT,
  body TEXT,
  created_by UUID,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE public.owner_notification_templates ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Authenticated users manage notification templates" ON public.owner_notification_templates FOR ALL TO authenticated USING (true) WITH CHECK (true);

-- Owner Notification Proofs
CREATE TABLE public.owner_notification_proofs (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  client_id UUID,
  association_id UUID REFERENCES public.associations(id) ON DELETE SET NULL,
  subject TEXT,
  body TEXT,
  owners_notified JSONB,
  attachments JSONB,
  delivery_status TEXT,
  validation_id TEXT DEFAULT gen_random_uuid()::TEXT,
  proof_url TEXT,
  created_by UUID,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE public.owner_notification_proofs ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Authenticated users manage proofs" ON public.owner_notification_proofs FOR ALL TO authenticated USING (true) WITH CHECK (true);

-- Email Server Settings (per association SMTP/IMAP config)
CREATE TABLE public.email_server_settings (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  association_id UUID NOT NULL REFERENCES public.associations(id) ON DELETE CASCADE,
  smtp_host TEXT,
  smtp_port INTEGER,
  smtp_username TEXT,
  smtp_password TEXT,
  imap_host TEXT,
  imap_port INTEGER,
  pop3_host TEXT,
  pop3_port INTEGER,
  is_configured BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(association_id)
);
ALTER TABLE public.email_server_settings ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Authenticated users manage server settings" ON public.email_server_settings FOR ALL TO authenticated USING (true) WITH CHECK (true);