CREATE TABLE users (
    id bigint IDENTITY(1,1) NOT NULL,
    nick character varying(64) NOT NULL,
    passw character varying(32) NOT NULL,
    lang VARCHAR(10) CHECK(lang IN ('ru', 'en', '__')) DEFAULT '__' NOT NULL,
    banned smallint DEFAULT (0) NOT NULL,
    lastmessage datetimeoffset,
    lastpm bigint DEFAULT (0) NOT NULL,
    lastphoto bigint DEFAULT (0) NOT NULL,
    karma smallint DEFAULT (0) NOT NULL,
    last_seen datetimeoffset,
    UNIQUE(nick),
    PRIMARY KEY (id)
);
CREATE TABLE user_services (
    user_id bigint NOT NULL,
    regid character varying(1024) NOT NULL,
    ts datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL, service_type varchar(255) not null default 'fcm',
    FOREIGN KEY (user_id) REFERENCES users(id),
    UNIQUE (regid)
);
CREATE TABLE auth (
    user_id bigint,
    protocol VARCHAR(10) CHECK (protocol IN ('xmpp', 'email', 'sms')) NOT NULL,
    account character varying(128) NOT NULL,
    authcode character varying(8) NOT NULL,
    ts datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE tags (
    tag_id INTEGER IDENTITY(1,1) PRIMARY KEY NOT NULL,
    synonym_id integer,
    name character varying(70),
    notop bit DEFAULT 0 NOT NULL,
    noindex bit DEFAULT 0 NOT NULL,
    stat_messages bigint DEFAULT (0) NOT NULL,
    stat_users smallint DEFAULT (0) NOT NULL,
    FOREIGN KEY (synonym_id) REFERENCES tags(tag_id)
);
CREATE TABLE bl_tags (
    user_id bigint NOT NULL,
    tag_id integer NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (tag_id) REFERENCES tags(tag_id)
);
CREATE TABLE bl_users (
    user_id bigint NOT NULL,
    bl_user_id bigint NOT NULL,
    ts datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL,
    PRIMARY KEY (user_id, bl_user_id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (bl_user_id) REFERENCES users(id)
);
CREATE TABLE emails (
    user_id bigint NOT NULL,
    email character varying(128) NOT NULL,
    subscr_hour smallint,
    FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE facebook (
    user_id bigint,
    fb_id numeric,
    loginhash character varying(36),
    access_token character varying(255),
    ts datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL,
    fb_name character varying(64),
    fb_link character varying(255) NOT NULL,
    crosspost bit DEFAULT 1 NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE reactions (
    like_id INTEGER PRIMARY KEY NOT NULL,
    description character varying(100) NOT NULL
);
CREATE TABLE favorites (
    user_id bigint NOT NULL,
    message_id bigint NOT NULL,
    ts datetimeoffset,
    like_id INTEGER DEFAULT 1 NOT NULL,
    user_uri character varying(255) NOT NULL DEFAULT '',
    FOREIGN KEY (like_id) REFERENCES reactions(like_id)
);
CREATE TABLE followers (
    user_id bigint,
    ts datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL,
    acct character varying(64) NOT NULL,
    PRIMARY KEY (user_id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE jids (
    user_id bigint,
    jid character varying(64) NOT NULL,
    active smallint DEFAULT 0 NOT NULL,
    loginhash character varying(36),
    ts datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE logins (
    user_id bigint NOT NULL,
    hash character varying(16) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE meon (
    id INTEGER NOT NULL,
    user_id bigint NOT NULL,
    link character varying(255) NOT NULL,
    name character varying(32) NOT NULL,
    ico smallint,
    FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE messages (
    message_id INTEGER IDENTITY(1,1) PRIMARY KEY NOT NULL,
    user_id bigint NOT NULL,
    lang varchar(max) DEFAULT '__' NOT NULL,
    ts datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL,
    replies smallint DEFAULT (0) NOT NULL,
    maxreplyid smallint DEFAULT (0) NOT NULL,
    privacy smallint DEFAULT (1) NOT NULL,
    readonly bit DEFAULT 0 NOT NULL,
    attach VARCHAR(10) CHECK (attach IN ('jpg', 'mp4', 'png')),
    place_id bigint,
    lat numeric(10,7),
    lon numeric(10,7),
    popular smallint DEFAULT (0) NOT NULL,
    hidden smallint DEFAULT (0) NOT NULL,
    likes smallint DEFAULT (0) NOT NULL,
    updated datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE messages_access (
    message_id INTEGER NOT NULL,
    user_id bigint NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (message_id) REFERENCES messages(message_id)
);
CREATE TABLE messages_properties (
    message_id INTEGER NOT NULL,
    reply_id smallint NOT NULL,
    property_key character varying(255) NOT NULL,
    property_value character varying(255) NOT NULL,
    UNIQUE (message_id, reply_id, property_key),
    FOREIGN KEY (message_id) REFERENCES messages(message_id)
);
CREATE TABLE messages_tags (
    message_id INTEGER NOT NULL,
    tag_id bigint NOT NULL,
    FOREIGN KEY (message_id) REFERENCES messages(message_id)
);
CREATE TABLE messages_txt (
    message_id INTEGER NOT NULL,
    repliesby varchar(max),
    txt varchar(max) NOT NULL,
    updated_at datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL,
    FOREIGN KEY (message_id) REFERENCES messages(message_id)
);
CREATE TABLE places (
    place_id INTEGER PRIMARY KEY NOT NULL,
    lat numeric(10,7) NOT NULL,
    lon numeric(10,7) NOT NULL,
    name character varying(64) NOT NULL,
    descr character varying(255),
    url character varying(128),
    user_id bigint NOT NULL,
    ts datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL
);
CREATE TABLE places_tags (
    place_id INTEGER NOT NULL,
    tag_id bigint NOT NULL,
    FOREIGN KEY (place_id) REFERENCES places(place_id)
);
CREATE TABLE pm (
    user_id bigint NOT NULL,
    user_id_to bigint NOT NULL,
    ts datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL,
    txt varchar(max) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (user_id_to) REFERENCES users(id)
);
CREATE TABLE replies (
    message_id integer NOT NULL,
    reply_id smallint NOT NULL,
    user_id bigint NOT NULL,
    replyto smallint DEFAULT (0) NOT NULL,
    ts datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL,
    attach VARCHAR(10) CHECK (attach IN ('jpg', 'mp4', 'png')),
    txt varchar(max) NOT NULL,
    updated_at datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL,
    user_uri character varying(255) DEFAULT NULL,
    reply_uri character varying(255) DEFAULT NULL,
    html smallint DEFAULT '0' NOT NULL,
    FOREIGN KEY (message_id) REFERENCES messages(message_id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE subscr_messages (
    message_id integer NOT NULL,
    suser_id bigint NOT NULL,
    last_read_rid smallint DEFAULT 0 NOT NULL,
    FOREIGN KEY (message_id) REFERENCES messages(message_id),
    FOREIGN KEY (suser_id) REFERENCES users(id)
);
CREATE TABLE subscr_tags (
    tag_id integer NOT NULL,
    suser_id bigint NOT NULL,
    FOREIGN KEY (tag_id) REFERENCES tags(tag_id),
    FOREIGN KEY (suser_id) REFERENCES users(id)
);
CREATE TABLE subscr_users (
    user_id bigint NOT NULL,
    suser_id bigint NOT NULL,
    ts datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (suser_id) REFERENCES users(id)
);
CREATE TABLE users_subscr (
    user_id bigint NOT NULL,
    cnt INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE tags_ignore (
    tag_id integer NOT NULL,
    FOREIGN KEY (tag_id) REFERENCES tags(tag_id)
);
CREATE TABLE telegram (
    user_id bigint,
    tg_id numeric NOT NULL,
    tg_name character varying(64) NOT NULL,
    ts datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL,
    loginhash character varying(36),
    FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE twitter (
    user_id bigint NOT NULL,
    access_token character varying(64) NOT NULL,
    access_token_secret character varying(64) NOT NULL,
    uname character varying(64) NOT NULL,
    ts datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL,
    crosspost bit DEFAULT 1 NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE usersinfo (
    user_id bigint NOT NULL,
    jid character varying(64),
    fullname character varying(64),
    country character varying(32),
    url character varying(128),
    gender character varying(32),
    bday character varying(32),
    descr varchar(max),
    UNIQUE(user_id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE vk (
    user_id bigint,
    vk_id numeric NULL,
    loginhash character varying(36),
    access_token character varying(128) NOT NULL,
    ts datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL,
    vk_name character varying(64) NOT NULL,
    vk_link character varying(64) NOT NULL,
    crosspost smallint DEFAULT (1) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE wl_users (
    user_id bigint NOT NULL,
    wl_user_id bigint NOT NULL,
    PRIMARY KEY (user_id, wl_user_id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (wl_user_id) REFERENCES users(id)
);
CREATE TABLE oauth2_registered_client (
    id varchar(100) NOT NULL,
    client_id varchar(100) NOT NULL,
    client_id_issued_at datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL,
    client_secret varchar(200) DEFAULT NULL,
    client_secret_expires_at datetimeoffset DEFAULT NULL,
    client_name varchar(200) NOT NULL,
    client_authentication_methods varchar(1000) NOT NULL,
    authorization_grant_types varchar(1000) NOT NULL,
    redirect_uris varchar(1000) DEFAULT NULL,
    scopes varchar(1000) NOT NULL,
    client_settings varchar(2000) NOT NULL,
    token_settings varchar(2000) NOT NULL,
    PRIMARY KEY (id)
);