create or replace view sim_info_view
as
select
    si.id_sim_info,
    si.id_log_info,
    date_format(si.begin_time, '%Y-%m-%d') as 'sim_date',
    date_format(si.begin_time, '%T') as 'begin_time',
    date_format(si.end_time, '%T') as 'end_time',
    ss.id_sim_stat,
    ss.name as 'sim_state_name',
    si.id_batt_left,
    si.id_batt_right,
    si.name as 'sim_name',
    replace (
        concat(

            '[',
            (case when sum(case when sd.current is not null then 1 else 0 end) - count(*) = 0 then '"sim_current",' else '' end),
            (case when sum(case when sd.batt_left_volt is not null then 1 else 0 end) - count(*) = 0 then '"sim_batt_left_volt",' else '' end),
            (case when sum(case when sd.batt_right_volt is not null then 1 else 0 end) - count(*) = 0 then '"sim_batt_right_volt",' else '' end),
            (case when sum(case when sd.batt_left_temp is not null then 1 else 0 end) - count(*) = 0 then '"sim_batt_left_temp",' else '' end),
            (case when sum(case when sd.batt_right_temp is not null then 1 else 0 end) - count(*) = 0 then '"sim_batt_right_temp",' else '' end),
            (case when sum(case when ld.motor_curr is not null then 1 else 0 end) - count(*) = 0 then '"log_motor_curr",' else '' end),
            (case when sum(case when ld.batt_left_volt is not null then 1 else 0 end) - count(*) = 0 then '"log_batt_left_volt",' else '' end),
            (case when sum(case when ld.batt_right_volt is not null then 1 else 0 end) - count(*) = 0 then '"log_batt_right_volt",' else '' end),
            ']'

        ),
        ',]',
        ']'
    ) as 'no_empty_data'
from
    sim_data sd
        join sim_info si
            using(id_sim_info)
            join sim_stat ss
                using(id_sim_stat)
                join log_data ld
                    using(id_log_data)
where
    si.id_sim_info > 0
group by
    sd.id_sim_info
order by
    sd.id_sim_info
desc
;