--- vk-channels: drop last_sent_message_id, secret

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

ALTER TABLE vk_channels
  DROP COLUMN last_sent_message_id, secret;

-- vk-channels

ALTER TABLE vk_channels
  ADD
  uuid VARCHAR(36),
  name NVARCHAR(1024),
  deleted_at DATETIMEOFFSET,
  restored_at DATETIMEOFFSET;

CREATE UNIQUE INDEX vk_channels_uuid_uniq
  ON vk_channels (uuid)
  WHERE uuid IS NOT NULL;

-- vk-channels: nullable group_id, token, code

DROP INDEX vk_channels.vk_channels_access_token_uniq;
DROP INDEX vk_channels.vk_channels_group_id_uniq;

ALTER TABLE vk_channels
  ALTER COLUMN group_id BIGINT NULL;
ALTER TABLE vk_channels
  ALTER COLUMN access_token VARCHAR(255) NULL;
ALTER TABLE vk_channels
  ALTER COLUMN confirmation_code VARCHAR(255) NULL;

CREATE UNIQUE INDEX vk_channels_group_id_uniq
  ON vk_channels (group_id)
  WHERE deleted = 0 AND group_id IS NOT NULL;

-- vk_users: add created_at, updated_at

ALTER TABLE vk_users
  ADD created_at DATETIMEOFFSET,
  updated_at DATETIMEOFFSET;

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

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

-- vk-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 = 'vk_users' AND
  KCU.COLUMN_NAME = 'client_id'
IF @ConstraintName IS NOT NULL
  EXEC ('ALTER TABLE vk_users DROP CONSTRAINT ' + @ConstraintName)

DROP INDEX vk_users.vk_users_client_id_uniq;

ALTER TABLE vk_users
  DROP COLUMN client_id;

-- vk-chats

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

CREATE UNIQUE INDEX vk_chats_chat_uniq
  ON vk_chats (chat_id);

-- vk-messages

DROP TABLE vk_messages;
