/* MariaDB-focused SQL queries to create the tables from GTFS zip files. By default drops tables and initializes them without data, assuming the database itself exists and is currently in use. Uncomment as you need! */

/*
CREATE DATABASE `opentakeets` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
grant select, insert, update, create, drop on opentakeets.* to opentakeets@localhost;
grant create temporary tables on opentakeets.* to opentakeets@localhost;
grant delete on opentakeets.trip_stop_updates to opentakeets@localhost; -- for the Python GTFS-rt to database script
grant reload on *.* to opentakeets@localhost; -- for the GTFS zipfile import script to avoid initially slow queries afterwards

USE `opentakeets`;
*/

/* I started doing this manually, then started cribbing from https://github.com/BlinkTagInc/gtfs-to-mysql/blob/master/gtfs_to_mysql.sql, then noticed it is often wrong for ETS boooo */


CREATE TABLE `agency` (
  agency_id VARCHAR(50) PRIMARY KEY,
  agency_name VARCHAR(255),
  agency_url VARCHAR(255),
  agency_timezone VARCHAR(50),
  agency_lang VARCHAR(50),
  agency_phone VARCHAR(50)
);


CREATE TABLE `areas` (
  `area_id` varchar(4) NOT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`area_id`)
);


CREATE TABLE `calendar_dates` (
  `service_id` varchar(50) NOT NULL,
  `date` varchar(8) NOT NULL,
  exception_type int(2),
  KEY `service_id` (service_id),
  KEY `exception_type` (exception_type)
);

-- TODO: fare_leg_rules, fare_media, fare_products, fare_transfer_rules


CREATE TABLE `feed_info` (
  feed_publisher_name text,
  feed_publisher_url text,
  feed_lang varchar(3),
  feed_start_date varchar(8),
  feed_end_date varchar(8),
  feed_version text,
  feed_contact_email text
);

-- TODO: levels, pathways


CREATE TABLE `routes` (
  route_id varchar(8) NOT NULL,
  agency_id int,
  route_short_name varchar(12),
  route_long_name varchar(128) NOT NULL,
  route_desc VARCHAR(255),
  route_type INT(2),
  route_color VARCHAR(50),
  route_text_color VARCHAR(50),
  KEY `route_id` (route_id),
  KEY `route_type` (route_type)
);

-- TODO: shapes


CREATE TABLE `stop_areas` (
  area_id varchar(4) NOT NULL,
  stop_id VARCHAR(50),
  PRIMARY KEY `stop_id` (stop_id)
);


/* Warns about timepoint truncation, probably some EOL stuff */
CREATE TABLE `stop_times` (
  trip_id VARCHAR(50),
  arrival_time TIME,
  departure_time TIME,
  stop_id VARCHAR(8),
  stop_sequence INT(11),
  stop_headsign VARCHAR(255),
  pickup_type INT(2),
  drop_off_type INT(2),
  timepoint INT(2), 
  KEY `trip_id` (trip_id),
  KEY `stop_id` (stop_id),
  KEY `stop_sequence` (stop_sequence)
);


CREATE TABLE `stops` (
  stop_id VARCHAR(50),
  stop_code VARCHAR(50),
  stop_name VARCHAR(255),
  stop_desc VARCHAR(50),
  stop_lat DECIMAL(9,6),
  stop_lon DECIMAL(9,6),
  zone_id VARCHAR(8),
  stop_url VARCHAR(50),
  location_type INT(2),
  parent_station VARCHAR(50),
  level_id VARCHAR(2),
  wheelchair_boarding VARCHAR(2),
  preferred INT(3),
  platform_code CHAR(1),
  PRIMARY KEY `stop_id` (stop_id),
  KEY `stop_lat` (stop_lat),
  KEY `stop_lon` (stop_lon)
);


/* Warns about timepoint truncation, probably some EOL stuff */
CREATE TABLE `trips` (
  route_id VARCHAR(50),
  service_id VARCHAR(50),
  trip_id VARCHAR(50) PRIMARY KEY,
  trip_headsign VARCHAR(255),
  direction_id TINYINT(1),
  block_id VARCHAR(50),
  shape_id VARCHAR(50),
  wheelchair_accessible INT(2),
  bikes_allowed INT(2),
  KEY `route_id` (route_id),
  KEY `service_id` (service_id),
  KEY `direction_id` (direction_id)
);

-- TODO: trip_pattern


-- LOAD DATA LOCAL INFILE 'agency.txt' INTO TABLE agency FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES;
-- 
-- LOAD DATA LOCAL INFILE 'calendar_dates.txt' INTO TABLE calendar_dates FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES;
-- 
-- LOAD DATA LOCAL INFILE 'fare_attributes.txt' INTO TABLE fare_attributes FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES;
-- 
-- LOAD DATA LOCAL INFILE 'fare_rules.txt' INTO TABLE fare_rules FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES;
-- 
-- LOAD DATA LOCAL INFILE 'feed_info.txt' INTO TABLE fare_rules FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES;
-- 
-- LOAD DATA LOCAL INFILE 'routes.txt' INTO TABLE routes FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES;
-- 
-- LOAD DATA LOCAL INFILE 'stop_times.txt' INTO TABLE stop_times FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES;
-- 
-- LOAD DATA LOCAL INFILE 'stops.txt' INTO TABLE stops FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES;
-- 
-- LOAD DATA LOCAL INFILE 'trips.txt' INTO TABLE trips FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES;


-- BEGIN special custom tables

create or replace table `trip_stop_updates` (
  trip_id VARCHAR(50),
  stop_id VARCHAR(50),
  route_id VARCHAR(50),
  direction_id TINYINT(1),
  delay SMALLINT SIGNED,
  time INT UNSIGNED,
  timestamp INT UNSIGNED,
  PRIMARY KEY(trip_id, stop_id)
);



-- BEGIN human convenience tables

-- some table for location_type from https://gtfs.org/documentation/schedule/reference/#stopstxt
