-- ============================================================ -- EVOLUA PROSPECT — Instalador único para Supabase do cliente -- ============================================================ -- Como rodar: -- 1. Crie um projeto novo no https://supabase.com -- 2. Vá em SQL Editor → New Query -- 3. Cole TODO o conteúdo deste arquivo -- 4. Clique em "Run" -- 5. Aguarde o "Success" (pode levar 10-30 segundos) -- -- Este script é idempotente: pode rodar mais de uma vez sem -- quebrar nada. Só cria o que ainda não existe. -- ============================================================ BEGIN; -- ============================================================ -- SECAO 1/7 ─ EXTENSIONS -- ============================================================ CREATE EXTENSION IF NOT EXISTS pgcrypto; -- gen_random_uuid() CREATE EXTENSION IF NOT EXISTS citext; -- case-insensitive text (email) -- ============================================================ -- SECAO 2/7 ─ FUNCTIONS (atualizam updated_at automaticamente) -- ============================================================ CREATE OR REPLACE FUNCTION public.set_updated_at() RETURNS trigger LANGUAGE plpgsql AS $function$ BEGIN NEW.updated_at = now(); RETURN NEW; END $function$; CREATE OR REPLACE FUNCTION public.set_updated_at_studio_flows() RETURNS trigger LANGUAGE plpgsql AS $function$ BEGIN new.updated_at = now(); RETURN new; END $function$; CREATE OR REPLACE FUNCTION public.update_followup_config_updated_at() RETURNS trigger LANGUAGE plpgsql AS $function$ BEGIN NEW.updated_at = NOW(); RETURN NEW; END; $function$; CREATE OR REPLACE FUNCTION public.update_plans_timestamp() RETURNS trigger LANGUAGE plpgsql AS $function$ BEGIN NEW.updated_at = now(); RETURN NEW; END; $function$; -- ============================================================ -- SECAO 3/7 ─ TABELAS (criadas em ordem de dependencia) -- ============================================================ -- ── 3.1 Tabelas SEM dependências ───────────────────────────── CREATE TABLE IF NOT EXISTS public.admin_ai_settings ( id integer NOT NULL DEFAULT 1, payer text NOT NULL DEFAULT 'admin'::text, admin_openai_key text NULL, updated_at timestamptz NOT NULL DEFAULT now(), updated_by uuid NULL, CONSTRAINT admin_ai_settings_pkey PRIMARY KEY (id), CONSTRAINT admin_ai_settings_payer_check CHECK ( payer = ANY (ARRAY['admin'::text, 'client'::text]) ), CONSTRAINT admin_ai_settings_singleton CHECK (id = 1) ); CREATE TABLE IF NOT EXISTS public.admin_smtp_settings ( id integer NOT NULL DEFAULT 1, smtp_host text NULL, smtp_port integer NULL DEFAULT 587, smtp_secure text NULL DEFAULT 'tls'::text, smtp_user text NULL, smtp_pass text NULL, mail_from text NULL, mail_from_name text NULL, reply_to text NULL, enabled boolean NOT NULL DEFAULT false, updated_at timestamptz NOT NULL DEFAULT now(), updated_by uuid NULL, mail_subject_reset text NULL DEFAULT 'Defina sua senha'::text, CONSTRAINT admin_smtp_settings_pkey PRIMARY KEY (id), CONSTRAINT admin_smtp_settings_secure_check CHECK ( smtp_secure = ANY (ARRAY['tls'::text, 'ssl'::text, 'none'::text]) ), CONSTRAINT admin_smtp_settings_singleton CHECK (id = 1) ); CREATE TABLE IF NOT EXISTS public.custom_menus ( id uuid NOT NULL DEFAULT gen_random_uuid(), label text NOT NULL, icon text NOT NULL DEFAULT 'link'::text, url text NOT NULL, position text NOT NULL DEFAULT 'end'::text, sort_order integer NOT NULL DEFAULT 0, active boolean NOT NULL DEFAULT true, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), updated_by uuid NULL, CONSTRAINT custom_menus_pkey PRIMARY KEY (id) ); CREATE TABLE IF NOT EXISTS public.custom_dicas ( id uuid NOT NULL DEFAULT gen_random_uuid(), title text NOT NULL, description text NOT NULL DEFAULT ''::text, youtube_url text NOT NULL, youtube_id text NOT NULL, custom_thumb text NULL, date_mode text NOT NULL DEFAULT 'auto'::text, custom_date date NULL, materials jsonb NOT NULL DEFAULT '[]'::jsonb, sort_order integer NOT NULL DEFAULT 0, active boolean NOT NULL DEFAULT true, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), updated_by uuid NULL, CONSTRAINT custom_dicas_pkey PRIMARY KEY (id), CONSTRAINT custom_dicas_date_mode_check CHECK ( date_mode = ANY (ARRAY['auto'::text, 'custom'::text]) ) ); CREATE TABLE IF NOT EXISTS public.super_admins ( id uuid NOT NULL DEFAULT gen_random_uuid(), email public.citext NOT NULL, password_hash text NOT NULL, name text NOT NULL, status text NOT NULL DEFAULT 'active'::text, last_login timestamptz NULL, created_at timestamptz NOT NULL DEFAULT now(), role text NOT NULL DEFAULT 'super_1'::text, permissions jsonb NOT NULL DEFAULT '{}'::jsonb, avatar_url text NULL, CONSTRAINT super_admins_pkey PRIMARY KEY (id), CONSTRAINT super_admins_email_key UNIQUE (email), CONSTRAINT super_admins_role_check CHECK ( role = ANY (ARRAY['super_1'::text, 'super_2'::text]) ) ); CREATE TABLE IF NOT EXISTS public.auth_users ( id uuid NOT NULL DEFAULT gen_random_uuid(), email public.citext NOT NULL, password_hash text NOT NULL, name text NULL, role text NOT NULL DEFAULT 'user'::text, status text NOT NULL DEFAULT 'active'::text, created_at timestamptz NOT NULL DEFAULT now(), last_login timestamptz NULL, reset_token text NULL, reset_expires timestamptz NULL, contratado_em timestamptz NOT NULL DEFAULT now(), expira_em timestamptz NOT NULL DEFAULT (now() + '30 days'::interval), plan text NULL DEFAULT 'basic'::text, licenca_google_maps varchar(255) NULL DEFAULT ''::varchar, licenca_instagram varchar(255) NULL DEFAULT ''::varchar, licenca_radar_cnpj varchar(255) NULL DEFAULT ''::varchar, dominio text NULL, dominio_login text NULL, url_link_cria_senha text NULL, waba_id text NULL, waba_phone_number_id text NULL, waba_phone_number text NULL, waba_access_token text NULL, waba_connection_name text NULL, wa_conn_name text NULL, wa_phone_number_id text NULL, wa_waba_id text NULL, wa_access_token text NULL, wa_phone_number text NULL, wa_connected_at timestamptz NULL, wa_updated_at timestamptz NULL, wa_evolua_chat_token text NULL, wa_evolua_chat_user text NULL, wa_evolua_chat_pass text NULL, webhook_waba_evo_chat text NULL, licenca_grupos_extrator varchar(255) NULL, openai_api_key text NULL, CONSTRAINT auth_users_pkey PRIMARY KEY (id), CONSTRAINT auth_users_email_key UNIQUE (email) ); CREATE TABLE IF NOT EXISTS public.users ( id uuid NOT NULL DEFAULT gen_random_uuid(), email public.citext NOT NULL, name text NULL, role text NULL DEFAULT 'user'::text, status text NULL DEFAULT 'active'::text, instance_name text NULL, api_key text NULL, state text NULL DEFAULT 'none'::text, meta jsonb NOT NULL DEFAULT '{}'::jsonb, last_qr jsonb NULL, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), prompt_support_agent text NULL, prompt_broadcast_agent text NULL, default_broadcast_message text NOT NULL DEFAULT ''::text, webhook_token text NULL, webhook_enabled boolean NOT NULL DEFAULT true, webhook_last_seen timestamptz NULL, webhook_note text NULL, agenteligado boolean NOT NULL DEFAULT false, contratado_em timestamptz NOT NULL DEFAULT now(), expira_em timestamptz NOT NULL DEFAULT (now() + '30 days'::interval), responder_todos boolean NOT NULL DEFAULT false, follow_up_enabled boolean NOT NULL DEFAULT false, follow_up_ignore_seconds integer NOT NULL DEFAULT 0, follow_up_timezone text NOT NULL DEFAULT 'America/Sao_Paulo'::text, tempo_de_set bigint NULL DEFAULT 12, media_biblioteca jsonb NOT NULL DEFAULT '[]'::jsonb, agenda_link text NULL, numero_falar_humano varchar(32) NULL, grupo_falar_humano varchar(128) NULL, url_api text NULL, whatsapp_notifications varchar(20) NULL, agente_ligado_waba boolean NOT NULL DEFAULT false, responder_todos_waba boolean NOT NULL DEFAULT false, CONSTRAINT users_pkey PRIMARY KEY (id), CONSTRAINT users_email_key UNIQUE (email), CONSTRAINT users_webhook_token_key UNIQUE (webhook_token) ); CREATE TABLE IF NOT EXISTS public.plans ( id text NOT NULL, name text NOT NULL, description text NULL, features jsonb NOT NULL DEFAULT '[]'::jsonb, is_default boolean NOT NULL DEFAULT false, sort_order integer NOT NULL DEFAULT 0, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), CONSTRAINT plans_pkey PRIMARY KEY (id) ); CREATE TABLE IF NOT EXISTS public.instancias_vencidas ( id serial NOT NULL, email text NULL, expira_em timestamptz NULL, instance_name text NULL, api_key text NULL, status text NULL DEFAULT 'pendente'::text, criado_em timestamptz NULL DEFAULT now(), CONSTRAINT instancias_vencidas_pkey PRIMARY KEY (id) ); CREATE TABLE IF NOT EXISTS public.prompt_models ( id bigserial NOT NULL, user_email varchar(255) NOT NULL, folder_name varchar(100) NOT NULL DEFAULT 'Geral'::varchar, model_name varchar(150) NOT NULL, prompt_text text NOT NULL DEFAULT ''::text, tempo_de_set integer NOT NULL DEFAULT 12, definitivo boolean NOT NULL DEFAULT false, is_active boolean NOT NULL DEFAULT false, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), CONSTRAINT prompt_models_pkey PRIMARY KEY (id) ); -- ── 3.2 Tabelas que dependem das primeiras ─────────────────── CREATE TABLE IF NOT EXISTS public.api_tokens ( id uuid NOT NULL DEFAULT gen_random_uuid(), name text NOT NULL, token_hash text NOT NULL, token_prefix text NOT NULL, admin_id uuid NOT NULL, last_used_at timestamptz NULL, last_used_ip text NULL, request_count bigint NOT NULL DEFAULT 0, revoked_at timestamptz NULL, revoked_by uuid NULL, created_at timestamptz NOT NULL DEFAULT now(), expires_at timestamptz NULL, CONSTRAINT api_tokens_pkey PRIMARY KEY (id), CONSTRAINT api_tokens_token_hash_key UNIQUE (token_hash), CONSTRAINT api_tokens_admin_fk FOREIGN KEY (admin_id) REFERENCES public.super_admins(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS public.crm_stages ( id serial NOT NULL, user_id text NOT NULL, name varchar(80) NOT NULL, position integer NOT NULL DEFAULT 0, created_at timestamp NULL DEFAULT now(), CONSTRAINT crm_stages_pkey PRIMARY KEY (id) ); CREATE TABLE IF NOT EXISTS public.lead_folders ( id bigserial NOT NULL, user_id uuid NOT NULL, name text NOT NULL, created_at timestamptz NOT NULL DEFAULT now(), sort_order integer NULL, CONSTRAINT lead_folders_pkey PRIMARY KEY (id), CONSTRAINT lead_folders_user_fk FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS public.funis ( id uuid NOT NULL DEFAULT gen_random_uuid(), user_id uuid NOT NULL, nome text NOT NULL, status text NOT NULL DEFAULT 'draft'::text, versao integer NOT NULL DEFAULT 1, sequencia jsonb NOT NULL DEFAULT '[]'::jsonb, published_at timestamptz NULL, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), CONSTRAINT funis_pkey PRIMARY KEY (id), CONSTRAINT funis_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE, CONSTRAINT funis_status_check CHECK ( status = ANY (ARRAY['draft'::text, 'published'::text, 'archived'::text]) ) ); CREATE TABLE IF NOT EXISTS public.followups ( id bigserial NOT NULL, user_id uuid NOT NULL, nome text NOT NULL, status text NOT NULL DEFAULT 'draft'::text, versao integer NOT NULL DEFAULT 1, initial_ignore_seconds integer NOT NULL DEFAULT 0, timezone text NOT NULL DEFAULT 'America/Sao_Paulo'::text, steps jsonb NOT NULL DEFAULT '[]'::jsonb, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), CONSTRAINT followups_pkey PRIMARY KEY (id), CONSTRAINT followups_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE, CONSTRAINT followups_status_check CHECK ( status = ANY (ARRAY['draft'::text, 'published'::text]) ) ); CREATE TABLE IF NOT EXISTS public.studio_flows ( id uuid NOT NULL DEFAULT gen_random_uuid(), user_id uuid NOT NULL, nome text NOT NULL, status text NOT NULL DEFAULT 'draft'::text, versao integer NOT NULL DEFAULT 1, steps jsonb NOT NULL DEFAULT '[]'::jsonb, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), CONSTRAINT studio_flows_pkey PRIMARY KEY (id), CONSTRAINT fk_studio_flows_users FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE, CONSTRAINT studio_flows_status_check CHECK ( status = ANY (ARRAY['draft'::text, 'published'::text]) ) ); CREATE TABLE IF NOT EXISTS public.leads_webhook_brutos ( id bigserial NOT NULL, user_id uuid NOT NULL, token text NOT NULL, method text NOT NULL DEFAULT 'POST'::text, received_at timestamptz NOT NULL DEFAULT now(), ip inet NULL, headers jsonb NULL, query jsonb NULL, body_raw text NULL, body_json jsonb NULL, status text NOT NULL DEFAULT 'new'::text, error text NULL, CONSTRAINT leads_webhook_brutos_pkey PRIMARY KEY (id), CONSTRAINT leads_webhook_brutos_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE, CONSTRAINT leads_webhook_brutos_status_check CHECK ( status = ANY (ARRAY['new'::text, 'processed'::text, 'error'::text]) ) ); CREATE TABLE IF NOT EXISTS public.disparos_oficial ( id bigserial NOT NULL, user_id uuid NOT NULL, nome text NOT NULL, min_delay integer NOT NULL DEFAULT 1, max_delay integer NOT NULL DEFAULT 1, status text NOT NULL DEFAULT 'draft'::text, total integer NOT NULL DEFAULT 0, enviados integer NOT NULL DEFAULT 0, erros integer NOT NULL DEFAULT 0, sem_whatsapp integer NOT NULL DEFAULT 0, created_at timestamptz NOT NULL DEFAULT now(), leads jsonb NULL, schedule jsonb NULL, template_id text NULL, template_snapshot jsonb NULL, started_webhook_at timestamptz NULL, paused_webhook_at timestamptz NULL, auto_paused boolean NOT NULL DEFAULT false, template_vars jsonb NULL, synced_evolua_chat boolean NOT NULL DEFAULT false, synced_evolua_chat_at timestamptz NULL, sync_webhook_status integer NULL, sync_webhook_response jsonb NULL, CONSTRAINT disparos_oficial_pkey PRIMARY KEY (id), CONSTRAINT disparos_oficial_user_fk FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS public.super_admin_logs ( id bigserial NOT NULL, admin_id uuid NULL, admin_email text NULL, action text NOT NULL, description text NULL, metadata jsonb NULL DEFAULT '{}'::jsonb, ip_address text NULL, created_at timestamptz NOT NULL DEFAULT now(), CONSTRAINT super_admin_logs_pkey PRIMARY KEY (id), CONSTRAINT super_admin_logs_admin_id_fkey FOREIGN KEY (admin_id) REFERENCES public.super_admins(id) ON DELETE SET NULL ); CREATE TABLE IF NOT EXISTS public.super_admin_master_passwords ( id bigserial NOT NULL, created_by_admin_id uuid NULL, created_by_admin_email text NOT NULL, password_hash text NOT NULL, expires_at timestamptz NOT NULL, revoked_at timestamptz NULL, revoked_by_admin_id uuid NULL, revoked_by_admin_email text NULL, usage_count integer NOT NULL DEFAULT 0, last_used_at timestamptz NULL, last_used_for_email text NULL, last_used_for_system text NULL, reason text NULL, created_at timestamptz NOT NULL DEFAULT now(), CONSTRAINT super_admin_master_passwords_pkey PRIMARY KEY (id), CONSTRAINT super_admin_master_passwords_created_by_admin_id_fkey FOREIGN KEY (created_by_admin_id) REFERENCES public.super_admins(id) ON DELETE SET NULL, CONSTRAINT super_admin_master_passwords_revoked_by_admin_id_fkey FOREIGN KEY (revoked_by_admin_id) REFERENCES public.super_admins(id) ON DELETE SET NULL ); -- ── 3.3 Tabelas com dependências de 2º nível ───────────────── CREATE TABLE IF NOT EXISTS public.leads ( id bigserial NOT NULL, user_id uuid NOT NULL, company_name text NOT NULL, phone text NULL, status text NULL DEFAULT 'novo'::text, created_at timestamptz NOT NULL DEFAULT now(), stage_id integer NULL, stage_pos integer NOT NULL DEFAULT 0, folder_id bigint NULL, CONSTRAINT leads_pkey PRIMARY KEY (id), CONSTRAINT leads_folder_id_fkey FOREIGN KEY (folder_id) REFERENCES public.lead_folders(id) ON DELETE SET NULL, CONSTRAINT leads_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS public.crm_followup_configs ( id serial NOT NULL, user_id text NOT NULL, stage_id integer NOT NULL, message_type varchar(20) NOT NULL, message_text text NULL, media_url text NULL, delay_days integer NOT NULL DEFAULT 1, move_to_stage_id integer NULL, interval_min integer NULL DEFAULT 60, interval_max integer NULL DEFAULT 300, active boolean NULL DEFAULT true, created_at timestamp NULL DEFAULT now(), updated_at timestamp NULL DEFAULT now(), scheduled_time time NULL, move_stage_se_respondeu integer NULL, CONSTRAINT crm_followup_configs_pkey PRIMARY KEY (id), CONSTRAINT crm_followup_configs_user_id_stage_id_key UNIQUE (user_id, stage_id), CONSTRAINT crm_followup_configs_move_to_stage_id_fkey FOREIGN KEY (move_to_stage_id) REFERENCES public.crm_stages(id) ON DELETE SET NULL, CONSTRAINT crm_followup_configs_stage_id_fkey FOREIGN KEY (stage_id) REFERENCES public.crm_stages(id) ON DELETE CASCADE, CONSTRAINT crm_followup_configs_check CHECK (interval_max >= interval_min), CONSTRAINT crm_followup_configs_interval_min_check CHECK (interval_min >= 0), CONSTRAINT crm_followup_configs_delay_days_check CHECK (delay_days >= 0), CONSTRAINT crm_followup_configs_message_type_check CHECK ( (message_type)::text = ANY (ARRAY[ ('text'::varchar)::text, ('image'::varchar)::text, ('video'::varchar)::text, ('audio'::varchar)::text ]) ) ); CREATE TABLE IF NOT EXISTS public.disparos ( id serial NOT NULL, instance_id varchar(64) NULL, nome varchar(120) NOT NULL, mensagem text NULL, min_delay integer NOT NULL DEFAULT 2, max_delay integer NOT NULL DEFAULT 6, status varchar(20) NOT NULL DEFAULT 'draft'::varchar, total integer NOT NULL DEFAULT 0, enviados integer NOT NULL DEFAULT 0, erros integer NOT NULL DEFAULT 0, created_at timestamptz NULL DEFAULT now(), user_id uuid NOT NULL, started_webhook_at timestamptz NULL, leads jsonb NOT NULL DEFAULT '[]'::jsonb, paused_webhook_at timestamptz NULL, ignore_default_message boolean NOT NULL DEFAULT false, ignore_timeout_seconds integer NOT NULL DEFAULT 0, funil_id uuid NULL, funil_snapshot jsonb NULL, schedule jsonb NULL, auto_paused boolean NOT NULL DEFAULT false, studio_id uuid NULL, studio_snapshot jsonb NULL, crm_stage_id integer NULL, falha_connection boolean NULL, CONSTRAINT disparos_pkey PRIMARY KEY (id), CONSTRAINT disparos_crm_stage_id_fkey FOREIGN KEY (crm_stage_id) REFERENCES public.crm_stages(id) ON DELETE SET NULL, CONSTRAINT disparos_funil_id_fkey FOREIGN KEY (funil_id) REFERENCES public.funis(id) ON DELETE SET NULL, CONSTRAINT disparos_user_fk FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE, CONSTRAINT disparos_ignore_timeout_seconds_ck CHECK (ignore_timeout_seconds >= 0), CONSTRAINT disparos_status_check CHECK ( (status)::text = ANY (ARRAY[ ('draft'::varchar)::text, ('running'::varchar)::text, ('paused'::varchar)::text, ('finished'::varchar)::text, ('canceled'::varchar)::text ]) ) ); -- ── 3.4 Tabelas finais (dependem de 3 niveis acima) ────────── CREATE TABLE IF NOT EXISTS public.crm_followup_queue ( id serial NOT NULL, user_id text NOT NULL, lead_id integer NOT NULL, config_id integer NOT NULL, scheduled_at timestamptz NULL, status varchar(20) NULL DEFAULT 'pending'::varchar, sent_at timestamp NULL, error_message text NULL, created_at timestamp NULL DEFAULT now(), phone varchar(20) NULL, CONSTRAINT crm_followup_queue_pkey PRIMARY KEY (id), CONSTRAINT crm_followup_queue_config_id_fkey FOREIGN KEY (config_id) REFERENCES public.crm_followup_configs(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS public.disparos_itens ( id bigserial NOT NULL, disparo_id integer NOT NULL, telefone varchar(32) NOT NULL, status varchar(20) NOT NULL DEFAULT 'pending'::varchar, last_error text NULL, sent_at timestamptz NULL, lead_id bigint NULL, replied_at timestamptz NULL, first_sent_at timestamptz NULL, follow_last_day_sent integer NOT NULL DEFAULT 0, follow_last_sent_at timestamptz NULL, follow_locked boolean NOT NULL DEFAULT false, etapa_studio jsonb NULL, CONSTRAINT disparos_itens_pkey PRIMARY KEY (id), CONSTRAINT disparos_itens_disparo_id_fkey FOREIGN KEY (disparo_id) REFERENCES public.disparos(id) ON DELETE CASCADE, CONSTRAINT fk_disparos_itens_disparo FOREIGN KEY (disparo_id) REFERENCES public.disparos(id) ON DELETE CASCADE, CONSTRAINT fk_disparos_itens_lead FOREIGN KEY (lead_id) REFERENCES public.leads(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS public.disparos_oficial_itens ( id bigserial NOT NULL, disparo_id bigint NOT NULL, lead_id integer NULL, telefone text NOT NULL, status text NOT NULL DEFAULT 'pending'::text, error_msg text NULL, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), CONSTRAINT disparos_oficial_itens_pkey PRIMARY KEY (id), CONSTRAINT disparos_oficial_itens_disparo_id_fkey FOREIGN KEY (disparo_id) REFERENCES public.disparos_oficial(id) ON DELETE CASCADE ); -- ============================================================ -- SECAO 4/7 ─ INDICES -- ============================================================ -- api_tokens CREATE INDEX IF NOT EXISTS idx_api_tokens_hash ON public.api_tokens USING btree (token_hash) WHERE (revoked_at IS NULL); CREATE INDEX IF NOT EXISTS idx_api_tokens_admin ON public.api_tokens USING btree (admin_id); -- auth_users CREATE INDEX IF NOT EXISTS auth_users_lower_email_idx ON public.auth_users USING btree (lower((email)::text)); CREATE UNIQUE INDEX IF NOT EXISTS idx_auth_users_reset_token ON public.auth_users USING btree (reset_token); CREATE INDEX IF NOT EXISTS idx_auth_users_status ON public.auth_users USING btree (status); -- super_admins CREATE UNIQUE INDEX IF NOT EXISTS super_admins_email_lower_idx ON public.super_admins USING btree (lower((email)::text)); -- super_admin_logs CREATE INDEX IF NOT EXISTS idx_admin_logs_action ON public.super_admin_logs USING btree (action); CREATE INDEX IF NOT EXISTS idx_admin_logs_admin ON public.super_admin_logs USING btree (admin_id); CREATE INDEX IF NOT EXISTS idx_admin_logs_created ON public.super_admin_logs USING btree (created_at DESC); -- super_admin_master_passwords CREATE INDEX IF NOT EXISTS idx_master_pass_active ON public.super_admin_master_passwords USING btree (expires_at) WHERE (revoked_at IS NULL); -- users CREATE INDEX IF NOT EXISTS idx_users_media_biblioteca_gin ON public.users USING gin (media_biblioteca); CREATE UNIQUE INDEX IF NOT EXISTS users_email_idx ON public.users USING btree (lower((email)::text)); CREATE INDEX IF NOT EXISTS users_lower_email_idx ON public.users USING btree (lower((email)::text)); -- plans CREATE INDEX IF NOT EXISTS idx_plans_is_default ON public.plans USING btree (is_default) WHERE (is_default = true); CREATE UNIQUE INDEX IF NOT EXISTS uq_plans_only_one_default ON public.plans USING btree ((true)) WHERE (is_default = true); -- prompt_models CREATE INDEX IF NOT EXISTS idx_prompt_models_active ON public.prompt_models USING btree (user_email, is_active) WHERE (is_active = true); CREATE INDEX IF NOT EXISTS idx_prompt_models_user ON public.prompt_models USING btree (user_email); CREATE UNIQUE INDEX IF NOT EXISTS uq_prompt_models_active_per_user ON public.prompt_models USING btree (user_email) WHERE (is_active = true); CREATE INDEX IF NOT EXISTS idx_prompt_models_user_name_active ON public.prompt_models USING btree (user_email, is_active DESC, lower((model_name)::text)); -- crm_stages CREATE INDEX IF NOT EXISTS idx_crm_stages_user ON public.crm_stages USING btree (user_id); -- crm_followup_configs CREATE INDEX IF NOT EXISTS idx_followup_configs_user_stage ON public.crm_followup_configs USING btree (user_id, stage_id); -- crm_followup_queue CREATE INDEX IF NOT EXISTS idx_followup_queue_lead ON public.crm_followup_queue USING btree (lead_id); CREATE INDEX IF NOT EXISTS idx_followup_queue_scheduled ON public.crm_followup_queue USING btree (scheduled_at, status); CREATE INDEX IF NOT EXISTS idx_followup_queue_user_status ON public.crm_followup_queue USING btree (user_id, status); -- lead_folders CREATE INDEX IF NOT EXISTS idx_lead_folders_user ON public.lead_folders USING btree (user_id); CREATE INDEX IF NOT EXISTS idx_lead_folders_user_order ON public.lead_folders USING btree (user_id, sort_order); CREATE UNIQUE INDEX IF NOT EXISTS lead_folders_unique_per_user ON public.lead_folders USING btree (user_id, lower(name)); -- leads CREATE INDEX IF NOT EXISTS idx_leads_user_folder ON public.leads USING btree (user_id, folder_id); CREATE INDEX IF NOT EXISTS idx_leads_user_id ON public.leads USING btree (user_id); CREATE UNIQUE INDEX IF NOT EXISTS uniq_leads_user_phone ON public.leads USING btree (user_id, phone); -- leads_webhook_brutos CREATE INDEX IF NOT EXISTS idx_lwb_body_json_gin ON public.leads_webhook_brutos USING gin (body_json); CREATE INDEX IF NOT EXISTS idx_lwb_received_at ON public.leads_webhook_brutos USING btree (received_at DESC); CREATE INDEX IF NOT EXISTS idx_lwb_status ON public.leads_webhook_brutos USING btree (status); CREATE INDEX IF NOT EXISTS idx_lwb_user_id ON public.leads_webhook_brutos USING btree (user_id); -- funis CREATE INDEX IF NOT EXISTS funis_seq_gin ON public.funis USING gin (sequencia); CREATE INDEX IF NOT EXISTS funis_status_idx ON public.funis USING btree (status); CREATE INDEX IF NOT EXISTS funis_user_idx ON public.funis USING btree (user_id); -- followups CREATE INDEX IF NOT EXISTS idx_followups_user ON public.followups USING btree (user_id, status, created_at DESC); -- studio_flows CREATE INDEX IF NOT EXISTS idx_studio_flows_updated ON public.studio_flows USING btree (updated_at DESC); CREATE INDEX IF NOT EXISTS idx_studio_flows_user ON public.studio_flows USING btree (user_id); -- disparos CREATE INDEX IF NOT EXISTS idx_disparos_crm_stage_id ON public.disparos USING btree (crm_stage_id); CREATE INDEX IF NOT EXISTS idx_disparos_status ON public.disparos USING btree (status); -- disparos_itens CREATE INDEX IF NOT EXISTS disparos_itens_etapa_studio_gin ON public.disparos_itens USING gin (etapa_studio); CREATE INDEX IF NOT EXISTS idx_di_first_sent_at ON public.disparos_itens USING btree (disparo_id, first_sent_at); CREATE INDEX IF NOT EXISTS idx_di_follow_day ON public.disparos_itens USING btree (disparo_id, follow_last_day_sent); CREATE INDEX IF NOT EXISTS idx_di_reply ON public.disparos_itens USING btree (disparo_id, replied_at); CREATE INDEX IF NOT EXISTS idx_disparos_itens_disparo_status ON public.disparos_itens USING btree (disparo_id, status); CREATE INDEX IF NOT EXISTS idx_itens_disparo_status ON public.disparos_itens USING btree (disparo_id, status); CREATE UNIQUE INDEX IF NOT EXISTS ux_disparos_itens_disparo_telefone ON public.disparos_itens USING btree (disparo_id, telefone); -- disparos_oficial CREATE INDEX IF NOT EXISTS idx_disparos_oficial_status ON public.disparos_oficial USING btree (status); CREATE INDEX IF NOT EXISTS idx_disparos_oficial_user_id ON public.disparos_oficial USING btree (user_id); -- disparos_oficial_itens CREATE INDEX IF NOT EXISTS idx_disparos_oficial_itens_disparo_id ON public.disparos_oficial_itens USING btree (disparo_id); CREATE INDEX IF NOT EXISTS idx_disparos_oficial_itens_status ON public.disparos_oficial_itens USING btree (status); CREATE UNIQUE INDEX IF NOT EXISTS uq_disparos_oficial_itens_disparo_tel ON public.disparos_oficial_itens USING btree (disparo_id, telefone); -- custom_menus CREATE INDEX IF NOT EXISTS idx_custom_menus_active_position ON public.custom_menus USING btree (active, position, sort_order) WHERE active = true; -- custom_dicas CREATE INDEX IF NOT EXISTS idx_custom_dicas_active_sort ON public.custom_dicas USING btree (active, sort_order, created_at DESC) WHERE active = true; -- ============================================================ -- SECAO 5/7 ─ TRIGGERS -- ============================================================ -- users.updated_at automático DROP TRIGGER IF EXISTS trg_users_updated ON public.users; CREATE TRIGGER trg_users_updated BEFORE UPDATE ON public.users FOR EACH ROW EXECUTE FUNCTION public.set_updated_at(); -- funis.updated_at automático DROP TRIGGER IF EXISTS funis_set_updated_at ON public.funis; CREATE TRIGGER funis_set_updated_at BEFORE UPDATE ON public.funis FOR EACH ROW EXECUTE FUNCTION public.set_updated_at(); -- followups.updated_at automático DROP TRIGGER IF EXISTS trg_followups_updated ON public.followups; CREATE TRIGGER trg_followups_updated BEFORE UPDATE ON public.followups FOR EACH ROW EXECUTE FUNCTION public.set_updated_at(); -- studio_flows.updated_at automático DROP TRIGGER IF EXISTS trg_set_updated_at_studio_flows ON public.studio_flows; CREATE TRIGGER trg_set_updated_at_studio_flows BEFORE UPDATE ON public.studio_flows FOR EACH ROW EXECUTE FUNCTION public.set_updated_at_studio_flows(); -- crm_followup_configs.updated_at automático DROP TRIGGER IF EXISTS trigger_update_followup_config_updated_at ON public.crm_followup_configs; CREATE TRIGGER trigger_update_followup_config_updated_at BEFORE UPDATE ON public.crm_followup_configs FOR EACH ROW EXECUTE FUNCTION public.update_followup_config_updated_at(); -- plans.updated_at automático DROP TRIGGER IF EXISTS trg_plans_updated_at ON public.plans; CREATE TRIGGER trg_plans_updated_at BEFORE UPDATE ON public.plans FOR EACH ROW EXECUTE FUNCTION public.update_plans_timestamp(); -- custom_menus.updated_at automático DROP TRIGGER IF EXISTS trg_custom_menus_updated ON public.custom_menus; CREATE TRIGGER trg_custom_menus_updated BEFORE UPDATE ON public.custom_menus FOR EACH ROW EXECUTE FUNCTION public.set_updated_at(); -- custom_dicas.updated_at automático DROP TRIGGER IF EXISTS trg_custom_dicas_updated ON public.custom_dicas; CREATE TRIGGER trg_custom_dicas_updated BEFORE UPDATE ON public.custom_dicas FOR EACH ROW EXECUTE FUNCTION public.set_updated_at(); -- ============================================================ -- SECAO 6/7 ─ SEEDS (dados padrão) -- ============================================================ -- Plano default com TUDO habilitado INSERT INTO public.plans (id, name, description, features, is_default, sort_order) VALUES ( 'default', 'Plano Padrão', 'Plano padrão com todas as funcionalidades habilitadas', '[ "painel", "conexoes", "leads", "disparos", "crm", "funis", "webhook", "agente-disparo", "agente-atendimento", "extrator-maps", "instagram", "radarcnpj", "gruposwpp", "apioficial", "disparos_oficial", "dicas", "evolua_chat", "minha_conta", "atualizacoes", "suporte" ]'::jsonb, true, 0 ) ON CONFLICT (id) DO NOTHING; -- Settings de IA: payer = client (cliente usa a chave OpenAI dele) INSERT INTO public.admin_ai_settings (id, payer) VALUES (1, 'client') ON CONFLICT (id) DO NOTHING; -- Settings de SMTP: registro singleton (cliente preenche no painel depois) INSERT INTO public.admin_smtp_settings (id, enabled) VALUES (1, false) ON CONFLICT (id) DO NOTHING; -- ============================================================ -- SECAO 7/7 ─ RLS DESABILITADO (todas as tabelas) -- ============================================================ -- Backend PHP usa Service Role Key, então RLS não é necessário. -- Manter desabilitado simplifica e evita erros pra cliente leigo. ALTER TABLE public.admin_ai_settings DISABLE ROW LEVEL SECURITY; ALTER TABLE public.admin_smtp_settings DISABLE ROW LEVEL SECURITY; ALTER TABLE public.api_tokens DISABLE ROW LEVEL SECURITY; ALTER TABLE public.auth_users DISABLE ROW LEVEL SECURITY; ALTER TABLE public.crm_followup_configs DISABLE ROW LEVEL SECURITY; ALTER TABLE public.crm_followup_queue DISABLE ROW LEVEL SECURITY; ALTER TABLE public.crm_stages DISABLE ROW LEVEL SECURITY; ALTER TABLE public.custom_dicas DISABLE ROW LEVEL SECURITY; ALTER TABLE public.custom_menus DISABLE ROW LEVEL SECURITY; ALTER TABLE public.disparos DISABLE ROW LEVEL SECURITY; ALTER TABLE public.disparos_itens DISABLE ROW LEVEL SECURITY; ALTER TABLE public.disparos_oficial DISABLE ROW LEVEL SECURITY; ALTER TABLE public.disparos_oficial_itens DISABLE ROW LEVEL SECURITY; ALTER TABLE public.followups DISABLE ROW LEVEL SECURITY; ALTER TABLE public.funis DISABLE ROW LEVEL SECURITY; ALTER TABLE public.instancias_vencidas DISABLE ROW LEVEL SECURITY; ALTER TABLE public.lead_folders DISABLE ROW LEVEL SECURITY; ALTER TABLE public.leads DISABLE ROW LEVEL SECURITY; ALTER TABLE public.leads_webhook_brutos DISABLE ROW LEVEL SECURITY; ALTER TABLE public.plans DISABLE ROW LEVEL SECURITY; ALTER TABLE public.prompt_models DISABLE ROW LEVEL SECURITY; ALTER TABLE public.studio_flows DISABLE ROW LEVEL SECURITY; ALTER TABLE public.super_admin_logs DISABLE ROW LEVEL SECURITY; ALTER TABLE public.super_admin_master_passwords DISABLE ROW LEVEL SECURITY; ALTER TABLE public.super_admins DISABLE ROW LEVEL SECURITY; ALTER TABLE public.users DISABLE ROW LEVEL SECURITY; CREATE TABLE IF NOT EXISTS system_metadata ( id INTEGER PRIMARY KEY DEFAULT 1 CHECK (id = 1), license_key TEXT, license_status TEXT DEFAULT 'pending' CHECK (license_status IN ('pending', 'active', 'revoked', 'suspended', 'invalid', 'expired')), allowed_domains JSONB DEFAULT '[]'::jsonb, expires_at TIMESTAMPTZ, customer_email TEXT, customer_name TEXT, plan TEXT, -- Dados de validação (assinados pelo servidor central via RSA) last_check_at TIMESTAMPTZ, last_check_payload TEXT, -- JSON assinado (TEXT preserva a ordem exata pra validação RSA) last_check_signature TEXT, -- assinatura RSA base64 do payload cached_until TIMESTAMPTZ, -- até quando o cache é válido (6h após last_check_at) notes TEXT, created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW() ); -- Trigger pra updated_at (reusa a função set_updated_at já existente no setup) DROP TRIGGER IF EXISTS trg_system_metadata_updated ON system_metadata; CREATE TRIGGER trg_system_metadata_updated BEFORE UPDATE ON system_metadata FOR EACH ROW EXECUTE FUNCTION set_updated_at(); -- Inicializa registro singleton vazio INSERT INTO system_metadata (id, license_status) VALUES (1, 'pending') ON CONFLICT (id) DO NOTHING; -- Sem RLS (padrão dos outros) ALTER TABLE system_metadata DISABLE ROW LEVEL SECURITY; COMMIT;