Changes between Version 2 and Version 3 of PurchasingSubscription

Timestamp:

01/22/26 02:50:30 (16 hours ago)

Author:

221296

Comment:

--

PurchasingSubscription

@@ -3 +3 @@
 ==== Actors: **Registered User (USER)**
 
-**1.** The user selects a subscription plan.
+**1.** Display Available Subscription Plans.
 
-**2.** The system creates an active subscription.
+{{{#!sql
+SELECT plan_id, name, price, duration_months, description
+FROM subscription_plan;
 
-**3.** The payment is recorded.
+}}}
+
+**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 (1, 2, CURRENT_DATE, CURRENT_DATE + INTERVAL '6 months', 'ACTIVE');
-
-INSERT INTO payment (user_id, subscription_id, amount)
-VALUES (1, 1, 49.99);
-
+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
+);
+
+}}}