API Monetization - PostgreSQL script need to be fixed

[NipuniC]

Description

In the APIM 4.4.0 documentation, APIM Monetization related DB script for postgreSQL [1] needs to be replaced with the following.

CREATE TABLE IF NOT EXISTS AM_MONETIZATION (
    API_ID INTEGER NOT NULL,
    TIER_NAME VARCHAR(512),
    STRIPE_PRODUCT_ID VARCHAR(512),
    STRIPE_PLAN_ID VARCHAR(512),
    FOREIGN KEY (API_ID) REFERENCES AM_API (API_ID) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS AM_POLICY_PLAN_MAPPING (
    POLICY_UUID VARCHAR(256),
    PRODUCT_ID VARCHAR(512),
    PLAN_ID VARCHAR(512),
    FOREIGN KEY (POLICY_UUID) REFERENCES AM_POLICY_SUBSCRIPTION(UUID)
);

CREATE SEQUENCE IF NOT EXISTS AM_MONETIZATION_PLATFORM_CUSTOMERS_ID_SEQ START WITH 1 INCREMENT BY 1;
CREATE TABLE IF NOT EXISTS AM_MONETIZATION_PLATFORM_CUSTOMERS (
    ID INTEGER PRIMARY KEY DEFAULT nextval('AM_MONETIZATION_PLATFORM_CUSTOMERS_ID_SEQ'),
    SUBSCRIBER_ID INTEGER NOT NULL,
    TENANT_ID INTEGER NOT NULL,
    CUSTOMER_ID VARCHAR(256) NOT NULL,    
    FOREIGN KEY (SUBSCRIBER_ID) REFERENCES AM_SUBSCRIBER(SUBSCRIBER_ID) ON DELETE CASCADE
);

CREATE SEQUENCE IF NOT EXISTS AM_MONETIZATION_SHARED_CUSTOMERS_ID_SEQ START WITH 1 INCREMENT BY 1;
CREATE TABLE IF NOT EXISTS AM_MONETIZATION_SHARED_CUSTOMERS (
    ID INTEGER PRIMARY KEY DEFAULT nextval('AM_MONETIZATION_SHARED_CUSTOMERS_ID_SEQ'),
    APPLICATION_ID INTEGER NOT NULL,
    API_PROVIDER VARCHAR(256) NOT NULL,
    TENANT_ID INTEGER NOT NULL,
    SHARED_CUSTOMER_ID VARCHAR(256) NOT NULL,
    PARENT_CUSTOMER_ID INTEGER NOT NULL,    
    FOREIGN KEY (APPLICATION_ID) REFERENCES AM_APPLICATION(APPLICATION_ID) ON DELETE CASCADE,
    FOREIGN KEY (PARENT_CUSTOMER_ID) REFERENCES AM_MONETIZATION_PLATFORM_CUSTOMERS(ID) ON DELETE CASCADE
);

CREATE SEQUENCE IF NOT EXISTS AM_MONETIZATION_SUBSCRIPTIONS_ID_SEQ START WITH 1 INCREMENT BY 1;
CREATE TABLE IF NOT EXISTS AM_MONETIZATION_SUBSCRIPTIONS (
    ID INTEGER PRIMARY KEY DEFAULT nextval('AM_MONETIZATION_SUBSCRIPTIONS_ID_SEQ'),
    SUBSCRIBED_APPLICATION_ID INTEGER NOT NULL,
    SUBSCRIBED_API_ID INTEGER NOT NULL,
    TENANT_ID INTEGER NOT NULL,
    SUBSCRIPTION_ID VARCHAR(256) NOT NULL,
    SHARED_CUSTOMER_ID INTEGER NOT NULL,    
    FOREIGN KEY (SUBSCRIBED_APPLICATION_ID) REFERENCES AM_APPLICATION(APPLICATION_ID) ON DELETE CASCADE,
    FOREIGN KEY (SUBSCRIBED_API_ID) REFERENCES AM_API(API_ID) ON DELETE CASCADE,
    FOREIGN KEY (SHARED_CUSTOMER_ID) REFERENCES AM_MONETIZATION_SHARED_CUSTOMERS(ID) ON DELETE CASCADE
);

[1] https://apim.docs.wso2.com/en/latest/design/api-monetization/monetizing-an-api/#step-4-send-usage-data-to-the-billing-engine:~:text=Execute%20the%20MySQL%20script%20in%20this%20example%20scenario

Steps to Reproduce

Version

4.4.0

Environment Details (with versions)

No response