agenciapsilmno/database-novo/schema/04_tables/patients.sql

-- =============================================================================
-- AgenciaPsi — Tables — Pacientes
-- =============================================================================
-- patients, patient_groups, patient_group_patient, patient_tags,
-- patient_patient_tag, patient_intake_requests, patient_invites,
-- patient_discounts
-- =============================================================================

CREATE TABLE public.patients (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    nome_completo text NOT NULL,
    email_principal text,
    telefone text,
    created_at timestamp with time zone DEFAULT now(),
    owner_id uuid,
    avatar_url text,
    status text DEFAULT 'Ativo'::text,
    last_attended_at timestamp with time zone,
    is_native boolean DEFAULT false,
    naturalidade text,
    data_nascimento date,
    rg text,
    cpf text,
    identification_color text,
    genero text,
    estado_civil text,
    email_alternativo text,
    pais text DEFAULT 'Brasil'::text,
    cep text,
    cidade text,
    estado text,
    endereco text,
    numero text,
    bairro text,
    complemento text,
    escolaridade text,
    profissao text,
    nome_parente text,
    grau_parentesco text,
    telefone_alternativo text,
    onde_nos_conheceu text,
    encaminhado_por text,
    nome_responsavel text,
    telefone_responsavel text,
    cpf_responsavel text,
    observacao_responsavel text,
    cobranca_no_responsavel boolean DEFAULT false,
    observacoes text,
    notas_internas text,
    updated_at timestamp with time zone DEFAULT now(),
    telefone_parente text,
    tenant_id uuid NOT NULL,
    responsible_member_id uuid NOT NULL,
    user_id uuid,
    patient_scope text DEFAULT 'clinic'::text NOT NULL,
    therapist_member_id uuid,
    CONSTRAINT cpf_responsavel_format_check CHECK (((cpf_responsavel IS NULL) OR (cpf_responsavel ~ '^\d{11}$'::text))),
    CONSTRAINT patients_cpf_format_check CHECK (((cpf IS NULL) OR (cpf ~ '^\d{11}$'::text))),
    CONSTRAINT patients_patient_scope_check CHECK ((patient_scope = ANY (ARRAY['clinic'::text, 'therapist'::text]))),
    CONSTRAINT patients_status_check CHECK ((status = ANY (ARRAY['Ativo'::text, 'Inativo'::text, 'Alta'::text, 'Encaminhado'::text, 'Arquivado'::text]))),
    CONSTRAINT patients_therapist_scope_consistency CHECK ((((patient_scope = 'clinic'::text) AND (therapist_member_id IS NULL)) OR ((patient_scope = 'therapist'::text) AND (therapist_member_id IS NOT NULL))))
);



CREATE TABLE public.patient_groups (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    nome text NOT NULL,
    descricao text,
    cor text,
    is_active boolean DEFAULT true NOT NULL,
    is_system boolean DEFAULT false NOT NULL,
    owner_id uuid DEFAULT auth.uid() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    therapist_id uuid,
    tenant_id uuid NOT NULL
);



CREATE TABLE public.patient_group_patient (
    patient_group_id uuid NOT NULL,
    patient_id uuid NOT NULL,
    created_at timestamp with time zone DEFAULT now(),
    tenant_id uuid NOT NULL
);



CREATE TABLE public.patient_tags (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    owner_id uuid NOT NULL,
    nome text NOT NULL,
    cor text,
    is_padrao boolean DEFAULT false NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone,
    tenant_id uuid NOT NULL
);



CREATE TABLE public.patient_patient_tag (
    owner_id uuid NOT NULL,
    patient_id uuid NOT NULL,
    tag_id uuid NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    tenant_id uuid NOT NULL
);



CREATE TABLE public.patient_intake_requests (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    owner_id uuid NOT NULL,
    token text NOT NULL,
    consent boolean DEFAULT false NOT NULL,
    status text DEFAULT 'new'::text NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    converted_patient_id uuid,
    rejected_reason text,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    cpf text,
    rg text,
    cep text,
    nome_completo text,
    email_principal text,
    telefone text,
    pais text,
    cidade text,
    estado text,
    endereco text,
    numero text,
    bairro text,
    complemento text,
    data_nascimento date,
    naturalidade text,
    genero text,
    estado_civil text,
    onde_nos_conheceu text,
    encaminhado_por text,
    observacoes text,
    notas_internas text,
    email_alternativo text,
    telefone_alternativo text,
    profissao text,
    escolaridade text,
    nacionalidade text,
    avatar_url text,
    tenant_id uuid,
    CONSTRAINT chk_intakes_status CHECK ((status = ANY (ARRAY['new'::text, 'converted'::text, 'rejected'::text])))
);



CREATE TABLE public.patient_invites (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    owner_id uuid NOT NULL,
    token text NOT NULL,
    active boolean DEFAULT true NOT NULL,
    expires_at timestamp with time zone,
    max_uses integer,
    uses integer DEFAULT 0 NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    tenant_id uuid
);



CREATE TABLE public.patient_discounts (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    owner_id uuid NOT NULL,
    tenant_id uuid NOT NULL,
    patient_id uuid NOT NULL,
    discount_pct numeric(5,2) DEFAULT 0,
    discount_flat numeric(10,2) DEFAULT 0,
    reason text,
    active boolean DEFAULT true NOT NULL,
    active_from timestamp with time zone DEFAULT now(),
    active_to timestamp with time zone,
    created_at timestamp with time zone DEFAULT now()
);