CREATE OR REPLACE DEFINER=`system`@`localhost` PROCEDURE `procPduPortPowerInformationSummary`(IN varSummaryType varchar(10),
                                                                                              IN varStartDate date,
                                                                                              IN varEndDate date)
BEGIN

    DECLARE v_procedureName varchar(64) default 'procPduPortPowerInformationSummary';

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
            GET DIAGNOSTICS CONDITION 1
                @d1 = RETURNED_SQLSTATE, @d2 = MESSAGE_TEXT;
            call log(concat(@d1, ' - ', @d2), v_procedureName, 'E');
        END;

    IF varSummaryType is null THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'ERROR: procPduPortPowerInformationSummary: varSummaryType parameter was not specified!';
    END IF;

    if upper(varSummaryType) = 'DAILY' then

        if varStartDate is null then
            set @startDate = CURDATE() - interval 1 DAY;
        else
            SET @startDate = varStartDate;
        end if;

        if varEndDate is null then
            set @endDate = CURDATE();
        else
            SET @endDate = varEndDate;
        end if;

    end if;

    if upper(varSummaryType) = 'HOURLY' then

        if varStartDate is null then
            set @startDate = date_format(current_timestamp - interval 1 hour, '%Y-%m-%d %H:00:00');
        else
            SET @startDate = date_format(varStartDate, '%Y-%m-%d %H:00:00');
        end if;

        if varEndDate is null then
            set @endDate = date_format(current_timestamp, '%Y-%m-%d %H:00:00');
        else
            SET @endDate = date_format(varEndDate, '%Y-%m-%d %H:00:00');
        end if;

    end if;

    call log(concat(varSummaryType, ' Summarizing of Pdu Port Power Information between: ', @startDate, ' and ', @endDate), v_procedureName, 'I');


    if upper(varSummaryType) = 'DAILY' then

        call log(concat('Deleting ', varSummaryType, ' data between: ', @startDate, ' and ', @endDate), v_procedureName, 'I');
        delete
        from metawan_history.dailyPduPortPowerInformation
        where reportDate >= @startDate and reportDate < @endDate;

        call log(concat(varSummaryType, ' Summarizing and inserting data between: ', @startDate, ' and ', @endDate), v_procedureName, 'I');
        insert into metawan_history.dailyPduPortPowerInformation (reportDate, deviceId, installationId, serialNumber,
                                                                  port,
                                                                  voltage, current, power, uptime)
            (SELECT date(ppi.receivedTimestamp) as `reportDate`,
                    ppi.deviceId,
                    i.id                        as `installationId`,
                    serialNumber,
                    ppi.port,
                    round(avg(ppi.voltage), 3)  as `avgVoltage`,
                    round(avg(ppi.current), 3)  as `avgCurrent`,
                    round(avg(ppi.power), 3)    as `avgPower`,
                    max(ppi.uptime)             as `maxUptime`
             FROM metawan_mqtt.pduPortPowerInformation ppi
                      left join opmon.installation i on i.deviceId = ppi.deviceId and i.terminationDate is null
             where ppi.receivedTimestamp >= @startDate
               and ppi.receivedTimestamp < @endDate
             group by reportDate, ppi.deviceId, ppi.port);
    end if;

    if upper(varSummaryType) = 'HOURLY' then

        call log(concat('Deleting ', varSummaryType, ' data between: ', @startDate, ' and ', @endDate), v_procedureName, 'I');
        delete
        from metawan_history.hourlyPduPortPowerInformation
        where reportDate >= @startDate and reportDate < @endDate;

        call log(concat(varSummaryType, ' Summarizing and inserting data between: ', @startDate, ' and ', @endDate), v_procedureName, 'I');
        insert into metawan_history.hourlyPduPortPowerInformation (reportDate, deviceId, installationId,
                                                                   serialNumber, port, voltage, current, power, uptime)
            (SELECT date_format(ppi.receivedTimestamp, '%Y-%m-%d %H:00:00') as `reportDate`,
                    ppi.deviceId,
                    i.id                                                    as `installationId`,
                    serialNumber,
                    ppi.port,
                    round(avg(ppi.voltage), 3)                              as `avgVoltage`,
                    round(avg(ppi.current), 3)                              as `avgCurrent`,
                    round(avg(ppi.power), 3)                                as `avgPower`,
                    max(ppi.uptime)                                         as `maxUptime`
             FROM metawan_mqtt.pduPortPowerInformation ppi
                      left join opmon.installation i on i.deviceId = ppi.deviceId and i.terminationDate is null
             where ppi.receivedTimestamp >= @startDate
               and ppi.receivedTimestamp < @endDate
             group by reportDate, ppi.deviceId, ppi.port);
    end if;

    call log(concat(varSummaryType, ' Summarizing Completed for data between: ', @startDate, ' and ', @endDate), v_procedureName, 'I');

END;