162 lines
4.0 KiB
SQL
162 lines
4.0 KiB
SQL
-- =====================
|
|
-- Subscription Plans
|
|
-- =====================
|
|
|
|
-- name: CreateSubscriptionPlan :one
|
|
INSERT INTO subscription_plans (
|
|
name, description, duration_value, duration_unit, price, currency, is_active
|
|
)
|
|
VALUES ($1, $2, $3, $4, $5, $6, COALESCE($7, true))
|
|
RETURNING *;
|
|
|
|
-- name: GetSubscriptionPlanByID :one
|
|
SELECT * FROM subscription_plans WHERE id = $1;
|
|
|
|
-- name: ListSubscriptionPlans :many
|
|
SELECT * FROM subscription_plans
|
|
WHERE ($1::BOOLEAN IS NULL OR $1 = true AND is_active = true OR $1 = false)
|
|
ORDER BY price ASC;
|
|
|
|
-- name: ListActiveSubscriptionPlans :many
|
|
SELECT * FROM subscription_plans
|
|
WHERE is_active = true
|
|
ORDER BY price ASC;
|
|
|
|
-- name: UpdateSubscriptionPlan :exec
|
|
UPDATE subscription_plans
|
|
SET
|
|
name = COALESCE($1, name),
|
|
description = COALESCE($2, description),
|
|
duration_value = COALESCE($3, duration_value),
|
|
duration_unit = COALESCE($4, duration_unit),
|
|
price = COALESCE($5, price),
|
|
currency = COALESCE($6, currency),
|
|
is_active = COALESCE($7, is_active),
|
|
updated_at = CURRENT_TIMESTAMP
|
|
WHERE id = $8;
|
|
|
|
-- name: DeleteSubscriptionPlan :exec
|
|
DELETE FROM subscription_plans WHERE id = $1;
|
|
|
|
-- =====================
|
|
-- User Subscriptions
|
|
-- =====================
|
|
|
|
-- name: CreateUserSubscription :one
|
|
INSERT INTO user_subscriptions (
|
|
user_id, plan_id, starts_at, expires_at, status, payment_reference, payment_method, auto_renew
|
|
)
|
|
VALUES ($1, $2, COALESCE($3, CURRENT_TIMESTAMP), $4, COALESCE($5, 'ACTIVE'), $6, $7, COALESCE($8, false))
|
|
RETURNING *;
|
|
|
|
-- name: GetUserSubscriptionByID :one
|
|
SELECT
|
|
us.*,
|
|
sp.name AS plan_name,
|
|
sp.duration_value,
|
|
sp.duration_unit,
|
|
sp.price,
|
|
sp.currency
|
|
FROM user_subscriptions us
|
|
JOIN subscription_plans sp ON sp.id = us.plan_id
|
|
WHERE us.id = $1;
|
|
|
|
-- name: GetActiveSubscriptionByUserID :one
|
|
SELECT
|
|
us.*,
|
|
sp.name AS plan_name,
|
|
sp.duration_value,
|
|
sp.duration_unit,
|
|
sp.price,
|
|
sp.currency
|
|
FROM user_subscriptions us
|
|
JOIN subscription_plans sp ON sp.id = us.plan_id
|
|
WHERE us.user_id = $1
|
|
AND us.status = 'ACTIVE'
|
|
AND us.expires_at > CURRENT_TIMESTAMP
|
|
ORDER BY us.expires_at DESC
|
|
LIMIT 1;
|
|
|
|
-- name: GetUserSubscriptionHistory :many
|
|
SELECT
|
|
us.*,
|
|
sp.name AS plan_name,
|
|
sp.duration_value,
|
|
sp.duration_unit,
|
|
sp.price,
|
|
sp.currency
|
|
FROM user_subscriptions us
|
|
JOIN subscription_plans sp ON sp.id = us.plan_id
|
|
WHERE us.user_id = $1
|
|
ORDER BY us.created_at DESC
|
|
LIMIT sqlc.narg('limit')::INT
|
|
OFFSET sqlc.narg('offset')::INT;
|
|
|
|
-- name: CountUserSubscriptions :one
|
|
SELECT COUNT(*) FROM user_subscriptions WHERE user_id = $1;
|
|
|
|
-- name: UpdateUserSubscriptionStatus :exec
|
|
UPDATE user_subscriptions
|
|
SET
|
|
status = $1,
|
|
updated_at = CURRENT_TIMESTAMP
|
|
WHERE id = $2;
|
|
|
|
-- name: CancelUserSubscription :exec
|
|
UPDATE user_subscriptions
|
|
SET
|
|
status = 'CANCELLED',
|
|
cancelled_at = CURRENT_TIMESTAMP,
|
|
auto_renew = false,
|
|
updated_at = CURRENT_TIMESTAMP
|
|
WHERE id = $1;
|
|
|
|
-- name: ExpireUserSubscription :exec
|
|
UPDATE user_subscriptions
|
|
SET
|
|
status = 'EXPIRED',
|
|
updated_at = CURRENT_TIMESTAMP
|
|
WHERE id = $1;
|
|
|
|
-- name: UpdateAutoRenew :exec
|
|
UPDATE user_subscriptions
|
|
SET
|
|
auto_renew = $1,
|
|
updated_at = CURRENT_TIMESTAMP
|
|
WHERE id = $2;
|
|
|
|
-- name: GetExpiredSubscriptions :many
|
|
SELECT us.*, sp.name AS plan_name
|
|
FROM user_subscriptions us
|
|
JOIN subscription_plans sp ON sp.id = us.plan_id
|
|
WHERE us.status = 'ACTIVE'
|
|
AND us.expires_at <= CURRENT_TIMESTAMP;
|
|
|
|
-- name: GetExpiringSubscriptions :many
|
|
SELECT
|
|
us.*,
|
|
sp.name AS plan_name,
|
|
u.email,
|
|
u.first_name
|
|
FROM user_subscriptions us
|
|
JOIN subscription_plans sp ON sp.id = us.plan_id
|
|
JOIN users u ON u.id = us.user_id
|
|
WHERE us.status = 'ACTIVE'
|
|
AND us.expires_at > CURRENT_TIMESTAMP
|
|
AND us.expires_at <= CURRENT_TIMESTAMP + INTERVAL '7 days';
|
|
|
|
-- name: HasActiveSubscription :one
|
|
SELECT EXISTS(
|
|
SELECT 1 FROM user_subscriptions
|
|
WHERE user_id = $1
|
|
AND status = 'ACTIVE'
|
|
AND expires_at > CURRENT_TIMESTAMP
|
|
) AS has_subscription;
|
|
|
|
-- name: ExtendSubscription :exec
|
|
UPDATE user_subscriptions
|
|
SET
|
|
expires_at = $1,
|
|
updated_at = CURRENT_TIMESTAMP
|
|
WHERE id = $2;
|