--- schema-cleanup

-- channels columns
DECLARE @constraint VARCHAR(255)
SELECT @constraint = 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 = 'channels' AND KCU.COLUMN_NAME = 'event_id'
EXEC ('ALTER TABLE channels DROP CONSTRAINT ' + @constraint);

ALTER TABLE channels
  DROP COLUMN event_id;


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

ALTER TABLE channels
  DROP COLUMN total_members;


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

ALTER TABLE channels
  DROP COLUMN total_chats;


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

ALTER TABLE channels
  DROP COLUMN total_open_chats;


DECLARE @constraint VARCHAR(255)
SELECT @constraint = 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 = 'channels' AND KCU.COLUMN_NAME = 'chat_event_id'
EXEC ('ALTER TABLE channels DROP CONSTRAINT ' + @constraint);

ALTER TABLE channels
  DROP COLUMN chat_event_id;


DECLARE @constraint VARCHAR(255)
SELECT @constraint = 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 = 'channels' AND KCU.COLUMN_NAME = 'apns_id'
EXEC ('ALTER TABLE channels DROP CONSTRAINT ' + @constraint);

ALTER TABLE channels
  DROP COLUMN apns_id;

-- channel_messages fk

DECLARE @constraint VARCHAR(255)
SELECT @constraint = 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 = 'channel_messages' AND KCU.COLUMN_NAME = 'event_id'
EXEC ('ALTER TABLE channel_messages DROP CONSTRAINT ' + @constraint);

DECLARE @constraint VARCHAR(255)
SELECT @constraint = 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 = 'channel_messages' AND KCU.COLUMN_NAME = 'session_id'
EXEC ('ALTER TABLE channel_messages DROP CONSTRAINT ' + @constraint);

-- channel_thread_events fk

DECLARE @constraint VARCHAR(255)
SELECT @constraint = 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 = 'channel_thread_events' AND KCU.COLUMN_NAME = 'message_id'
EXEC ('ALTER TABLE channel_thread_events DROP CONSTRAINT ' + @constraint);

DECLARE @constraint VARCHAR(255)
SELECT @constraint = 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 = 'channel_thread_events' AND KCU.COLUMN_NAME = 'session_id'
EXEC ('ALTER TABLE channel_thread_events DROP CONSTRAINT ' + @constraint);

-- channel_threads fk

DECLARE @constraint VARCHAR(255)
SELECT @constraint = 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 = 'channel_threads' AND KCU.COLUMN_NAME = 'event_id'
EXEC ('ALTER TABLE channel_threads DROP CONSTRAINT ' + @constraint);

DECLARE @constraint VARCHAR(255)
SELECT @constraint = 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 = 'channel_threads' AND KCU.COLUMN_NAME = 'message_id'
EXEC ('ALTER TABLE channel_threads DROP CONSTRAINT ' + @constraint);

DECLARE @constraint VARCHAR(255)
SELECT @constraint = 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 = 'channel_threads' AND KCU.COLUMN_NAME = 'session_id'
EXEC ('ALTER TABLE channel_threads DROP CONSTRAINT ' + @constraint);

-- channel tables
DROP TABLE channel_apns;
DROP TABLE channel_events;
DROP TABLE channel_members;
DROP TABLE channel_messages;
DROP TABLE channel_thread_events;
DROP TABLE channel_thread_members;
DROP TABLE channel_thread_sessions;
DROP TABLE channel_threads;

-- chat columns
DECLARE @constraint NVARCHAR(100)
SELECT @constraint = OBJECT_NAME([default_object_id])
FROM SYS.COLUMNS
WHERE [object_id] = OBJECT_ID('chats') AND [name] = 'open'
EXEC ('ALTER TABLE chats DROP CONSTRAINT ' + @constraint);

DROP INDEX chats.chats_channel_id_open_changed_at_ix;
DROP INDEX chats.chats_channel_id_open_ix;
DROP INDEX chats.chats_channel_id_opened_at_desc_ix;
ALTER TABLE chats
  DROP COLUMN "open";


DECLARE @constraint VARCHAR(255)
SELECT @constraint = 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 = 'chats' AND KCU.COLUMN_NAME = 'message_id'
EXEC ('ALTER TABLE chats DROP CONSTRAINT ' + @constraint);

ALTER TABLE chats
  DROP COLUMN message_id;


DECLARE @constraint VARCHAR(255)
SELECT @constraint = 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 = 'chats' AND KCU.COLUMN_NAME = 'session_id'
EXEC ('ALTER TABLE chats DROP CONSTRAINT ' + @constraint);

ALTER TABLE chats
  DROP COLUMN session_id;


DECLARE @constraint VARCHAR(255)
SELECT @constraint = 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 = 'chats' AND KCU.COLUMN_NAME = 'assignee_id'
EXEC ('ALTER TABLE chats DROP CONSTRAINT ' + @constraint);

DROP INDEX chats.chats_assignee_id_ix;
ALTER TABLE chats
  DROP COLUMN assignee_id;


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

ALTER TABLE chats
  DROP COLUMN user_unread;


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

ALTER TABLE chats
  DROP COLUMN total_members;


ALTER TABLE chats
  DROP COLUMN opened_at;


ALTER TABLE chats
  DROP COLUMN closed_at;

-- chat_messages

ALTER TABLE chat_messages
  DROP COLUMN uid;


DECLARE @constraint VARCHAR(255)
SELECT @constraint = 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 = 'chat_messages' AND KCU.COLUMN_NAME = 'session_id'
EXEC ('ALTER TABLE chat_messages DROP CONSTRAINT ' + @constraint);

ALTER TABLE chat_messages
  DROP COLUMN session_id;


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

ALTER TABLE chat_messages
  DROP COLUMN "public";


DECLARE @constraint VARCHAR(255)
SELECT @constraint = 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 = 'chat_messages' AND KCU.COLUMN_NAME = 'notice_id'
EXEC ('ALTER TABLE chat_messages DROP CONSTRAINT ' + @constraint);

ALTER TABLE chat_messages
  DROP COLUMN notice_id;


DECLARE @constraint VARCHAR(255)
SELECT @constraint = 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 = 'chat_messages' AND KCU.COLUMN_NAME = 'autoreply_id'
EXEC ('ALTER TABLE chat_messages DROP CONSTRAINT ' + @constraint);

ALTER TABLE chat_messages
  DROP COLUMN autoreply_id;

-- chat_events

DECLARE @constraint VARCHAR(255)
SELECT @constraint = 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 = 'chat_events' AND KCU.COLUMN_NAME = 'member_id'
EXEC ('ALTER TABLE chat_events DROP CONSTRAINT ' + @constraint);

ALTER TABLE chat_events
  DROP COLUMN member_id;


DECLARE @constraint VARCHAR(255)
SELECT @constraint = 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 = 'chat_events' AND KCU.COLUMN_NAME = 'inbox_id'
EXEC ('ALTER TABLE chat_events DROP CONSTRAINT ' + @constraint);

ALTER TABLE chat_events
  DROP COLUMN inbox_id;


DECLARE @constraint VARCHAR(255)
SELECT @constraint = 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 = 'chat_events' AND KCU.COLUMN_NAME = 'thread_id'
EXEC ('ALTER TABLE chat_events DROP CONSTRAINT ' + @constraint);

ALTER TABLE chat_events
  DROP COLUMN thread_id;


DECLARE @constraint VARCHAR(255)
SELECT @constraint = 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 = 'chat_notices' AND KCU.COLUMN_NAME = 'member_id'
EXEC ('ALTER TABLE chat_notices DROP CONSTRAINT ' + @constraint);


DROP TABLE chat_channel_events;
DROP TABLE chat_client_events;
DROP TABLE chat_event_tree;

DROP TABLE chat_inboxes;
DROP TABLE chat_members;
DROP TABLE chat_notices;
DROP TABLE chat_sessions;

DROP TABLE chat_user_changes;
DROP TABLE chat_user_events;
DROP TABLE chat_user_inbox_changes;
DROP TABLE chat_user_inboxes;
DROP TABLE chat_user_thread_changes;
DROP TABLE chat_user_threads;

-- Events
DROP TABLE event_subscribers;

-- Inboxes
DECLARE @constraint VARCHAR(255)
SELECT @constraint = 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 = 'inbox_threads' AND KCU.COLUMN_NAME = 'message_id'
EXEC ('ALTER TABLE inbox_threads DROP CONSTRAINT ' + @constraint);

ALTER TABLE inbox_threads
  DROP COLUMN message_id;

-- Projects
DROP TABLE project_members;
DROP TABLE project_roles;

-- Stats
DROP TABLE stats_channels;

-- Sys
ALTER TABLE sys_queue_consumers
  DROP COLUMN collected_at;

DROP TABLE sys_queue_items;

-- Users
DECLARE @constraint VARCHAR(255)
SELECT @constraint = 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 = 'user_events' AND KCU.COLUMN_NAME = 'group_id'
EXEC ('ALTER TABLE user_events DROP CONSTRAINT ' + @constraint);

DECLARE @constraint VARCHAR(255)
SELECT @constraint = 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 = 'user_groups_members' AND KCU.COLUMN_NAME = 'group_id'
EXEC ('ALTER TABLE user_groups_members DROP CONSTRAINT ' + @constraint);


DROP TABLE user_events;
DROP TABLE user_groups;
DROP TABLE user_groups_members;
