-- 014-telegram

CREATE TABLE telegram_channels (
  id          NUMBER(19) PRIMARY KEY,
  uuid        VARCHAR2(36 CHAR),
  deleted     NUMBER(1)                NOT NULL,

  user_id     NUMBER(19),
  user_name   VARCHAR2(1024 CHAR),

  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 telegram_channels_uuid_uniq
  ON telegram_channels (
    CASE WHEN uuid IS NOT NULL
      THEN uuid END
  );

CREATE UNIQUE INDEX telegram_channels_user_uniq
  ON telegram_channels (
    CASE WHEN deleted = 0 AND user_id IS NOT NULL
      THEN user_id END
  );

-- telegram-configs

CREATE TABLE telegram_configs (
  channel_id NUMBER(19) PRIMARY KEY  REFERENCES channels (id),
  bot_token  VARCHAR2(255)
);

-- telegram-users

CREATE TABLE telegram_users (
  id         NUMBER(19) PRIMARY KEY,
  user_name  VARCHAR2(255 CHAR),
  first_name VARCHAR2(255 CHAR),
  last_name  VARCHAR2(255 CHAR),
  created_at TIMESTAMP WITH TIME ZONE NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL
);

-- telegram-chats

CREATE TABLE telegram_chats (
  id               NUMBER(19) PRIMARY KEY,
  chat_id          NUMBER(19)               NOT NULL,
  telegram_chat_id NUMBER(19)               NOT NULL,

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

CREATE SEQUENCE telegram_chats_id_seq;

CREATE UNIQUE INDEX telegram_chats_chat_uniq
  ON telegram_chats (chat_id);
