-- ============================================================================= -- 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() );