 -- 2. Use tafid to filter in order:
 --    * taf forecast, icing, sky, temp, turbulence
 -- ===============================================================
-drop table if exists training2_taf;
-create table training2_taf as (
+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 training2_tafforecast;
-CREATE TABLE training2_tafforecast as (
+drop table if exists training3_tafforecast;
+CREATE TABLE training3_tafforecast as (
     select * from tafforecast where tafid in
-        (select tafid from training2_taf)
+        (select tafid from training3_taf)
 );
 -- taf icing, sky, temp, turbulence on tafforecastid
-drop table if exists training2_taficing;
-CREATE TABLE training2_taficing as (
+drop table if exists training3_taficing;
+CREATE TABLE training3_taficing as (
     select * from taficing where tafforecastid in
-        (select tafforecastid from training2_tafforecast)
-);
-drop table if exists training2_tafsky;
-CREATE TABLE training2_tafsky as (
+        (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 training2_tafforecast)
-);
-drop table if exists training2_taftemperature;
-CREATE TABLE training2_taftemperature as (
+        (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 training2_tafforecast)
-);
-drop table if exists training2_tafturbulence;
-CREATE TABLE training2_tafturbulence as (
+        (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 training2_tafforecast)
-);
-
-copy (select * from training2_taf) to 'C:/FQ2DataRelease2/training2_taf.csv' delimiter ',' csv header;
-copy (select * from training2_tafforecast) to 'C:/FQ2DataRelease2/training2_tafforecast.csv' delimiter ',' csv header;
-copy (select * from training2_taficing) to 'C:/FQ2DataRelease2/training2_taficing.csv' delimiter ',' csv header;
-copy (select * from training2_tafsky) to 'C:/FQ2DataRelease2/training2_tafsky.csv' delimiter ',' csv header;
-copy (select * from training2_taftemperature) to 'C:/FQ2DataRelease2/training2_taftemperature.csv' delimiter ',' csv header;
-copy (select * from training2_tafturbulence) to 'C:/FQ2DataRelease2/training2_tafturbulence.csv' delimiter ',' csv header;
+        (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 training2_airsigmet;
-CREATE TABLE training2_airsigmet as (
+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 training2_airsigmetarea;
-CREATE TABLE training2_airsigmetarea as (
+drop table if exists training3_airsigmetarea;
+CREATE TABLE training3_airsigmetarea as (
     select * from airsigmetarea where 
-    airsigmetid in (select airsigmetid from training2_airsigmet)
-);
-
-copy (select * from training2_airsigmet) to 'C:/FQ2DataRelease2/training2_airsigmet.csv' delimiter ',' csv header;
-copy (select * from training2_airsigmetarea) to 'C:/FQ2DataRelease2/training2_airsigmetarea.csv' delimiter ',' csv header;
+    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;
 
  


 -- flighthistory ids
drop table if exists flighthistory_ids;
create table flighthistory_ids as select id from training2_flighthistory;


-- Training: flighthistoryevents
drop table if exists training2_flighthistoryevents;
create table training2_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
    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;

-- ASDI airway
drop table if exists training2_asdiairway;
create table training2_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
    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
    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
    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
    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
    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;




--      * presentconditions, skyconditions, runwayconditions
-- ===============================================================
-- metar_reports: create view with training date constraints
drop table if exists training3_metar_reports;
create table training3_metar_reports as ( 
    select * from metar_reports where
    date_time_issued >= '2013-11-14 09:00:00+00' and date_time_issued < '2013-12-12 09:00:00+00'
);

-- presentconditions
drop table if exists training3_metar_presentconditions;
create table training3_metar_presentconditions as (
    select * from metar_presentconditions
    where metar_reports_id in (select id from training3_metar_reports)
);
-- skyconditions
drop table if exists training3_metar_skyconditions;
create table training3_metar_skyconditions as (
    select * from metar_skyconditions
    where metar_reports_id in (select id from training3_metar_reports)
);

-- runwayconditions
drop table if exists training3_metar_runwaygroups;
create table training3_metar_runwaygroups as (
    select * from metar_runwaygroups
    where metar_reports_id in (select id from training3_metar_reports)
);

copy (select * from training3_metar_reports) to 'E:/FQ2_Data/training3_metar_reports.csv' delimiter ',' csv header;
copy (select * from training3_metar_presentconditions) to 'E:/FQ2_Data/training3_metar_presentconditions.csv' delimiter ',' csv header;
copy (select * from training3_metar_skyconditions) to 'E:/FQ2_Data/training3_metar_skyconditions.csv' delimiter ',' csv header;
copy (select * from training3_metar_runwaygroups) to 'E:/FQ2_Data/training3_metar_runwaygroups.csv' delimiter ',' csv header;


-- ===============================================================
