drop view s_all;
create view s_all as
select time AT TIME ZONE 'UTC-2' as time,
	addr,
	(json->'sensor_type')::int  as sensor_type,
	(json->'sensor_name')::text as sensor_name,
	(json->>'battery')::float as bettery,
	(json->>'battery_percent')::float as battery_percent,
	(json->'counter')::int as counter
from eg5120
;
grant select on s_all to gread ;

-- 1 Temperature/Humidity
drop view s_1;
create view s_1 as
select time AT TIME ZONE 'UTC-2' as time,
	addr,
	(json->'sensor_type')::int  as sensor_type,
	(json->'sensor_name')::text as sensor_name,
	(json->'sensor_data'->'temperature')::float as temperature,
	(json->'sensor_data'->'humidity')::float as humidity,
	(json->>'battery')::float as bettery,
	(json->>'battery_percent')::float as battery_percent,
	(json->'counter')::int as counter
from eg5120
where sensor_type = 1;
;
grant select on s_1 to gread ;

-- 2 2 Channel Push Notification
drop view s_2;
create view s_2 as
select time AT TIME ZONE 'UTC-2' as time,
	addr,
	(json->'sensor_type')::int  as sensor_type,
	(json->'sensor_name')::text as sensor_name,
	(json->'sensor_data'->'input_1')::int as input_1,
	(json->'sensor_data'->'input_2')::int as input_2,
	(json->>'battery')::float as bettery,
	(json->>'battery_percent')::float as battery_percent,
	(json->'counter')::int as counter
from eg5120
where sensor_type = 2;
;
grant select on s_2 to gread ;

-- 29 Linear Displacement
drop view s_29;
create view s_29 as
select time AT TIME ZONE 'UTC-2' as time,
	addr,
	(json->'sensor_type')::int  as sensor_type,
	(json->'sensor_name')::text as sensor_name,
	(json->'sensor_data'->'adc')::int as adc,
	(json->'sensor_data'->'position')::float as position,
	(json->>'battery')::float as bettery,
	(json->>'battery_percent')::float as battery_percent,
	(json->'counter')::int as counter
from eg5120
where sensor_type = 29;
;
grant select on s_29 to gread ;

-- 30 Structural Monitoring
drop view s_30;
create view s_30 as
select time AT TIME ZONE 'UTC-2' as time,
	addr,
	(json->'sensor_type')::int  as sensor_type,
	(json->'sensor_name')::text as sensor_name,
	(json->'sensor_data'->'adc')::int as adc,
	(json->'sensor_data'->'position')::float as position,
	(json->>'battery')::float as bettery,
	(json->>'battery_percent')::float as battery_percent,
	(json->'counter')::int as counter
from eg5120
where sensor_type = 30;
;
grant select on s_30 to gread ;

-- 47 Wireless Tilt Sensor
drop view s_47;
create view s_47 as
select time AT TIME ZONE 'UTC-2' as time,
	addr,
	(json->'sensor_type')::int  as sensor_type,
	(json->'sensor_name')::text as sensor_name,
	(json->'sensor_data'->'Roll')::float as Roll,
	(json->'sensor_data'->'Pitch')::float as Pitch,
	(json->>'battery')::float as bettery,
	(json->>'battery_percent')::float as battery_percent,
	(json->'counter')::int as counter
from eg5120
where sensor_type = 47;
;
grant select on s_47 to gread ;

-- 81 Two Channel Vibration Plus
drop view s_81;
create view s_81 as
select time AT TIME ZONE 'UTC-2' as time,
	addr,
	(json->'sensor_type')::int  as sensor_type,
	(json->'sensor_name')::text as sensor_name,
	(json->'sensor_data'->'s1_temperature')::float as s1_temperature,
	(json->'sensor_data'->'s2_temperature')::float as s2_temperature,
	(json->'sensor_data'->'x1_max_ACC_G')::float as x1_max_ACC_G,
	(json->'sensor_data'->'x1_rms_ACC_G')::float as x1_rms_ACC_G,
	(json->'sensor_data'->'x2_max_ACC_G')::float as x2_max_ACC_G,
	(json->'sensor_data'->'x2_rms_ACC_G')::float as x2_rms_ACC_G,
	(json->'sensor_data'->'y1_max_ACC_G')::float as y1_max_ACC_G,
	(json->'sensor_data'->'y1_rms_ACC_G')::float as y1_rms_ACC_G,
	(json->'sensor_data'->'y2_max_ACC_G')::float as y2_max_ACC_G,
	(json->'sensor_data'->'y2_rms_ACC_G')::float as y2_rms_ACC_G,
	(json->'sensor_data'->'z1_max_ACC_G')::float as z1_max_ACC_G,
	(json->'sensor_data'->'z1_rms_ACC_G')::float as z1_rms_ACC_G,
	(json->'sensor_data'->'z2_max_ACC_G')::float as z2_max_ACC_G,
	(json->'sensor_data'->'z2_rms_ACC_G')::float as z2_rms_ACC_G,
	(json->'sensor_data'->'x1_peak_one_Hz')::float as x1_peak_one_Hz,
	(json->'sensor_data'->'x1_peak_two_Hz')::float as x1_peak_two_Hz,
	(json->'sensor_data'->'x2_peak_one_Hz')::float as x2_peak_one_Hz,
	(json->'sensor_data'->'x2_peak_two_Hz')::float as x2_peak_two_Hz,
	(json->'sensor_data'->'y1_peak_one_Hz')::float as y1_peak_one_Hz,
	(json->'sensor_data'->'y1_peak_two_Hz')::float as y1_peak_two_Hz,
	(json->'sensor_data'->'y2_peak_one_Hz')::float as y2_peak_one_Hz,
	(json->'sensor_data'->'y2_peak_two_Hz')::float as y2_peak_two_Hz,
	(json->'sensor_data'->'z1_peak_one_Hz')::float as z1_peak_one_Hz,
	(json->'sensor_data'->'z1_peak_two_Hz')::float as z1_peak_two_Hz,
	(json->'sensor_data'->'z2_peak_one_Hz')::float as z2_peak_one_Hz,
	(json->'sensor_data'->'z2_peak_two_Hz')::float as z2_peak_two_Hz,
	(json->'sensor_data'->'x1_peak_three_Hz')::float as x1_peak_three_Hz,
	(json->'sensor_data'->'x2_peak_three_Hz')::float as x2_peak_three_Hz,
	(json->'sensor_data'->'y1_peak_three_Hz')::float as y1_peak_three_Hz,
	(json->'sensor_data'->'y2_peak_three_Hz')::float as y2_peak_three_Hz,
	(json->'sensor_data'->'z1_peak_three_Hz')::float as z1_peak_three_Hz,
	(json->'sensor_data'->'z2_peak_three_Hz')::float as z2_peak_three_Hz,
	(json->'sensor_data'->'x1_displacement_mm')::float as x1_displacement_mm,
	(json->'sensor_data'->'x1_velocity_mm_sec')::float as x1_velocity_mm_sec,
	(json->'sensor_data'->'x2_displacement_mm')::float as x2_displacement_mm,
	(json->'sensor_data'->'x2_velocity_mm_sec')::float as x2_velocity_mm_sec,
	(json->'sensor_data'->'y1_displacement_mm')::float as y1_displacement_mm,
	(json->'sensor_data'->'y1_velocity_mm_sec')::float as y1_velocity_mm_sec,
	(json->'sensor_data'->'y2_displacement_mm')::float as y2_displacement_mm,
	(json->'sensor_data'->'y2_velocity_mm_sec')::float as y2_velocity_mm_sec,
	(json->'sensor_data'->'z1_displacement_mm')::float as z1_displacement_mm,
	(json->'sensor_data'->'z1_velocity_mm_sec')::float as z1_velocity_mm_sec,
	(json->'sensor_data'->'z2_displacement_mm')::float as z2_displacement_mm,
	(json->'sensor_data'->'z2_velocity_mm_sec')::float as z2_velocity_mm_sec,
	(json->>'battery')::float as bettery,
	(json->>'battery_percent')::float as battery_percent,
	(json->'counter')::int as counter
from eg5120
where sensor_type = 81
order by time
;
grant select on s_81 to gread ;

