--
-- PostgreSQL database dump
--

-- Dumped from database version 9.6.10
-- Dumped by pg_dump version 10.5

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;

--
-- Name: sp2; Type: SCHEMA; Schema: -; Owner: sp2app
--

CREATE SCHEMA sp2;


ALTER SCHEMA sp2 OWNER TO sp2app;

--
-- 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 default_tablespace = '';

SET default_with_oids = false;

--
-- Name: acls; Type: TABLE; Schema: sp2; Owner: sp2app
--

CREATE TABLE sp2.acls (
    "rowDT" timestamp with time zone DEFAULT now() NOT NULL,
    "aclID" character(36) DEFAULT ''::bpchar NOT NULL,
    "userID" character(36) DEFAULT ''::bpchar NOT NULL,
    "userStatus" bigint DEFAULT '10'::bigint NOT NULL,
    role bigint DEFAULT '0'::bigint NOT NULL,
    status bigint DEFAULT '0'::bigint NOT NULL,
    "expiredD" date DEFAULT '2100-01-01'::date,
    transport json,
    name character varying(200),
    surname character varying(200),
    phone numeric DEFAULT '0'::numeric,
    email character varying(320),
    "createDT" timestamp with time zone DEFAULT now() NOT NULL,
    "modifyDT" timestamp with time zone DEFAULT now() NOT NULL,
    "fromTS" numeric DEFAULT '0'::numeric,
    "syncTS" numeric DEFAULT '0'::numeric,
    "adminID" character(36) DEFAULT ''::bpchar NOT NULL
);


ALTER TABLE sp2.acls OWNER TO sp2app;

--
-- Name: devices; Type: TABLE; Schema: sp2; Owner: sp2app
--

CREATE TABLE sp2.devices (
    "rowDT" timestamp with time zone DEFAULT now() NOT NULL,
    "userID" character(36) DEFAULT ''::bpchar NOT NULL,
    "ID" character(36) DEFAULT ''::bpchar NOT NULL,
    phone character varying(20) DEFAULT ''::character varying NOT NULL,
    "devToken" character(100) DEFAULT ''::bpchar,
    "devPlatform" character varying(20) DEFAULT ''::character varying NOT NULL,
    "devOS" character varying(200),
    "devModel" character varying(200),
    "devName" character varying(200),
    "appName" character varying(50) DEFAULT '0'::character varying NOT NULL,
    "appVer" character varying(50) DEFAULT '0'::character varying NOT NULL,
    "appBuild" bigint DEFAULT '0'::bigint NOT NULL,
    "appRuns" integer DEFAULT 0,
    region character varying(20),
    lang character varying(6),
    tz character varying(100),
    geo json,
    "pushToken" character varying(128),
    "createDT" timestamp with time zone DEFAULT now() NOT NULL,
    "modifyDT" timestamp with time zone DEFAULT now() NOT NULL,
    "syncTS" numeric DEFAULT '0'::numeric
);


ALTER TABLE sp2.devices OWNER TO sp2app;

--
-- Name: entities; Type: TABLE; Schema: sp2; Owner: sp2app
--

CREATE TABLE sp2.entities (
    "rowDT" timestamp with time zone DEFAULT now() NOT NULL,
    "storeTS" numeric DEFAULT '0'::numeric,
    "entType" character varying(16) DEFAULT 'entry'::character varying NOT NULL,
    "ID" character varying(36) NOT NULL,
    "parentID" character varying(36),
    "aclID" character varying(36),
    "indexedInt" numeric DEFAULT '0'::numeric,
    "create" json,
    modify json,
    status smallint DEFAULT '10'::smallint NOT NULL,
    published character varying(128),
    content json,
    meta json,
    "sharedFieldsTM" json,
    "sharedFields" json,
    "sharedSyncTS" numeric DEFAULT '0'::numeric,
    "usersFieldsTM" json,
    "usersFields" json,
    "usersSyncTS" json,
    search text
);


ALTER TABLE sp2.entities OWNER TO sp2app;

--
-- Name: COLUMN entities."indexedInt"; Type: COMMENT; Schema: sp2; Owner: sp2app
--

COMMENT ON COLUMN sp2.entities."indexedInt" IS 'Custom indexed bigint field, role depends from entType';


--
-- Name: users; Type: TABLE; Schema: sp2; Owner: sp2app
--

CREATE TABLE sp2.users (
    "rowDT" timestamp with time zone DEFAULT now() NOT NULL,
    "ID" character varying(36) DEFAULT ''::character varying NOT NULL,
    status bigint DEFAULT '10'::bigint NOT NULL,
    phone character varying(20),
    email character varying(200),
    password character varying(100),
    region character varying(10) DEFAULT 'US'::character varying NOT NULL,
    lang character varying(10) DEFAULT 'en'::character varying NOT NULL,
    personal json DEFAULT '""'::json,
    subscription json,
    settings json,
    sources json,
    "createDT" timestamp with time zone DEFAULT now() NOT NULL,
    "modifyDT" timestamp with time zone DEFAULT now() NOT NULL,
    "syncTS" numeric DEFAULT '0'::numeric
);


ALTER TABLE sp2.users OWNER TO sp2app;

--
-- Name: acls acls_pkey; Type: CONSTRAINT; Schema: sp2; Owner: sp2app
--

ALTER TABLE ONLY sp2.acls
    ADD CONSTRAINT acls_pkey PRIMARY KEY ("aclID", "userID");


--
-- Name: devices devices_pkey; Type: CONSTRAINT; Schema: sp2; Owner: sp2app
--

ALTER TABLE ONLY sp2.devices
    ADD CONSTRAINT devices_pkey PRIMARY KEY ("ID");


--
-- Name: entities entities_pkey; Type: CONSTRAINT; Schema: sp2; Owner: sp2app
--

ALTER TABLE ONLY sp2.entities
    ADD CONSTRAINT entities_pkey PRIMARY KEY ("ID");


--
-- Name: users users_pkey; Type: CONSTRAINT; Schema: sp2; Owner: sp2app
--

ALTER TABLE ONLY sp2.users
    ADD CONSTRAINT users_pkey PRIMARY KEY ("ID");


--
-- Name: idx_16388_sync; Type: INDEX; Schema: sp2; Owner: sp2app
--

CREATE INDEX idx_16388_sync ON sp2.acls USING btree ("aclID", "userID", status, "expiredD", "syncTS");


--
-- Name: idx_16407_newdevice; Type: INDEX; Schema: sp2; Owner: sp2app
--

CREATE INDEX idx_16407_newdevice ON sp2.devices USING btree ("userID", "devPlatform", "devName");


--
-- Name: idx_16407_online; Type: INDEX; Schema: sp2; Owner: sp2app
--

CREATE INDEX idx_16407_online ON sp2.devices USING btree ("userID", "syncTS");


--
-- Name: idx_16426_search; Type: INDEX; Schema: sp2; Owner: sp2app
--

CREATE INDEX idx_16426_search ON sp2.entities USING gin (to_tsvector('simple'::regconfig, search));


--
-- Name: idx_16438_phone; Type: INDEX; Schema: sp2; Owner: sp2app
--

CREATE UNIQUE INDEX idx_16438_phone ON sp2.users USING btree (phone);


--
-- PostgreSQL database dump complete
--

