Preparar Supabase

-- ============================================================
-- 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;