CREATE TABLE [dbo].[OrderEmailAddress]
(
[OrderEmailAddressID] [int] NOT NULL IDENTITY(1, 1),
[OrderFID] [int] NOT NULL,
[OrderEmailTypeFID] [int] NOT NULL,
[EmailLabel] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EmailAddress] [nvarchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Ranking] [int] NOT NULL,
[OrderAuditInfoFID] [bigint] NULL,
[OrderAuditInfoManualCleanup] [bigint] NULL
) ON [PRIMARY]
GO

Create trigger [dbo].[OrderEmailAddressAuditFieldsChanged] on [dbo].[OrderEmailAddress]
after update
as
set nocount on;

-- When we clear the OrderAuditInfoFID field, no auditing is needed.
if (
       update(OrderAuditInfoFID)
       and not exists
(
    select OrderAuditInfoFID
    from inserted
    where OrderAuditInfoFID is not null
)
   )
begin
    return;
end;
else
begin
    declare @theAuditFields table
    (
        OrderEmailAddressID int,
        FieldName varchar(128),
        Value varchar(256),
        EmailTypeFID int,
        Sequence int
    );

    insert into @theAuditFields
    (
        OrderEmailAddressID,
        FieldName,
        Value,
        EmailTypeFID,
        Sequence
    )
    select inserted.OrderEmailAddressID,
           'EmailAddress',
           NULL,
           Deleted.OrderEmailTypeFID,
           1
    from inserted
        INNER JOIN deleted
            ON inserted.OrderEmailAddressID = deleted.OrderEmailAddressID
               AND inserted.OrderEmailTypeFID != deleted.OrderEmailTypeFID
    where update(OrderEmailTypeFID)
    union all
    select inserted.OrderEmailAddressID,
           'EmailAddress',
           CONVERT(varchar(256), Inserted.EmailAddress),
           Inserted.OrderEmailTypeFID,
           2
    from inserted
        inner join deleted
            on inserted.OrderEmailAddressID = deleted.OrderEmailAddressID
               AND
               (
                   inserted.OrderEmailTypeFID != deleted.OrderEmailTypeFID
                   OR inserted.EmailAddress != deleted.EmailAddress
               )
    where UPDATE(OrderEmailTypeFID)
          OR UPDATE(EmailAddress);


    DECLARE @theChangedOn DATETIME;
    SET @theChangedOn = dbo.GetMssDateTime();

    DECLARE @theExtAppUser INT;

    IF EXISTS
    (
        SELECT TOP 1
               OrderAuditInfoFID
        FROM inserted
        WHERE OrderAuditInfoFID IS NULL
    )
    BEGIN
        SELECT @theExtAppUser = Sysuser.SysUserID
        FROM dbo.Sysuser
        WHERE Sysuser.FIRSTNAME = 'External'
              AND Sysuser.LASTNAME = 'Application';
    END;

    insert into OrderHistory
    (
        OrderFID,
        ChangedBy,
        ChangedOn,
        OrderHistoryFieldFID,
        ChangedTo,
        ChangedIn,
        CustomDescription
    )
    select OrderFID = Inserted.OrderFID,
           ChangedBy = isnull(OrderAuditInfo.SysUserFID, @theExtAppUser),
           ChangedOn = @theChangedOn,
           OrderHistoryFieldFID = OrderHistoryField.OrderHistoryFieldID,
           ChangedTo = theAuditFields.[Value],
           ChangedIn = case
                           when OrderAuditInfo.OrderAuditInfoID is null then
                               'Direct SQL'
                           else
                               OrderAuditInfo.UpdateSource
                       end,
           CustomDescription = concat(OrderHistoryField.DisplayName, ' (', OrderEmailType.EmailType, ')')
    from @theAuditFields theAuditFields
        inner join dbo.OrderHistoryField
            on theAuditFields.FieldName = OrderHistoryField.FieldName
        inner join inserted
            on theAuditFields.OrderEmailAddressID = inserted.OrderEmailAddressID
        inner join dbo.OrderEmailType
            on OrderEmailType.OrderEmailTypeID = theAuditFields.EmailTypeFID
        left outer join dbo.OrderAuditInfo
            on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    order by theAuditFields.Sequence;


    -- we're done with the metadata, so clean it up. (unless a manual cleanup flag was specified, in
    -- which case its the responsibility of the updater to clean up this record ).
    if not (update(OrderAuditInfoManualCleanup))
    begin
        update dbo.OrderEmailAddress
        set OrderAuditInfoFID = null
        from inserted
            inner join dbo.OrderEmailAddress
                on inserted.OrderEmailAddressID = OrderEmailAddress.OrderEmailAddressID;

        delete dbo.OrderAuditInfo
        from inserted
            inner join dbo.OrderAuditInfo
                on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID;
    end;
end; -- if update( OrderAuditInfoFID )
GO

create trigger [dbo].[OrderEmailAddressAuditFieldsInitiallySet] on [dbo].[OrderEmailAddress]
after insert
as
set nocount on
        
    declare @theChangedOn datetime;
    set @theChangedOn = dbo.GetMssDateTime();

    declare @theExtAppUser int;

    if exists( select top 1 OrderAuditInfoFID from inserted where OrderAuditInfoFID IS NULL )
    begin
      select @theExtAppUser = SysUser.SysUserID
        from dbo.Sysuser
        where SysUser.FIRSTNAME = 'External' AND
        SysUser.LASTNAME = 'Application'      
    end;

    insert into dbo.OrderHistory
    (
        OrderFID,
        ChangedBy,
        ChangedOn,
        OrderHistoryFieldFID,
        ChangedTo,
        ChangedIn,
        CustomDescription
    )
    select
    OrderFID = inserted.OrderFID,
    ChangedBy = ISNULL( OrderAuditInfo.SysUserFID, @theExtAppUser ),
    ChangedOn = @theChangedOn,
    OrderHistoryFieldFID = OrderHistoryfield.OrderHistoryFieldID,
    ChangedTo =  convert( varchar(256), Inserted.EmailAddress ),
    ChangedIn =         case
                            when OrderAuditInfo.OrderAuditInfoID IS NULL then 'Direct SQL'
                            else OrderAuditInfo.UpdateSource
                        end,
    CustomDescription = CONCAT( OrderHistoryField.DisplayName,' (', OrderEmailType.EmailType,')')
    from inserted
    INNER JOIN OrderHistoryField on OrderHistoryField.FieldName = 'EmailAddress'
    INNER JOIN dbo.OrderEmailType on Inserted.OrderEmailTypeFID = OrderEmailType.OrderEmailTypeID
    LEFT OUTER JOIN OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID;


    -- we're done with the metadata, so clean it up. (unless a manual cleanup flag was specified, in
    -- which case its the responsibility of the inserter to clean up this record ).
    update dbo.OrderEmailAddress
    set OrderAuditInfoFID = NULL
    from inserted
    inner join dbo.OrderEmailAddress on inserted.OrderEmailAddressID = OrderEmailAddress.OrderEmailAddressID
    where inserted.OrderAuditInfoManualCleanup is null;

    delete OrderAuditInfo
    from inserted
    inner join OrderAuditInfo ON inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    where inserted.OrderAuditInfoManualCleanup is null;
GO

-- Create a trigger that records audit information when an OrderEmailAddress record is deleted.
-- Only records audit information for fields that contained data before the record was deleted.
create trigger [dbo].[OrderEmailAddressAuditRecordDeleted] on [dbo].[OrderEmailAddress]
after delete
AS
SET NOCOUNT ON;

DECLARE @theChangedOn DATETIME;
SET @theChangedOn = dbo.GetMssDateTime();

DECLARE @theExtAppUser INT;

IF EXISTS
(
    SELECT TOP 1
           OrderAuditInfoFID
    FROM deleted
    WHERE OrderAuditInfoFID IS NULL
)
BEGIN
    SELECT @theExtAppUser = SysUser.SysUserID
    FROM dbo.Sysuser
    WHERE SysUser.FIRSTNAME = 'External'
          AND SysUser.LASTNAME = 'Application';
END;

INSERT INTO dbo.OrderHistory
(
    OrderFID,
    ChangedBy,
    ChangedOn,
    OrderHistoryFieldFID,
    ChangedTo,
    ChangedIn,
    CustomDescription
)
SELECT OrderFID = deleted.OrderFID,
       ChangedBy = ISNULL(OrderAuditInfo.SysUserFID, @theExtAppUser),
       ChangedOn = @theChangedOn,
       OrderHistoryFieldFID = OrderHistoryField.OrderHistoryFieldID,
       ChangedTo = NULL,
       ChangedIn = CASE
                       WHEN OrderAuditInfo.OrderAuditInfoID IS NULL THEN
                           'Direct SQL'
                       ELSE
                           OrderAuditInfo.UpdateSource
                   END,
       CustomDescription = CONCAT(OrderHistoryField.DisplayName, ' (', OrderEmailType.EmailType, ')')
FROM Deleted
    INNER JOIN dbo.OrderEmailType
        ON OrderEmailType.OrderEmailTypeID = Deleted.OrderEmailTypeFID
    -- we need to filter by the existence of the parent order, in order to play nice with the OrderEmailAddress table's "ON DELETE CASCADE"
    INNER JOIN dbo.Orders
        ON deleted.OrderFID = Orders.PriKey
    LEFT OUTER JOIN dbo.OrderAuditInfo
        ON deleted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    INNER JOIN dbo.OrderHistoryField
        ON OrderHistoryField.FieldName = 'EmailAddress';


DELETE OrderAuditInfo
FROM deleted
INNER JOIN dbo.OrderAuditInfo
        ON deleted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID;
GO
ALTER TABLE [dbo].[OrderEmailAddress] ADD CONSTRAINT [PK_OrderEmailAddress] PRIMARY KEY NONCLUSTERED  ([OrderEmailAddressID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderEmailAddress] ADD CONSTRAINT [IX_OrderEmailAddress_OrderEmailType] UNIQUE NONCLUSTERED  ([OrderFID], [OrderEmailTypeFID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_OrderEmailAddress_OrderAuditInfoFID] ON [dbo].[OrderEmailAddress] ([OrderAuditInfoFID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderEmailAddress] ADD CONSTRAINT [FK_OrderEmailAddress_OrderAuditInfo] FOREIGN KEY ([OrderAuditInfoFID]) REFERENCES [dbo].[OrderAuditInfo] ([OrderAuditInfoID])
GO
ALTER TABLE [dbo].[OrderEmailAddress] ADD CONSTRAINT [FK_OrderEmailAddress_OrderEmailType] FOREIGN KEY ([OrderEmailTypeFID]) REFERENCES [dbo].[OrderEmailType] ([OrderEmailTypeID])
GO
ALTER TABLE [dbo].[OrderEmailAddress] ADD CONSTRAINT [FK_OrderEmailAddress_Order] FOREIGN KEY ([OrderFID]) REFERENCES [dbo].[Orders] ([PriKey]) ON DELETE CASCADE
GO
GRANT SELECT ON  [dbo].[OrderEmailAddress] TO [MssExec]
GRANT INSERT ON  [dbo].[OrderEmailAddress] TO [MssExec]
GRANT DELETE ON  [dbo].[OrderEmailAddress] TO [MssExec]
GRANT UPDATE ON  [dbo].[OrderEmailAddress] TO [MssExec]
GO