CREATE TABLE [dbo].[OrderConsignPhoneNumber]
(
[OrderConsignPhoneNumberID] [int] NOT NULL IDENTITY(1, 1),
[OrderConsignFID] [int] NULL,
[CountryCodeStandardFID] [int] NULL,
[AreaCode] [dbo].[PhoneAreaCode] NULL,
[LocalNumber] [dbo].[PhoneLocalNumber] NULL,
[Extension] [dbo].[PhoneExtension] NULL,
[OrderAuditInfoFID] [bigint] NULL,
[OrderAuditInfoManualCleanup] [bigint] NULL
) ON [PRIMARY]
GO

CREATE trigger [dbo].[ConsignPhoneNumberAuditFieldsInitiallySet] on [dbo].[OrderConsignPhoneNumber]
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 = OrderConsign.OrdersFID,
        ChangedBy = ISNULL( OrderAuditInfo.SysUserFID, @theExtAppUser ),
        ChangedOn = @theChangedOn,
        OrderHistoryFieldFID = OrderHistoryfield.OrderHistoryFieldID,
        ChangedTo =    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( iif(ConsignType.[Description] = 'Origin', 'Consignor ','Consignee '), OrderHistoryField.DisplayName)
    from inserted
    inner join OrderConsign on OrderConsign.OrderConsignID = inserted.OrderConsignFID
    inner join OrderHistoryField on OrderHistoryField.FieldName = 'ConsignPhoneNumber'
    --LEFT OUTER JOIN dbo.OrderConsignPhoneNumber OCPhoneNum ON OCPhoneNum.OrderConsignFID = inserted.OrderConsignID
    --LEFT OUTER JOIN    dbo.CountryCodeStandard cc ON  cc.CountryCodeStandardID = OCPhoneNum.CountryCodeStandardFID
    inner join ConsignType ON OrderConsign.ConsignTypeFID = ConsignType.ConsignTypeID
    left outer join dbo.OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    where
        isnull( inserted.CountryCodeStandardFID, -1 ) <> -1 or
        isnull( inserted.AreaCode, '' ) <> '' or
        isnull( inserted.LocalNumber, '' ) <> '' or
        isnull( inserted.Extension, '' ) <> ''

    -- 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 OrderConsignPhoneNumber
    set OrderAuditInfoFID = NULL
    from inserted
    inner join OrderConsignPhoneNumber on inserted.OrderConsignFID = OrderConsignPhoneNumber.OrderConsignFID
    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].[OrderConsignPhoneNumberAuditFieldsChanged] ON [dbo].[OrderConsignPhoneNumber]
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
    delete dbo.OrderAuditInfo
        from deleted
        inner join OrderAuditInfo ON deleted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID;
    return;
end;
else
begin
    
    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 = OrderConsign.OrdersFID,
        ChangedBy = ISNULL(OrderAuditInfo.SysUserFID, @theExtAppUser),
        ChangedOn = @theChangedOn,
        OrderHistoryFieldFID = OrderHistoryField.OrderHistoryFieldID,
        ChangedTo = 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( iif(ConsignType.[Description] = 'Origin', 'Consignor ','Consignee '), OrderHistoryField.DisplayName)
    FROM inserted
    INNER JOIN deleted ON inserted.OrderConsignPhoneNumberID = deleted.OrderConsignPhoneNumberID and
    (
        isnull( inserted.CountryCodeStandardFID, -1 ) <> isnull( deleted.CountryCodeStandardFID, -1 ) or
        isnull( inserted.AreaCode, '' ) <> isnull( deleted.AreaCode, '' ) or
        isnull( inserted.LocalNumber, '' ) <> isnull( deleted.LocalNumber, '' ) or
        isnull( inserted.Extension, '' ) <> isnull( deleted.Extension, '' )
    )
    inner join OrderConsign on OrderConsign.OrderConsignID = inserted.OrderConsignFID
    inner join OrderHistoryField on OrderHistoryField.FieldName = 'ConsignPhoneNumber'
    INNER JOIN dbo.ConsignType ON ConsignType.ConsignTypeID = OrderConsign.ConsignTypeFID
    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 updater to clean up this record ).
    if not (update(OrderAuditInfoManualCleanup))
    begin
        update dbo.OrderConsignPhoneNumber
        set OrderAuditInfoFID = NULL
        from inserted
        inner join OrderConsignPhoneNumber on inserted.OrderConsignFID = OrderConsignPhoneNumber.OrderConsignFID

        delete dbo.OrderAuditInfo
        from inserted
        inner join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID;
    end;
end; -- if update( OrderAuditInfoFID )
GO
ALTER TABLE [dbo].[OrderConsignPhoneNumber] ADD CONSTRAINT [PK_OrderConsignPhoneNumber] PRIMARY KEY NONCLUSTERED  ([OrderConsignPhoneNumberID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_OrderConsignPhoneNumber_OrderAuditInfoFID] ON [dbo].[OrderConsignPhoneNumber] ([OrderAuditInfoFID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_OrderConsignPhoneNumber_OrderConsignFID] ON [dbo].[OrderConsignPhoneNumber] ([OrderConsignFID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderConsignPhoneNumber] ADD CONSTRAINT [FK_OrderConsignPhoneNumber_CountryCodeStandard] FOREIGN KEY ([CountryCodeStandardFID]) REFERENCES [dbo].[CountryCodeStandard] ([CountryCodeStandardID])
GO
ALTER TABLE [dbo].[OrderConsignPhoneNumber] ADD CONSTRAINT [FK_OrderConsignPhoneNumber_OrderAuditInfo] FOREIGN KEY ([OrderAuditInfoFID]) REFERENCES [dbo].[OrderAuditInfo] ([OrderAuditInfoID])
GO
ALTER TABLE [dbo].[OrderConsignPhoneNumber] ADD CONSTRAINT [FK_OrderConsignPhoneNumber_OrderConsign] FOREIGN KEY ([OrderConsignFID]) REFERENCES [dbo].[OrderConsign] ([OrderConsignID]) ON DELETE CASCADE
GO
GRANT SELECT ON  [dbo].[OrderConsignPhoneNumber] TO [MssExec]
GRANT INSERT ON  [dbo].[OrderConsignPhoneNumber] TO [MssExec]
GRANT DELETE ON  [dbo].[OrderConsignPhoneNumber] TO [MssExec]
GRANT UPDATE ON  [dbo].[OrderConsignPhoneNumber] TO [MssExec]
GO