/* 
   This begins the NUT application logic which is implemented as SQL
   tables and triggers in order that the NUT code be independent of the
   GUI and its language, be implemented in C for performance,
   and be implemented by SQLite for portability.
*/ 

begin;
    
/* 
   First we create various tables just for internal computation
  
   The following tables are for intermediate values in the computation
   of Daily Values
*/
  
DROP TABLE if exists z_vars1;
CREATE TABLE z_vars1 (am_cals2gram_pro real, am_cals2gram_fat real, am_cals2gram_cho real, am_alccals real, am_fa2fat real, balance_of_calories int);

DROP TABLE if exists z_vars2;
CREATE TABLE z_vars2 (am_fat_dv_not_boc real, am_cho_nonfib_dv_not_boc real, am_chocdf_dv_not_boc real);

DROP TABLE if exists z_vars3;
CREATE TABLE z_vars3 (am_fat_dv_boc real, am_chocdf_dv_boc real, am_cho_nonfib_dv_boc real);

DROP TABLE if exists z_vars4;
CREATE TABLE z_vars4 (Nutr_No int, dv real, Nutr_Val real);
  
/*
  The following table is used in conjunction with recursive triggers to
  compute essential fatty acid reference values
*/  

DROP TABLE if exists z_n6;
CREATE TABLE z_n6 (n6hufa real, FAPU1 real, pufa_reduction real, iter int, reduce int, p3 real, p6 real, h3 real, h6 real, o real);

/*  
   The following table is the am analysis minus the currentmeal.  That way,
   we avoid the overhead of reanalyzing everything while we are
   doing automatic portion control.  The "am_analysis" view is the
   sum of the analyses in this table and those in the "rm_analysis"
   table, the analysis of the current meal.  Of course, this means we
   have to redo this table whenever the currentmeal changes to a
   different meal, and we always need to have a good rm_analysis
   before we can see a good am_analysis.
*/
  
drop table if exists z_anal;
create table z_anal (Nutr_No int primary key, null_value int, Nutr_Val real);

/*    
   The following tables and views are intermediates for functions in NUT.
   The prefixes are:
	am	Analyze meals
	rm	Record meals aka currentmeal
*/    

/* An "analysis header" is various info that is not specifically nutrient
   values:
   maxmeal is maximum number of meals the user can get no matter how many he
              asks for
   mealcount is actual number of meals being analyzed
   meals_per_day is yes, meals per day
   firstmeal is earliest meal in the analysis
   lastmeal is latest meal in the analysis
   currentmeal is the current meal as specified in the options table
   caloriebutton - NUT has a calorie button that specifies the calorie DV, so
                   here it is
   macropct is the percentages of protein, carbs, and fat calories in the
               analysis
   n6balance is the supposed percentages of omega-6 and omega-3 in tissue
             phospholipids according to William Lands' equation if these fatty
             acids saturate the phospholipids as they probably do due to
             excessive omega-6 in modern diets
*/

drop table if exists am_analysis_header;
create table am_analysis_header (maxmeal int, mealcount int, meals_per_day int, firstmeal integer, lastmeal integer, currentmeal integer, caloriebutton text, macropct text, n6balance text);

/* This table lists the nutrient and its computed Daily Value or dv.  It also
   has the cryptically named dvpct_offset which is the percentage by which the
   actual nutrient value is off from 100% of the DV.  So if you have 99% of the
   DV for potassium the dvpct_offset is -1.0, if you have 103% the dvpct_offset
   is 3.0.  So, you get the % of the DV by adding 100.0 to the dvpct_offset.
*/

drop table if exists am_dv;
create table am_dv (Nutr_No int primary key asc, dv real, dvpct_offset real);
    
drop table if exists rm_analysis_header;
create table rm_analysis_header (maxmeal int, mealcount int, meals_per_day int, firstmeal integer, lastmeal integer, currentmeal integer, caloriebutton text, macropct text, n6balance text);

drop table if exists rm_analysis;
create table rm_analysis (Nutr_No int primary key asc, null_value int, Nutr_Val real);

drop table if exists rm_dv;
create table rm_dv (Nutr_No int primary key asc, dv real, dvpct_offset real);

drop view if exists am_analysis;
create view am_analysis as select am.Nutr_No as Nutr_No, case when currentmeal between firstmeal and lastmeal and am.null_value = 1 and rm.null_value = 1 then 1 when currentmeal not between firstmeal and lastmeal and am.null_value = 1 then 1 else 0 end as null_value, case when currentmeal between firstmeal and lastmeal then ifnull(am.Nutr_Val,0.0) + 1.0 / mealcount * ifnull(rm.Nutr_Val, 0.0) else am.Nutr_Val end as Nutr_Val from z_anal am left join rm_analysis rm on am.Nutr_No = rm.Nutr_No join am_analysis_header;
    
/*
   PCF is automatic portion control; aka protein/carb/fat which was extended
   to include all DV nutrients.  The idea is you can control macronutrients
   per meal, but also include a modicum of micronutrients for which
   experience has shown that one's diet never achieves the nutrition standard
   otherwise.  PCF means to adjust the quantity of a particular food to
   achieve a particular nutrition standard for an entire meal.  PCF processing
   only applies to the currentmeal and is accomplished by complicated recursive
   triggers instead of linear algebra.

   Triggers for PCF have to be in "user.sqlite3" because if they are always
   turned on, it is impossible to do any bulk update of the database.
*/

/*
   Some of the triggers need repetitive elements since we don't have
   a lot of choices for how they flow.  Thus this table is used to
   to start triggers so we can use the same code in different
   triggers without literally repeating it in every trigger that
   needs it
*/  

drop table if exists z_trig_ctl;
CREATE TABLE z_trig_ctl(am_analysis_header integer default 0, rm_analysis_header integer default 0, am_analysis_minus_currentmeal integer default 0, am_analysis_null integer default 0, am_analysis integer default 0, rm_analysis integer default 0, rm_analysis_null integer default 0, am_dv integer default 0, PCF_processing integer default 0, block_setting_preferred_weight integer default 0, block_mealfoods_insert_trigger default 0, block_mealfoods_delete_trigger integer default 0);
insert into z_trig_ctl default values;
  
/*
   Procedures implemented as triggers started by a true bool in z_trig_ctl
*/  

drop trigger if exists am_analysis_header_trigger;
CREATE TRIGGER am_analysis_header_trigger after update of am_analysis_header on z_trig_ctl when NEW.am_analysis_header = 1 begin 
update z_trig_ctl set am_analysis_header = 0;
delete from am_analysis_header;
insert into am_analysis_header select (select count(distinct meal_id) from mealfoods) as maxmeal, count(meal_id) as mealcount, meals_per_day, ifnull(min(meal_id),0) as firstmeal, ifnull(max(meal_id),0) as lastmeal, currentmeal, NULL as caloriebutton, NULL as macropct, NULL as n6balance from options left join (select distinct meal_id from mealfoods order by meal_id desc limit (select defanal_am from options));
end;
  
drop trigger if exists rm_analysis_header_trigger;
CREATE TRIGGER rm_analysis_header_trigger after update of rm_analysis_header on z_trig_ctl when NEW.rm_analysis_header = 1 begin 
update z_trig_ctl set rm_analysis_header = 0;
delete from rm_analysis_header;
insert into rm_analysis_header select maxmeal, case when (select count(*) from mealfoods where meal_id = currentmeal) = 0 then 0 else 1 end as mealcount, meals_per_day, currentmeal as firstmeal, currentmeal as lastmeal, currentmeal as currentmeal, NULL as caloriebutton, '0 / 0 / 0' as macropct, '0 / 0' as n6balance from am_analysis_header;
end;
    
drop trigger if exists am_analysis_minus_currentmeal_trigger;
CREATE TRIGGER am_analysis_minus_currentmeal_trigger after update of am_analysis_minus_currentmeal on z_trig_ctl when NEW.am_analysis_minus_currentmeal = 1 begin 
update z_trig_ctl set am_analysis_minus_currentmeal = 0;
delete from z_anal;
insert into z_anal select Nutr_No, case when sum(mhectograms * Nutr_Val) is null then 1 else 0 end, ifnull(sum(mhectograms * Nutr_Val), 0.0) from (select NDB_No, total(Gm_Wgt / 100.0 / mealcount * meals_per_day) as mhectograms from mealfoods join am_analysis_header where meal_id between firstmeal and lastmeal and meal_id != currentmeal group by NDB_No) join nutr_def natural left join nut_data group by Nutr_No;
end;
  
/*  We need null triggers because processing is so different when the analysis
    is null; i.e. there is no food in the analysis.
*/

drop trigger if exists am_analysis_null_trigger;
CREATE TRIGGER am_analysis_null_trigger after update of am_analysis_null on z_trig_ctl when NEW.am_analysis_null = 1 begin 
update z_trig_ctl set am_analysis_null = 0;
delete from z_anal;
insert into z_anal select nutr_no, 1, 0.0 from nutr_def join am_analysis_header where firstmeal = currentmeal and lastmeal = currentmeal;
insert into z_anal select nutr_no, 0, 0.0 from nutr_def join am_analysis_header where firstmeal != currentmeal or lastmeal != currentmeal;
update am_analysis_header set macropct = '0 / 0 / 0', n6balance = '0 / 0';
end;
   
drop trigger if exists rm_analysis_null_trigger;
CREATE TRIGGER rm_analysis_null_trigger after update of rm_analysis_null on z_trig_ctl when NEW.rm_analysis_null = 1 begin 
update z_trig_ctl set rm_analysis_null = 0;
delete from rm_analysis;
insert into rm_analysis select Nutr_No, 0, 0.0 from nutr_def;
update rm_analysis_header set caloriebutton = (select caloriebutton from am_analysis_header), macropct = '0 / 0 / 0', n6balance = '0 / 0';
end;

/* 
   These triggers are gnarly because many DVs require the results of other DVs
   so it takes many steps.  And also, figuring the omega-6/3 balance and
   the essential fatty acid DVs requires division; however, the values
   themselves are often the divisors so we get division by zero unless we fudge
   and put a trace of the nutrient in when the value is actually zero.  Plus
   we need a lot of joins to get all the necessary nutrient values together
   for computation.
*/
  
drop trigger if exists am_analysis_trigger;
CREATE TRIGGER am_analysis_trigger after update of am_analysis on z_trig_ctl when NEW.am_analysis = 1 begin 
update z_trig_ctl set am_analysis = 0;
update am_analysis_header set macropct = (select cast (ifnull(round(100 * PROT_KCAL.Nutr_Val / ENERC_KCAL.Nutr_Val,0),0) as int) || ' / ' || cast (ifnull(round(100 * CHO_KCAL.Nutr_Val / ENERC_KCAL.Nutr_Val,0),0) as int) || ' / ' || cast (ifnull(round(100 * FAT_KCAL.Nutr_Val / ENERC_KCAL.Nutr_Val,0),0) as int) from am_analysis ENERC_KCAL join am_analysis PROT_KCAL on ENERC_KCAL.Nutr_No = 208 and PROT_KCAL.Nutr_No = 3000 join am_analysis CHO_KCAL on CHO_KCAL.Nutr_No = 3002 join am_analysis FAT_KCAL on FAT_KCAL.Nutr_No = 3001);
delete from z_n6;
insert into z_n6 select NULL, NULL, NULL, 1, 1, 900.0 * case when SHORT3.Nutr_Val > 0.0 then SHORT3.Nutr_Val else 0.000000001 end / case when ENERC_KCAL.Nutr_Val > 0.0 then ENERC_KCAL.Nutr_Val else 0.000000001 end, 900.0 * case when SHORT6.Nutr_Val > 0.0 then SHORT6.Nutr_Val else 0.000000001 end / case when ENERC_KCAL.Nutr_Val > 0.0 then ENERC_KCAL.Nutr_Val else 0.000000001 end, 900.0 * case when LONG3.Nutr_Val > 0.0 then LONG3.Nutr_Val else 0.000000001 end / case when ENERC_KCAL.Nutr_Val > 0.0 then ENERC_KCAL.Nutr_Val else 0.000000001 end, 900.0 * case when LONG6.Nutr_Val > 0.0 then LONG6.Nutr_Val else 0.000000001 end / case when ENERC_KCAL.Nutr_Val > 0.0 then ENERC_KCAL.Nutr_Val else 0.000000001 end, 900.0 * (FASAT.Nutr_Val + FAMS.Nutr_Val + FAPU.Nutr_Val - max(SHORT3.Nutr_Val,0.000000001) - max(SHORT6.Nutr_Val,0.000000001) - max(LONG3.Nutr_Val,0.000000001) - max(LONG6.Nutr_Val,0.000000001)) / case when ENERC_KCAL.Nutr_Val > 0.0 then ENERC_KCAL.Nutr_Val else 0.000000001 end from am_analysis SHORT3 join am_analysis SHORT6 on SHORT3.Nutr_No = 3005 and SHORT6.Nutr_No = 3003 join am_analysis LONG3 on LONG3.Nutr_No = 3006 join am_analysis LONG6 on LONG6.Nutr_No = 3004 join am_analysis FAPUval on FAPUval.Nutr_No = 646 join am_analysis FASAT on FASAT.Nutr_No = 606 join am_analysis FAMS on FAMS.Nutr_No = 645 join am_analysis FAPU on FAPU.Nutr_No = 646 join am_analysis ENERC_KCAL on ENERC_KCAL.Nutr_No = 208;
update am_analysis_header set n6balance = (select case when n6hufa_int = 0 or n6hufa_int is null then 0 when n6hufa_int between 1 and 14 then 15 when n6hufa_int > 90 then 90 else n6hufa_int end || ' / ' || (100 - case when n6hufa_int = 0 then 100 when n6hufa_int between 1 and 14 then 15 when n6hufa_int > 90 then 90 else n6hufa_int end) from (select cast (round(n6hufa,0) as int) as n6hufa_int from z_n6));
update am_analysis_header set n6balance = case when n6balance is null then '0 / 0' else n6balance end;
end;

drop trigger if exists rm_analysis_trigger;
CREATE TRIGGER rm_analysis_trigger after update of rm_analysis on z_trig_ctl when NEW.rm_analysis = 1 begin 
update z_trig_ctl set rm_analysis = 0;
delete from rm_analysis;
insert into rm_analysis select Nutr_No, case when sum(mhectograms * Nutr_Val) is null then 1 else 0 end, ifnull(sum(mhectograms * Nutr_Val), 0.0) from (select NDB_No, total(Gm_Wgt / 100.0 * meals_per_day) as mhectograms from mealfoods join am_analysis_header where meal_id = currentmeal group by NDB_No) join nutr_def natural left join nut_data group by Nutr_No;
update rm_analysis_header set caloriebutton = (select caloriebutton from am_analysis_header), macropct = (select cast (ifnull(round(100 * PROT_KCAL.Nutr_Val / ENERC_KCAL.Nutr_Val,0),0) as int) || ' / ' || cast (ifnull(round(100 * CHO_KCAL.Nutr_Val / ENERC_KCAL.Nutr_Val,0),0) as int) || ' / ' || cast (ifnull(round(100 * FAT_KCAL.Nutr_Val / ENERC_KCAL.Nutr_Val,0),0) as int) from rm_analysis ENERC_KCAL join rm_analysis PROT_KCAL on ENERC_KCAL.Nutr_No = 208 and PROT_KCAL.Nutr_No = 3000 join rm_analysis CHO_KCAL on CHO_KCAL.Nutr_No = 3002 join rm_analysis FAT_KCAL on FAT_KCAL.Nutr_No = 3001);
delete from z_n6;
insert into z_n6 select NULL, NULL, NULL, 1, 1, 900.0 * case when SHORT3.Nutr_Val > 0.0 then SHORT3.Nutr_Val else 0.000000001 end / case when ENERC_KCAL.Nutr_Val > 0.0 then ENERC_KCAL.Nutr_Val else 0.000000001 end, 900.0 * case when SHORT6.Nutr_Val > 0.0 then SHORT6.Nutr_Val else 0.000000001 end / case when ENERC_KCAL.Nutr_Val > 0.0 then ENERC_KCAL.Nutr_Val else 0.000000001 end, 900.0 * case when LONG3.Nutr_Val > 0.0 then LONG3.Nutr_Val else 0.000000001 end / case when ENERC_KCAL.Nutr_Val > 0.0 then ENERC_KCAL.Nutr_Val else 0.000000001 end, 900.0 * case when LONG6.Nutr_Val > 0.0 then LONG6.Nutr_Val else 0.000000001 end / case when ENERC_KCAL.Nutr_Val > 0.0 then ENERC_KCAL.Nutr_Val else 0.000000001 end, 900.0 * (FASAT.Nutr_Val + FAMS.Nutr_Val + FAPU.Nutr_Val - max(SHORT3.Nutr_Val,0.000000001) - max(SHORT6.Nutr_Val,0.000000001) - max(LONG3.Nutr_Val,0.000000001) - max(LONG6.Nutr_Val,0.000000001)) / case when ENERC_KCAL.Nutr_Val > 0.0 then ENERC_KCAL.Nutr_Val else 0.000000001 end from rm_analysis SHORT3 join rm_analysis SHORT6 on SHORT3.Nutr_No = 3005 and SHORT6.Nutr_No = 3003 join rm_analysis LONG3 on LONG3.Nutr_No = 3006 join rm_analysis LONG6 on LONG6.Nutr_No = 3004 join rm_analysis FAPUval on FAPUval.Nutr_No = 646 join rm_analysis FASAT on FASAT.Nutr_No = 606 join rm_analysis FAMS on FAMS.Nutr_No = 645 join rm_analysis FAPU on FAPU.Nutr_No = 646 join rm_analysis ENERC_KCAL on ENERC_KCAL.Nutr_No = 208;
update rm_analysis_header set n6balance = (select case when n6hufa_int = 0 or n6hufa_int is null then 0 when n6hufa_int between 1 and 14 then 15 when n6hufa_int > 90 then 90 else n6hufa_int end || ' / ' || (100 - case when n6hufa_int = 0 then 100 when n6hufa_int between 1 and 14 then 15 when n6hufa_int > 90 then 90 else n6hufa_int end) from (select cast (round(n6hufa,0) as int) as n6hufa_int from z_n6));
end;

drop trigger if exists am_dv_trigger;
CREATE TRIGGER am_dv_trigger after update of am_dv on z_trig_ctl when NEW.am_dv = 1 begin 
update z_trig_ctl set am_dv = 0;
delete from am_dv;
insert into am_dv select Nutr_No, dv, 100.0 * Nutr_Val / dv - 100.0 from (select Nutr_No, Nutr_Val, case when nutopt = 0.0 then dv_default when nutopt = -1.0 and Nutr_Val > 0.0 then Nutr_Val when nutopt = -1.0 and Nutr_Val <= 0.0 then dv_default else nutopt end as dv from nutr_def natural join am_analysis where dv_default > 0.0 and (Nutr_No = 208 or Nutr_No between 301 and 601 or Nutr_No = 2008));
insert into am_dv select Nutr_No, dv, 100.0 * Nutr_Val / dv - 100.0 from (select Nutr_No, Nutr_Val, case when nutopt = 0.0 and (select dv from am_dv where Nutr_No = 208) > 0.0 then (select dv from am_dv where Nutr_No = 208) / 2000.0 * dv_default when nutopt = 0.0 then dv_default when nutopt = -1.0 and Nutr_Val > 0.0 then Nutr_Val when nutopt = -1.0 and Nutr_Val <= 0.0 then (select dv from am_dv where Nutr_No = 208) / 2000.0 * dv_default else nutopt end as dv from nutr_def natural join am_analysis where Nutr_No = 291);
delete from z_vars1;
insert into z_vars1 select ifnull(PROT_KCAL.Nutr_Val / PROCNT.Nutr_Val, 4.0), ifnull(FAT_KCAL.Nutr_Val / FAT.Nutr_Val, 9.0), ifnull(CHO_KCAL.Nutr_Val / CHOCDF.Nutr_Val, 4.0), ifnull(ALC.Nutr_Val * 6.93, 0.0), ifnull((FASAT.Nutr_Val + FAMS.Nutr_Val + FAPU.Nutr_Val) / FAT.Nutr_Val, 0.94615385), case when ENERC_KCALopt.nutopt = -1 then 208 when FATopt.nutopt <= 0.0 and CHO_NONFIBopt.nutopt = 0.0 then 2000 else 204 end from am_analysis PROT_KCAL join am_analysis PROCNT on PROT_KCAL.Nutr_No = 3000 and PROCNT.Nutr_No = 203 join am_analysis FAT_KCAL on FAT_KCAL.Nutr_No = 3001 join am_analysis FAT on FAT.Nutr_No = 204 join am_analysis CHO_KCAL on CHO_KCAL.Nutr_No = 3002 join am_analysis CHOCDF on CHOCDF.Nutr_No = 205 join am_analysis ALC on ALC.Nutr_No = 221 join am_analysis FASAT on FASAT.Nutr_No = 606 join am_analysis FAMS on FAMS.Nutr_No = 645 join am_analysis FAPU on FAPU.Nutr_No = 646 join nutr_def ENERC_KCALopt on ENERC_KCALopt.Nutr_No = 208 join nutr_def FATopt on FATopt.Nutr_No = 204 join nutr_def CHO_NONFIBopt on CHO_NONFIBopt.Nutr_No = 2000;
insert into am_dv select Nutr_No, dv, 100.0 * Nutr_Val / dv - 100.0 from (select PROCNTnd.Nutr_No, case when (PROCNTnd.nutopt = 0.0 and ENERC_KCAL.dv > 0.0) or (PROCNTnd.nutopt = -1.0 and PROCNT.Nutr_Val <= 0.0) then PROCNTnd.dv_default * ENERC_KCAL.dv / 2000.0 when PROCNTnd.nutopt > 0.0 then PROCNTnd.nutopt else PROCNT.Nutr_Val end as dv, PROCNT.Nutr_Val from nutr_def PROCNTnd natural join am_analysis PROCNT join z_vars1 join am_dv ENERC_KCAL on ENERC_KCAL.Nutr_No = 208 where PROCNTnd.Nutr_No = 203);
delete from z_vars2;
insert into z_vars2 select am_fat_dv_not_boc, am_cho_nonfib_dv_not_boc, am_cho_nonfib_dv_not_boc + FIBTGdv from (select case when FATnd.nutopt = -1 and FAT.Nutr_Val > 0.0 then FAT.Nutr_Val when FATnd.nutopt > 0.0 then FATnd.nutopt else FATnd.dv_default * ENERC_KCAL.dv / 2000.0 end as am_fat_dv_not_boc, case when CHO_NONFIBnd.nutopt = -1 and CHO_NONFIB.Nutr_Val > 0.0 then CHO_NONFIB.Nutr_Val when CHO_NONFIBnd.nutopt > 0.0 then CHO_NONFIBnd.nutopt else (CHOCDFnd.dv_default * ENERC_KCAL.dv / 2000.0) - FIBTG.dv end as am_cho_nonfib_dv_not_boc, FIBTG.dv as FIBTGdv from z_vars1 join am_analysis FAT on FAT.Nutr_No = 204 join am_dv ENERC_KCAL on ENERC_KCAL.Nutr_No = 208 join nutr_def FATnd on FATnd.Nutr_No = 204 join nutr_def CHOCDFnd on CHOCDFnd.Nutr_No = 205 join nutr_def CHO_NONFIBnd on CHO_NONFIBnd.Nutr_No = 2000 join am_analysis CHO_NONFIB on CHO_NONFIB.Nutr_No = 2000 join am_dv FIBTG on FIBTG.Nutr_No = 291);
delete from z_vars3;
insert into z_vars3 select am_fat_dv_boc, am_chocdf_dv_boc, am_chocdf_dv_boc - FIBTGdv from (select (ENERC_KCAL.dv - (PROCNT.dv * am_cals2gram_pro) - (am_chocdf_dv_not_boc * am_cals2gram_cho)) / am_cals2gram_fat as am_fat_dv_boc, (ENERC_KCAL.dv - (PROCNT.dv * am_cals2gram_pro) - (am_fat_dv_not_boc * am_cals2gram_fat)) / am_cals2gram_cho as am_chocdf_dv_boc, FIBTG.dv as FIBTGdv from z_vars1 join z_vars2 join am_dv ENERC_KCAL on ENERC_KCAL.Nutr_No = 208 join am_dv PROCNT on PROCNT.Nutr_No = 203 join am_dv FIBTG on FIBTG.Nutr_No = 291);
insert into am_dv select Nutr_No, case when balance_of_calories = 204 then am_fat_dv_boc else am_fat_dv_not_boc end, case when balance_of_calories = 204 then 100.0 * Nutr_Val / am_fat_dv_boc - 100.0 else 100.0 * Nutr_Val / am_fat_dv_not_boc - 100.0 end from z_vars1 join z_vars2 join z_vars3 join nutr_def on Nutr_No = 204 natural join am_analysis;
insert into am_dv select Nutr_No, case when balance_of_calories = 2000 then am_cho_nonfib_dv_boc else am_cho_nonfib_dv_not_boc end, case when balance_of_calories = 2000 then 100.0 * Nutr_Val / am_cho_nonfib_dv_boc - 100.0 else 100.0 * Nutr_Val / am_cho_nonfib_dv_not_boc - 100.0 end from z_vars1 join z_vars2 join z_vars3 join nutr_def on Nutr_No = 2000 natural join am_analysis;
insert into am_dv select Nutr_No, case when balance_of_calories = 2000 then am_chocdf_dv_boc else am_chocdf_dv_not_boc end, case when balance_of_calories = 2000 then 100.0 * Nutr_Val / am_chocdf_dv_boc - 100.0 else 100.0 * Nutr_Val / am_chocdf_dv_not_boc - 100.0 end from z_vars1 join z_vars2 join z_vars3 join nutr_def on Nutr_No = 205 natural join am_analysis;
insert into am_dv select FASATnd.Nutr_No, case when FASATnd.nutopt = -1.0 and FASAT.Nutr_Val > 0.0 then FASAT.Nutr_Val when FASATnd.nutopt > 0.0 then FASATnd.nutopt else ENERC_KCAL.dv / 2000.0 * FASATnd.dv_default end, case when FASATnd.nutopt = -1.0 and FASAT.Nutr_Val > 0.0 then 0.0 when FASATnd.nutopt > 0.0 then 100.0 * FASAT.Nutr_Val / FASATnd.nutopt - 100.0 else 100.0 * FASAT.Nutr_Val / (ENERC_KCAL.dv / 2000.0 * FASATnd.dv_default) - 100.0 end from z_vars1 join nutr_def FASATnd on FASATnd.Nutr_No = 606 join am_dv ENERC_KCAL on ENERC_KCAL.Nutr_No = 208 join am_analysis FASAT on FASAT.Nutr_No = 606;
insert into am_dv select FAPUnd.Nutr_No, case when FAPUnd.nutopt = -1.0 and FAPU.Nutr_Val > 0.0 then FAPU.Nutr_Val when FAPUnd.nutopt > 0.0 then FAPUnd.nutopt else ENERC_KCAL.dv * 0.04 / am_cals2gram_fat end, case when FAPUnd.nutopt = -1.0 and FAPU.Nutr_Val > 0.0 then 0.0 when FAPUnd.nutopt > 0.0 then 100.0 * FAPU.Nutr_Val / FAPUnd.nutopt - 100.0 else 100.0 * FAPU.Nutr_Val / (ENERC_KCAL.dv * 0.04 / am_cals2gram_fat) - 100.0 end from z_vars1 join nutr_def FAPUnd on FAPUnd.Nutr_No = 646 join am_dv ENERC_KCAL on ENERC_KCAL.Nutr_No = 208 join am_analysis FAPU on FAPU.Nutr_No = 646;
insert into am_dv select FAMSnd.Nutr_No, (FAT.dv * am_fa2fat) - FASAT.dv - FAPU.dv, 100.0 * FAMS.Nutr_Val / ((FAT.dv * am_fa2fat) - FASAT.dv - FAPU.dv) - 100.0 from z_vars1 join am_dv FAT on FAT.Nutr_No = 204 join am_dv FASAT on FASAT.Nutr_No = 606 join am_dv FAPU on FAPU.Nutr_No = 646 join nutr_def FAMSnd on FAMSnd.Nutr_No = 645 join am_analysis FAMS on FAMS.Nutr_No = 645;
delete from z_n6;
insert into z_n6 select NULL, case when FAPU1 = 0.0 then 50.0 when FAPU1 < 15.0 then 15.0 when FAPU1 > 90.0 then 90.0 else FAPU1 end, case when FAPUval.Nutr_Val / FAPU.dv >= 1.0 then FAPUval.Nutr_Val / FAPU.dv else 1.0 end, 1, 0, 900.0 * case when SHORT3.Nutr_Val > 0.0 then SHORT3.Nutr_Val else 0.000000001 end / ENERC_KCAL.dv, 900.0 * case when SHORT6.Nutr_Val > 0.0 then SHORT6.Nutr_Val else 0.000000001 end / ENERC_KCAL.dv / case when FAPUval.Nutr_Val / FAPU.dv >= 1.0 then FAPUval.Nutr_Val / FAPU.dv else 1.0 end, 900.0 * case when LONG3.Nutr_Val > 0.0 then LONG3.Nutr_Val else 0.000000001 end / ENERC_KCAL.dv, 900.0 * case when LONG6.Nutr_Val > 0.0 then LONG6.Nutr_Val else 0.000000001 end / ENERC_KCAL.dv / case when FAPUval.Nutr_Val / FAPU.dv >= 1.0 then FAPUval.Nutr_Val / FAPU.dv else 1.0 end, 900.0 * (FASAT.dv + FAMS.dv + FAPU.dv - max(SHORT3.Nutr_Val,0.000000001) - max(SHORT6.Nutr_Val,0.000000001) - max(LONG3.Nutr_Val,0.000000001) - max(LONG6.Nutr_Val,0.000000001)) / ENERC_KCAL.dv from am_analysis SHORT3 join am_analysis SHORT6 on SHORT3.Nutr_No = 3005 and SHORT6.Nutr_No = 3003 join am_analysis LONG3 on LONG3.Nutr_No = 3006 join am_analysis LONG6 on LONG6.Nutr_No = 3004 join am_analysis FAPUval on FAPUval.Nutr_No = 646 join am_dv FASAT on FASAT.Nutr_No = 606 join am_dv FAMS on FAMS.Nutr_No = 645 join am_dv FAPU on FAPU.Nutr_No = 646 join am_dv ENERC_KCAL on ENERC_KCAL.Nutr_No = 208 join options;
delete from z_vars4;
insert into z_vars4 select Nutr_No, case when Nutr_Val > 0.0 and reduce = 3 then Nutr_Val / pufa_reduction when Nutr_Val > 0.0 and reduce = 6 then Nutr_Val / pufa_reduction - Nutr_Val / pufa_reduction * 0.01 * (iter - 1) else dv_default end, Nutr_Val from nutr_def natural join am_analysis join z_n6 where Nutr_No in (2006, 2001, 2002);
insert into z_vars4 select Nutr_No, case when Nutr_Val > 0.0 and reduce = 6 then Nutr_Val when Nutr_Val > 0.0 and reduce = 3 then Nutr_Val - Nutr_Val * 0.01 * (iter - 2) else dv_default end, Nutr_Val from nutr_def natural join am_analysis join z_n6 where Nutr_No in (2007, 2003, 2004, 2005);
insert into am_dv select Nutr_No, dv, 100.0 * Nutr_Val / dv - 100.0 from z_vars4;
update am_analysis_header set caloriebutton = 'Calories (' || (select cast (round(dv) as int) from am_dv where Nutr_No = 208) || ')';
delete from rm_dv;
insert into rm_dv select Nutr_No, dv, 100.0 * Nutr_Val / dv - 100.0 from rm_analysis natural join am_dv;
insert or replace into mealfoods select meal_id, NDB_No, Gm_Wgt - dv * dvpct_offset / (select meals_per_day from options) / Nutr_Val, Nutr_No from rm_dv natural join nut_data natural join mealfoods where abs(dvpct_offset) > 0.001 order by abs(dvpct_offset) desc limit 1;
end;

/*
  This view is NUT's idea how to update mealfoods to achieve portion control.
  We need it now because it is referenced in the following trigger.
*/

drop view if exists z_pcf;
create view z_pcf as select meal_id, 
NDB_No, Gm_Wgt + dv / meals_per_day * dvpct_offset / Nutr_Val * -1.0 as Gm_Wgt, Nutr_No
from mealfoods natural join rm_dv natural join nut_data join options 
where abs(dvpct_offset) >= 0.05 order by abs(dvpct_offset);

drop trigger if exists PCF_processing;
CREATE TRIGGER PCF_processing after update of PCF_processing on z_trig_ctl when NEW.PCF_processing = 1 begin 
update z_trig_ctl set PCF_processing = 0;
replace into mealfoods select * from z_pcf limit 1;
update z_trig_ctl set block_mealfoods_delete_trigger = 0;
end;
  
/*
   Now start the actual triggers that kick off when something happens.
   They replay the previous procedures as required by the different
   circumstances that update the appropriate column in z_trig_ctl to true.
*/  

/*
   Update to defanal_am in options (number of meals to analyze)
   so we need to rewrite the am_analysis and am_dv and thus practically
   everything
*/
  
drop trigger if exists defanal_am_trigger;
CREATE TRIGGER defanal_am_trigger after update of defanal_am on options begin 
update z_trig_ctl set am_analysis_header = 1;
update z_trig_ctl set am_analysis_minus_currentmeal = case when (select mealcount from am_analysis_header) > 1 then 1 when (select mealcount from am_analysis_header) = 1 and (select lastmeal from am_analysis_header) != (select currentmeal from am_analysis_header) then 1 else 0 end;
update z_trig_ctl set am_analysis_null = case when (select mealcount from am_analysis_header) > 1 then 0 when (select mealcount from am_analysis_header) = 1 and (select lastmeal from am_analysis_header) != (select currentmeal from am_analysis_header) then 0 else 1 end;
update z_trig_ctl set am_analysis = 1;
update z_trig_ctl set am_dv = 1;
update z_trig_ctl set PCF_processing = 1;
end;
  
/*
   Update to currentmeal in options
   so we need to rewrite practically everything
*/  

drop trigger if exists currentmeal_trigger;
CREATE TRIGGER currentmeal_trigger after update of currentmeal on options begin 
update mealfoods set Nutr_No = null where Nutr_No is not null;
update z_trig_ctl set am_analysis_header = 1;
update z_trig_ctl set am_analysis_minus_currentmeal = case when (select mealcount from am_analysis_header) > 1 then 1 when (select mealcount from am_analysis_header) = 1 and (select lastmeal from am_analysis_header) != (select currentmeal from am_analysis_header) then 1 else 0 end;
update z_trig_ctl set am_analysis_null = case when (select mealcount from am_analysis_header) > 1 then 0 when (select mealcount from am_analysis_header) = 1 and (select lastmeal from am_analysis_header) != (select currentmeal from am_analysis_header) then 0 else 1 end;
update z_trig_ctl set rm_analysis_header = 1;
update z_trig_ctl set rm_analysis = case when (select mealcount from rm_analysis_header) = 1 then 1 else 0 end;
update z_trig_ctl set rm_analysis_null = case when (select mealcount from rm_analysis_header) = 0 then 1 else 0 end;
update z_trig_ctl set am_analysis = 1;
update z_trig_ctl set am_dv = 1;
end;
  
/*
   Input: table z_n6 to compute column n6hufa
   Output column:  n6hufa
   Purpose:  First step to set reference value for essential fatty acids
             Compute Lands' n6hufa % and set up for following triggers to
             determine reference values
*/
 
drop trigger if exists z_n6_insert_trigger;
CREATE TRIGGER z_n6_insert_trigger after insert on z_n6 begin 
update z_n6 set n6hufa = (select 100.0 / (1.0 + 0.0441 / p6 * (1.0 + p3 / 0.0555 + h3 / 0.005 + o / 5.0 + p6 / 0.175)) + 100.0 / (1.0 + 0.7 / h6 * (1.0 + h3 / 3.0))), reduce = 0, iter = 0;
end;
    
/*
   Input:  If column "reduce" is set right, this trigger recursively
           subtracts omega-6 fatty acids to produce n6hufa numbers to match the
           option for Omega-6/3 balance
   Output:  recursive
   Purpose:  determine daily values for n-6 when we know n-6 is excessive
             because n6hufa > target FAPU1 from options
*/

drop trigger if exists z_n6_reduce6_trigger;
CREATE TRIGGER z_n6_reduce6_trigger after update on z_n6 when NEW.n6hufa > OLD.FAPU1 and NEW.iter < 100 and NEW.reduce in (0, 6) begin 
update z_n6 set iter = iter + 1, reduce = 6, n6hufa = (select 100.0 / (1.0 + 0.0441 / (p6 - iter * .01 * p6) * (1.0 + p3 / 0.0555 + h3 / 0.005 + o / 5.0 + p6 / 0.175)) + 100.0 / (1.0 + 0.7 / (h6 - iter * .01 * h6) * (1.0 + h3 / 3.0)));
end;
    
/*
   Input:  If column "reduce" is set right, this trigger recursively
           subtracts omega-3 fatty acids to produce n6hufa numbers to match the
           option for Omega-6/3 balance
   Output:  recursive
   Purpose:  determine daily values for n-3 when we know n-3 is excessive
             because n6hufa < target FAPU1
*/
  
drop trigger if exists z_n6_reduce3_trigger;
CREATE TRIGGER z_n6_reduce3_trigger after update of n6hufa on z_n6 when NEW.n6hufa < OLD.FAPU1 and NEW.iter < 100 and NEW.reduce in (0, 3) begin 
update z_n6 set iter = iter + 1, reduce = 3, n6hufa = (select 100.0 / (1.0 + 0.0441 / p6 * (1.0 + (p3 - iter * .01 * p3) / 0.0555 + (h3 - iter * .01 * h3) / 0.005 + o / 5.0 + p6 / 0.175)) + 100.0 / (1.0 + 0.7 / h6 * (1.0 + (h3 - iter * .01 * h3) / 3.0)));
end;
    
/*
  First insert into currentmeal is special because it changes everything!
*/

drop trigger if exists insert_mealfoods_trigger;
 
CREATE TRIGGER insert_mealfoods_trigger after insert on mealfoods when NEW.meal_id = (select currentmeal from options) and (select count(*) from mealfoods where meal_id = NEW.meal_id) = 1 begin 
update z_trig_ctl set am_analysis_header = 1;
update z_trig_ctl set am_analysis_minus_currentmeal = case when (select mealcount from am_analysis_header) > 1 then 1 when (select mealcount from am_analysis_header) = 1 and (select lastmeal from am_analysis_header) != (select currentmeal from am_analysis_header) then 1 else 0 end;
update z_trig_ctl set am_analysis_null = case when (select mealcount from am_analysis_header) > 1 then 0 when (select mealcount from am_analysis_header) = 1 and (select lastmeal from am_analysis_header) != (select currentmeal from am_analysis_header) then 0 else 1 end;
update z_trig_ctl set rm_analysis_header = 1;
update z_trig_ctl set rm_analysis = case when (select mealcount from rm_analysis_header) = 1 then 1 else 0 end;
update z_trig_ctl set rm_analysis_null = case when (select mealcount from rm_analysis_header) = 0 then 1 else 0 end;
update z_trig_ctl set am_analysis = 1;
update z_trig_ctl set am_dv = 1;
end;
  
/*
  Last delete from currentmeal is special because it changes everything!
*/
  
drop trigger if exists delete_mealfoods_trigger;
CREATE TRIGGER delete_mealfoods_trigger after delete on mealfoods when OLD.meal_id = (select currentmeal from options) and (select count(*) from mealfoods where meal_id = OLD.meal_id) = 0 begin 
update mealfoods set Nutr_No = null where Nutr_No is not null;
update z_trig_ctl set am_analysis_header = 1;
update z_trig_ctl set am_analysis_minus_currentmeal = case when (select mealcount from am_analysis_header) > 1 then 1 when (select mealcount from am_analysis_header) = 1 and (select lastmeal from am_analysis_header) != (select currentmeal from am_analysis_header) then 1 else 0 end;
update z_trig_ctl set am_analysis_null = case when (select mealcount from am_analysis_header) > 1 then 0 when (select mealcount from am_analysis_header) = 1 and (select lastmeal from am_analysis_header) != (select currentmeal from am_analysis_header) then 0 else 1 end;
update z_trig_ctl set rm_analysis_header = 1;
update z_trig_ctl set rm_analysis = case when (select mealcount from rm_analysis_header) = 1 then 1 else 0 end;
update z_trig_ctl set rm_analysis_null = case when (select mealcount from rm_analysis_header) = 0 then 1 else 0 end;
update z_trig_ctl set am_analysis = 1;
update z_trig_ctl set am_dv = 1;
end;
  
/*
  Input: mealfoods modified Gm_Wgt
  Output: weight Gm_Wgt
  Purpose:  NUT remembers user serving size preference in first weight record
            when ordered by Seq (although origSeq is the actual key).
*/
    
drop trigger if exists update_mealfoods2weight_trigger;
CREATE TRIGGER update_mealfoods2weight_trigger AFTER UPDATE ON mealfoods when NEW.Gm_Wgt > 0.0 and (select block_setting_preferred_weight from z_trig_ctl) = 0 BEGIN 
update weight set Gm_Wgt = NEW.Gm_Wgt where NDB_No = NEW.NDB_No and Seq = (select min(Seq) from weight where NDB_No = NEW.NDB_No) ;
end;
    
drop trigger if exists insert_mealfoods2weight_trigger;
CREATE TRIGGER insert_mealfoods2weight_trigger AFTER INSERT ON mealfoods when NEW.Gm_Wgt > 0.0 and (select block_setting_preferred_weight from z_trig_ctl) = 0 BEGIN 
update weight set Gm_Wgt = NEW.Gm_Wgt where NDB_No = NEW.NDB_No and Seq = (select min(Seq) from weight where NDB_No = NEW.NDB_No) ;
end;
    
    
/*
  If you follow the weight saga so far, you realize that if the user wants to
  always see a different serving unit, you have to change the Seq to 0 for that
  record.  Here are convenience triggers that change the current Seq = 0
  record back to the original Seq immediately before the change so you don't
  have to explicitly do it:
*/

drop trigger if exists update_weight_Seq;
create trigger update_weight_Seq BEFORE update of Seq on weight when NEW.Seq = 0 BEGIN
update weight set Seq = origSeq, Gm_Wgt = origGm_Wgt where NDB_No = NEW.NDB_No;
end;

drop trigger if exists insert_weight_Seq;
create trigger insert_weight_Seq BEFORE insert on weight when NEW.Seq = 0 BEGIN
update weight set Seq = origSeq, Gm_Wgt = origGm_Wgt where NDB_No = NEW.NDB_No;
end;

/*
  Now we need some stuff to support the weight log mini-application.  First,
  a view to figure the slope and y-intercept of uncleared weight records.
  The slope is the average daily weight gain or loss in the user's units,
  whatever they might be; the y-intercept is the prediction for today; and
  finally we present "n" which is the sample count.  We use linear regression
  to get all these values.
*/

drop view if exists z_wslope;
CREATE VIEW z_wslope as select ifnull(weightslope,0.0) as "weightslope", ifnull(round(sumy / n - weightslope * sumx / n,1),0.0) as "weightyintercept", n as "weightn" from (select (sumxy - (sumx * sumy / n)) / (sumxx - (sumx * sumx / n)) as weightslope, sumy, n, sumx from (select sum(x) as sumx, sum(y) as sumy, sum(x*y) as sumxy, sum(x*x) as sumxx, n from (select cast (cast (julianday(substr(wldate,1,4) || '-' || substr(wldate,5,2) || '-' || substr(wldate,7,2)) - julianday('now', 'localtime') as int) as real) as x, weight as y, cast ((select count(*) from z_wl where cleardate is null) as real) as n from z_wl where cleardate is null)));

/*
  Basically the same thing for the slope, y-intercept, and "n" of fat mass.
*/

drop view if exists z_fslope;
CREATE VIEW z_fslope as select ifnull(fatslope,0.0) as "fatslope", ifnull(round(sumy / n - fatslope * sumx / n,1),0.0) as "fatyintercept", n as "fatn" from (select (sumxy - (sumx * sumy / n)) / (sumxx - (sumx * sumx / n)) as fatslope, sumy, n, sumx from (select sum(x) as sumx, sum(y) as sumy, sum(x*y) as sumxy, sum(x*x) as sumxx, n from (select cast (cast (julianday(substr(wldate,1,4) || '-' || substr(wldate,5,2) || '-' || substr(wldate,7,2)) - julianday('now', 'localtime') as int) as real) as x, bodyfat * weight / 100.0 as y, cast ((select count(*) from z_wl where ifnull(bodyfat,0.0) > 0.0 and cleardate is null) as real) as n from z_wl where ifnull(bodyfat,0.0) > 0.0 and cleardate is null)));

/*
  In our computations we not only need the number of samples, we also need the
  "span" which enumerates how many days from the first measurement to the
  present.
*/

drop view if exists z_span;
create view z_span as select abs(min(cast (julianday(substr(wldate,1,4) || '-' || substr(wldate,5,2) || '-' || substr(wldate,7,2)) - julianday('now', 'localtime') as int))) as span from z_wl where cleardate is null;

/*
  Here's the user's view of the weight log which we provide only so we can
  control inserts.
*/

drop view if exists wlog;
create view wlog as select * from z_wl;

/*
  Insert to wlog.  User supplies weight and bodyfat percentage plus two
  nulls, but we supply today's date.
*/

drop trigger if exists wlog_insert;
create trigger wlog_insert instead of insert on wlog begin
insert or replace into z_wl values (NEW.weight, NEW.bodyfat, (select strftime('%Y%m%d', 'now', 'localtime')), null);
end;

/*
  Here's the user's view of the weight log with some additional interesting
  columns.
*/

drop view if exists wlview;
CREATE VIEW wlview as select wldate, weight, bodyfat, round(weight - weight * bodyfat / 100, 1) as leanmass, round(weight * bodyfat / 100, 1) as fatmass, round(weight - 2 * weight * bodyfat / 100) as bodycomp, cleardate from z_wl;

/*
  Here's the verbiage associated with analysis of the user's measurements.
*/

drop view if exists wlsummary;
create view wlsummary as select case 
when (select weightn from z_wslope) > 1 then
'Weight:  ' || (select round(weightyintercept,1) from z_wslope) || char(13) || char(10) ||
'Bodyfat:  ' || case when (select weightyintercept from z_wslope) > 0.0 then round(1000.0 * (select fatyintercept from z_fslope) / (select weightyintercept from z_wslope)) / 10.0 else 0.0 end || '%' || char(13) || char(10)
when (select weightn from z_wslope) = 1 then
'Weight:  ' || (select weight from z_wl where cleardate is null) || char(13) || char(10) || 
'Bodyfat:  ' || (select bodyfat from z_wl where cleardate is null) || '%'
else 
'Weight:  0.0' || char(13) || char(10) || 
'Bodyfat:  0.0%'
end || char(13) || char(10) ||
'Today' || "'" || 's Calorie level = ' || (select cast(round(nutopt) as int) from nutr_def where Nutr_No = 208)
|| char(13) || char(10)
|| char(13) || char(10) ||
case when (select weightn from z_wslope) = 0 then '0 data points so far...' 
when (select weightn from z_wslope) = 1 then '1 data point so far...' 
else
'Based on the trend of ' || (select cast(cast(weightn as int) as text) from z_wslope) || ' data points so far...' || char(13) || char(10) || char(10) ||
'Predicted lean mass today = ' ||
(select cast(round(10.0 * (weightyintercept - fatyintercept)) / 10.0 as text) from z_wslope, z_fslope) || char(13) || char(10) ||
'Predicted fat mass today  =  ' ||
(select cast(round(fatyintercept, 1) as text) from z_fslope) || char(13) || char(10) || char(10) ||
'If the predictions are correct, you ' ||
case when (select weightslope - fatslope from z_wslope, z_fslope) >= 0.0 then 'gained ' else 'lost ' end ||
(select cast(abs(round((weightslope - fatslope) * span * 1000.0) / 1000.0) as text) from z_wslope, z_fslope, z_span) ||
' lean mass over ' ||
(select span from z_span) || 
case when (select span from z_span) = 1 then ' day' else ' days' end || char(13) || char(10) ||
case when (select fatslope from z_fslope) > 0.0 then 'and gained ' else 'and lost ' end ||
(select cast(abs(round(fatslope * span * 1000.0) / 1000.0) as text) from z_fslope, z_span) || ' fat mass.'

end
as verbiage;

/*
  The user indicates he wants to clear the weight log
  with an "insert into wlsummary select 'clear'" but we only actually clear if
  the user is not using the calorie autoset feature.
*/

drop trigger if exists clear_wlsummary;
create trigger clear_wlsummary instead of insert on wlsummary
when (select autocal from options) = 0
begin
update z_wl set cleardate = (select strftime('%Y%m%d', 'now', 'localtime'))
where cleardate is null;
insert into z_wl select weight, bodyfat, wldate, null from z_wl
where wldate = (select max(wldate) from z_wl);
end;

/*
  When the user takes the autocal function, we initialize wltweak, a boolean
  that indicates if calorie level has been "tweaked" and wlpolarity, a boolean
  that bounces between true and false to balance bias between fat mass loss
  and lean mass gain.

  Note:  These bools are not operative in the current version of autocal.
*/

drop trigger if exists autocal_initialization;
create trigger autocal_initialization after update of autocal on options
when NEW.autocal in (1, 2, 3) and OLD.autocal not in (1, 2, 3)
begin
update options set wltweak = 0, wlpolarity = 0;
end;

/*
  Updating meals_per_day on options results in archiving the meals at the old
  meals_per_day and restoring meals archived from the new meals_per_day
*/

drop trigger if exists mpd_archive;
create trigger mpd_archive after update of meals_per_day on options
when NEW.meals_per_day != OLD.meals_per_day
begin
insert or ignore into archive_mealfoods select meal_id, NDB_No, Gm_Wgt, OLD.meals_per_day from mealfoods;
delete from mealfoods;
insert or ignore into mealfoods select meal_id, NDB_No, Gm_Wgt, null from archive_mealfoods where meals_per_day = NEW.meals_per_day;
delete from archive_mealfoods where meals_per_day = NEW.meals_per_day;
update options set defanal_am = (select count(distinct meal_id) from mealfoods);
end;

/*
  Now we're done with setting it all up and we need to initialize after the load
  because NUT expects the analysis to already be there when it comes up.
  So, write the first analysis after a USDA load and initialize nutopts if
  necessary.
*/
  
update nutr_def set nutopt = 0.0 where nutopt is null;
update options set currentmeal = case when currentmeal is null then 0 else currentmeal end;
update options set defanal_am = case when defanal_am is null then 0 else defanal_am end;
    
/* 
  SQLite supposedly performs better if it has analyzed how big the tables are
*/

commit;
analyze main;

/*
  End of NUT application logic implemented by SQL tables and triggers
*/