--Source Path field
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'pal_Attachment' AND COLUMN_NAME = 'sourcePath')
BEGIN
	Alter Table pal_Attachment
	Add sourcePath varchar(350) NULL
END

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'pal_TaskSnapshot' AND COLUMN_NAME = 'sourcePath')
BEGIN
	Alter Table pal_TaskSnapshot
	Add sourcePath varchar(350) NULL
END

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'pal_TaskSnapshot' AND COLUMN_NAME = 'customerCode')
BEGIN
	Alter Table pal_TaskSnapshot
	Add customerCode varchar(10) NULL
END



IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[pal_Project]')
 AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
 BEGIN
CREATE TABLE [dbo].[pal_Project](
	[recId] [int] IDENTITY(1,1) NOT NULL,
	[customerCode] [varchar](10) NULL,
	[projectCode] [varchar](10) NULL,
	[name] [varchar](350) NULL,
	[description] [varchar](500) NULL,
	[isActive] [bit] NULL,
	[dateCreated] [datetime] NULL,
	[dateUpdated] [datetime] NULL,
 CONSTRAINT [PK_pal_Project] PRIMARY KEY CLUSTERED 
(
	[recId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

END



--Project Number Table
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[pal_ProjectNumber]')
 AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
 BEGIN
	CREATE TABLE [dbo].[pal_ProjectNumber](
		[recId] [int] IDENTITY(1,1) NOT NULL,
		[custCode] [varchar](10) NULL,
		[prefix] [varchar](10) NULL,
		[lastNumberUsed] [int] NULL,
	 CONSTRAINT [PK_pal_ProjectNumber] PRIMARY KEY CLUSTERED 
	(
		[recId] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
END

--Task Number Table
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[pal_TaskNumber]')
 AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
 BEGIN
	CREATE TABLE [dbo].[pal_TaskNumber](
		[recId] [int] IDENTITY(1,1) NOT NULL,
		[custCode] [varchar](10) NULL,
		[projCode] [varchar](10) NULL,
		[prefix] [varchar](10) NULL,
		[lastNumberUsed] [int] NULL,
	 CONSTRAINT [PK_pal_TaskNumber] PRIMARY KEY CLUSTERED 
	(
		[recId] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
END


--Task Fields

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'pal_Task' AND COLUMN_NAME = 'customerCode')
BEGIN
	Alter Table pal_Task
	Add customerCode varchar(10) NULL
END

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'pal_Task' AND COLUMN_NAME = 'projectCode')
BEGIN
	Alter Table pal_Task
	Add projectCode varchar(10) NULL
END


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'pal_TaskHistory' AND COLUMN_NAME = 'projectCode')
BEGIN
	Alter Table pal_TaskHistory
	Add projectCode varchar(10) NULL
END

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'pal_TaskHistory' AND COLUMN_NAME = 'customerCode')
BEGIN
	Alter Table pal_TaskHistory
	Add customerCode varchar(10) NULL
END





IF EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_Task_ProjectSurfaceItemId')
BEGIN
	DROP INDEX [IX_Task_ProjectSurfaceItemId] ON [dbo].[pal_Task]
	Update Statistics pal_Task
END

IF EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_TaskHistory_ProjectSurfaceItemId')
BEGIN
	DROP INDEX [IX_TaskHistory_ProjectSurfaceItemId] ON [dbo].[pal_TaskHistory]
	Update Statistics pal_TaskHistory
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'pal_Task' AND COLUMN_NAME = 'projectSurfaceItemId')
BEGIN
	Alter Table pal_Task
	Drop Column projectSurfaceItemId
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'pal_TaskHistory' AND COLUMN_NAME = 'projectSurfaceItemId')
BEGIN
	Alter Table pal_TaskHistory
	Drop Column projectSurfaceItemId
END