SET NOCOUNT ON

IF NOT EXISTS 
(
  SELECT * 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID AND SysColumns.Name = 'certificateData'
  WHERE SysObjects.Name = 'ReceiptFiscal'
) 
BEGIN
  DECLARE @DropStatement VARCHAR(100)
  IF EXISTS
  (
    SELECT * 
    FROM SysObjects WITH (NOLOCK) 
      INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
        AND SysColumns.Name = 'Signature'
    WHERE SysObjects.Name = 'ReceiptFiscal'
  )
  BEGIN    
    SET @DropStatement = 'ALTER TABLE ReceiptFiscal DROP COLUMN Signature'
    EXEC (@DropStatement)  
  END
  IF EXISTS
  (
    SELECT * 
    FROM SysObjects WITH (NOLOCK) 
      INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
        AND SysColumns.Name = 'KeyVersion'
    WHERE SysObjects.Name = 'ReceiptFiscal'
  )
  BEGIN    
    SET @DropStatement = 'ALTER TABLE ReceiptFiscal DROP COLUMN KeyVersion'
    EXEC (@DropStatement)  
  END
END
GO
IF NOT EXISTS 
(
  SELECT * 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID AND SysColumns.Name = 'certificateData'
  WHERE SysObjects.Name = 'ReceiptFiscal'
) 
BEGIN
  ALTER TABLE ReceiptFiscal ADD Signature nvarchar(999) null
  ALTER TABLE ReceiptFiscal ADD KeyVersion nvarchar(50) null
  ALTER TABLE ReceiptFiscal ADD certificateData nvarchar(4000) null
END
GO
--Prepare for change dbo.CashBoxTerminalXTab.FkTerminal from VACHAR to INT 
IF NOT EXISTS
(
SELECT *
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_NAME='FK_CashBoxTerminalXTab_Terminals'
)
delete from dbo.CashBoxTerminalXTab WHERE NOT FkTerminal IN (
  SELECT TerminalNumber FROM dbo.Terminals (NOLOCK))
GO
IF NOT EXISTS
(
SELECT *
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_NAME='FK_CashBoxTerminalXTab_CashBox'
)
delete from dbo.CashBoxTerminalXTab WHERE NOT FkCashBox IN (
  SELECT PK_Cashbox FROM dbo.Cashbox (NOLOCK))

