Evandro Fonseca Junior
d27526e026
- Adds subscription tier info to access/refresh tokens. - Stores tier details in session Redis record. - Returns tier data in login/register response. - Uses default tier when user has none.
94 lines
3.8 KiB
SQL
94 lines
3.8 KiB
SQL
-- Migration 019: Subscription tiers e permissões iniciais
|
|
-- Criado em: 2025-02-07
|
|
|
|
CREATE TABLE IF NOT EXISTS subscription_tiers (
|
|
subscription_tier_id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
slug VARCHAR(50) NOT NULL UNIQUE,
|
|
description TEXT,
|
|
level INTEGER NOT NULL DEFAULT 0,
|
|
price_monthly NUMERIC(10, 2),
|
|
price_yearly NUMERIC(10, 2),
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_subscription_tiers_slug ON subscription_tiers(slug);
|
|
CREATE INDEX IF NOT EXISTS idx_subscription_tiers_level ON subscription_tiers(level);
|
|
|
|
CREATE TABLE IF NOT EXISTS subscription_tier_features (
|
|
feature_id SERIAL PRIMARY KEY,
|
|
subscription_tier_id INTEGER NOT NULL REFERENCES subscription_tiers(subscription_tier_id) ON DELETE CASCADE,
|
|
feature_key VARCHAR(100) NOT NULL,
|
|
label VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT subscription_tier_features_unique UNIQUE (subscription_tier_id, feature_key)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS subscription_tier_features_sort_idx ON subscription_tier_features(subscription_tier_id, sort_order);
|
|
|
|
ALTER TABLE users
|
|
ADD COLUMN IF NOT EXISTS subscription_tier_id INTEGER;
|
|
|
|
ALTER TABLE users
|
|
ADD CONSTRAINT users_subscription_tier_id_fkey
|
|
FOREIGN KEY (subscription_tier_id)
|
|
REFERENCES subscription_tiers(subscription_tier_id)
|
|
ON DELETE SET NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_users_subscription_tier_id ON users(subscription_tier_id);
|
|
|
|
INSERT INTO subscription_tiers (name, slug, description, level, price_monthly, price_yearly, is_active)
|
|
VALUES
|
|
('Free', 'free', 'Plano gratuito com recursos essenciais.', 0, 0, 0, TRUE),
|
|
('Pro', 'pro', 'Plano profissional com recursos avançados.', 1, 29.90, 299.00, TRUE),
|
|
('Ultra', 'ultra', 'Plano completo com todos os recursos e benefícios.', 2, 59.90, 599.00, TRUE)
|
|
ON CONFLICT (slug) DO UPDATE
|
|
SET
|
|
name = EXCLUDED.name,
|
|
description = EXCLUDED.description,
|
|
level = EXCLUDED.level,
|
|
price_monthly = EXCLUDED.price_monthly,
|
|
price_yearly = EXCLUDED.price_yearly,
|
|
is_active = EXCLUDED.is_active,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|
|
WITH free_tier AS (
|
|
SELECT subscription_tier_id
|
|
FROM subscription_tiers
|
|
WHERE slug = 'free'
|
|
LIMIT 1
|
|
)
|
|
UPDATE users
|
|
SET subscription_tier_id = free_tier.subscription_tier_id
|
|
FROM free_tier
|
|
WHERE users.subscription_tier_id IS NULL;
|
|
|
|
INSERT INTO subscription_tier_features (subscription_tier_id, feature_key, label, description, sort_order)
|
|
SELECT tier.subscription_tier_id,
|
|
feat.feature_key,
|
|
feat.label,
|
|
feat.description,
|
|
feat.sort_order
|
|
FROM (
|
|
VALUES
|
|
('free', 'core.marketplace', 'Acesso ao marketplace', 'Publicar e negociar itens com segurança.', 0),
|
|
('free', 'core.notifications', 'Notificações básicas', 'Alertas essenciais sobre atividades da conta.', 1),
|
|
('pro', 'advanced.analytics', 'Analytics detalhadas', 'Painéis avançados com métricas de performance.', 0),
|
|
('pro', 'advanced.priority-support', 'Suporte prioritário', 'Canal exclusivo de atendimento mais rápido.', 1),
|
|
('ultra', 'premium.whitelabel', 'Whitelabel & branding', 'Personalização completa da experiência.', 0),
|
|
('ultra', 'premium.integrations', 'Integrações premium', 'Acesso antecipado a integrações e APIs.', 1)
|
|
) AS feat(slug, feature_key, label, description, sort_order)
|
|
JOIN subscription_tiers AS tier ON tier.slug = feat.slug
|
|
ON CONFLICT (subscription_tier_id, feature_key) DO UPDATE
|
|
SET
|
|
label = EXCLUDED.label,
|
|
description = EXCLUDED.description,
|
|
sort_order = EXCLUDED.sort_order,
|
|
updated_at = CURRENT_TIMESTAMP;
|
|
|