133 lines
6.3 KiB
SQL
133 lines
6.3 KiB
SQL
-- =============================================================================
|
|
-- AgenciaPsi — Migration 001: Twilio WhatsApp Subaccounts
|
|
-- =============================================================================
|
|
-- Adiciona suporte a subcontas Twilio com número WhatsApp dedicado por tenant.
|
|
-- Cada clínica/terapeuta recebe sua própria subconta Twilio com número exclusivo.
|
|
-- =============================================================================
|
|
|
|
-- ── 1. Campos de subconta Twilio em notification_channels ──────────────────
|
|
|
|
ALTER TABLE public.notification_channels
|
|
ADD COLUMN IF NOT EXISTS twilio_subaccount_sid text,
|
|
ADD COLUMN IF NOT EXISTS twilio_phone_number text,
|
|
ADD COLUMN IF NOT EXISTS twilio_phone_sid text,
|
|
ADD COLUMN IF NOT EXISTS webhook_url text,
|
|
ADD COLUMN IF NOT EXISTS cost_per_message_usd numeric(8,6) DEFAULT 0,
|
|
ADD COLUMN IF NOT EXISTS price_per_message_brl numeric(8,4) DEFAULT 0,
|
|
ADD COLUMN IF NOT EXISTS provisioned_at timestamp with time zone;
|
|
|
|
COMMENT ON COLUMN public.notification_channels.twilio_subaccount_sid IS 'SID da subconta Twilio criada para este tenant';
|
|
COMMENT ON COLUMN public.notification_channels.twilio_phone_number IS 'Número WhatsApp provisionado (E.164, ex: +5511999990000)';
|
|
COMMENT ON COLUMN public.notification_channels.twilio_phone_sid IS 'SID do número de telefone na subconta Twilio';
|
|
COMMENT ON COLUMN public.notification_channels.webhook_url IS 'URL do webhook configurada na Twilio para receber callbacks de status';
|
|
COMMENT ON COLUMN public.notification_channels.cost_per_message_usd IS 'Custo real Twilio por mensagem WhatsApp (USD)';
|
|
COMMENT ON COLUMN public.notification_channels.price_per_message_brl IS 'Valor cobrado do tenant por mensagem (BRL, inclui margem SaaS)';
|
|
COMMENT ON COLUMN public.notification_channels.provisioned_at IS 'Timestamp do provisionamento da subconta';
|
|
|
|
-- Índice para busca rápida por subconta
|
|
CREATE INDEX IF NOT EXISTS idx_notification_channels_twilio_subaccount_sid
|
|
ON public.notification_channels (twilio_subaccount_sid)
|
|
WHERE twilio_subaccount_sid IS NOT NULL;
|
|
|
|
-- ── 2. Tabela de consumo por subconta ─────────────────────────────────────
|
|
|
|
CREATE TABLE IF NOT EXISTS public.twilio_subaccount_usage (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
tenant_id uuid NOT NULL,
|
|
channel_id uuid NOT NULL,
|
|
twilio_subaccount_sid text NOT NULL,
|
|
period_start date NOT NULL,
|
|
period_end date NOT NULL,
|
|
messages_sent integer DEFAULT 0 NOT NULL,
|
|
messages_delivered integer DEFAULT 0 NOT NULL,
|
|
messages_failed integer DEFAULT 0 NOT NULL,
|
|
cost_usd numeric(12,6) DEFAULT 0 NOT NULL,
|
|
cost_brl numeric(12,4) DEFAULT 0 NOT NULL,
|
|
revenue_brl numeric(12,4) DEFAULT 0 NOT NULL,
|
|
margin_brl numeric(12,4) GENERATED ALWAYS AS (revenue_brl - cost_brl) STORED,
|
|
usd_brl_rate numeric(8,4) DEFAULT 0,
|
|
synced_at timestamp with time zone DEFAULT now(),
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
|
|
CONSTRAINT twilio_subaccount_usage_pkey PRIMARY KEY (id),
|
|
CONSTRAINT twilio_subaccount_usage_channel_fk
|
|
FOREIGN KEY (channel_id) REFERENCES public.notification_channels(id) ON DELETE CASCADE,
|
|
CONSTRAINT twilio_subaccount_usage_period_check
|
|
CHECK (period_end >= period_start)
|
|
);
|
|
|
|
COMMENT ON TABLE public.twilio_subaccount_usage IS
|
|
'Consumo mensal de mensagens WhatsApp por subconta Twilio. Sincronizado via Edge Function.';
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_twilio_usage_tenant_period
|
|
ON public.twilio_subaccount_usage (tenant_id, period_start DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_twilio_usage_channel
|
|
ON public.twilio_subaccount_usage (channel_id, period_start DESC);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_twilio_usage_unique_period
|
|
ON public.twilio_subaccount_usage (channel_id, period_start, period_end);
|
|
|
|
ALTER TABLE public.twilio_subaccount_usage OWNER TO supabase_admin;
|
|
|
|
-- ── 3. RLS: twilio_subaccount_usage ───────────────────────────────────────
|
|
|
|
ALTER TABLE public.twilio_subaccount_usage ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Tenant vê apenas seu próprio consumo
|
|
CREATE POLICY "tenant_select_own_usage"
|
|
ON public.twilio_subaccount_usage
|
|
FOR SELECT
|
|
USING (
|
|
tenant_id IN (
|
|
SELECT tenant_id FROM public.tenant_members
|
|
WHERE user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
-- Apenas service_role pode inserir/atualizar (via Edge Function)
|
|
CREATE POLICY "service_role_manage_usage"
|
|
ON public.twilio_subaccount_usage
|
|
FOR ALL
|
|
USING (auth.role() = 'service_role');
|
|
|
|
-- ── 4. RLS: notification_channels — acesso ao twilio_subaccount_sid ───────
|
|
-- As políticas existentes já cobrem SELECT/UPDATE. Nenhuma alteração necessária.
|
|
|
|
-- ── 5. View: resumo de subcontas para o painel SaaS admin ─────────────────
|
|
|
|
CREATE OR REPLACE VIEW public.v_twilio_whatsapp_overview AS
|
|
SELECT
|
|
nc.id AS channel_id,
|
|
nc.tenant_id,
|
|
nc.owner_id,
|
|
nc.is_active,
|
|
nc.connection_status,
|
|
nc.display_name,
|
|
nc.twilio_subaccount_sid,
|
|
nc.twilio_phone_number,
|
|
nc.twilio_phone_sid,
|
|
nc.cost_per_message_usd,
|
|
nc.price_per_message_brl,
|
|
nc.provisioned_at,
|
|
nc.created_at,
|
|
nc.updated_at,
|
|
-- Uso do mês atual
|
|
COALESCE(u.messages_sent, 0) AS current_month_sent,
|
|
COALESCE(u.messages_delivered, 0) AS current_month_delivered,
|
|
COALESCE(u.messages_failed, 0) AS current_month_failed,
|
|
COALESCE(u.cost_usd, 0) AS current_month_cost_usd,
|
|
COALESCE(u.cost_brl, 0) AS current_month_cost_brl,
|
|
COALESCE(u.revenue_brl, 0) AS current_month_revenue_brl,
|
|
COALESCE(u.margin_brl, 0) AS current_month_margin_brl
|
|
FROM public.notification_channels nc
|
|
LEFT JOIN public.twilio_subaccount_usage u
|
|
ON u.channel_id = nc.id
|
|
AND u.period_start = date_trunc('month', CURRENT_DATE)::date
|
|
WHERE nc.channel = 'whatsapp'
|
|
AND nc.provider = 'twilio'
|
|
AND nc.deleted_at IS NULL;
|
|
|
|
COMMENT ON VIEW public.v_twilio_whatsapp_overview IS
|
|
'Visão consolidada de subcontas Twilio WhatsApp com uso do mês corrente.';
|