/*
 * Copyright (c) 2018 WSO2 Inc. (http://www.wso2.org) All Rights Reserved.
 *
 * WSO2 Inc. licenses this file to you under the Apache License,
 * Version 2.0 (the "License"); you may not use this file except
 * in compliance with the License.
 * You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 */

@App:name("IS_ANALYTICS_AUTHENTICATION_OVERALL")
@App:description("Handle Event from IS and save data in a table for Overall Authentication")

-- Read data from OverallAuthenticationProcessedStream (Sinked from IsAnalytics_Auth_Common)
@source(type='inMemory', topic='IsAnalytics.Authentication.Common.OutputSinkFromOverallAuthenticationProcessedStream', @map(type='passThrough'))
define stream OverallAuthenticationProcessedStream (
    meta_tenantId int,
    contextId string,
    eventId string,
    eventType string,
    username string,
    localUsername string,
    userStoreDomain string,
    tenantDomain string,
    remoteIp string,
    region string,
    inboundAuthType string,
    serviceProvider string,
    rememberMeEnabled bool,
    forceAuthEnabled bool,
    passiveAuthEnabled bool,
    rolesCommaSeparated string,
    authenticationStep string,
    identityProvider string,
    authenticationSuccess bool,
    authStepSuccess bool,
    stepAuthenticator string,
    isFirstLogin bool,
    identityProviderType string,
    timestamp long
);

define trigger TablePurgingTriggerStream at '0 0 23 * * ?';

-- TABLE DEFINITIONS

-- Overall Authentication Table
@store(type='rdbms', datasource='IS_ANALYTICS_DB', 'field.length=serviceProvider:100, identityProvider:100, userStoreDomain:50, region:45')
@primaryKey('meta_tenantId', 'eventId', 'eventType')
define table OverallAuthTable (
    meta_tenantId int,
    contextId string,
    eventId string,
    eventType string,
    username string,
    localUsername string,
    userStoreDomain string,
    tenantDomain string,
    remoteIp string,
    region string,
    inboundAuthType string,
    serviceProvider string,
    rememberMeEnabled bool,
    forceAuthEnabled bool,
    passiveAuthEnabled bool,
    rolesCommaSeparated string,
    authenticationStep string,
    identityProvider string,
    authenticationSuccess bool,
    authStepSuccess bool,
    stepAuthenticator string,
    isFirstLogin bool,
    identityProviderType string,
    utcTime string,
    timestamp long
);

-- QUERIES

-- Update OverallAuthTable
from OverallAuthenticationProcessedStream
select
    meta_tenantId,
    contextId,
    eventId,
    eventType,
    username,
    localUsername,
    userStoreDomain,
    tenantDomain,
    remoteIp,
    region,
    inboundAuthType,
    serviceProvider,
    rememberMeEnabled,
    forceAuthEnabled,
    passiveAuthEnabled,
    rolesCommaSeparated,
    authenticationStep,
    identityProvider,
    authenticationSuccess,
    authStepSuccess,
    stepAuthenticator,
    isFirstLogin,
    identityProviderType,
    time:dateFormat(timestamp, "MMM dd, yyyy hh:mm:ss a zzz") as utcTime,
    timestamp
group by
    meta_tenantId,
    contextId,
    eventId,
    eventType,
    region
update or insert into OverallAuthTable
on
    OverallAuthTable.meta_tenantId==meta_tenantId
    and OverallAuthTable.contextId==contextId
    and OverallAuthTable.eventType==eventType
    and OverallAuthTable.eventId==eventId;

from TablePurgingTriggerStream
select time:dateSub(triggered_time, 1, 'year') as purgingTime
delete OverallAuthTable
on OverallAuthTable.timestamp < purgingTime;
