662 lines
27 KiB
PL/PgSQL
662 lines
27 KiB
PL/PgSQL
-- =============================================================================
|
|
-- MIGRATION: patients — melhorias completas
|
|
-- Gerado em: 2025-03
|
|
-- Estratégia: cirúrgico — só adiciona, nunca destrói o que existe
|
|
-- =============================================================================
|
|
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- 1. ALTERAÇÕES NA TABELA patients
|
|
-- Novos campos adicionados sem tocar nos existentes
|
|
-- -----------------------------------------------------------------------------
|
|
|
|
ALTER TABLE public.patients
|
|
-- Identidade & pronomes
|
|
ADD COLUMN IF NOT EXISTS nome_social text,
|
|
ADD COLUMN IF NOT EXISTS pronomes text,
|
|
|
|
-- Dados socioeconômicos (opcionais, clínicamente relevantes)
|
|
ADD COLUMN IF NOT EXISTS etnia text,
|
|
ADD COLUMN IF NOT EXISTS religiao text,
|
|
ADD COLUMN IF NOT EXISTS faixa_renda text,
|
|
|
|
-- Preferências de comunicação (alimenta lembretes automáticos)
|
|
ADD COLUMN IF NOT EXISTS canal_preferido text DEFAULT 'whatsapp',
|
|
ADD COLUMN IF NOT EXISTS horario_contato_inicio time DEFAULT '08:00',
|
|
ADD COLUMN IF NOT EXISTS horario_contato_fim time DEFAULT '20:00',
|
|
ADD COLUMN IF NOT EXISTS idioma text DEFAULT 'pt-BR',
|
|
|
|
-- Origem estruturada (permite filtros e relatórios)
|
|
ADD COLUMN IF NOT EXISTS origem text,
|
|
|
|
-- Financeiro
|
|
ADD COLUMN IF NOT EXISTS metodo_pagamento_preferido text,
|
|
|
|
-- Ciclo de vida
|
|
ADD COLUMN IF NOT EXISTS motivo_saida text,
|
|
ADD COLUMN IF NOT EXISTS data_saida date,
|
|
ADD COLUMN IF NOT EXISTS encaminhado_para text,
|
|
|
|
-- Risco clínico (flag de atenção visível no topo do cadastro)
|
|
ADD COLUMN IF NOT EXISTS risco_elevado boolean DEFAULT false NOT NULL,
|
|
ADD COLUMN IF NOT EXISTS risco_nota text,
|
|
ADD COLUMN IF NOT EXISTS risco_sinalizado_em timestamp with time zone,
|
|
ADD COLUMN IF NOT EXISTS risco_sinalizado_por uuid REFERENCES auth.users(id) ON DELETE SET NULL;
|
|
|
|
|
|
-- Constraints de validação para novos campos enum-like
|
|
ALTER TABLE public.patients
|
|
DROP CONSTRAINT IF EXISTS patients_canal_preferido_check,
|
|
ADD CONSTRAINT patients_canal_preferido_check
|
|
CHECK (canal_preferido IS NULL OR canal_preferido = ANY (
|
|
ARRAY['whatsapp','email','sms','telefone']
|
|
));
|
|
|
|
ALTER TABLE public.patients
|
|
DROP CONSTRAINT IF EXISTS patients_metodo_pagamento_check,
|
|
ADD CONSTRAINT patients_metodo_pagamento_check
|
|
CHECK (metodo_pagamento_preferido IS NULL OR metodo_pagamento_preferido = ANY (
|
|
ARRAY['pix','cartao','dinheiro','deposito','convenio']
|
|
));
|
|
|
|
ALTER TABLE public.patients
|
|
DROP CONSTRAINT IF EXISTS patients_faixa_renda_check,
|
|
ADD CONSTRAINT patients_faixa_renda_check
|
|
CHECK (faixa_renda IS NULL OR faixa_renda = ANY (
|
|
ARRAY['ate_1sm','1_3sm','3_6sm','6_10sm','acima_10sm','nao_informado']
|
|
));
|
|
|
|
-- Constraint: risco_elevado = true exige nota e sinalizante
|
|
ALTER TABLE public.patients
|
|
DROP CONSTRAINT IF EXISTS patients_risco_consistency_check,
|
|
ADD CONSTRAINT patients_risco_consistency_check
|
|
CHECK (
|
|
(risco_elevado = false)
|
|
OR (risco_elevado = true AND risco_nota IS NOT NULL AND risco_sinalizado_por IS NOT NULL)
|
|
);
|
|
|
|
-- Comments
|
|
COMMENT ON COLUMN public.patients.nome_social IS 'Nome social preferido — exibido no lugar do nome completo quando preenchido';
|
|
COMMENT ON COLUMN public.patients.pronomes IS 'Pronomes preferidos: ele/dele, ela/dela, eles/deles, etc.';
|
|
COMMENT ON COLUMN public.patients.etnia IS 'Autodeclaração étnico-racial (opcional)';
|
|
COMMENT ON COLUMN public.patients.religiao IS 'Religião ou espiritualidade (opcional, relevante clinicamente)';
|
|
COMMENT ON COLUMN public.patients.faixa_renda IS 'Faixa de renda em salários mínimos — usado para precificação solidária';
|
|
COMMENT ON COLUMN public.patients.canal_preferido IS 'Canal de comunicação preferido para lembretes e notificações';
|
|
COMMENT ON COLUMN public.patients.horario_contato_inicio IS 'Início da janela de horário preferida para contato';
|
|
COMMENT ON COLUMN public.patients.horario_contato_fim IS 'Fim da janela de horário preferida para contato';
|
|
COMMENT ON COLUMN public.patients.origem IS 'Como o paciente chegou: indicacao, agendador, redes_sociais, encaminhamento, outro';
|
|
COMMENT ON COLUMN public.patients.metodo_pagamento_preferido IS 'Método de pagamento habitual — sugerido ao criar cobrança';
|
|
COMMENT ON COLUMN public.patients.motivo_saida IS 'Motivo da alta, inativação ou encaminhamento';
|
|
COMMENT ON COLUMN public.patients.data_saida IS 'Data em que o paciente foi desligado/encaminhado';
|
|
COMMENT ON COLUMN public.patients.encaminhado_para IS 'Nome ou serviço para onde o paciente foi encaminhado';
|
|
COMMENT ON COLUMN public.patients.risco_elevado IS 'Flag de atenção clínica — exibe alerta no topo do cadastro e prontuário';
|
|
COMMENT ON COLUMN public.patients.risco_nota IS 'Descrição do risco (obrigatória quando risco_elevado = true)';
|
|
COMMENT ON COLUMN public.patients.risco_sinalizado_em IS 'Timestamp em que o risco foi sinalizado';
|
|
COMMENT ON COLUMN public.patients.risco_sinalizado_por IS 'Usuário que sinalizou o risco';
|
|
|
|
-- Índices úteis para filtros frequentes
|
|
CREATE INDEX IF NOT EXISTS idx_patients_risco_elevado
|
|
ON public.patients (tenant_id, risco_elevado)
|
|
WHERE risco_elevado = true;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_patients_status_tenant
|
|
ON public.patients (tenant_id, status);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_patients_origem
|
|
ON public.patients (tenant_id, origem)
|
|
WHERE origem IS NOT NULL;
|
|
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- 2. TABELA patient_contacts
|
|
-- Substitui os campos soltos nome_parente/telefone_parente na tabela principal
|
|
-- Os campos antigos ficam intactos (retrocompatibilidade)
|
|
-- -----------------------------------------------------------------------------
|
|
|
|
CREATE TABLE IF NOT EXISTS public.patient_contacts (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
patient_id uuid NOT NULL REFERENCES public.patients(id) ON DELETE CASCADE,
|
|
tenant_id uuid NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Identificação
|
|
nome text NOT NULL,
|
|
tipo text NOT NULL, -- emergencia | responsavel_legal | profissional_saude | outro
|
|
relacao text, -- mãe, pai, psiquiatra, médico, cônjuge...
|
|
|
|
-- Contato
|
|
telefone text,
|
|
email text,
|
|
cpf text,
|
|
|
|
-- Profissional de saúde
|
|
especialidade text, -- preenchido quando tipo = profissional_saude
|
|
registro_profissional text, -- CRM, CRP, etc.
|
|
|
|
-- Flags
|
|
is_primario boolean DEFAULT false NOT NULL, -- contato principal de emergência
|
|
ativo boolean DEFAULT true NOT NULL,
|
|
|
|
-- Auditoria
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
|
|
CONSTRAINT patient_contacts_pkey PRIMARY KEY (id),
|
|
CONSTRAINT patient_contacts_tipo_check CHECK (tipo = ANY (
|
|
ARRAY['emergencia','responsavel_legal','profissional_saude','outro']
|
|
))
|
|
);
|
|
|
|
COMMENT ON TABLE public.patient_contacts IS 'Contatos vinculados ao paciente: emergência, responsável legal, outros profissionais de saúde';
|
|
COMMENT ON COLUMN public.patient_contacts.tipo IS 'Categoria do contato: emergencia | responsavel_legal | profissional_saude | outro';
|
|
COMMENT ON COLUMN public.patient_contacts.is_primario IS 'Contato de emergência principal — exibido em destaque no cadastro';
|
|
|
|
-- Garante no máximo 1 contato primário por paciente
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uq_patient_contacts_primario
|
|
ON public.patient_contacts (patient_id)
|
|
WHERE is_primario = true AND ativo = true;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_patient_contacts_patient
|
|
ON public.patient_contacts (patient_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_patient_contacts_tenant
|
|
ON public.patient_contacts (tenant_id);
|
|
|
|
-- updated_at automático
|
|
CREATE TRIGGER trg_patient_contacts_updated_at
|
|
BEFORE UPDATE ON public.patient_contacts
|
|
FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();
|
|
|
|
-- RLS — mesmas regras de patients
|
|
ALTER TABLE public.patient_contacts ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY patient_contacts_select ON public.patient_contacts
|
|
FOR SELECT USING (
|
|
public.is_clinic_tenant(tenant_id)
|
|
AND public.is_tenant_member(tenant_id)
|
|
AND public.tenant_has_feature(tenant_id, 'patients.view')
|
|
);
|
|
|
|
CREATE POLICY patient_contacts_write ON public.patient_contacts
|
|
USING (
|
|
public.is_clinic_tenant(tenant_id)
|
|
AND public.is_tenant_member(tenant_id)
|
|
AND public.tenant_has_feature(tenant_id, 'patients.edit')
|
|
)
|
|
WITH CHECK (
|
|
public.is_clinic_tenant(tenant_id)
|
|
AND public.is_tenant_member(tenant_id)
|
|
AND public.tenant_has_feature(tenant_id, 'patients.edit')
|
|
);
|
|
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- 3. TABELA patient_status_history
|
|
-- Trilha de auditoria de todas as mudanças de status do paciente
|
|
-- -----------------------------------------------------------------------------
|
|
|
|
CREATE TABLE IF NOT EXISTS public.patient_status_history (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
patient_id uuid NOT NULL REFERENCES public.patients(id) ON DELETE CASCADE,
|
|
tenant_id uuid NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
|
|
|
|
status_anterior text, -- NULL na primeira inserção
|
|
status_novo text NOT NULL,
|
|
motivo text,
|
|
encaminhado_para text, -- preenchido quando status = Encaminhado
|
|
data_saida date, -- preenchido quando Alta/Encaminhado/Arquivado
|
|
|
|
alterado_por uuid REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
alterado_em timestamp with time zone DEFAULT now() NOT NULL,
|
|
|
|
CONSTRAINT patient_status_history_pkey PRIMARY KEY (id),
|
|
CONSTRAINT psh_status_novo_check CHECK (status_novo = ANY (
|
|
ARRAY['Ativo','Inativo','Alta','Encaminhado','Arquivado']
|
|
))
|
|
);
|
|
|
|
COMMENT ON TABLE public.patient_status_history IS 'Histórico imutável de todas as mudanças de status do paciente — não editar, apenas inserir';
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_psh_patient
|
|
ON public.patient_status_history (patient_id, alterado_em DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_psh_tenant
|
|
ON public.patient_status_history (tenant_id, alterado_em DESC);
|
|
|
|
-- RLS
|
|
ALTER TABLE public.patient_status_history ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY psh_select ON public.patient_status_history
|
|
FOR SELECT USING (
|
|
public.is_clinic_tenant(tenant_id)
|
|
AND public.is_tenant_member(tenant_id)
|
|
AND public.tenant_has_feature(tenant_id, 'patients.view')
|
|
);
|
|
|
|
CREATE POLICY psh_insert ON public.patient_status_history
|
|
FOR INSERT WITH CHECK (
|
|
public.is_clinic_tenant(tenant_id)
|
|
AND public.is_tenant_member(tenant_id)
|
|
AND public.tenant_has_feature(tenant_id, 'patients.edit')
|
|
);
|
|
|
|
-- Trigger: registra automaticamente no histórico quando status muda em patients
|
|
CREATE OR REPLACE FUNCTION public.trg_fn_patient_status_history()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
BEGIN
|
|
IF (TG_OP = 'INSERT') OR (OLD.status IS DISTINCT FROM NEW.status) THEN
|
|
INSERT INTO public.patient_status_history (
|
|
patient_id, tenant_id,
|
|
status_anterior, status_novo,
|
|
motivo, encaminhado_para, data_saida,
|
|
alterado_por, alterado_em
|
|
) VALUES (
|
|
NEW.id, NEW.tenant_id,
|
|
CASE WHEN TG_OP = 'INSERT' THEN NULL ELSE OLD.status END,
|
|
NEW.status,
|
|
NEW.motivo_saida,
|
|
NEW.encaminhado_para,
|
|
NEW.data_saida,
|
|
auth.uid(),
|
|
now()
|
|
);
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
DROP TRIGGER IF EXISTS trg_patient_status_history ON public.patients;
|
|
CREATE TRIGGER trg_patient_status_history
|
|
AFTER INSERT OR UPDATE OF status ON public.patients
|
|
FOR EACH ROW EXECUTE FUNCTION public.trg_fn_patient_status_history();
|
|
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- 4. TABELA patient_timeline
|
|
-- Feed cronológico automático de eventos relevantes do paciente
|
|
-- -----------------------------------------------------------------------------
|
|
|
|
CREATE TABLE IF NOT EXISTS public.patient_timeline (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
patient_id uuid NOT NULL REFERENCES public.patients(id) ON DELETE CASCADE,
|
|
tenant_id uuid NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Tipo do evento
|
|
evento_tipo text NOT NULL,
|
|
-- Exemplos: primeira_sessao | sessao_realizada | sessao_cancelada | falta |
|
|
-- status_alterado | risco_sinalizado | documento_assinado |
|
|
-- escala_respondida | pagamento_vencido | pagamento_recebido |
|
|
-- tarefa_combinada | contato_adicionado | prontuario_editado
|
|
|
|
titulo text NOT NULL, -- Ex: "Sessão realizada"
|
|
descricao text, -- Ex: "Sessão 47 · presencial · 50min"
|
|
icone_cor text DEFAULT 'gray', -- green | blue | amber | red | gray
|
|
link_ref_tipo text, -- agenda_evento | financial_record | documento | escala
|
|
link_ref_id uuid, -- FK genérico — sem constraint formal (polimórfico)
|
|
gerado_por uuid REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
ocorrido_em timestamp with time zone DEFAULT now() NOT NULL,
|
|
|
|
CONSTRAINT patient_timeline_pkey PRIMARY KEY (id),
|
|
CONSTRAINT pt_evento_tipo_check CHECK (evento_tipo = ANY (ARRAY[
|
|
'primeira_sessao','sessao_realizada','sessao_cancelada','falta',
|
|
'status_alterado','risco_sinalizado','risco_removido',
|
|
'documento_assinado','documento_adicionado',
|
|
'escala_respondida','escala_enviada',
|
|
'pagamento_vencido','pagamento_recebido',
|
|
'tarefa_combinada','contato_adicionado',
|
|
'prontuario_editado','nota_adicionada','manual'
|
|
])),
|
|
CONSTRAINT pt_icone_cor_check CHECK (icone_cor = ANY (
|
|
ARRAY['green','blue','amber','red','gray','purple']
|
|
))
|
|
);
|
|
|
|
COMMENT ON TABLE public.patient_timeline IS 'Feed cronológico de eventos do paciente — alimentado por triggers e inserções manuais';
|
|
COMMENT ON COLUMN public.patient_timeline.link_ref_tipo IS 'Tipo da entidade referenciada (polimórfico): agenda_evento | financial_record | documento | escala';
|
|
COMMENT ON COLUMN public.patient_timeline.link_ref_id IS 'ID da entidade referenciada — sem FK formal para suportar múltiplos tipos';
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_pt_patient_ocorrido
|
|
ON public.patient_timeline (patient_id, ocorrido_em DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_pt_tenant
|
|
ON public.patient_timeline (tenant_id, ocorrido_em DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_pt_evento_tipo
|
|
ON public.patient_timeline (patient_id, evento_tipo);
|
|
|
|
-- RLS
|
|
ALTER TABLE public.patient_timeline ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY pt_select ON public.patient_timeline
|
|
FOR SELECT USING (
|
|
public.is_clinic_tenant(tenant_id)
|
|
AND public.is_tenant_member(tenant_id)
|
|
AND public.tenant_has_feature(tenant_id, 'patients.view')
|
|
);
|
|
|
|
CREATE POLICY pt_insert ON public.patient_timeline
|
|
FOR INSERT WITH CHECK (
|
|
public.is_clinic_tenant(tenant_id)
|
|
AND public.is_tenant_member(tenant_id)
|
|
AND public.tenant_has_feature(tenant_id, 'patients.edit')
|
|
);
|
|
|
|
-- Trigger: registra na timeline quando risco é sinalizado/removido
|
|
CREATE OR REPLACE FUNCTION public.trg_fn_patient_risco_timeline()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
BEGIN
|
|
IF OLD.risco_elevado IS DISTINCT FROM NEW.risco_elevado THEN
|
|
INSERT INTO public.patient_timeline (
|
|
patient_id, tenant_id,
|
|
evento_tipo, titulo, descricao, icone_cor,
|
|
gerado_por, ocorrido_em
|
|
) VALUES (
|
|
NEW.id, NEW.tenant_id,
|
|
CASE WHEN NEW.risco_elevado THEN 'risco_sinalizado' ELSE 'risco_removido' END,
|
|
CASE WHEN NEW.risco_elevado THEN 'Risco elevado sinalizado' ELSE 'Sinalização de risco removida' END,
|
|
NEW.risco_nota,
|
|
CASE WHEN NEW.risco_elevado THEN 'red' ELSE 'green' END,
|
|
auth.uid(),
|
|
now()
|
|
);
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
DROP TRIGGER IF EXISTS trg_patient_risco_timeline ON public.patients;
|
|
CREATE TRIGGER trg_patient_risco_timeline
|
|
AFTER UPDATE OF risco_elevado ON public.patients
|
|
FOR EACH ROW EXECUTE FUNCTION public.trg_fn_patient_risco_timeline();
|
|
|
|
-- Trigger: registra na timeline quando status muda
|
|
CREATE OR REPLACE FUNCTION public.trg_fn_patient_status_timeline()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
BEGIN
|
|
IF (TG_OP = 'INSERT') OR (OLD.status IS DISTINCT FROM NEW.status) THEN
|
|
INSERT INTO public.patient_timeline (
|
|
patient_id, tenant_id,
|
|
evento_tipo, titulo, descricao, icone_cor,
|
|
gerado_por, ocorrido_em
|
|
) VALUES (
|
|
NEW.id, NEW.tenant_id,
|
|
'status_alterado',
|
|
'Status alterado para ' || NEW.status,
|
|
CASE
|
|
WHEN TG_OP = 'INSERT' THEN 'Paciente cadastrado'
|
|
ELSE 'De ' || OLD.status || ' → ' || NEW.status ||
|
|
CASE WHEN NEW.motivo_saida IS NOT NULL THEN ' · ' || NEW.motivo_saida ELSE '' END
|
|
END,
|
|
CASE NEW.status
|
|
WHEN 'Ativo' THEN 'green'
|
|
WHEN 'Alta' THEN 'blue'
|
|
WHEN 'Inativo' THEN 'gray'
|
|
WHEN 'Encaminhado' THEN 'amber'
|
|
WHEN 'Arquivado' THEN 'gray'
|
|
ELSE 'gray'
|
|
END,
|
|
auth.uid(),
|
|
now()
|
|
);
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
DROP TRIGGER IF EXISTS trg_patient_status_timeline ON public.patients;
|
|
CREATE TRIGGER trg_patient_status_timeline
|
|
AFTER INSERT OR UPDATE OF status ON public.patients
|
|
FOR EACH ROW EXECUTE FUNCTION public.trg_fn_patient_status_timeline();
|
|
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- 5. VIEW v_patient_engajamento
|
|
-- Score calculado em tempo real — sem armazenar, sem inconsistência
|
|
-- -----------------------------------------------------------------------------
|
|
|
|
CREATE OR REPLACE VIEW public.v_patient_engajamento
|
|
WITH (security_invoker = on)
|
|
AS
|
|
WITH sessoes AS (
|
|
SELECT
|
|
ae.patient_id,
|
|
ae.tenant_id,
|
|
COUNT(*) FILTER (WHERE ae.status = 'realizado') AS total_realizadas,
|
|
COUNT(*) FILTER (WHERE ae.status IN ('realizado','cancelado','faltou')) AS total_marcadas,
|
|
COUNT(*) FILTER (WHERE ae.status = 'faltou') AS total_faltas,
|
|
MAX(ae.inicio_em) FILTER (WHERE ae.status = 'realizado') AS ultima_sessao_em,
|
|
MIN(ae.inicio_em) FILTER (WHERE ae.status = 'realizado') AS primeira_sessao_em,
|
|
COUNT(*) FILTER (WHERE ae.status = 'realizado'
|
|
AND ae.inicio_em >= now() - interval '30 days') AS sessoes_ultimo_mes
|
|
FROM public.agenda_eventos ae
|
|
WHERE ae.patient_id IS NOT NULL
|
|
GROUP BY ae.patient_id, ae.tenant_id
|
|
),
|
|
financeiro AS (
|
|
SELECT
|
|
fr.patient_id,
|
|
fr.tenant_id,
|
|
COALESCE(SUM(fr.final_amount) FILTER (WHERE fr.status = 'paid'), 0) AS total_pago,
|
|
COALESCE(AVG(fr.final_amount) FILTER (WHERE fr.status = 'paid'), 0) AS ticket_medio,
|
|
COUNT(*) FILTER (WHERE fr.status IN ('pending','overdue')
|
|
AND fr.due_date < now()) AS cobr_vencidas,
|
|
COUNT(*) FILTER (WHERE fr.status IN ('pending','overdue')) AS cobr_pendentes,
|
|
COUNT(*) FILTER (WHERE fr.type = 'receita' AND fr.status = 'paid') AS cobr_pagas
|
|
FROM public.financial_records fr
|
|
WHERE fr.patient_id IS NOT NULL
|
|
AND fr.deleted_at IS NULL
|
|
GROUP BY fr.patient_id, fr.tenant_id
|
|
)
|
|
SELECT
|
|
p.id AS patient_id,
|
|
p.tenant_id,
|
|
p.nome_completo,
|
|
p.status,
|
|
p.risco_elevado,
|
|
|
|
-- Sessões
|
|
COALESCE(s.total_realizadas, 0) AS total_sessoes,
|
|
COALESCE(s.sessoes_ultimo_mes, 0) AS sessoes_ultimo_mes,
|
|
s.primeira_sessao_em,
|
|
s.ultima_sessao_em,
|
|
EXTRACT(DAY FROM now() - s.ultima_sessao_em)::int AS dias_sem_sessao,
|
|
|
|
-- Taxa de comparecimento (%)
|
|
CASE
|
|
WHEN COALESCE(s.total_marcadas, 0) = 0 THEN NULL
|
|
ELSE ROUND((s.total_realizadas::numeric / s.total_marcadas) * 100, 1)
|
|
END AS taxa_comparecimento,
|
|
|
|
-- Financeiro
|
|
COALESCE(f.total_pago, 0) AS ltv_total,
|
|
ROUND(COALESCE(f.ticket_medio, 0), 2) AS ticket_medio,
|
|
COALESCE(f.cobr_vencidas, 0) AS cobr_vencidas,
|
|
COALESCE(f.cobr_pagas, 0) AS cobr_pagas,
|
|
|
|
-- Taxa de pagamentos em dia (%)
|
|
CASE
|
|
WHEN COALESCE(f.cobr_pagas + f.cobr_vencidas, 0) = 0 THEN NULL
|
|
ELSE ROUND(
|
|
f.cobr_pagas::numeric / (f.cobr_pagas + f.cobr_vencidas) * 100, 1
|
|
)
|
|
END AS taxa_pagamentos_dia,
|
|
|
|
-- Score de engajamento composto (0-100)
|
|
-- Pesos: comparecimento 50%, pagamentos 30%, recência 20%
|
|
ROUND(
|
|
LEAST(100,
|
|
COALESCE(
|
|
(
|
|
-- Comparecimento (50 pts)
|
|
CASE WHEN COALESCE(s.total_marcadas, 0) = 0 THEN 50
|
|
ELSE LEAST(50, (s.total_realizadas::numeric / s.total_marcadas) * 50)
|
|
END
|
|
+
|
|
-- Pagamentos em dia (30 pts)
|
|
CASE WHEN COALESCE(f.cobr_pagas + f.cobr_vencidas, 0) = 0 THEN 30
|
|
ELSE LEAST(30, f.cobr_pagas::numeric / (f.cobr_pagas + f.cobr_vencidas) * 30)
|
|
END
|
|
+
|
|
-- Recência (20 pts — penaliza quem está há muito tempo sem sessão)
|
|
CASE
|
|
WHEN s.ultima_sessao_em IS NULL THEN 0
|
|
WHEN EXTRACT(DAY FROM now() - s.ultima_sessao_em) <= 14 THEN 20
|
|
WHEN EXTRACT(DAY FROM now() - s.ultima_sessao_em) <= 30 THEN 15
|
|
WHEN EXTRACT(DAY FROM now() - s.ultima_sessao_em) <= 60 THEN 8
|
|
ELSE 0
|
|
END
|
|
), 0
|
|
)
|
|
)
|
|
, 0) AS engajamento_score,
|
|
|
|
-- Duração do tratamento
|
|
CASE
|
|
WHEN s.primeira_sessao_em IS NULL THEN NULL
|
|
ELSE EXTRACT(DAY FROM now() - s.primeira_sessao_em)::int
|
|
END AS duracao_tratamento_dias
|
|
|
|
FROM public.patients p
|
|
LEFT JOIN sessoes s ON s.patient_id = p.id AND s.tenant_id = p.tenant_id
|
|
LEFT JOIN financeiro f ON f.patient_id = p.id AND f.tenant_id = p.tenant_id;
|
|
|
|
COMMENT ON VIEW public.v_patient_engajamento IS
|
|
'Score de engajamento e métricas consolidadas por paciente. Calculado em tempo real via RLS (security_invoker=on).';
|
|
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- 6. VIEW v_patients_risco
|
|
-- Lista rápida de pacientes que precisam de atenção imediata
|
|
-- -----------------------------------------------------------------------------
|
|
|
|
CREATE OR REPLACE VIEW public.v_patients_risco
|
|
WITH (security_invoker = on)
|
|
AS
|
|
SELECT
|
|
p.id,
|
|
p.tenant_id,
|
|
p.nome_completo,
|
|
p.status,
|
|
p.risco_elevado,
|
|
p.risco_nota,
|
|
p.risco_sinalizado_em,
|
|
e.dias_sem_sessao,
|
|
e.engajamento_score,
|
|
e.taxa_comparecimento,
|
|
-- Motivo do alerta
|
|
CASE
|
|
WHEN p.risco_elevado THEN 'risco_sinalizado'
|
|
WHEN COALESCE(e.dias_sem_sessao, 999) > 30
|
|
AND p.status = 'Ativo' THEN 'sem_sessao_30d'
|
|
WHEN COALESCE(e.taxa_comparecimento, 100) < 60 THEN 'baixo_comparecimento'
|
|
WHEN COALESCE(e.cobr_vencidas, 0) > 0 THEN 'cobranca_vencida'
|
|
ELSE 'ok'
|
|
END AS alerta_tipo
|
|
FROM public.patients p
|
|
JOIN public.v_patient_engajamento e ON e.patient_id = p.id
|
|
WHERE p.status = 'Ativo'
|
|
AND (
|
|
p.risco_elevado = true
|
|
OR COALESCE(e.dias_sem_sessao, 999) > 30
|
|
OR COALESCE(e.taxa_comparecimento, 100) < 60
|
|
OR COALESCE(e.cobr_vencidas, 0) > 0
|
|
);
|
|
|
|
COMMENT ON VIEW public.v_patients_risco IS
|
|
'Pacientes ativos que precisam de atenção: risco clínico, sem sessão há 30+ dias, baixo comparecimento ou cobrança vencida';
|
|
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- 7. Migração de dados: popular patient_contacts com os dados já existentes
|
|
-- Roda só uma vez — protegido por WHERE NOT EXISTS
|
|
-- -----------------------------------------------------------------------------
|
|
|
|
INSERT INTO public.patient_contacts (
|
|
patient_id, tenant_id,
|
|
nome, tipo, relacao,
|
|
telefone, is_primario, ativo
|
|
)
|
|
SELECT
|
|
p.id,
|
|
p.tenant_id,
|
|
p.nome_parente,
|
|
'emergencia',
|
|
p.grau_parentesco,
|
|
p.telefone_parente,
|
|
true,
|
|
true
|
|
FROM public.patients p
|
|
WHERE p.nome_parente IS NOT NULL
|
|
AND p.telefone_parente IS NOT NULL
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM public.patient_contacts pc
|
|
WHERE pc.patient_id = p.id AND pc.tipo = 'emergencia'
|
|
);
|
|
|
|
-- Migra responsável legal quando diferente do parente de emergência
|
|
INSERT INTO public.patient_contacts (
|
|
patient_id, tenant_id,
|
|
nome, tipo, relacao,
|
|
telefone, cpf,
|
|
is_primario, ativo
|
|
)
|
|
SELECT
|
|
p.id,
|
|
p.tenant_id,
|
|
p.nome_responsavel,
|
|
'responsavel_legal',
|
|
'Responsável legal',
|
|
p.telefone_responsavel,
|
|
p.cpf_responsavel,
|
|
false,
|
|
true
|
|
FROM public.patients p
|
|
WHERE p.nome_responsavel IS NOT NULL
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM public.patient_contacts pc
|
|
WHERE pc.patient_id = p.id AND pc.tipo = 'responsavel_legal'
|
|
);
|
|
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- 8. Seed do histórico de status para pacientes já existentes
|
|
-- Cria a primeira entrada de histórico com o status atual
|
|
-- -----------------------------------------------------------------------------
|
|
|
|
INSERT INTO public.patient_status_history (
|
|
patient_id, tenant_id,
|
|
status_anterior, status_novo,
|
|
motivo, alterado_em
|
|
)
|
|
SELECT
|
|
p.id,
|
|
p.tenant_id,
|
|
NULL,
|
|
p.status,
|
|
'Status inicial — migração de dados',
|
|
COALESCE(p.created_at, now())
|
|
FROM public.patients p
|
|
WHERE NOT EXISTS (
|
|
SELECT 1 FROM public.patient_status_history psh
|
|
WHERE psh.patient_id = p.id
|
|
);
|
|
|
|
|
|
-- =============================================================================
|
|
-- FIM DO MIGRATION
|
|
-- Resumo do que foi feito:
|
|
-- 1. ALTER TABLE patients — 16 novos campos (pronomes, risco, origem, etc.)
|
|
-- 2. CREATE TABLE patient_contacts — múltiplos contatos por paciente
|
|
-- 3. CREATE TABLE patient_status_history — trilha imutável de mudanças de status
|
|
-- 4. CREATE TABLE patient_timeline — feed cronológico de eventos
|
|
-- 5. Triggers automáticos — status history, timeline de risco e status
|
|
-- 6. VIEW v_patient_engajamento — score 0-100 + métricas calculadas em tempo real
|
|
-- 7. VIEW v_patients_risco — lista de pacientes que precisam de atenção
|
|
-- 8. Migração de dados — popula patient_contacts e status_history com dados existentes
|
|
-- =============================================================================
|