CREATE TABLE [dbo].[InternationalVoyage]
(
[InternationalVoyageID] [int] NOT NULL IDENTITY(1, 1),
[OrdersFID] [int] NOT NULL,
[InternationalPortFID] [int] NULL,
[Name] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EstimatedDepartureDate] [datetime] NULL,
[ActualDepartureDate] [datetime] NULL,
[EstimatedArrivalDate] [datetime] NULL,
[ActualArrivalDate] [datetime] NULL,
[InternationalOriginPortFID] [int] NULL,
[OrderAuditInfoFID] [bigint] NULL,
[OrderAuditInfoManualCleanup] [bigint] NULL
) ON [PRIMARY]
GO

-- Create a trigger that records audit information when a InternationalVoyage record is deleted.
-- Only records audit information for "intermediate" arrival and departure dates (dates after the first departure and before the last arrival).
-- Also, only records audit information for fields that contained data before the record was deleted.
CREATE trigger [dbo].[InternationalVoyageDeletedAudit] on [dbo].[InternationalVoyage]
after delete
as
    set nocount on

    declare @theAuditFields table
    (
        InternationalVoyageFID int,
        OrderHistoryFieldName varchar(128),
        CustomDescription varchar(128),
        [Value] varchar(256)
    )

    -- Get max and min SQL datetimes
    declare @theSqlMinDate datetime
    select
        @theSqlMinDate = '1753-1-1'

    insert into @theAuditFields
    (
        InternationalVoyageFID,
        OrderHistoryFieldName,
        CustomDescription,
        [Value]
    )
    select
        InternationalVoyageID,
        'CustomDisplayNameDateField',
        rtrim( 'Actual Arrival at Destination Port ' + isnull( DestPort.PortCode, '' ) ),
        null
    from deleted
    left outer join InternationalPort DestPort on DestPort.InternationalPortID = deleted.InternationalPortFID  
    where
        isnull( deleted.ActualArrivalDate, @theSqlMinDate ) != @theSqlMinDate
    union all
    select
        InternationalVoyageID,
        'CustomDisplayNameDateField',
        rtrim( 'Actual Departure from Origin Port ' + isnull( OrigPort.PortCode, '' ) ),
        null
    from deleted
    left outer join InternationalPort OrigPort on OrigPort.InternationalPortID = deleted.InternationalOriginPortFID  
    where
        isnull( deleted.ActualDepartureDate, @theSqlMinDate ) != @theSqlMinDate
        
    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,
        CustomDescription,
        ChangedTo,
        ChangedIn
    )
    select
        OrderFID = deleted.OrdersFID,
        ChangedBy = isnull( OrderAuditInfo.SysUserFID, @theExtAppUser ),
        ChangedOn = @theChangedOn,
        OrderHistoryFieldFID = OrderHistoryField.OrderHistoryFieldID,
        CustomDescription = theAuditFields.CustomDescription,
        ChangedTo = Value,
        ChangedIn =        case
                            when OrderAuditInfo.OrderAuditInfoID is null then 'Direct SQL'
                            else OrderAuditInfo.UpdateSource
                        end
    from @theAuditFields theAuditFields
    inner join deleted on theAuditFields.InternationalVoyageFID = deleted.InternationalVoyageID
    left outer join OrderAuditInfo on deleted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    inner join OrderHistoryField on theAuditFields.OrderHistoryFieldName = OrderHistoryField.FieldName

    -- we're done with the metadata, so clean it up.
    update InternationalVoyage
    set OrderAuditInfoFID = NULL
    from deleted
    inner join InternationalVoyage on deleted.InternationalVoyageID = InternationalVoyage.InternationalVoyageID
    where deleted.OrderAuditInfoManualCleanup is null

    delete OrderAuditInfo
    from deleted
    inner join OrderAuditInfo on deleted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    where deleted.OrderAuditInfoManualCleanup is null
GO

-- Create a trigger that records audit information when a InternationalVoyage record is inserted.
-- Only records audit information for fields that have a value.
CREATE trigger [dbo].[InternationalVoyageInsertedAudit] on [dbo].[InternationalVoyage]
after insert
as
set nocount on
    declare @theAuditFields table
    (
        InternationalVoyageFID int,
        OrderHistoryFieldName varchar(128),
        CustomDescription varchar(128),
        [Value] varchar(256)
    )

    -- Get min SQL datetime
    declare @theSqlMinDate datetime
    select
        @theSqlMinDate = '1753-1-1'

    insert into @theAuditFields
    (
        InternationalVoyageFID,
        OrderHistoryFieldName,
        CustomDescription,
        [Value]
    )
    select
        inserted.InternationalVoyageID,
        'CustomDisplayNameDateField',
        rtrim( 'Actual Arrival at Destination Port ' + isnull( DestPort.PortCode, '' ) ),
        convert( varchar(256), inserted.ActualArrivalDate )
    from inserted
    left outer join InternationalPort DestPort on DestPort.InternationalPortID = inserted.InternationalPortFID
    where
        update( ActualArrivalDate ) and isnull( ActualArrivalDate, @theSqlMinDate ) != @theSqlMinDate
    union all
    select
        inserted.InternationalVoyageID,
        'CustomDisplayNameDateField',
        rtrim( 'Actual Departure from Origin Port ' + isnull( OrigPort.PortCode, '' ) ),
        convert( varchar(256), inserted.ActualDepartureDate )
    from inserted
    left outer join InternationalPort OrigPort on OrigPort.InternationalPortID = inserted.InternationalOriginPortFID
    where
        update( ActualDepartureDate ) and isnull( ActualDepartureDate, @theSqlMinDate ) != @theSqlMinDate

    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,
        CustomDescription,
        ChangedTo,
        ChangedIn
    )
    select
    OrderFID = inserted.OrdersFID,
    ChangedBy = isnull( OrderAuditInfo.SysUserFID, @theExtAppUser ),
    ChangedOn = @theChangedOn,
    OrderHistoryFieldFID = OrderHistoryField.OrderHistoryFieldID,
    CustomDescription = theAuditFields.CustomDescription,
    ChangedTo = [Value],
    ChangedIn =     case
                            when OrderAuditInfo.OrderAuditInfoID is null then 'Direct SQL'
                            else OrderAuditInfo.UpdateSource
                    end
    from @theAuditFields theAuditFields
    inner join inserted on theAuditFields.InternationalVoyageFID = inserted.InternationalVoyageID
    left outer join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    inner join OrderHistoryField on theAuditFields.OrderHistoryFieldName = OrderHistoryField.FieldName
    where theAuditFields.[Value] is not null

    -- 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 InternationalVoyage
    set OrderAuditInfoFID = NULL
    from inserted
    inner join InternationalVoyage on inserted.InternationalVoyageID = InternationalVoyage.InternationalVoyageID
    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 a InternationalVoyage record is updated.
-- Only records audit information for fields that were updated.
CREATE trigger [dbo].[InternationalVoyageUpdatedAudit] on [dbo].[InternationalVoyage]
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
        (
            InternationalVoyageFID int,
            OrderHistoryFieldName varchar(128),
            CustomDescription varchar(128),
            [Value] varchar(256)
        )

        -- Get max and min SQL datetimes
        declare @theSqlMinDate datetime
        select
            @theSqlMinDate = '1753-1-1'

        insert into @theAuditFields
        (
            InternationalVoyageFID,
            OrderHistoryFieldName,
            CustomDescription,
            [Value]
        )
        select
            inserted.InternationalVoyageID,
            'CustomDisplayNameDateField',
            rtrim( 'Actual Arrival at Destination Port ' + isnull( DestPort.PortCode, '' ) ),
            CONVERT( varchar(256), inserted.ActualArrivalDate )
        from inserted
        inner join deleted on inserted.InternationalVoyageID = deleted.InternationalVoyageID and
            isnull( inserted.ActualArrivalDate, @theSqlMinDate ) != isnull( deleted.ActualArrivalDate, @theSqlMinDate )
        left outer join InternationalPort DestPort on DestPort.InternationalPortID = inserted.InternationalPortFID
        where update( ActualArrivalDate )
        union all
        select
            inserted.InternationalVoyageID,
            'CustomDisplayNameDateField',
            rtrim( 'Actual Departure from Origin Port ' + isnull( OrigPort.PortCode, '' ) ),
            CONVERT( varchar(256), inserted.ActualDepartureDate )
        from inserted
        inner join deleted on inserted.InternationalVoyageID = deleted.InternationalVoyageID and
            isnull( inserted.ActualDepartureDate, @theSqlMinDate ) != isnull( deleted.ActualDepartureDate, @theSqlMinDate )
        left outer join InternationalPort OrigPort on OrigPort.InternationalPortID = inserted.InternationalOriginPortFID
        where update( ActualDepartureDate )
            
        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,
            CustomDescription,
            ChangedTo,
            ChangedIn
        )
        select
        OrderFID = inserted.OrdersFID,
        ChangedBy = isnull( OrderAuditInfo.SysUserFID, @theExtAppUser ),
        ChangedOn = @theChangedOn,
        OrderHistoryFieldFID = OrderHistoryField.OrderHistoryFieldID,
        CustomDescription = theAuditFields.CustomDescription,
        ChangedTo = [Value],
        ChangedIn =     case
                            when OrderAuditInfo.OrderAuditInfoID is null then 'Direct SQL'
                            else OrderAuditInfo.UpdateSource
                        end
        from @theAuditFields theAuditFields
        inner join inserted on theAuditFields.InternationalVoyageFID = inserted.InternationalVoyageID
        left outer join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
        inner join OrderHistoryField on theAuditFields.OrderHistoryFieldName = OrderHistoryField.FieldName

        -- 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 InternationalVoyage
            set OrderAuditInfoFID = NULL
            from inserted
            inner join InternationalVoyage on inserted.InternationalVoyageID = InternationalVoyage.InternationalVoyageID

            delete OrderAuditInfo
            from inserted
            inner join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
        end
    end -- if update( OrderAuditInfoFID )
GO
ALTER TABLE [dbo].[InternationalVoyage] ADD CONSTRAINT [PK_InternationalVoyage] PRIMARY KEY NONCLUSTERED  ([InternationalVoyageID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_InternationalVoyage_OrderAuditInfoFID] ON [dbo].[InternationalVoyage] ([OrderAuditInfoFID]) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_InternationalVoyage_OrdersFID_InternationalVoyageID] ON [dbo].[InternationalVoyage] ([OrdersFID], [InternationalVoyageID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[InternationalVoyage] ADD CONSTRAINT [FK_InternationalVoyage_InternationalOriginPortFID] FOREIGN KEY ([InternationalOriginPortFID]) REFERENCES [dbo].[InternationalPort] ([InternationalPortID])
GO
ALTER TABLE [dbo].[InternationalVoyage] ADD CONSTRAINT [FK_InternationalVoyage_InternationalPort] FOREIGN KEY ([InternationalPortFID]) REFERENCES [dbo].[InternationalPort] ([InternationalPortID])
GO
ALTER TABLE [dbo].[InternationalVoyage] ADD CONSTRAINT [FK_InternationalVoyage_OrderAuditInfo] FOREIGN KEY ([OrderAuditInfoFID]) REFERENCES [dbo].[OrderAuditInfo] ([OrderAuditInfoID])
GO
ALTER TABLE [dbo].[InternationalVoyage] ADD CONSTRAINT [FK_InternationalVoyage_Orders] FOREIGN KEY ([OrdersFID]) REFERENCES [dbo].[Orders] ([PriKey])
GO
GRANT SELECT ON  [dbo].[InternationalVoyage] TO [MssExec]
GRANT INSERT ON  [dbo].[InternationalVoyage] TO [MssExec]
GRANT DELETE ON  [dbo].[InternationalVoyage] TO [MssExec]
GRANT UPDATE ON  [dbo].[InternationalVoyage] TO [MssExec]
GO