/*
 *  Copyright (C) 2022,2023 Joey Allard
 *  Copyright (C) 2023 Ruben Rodriguez <ruben@gnu.org>
 *
 *  This program is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU Affero 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 Affero General Public License for more details.
 *
 *  You should have received a copy of the GNU Affero General Public License
 *  along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

DROP TABLE IF EXISTS `categories`;
DROP TABLE IF EXISTS `extension_locale`;
DROP TABLE IF EXISTS `preview_locale`;
DROP TABLE IF EXISTS `ext_cat`;
DROP TABLE IF EXISTS `licenses`;
DROP TABLE IF EXISTS `extensions`;

CREATE TABLE `categories` (
  `cat_id` varchar(50) NOT NULL PRIMARY KEY,
  `display_en` varchar(50) NOT NULL,
  `description_en` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `extensions` (
  `ext_id` int(11) NOT NULL PRIMARY KEY,
  `slug` varchar(255) NOT NULL,
  `guid` varchar(255) NOT NULL,
  `download_link` varchar(255) NOT NULL,
  `homepage` varchar(255) NOT NULL,
  `support_email` varchar(255) NOT NULL,
  `contributions_url` varchar(255) NOT NULL,
  `support_url` varchar(255) NOT NULL,
  `url` varchar(255) NOT NULL,
  `default_locale` varchar(10) DEFAULT 'en-US',
  `lic_id` varchar(30) DEFAULT NULL,
  `created` datetime NOT NULL,
  `last_updated` datetime NOT NULL,
  `average_daily_users` int(11) NOT NULL DEFAULT 0,
  `weekly_downloads` int(11) NOT NULL DEFAULT 0,
  `average_rating` float NOT NULL DEFAULT 0,
  `ratings_count` int(11) NOT NULL DEFAULT 0,
  `promoted` boolean DEFAULT false
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `extension_locale` (
  `ext_id` int(11) NOT NULL,
  `locale` varchar(10) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `summary` text DEFAULT NULL,
   PRIMARY KEY (`ext_id`,`locale`),
   FOREIGN KEY (`ext_id`)
       REFERENCES extensions (`ext_id`)
       ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `preview_locale` (
  `img_id` int(11) NOT NULL,
  `ext_id` int(11) NOT NULL,
  `locale` varchar(10) NOT NULL,
  `caption` text DEFAULT NULL,
   PRIMARY KEY (`img_id`,`ext_id`,`locale`),
   FOREIGN KEY (`ext_id`)
       REFERENCES extensions (`ext_id`)
       ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `ext_cat` (
  `ext_id` int(11) NOT NULL,
  `cat_id` varchar(50) NOT NULL,
  `app_id` varchar(20) NOT NULL,
   PRIMARY KEY (`ext_id`,`cat_id`,`app_id`),
   FOREIGN KEY (`ext_id`)
       REFERENCES extensions (`ext_id`)
       ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `licenses` (
  `lic_id` varchar(30) NOT NULL PRIMARY KEY,
  `license_url` varchar(255) NOT NULL,
  `lic_name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;