--- facebook-channels


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

ALTER TABLE facebook_channels
  DROP COLUMN app_id,
  DROP COLUMN app_secret,
  DROP COLUMN last_sent_message_id,
  ALTER COLUMN page_id DROP NOT NULL,
  ALTER COLUMN page_token DROP NOT NULL;

DROP INDEX fb_channels_page_id_uniq;
DROP INDEX fb_channels_page_token_uniq;

UPDATE facebook_channels
SET verify_token = '';

ALTER TABLE facebook_channels
  ALTER COLUMN verify_token SET NOT NULL;

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

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

-- facebook-users

ALTER TABLE facebook_users
  ALTER COLUMN user_id TYPE VARCHAR(255),
  ALTER COLUMN user_id SET NOT NULL;

ALTER TABLE facebook_users
  ADD created_at TIMESTAMPTZ,
  ADD updated_at TIMESTAMPTZ;

DROP INDEX facebook_users_channel_user_uniq;

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

ALTER TABLE facebook_users
  ALTER COLUMN created_at SET NOT NULL,
  ALTER COLUMN updated_at SET NOT NULL;

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

-- facebook-chats

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

CREATE SEQUENCE facebook_chats_id_seq;

CREATE UNIQUE INDEX facebook_chats_chat_uniq
  ON facebook_chats (chat_id);

-- facebook-messages

DROP TABLE facebook_messages;
