-- QW_TICKETESCALATION_PRIORITY --

CREATE TABLE dbo.TMP_TICKETESCALATION_PRIORITY(
	QW_PRIORITY int NULL,
	QW_ESCALATIONID int NULL
)
go

INSERT INTO dbo.TMP_TICKETESCALATION_PRIORITY
select * from dbo.QW_TICKETESCALATION_PRIORITY
go

TRUNCATE TABLE dbo.QW_TICKETESCALATION_PRIORITY
go

ALTER TABLE dbo.QW_TICKETESCALATION_PRIORITY
ADD QW_ID int NULL
go

ALTER TABLE dbo.QW_TICKETESCALATION_PRIORITY
ALTER COLUMN QW_ID int NOT NULL
go

DECLARE @prt int
DECLARE @esc int
DECLARE @id int

DECLARE cur CURSOR FOR
  SELECT QW_PRIORITY, QW_ESCALATIONID 
    FROM dbo.TMP_TICKETESCALATION_PRIORITY

  OPEN cur

  FETCH NEXT FROM cur INTO @prt, @esc
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
      EXEC dbo.QX_NEXT_KEY 'qw_ticketescalation_priority', 1, @id OUTPUT
      INSERT INTO dbo.QW_TICKETESCALATION_PRIORITY (QW_ID, QW_PRIORITY, QW_ESCALATIONID)
        VALUES(@id, @prt, @esc)

      FETCH NEXT FROM cur INTO @prt, @esc
    END
  CLOSE cur
  DEALLOCATE cur
GO

DROP TABLE dbo.TMP_TICKETESCALATION_PRIORITY
GO


-- QW_TICKETESCALATION_RECIPIENT --

CREATE TABLE dbo.TMP_TICKETESCALATION_RECIPIENT(
	QW_RECIPIENT int NULL,
	QW_ESCALATIONID int NULL
)
go

INSERT INTO dbo.TMP_TICKETESCALATION_RECIPIENT
select * from dbo.QW_TICKETESCALATION_RECIPIENT
go

TRUNCATE TABLE dbo.QW_TICKETESCALATION_RECIPIENT
go

ALTER TABLE dbo.QW_TICKETESCALATION_RECIPIENT
ADD QW_ID int NULL
go

ALTER TABLE dbo.QW_TICKETESCALATION_RECIPIENT
ALTER COLUMN QW_ID int NOT NULL
go

DECLARE @rcpt int
DECLARE @esc int
DECLARE @id int

DECLARE cur CURSOR FOR
  SELECT QW_RECIPIENT, QW_ESCALATIONID 
    FROM dbo.TMP_TICKETESCALATION_RECIPIENT

  OPEN cur

  FETCH NEXT FROM cur INTO @rcpt, @esc
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
      EXEC dbo.QX_NEXT_KEY 'qw_ticketescalation_recipient', 1, @id OUTPUT
      INSERT INTO dbo.QW_TICKETESCALATION_RECIPIENT (QW_ID, QW_RECIPIENT, QW_ESCALATIONID)
        VALUES(@id, @rcpt, @esc)

      FETCH NEXT FROM cur INTO @rcpt, @esc
    END
  CLOSE cur
  DEALLOCATE cur
GO

DROP TABLE dbo.TMP_TICKETESCALATION_RECIPIENT
GO



-- QW_TICKETESCALATION_STATUS --

CREATE TABLE dbo.TMP_TICKETESCALATION_STATUS(
	QW_STATUS int NULL,
	QW_ESCALATIONID int NULL
)
go

INSERT INTO dbo.TMP_TICKETESCALATION_STATUS
select * from dbo.QW_TICKETESCALATION_STATUS
go

TRUNCATE TABLE dbo.QW_TICKETESCALATION_STATUS
go

ALTER TABLE dbo.QW_TICKETESCALATION_STATUS
ADD QW_ID int NULL
go

ALTER TABLE dbo.QW_TICKETESCALATION_STATUS
ALTER COLUMN QW_ID int NOT NULL
go

DECLARE @stat int
DECLARE @esc int
DECLARE @id int

DECLARE cur CURSOR FOR
  SELECT QW_STATUS, QW_ESCALATIONID 
    FROM dbo.TMP_TICKETESCALATION_STATUS

  OPEN cur

  FETCH NEXT FROM cur INTO @stat, @esc
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
      EXEC dbo.QX_NEXT_KEY 'qw_ticketescalation_status', 1, @id OUTPUT
      INSERT INTO dbo.QW_TICKETESCALATION_STATUS (QW_ID, QW_STATUS, QW_ESCALATIONID)
        VALUES(@id, @stat, @esc)

      FETCH NEXT FROM cur INTO @stat, @esc
    END
  CLOSE cur
  DEALLOCATE cur
GO

DROP TABLE dbo.TMP_TICKETESCALATION_STATUS
GO


-- QW_TICKETNOTIFICATION_PRIORITY --

CREATE TABLE dbo.TMP_TICKETNOTIFICATION_PRIORITY(
	QW_PRIORITY int NULL,
	QW_NOTIFICATIONID int NULL
)
go

INSERT INTO dbo.TMP_TICKETNOTIFICATION_PRIORITY
select * from QW_TICKETNOTIFICATION_PRIORITY
go

TRUNCATE TABLE dbo.QW_TICKETNOTIFICATION_PRIORITY
go

ALTER TABLE dbo.QW_TICKETNOTIFICATION_PRIORITY
ADD QW_ID int NULL
go

ALTER TABLE dbo.QW_TICKETNOTIFICATION_PRIORITY
ALTER COLUMN QW_ID int NOT NULL
go

DECLARE @prt int
DECLARE @notif int
DECLARE @id int

DECLARE cur CURSOR FOR
  SELECT QW_PRIORITY, QW_NOTIFICATIONID 
    FROM dbo.TMP_TICKETNOTIFICATION_PRIORITY

  OPEN cur

  FETCH NEXT FROM cur INTO @prt, @notif
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
      EXEC dbo.QX_NEXT_KEY 'qw_ticketnotification_priority', 1, @id OUTPUT
      INSERT INTO dbo.QW_TICKETNOTIFICATION_PRIORITY (QW_ID, QW_PRIORITY, QW_NOTIFICATIONID)
        VALUES(@id, @prt, @notif)

      FETCH NEXT FROM cur INTO @prt, @notif
    END
  CLOSE cur
  DEALLOCATE cur
GO

DROP TABLE dbo.TMP_TICKETNOTIFICATION_PRIORITY
GO


-- QW_TICKETNOTIFICATION_RECIP --

CREATE TABLE dbo.TMP_TICKETNOTIFICATION_RECIPIENT(
	QW_RECIPIENT int NULL,
	QW_NOTIFICATIONID int NULL
)
go

INSERT INTO dbo.TMP_TICKETNOTIFICATION_RECIPIENT
select * from QW_TICKETNOTIFICATION_RECIP
go

TRUNCATE TABLE dbo.QW_TICKETNOTIFICATION_RECIP
go

ALTER TABLE dbo.QW_TICKETNOTIFICATION_RECIP
ADD QW_ID int NULL
go

ALTER TABLE dbo.QW_TICKETNOTIFICATION_RECIP
ALTER COLUMN QW_ID int NOT NULL
go

DECLARE @rpt int
DECLARE @notif int
DECLARE @id int

DECLARE cur CURSOR FOR
  SELECT QW_RECIPIENT, QW_NOTIFICATIONID 
    FROM dbo.TMP_TICKETNOTIFICATION_RECIPIENT

  OPEN cur

  FETCH NEXT FROM cur INTO @rpt, @notif
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
      EXEC dbo.QX_NEXT_KEY 'qw_ticketnotification_recip', 1, @id OUTPUT
      INSERT INTO dbo.QW_TICKETNOTIFICATION_RECIP (QW_ID, QW_RECIPIENT, QW_NOTIFICATIONID)
        VALUES(@id, @rpt, @notif)

      FETCH NEXT FROM cur INTO @rpt, @notif
    END
  CLOSE cur
  DEALLOCATE cur
GO

DROP TABLE dbo.TMP_TICKETNOTIFICATION_RECIPIENT
GO


-- QW_TICKETNOTIFICATION_STATUS --

CREATE TABLE dbo.TMP_TICKETNOTIFICATION_STATUS(
	QW_STATUS int NULL,
	QW_NOTIFICATIONID int NULL
)
go

INSERT INTO dbo.TMP_TICKETNOTIFICATION_STATUS
select * from QW_TICKETNOTIFICATION_STATUS
go

TRUNCATE TABLE dbo.QW_TICKETNOTIFICATION_STATUS
go

ALTER TABLE dbo.QW_TICKETNOTIFICATION_STATUS
ADD QW_ID int NULL
go

ALTER TABLE dbo.QW_TICKETNOTIFICATION_STATUS
ALTER COLUMN QW_ID int NOT NULL
go

DECLARE @stat int
DECLARE @notif int
DECLARE @id int

DECLARE cur CURSOR FOR
  SELECT QW_STATUS, QW_NOTIFICATIONID 
    FROM dbo.TMP_TICKETNOTIFICATION_STATUS

  OPEN cur

  FETCH NEXT FROM cur INTO @stat, @notif
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
      EXEC dbo.QX_NEXT_KEY 'qw_ticketnotification_status', 1, @id OUTPUT
      INSERT INTO dbo.QW_TICKETNOTIFICATION_STATUS (QW_ID, QW_STATUS, QW_NOTIFICATIONID)
        VALUES(@id, @stat, @notif)

      FETCH NEXT FROM cur INTO @stat, @notif
    END
  CLOSE cur
  DEALLOCATE cur
GO

DROP TABLE dbo.TMP_TICKETNOTIFICATION_STATUS
GO


-- QW_INTER_TICKET --

CREATE TABLE dbo.TMP_INTER_TICKET(
	QW_INTERACTIONID int NULL,
	QW_TICKETID int NULL,
)
go

ALTER TABLE dbo.QW_INTER_TICKET DROP CONSTRAINT PK_INTER_TICKET
go

INSERT INTO dbo.TMP_INTER_TICKET
select * from dbo.QW_INTER_TICKET
go

TRUNCATE TABLE dbo.QW_INTER_TICKET
go

ALTER TABLE dbo.QW_INTER_TICKET
ADD QW_ID int NULL
go

ALTER TABLE dbo.QW_INTER_TICKET
ALTER COLUMN QW_ID int NOT NULL
go

ALTER TABLE dbo.QW_INTER_TICKET
ADD CONSTRAINT PK_INTER_TICKET PRIMARY KEY CLUSTERED (QW_ID)
go

DECLARE @ticket int
DECLARE @inter int
DECLARE @id int

DECLARE cur CURSOR FOR
  SELECT QW_INTERACTIONID, QW_TICKETID 
    FROM dbo.TMP_INTER_TICKET

  OPEN cur

  FETCH NEXT FROM cur INTO @inter, @ticket
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
      EXEC dbo.QX_NEXT_KEY 'qw_inter_ticket', 1, @id OUTPUT
      INSERT INTO dbo.QW_INTER_TICKET (QW_ID, QW_INTERACTIONID, QW_TICKETID)
        VALUES(@id, @inter, @ticket)

      FETCH NEXT FROM cur INTO @inter, @ticket
    END
  CLOSE cur
  DEALLOCATE cur
GO

DROP TABLE dbo.TMP_INTER_TICKET
GO

-- all new update scripts must include a line like this:
UPDATE QX_SYS_PROP SET PROP_VAL = '057' WHERE (PROP_ID = 'DB_APP_VERSION');
GO