== Purchasing a Subscription

==== Actors: **Registered User (USER)**

**1.** Display Available Subscription Plans.

{{{#!sql
SELECT plan_id, name, price, duration_months, description
FROM subscription_plan;

}}}

**2.** Check for an Active Subscription.


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

}}}

**3.** Create a New Subscription.


{{{#!sql
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.

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

}}}