agenciapsilmno/database-novo/migrations/20260423000010_intake_abandoned_lead.sql

-- ==========================================================================
-- Agencia PSI — Migracao: Intake abandonado vira lead no CRM (8.4)
-- ==========================================================================
-- Quando paciente abre o link de cadastro externo e preenche dados parciais
-- (ex: nome + telefone) mas nao finaliza, rodamos um cron periodico que:
--   1. Varre patient_intake_requests com status='in_progress' ha > N min
--      (default 30)
--   2. Pra cada, cria uma mensagem outbound placeholder no CRM anunciando:
--      "Paciente X preencheu parcialmente o cadastro e abandonou" + dados
--      coletados (nome, telefone, motivo se tiver)
--   3. Marca intake como status='abandoned_lead' pra nao reprocessar
--
-- Pre-requisito pro fluxo funcionar: o form de intake (CadastroPacienteExterno)
-- precisa chamar save-intake-progress em algum evento de progresso
-- (ex: ao preencher telefone). Sem isso, nenhum row fica 'in_progress'.
-- ==========================================================================

-- Expande CHECK de status pra incluir 'in_progress' e 'abandoned_lead'
ALTER TABLE public.patient_intake_requests
    DROP CONSTRAINT IF EXISTS chk_intakes_status;

ALTER TABLE public.patient_intake_requests
    ADD CONSTRAINT chk_intakes_status
    CHECK (status = ANY (ARRAY[
        'new'::text,
        'in_progress'::text,
        'converted'::text,
        'rejected'::text,
        'abandoned_lead'::text
    ]));

-- Timestamp da ultima atualizacao de progresso (autosave)
ALTER TABLE public.patient_intake_requests
    ADD COLUMN IF NOT EXISTS last_progress_at TIMESTAMPTZ;

-- Referencia pra mensagem/thread criada como lead (evita duplicacao)
ALTER TABLE public.patient_intake_requests
    ADD COLUMN IF NOT EXISTS lead_thread_key TEXT;

CREATE INDEX IF NOT EXISTS idx_intakes_progress_pending
    ON public.patient_intake_requests (last_progress_at)
    WHERE status = 'in_progress';

COMMENT ON COLUMN public.patient_intake_requests.last_progress_at IS
    'Ultima vez que o form salvou dados parciais. Cron usa pra detectar abandonados.';
COMMENT ON COLUMN public.patient_intake_requests.lead_thread_key IS
    'Thread key da conversa criada no CRM quando vira lead abandonado.';


-- ---------------------------------------------------------------------------
-- RPC: convert_abandoned_intake_to_lead
-- ---------------------------------------------------------------------------
-- Processa 1 intake abandonado: cria conversation_message (outbound placeholder)
-- + conversation_note + marca status. Retorna o thread_key gerado.
--
-- SECURITY DEFINER pra bypass RLS do conversation_messages/notes.
-- ---------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.convert_abandoned_intake_to_lead(p_intake_id UUID)
RETURNS UUID
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
    v_intake RECORD;
    v_tenant_id UUID;
    v_thread_key TEXT;
    v_phone TEXT;
    v_note_body TEXT;
    v_admin_id UUID;
    v_msg_id BIGINT;
BEGIN
    SELECT * INTO v_intake FROM public.patient_intake_requests WHERE id = p_intake_id;
    IF NOT FOUND THEN RAISE EXCEPTION 'intake_not_found'; END IF;
    IF v_intake.status = 'abandoned_lead' THEN RETURN v_intake.lead_thread_key::UUID; END IF;

    -- Tenant_id vem via owner_id (tenant_members)
    SELECT tenant_id INTO v_tenant_id
        FROM public.tenant_members
        WHERE user_id = v_intake.owner_id
        ORDER BY CASE role WHEN 'tenant_admin' THEN 1 WHEN 'clinic_admin' THEN 2 ELSE 3 END
        LIMIT 1;

    IF v_tenant_id IS NULL THEN RAISE EXCEPTION 'tenant_not_resolved'; END IF;

    -- Normaliza telefone pra thread_key
    v_phone := regexp_replace(COALESCE(v_intake.telefone, ''), '\D', '', 'g');
    IF length(v_phone) BETWEEN 10 AND 11 THEN v_phone := '55' || v_phone; END IF;
    IF v_phone = '' THEN v_phone := 'unknown'; END IF;
    v_thread_key := 'anon:' || v_phone;

    -- Nota com dados coletados
    v_note_body := format(
        '📋 Lead abandonado (cadastro externo):%s%sNome: %s%sTelefone: %s%sE-mail: %s%sMotivo/Observacoes: %s%s%sIniciou em: %s · Ultima atualizacao: %s',
        E'\n', E'\n',
        COALESCE(v_intake.nome_completo, '—'),
        E'\n',
        COALESCE(v_intake.telefone, '—'),
        E'\n',
        COALESCE(v_intake.email_principal, '—'),
        E'\n',
        COALESCE(v_intake.onde_nos_conheceu, '—'),
        E'\n', E'\n',
        to_char(v_intake.created_at AT TIME ZONE 'America/Sao_Paulo', 'DD/MM HH24:MI'),
        to_char(COALESCE(v_intake.last_progress_at, v_intake.updated_at) AT TIME ZONE 'America/Sao_Paulo', 'DD/MM HH24:MI')
    );

    -- Pega 1 admin do tenant pra preencher created_by da nota
    SELECT user_id INTO v_admin_id
        FROM public.tenant_members
        WHERE tenant_id = v_tenant_id
          AND role IN ('tenant_admin', 'clinic_admin')
          AND status = 'active'
        LIMIT 1;

    IF v_admin_id IS NULL THEN
        v_admin_id := v_intake.owner_id;
    END IF;

    -- Cria mensagem placeholder (outbound sistema — entra no thread do CRM)
    INSERT INTO public.conversation_messages
        (tenant_id, channel, direction, from_number, to_number, body, provider,
         provider_raw, kanban_status)
    VALUES (
        v_tenant_id, 'whatsapp', 'inbound',
        CASE WHEN v_phone = 'unknown' THEN NULL ELSE v_phone END,
        NULL,
        format('🧾 Cadastro externo iniciado e não finalizado. %s entrou em contato via link público mas abandonou o formulário — ver nota interna.',
               COALESCE(v_intake.nome_completo, 'Visitante')),
        'system',
        jsonb_build_object('lead_from_abandoned_intake', true, 'intake_id', v_intake.id),
        'awaiting_us'
    ) RETURNING id INTO v_msg_id;

    -- Cria nota interna
    INSERT INTO public.conversation_notes
        (tenant_id, thread_key, contact_number, body, created_by)
    VALUES (
        v_tenant_id, v_thread_key,
        CASE WHEN v_phone = 'unknown' THEN NULL ELSE v_phone END,
        v_note_body, v_admin_id
    );

    -- Atualiza intake
    UPDATE public.patient_intake_requests
        SET status = 'abandoned_lead',
            lead_thread_key = v_thread_key,
            updated_at = now()
        WHERE id = p_intake_id;

    RETURN p_intake_id;
END;
$$;

REVOKE ALL ON FUNCTION public.convert_abandoned_intake_to_lead(UUID) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.convert_abandoned_intake_to_lead(UUID) TO service_role;

COMMENT ON FUNCTION public.convert_abandoned_intake_to_lead(UUID) IS
    'Transforma 1 intake abandonado em lead no CRM: cria mensagem placeholder + nota com dados + marca status.';