-- facebook-channels

ALTER TABLE facebook_channels
  ADD
  uuid VARCHAR(36),
  page_name NVARCHAR(1024),
  verify_token NVARCHAR(255),
  deleted_at DATETIMEOFFSET,
  restored_at DATETIMEOFFSET;

UPDATE facebook_channels
SET verify_token = '';

ALTER TABLE facebook_channels
  ALTER COLUMN verify_token NVARCHAR(255) NOT NULL;

CREATE UNIQUE INDEX facebook_channels_uuid_uniq
  ON facebook_channels (uuid)
  WHERE uuid IS NOT NULL;

-- facebook-channels: nullable page_id, page_token

DROP INDEX facebook_channels.fb_channels_page_id_uniq;
DROP INDEX facebook_channels.fb_channels_page_token_uniq;

ALTER TABLE facebook_channels
  ALTER COLUMN page_id VARCHAR(255) NULL;
ALTER TABLE facebook_channels
  ALTER COLUMN page_token VARCHAR(255) NULL;

CREATE UNIQUE INDEX facebook_channels_page_id_uniq
  ON facebook_channels (page_id)
  WHERE deleted = 0 AND page_id IS NOT NULL;

--- facebook-channels: drop app_id, app_secret, last_sent_message_id

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

DROP INDEX facebook_channels.fb_channels_app_id_idx;

ALTER TABLE facebook_channels
  DROP COLUMN app_id, app_secret, last_sent_message_id;

-- facebook-users

ALTER TABLE facebook_users
  ADD created_at DATETIMEOFFSET,
  updated_at DATETIMEOFFSET;

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

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

-- facebook-users: alter user_id

DROP INDEX facebook_users.facebook_users_channel_user_uniq;

ALTER TABLE facebook_users
  ALTER COLUMN user_id NVARCHAR(255) NOT NULL;

CREATE UNIQUE INDEX facebook_users_channel_user_uniq
  ON facebook_users (channel_id, user_id);

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

DROP INDEX facebook_users.facebook_users_client_id_uniq;

ALTER TABLE facebook_users
  DROP COLUMN client_id;

-- facebook-chats

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

CREATE UNIQUE INDEX facebook_chats_chat_uniq
  ON facebook_chats (chat_id);

-- facebook-messages

DROP TABLE facebook_messages;
