-- pushes

ALTER TABLE pushes
  DROP COLUMN enqueued_at;

DROP INDEX pushes.pushes_message_id_enqueued_ix;

CREATE UNIQUE INDEX pushes_token_message_uniq
  ON pushes (token_id, message_id);

---pushes: drop channel_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 = 'pushes' AND
  KCU.COLUMN_NAME = 'channel_id'
IF @ConstraintName IS NOT NULL
  EXEC ('ALTER TABLE pushes DROP CONSTRAINT ' + @ConstraintName)

DROP INDEX pushes.pushes_channel_id_state_created_at_ix;
ALTER TABLE pushes
  DROP COLUMN channel_id;

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

DROP INDEX pushes.pushes_client_id_state_created_at_ix;
ALTER TABLE pushes
  DROP COLUMN client_id;
