Purchasing a Subscription

Actors: Registered User (USER)

1. Display Available Subscription Plans.

SELECT plan_id, name, price, duration_months, description
FROM subscription_plan;

2. Check for an Active Subscription.

SELECT *
FROM user_subscription
WHERE user_id = :userId
  AND status = 'ACTIVE'
  AND end_date >= CURRENT_DATE;

3. Create a New Subscription.

INSERT INTO user_subscription (user_id, plan_id, start_date, end_date, status)
VALUES (
    :userId,
    :planId,
    CURRENT_DATE,
    CURRENT_DATE + 
      (SELECT duration_months 
       FROM subscription_plan 
       WHERE plan_id = :planId) * INTERVAL '1 month',
    'ACTIVE'
);

4. Record the Payment.

INSERT INTO payment (user_id, subscription_id, amount)
VALUES (
    :userId,
    currval('user_subscription_subscription_id_seq'),
    :amount
);