<commit_before>DROP schema PUBLIC cascade;
CREATE schema PUBLIC;

CREATE EXTENSION IF NOT EXISTS cube;
CREATE EXTENSION IF NOT EXISTS earthdistance;

-- route contains selected fields from 
-- https://developers.google.com/transit/gtfs/reference#routestxt
CREATE TABLE route (
    -- route_id is the unique id of this route
    route_id            TEXT NOT NULL PRIMARY KEY,

    -- route_type is an id code for whether it's a subway, bus, etc. 
    -- (e.g., subway=1, bus=3, see link above for full doc)
    route_type          INT NOT NULL,

    -- route_color and route_text_color are the hex values for the background
    -- and foreground color of the route, respectively (e.g., #000000)
    route_color         TEXT NOT NULL,
    route_text_color    TEXT NOT NULL
);

CREATE TABLE stop (
    -- the unique id and name of the stop
    stop_id   TEXT NOT NULL,
    stop_name TEXT NOT NULL,

    -- direction of the stop, either 0 or 1
    direction_id INT NOT NULL,

    -- the direction this stop is going, e.g., "LI CITY QUEENS PLAZA" or "8 AV"
    headsign TEXT NOT NULL,

    -- the name of the route, e.g., "B62" or "G"
    route_id    TEXT NOT NULL,

    -- lat and lon converted into an earthdistance type
    location    EARTH NOT NULL,

    UNIQUE(route_id, stop_id)
);
CREATE INDEX idx_location_stop ON stop USING gist(location);

CREATE TYPE day_type AS ENUM ('monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday', 'sunday');

CREATE TABLE service_route_day (
    route_id    TEXT NOT NULL,
    service_id  TEXT NOT NULL,
    day         DAY_TYPE NOT NULL,
    start_date  DATE NOT NULL,
    end_date    DATE NOT NULL,

    UNIQUE(route_id, service_id, day, start_date, end_date)
);

CREATE TABLE service_route_exception (
    route_id        TEXT NOT NULL,
    service_id      TEXT NOT NULL,
    exception_date  DATE NOT NULL,

    UNIQUE(route_id, service_id, exception_date)
);

CREATE TABLE scheduled_stop_time (
    route_id       TEXT NOT NULL,
    stop_id        TEXT NOT NULL,
    service_id     TEXT NOT NULL,
    departure_sec  INT  NOT NULL,
    
    UNIQUE(route_id, stop_id, service_id, departure_sec)
);
-- CREATE INDEX idx_sst ON scheduled_stop_time (route_id, stop_id, service_id, departure_sec);
-- select route_id, service_id, max(start_day) from service_route_day where route_id = 'G' and day = 'monday' group by route_id, service_id;

--  select route_id, service_id, max(start_date) from service_route_day where route_id = 'G' and day = 'monday' and end_date > current_timestamp and start_date < current_timestamp group by route_id, service_id;
<commit_msg>adding agency, trip shape to schema<commit_after>DROP schema PUBLIC cascade;
CREATE schema PUBLIC;

CREATE EXTENSION IF NOT EXISTS cube;
CREATE EXTENSION IF NOT EXISTS earthdistance;

-- route contains selected fields from 
-- https://developers.google.com/transit/gtfs/reference#routestxt
CREATE TABLE route (
    agency_id           TEXT NOT NULL,

    -- route_id is the unique id of this route
    route_id            TEXT NOT NULL,

    -- route_type is an id code for whether it's a subway, bus, etc. 
    -- (e.g., subway=1, bus=3, see link above for full doc)
    route_type          INT NOT NULL,

    -- route_color and route_text_color are the hex values for the background
    -- and foreground color of the route, respectively (e.g., 000000)
    route_color         TEXT NOT NULL,
    route_text_color    TEXT NOT NULL

    UNIQUE(agency_id, route_id)
);

CREATE TABLE stop (
    -- the unique id and name of the stop
    agency_id TEXT NOT NULL,
    stop_id   TEXT NOT NULL,
    stop_name TEXT NOT NULL,

    -- direction of the stop, either 0 or 1
    direction_id INT NOT NULL,

    -- the direction this stop is going, e.g., "LI CITY QUEENS PLAZA" or "8 AV"
    headsign TEXT NOT NULL,

    -- the name of the route, e.g., "B62" or "G"
    route_id    TEXT NOT NULL,

    -- lat and lon converted into an earthdistance type
    location    EARTH NOT NULL,

    UNIQUE(agency_id, route_id, stop_id)
);
CREATE INDEX idx_location_stop ON stop USING gist(location);

CREATE TYPE day_type AS ENUM ('monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday', 'sunday');

CREATE TABLE service_route_day (
    agency_id   TEXT NOT NULL,
    route_id    TEXT NOT NULL,
    service_id  TEXT NOT NULL,
    day         DAY_TYPE NOT NULL,
    start_date  DATE NOT NULL,
    end_date    DATE NOT NULL,

    UNIQUE(agency_id, route_id, service_id, day, start_date, end_date)
);

CREATE TABLE trip (
    trip_id     TEXT NOT NULL PRIMARY KEY,
    service_id  TEXT NOT NULL,
    shape_id    TEXT NOT NULL
)

CREATE TABLE shape (
    shape_id TEXT NOT NULL,
    location EARTH NOT NULL,
    seq      INT NOT NULL,

    UNIQUE(shape_id, seq)
)

CREATE TABLE service_route_exception (
    agency_id       TEXT NOT NULL,
    route_id        TEXT NOT NULL,
    service_id      TEXT NOT NULL,
    exception_date  DATE NOT NULL,

    UNIQUE(agency_id, route_id, service_id, exception_date)
);

CREATE TABLE scheduled_stop_time (
    agency_id      TEXT NOT NULL,
    route_id       TEXT NOT NULL,
    stop_id        TEXT NOT NULL,
    service_id     TEXT NOT NULL,
    departure_sec  INT  NOT NULL,
    
    UNIQUE(agency_id, route_id, stop_id, service_id, departure_sec)
);
-- CREATE INDEX idx_sst ON scheduled_stop_time (route_id, stop_id, service_id, departure_sec);
-- select route_id, service_id, max(start_day) from service_route_day where route_id = 'G' and day = 'monday' group by route_id, service_id;

--  select route_id, service_id, max(start_date) from service_route_day where route_id = 'G' and day = 'monday' and end_date > current_timestamp and start_date < current_timestamp group by route_id, service_id;

{'fraction_non_alphanumeric': 0.07223870179724307, 'fraction_numerical': 0.004536730064561159, 'mean_word_length': 2.9888656924147528, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3662858', 'n_tokens_mistral': 1883, 'n_tokens_neox': 1792, 'n_words': 673}