# #######################################################################################################
# Create tables with zero values for distributions
# #######################################################################################################
# ERG (A. DenBleyker) updated this script on 12-4-2015 to reference the most recent MOVES database (20151028) and truncate the hotellinghours table.
# ERG (D. Jackson), 01aug16: Updated this script to allow the database to be specified within the script.
# ERG (D. Jackson), 13sep17: Updated to seed the sourceTypeYearVMT table and to ensure that all sourceTypeIDs are in
# sourceTypeYearVMT and sourceTypeYear
# ERG (A. DenBleyker), 10apr18: Updated to ensure all road type IDs are in the zoneRoadType table
# ERG (A. DenBleyker), 16jul19: Updated to latest MOVES database, 20181022.

USE CDBPLACEHOLDER;

-- flush tables;

create database if not exists test;

# create avgspeeddistribution table with zero values.
drop table if exists test.z_avgspeeddistribution;
create table test.z_avgspeeddistribution
 like movesdb20181022.avgspeeddistribution;
insert into test.z_avgspeeddistribution
 select * from movesdb20181022.avgspeeddistribution;
update test.z_avgspeeddistribution
 set avgspeedfraction=0.0;

# create dayvmtfraction table with zero values.
drop table if exists test.z_dayvmtfraction;
create table test.z_dayvmtfraction
 like movesdb20181022.dayvmtfraction;
insert into test.z_dayvmtfraction
 select * from movesdb20181022.dayvmtfraction;
update test.z_dayvmtfraction
 set dayvmtfraction=0.0;

# create hourvmtfraction table with zero values.
drop table if exists test.z_hourvmtfraction;
create table test.z_hourvmtfraction
 like movesdb20181022.hourvmtfraction;
insert into test.z_hourvmtfraction
 select * from movesdb20181022.hourvmtfraction;
update test.z_hourvmtfraction
 set hourvmtfraction=0.0;

# create monthvmtfraction table with zero values.
drop table if exists test.z_monthvmtfraction;
create table test.z_monthvmtfraction
 like movesdb20181022.monthvmtfraction;
insert into test.z_monthvmtfraction
 select * from movesdb20181022.monthvmtfraction;
update test.z_monthvmtfraction
 set monthvmtfraction=0.0;

# create roadtypedistribution table with zero values.
drop table if exists test.z_roadtypedistribution;
create table test.z_roadtypedistribution
 like movesdb20181022.roadtypedistribution;
insert into test.z_roadtypedistribution
 select * from movesdb20181022.roadtypedistribution;
update test.z_roadtypedistribution
 set roadtypevmtfraction=0.0;

# create sourcetypeagedistribution table with zero values.
drop table if exists test.z_sourcetypeagedistribution;
create table test.z_sourcetypeagedistribution
 like movesdb20181022.sourcetypeagedistribution;
insert into test.z_sourcetypeagedistribution
 select a.* from movesdb20181022.sourcetypeagedistribution as a,
          year as b
 where a.yearid=b.yearid;
update test.z_sourcetypeagedistribution
 set agefraction=0.0;

# create avft table with zero values.
drop table if exists test.z_avft;
create table test.z_avft
 like movesdb20181022.avft;
insert into test.z_avft
 select * from seed.avft;
update test.z_avft
 set fuelengfraction=0.0;

# =======================================================================================================
# avgspeeddistribution
# =======================================================================================================

-- flush tables;

# create working copy of the table.
drop table if exists test.s_avgspeeddistribution;
create table test.s_avgspeeddistribution
 like movesdb20181022.avgspeeddistribution;

# Populate the copy with zero values.
insert into test.s_avgspeeddistribution
 select * from test.z_avgspeeddistribution;

# Update the copy with state supplied values from the CDB.
# The copy will have zeros for any missing rows.
update test.s_avgspeeddistribution as a,
       avgspeeddistribution as b
 set a.avgspeedfraction=b.avgspeedfraction
 where a.sourcetypeid=b.sourcetypeid
   and a.roadtypeid=b.roadtypeid
   and a.hourdayid=b.hourdayid
   and a.avgspeedbinid=b.avgspeedbinid;

# Eliminate and distributions that are missing.
drop table if exists test.junk;
create table test.junk
 select sourcetypeid,roadtypeid,hourdayid,avgspeedbinid,sum(avgspeedfraction) as fracsum
 from test.s_avgspeeddistribution
 group by sourcetypeid,roadtypeid,hourdayid;
alter table test.junk add index myidx (sourcetypeid,roadtypeid,hourdayid);
alter table test.s_avgspeeddistribution add fracsum double;
update test.s_avgspeeddistribution as a,
       test.junk as b
 set a.fracsum=b.fracsum
 where a.sourcetypeid=b.sourcetypeid
   and a.roadtypeid=b.roadtypeid
   and a.hourdayid=b.hourdayid;
delete from test.s_avgspeeddistribution
 where fracsum<0.1;

# Create new table to contain seeded data.
drop table if exists test.n_avgspeeddistribution;
create table test.n_avgspeeddistribution
 like movesdb20181022.avgspeeddistribution;

# Populate the new table with the default data.
insert into test.n_avgspeeddistribution
 select * from movesdb20181022.avgspeeddistribution;

# Update the new table with non-zero state supplied values.
# Cases with zero will now use default distributions.
update test.n_avgspeeddistribution as a,
       avgspeeddistribution as b
 set a.avgspeedfraction=b.avgspeedfraction
 where a.sourcetypeid=b.sourcetypeid
   and a.roadtypeid=b.roadtypeid
   and a.hourdayid=b.hourdayid
   and a.avgspeedbinid=b.avgspeedbinid;

# Ensure that all cases will have results
# by substituting a small poitive value
# for every case with a zero.
update test.n_avgspeeddistribution
 set avgspeedfraction=1.0E-15
 where avgspeedfraction<1.0E-15;

# Truncate and update the CDB table.
truncate avgspeeddistribution;
insert into avgspeeddistribution
 select * from test.n_avgspeeddistribution;
 
# =======================================================================================================
# dayvmtfraction
# =======================================================================================================

-- flush tables;

# create working copy of the table.
drop table if exists test.s_dayvmtfraction;
create table test.s_dayvmtfraction
 like movesdb20181022.dayvmtfraction;

# Populate the copy with zero values.
insert into test.s_dayvmtfraction
 select * from test.z_dayvmtfraction;

# Update the copy with state supplied values from the CDB.
# The copy will have zeros for any missing rows.
update test.s_dayvmtfraction as a,
       dayvmtfraction as b
 set a.dayvmtfraction=b.dayvmtfraction
 where a.sourcetypeid=b.sourcetypeid
   and a.monthid=b.monthid
   and a.roadtypeid=b.roadtypeid
   and a.dayid=b.dayid;

# Eliminate and distributions that are missing.
drop table if exists test.junk;
create table test.junk
 select sourcetypeid,monthid,roadtypeid,sum(dayvmtfraction) as fracsum
 from test.s_dayvmtfraction
 group by sourcetypeid,monthid,roadtypeid;
alter table test.junk add index myidx (sourcetypeid,monthid,roadtypeid);
alter table test.s_dayvmtfraction add fracsum double;
update test.s_dayvmtfraction as a,
       test.junk as b
 set a.fracsum=b.fracsum
 where a.sourcetypeid=b.sourcetypeid
   and a.monthid=b.monthid
   and a.roadtypeid=b.roadtypeid;
delete from test.s_dayvmtfraction
 where fracsum<0.1;

# Create new table to contain seeded data.
drop table if exists test.n_dayvmtfraction;
create table test.n_dayvmtfraction
 like movesdb20181022.dayvmtfraction;

# Populate the new table with the default data.
insert into test.n_dayvmtfraction
 select * from movesdb20181022.dayvmtfraction;

# Update the new table with non-zero state supplied values.
# Cases with zero will now use default distributions.
update test.n_dayvmtfraction as a,
       test.s_dayvmtfraction as b
 set a.dayvmtfraction=b.dayvmtfraction
 where a.sourcetypeid=b.sourcetypeid
   and a.monthid=b.monthid
   and a.roadtypeid=b.roadtypeid
   and a.dayid=b.dayid;

# Ensure that all cases will have results
# by substituting a small poitive value
# for every case with a zero.
update test.n_dayvmtfraction
 set dayvmtfraction=1.0E-15
 where dayvmtfraction<1.0E-15;

# Truncate and update the CDB table.
truncate dayvmtfraction;
insert into dayvmtfraction
 select * from test.n_dayvmtfraction;

# =======================================================================================================
# hourvmtfraction
# =======================================================================================================

-- flush tables;

# create working copy of the table.
drop table if exists test.s_hourvmtfraction;
create table test.s_hourvmtfraction
 like movesdb20181022.hourvmtfraction;

# Populate the copy with zero values.
insert into test.s_hourvmtfraction
 select * from test.z_hourvmtfraction;

# Update the copy with state supplied values from the CDB.
# The copy will have zeros for any missing rows.
update test.s_hourvmtfraction as a,
       hourvmtfraction as b
 set a.hourvmtfraction=b.hourvmtfraction
 where a.sourcetypeid=b.sourcetypeid
   and a.roadtypeid=b.roadtypeid
   and a.dayid=b.dayid
   and a.hourid=b.hourid;

# Eliminate and distributions that are missing.
drop table if exists test.junk;
create table test.junk
 select sourcetypeid,roadtypeid,dayid,sum(hourvmtfraction) as fracsum
 from test.s_hourvmtfraction
 group by sourcetypeid,roadtypeid,dayid;
alter table test.junk add index myidx (sourcetypeid,roadtypeid,dayid);
alter table test.s_hourvmtfraction add fracsum double;
update test.s_hourvmtfraction as a,
       test.junk as b
 set a.fracsum=b.fracsum
 where a.sourcetypeid=b.sourcetypeid
   and a.roadtypeid=b.roadtypeid
   and a.dayid=b.dayid;
delete from test.s_hourvmtfraction
 where fracsum<0.1;

# Create new table to contain seeded data.
drop table if exists test.n_hourvmtfraction;
create table test.n_hourvmtfraction
 like movesdb20181022.hourvmtfraction;

# Populate the new table with the default data.
insert into test.n_hourvmtfraction
 select * from movesdb20181022.hourvmtfraction;

# Update the new table with non-zero state supplied values.
# Cases with zero will now use default distributions.
update test.n_hourvmtfraction as a,
       test.s_hourvmtfraction as b
 set a.hourvmtfraction=b.hourvmtfraction
 where a.sourcetypeid=b.sourcetypeid
   and a.roadtypeid=b.roadtypeid
   and a.dayid=b.dayid
   and a.hourid=b.hourid;

# Ensure that all cases will have results
# by substituting a small poitive value
# for every case with a zero.
update test.n_hourvmtfraction
 set hourvmtfraction=1.0E-15
 where hourvmtfraction<1.0E-15;

# Truncate and update the CDB table.
truncate hourvmtfraction;
insert into hourvmtfraction
 select * from test.n_hourvmtfraction;

# =======================================================================================================
# monthvmtfraction
# =======================================================================================================

-- flush tables;

# create working copy of the table.
drop table if exists test.s_monthvmtfraction;
create table test.s_monthvmtfraction
 like movesdb20181022.monthvmtfraction;

# Populate the copy with zero values.
insert into test.s_monthvmtfraction
 select * from test.z_monthvmtfraction;

# Update the copy with state supplied values from the CDB.
# The copy will have zeros for any missing rows.
update test.s_monthvmtfraction as a,
       monthvmtfraction as b
 set a.monthvmtfraction=b.monthvmtfraction
 where a.sourcetypeid=b.sourcetypeid
   and a.monthid=b.monthid;

# Eliminate and distributions that are missing.
drop table if exists test.junk;
create table test.junk
 select sourcetypeid,sum(monthvmtfraction) as fracsum
 from test.s_monthvmtfraction
 group by sourcetypeid;
alter table test.junk add index myidx (sourcetypeid);
alter table test.s_monthvmtfraction add fracsum double;
update test.s_monthvmtfraction as a,
       test.junk as b
 set a.fracsum=b.fracsum
 where a.sourcetypeid=b.sourcetypeid;
delete from test.s_monthvmtfraction
 where fracsum<0.1;

# Create new table to contain seeded data.
drop table if exists test.n_monthvmtfraction;
create table test.n_monthvmtfraction
 like movesdb20181022.monthvmtfraction;

# Populate the new table with the default data.
insert into test.n_monthvmtfraction
 select * from movesdb20181022.monthvmtfraction;

# Update the new table with non-zero state supplied values.
# Cases with zero will now use default distributions.
update test.n_monthvmtfraction as a,
       test.s_monthvmtfraction as b
 set a.monthvmtfraction=b.monthvmtfraction
 where a.sourcetypeid=b.sourcetypeid
   and a.monthid=b.monthid;

# Ensure that all cases will have results
# by substituting a small poitive value
# for every case with a zero.
update test.n_monthvmtfraction
 set monthvmtfraction=1.0E-15
 where monthvmtfraction<1.0E-15;

# Truncate and update the CDB table.
truncate monthvmtfraction;
insert into monthvmtfraction
 select * from test.n_monthvmtfraction;

# =======================================================================================================
# roadtypedistribution
# =======================================================================================================

-- flush tables;

# create working copy of the table.
drop table if exists test.s_roadtypedistribution;
create table test.s_roadtypedistribution
 like movesdb20181022.roadtypedistribution;

# Populate the copy with zero values.
insert into test.s_roadtypedistribution
 select * from test.z_roadtypedistribution;

# Update the copy with state supplied values from the CDB.
# The copy will have zeros for any missing rows.
update test.s_roadtypedistribution as a,
       roadtypedistribution as b
 set a.roadtypevmtfraction=b.roadtypevmtfraction
 where a.sourcetypeid=b.sourcetypeid
   and a.roadtypeid=b.roadtypeid;

# Eliminate and distributions that are missing.
drop table if exists test.junk;
create table test.junk
 select sourcetypeid,sum(roadtypevmtfraction) as fracsum
 from test.s_roadtypedistribution
 group by sourcetypeid;
alter table test.junk add index myidx (sourcetypeid);
alter table test.s_roadtypedistribution add fracsum double;
update test.s_roadtypedistribution as a,
       test.junk as b
 set a.fracsum=b.fracsum
 where a.sourcetypeid=b.sourcetypeid;
delete from test.s_roadtypedistribution
 where fracsum<0.1;

# Create new table to contain seeded data.
drop table if exists test.n_roadtypedistribution;
create table test.n_roadtypedistribution
 like movesdb20181022.roadtypedistribution;

# Populate the new table with the default data.
insert into test.n_roadtypedistribution
 select * from movesdb20181022.roadtypedistribution;

# Update the new table with non-zero state supplied values.
# Cases with zero will now use default distributions.
update test.n_roadtypedistribution as a,
       test.s_roadtypedistribution as b
 set a.roadtypevmtfraction=b.roadtypevmtfraction
 where a.sourcetypeid=b.sourcetypeid
   and a.roadtypeid=b.roadtypeid;

# Ensure that all cases will have results
# by substituting a small poitive value
# for every case with a zero.
update test.n_roadtypedistribution
 set roadtypevmtfraction=1.0E-15
 where roadtypevmtfraction<1.0E-15;

# Truncate and update the CDB table.
truncate roadtypedistribution;
insert into roadtypedistribution
 select * from test.n_roadtypedistribution;

# =======================================================================================================
# sourcetypeagedistribution
# =======================================================================================================

-- flush tables;

# create working copy of the table.
drop table if exists test.s_sourcetypeagedistribution;
create table test.s_sourcetypeagedistribution
 like movesdb20181022.sourcetypeagedistribution;

# Populate the copy with zero values.
insert into test.s_sourcetypeagedistribution
 select * from test.z_sourcetypeagedistribution;

# Update the copy with state supplied values from the CDB.
# The copy will have zeros for any missing rows.
update test.s_sourcetypeagedistribution as a,
       sourcetypeagedistribution as b
 set a.agefraction=b.agefraction
 where a.sourcetypeid=b.sourcetypeid
   and a.yearid=b.yearid
   and a.ageid=b.ageid;

# Eliminate and distributions that are missing.
drop table if exists test.junk;
create table test.junk
 select sourcetypeid,yearid,sum(agefraction) as fracsum
 from test.s_sourcetypeagedistribution
 group by sourcetypeid,yearid;
alter table test.junk add index myidx (sourcetypeid,yearid);
alter table test.s_sourcetypeagedistribution add fracsum double;
update test.s_sourcetypeagedistribution as a,
       test.junk as b
 set a.fracsum=b.fracsum
 where a.sourcetypeid=b.sourcetypeid
   and a.yearid=b.yearid;
delete from test.s_sourcetypeagedistribution
 where fracsum<0.1;

# Create new table to contain seeded data.
drop table if exists test.n_sourcetypeagedistribution;
create table test.n_sourcetypeagedistribution
 like movesdb20181022.sourcetypeagedistribution;

# Populate the new table with the default data.
insert into test.n_sourcetypeagedistribution
 select a.* from movesdb20181022.sourcetypeagedistribution as a,
          year as b
 where a.yearid=b.yearid;

# Update the new table with non-zero state supplied values.
# Cases with zero will now use default distributions.
update test.n_sourcetypeagedistribution as a,
       test.s_sourcetypeagedistribution as b
 set a.agefraction=b.agefraction
 where a.sourcetypeid=b.sourcetypeid
   and a.yearid=b.yearid
   and a.ageid=b.ageid;

# Ensure that all cases will have results
# by substituting a small poitive value
# for every case with a zero.
update test.n_sourcetypeagedistribution
 set agefraction=1.0E-15
 where agefraction<1.0E-15;

# Truncate and update the CDB table.
truncate sourcetypeagedistribution;
insert into sourcetypeagedistribution
 select * from test.n_sourcetypeagedistribution;
 
# =======================================================================================================
# avft -- Requires the default populated AVFT table in the seed database.
# =======================================================================================================

-- flush tables;

# create working copy of the table.
drop table if exists test.s_avft;
create table test.s_avft
 like movesdb20181022.avft;

# Populate the copy with zero values.
insert into test.s_avft
 select * from test.z_avft;

# Update the copy with state supplied values from the CDB.
# The copy will have zeros for any missing rows.
update test.s_avft as a,
       avft as b
 set a.fuelengfraction=b.fuelengfraction
 where a.sourcetypeid=b.sourcetypeid
   and a.modelyearid=b.modelyearid
   and a.fueltypeid=b.fueltypeid
   and a.engtechid=b.engtechid;

# Eliminate and distributions that are missing.
drop table if exists test.junk;
create table test.junk
 select sourcetypeid,modelyearid,sum(fuelengfraction) as fracsum
 from test.s_avft
 group by sourcetypeid,modelyearid;
alter table test.junk add index myidx (sourcetypeid,modelyearid);
alter table test.s_avft add fracsum double;
update test.s_avft as a,
       test.junk as b
 set a.fracsum=b.fracsum
 where a.sourcetypeid=b.sourcetypeid
   and a.modelyearid=b.modelyearid;
delete from test.s_avft
 where fracsum<0.1;

# Create new table to contain seeded data.
drop table if exists test.n_avft;
create table test.n_avft
 like movesdb20181022.avft;

# Populate the new table with the default data.
insert into test.n_avft
 select * from seed.avft;

# Update the new table with non-zero state supplied values.
# Cases with zero will now use default distributions.
update test.n_avft as a,
       test.s_avft as b
 set a.fuelengfraction=b.fuelengfraction
 where a.sourcetypeid=b.sourcetypeid
   and a.modelyearid=b.modelyearid
   and a.fueltypeid=b.fueltypeid
   and a.engtechid=b.engtechid;

# Ensure that all cases will have results
# by substituting a small poitive value
# for every case with a zero.
update test.n_avft
 set fuelengfraction=1.0E-15
 where fuelengfraction<1.0E-15;

# Truncate and update the CDB table.
truncate avft;
insert into avft
 select * from test.n_avft;

# =======================================================================================================
# hotellingactivitydistribution - Only fills in zero values.
# =======================================================================================================

# Ensure that all cases will have results
# by substituting a small poitive value
# for every case with a zero.
update hotellingactivitydistribution
 set opmodefraction=1.0E-15
 where opmodefraction<1.0E-15;

# =======================================================================================================
# hotellinghours - Only truncates the CDB table.
# =======================================================================================================

# Ensure that all cases will have results
# by truncating any user-submitted hotelling hours data.
# MOVES will revert to the hotelling hours calculation
# as a ratio of hours per mile of rural interstate VMT.
# This default method will produce EFs for all cases 
# because rural interstate VMT is seeded (ensures some 
# non-zero hotelling) and hotellingactivitydistribution
# is seeded (ensures APU and main engine "extended idle"
# process rates).	

truncate hotellinghours;
 
# =======================================================================================================
# hpmsvtypeyear - QA script will prevent missing rows.
#                 If VMT is zero, replace both VMT and population with defaults.
# =======================================================================================================

# Replace any zero values with national annual defaults.
update sourcetypeyear as a,
       movesdb20181022.sourcetypeyear as b,
       movesdb20181022.sourceusetype as c,
       hpmsvtypeyear as d
 set a.sourcetypepopulation=b.sourcetypepopulation
 where d.hpmsvtypeid=c.hpmsvtypeid
   and a.sourcetypeid=b.sourcetypeid
   and a.sourcetypeid=c.sourcetypeid
   and d.hpmsbaseyearvmt<1.0E-15;
       
update hpmsvtypeyear as a,
       movesdb20181022.hpmsvtypeyear as b
 set a.hpmsbaseyearvmt=b.hpmsbaseyearvmt
 where a.hpmsbaseyearvmt<1.0E-15;

# =======================================================================================================
# sourcetypeyear - QA script will prevent missing rows.
# =======================================================================================================
# Ensure that all yearID-sourceTypeID combinations are present in sourceTypeYear

# First, create a complete list of the possible combinations
DROP TABLE IF EXISTS test.yearIDs;
CREATE TABLE IF NOT EXISTS test.yearIDs (`yearID` integer);
INSERT INTO test.yearIDs SELECT DISTINCT(yearID) FROM sourceTypeYear;

DROP TABLE IF EXISTS test.sourceTypeIDs;
CREATE TABLE IF NOT EXISTS test.sourceTypeIDs (`sourceTypeID` integer);
INSERT INTO test.sourceTypeIDs SELECT DISTINCT(sourceTypeID) FROM movesdb20181022.sourceUseType;

DROP TABLE IF EXISTS test.yearIDsSourceTypeIDs;
CREATE TABLE test.yearIDsSourceTypeIDs (`yearID` integer, `sourceTypeID` integer);
INSERT INTO test.yearIDsSourceTypeIDs SELECT * FROM test.yearIDs CROSS JOIN test.sourceTypeIDs;

DROP TABLE IF EXISTS test.sourceTypeYear;
CREATE TABLE IF NOT EXISTS test.sourceTypeYear LIKE sourceTypeYear;
INSERT INTO test.sourceTypeYear SELECT l.yearID, l.sourceTypeID, r.salesGrowthFactor, r.sourceTypePopulation, r.migrationrate 
    FROM test.yearIDsSourceTypeIDs l LEFT JOIN sourceTypeYear r on l.yearID=r.yearID and l.sourceTypeID=r.sourceTypeID;

DELETE FROM sourceTypeYear;
INSERT INTO sourceTypeYear SELECT * FROM test.sourceTypeYear;

UPDATE sourceTypeYear SET sourceTypePopulation=0 WHERE sourceTypePopulation IS NULL;

# Ensure that all cases will have results
# by substituting a small positive value
# for every case with a zero.
update sourcetypeyear
 set sourcetypepopulation=1.0E-15
 where sourcetypepopulation<1.0E-15;

# =======================================================================================================
# sourcetypeyearVMT - QA script will prevent missing rows.
# =======================================================================================================
# Ensure that all yearID-sourceTypeID combinations are present in sourceTypeYearVMT

# First, create a complete list of the possible combinations
DROP TABLE IF EXISTS test.yearIDs;
CREATE TABLE IF NOT EXISTS test.yearIDs (`yearID` integer);
INSERT INTO test.yearIDs SELECT DISTINCT(yearID) FROM sourceTypeYearVMT;

DROP TABLE IF EXISTS test.yearIDsSourceTypeIDs;
CREATE TABLE test.yearIDsSourceTypeIDs (`yearID` integer, `sourceTypeID` integer);
INSERT INTO test.yearIDsSourceTypeIDs SELECT * FROM test.yearIDs CROSS JOIN test.sourceTypeIDs;

DROP TABLE IF EXISTS test.sourceTypeYearVMT;
CREATE TABLE IF NOT EXISTS test.sourceTypeYearVMT LIKE sourceTypeYearVMT;
INSERT INTO test.sourceTypeYearVMT SELECT l.yearID, l.sourceTypeID, r.VMT
    FROM test.yearIDsSourceTypeIDs l LEFT JOIN sourceTypeYearVMT r on l.yearID=r.yearID and l.sourceTypeID=r.sourceTypeID;

DELETE FROM sourceTypeYearVMT;
INSERT INTO sourceTypeYearVMT SELECT * FROM test.sourceTypeYearVMT;

UPDATE sourceTypeYearVMT SET VMT=0 WHERE VMT IS NULL;

# Ensure that all cases will have results
# by substituting a small positive value
# for every case with a zero.
UPDATE sourcetypeyearVMT SET VMT=1.0E-15 WHERE VMT<1.0E-15;
 
# =======================================================================================================
# zoneRoadType - prevent missing rows.
# =======================================================================================================
# Ensure that all roadTypeIDs are present in zoneRoadType
DROP TABLE IF EXISTS test.missingroadtypes;
CREATE TABLE IF NOT EXISTS test.missingroadtypes (roadTypeID integer);
INSERT INTO test.missingroadtypes SELECT distinct m.roadTypeID FROM movesdb20181022.zoneRoadType m
	WHERE m.roadTypeID NOT IN (select roadTypeID from zoneRoadType);
INSERT INTO zoneRoadType select c.countyID*10 as zoneID, t.roadTypeID, 1 FROM test.missingroadTypes t, county c;	

# =======================================================================================================
# zonemonthhour - SMOKE-MOVES uses special temperature/humidity values.  Remove state supplied values.
# =======================================================================================================
truncate zonemonthhour;

# =======================================================================================================
# fuelusagefraction - Assume that the default data has gasoline, diesel and E85 in all counties.
# =======================================================================================================
truncate fuelusagefraction;

# =======================================================================================================
# fuelsupply - Assume that the default data has gasoline, diesel and E85 in all counties.
# ADB 16jul2019 - I commented out the truncate queries to keep fuels in the CDB that I put from 2016beta external DB.
# =======================================================================================================
-- truncate fuelsupply;
-- truncate fuelformulation;

# =======================================================================================================
# starts - Not found in MOVES2010b CDBs - Will need a check in future scripts.
# =======================================================================================================

# =======================================================================================================
# startshourfraction - Not found in MOVES2010b CDBs - Will need a check in future scripts.
# =======================================================================================================

# =======================================================================================================
# startsmonthadjust - Not found in MOVES2010b CDBs - Will need a check in future scripts.
# =======================================================================================================

# =======================================================================================================
# startsperday - Not found in MOVES2010b CDBs - Will need a check in future scripts.
# =======================================================================================================

# =======================================================================================================
# startssourcetypefraction - Not found in MOVES2010b CDBs - Will need a check in future scripts.
# =======================================================================================================

# #######################################################################################################
# Drop all the temporary tables.
# #######################################################################################################
drop table if exists test.s_avgspeeddistribution;
drop table if exists test.n_avgspeeddistribution;
drop table if exists test.s_dayvmtfraction;
drop table if exists test.n_dayvmtfraction;
drop table if exists test.s_hourvmtfraction;
drop table if exists test.n_hourvmtfraction;
drop table if exists test.s_monthvmtfraction;
drop table if exists test.n_monthvmtfraction;
drop table if exists test.s_roadtypedistribution;
drop table if exists test.n_roadtypedistribution;
drop table if exists test.s_sourcetypeagedistribution;
drop table if exists test.n_sourcetypeagedistribution;
drop table if exists test.s_avft;
drop table if exists test.n_avft;
drop table if exists test.junk;
DROP TABLE IF EXISTS test.sourceTypeYear;
drop table if exists test.missingroadtypes;

# #######################################################################################################
# Drop tables with zero distributions.
# #######################################################################################################
drop table if exists test.z_avgspeeddistribution;
drop table if exists test.z_dayvmtfraction;
drop table if exists test.z_hourvmtfraction;
drop table if exists test.z_monthvmtfraction;
drop table if exists test.z_roadtypedistribution;
drop table if exists test.z_sourcetypeagedistribution;
drop table if exists test.z_avft;

-- flush tables;
