597 lines
20 KiB
SQL
597 lines
20 KiB
SQL
-- =============================================================================
|
|
-- AgenciaPsi — Views
|
|
-- =============================================================================
|
|
-- current_tenant_id, owner_feature_entitlements, subscription_intents,
|
|
-- v_auth_users_public, v_cashflow_projection, v_commitment_totals,
|
|
-- v_patient_groups_with_counts, v_plan_active_prices, v_public_pricing,
|
|
-- v_subscription_feature_mismatch, v_subscription_health, v_subscription_health_v2,
|
|
-- v_tag_patient_counts, v_tenant_active_subscription, v_tenant_entitlements,
|
|
-- v_tenant_entitlements_full, v_tenant_entitlements_json,
|
|
-- v_tenant_feature_exceptions, v_tenant_feature_mismatch,
|
|
-- v_tenant_members_with_profiles, v_tenant_people, v_tenant_staff,
|
|
-- v_user_active_subscription, v_user_entitlements
|
|
-- =============================================================================
|
|
|
|
CREATE VIEW public.current_tenant_id AS
|
|
SELECT current_setting('request.jwt.claim.tenant_id'::text, true) AS current_setting;
|
|
|
|
|
|
ALTER VIEW public.current_tenant_id OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: determined_commitment_fields; Type: TABLE; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
|
|
CREATE VIEW public.owner_feature_entitlements AS
|
|
WITH base AS (
|
|
SELECT s.user_id AS owner_id,
|
|
f.key AS feature_key,
|
|
pf.limits,
|
|
'plan'::text AS source
|
|
FROM ((public.subscriptions s
|
|
JOIN public.plan_features pf ON (((pf.plan_id = s.plan_id) AND (pf.enabled = true))))
|
|
JOIN public.features f ON ((f.id = pf.feature_id)))
|
|
WHERE ((s.status = 'active'::text) AND (s.user_id IS NOT NULL))
|
|
UNION ALL
|
|
SELECT tm.owner_id,
|
|
f.key AS feature_key,
|
|
mf.limits,
|
|
'module'::text AS source
|
|
FROM (((public.tenant_modules tm
|
|
JOIN public.modules m ON (((m.id = tm.module_id) AND (m.is_active = true))))
|
|
JOIN public.module_features mf ON (((mf.module_id = m.id) AND (mf.enabled = true))))
|
|
JOIN public.features f ON ((f.id = mf.feature_id)))
|
|
WHERE ((tm.status = 'active'::text) AND (tm.owner_id IS NOT NULL))
|
|
)
|
|
SELECT owner_id,
|
|
feature_key,
|
|
array_agg(DISTINCT source) AS sources,
|
|
jsonb_agg(limits) FILTER (WHERE (limits IS NOT NULL)) AS limits_list
|
|
FROM base
|
|
GROUP BY owner_id, feature_key;
|
|
|
|
|
|
ALTER VIEW public.owner_feature_entitlements OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: owner_users; Type: TABLE; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
|
|
CREATE VIEW public.subscription_intents AS
|
|
SELECT t.id,
|
|
t.user_id,
|
|
t.created_by_user_id,
|
|
t.email,
|
|
t.plan_id,
|
|
t.plan_key,
|
|
t."interval",
|
|
t.amount_cents,
|
|
t.currency,
|
|
t.status,
|
|
t.source,
|
|
t.notes,
|
|
t.created_at,
|
|
t.paid_at,
|
|
t.tenant_id,
|
|
t.subscription_id,
|
|
'clinic'::text AS plan_target
|
|
FROM public.subscription_intents_tenant t
|
|
UNION ALL
|
|
SELECT p.id,
|
|
p.user_id,
|
|
p.created_by_user_id,
|
|
p.email,
|
|
p.plan_id,
|
|
p.plan_key,
|
|
p."interval",
|
|
p.amount_cents,
|
|
p.currency,
|
|
p.status,
|
|
p.source,
|
|
p.notes,
|
|
p.created_at,
|
|
p.paid_at,
|
|
NULL::uuid AS tenant_id,
|
|
p.subscription_id,
|
|
'therapist'::text AS plan_target
|
|
FROM public.subscription_intents_personal p;
|
|
|
|
|
|
ALTER VIEW public.subscription_intents OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: subscription_intents_legacy; Type: TABLE; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
|
|
CREATE VIEW public.v_auth_users_public AS
|
|
SELECT id AS user_id,
|
|
email,
|
|
created_at,
|
|
last_sign_in_at
|
|
FROM auth.users u;
|
|
|
|
|
|
ALTER VIEW public.v_auth_users_public OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: v_cashflow_projection; Type: VIEW; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE VIEW public.v_cashflow_projection WITH (security_invoker='on') AS
|
|
SELECT gs.mes,
|
|
to_char(gs.mes, 'YYYY-MM'::text) AS mes_label,
|
|
COALESCE(sum(fr.final_amount) FILTER (WHERE ((fr.type = 'receita'::public.financial_record_type) AND (fr.status = ANY (ARRAY['pending'::text, 'overdue'::text])))), (0)::numeric) AS receitas_projetadas,
|
|
COALESCE(sum(fr.final_amount) FILTER (WHERE ((fr.type = 'despesa'::public.financial_record_type) AND (fr.status = ANY (ARRAY['pending'::text, 'overdue'::text])))), (0)::numeric) AS despesas_projetadas,
|
|
COALESCE(sum(fr.final_amount) FILTER (WHERE ((fr.type = 'receita'::public.financial_record_type) AND (fr.status = 'pending'::text))), (0)::numeric) AS receitas_pendentes,
|
|
COALESCE(sum(fr.final_amount) FILTER (WHERE ((fr.type = 'receita'::public.financial_record_type) AND (fr.status = 'overdue'::text))), (0)::numeric) AS receitas_vencidas,
|
|
COALESCE(sum(fr.final_amount) FILTER (WHERE ((fr.type = 'despesa'::public.financial_record_type) AND (fr.status = 'pending'::text))), (0)::numeric) AS despesas_pendentes,
|
|
COALESCE(sum(fr.final_amount) FILTER (WHERE ((fr.type = 'despesa'::public.financial_record_type) AND (fr.status = 'overdue'::text))), (0)::numeric) AS despesas_vencidas,
|
|
(COALESCE(sum(fr.final_amount) FILTER (WHERE ((fr.type = 'receita'::public.financial_record_type) AND (fr.status = ANY (ARRAY['pending'::text, 'overdue'::text])))), (0)::numeric) - COALESCE(sum(fr.final_amount) FILTER (WHERE ((fr.type = 'despesa'::public.financial_record_type) AND (fr.status = ANY (ARRAY['pending'::text, 'overdue'::text])))), (0)::numeric)) AS saldo_projetado,
|
|
count(fr.id) FILTER (WHERE (fr.status = ANY (ARRAY['pending'::text, 'overdue'::text]))) AS count_registros
|
|
FROM (generate_series(((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time zone))::date)::timestamp with time zone, (((date_trunc('month'::text, (CURRENT_DATE)::timestamp with time zone) + '5 mons'::interval))::date)::timestamp with time zone, '1 mon'::interval) gs(mes)
|
|
LEFT JOIN public.financial_records fr ON (((fr.deleted_at IS NULL) AND (fr.status = ANY (ARRAY['pending'::text, 'overdue'::text])) AND ((date_trunc('month'::text, (fr.due_date)::timestamp with time zone))::date = gs.mes))))
|
|
GROUP BY gs.mes
|
|
ORDER BY gs.mes;
|
|
|
|
|
|
ALTER VIEW public.v_cashflow_projection OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: VIEW v_cashflow_projection; Type: COMMENT; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
COMMENT ON VIEW public.v_cashflow_projection IS 'Fluxo de caixa projetado: próximos 6 meses com totais de pending+overdue por due_date. Usa security_invoker=on — filtra automaticamente pelo auth.uid() via RLS de financial_records.';
|
|
|
|
|
|
--
|
|
-- Name: v_commitment_totals; Type: VIEW; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE VIEW public.v_commitment_totals AS
|
|
SELECT c.tenant_id,
|
|
c.id AS commitment_id,
|
|
(COALESCE(sum(l.minutes), (0)::bigint))::integer AS total_minutes
|
|
FROM (public.determined_commitments c
|
|
LEFT JOIN public.commitment_time_logs l ON ((l.commitment_id = c.id)))
|
|
GROUP BY c.tenant_id, c.id;
|
|
|
|
|
|
ALTER VIEW public.v_commitment_totals OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: v_patient_groups_with_counts; Type: VIEW; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE VIEW public.v_patient_groups_with_counts AS
|
|
SELECT pg.id,
|
|
pg.nome,
|
|
pg.cor,
|
|
pg.owner_id,
|
|
pg.is_system,
|
|
pg.is_active,
|
|
pg.created_at,
|
|
pg.updated_at,
|
|
(COALESCE(count(pgp.patient_id), (0)::bigint))::integer AS patients_count
|
|
FROM (public.patient_groups pg
|
|
LEFT JOIN public.patient_group_patient pgp ON ((pgp.patient_group_id = pg.id)))
|
|
GROUP BY pg.id, pg.nome, pg.cor, pg.owner_id, pg.is_system, pg.is_active, pg.created_at, pg.updated_at;
|
|
|
|
|
|
ALTER VIEW public.v_patient_groups_with_counts OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: v_plan_active_prices; Type: VIEW; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE VIEW public.v_plan_active_prices AS
|
|
SELECT plan_id,
|
|
max(
|
|
CASE
|
|
WHEN (("interval" = 'month'::text) AND is_active) THEN amount_cents
|
|
ELSE NULL::integer
|
|
END) AS monthly_cents,
|
|
max(
|
|
CASE
|
|
WHEN (("interval" = 'year'::text) AND is_active) THEN amount_cents
|
|
ELSE NULL::integer
|
|
END) AS yearly_cents,
|
|
max(
|
|
CASE
|
|
WHEN (("interval" = 'month'::text) AND is_active) THEN currency
|
|
ELSE NULL::text
|
|
END) AS monthly_currency,
|
|
max(
|
|
CASE
|
|
WHEN (("interval" = 'year'::text) AND is_active) THEN currency
|
|
ELSE NULL::text
|
|
END) AS yearly_currency
|
|
FROM public.plan_prices
|
|
GROUP BY plan_id;
|
|
|
|
|
|
ALTER VIEW public.v_plan_active_prices OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: v_public_pricing; Type: VIEW; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE VIEW public.v_public_pricing AS
|
|
SELECT p.id AS plan_id,
|
|
p.key AS plan_key,
|
|
p.name AS plan_name,
|
|
COALESCE(pp.public_name, ''::text) AS public_name,
|
|
COALESCE(pp.public_description, ''::text) AS public_description,
|
|
pp.badge,
|
|
COALESCE(pp.is_featured, false) AS is_featured,
|
|
COALESCE(pp.is_visible, true) AS is_visible,
|
|
COALESCE(pp.sort_order, 0) AS sort_order,
|
|
ap.monthly_cents,
|
|
ap.yearly_cents,
|
|
ap.monthly_currency,
|
|
ap.yearly_currency,
|
|
COALESCE(( SELECT jsonb_agg(jsonb_build_object('id', b.id, 'text', b.text, 'highlight', b.highlight, 'sort_order', b.sort_order) ORDER BY b.sort_order, b.created_at) AS jsonb_agg
|
|
FROM public.plan_public_bullets b
|
|
WHERE (b.plan_id = p.id)), '[]'::jsonb) AS bullets,
|
|
p.target AS plan_target
|
|
FROM ((public.plans p
|
|
LEFT JOIN public.plan_public pp ON ((pp.plan_id = p.id)))
|
|
LEFT JOIN public.v_plan_active_prices ap ON ((ap.plan_id = p.id)))
|
|
ORDER BY COALESCE(pp.sort_order, 0), p.key;
|
|
|
|
|
|
ALTER VIEW public.v_public_pricing OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: v_subscription_feature_mismatch; Type: VIEW; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE VIEW public.v_subscription_feature_mismatch AS
|
|
WITH expected AS (
|
|
SELECT s.user_id AS owner_id,
|
|
f.key AS feature_key
|
|
FROM ((public.subscriptions s
|
|
JOIN public.plan_features pf ON (((pf.plan_id = s.plan_id) AND (pf.enabled = true))))
|
|
JOIN public.features f ON ((f.id = pf.feature_id)))
|
|
WHERE ((s.status = 'active'::text) AND (s.tenant_id IS NULL) AND (s.user_id IS NOT NULL))
|
|
), actual AS (
|
|
SELECT e.owner_id,
|
|
e.feature_key
|
|
FROM public.owner_feature_entitlements e
|
|
)
|
|
SELECT COALESCE(expected.owner_id, actual.owner_id) AS owner_id,
|
|
COALESCE(expected.feature_key, actual.feature_key) AS feature_key,
|
|
CASE
|
|
WHEN ((expected.feature_key IS NOT NULL) AND (actual.feature_key IS NULL)) THEN 'missing_entitlement'::text
|
|
WHEN ((expected.feature_key IS NULL) AND (actual.feature_key IS NOT NULL)) THEN 'unexpected_entitlement'::text
|
|
ELSE NULL::text
|
|
END AS mismatch_type
|
|
FROM (expected
|
|
FULL JOIN actual ON (((expected.owner_id = actual.owner_id) AND (expected.feature_key = actual.feature_key))))
|
|
WHERE ((expected.feature_key IS NULL) OR (actual.feature_key IS NULL));
|
|
|
|
|
|
ALTER VIEW public.v_subscription_feature_mismatch OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: v_subscription_health; Type: VIEW; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE VIEW public.v_subscription_health AS
|
|
SELECT s.id AS subscription_id,
|
|
s.user_id AS owner_id,
|
|
s.status,
|
|
s.plan_id,
|
|
p.key AS plan_key,
|
|
s.current_period_start,
|
|
s.current_period_end,
|
|
s.updated_at,
|
|
CASE
|
|
WHEN (s.plan_id IS NULL) THEN 'missing_plan'::text
|
|
WHEN (p.id IS NULL) THEN 'invalid_plan'::text
|
|
WHEN ((s.status = 'active'::text) AND (s.current_period_end IS NOT NULL) AND (s.current_period_end < now())) THEN 'expired_but_active'::text
|
|
WHEN ((s.status = 'canceled'::text) AND (s.current_period_end > now())) THEN 'canceled_but_still_in_period'::text
|
|
ELSE 'ok'::text
|
|
END AS health_status,
|
|
CASE
|
|
WHEN (s.tenant_id IS NOT NULL) THEN 'clinic'::text
|
|
ELSE 'therapist'::text
|
|
END AS owner_type,
|
|
COALESCE(s.tenant_id, s.user_id) AS owner_ref
|
|
FROM (public.subscriptions s
|
|
LEFT JOIN public.plans p ON ((p.id = s.plan_id)));
|
|
|
|
|
|
ALTER VIEW public.v_subscription_health OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: v_subscription_health_v2; Type: VIEW; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE VIEW public.v_subscription_health_v2 AS
|
|
SELECT s.id AS subscription_id,
|
|
s.user_id AS owner_id,
|
|
CASE
|
|
WHEN (s.tenant_id IS NOT NULL) THEN 'clinic'::text
|
|
ELSE 'therapist'::text
|
|
END AS owner_type,
|
|
COALESCE(s.tenant_id, s.user_id) AS owner_ref,
|
|
s.status,
|
|
s.plan_id,
|
|
p.key AS plan_key,
|
|
s.current_period_start,
|
|
s.current_period_end,
|
|
s.updated_at,
|
|
CASE
|
|
WHEN (s.plan_id IS NULL) THEN 'missing_plan'::text
|
|
WHEN (p.id IS NULL) THEN 'invalid_plan'::text
|
|
WHEN ((s.status = 'active'::text) AND (s.current_period_end IS NOT NULL) AND (s.current_period_end < now())) THEN 'expired_but_active'::text
|
|
WHEN ((s.status = 'canceled'::text) AND (s.current_period_end > now())) THEN 'canceled_but_still_in_period'::text
|
|
ELSE 'ok'::text
|
|
END AS health_status
|
|
FROM (public.subscriptions s
|
|
LEFT JOIN public.plans p ON ((p.id = s.plan_id)));
|
|
|
|
|
|
ALTER VIEW public.v_subscription_health_v2 OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: v_tag_patient_counts; Type: VIEW; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE VIEW public.v_tag_patient_counts AS
|
|
SELECT t.id,
|
|
t.owner_id,
|
|
t.nome,
|
|
t.cor,
|
|
t.is_padrao,
|
|
t.created_at,
|
|
t.updated_at,
|
|
(COALESCE(count(ppt.patient_id), (0)::bigint))::integer AS pacientes_count,
|
|
(COALESCE(count(ppt.patient_id), (0)::bigint))::integer AS patient_count
|
|
FROM (public.patient_tags t
|
|
LEFT JOIN public.patient_patient_tag ppt ON (((ppt.tag_id = t.id) AND (ppt.owner_id = t.owner_id))))
|
|
GROUP BY t.id, t.owner_id, t.nome, t.cor, t.is_padrao, t.created_at, t.updated_at;
|
|
|
|
|
|
ALTER VIEW public.v_tag_patient_counts OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: v_tenant_active_subscription; Type: VIEW; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE VIEW public.v_tenant_active_subscription AS
|
|
SELECT DISTINCT ON (tenant_id) tenant_id,
|
|
plan_id,
|
|
plan_key,
|
|
"interval",
|
|
status,
|
|
current_period_start,
|
|
current_period_end,
|
|
created_at
|
|
FROM public.subscriptions s
|
|
WHERE ((tenant_id IS NOT NULL) AND (status = 'active'::text) AND ((current_period_end IS NULL) OR (current_period_end > now())))
|
|
ORDER BY tenant_id, created_at DESC;
|
|
|
|
|
|
ALTER VIEW public.v_tenant_active_subscription OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: v_tenant_entitlements; Type: VIEW; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE VIEW public.v_tenant_entitlements AS
|
|
SELECT a.tenant_id,
|
|
f.key AS feature_key,
|
|
true AS allowed
|
|
FROM ((public.v_tenant_active_subscription a
|
|
JOIN public.plan_features pf ON (((pf.plan_id = a.plan_id) AND (pf.enabled = true))))
|
|
JOIN public.features f ON ((f.id = pf.feature_id)));
|
|
|
|
|
|
ALTER VIEW public.v_tenant_entitlements OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: v_tenant_entitlements_full; Type: VIEW; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE VIEW public.v_tenant_entitlements_full AS
|
|
SELECT a.tenant_id,
|
|
f.key AS feature_key,
|
|
(pf.enabled = true) AS allowed,
|
|
pf.limits,
|
|
a.plan_id,
|
|
p.key AS plan_key
|
|
FROM (((public.v_tenant_active_subscription a
|
|
JOIN public.plan_features pf ON ((pf.plan_id = a.plan_id)))
|
|
JOIN public.features f ON ((f.id = pf.feature_id)))
|
|
JOIN public.plans p ON ((p.id = a.plan_id)));
|
|
|
|
|
|
ALTER VIEW public.v_tenant_entitlements_full OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: v_tenant_entitlements_json; Type: VIEW; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE VIEW public.v_tenant_entitlements_json AS
|
|
SELECT tenant_id,
|
|
max(plan_key) AS plan_key,
|
|
jsonb_object_agg(feature_key, jsonb_build_object('allowed', allowed, 'limits', COALESCE(limits, '{}'::jsonb)) ORDER BY feature_key) AS entitlements
|
|
FROM public.v_tenant_entitlements_full
|
|
GROUP BY tenant_id;
|
|
|
|
|
|
ALTER VIEW public.v_tenant_entitlements_json OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: v_tenant_feature_exceptions; Type: VIEW; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE VIEW public.v_tenant_feature_exceptions AS
|
|
SELECT tf.tenant_id,
|
|
a.plan_key,
|
|
tf.feature_key,
|
|
'commercial_exception'::text AS exception_type
|
|
FROM ((public.tenant_features tf
|
|
JOIN public.v_tenant_active_subscription a ON ((a.tenant_id = tf.tenant_id)))
|
|
LEFT JOIN public.v_tenant_entitlements_full v ON (((v.tenant_id = tf.tenant_id) AND (v.feature_key = tf.feature_key))))
|
|
WHERE ((tf.enabled = true) AND (COALESCE(v.allowed, false) = false));
|
|
|
|
|
|
ALTER VIEW public.v_tenant_feature_exceptions OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: v_tenant_feature_mismatch; Type: VIEW; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE VIEW public.v_tenant_feature_mismatch AS
|
|
WITH plan_allowed AS (
|
|
SELECT v.tenant_id,
|
|
v.feature_key,
|
|
v.allowed
|
|
FROM public.v_tenant_entitlements_full v
|
|
), overrides AS (
|
|
SELECT tf.tenant_id,
|
|
tf.feature_key,
|
|
tf.enabled
|
|
FROM public.tenant_features tf
|
|
)
|
|
SELECT o.tenant_id,
|
|
o.feature_key,
|
|
CASE
|
|
WHEN ((o.enabled = true) AND (COALESCE(p.allowed, false) = false)) THEN 'unexpected_override'::text
|
|
ELSE NULL::text
|
|
END AS mismatch_type
|
|
FROM (overrides o
|
|
LEFT JOIN plan_allowed p ON (((p.tenant_id = o.tenant_id) AND (p.feature_key = o.feature_key))))
|
|
WHERE ((o.enabled = true) AND (COALESCE(p.allowed, false) = false));
|
|
|
|
|
|
ALTER VIEW public.v_tenant_feature_mismatch OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: v_tenant_members_with_profiles; Type: VIEW; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE VIEW public.v_tenant_members_with_profiles AS
|
|
SELECT tm.id AS tenant_member_id,
|
|
tm.tenant_id,
|
|
tm.user_id,
|
|
tm.role,
|
|
tm.status,
|
|
tm.created_at,
|
|
p.full_name,
|
|
au.email
|
|
FROM ((public.tenant_members tm
|
|
LEFT JOIN public.profiles p ON ((p.id = tm.user_id)))
|
|
LEFT JOIN auth.users au ON ((au.id = tm.user_id)));
|
|
|
|
|
|
ALTER VIEW public.v_tenant_members_with_profiles OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: v_tenant_people; Type: VIEW; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE VIEW public.v_tenant_people AS
|
|
SELECT 'member'::text AS type,
|
|
m.tenant_id,
|
|
m.user_id,
|
|
u.email,
|
|
m.role,
|
|
m.status,
|
|
NULL::uuid AS invite_token,
|
|
NULL::timestamp with time zone AS expires_at
|
|
FROM (public.tenant_members m
|
|
JOIN auth.users u ON ((u.id = m.user_id)))
|
|
UNION ALL
|
|
SELECT 'invite'::text AS type,
|
|
i.tenant_id,
|
|
NULL::uuid AS user_id,
|
|
i.email,
|
|
i.role,
|
|
'invited'::text AS status,
|
|
i.token AS invite_token,
|
|
i.expires_at
|
|
FROM public.tenant_invites i
|
|
WHERE ((i.accepted_at IS NULL) AND (i.revoked_at IS NULL));
|
|
|
|
|
|
ALTER VIEW public.v_tenant_people OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: v_tenant_staff; Type: VIEW; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE VIEW public.v_tenant_staff AS
|
|
SELECT ('m_'::text || (tm.id)::text) AS row_id,
|
|
tm.tenant_id,
|
|
tm.user_id,
|
|
tm.role,
|
|
tm.status,
|
|
tm.created_at,
|
|
p.full_name,
|
|
au.email,
|
|
NULL::uuid AS invite_token
|
|
FROM ((public.tenant_members tm
|
|
LEFT JOIN public.profiles p ON ((p.id = tm.user_id)))
|
|
LEFT JOIN auth.users au ON ((au.id = tm.user_id)))
|
|
UNION ALL
|
|
SELECT ('i_'::text || (ti.id)::text) AS row_id,
|
|
ti.tenant_id,
|
|
NULL::uuid AS user_id,
|
|
ti.role,
|
|
'invited'::text AS status,
|
|
ti.created_at,
|
|
NULL::text AS full_name,
|
|
ti.email,
|
|
ti.token AS invite_token
|
|
FROM public.tenant_invites ti
|
|
WHERE ((ti.accepted_at IS NULL) AND (ti.revoked_at IS NULL) AND (ti.expires_at > now()));
|
|
|
|
|
|
ALTER VIEW public.v_tenant_staff OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: v_user_active_subscription; Type: VIEW; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE VIEW public.v_user_active_subscription AS
|
|
SELECT DISTINCT ON (user_id) user_id,
|
|
plan_id,
|
|
plan_key,
|
|
"interval",
|
|
status,
|
|
current_period_start,
|
|
current_period_end,
|
|
created_at
|
|
FROM public.subscriptions s
|
|
WHERE ((tenant_id IS NULL) AND (user_id IS NOT NULL) AND (status = 'active'::text) AND ((current_period_end IS NULL) OR (current_period_end > now())))
|
|
ORDER BY user_id, created_at DESC;
|
|
|
|
|
|
ALTER VIEW public.v_user_active_subscription OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: v_user_entitlements; Type: VIEW; Schema: public; Owner: supabase_admin
|
|
--
|
|
|
|
CREATE VIEW public.v_user_entitlements AS
|
|
SELECT a.user_id,
|
|
f.key AS feature_key,
|
|
true AS allowed
|
|
FROM ((public.v_user_active_subscription a
|
|
JOIN public.plan_features pf ON (((pf.plan_id = a.plan_id) AND (pf.enabled = true))))
|
|
JOIN public.features f ON ((f.id = pf.feature_id)));
|
|
|
|
|
|
ALTER VIEW public.v_user_entitlements OWNER TO supabase_admin;
|
|
|
|
--
|
|
-- Name: messages; Type: TABLE; Schema: realtime; Owner: supabase_realtime_admin
|
|
--
|
|
|