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

-- 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 `photos` (
  `id` INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  `airport` INT UNSIGNED NOT NULL,
  `uploadedBy` INT UNSIGNED NOT NULL,
  `uploadedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
  `title` VARCHAR(200),
  `description` TEXT NOT NULL,
  `runway` INT UNSIGNED,
  `category` BIT(8) NOT NULL DEFAULT 0b00000000,
  `hidden` BOOLEAN NOT NULL DEFAULT FALSE,
  CONSTRAINT `photos.fk_uploadedBy` FOREIGN KEY (`uploadedBy`) REFERENCES users(id),
  CONSTRAINT `photos.fk_airport` FOREIGN KEY (`airport`) REFERENCES airports(id),
  CONSTRAINT `photos.fk_runway` FOREIGN KEY (`runway`) REFERENCES runways(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

COMMIT;