Recurring Billing

-- Set Subscription Bill Dates back by specified number of days
-- SET interval.days TO '30 DAY';
SET interval.days TO '1 month';

update billing.subscription
set next_status_change_date = next_status_change_date - current_setting('interval.days')::interval,
next_bill_date = next_bill_date - current_setting('interval.days')::interval,
prior_bill_date = prior_bill_date - current_setting('interval.days')::interval,
end_of_term_date = end_of_term_date - current_setting('interval.days')::interval,
start_of_term_date = start_of_term_date - current_setting('interval.days')::interval,
created_date = created_date - current_setting('interval.days')::interval;

-- put billing events back by specified number of days
update billing.billing_event
set bill_date = bill_date - current_setting('interval.days')::interval,
billing_cycle_start_date = billing_cycle_start_date - current_setting('interval.days')::interval,
billing_cycle_end_date = billing_cycle_end_date - current_setting('interval.days')::interval;

-- ************** WHEN THERE ARE TWO PERIODS **************
-- period 1 & period 2 bill dates
update billing.subscription
set
periods = jsonb_set(
  jsonb_set(periods::jsonb,
    '{0,billDate}',
     to_jsonb((periods::jsonb->0->>'billDate')::timestamp - current_setting('interval.days')::interval)
   ),
  	'{1,billDate}',
   	to_jsonb((periods::jsonb->1->>'billDate')::timestamp - current_setting('interval.days')::interval)
);

-- period 1 & period 2 periodEndDates
update billing.subscription
set
periods = jsonb_set(
  jsonb_set(periods::jsonb,
    '{0,periodEndDate}',
     to_jsonb((periods::jsonb->0->>'periodEndDate')::timestamp - current_setting('interval.days')::interval)
   ),
  	'{1,periodEndDate}',
   	to_jsonb((periods::jsonb->1->>'periodEndDate')::timestamp - current_setting('interval.days')::interval)
);

-- period 1 & period 2 periodStartDates
update billing.subscription
set
periods = jsonb_set(
  jsonb_set(periods::jsonb,
    '{0,periodStartDate}',
     to_jsonb((periods::jsonb->0->>'periodStartDate')::timestamp - current_setting('interval.days')::interval)
   ),
  	'{1,periodStartDate}',
   	to_jsonb((periods::jsonb->1->>'periodStartDate')::timestamp - current_setting('interval.days')::interval)
);
-- ************** END **************

-- ************** WHEN THERE IS ONLY ONE PERIOD **************
-- period 1 ONLY bill dates
update billing.subscription
set
periods = jsonb_set(periods::jsonb,
    '{0,billDate}',
     to_jsonb((periods::jsonb->0->>'billDate')::timestamp - current_setting('interval.days')::interval)
   );

-- period 1 ONLY periodEndDates
update billing.subscription
set
periods = jsonb_set(periods::jsonb,
    '{0,periodEndDate}',
     to_jsonb((periods::jsonb->0->>'periodEndDate')::timestamp - current_setting('interval.days')::interval)
   );

-- period 1 ONLY periodStartDates
update billing.subscription
set
periods = jsonb_set(periods::jsonb,
    '{0,periodStartDate}',
     to_jsonb((periods::jsonb->0->>'periodStartDate')::timestamp - current_setting('interval.days')::interval)
   );
-- ************** END **************

DECLARE
    -- build an INTERVAL once, then reuse it
--     shift_intervals  INTERVAL DAY(3) TO SECOND := NUMTODSINTERVAL(4, 'DAY');
    shift_intervals    INTERVAL YEAR(2) TO MONTH       := NUMTOYMINTERVAL(1, 'MONTH');
BEGIN
    -- shift all the date/timestamps:
    UPDATE BILLINGSCHEMA.subscription
    SET
        next_status_change_date = next_status_change_date - shift_intervals,
        next_bill_date          = next_bill_date - shift_intervals,
        prior_bill_date         = prior_bill_date - shift_intervals,
        end_of_term_date        = end_of_term_date - shift_intervals,
        start_of_term_date      = start_of_term_date - shift_intervals,
        created_date            = created_date - shift_intervals;

    -- put billing events back by specified number of days
    UPDATE BILLINGSCHEMA.billing_event
    SET bill_date = bill_date - shift_intervals,
        billing_cycle_start_date = billing_cycle_start_date - shift_intervals,
        billing_cycle_end_date = billing_cycle_end_date - shift_intervals;

    UPDATE BILLINGSCHEMA.subscription s
    SET periods = (
        SELECT JSON_ARRAYAGG(
                       JSON_OBJECT(
                               KEY 'period'           VALUE jsontable.period,
                               KEY 'billDate'         VALUE TO_CHAR(
                                           TO_TIMESTAMP_TZ(jsontable.billDate,
                                                           'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM')
                                           - shift_intervals,
                                           'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM'
                                   ),
                               KEY 'periodStartDate'  VALUE TO_CHAR(
                                           TO_TIMESTAMP_TZ(jsontable.periodStartDate,
                                                           'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM')
                                           - shift_intervals,
                                           'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM'
                                   ),
                               KEY 'periodEndDate'    VALUE TO_CHAR(
                                           TO_TIMESTAMP_TZ(jsontable.periodEndDate,
                                                           'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM')
                                           - shift_intervals,
                                           'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM'
                                   ),
                               KEY 'freeTrial'        VALUE jsontable.freeTrial
                           )
                       ORDER BY jsontable.period
                   )
        FROM JSON_TABLE(
                     s.periods,
                     '$[*]'
                     COLUMNS (
                         period            NUMBER        PATH '$.period',
                         billDate          VARCHAR2(30)  PATH '$.billDate',
                         periodStartDate   VARCHAR2(30)  PATH '$.periodStartDate',
                         periodEndDate     VARCHAR2(30)  PATH '$.periodEndDate',
                         freeTrial         VARCHAR2(5)   PATH '$.freeTrial'
                         )
                 ) jsontable
    );
END;

DELETE FROM billing.subscription_status_audit;
DELETE FROM billing.subscription_lock;
DELETE FROM billing.subscription_item_adjustment;
DELETE FROM billing.subscription_item;
DELETE FROM billing.subscription_billing_audit;
DELETE FROM billing.subscription;

DELETE FROM billing.billing_event_item_adjustment;
DELETE FROM billing.billing_event_item_attr;
DELETE FROM billing.billing_event_item;
DELETE FROM billing.billing_event_payment;
DELETE FROM billing.billing_event;

DELETE FROM workflow.blc_side_effect;
DELETE FROM workflow.blc_context_value;
DELETE FROM workflow.blc_activity_log;
DELETE FROM workflow.blc_notification_state;
DELETE FROM workflow.blc_resource_lock;
DELETE FROM workflow.blc_workflow;

-- DELETE ALL Subscriptions
DELETE FROM BILLINGSCHEMA.subscription_status_audit;
DELETE FROM BILLINGSCHEMA.subscription_lock;
DELETE FROM BILLINGSCHEMA.subscription_item_adjustment;
DELETE FROM BILLINGSCHEMA.subscription_item;
DELETE FROM BILLINGSCHEMA.subscription_billing_audit;
DELETE FROM BILLINGSCHEMA.subscription;

DELETE FROM BILLINGSCHEMA.billing_event_item_adjustment;
DELETE FROM BILLINGSCHEMA.billing_event_item_attr;
DELETE FROM BILLINGSCHEMA.billing_event_item;
DELETE FROM BILLINGSCHEMA.billing_event_payment;
DELETE FROM BILLINGSCHEMA.billing_event;

-- DELETE ALL Workflows
DELETE FROM WORKFLOWSCHEMA.blc_side_effect;
DELETE FROM WORKFLOWSCHEMA.blc_context_value;
DELETE FROM WORKFLOWSCHEMA.blc_activity_log;
DELETE FROM WORKFLOWSCHEMA.blc_notification_state;
DELETE FROM WORKFLOWSCHEMA.blc_resource_lock;
DELETE FROM WORKFLOWSCHEMA.blc_workflow;