372 lines
13 KiB
SQL
372 lines
13 KiB
SQL
-- =============================================================================
|
|
-- AgenciaPsi — Tables — Plans, Billing, Subscriptions
|
|
-- =============================================================================
|
|
-- plans, plan_prices, plan_features, plan_public, plan_public_bullets,
|
|
-- features, entitlements_invalidation, subscriptions, subscription_events,
|
|
-- subscription_intents_personal, subscription_intents_tenant,
|
|
-- subscription_intents_legacy, billing_contracts,
|
|
-- addon_credits, addon_products, addon_transactions,
|
|
-- modules, module_features, tenant_modules
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE public.plans (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
key text NOT NULL,
|
|
name text NOT NULL,
|
|
description text,
|
|
is_active boolean DEFAULT true NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
price_cents integer DEFAULT 0 NOT NULL,
|
|
currency text DEFAULT 'BRL'::text NOT NULL,
|
|
billing_interval text DEFAULT 'month'::text NOT NULL,
|
|
target text,
|
|
max_supervisees integer,
|
|
CONSTRAINT plans_target_check CHECK ((target = ANY (ARRAY['patient'::text, 'therapist'::text, 'clinic'::text, 'supervisor'::text])))
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE public.plan_prices (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
plan_id uuid NOT NULL,
|
|
currency text DEFAULT 'BRL'::text NOT NULL,
|
|
"interval" text NOT NULL,
|
|
amount_cents integer NOT NULL,
|
|
is_active boolean DEFAULT true NOT NULL,
|
|
active_from timestamp with time zone DEFAULT now() NOT NULL,
|
|
active_to timestamp with time zone,
|
|
source text DEFAULT 'manual'::text NOT NULL,
|
|
provider text,
|
|
provider_price_id text,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT plan_prices_amount_cents_check CHECK ((amount_cents >= 0)),
|
|
CONSTRAINT plan_prices_interval_check CHECK (("interval" = ANY (ARRAY['month'::text, 'year'::text])))
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE public.plan_features (
|
|
plan_id uuid NOT NULL,
|
|
feature_id uuid NOT NULL,
|
|
enabled boolean DEFAULT true NOT NULL,
|
|
limits jsonb,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE public.plan_public (
|
|
plan_id uuid NOT NULL,
|
|
public_name text DEFAULT ''::text NOT NULL,
|
|
public_description text DEFAULT ''::text NOT NULL,
|
|
badge text,
|
|
is_featured boolean DEFAULT false NOT NULL,
|
|
is_visible boolean DEFAULT true NOT NULL,
|
|
sort_order integer DEFAULT 0 NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE public.plan_public_bullets (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
plan_id uuid NOT NULL,
|
|
text text NOT NULL,
|
|
sort_order integer DEFAULT 0 NOT NULL,
|
|
highlight boolean DEFAULT false NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE public.features (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
key text NOT NULL,
|
|
description text,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
descricao text DEFAULT ''::text NOT NULL,
|
|
name text DEFAULT ''::text NOT NULL
|
|
);
|
|
|
|
|
|
ALTER TABLE public.features OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: COLUMN features.descricao; Type: COMMENT; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
COMMENT ON COLUMN public.features.descricao IS 'Descrição humana da feature (exibição no admin e documentação).';
|
|
|
|
|
|
--
|
|
-- Name: feriados; Type: TABLE; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
|
|
CREATE TABLE public.entitlements_invalidation (
|
|
owner_id uuid NOT NULL,
|
|
changed_at timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE public.subscriptions (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
user_id uuid,
|
|
plan_id uuid NOT NULL,
|
|
status text DEFAULT 'active'::text NOT NULL,
|
|
current_period_start timestamp with time zone,
|
|
current_period_end timestamp with time zone,
|
|
cancel_at_period_end boolean DEFAULT false NOT NULL,
|
|
provider text DEFAULT 'manual'::text NOT NULL,
|
|
provider_customer_id text,
|
|
provider_subscription_id text,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
tenant_id uuid,
|
|
plan_key text,
|
|
"interval" text,
|
|
source text DEFAULT 'manual'::text NOT NULL,
|
|
started_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
canceled_at timestamp with time zone,
|
|
activated_at timestamp with time zone,
|
|
past_due_since timestamp with time zone,
|
|
suspended_at timestamp with time zone,
|
|
suspended_reason text,
|
|
cancelled_at timestamp with time zone,
|
|
cancel_reason text,
|
|
expired_at timestamp with time zone,
|
|
CONSTRAINT subscriptions_interval_check CHECK (("interval" = ANY (ARRAY['month'::text, 'year'::text]))),
|
|
CONSTRAINT subscriptions_owner_xor CHECK ((((tenant_id IS NOT NULL) AND (user_id IS NULL)) OR ((tenant_id IS NULL) AND (user_id IS NOT NULL)))),
|
|
CONSTRAINT subscriptions_status_check CHECK ((status = ANY (ARRAY['pending'::text, 'active'::text, 'past_due'::text, 'suspended'::text, 'cancelled'::text, 'expired'::text])))
|
|
);
|
|
|
|
CREATE TABLE public.subscription_events (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
subscription_id uuid NOT NULL,
|
|
owner_id uuid NOT NULL,
|
|
event_type text NOT NULL,
|
|
old_plan_id uuid,
|
|
new_plan_id uuid,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
created_by uuid,
|
|
source text DEFAULT 'admin_ui'::text,
|
|
reason text,
|
|
metadata jsonb,
|
|
owner_type text NOT NULL,
|
|
owner_ref uuid NOT NULL,
|
|
CONSTRAINT subscription_events_owner_ref_consistency_chk CHECK ((owner_id = owner_ref)),
|
|
CONSTRAINT subscription_events_owner_type_chk CHECK (((owner_type IS NULL) OR (owner_type = ANY (ARRAY['clinic'::text, 'therapist'::text]))))
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE public.subscription_intents_personal (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
created_by_user_id uuid,
|
|
email text NOT NULL,
|
|
plan_id uuid NOT NULL,
|
|
plan_key text,
|
|
"interval" text,
|
|
amount_cents integer,
|
|
currency text,
|
|
status text DEFAULT 'new'::text NOT NULL,
|
|
source text DEFAULT 'manual'::text NOT NULL,
|
|
notes text,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
paid_at timestamp with time zone,
|
|
subscription_id uuid,
|
|
CONSTRAINT sint_personal_interval_check CHECK ((("interval" IS NULL) OR ("interval" = ANY (ARRAY['month'::text, 'year'::text])))),
|
|
CONSTRAINT sint_personal_status_check CHECK ((status = ANY (ARRAY['new'::text, 'waiting_payment'::text, 'paid'::text, 'canceled'::text])))
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE public.subscription_intents_tenant (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
created_by_user_id uuid,
|
|
email text NOT NULL,
|
|
plan_id uuid NOT NULL,
|
|
plan_key text,
|
|
"interval" text,
|
|
amount_cents integer,
|
|
currency text,
|
|
status text DEFAULT 'new'::text NOT NULL,
|
|
source text DEFAULT 'manual'::text NOT NULL,
|
|
notes text,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
paid_at timestamp with time zone,
|
|
tenant_id uuid NOT NULL,
|
|
subscription_id uuid,
|
|
CONSTRAINT sint_tenant_interval_check CHECK ((("interval" IS NULL) OR ("interval" = ANY (ARRAY['month'::text, 'year'::text])))),
|
|
CONSTRAINT sint_tenant_status_check CHECK ((status = ANY (ARRAY['new'::text, 'waiting_payment'::text, 'paid'::text, 'canceled'::text])))
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE public.subscription_intents_legacy (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
user_id uuid,
|
|
email text,
|
|
plan_key text NOT NULL,
|
|
"interval" text NOT NULL,
|
|
amount_cents integer NOT NULL,
|
|
currency text DEFAULT 'BRL'::text NOT NULL,
|
|
status text DEFAULT 'new'::text NOT NULL,
|
|
source text DEFAULT 'landing'::text NOT NULL,
|
|
notes text,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
paid_at timestamp with time zone,
|
|
tenant_id uuid NOT NULL,
|
|
created_by_user_id uuid,
|
|
CONSTRAINT subscription_intents_interval_check CHECK (("interval" = ANY (ARRAY['month'::text, 'year'::text]))),
|
|
CONSTRAINT subscription_intents_status_check CHECK ((status = ANY (ARRAY['new'::text, 'waiting_payment'::text, 'paid'::text, 'canceled'::text])))
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE public.billing_contracts (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
owner_id uuid NOT NULL,
|
|
tenant_id uuid NOT NULL,
|
|
patient_id uuid NOT NULL,
|
|
type text NOT NULL,
|
|
total_sessions integer,
|
|
sessions_used integer DEFAULT 0,
|
|
package_price numeric(10,2),
|
|
amount numeric(10,2),
|
|
billing_interval text,
|
|
active_from timestamp with time zone DEFAULT now(),
|
|
active_to timestamp with time zone,
|
|
status text DEFAULT 'active'::text NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT billing_contracts_interval_chk CHECK (((billing_interval IS NULL) OR (billing_interval = ANY (ARRAY['monthly'::text, 'weekly'::text])))),
|
|
CONSTRAINT billing_contracts_sess_used_chk CHECK (((sessions_used IS NULL) OR (sessions_used >= 0))),
|
|
CONSTRAINT billing_contracts_status_chk CHECK ((status = ANY (ARRAY['active'::text, 'completed'::text, 'cancelled'::text]))),
|
|
CONSTRAINT billing_contracts_total_sess_chk CHECK (((total_sessions IS NULL) OR (total_sessions > 0))),
|
|
CONSTRAINT billing_contracts_type_chk CHECK ((type = ANY (ARRAY['per_session'::text, 'package'::text, 'subscription'::text])))
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE public.addon_credits (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
tenant_id uuid NOT NULL,
|
|
owner_id uuid,
|
|
addon_type text NOT NULL,
|
|
balance integer DEFAULT 0 NOT NULL,
|
|
total_purchased integer DEFAULT 0 NOT NULL,
|
|
total_consumed integer DEFAULT 0 NOT NULL,
|
|
low_balance_threshold integer DEFAULT 10,
|
|
low_balance_notified boolean DEFAULT false,
|
|
daily_limit integer,
|
|
hourly_limit integer,
|
|
daily_used integer DEFAULT 0,
|
|
hourly_used integer DEFAULT 0,
|
|
daily_reset_at timestamp with time zone,
|
|
hourly_reset_at timestamp with time zone,
|
|
from_number_override text,
|
|
expires_at timestamp with time zone,
|
|
is_active boolean DEFAULT true,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now()
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE public.addon_products (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
slug text NOT NULL,
|
|
name text NOT NULL,
|
|
description text,
|
|
addon_type text NOT NULL,
|
|
icon text DEFAULT 'pi pi-box'::text,
|
|
credits_amount integer DEFAULT 0,
|
|
price_cents integer DEFAULT 0 NOT NULL,
|
|
currency text DEFAULT 'BRL'::text,
|
|
is_active boolean DEFAULT true,
|
|
is_visible boolean DEFAULT true,
|
|
sort_order integer DEFAULT 0,
|
|
metadata jsonb DEFAULT '{}'::jsonb,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
deleted_at timestamp with time zone
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE public.addon_transactions (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
tenant_id uuid NOT NULL,
|
|
owner_id uuid,
|
|
addon_type text NOT NULL,
|
|
type text NOT NULL,
|
|
amount integer NOT NULL,
|
|
balance_before integer DEFAULT 0 NOT NULL,
|
|
balance_after integer DEFAULT 0 NOT NULL,
|
|
product_id uuid,
|
|
queue_id uuid,
|
|
description text,
|
|
admin_user_id uuid,
|
|
payment_method text,
|
|
payment_reference text,
|
|
price_cents integer,
|
|
currency text DEFAULT 'BRL'::text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
metadata jsonb DEFAULT '{}'::jsonb
|
|
);
|
|
|
|
|
|
ALTER TABLE public.addon_transactions OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: TABLE addon_transactions; Type: COMMENT; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
COMMENT ON TABLE public.addon_transactions IS 'Histórico de todas as transações de créditos: compras, consumo, ajustes, reembolsos.';
|
|
|
|
|
|
--
|
|
-- Name: agenda_bloqueios; Type: TABLE; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
|
|
CREATE TABLE public.modules (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
key text NOT NULL,
|
|
name text NOT NULL,
|
|
description text,
|
|
is_active boolean DEFAULT true NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE public.module_features (
|
|
module_id uuid NOT NULL,
|
|
feature_id uuid NOT NULL,
|
|
enabled boolean DEFAULT true NOT NULL,
|
|
limits jsonb,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE public.tenant_modules (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
owner_id uuid NOT NULL,
|
|
module_id uuid NOT NULL,
|
|
status text DEFAULT 'active'::text NOT NULL,
|
|
settings jsonb,
|
|
provider text DEFAULT 'manual'::text NOT NULL,
|
|
provider_item_id text,
|
|
installed_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
|
|
ALTER TABLE public.tenant_modules OWNER TO supabase_admin;
|