CREATE TABLE [dbo].[OrderPhoneNumber]
(
[OrderPhoneNumberID] [int] NOT NULL IDENTITY(1, 1),
[CountryCodeStandardFID] [int] NULL,
[AreaCode] [dbo].[PhoneAreaCode] NULL,
[LocalNumber] [dbo].[PhoneLocalNumber] NULL,
[Extension] [dbo].[PhoneExtension] NULL,
[OrderFID] [int] NOT NULL,
[OrderPhoneTypeFID] [int] NOT NULL,
[Ranking] [int] NULL,
[OrderAuditInfoFID] [bigint] NULL,
[OrderAuditInfoManualCleanup] [bigint] NULL
) ON [PRIMARY]
GO

create trigger [dbo].[OrderPhoneNumberAuditFieldsChanged] ON [dbo].[OrderPhoneNumber]
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
    (
        OrderPhoneNumberID int,
        FieldName varchar(128),
        value varchar(256),
        OrderPhoneTypeID int,
        Sequence int
    );

    insert into @theAuditFields
    (
        OrderPhoneNumberID,
        FieldName,
        value,
        OrderPhoneTypeID,
        Sequence
    )
    select deleted.OrderPhoneNumberID,
           'OrderPhoneNumber',
           NULL,
           deleted.OrderPhoneTypeFID,
           1
    from inserted
        INNER JOIN deleted
        INNER JOIN dbo.OrderPhoneType on deleted.OrderPhoneTypeFID = OrderPhoneType.OrderPhoneTypeID
            ON inserted.OrderPhoneNumberID = deleted.OrderPhoneNumberID
               AND inserted.OrderPhoneTypeFID <> deleted.OrderPhoneTypeFID
    where update(OrderPhoneTypeFID)
    union all
    select inserted.OrderPhoneNumberID,
           'OrderPhoneNumber',
          convert( varchar(256), dbo.GetFormattedPhoneNumberWithExtension(Inserted.CountryCodeStandardFID,Inserted.AreaCode,Inserted.LocalNumber, Inserted.Extension) ),
           Inserted.OrderPhoneTypeFID,
           2
    from inserted
        inner join deleted
            on inserted.OrderPhoneNumberID = deleted.OrderPhoneNumberID
               AND
               (
                   inserted.OrderPhoneTypeFID <> deleted.OrderPhoneTypeFID
                   OR inserted.CountryCodeStandardFID <> Deleted.CountryCodeStandardFID
                   OR inserted.AreaCode <> deleted.AreaCode
                   OR  inserted.LocalNumber <> deleted.LocalNumber
                   OR  isnull(inserted.Extension,'') <> isnull(deleted.Extension,'')
               )
        INNER JOIN    dbo.CountryCodeStandard ON  CountryCodeStandard.CountryCodeStandardID = inserted.CountryCodeStandardFID
    where UPDATE(OrderPhoneTypeFID)
          OR UPDATE(CountryCodeStandardFID) OR UPDATE(AreaCode) OR UPDATE(LocalNumber) OR UPDATE(Extension);


    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, ' (', OrderPhoneType.TypeName, ')')
    FROM @theAuditFields theAuditFields
        INNER JOIN OrderHistoryField
            ON theAuditFields.FieldName = OrderHistoryField.FieldName
        INNER JOIN inserted
            ON theAuditFields.OrderPhoneNumberID = inserted.OrderPhoneNumberID
        INNER JOIN dbo.OrderPhoneType
            ON OrderPhoneType.OrderPhoneTypeID = theAuditFields.OrderPhoneTypeID and OrderPhoneType.IsShipperPhoneType = 1
        LEFT OUTER JOIN 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.OrderPhoneNumber
        SET OrderAuditInfoFID = NULL
        FROM inserted
            INNER JOIN dbo.OrderPhoneNumber
                ON inserted.OrderPhoneNumberID = OrderPhoneNumber.OrderPhoneNumberID;

        DELETE dbo.OrderAuditInfo
        FROM inserted
            INNER JOIN OrderAuditInfo
                ON inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID;
    END;
END; -- if update( OrderAuditInfoFID )
GO


create trigger [dbo].[OrderPhoneNumberAuditFieldsInitiallySet] on [dbo].[OrderPhoneNumber]
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 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), dbo.GetFormattedPhoneNumberWithExtension(Inserted.CountryCodeStandardFID,Inserted.AreaCode,Inserted.LocalNumber, Inserted.Extension) ),
    ChangedIn =         case
                            when OrderAuditInfo.OrderAuditInfoID IS NULL then 'Direct SQL'
                            else OrderAuditInfo.UpdateSource
                        end,
    CustomDescription = CONCAT( 'Phone Number (', OrderPhoneType.TypeName,')')
    from inserted
    INNER JOIN OrderHistoryField on OrderHistoryField.FieldName = 'OrderPhoneNumber'
    INNER JOIN dbo.OrderPhoneType on Inserted.OrderPhoneTypeFID = OrderPhoneType.OrderPhoneTypeID and OrderPhoneType.IsShipperPhoneType = 1
    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 OrderPhoneNumber
    set OrderAuditInfoFID = NULL
    from inserted
    inner join OrderPhoneNumber on inserted.OrderPhoneNumberID = OrderPhoneNumber.OrderPhoneNumberID
    where inserted.OrderAuditInfoManualCleanup is null

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

create trigger [dbo].[OrderPhoneNumberAuditRecordDeleted] on [dbo].[OrderPhoneNumber]
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 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, ' (', OrderPhoneType.TypeName ,')')
FROM Deleted
    INNER JOIN dbo.OrderPhoneType
        ON OrderPhoneType.OrderPhoneTypeID = Deleted.OrderPhoneTypeFID and OrderPhoneType.IsShipperPhoneType = 1
    -- we need to filter by the existence of the parent order, in order to play nice with the OrderPhoneNumber table's "ON DELETE CASCADE"
    INNER JOIN Orders
        ON deleted.OrderFID = Orders.PriKey
    LEFT OUTER JOIN OrderAuditInfo
        ON deleted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    INNER JOIN OrderHistoryField
        ON OrderHistoryField.FieldName = 'OrderPhoneNumber';


DELETE OrderAuditInfo
FROM deleted
    INNER JOIN OrderAuditInfo
        ON deleted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID;
GO
ALTER TABLE [dbo].[OrderPhoneNumber] ADD CONSTRAINT [PK_OrderPhoneNumber] PRIMARY KEY NONCLUSTERED  ([OrderPhoneNumberID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderPhoneNumber] ADD CONSTRAINT [IX_OrderPhoneNumber_OrderAndPhoneType] UNIQUE NONCLUSTERED  ([OrderFID], [OrderPhoneTypeFID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_OrderPhoneNumber_LocalNumber] ON [dbo].[OrderPhoneNumber] ([LocalNumber]) INCLUDE ([AreaCode], [CountryCodeStandardFID], [Extension], [OrderFID], [OrderPhoneNumberID], [OrderPhoneTypeFID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_OrderPhoneNumber_OrderAuditInfoFID] ON [dbo].[OrderPhoneNumber] ([OrderAuditInfoFID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderPhoneNumber] ADD CONSTRAINT [FK_OrderPhoneNumber_CountryCodeStandard] FOREIGN KEY ([CountryCodeStandardFID]) REFERENCES [dbo].[CountryCodeStandard] ([CountryCodeStandardID])
GO
ALTER TABLE [dbo].[OrderPhoneNumber] ADD CONSTRAINT [FK_OrderPhoneNumber_OrderAuditInfo] FOREIGN KEY ([OrderAuditInfoFID]) REFERENCES [dbo].[OrderAuditInfo] ([OrderAuditInfoID])
GO
ALTER TABLE [dbo].[OrderPhoneNumber] ADD CONSTRAINT [FK_OrderPhoneNumber_Order] FOREIGN KEY ([OrderFID]) REFERENCES [dbo].[Orders] ([PriKey])
GO
ALTER TABLE [dbo].[OrderPhoneNumber] ADD CONSTRAINT [FK_OrderPhoneNumber_OrderPhoneType] FOREIGN KEY ([OrderPhoneTypeFID]) REFERENCES [dbo].[OrderPhoneType] ([OrderPhoneTypeID])
GO
GRANT SELECT ON  [dbo].[OrderPhoneNumber] TO [MssExec]
GRANT INSERT ON  [dbo].[OrderPhoneNumber] TO [MssExec]
GRANT DELETE ON  [dbo].[OrderPhoneNumber] TO [MssExec]
GRANT UPDATE ON  [dbo].[OrderPhoneNumber] TO [MssExec]
GO