-- 015-vk

CREATE TABLE vk_channels (
  id          NUMBER(19) PRIMARY KEY REFERENCES channels (id),
  uuid        VARCHAR2(36 CHAR),
  group_id    NUMBER(19),
  name        VARCHAR2(1024 CHAR),
  deleted     NUMBER(1)                NOT NULL,

  created_at  TIMESTAMP WITH TIME ZONE NOT NULL,
  updated_at  TIMESTAMP WITH TIME ZONE NOT NULL,
  deleted_at  TIMESTAMP WITH TIME ZONE,
  restored_at TIMESTAMP WITH TIME ZONE
);

CREATE UNIQUE INDEX vk_channels_uuid_uniq
  ON vk_channels (
    CASE WHEN uuid IS NOT NULL
      THEN uuid END
  );

CREATE UNIQUE INDEX vk_channels_group_id_uniq
  ON vk_channels (
    CASE WHEN deleted = 0 AND group_id IS NOT NULL
      THEN group_id END
  );

-- vk-configs

CREATE TABLE vk_configs (
  channel_id        NUMBER(19) PRIMARY KEY REFERENCES channels (id),
  secret_key        VARCHAR2(255 CHAR) NOT NULL,
  access_token      VARCHAR2(255 CHAR),
  confirmation_code VARCHAR2(255 CHAR)
);

-- vk-users

CREATE TABLE vk_users (
  id          NUMBER(19) PRIMARY KEY,
  first_name  VARCHAR2(255 CHAR),
  last_name   VARCHAR2(255 CHAR),
  screen_name VARCHAR2(255 CHAR),
  sex         NUMBER(10)               NOT NULL,
  photo       VARCHAR2(255 CHAR),

  created_at  TIMESTAMP WITH TIME ZONE NOT NULL,
  updated_at  TIMESTAMP WITH TIME ZONE NOT NULL
);

-- vk-chats

CREATE TABLE vk_chats (
  id         NUMBER(19) PRIMARY KEY,
  chat_id    NUMBER(19)               NOT NULL,
  user_id    NUMBER(19)               NOT NULL REFERENCES vk_users (id),
  created_at TIMESTAMP WITH TIME ZONE NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE SEQUENCE vk_chats_id_seq;

CREATE UNIQUE INDEX vk_chats_chat_uniq
  ON vk_chats (chat_id);
