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