# ----------------------------------------------------------------
# ----------------- Create FF10 Database Script ------------------
# ----------------------------------------------------------------
drop schema if exists draft_epa2017ff10_7jan2020;
create database draft_epa2017ff10_7jan2020;

# FF10 formatted table for population values.
drop table if exists draft_epa2017ff10_7jan2020.ff10POP;         -- Populations only.
CREATE TABLE         draft_epa2017ff10_7jan2020.ff10POP
  (  country_cd         char(2),   -- US
     region_cd          char(5),   -- county Id
     tribal_code        char(2),
     census_tract_cd    char(2),
     shape_id           char(2),
     scc                char(12),
     CD                 char(1),
     MSR                char(1),
     activity_type      char(8),  -- POP
     ann_value          double,   -- annual value for VMT or POP
     calc_year          smallint, -- calc year
     date_updated       char(12), -- date updated
     data_set_id        char(2),  -- vmt only
     jan_value          double,   -- vmt only
     feb_value          double,   -- vmt only
     mar_value          double,   -- vmt only
     apr_value          double,   -- vmt only
     may_value          double,   -- vmt only
     jun_value          double,   -- vmt only
     jul_value          double,   -- vmt only
     aug_value          double,   -- vmt only
     sep_value          double,   -- vmt only
     oct_value          double,   -- vmt only
     nov_value          double,   -- vmt only
     dec_value          double,
     comment            char(255)
  )
     ENGINE=MyISAM DEFAULT CHARSET=latin1;

# FF10 formatted table for vehicle miles traveled (VMT) values.
drop table if exists draft_epa2017ff10_7jan2020.ff10vmt;         -- vehicle miles traveled only.
CREATE TABLE         draft_epa2017ff10_7jan2020.FF10VMT
  (  country_cd         char(2),   -- US
     region_cd          char(5),   -- county Id
     tribal_code        char(2),
     census_tract_cd    char(2),
     shape_id           char(2),
     scc                char(12),
     CD                 char(1),
     MSR                char(1),
     activity_type      char(8),  -- VMT
     ann_value          double,   -- annual value for VMT or POP
     calc_year          smallint, -- calc year
     date_updated       char(12), -- date updated
     data_set_id        char(2),  -- vmt only
     jan_value          double,   -- vmt only
     feb_value          double,   -- vmt only
     mar_value          double,   -- vmt only
     apr_value          double,   -- vmt only
     may_value          double,   -- vmt only
     jun_value          double,   -- vmt only
     jul_value          double,   -- vmt only
     aug_value          double,   -- vmt only
     sep_value          double,   -- vmt only
     oct_value          double,   -- vmt only
     nov_value          double,   -- vmt only
     dec_value          double,
     comment            char(255)
  )
     ENGINE=MyISAM DEFAULT CHARSET=latin1;

# FF10 formatted table for average speed values.
drop table if exists draft_epa2017ff10_7jan2020.ff10spd;
CREATE TABLE         draft_epa2017ff10_7jan2020.FF10SPD (
country_cd          char(2)
,region_cd          char(5)
,tribal_code        char(2)
,census_tract_cd    char(2)
,shape_id           char(2)
,scc                char(12)
,CD                 char(1)
,MSR                char(1)
,activity_type      char(8)
,ann_value          double
,calc_year          smallint
,date_updated       char(12)
,data_set_id        char(2)
,jan_value          double
,feb_value          double
,mar_value          double
,apr_value          double
,may_value          double
,jun_value          double
,jul_value          double
,aug_value          double
,sep_value          double
,oct_value          double
,nov_value          double
,dec_value          double
,comment            char(255)
)ENGINE=MyISAM DEFAULT CHARSET=latin1;

# SPDPRO formatted table for average speed values.
drop table if exists draft_epa2017ff10_7jan2020.spdpro;
CREATE TABLE         draft_epa2017ff10_7jan2020.SPDPRO (
  `countyid` int(11) NOT NULL,
  `scc` bigint(15) unsigned NOT NULL DEFAULT '0',
  `weekday` varchar(7) DEFAULT NULL,
  `hr01a` varchar(22) DEFAULT NULL,
  `hr02a` varchar(22) DEFAULT NULL,
  `hr03a` varchar(22) DEFAULT NULL,
  `hr04a` varchar(22) DEFAULT NULL,
  `hr05a` varchar(22) DEFAULT NULL,
  `hr06a` varchar(22) DEFAULT NULL,
  `hr07a` varchar(22) DEFAULT NULL,
  `hr08a` varchar(22) DEFAULT NULL,
  `hr09a` varchar(22) DEFAULT NULL,
  `hr10a` varchar(22) DEFAULT NULL,
  `hr11a` varchar(22) DEFAULT NULL,
  `hr12a` varchar(22) DEFAULT NULL,
  `hr13a` varchar(22) DEFAULT NULL,
  `hr14a` varchar(22) DEFAULT NULL,
  `hr15a` varchar(22) DEFAULT NULL,
  `hr16a` varchar(22) DEFAULT NULL,
  `hr17a` varchar(22) DEFAULT NULL,
  `hr18a` varchar(22) DEFAULT NULL,
  `hr19a` varchar(22) DEFAULT NULL,
  `hr20a` varchar(22) DEFAULT NULL,
  `hr21a` varchar(22) DEFAULT NULL,
  `hr22a` varchar(22) DEFAULT NULL,
  `hr23a` varchar(22) DEFAULT NULL,
  `hr24a` varchar(22) DEFAULT NULL,
  `weekend` varchar(7) DEFAULT NULL,
  `hr01b` varchar(22) DEFAULT NULL,
  `hr02b` varchar(22) DEFAULT NULL,
  `hr03b` varchar(22) DEFAULT NULL,
  `hr04b` varchar(22) DEFAULT NULL,
  `hr05b` varchar(22) DEFAULT NULL,
  `hr06b` varchar(22) DEFAULT NULL,
  `hr07b` varchar(22) DEFAULT NULL,
  `hr08b` varchar(22) DEFAULT NULL,
  `hr09b` varchar(22) DEFAULT NULL,
  `hr10b` varchar(22) DEFAULT NULL,
  `hr11b` varchar(22) DEFAULT NULL,
  `hr12b` varchar(22) DEFAULT NULL,
  `hr13b` varchar(22) DEFAULT NULL,
  `hr14b` varchar(22) DEFAULT NULL,
  `hr15b` varchar(22) DEFAULT NULL,
  `hr16b` varchar(22) DEFAULT NULL,
  `hr17b` varchar(22) DEFAULT NULL,
  `hr18b` varchar(22) DEFAULT NULL,
  `hr19b` varchar(22) DEFAULT NULL,
  `hr20b` varchar(22) DEFAULT NULL,
  `hr21b` varchar(22) DEFAULT NULL,
  `hr22b` varchar(22) DEFAULT NULL,
  `hr23b` varchar(22) DEFAULT NULL,
  `hr24b` varchar(22) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;

# FF10 formatted table for hotelling hours values.
drop table if exists draft_epa2017ff10_7jan2020.ff10hot;
CREATE TABLE         draft_epa2017ff10_7jan2020.FF10HOT (
country_cd          char(2)
,region_cd          char(5)
,tribal_code        char(2)
,census_tract_cd    char(2)
,shape_id           char(2)
,scc                char(12)
,CD                 char(1)
,MSR                char(1)
,activity_type      char(10)
,ann_value          double
,calc_year          smallint
,date_updated       char(12)
,data_set_id        char(2)
,jan_value          double
,feb_value          double
,mar_value          double
,apr_value          double
,may_value          double
,jun_value          double
,jul_value          double
,aug_value          double
,sep_value          double
,oct_value          double
,nov_value          double
,dec_value          double
,comment            char(255)
)ENGINE=MyISAM DEFAULT CHARSET=latin1;

# drop table if exists draft_epa2017ff10_7jan2020.ff10hot_direct;
# create table draft_epa2017ff10_7jan2020.ff10hot_direct like draft_epa2017ff10_7jan2020.ff10hot;

# FF10 formatted table for hotelling hours values (Directly Submitted in the CDB via HotellingHours and HotellingActivityDistribution tables).
drop table if exists draft_epa2017ff10_7jan2020.ff10hot_direct;
CREATE TABLE         draft_epa2017ff10_7jan2020.FF10HOT_direct (
country_cd          char(2)
,region_cd          char(5)
,tribal_code        char(2)
,census_tract_cd    char(2)
,shape_id           char(2)
,scc                char(12)
,CD                 char(1)
,MSR                char(1)
,activity_type      char(10)
,ann_value          double
,calc_year          smallint
,date_updated       char(12)
,data_set_id        char(60)
,jan_value          double
,feb_value          double
,mar_value          double
,apr_value          double
,may_value          double
,jun_value          double
,jul_value          double
,aug_value          double
,sep_value          double
,oct_value          double
,nov_value          double
,dec_value          double
,comment            char(255)
)ENGINE=MyISAM DEFAULT CHARSET=latin1;


flush tables;
