--- drop-org-id

-- channels

declare @constraint varchar(100)
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 = 'org_id'
EXEC('alter table channels drop  CONSTRAINT ' + @constraint);

DROP INDEX channels_org_id_ix on channels;
DROP INDEX channels_org_id_name_nondeleted_uniq on channels;

ALTER TABLE channels DROP COLUMN org_id;
CREATE UNIQUE INDEX channels_name_nondeleted_uniq
  ON channels (name_lower)
  WHERE deleted = 0;

-- client_sessions

declare @constraint varchar(100)
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 = 'client_sessions' AND KCU.COLUMN_NAME = 'org_id'
EXEC('alter table client_sessions drop  CONSTRAINT ' + @constraint);

DROP INDEX client_sessions_org_id_integration_credentials_id_uniq on client_sessions;

ALTER TABLE client_sessions
  DROP COLUMN org_id;
CREATE UNIQUE INDEX client_sessions_integration_credentials_id_uniq
  ON client_sessions (integration_credentials)
  WHERE integration_credentials IS NOT NULL;

-- clients

declare @constraint varchar(100)
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 = 'clients' AND KCU.COLUMN_NAME = 'org_id'
EXEC('alter table clients drop  CONSTRAINT ' + @constraint);

DROP INDEX clients_org_id_ix on clients;
DROP INDEX clients_org_id_integration_id_uniq on clients;

ALTER TABLE clients
  DROP COLUMN org_id;
CREATE UNIQUE INDEX clients_integration_id_uniq
  ON clients (integration_id)
  WHERE integration_id IS NOT NULL;

-- user_agents

declare @constraint varchar(100)
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_agents' AND KCU.COLUMN_NAME = 'org_id'
EXEC('alter table user_agents drop  CONSTRAINT ' + @constraint);

DROP INDEX user_agents_org_id_user_agent_uniq on user_agents;

ALTER TABLE user_agents
  DROP COLUMN org_id;
CREATE UNIQUE INDEX user_agents_org_id_user_agent_uniq
  ON user_agents (user_agent_lower);

-- user_events

declare @constraint varchar(100)
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 = 'org_id'
EXEC('alter table user_events drop  CONSTRAINT ' + @constraint);

ALTER TABLE user_events
  DROP COLUMN org_id;

-- user_groups

declare @constraint varchar(100)
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' AND KCU.COLUMN_NAME = 'org_id'
EXEC('alter table user_groups drop  CONSTRAINT ' + @constraint);

DROP INDEX user_groups_org_id_name_nondeleted_uniq on user_groups;

ALTER TABLE user_groups
  DROP COLUMN org_id;
CREATE UNIQUE INDEX user_groups_name_nondeleted_uniq
  ON user_groups (name_lower)
  WHERE deleted = 0;

-- users

declare @constraint varchar(100)
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 = 'users' AND KCU.COLUMN_NAME = 'org_id'
EXEC('alter table users drop  CONSTRAINT ' + @constraint);

DROP INDEX users_org_id_ix on users;

ALTER TABLE users
  DROP COLUMN org_id;
