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

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

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

DECLARE @ver int
SET @ver = 10

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

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

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

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

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

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


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

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

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

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

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

DECLARE @ver int
SET @ver = 20

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

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

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

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

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

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

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

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


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

DECLARE @ver int
SET @ver = 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