<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Analytics>
    <Editable>true</Editable>
    <Name>AndroidFrequentContactsScript</Name>
    <Script>        CREATE TEMPORARY TABLE AndroidFrequentCallData USING CarbonAnalytics OPTIONS(tableName "IOT_PER_DEVICE_STREAM_ANDROID_CALL_STATS", incrementalParams "IOT_PER_DEVICE_STREAM_ANDROID_CALL_STATS, DAY");

        CREATE TEMPORARY TABLE AndroidFrequentSmsData USING CarbonAnalytics OPTIONS(tableName "IOT_PER_DEVICE_STREAM_ANDROID_SMS_STATS", incrementalParams "IOT_PER_DEVICE_STREAM_ANDROID_SMS_STATS, DAY");
        
        CREATE TEMPORARY TABLE Android_Frequent_Contacts_Per_Day USING CarbonAnalytics
        OPTIONS (tableName "Android_Frequent_Contacts_Per_Day",
        schema "owner STRING -i, deviceId STRING -i, number STRING -i, callCount INT -i, smsCount INT -i, total INT -i, year INT -i, month INT -i, day INT -i, _timestamp LONG -i",
        primaryKeys "deviceId, owner, number, year, month, day",
        mergeSchema "false", incrementalParams "Android_Frequent_Contacts_Per_Day, MONTH");

        CREATE TEMPORARY TABLE Android_Frequent_Contacts_Per_Month USING CarbonAnalytics
        OPTIONS (tableName "Android_Frequent_Contacts_Per_Month",
        schema "owner STRING -i, deviceId STRING -i, number STRING -i, callCount INT -i, smsCount INT -i, total INT -i, year INT -i, month INT -i, _timestamp LONG -i",
        primaryKeys "deviceId, owner, number, year, month",
        mergeSchema "false", incrementalParams "Android_Frequent_Contacts_Per_Month, YEAR");

        CREATE TEMPORARY TABLE Android_Frequent_Contacts_Per_Year USING CarbonAnalytics
        OPTIONS (tableName "Android_Frequent_Contacts_Per_Year",
        schema "owner STRING -i, deviceId STRING -i, number STRING -i, callCount INT -i, smsCount INT -i, total INT -i, year INT -i",
        primaryKeys "deviceId, owner, number, year",
        mergeSchema "false");

        INSERT INTO TABLE Android_Frequent_Contacts_Per_Day
        SELECT IF(callData.owner is null,smsData.owner,callData.owner) as owner, IF(callData.deviceId is null,smsData.deviceId,callData.deviceId) as deviceId, IF(callData.number is null,smsData.number,callData.number) as number, IF(callData.callCount is null,0,callData.callCount) as callCount, IF(smsData.smsCount is null,0,smsData.smsCount) as smsCount, (IF(callData.callCount is null,0,callData.callCount) + IF(smsData.smsCount is null,0,smsData.smsCount)) as total, IF(callData.year is null,smsData.year,callData.year) as year, IF(callData.month is null,smsData.month,callData.month) as month, IF(callData.day is null,smsData.day,callData.day) as day, getDateStartingTime(IF(callData.year is null,smsData.year,callData.year), IF(callData.month is null,smsData.month,callData.month), IF(callData.day is null,smsData.day,callData.day)) as _timestamp
        FROM (SELECT meta_owner as owner, meta_deviceId as deviceId, number, count(*) as callCount, year, month, day
        FROM AndroidFrequentCallData
        GROUP BY meta_deviceId, meta_owner, number, year, month, day) callData FULL OUTER JOIN
        (SELECT meta_owner as owner, meta_deviceId as deviceId, number, count(*) as smsCount, year, month, day
        FROM AndroidFrequentSmsData
        GROUP BY meta_deviceId, meta_owner, number, year, month, day) smsData ON smsData.owner = callData.owner AND smsData.deviceId = callData.deviceId  AND smsData.number = callData.number AND smsData.year = callData.year AND smsData.month = callData.month AND smsData.day = callData.day
        ORDER BY total DESC;

        INCREMENTAL_TABLE_COMMIT IOT_PER_DEVICE_STREAM_ANDROID_CALL_STATS;
        INCREMENTAL_TABLE_COMMIT IOT_PER_DEVICE_STREAM_ANDROID_SMS_STATS;

        INSERT INTO TABLE Android_Frequent_Contacts_Per_Month
        SELECT owner, deviceId, number, sum(callCount) as callCount, sum(smsCount) as smsCount, sum(total) as total, year, month, getMonthStartingTime(year, month) as _timestamp
        FROM Android_Frequent_Contacts_Per_Day
        GROUP BY deviceId, owner, number, year, month
        ORDER BY total DESC;

        INCREMENTAL_TABLE_COMMIT Android_Frequent_Contacts_Per_Month; 

        INSERT INTO TABLE Android_Frequent_Contacts_Per_Year
        SELECT owner, deviceId, number, sum(callCount) as callCount, sum(smsCount) as smsCount, sum(total) as total, year
        FROM Android_Frequent_Contacts_Per_Month
        GROUP BY deviceId, owner, number, year
        ORDER BY total DESC;
    </Script>
    <CronExpression>0 0/5 * * * ?</CronExpression>
</Analytics>
