 
  -- flighthistory ids
 drop table if exists flighthistory_ids;
-create table flighthistory_ids as select id from training2_flighthistory;
+create table flighthistory_ids as select id from training3_flighthistory;
 
 
 -- Training: flighthistoryevents
-drop table if exists training2_flighthistoryevents;
-create table training2_flighthistoryevents as
+drop table if exists training3_flighthistoryevents;
+create table training3_flighthistoryevents as
     select * from flighthistoryevents where 
         flighthistory_id in (select * from flighthistory_ids);
 
 
 -- ASDI flight plan, uses flighthistory_id)
-drop table if exists training2_asdiflightplan;
-create table training2_asdiflightplan as
+drop table if exists training3_asdiflightplan;
+create table training3_asdiflightplan as
     select * from asdiflightplan where
         flighthistory_id in (select id from flighthistory_ids);
 
 
 -- ASDI Flight Plan IDs
 drop table if exists asdiflightplan_ids;
-create table asdiflightplan_ids as select id from training2_asdiflightplan;
+create table asdiflightplan_ids as select id from training3_asdiflightplan;
 
 -- ASDI airway
-drop table if exists training2_asdiairway;
-create table training2_asdiairway as
+drop table if exists training3_asdiairway;
+create table training3_asdiairway as
     select * from asdiairway where
         asdiflightplan_id in (select * from asdiflightplan_ids);
 -- ASDI FP Fix
-drop table if exists training2_asdifpfix;
-create table training2_asdifpfix as
+drop table if exists training3_asdifpfix;
+create table training3_asdifpfix as
     select * from asdifpfix where
         asdiflightplan_id in (select * from asdiflightplan_ids);
 -- ASDI FP Center
-drop table if exists training2_asdifpcenter;
-create table training2_asdifpcenter as
+drop table if exists training3_asdifpcenter;
+create table training3_asdifpcenter as
     select * from asdifpcenter where
         asdiflightplan_id in (select * from asdiflightplan_ids);
 -- ASDI FP Sector
-drop table if exists training2_asdifpsector;
-create table training2_asdifpsector as
+drop table if exists training3_asdifpsector;
+create table training3_asdifpsector as
     select * from asdifpsector where
         asdiflightplan_id in (select * from asdiflightplan_ids);
 -- ASDI Position (uses flighthistoryid)
-drop table if exists training2_asdiposition;
-create table training2_asdiposition as
+drop table if exists training3_asdiposition;
+create table training3_asdiposition as
     select * from asdiposition where
         flighthistory_id in (select * from flighthistory_ids);
 -- ASDI FP Waypoint
-drop table if exists training2_asdifpwaypoint;
-create table training2_asdifpwaypoint as
+drop table if exists training3_asdifpwaypoint;
+create table training3_asdifpwaypoint as
     select * from asdifpwaypoint where
         asdiflightplan_id in (select * from asdiflightplan_ids);
 
 
 -- Write to file
-copy (select * from training2_flighthistory) to 'C:/FQ2DataRelease2/training2_flighthistory.csv' delimiter ',' csv header;
-copy (select * from training2_flighthistoryevents) to 'C:/FQ2DataRelease2/training2_flighthistoryevents.csv' delimiter ',' csv header;
-copy (select * from training2_asdiairway) to 'C:/FQ2DataRelease2/training2_asdiairway.csv' delimiter ',' csv header; 
-copy (select * from training2_asdiflightplan) to 'C:/FQ2DataRelease2/training2_asdiflightplan.csv' delimiter ',' csv header; 
-copy (select * from training2_asdifpcenter) to 'C:/FQ2DataRelease2/training2_asdifpcenter.csv' delimiter ',' csv header; 
-copy (Select * from training2_asdifpfix) to 'C:/FQ2DataRelease2/training2_asdifpfix.csv' delimiter ',' csv header; 
-copy (select * from training2_asdifpsector) to 'C:/FQ2DataRelease2/training2_asdifpsector.csv' delimiter ',' csv header; 
-copy (select * from training2_asdifpwaypoint) to 'C:/FQ2DataRelease2/training2_asdifpwaypoint.csv' delimiter ',' csv header; 
-copy (select * from training2_asdiposition) to 'C:/FQ2DataRelease2/training2_asdiposition.csv' delimiter ',' csv header;
+copy (select * from training3_flighthistory) to 'E:/FQ2_Data/training3_flighthistory.csv' delimiter ',' csv header;
+copy (select * from training3_flighthistoryevents) to 'E:/FQ2_Data/training3_flighthistoryevents.csv' delimiter ',' csv header;
+copy (select * from training3_asdiairway) to 'E:/FQ2_Data/training3_asdiairway.csv' delimiter ',' csv header; 
+copy (select * from training3_asdiflightplan) to 'E:/FQ2_Data/training3_asdiflightplan.csv' delimiter ',' csv header; 
+copy (select * from training3_asdifpcenter) to 'E:/FQ2_Data//training3_asdifpcenter.csv' delimiter ',' csv header; 
+copy (Select * from training3_asdifpfix) to 'E:/FQ2_Data//training3_asdifpfix.csv' delimiter ',' csv header; 
+copy (select * from training3_asdifpsector) to 'E:/FQ2_Data/training3_asdifpsector.csv' delimiter ',' csv header; 
+copy (select * from training3_asdifpwaypoint) to 'E:/FQ2_Data/training3_asdifpwaypoint.csv' delimiter ',' csv header; 
+copy (select * from training3_asdiposition) to 'E:/FQ2_Data/training3_asdiposition.csv' delimiter ',' csv header;
 
 
 

--          This is because NOAA has changed the name of their product
--          to FDwind but did not change column headers
-- ===============================================================
drop table if exists training2_fdwindreport;
create table training2_fdwindreport as (
    select * from fdwindreport where
    createdutc >= '2013-11-14 09:00:00+00' and createdutc < '2013-12-12 09:00:00+00'
);
-- airport using report ids
drop table if exists training2_fdwindairport;
create table training2_fdwindairport as (
    select * from fdwindairport where fbwindreportid in 
        (select fbwindreportid from training2_fdwindreport)
);

-- altitude uses report ids
drop table if exists training2_fdwindaltitude;
create table training2_fdwindaltitude as (
    select * from fdwindaltitude where fbwindreportid in 
        (select fbwindreportid from training2_fdwindreport)
);
-- wind using airport ids
drop table if exists training2_fdwind;
create table training2_fdwind as (
    select * from fdwind where fbwindairportid in
        (SELECT fbwindairportid from training2_fdwindairport)
);

copy (select * from training2_fdwindreport) to 'C:/FQ2DataRelease2/training2_fdwindreport.csv' delimiter ',' csv header;
copy (select * from training2_fdwindairport) to 'C:/FQ2DataRelease2/training2_fdwindairport.csv' delimiter ',' csv header;
copy (select * from training2_fdwindaltitude) to 'C:/FQ2DataRelease2/training2_fdwindaltitude.csv' delimiter ',' csv header;
copy (select * from training2_fdwind) to 'C:/FQ2DataRelease2/training2_fdwind.csv' delimiter ',' csv header;

-- ===============================================================
-- TAF

-- 2. Use tafid to filter in order:
--    * taf forecast, icing, sky, temp, turbulence
-- ===============================================================
drop table if exists training3_taf;
create table training3_taf as (
    select * from taf where 
    bulletintimeutc >= '2013-11-14 09:00:00+00' and bulletintimeutc < '2013-12-12 09:00:00+00'
);
-- taf forecast on taf ids
drop table if exists training3_tafforecast;
CREATE TABLE training3_tafforecast as (
    select * from tafforecast where tafid in
        (select tafid from training3_taf)
);
-- taf icing, sky, temp, turbulence on tafforecastid
drop table if exists training3_taficing;
CREATE TABLE training3_taficing as (
    select * from taficing where tafforecastid in
        (select tafforecastid from training3_tafforecast)
);
drop table if exists training3_tafsky;
CREATE TABLE training3_tafsky as (
    select * from tafsky where tafforecastid in
        (select tafforecastid from training3_tafforecast)
);
drop table if exists training3_taftemperature;
CREATE TABLE training3_taftemperature as (
    select * from taftemperature where tafforecastid in
        (select tafforecastid from training3_tafforecast)
);
drop table if exists training3_tafturbulence;
CREATE TABLE training3_tafturbulence as (
    select * from tafturbulence where tafforecastid in
        (select tafforecastid from training3_tafforecast)
);

copy (select * from training3_taf) to 'E:/FQ2_Data/training3_taf.csv' delimiter ',' csv header;
copy (select * from training3_tafforecast) to 'E:/FQ2_Data/training3_tafforecast.csv' delimiter ',' csv header;
copy (select * from training3_taficing) to 'E:/FQ2_Data/training3_taficing.csv' delimiter ',' csv header;
copy (select * from training3_tafsky) to 'E:/FQ2_Data/training3_tafsky.csv' delimiter ',' csv header;
copy (select * from training3_taftemperature) to 'E:/FQ2_Data/training3_taftemperature.csv' delimiter ',' csv header;
copy (select * from training3_tafturbulence) to 'E:/FQ2_Data/training3_tafturbulence.csv' delimiter ',' csv header;

-- ===============================================================
-- AIRSIGMET
-- 1. Filter airsigmet on training dates, Need ids
-- 2. Filter airsigmetarea using airsigmet ids
-- ===============================================================
drop table if exists training3_airsigmet;
CREATE TABLE training3_airsigmet as (
    select * from airsigmet where
    timevalidfromutc >= '2013-11-14 09:00:00+00'
    and timevalidfromutc < '2013-12-12 09:00:00+00'
);
-- airsigmetarea
drop table if exists training3_airsigmetarea;
CREATE TABLE training3_airsigmetarea as (
    select * from airsigmetarea where 
    airsigmetid in (select airsigmetid from training3_airsigmet)
);

copy (select * from training3_airsigmet) to 'E:/FQ2_Data/training3_airsigmet.csv' delimiter ',' csv header;
copy (select * from training3_airsigmetarea) to 'E:/FQ2_Data/training3_airsigmetarea.csv' delimiter ',' csv header;

 
