freedom-kiel.sql

-- DDL generated by Postico 1.5.6
-- Not all database features are supported. Do not use for backup.

-- Table Definition ----------------------------------------------

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username character varying NOT NULL,
    email character varying NOT NULL,
    password character varying NOT NULL,
    activation_key character varying NOT NULL,
    last_connection timestamp without time zone NOT NULL DEFAULT now(),
    create_date timestamp without time zone NOT NULL DEFAULT now(),
    modified_date timestamp without time zone NOT NULL DEFAULT now(),
    status smallint NOT NULL DEFAULT '-1'::integer
);


-- DDL generated by Postico 1.5.6
-- Not all database features are supported. Do not use for backup.

-- Table Definition ----------------------------------------------

CREATE TABLE user_information (
    id SERIAL PRIMARY KEY,
    user_id integer NOT NULL REFERENCES users(id),
    first_name character varying,
    last_name character varying,
    address_line_1 character varying,
    address_line_2 character varying,
    mobile_number character varying,
    nationality character varying,
    create_date timestamp without time zone NOT NULL DEFAULT now(),
    modified_date timestamp without time zone NOT NULL DEFAULT now(),
    avatar character varying
);


-- DDL generated by Postico 1.5.6
-- Not all database features are supported. Do not use for backup.

-- Table Definition ----------------------------------------------

CREATE TABLE bikes (
    id SERIAL PRIMARY KEY,
    title character varying NOT NULL,
    address character varying(255) NOT NULL,
    city character varying(65) NOT NULL DEFAULT 'Kiel'::character varying,
    zip character varying(11) NOT NULL,
    lat numeric NOT NULL,
    lon numeric NOT NULL,
    created_by integer NOT NULL REFERENCES users(id),
    create_date timestamp without time zone NOT NULL DEFAULT now(),
    modified_date timestamp without time zone NOT NULL DEFAULT now(),
    status smallint NOT NULL DEFAULT 1,
    geom geometry(Point,4326)
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX bikes_pkey ON bikes(id int4_ops);
CREATE INDEX bikes_gist ON bikes USING GIST (geom gist_geometry_ops_2d);


-- DDL generated by Postico 1.5.6
-- Not all database features are supported. Do not use for backup.

-- Table Definition ----------------------------------------------

CREATE TABLE bike_info (
    id SERIAL PRIMARY KEY,
    bike_id integer NOT NULL REFERENCES bikes(id),
    description text,
    size smallint NOT NULL DEFAULT 0,
    sex smallint NOT NULL DEFAULT 0,
    photos character varying,
    minimum_rent numeric NOT NULL DEFAULT 0,
    daily_rent numeric NOT NULL DEFAULT 0,
    security_deposit numeric NOT NULL DEFAULT 0,
    create_date timestamp without time zone NOT NULL DEFAULT now(),
    modified_date timestamp without time zone NOT NULL DEFAULT now(),
    company_name character varying(255),
    bike_type character varying(55)
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX bike_info_pkey ON bike_info(id int4_ops);


-- DDL generated by Postico 1.5.6
-- Not all database features are supported. Do not use for backup.

-- Table Definition ----------------------------------------------

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    buyer_user_id integer NOT NULL REFERENCES users(id),
    bike_id integer NOT NULL,
    pickup_time timestamp without time zone NOT NULL,
    dropoff_time timestamp without time zone NOT NULL,
    address_pick_drop character varying NOT NULL,
    rent_total numeric NOT NULL,
    payment_methods character varying,
    create_date timestamp without time zone NOT NULL DEFAULT now(),
    modified_date timestamp without time zone NOT NULL DEFAULT now(),
    status smallint NOT NULL DEFAULT 3
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX order_items_pkey ON order_items(id int4_ops);


-- DDL generated by Postico 1.5.6
-- Not all database features are supported. Do not use for backup.

-- Table Definition ----------------------------------------------

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    bike_id integer NOT NULL REFERENCES bikes(id),
    user_id integer NOT NULL REFERENCES users(id),
    content text NOT NULL,
    rating smallint NOT NULL,
    comment_parent integer NOT NULL DEFAULT 0,
    create_date timestamp without time zone NOT NULL DEFAULT now(),
    modified_date timestamp without time zone NOT NULL DEFAULT now(),
    status smallint NOT NULL DEFAULT 1
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX comments_pkey ON comments(id int4_ops);


UPDATE coffee_shops SET geom = ST_SetSRID(ST_MakePoint(lon,lat),4326);