create table weight.weight (
        measured_at              timestamp with time zone primary key default now(),
        value                    numeric(4,1) not null
);

create table weight.bp (
       measured_at               timestamp with time zone primary key default now(),
       systolic                  numeric(3, 0) not null,
       diastolic                 numeric(3, 0) not null,
       kind                      text check (kind in ('standard', 'home', 'doctor')) default 'standard' not null
);

create table weight.pressure_medication (
       taken_at                  timestamp with time zone primary key default now(),
       dose_mg                   numeric(3, 1) not null
);

create schema zqxjk;

create view zqxjk.weight as (
        select to_char(measured_at, 'YYYY-MM-DD"T"HH24:MI:SSOF') as _id,
               measured_at || ' ' || value as _display,
               value
        from   weight.weight
);

create view zqxjk.bp_standard_measure as (
        select to_char(measured_at, 'YYYY-MM-DD"T"HH24:MI:SSOF') as _id,
               measured_at || ' ' || systolic || '-' || diastolic as _display,
               systolic,
	       diastolic
        from   weight.bp
);

create view zqxjk.pressure_medication as (
        select to_char(taken_at, 'YYYY-MM-DD"T"HH24:MI:SSOF') as _id,
               taken_at || ' ' || dose_mg || 'mg' as _display,
               dose_mg
        from   weight.pressure_medication
);

create view zqxjk.admin_weight as (
        select to_char(measured_at, 'YYYY-MM-DD"T"HH24:MI:SSOF') as _id,
               measured_at || ' ' || value as _display,
               measured_at,
               value
        from   weight.weight
);

create table zqxjk._tables (
        name                    text primary key,
        default_sort            text[]
);

insert into zqxjk._tables(name, default_sort) values ('weight', '{"_id", "desc"}');
insert into zqxjk._tables(name, default_sort) values ('admin_weight', '{"_id", "desc"}');
insert into zqxjk._tables(name, default_sort) values ('bp_standard_measure', '{"_id", "desc"}');
insert into zqxjk._tables(name, default_sort) values ('pressure_medication', '{"_id", "desc"}');

create schema reporting;

create view reporting.weekly_blood_pressure as (
       with limits as (
       	    select date_trunc('week', min(taken_at)) as min_week,
	           date_trunc('week', max(taken_at)) as max_week
	    from   weight.pressure_medication
       )
select    week.week,
          min(systolic) as min_systolic,
          max(systolic) as max_systolic,
          avg(systolic) as avg_systolic,
          min(diastolic) as min_diastolic,
          max(diastolic) as max_diastolic,
          avg(diastolic) as avg_diastolic
from      generate_series((select min_week from limits), (select max_week from limits), '7 days') as week
left join weight.bp on date_trunc('week', week.week) = date_trunc('week', bp.measured_at) and bp.kind = 'standard'
group by  week.week order by week.week);

create view reporting.weekly_medication as (
       with limits as (
       	    select date_trunc('week', min(taken_at)) as min_week,
	           date_trunc('week', max(taken_at)) as max_week
	    from   weight.pressure_medication
       )
select    week.week,
          sum(dose_mg)/7 as average_daily_dose
from      generate_series((select min_week from limits), (select max_week from limits), '7 days') as week
left join weight.pressure_medication on date_trunc('week', week.week) = date_trunc('week', pressure_medication.taken_at)
group by  week.week order by week.week);

create view reporting.weekly_summary as select * from reporting.weekly_blood_pressure full join reporting.weekly_medication using(week);
