SET statement_timeout = 0;

SET lock_timeout = 0;

SET idle_in_transaction_session_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = on;

SELECT pg_catalog.set_config('search_path', '', false);

SET check_function_bodies = false;

SET client_min_messages = warning;

SET row_security = off;

DROP DATABASE IF EXISTS jcrm;

CREATE DATABASE jcrm WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'ru_RU.UTF-8' LC_CTYPE = 'ru_RU.UTF-8';

ALTER DATABASE jcrm OWNER TO postgres;

\connect jcrm

SET statement_timeout = 0;

SET lock_timeout = 0;

SET idle_in_transaction_session_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = on;

SELECT pg_catalog.set_config('search_path', '', false);

SET check_function_bodies = false;

SET client_min_messages = warning;

SET row_security = off;

CREATE SCHEMA "jcrm-dictionary-db";

ALTER SCHEMA "jcrm-dictionary-db" OWNER TO postgres;

CREATE SCHEMA "jcrm-user-db";

ALTER SCHEMA "jcrm-user-db" OWNER TO postgres;

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

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

SET default_tablespace = '';

SET default_with_oids = false;

CREATE TABLE "jcrm-dictionary-db".city (

id bigint NOT NULL,

name character varying(45) NOT NULL,

country_id bigint NOT NULL

);

ALTER TABLE "jcrm-dictionary-db".city OWNER TO postgres;

CREATE SEQUENCE "jcrm-dictionary-db".city_id_seq

START WITH 1

INCREMENT BY 1

NO MINVALUE

NO MAXVALUE

CACHE 1;

ALTER TABLE "jcrm-dictionary-db".city_id_seq OWNER TO postgres;

ALTER SEQUENCE "jcrm-dictionary-db".city_id_seq OWNED BY "jcrm-dictionary-db".city.id;

CREATE TABLE "jcrm-dictionary-db".country (

id bigint NOT NULL,

name character varying(45) NOT NULL,

flag_code character varying(25) NOT NULL,

phone_code character varying(10) NOT NULL

);

ALTER TABLE "jcrm-dictionary-db".country OWNER TO postgres;

CREATE SEQUENCE "jcrm-dictionary-db".country_id_seq

START WITH 1

INCREMENT BY 1

NO MINVALUE

NO MAXVALUE

CACHE 1;

ALTER TABLE "jcrm-dictionary-db".country_id_seq OWNER TO postgres;

ALTER SEQUENCE "jcrm-dictionary-db".country_id_seq OWNED BY "jcrm-dictionary-db".country.id;

CREATE TABLE "jcrm-dictionary-db".industry (

id bigint NOT NULL,

name character varying(45) NOT NULL

);

ALTER TABLE "jcrm-dictionary-db".industry OWNER TO postgres;

CREATE SEQUENCE "jcrm-dictionary-db".industry_id_seq

START WITH 1

INCREMENT BY 1

NO MINVALUE

NO MAXVALUE

CACHE 1;

ALTER TABLE "jcrm-dictionary-db".industry_id_seq OWNER TO postgres;

ALTER SEQUENCE "jcrm-dictionary-db".industry_id_seq OWNED BY "jcrm-dictionary-db".industry.id;

CREATE TABLE "jcrm-user-db".email_confirmation (

id bigint NOT NULL,

code character varying(15) NOT NULL,

email character varying(100) NOT NULL,

expiration timestamp without time zone NOT NULL

);

ALTER TABLE "jcrm-user-db".email_confirmation OWNER TO postgres;

CREATE SEQUENCE "jcrm-user-db".email_confirmation_id_seq

START WITH 1

INCREMENT BY 1

NO MINVALUE

NO MAXVALUE

CACHE 1;

ALTER TABLE "jcrm-user-db".email_confirmation_id_seq OWNER TO postgres;

ALTER SEQUENCE "jcrm-user-db".email_confirmation_id_seq OWNED BY "jcrm-user-db".email_confirmation.id;

CREATE TABLE "jcrm-user-db".organization (

id bigint NOT NULL,

name character varying(255) NOT NULL,

country_id bigint NOT NULL,

version integer DEFAULT 0 NOT NULL,

zip_code character varying(50),

address character varying(255),

email character varying(50),

phone_number character varying(50),

city_id bigint,

industries jsonb

);

ALTER TABLE "jcrm-user-db".organization OWNER TO postgres;

CREATE SEQUENCE "jcrm-user-db".organization_id_seq

START WITH 1

INCREMENT BY 1

NO MINVALUE

NO MAXVALUE

CACHE 1;

ALTER TABLE "jcrm-user-db".organization_id_seq OWNER TO postgres;

ALTER SEQUENCE "jcrm-user-db".organization_id_seq OWNED BY "jcrm-user-db".organization.id;

CREATE TABLE "jcrm-user-db"."user" (

id bigint NOT NULL,

name character varying(100) NOT NULL,

password bytea NOT NULL,

salt bytea NOT NULL,

organization_id bigint,

roles jsonb,

version integer DEFAULT 0 NOT NULL,

first_name character varying(45),

second_name character varying(45),

third_name character varying(45),

phone_number character varying(45),

groups jsonb,

email_confirmed boolean DEFAULT false NOT NULL

);

ALTER TABLE "jcrm-user-db"."user" OWNER TO postgres;

CREATE TABLE "jcrm-user-db".user_group (

id bigint NOT NULL,

name character varying(45) NOT NULL,

organization_id bigint NOT NULL,

roles jsonb,

version integer DEFAULT 0 NOT NULL

);

ALTER TABLE "jcrm-user-db".user_group OWNER TO postgres;

CREATE SEQUENCE "jcrm-user-db".user_group_id_seq

START WITH 1

INCREMENT BY 1

NO MINVALUE

NO MAXVALUE

CACHE 1;

ALTER TABLE "jcrm-user-db".user_group_id_seq OWNER TO postgres;

ALTER SEQUENCE "jcrm-user-db".user_group_id_seq OWNED BY "jcrm-user-db".user_group.id;

CREATE SEQUENCE "jcrm-user-db".user_id_seq

START WITH 1

INCREMENT BY 1

NO MINVALUE

NO MAXVALUE

CACHE 1;

ALTER TABLE "jcrm-user-db".user_id_seq OWNER TO postgres;

ALTER SEQUENCE "jcrm-user-db".user_id_seq OWNED BY "jcrm-user-db"."user".id;

CREATE TABLE public.flyway_schema_history (

installed_rank integer NOT NULL,

version character varying(50),

description character varying(200) NOT NULL,

type character varying(20) NOT NULL,

script character varying(1000) NOT NULL,

checksum integer,

installed_by character varying(100) NOT NULL,

installed_on timestamp without time zone DEFAULT now() NOT NULL,

execution_time integer NOT NULL,

success boolean NOT NULL

);

ALTER TABLE public.flyway_schema_history OWNER TO postgres;

ALTER TABLE ONLY "jcrm-dictionary-db".city ALTER COLUMN id SET DEFAULT nextval('"jcrm-dictionary-db".city_id_seq'::regclass);

ALTER TABLE ONLY "jcrm-dictionary-db".country ALTER COLUMN id SET DEFAULT nextval('"jcrm-dictionary-db".country_id_seq'::regclass);

ALTER TABLE ONLY "jcrm-dictionary-db".industry ALTER COLUMN id SET DEFAULT nextval('"jcrm-dictionary-db".industry_id_seq'::regclass);

ALTER TABLE ONLY "jcrm-user-db".email_confirmation ALTER COLUMN id SET DEFAULT nextval('"jcrm-user-db".email_confirmation_id_seq'::regclass);

ALTER TABLE ONLY "jcrm-user-db".organization ALTER COLUMN id SET DEFAULT nextval('"jcrm-user-db".organization_id_seq'::regclass);

ALTER TABLE ONLY "jcrm-user-db"."user" ALTER COLUMN id SET DEFAULT nextval('"jcrm-user-db".user_id_seq'::regclass);

ALTER TABLE ONLY "jcrm-user-db".user_group ALTER COLUMN id SET DEFAULT nextval('"jcrm-user-db".user_group_id_seq'::regclass);

ALTER TABLE ONLY "jcrm-dictionary-db".city

ADD CONSTRAINT city_pkey PRIMARY KEY (id);

ALTER TABLE ONLY "jcrm-dictionary-db".country

ADD CONSTRAINT country_pkey PRIMARY KEY (id);

ALTER TABLE ONLY "jcrm-dictionary-db".industry

ADD CONSTRAINT industry_pkey PRIMARY KEY (id);

ALTER TABLE ONLY "jcrm-user-db".email_confirmation

ADD CONSTRAINT email_confirmation_pk PRIMARY KEY (id);

ALTER TABLE ONLY "jcrm-user-db".organization

ADD CONSTRAINT organization_pkey PRIMARY KEY (id);

ALTER TABLE ONLY "jcrm-user-db".user_group

ADD CONSTRAINT user_group_pkey PRIMARY KEY (id);

ALTER TABLE ONLY "jcrm-user-db"."user"

ADD CONSTRAINT user_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.flyway_schema_history

ADD CONSTRAINT flyway_schema_history_pk PRIMARY KEY (installed_rank);

CREATE UNIQUE INDEX city_name_and_country_uindex ON "jcrm-dictionary-db".city USING btree (name, country_id);

CREATE INDEX country__name_index ON "jcrm-dictionary-db".country USING btree (name);

CREATE UNIQUE INDEX industry_name_uindex ON "jcrm-dictionary-db".industry USING btree (name);

CREATE INDEX email_confirmation_code_and_email_index ON "jcrm-user-db".email_confirmation USING btree (code, email);

CREATE UNIQUE INDEX organization_name_uindex ON "jcrm-user-db".organization USING btree (name);

CREATE UNIQUE INDEX user_group_name_and_org_uindex ON "jcrm-user-db".user_group USING btree (name, organization_id);

CREATE UNIQUE INDEX user_name_uindex ON "jcrm-user-db"."user" USING btree (name);

CREATE INDEX flyway_schema_history_s_idx ON public.flyway_schema_history USING btree (success);

ALTER TABLE ONLY "jcrm-dictionary-db".city

ADD CONSTRAINT city_to_country_fk FOREIGN KEY (country_id) REFERENCES "jcrm-dictionary-db".country(id);

ALTER TABLE ONLY "jcrm-user-db".user_group

ADD CONSTRAINT user_group_to_org_fk FOREIGN KEY (organization_id) REFERENCES "jcrm-user-db".organization(id);

ALTER TABLE ONLY "jcrm-user-db"."user"

ADD CONSTRAINT user_to_org_fk FOREIGN KEY (organization_id) REFERENCES "jcrm-user-db".organization(id);

GRANT ALL ON SCHEMA public TO PUBLIC;