200 lines
7.5 KiB
SQL
200 lines
7.5 KiB
SQL
-- =============================================================================
|
|
-- AgenciaPsi — Tables — Financeiro
|
|
-- =============================================================================
|
|
-- financial_records, financial_categories, financial_exceptions,
|
|
-- payment_settings, professional_pricing, therapist_payouts,
|
|
-- therapist_payout_records, services, insurance_plans, insurance_plan_services
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE public.financial_records (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
owner_id uuid NOT NULL,
|
|
tenant_id uuid,
|
|
type public.financial_record_type DEFAULT 'receita'::public.financial_record_type NOT NULL,
|
|
amount numeric(10,2) NOT NULL,
|
|
description text,
|
|
category text,
|
|
payment_method text,
|
|
paid_at timestamp with time zone,
|
|
due_date date,
|
|
installments smallint DEFAULT 1,
|
|
installment_number smallint DEFAULT 1,
|
|
installment_group uuid,
|
|
agenda_evento_id uuid,
|
|
patient_id uuid,
|
|
clinic_fee_pct numeric(5,2) DEFAULT 0,
|
|
clinic_fee_amount numeric(10,2) DEFAULT 0,
|
|
net_amount numeric(10,2) GENERATED ALWAYS AS ((amount - clinic_fee_amount)) STORED,
|
|
insurance_plan_id uuid,
|
|
notes text,
|
|
tags text[],
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
deleted_at timestamp with time zone,
|
|
discount_amount numeric(10,2) DEFAULT 0 NOT NULL,
|
|
final_amount numeric(10,2) DEFAULT 0 NOT NULL,
|
|
status text DEFAULT 'pending'::text NOT NULL,
|
|
category_id uuid,
|
|
CONSTRAINT financial_records_amount_check CHECK ((amount >= (0)::numeric)),
|
|
CONSTRAINT financial_records_clinic_fee_amount_check CHECK ((clinic_fee_amount >= (0)::numeric)),
|
|
CONSTRAINT financial_records_clinic_fee_pct_check CHECK (((clinic_fee_pct >= (0)::numeric) AND (clinic_fee_pct <= (100)::numeric))),
|
|
CONSTRAINT financial_records_discount_amount_check CHECK ((discount_amount >= (0)::numeric)),
|
|
CONSTRAINT financial_records_final_amount_check CHECK ((final_amount >= (0)::numeric)),
|
|
CONSTRAINT financial_records_installments_check CHECK ((installments >= 1)),
|
|
CONSTRAINT financial_records_status_check CHECK ((status = ANY (ARRAY['pending'::text, 'paid'::text, 'partial'::text, 'overdue'::text, 'cancelled'::text, 'refunded'::text])))
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE public.financial_categories (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
name text NOT NULL,
|
|
type public.financial_record_type DEFAULT 'receita'::public.financial_record_type NOT NULL,
|
|
color text DEFAULT '#6366f1'::text,
|
|
icon text DEFAULT 'pi pi-tag'::text,
|
|
sort_order integer DEFAULT 0,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
|
|
ALTER TABLE public.financial_categories OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: financial_exceptions; Type: TABLE; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE TABLE public.financial_exceptions (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
owner_id uuid,
|
|
tenant_id uuid NOT NULL,
|
|
exception_type text NOT NULL,
|
|
charge_mode text NOT NULL,
|
|
charge_value numeric(10,2),
|
|
charge_pct numeric(5,2),
|
|
min_hours_notice integer,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT financial_exceptions_charge_chk CHECK ((charge_mode = ANY (ARRAY['none'::text, 'full'::text, 'fixed_fee'::text, 'percentage'::text]))),
|
|
CONSTRAINT financial_exceptions_type_chk CHECK ((exception_type = ANY (ARRAY['patient_no_show'::text, 'patient_cancellation'::text, 'professional_cancellation'::text])))
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE public.payment_settings (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
owner_id uuid NOT NULL,
|
|
tenant_id uuid,
|
|
pix_ativo boolean DEFAULT false NOT NULL,
|
|
pix_tipo text DEFAULT 'cpf'::text NOT NULL,
|
|
pix_chave text DEFAULT ''::text NOT NULL,
|
|
pix_nome_titular text DEFAULT ''::text NOT NULL,
|
|
deposito_ativo boolean DEFAULT false NOT NULL,
|
|
deposito_banco text DEFAULT ''::text NOT NULL,
|
|
deposito_agencia text DEFAULT ''::text NOT NULL,
|
|
deposito_conta text DEFAULT ''::text NOT NULL,
|
|
deposito_tipo_conta text DEFAULT 'corrente'::text NOT NULL,
|
|
deposito_titular text DEFAULT ''::text NOT NULL,
|
|
deposito_cpf_cnpj text DEFAULT ''::text NOT NULL,
|
|
dinheiro_ativo boolean DEFAULT false NOT NULL,
|
|
cartao_ativo boolean DEFAULT false NOT NULL,
|
|
cartao_instrucao text DEFAULT ''::text NOT NULL,
|
|
convenio_ativo boolean DEFAULT false NOT NULL,
|
|
convenio_lista text DEFAULT ''::text NOT NULL,
|
|
observacoes_pagamento text DEFAULT ''::text NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now()
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE public.professional_pricing (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
owner_id uuid NOT NULL,
|
|
tenant_id uuid NOT NULL,
|
|
determined_commitment_id uuid,
|
|
price numeric(10,2) NOT NULL,
|
|
notes text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now()
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE public.therapist_payouts (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
owner_id uuid NOT NULL,
|
|
tenant_id uuid NOT NULL,
|
|
period_start date NOT NULL,
|
|
period_end date NOT NULL,
|
|
total_sessions integer DEFAULT 0 NOT NULL,
|
|
gross_amount numeric(10,2) DEFAULT 0 NOT NULL,
|
|
clinic_fee_total numeric(10,2) DEFAULT 0 NOT NULL,
|
|
net_amount numeric(10,2) DEFAULT 0 NOT NULL,
|
|
status text DEFAULT 'pending'::text NOT NULL,
|
|
paid_at timestamp with time zone,
|
|
notes text,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT therapist_payouts_clinic_fee_total_check CHECK ((clinic_fee_total >= (0)::numeric)),
|
|
CONSTRAINT therapist_payouts_gross_amount_check CHECK ((gross_amount >= (0)::numeric)),
|
|
CONSTRAINT therapist_payouts_net_amount_check CHECK ((net_amount >= (0)::numeric)),
|
|
CONSTRAINT therapist_payouts_period_chk CHECK ((period_end >= period_start)),
|
|
CONSTRAINT therapist_payouts_status_check CHECK ((status = ANY (ARRAY['pending'::text, 'paid'::text, 'cancelled'::text])))
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE public.therapist_payout_records (
|
|
payout_id uuid NOT NULL,
|
|
financial_record_id uuid NOT NULL
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE public.services (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
owner_id uuid NOT NULL,
|
|
tenant_id uuid NOT NULL,
|
|
name text NOT NULL,
|
|
description text,
|
|
price numeric(10,2) NOT NULL,
|
|
duration_min integer,
|
|
active boolean DEFAULT true NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now()
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE public.insurance_plan_services (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
insurance_plan_id uuid NOT NULL,
|
|
name text NOT NULL,
|
|
value numeric(10,2) NOT NULL,
|
|
active boolean DEFAULT true NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now()
|
|
);
|
|
|
|
|
|
ALTER TABLE public.insurance_plan_services OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: insurance_plans; Type: TABLE; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE TABLE public.insurance_plans (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
owner_id uuid NOT NULL,
|
|
tenant_id uuid NOT NULL,
|
|
name text NOT NULL,
|
|
notes text,
|
|
default_value numeric(10,2),
|
|
active boolean DEFAULT true NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now()
|
|
);
|
|
|
|
|