-- ****************************************************************************************************
-- *      ______                                                  _                       _           *
-- *     |  ____|                                           /\   (_)                     | |          *
-- *     | |__  _   _ _ __ ___  _ __   ___  __ _ _ __      /  \   _ _ __ _ __   ___  _ __| |_ ___     *
-- *     |  __|| | | | '__/ _ \| '_ \ / _ \/ _` | '_ \    / /\ \ | | '__| '_ \ / _ \| '__| __/ __|    *
-- *     | |___| |_| | | | (_) | |_) |  __/ (_| | | | |  / ____ \| | |  | |_) | (_) | |  | |_\__ \    *
-- *     |______\__,_|_|  \___/| .__/ \___|\__,_|_| |_| /_/    \_\_|_|  | .__/ \___/|_|   \__|___/    *
-- *                           | |                                      | |                           *
-- *                           |_|                                      |_|                           *
-- *                                                                                                  *
-- ****************************************************************************************************

-- This file is part of the European Airports Project, a free, collaborative platform of airport data 
-- extending beyond the official AIPs.

-- Copyright (C) 2023

-- Florian Meissner <florianmeissner@gmx.de>

-- This program is free software: you can redistribute it and/or modify it under the 
-- terms of the GNU General Public License as published by the Free Software Foundation, either 
-- version 3 of the License, or (at your option) any later version. This program is distributed in the 
-- hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of 
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more 
-- details. You should have received a copy of the GNU General Public License along with this program. 
-- (license.md in the root folder of this project) If not, see <http://www.gnu.org/licenses/>.


USE eaMain;

START TRANSACTION;

CREATE TABLE IF NOT EXISTS `airports` (
  `id` INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  `icao` CHAR(4) UNIQUE,
  `ident` VARCHAR(6) NOT NULL UNIQUE COMMENT 'Local identifier',
  `iata` CHAR(3),
  `name` VARCHAR(100) NOT NULL,
  `type` ENUM('B', 'G', 'H', 'I', 'N', 'U') NOT NULL,
  `lat` DECIMAL(7, 2) NOT NULL COMMENT 'degrees',
  `lon` DECIMAL(8, 2) NOT NULL COMMENT 'degrees',
  `continent` ENUM('AF', 'AN', 'AS', 'CA', 'EU', 'NA', 'OC', 'SA') DEFAULT 'EU' NOT NULL,
  `country` CHAR(2) DEFAULT 'ZZ' NOT NULL COMMENT 'ISO 3166-1 alpha-2 identifier',
  `region` CHAR(5) COMMENT 'ISO 3166-2 identifier',
  `city` VARCHAR(100),
  CONSTRAINT `airports.hasIdent` CHECK (
    NOT (
      `icao` IS NULL 
      AND `ident` IS NULL 
      AND `iata` IS NULL
    )
  )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

COMMIT;