-- vd20130813: New table for multiple mailaddresses on customer

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WITH (NOLOCK) 
               WHERE 
                 id = object_id(N'[dbo].[MailAddresses]') AND 
                 OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN

  CREATE TABLE MailAddresses(
    CustomerNumber INTEGER NOT NULL,
    MailAddress VARCHAR(100) NOT NULL,
    MailAddressType TINYINT NOT NULL)

  ALTER TABLE MailAddresses 
    ADD CONSTRAINT DF_MailAddresses_MailAddressType DEFAULT 0 FOR MailAddressType 

  ALTER TABLE MailAddresses ADD CONSTRAINT FK_MailAddresses_CustomerNumberFK 
    FOREIGN KEY (CustomerNumber)
    REFERENCES Customer (CustomerNumber)
  
  DECLARE @CustomerNumber INTEGER
  DECLARE @EMail VARCHAR(100)
  
  DECLARE ConversionCursor Cursor FOR
    SELECT CustomerNumber, EMail FROM Customer WITH (NOLOCK)
  
  OPEN ConversionCursor
  FETCH NEXT FROM ConversionCursor INTO @CustomerNumber, @EMail
  
  WHILE @@Fetch_status = 0
  BEGIN
    IF RTRIM(LTRIM(ISNULL (@EMail, ''))) <> ''
    BEGIN
      INSERT INTO MailAddresses (CustomerNumber, MailAddress, MailAddressType) VALUES (@CustomerNumber, @EMail, 0)	
      INSERT INTO MailAddresses (CustomerNumber, MailAddress, MailAddressType) VALUES (@CustomerNumber, @EMail, 1)	
      INSERT INTO MailAddresses (CustomerNumber, MailAddress, MailAddressType) VALUES (@CustomerNumber, @EMail, 2)	
      INSERT INTO MailAddresses (CustomerNumber, MailAddress, MailAddressType) VALUES (@CustomerNumber, @EMail, 3)
      INSERT INTO MailAddresses (CustomerNumber, MailAddress, MailAddressType) VALUES (@CustomerNumber, @EMail, 4)	
      INSERT INTO MailAddresses (CustomerNumber, MailAddress, MailAddressType) VALUES (@CustomerNumber, @EMail, 5)	
      INSERT INTO MailAddresses (CustomerNumber, MailAddress, MailAddressType) VALUES (@CustomerNumber, @EMail, 6)	
      INSERT INTO MailAddresses (CustomerNumber, MailAddress, MailAddressType) VALUES (@CustomerNumber, @EMail, 7)	   
    END 
    FETCH NEXT FROM ConversionCursor INTO @CustomerNumber, @EMail
  END

  CLOSE ConversionCursor
  DEALLOCATE ConversionCursor  

  EXEC sp_RENAME 'Customer.EMAIL' , 'zzzEMAIL', 'COLUMN'
  --ALTER TABLE CUSTOMER DROP COLUMN EMAIL

END
