CREATE OR REPLACE TABLE rel595.t3_scada_alarms_oper_mod_ots_states_cycles_2017_Events

ENGINE = Aria

  WITH t3_scada_alarms_oper_mod_ots_states_cycles_2017_aux

  AS

  (

    SELECT

      A.id_cycles,

      A.MachineID,

      A.MachineUT,

      A.ENVELOPE,

      A.Cycle_DownTime_State,

      A.Cycle_DownTime_State_Description,

      A.Cycle_DateTimeON,

      A.Cycle_DateTimeOFF,

      A.`Cycle_Time[sec]`,

      A.`Working Hours`,

      A.Cycle_Alarm_Code_OrderByAlarm,

      A.Cycle_Alarm_Description_OrderByAlarm,

      A.Cycle_First_Alarm_Code,

      A.Cycle_First_Alarm_Description,

      A.Cycle_Alarm_OrderByDateTimeON,

      A.Cycle_Alarm_Functional_Group,

      A.Cycle_Alarm_Turbine_State_Request,

      A.Cycle_Operation_Mode_Code,

      A.Cycle_Operation_Mode_Description,

      A.Cycle_Operation_Mode_Turbine_State_Request,

      A.Cycle_Final_Reset_Type,

      A.Cycle_Final_Reset_Criteria,

      A.Cycle_Final_Availability,

      A.Cycle_OT_NumOT,

      A.Cycle_OT_Description,

      A.Cycle_OT_AlarmCode,

      A.Cycle_OT_AlarmDescription,

      A.Cycle_OT_TypeCode,

      A.Cycle_OT_TypeDescription,

      A.Cycle_OT_Subtipo,

      A.Cycle_OT_LCNId,

      A.Cycle_OT_Planned,

      A.Cycle_OT_With_Consumption,

      A.Cycle_OT_Availability_Stop,

      B.Event_Code,

      B.Event_Description,

      B.DateTimeON

        AS Event_DateTimeON,

      B.DateTimeOFF

        AS Event_DateTimeOFF,

      MAX(B.DateTimeOFF) 

        OVER (PARTITION BY A.id_cycles)

        AS Event_Max_DateTimeOFF

    FROM

      rel595.t3_scada_alarms_oper_mod_ots_states_cycles_2017 AS A

      LEFT JOIN rel595.t1_scada_events_joined_dbvar_17543 AS B

        ON

          B.DateTimeON BETWEEN A.Cycle_DateTimeON AND A.Cycle_DateTimeOFF

          AND A.MachineUT = B.MachineUT

  )

    SELECT

      MachineID,

      MachineUT,

      ENVELOPE,

      Cycle_DownTime_State,

      Cycle_DownTime_State_Description,

      Cycle_DateTimeON,

      Cycle_DateTimeOFF,

      `Cycle_Time[sec]`,

      `Working Hours`,

      Cycle_Alarm_Code_OrderByAlarm,

      Cycle_Alarm_Description_OrderByAlarm,

      Cycle_First_Alarm_Code,

      Cycle_First_Alarm_Description,

      Cycle_Alarm_OrderByDateTimeON,

      Cycle_Alarm_Functional_Group,

      Cycle_Alarm_Turbine_State_Request,

      Cycle_Operation_Mode_Code,

      Cycle_Operation_Mode_Description,

      Cycle_Operation_Mode_Turbine_State_Request,

      Cycle_Final_Reset_Type,

      Cycle_Final_Reset_Criteria,

      Cycle_Final_Availability,

      Cycle_OT_NumOT,

      Cycle_OT_Description,

      Cycle_OT_AlarmCode,

      Cycle_OT_AlarmDescription,

      Cycle_OT_TypeCode,

      Cycle_OT_TypeDescription,

      Cycle_OT_Subtipo,

      Cycle_OT_LCNId,

      Cycle_OT_Planned,

      Cycle_OT_With_Consumption,

      Cycle_OT_Availability_Stop,

      CASE

        WHEN

          Event_Code IS NOT NULL

          AND NOT Cycle_Final_Reset_Criteria = 'LOCAL-WO'

          AND NOT Cycle_Alarm_Code_OrderByAlarm NOT REGEXP '903|904'

          -- Not Alarm Codes "903: Authoriz. person in turbine"

          -- nor "904:Presence of unauth. person in turbine".

        THEN

          1

        ELSE

          0

      END AS `WTG_Reset_by_SCADA_Command`,

      CASE

        WHEN

          Event_DateTimeOFF BETWEEN SUBTIME(Cycle_DateTimeOFF, '00:10:00') AND ADDTIME(Cycle_DateTimeOFF, '00:10:00')

          AND 

          (

            Event_Code IS NOT NULL

            AND NOT Cycle_Final_Reset_Criteria = 'LOCAL-WO'

            AND NOT Cycle_Alarm_Code_OrderByAlarm NOT REGEXP '903|904'

            -- Not Alarm Codes "903: Authoriz. person in turbine"

            -- nor "904:Presence of unauth. person in turbine".

          )

          AND 

          (

            ADDTIME(Cycle_DateTimeOFF, '00:30:00') < LEAD(Cycle_DateTimeON) OVER (PARTITION BY MachineID ORDER BY MachineID, Cycle_DateTimeON)

            OR (LEAD(Cycle_DateTimeON) OVER (PARTITION BY MachineID ORDER BY MachineID, Cycle_DateTimeON) IS NULL)

          )

        THEN

          1

        ELSE

          0

      END AS `WTG_SCADA_Command_Success_Offset10`,

      TIMESTAMPDIFF(MINUTE, Event_DateTimeOFF, Cycle_DateTimeOFF)

        AS DateTimeDiff_StateOFF_EventOFF,

      Event_Code,

      Event_Description,

      Event_DateTimeON,

      Event_DateTimeOFF

    FROM

      t3_scada_alarms_oper_mod_ots_states_cycles_2017_aux

    WHERE

      (Event_DateTimeOFF = Event_Max_DateTimeOFF)

      OR Event_DateTimeOFF IS NULL

    GROUP BY

      id_cycles

    ORDER BY

      MachineID,

      Cycle_DateTimeON