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

        CREATE TEMPORARY TABLE Android_Location_Stats_Per_Day USING CarbonAnalytics
        OPTIONS (tableName "Android_Location_Stats_Per_Day",
        schema "owner STRING, deviceId STRING, latitude DOUBLE -i, longitude DOUBLE -i, streetName STRING, noOfVisits INT -i, year INT -i, month INT -i, day INT -i, _timestamp LONG -i",
        primaryKeys "deviceId, owner, streetName, latitude, longitude, year, month, day",
        mergeSchema "false", incrementalParams "Android_Location_Stats_Per_Day, MONTH");

        CREATE TEMPORARY TABLE Android_Location_Stats_Per_Month USING CarbonAnalytics
        OPTIONS (tableName "Android_Location_Stats_Per_Month",
        schema "owner STRING, deviceId STRING, latitude DOUBLE -i, longitude DOUBLE -i, streetName STRING, noOfVisits INT -i, year INT -i, month INT -i, _timestamp LONG -i",
        primaryKeys "deviceId, owner, streetName, latitude, longitude, year, month",
        mergeSchema "false", incrementalParams "Android_Location_Stats_Per_Month, YEAR");

        CREATE TEMPORARY TABLE Android_Location_Stats_Per_Year USING CarbonAnalytics
        OPTIONS (tableName "Android_Location_Stats_Per_Year",
        schema "owner STRING, deviceId STRING, latitude DOUBLE -i, longitude DOUBLE -i, streetName STRING, noOfVisits INT -i, year INT -i",
        primaryKeys "deviceId, owner, streetName, latitude, longitude, year",
        mergeSchema "false");

        INSERT INTO TABLE Android_Location_Stats_Per_Day
        SELECT meta_owner as owner, meta_deviceId as deviceId, latitude, longitude, streetName, count(*) as noOfVisits, year, month, day, getDateStartingTime(year, month, day) as _timestamp
        FROM AndroidLocationData
        GROUP BY year, month, day, meta_deviceId, streetName, meta_owner, latitude, longitude;
        
        INCREMENTAL_TABLE_COMMIT IOT_PER_DEVICE_STREAM_ANDROID_LOCATION_STATS;

        INSERT INTO TABLE Android_Location_Stats_Per_Month
        SELECT owner, deviceId, latitude, longitude, streetName, sum(noOfVisits) as noOfVisits, year, month, getMonthStartingTime(year, month) as _timestamp
        FROM Android_Location_Stats_Per_Day
        GROUP BY deviceId, owner, year, month, streetName, latitude, longitude;

        INCREMENTAL_TABLE_COMMIT Android_Location_Stats_Per_Month; 

        INSERT INTO TABLE Android_Location_Stats_Per_Year
        SELECT owner, deviceId, latitude, longitude, streetName, sum(noOfVisits) as noOfVisits, year
        FROM Android_Location_Stats_Per_Month
        GROUP BY deviceId, owner, year, streetName, latitude, longitude;
    </Script>
    <CronExpression>0 0/5 * * * ?</CronExpression>
</Analytics>
