<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Analytics>
  <Name>APIM_STAT_SCRIPT</Name>
  <Script>

    <!--create temp table for request summary table in RDBMS-->
    create temporary table APIRequestSummaryData using CarbonJDBC options (dataSource "WSO2AM_STATS_DB", tableName "API_REQUEST_SUMMARY",
    schema "api STRING ,
    api_version STRING ,
    version STRING ,
    apiPublisher STRING ,
    consumerKey STRING ,
    userId STRING ,
    context STRING ,
    max_request_time LONG ,
    total_request_count INTEGER ,
    hostName STRING ,
    year INTEGER ,
    month INTEGER ,
    day INTEGER ,
    time STRING",
    primaryKeys "api,api_version,version,apiPublisher,consumerKey,userId,context,hostName,year,month,day"
    );

    <!--incrementally processed per day request data table-->
    create temporary table APIMGT_PERDAY_REQUEST_DATA USING CarbonAnalytics OPTIONS(tableName "ORG_WSO2_APIMGT_STATISTICS_PERDAYREQUEST", schema "
    year INT, month INT, day INT,
    consumerKey STRING, context STRING, api_version STRING, api STRING, version STRING, maxRequestTime LONG, firstRequestTime LONG, userId STRING, hostName STRING,
    apiPublisher STRING, total_request_count_per_day LONG, resourceTemplate STRING, method STRING, applicationName STRING, tenantDomain STRING,
    userAgent STRING, resourcePath STRING, request INT, applicationId STRING, tier STRING, throttledOut BOOLEAN, clientIp STRING,
    applicationOwner STRING",
    primaryKeys "year, month, day, consumerKey, context, api_version, userId, hostName, apiPublisher, resourceTemplate, method, userAgent, clientIp",
    incrementalParams "STAT_SCRIPT_APIMGT_PERDAY_REQUEST_DATA, DAY",
    mergeSchema "false");

    <!--summarised request event and insert into summary table -->
    insert into table APIRequestSummaryData select api, api_version,version, apiPublisher,
    COALESCE(consumerKey,'-'),
    userId,context,
    max(maxRequestTime) as max_request_time,
    sum(total_request_count_per_day) as total_request_count,
    hostName,
    year,
    month,
    day,
    substring(cast(first(firstRequestTime)/1000 as timestamp),0,16)
    from APIMGT_PERDAY_REQUEST_DATA
    where context is not NULL
    group by api,api_version,version,apiPublisher,consumerKey,userId,context,hostName,year,month,day;

    <!--create temp table for API version usage summary table in RDBMS-->
    create temporary table APIVersionUsageSummaryData using CarbonJDBC options (dataSource "WSO2AM_STATS_DB", tableName "API_VERSION_USAGE_SUMMARY",
    schema "api STRING ,
    version STRING ,
    apiPublisher STRING ,
    context STRING ,
    total_request_count INTEGER ,
    hostName STRING ,
    year INTEGER ,
    month INTEGER ,
    day INTEGER ,
    time STRING",
    primaryKeys "api,version,apiPublisher,context,hostName,year,month,day"
    );


    <!--insert API version usage summary -->
    insert into table APIVersionUsageSummaryData select api,version,apiPublisher,context,sum(total_request_count_per_day) as total_request_count,hostName,year,month,day,
    substring(cast(first(firstRequestTime)/1000 as timestamp),0,16)
    from APIMGT_PERDAY_REQUEST_DATA
    where context is not NULL
    group by api,version,apiPublisher,context,hostName,year,month,day;


    <!--create temp table for API Resource usage summary table in RDBMS-->
    create temporary table APIResourcePathUsageSummaryData using CarbonJDBC options (dataSource "WSO2AM_STATS_DB", tableName "API_Resource_USAGE_SUMMARY",
    schema "api STRING ,
    version STRING ,
    apiPublisher STRING ,
    consumerKey STRING ,
    resourcePath STRING ,
    context STRING ,
    method STRING ,
    total_request_count INTEGER ,
    hostName STRING ,
    year INTEGER ,
    month INTEGER ,
    day INTEGER ,
    time STRING",
    primaryKeys "api,version,apiPublisher,consumerKey,context,resourcePath,method,hostName,year,month,day"
    );

    <!--inserting API resource usage summary data-->
    insert into table APIResourcePathUsageSummaryData select
    api,version,apiPublisher,COALESCE(consumerKey,'-'),
    resourceTemplate,context,method,sum(total_request_count_per_day) as total_request_count,hostName,
    year,month,day,
    substring(cast(first(firstRequestTime)/1000 as timestamp),0,16)
    from APIMGT_PERDAY_REQUEST_DATA
    where context is not NULL
    group by api,version,apiPublisher,consumerKey,resourceTemplate,context,method,hostName,year,month,day;


    INCREMENTAL_TABLE_COMMIT STAT_SCRIPT_APIMGT_PERDAY_REQUEST_DATA;


    <!--create temp table for API Response summary table in RDBMS-->
    create temporary table APIResponseSummaryData using CarbonJDBC options (dataSource "WSO2AM_STATS_DB", tableName "API_RESPONSE_SUMMARY",
    schema "api_version STRING ,
    apiPublisher STRING ,
    context STRING ,
    serviceTime INTEGER ,
    total_response_count INTEGER ,
    hostName STRING ,
    year INTEGER ,
    month INTEGER ,
    day INTEGER ,
    time STRING",
    primaryKeys "api_version,apiPublisher,context,hostName,year,month,day"
    );

    <!--incrementally processed per day response data table -->
    create temporary table APIMGT_PERDAY_RESPONSE_DATA USING CarbonAnalytics OPTIONS(tableName "ORG_WSO2_APIMGT_STATISTICS_PERDAYRESPONSE", schema "
    year INT, month INT, day INT,
    context STRING, api_version STRING, api STRING, resourceTemplate STRING, version STRING, tenantDomain STRING, hostName STRING, apiPublisher STRING,
    destination STRING, consumerKey STRING, resourcePath STRING, method STRING, response INT, serviceTime LONG, username STRING, maxEventTime LONG, firstEventTime LONG,
    applicationName STRING, applicationId STRING, cacheHit BOOLEAN, responseSize LONG, protocol STRING, responseCode INT,
    total_response_count_per_day LONG",
    primaryKeys "year, month, day, context, api_version, resourceTemplate, hostName, apiPublisher, destination, consumerKey, method, username",
    incrementalParams "STAT_SCRIPT_APIMGT_PERDAY_RESPONSE_DATA, DAY",
    mergeSchema "false");

    <!--inserting API Response usage summary data-->
    insert into table APIResponseSummaryData select
    api_version, COALESCE(apiPublisher,''),
    context, sum(serviceTime) as avg_service_time,
    sum(total_response_count_per_day) as total_response_count, hostName,
    year,month,day,
    substring(cast(first(firstEventTime)/1000 as timestamp),0,16)
    from APIMGT_PERDAY_RESPONSE_DATA
    where context is not NULL
    group by api_version,apiPublisher,context,hostName,year,month,day;



    <!--create temp table for API Fault summary table in RDBMS-->
    create temporary table APIFaultSummaryData using CarbonJDBC options (dataSource "WSO2AM_STATS_DB", tableName "API_FAULT_SUMMARY",
    schema "api STRING ,
    version STRING ,
    apiPublisher STRING ,
    consumerKey STRING ,
    context STRING ,
    total_fault_count INTEGER ,
    hostName STRING ,
    year INTEGER ,
    month INTEGER ,
    day INTEGER ,
    time STRING",
    primaryKeys "api,version,apiPublisher,consumerKey,context,hostName,year,month,day"
    );
    <!--create temp table for Fault event stream data-->
    create temporary table APIFaultData USING CarbonAnalytics OPTIONS(tableName "ORG_WSO2_APIMGT_STATISTICS_FAULT",
    schema "consumerKey string,
    context string,
    api_version string,
    api string,
    resourcePath string,
    method string,
    version string,
    errorCode string,
    errorMessage string,
    requestTime long,
    userId string,
    tenantDomain string,
    hostName string,
    apiPublisher string,
    applicationName string,
    applicationId string,
    protocol string,
    _timestamp LONG",
    incrementalParams "STAT_SCRIPT_APIFaultData, DAY",
    mergeSchema "false"
    );

    <!--inserting API Fault summary data-->
    insert into table APIFaultSummaryData select
    api,version,apiPublisher,COALESCE(consumerKey,'-'),context,
    count(errorMessage) as total_fault_count,
    hostName,
    substring(cast(first(requestTime)/1000 as timestamp),0,4),
    substring(cast(first(requestTime)/1000 as timestamp),6,2),
    substring(cast(first(requestTime)/1000 as timestamp),9,2),
    substring(cast(first(requestTime)/1000 as timestamp),0,16)
    from APIFaultData where
    context is not NULL group by api,version,apiPublisher,consumerKey ,context,hostName,
    substring(cast(requestTime/1000 as timestamp),0,4),
    substring(cast(requestTime/1000 as timestamp),6,2),
    substring(cast(requestTime/1000 as timestamp),9,2);

    INCREMENTAL_TABLE_COMMIT STAT_SCRIPT_APIFaultData;

    <!--create temp table for API Destination summary table in RDBMS-->
    create temporary table APIDestinationDataSummaryData using CarbonJDBC options (dataSource "WSO2AM_STATS_DB", tableName "API_DESTINATION_SUMMARY",
    schema "api STRING ,
    version STRING ,
    apiPublisher STRING ,
    context STRING ,
    destination STRING ,
    total_request_count INTEGER ,
    hostName STRING ,
    year INTEGER ,
    month INTEGER ,
    day INTEGER ,
    time STRING",
    primaryKeys "api,version,apiPublisher,context,destination,hostName,year,month,day"
    );

    <!--inserting API Destination usage summary data-->
    insert into table APIDestinationDataSummaryData select
    api,version,apiPublisher, context, COALESCE(destination,'-'), sum(total_response_count_per_day) as total_request_count,
    hostName,year,month,day,
    substring(cast(first(firstEventTime)/1000 as timestamp),0,16)
    from APIMGT_PERDAY_RESPONSE_DATA
    where context is not NULL
    group by api,version,apiPublisher,context,destination,hostName,year,month,day;

    INCREMENTAL_TABLE_COMMIT STAT_SCRIPT_APIMGT_PERDAY_RESPONSE_DATA;

  </Script>
  <CronExpression>0 0/5 * 1/1 * ? *</CronExpression>
</Analytics>