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

-- 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 `runways` (
  `id` INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  `airport` INT UNSIGNED NOT NULL,
  `length` MEDIUMINT UNSIGNED NOT NULL COMMENT 'meters',
  `width` SMALLINT UNSIGNED NOT NULL COMMENT 'meters',
  `surface` ENUM('ASP', 'BIT', 'BRI', 'CLA', 'COM', 'CON', 'COP', 'COR', 'GRE', 'GRS', 'GVL', 
    'ICE', 'LAT', 'MAC', 'PEM', 'PER', 'PSP', 'SAN', 'SMT', 'SNO', 'U', 'WAT') NOT NULL DEFAULT 'U',
  `lighted` BOOLEAN NOT NULL DEFAULT FALSE,
  `closed` BOOLEAN NOT NULL DEFAULT FALSE,
  `leIdent` VARCHAR(3) NOT NULL COMMENT 'runway designator',
  `leLat` DECIMAL(7, 2) NOT NULL COMMENT 'degrees',
  `leLon` DECIMAL(8, 2) NOT NULL COMMENT 'degrees',
  `leElev` FLOAT(7, 3) NOT NULL COMMENT 'meters',
  `leHdg` MEDIUMINT UNSIGNED NOT NULL COMMENT 'degrees',
  `leDisplThres` MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'meters',
  `heIdent` VARCHAR(3) NOT NULL COMMENT 'runway designator',
  `heLat` DECIMAL(7, 2) NOT NULL COMMENT 'degrees',
  `heLon` DECIMAL(8, 2) NOT NULL COMMENT 'degrees',
  `heElev` FLOAT(7, 3) NOT NULL COMMENT 'meters',
  `heHdg` MEDIUMINT UNSIGNED NOT NULL COMMENT 'degrees',
  `heDisplThres` MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'meters',
  CONSTRAINT `runways.fk_airport` FOREIGN KEY (`airport`) REFERENCES airports(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='le=low end; he=high end';

COMMIT;