---------------------------------------------------------------------
-- BASE_VERSION 580

-- 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


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

DECLARE @ver int
SET @ver = 60

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_discount_values' AND syscolumns.name='apply_type')
BEGIN
	ALTER TABLE mp_discount_values ADD apply_type smallint 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


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

DECLARE @ver int
SET @ver = 70

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_tariff_scales' AND syscolumns.name='name' AND syscolumns.name='end_date' AND syscolumns.name='default_scale')
BEGIN
	ALTER TABLE mp_tariff_scales ADD name varchar(128) NULL;
	ALTER TABLE mp_tariff_scales ADD end_date datetime NULL;
	ALTER TABLE mp_tariff_scales ADD default_scale bit NOT NULL DEFAULT(0);
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 = 80

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_spot_types' AND TYPE='U')
BEGIN
	CREATE TABLE mp_spot_types
	(
		id int identity NOT NULL,
		show_type int NULL,
		name varchar(255) NULL,
		system_type bit default(0),
		constraint PK_MP_SPOT_TYPES primary key nonclustered (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 = 90

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_tariff_scales' AND syscolumns.name='max_bonus_spots')
BEGIN
	ALTER TABLE mp_tariff_scales ADD max_bonus_spots varchar(16) 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


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

DECLARE @ver int
SET @ver = 100

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_accounts' AND syscolumns.name='barter')
BEGIN
	ALTER TABLE mp_accounts ADD barter BIT DEFAULT(0);
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_payments' AND syscolumns.name='barter')
BEGIN
	ALTER TABLE mp_payments ADD barter BIT DEFAULT(0);
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 = 110

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_account_services' AND TYPE='U')
BEGIN
	CREATE TABLE mp_account_services
	(
		id int identity NOT NULL,
		account_id int NULL,
		service_name nvarchar(1024) NULL,
		service_cost money NULL,
		constraint PK_MP_ACCOUNT_SERVICES 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 = 'PK_MP_ACCOUNT_SERVICES_MP_ACCOUNTS')
BEGIN
	ALTER TABLE mp_account_services
	   add constraint PK_MP_ACCOUNT_SERVICES_MP_ACCOUNTS foreign key (account_id)
		  references mp_accounts (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 = 120

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_account_mediaplans' AND TYPE='U')
BEGIN
	CREATE TABLE mp_account_mediaplans
	(
		id int identity NOT NULL,
		account_id int NULL,
		mediaplan_id int NULL,
		constraint PK_MP_ACCOUNT_MEDIAPLANS 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 = 'PK_MP_ACCOUNT_MEDIAPLANS_MP_ACCOUNTS')
BEGIN
	ALTER TABLE mp_account_mediaplans
	   add constraint PK_MP_ACCOUNT_MEDIAPLANS_MP_ACCOUNTS foreign key (account_id)
		  references mp_accounts (id)
			on delete cascade
END
if(@@ERROR<>0) BEGIN
	ROLLBACK
	RAISERROR('',16,1)
	RETURN
END

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


IF EXISTS(SELECT sysobjects.id
FROM sysobjects
WHERE xtype = 'F' AND name = 'FK_MP_MEDIA_MP_ACCOUNT')
BEGIN
	ALTER TABLE mp_accounts DROP constraint FK_MP_MEDIA_MP_ACCOUNT
END
if(@@ERROR<>0) BEGIN
	ROLLBACK
	RAISERROR('',16,1)
	RETURN
END

if EXISTS(
	SELECT syscolumns.name
FROM syscolumns
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id
WHERE sysobjects.name='mp_accounts' AND syscolumns.name='mediaplan_id')
BEGIN

	insert into mp_account_mediaplans
		(account_id, mediaplan_id)
	select id as account_id, mediaplan_id
	from mp_accounts

--ALTER TABLE mp_accounts DROP COLUMN mediaplan_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 = 130

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_phonograms' AND syscolumns.name='use_planned_duration')
BEGIN
	ALTER TABLE mp_phonograms ADD use_planned_duration BIT DEFAULT(0);
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 = 140

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_discount_values' AND syscolumns.name='combination_type')
BEGIN
	ALTER TABLE mp_discount_values ADD combination_type smallint DEFAULT(0);
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 = 150

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_base_costs' AND syscolumns.name='spot_duration')
BEGIN
	ALTER TABLE mp_base_costs ADD spot_duration int NOT NULL DEFAULT(0);
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 = 160

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
------------------

CREATE TABLE [dbo].[mp_owners_mediaplans]
(
	[id] [int] IDENTITY(1,1) NOT NULL,
	[id_mediaplan] [int] NULL,
	[id_user] [int] NULL,
	CONSTRAINT [PK_mp_owners_mediaplans] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

------------------
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 = 170

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
------------------

ALTER TABLE [dbo].[mp_owners_mediaplans]  WITH CHECK ADD  CONSTRAINT [FK_mp_owners_mediaplans_mp_mediaplans] FOREIGN KEY([id_mediaplan])
REFERENCES [dbo].[mp_mediaplans] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE

ALTER TABLE [dbo].[mp_owners_mediaplans] CHECK CONSTRAINT [FK_mp_owners_mediaplans_mp_mediaplans]

ALTER TABLE [dbo].[mp_owners_mediaplans]  WITH CHECK ADD  CONSTRAINT [FK_mp_owners_mediaplans_USERS] FOREIGN KEY([id_user])
REFERENCES [dbo].[USERS] ([id])
ON UPDATE CASCADE
ON DELETE SET NULL

ALTER TABLE [dbo].[mp_owners_mediaplans] CHECK CONSTRAINT [FK_mp_owners_mediaplans_USERS]

------------------
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 = 180

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
------------------

insert into mp_owners_mediaplans
	(id_mediaplan, id_user)
select id as id_mediaplan, users_id as id_user
from mp_mediaplans

------------------
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 = 190

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_positions' AND syscolumns.name='grid_block_id')
BEGIN
	ALTER TABLE mp_positions ADD grid_block_id int NULL

	ALTER TABLE [dbo].mp_positions  WITH CHECK ADD  CONSTRAINT [FK_mp_positions_GRID_BLOCKS] FOREIGN KEY([grid_block_id])
REFERENCES [dbo].[GRID_BLOCKS] ([id])
ON UPDATE CASCADE
ON DELETE SET 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

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

DECLARE @ver int
SET @ver = 200

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_discount_values' AND syscolumns.name='default_value_f' AND syscolumns.name='min_value_f' AND syscolumns.name='max_value_f')
BEGIN
	ALTER TABLE mp_discount_values ADD default_value_f float default 0 not null;
	ALTER TABLE mp_discount_values ADD min_value_f float default 0 not null;
	ALTER TABLE mp_discount_values ADD max_value_f float default 0 not null;
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_used_discounts' AND syscolumns.name='manager_value_f')
BEGIN
	ALTER TABLE mp_used_discounts ADD manager_value_f float default 0 not 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

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

DECLARE @ver int
SET @ver = 210

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
------------------

UPDATE mp_discount_values SET default_value_f=default_value, min_value_f=min_value, max_value_f=max_value WHERE default_value_f=0 AND min_value_f=0 AND max_value_f=0
UPDATE mp_used_discounts SET  manager_value_f=manager_value WHERE manager_value_f=0

------------------
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 @comp_ver int
DECLARE @comp_name varchar(128)
SET @comp_ver = 220
SET @comp_name = 'MediaPlannerComponent'

IF EXISTS( SELECT *
FROM components
WHERE name = @comp_name and veri >= @comp_ver) 
    BEGIN
	print 'Component: '+ @comp_name + ' already updated to version '  +   CAST(@comp_ver as varchar(128))+ ' or more'
	return
END

SET XACT_ABORT ON
BEGIN TRAN


alter table mp_accounts add edit_type int not null default(1)
if(@@ERROR<>0) BEGIN
	ROLLBACK
	RAISERROR('',16,1)
	RETURN
END


-- ------  end payload  --------------------------------------------------------------------------------------
INSERT INTO components
	(name, veri)
VALUES(@comp_name, @comp_ver)
if(@@ERROR<>0) BEGIN
	ROLLBACK
	RAISERROR('',16,1)
	RETURN
END
COMMIT
print '**** Component:   ' + @comp_name + '   v: ' + CAST(@comp_ver as varchar(128)) + '    OK'
GO
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< end step <<



--#########################################################################################################
if(@@ERROR<>0) BEGIN
	RAISERROR('Script terminated...',16,1)
	return
END
DECLARE @comp_ver int
DECLARE @comp_name varchar(128)
SET @comp_ver = 230
SET @comp_name = 'MediaPlannerComponent'

IF EXISTS( SELECT *
FROM components
WHERE name = @comp_name and veri >= @comp_ver) 
    BEGIN
	print 'Component: '+ @comp_name + ' already updated to version '  +   CAST(@comp_ver as varchar(128))+ ' or more'
	return
END

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_mediaplan_scheds' AND syscolumns.name='tariff_scales_id')
BEGIN
	ALTER TABLE mp_mediaplan_scheds ADD tariff_scales_id int null;
END
if(@@ERROR<>0) BEGIN
	ROLLBACK
	RAISERROR('',16,1)
	RETURN
END


-- ------  end payload  --------------------------------------------------------------------------------------
INSERT INTO components
	(name, veri)
VALUES(@comp_name, @comp_ver)
if(@@ERROR<>0) BEGIN
	ROLLBACK
	RAISERROR('',16,1)
	RETURN
END
COMMIT
print '**** Component:   ' + @comp_name + '   v: ' + CAST(@comp_ver as varchar(128)) + '    OK'
GO
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< end step <<

--#########################################################################################################
if(@@ERROR<>0) BEGIN
	RAISERROR('Script terminated...',16,1)
	return
END
DECLARE @comp_ver int
DECLARE @comp_name varchar(128)
SET @comp_ver = 240
SET @comp_name = 'MediaPlannerComponent'

IF EXISTS( SELECT *
FROM components
WHERE name = @comp_name and veri >= @comp_ver) 
    BEGIN
	print 'Component: '+ @comp_name + ' already updated to version '  +   CAST(@comp_ver as varchar(128))+ ' or more'
	return
END

SET XACT_ABORT ON
BEGIN TRAN


ALTER TABLE mp_mediaplan_scheds  WITH CHECK ADD  CONSTRAINT FK_mp_mediaplan_scheds_mp_tariff_scales FOREIGN KEY(tariff_scales_id)
REFERENCES mp_tariff_scales (id)
ON UPDATE CASCADE
ON DELETE SET NULL

ALTER TABLE mp_mediaplan_scheds CHECK CONSTRAINT FK_mp_mediaplan_scheds_mp_tariff_scales

-- ------  end payload  --------------------------------------------------------------------------------------
INSERT INTO components
	(name, veri)
VALUES(@comp_name, @comp_ver)
if(@@ERROR<>0) BEGIN
	ROLLBACK
	RAISERROR('',16,1)
	RETURN
END
COMMIT
print '**** Component:   ' + @comp_name + '   v: ' + CAST(@comp_ver as varchar(128)) + '    OK'
GO
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< end step <<


--#########################################################################################################
if(@@ERROR<>0) BEGIN
	RAISERROR('Script terminated...',16,1)
	return
END
DECLARE @comp_ver int
DECLARE @comp_name varchar(128)
SET @comp_ver = 250
SET @comp_name = 'MediaPlannerComponent'

IF EXISTS( SELECT *
FROM components
WHERE name = @comp_name and veri >= @comp_ver) 
    BEGIN
	print 'Component: '+ @comp_name + ' already updated to version '  +   CAST(@comp_ver as varchar(128))+ ' or more'
	return
END

SET XACT_ABORT ON
BEGIN TRAN

update mp_mediaplan_scheds set mp_mediaplan_scheds.tariff_scales_id = (select tariff_scales_id
from mp_mediaplans
where id=mp_mediaplan_scheds.mediaplan_id)


-- ------  end payload  --------------------------------------------------------------------------------------
INSERT INTO components
	(name, veri)
VALUES(@comp_name, @comp_ver)
if(@@ERROR<>0) BEGIN
	ROLLBACK
	RAISERROR('',16,1)
	RETURN
END
COMMIT
print '**** Component:   ' + @comp_name + '   v: ' + CAST(@comp_ver as varchar(128)) + '    OK'
GO
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< end step <<


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

DECLARE @ver int
SET @ver = 260

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
------------------

ALTER TABLE mp_discount_types
ADD base_discount_id int NULL

ALTER TABLE mp_discount_values
ADD use_in_max_discount_calculation bit

------------------
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 = 270

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
------------------

UPDATE mp_discount_types
SET mp_discount_types.base_discount_id = mp_discount_types.id
WHERE id >= 1 AND id <= 8

DECLARE @id int
SET @id = (SELECT MAX(id)
FROM mp_discount_types) + 1

INSERT INTO mp_discount_types
VALUES
	(@id, 'Скидка для бонусного проката', 0, 9)

UPDATE mp_discount_values
SET use_in_max_discount_calculation=1
WHERE type_id<>@id

------------------
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

--#########################################################################################################
--обновление mp_used_discounts 1
if(@@ERROR<>0)
BEGIN
	RAISERROR('Script terminated...',16,1)
	return
END

DECLARE @ver int
SET @ver = 290

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_used_discounts' AND syscolumns.name='schedule_id')
BEGIN
	ALTER TABLE mp_used_discounts ADD schedule_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

--#########################################################################################################
--обновление mp_used_discounts 2
if(@@ERROR<>0)
BEGIN
	RAISERROR('Script terminated...',16,1)
	return
END

DECLARE @ver int
SET @ver = 295

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 schedule_id
FROM mp_used_discounts
WHERE schedule_id IS NULL )
BEGIN
	--1
	update mp_used_discounts set schedule_id = (select mp_mediaplans.schedules_id
	from mp_mediaplans
	where mp_mediaplans.id = mediaplans_id)
	if(@@ERROR<>0) BEGIN
		ROLLBACK
		RAISERROR('',16,1)
		RETURN
	END

	--2
	insert mp_used_discounts
		(mediaplans_id, discount_values_id, manager_value, manager_value_f, schedule_id)
	select mp_used_discounts.mediaplans_id as mediaplans_id,
		mp_used_discounts.discount_values_id as discount_values_id,
		mp_used_discounts.manager_value as manager_value,
		mp_used_discounts.manager_value_f as manager_value_f,
		mp_mediaplan_scheds.schedule_id as schedule_id
	from mp_mediaplan_scheds inner join mp_used_discounts
		on mp_used_discounts.mediaplans_id = mp_mediaplan_scheds.mediaplan_id
	where mp_used_discounts.schedule_id = -1 and mp_mediaplan_scheds.schedule_id <> -1
	if(@@ERROR<>0) BEGIN
		ROLLBACK
		RAISERROR('',16,1)
		RETURN
	END

	--3
	delete from mp_used_discounts where schedule_id=-1

	--4
	update mp_used_discounts set schedule_id = (select schedules_id
	from mp_mediaplans
	where id = mp_used_discounts.mediaplans_id) where schedule_id is null

	if(@@ERROR<>0) BEGIN
		ROLLBACK
		RAISERROR('',16,1)
		RETURN
	END
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


--#########################################################################################################
--обновление mp_used_discounts 3
if(@@ERROR<>0)
BEGIN
	RAISERROR('Script terminated...',16,1)
	return
END

DECLARE @ver int
SET @ver = 300

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
------------------

ALTER TABLE mp_used_discounts  WITH CHECK ADD  CONSTRAINT FK_mp_used_discounts_schedules FOREIGN KEY(schedule_id)
REFERENCES SCHEDULES (id)
ON UPDATE CASCADE
ON DELETE SET NULL

ALTER TABLE mp_used_discounts CHECK CONSTRAINT FK_mp_used_discounts_schedules
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 = 310

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
------------------

ALTER TABLE mp_used_discounts
ADD apply_type smallint NULL

ALTER TABLE mp_covenantees
ADD discount_type smallint NOT NULL DEFAULT 0, 
    discount_value float NOT NULL DEFAULT 0

DECLARE @id int
SET @id = (SELECT MAX(id)
FROM mp_discount_types) + 1

INSERT INTO mp_discount_types
VALUES
	(@id, 'Скидка контрагента', 0, 10)

------------------
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 = 320

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
------------------

ALTER TABLE mp_covenantees
ADD use_discount bit NOT NULL DEFAULT 0

ALTER TABLE mp_used_discounts
ADD discount_enabled bit NOT NULL DEFAULT 1

------------------
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 = 330

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
------------------

ALTER TABLE mp_spot_types
ADD template int NOT NULL DEFAULT 0

------------------
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 = 340

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
------------------

ALTER TABLE mp_mediaplans ALTER COLUMN name nvarchar(1024)

------------------
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 = 350

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
------------------

ALTER TABLE locked_objects ALTER COLUMN obj_name nvarchar(1024)

------------------
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 = 360

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_schedule_tariff_scale' AND TYPE='U')
BEGIN
	create table mp_schedule_tariff_scale
	(
		id int identity NOT NULL,
		schedule_id int unique NOT NULL,
		tariff_scale_id int unique NOT NULL,
		constraint PK_MP_SCHEDULE_TARIFF_SCALE
			primary key clustered (id),
		constraint FK_MP_SCHEDULE_TARIFF_SCALE_SCHEDULE
			foreign key (schedule_id) references SCHEDULES(id)
			on delete cascade,
		constraint FK_MP_SCHEDULE_TARIFF_SCALE_MP_TARIFF_SCALES 
			foreign key (tariff_scale_id) references mp_tariff_scales(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 = 370

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 sysobjects.id
FROM sysobjects
WHERE sysobjects.name='mp_schedule_tariff_scale' AND TYPE='U')
BEGIN

	DECLARE @Results TABLE
(
		Name nvarchar(100)
);
	INSERT INTO @Results
	SELECT sys.objects.name
	FROM sys.objects 
cross join sys.columns
	WHERE type = 'UQ' AND OBJECT_NAME(parent_object_id) = N'mp_schedule_tariff_scale' AND sys.columns.name = 'tariff_scale_id'

	DECLARE @Name NVARCHAR(MAX)
	WHILE EXISTS (SELECT 1
	FROM @Results)
  BEGIN
		-- Get the top record
		SELECT TOP 1
			@Name = r.Name
		FROM
			@Results r

		-- This is where the work will be done
		--Insert/Update
		DECLARE @Command NVARCHAR(MAX)
		SELECT @Command ='Alter Table dbo.mp_schedule_tariff_scale Drop Constraint [' + @Name + ']'
		exec sp_executesql @Command
		-- Remove the record
		DELETE FROM @Results WHERE Name = @Name
	END
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 = 380

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
------------------

UPDATE mp_discount_values
   SET use_in_max_discount_calculation = 1
  FROM mp_discount_values
	INNER JOIN mp_discount_types ON mp_discount_values.type_id = mp_discount_types.id
  WHERE mp_discount_types.base_discount_id <> 9

UPDATE mp_discount_values
   SET use_in_max_discount_calculation = 0
  FROM mp_discount_values
	INNER JOIN mp_discount_types ON mp_discount_values.type_id = mp_discount_types.id
  WHERE mp_discount_types.base_discount_id = 9

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 = 390

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
------------------

ALTER TABLE mp_positions ADD block_time time(7) NULL
ALTER TABLE mp_positions ADD clock_id int NULL

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 = 400

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
------------------

ALTER TABLE mp_mediaplans ADD creation_date datetime NULL

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 = 410

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
------------------

--добавляем скидки для бонусного проката во все тарифные сетки (если таких скидок нет)
insert into mp_discount_values
	(tariff_scales_id, default_value, min_value, max_value, apply_method, name, type_id, apply_type, combination_type, default_value_f, min_value_f, max_value_f, use_in_max_discount_calculation)
select tariff_scales_id, 100, 100, 100, 0, 'Скидка для бонусного проката', 9, 0, 0, 100, 100, 100, 0
from (
  select tariff_scales_id
	from mp_discount_values as dv
	where not exists (select id
	from mp_discount_values
	where type_id=9 and dv.tariff_scales_id=tariff_scales_id)
	group by tariff_scales_id
  ) 
  as tbl

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 = 420

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
------------------

--новая таблица для дэйпартов
create table mp_dayparts
(
	id int identity,
	name varchar(1000) not null,
	tariff_scales_id int not null,
	time_start time null,
	time_end time null,
	block_types int not null,
	week_days int not null,
	hide_empty_rows bit not null,
	hide_empty_columns bit not null,
	constraint PK_MP_DAYPARTS primary key nonclustered (id)
)

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 = 430

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
------------------

--новая колонка для медиплана - расширенное имя (описание)
ALTER TABLE mp_mediaplans ADD name_detailed varchar(1024) NULL

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 = 440

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
------------------

ALTER TABLE mp_position_replacement ADD sort_position int not null default 0

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 = 450

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
------------------

ALTER TABLE mp_tariff_scales ADD default_payment_type_id int NULL

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 = 460

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
------------------

ALTER TABLE mp_covenantees ADD ID_Number nvarchar(128) NULL

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 = 470

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
------------------

CREATE UNIQUE INDEX covenantee_id_number_uniq ON mp_covenantees(ID_Number)
WHERE mp_covenantees.ID_Number IS NOT NULL

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 = 480

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
------------------

ALTER TABLE mp_phonograms ADD broadcast_id int NULL

ALTER TABLE mp_phonograms
add constraint PK_MP_PHONOGRAMS_BROADCASTS foreign key (broadcast_id)
references BROADCASTS (id)
on update cascade

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 = 490

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
------------------

ALTER TABLE mp_base_costs ADD broadcast_id int NULL

ALTER TABLE mp_base_costs
add constraint PK_MP_BASE_COST_BROADCASTS foreign key (broadcast_id)
references BROADCASTS (id)
on update cascade

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 = 500

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_phonograms' AND syscolumns.name='template_type')
BEGIN
	ALTER TABLE mp_phonograms ADD template_type int DEFAULT(0);

	UPDATE mp_spot_types SET template=show_type WHERE system_type=1
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 = 510

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 syscolumns.name
FROM syscolumns
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id
WHERE sysobjects.name='mp_phonograms' AND syscolumns.name='template_type')
BEGIN

	UPDATE mp_phonograms SET mp_phonograms.template_type = (SELECT template
	FROM mp_spot_types
	WHERE mp_spot_types.show_type = mp_phonograms.type)

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 = 520

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
------------------

ALTER TABLE mp_organizations ADD last_sync datetime NULL
-- хранит последний дату последней синхронизации (с bpm)

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 = 530

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
------------------

ALTER TABLE mp_position_replacement ADD discounts_enum nvarchar(128) NULL

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 = 540

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_base_costs' AND syscolumns.name='holiday')
BEGIN
	ALTER TABLE mp_base_costs ADD holiday bit NULL DEFAULT(0);
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 = 550

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_tariff_scales' AND syscolumns.name='hide')
BEGIN
	ALTER TABLE mp_tariff_scales ADD hide bit NULL DEFAULT(0);
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 = 560

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 sysindexes.id
from sysindexes
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects on sysobjects.id   = syscolumns.id
where sysindexes.name='FK_MP_POSITION_REPLACEMENT_VS_MP_POSITION' AND sysobjects.name='mp_position_replacement'
)
BEGIN
	create index FK_MP_POSITION_REPLACEMENT_VS_MP_POSITION on mp_position_replacement (position_id ASC)
END
if(@@ERROR<>0) BEGIN
	ROLLBACK
	RAISERROR('',16,1)
	RETURN
END

IF NOT EXISTS (
	select sysindexes.id
from sysindexes
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects on sysobjects.id   = syscolumns.id
where sysindexes.name='FK_MP_USED_DISCOUNTS_VS_MP_DISCOUNT_VALUES' AND sysobjects.name='mp_used_discounts'
)
BEGIN
	create index FK_MP_USED_DISCOUNTS_VS_MP_DISCOUNT_VALUES on mp_used_discounts (discount_values_id ASC)
END
if(@@ERROR<>0) BEGIN
	ROLLBACK
	RAISERROR('',16,1)
	RETURN
END


IF NOT EXISTS (
	select sysindexes.id
from sysindexes
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects on sysobjects.id   = syscolumns.id
where sysindexes.name='FK_MP_DISCOUNT_VALUES_VS_MP_TYPE_ID' AND sysobjects.name='mp_discount_values'
)
BEGIN
	create index FK_MP_DISCOUNT_VALUES_VS_MP_TYPE_ID on mp_discount_values (type_id ASC)
END
if(@@ERROR<>0) BEGIN
	ROLLBACK
	RAISERROR('',16,1)
	RETURN
END


IF NOT EXISTS (
	select sysindexes.id
from sysindexes
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects on sysobjects.id   = syscolumns.id
where sysindexes.name='FK_MP_PAYMENTS_VS_MP_ACCOUNTS' AND sysobjects.name='mp_payments'
)
BEGIN
	create index FK_MP_PAYMENTS_VS_MP_ACCOUNTS on mp_payments (account_id ASC)
END
if(@@ERROR<>0) BEGIN
	ROLLBACK
	RAISERROR('',16,1)
	RETURN
END

IF NOT EXISTS (
	select sysindexes.id
from sysindexes
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects on sysobjects.id   = syscolumns.id
where sysindexes.name='FK_MP_MEDIAPLAN_SCHEDS_VS_MP_MEDIAPLANS' AND sysobjects.name='mp_mediaplan_scheds'
)
BEGIN
	create index FK_MP_PAYMENTS_VS_MP_ACCOUNTS on mp_mediaplan_scheds (mediaplan_id ASC)
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 = 570

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
------------------

ALTER TABLE mp_reports ADD processor_type int DEFAULT(0) NOT NULL;
ALTER TABLE mp_reports ADD external_processor_name varchar(128) NULL;

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 = 580

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
------------------

UPDATE mp_tariff_scales set name=CONVERT(nvarchar, begin_date) where name is null

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 = 590

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
------------------

ALTER TABLE mp_positions ADD discounts_enum nvarchar(128) NULL;
ALTER TABLE mp_positions ADD sort_position int DEFAULT(0) NOT NULL;

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 = 600

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
------------------

ALTER TABLE mp_mediaplans ADD mode int DEFAULT(0) NOT NULL;

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 = 610

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
------------------

ALTER TABLE mp_mediaplans ADD last_position_changed_time datetime NULL;

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 = 620

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
------------------

ALTER TABLE mp_mediaplans ADD force_simplified_state_check_mode int NOT NULL DEFAULT(0);

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


--MediaIntegrationApi
--#########################################################################################################

-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- begin step: Add Remote Players Tables
IF (@@ERROR <> 0)
    BEGIN
        RAISERROR ('Script terminated...',16,1)
        RETURN
    END
DECLARE @comp_ver  int
DECLARE @comp_name varchar(128)
SET @comp_ver = 10
SET @comp_name = 'MediaIntegrationApi'

IF EXISTS(SELECT *
          FROM components
          WHERE name = @comp_name
            AND veri >= @comp_ver)
    BEGIN
        PRINT 'Component: ' + @comp_name + ' already updated to version ' + CAST(@comp_ver AS varchar(128)) + ' or more'
        RETURN
    END

SET XACT_ABORT ON
BEGIN TRAN
-- ++++++  begin payload  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

IF NOT EXISTS(
        SELECT sysobjects.id
        FROM sysobjects
        WHERE sysobjects.name = 'dmdb_Players'
          AND TYPE = 'U')
    BEGIN
        CREATE TABLE dmdb_Players
        (
            id              INT          NOT NULL IDENTITY (1,1) PRIMARY KEY,
            apiUserId       INT          NULL FOREIGN KEY REFERENCES ApiUsers (id),
            scheduleId      INT          NULL FOREIGN KEY REFERENCES SCHEDULES (id) ON DELETE CASCADE,
			extPlayerId		NVARCHAR(128) NULL,
            name            NVARCHAR(512) NULL,
            extName         NVARCHAR(512) NULL,
			description		NVARCHAR(4000) NULL,
            activated		INT          DEFAULT 0,  /*syncEnabled*/
			playbackEnabled INT          DEFAULT 0,
            requestPeriod   INT          DEFAULT 60, /*syncPeriod*/
            schedulePeriodOffset  INT    DEFAULT 0,
			scheduleLiveTime  INT		 DEFAULT 1,
            stamp           TIMESTAMP    NOT NULL
        );
        CREATE NONCLUSTERED INDEX IX_dmdb_Players_timestamp ON dmdb_Players (stamp);
    END


IF NOT EXISTS(
        SELECT sysobjects.id
        FROM sysobjects
        WHERE sysobjects.name = 'dmdb_PlayerStates'
          AND TYPE = 'U')
    BEGIN
        CREATE TABLE dmdb_PlayerStates
        (
            id       INT       NOT NULL IDENTITY (1,1) PRIMARY KEY,
            playerId INT       NOT NULL UNIQUE FOREIGN KEY REFERENCES dmdb_Players (id) ON DELETE CASCADE,
            lastSync DATETIME2 NULL,
            stamp    TIMESTAMP NOT NULL
        );
        CREATE NONCLUSTERED INDEX IX_dmdb_PlayerStates_timestamp ON dmdb_PlayerStates (stamp);
    END


IF NOT EXISTS(
        SELECT sysobjects.id
        FROM sysobjects
        WHERE sysobjects.name = 'dmdb_PlayerProfiles'
          AND TYPE = 'U')
    BEGIN
        CREATE TABLE dmdb_PlayerProfiles
        (
            id           INT           NOT NULL IDENTITY (1,1) PRIMARY KEY,
            apiUserId    INT           NULL FOREIGN KEY REFERENCES ApiUsers (id),
            name         NVARCHAR(512) NULL,
            address      NVARCHAR(MAX) NULL,
            pathToRootId NVARCHAR(MAX) NULL,
            stamp        TIMESTAMP     NOT NULL
        );
        CREATE NONCLUSTERED INDEX IX_dmdb_PlayerProfiles_timestamp ON dmdb_PlayerProfiles (stamp);
    END


IF NOT EXISTS(
        SELECT sysobjects.id
        FROM sysobjects
        WHERE sysobjects.name = 'dmdb_Player_PlayerProfile'
          AND TYPE = 'U')
    BEGIN
        CREATE TABLE dmdb_Player_PlayerProfile
        (
            id              INT       NOT NULL IDENTITY (1,1) PRIMARY KEY,
            playerId        INT       NOT NULL FOREIGN KEY REFERENCES dmdb_Players (id) ON DELETE CASCADE,
            playerProfileId INT       NOT NULL FOREIGN KEY REFERENCES dmdb_PlayerProfiles (id) ON DELETE CASCADE,
            stamp           TIMESTAMP NOT NULL
        );
        CREATE NONCLUSTERED INDEX IX_dmdb_Player_PlayerProfile_timestamp ON dmdb_Player_PlayerProfile (stamp);
    END


IF NOT EXISTS(
        SELECT sysobjects.id
        FROM sysobjects
        WHERE sysobjects.name = 'dmdb_Player_MediaPlan'
          AND TYPE = 'U')
    BEGIN
        CREATE TABLE dmdb_Player_MediaPlan
        (
            id          INT       NOT NULL IDENTITY (1,1) PRIMARY KEY,
            playerId    INT       NOT NULL FOREIGN KEY REFERENCES dmdb_Players (id) ON DELETE CASCADE,
            mediaPlanId INT       NOT NULL FOREIGN KEY REFERENCES mp_mediaplans (id) ON DELETE CASCADE,
            stamp       TIMESTAMP NOT NULL
        );
        CREATE NONCLUSTERED INDEX IX_dmdb_Player_MediaPlan_timestamp ON dmdb_Player_MediaPlan (stamp);
    END


IF NOT EXISTS(
        SELECT sysobjects.id
        FROM sysobjects
        WHERE sysobjects.name = 'dmdb_PlayerProfile_MediaPlan'
          AND TYPE = 'U')
    BEGIN
        CREATE TABLE dmdb_PlayerProfile_MediaPlan
        (
            id              INT       NOT NULL IDENTITY (1,1) PRIMARY KEY,
            playerProfileId INT       NOT NULL FOREIGN KEY REFERENCES dmdb_PlayerProfiles (id) ON DELETE CASCADE,
            mediaPlanId     INT       NOT NULL FOREIGN KEY REFERENCES mp_mediaplans (id) ON DELETE CASCADE,
            stamp           TIMESTAMP NOT NULL
        );
        CREATE NONCLUSTERED INDEX IX_dmdb_PlayerProfile_MediaPlan_timestamp ON dmdb_PlayerProfile_MediaPlan (stamp);
    END

	
ALTER TABLE mp_mediaplans
    ADD ignoreByMediaPlaner BIT NOT NULL DEFAULT 0;

ALTER TABLE PH_PLAY_HISTORY ADD playerId INT NULL DEFAULT NULL;
ALTER TABLE PH_PLAY_HISTORY ADD playBlockId INT NULL DEFAULT NULL;
	

-- ------  end payload  --------------------------------------------------------------------------------------
INSERT INTO components
	(name, veri)
VALUES(@comp_name, @comp_ver)
if(@@ERROR<>0) BEGIN
	ROLLBACK
	RAISERROR('',16,1)
	RETURN
END
COMMIT
print '**** Component:   ' + @comp_name + '   v: ' + CAST(@comp_ver as varchar(128)) + '    OK'
GO
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< end step <<


-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- begin step: Add Remote Players Tables
IF (@@ERROR <> 0)
    BEGIN
        RAISERROR ('Script terminated...',16,1)
        RETURN
    END
DECLARE @comp_ver  int
DECLARE @comp_name varchar(128)
SET @comp_ver = 20
SET @comp_name = 'MediaIntegrationApi'

IF EXISTS(SELECT *
          FROM components
          WHERE name = @comp_name
            AND veri >= @comp_ver)
    BEGIN
        PRINT 'Component: ' + @comp_name + ' already updated to version ' + CAST(@comp_ver AS varchar(128)) + ' or more'
        RETURN
    END

SET XACT_ABORT ON
BEGIN TRAN
-- ++++++  begin payload  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


ALTER TABLE dmdb_PlayerStates ADD fullLogUploadTime DATETIME2 NULL DEFAULT NULL;
	

-- ------  end payload  --------------------------------------------------------------------------------------
INSERT INTO components
	(name, veri)
VALUES(@comp_name, @comp_ver)
if(@@ERROR<>0) BEGIN
	ROLLBACK
	RAISERROR('',16,1)
	RETURN
END
COMMIT
print '**** Component:   ' + @comp_name + '   v: ' + CAST(@comp_ver as varchar(128)) + '    OK'
GO
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< end step <<

-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- begin step: Add Remote Players Tables
IF (@@ERROR <> 0)
    BEGIN
        RAISERROR ('Script terminated...',16,1)
        RETURN
    END
DECLARE @comp_ver  int
DECLARE @comp_name varchar(128)
SET @comp_ver = 30
SET @comp_name = 'MediaIntegrationApi'

IF EXISTS(SELECT *
          FROM components
          WHERE name = @comp_name
            AND veri >= @comp_ver)
    BEGIN
        PRINT 'Component: ' + @comp_name + ' already updated to version ' + CAST(@comp_ver AS varchar(128)) + ' or more'
        RETURN
    END

SET XACT_ABORT ON
BEGIN TRAN
-- ++++++  begin payload  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

ALTER TABLE dmdb_Players ADD remoteName NVARCHAR(512) NULL;
ALTER TABLE dmdb_Players ADD ipAddress NVARCHAR(16) NULL;
ALTER TABLE dmdb_Players ADD macAddress NVARCHAR(32) NULL;
ALTER TABLE dmdb_Players ADD version NVARCHAR(32) NULL;
	

-- ------  end payload  --------------------------------------------------------------------------------------
INSERT INTO components
	(name, veri)
VALUES(@comp_name, @comp_ver)
if(@@ERROR<>0) BEGIN
	ROLLBACK
	RAISERROR('',16,1)
	RETURN
END
COMMIT
print '**** Component:   ' + @comp_name + '   v: ' + CAST(@comp_ver as varchar(128)) + '    OK'
GO
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< end step <<

-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
IF (@@ERROR <> 0)
    BEGIN
        RAISERROR ('Script terminated...',16,1)
        RETURN
    END
DECLARE @comp_ver  int
DECLARE @comp_name varchar(128)
SET @comp_ver = 40
SET @comp_name = 'MediaIntegrationApi'

IF EXISTS(SELECT *
          FROM components
          WHERE name = @comp_name
            AND veri >= @comp_ver)
    BEGIN
        PRINT 'Component: ' + @comp_name + ' already updated to version ' + CAST(@comp_ver AS varchar(128)) + ' or more'
        RETURN
    END

SET XACT_ABORT ON
BEGIN TRAN
-- ++++++  begin payload  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

ALTER TABLE dmdb_Players ADD enableLog int default 0;

-- ------  end payload  --------------------------------------------------------------------------------------
INSERT INTO components
	(name, veri)
VALUES(@comp_name, @comp_ver)
if(@@ERROR<>0) BEGIN
	ROLLBACK
	RAISERROR('',16,1)
	RETURN
END
COMMIT
print '**** Component:   ' + @comp_name + '   v: ' + CAST(@comp_ver as varchar(128)) + '    OK'
GO
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< end step <<

-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
IF (@@ERROR <> 0)
    BEGIN
        RAISERROR ('Script terminated...',16,1)
        RETURN
    END
DECLARE @comp_ver  int
DECLARE @comp_name varchar(128)
SET @comp_ver = 50
SET @comp_name = 'MediaIntegrationApi'

IF EXISTS(SELECT *
          FROM components
          WHERE name = @comp_name
            AND veri >= @comp_ver)
    BEGIN
        PRINT 'Component: ' + @comp_name + ' already updated to version ' + CAST(@comp_ver AS varchar(128)) + ' or more'
        RETURN
    END

SET XACT_ABORT ON
BEGIN TRAN
-- ++++++  begin payload  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

ALTER TABLE dmdb_Players ADD updateMode int default 0;
ALTER TABLE dmdb_Players ADD utcOffset int default 0;
ALTER TABLE dmdb_Players ADD activeTimeBegin int default 0;
ALTER TABLE dmdb_Players ADD activeTimeEnd int default 0;

-- ------  end payload  --------------------------------------------------------------------------------------
INSERT INTO components
	(name, veri)
VALUES(@comp_name, @comp_ver)
if(@@ERROR<>0) BEGIN
	ROLLBACK
	RAISERROR('',16,1)
	RETURN
END
COMMIT
print '**** Component:   ' + @comp_name + '   v: ' + CAST(@comp_ver as varchar(128)) + '    OK'
GO
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< end step <<