/*view для получения всех сигналов за последние 90-дней*/
----------------------------------------------------------------------------------
do $do$
begin
if not exists(
select
    schemaname as schema_name,
    matviewname as view_name,
    matviewowner as owner,
    ispopulated as is_populated,
    definition
from
    pg_matviews
order by
    schema_name,
    view_name

)
then create materialized view signals_last_3_months as
select
    *
from
    signal_log as sl
where
    sl.datetime > (
    select
        cast((select now() - interval '90 DAYS') as date)) with no data;
end if;
end $do$;
/*необходимо выполнение по расписанию(1 раз в день)*/
----------------------------------------------------------------------------------
refresh materialized view  signals_last_3_months;
----------------------------------------------------------------------------------
-- signal_type = 0 (охранный сигнал)
-- signal_type = 1 (тестовый сигнал)
-- signal_type = 2 (охранный сигнал или тестовый сигнал)
-- любое другое число (все сигналы)
create or replace function get_lost_singals_info(start_date date, end_date date, signal_type int)
 returns table
 (
    id bigint,
    object_number int,
    city_id int,
    device_number int,
    firmware int,
    securige_type int,
    protocol_type int,
    device_system int,
    gbr_timer int,
    gbr_id int,
    kvituserid int,
    unicom int,
    sectionbarierbollid int,
    num_losts bigint
 ) as $$
 declare
    query_to_execute text;
begin
    query_to_execute:= '
        select  max(sl1.id), sl1.object_number as object_number, max(city_id) as city_id, max(sl1.device_number) as device_number, max(sl1.firmware) as firmware
        ,max(sl1.securige_type) as securige_type, max(sl1.protocol_type) as protocol_type, max(sl1.device_system) as device_system
        ,max(sl1.gbr_timer) as gbr_timer,max(sl1.gbr_id) as gbr_id, max(sl1.kvituserid) as kvituserid
        ,max(sl1.unicom) as unicom, max(sl1.sectionbarierbollid) as sectionbarierbollid, count(sl1.id) as num_losts
        from signals_last_3_months sl1
        where -signal_type-
        sl1.device_number is not null
        and sl1.datetime between ''-start_date-'' and ''-end_date-''
        group by sl1.object_number, sl1.device_number
        having count(sl1.id) > 0';

    if signal_type = 0 then
        query_to_execute:= REPLACE(query_to_execute, '-signal_type-', 'sl1.propazhaohrannogosignala = true and' );
    elsif signal_type = 1 then
        query_to_execute:= REPLACE(query_to_execute, '-signal_type-', 'sl1.propazhatestovogosignala = true and' );
    elsif signal_type = 2 then
        query_to_execute:= REPLACE(query_to_execute, '-signal_type-', 'sl1.propazhatestovogosignala = true or sl1.propazhaohrannogosignala = true and' );
    else
        query_to_execute:= REPLACE(query_to_execute, '-signal_type-', '' );
    end if;

    query_to_execute:= REPLACE(query_to_execute, '-start_date-', to_char(start_date, 'YYYY-MM-DD'));
    query_to_execute:= REPLACE(query_to_execute, '-end_date-', to_char(end_date, 'YYYY-MM-DD'));

    return query
    execute query_to_execute;
END; $$
LANGUAGE 'plpgsql';
----------------------------------------------------------------------------------