FloatBackOfffice/CRONS/weather/new_schema.sql

CREATE TABLE estimates (
    id bigserial,
    price numeric,
    surge_price numeric,
    trip_time character varying,
    distance integer,
    is_holiday boolean DEFAULT false,
    day_of_week integer,
    weather_conditions text,
    created_at timestamp(6) without time zone NOT NULL,
    updated_at timestamp(6) without time zone NOT NULL,
    parsedemail_item_id bigint REFERENCES parsedemail_item(id),
    PRIMARY KEY (id)
);

CREATE TABLE holidays (
    id serial,
    date date NOT NULL,
    note text,
    active boolean DEFAULT true,
    country character varying (2) NOT NULL REFERENCES country(code),
    created_at timestamp without time zone DEFAULT now() NOT NULL,
    PRIMARY KEY (id),
    UNIQUE (date,country)
);

CREATE TABLE trip_holidays (
    id bigserial,
    data_source_id bigint,
    data_source smallint,
    created timestamp without time zone DEFAULT now() NOT NULL,
    holiday_id integer NOT NULL REFERENCES holidays(id),
    normal_cost numeric,
    PRIMARY KEY(id),
    UNIQUE (data_source_id, data_source)
);

CREATE TABLE trip_price_comparison (
    id bigserial,
    data_source_id bigint,
    root_id bigint,
    cost numeric,
    average numeric,
    created timestamp without time zone DEFAULT now(),
    data_source smallint,
    root_type smallint,
    PRIMARY KEY(id),
    UNIQUE(data_source_id,data_source,root_id)
);

CREATE TABLE trip_surge_price (
    id bigserial,
    data_source_id bigint,
    created timestamp without time zone DEFAULT now(),
    data_source smallint,
    PRIMARY KEY(id),
    UNIQUE (data_source_id)
);

CREATE TABLE weather (
    id bigserial,
    temp_c numeric,
    avg_temp_c numeric,
    total_snow_cm numeric,
    wind_speed_kmph varchar,
    weather_code varchar,
    precip_mm varchar,
    humidity varchar,
    visibility varchar,
    pressure varchar,
    heat_index_c varchar,
    wind_chill_c varchar,
    wind_gust_kmph varchar,
    feels_like_c varchar,
    latitude float8,
    longitude float8,
    geometry geometry,
    created timestamp without time zone DEFAULT now(),
    date date,
    dewpoint float4,
    precipitation float4,
    precipitation_3 float4,
    precipitation_6 float4,
    winddirection int4,
    condition int4,
    time timestamp,
    time_local timestamp
    PRIMARY KEY(id)
);

CREATE TABLE trip_weather(
    id bigserial,
    data_source_id bigint,
    data_source smallint,
    weather_id bigint REFERENCES weather(id),
    created timestamp without time zone DEFAULT now(),
    PRIMARY KEY(id),
    UNIQUE (data_source_id)
);

CREATE TABLE country_weather_stations (
    id bigserial,
    country_code bigint,
    station_id bigint,
    station_name varchar,
    latitude varchar,
    longitude varchar,
    region varchar,
    created timestamp without time zone DEFAULT now(),
    PRIMARY KEY(id)
);

CREATE TABLE weather_services (
    id bigserial,
    service_name varchar,
    api_key varchar,
    url varchar,
    active boolean DEFAULT true,
    used boolean DEFAULT false,
    stop_time timestamp without time zone,
    remaining_requests int4,
    PRIMARY KEY(id)
);

CREATE TABLE address (
    id             bigserial,
    address        varchar(200),
    latitude       float8,
    longitude      float8,
    timezone       int4,
    geocoding_date date,
    postal         varchar(40),
    country        varchar(2),
    geometry       geometry,
    description    varchar,
    PRIMARY KEY(id)
);

CREATE TABLE trackedemail_item (
  id bigserial,
  member_id bigserial,
  PRIMARY KEY(id)
);

CREATE VIEW union_trip_and_quote_view_table AS
(
  SELECT
    trip.id AS data_source_id,
    trip.cost,
    trip.location_start_id,
    trip.location_end_id,
    trip.travel_date as travel_date,
    1 AS data_source
  FROM
    parsedemail_item AS trip
  WHERE
    trip.cost IS NOT NULL AND trip.cost>0
)
UNION
(
  SELECT
    quotes.id AS data_source_id,
    quotes.cost,
    quotes.location_start_id,
    quotes.location_end_id,
    quotes.travel_date as travel_date,
    2 AS data_source
  FROM
    quotes
  WHERE
    quotes.cost IS NOT NULL AND quotes.cost>0
);

CREATE TABLE global_settings (
    id          bigserial,
    key         varchar,
    description varchar,
    value       int4,
    status      smallint,
    added       date,
    PRIMARY KEY(id)
);