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

  <!--create temp table for throttle out summary table in RDBMS-->
    create temporary table APIThrottleSummaryData using CarbonJDBC options (dataSource "WSO2AM_STATS_DB", tableName "API_THROTTLED_OUT_SUMMARY",
    schema "api STRING ,
    api_version STRING ,
    context STRING ,
    apiPublisher STRING ,
    applicationName STRING ,
    tenantDomain STRING ,
    year INTEGER ,
    month INTEGER ,
    day INTEGER ,
    week INTEGER ,
    time STRING ,
    success_request_count INTEGER ,
    throttleout_count INTEGER ,
    throttledOutReason STRING",
    primaryKeys "api,api_version,context,apiPublisher,applicationName,tenantDomain,year,month,day,throttledOutReason"
    );

    <!--create temp table for Throttle event stream data-->
    create temporary table APIThrottleData USING CarbonAnalytics OPTIONS(
      tableName "ORG_WSO2_APIMGT_STATISTICS_THROTTLE",
      schema "meta_clientType STRING, accessToken STRING, userId STRING, tenantDomain STRING, api STRING, api_version STRING,
      context STRING, apiPublisher STRING, throttledTime LONG, applicationName STRING, applicationId STRING, subscriber STRING,
      throttledOutReason STRING",
      incrementalParams "THROTTLE_APIThrottleData, DAY",
      mergeSchema "false"
    );

    <!--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 "THROTTLE_ORG_WSO2_APIMGT_STATISTICS_PERDAYREQUEST, DAY",
    mergeSchema "false");


  <!--inserting API throttle out summary data-->
  insert into table APIThrottleSummaryData select
    COALESCE(a.api,b.api),
    COALESCE(a.api_version,b.api_version),
    COALESCE(a.context,b.context),
    COALESCE(a.apiPublisher,b.apiPublisher),
    COALESCE(a.applicationName,b.applicationName,'_ANONYMOUS_'),
    COALESCE(a.tenantDomain,b.tenantDomain),
    COALESCE(a.year,b.year),
    COALESCE(a.month,b.month),
    COALESCE(a.day,b.day),
    COALESCE(a.week,b.week),
    COALESCE(a.time,b.time),
    COALESCE(success_request_count,0),
    COALESCE(throttleout_count,0),
    COALESCE(b.throttledOutReason,'_')
    from
    (select api,api_version,context,apiPublisher,COALESCE(applicationName,'_ANONYMOUS_') as applicationName,tenantDomain,first(firstRequestTime) as times,
    cast(year as STRING) year,cast(month as STRING) month,cast(day as STRING) day,
    cast(first(firstRequestTime)/(1000 * 60 * 60 *24 * 7) as INT) week,
    substring(cast(first(firstRequestTime)/1000 as timestamp),0,16) time,
    sum(total_request_count_per_day) as success_request_count

    from APIMGT_PERDAY_REQUEST_DATA where context is not NULL group by
    api,api_version,context,apiPublisher,applicationName,tenantDomain,
    cast(year as STRING),cast(month as STRING),cast(day as STRING),
    cast(firstRequestTime/(1000 * 60 * 60 *24 * 7) as INT)
    ) a full outer join

    (select api,api_version,context,apiPublisher,COALESCE(applicationName,'_ANONYMOUS_') as applicationName,tenantDomain,throttledOutReason,first(throttledTime) as times,
    substring(cast(first(throttledTime)/1000 as timestamp),0,4) year,
    substring(cast(first(throttledTime)/1000 as timestamp),6,2) month,
    substring(cast(first(throttledTime)/1000 as timestamp),9,2) day,
    cast(first(throttledTime)/(1000 * 60 * 60 *24 * 7) as INT) week,
    substring(cast(first(throttledTime)/1000 as timestamp),0,16) time,
    count(apiPublisher) as throttleout_count
    from APIThrottleData where context is not NULL group by
    api,api_version,context,apiPublisher,applicationName,tenantDomain,throttledOutReason,
    substring(cast(throttledTime/1000 as timestamp),0,4),
    substring(cast(throttledTime/1000 as timestamp),6,2),
    substring(cast(throttledTime/1000 as timestamp),9,2),
    cast((throttledTime/(1000 * 60 * 60 *24 * 7)) as INT)
    ) b ON a.api=b.api AND a.api_version=b.api_version AND a.context=b.context AND a.apiPublisher=b.apiPublisher AND a.applicationName=b.applicationName AND a.tenantDomain=b.tenantDomain AND
    a.year=b.year AND a.month=b.month AND a.day=b.day AND a.week=b.week;

    INCREMENTAL_TABLE_COMMIT THROTTLE_ORG_WSO2_APIMGT_STATISTICS_PERDAYREQUEST;
    INCREMENTAL_TABLE_COMMIT THROTTLE_APIThrottleData;
  </Script>
  <CronExpression>0 0/5 * 1/1 * ? *</CronExpression>
</Analytics>