-- ============================================================================= -- AgenciaPsi — Tables — Notificações + Email Templates -- ============================================================================= -- notification_channels, notification_logs, notification_preferences, -- notification_queue, notification_schedules, notification_templates, -- notifications, email_templates_global, email_templates_tenant, -- email_layout_config -- ============================================================================= CREATE TABLE public.notification_channels ( id uuid DEFAULT gen_random_uuid() NOT NULL, tenant_id uuid NOT NULL, owner_id uuid NOT NULL, channel text NOT NULL, provider text NOT NULL, is_active boolean DEFAULT false NOT NULL, display_name text, sender_address text, credentials jsonb DEFAULT '{}'::jsonb NOT NULL, connection_status text DEFAULT 'disconnected'::text, last_health_check timestamp with time zone, metadata jsonb DEFAULT '{}'::jsonb, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL, deleted_at timestamp with time zone, CONSTRAINT notification_channels_channel_check CHECK ((channel = ANY (ARRAY['whatsapp'::text, 'email'::text, 'sms'::text]))), CONSTRAINT notification_channels_connection_status_check CHECK ((connection_status = ANY (ARRAY['connected'::text, 'disconnected'::text, 'connecting'::text, 'qr_pending'::text, 'error'::text]))), CONSTRAINT notification_channels_provider_check CHECK ((provider = ANY (ARRAY['evolution_api'::text, 'meta_official'::text, 'twilio'::text, 'zenvia'::text, 'sendgrid'::text, 'resend'::text, 'smtp'::text, 'zapi'::text]))) ); ALTER TABLE public.notification_channels OWNER TO supabase_admin; -- -- Name: notification_logs; Type: TABLE; Schema: public; Owner: supabase_admin -- CREATE TABLE public.notification_logs ( id uuid DEFAULT gen_random_uuid() NOT NULL, tenant_id uuid NOT NULL, owner_id uuid NOT NULL, queue_id uuid, agenda_evento_id uuid, patient_id uuid NOT NULL, channel text NOT NULL, template_key text NOT NULL, schedule_key text, recipient_address text NOT NULL, resolved_message text, resolved_vars jsonb, status text NOT NULL, provider text, provider_message_id text, provider_status text, provider_response jsonb, sent_at timestamp with time zone, delivered_at timestamp with time zone, read_at timestamp with time zone, failed_at timestamp with time zone, failure_reason text, estimated_cost_brl numeric(8,4) DEFAULT 0, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL, CONSTRAINT notification_logs_status_check CHECK ((status = ANY (ARRAY['sent'::text, 'delivered'::text, 'read'::text, 'failed'::text, 'bounced'::text, 'opted_out'::text]))) ); ALTER TABLE public.notification_logs OWNER TO supabase_admin; -- -- Name: notification_preferences; Type: TABLE; Schema: public; Owner: supabase_admin -- CREATE TABLE public.notification_preferences ( id uuid DEFAULT gen_random_uuid() NOT NULL, tenant_id uuid NOT NULL, owner_id uuid NOT NULL, patient_id uuid NOT NULL, whatsapp_opt_in boolean DEFAULT true NOT NULL, email_opt_in boolean DEFAULT true NOT NULL, sms_opt_in boolean DEFAULT false NOT NULL, preferred_time_start time without time zone DEFAULT '08:00:00'::time without time zone, preferred_time_end time without time zone DEFAULT '20:00:00'::time without time zone, lgpd_consent_given boolean DEFAULT false NOT NULL, lgpd_consent_date timestamp with time zone, lgpd_consent_version text, lgpd_consent_ip inet, lgpd_opt_out_date timestamp with time zone, lgpd_opt_out_reason text, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL, deleted_at timestamp with time zone ); ALTER TABLE public.notification_preferences OWNER TO supabase_admin; -- -- Name: notification_queue; Type: TABLE; Schema: public; Owner: supabase_admin -- CREATE TABLE public.notification_queue ( id uuid DEFAULT gen_random_uuid() NOT NULL, tenant_id uuid NOT NULL, owner_id uuid NOT NULL, agenda_evento_id uuid, patient_id uuid NOT NULL, channel text NOT NULL, template_key text NOT NULL, schedule_key text NOT NULL, resolved_vars jsonb DEFAULT '{}'::jsonb NOT NULL, recipient_address text NOT NULL, status text DEFAULT 'pendente'::text NOT NULL, scheduled_at timestamp with time zone NOT NULL, sent_at timestamp with time zone, next_retry_at timestamp with time zone, attempts integer DEFAULT 0 NOT NULL, max_attempts integer DEFAULT 5 NOT NULL, last_error text, idempotency_key text NOT NULL, provider_message_id text, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL, CONSTRAINT notification_queue_channel_check CHECK ((channel = ANY (ARRAY['whatsapp'::text, 'email'::text, 'sms'::text]))), CONSTRAINT notification_queue_status_check CHECK ((status = ANY (ARRAY['pendente'::text, 'processando'::text, 'enviado'::text, 'falhou'::text, 'cancelado'::text, 'ignorado'::text]))) ); ALTER TABLE public.notification_queue OWNER TO supabase_admin; -- -- Name: notification_schedules; Type: TABLE; Schema: public; Owner: supabase_admin -- CREATE TABLE public.notification_schedules ( id uuid DEFAULT gen_random_uuid() NOT NULL, tenant_id uuid NOT NULL, owner_id uuid NOT NULL, schedule_key text NOT NULL, event_type text NOT NULL, trigger_type text NOT NULL, offset_minutes integer DEFAULT 0, whatsapp_enabled boolean DEFAULT true NOT NULL, email_enabled boolean DEFAULT true NOT NULL, sms_enabled boolean DEFAULT false NOT NULL, allowed_time_start time without time zone DEFAULT '08:00:00'::time without time zone, allowed_time_end time without time zone DEFAULT '20:00:00'::time without time zone, skip_weekends boolean DEFAULT false, skip_holidays boolean DEFAULT false, is_active boolean DEFAULT true NOT NULL, sort_order integer DEFAULT 0, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL, deleted_at timestamp with time zone, CONSTRAINT notification_schedules_event_type_check CHECK ((event_type = ANY (ARRAY['lembrete_sessao'::text, 'confirmacao_sessao'::text, 'cancelamento_sessao'::text, 'reagendamento'::text, 'cobranca_pendente'::text, 'boas_vindas_paciente'::text]))), CONSTRAINT notification_schedules_trigger_type_check CHECK ((trigger_type = ANY (ARRAY['before_event'::text, 'after_event'::text, 'immediate'::text]))) ); ALTER TABLE public.notification_schedules OWNER TO supabase_admin; -- -- Name: notification_templates; Type: TABLE; Schema: public; Owner: supabase_admin -- CREATE TABLE public.notification_templates ( id uuid DEFAULT gen_random_uuid() NOT NULL, tenant_id uuid, owner_id uuid, key text NOT NULL, domain text NOT NULL, channel text NOT NULL, event_type text NOT NULL, body_text text NOT NULL, meta_template_name text, meta_template_namespace text, meta_components jsonb, meta_status text DEFAULT 'draft'::text, variables jsonb DEFAULT '[]'::jsonb, version integer DEFAULT 1 NOT NULL, is_active boolean DEFAULT true NOT NULL, is_default boolean DEFAULT false NOT NULL, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL, deleted_at timestamp with time zone, CONSTRAINT notification_templates_channel_check CHECK ((channel = ANY (ARRAY['whatsapp'::text, 'sms'::text]))), CONSTRAINT notification_templates_domain_check CHECK ((domain = ANY (ARRAY['session'::text, 'intake'::text, 'billing'::text, 'system'::text]))), CONSTRAINT notification_templates_event_type_check CHECK ((event_type = ANY (ARRAY['lembrete_sessao'::text, 'confirmacao_sessao'::text, 'cancelamento_sessao'::text, 'reagendamento'::text, 'cobranca_pendente'::text, 'boas_vindas_paciente'::text, 'intake_recebido'::text, 'intake_aprovado'::text, 'intake_rejeitado'::text]))), CONSTRAINT notification_templates_meta_status_check CHECK ((meta_status = ANY (ARRAY['draft'::text, 'pending_approval'::text, 'approved'::text, 'rejected'::text]))) ); ALTER TABLE public.notification_templates OWNER TO supabase_admin; -- -- Name: notifications; Type: TABLE; Schema: public; Owner: supabase_admin -- CREATE TABLE public.notifications ( id uuid DEFAULT gen_random_uuid() NOT NULL, owner_id uuid NOT NULL, tenant_id uuid, type text NOT NULL, ref_id uuid, ref_table text, payload jsonb DEFAULT '{}'::jsonb NOT NULL, read_at timestamp with time zone, archived boolean DEFAULT false NOT NULL, created_at timestamp with time zone DEFAULT now() NOT NULL, CONSTRAINT notifications_type_check CHECK ((type = ANY (ARRAY['new_scheduling'::text, 'new_patient'::text, 'recurrence_alert'::text, 'session_status'::text]))) ); CREATE TABLE public.email_layout_config ( id uuid DEFAULT gen_random_uuid() NOT NULL, tenant_id uuid NOT NULL, header_config jsonb DEFAULT '{"layout": null, "content": "", "enabled": false}'::jsonb NOT NULL, footer_config jsonb DEFAULT '{"layout": null, "content": "", "enabled": false}'::jsonb NOT NULL, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); ALTER TABLE public.email_layout_config OWNER TO supabase_admin; -- -- Name: email_templates_global; Type: TABLE; Schema: public; Owner: supabase_admin -- CREATE TABLE public.email_templates_global ( id uuid DEFAULT gen_random_uuid() NOT NULL, key text NOT NULL, domain text NOT NULL, channel text DEFAULT 'email'::text NOT NULL, subject text NOT NULL, body_html text NOT NULL, body_text text, version integer DEFAULT 1 NOT NULL, is_active boolean DEFAULT true NOT NULL, variables jsonb, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL ); ALTER TABLE public.email_templates_global OWNER TO supabase_admin; -- -- Name: email_templates_tenant; Type: TABLE; Schema: public; Owner: supabase_admin -- CREATE TABLE public.email_templates_tenant ( id uuid DEFAULT gen_random_uuid() NOT NULL, tenant_id uuid NOT NULL, owner_id uuid, template_key text NOT NULL, subject text, body_html text, body_text text, enabled boolean DEFAULT true NOT NULL, synced_version integer, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL );