---------------------------------------------------------------------
-- BASE_VERSION 50

-- You could execute this script from MSSQL Query Analyzer
-- or use OSQL utility 
-- Don't forget that user should have appropriate rights
--
--#########################################################################################################

if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 10

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE veri>=@ver )
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_mediaplan_scheds' AND TYPE='U')
BEGIN
	CREATE TABLE mp_mediaplan_scheds(
	id				int identity NOT NULL,
	mediaplan_id	int NULL,
	schedule_id		int NULL,
	constraint PK_MP_MEDIAPLAN_SCHEDS primary key nonclustered (id) 
)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(SELECT sysobjects.id FROM sysobjects WHERE xtype = 'F' AND name = 'FK_MP_MEDIAPLAN_SCHEDS_14_MP_MEDIAPLANS')
BEGIN
	ALTER TABLE mp_mediaplan_scheds
	   add constraint FK_MP_MEDIAPLAN_SCHEDS_14_MP_MEDIAPLANS  foreign key (mediaplan_id)
		  references mp_mediaplans (id)
			on delete cascade
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END


IF NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_phonograms' AND syscolumns.name='placement_method')
BEGIN 
	ALTER TABLE mp_phonograms
	ADD placement_method int default 0 not null
END

IF NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_payment_types' AND syscolumns.name='color')
BEGIN 
	ALTER TABLE mp_payment_types
	ADD color int default null
END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 20

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE veri>=@ver )
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_settings_tmp' AND TYPE='U')
BEGIN
	create table mp_settings_tmp (
	   id                   int                  identity,
	   name                 varchar(1000)        not null,
	   value                image				 not null,
	   enabled              bit                  not null,
	   constraint PK_MP_SETTINGS_EXT primary key nonclustered (id)
	)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_settings_tmp' AND TYPE='U')
BEGIN
	insert into mp_settings_tmp (name, value, enabled) select name, value, enabled from mp_settings
	DROP TABLE mp_settings
	EXEC sp_rename 'mp_settings_tmp', 'mp_settings'
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 30

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE veri>=@ver )
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='UpdateMpPositions' AND TYPE='P')
	DROP PROCEDURE UpdateMpPositions
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

exec sp_executesql N'CREATE PROCEDURE UpdateMpPositions @Type varchar(32), @sXML xml
AS
BEGIN

DECLARE @tmpTable TABLE
(
id int,
date datetime,
block_number smallint,
plan_number smallint,
version int,
type int, 
position int
);

BEGIN TRANSACTION Transact;

IF @Type = ''INSERT''
BEGIN

;WITH XmlData AS 
(
    SELECT
	   List.Id.query(''mediaplans_id'').value(''.'', ''int'') as mediaplans_id,
	   List.Id.query(''users_id'').value(''.'', ''int'') as users_id,
	   List.Id.query(''date'').value(''.'', ''datetime'') as date,
	   List.Id.query(''block_number'').value(''.'', ''smallint'') as block_number,
	   List.Id.query(''plan_number'').value(''.'', ''smallint'') as plan_number,
	   List.Id.query(''first'').value(''.'', ''bit'') as first,
	   List.Id.query(''version'').value(''.'', ''int'') as version,
	   List.Id.query(''type'').value(''.'', ''int'') as type,
	   List.Id.query(''position'').value(''.'', ''int'') as position,
	   List.Id.query(''placementMethod'').value(''.'', ''int'') as placementMethod,
	   List.Id.query(''is_bonus'').value(''.'', ''bit'') as is_bonus
    FROM 
        @sXML.nodes(''/positions/position'') AS List(Id)
)
INSERT INTO mp_positions(mediaplans_id, users_id, date, block_number, plan_number, first, version, type, position, placementMethod, is_bonus)
OUTPUT INSERTED.id, INSERTED.date, INSERTED.block_number, INSERTED.plan_number, INSERTED.version, INSERTED.type, INSERTED.position INTO @tmpTable
SELECT DISTINCT mediaplans_id, users_id, date, block_number, plan_number, first, version, type, position, placementMethod, is_bonus FROM XmlData;

END

IF @Type = ''UPDATE''
BEGIN

;WITH XmlData AS 
(
    SELECT
	   List.Id.query(''id'').value(''.'', ''int'') as id,
	   List.Id.query(''mediaplans_id'').value(''.'', ''int'') as mediaplans_id,
	   List.Id.query(''users_id'').value(''.'', ''int'') as users_id,
	   List.Id.query(''date'').value(''.'', ''datetime'') as date,
	   List.Id.query(''block_number'').value(''.'', ''smallint'') as block_number,
	   List.Id.query(''plan_number'').value(''.'', ''smallint'') as plan_number,
	    List.Id.query(''version'').value(''.'', ''int'') as version,
	   List.Id.query(''type'').value(''.'', ''int'') as type,
	   List.Id.query(''position'').value(''.'', ''int'') as position
    FROM 
        @sXML.nodes(''/positions/position'') AS List(Id)
)
UPDATE mp_positions SET 
	   mp_positions.mediaplans_id = XmlData.mediaplans_id,
	   mp_positions.users_id = XmlData.users_id,
	   mp_positions.date = XmlData.date,
	   mp_positions.block_number = XmlData.block_number,
	   mp_positions.plan_number = XmlData.plan_number,
	   mp_positions.version = XmlData.version,
	   mp_positions.type = XmlData.type,
	   mp_positions.position = XmlData.position
FROM   mp_positions INNER JOIN XmlData ON mp_positions.id = XmlData.id;

END

IF @Type = ''DELETE'' 
BEGIN

;WITH XmlData AS 
(
    SELECT
	   List.Id.query(''id'').value(''.'', ''int'') as id
    FROM 
        @sXML.nodes(''/positions/position'') AS List(Id)
)
DELETE mp_positions FROM mp_positions JOIN XmlData ON mp_positions.id = XmlData.id;

END

COMMIT TRANSACTION Transact;

SELECT *FROM @tmpTable;

END'
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END


IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='UpdateMpPositionReplacement' AND TYPE='P')
	DROP PROCEDURE UpdateMpPositionReplacement
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

exec sp_executesql N'CREATE PROCEDURE UpdateMpPositionReplacement @Type varchar(32), @sXML xml
AS
BEGIN

DECLARE @tmpTable TABLE
(
id int,
position_id int,
schedules_id int
);

BEGIN TRANSACTION Transact;

IF @Type = ''UPDATE'' 
BEGIN

;WITH XmlData AS 
(
    SELECT
	   List.Id.query(''id'').value(''.'', ''int'') as id,
	   List.Id.query(''position_id'').value(''.'', ''int'') as position_id,
	   List.Id.query(''schedules_id'').value(''.'', ''int'') as schedules_id,
	   List.Id.query(''release_date'').value(''.'', ''datetime'') as release_date,
	   List.Id.query(''placementMethod'').value(''.'', ''int'') as placementMethod,
	   List.Id.query(''released'').value(''.'', ''bit'') as released,
	   List.Id.query(''is_bonus'').value(''.'', ''bit'') as is_bonus
    FROM 
        @sXML.nodes(''/positions/position'') AS List(Id)
)
UPDATE	mp_position_replacement  SET 
		mp_position_replacement.position_id = XmlData.position_id,
		mp_position_replacement.schedules_id = XmlData.schedules_id,
		mp_position_replacement.release_date = CASE WHEN XmlData.release_date = '''' THEN NULL ELSE XmlData.release_date END,
		mp_position_replacement.placementMethod = XmlData.placementMethod,
		mp_position_replacement.released = CASE WHEN XmlData.released = '''' THEN NULL ELSE XmlData.released END,
		mp_position_replacement.is_bonus = XmlData.is_bonus 
FROM	mp_position_replacement INNER JOIN XmlData ON mp_position_replacement.id = XmlData.id;

END

IF @Type = ''INSERT''
BEGIN

;WITH XmlData AS 
(
    SELECT
	   List.Id.query(''id'').value(''.'', ''int'') as id,
	   List.Id.query(''position_id'').value(''.'', ''int'') as position_id,
	   List.Id.query(''schedules_id'').value(''.'', ''int'') as schedules_id,
	   List.Id.query(''release_date'').value(''.'', ''datetime'') as release_date,
	   List.Id.query(''placementMethod'').value(''.'', ''int'') as placementMethod,
	   List.Id.query(''released'').value(''.'', ''bit'') as released,
	   List.Id.query(''is_bonus'').value(''.'', ''bit'') as is_bonus
    FROM 
        @sXML.nodes(''/positions/position'') AS List(Id)
)
INSERT mp_position_replacement(position_id, schedules_id, release_date, placementMethod, released, is_bonus)
OUTPUT INSERTED.id, INSERTED.position_id, INSERTED.schedules_id INTO @tmpTable
SELECT DISTINCT position_id, schedules_id, release_date, placementMethod, released, is_bonus FROM XmlData;

END

IF @Type = ''DELETE''
BEGIN

;WITH XmlData AS 
(
    SELECT
	   List.Id.query(''position_id'').value(''.'', ''int'') as position_id,
	   List.Id.query(''schedules_id'').value(''.'', ''int'') as schedules_id
    FROM 
        @sXML.nodes(''/positions/position'') AS List(Id)
)
delete mp_position_replacement FROM mp_position_replacement JOIN XmlData ON 
mp_position_replacement.position_id = XmlData.position_id AND 
mp_position_replacement.schedules_id not in (select x.schedules_id from XmlData x where mp_position_replacement.position_id = x.position_id);

END

COMMIT TRANSACTION Transact;

SELECT tmp.*, pos.version as version FROM @tmpTable tmp inner join mp_positions pos on pos.id = tmp.position_id;
END'
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO


--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 40

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE veri>=@ver )
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_running_app' AND TYPE='U')
BEGIN
	create table mp_running_app (
	   mp_id                uniqueidentifier    not null,
	   owner_name           varchar(255)        not null,
	   last_time_active     datetime			not null,
	   constraint PK_MP_RUNNING_APP primary key nonclustered (mp_id) 
	)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_sync_events' AND TYPE='U')
BEGIN
	create table mp_sync_events (
	   id					int					identity,
	   mp_id				uniqueidentifier	not null,
	   event_type           int					not null,
	   gen_evt_time			datetime			not null,
	   constraint PK_MP_SYNC_EVENTS primary key nonclustered (id)
	)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(SELECT sysobjects.id FROM sysobjects WHERE xtype = 'F' AND name = 'FK_MP_SYNC_EVENTS_VS_MP_RUNNING_APP')
BEGIN
	ALTER TABLE mp_sync_events
	   add constraint FK_MP_SYNC_EVENTS_VS_MP_RUNNING_APP  foreign key (mp_id)
		  references mp_running_app (mp_id)
			on delete cascade
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO


--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 50

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE veri>=@ver )
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_mediaplans' AND syscolumns.name='organization_id')
BEGIN
ALTER TABLE mp_mediaplans ADD organization_id INT NULL;
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO