-- NOTES ON UPDATES AFTER PUBLICATION 
-- 1) Nov 17, 2019: extended unique constraint in Cities from
--    UNIQUE (Name, CountryID) to UNIQUE (Name, Timezone, CountryID)

DROP TABLE IF EXISTS RoutesPlanes, Planes, Routes, Airports, Airlines, Cities, Countries;

CREATE TABLE Countries( 
  CountryID SERIAL PRIMARY KEY,
  Name VARCHAR(256) NOT NULL UNIQUE
); 

CREATE TABLE Cities( 
  CityID SERIAL PRIMARY KEY,
  Name VARCHAR(256) NOT NULL, 
  Timezone DECIMAL CHECK((timezone BETWEEN -12 AND 14) OR timezone = NULL),
  DST VARCHAR(128) CHECK (DST IN ('E', 'A', 'S', 'O', 'Z', 'N', 'U')),
  CountryID INT NOT NULL REFERENCES Countries(CountryID),
  UNIQUE (Name, Timezone, CountryID)
); 

CREATE TABLE Airports(
  IATA CHAR(3) PRIMARY KEY,
  ICAO CHAR(4) UNIQUE,
  Name VARCHAR(256),
  CityID INT NOT NULL REFERENCES Cities(CityID),
  Latitude DECIMAL NOT NULL,
  Longtitude DECIMAL NOT NULL,
  Altitude SMALLINT,
  UNIQUE (Name, CityID)
); 

CREATE TABLE Airlines(
  IATA CHAR(2) PRIMARY KEY,
  ICAO CHAR(3) UNIQUE,
  Name VARCHAR(256),
  CountryID INT NOT NULL REFERENCES Countries(CountryID), 
  IsActive BOOLEAN DEFAULT TRUE
);

CREATE TABLE Routes(
  RID SERIAL PRIMARY KEY,
  Airline CHAR(2) NOT NULL REFERENCES Airlines(IATA),
  DepAir CHAR(3) NOT NULL REFERENCES Airports(IATA),
  ArrAir CHAR(3) NOT NULL REFERENCES Airports(IATA),
  UNIQUE(Airline, DepAir, ArrAir)
);

CREATE TABLE Planes(
  IATA CHAR(3) PRIMARY KEY,
  ICAO CHAR(4) UNIQUE, 
  Name VARCHAR(256) NOT NULL
);

CREATE TABLE RoutesPlanes(
  RID INT NOT NULL REFERENCES Routes(RID),
  Plane CHAR(3) REFERENCES Planes(IATA), 
  PRIMARY KEY(RID, Plane)
);
