-- H2 2.1.214;
CREATE MEMORY TABLE "PUBLIC"."JIDS"(
    "USER_ID" INTEGER DEFAULT NULL,
    "JID" VARCHAR_IGNORECASE(255) NOT NULL,
    "ACTIVE" TINYINT DEFAULT '1' NOT NULL,
    "LOGINHASH" CHARACTER(36) DEFAULT NULL,
    "TS" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);
CREATE MEMORY TABLE "PUBLIC"."BL_TAGS"(
    "USER_ID" INTEGER NOT NULL,
    "TAG_ID" INTEGER NOT NULL
);
CREATE MEMORY TABLE "PUBLIC"."BL_USERS"(
    "USER_ID" INTEGER NOT NULL,
    "BL_USER_ID" INTEGER NOT NULL,
    "TS" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);
ALTER TABLE "PUBLIC"."BL_USERS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_2" PRIMARY KEY("USER_ID", "BL_USER_ID");
CREATE MEMORY TABLE "PUBLIC"."REACTIONS"(
    "LIKE_ID" INTEGER NOT NULL,
    "DESCRIPTION" CHARACTER VARYING(100) NOT NULL
);
CREATE MEMORY TABLE "PUBLIC"."FAVORITES"(
    "USER_ID" INTEGER NOT NULL,
    "MESSAGE_ID" INTEGER NOT NULL,
    "TS" TIMESTAMP NOT NULL,
    "LIKE_ID" INTEGER,
    "USER_URI" CHARACTER VARYING(255) DEFAULT '' NOT NULL
);
CREATE MEMORY TABLE "PUBLIC"."AUTH"(
    "USER_ID" INTEGER,
    "PROTOCOL" ENUM('xmpp', 'email', 'sms') NOT NULL,
    "ACCOUNT" CHARACTER(64) NOT NULL,
    "AUTHCODE" CHARACTER(8) NOT NULL,
    "TS" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL
);
CREATE MEMORY TABLE "PUBLIC"."MESSAGES_PROPERTIES"(
    "MESSAGE_ID" INTEGER NOT NULL,
    "REPLY_ID" SMALLINT NOT NULL,
    "PROPERTY_KEY" CHARACTER VARYING(255) NOT NULL,
    "PROPERTY_VALUE" CHARACTER VARYING NOT NULL
);
CREATE MEMORY TABLE "PUBLIC"."MEON"(
    "ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) DEFAULT ON NULL NOT NULL,
    "USER_ID" INTEGER NOT NULL,
    "LINK" CHARACTER(255) NOT NULL,
    "NAME" CHARACTER(32) NOT NULL,
    "ICO" SMALLINT DEFAULT NULL
);
ALTER TABLE "PUBLIC"."MEON" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_24" PRIMARY KEY("ID");
CREATE MEMORY TABLE "PUBLIC"."MESSAGES_ACCESS"(
    "MESSAGE_ID" INTEGER NOT NULL,
    "USER_ID" INTEGER NOT NULL
);
CREATE MEMORY TABLE "PUBLIC"."MESSAGES_TAGS"(
    "MESSAGE_ID" INTEGER NOT NULL,
    "TAG_ID" INTEGER NOT NULL
);
CREATE MEMORY TABLE "PUBLIC"."USERS"(
    "ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0 RESTART WITH 6 MINVALUE 0) DEFAULT ON NULL NOT NULL,
    "NICK" VARCHAR_IGNORECASE(64) NOT NULL,
    "PASSW" CHARACTER VARYING(32) NOT NULL,
    "LANG" ENUM('en', 'ru', 'fr', 'fa', '__') DEFAULT '__' NOT NULL,
    "BANNED" TINYINT DEFAULT '0' NOT NULL,
    "LASTMESSAGE" TIMESTAMP(6),
    "LASTPM" INTEGER DEFAULT '0' NOT NULL,
    "LASTPHOTO" INTEGER DEFAULT '0' NOT NULL,
    "KARMA" SMALLINT DEFAULT '0' NOT NULL,
    "PREMIUM" SMALLINT DEFAULT '0' NOT NULL,
    "LAST_SEEN" TIMESTAMP(6)
);
ALTER TABLE "PUBLIC"."USERS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_4" PRIMARY KEY("ID");
CREATE MEMORY TABLE "PUBLIC"."PM"(
    "USER_ID" INTEGER NOT NULL,
    "USER_ID_TO" INTEGER NOT NULL,
    "TS" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL,
    "TXT" CHARACTER VARYING NOT NULL
);
CREATE MEMORY TABLE "PUBLIC"."FOLLOWERS"(
    "USER_ID" INTEGER DEFAULT NULL,
    "TS" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    "ACCT" CHARACTER VARYING(64) NOT NULL
);
CREATE MEMORY TABLE "PUBLIC"."SUBSCR_USERS"(
    "USER_ID" INTEGER NOT NULL,
    "SUSER_ID" INTEGER NOT NULL,
    "TS" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);
CREATE MEMORY TABLE "PUBLIC"."MESSENGER"(
    "USER_ID" INTEGER DEFAULT NULL,
    "SENDER_ID" BIGINT NOT NULL,
    "DISPLAY_NAME" CHARACTER(64) NOT NULL,
    "TS" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    "LOGINHASH" CHARACTER(36) DEFAULT NULL
);
CREATE MEMORY TABLE "PUBLIC"."PLACES"(
    "PLACE_ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) DEFAULT ON NULL NOT NULL,
    "LAT" DECIMAL(10, 7) NOT NULL,
    "LON" DECIMAL(10, 7) NOT NULL,
    "NAME" CHARACTER(64) NOT NULL,
    "DESCR" CHARACTER(255) DEFAULT NULL,
    "URL" CHARACTER(128) DEFAULT NULL,
    "USER_ID" INTEGER NOT NULL,
    "TS" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);
ALTER TABLE "PUBLIC"."PLACES" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_8C" PRIMARY KEY("PLACE_ID");
CREATE MEMORY TABLE "PUBLIC"."PLACES_TAGS"(
    "PLACE_ID" INTEGER NOT NULL,
    "TAG_ID" INTEGER NOT NULL
);
CREATE MEMORY TABLE "PUBLIC"."REPLIES"(
    "MESSAGE_ID" INTEGER NOT NULL,
    "REPLY_ID" SMALLINT NOT NULL,
    "USER_ID" INTEGER NOT NULL,
    "REPLYTO" SMALLINT DEFAULT '0' NOT NULL,
    "TS" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL,
    "ATTACH" ENUM('jpg', 'mp4', 'png') DEFAULT NULL,
    "TXT" CHARACTER VARYING NOT NULL,
    "UPDATED_AT" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL,
    "USER_URI" CHARACTER VARYING(255) DEFAULT NULL,
    "REPLY_URI" CHARACTER VARYING(255) DEFAULT NULL,
    "HTML" TINYINT DEFAULT '0' NOT NULL
);
CREATE INDEX "PUBLIC"."REPLY_URI_INDEX" ON "PUBLIC"."REPLIES"("REPLY_URI" NULLS FIRST);
CREATE MEMORY TABLE "PUBLIC"."MESSAGES"(
    "MESSAGE_ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) DEFAULT ON NULL NOT NULL,
    "USER_ID" INTEGER NOT NULL,
    "LANG" ENUM('en', 'ru', 'fr', 'fa', '__') DEFAULT '__' NOT NULL,
    "TS" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    "REPLIES" SMALLINT DEFAULT '0' NOT NULL,
    "MAXREPLYID" SMALLINT DEFAULT '0' NOT NULL,
    "PRIVACY" TINYINT DEFAULT '1' NOT NULL,
    "READONLY" TINYINT DEFAULT '0' NOT NULL,
    "ATTACH" ENUM('jpg', 'mp4', 'png') DEFAULT NULL,
    "PLACE_ID" INTEGER DEFAULT NULL,
    "LAT" DECIMAL(10, 7) DEFAULT NULL,
    "LON" DECIMAL(10, 7) DEFAULT NULL,
    "POPULAR" TINYINT DEFAULT '0' NOT NULL,
    "HIDDEN" TINYINT DEFAULT '0' NOT NULL,
    "LIKES" SMALLINT DEFAULT '0' NOT NULL,
    "UPDATED" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    "REPLIESBY" CHARACTER VARYING(96) DEFAULT NULL,
    "TXT" CHARACTER VARYING NOT NULL,
    "UPDATED_AT" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL
);
ALTER TABLE "PUBLIC"."MESSAGES" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_13" PRIMARY KEY("MESSAGE_ID");
CREATE MEMORY TABLE "PUBLIC"."VK"(
    "USER_ID" INTEGER DEFAULT NULL,
    "VK_ID" BIGINT NOT NULL,
    "LOGINHASH" CHARACTER(36) DEFAULT NULL,
    "ACCESS_TOKEN" CHARACTER VARYING(255),
    "TS" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    "VK_NAME" CHARACTER(64) NOT NULL,
    "VK_LINK" CHARACTER(64) NOT NULL,
    "CROSSPOST" SMALLINT DEFAULT '1' NOT NULL
);
CREATE MEMORY TABLE "PUBLIC"."SUBSCR_MESSAGES"(
    "MESSAGE_ID" INTEGER NOT NULL,
    "SUSER_ID" INTEGER NOT NULL,
    "LAST_READ_RID" SMALLINT DEFAULT '0' NOT NULL
);
CREATE MEMORY TABLE "PUBLIC"."SUBSCR_TAGS"(
    "TAG_ID" INTEGER NOT NULL,
    "SUSER_ID" INTEGER NOT NULL
);
CREATE MEMORY TABLE "PUBLIC"."OAUTH2_REGISTERED_CLIENT"(
    "ID" CHARACTER VARYING(100) NOT NULL,
    "CLIENT_ID" CHARACTER VARYING(100) NOT NULL,
    "CLIENT_ID_ISSUED_AT" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    "CLIENT_SECRET" CHARACTER VARYING(200) DEFAULT NULL,
    "CLIENT_SECRET_EXPIRES_AT" TIMESTAMP DEFAULT NULL,
    "CLIENT_NAME" CHARACTER VARYING(200) NOT NULL,
    "CLIENT_AUTHENTICATION_METHODS" CHARACTER VARYING(1000) NOT NULL,
    "AUTHORIZATION_GRANT_TYPES" CHARACTER VARYING(1000) NOT NULL,
    "REDIRECT_URIS" CHARACTER VARYING(1000) DEFAULT NULL,
    "POST_LOGOUT_REDIRECT_URIS" CHARACTER VARYING(1000) DEFAULT NULL,
    "SCOPES" CHARACTER VARYING(1000) NOT NULL,
    "CLIENT_SETTINGS" CHARACTER VARYING(2000) NOT NULL,
    "TOKEN_SETTINGS" CHARACTER VARYING(2000) NOT NULL
);
ALTER TABLE "PUBLIC"."OAUTH2_REGISTERED_CLIENT" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_83" PRIMARY KEY("ID");
CREATE MEMORY TABLE "PUBLIC"."TAGS"(
    "TAG_ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1 RESTART WITH 3) DEFAULT ON NULL NOT NULL,
    "SYNONYM_ID" INTEGER DEFAULT NULL,
    "NAME" VARCHAR_IGNORECASE(70) DEFAULT NULL,
    "NOTOP" SMALLINT DEFAULT '0' NOT NULL,
    "NOINDEX" SMALLINT DEFAULT '0' NOT NULL,
    "STAT_MESSAGES" INTEGER DEFAULT '0' NOT NULL,
    "STAT_USERS" SMALLINT DEFAULT '0' NOT NULL
);
ALTER TABLE "PUBLIC"."TAGS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_27" PRIMARY KEY("TAG_ID");
CREATE MEMORY TABLE "PUBLIC"."TAGS_IGNORE"(
    "TAG_ID" INTEGER NOT NULL
);
CREATE MEMORY TABLE "PUBLIC"."TELEGRAM"(
    "USER_ID" INTEGER DEFAULT NULL,
    "TG_ID" BIGINT NOT NULL,
    "TG_NAME" CHARACTER(64) DEFAULT NULL,
    "TS" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    "LOGINHASH" CHARACTER(36) DEFAULT NULL
);
CREATE MEMORY TABLE "PUBLIC"."FACEBOOK"(
    "USER_ID" INTEGER DEFAULT NULL,
    "FB_ID" BIGINT,
    "LOGINHASH" CHARACTER(36) DEFAULT NULL,
    "ACCESS_TOKEN" CHARACTER VARYING(255),
    "TS" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    "FB_NAME" CHARACTER(64),
    "FB_LINK" CHARACTER(255),
    "CROSSPOST" TINYINT DEFAULT '1' NOT NULL
);
CREATE MEMORY TABLE "PUBLIC"."TWITTER"(
    "USER_ID" INTEGER NOT NULL,
    "ACCESS_TOKEN" CHARACTER VARYING(255),
    "REFRESH_TOKEN" CHARACTER VARYING(255) NOT NULL DEFAULT '',
    "ACCESS_TOKEN_SECRET" CHARACTER(64) NOT NULL,
    "UNAME" CHARACTER(64) NOT NULL,
    "TS" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    "CROSSPOST" TINYINT DEFAULT '1' NOT NULL
);
ALTER TABLE "PUBLIC"."TWITTER" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_F" PRIMARY KEY("USER_ID");
CREATE MEMORY TABLE "PUBLIC"."USERSINFO"(
    "USER_ID" INTEGER NOT NULL,
    "JID" CHARACTER(32) DEFAULT NULL,
    "FULLNAME" CHARACTER VARYING(32) DEFAULT NULL,
    "COUNTRY" CHARACTER VARYING(32) DEFAULT NULL,
    "URL" CHARACTER(64) DEFAULT NULL,
    "GENDER" CHARACTER(32) DEFAULT NULL,
    "BDAY" CHARACTER(10) DEFAULT NULL,
    "DESCR" CHARACTER VARYING(255) DEFAULT NULL
);
ALTER TABLE "PUBLIC"."USERSINFO" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_BB6" PRIMARY KEY("USER_ID");
CREATE MEMORY TABLE "PUBLIC"."EMAILS"(
    "USER_ID" INTEGER NOT NULL,
    "EMAIL" VARCHAR_IGNORECASE(255) NOT NULL,
    "SUBSCR_HOUR" TINYINT DEFAULT NULL
);
ALTER TABLE "PUBLIC"."EMAILS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_7A" PRIMARY KEY("EMAIL");
CREATE MEMORY TABLE "PUBLIC"."LOGINS"(
    "USER_ID" INTEGER NOT NULL,
    "HASH" CHARACTER(16) NOT NULL
);
CREATE MEMORY TABLE "PUBLIC"."WL_USERS"(
    "USER_ID" INTEGER NOT NULL,
    "WL_USER_ID" INTEGER NOT NULL
);
ALTER TABLE "PUBLIC"."WL_USERS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_A8" PRIMARY KEY("USER_ID", "WL_USER_ID");
CREATE MEMORY TABLE "PUBLIC"."USER_SERVICES"(
    "USER_ID" INTEGER NOT NULL,
    "REGID" CHARACTER VARYING(1024) NOT NULL,
    "TS" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    "SERVICE_TYPE" CHARACTER VARYING(255) DEFAULT 'fcm' NOT NULL
);
ALTER TABLE "PUBLIC"."LOGINS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_86" UNIQUE("USER_ID");
ALTER TABLE "PUBLIC"."REACTIONS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_6" UNIQUE("LIKE_ID");
ALTER TABLE "PUBLIC"."SUBSCR_TAGS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_B" UNIQUE("TAG_ID", "SUSER_ID");
ALTER TABLE "PUBLIC"."FOLLOWERS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_D3" UNIQUE("USER_ID", "ACCT");
ALTER TABLE "PUBLIC"."SUBSCR_USERS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_C" UNIQUE("USER_ID", "SUSER_ID");
ALTER TABLE "PUBLIC"."MESSAGES_PROPERTIES" ADD CONSTRAINT "PUBLIC"."MESSAGE_KEY" UNIQUE("MESSAGE_ID", "REPLY_ID", "PROPERTY_KEY");
ALTER TABLE "PUBLIC"."USERS" ADD CONSTRAINT "PUBLIC"."NICK" UNIQUE("NICK");
ALTER TABLE "PUBLIC"."MESSAGES_TAGS" ADD CONSTRAINT "PUBLIC"."MESSAGE_ID_2" UNIQUE("MESSAGE_ID", "TAG_ID");
ALTER TABLE "PUBLIC"."SUBSCR_MESSAGES" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_6D" UNIQUE("MESSAGE_ID", "SUSER_ID");
ALTER TABLE "PUBLIC"."JIDS" ADD CONSTRAINT "PUBLIC"."JID" UNIQUE("JID");
ALTER TABLE "PUBLIC"."VK" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_A" FOREIGN KEY("USER_ID") REFERENCES "PUBLIC"."USERS"("ID") NOCHECK;
ALTER TABLE "PUBLIC"."FAVORITES" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_3" FOREIGN KEY("LIKE_ID") REFERENCES "PUBLIC"."REACTIONS"("LIKE_ID") NOCHECK;
ALTER TABLE "PUBLIC"."MESSAGES" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_131A" FOREIGN KEY("USER_ID") REFERENCES "PUBLIC"."USERS"("ID") NOCHECK;
ALTER TABLE "PUBLIC"."FOLLOWERS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_D" FOREIGN KEY("USER_ID") REFERENCES "PUBLIC"."USERS"("ID") NOCHECK;
ALTER TABLE "PUBLIC"."JIDS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_22" FOREIGN KEY("USER_ID") REFERENCES "PUBLIC"."USERS"("ID") NOCHECK;
ALTER TABLE "PUBLIC"."EMAILS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_7A1" FOREIGN KEY("USER_ID") REFERENCES "PUBLIC"."USERS"("ID") NOCHECK;