--drop surname
IF EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'surname'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
    ALTER TABLE pal_Setup
	DROP COLUMN surname; 
END


--alter name
IF EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'name'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
	ALTER TABLE pal_Setup
	DROP COLUMN name; 
END
IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'primaryPerson'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
    ALTER TABLE pal_Setup
	ADD primaryPerson VARCHAR(350); 
END


--add communicationEmail
IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'communicationEmail'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
    ALTER TABLE pal_Setup
	ADD communicationEmail VARCHAR(350); 
END


--add sendingEmailName
IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'sendingEmailName'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
    ALTER TABLE pal_Setup
	ADD sendingEmailName VARCHAR(350); 
END



IF EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'bankName'
      AND Object_ID = Object_ID(N'pal_Customer'))
BEGIN
    ALTER TABLE pal_Customer
	DROP COLUMN bankName 
END


IF EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'bankAccountNumber'
      AND Object_ID = Object_ID(N'pal_Customer'))
BEGIN
    ALTER TABLE pal_Customer
	DROP COLUMN bankAccountNumber 
END


IF EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'bankBranch'
      AND Object_ID = Object_ID(N'pal_Customer'))
BEGIN
    ALTER TABLE pal_Customer
	DROP COLUMN bankBranch 
END


IF EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'bankBranchCode'
      AND Object_ID = Object_ID(N'pal_Customer'))
BEGIN
    ALTER TABLE pal_Customer
	DROP COLUMN bankBranchCode 
END


IF EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'bankSwift'
      AND Object_ID = Object_ID(N'pal_Customer'))
BEGIN
    ALTER TABLE pal_Customer
	DROP COLUMN bankSwift 
END




--drop surname
IF EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'surname'
      AND Object_ID = Object_ID(N'pal_Customer'))
BEGIN
    ALTER TABLE pal_Customer
	DROP COLUMN surname; 
END


--alter name
IF EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'name'
      AND Object_ID = Object_ID(N'pal_Customer'))
BEGIN
	ALTER TABLE pal_Customer
	DROP COLUMN name; 
END
IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'primaryPerson'
      AND Object_ID = Object_ID(N'pal_Customer'))
BEGIN
    ALTER TABLE pal_Customer
	ADD primaryPerson VARCHAR(350); 
END


--add communicationEmail
IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'communicationEmail'
      AND Object_ID = Object_ID(N'pal_Customer'))
BEGIN
    ALTER TABLE pal_Customer
	ADD communicationEmail VARCHAR(350); 
END


--add tradingAs
IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'tradingAs'
      AND Object_ID = Object_ID(N'pal_Customer'))
BEGIN
    ALTER TABLE pal_Customer
	ADD tradingAs VARCHAR(350); 
END



--add sendingEmailName
IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'sendingEmailName'
      AND Object_ID = Object_ID(N'pal_Customer'))
BEGIN
    ALTER TABLE pal_Customer
	ADD sendingEmailName VARCHAR(350); 
END





--rename sale start number
IF EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'saleQTEStartNum'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
	ALTER TABLE pal_Setup
	DROP COLUMN saleQTEStartNum; 
END
IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'saleQTENextNum'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
    ALTER TABLE pal_Setup
	ADD saleQTENextNum INT; 
END
IF EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'saleINVStartNum'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
	ALTER TABLE pal_Setup
	DROP COLUMN saleINVStartNum; 
END
IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'saleINVNextNum'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
    ALTER TABLE pal_Setup
	ADD saleINVNextNum INT; 
END
IF EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'saleRCTStartNum'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
	ALTER TABLE pal_Setup
	DROP COLUMN saleRCTStartNum; 
END
IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'saleRCTNextNum'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
    ALTER TABLE pal_Setup
	ADD saleRCTNextNum INT; 
END
IF EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'saleCRNStartNum'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
	ALTER TABLE pal_Setup
	DROP COLUMN saleCRNStartNum; 
END
IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'saleCRNNextNum'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
    ALTER TABLE pal_Setup
	ADD saleCRNNextNum INT; 
END





--add sale number length
IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'saleQTENumLength'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
    ALTER TABLE pal_Setup
	ADD saleQTENumLength INT; 
END
IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'saleINVNumLength'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
    ALTER TABLE pal_Setup
	ADD saleINVNumLength INT; 
END
IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'saleRCTNumLength'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
    ALTER TABLE pal_Setup
	ADD saleRCTNumLength INT; 
END
IF NOT EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name      = N'saleCRNNumLength'
      AND Object_ID = Object_ID(N'pal_Setup'))
BEGIN
    ALTER TABLE pal_Setup
	ADD saleCRNNumLength INT; 
END




--recreate procedure - sales numbering
IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'[dbo].[sp_GetSalesData]') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    DROP PROCEDURE [dbo].[sp_GetSalesData]
END
IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'[dbo].[sp_GetSalesQuotesToAllocate]') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    DROP PROCEDURE [dbo].[sp_GetSalesQuotesToAllocate]
END
IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'[dbo].[sp_GetSalesInvoicesToAllocate]') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    DROP PROCEDURE [dbo].[sp_GetSalesInvoicesToAllocate]
END