structure.sql

structure.sql

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';


SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: alternative_sources; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE alternative_sources (
    id integer NOT NULL,
    alternative_id integer,
    name character varying(255),
    realm_id integer,
    agency_id character varying(255),
    type character varying(255),
    url text,
    lat double precision,
    lng double precision,
    data json,
    created_at timestamp without time zone,
    updated_at timestamp without time zone
);


--
-- Name: alternative_sources_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE alternative_sources_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: alternative_sources_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE alternative_sources_id_seq OWNED BY alternative_sources.id;


--
-- Name: alternatives; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE alternatives (
    id integer NOT NULL,
    name character varying(255) NOT NULL,
    reviews_count integer DEFAULT 0 NOT NULL,
    realm_id integer,
    ta_id integer,
    lat real,
    lng real,
    location_name character varying(255),
    country_name character varying(255)
);


--
-- Name: alternatives_criteria; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE alternatives_criteria (
    alternative_id integer,
    criterion_id integer,
    rating double precision,
    reviews_count integer DEFAULT 0 NOT NULL,
    rank_world integer,
    weighted_rating double precision,
    review_sentences_count integer DEFAULT 0 NOT NULL,
    rank_location integer,
    rank_country integer
);


--
-- Name: alternatives_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE alternatives_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: alternatives_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE alternatives_id_seq OWNED BY alternatives.id;


--
-- Name: criteria; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE criteria (
    id integer NOT NULL,
    name_en character varying(255) NOT NULL,
    short_name character varying(255),
    description character varying(255),
    status integer DEFAULT 0 NOT NULL,
    ancestry character varying(255),
    external_id integer,
    slug character varying(255),
    ancestry_depth integer DEFAULT 0,
    name_ru character varying(255)
);


--
-- Name: criteria_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE criteria_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: criteria_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE criteria_id_seq OWNED BY criteria.id;


--
-- Name: identities; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE identities (
    id integer NOT NULL,
    user_id integer NOT NULL,
    provider character varying(255) NOT NULL,
    uid character varying(255) NOT NULL,
    created_at timestamp without time zone,
    updated_at timestamp without time zone
);


--
-- Name: identities_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE identities_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: identities_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE identities_id_seq OWNED BY identities.id;


--
-- Name: invites; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE invites (
    id integer NOT NULL,
    email character varying(255),
    created_at timestamp without time zone,
    updated_at timestamp without time zone
);


--
-- Name: invites_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE invites_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: invites_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE invites_id_seq OWNED BY invites.id;


--
-- Name: media; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE media (
    id integer NOT NULL,
    alternative_id integer,
    type character varying(255),
    agency_id character varying(255),
    url character varying(255),
    medium_type character varying(255),
    cover boolean DEFAULT false NOT NULL,
    status integer DEFAULT 0 NOT NULL,
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    file_file_name character varying(255),
    file_content_type character varying(255),
    file_file_size integer,
    file_updated_at timestamp without time zone
);


--
-- Name: media_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE media_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: media_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE media_id_seq OWNED BY media.id;


--
-- Name: property_fields; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE property_fields (
    id integer NOT NULL,
    group_id integer,
    field_type character varying(255) NOT NULL,
    short_name character varying(255) NOT NULL,
    name character varying(255) NOT NULL,
    status integer DEFAULT 0 NOT NULL,
    created_at timestamp without time zone,
    updated_at timestamp without time zone
);


--
-- Name: property_fields_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE property_fields_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: property_fields_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE property_fields_id_seq OWNED BY property_fields.id;


--
-- Name: property_groups; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE property_groups (
    id integer NOT NULL,
    name character varying(255),
    created_at timestamp without time zone,
    updated_at timestamp without time zone
);


--
-- Name: property_groups_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE property_groups_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: property_groups_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE property_groups_id_seq OWNED BY property_groups.id;


--
-- Name: property_values; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE property_values (
    id integer NOT NULL,
    alternative_id integer,
    field_id integer,
    value text,
    created_at timestamp without time zone,
    updated_at timestamp without time zone
);


--
-- Name: property_values_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE property_values_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: property_values_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE property_values_id_seq OWNED BY property_values.id;


--
-- Name: review_sentences; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE review_sentences (
    id integer NOT NULL,
    alternative_id integer,
    criterion_id integer,
    review_id integer,
    score double precision,
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    ta_id integer,
    prematch text,
    match text,
    postmatch text
);


--
-- Name: review_sentences_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE review_sentences_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: review_sentences_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE review_sentences_id_seq OWNED BY review_sentences.id;


--
-- Name: reviews; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE reviews (
    id integer NOT NULL,
    alternative_id integer,
    body text,
    date date,
    type character varying(255),
    agency_id integer
);


--
-- Name: reviews_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE reviews_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: reviews_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE reviews_id_seq OWNED BY reviews.id;


--
-- Name: schema_migrations; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE schema_migrations (
    version character varying(255) NOT NULL
);


--
-- Name: users; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE users (
    id integer NOT NULL,
    email character varying(255) DEFAULT ''::character varying NOT NULL,
    encrypted_password character varying(255) DEFAULT ''::character varying NOT NULL,
    reset_password_token character varying(255),
    reset_password_sent_at timestamp without time zone,
    remember_created_at timestamp without time zone,
    sign_in_count integer DEFAULT 0 NOT NULL,
    current_sign_in_at timestamp without time zone,
    last_sign_in_at timestamp without time zone,
    current_sign_in_ip character varying(255),
    last_sign_in_ip character varying(255),
    full_name character varying(255),
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    is_faked_email boolean DEFAULT false NOT NULL,
    favorite_alternative_ids integer[] DEFAULT '{}'::integer[] NOT NULL
);


--
-- Name: users_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE users_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: users_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE users_id_seq OWNED BY users.id;


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY alternative_sources ALTER COLUMN id SET DEFAULT nextval('alternative_sources_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY alternatives ALTER COLUMN id SET DEFAULT nextval('alternatives_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY criteria ALTER COLUMN id SET DEFAULT nextval('criteria_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY identities ALTER COLUMN id SET DEFAULT nextval('identities_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY invites ALTER COLUMN id SET DEFAULT nextval('invites_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY media ALTER COLUMN id SET DEFAULT nextval('media_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY property_fields ALTER COLUMN id SET DEFAULT nextval('property_fields_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY property_groups ALTER COLUMN id SET DEFAULT nextval('property_groups_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY property_values ALTER COLUMN id SET DEFAULT nextval('property_values_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY review_sentences ALTER COLUMN id SET DEFAULT nextval('review_sentences_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY reviews ALTER COLUMN id SET DEFAULT nextval('reviews_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY users ALTER COLUMN id SET DEFAULT nextval('users_id_seq'::regclass);


--
-- Name: alternative_sources_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
--

ALTER TABLE ONLY alternative_sources
    ADD CONSTRAINT alternative_sources_pkey PRIMARY KEY (id);


--
-- Name: alternatives_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
--

ALTER TABLE ONLY alternatives
    ADD CONSTRAINT alternatives_pkey PRIMARY KEY (id);


--
-- Name: criteria_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
--

ALTER TABLE ONLY criteria
    ADD CONSTRAINT criteria_pkey PRIMARY KEY (id);


--
-- Name: identities_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
--

ALTER TABLE ONLY identities
    ADD CONSTRAINT identities_pkey PRIMARY KEY (id);


--
-- Name: invites_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
--

ALTER TABLE ONLY invites
    ADD CONSTRAINT invites_pkey PRIMARY KEY (id);


--
-- Name: media_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
--

ALTER TABLE ONLY media
    ADD CONSTRAINT media_pkey PRIMARY KEY (id);


--
-- Name: property_fields_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
--

ALTER TABLE ONLY property_fields
    ADD CONSTRAINT property_fields_pkey PRIMARY KEY (id);


--
-- Name: property_groups_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
--

ALTER TABLE ONLY property_groups
    ADD CONSTRAINT property_groups_pkey PRIMARY KEY (id);


--
-- Name: property_values_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
--

ALTER TABLE ONLY property_values
    ADD CONSTRAINT property_values_pkey PRIMARY KEY (id);


--
-- Name: review_sentences_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
--

ALTER TABLE ONLY review_sentences
    ADD CONSTRAINT review_sentences_pkey PRIMARY KEY (id);


--
-- Name: reviews_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
--

ALTER TABLE ONLY reviews
    ADD CONSTRAINT reviews_pkey PRIMARY KEY (id);


--
-- Name: users_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
--

ALTER TABLE ONLY users
    ADD CONSTRAINT users_pkey PRIMARY KEY (id);


--
-- Name: index_alternative_sources_on_agency_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 
--

CREATE INDEX index_alternative_sources_on_agency_id ON alternative_sources USING btree (agency_id);


--
-- Name: index_alternative_sources_on_alternative_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 
--

CREATE INDEX index_alternative_sources_on_alternative_id ON alternative_sources USING btree (alternative_id);


--
-- Name: index_alternative_sources_on_realm_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 
--

CREATE INDEX index_alternative_sources_on_realm_id ON alternative_sources USING btree (realm_id);


--
-- Name: index_alternative_sources_on_type; Type: INDEX; Schema: public; Owner: -; Tablespace: 
--

CREATE INDEX index_alternative_sources_on_type ON alternative_sources USING btree (type);


--
-- Name: index_alternatives_criteria_on_alternative_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 
--

CREATE INDEX index_alternatives_criteria_on_alternative_id ON alternatives_criteria USING btree (alternative_id);


--
-- Name: index_alternatives_criteria_on_criterion_id_and_alternative_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 
--

CREATE UNIQUE INDEX index_alternatives_criteria_on_criterion_id_and_alternative_id ON alternatives_criteria USING btree (criterion_id, alternative_id);


--
-- Name: index_alternatives_on_ta_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 
--

CREATE INDEX index_alternatives_on_ta_id ON alternatives USING btree (ta_id);


--
-- Name: index_criteria_on_ancestry; Type: INDEX; Schema: public; Owner: -; Tablespace: 
--

CREATE INDEX index_criteria_on_ancestry ON criteria USING btree (ancestry);


--
-- Name: index_identities_on_user_id_and_provider; Type: INDEX; Schema: public; Owner: -; Tablespace: 
--

CREATE UNIQUE INDEX index_identities_on_user_id_and_provider ON identities USING btree (user_id, provider);


--
-- Name: index_media_on_alternative_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 
--

CREATE INDEX index_media_on_alternative_id ON media USING btree (alternative_id);


--
-- Name: index_property_fields_on_group_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 
--

CREATE INDEX index_property_fields_on_group_id ON property_fields USING btree (group_id);


--
-- Name: index_property_values_on_alternative_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 
--

CREATE INDEX index_property_values_on_alternative_id ON property_values USING btree (alternative_id);


--
-- Name: index_property_values_on_field_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 
--

CREATE INDEX index_property_values_on_field_id ON property_values USING btree (field_id);


--
-- Name: index_review_sentences_on_alternative_id_and_criterion_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 
--

CREATE INDEX index_review_sentences_on_alternative_id_and_criterion_id ON review_sentences USING btree (alternative_id, criterion_id);


--
-- Name: index_review_sentences_on_review_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 
--

CREATE INDEX index_review_sentences_on_review_id ON review_sentences USING btree (review_id);


--
-- Name: index_review_sentences_on_ta_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 
--

CREATE INDEX index_review_sentences_on_ta_id ON review_sentences USING btree (ta_id);


--
-- Name: index_reviews_on_alternative_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 
--

CREATE INDEX index_reviews_on_alternative_id ON reviews USING btree (alternative_id);


--
-- Name: index_reviews_on_type_and_agency_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 
--

CREATE INDEX index_reviews_on_type_and_agency_id ON reviews USING btree (type, agency_id);


--
-- Name: index_users_on_email; Type: INDEX; Schema: public; Owner: -; Tablespace: 
--

CREATE UNIQUE INDEX index_users_on_email ON users USING btree (email);


--
-- Name: index_users_on_reset_password_token; Type: INDEX; Schema: public; Owner: -; Tablespace: 
--

CREATE UNIQUE INDEX index_users_on_reset_password_token ON users USING btree (reset_password_token);


--
-- Name: unique_schema_migrations; Type: INDEX; Schema: public; Owner: -; Tablespace: 
--

CREATE UNIQUE INDEX unique_schema_migrations ON schema_migrations USING btree (version);


--
-- PostgreSQL database dump complete
--

SET search_path TO "$user",public;