DROP TABLE IF EXISTS 'pilots';
CREATE TABLE IF NOT EXISTS 'pilots' (
	'pilot_id'	INTEGER NOT NULL,
	'lastname'	TEXT NOT NULL,
	'firstname'	TEXT,
        'alias'		TEXT,
	'company'	TEXT,
	'employeeid'	TEXT,
        'phone'		TEXT,
        'email'		TEXT,
	PRIMARY KEY('pilot_id' AUTOINCREMENT)
);
DROP TABLE IF EXISTS 'tails';
CREATE TABLE IF NOT EXISTS 'tails' (
	'tail_id'	INTEGER NOT NULL,
	'registration'	TEXT NOT NULL,
	'company'	TEXT,
        'make'		TEXT,
        'model'		TEXT,
	'variant'	TEXT,
	'multipilot'	INTEGER,
	'multiengine'	INTEGER,
	'engineType'	INTEGER,
	'weightClass'	INTEGER,
        'typeString'	TEXT,
	PRIMARY KEY('tail_id' AUTOINCREMENT)
);
DROP TABLE IF EXISTS 'flights';
CREATE TABLE IF NOT EXISTS 'flights' (
	'flight_id'	INTEGER NOT NULL,
        'doft'		NUMERIC NOT NULL,
        'dept'		TEXT NOT NULL,
        'dest'		TEXT NOT NULL,
        'tofb'		INTEGER NOT NULL,
        'tonb'		INTEGER NOT NULL,
        'pic'		INTEGER NOT NULL,
        'acft'		INTEGER NOT NULL,
        'tblk'		INTEGER NOT NULL,
        'tSPSE'		INTEGER,
        'tSPME'		INTEGER,
        'tMP'		INTEGER,
	'tNIGHT'	INTEGER,
        'tIFR'		INTEGER,
        'tPIC'		INTEGER,
	'tPICUS'	INTEGER,
        'tSIC'		INTEGER,
        'tDUAL'		INTEGER,
        'tFI'		INTEGER,
        'tSIM'		INTEGER,
	'pilotFlying'	INTEGER,
        'toDay'		INTEGER,
	'toNight'	INTEGER,
	'ldgDay'	INTEGER,
	'ldgNight'	INTEGER,
	'autoland'	INTEGER,
	'secondPilot'	INTEGER,
	'thirdPilot'	INTEGER,
	'approachType'	TEXT,
	'flightNumber'	TEXT,
	'remarks'	TEXT,
	FOREIGN KEY('pic') REFERENCES 'pilots'('pilot_id') ON DELETE RESTRICT,
	FOREIGN KEY('acft') REFERENCES 'tails'('tail_id') ON DELETE RESTRICT,
	PRIMARY KEY('flight_id' AUTOINCREMENT)
);
DROP TABLE IF EXISTS 'aircraft';
CREATE TABLE IF NOT EXISTS 'aircraft' (
	'aircraft_id'	INTEGER NOT NULL,
        'make'		TEXT,
        'model'		TEXT,
	'variant'	TEXT,
        'name'		TEXT,
        'iata'		TEXT,
        'icao'		TEXT,
	'multipilot'	INTEGER,
	'multiengine'	INTEGER,
	'engineType'	INTEGER,
	'weightClass'	INTEGER,
	PRIMARY KEY('aircraft_id' AUTOINCREMENT)
);
DROP TABLE IF EXISTS 'airports';
CREATE TABLE IF NOT EXISTS 'airports' (
	'airport_id'	INTEGER NOT NULL,
        'icao'		TEXT NOT NULL,
        'iata'		TEXT,
        'name'		TEXT,
        'lat'		REAL,
        'long'		REAL,
	'country'	TEXT,
	'tzolson'	TEXT,
	PRIMARY KEY('airport_id' AUTOINCREMENT)
);
DROP TABLE IF EXISTS 'currencies';
CREATE TABLE IF NOT EXISTS "currencies" (
        "currency_id"	INTEGER NOT NULL,
        "currencyName"	TEXT,
        "expiryDate"	NUMERIC,
        PRIMARY KEY('currency_id' AUTOINCREMENT)
);
DROP TABLE IF EXISTS 'changelog';
CREATE TABLE IF NOT EXISTS 'changelog' (
	'revision'	INTEGER NOT NULL,
	'comment'	TEXT,
        'date'		NUMERIC,
	PRIMARY KEY('revision' AUTOINCREMENT)
);
DROP TABLE IF EXISTS 'simulators';
CREATE TABLE IF NOT EXISTS 'simulators' (
	'session_id'	INTEGER NOT NULL,
        'date'		NUMERIC NOT NULL,
	'totalTime'	INTEGER NOT NULL,
	'deviceType'	TEXT NOT NULL,
	'aircraftType'	TEXT,
	'registration'	TEXT,
	'remarks'	TEXT,
	PRIMARY KEY('session_id' AUTOINCREMENT)
);
DROP TABLE IF EXISTS 'previousExperience';
CREATE TABLE 'previousExperience' (
        'tblk'		INTEGER,
        'tSPSE'		INTEGER,
        'tSPME'		INTEGER,
        'tMP'		INTEGER,
        'tNIGHT'	INTEGER,
        'tIFR'		INTEGER,
        'tPIC'		INTEGER,
        'tPICUS'	INTEGER,
        'tSIC'		INTEGER,
        'tDUAL'		INTEGER,
        'tFI'		INTEGER,
        'tSIM'		INTEGER,
        'toDay'		INTEGER,
        'toNight'	INTEGER,
        'ldgDay'	INTEGER,
        'ldgNight'	INTEGER,
        'autoland'	INTEGER
);
DROP VIEW IF EXISTS 'viewDefault';
CREATE VIEW viewDefault AS
SELECT 	flight_id,
        doft,
        dept,
        tofb,
        dest,
        tonb,
        tblk,
        pilots.pilot_id,
        tails.tail_id,
        tails.registration,
        flightNumber,
        remarks
FROM flights
INNER JOIN pilots on flights.pic = pilots.pilot_id
INNER JOIN tails on flights.acft = tails.tail_id
ORDER BY doft DESC

DROP VIEW IF EXISTS 'viewDefaultSim';
CREATE VIEW viewDefaultSim AS
SELECT 	flights.flight_id,
        flights.doft,
        flights.dept,
        flights.tofb,
        flights.dest,
        flights.tonb,
        flights.tblk,
        pilots.pilot_id ,
        tails.tail_id,
        tails.registration,
        null AS 'deviceType',
        null AS 'SimTime',
        flights.remarks
FROM flights
INNER JOIN pilots on flights.pic = pilots.pilot_id
INNER JOIN tails on flights.acft = tails.tail_id
UNION
        SELECT (simulators.session_id * -1),
        simulators.date,
        null, null, null, null, null, null,
        simulators.aircraftType,
        simulators.registration,
        simulators.deviceType,
        simulators.totalTime,
        remarks
FROM simulators
ORDER BY date DESC

DROP VIEW IF EXISTS 'viewEasa';
CREATE VIEW viewEasa AS
SELECT
        flight_id,
        doft,
        dept,
        tofb,
        dest,
        tonb,
        tail_id,
        registration,
        tSPSE,
        tSPME,
        tMP,
        tblk,
        pilot_id,
        ldgDay,
        ldgNight,
        tNight,
        tIFR,
        tPIC,
        tSIC,
        tDUAL,
        tFI,
        remarks
FROM flights
INNER JOIN pilots on flights.pic = pilots.pilot_id
INNER JOIN tails on flights.acft = tails.tail_id  ORDER BY doft DESC

DROP VIEW IF EXISTS 'viewEasaSim';
CREATE VIEW viewEasaSim AS
SELECT  flight_id,
        flights.doft as 'Date',
        flights.dept,
        flights.tofb,
        flights.dest,
        flights.tonb,
        tails.tail_id AS 'Type',
        tails.registration AS 'Registration',
        flights.tSPSE,
        flights.tSPME,
        flights.tMP,
        flights.tblk,
        pilots.pilot_id AS 'PIC',
        flights.ldgDay,
        flights.ldgNight,
        flights.tNight,
        flights.tIFR,
        flights.tPIC,
        flights.tSIC,
        flights.tDual,
        flights.tFI,
        null AS 'deviceType',
        null AS 'simTime',
        flights.remarks
FROM flights
INNER JOIN pilots on flights.pic = pilots.pilot_id
INNER JOIN tails on flights.acft = tails.tail_id
UNION
SELECT (session_id * -1),
        simulators.date,
        null, null, null, null,
        simulators.aircraftType,
        simulators.registration,
        null, null, null, null,
        null, null, null, null,
        null, null, null, null,
        null,
        simulators.deviceType,
        simulators.totalTime,
        simulators.remarks
FROM simulators
ORDER BY date DESC

DROP VIEW IF EXISTS 'viewSimulators';
CREATE VIEW viewSimulators AS
SELECT (session_id * -1),
        date,
        registration,
        aircraftType,
        deviceType,
        totalTime,
        remarks
FROM simulators
ORDER BY date DESC

DROP VIEW IF EXISTS 'viewTails';
CREATE VIEW viewTails AS  
SELECT  tail_id AS 'ID',  
        registration AS 'Registration',
        make||' '||model AS 'Type',
        company AS 'Company'
FROM tails WHERE model IS NOT NULL AND variant IS NULL  
UNION  
SELECT  tail_id AS 'ID',  
        registration AS 'Registration',
        make||' '||model||'-'||variant AS 'Type',
        company AS 'Company'
FROM tails WHERE variant IS NOT NULL;

DROP VIEW IF EXISTS 'viewPilots';
CREATE VIEW viewPilots AS  
SELECT  pilot_id AS 'ID',  
        lastname AS 'Last Name',
        firstname AS 'First Name',
        company AS 'Company'
FROM pilots;

DROP VIEW IF EXISTS 'viewTotals';
CREATE VIEW viewTotals AS  
SELECT  printf('%02d',CAST(SUM(tblk) AS INT)/60)||':'||printf('%02d',CAST(SUM(tblk) AS INT)%60) AS 'TOTAL',  
        printf('%02d',CAST(SUM(tSPSE) AS INT)/60)||':'||printf('%02d',CAST(SUM(tSPSE) AS INT)%60) AS 'SP SE',
        printf('%02d',CAST(SUM(tSPME) AS INT)/60)||':'||printf('%02d',CAST(SUM(tSPME) AS INT)%60) AS 'SP ME',
        printf('%02d',CAST(SUM(tNIGHT) AS INT)/60)||':'||printf('%02d',CAST(SUM(tNIGHT) AS INT)%60) AS 'NIGHT',
        printf('%02d',CAST(SUM(tIFR) AS INT)/60)||':'||printf('%02d',CAST(SUM(tIFR) AS INT)%60) AS 'IFR',
        printf('%02d',CAST(SUM(tPIC) AS INT)/60)||':'||printf('%02d',CAST(SUM(tPIC) AS INT)%60) AS 'PIC',
        printf('%02d',CAST(SUM(tPICUS) AS INT)/60)||':'||printf('%02d',CAST(SUM(tPICUS) AS INT)%60) AS 'PICUS',
        printf('%02d',CAST(SUM(tSIC) AS INT)/60)||':'||printf('%02d',CAST(SUM(tSIC) AS INT)%60) AS 'SIC',
        printf('%02d',CAST(SUM(tDual) AS INT)/60)||':'||printf('%02d',CAST(SUM(tDual) AS INT)%60) AS 'DUAL',
        printf('%02d',CAST(SUM(tFI) AS INT)/60)||':'||printf('%02d',CAST(SUM(tFI) AS INT)%60) AS 'INSTRUCTOR',
        printf('%02d',CAST(SUM(tSIM) AS INT)/60)||':'||printf('%02d',CAST(SUM(tSIM) AS INT)%60) AS 'SIMULATOR',
        printf('%02d',CAST(SUM(tMP) AS INT)/60)||':'||printf('%02d',CAST(SUM(tMP) AS INT)%60) AS 'MultPilot',
        CAST(SUM(toDay) AS INT) AS 'TO Day',
        CAST(SUM(toNight) AS INT) AS 'TO Night',
        CAST(SUM(ldgDay) AS INT) AS 'LDG Day',
        CAST(SUM(ldgNight) AS INT) AS 'LDG Night'
FROM flights;

DROP VIEW IF EXISTS 'viewExport';
CREATE VIEW viewExport AS
SELECT  flight_id,
        doft as 'Date',
        dept AS 'Dept',
        printf('%02d',(tofb/60))||':'||printf('%02d',(tofb%60)) AS 'Time Out',
        dest AS 'Dest',
        printf('%02d',(tonb/60))||':'||printf('%02d',(tonb%60)) AS 'Time In ',
        CASE  WHEN variant IS NOT NULL THEN make||' '||model||'-'||variant  ELSE make||' '||model  END  AS 'Type',
        registration AS 'Registration',
        (SELECT printf('%02d',(tSPSE/60))||':'||printf('%02d',(tSPSE%60)) WHERE tSPSE IS NOT NULL) AS 'SP SE',
        (SELECT printf('%02d',(tSPME/60))||':'||printf('%02d',(tSPME%60)) WHERE tSPME IS NOT NULL) AS 'SP ME',
        (SELECT printf('%02d',(tMP/60))||':'||printf('%02d',(tMP%60)) WHERE tMP IS NOT NULL) AS 'MP',
        printf('%02d',(tblk/60))||':'||printf('%02d',(tblk%60)) AS 'Total',
        CASE  WHEN pilot_id = 1 THEN alias  ELSE lastname||', '||substr(firstname, 1, 1)||'.'  END  AS 'Name PIC',
        toDay AS 'Take-Off Day',
        ldgDay AS 'Landings Day',
        toNight AS 'Take-Off Night',
        ldgNight AS 'Landings Night',
        (SELECT printf('%02d',(tNight/60))||':'||printf('%02d',(tNight%60)) WHERE tNight IS NOT NULL)  AS 'Night',
        (SELECT printf('%02d',(tIFR/60))||':'||printf('%02d',(tIFR%60)) WHERE tIFR IS NOT NULL)  AS 'IFR',
        (SELECT printf('%02d',(tPIC/60))||':'||printf('%02d',(tPIC%60)) WHERE tPIC IS NOT NULL)  AS 'PIC',
        (SELECT printf('%02d',(tSIC/60))||':'||printf('%02d',(tSIC%60)) WHERE tSIC IS NOT NULL)  AS 'SIC',
        (SELECT printf('%02d',(tDual/60))||':'||printf('%02d',(tDual%60)) WHERE tDual IS NOT NULL)  AS 'Dual',
        (SELECT printf('%02d',(tFI/60))||':'||printf('%02d',(tFI%60)) WHERE tFI IS NOT NULL)  AS 'FI',
        null AS 'Sim Type',
        null AS 'Time of Session',
        remarks AS 'Remarks'
FROM flights
INNER JOIN pilots on flights.pic = pilots.pilot_id
INNER JOIN tails on flights.acft = tails.tail_id
UNION
SELECT (session_id * -1),
        date,
        null,  null,  null,  null,
        aircraftType,
        registration,
        null,  null,  null,
        'SIM',
        null,  null,  null,  null,  null,  null,  null,  null,  null,  null, null,
        deviceType,  printf('%02d',(totalTime/60))||':'||printf('%02d',(totalTime%60)),
        remarks
FROM simulators
ORDER BY date DESC;