/* Especially when you add a GUI and a second thread to handle the database, the
   application runs much faster with write-ahead logging.  However, to put the
   database back into one file, issue the command "pragma journal_mode = delete;".
   You would do this if you wanted to move the database to another system.  If
   you delete nut.db-wal and/or nut.db-shm manually, you will corrupt the database.

PRAGMA journal_mode = WAL;
*/

begin;
   
/* These temp tables must start out corresponding exactly to the USDA schemas
   for import from the USDA's distributed files but in some cases we need
   transitional temp tables to safely add what's new from the USDA to what the
   user already has. 
*/

/* For NUTR_DEF, we get rid of the tildes which escape non-numeric USDA fields,
   and add two fields:  dv_default to use when Daily Value is undefined, and
   nutopt which has three basic values:  -1 which means DV is whatever is in
   the user's analysis unless null or <= 0.0 in which case the dv_default is
   used; 0.0 which means the default Daily Value or computation; and > 0.0 which
   is a specific gram amount of the nutrient.

   We also shorten the names of nutrients so they can better fit on the screen
   and add some nutrients that are derived from USDA values.
*/

create temp table ttnutr_def (Nutr_No text, Units text, Tagname text, NutrDesc text, Num_Dec text, SR_Order int);
create temp table tnutr_def (Nutr_No int primary key, Units text, Tagname text, NutrDesc text, dv_default real, nutopt real);

/* FD_GROUP
*/

create temp table tfd_group (FdGrp_Cd int, FdGrp_Desc text);

/* FOOD_DES gets a new Long_Desc which is the USDA Long_Desc with the SciName
   appended in parenthesis.  If the new Long_Desc is <= 60 characters, it
   replaces the USDA's Shrt_Desc, which is sometimes unnecessarily cryptic.
*/

create temp table tfood_des (NDB_No text, FdGrp_Cd text, Long_Desc text, Shrt_Desc text, ComName text, ManufacName text, Survey text, Ref_desc text, Refuse integer, SciName text, N_Factor real, Pro_Factor real, Fat_Factor real, CHO_Factor real);

/* WEIGHT gets two new fields, origSeq and origGm_Wgt.  USDA Seq numbers start
   at one, so we change the Seq to 0 when we want to save the user's serving
   unit preference.  origSeq allows us to put the record back to normal if the
   user later chooses another Serving Unit.  The first record for a food when
   ordered by Seq can have its Gm_Wgt changed, and later we will define views
   that present the Amount of the serving unit as Gm_Wgt / origGm_Wgt * Amount.
*/

create temp table tweight (NDB_No text, Seq text, Amount real, Msre_Desc text, Gm_Wgt real, Num_Data_P int, Std_Dev real);
create temp table zweight (NDB_No int, Seq int, Amount real, Msre_Desc text, Gm_Wgt real, origSeq int, origGm_Wgt real, primary key(NDB_No, origSeq));
create temp table tnut_data (NDB_No text, Nutr_No text, Nutr_Val real, Num_Data_Pts int, Std_Error real, Src_Cd text, Deriv_Cd text, Ref_NDB_No text, Add_Nutr_Mark text, Num_Studies int, Min real, Max real, DF int, Low_EB real, Up_EB real, Stat_cmt text, AddMod_Date text, CC text);

/* The USDA uses a caret as a column separator and has no special end-of-line */

.separator "^"

/* We import the USDA data to the temp tables */

.import NUTR_DEF.txt ttnutr_def
.import FD_GROUP.txt tfd_group
.import FOOD_DES.txt tfood_des
.import WEIGHT.txt tweight
.import NUT_DATA.txt tnut_data

/* These real NUT tables may already exist and contain user data */

create table if not exists nutr_def (Nutr_No int primary key, Units text, Tagname text, NutrDesc text, dv_default real, nutopt real); 
create table if not exists fd_group (FdGrp_Cd int primary key, FdGrp_Desc text);
create table if not exists food_des (NDB_No int primary key, FdGrp_Cd int, Long_Desc text, Shrt_Desc text, Ref_desc text, Refuse integer, Pro_Factor real, Fat_Factor real, CHO_Factor real);
create table if not exists weight (NDB_No int, Seq int, Amount real, Msre_Desc text, Gm_Wgt real, origSeq int, origGm_Wgt real, primary key(NDB_No, origSeq));
create table if not exists nut_data (NDB_No int, Nutr_No int, Nutr_Val real, primary key(NDB_No, Nutr_No));

/* Update table nutr_def. */

insert into tnutr_def select * from nutr_def;
insert or ignore into tnutr_def select trim(Nutr_No, '~'), trim(Units, '~'), trim(Tagname, '~'), trim(NutrDesc, '~'), NULL, NULL from ttnutr_def;
update tnutr_def set Tagname = 'ADPROT' where Nutr_No = 257;
update tnutr_def set Tagname = 'VITD_BOTH' where Nutr_No = 328;
update tnutr_def set Tagname = 'LUT_ZEA' where Nutr_No = 338;
update tnutr_def set Tagname = 'VITE_ADDED' where Nutr_No = 573;
update tnutr_def set Tagname = 'VITB12_ADDED' where Nutr_No = 578;
update tnutr_def set Tagname = 'F22D1T' where Nutr_No = 664;
update tnutr_def set Tagname = 'F18D2T' where Nutr_No = 665;
update tnutr_def set Tagname = 'F18D2I' where Nutr_No = 666;
update tnutr_def set Tagname = 'F22D1C' where Nutr_No = 676;
update tnutr_def set Tagname = 'F18D3I' where Nutr_No = 856;
-- comment out the next line if you want to hassle the non-ascii micro char
update tnutr_def set Units = 'mcg' where hex(Units) = 'B567';
update tnutr_def set Units = 'kc' where Nutr_No = 208;
update tnutr_def set NutrDesc = 'Protein' where Nutr_No = 203;
update tnutr_def set NutrDesc = 'Total Fat' where Nutr_No = 204;
update tnutr_def set NutrDesc = 'Total Carb' where Nutr_No = 205;
update tnutr_def set NutrDesc = 'Ash' where Nutr_No = 207;
update tnutr_def set NutrDesc = 'Calories' where Nutr_No = 208;
update tnutr_def set NutrDesc = 'Starch' where Nutr_No = 209;
update tnutr_def set NutrDesc = 'Sucrose' where Nutr_No = 210;
update tnutr_def set NutrDesc = 'Glucose' where Nutr_No = 211;
update tnutr_def set NutrDesc = 'Fructose' where Nutr_No = 212;
update tnutr_def set NutrDesc = 'Lactose' where Nutr_No = 213;
update tnutr_def set NutrDesc = 'Maltose' where Nutr_No = 214;
update tnutr_def set NutrDesc = 'Ethyl Alcohol' where Nutr_No = 221;
update tnutr_def set NutrDesc = 'Water' where Nutr_No = 255;
update tnutr_def set NutrDesc = 'Adj. Protein' where Nutr_No = 257;
update tnutr_def set NutrDesc = 'Caffeine' where Nutr_No = 262;
update tnutr_def set NutrDesc = 'Theobromine' where Nutr_No = 263;
update tnutr_def set NutrDesc = 'Sugars' where Nutr_No = 269;
update tnutr_def set NutrDesc = 'Galactose' where Nutr_No = 287;
update tnutr_def set NutrDesc = 'Fiber' where Nutr_No = 291;
update tnutr_def set NutrDesc = 'Calcium' where Nutr_No = 301;
update tnutr_def set NutrDesc = 'Iron' where Nutr_No = 303;
update tnutr_def set NutrDesc = 'Magnesium' where Nutr_No = 304;
update tnutr_def set NutrDesc = 'Phosphorus' where Nutr_No = 305;
update tnutr_def set NutrDesc = 'Potassium' where Nutr_No = 306;
update tnutr_def set NutrDesc = 'Sodium' where Nutr_No = 307;
update tnutr_def set NutrDesc = 'Zinc' where Nutr_No = 309;
update tnutr_def set NutrDesc = 'Copper' where Nutr_No = 312;
update tnutr_def set NutrDesc = 'Fluoride' where Nutr_No = 313;
update tnutr_def set NutrDesc = 'Manganese' where Nutr_No = 315;
update tnutr_def set NutrDesc = 'Selenium' where Nutr_No = 317;
update tnutr_def set NutrDesc = 'Vit. A, IU' where Nutr_No = 318;
update tnutr_def set NutrDesc = 'Retinol' where Nutr_No = 319;
update tnutr_def set NutrDesc = 'Vitamin A' where Nutr_No = 320;
update tnutr_def set NutrDesc = 'B-Carotene' where Nutr_No = 321;
update tnutr_def set NutrDesc = 'A-Carotene' where Nutr_No = 322;
update tnutr_def set NutrDesc = 'A-Tocopherol' where Nutr_No = 323;
update tnutr_def set NutrDesc = 'Vit. D, IU' where Nutr_No = 324;
update tnutr_def set NutrDesc = 'Vitamin D2' where Nutr_No = 325;
update tnutr_def set NutrDesc = 'Vitamin D3' where Nutr_No = 326;
update tnutr_def set NutrDesc = 'Vitamin D' where Nutr_No = 328;
update tnutr_def set NutrDesc = 'B-Cryptoxanth.' where Nutr_No = 334;
update tnutr_def set NutrDesc = 'Lycopene' where Nutr_No = 337;
update tnutr_def set NutrDesc = 'Lutein+Zeaxan.' where Nutr_No = 338;
update tnutr_def set NutrDesc = 'B-Tocopherol' where Nutr_No = 341;
update tnutr_def set NutrDesc = 'G-Tocopherol' where Nutr_No = 342;
update tnutr_def set NutrDesc = 'D-Tocopherol' where Nutr_No = 343;
update tnutr_def set NutrDesc = 'A-Tocotrienol' where Nutr_No = 344;
update tnutr_def set NutrDesc = 'B-Tocotrienol' where Nutr_No = 345;
update tnutr_def set NutrDesc = 'G-Tocotrienol' where Nutr_No = 346;
update tnutr_def set NutrDesc = 'D-Tocotrienol' where Nutr_No = 347;
update tnutr_def set NutrDesc = 'Vitamin C' where Nutr_No = 401;
update tnutr_def set NutrDesc = 'Thiamin' where Nutr_No = 404;
update tnutr_def set NutrDesc = 'Riboflavin' where Nutr_No = 405;
update tnutr_def set NutrDesc = 'Niacin' where Nutr_No = 406;
update tnutr_def set NutrDesc = 'Panto. Acid' where Nutr_No = 410;
update tnutr_def set NutrDesc = 'Vitamin B6' where Nutr_No = 415;
update tnutr_def set NutrDesc = 'Folate' where Nutr_No = 417;
update tnutr_def set NutrDesc = 'Vitamin B12' where Nutr_No = 418;
update tnutr_def set NutrDesc = 'Choline' where Nutr_No = 421;
update tnutr_def set NutrDesc = 'Menaquinone-4' where Nutr_No = 428;
update tnutr_def set NutrDesc = 'Dihydro-K1' where Nutr_No = 429;
update tnutr_def set NutrDesc = 'Vitamin K1' where Nutr_No = 430;
update tnutr_def set NutrDesc = 'Folic Acid' where Nutr_No = 431;
update tnutr_def set NutrDesc = 'Folate, food' where Nutr_No = 432;
update tnutr_def set NutrDesc = 'Folate, DFE' where Nutr_No = 435;
update tnutr_def set NutrDesc = 'Betaine' where Nutr_No = 454;
update tnutr_def set NutrDesc = 'Tryptophan' where Nutr_No = 501;
update tnutr_def set NutrDesc = 'Threonine' where Nutr_No = 502;
update tnutr_def set NutrDesc = 'Isoleucine' where Nutr_No = 503;
update tnutr_def set NutrDesc = 'Leucine' where Nutr_No = 504;
update tnutr_def set NutrDesc = 'Lysine' where Nutr_No = 505;
update tnutr_def set NutrDesc = 'Methionine' where Nutr_No = 506;
update tnutr_def set NutrDesc = 'Cystine' where Nutr_No = 507;
update tnutr_def set NutrDesc = 'Phenylalanine' where Nutr_No = 508;
update tnutr_def set NutrDesc = 'Tyrosine' where Nutr_No = 509;
update tnutr_def set NutrDesc = 'Valine' where Nutr_No = 510;
update tnutr_def set NutrDesc = 'Arginine' where Nutr_No = 511;
update tnutr_def set NutrDesc = 'Histidine' where Nutr_No = 512;
update tnutr_def set NutrDesc = 'Alanine' where Nutr_No = 513;
update tnutr_def set NutrDesc = 'Aspartic acid' where Nutr_No = 514;
update tnutr_def set NutrDesc = 'Glutamic acid' where Nutr_No = 515;
update tnutr_def set NutrDesc = 'Glycine' where Nutr_No = 516;
update tnutr_def set NutrDesc = 'Proline' where Nutr_No = 517;
update tnutr_def set NutrDesc = 'Serine' where Nutr_No = 518;
update tnutr_def set NutrDesc = 'Hydroxyproline' where Nutr_No = 521;
update tnutr_def set NutrDesc = 'Vit. E added' where Nutr_No = 573;
update tnutr_def set NutrDesc = 'Vit. B12 added' where Nutr_No = 578;
update tnutr_def set NutrDesc = 'Cholesterol' where Nutr_No = 601;
update tnutr_def set NutrDesc = 'Trans Fat' where Nutr_No = 605;
update tnutr_def set NutrDesc = 'Sat Fat' where Nutr_No = 606;
update tnutr_def set NutrDesc = '4:0' where Nutr_No = 607;
update tnutr_def set NutrDesc = '6:0' where Nutr_No = 608;
update tnutr_def set NutrDesc = '8:0' where Nutr_No = 609;
update tnutr_def set NutrDesc = '10:0' where Nutr_No = 610;
update tnutr_def set NutrDesc = '12:0' where Nutr_No = 611;
update tnutr_def set NutrDesc = '14:0' where Nutr_No = 612;
update tnutr_def set NutrDesc = '16:0' where Nutr_No = 613;
update tnutr_def set NutrDesc = '18:0' where Nutr_No = 614;
update tnutr_def set NutrDesc = '20:0' where Nutr_No = 615;
update tnutr_def set NutrDesc = '18:1' where Nutr_No = 617;
update tnutr_def set NutrDesc = '18:2' where Nutr_No = 618;
update tnutr_def set NutrDesc = '18:3' where Nutr_No = 619;
update tnutr_def set NutrDesc = '20:4' where Nutr_No = 620;
update tnutr_def set NutrDesc = '22:6n-3' where Nutr_No = 621;
update tnutr_def set NutrDesc = '22:0' where Nutr_No = 624;
update tnutr_def set NutrDesc = '14:1' where Nutr_No = 625;
update tnutr_def set NutrDesc = '16:1' where Nutr_No = 626;
update tnutr_def set NutrDesc = '18:4' where Nutr_No = 627;
update tnutr_def set NutrDesc = '20:1' where Nutr_No = 628;
update tnutr_def set NutrDesc = '20:5n-3' where Nutr_No = 629;
update tnutr_def set NutrDesc = '22:1' where Nutr_No = 630;
update tnutr_def set NutrDesc = '22:5n-3' where Nutr_No = 631;
update tnutr_def set NutrDesc = 'Phytosterols' where Nutr_No = 636;
update tnutr_def set NutrDesc = 'Stigmasterol' where Nutr_No = 638;
update tnutr_def set NutrDesc = 'Campesterol' where Nutr_No = 639;
update tnutr_def set NutrDesc = 'BetaSitosterol' where Nutr_No = 641;
update tnutr_def set NutrDesc = 'Mono Fat' where Nutr_No = 645;
update tnutr_def set NutrDesc = 'Poly Fat' where Nutr_No = 646;
update tnutr_def set NutrDesc = '15:0' where Nutr_No = 652;
update tnutr_def set NutrDesc = '17:0' where Nutr_No = 653;
update tnutr_def set NutrDesc = '24:0' where Nutr_No = 654;
update tnutr_def set NutrDesc = '16:1t' where Nutr_No = 662;
update tnutr_def set NutrDesc = '18:1t' where Nutr_No = 663;
update tnutr_def set NutrDesc = '22:1t' where Nutr_No = 664;
update tnutr_def set NutrDesc = '18:2t' where Nutr_No = 665;
update tnutr_def set NutrDesc = '18:2i' where Nutr_No = 666;
update tnutr_def set NutrDesc = '18:2t,t' where Nutr_No = 669;
update tnutr_def set NutrDesc = '18:2CLA' where Nutr_No = 670;
update tnutr_def set NutrDesc = '24:1c' where Nutr_No = 671;
update tnutr_def set NutrDesc = '20:2n-6c,c' where Nutr_No = 672;
update tnutr_def set NutrDesc = '16:1c' where Nutr_No = 673;
update tnutr_def set NutrDesc = '18:1c' where Nutr_No = 674;
update tnutr_def set NutrDesc = '18:2n-6c,c' where Nutr_No = 675;
update tnutr_def set NutrDesc = '22:1c' where Nutr_No = 676;
update tnutr_def set NutrDesc = '18:3n-6c,c,c' where Nutr_No = 685;
update tnutr_def set NutrDesc = '17:1' where Nutr_No = 687;
update tnutr_def set NutrDesc = '20:3' where Nutr_No = 689;
update tnutr_def set NutrDesc = 'TransMonoenoic' where Nutr_No = 693;
update tnutr_def set NutrDesc = 'TransPolyenoic' where Nutr_No = 695;
update tnutr_def set NutrDesc = '13:0' where Nutr_No = 696;
update tnutr_def set NutrDesc = '15:1' where Nutr_No = 697;
update tnutr_def set NutrDesc = '18:3n-3c,c,c' where Nutr_No = 851;
update tnutr_def set NutrDesc = '20:3n-3' where Nutr_No = 852;
update tnutr_def set NutrDesc = '20:3n-6' where Nutr_No = 853;
update tnutr_def set NutrDesc = '20:4n-6' where Nutr_No = 855;
update tnutr_def set NutrDesc = '18:3i' where Nutr_No = 856;
update tnutr_def set NutrDesc = '21:5' where Nutr_No = 857;
update tnutr_def set NutrDesc = '22:4' where Nutr_No = 858;
update tnutr_def set NutrDesc = '18:1n-7t' where Nutr_No = 859;
insert or ignore into tnutr_def values(3000,'kc','PROT_KCAL','Protein Calories', NULL, NULL);
insert or ignore into tnutr_def values(3001,'kc','FAT_KCAL','Fat Calories', NULL, NULL);
insert or ignore into tnutr_def values(3002,'kc','CHO_KCAL','Carb Calories', NULL, NULL);
insert or ignore into tnutr_def values(2000,'g','CHO_NONFIB','Non-Fiber Carb', NULL, NULL);
insert or ignore into tnutr_def values(2001,'g','LA','LA', NULL, NULL);
insert or ignore into tnutr_def values(2002,'g','AA','AA', NULL, NULL);
insert or ignore into tnutr_def values(2003,'g','ALA','ALA', NULL, NULL);
insert or ignore into tnutr_def values(2004,'g','EPA','EPA', NULL, NULL);
insert or ignore into tnutr_def values(2005,'g','DHA','DHA', NULL, NULL);
insert or ignore into tnutr_def values(2006,'g','OMEGA6','Omega-6', NULL, NULL);
insert or ignore into tnutr_def values(3003,'g','SHORT6','Short-chain Omega-6', NULL, NULL);
insert or ignore into tnutr_def values(3004,'g','LONG6','Long-chain Omega-6', NULL, NULL);
insert or ignore into tnutr_def values(2007,'g','OMEGA3','Omega-3', NULL, NULL);
insert or ignore into tnutr_def values(3005,'g','SHORT3','Short-chain Omega-3', NULL, NULL);
insert or ignore into tnutr_def values(3006,'g','LONG3','Long-chain Omega-3', NULL, NULL);

-- These are the new "daily value" labeling standards minus "ADDED SUGARS" which
-- have not yet appeared in the USDA data.

insert or ignore into tnutr_def values(2008,'mg','VITE','Vitamin E', NULL, NULL);
update tnutr_def set dv_default = 2000.0 where Tagname = 'ENERC_KCAL';
update tnutr_def set dv_default = 50.0 where Tagname = 'PROCNT';
update tnutr_def set dv_default = 78.0 where Tagname = 'FAT';
update tnutr_def set dv_default = 275.0 where Tagname = 'CHOCDF';
update tnutr_def set dv_default = 28.0 where Tagname = 'FIBTG';
update tnutr_def set dv_default = 247.0 where Tagname = 'CHO_NONFIB';
update tnutr_def set dv_default = 1300.0 where Tagname = 'CA';
update tnutr_def set dv_default = 1250.0 where Tagname = 'P';
update tnutr_def set dv_default = 18.0 where Tagname = 'FE';
update tnutr_def set dv_default = 2300.0 where Tagname = 'NA';
update tnutr_def set dv_default = 4700.0 where Tagname = 'K';
update tnutr_def set dv_default = 420.0 where Tagname = 'MG';
update tnutr_def set dv_default = 11.0 where Tagname = 'ZN';
update tnutr_def set dv_default = 0.9 where Tagname = 'CU';
update tnutr_def set dv_default = 2.3 where Tagname = 'MN';
update tnutr_def set dv_default = 55.0 where Tagname = 'SE';
update tnutr_def set dv_default = null where Tagname = 'VITA_IU';
update tnutr_def set dv_default = 900.0 where Tagname = 'VITA_RAE';
update tnutr_def set dv_default = 15.0 where Tagname = 'VITE';
update tnutr_def set dv_default = 120.0 where Tagname = 'VITK1';
update tnutr_def set dv_default = 1.2 where Tagname = 'THIA';
update tnutr_def set dv_default = 1.3 where Tagname = 'RIBF';
update tnutr_def set dv_default = 16.0 where Tagname = 'NIA';
update tnutr_def set dv_default = 5.0 where Tagname = 'PANTAC';
update tnutr_def set dv_default = 1.7 where Tagname = 'VITB6A';
update tnutr_def set dv_default = 400.0 where Tagname = 'FOL';
update tnutr_def set dv_default = 2.4 where Tagname = 'VITB12';
update tnutr_def set dv_default = 550.0 where Tagname = 'CHOLN';
update tnutr_def set dv_default = 90.0 where Tagname = 'VITC';
update tnutr_def set dv_default = 20.0 where Tagname = 'FASAT';
update tnutr_def set dv_default = 300.0 where Tagname = 'CHOLE';
update tnutr_def set dv_default = null where Tagname = 'VITD';
update tnutr_def set dv_default = 20.0 where Tagname = 'VITD_BOTH';
update tnutr_def set dv_default = 8.9 where Tagname = 'FAPU';
update tnutr_def set dv_default = 0.2 where Tagname = 'AA';
update tnutr_def set dv_default = 3.8 where Tagname = 'ALA';
update tnutr_def set dv_default = 0.1 where Tagname = 'EPA';
update tnutr_def set dv_default = 0.1 where Tagname = 'DHA';
update tnutr_def set dv_default = 4.7 where Tagname = 'LA';
update tnutr_def set dv_default = 4.0 where Tagname = 'OMEGA3';
update tnutr_def set dv_default = 4.9 where Tagname = 'OMEGA6';
update tnutr_def set dv_default = 32.6 where Tagname = 'FAMS';
update tnutr_def set nutopt = 0.0 where dv_default > 0.0 and nutopt is null;
delete from nutr_def;
insert into nutr_def select * from tnutr_def;
create index if not exists tagname_index on nutr_def (Tagname asc);
drop table ttnutr_def;
drop table tnutr_def;

/* Update table fg_group */

insert or replace into fd_group select trim(FdGrp_Cd, '~'), trim(FdGrp_Desc, '~') from tfd_group;
insert or replace into fd_group values (9999, 'Added Recipes');
drop table tfd_group;

/* Update table food_des. */

INSERT OR REPLACE INTO food_des (NDB_No, FdGrp_Cd, Long_Desc, Shrt_Desc, Ref_desc, Refuse, Pro_Factor, Fat_Factor, CHO_Factor) select trim(NDB_No, '~'), trim(FdGrp_Cd, '~'), replace(trim(trim(Long_Desc, '~') || ' (' || trim(SciName, '~') || ')',' ('),' ()',''), upper(substr(trim(Shrt_Desc, '~'),1,1)) || lower(substr(trim(Shrt_Desc, '~'),2)), trim(Ref_desc, '~'), Refuse, Pro_Factor, Fat_Factor, CHO_Factor from tfood_des;
update food_des set Shrt_Desc = Long_Desc where length(Long_Desc) <= 60;
  
drop table tfood_des;
  
/* 
   the weight table is next, and needs a little explanation.  The Seq
   column is a key and starts at 1 from the USDA; however, we want
   the user to be able to select his own serving unit, and we do that
   by changing the serving unit the user wants to Seq = 0, while saving
   what the original Seq was in the origSeq column so that we can get back
   later.  Furthermore, a min(Seq) as grouped by NDB_No can have its weight
   modified in order to save a preferred serving size, so we also make a copy
   of the original weight of the serving unit called origGm_Wgt.  Thus we
   always get the Amount of the serving to be displayed by the equation:
	Amount displayed = Gm_Wgt / origGm_Wgt * Amount
*/

update tweight set NDB_No = trim(NDB_No,'~');
update tweight set Seq = trim(Seq,'~');
update tweight set Msre_Desc = trim(Msre_Desc,'~');

--We want every food to have a weight, so we make a '100 grams' weight
insert or replace into zweight select NDB_No, 99, 100, 'grams', 100, 99, 100 from food_des;

--Now we update zweight with the user's existing weight preferences
insert or replace into zweight select * from weight where Seq != origSeq or Gm_Wgt != origGm_Wgt;

--We overwrite real weight table with new USDA records
INSERT OR REPLACE INTO weight select NDB_No, Seq, Amount, Msre_Desc, Gm_Wgt, Seq, Gm_Wgt from tweight;

--We overwrite the real weight table with the original user mods
insert or replace into weight select * from zweight;
drop table tweight;
drop table zweight;

/* Update table nut_data */

insert or replace into nut_data select trim(NDB_No, '~'), trim(Nutr_No, '~'), Nutr_Val from tnut_data;
drop table tnut_data;
  
/* NUT has derived nutrient values that are handled as if they are
   USDA nutrients to save a lot of computation and confusion at runtime
   because the values are already there */

  --insert VITE records into nut_data
insert or replace into nut_data select f.NDB_No, 2008, ifnull(tocpha.Nutr_Val, 0.0) from food_des f left join nut_data tocpha on f.NDB_No = tocpha.NDB_No and tocpha.Nutr_No = 323 where tocpha.Nutr_Val is not null;
  
  --insert LA records into nut_data
insert or replace into nut_data select f.NDB_No, 2001, case when f18d2cn6.Nutr_Val is not null then f18d2cn6.Nutr_Val when f18d2.Nutr_Val is not null then f18d2.Nutr_Val - ifnull(f18d2t.Nutr_Val, 0.0) - ifnull(f18d2tt.Nutr_Val, 0.0) - ifnull(f18d2i.Nutr_Val, 0.0) - ifnull(f18d2cla.Nutr_Val, 0.0) end from food_des f left join nut_data f18d2 on f.NDB_No = f18d2.NDB_No and f18d2.Nutr_No = 618 left join nut_data f18d2cn6 on f.NDB_No = f18d2cn6.NDB_No and f18d2cn6.Nutr_No = 675 left join nut_data f18d2t on f.NDB_No = f18d2t.NDB_No and f18d2t.Nutr_No = 665 left join nut_data f18d2tt on f.NDB_No = f18d2tt.NDB_No and f18d2tt.Nutr_No = 669 left join nut_data f18d2i on f.NDB_No = f18d2i.NDB_No and f18d2i.Nutr_No = 666 left join nut_data f18d2cla on f.NDB_No = f18d2cla.NDB_No and f18d2cla.Nutr_No = 670 where f18d2.Nutr_Val is not null or f18d2cn6.Nutr_Val is not null or f18d2t.Nutr_Val is not null or f18d2tt.Nutr_Val is not null or f18d2i.Nutr_Val is not null or f18d2cla.Nutr_Val is not null;
  
  --insert ALA records into nut_data
insert or replace into nut_data select f.NDB_No, 2003, case when f18d3cn3.Nutr_Val is not null then f18d3cn3.Nutr_Val when f18d3.Nutr_Val is not null then f18d3.Nutr_Val - ifnull(f18d3cn6.Nutr_Val, 0.0) - ifnull(f18d3i.Nutr_Val, 0.0) end from food_des f left join nut_data f18d3 on f.NDB_No = f18d3.NDB_No and f18d3.Nutr_No = 619 left join nut_data f18d3cn3 on f.NDB_No = f18d3cn3.NDB_No and f18d3cn3.Nutr_No = 851 left join nut_data f18d3cn6 on f.NDB_No = f18d3cn6.NDB_No and f18d3cn6.Nutr_No = 685 left join nut_data f18d3i on f.NDB_No = f18d3i.NDB_No and f18d3i.Nutr_No = 856 where f18d3.Nutr_Val is not null or f18d3cn3.Nutr_Val is not null or f18d3cn6.Nutr_Val is not null or f18d3i.Nutr_Val is not null;
  
  --insert SHORT6 records into nut_data
insert or replace into nut_data select f.NDB_No, 3003, ifnull(la.Nutr_Val, 0.0) + ifnull(f18d3cn6.Nutr_Val, 0.0) from food_des f left join nut_data la on f.NDB_No = la.NDB_No and la.Nutr_No = 2001 left join nut_data f18d3cn6 on f.NDB_No = f18d3cn6.NDB_No and f18d3cn6.Nutr_No = 685 where la.Nutr_Val is not null or f18d3cn6.Nutr_Val is not null;
  
  --insert SHORT3 records into nut_data
insert or replace into nut_data select f.NDB_No, 3005, ifnull(ala.Nutr_Val, 0.0) + ifnull(f18d4.Nutr_Val, 0.0) from food_des f left join nut_data ala on f.NDB_No = ala.NDB_No and ala.Nutr_No = 2003 left join nut_data f18d4 on f.NDB_No = f18d4.NDB_No and f18d4.Nutr_No = 627 where ala.Nutr_Val is not null or f18d4.Nutr_Val is not null;
  
  --insert AA records into nut_data
insert or replace into nut_data select f.NDB_No, 2002, case when f20d4n6.Nutr_Val is not null then f20d4n6.Nutr_Val else f20d4.Nutr_Val end from food_des f left join nut_data f20d4 on f.NDB_No = f20d4.NDB_No and f20d4.Nutr_No = 620 left join nut_data f20d4n6 on f.NDB_No = f20d4n6.NDB_No and f20d4n6.Nutr_No = 855 where f20d4.Nutr_Val is not null or f20d4n6.Nutr_Val is not null;
  
  --insert LONG6 records into nut_data
insert or replace into nut_data select f.NDB_No, 3004, case when f20d3n6.Nutr_Val is not null then ifnull(aa.Nutr_Val,0.0) + f20d3n6.Nutr_Val + ifnull(f22d4.Nutr_Val,0.0) else ifnull(aa.Nutr_Val,0.0) + ifnull(f20d3.Nutr_Val,0.0) + ifnull(f22d4.Nutr_Val, 0.0) end from food_des f left join nut_data aa on f.NDB_No = aa.NDB_No and aa.Nutr_No = 2002 left join nut_data f20d3n6 on f.NDB_No = f20d3n6.NDB_No and f20d3n6.Nutr_No = 853 left join nut_data f20d3 on f.NDB_No = f20d3.NDB_No and f20d3.Nutr_No = 689 left join nut_data f22d4 on f.NDB_No = f22d4.NDB_No and f22d4.Nutr_No = 858 where aa.Nutr_Val is not null or f20d3n6.Nutr_Val is not null or f20d3.Nutr_Val is not null or f22d4.Nutr_Val is not null;
  
  --insert EPA records into nut_data
insert or replace into nut_data select f.NDB_No, 2004, f20d5.Nutr_Val from food_des f left join nut_data f20d5 on f.NDB_No = f20d5.NDB_No and f20d5.Nutr_No = 629 where f20d5.Nutr_Val is not null;
  
  --insert DHA records into nut_data
insert or replace into nut_data select f.NDB_No, 2005, f22d6.Nutr_Val from food_des f left join nut_data f22d6 on f.NDB_No = f22d6.NDB_No and f22d6.Nutr_No = 621 where f22d6.Nutr_Val is not null;
  
  --insert LONG3 records into nut_data
insert or replace into nut_data select f.NDB_No, 3006, ifnull(epa.Nutr_Val, 0.0) + ifnull(dha.Nutr_Val, 0.0) + ifnull(f20d3n3.Nutr_Val, 0.0) + ifnull(f22d5.Nutr_Val, 0.0) from food_des f left join nut_data epa on f.NDB_No = epa.NDB_No and epa.Nutr_No = 2004 left join nut_data dha on f.NDB_No = dha.NDB_No and dha.Nutr_No = 2005 left join nut_data f20d3n3 on f.NDB_No = f20d3n3.NDB_No and f20d3n3.Nutr_No = 852 left join nut_data f22d5 on f.NDB_No = f22d5.NDB_No and f22d5.Nutr_No = 631 where epa.Nutr_Val is not null or dha.Nutr_Val is not null or f20d3n3.Nutr_Val is not null or f22d5.Nutr_Val is not null;
  
  --insert OMEGA6 records into nut_data
insert or replace into nut_data select f.NDB_No, 2006, ifnull(short6.Nutr_Val, 0.0) + ifnull(long6.Nutr_Val, 0.0) from food_des f left join nut_data short6 on f.NDB_No = short6.NDB_No and short6.Nutr_No = 3003 left join nut_data long6 on f.NDB_No = long6.NDB_No and long6.Nutr_No = 3004 where short6.Nutr_Val is not null or long6.Nutr_Val is not null;
  
  --insert OMEGA3 records into nut_data
insert or replace into nut_data select f.NDB_No, 2007, ifnull(short3.Nutr_Val, 0.0) + ifnull(long3.Nutr_Val, 0.0) from food_des f left join nut_data short3 on f.NDB_No = short3.NDB_No and short3.Nutr_No = 3005 left join nut_data long3 on f.NDB_No = long3.NDB_No and long3.Nutr_No = 3006 where short3.Nutr_Val is not null or long3.Nutr_Val is not null;
  
  --insert CHO_NONFIB records into nut_data
insert or replace into nut_data select f.NDB_No, 2000, case when chocdf.Nutr_Val - ifnull(fibtg.Nutr_Val, 0.0) < 0.0 then 0.0 else chocdf.Nutr_Val - ifnull(fibtg.Nutr_Val, 0.0) end from food_des f left join nut_data chocdf on f.NDB_No = chocdf.NDB_No and chocdf.Nutr_No = 205 left join nut_data fibtg on f.NDB_No = fibtg.NDB_No and fibtg.Nutr_No = 291 where chocdf.Nutr_Val is not null;
  
  --replace empty strings with values for macronutrient factors in food_des
update food_des set Pro_Factor = 4.0 where Pro_Factor = '' or Pro_Factor is null;
update food_des set Fat_Factor = 9.0 where Fat_Factor = '' or Fat_Factor is null;
update food_des set CHO_Factor = 4.0 where CHO_Factor = '' or CHO_Factor is null;
  
  --insert calories from macronutrients into nut_data
insert or replace into nut_data select f.NDB_No, 3000, f.Pro_Factor * procnt.Nutr_Val from food_des f join nut_data procnt on f.NDB_No = procnt.NDB_No and procnt.Nutr_No = 203;
insert or replace into nut_data select f.NDB_No, 3001, f.Fat_Factor * fat.Nutr_Val from food_des f join nut_data fat on f.NDB_No = fat.NDB_No and fat.Nutr_No = 204;
insert or replace into nut_data select f.NDB_No, 3002, f.CHO_Factor * chocdf.Nutr_Val from food_des f join nut_data chocdf on f.NDB_No = chocdf.NDB_No and chocdf.Nutr_No = 205;
  
/* NUT needs some additional permanent tables for options, mealfoods, archive
   of mealfoods if meals per day changes, customary meals (theusual), and
   the weight log */

/* This table is global options:  
    defanal_am    how many meals to analyze starting at the latest and going
                  back in time
    FAPU1         the "target" for Omega-6/3 balance
    meals_per_day yes, meals per day
    grams         boolean true means grams, false means ounces avoirdupois and
                  never means fluid ounces
    currentmeal   10 digit integer YYYYMMDDxx where xx is daily meal number
    wltweak       Part of the automatic calorie set feature.  If NUT moves the
                  calories during a cycle to attempt better body composition,
                  wltweak is true.  It is always changed to false at the
                  beginning of a cycle.  However, current algorithm doesn't use it.
    wlpolarity    In order not to favor gaining lean mass over losing fat mass,
                  NUT cycles this between true and false to alternate strategies.
                  However, current algorithm doesn't use it.
    autocal       0 means no autocal feature, 2 means feature turned on.
                  The autocal feature moves calories to try to achieve
                  a calorie level that allows both fat mass loss and lean mass
                  gain.
*/

create table if not exists options(protect integer primary key, defanal_am integer default 2147123119, FAPU1 real default 0.0, meals_per_day int default 3, grams int default 1, currentmeal int default 0, wltweak integer default 0, wlpolarity integer default 0, autocal integer default 0);

/*
   The table of what and how much eaten at each meal, plus a place for a
   nutrient number to signify automatic portion control on this serving.
   Automatic portion control (PCF) means add up everything from this meal
   for this single nutrient and then adjust the quantity of this particular
   food so that the daily value is exactly satisfied.
*/
 
create table if not exists mealfoods(meal_id int, NDB_No int, Gm_Wgt real, Nutr_No int, primary key(meal_id, NDB_No));

/* 
   There is no easy way to analyze a meal where each day can have a 
   different number of meals per day because you have to do a lot of computation
   to combine the meals, and for any particular meal, you cannot provide 
   guidance because you don't know how many more meals are coming for the day.
   So, when the user changes meals_per_day we archive the non-compliant meals
   (different number of meals per day from new setting)  and restore the
   compliant ones (same number of meals per day as new setting).
*/

create table if not exists archive_mealfoods(meal_id int, NDB_No int, Gm_Wgt real, meals_per_day integer, primary key(meal_id desc, NDB_No asc, meals_per_day));

/* Table of customary meals which also has a Nutr_No for specification of
   PCF or automatic portion control.  We call it z_tu so we can define a
   "theusual" view later to better control user interaction.
*/

create table if not exists z_tu(meal_name text, NDB_No int, Nutr_No int, primary key(meal_name, NDB_No), unique(meal_name, Nutr_No));

/* The weight log.  When the weight log is "cleared" the info is not erased.
   Null cleardates identify the current log.  As we have been doing, we call
   the real table z_wl, so we can have a couple of views that allow us to
   control user interaction, wlog and wlsummary.
*/

create table if not exists z_wl(weight real, bodyfat real, wldate int, cleardate int, primary key(wldate, cleardate));

/* To protect table options from extraneous inserts we create a trigger */

drop trigger if exists protect_options;
create trigger protect_options after insert on options begin delete from options where protect != 1; end;

/* This insert will have no effect if options are already there */

insert into options default values;

drop trigger protect_options;
commit;
vacuum;