--- telegram

-- telegram-channels

ALTER TABLE telegram_channels
  ADD
  uuid VARCHAR(36),
  user_id BIGINT,
  user_name NVARCHAR(1024),
  deleted_at DATETIMEOFFSET,
  restored_at DATETIMEOFFSET;

CREATE UNIQUE INDEX telegram_channels_uuid_uniq
  ON telegram_channels (uuid)
  WHERE uuid IS NOT NULL;

CREATE UNIQUE INDEX telegram_channels_nondeleted_user_id_uniq
  ON telegram_channels (user_id)
  WHERE user_id IS NOT NULL AND deleted = 0;

-- telegram-channels: nullable bot_token

DROP INDEX telegram_channels.tg_channels_bot_token_uniq;

ALTER TABLE telegram_channels
  ALTER COLUMN bot_token NVARCHAR(255) NULL;

-- telegram-channels: drop last_sent_message_id

DECLARE @ObjectName NVARCHAR(100)
SELECT @ObjectName = OBJECT_NAME([default_object_id])
FROM SYS.COLUMNS
WHERE [object_id] = OBJECT_ID('telegram_channels') AND [name] = 'last_sent_message_id'
EXEC ('ALTER TABLE telegram_channels DROP CONSTRAINT ' + @ObjectName);

ALTER TABLE telegram_channels
  DROP COLUMN last_sent_message_id;

-- telegram-users

ALTER TABLE telegram_users
  ADD
  created_at DATETIMEOFFSET,
  updated_at DATETIMEOFFSET;

UPDATE telegram_users
SET
  created_at = sysdatetimeoffset(),
  updated_at = sysdatetimeoffset();

ALTER TABLE telegram_users
  ALTER COLUMN created_at DATETIMEOFFSET NOT NULL;
ALTER TABLE telegram_users
  ALTER COLUMN updated_at DATETIMEOFFSET NOT NULL;

-- telegram-users: drop_client_id

DECLARE @ConstraintName NVARCHAR(200)
SELECT @ConstraintName = KCU.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
  INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
    ON KCU.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
       AND KCU.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
       AND KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
WHERE
  KCU.TABLE_NAME = 'telegram_users' AND
  KCU.COLUMN_NAME = 'client_id'
IF @ConstraintName IS NOT NULL
  EXEC ('ALTER TABLE telegram_users DROP CONSTRAINT ' + @ConstraintName)

DROP INDEX telegram_users.telegram_users_client_id_uniq;

ALTER TABLE telegram_users
  DROP COLUMN client_id;

-- telegram-chats

CREATE TABLE telegram_chats (
  id               BIGINT PRIMARY KEY IDENTITY (1, 1),
  chat_id          BIGINT         NOT NULL REFERENCES chats (id),
  telegram_chat_id BIGINT         NOT NULL,
  created_at       DATETIMEOFFSET NOT NULL,
  updated_at       DATETIMEOFFSET NOT NULL
);

CREATE UNIQUE INDEX telegram_chats_chat_uniq
  ON telegram_chats (chat_id);

-- telegram-messages

DROP TABLE telegram_messages;
