/* Enter a unique ExecutionPlan */

@Plan:name('APIMAnalytics-RequestSummarizer-RequestSummarizer-realtime1')



/* Enter a unique description for ExecutionPlan */

-- @Plan:description('ExecutionPlan')

@Plan:statistics('false')

@Import('org.wso2.apimgt.statistics.request:1.1.0')
define stream requests (meta_clientType string, consumerKey string, context string, api_version string, api string, resourcePath string, resourceTemplate string, method string, version string, request int, requestTime long, userId string, tenantDomain string, hostName string, apiPublisher string, applicationName string, applicationId string, userAgent string, tier string, throttledOut bool, clientIp string, applicationOwner string);

@Import('org.wso2.apimgt.statistics.response:1.1.0')
define stream responses (meta_clientType string, consumerKey string, context string, api_version string, api string, resourcePath string, resourceTemplate string, method string, version string, response int, responseTime long, serviceTime long, backendTime long, username string, eventTime long, tenantDomain string, hostName string, apiPublisher string, applicationName string, applicationId string, cacheHit bool, responseSize long, protocol string, responseCode int, destination string);

@Import('org.wso2.apimgt.statistics.execution.time:1.0.0')
define stream executionTimes (meta_clientType string, api string, api_version string, tenantDomain string, apiPublisher string, apiResponseTime long, context string, securityLatency long, throttlingLatency long, requestMediationLatency long, responseMediationLatency long, backendLatency long, otherLatency long, eventTime long);

@Export('org.wso2.analytics.apim.stateStream:1.0.0')
define stream stateStream (context string, method string, resourceTemplate string, tenantDomain string, state string, userId string, requestTimestamp string, consumerKey string, applicationName string, applicationOwner string);

@Export('org.wso2.apimgt.statistics.perMinuteRequest:1.0.0')
define stream perMinuteRequests (meta_clientType string, year int, month int, day int, hour int, minute int, consumerKey string, context string, api_version string, api string, version string, requestTime long, userId string, hostName string, apiPublisher string, total_request_count long, resourceTemplate string, method string, applicationName string, tenantDomain string, userAgent string, resourcePath string, request int, applicationId string, tier string, throttledOut bool, clientIp string, applicationOwner string, _timestamp long);

@Export('org.wso2.apimgt.statistics.perMinuteResponse:1.0.0')
define stream perMinuteResponses (meta_clientType string, year int, month int, day int, hour int, minute 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, responseTime long, serviceTime long, backendTime long, username string, eventTime long, applicationName string, applicationId string, cacheHit bool, responseSize long, protocol string, responseCode int, total_response_count long, _timestamp long);

@Export('org.wso2.apimgt.statistics.perMinuteExecutionTimes:1.0.0')
define stream perMinuteExecutionTimes (meta_clientType string, year int, month int, day int, hour int, minute int, context string, api_version string, api string, tenantDomain string, apiPublisher string, apiResponseTime double, securityLatency double, throttlingLatency double, requestMediationLatency double, responseMediationLatency double, backendLatency double, otherLatency double, eventTime long, _timestamp long);

@Export('org.wso2.analytics.apim.responsePerMinPerApiStream:1.0.0')
define stream responsePerMinPerApiStream (api_version string, tenantDomain string, responsesPerMinPerApi long);

@Export('org.wso2.analytics.apim.requestPerMinPerApiStream:1.0.0')
define stream requestPerMinPerApiStream (api_version string, tenantDomain string, requestsPerMinPerApi long);

@Export('org.wso2.apimgt.statistics.requestsPerMinPerKeyStream:1.0.0')
define stream requestsPerMinPerKeyStream (consumerKey string, requestsPerMinPerKey long);


-- requests

-- second
-- global
from requests#window.externalTimeBatch(requestTime, 1 sec, 0, 10 sec, true)
select requestTime as startTime, meta_clientType, consumerKey, context, api_version, api, version,
max(requestTime) as requestTime, userId, hostName, apiPublisher, count() as total_request_count, 
resourceTemplate, method, applicationName, tenantDomain, userAgent, resourcePath, request, applicationId, 
tier, throttledOut, clientIp, applicationOwner
group by consumerKey, context, api_version, userId, hostName, apiPublisher, resourceTemplate, method, userAgent, clientIp  
insert into perSecondRequestsTemp;

from perSecondRequestsTemp
select meta_clientType, (startTime - (startTime % 1000)) as startTime, convert((startTime - (startTime % 1000)), 'string') as facetStartTime,
time:extract(startTime, 'year') as year, time:extract(startTime, 'month') as month, 
time:extract(startTime, 'day') as day, time:extract(startTime, 'hour') as hour, 
time:extract(startTime, 'minute') as minute, time:extract(startTime, 'second') as second,
consumerKey, context, api_version, api, version, 
requestTime, userId, hostName, apiPublisher, total_request_count, 
resourceTemplate, method, applicationName, tenantDomain, userAgent, resourcePath, request, applicationId, 
tier, throttledOut, clientIp, applicationOwner
insert into perSecondRequests;

--api_version
from requests#window.externalTimeBatch(requestTime, 1 sec, 0, 10 sec, true)
select requestTime as startTime, api_version, tenantDomain, count() as total_request_count
group by api_version, tenantDomain 
insert into perSecondPerApiRequestsTemp;

from perSecondPerApiRequestsTemp
select (startTime - (startTime % 1000)) as startTime, convert((startTime - (startTime % 1000)), 'string') as facetStartTime,
api_version, tenantDomain, total_request_count
insert into perSecondPerApiRequests;

-- consumer key
from requests#window.externalTimeBatch(requestTime, 1 sec, 0, 10 sec)
select requestTime as startTime, consumerKey, count() as total_request_count
group by consumerKey 
insert into perSecondPerKeyRequestsTemp;

from perSecondPerKeyRequestsTemp
select (startTime - (startTime % 1000)) as startTime, convert((startTime - (startTime % 1000)), 'string') as facetStartTime,
consumerKey, total_request_count
insert into perSecondPerKeyRequests;

-- minute
-- global
from perSecondRequests
select startTime, meta_clientType, str:concat(facetStartTime, '_', consumerKey, '_', context, '_', 
	api_version, '_', userId, '_', hostName, '_', apiPublisher, '_', resourceTemplate, '_', method, '_', userAgent, '_', clientIp) 
as uniqueId, year, month, day, hour, minute, second, consumerKey, context, api_version, api, version, 
requestTime, userId, hostName, apiPublisher, total_request_count, resourceTemplate, method, applicationName, 
tenantDomain, userAgent, resourcePath, request, applicationId, tier, throttledOut, clientIp, applicationOwner
insert into perSecondRequestsWithUniqueId;

from perSecondRequestsWithUniqueId#window.uniqueExternalTimeBatch(uniqueId, startTime, 60 sec, 59999 millisec, 60 sec, true)
select startTime, meta_clientType, consumerKey, context, api_version, api, version,
max(requestTime) as requestTime, userId, hostName, apiPublisher, sum(total_request_count) as total_request_count, 
resourceTemplate, method, applicationName, tenantDomain, userAgent, resourcePath, request, applicationId, tier, 
throttledOut, clientIp, applicationOwner
group by consumerKey, context, api_version, userId, hostName, apiPublisher, resourceTemplate, method, userAgent, clientIp  
insert into perMinuteRequestsTemp;

from perMinuteRequestsTemp
select meta_clientType, time:extract(startTime, 'year') as year, time:extract(startTime, 'month') as month, 
time:extract(startTime, 'day') as day, time:extract(startTime, 'hour') as hour, 
time:extract(startTime, 'minute') as minute,
consumerKey, context, api_version, api, version, 
requestTime, userId, hostName, apiPublisher, total_request_count, 
resourceTemplate, method, applicationName, tenantDomain, userAgent, resourcePath, request, applicationId, 
tier, throttledOut, clientIp, applicationOwner, startTime as _timestamp
insert into perMinuteRequests;

-- api_version
from perSecondPerApiRequests
select startTime, str:concat(facetStartTime, '_', api_version) 
as uniqueId, api_version, tenantDomain, total_request_count
insert into perSecondPerApiRequestsWithUniqueId;

from perSecondPerApiRequestsWithUniqueId#window.uniqueExternalTimeBatch(uniqueId, startTime, 60 sec, 59999 millisec, 10 sec, true)
select api_version, tenantDomain, sum(total_request_count) as requestsPerMinPerApi
group by api_version, tenantDomain
insert into requestPerMinPerApiStream;

-- consumer key
from perSecondPerKeyRequests
select startTime, str:concat(facetStartTime, '_', consumerKey) 
as uniqueId, consumerKey, total_request_count
insert into perSecondPerKeyRequestsWithUniqueId;

from perSecondPerKeyRequestsWithUniqueId#window.uniqueExternalTimeBatch(uniqueId, startTime, 60 sec, 59999 millisec, 10 sec)
select consumerKey, sum(total_request_count) as requestsPerMinPerKey
group by consumerKey 
insert into requestsPerMinPerKeyStream;

-- state stream
from requests
select context,method,resourceTemplate, tenantDomain, str:concat(method,' ',context,' ',resourceTemplate) as state, userId as userId, 
time:dateFormat(requestTime, 'yyyy-MM-dd HH:mm:ss') as requestTimestamp, consumerKey, applicationName, applicationOwner 
insert into stateStream;

-- responses

-- second
-- global
from responses#window.externalTimeBatch(eventTime, 1 sec, 0, 10 sec, true)
select eventTime as startTime, meta_clientType, context, api_version, api, resourceTemplate, version,
tenantDomain, hostName, apiPublisher, destination, consumerKey, resourcePath, method, response, 
avg(responseTime) as responseTime, avg(serviceTime) as serviceTime, avg(backendTime) as backendTime, 
username, max(eventTime) as eventTime, applicationName, applicationId, cacheHit, avg(responseSize) as responseSize, 
protocol, responseCode, count() as total_response_count 
group by consumerKey, context, api_version, username, resourceTemplate, hostName, apiPublisher, method, destination  
insert into perSecondResponsesTemp;

from perSecondResponsesTemp
select meta_clientType, (startTime - (startTime % 1000)) as startTime, convert((startTime - (startTime % 1000)), 'string') as facetStartTime,
time:extract(startTime, 'year') as year, 
time:extract(startTime, 'month') as month, time:extract(startTime, 'day') as day, 
time:extract(startTime, 'hour') as hour, time:extract(startTime, 'minute') as minute, 
time:extract(startTime, 'second') as second, 
context, api_version, api, resourceTemplate, version, 
tenantDomain, hostName, apiPublisher, destination, consumerKey, resourcePath, method, response, 
responseTime, serviceTime, backendTime, 
username, eventTime, applicationName, applicationId, cacheHit, responseSize, 
protocol, responseCode, total_response_count 
insert into perSecondResponses;

-- api_version
from responses#window.externalTimeBatch(eventTime, 1 sec, 0, 10 sec, true)
select eventTime as startTime, api_version, tenantDomain, count() as total_response_count
group by api_version, tenantDomain
insert into perSecondPerApiResponsesTemp;

from perSecondPerApiResponsesTemp
select (startTime - (startTime % 1000)) as startTime, convert((startTime - (startTime % 1000)), 'string') as facetStartTime,
api_version, tenantDomain, total_response_count
insert into perSecondPerApiResponses;

-- minute
-- global
from perSecondResponses
select startTime, meta_clientType, str:concat(facetStartTime, '_', consumerKey, '_', context, '_', api_version, 
	'_', username, '_', hostName, '_', apiPublisher, '_', resourceTemplate, '_', method, '_', destination) as uniqueId, 
year, month, day, hour, minute, second, context, api_version, api, resourceTemplate, version, tenantDomain, 
hostName, apiPublisher, destination, consumerKey, resourcePath, method, response, responseTime, serviceTime, 
backendTime, username, eventTime, applicationName, applicationId, cacheHit, responseSize, protocol, responseCode, 
total_response_count
insert into perSecondResponsesWithUniqueId;

from perSecondResponsesWithUniqueId#window.uniqueExternalTimeBatch(uniqueId, startTime, 60 sec, 59999 millisec, 60 sec, true)
select startTime, meta_clientType, context, api_version, api, resourceTemplate, version, tenantDomain,
hostName, apiPublisher, destination, consumerKey, resourcePath, method, response, avg(responseTime) as responseTime, 
avg(serviceTime) as serviceTime, avg(backendTime) as backendTime, username, max(eventTime) as eventTime, applicationName, 
applicationId, cacheHit, max(responseSize) as responseSize, protocol, responseCode, 
sum(total_response_count) as total_response_count
group by consumerKey, context, api_version, username, resourceTemplate, hostName, apiPublisher, method, destination    
insert into perMinuteResponsesTemp;

from perMinuteResponsesTemp
select meta_clientType, time:extract(startTime, 'year') as year, time:extract(startTime, 'month') as month, 
time:extract(startTime, 'day') as day, time:extract(startTime, 'hour') as hour, 
time:extract(startTime, 'minute') as minute,
context, api_version, api, resourceTemplate, version, tenantDomain, 
hostName, apiPublisher, destination, consumerKey, resourcePath, method, response, math:round(responseTime) as responseTime, 
math:round(serviceTime) as serviceTime, math:round(backendTime) as backendTime, username, eventTime, applicationName, 
applicationId, cacheHit, math:round(responseSize) as responseSize, protocol, responseCode, total_response_count, startTime as _timestamp
insert into perMinuteResponses;

-- api_version
from perSecondPerApiResponses
select startTime, str:concat(facetStartTime, '_', api_version) 
as uniqueId, api_version, tenantDomain, total_response_count
insert into perSecondPerApiResponsesWithUniqueId;

from perSecondPerApiResponsesWithUniqueId#window.uniqueExternalTimeBatch(uniqueId, startTime, 60 sec, 59999 millisec, 10 sec, true)
select api_version, tenantDomain, sum(total_response_count) as responsesPerMinPerApi
group by api_version, tenantDomain
insert into responsePerMinPerApiStream;

-- execution times

-- second

from executionTimes#window.externalTimeBatch(eventTime, 1 sec, 0, 10 sec, true)
select eventTime as startTime, meta_clientType, context, api_version, api,
tenantDomain, apiPublisher, avg(apiResponseTime) as apiResponseTime, avg(securityLatency) as securityLatency,
avg(throttlingLatency) as throttlingLatency, avg(requestMediationLatency) as requestMediationLatency,
avg(responseMediationLatency) as responseMediationLatency, avg(backendLatency) as backendLatency,
avg(otherLatency) as otherLatency
group by context, api_version, tenantDomain, apiPublisher 
insert into perSecondExecutionTimesTemp;

from perSecondExecutionTimesTemp
select meta_clientType, (startTime - (startTime % 1000)) as startTime, convert((startTime - (startTime % 1000)), 'string') as facetStartTime,
time:extract(startTime, 'year') as year, 
time:extract(startTime, 'month') as month, time:extract(startTime, 'day') as day, 
time:extract(startTime, 'hour') as hour, time:extract(startTime, 'minute') as minute, 
time:extract(startTime, 'second') as second, 
context, api_version, api, tenantDomain, apiPublisher, apiResponseTime, securityLatency,
throttlingLatency, requestMediationLatency, responseMediationLatency, backendLatency, otherLatency,
startTime as eventTime, startTime as _timestamp
insert into perSecondExecutionTimes;

-- minute

from perSecondExecutionTimes
select startTime, meta_clientType, str:concat(facetStartTime, '_', context, '_', 
	api_version, '_', apiPublisher, '_', tenantDomain) 
as uniqueId, year, month, day, hour, minute, second, context, api_version, api, tenantDomain, apiPublisher, apiResponseTime, securityLatency,
throttlingLatency, requestMediationLatency, responseMediationLatency, backendLatency, otherLatency
insert into perSecondExecutionTimeWithUniqueId;

from perSecondExecutionTimeWithUniqueId#window.uniqueExternalTimeBatch(uniqueId, startTime, 60 sec, 59999 millisec, 10 sec, true)
select startTime, meta_clientType, context, api_version, api,
tenantDomain, apiPublisher, avg(apiResponseTime) as apiResponseTime, avg(securityLatency) as securityLatency,
avg(throttlingLatency) as throttlingLatency, avg(requestMediationLatency) as requestMediationLatency,
avg(responseMediationLatency) as responseMediationLatency, avg(backendLatency) as backendLatency,
avg(otherLatency) as otherLatency
group by context, api_version, tenantDomain, apiPublisher  
insert into perMinuteExecutionTimeTemp;

from perMinuteExecutionTimeTemp
select meta_clientType, time:extract(startTime, 'year') as year, time:extract(startTime, 'month') as month, 
time:extract(startTime, 'day') as day, time:extract(startTime, 'hour') as hour, 
time:extract(startTime, 'minute') as minute,
context, api_version, api, tenantDomain, apiPublisher, apiResponseTime, securityLatency,
throttlingLatency, requestMediationLatency, responseMediationLatency, backendLatency, otherLatency,
startTime as eventTime, startTime as _timestamp
insert into perMinuteExecutionTimes;