Complex Bitemporal Test

---------------------------- SETUP DATABASE AND MODEL --------------------------------
USE master;

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'ComplexTest')
BEGIN
ALTER DATABASE ComplexTest SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE ComplexTest SET ONLINE;
DROP DATABASE ComplexTest
END

CREATE DATABASE ComplexTest;

USE ComplexTest;


-------------------------------- [Drop Perspectives] ---------------------------------
-- perspectives are recreated every time the script is run
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dCO_Company' and type LIKE '%F%')
DROP FUNCTION [dbo].[dCO_Company];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'pCO_Company' AND type LIKE '%F%')
DROP FUNCTION [dbo].[pCO_Company];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'lCO_Company' AND type LIKE '%V%')
DROP VIEW [dbo].[lCO_Company];
GO
IF EXISTS (SELECT * FROM sys.synonyms WHERE name = 'llCO_Company')
DROP SYNONYM [dbo].[llCO_Company];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'lpCO_Company' and type LIKE '%F%')
DROP FUNCTION [dbo].[lpCO_Company];
GO
IF EXISTS (SELECT * FROM sys.synonyms WHERE name = 'plCO_Company')
DROP SYNONYM [dbo].[plCO_Company];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'ppCO_Company' and type LIKE '%F%')
DROP FUNCTION [dbo].[ppCO_Company];
GO

-------------------------------- [Drop Perspectives] ---------------------------------
-- perspectives are recreated every time the script is run
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dCU_Customer' and type LIKE '%F%')
DROP FUNCTION [dbo].[dCU_Customer];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'pCU_Customer' AND type LIKE '%F%')
DROP FUNCTION [dbo].[pCU_Customer];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'lCU_Customer' AND type LIKE '%V%')
DROP VIEW [dbo].[lCU_Customer];
GO
IF EXISTS (SELECT * FROM sys.synonyms WHERE name = 'llCU_Customer')
DROP SYNONYM [dbo].[llCU_Customer];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'lpCU_Customer' and type LIKE '%F%')
DROP FUNCTION [dbo].[lpCU_Customer];
GO
IF EXISTS (SELECT * FROM sys.synonyms WHERE name = 'plCU_Customer')
DROP SYNONYM [dbo].[plCU_Customer];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'ppCU_Customer' and type LIKE '%F%')
DROP FUNCTION [dbo].[ppCU_Customer];
GO

-------------------------------- [Drop Perspectives] ---------------------------------
-- perspectives are recreated every time the script is run
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dPO_Position' and type LIKE '%F%')
DROP FUNCTION [dbo].[dPO_Position];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'pPO_Position' AND type LIKE '%F%')
DROP FUNCTION [dbo].[pPO_Position];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'lPO_Position' AND type LIKE '%V%')
DROP VIEW [dbo].[lPO_Position];
GO
IF EXISTS (SELECT * FROM sys.synonyms WHERE name = 'llPO_Position')
DROP SYNONYM [dbo].[llPO_Position];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'lpPO_Position' and type LIKE '%F%')
DROP FUNCTION [dbo].[lpPO_Position];
GO
IF EXISTS (SELECT * FROM sys.synonyms WHERE name = 'plPO_Position')
DROP SYNONYM [dbo].[plPO_Position];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'ppPO_Position' and type LIKE '%F%')
DROP FUNCTION [dbo].[ppPO_Position];
GO

-------------------------------- [Drop Perspectives] ---------------------------------
-- perspectives are recreated every time the script is run
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dCU_is_PO_holding' and type LIKE '%F%')
DROP FUNCTION [dbo].[dCU_is_PO_holding];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'pCU_is_PO_holding' AND type LIKE '%F%')
DROP FUNCTION [dbo].[pCU_is_PO_holding];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'lCU_is_PO_holding' AND type LIKE '%V%')
DROP VIEW [dbo].[lCU_is_PO_holding];
GO
IF EXISTS (SELECT * FROM sys.synonyms WHERE name = 'llCU_is_PO_holding')
DROP SYNONYM [dbo].[llCU_is_PO_holding];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'lpCU_is_PO_holding' and type LIKE '%F%')
DROP FUNCTION [dbo].[lpCU_is_PO_holding];
GO
IF EXISTS (SELECT * FROM sys.synonyms WHERE name = 'plCU_is_PO_holding')
DROP SYNONYM [dbo].[plCU_is_PO_holding];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'ppCU_is_PO_holding' and type LIKE '%F%')
DROP FUNCTION [dbo].[ppCU_is_PO_holding];
GO

-------------------------------- [Drop Perspectives] ---------------------------------
-- perspectives are recreated every time the script is run
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dPO_for_CO_the' and type LIKE '%F%')
DROP FUNCTION [dbo].[dPO_for_CO_the];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'pPO_for_CO_the' AND type LIKE '%F%')
DROP FUNCTION [dbo].[pPO_for_CO_the];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'lPO_for_CO_the' AND type LIKE '%V%')
DROP VIEW [dbo].[lPO_for_CO_the];
GO
IF EXISTS (SELECT * FROM sys.synonyms WHERE name = 'llPO_for_CO_the')
DROP SYNONYM [dbo].[llPO_for_CO_the];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'lpPO_for_CO_the' and type LIKE '%F%')
DROP FUNCTION [dbo].[lpPO_for_CO_the];
GO
IF EXISTS (SELECT * FROM sys.synonyms WHERE name = 'plPO_for_CO_the')
DROP SYNONYM [dbo].[plPO_for_CO_the];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'ppPO_for_CO_the' and type LIKE '%F%')
DROP FUNCTION [dbo].[ppPO_for_CO_the];
GO

----------------------------------- [Knot Table] -------------------------------------
-- TIC_Ticker table
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'TIC_Ticker' and type LIKE '%U%')
CREATE TABLE [dbo].[TIC_Ticker] (
   TIC_ID smallint not null,
   TIC_Ticker varchar(6) not null,
   constraint pkTIC_Ticker primary key (
      TIC_ID asc
   ),
   constraint uqTIC_Ticker unique (
      TIC_Ticker
   )
);
GO

----------------------------------- [Knot Table] -------------------------------------
-- RAT_Rating table
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'RAT_Rating' and type LIKE '%U%')
CREATE TABLE [dbo].[RAT_Rating] (
   RAT_ID tinyint not null,
   RAT_Rating char(1) not null,
   constraint pkRAT_Rating primary key (
      RAT_ID asc
   ),
   constraint uqRAT_Rating unique (
      RAT_Rating
   )
);
GO

---------------------------------- [Anchor Table] ------------------------------------
-- CO_Company table (with 1 attributes)
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'CO_Company' AND type LIKE '%U%')
CREATE TABLE [dbo].[CO_Company] (
   CO_ID int identity(1, 1) not null,
   CO_Dummy bit null,
   constraint pkCO_Company primary key (
      CO_ID asc
   )
);
GO

----------------------- [Key Generation Stored Procedure] ----------------------------
-- CO_Company surrogate key generation stored procedure
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'kCO_Company' AND type in ('P','PC'))
DROP PROCEDURE [dbo].[kCO_Company]
GO
CREATE PROCEDURE [dbo].[kCO_Company] (
   @requestedNumberOfIdentities bigint
) AS
BEGIN
   SET NOCOUNT ON;
   IF @requestedNumberOfIdentities > 0
   BEGIN
      WITH rowGenerator (rowNumber) AS (
         SELECT
            1
         UNION ALL
         SELECT
            rowNumber + 1
         FROM
            rowGenerator
         WHERE
            rowNumber < @requestedNumberOfIdentities
      )
      INSERT INTO [dbo].[CO_Company] (CO_Dummy)
      OUTPUT
         inserted.CO_ID
      SELECT
         null
      FROM
         rowGenerator
      OPTION (maxrecursion 0);
   END
END
GO

--------------------------------- [Attribute Table] ----------------------------------
-- CO_TIC_Company_Ticker table (on CO_Company)
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'CO_TIC_Company_Ticker' AND type LIKE '%U%')
CREATE TABLE [dbo].[CO_TIC_Company_Ticker] (
   CO_ID int not null foreign key references [dbo].[CO_Company](CO_ID),
   TIC_ID smallint not null foreign key references [dbo].[TIC_Ticker](TIC_ID),
   CO_TIC_RecordedAt datetime not null,
   CO_TIC_ErasedAt datetime not null,
   constraint uqCO_TIC_Company_Ticker unique (
      CO_TIC_ErasedAt,
      CO_ID
   ),
   constraint pkCO_TIC_Company_Ticker primary key (
      CO_ID asc,
      CO_TIC_RecordedAt desc,
      CO_TIC_ErasedAt desc
   )
) ON [PRIMARY];
GO

-------------------- [All changing currently recorded perspective] -------------------
-- acCO_TIC_Company_Ticker view
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'acCO_TIC_Company_Ticker' AND type LIKE '%V%')
DROP VIEW [dbo].[acCO_TIC_Company_Ticker];
GO
CREATE VIEW [dbo].[acCO_TIC_Company_Ticker] WITH SCHEMABINDING AS
SELECT
   CO_ID,
   TIC_ID,
   CO_TIC_RecordedAt,
   CO_TIC_ErasedAt
FROM
   [dbo].[CO_TIC_Company_Ticker]
WHERE
   CO_TIC_ErasedAt >= '9999-12-31';
GO

--------------------- [All changing rewind recording perspective] --------------------
-- arCO_TIC_Company_Ticker function
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'arCO_TIC_Company_Ticker' AND type LIKE '%F%')
DROP FUNCTION [dbo].[arCO_TIC_Company_Ticker];
GO
CREATE FUNCTION [dbo].[arCO_TIC_Company_Ticker] (@recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
   CO_ID,
   TIC_ID,
   CO_TIC_RecordedAt,
   CO_TIC_ErasedAt
FROM
   [dbo].[CO_TIC_Company_Ticker]
WHERE
   CO_TIC_ErasedAt > @recordingTimepoint
AND
   CO_TIC_RecordedAt <= @recordingTimepoint;
GO

------------------------------- [Latest Perspective] ---------------------------------
-- CO_Company viewed as is (given by the latest available information)
--------------------------------------------------------------------------------------
CREATE VIEW [dbo].[lCO_Company] WITH SCHEMABINDING AS
SELECT
   [CO].CO_ID,
   [TIC].TIC_ID,
   [TIC].TIC_Ticker,
   [CO_TIC].CO_TIC_RecordedAt,
   [CO_TIC].CO_TIC_ErasedAt
FROM
   [dbo].[CO_Company] [CO]
LEFT JOIN
   [dbo].[acCO_TIC_Company_Ticker] [CO_TIC]
ON
   [CO_TIC].CO_ID = [CO].CO_ID
LEFT JOIN
   [dbo].[TIC_Ticker] [TIC]
ON
   [TIC].TIC_ID = [CO_TIC].TIC_ID;
GO

CREATE SYNONYM [dbo].[llCO_Company] FOR [dbo].[lCO_Company];
GO

----------------- [Latest changing point-in-recording Perspective] -------------------
-- CO_Company viewed as is at the given recording time
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[lpCO_Company] (@recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
   [CO].CO_ID,
   [TIC].TIC_ID,
   [TIC].TIC_Ticker,
   [CO_TIC].CO_TIC_RecordedAt,
   [CO_TIC].CO_TIC_ErasedAt
FROM
   [dbo].[CO_Company] [CO]
LEFT JOIN
   [dbo].[arCO_TIC_Company_Ticker] (@recordingTimepoint) [CO_TIC]
ON
   [CO_TIC].CO_ID = [CO].CO_ID
LEFT JOIN
   [dbo].[TIC_Ticker] [TIC]
ON
   [TIC].TIC_ID = [CO_TIC].TIC_ID;
GO

--------------------------------- [Insert Trigger] -----------------------------------
-- CO_Company insert trigger on the latest perspective
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'itCO_Company')
DROP TRIGGER [dbo].[itCO_Company]
GO
CREATE TRIGGER [dbo].[itCO_Company] ON lCO_Company
INSTEAD OF INSERT
AS
BEGIN
   SET NOCOUNT ON;
   DECLARE @now DATETIME2(7) = SYSDATETIME();
   DECLARE @CO TABLE (
      Row int identity(1,1) not null primary key,
      CO_ID int not null
   );
   INSERT INTO [dbo].[CO_Company](
      CO_Dummy
   )
   OUTPUT
      inserted.CO_ID
   INTO
      @CO
   SELECT
      null
   FROM
      inserted
   WHERE
      inserted.CO_ID is null;
   INSERT INTO [dbo].[CO_TIC_Company_Ticker](
      CO_ID,
      TIC_ID,
      CO_TIC_RecordedAt,
      CO_TIC_ErasedAt
   )
   SELECT
      ISNULL(i.CO_ID, a.CO_ID),
      k.TIC_ID,
      ISNULL(i.CO_TIC_RecordedAt, @now),
      ISNULL(i.CO_TIC_ErasedAt, '9999-12-31')
   FROM (
      SELECT
         *,
         ROW_NUMBER() OVER (PARTITION BY CO_ID ORDER BY CO_ID) AS Row
      FROM
         inserted
   ) i
   LEFT JOIN
      @CO a
   ON
      a.Row = i.Row
   JOIN
      [dbo].[TIC_Ticker] k
   ON
      k.TIC_Ticker = i.TIC_Ticker
   WHERE
      i.TIC_Ticker is not null;
END
GO

--------------------------------- [Update Trigger] -----------------------------------
-- CO_Company update trigger on the latest perspective
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'utCO_Company')
DROP TRIGGER [dbo].[utCO_Company]
GO
CREATE TRIGGER [dbo].[utCO_Company] ON lCO_Company
INSTEAD OF UPDATE
AS
BEGIN
   SET NOCOUNT ON;
   DECLARE @now DATETIME2(7) = SYSDATETIME();
   IF(UPDATE(CO_ID) OR UPDATE(CO_TIC_RecordedAt))
   RAISERROR('Primary key columns are not updatable.', 16, 1);
   IF(UPDATE(CO_TIC_ErasedAt))
   UPDATE CO_TIC
   SET
      CO_TIC.CO_TIC_ErasedAt = ISNULL(i.CO_TIC_ErasedAt, @now)
   FROM
      [dbo].[CO_TIC_Company_Ticker] CO_TIC
   JOIN
      inserted i
   ON
      i.CO_ID = CO_TIC.CO_ID
   AND
      i.CO_TIC_RecordedAt = CO_TIC.CO_TIC_RecordedAt;
   IF(UPDATE(TIC_Ticker))
   INSERT INTO [dbo].[CO_TIC_Company_Ticker](
      CO_ID,
      TIC_ID,
      CO_TIC_RecordedAt,
      CO_TIC_ErasedAt
   )
   SELECT
      i.CO_ID,
      k.TIC_ID,
      CASE WHEN UPDATE(CO_TIC_ErasedAt) THEN ISNULL(i.CO_TIC_ErasedAt, @now) ELSE @now END,
      '9999-12-31'
   FROM
      inserted i
   JOIN
      [dbo].[TIC_Ticker] k
   ON
      k.TIC_Ticker = i.TIC_Ticker;
END
GO

--------------------------------- [Delete Trigger] -----------------------------------
-- CO_Company delete trigger on the latest perspective
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'dtCO_Company')
DROP TRIGGER [dbo].[dtCO_Company]
GO
CREATE TRIGGER [dbo].[dtCO_Company] ON lCO_Company
INSTEAD OF DELETE
AS
BEGIN
   SET NOCOUNT ON;
   DECLARE @now DATETIME2(7) = SYSDATETIME();
   UPDATE CO_TIC
   SET
      CO_TIC.CO_TIC_ErasedAt = @now
   FROM
      [dbo].[CO_TIC_Company_Ticker] CO_TIC
   JOIN
      deleted d
   ON
      d.CO_ID = CO_TIC.CO_ID
   AND
      d.CO_TIC_RecordedAt = CO_TIC.CO_TIC_RecordedAt;
END
GO

---------------------------- [Point-in-Time Perspective] -----------------------------
-- CO_Company viewed as was (at the given timepoint)
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[pCO_Company] (@changingTimepoint DATETIME2(7))
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
   [CO].CO_ID,
   [TIC].TIC_ID,
   [TIC].TIC_Ticker,
   [CO_TIC].CO_TIC_RecordedAt,
   [CO_TIC].CO_TIC_ErasedAt
FROM
   [dbo].[CO_Company] [CO]
LEFT JOIN
   [dbo].[acCO_TIC_Company_Ticker] [CO_TIC]
ON
   [CO_TIC].CO_ID = [CO].CO_ID
LEFT JOIN
   [dbo].[TIC_Ticker] [TIC]
ON
   [TIC].TIC_ID = [CO_TIC].TIC_ID;
GO

CREATE SYNONYM [dbo].[plCO_Company] FOR [dbo].[pCO_Company];
GO

----------------- [Point-in-changing point-in-recording Perspective] -------------------
-- CO_Company viewed as was (timepoint) at the given recording timepoint
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[ppCO_Company] (@changingTimepoint DATETIME2(7), @recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
   [CO].CO_ID,
   [TIC].TIC_ID,
   [TIC].TIC_Ticker,
   [CO_TIC].CO_TIC_RecordedAt,
   [CO_TIC].CO_TIC_ErasedAt
FROM
   [dbo].[CO_Company] [CO]
LEFT JOIN
   [dbo].[arCO_TIC_Company_Ticker] (@recordingTimepoint) [CO_TIC]
ON
   [CO_TIC].CO_ID = [CO].CO_ID
LEFT JOIN
   [dbo].[TIC_Ticker] [TIC]
ON
   [TIC].TIC_ID = [CO_TIC].TIC_ID;
GO

---------------------------------- [Anchor Table] ------------------------------------
-- CU_Customer table (with 2 attributes)
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'CU_Customer' AND type LIKE '%U%')
CREATE TABLE [dbo].[CU_Customer] (
   CU_ID int identity(1, 1) not null,
   CU_Dummy bit null,
   constraint pkCU_Customer primary key (
      CU_ID asc
   )
);
GO

----------------------- [Key Generation Stored Procedure] ----------------------------
-- CU_Customer surrogate key generation stored procedure
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'kCU_Customer' AND type in ('P','PC'))
DROP PROCEDURE [dbo].[kCU_Customer]
GO
CREATE PROCEDURE [dbo].[kCU_Customer] (
   @requestedNumberOfIdentities bigint
) AS
BEGIN
   SET NOCOUNT ON;
   IF @requestedNumberOfIdentities > 0
   BEGIN
      WITH rowGenerator (rowNumber) AS (
         SELECT
            1
         UNION ALL
         SELECT
            rowNumber + 1
         FROM
            rowGenerator
         WHERE
            rowNumber < @requestedNumberOfIdentities
      )
      INSERT INTO [dbo].[CU_Customer] (CU_Dummy)
      OUTPUT
         inserted.CU_ID
      SELECT
         null
      FROM
         rowGenerator
      OPTION (maxrecursion 0);
   END
END
GO

--------------------------------- [Attribute Table] ----------------------------------
-- CU_RAT_Customer_Rating table (on CU_Customer)
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'CU_RAT_Customer_Rating' AND type LIKE '%U%')
CREATE TABLE [dbo].[CU_RAT_Customer_Rating] (
   CU_ID int not null foreign key references [dbo].[CU_Customer](CU_ID),
   RAT_ID tinyint not null foreign key references [dbo].[RAT_Rating](RAT_ID),
   CU_RAT_ChangedAt datetime not null,
   CU_RAT_RecordedAt datetime not null,
   CU_RAT_ErasedAt datetime not null,
   constraint uqCU_RAT_Customer_Rating unique (
      CU_RAT_ErasedAt,
      CU_ID,
      CU_RAT_ChangedAt desc
   ),
   constraint pkCU_RAT_Customer_Rating primary key (
      CU_ID asc,
      CU_RAT_ChangedAt desc,
      CU_RAT_RecordedAt desc,
      CU_RAT_ErasedAt desc
   )
) ON [PRIMARY];
GO

-------------------- [All changing currently recorded perspective] -------------------
-- acCU_RAT_Customer_Rating view
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'acCU_RAT_Customer_Rating' AND type LIKE '%V%')
DROP VIEW [dbo].[acCU_RAT_Customer_Rating];
GO
CREATE VIEW [dbo].[acCU_RAT_Customer_Rating] WITH SCHEMABINDING AS
SELECT
   CU_ID,
   RAT_ID,
   CU_RAT_ChangedAt,
   CU_RAT_RecordedAt,
   CU_RAT_ErasedAt
FROM
   [dbo].[CU_RAT_Customer_Rating]
WHERE
   CU_RAT_ErasedAt >= '9999-12-31';
GO

--------------------- [All changing rewind recording perspective] --------------------
-- arCU_RAT_Customer_Rating function
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'arCU_RAT_Customer_Rating' AND type LIKE '%F%')
DROP FUNCTION [dbo].[arCU_RAT_Customer_Rating];
GO
CREATE FUNCTION [dbo].[arCU_RAT_Customer_Rating] (@recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
   CU_ID,
   RAT_ID,
   CU_RAT_ChangedAt,
   CU_RAT_RecordedAt,
   CU_RAT_ErasedAt
FROM
   [dbo].[CU_RAT_Customer_Rating]
WHERE
   CU_RAT_ErasedAt > @recordingTimepoint
AND
   CU_RAT_RecordedAt <= @recordingTimepoint;
GO

------------------ [Rewind changing currently recorded perspective] ------------------
-- rcCU_RAT_Customer_Rating function
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'rcCU_RAT_Customer_Rating' AND type LIKE '%F%')
DROP FUNCTION [dbo].[rcCU_RAT_Customer_Rating];
GO
CREATE FUNCTION [dbo].[rcCU_RAT_Customer_Rating] (@changingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
   CU_ID,
   RAT_ID,
   CU_RAT_ChangedAt,
   CU_RAT_RecordedAt,
   CU_RAT_ErasedAt
FROM
   [dbo].[CU_RAT_Customer_Rating]
WHERE
   CU_RAT_ErasedAt >= '9999-12-31'
AND
   CU_RAT_ChangedAt <= @changingTimepoint;
GO

------------------- [Rewind changing rewind recording perspective] -------------------
-- rrCU_RAT_Customer_Rating function
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'rrCU_RAT_Customer_Rating' AND type LIKE '%F%')
DROP FUNCTION [dbo].[rrCU_RAT_Customer_Rating];
GO
CREATE FUNCTION [dbo].[rrCU_RAT_Customer_Rating] (
   @changingTimepoint datetime,
   @recordingTimepoint datetime
)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
   CU_ID,
   RAT_ID,
   CU_RAT_ChangedAt,
   CU_RAT_RecordedAt,
   CU_RAT_ErasedAt
FROM
   [dbo].[CU_RAT_Customer_Rating]
WHERE
   CU_RAT_ErasedAt > @recordingTimepoint
AND
   CU_RAT_ChangedAt <= @changingTimepoint
AND
   CU_RAT_RecordedAt <= @recordingTimepoint;
GO

--------------------------------- [Attribute Table] ----------------------------------
-- CU_NAM_Customer_Name table (on CU_Customer)
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'CU_NAM_Customer_Name' AND type LIKE '%U%')
CREATE TABLE [dbo].[CU_NAM_Customer_Name] (
   CU_ID int not null foreign key references [dbo].[CU_Customer](CU_ID),
   CU_NAM_Customer_Name varchar(42) not null,
   CU_NAM_RecordedAt datetime not null,
   CU_NAM_ErasedAt datetime not null,
   constraint uqCU_NAM_Customer_Name unique (
      CU_NAM_ErasedAt,
      CU_ID
   ),
   constraint pkCU_NAM_Customer_Name primary key (
      CU_ID asc,
      CU_NAM_RecordedAt desc,
      CU_NAM_ErasedAt desc
   )
) ON [PRIMARY];
GO

-------------------- [All changing currently recorded perspective] -------------------
-- acCU_NAM_Customer_Name view
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'acCU_NAM_Customer_Name' AND type LIKE '%V%')
DROP VIEW [dbo].[acCU_NAM_Customer_Name];
GO
CREATE VIEW [dbo].[acCU_NAM_Customer_Name] WITH SCHEMABINDING AS
SELECT
   CU_ID,
   CU_NAM_Customer_Name,
   CU_NAM_RecordedAt,
   CU_NAM_ErasedAt
FROM
   [dbo].[CU_NAM_Customer_Name]
WHERE
   CU_NAM_ErasedAt >= '9999-12-31';
GO

--------------------- [All changing rewind recording perspective] --------------------
-- arCU_NAM_Customer_Name function
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'arCU_NAM_Customer_Name' AND type LIKE '%F%')
DROP FUNCTION [dbo].[arCU_NAM_Customer_Name];
GO
CREATE FUNCTION [dbo].[arCU_NAM_Customer_Name] (@recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
   CU_ID,
   CU_NAM_Customer_Name,
   CU_NAM_RecordedAt,
   CU_NAM_ErasedAt
FROM
   [dbo].[CU_NAM_Customer_Name]
WHERE
   CU_NAM_ErasedAt > @recordingTimepoint
AND
   CU_NAM_RecordedAt <= @recordingTimepoint;
GO

------------------------------- [Latest Perspective] ---------------------------------
-- CU_Customer viewed as is (given by the latest available information)
--------------------------------------------------------------------------------------
CREATE VIEW [dbo].[lCU_Customer] WITH SCHEMABINDING AS
SELECT
   [CU].CU_ID,
   [RAT].RAT_ID,
   [RAT].RAT_Rating,
   [CU_RAT].CU_RAT_ChangedAt,
   [CU_RAT].CU_RAT_RecordedAt,
   [CU_RAT].CU_RAT_ErasedAt,
   [CU_NAM].CU_NAM_Customer_Name,
   [CU_NAM].CU_NAM_RecordedAt,
   [CU_NAM].CU_NAM_ErasedAt
FROM
   [dbo].[CU_Customer] [CU]
LEFT JOIN
   [dbo].[acCU_RAT_Customer_Rating] [CU_RAT]
ON
   [CU_RAT].CU_ID = [CU].CU_ID
AND
   [CU_RAT].CU_RAT_ChangedAt = (
      SELECT
         max(sub.CU_RAT_ChangedAt)
      FROM
         [dbo].[acCU_RAT_Customer_Rating] sub
      WHERE
         sub.CU_ID = [CU].CU_ID
   )
LEFT JOIN
   [dbo].[RAT_Rating] [RAT]
ON
   [RAT].RAT_ID = [CU_RAT].RAT_ID
LEFT JOIN
   [dbo].[acCU_NAM_Customer_Name] [CU_NAM]
ON
   [CU_NAM].CU_ID = [CU].CU_ID;
GO

CREATE SYNONYM [dbo].[llCU_Customer] FOR [dbo].[lCU_Customer];
GO

----------------- [Latest changing point-in-recording Perspective] -------------------
-- CU_Customer viewed as is at the given recording time
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[lpCU_Customer] (@recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
   [CU].CU_ID,
   [RAT].RAT_ID,
   [RAT].RAT_Rating,
   [CU_RAT].CU_RAT_ChangedAt,
   [CU_RAT].CU_RAT_RecordedAt,
   [CU_RAT].CU_RAT_ErasedAt,
   [CU_NAM].CU_NAM_Customer_Name,
   [CU_NAM].CU_NAM_RecordedAt,
   [CU_NAM].CU_NAM_ErasedAt
FROM
   [dbo].[CU_Customer] [CU]
LEFT JOIN
   [dbo].[arCU_RAT_Customer_Rating] (@recordingTimepoint) [CU_RAT]
ON
   [CU_RAT].CU_ID = [CU].CU_ID
AND
   [CU_RAT].CU_RAT_ChangedAt = (
      SELECT
         max(sub.CU_RAT_ChangedAt)
      FROM
         [dbo].[arCU_RAT_Customer_Rating] (@recordingTimepoint) sub
      WHERE
         sub.CU_ID = [CU].CU_ID
   )
LEFT JOIN
   [dbo].[RAT_Rating] [RAT]
ON
   [RAT].RAT_ID = [CU_RAT].RAT_ID
LEFT JOIN
   [dbo].[arCU_NAM_Customer_Name] (@recordingTimepoint) [CU_NAM]
ON
   [CU_NAM].CU_ID = [CU].CU_ID;
GO

--------------------------------- [Insert Trigger] -----------------------------------
-- CU_Customer insert trigger on the latest perspective
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'itCU_Customer')
DROP TRIGGER [dbo].[itCU_Customer]
GO
CREATE TRIGGER [dbo].[itCU_Customer] ON lCU_Customer
INSTEAD OF INSERT
AS
BEGIN
   SET NOCOUNT ON;
   DECLARE @now DATETIME2(7) = SYSDATETIME();
   DECLARE @CU TABLE (
      Row int identity(1,1) not null primary key,
      CU_ID int not null
   );
   INSERT INTO [dbo].[CU_Customer](
      CU_Dummy
   )
   OUTPUT
      inserted.CU_ID
   INTO
      @CU
   SELECT
      null
   FROM
      inserted
   WHERE
      inserted.CU_ID is null;
   INSERT INTO [dbo].[CU_RAT_Customer_Rating](
      CU_ID,
      RAT_ID,
      CU_RAT_ChangedAt,
      CU_RAT_RecordedAt,
      CU_RAT_ErasedAt
   )
   SELECT
      ISNULL(i.CU_ID, a.CU_ID),
      k.RAT_ID,
      ISNULL(i.CU_RAT_ChangedAt, @now),
      ISNULL(i.CU_RAT_RecordedAt, @now),
      ISNULL(i.CU_RAT_ErasedAt, '9999-12-31')
   FROM (
      SELECT
         *,
         ROW_NUMBER() OVER (PARTITION BY CU_ID ORDER BY CU_ID) AS Row
      FROM
         inserted
   ) i
   LEFT JOIN
      @CU a
   ON
      a.Row = i.Row
   JOIN
      [dbo].[RAT_Rating] k
   ON
      k.RAT_Rating = i.RAT_Rating
   WHERE
      i.RAT_Rating is not null;
   INSERT INTO [dbo].[CU_NAM_Customer_Name](
      CU_ID,
      CU_NAM_Customer_Name,
      CU_NAM_RecordedAt,
      CU_NAM_ErasedAt
   )
   SELECT
      ISNULL(i.CU_ID, a.CU_ID),
      i.CU_NAM_Customer_Name,
      ISNULL(i.CU_NAM_RecordedAt, @now),
      ISNULL(i.CU_NAM_ErasedAt, '9999-12-31')
   FROM (
      SELECT
         *,
         ROW_NUMBER() OVER (PARTITION BY CU_ID ORDER BY CU_ID) AS Row
      FROM
         inserted
   ) i
   LEFT JOIN
      @CU a
   ON
      a.Row = i.Row
   WHERE
      i.CU_NAM_Customer_Name is not null;
END
GO

--------------------------------- [Update Trigger] -----------------------------------
-- CU_Customer update trigger on the latest perspective
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'utCU_Customer')
DROP TRIGGER [dbo].[utCU_Customer]
GO
CREATE TRIGGER [dbo].[utCU_Customer] ON lCU_Customer
INSTEAD OF UPDATE
AS
BEGIN
   SET NOCOUNT ON;
   DECLARE @now DATETIME2(7) = SYSDATETIME();
   IF(UPDATE(CU_ID) OR UPDATE(CU_RAT_ChangedAt) OR UPDATE(CU_RAT_RecordedAt))
   RAISERROR('Primary key columns are not updatable.', 16, 1);
   IF(UPDATE(CU_RAT_ErasedAt))
   UPDATE CU_RAT
   SET
      CU_RAT.CU_RAT_ErasedAt = ISNULL(i.CU_RAT_ErasedAt, @now)
   FROM
      [dbo].[CU_RAT_Customer_Rating] CU_RAT
   JOIN
      inserted i
   ON
      i.CU_ID = CU_RAT.CU_ID
   AND
      i.CU_RAT_ChangedAt = CU_RAT.CU_RAT_ChangedAt
   AND
      i.CU_RAT_RecordedAt = CU_RAT.CU_RAT_RecordedAt;
   IF(UPDATE(RAT_Rating))
   INSERT INTO [dbo].[CU_RAT_Customer_Rating](
      CU_ID,
      RAT_ID,
      CU_RAT_ChangedAt,
      CU_RAT_RecordedAt,
      CU_RAT_ErasedAt
   )
   SELECT
      i.CU_ID,
      k.RAT_ID,
      CASE WHEN UPDATE(CU_RAT_ErasedAt) THEN i.CU_RAT_ChangedAt ELSE @now END,
      CASE WHEN UPDATE(CU_RAT_ErasedAt) THEN ISNULL(i.CU_RAT_ErasedAt, @now) ELSE @now END,
      '9999-12-31'
   FROM
      inserted i
   JOIN
      [dbo].[RAT_Rating] k
   ON
      k.RAT_Rating = i.RAT_Rating;
   IF(UPDATE(CU_ID) OR UPDATE(CU_NAM_RecordedAt))
   RAISERROR('Primary key columns are not updatable.', 16, 1);
   IF(UPDATE(CU_NAM_ErasedAt))
   UPDATE CU_NAM
   SET
      CU_NAM.CU_NAM_ErasedAt = ISNULL(i.CU_NAM_ErasedAt, @now)
   FROM
      [dbo].[CU_NAM_Customer_Name] CU_NAM
   JOIN
      inserted i
   ON
      i.CU_ID = CU_NAM.CU_ID
   AND
      i.CU_NAM_RecordedAt = CU_NAM.CU_NAM_RecordedAt;
   IF(UPDATE(CU_NAM_Customer_Name))
   INSERT INTO [dbo].[CU_NAM_Customer_Name](
      CU_ID,
      CU_NAM_Customer_Name,
      CU_NAM_RecordedAt,
      CU_NAM_ErasedAt
   )
   SELECT
      i.CU_ID,
      i.CU_NAM_Customer_Name,
      CASE WHEN UPDATE(CU_NAM_ErasedAt) THEN ISNULL(i.CU_NAM_ErasedAt, @now) ELSE @now END,
      '9999-12-31'
   FROM
      inserted i;
END
GO

--------------------------------- [Delete Trigger] -----------------------------------
-- CU_Customer delete trigger on the latest perspective
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'dtCU_Customer')
DROP TRIGGER [dbo].[dtCU_Customer]
GO
CREATE TRIGGER [dbo].[dtCU_Customer] ON lCU_Customer
INSTEAD OF DELETE
AS
BEGIN
   SET NOCOUNT ON;
   DECLARE @now DATETIME2(7) = SYSDATETIME();
   UPDATE CU_RAT
   SET
      CU_RAT.CU_RAT_ErasedAt = @now
   FROM
      [dbo].[CU_RAT_Customer_Rating] CU_RAT
   JOIN
      deleted d
   ON
      d.CU_ID = CU_RAT.CU_ID
   AND
      d.CU_RAT_ChangedAt = CU_RAT.CU_RAT_ChangedAt
   AND
      d.CU_RAT_RecordedAt = CU_RAT.CU_RAT_RecordedAt;
   UPDATE CU_NAM
   SET
      CU_NAM.CU_NAM_ErasedAt = @now
   FROM
      [dbo].[CU_NAM_Customer_Name] CU_NAM
   JOIN
      deleted d
   ON
      d.CU_ID = CU_NAM.CU_ID
   AND
      d.CU_NAM_RecordedAt = CU_NAM.CU_NAM_RecordedAt;
END
GO

---------------------------- [Point-in-Time Perspective] -----------------------------
-- CU_Customer viewed as was (at the given timepoint)
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[pCU_Customer] (@changingTimepoint DATETIME2(7))
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
   [CU].CU_ID,
   [RAT].RAT_ID,
   [RAT].RAT_Rating,
   [CU_RAT].CU_RAT_ChangedAt,
   [CU_RAT].CU_RAT_RecordedAt,
   [CU_RAT].CU_RAT_ErasedAt,
   [CU_NAM].CU_NAM_Customer_Name,
   [CU_NAM].CU_NAM_RecordedAt,
   [CU_NAM].CU_NAM_ErasedAt
FROM
   [dbo].[CU_Customer] [CU]
LEFT JOIN
   [dbo].[rcCU_RAT_Customer_Rating] (@changingTimepoint) [CU_RAT]
ON
   [CU_RAT].CU_ID = [CU].CU_ID
AND
   [CU_RAT].CU_RAT_ChangedAt = (
      SELECT
         max(sub.CU_RAT_ChangedAt)
      FROM
         [dbo].[rcCU_RAT_Customer_Rating] (@changingTimepoint) sub
      WHERE
         sub.CU_ID = [CU].CU_ID
   )
LEFT JOIN
   [dbo].[RAT_Rating] [RAT]
ON
   [RAT].RAT_ID = [CU_RAT].RAT_ID
LEFT JOIN
   [dbo].[acCU_NAM_Customer_Name] [CU_NAM]
ON
   [CU_NAM].CU_ID = [CU].CU_ID;
GO

CREATE SYNONYM [dbo].[plCU_Customer] FOR [dbo].[pCU_Customer];
GO

----------------- [Point-in-changing point-in-recording Perspective] -------------------
-- CU_Customer viewed as was (timepoint) at the given recording timepoint
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[ppCU_Customer] (@changingTimepoint DATETIME2(7), @recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
   [CU].CU_ID,
   [RAT].RAT_ID,
   [RAT].RAT_Rating,
   [CU_RAT].CU_RAT_ChangedAt,
   [CU_RAT].CU_RAT_RecordedAt,
   [CU_RAT].CU_RAT_ErasedAt,
   [CU_NAM].CU_NAM_Customer_Name,
   [CU_NAM].CU_NAM_RecordedAt,
   [CU_NAM].CU_NAM_ErasedAt
FROM
   [dbo].[CU_Customer] [CU]
LEFT JOIN
   [dbo].[rrCU_RAT_Customer_Rating] (@changingTimepoint, @recordingTimepoint) [CU_RAT]
ON
   [CU_RAT].CU_ID = [CU].CU_ID
AND
   [CU_RAT].CU_RAT_ChangedAt = (
      SELECT
         max(sub.CU_RAT_ChangedAt)
      FROM
         [dbo].[rrCU_RAT_Customer_Rating] (@changingTimepoint, @recordingTimepoint) sub
      WHERE
         sub.CU_ID = [CU].CU_ID
   )
LEFT JOIN
   [dbo].[RAT_Rating] [RAT]
ON
   [RAT].RAT_ID = [CU_RAT].RAT_ID
LEFT JOIN
   [dbo].[arCU_NAM_Customer_Name] (@recordingTimepoint) [CU_NAM]
ON
   [CU_NAM].CU_ID = [CU].CU_ID;
GO

------------------------------ [Difference Perspective] ------------------------------
-- CU_Customer viewed by differences in CU_RAT_Customer_Rating
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dCU_RAT_Customer_Rating' AND type LIKE '%F%')
DROP FUNCTION [dbo].[dCU_RAT_Customer_Rating];
GO
CREATE FUNCTION [dbo].[dCU_RAT_Customer_Rating] (@intervalStart DATETIME2(7), @intervalEnd DATETIME2(7))
RETURNS TABLE AS RETURN
SELECT
   timepoints.inspectedTimepoint,
   [CU].*
FROM (
   SELECT DISTINCT
      CU_RAT_ChangedAt AS inspectedTimepoint
   FROM
      [dbo].[CU_RAT_Customer_Rating]
   WHERE
      CU_RAT_ChangedAt BETWEEN @intervalStart AND @intervalEnd
) timepoints
CROSS APPLY
   pCU_Customer(timepoints.inspectedTimepoint) [CU];
GO

------------------------------ [Difference Perspective] ------------------------------
-- CU_Customer viewed by differences in every historized attribute
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[dCU_Customer] (@intervalStart DATETIME2(7), @intervalEnd DATETIME2(7))
RETURNS TABLE AS RETURN
SELECT
   timepoints.inspectedTimepoint,
   [CU].*
FROM (
   SELECT DISTINCT
      CU_RAT_ChangedAt AS inspectedTimepoint
   FROM
      [dbo].[CU_RAT_Customer_Rating]
   WHERE
      CU_RAT_ChangedAt BETWEEN @intervalStart AND @intervalEnd
) timepoints
CROSS APPLY
   [dbo].[pCU_Customer](timepoints.inspectedTimepoint) [CU];
GO

---------------------------------- [Anchor Table] ------------------------------------
-- PO_Position table (with 1 attributes)
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'PO_Position' AND type LIKE '%U%')
CREATE TABLE [dbo].[PO_Position] (
   PO_ID int identity(1, 1) not null,
   PO_Dummy bit null,
   constraint pkPO_Position primary key (
      PO_ID asc
   )
);
GO

----------------------- [Key Generation Stored Procedure] ----------------------------
-- PO_Position surrogate key generation stored procedure
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'kPO_Position' AND type in ('P','PC'))
DROP PROCEDURE [dbo].[kPO_Position]
GO
CREATE PROCEDURE [dbo].[kPO_Position] (
   @requestedNumberOfIdentities bigint
) AS
BEGIN
   SET NOCOUNT ON;
   IF @requestedNumberOfIdentities > 0
   BEGIN
      WITH rowGenerator (rowNumber) AS (
         SELECT
            1
         UNION ALL
         SELECT
            rowNumber + 1
         FROM
            rowGenerator
         WHERE
            rowNumber < @requestedNumberOfIdentities
      )
      INSERT INTO [dbo].[PO_Position] (PO_Dummy)
      OUTPUT
         inserted.PO_ID
      SELECT
         null
      FROM
         rowGenerator
      OPTION (maxrecursion 0);
   END
END
GO

--------------------------------- [Attribute Table] ----------------------------------
-- PO_QUA_Position_Quantity table (on PO_Position)
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'PO_QUA_Position_Quantity' AND type LIKE '%U%')
CREATE TABLE [dbo].[PO_QUA_Position_Quantity] (
   PO_ID int not null foreign key references [dbo].[PO_Position](PO_ID),
   PO_QUA_Position_Quantity int not null,
   PO_QUA_ChangedAt datetime not null,
   PO_QUA_RecordedAt datetime not null,
   PO_QUA_ErasedAt datetime not null,
   constraint uqPO_QUA_Position_Quantity unique (
      PO_QUA_ErasedAt,
      PO_ID,
      PO_QUA_ChangedAt desc
   ),
   constraint pkPO_QUA_Position_Quantity primary key (
      PO_ID asc,
      PO_QUA_ChangedAt desc,
      PO_QUA_RecordedAt desc,
      PO_QUA_ErasedAt desc
   )
) ON [PRIMARY];
GO

-------------------- [All changing currently recorded perspective] -------------------
-- acPO_QUA_Position_Quantity view
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'acPO_QUA_Position_Quantity' AND type LIKE '%V%')
DROP VIEW [dbo].[acPO_QUA_Position_Quantity];
GO
CREATE VIEW [dbo].[acPO_QUA_Position_Quantity] WITH SCHEMABINDING AS
SELECT
   PO_ID,
   PO_QUA_Position_Quantity,
   PO_QUA_ChangedAt,
   PO_QUA_RecordedAt,
   PO_QUA_ErasedAt
FROM
   [dbo].[PO_QUA_Position_Quantity]
WHERE
   PO_QUA_ErasedAt >= '9999-12-31';
GO

--------------------- [All changing rewind recording perspective] --------------------
-- arPO_QUA_Position_Quantity function
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'arPO_QUA_Position_Quantity' AND type LIKE '%F%')
DROP FUNCTION [dbo].[arPO_QUA_Position_Quantity];
GO
CREATE FUNCTION [dbo].[arPO_QUA_Position_Quantity] (@recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
   PO_ID,
   PO_QUA_Position_Quantity,
   PO_QUA_ChangedAt,
   PO_QUA_RecordedAt,
   PO_QUA_ErasedAt
FROM
   [dbo].[PO_QUA_Position_Quantity]
WHERE
   PO_QUA_ErasedAt > @recordingTimepoint
AND
   PO_QUA_RecordedAt <= @recordingTimepoint;
GO

------------------ [Rewind changing currently recorded perspective] ------------------
-- rcPO_QUA_Position_Quantity function
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'rcPO_QUA_Position_Quantity' AND type LIKE '%F%')
DROP FUNCTION [dbo].[rcPO_QUA_Position_Quantity];
GO
CREATE FUNCTION [dbo].[rcPO_QUA_Position_Quantity] (@changingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
   PO_ID,
   PO_QUA_Position_Quantity,
   PO_QUA_ChangedAt,
   PO_QUA_RecordedAt,
   PO_QUA_ErasedAt
FROM
   [dbo].[PO_QUA_Position_Quantity]
WHERE
   PO_QUA_ErasedAt >= '9999-12-31'
AND
   PO_QUA_ChangedAt <= @changingTimepoint;
GO

------------------- [Rewind changing rewind recording perspective] -------------------
-- rrPO_QUA_Position_Quantity function
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'rrPO_QUA_Position_Quantity' AND type LIKE '%F%')
DROP FUNCTION [dbo].[rrPO_QUA_Position_Quantity];
GO
CREATE FUNCTION [dbo].[rrPO_QUA_Position_Quantity] (
   @changingTimepoint datetime,
   @recordingTimepoint datetime
)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
   PO_ID,
   PO_QUA_Position_Quantity,
   PO_QUA_ChangedAt,
   PO_QUA_RecordedAt,
   PO_QUA_ErasedAt
FROM
   [dbo].[PO_QUA_Position_Quantity]
WHERE
   PO_QUA_ErasedAt > @recordingTimepoint
AND
   PO_QUA_ChangedAt <= @changingTimepoint
AND
   PO_QUA_RecordedAt <= @recordingTimepoint;
GO

------------------------------- [Latest Perspective] ---------------------------------
-- PO_Position viewed as is (given by the latest available information)
--------------------------------------------------------------------------------------
CREATE VIEW [dbo].[lPO_Position] WITH SCHEMABINDING AS
SELECT
   [PO].PO_ID,
   [PO_QUA].PO_QUA_Position_Quantity,
   [PO_QUA].PO_QUA_ChangedAt,
   [PO_QUA].PO_QUA_RecordedAt,
   [PO_QUA].PO_QUA_ErasedAt
FROM
   [dbo].[PO_Position] [PO]
LEFT JOIN
   [dbo].[acPO_QUA_Position_Quantity] [PO_QUA]
ON
   [PO_QUA].PO_ID = [PO].PO_ID
AND
   [PO_QUA].PO_QUA_ChangedAt = (
      SELECT
         max(sub.PO_QUA_ChangedAt)
      FROM
         [dbo].[acPO_QUA_Position_Quantity] sub
      WHERE
         sub.PO_ID = [PO].PO_ID
   );
GO

CREATE SYNONYM [dbo].[llPO_Position] FOR [dbo].[lPO_Position];
GO

----------------- [Latest changing point-in-recording Perspective] -------------------
-- PO_Position viewed as is at the given recording time
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[lpPO_Position] (@recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
   [PO].PO_ID,
   [PO_QUA].PO_QUA_Position_Quantity,
   [PO_QUA].PO_QUA_ChangedAt,
   [PO_QUA].PO_QUA_RecordedAt,
   [PO_QUA].PO_QUA_ErasedAt
FROM
   [dbo].[PO_Position] [PO]
LEFT JOIN
   [dbo].[arPO_QUA_Position_Quantity] (@recordingTimepoint) [PO_QUA]
ON
   [PO_QUA].PO_ID = [PO].PO_ID
AND
   [PO_QUA].PO_QUA_ChangedAt = (
      SELECT
         max(sub.PO_QUA_ChangedAt)
      FROM
         [dbo].[arPO_QUA_Position_Quantity] (@recordingTimepoint) sub
      WHERE
         sub.PO_ID = [PO].PO_ID
   );
GO

--------------------------------- [Insert Trigger] -----------------------------------
-- PO_Position insert trigger on the latest perspective
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'itPO_Position')
DROP TRIGGER [dbo].[itPO_Position]
GO
CREATE TRIGGER [dbo].[itPO_Position] ON lPO_Position
INSTEAD OF INSERT
AS
BEGIN
   SET NOCOUNT ON;
   DECLARE @now DATETIME2(7) = SYSDATETIME();
   DECLARE @PO TABLE (
      Row int identity(1,1) not null primary key,
      PO_ID int not null
   );
   INSERT INTO [dbo].[PO_Position](
      PO_Dummy
   )
   OUTPUT
      inserted.PO_ID
   INTO
      @PO
   SELECT
      null
   FROM
      inserted
   WHERE
      inserted.PO_ID is null;
   INSERT INTO [dbo].[PO_QUA_Position_Quantity](
      PO_ID,
      PO_QUA_Position_Quantity,
      PO_QUA_ChangedAt,
      PO_QUA_RecordedAt,
      PO_QUA_ErasedAt
   )
   SELECT
      ISNULL(i.PO_ID, a.PO_ID),
      i.PO_QUA_Position_Quantity,
      ISNULL(i.PO_QUA_ChangedAt, @now),
      ISNULL(i.PO_QUA_RecordedAt, @now),
      ISNULL(i.PO_QUA_ErasedAt, '9999-12-31')
   FROM (
      SELECT
         *,
         ROW_NUMBER() OVER (PARTITION BY PO_ID ORDER BY PO_ID) AS Row
      FROM
         inserted
   ) i
   LEFT JOIN
      @PO a
   ON
      a.Row = i.Row
   WHERE
      i.PO_QUA_Position_Quantity is not null;
END
GO

--------------------------------- [Update Trigger] -----------------------------------
-- PO_Position update trigger on the latest perspective
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'utPO_Position')
DROP TRIGGER [dbo].[utPO_Position]
GO
CREATE TRIGGER [dbo].[utPO_Position] ON lPO_Position
INSTEAD OF UPDATE
AS
BEGIN
   SET NOCOUNT ON;
   DECLARE @now DATETIME2(7) = SYSDATETIME();
   IF(UPDATE(PO_ID) OR UPDATE(PO_QUA_ChangedAt) OR UPDATE(PO_QUA_RecordedAt))
   RAISERROR('Primary key columns are not updatable.', 16, 1);
   IF(UPDATE(PO_QUA_ErasedAt))
   UPDATE PO_QUA
   SET
      PO_QUA.PO_QUA_ErasedAt = ISNULL(i.PO_QUA_ErasedAt, @now)
   FROM
      [dbo].[PO_QUA_Position_Quantity] PO_QUA
   JOIN
      inserted i
   ON
      i.PO_ID = PO_QUA.PO_ID
   AND
      i.PO_QUA_ChangedAt = PO_QUA.PO_QUA_ChangedAt
   AND
      i.PO_QUA_RecordedAt = PO_QUA.PO_QUA_RecordedAt;
   IF(UPDATE(PO_QUA_Position_Quantity))
   INSERT INTO [dbo].[PO_QUA_Position_Quantity](
      PO_ID,
      PO_QUA_Position_Quantity,
      PO_QUA_ChangedAt,
      PO_QUA_RecordedAt,
      PO_QUA_ErasedAt
   )
   SELECT
      i.PO_ID,
      i.PO_QUA_Position_Quantity,
      CASE WHEN UPDATE(PO_QUA_ErasedAt) THEN i.PO_QUA_ChangedAt ELSE @now END,
      CASE WHEN UPDATE(PO_QUA_ErasedAt) THEN ISNULL(i.PO_QUA_ErasedAt, @now) ELSE @now END,
      '9999-12-31'
   FROM
      inserted i;
END
GO

--------------------------------- [Delete Trigger] -----------------------------------
-- PO_Position delete trigger on the latest perspective
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'dtPO_Position')
DROP TRIGGER [dbo].[dtPO_Position]
GO
CREATE TRIGGER [dbo].[dtPO_Position] ON lPO_Position
INSTEAD OF DELETE
AS
BEGIN
   SET NOCOUNT ON;
   DECLARE @now DATETIME2(7) = SYSDATETIME();
   UPDATE PO_QUA
   SET
      PO_QUA.PO_QUA_ErasedAt = @now
   FROM
      [dbo].[PO_QUA_Position_Quantity] PO_QUA
   JOIN
      deleted d
   ON
      d.PO_ID = PO_QUA.PO_ID
   AND
      d.PO_QUA_ChangedAt = PO_QUA.PO_QUA_ChangedAt
   AND
      d.PO_QUA_RecordedAt = PO_QUA.PO_QUA_RecordedAt;
END
GO

---------------------------- [Point-in-Time Perspective] -----------------------------
-- PO_Position viewed as was (at the given timepoint)
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[pPO_Position] (@changingTimepoint DATETIME2(7))
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
   [PO].PO_ID,
   [PO_QUA].PO_QUA_Position_Quantity,
   [PO_QUA].PO_QUA_ChangedAt,
   [PO_QUA].PO_QUA_RecordedAt,
   [PO_QUA].PO_QUA_ErasedAt
FROM
   [dbo].[PO_Position] [PO]
LEFT JOIN
   [dbo].[rcPO_QUA_Position_Quantity] (@changingTimepoint) [PO_QUA]
ON
   [PO_QUA].PO_ID = [PO].PO_ID
AND
   [PO_QUA].PO_QUA_ChangedAt = (
      SELECT
         max(sub.PO_QUA_ChangedAt)
      FROM
         [dbo].[rcPO_QUA_Position_Quantity] (@changingTimepoint) sub
      WHERE
         sub.PO_ID = [PO].PO_ID
   );
GO

CREATE SYNONYM [dbo].[plPO_Position] FOR [dbo].[pPO_Position];
GO

----------------- [Point-in-changing point-in-recording Perspective] -------------------
-- PO_Position viewed as was (timepoint) at the given recording timepoint
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[ppPO_Position] (@changingTimepoint DATETIME2(7), @recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
   [PO].PO_ID,
   [PO_QUA].PO_QUA_Position_Quantity,
   [PO_QUA].PO_QUA_ChangedAt,
   [PO_QUA].PO_QUA_RecordedAt,
   [PO_QUA].PO_QUA_ErasedAt
FROM
   [dbo].[PO_Position] [PO]
LEFT JOIN
   [dbo].[rrPO_QUA_Position_Quantity] (@changingTimepoint, @recordingTimepoint) [PO_QUA]
ON
   [PO_QUA].PO_ID = [PO].PO_ID
AND
   [PO_QUA].PO_QUA_ChangedAt = (
      SELECT
         max(sub.PO_QUA_ChangedAt)
      FROM
         [dbo].[rrPO_QUA_Position_Quantity] (@changingTimepoint, @recordingTimepoint) sub
      WHERE
         sub.PO_ID = [PO].PO_ID
   );
GO

------------------------------ [Difference Perspective] ------------------------------
-- PO_Position viewed by differences in PO_QUA_Position_Quantity
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dPO_QUA_Position_Quantity' AND type LIKE '%F%')
DROP FUNCTION [dbo].[dPO_QUA_Position_Quantity];
GO
CREATE FUNCTION [dbo].[dPO_QUA_Position_Quantity] (@intervalStart DATETIME2(7), @intervalEnd DATETIME2(7))
RETURNS TABLE AS RETURN
SELECT
   timepoints.inspectedTimepoint,
   [PO].*
FROM (
   SELECT DISTINCT
      PO_QUA_ChangedAt AS inspectedTimepoint
   FROM
      [dbo].[PO_QUA_Position_Quantity]
   WHERE
      PO_QUA_ChangedAt BETWEEN @intervalStart AND @intervalEnd
) timepoints
CROSS APPLY
   pPO_Position(timepoints.inspectedTimepoint) [PO];
GO

------------------------------ [Difference Perspective] ------------------------------
-- PO_Position viewed by differences in every historized attribute
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[dPO_Position] (@intervalStart DATETIME2(7), @intervalEnd DATETIME2(7))
RETURNS TABLE AS RETURN
SELECT
   timepoints.inspectedTimepoint,
   [PO].*
FROM (
   SELECT DISTINCT
      PO_QUA_ChangedAt AS inspectedTimepoint
   FROM
      [dbo].[PO_QUA_Position_Quantity]
   WHERE
      PO_QUA_ChangedAt BETWEEN @intervalStart AND @intervalEnd
) timepoints
CROSS APPLY
   [dbo].[pPO_Position](timepoints.inspectedTimepoint) [PO];
GO

------------------------------------- [Tie Table] ------------------------------------
-- CU_is_PO_holding table (2-ary)
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'CU_is_PO_holding' and type LIKE '%U%')
CREATE TABLE [dbo].[CU_is_PO_holding] (
   CU_ID_is int not null foreign key references [dbo].[CU_Customer](CU_ID),
   PO_ID_holding int not null foreign key references [dbo].[PO_Position](PO_ID),
   CU_is_PO_holding_RecordedAt datetime not null,
   CU_is_PO_holding_ErasedAt datetime not null,
   constraint uqCU_is_PO_holding unique (
      CU_is_PO_holding_ErasedAt,
      PO_ID_holding asc
   ),
   constraint pkCU_is_PO_holding primary key (
      PO_ID_holding asc,
      CU_is_PO_holding_RecordedAt desc,
      CU_is_PO_holding_ErasedAt desc
   )
) ON [PRIMARY];
GO

-------------------------- [Currently recorded perspective] --------------------------
-- cCU_is_PO_holding view
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'cCU_is_PO_holding' AND type LIKE '%V%')
DROP VIEW [dbo].[cCU_is_PO_holding];
GO
CREATE VIEW [dbo].[cCU_is_PO_holding] WITH SCHEMABINDING AS
SELECT
   tie.CU_ID_is,
   tie.PO_ID_holding,
   tie.CU_is_PO_holding_RecordedAt,
   tie.CU_is_PO_holding_ErasedAt
FROM
   [dbo].[CU_is_PO_holding] tie
WHERE
   CU_is_PO_holding_ErasedAt >= '9999-12-31';
GO

--------------------------- [Rewind recording perspective] ---------------------------
-- rCU_is_PO_holding function
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'rCU_is_PO_holding' AND type LIKE '%F%')
DROP FUNCTION [dbo].[rCU_is_PO_holding];
GO
CREATE FUNCTION [dbo].[rCU_is_PO_holding] (@recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
   tie.CU_ID_is,
   tie.PO_ID_holding,
   tie.CU_is_PO_holding_RecordedAt,
   tie.CU_is_PO_holding_ErasedAt
FROM
   [dbo].[CU_is_PO_holding] tie
WHERE
   CU_is_PO_holding_ErasedAt > @recordingTimepoint
AND
   CU_is_PO_holding_RecordedAt <= @recordingTimepoint;
GO

--------------------------------- [Latest Perspective] -------------------------------
-- CU_is_PO_holding viewed as is (given by the latest available information)
--------------------------------------------------------------------------------------
CREATE VIEW [dbo].[lCU_is_PO_holding] WITH SCHEMABINDING AS
SELECT
   tie.CU_ID_is,
   tie.PO_ID_holding,
   tie.CU_is_PO_holding_RecordedAt,
   tie.CU_is_PO_holding_ErasedAt
FROM
   [dbo].[cCU_is_PO_holding] tie;
GO

CREATE SYNONYM [dbo].[llCU_is_PO_holding] FOR [dbo].[lCU_is_PO_holding];
GO

----------------- [Latest changing point-in-recording Perspective] -------------------
-- CU_is_PO_holding viewed as is at the given recording timepoint
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[lpCU_is_PO_holding] (@recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
   tie.CU_ID_is,
   tie.PO_ID_holding,
   tie.CU_is_PO_holding_RecordedAt,
   tie.CU_is_PO_holding_ErasedAt
FROM
   [dbo].[rCU_is_PO_holding](@recordingTimepoint) tie;
GO

---------------------------- [Point-in-Time Perspective] -----------------------------
-- CU_is_PO_holding viewed as was (at the given timepoint)
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[pCU_is_PO_holding] (@changingTimepoint DATETIME2(7))
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
   tie.CU_ID_is,
   tie.PO_ID_holding,
   tie.CU_is_PO_holding_RecordedAt,
   tie.CU_is_PO_holding_ErasedAt
FROM
   [dbo].[cCU_is_PO_holding] tie;
GO

CREATE SYNONYM [dbo].[plCU_is_PO_holding] FOR [dbo].[pCU_is_PO_holding];
GO

----------------- [Point-in-changing point-in-recording Perspective] -------------------
-- CU_is_PO_holding viewed as was (timepoint) at the given recording timepoint
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[ppCU_is_PO_holding] (@changingTimepoint DATETIME2(7), @recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
   tie.CU_ID_is,
   tie.PO_ID_holding,
   tie.CU_is_PO_holding_RecordedAt,
   tie.CU_is_PO_holding_ErasedAt
FROM
   [dbo].[rCU_is_PO_holding](@recordingTimepoint) tie;
GO

------------------------------ [Difference Perspective] ------------------------------
-- CU_is_PO_holding viewed by differences in the tie
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[dCU_is_PO_holding] (@intervalStart DATETIME2(7), @intervalEnd DATETIME2(7))
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
   tie.CU_ID_is,
   tie.PO_ID_holding,
   tie.CU_is_PO_holding_RecordedAt,
   tie.CU_is_PO_holding_ErasedAt
FROM
   [dbo].[cCU_is_PO_holding] tie;
GO

------------------------------------- [Tie Table] ------------------------------------
-- PO_for_CO_the table (2-ary)
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'PO_for_CO_the' and type LIKE '%U%')
CREATE TABLE [dbo].[PO_for_CO_the] (
   PO_ID_for int not null foreign key references [dbo].[PO_Position](PO_ID),
   CO_ID_the int not null foreign key references [dbo].[CO_Company](CO_ID),
   PO_for_CO_the_RecordedAt datetime not null,
   PO_for_CO_the_ErasedAt datetime not null,
   constraint uqPO_for_CO_the unique (
      PO_for_CO_the_ErasedAt,
      PO_ID_for asc
   ),
   constraint pkPO_for_CO_the primary key (
      PO_ID_for asc,
      PO_for_CO_the_RecordedAt desc,
      PO_for_CO_the_ErasedAt desc
   )
) ON [PRIMARY];
GO

-------------------------- [Currently recorded perspective] --------------------------
-- cPO_for_CO_the view
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'cPO_for_CO_the' AND type LIKE '%V%')
DROP VIEW [dbo].[cPO_for_CO_the];
GO
CREATE VIEW [dbo].[cPO_for_CO_the] WITH SCHEMABINDING AS
SELECT
   tie.PO_ID_for,
   tie.CO_ID_the,
   tie.PO_for_CO_the_RecordedAt,
   tie.PO_for_CO_the_ErasedAt
FROM
   [dbo].[PO_for_CO_the] tie
WHERE
   PO_for_CO_the_ErasedAt >= '9999-12-31';
GO

--------------------------- [Rewind recording perspective] ---------------------------
-- rPO_for_CO_the function
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'rPO_for_CO_the' AND type LIKE '%F%')
DROP FUNCTION [dbo].[rPO_for_CO_the];
GO
CREATE FUNCTION [dbo].[rPO_for_CO_the] (@recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
   tie.PO_ID_for,
   tie.CO_ID_the,
   tie.PO_for_CO_the_RecordedAt,
   tie.PO_for_CO_the_ErasedAt
FROM
   [dbo].[PO_for_CO_the] tie
WHERE
   PO_for_CO_the_ErasedAt > @recordingTimepoint
AND
   PO_for_CO_the_RecordedAt <= @recordingTimepoint;
GO

--------------------------------- [Latest Perspective] -------------------------------
-- PO_for_CO_the viewed as is (given by the latest available information)
--------------------------------------------------------------------------------------
CREATE VIEW [dbo].[lPO_for_CO_the] WITH SCHEMABINDING AS
SELECT
   tie.PO_ID_for,
   tie.CO_ID_the,
   tie.PO_for_CO_the_RecordedAt,
   tie.PO_for_CO_the_ErasedAt
FROM
   [dbo].[cPO_for_CO_the] tie;
GO

CREATE SYNONYM [dbo].[llPO_for_CO_the] FOR [dbo].[lPO_for_CO_the];
GO

----------------- [Latest changing point-in-recording Perspective] -------------------
-- PO_for_CO_the viewed as is at the given recording timepoint
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[lpPO_for_CO_the] (@recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
   tie.PO_ID_for,
   tie.CO_ID_the,
   tie.PO_for_CO_the_RecordedAt,
   tie.PO_for_CO_the_ErasedAt
FROM
   [dbo].[rPO_for_CO_the](@recordingTimepoint) tie;
GO

---------------------------- [Point-in-Time Perspective] -----------------------------
-- PO_for_CO_the viewed as was (at the given timepoint)
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[pPO_for_CO_the] (@changingTimepoint DATETIME2(7))
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
   tie.PO_ID_for,
   tie.CO_ID_the,
   tie.PO_for_CO_the_RecordedAt,
   tie.PO_for_CO_the_ErasedAt
FROM
   [dbo].[cPO_for_CO_the] tie;
GO

CREATE SYNONYM [dbo].[plPO_for_CO_the] FOR [dbo].[pPO_for_CO_the];
GO

----------------- [Point-in-changing point-in-recording Perspective] -------------------
-- PO_for_CO_the viewed as was (timepoint) at the given recording timepoint
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[ppPO_for_CO_the] (@changingTimepoint DATETIME2(7), @recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
   tie.PO_ID_for,
   tie.CO_ID_the,
   tie.PO_for_CO_the_RecordedAt,
   tie.PO_for_CO_the_ErasedAt
FROM
   [dbo].[rPO_for_CO_the](@recordingTimepoint) tie;
GO

------------------------------ [Difference Perspective] ------------------------------
-- PO_for_CO_the viewed by differences in the tie
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[dPO_for_CO_the] (@intervalStart DATETIME2(7), @intervalEnd DATETIME2(7))
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
   tie.PO_ID_for,
   tie.CO_ID_the,
   tie.PO_for_CO_the_RecordedAt,
   tie.PO_for_CO_the_ErasedAt
FROM
   [dbo].[cPO_for_CO_the] tie;
GO

--------------------------------- [Schema Evolution] ---------------------------------
-- Schema evolution tables, views and functions
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = '_Schema' and type LIKE '%U%')
   CREATE TABLE [_Schema] (
      [version] int identity(1, 1) not null primary key,
      [activation] DATETIME2(7) not null,
      [schema] xml not null
   );
GO

INSERT INTO [_Schema] (
   [activation],
   [schema]
)
SELECT
   current_timestamp,
   N'<schema><metadata format="0.94" temporalization="bi"/><knot mnemonic="TIC" descriptor="Ticker" identity="smallint" dataRange="varchar(6)"><metadata capsule="dbo" generator="false"/><layout x="829.78" y="32.45" fixed="false"/></knot><knot mnemonic="RAT" descriptor="Rating" identity="tinyint" dataRange="char(1)"><metadata capsule="dbo" generator="false"/><layout x="464.58" y="368.24" fixed="false"/></knot><anchor mnemonic="CO" descriptor="Company" identity="int"><metadata capsule="dbo" generator="true"/><attribute mnemonic="TIC" descriptor="Ticker" knotRange="TIC"><metadata capsule="dbo"/><layout x="800.42" y="78.62" fixed="false"/></attribute><layout x="777.21" y="152.28" fixed="false"/></anchor><anchor mnemonic="CU" descriptor="Customer" identity="int"><metadata capsule="dbo" generator="true"/><attribute mnemonic="RAT" descriptor="Rating" timeRange="datetime" knotRange="RAT"><metadata capsule="dbo"/><layout x="520.76" y="403.15" fixed="false"/></attribute><attribute mnemonic="NAM" descriptor="Name" dataRange="varchar(42)"><metadata capsule="dbo"/><layout x="623.23" y="476.92" fixed="false"/></attribute><layout x="601.62" y="413.27" fixed="false"/></anchor><anchor mnemonic="PO" descriptor="Position" identity="int"><metadata capsule="dbo" generator="true"/><attribute mnemonic="QUA" descriptor="Quantity" timeRange="datetime" dataRange="int"><metadata capsule="dbo"/><layout x="609.43" y="229.66" fixed="false"/></attribute><layout x="672.26" y="267.43" fixed="false"/></anchor><tie><anchorRole role="is" type="CU" identifier="false"/><anchorRole role="holding" type="PO" identifier="true"/><metadata capsule="dbo"/><layout x="647.30" y="349.69" fixed="false"/></tie><tie><anchorRole role="for" type="PO" identifier="true"/><anchorRole role="the" type="CO" identifier="false"/><metadata capsule="dbo"/><layout x="731.35" y="220.87" fixed="false"/></tie></schema>';
GO

IF EXISTS (SELECT * FROM sys.views WHERE name = '_Anchor')
DROP VIEW [_Anchor]
GO

CREATE VIEW [_Anchor]
AS
SELECT
   S.version,
   S.activation,
   Nodeset.anchor.value('concat(@mnemonic, "_", @descriptor)', 'nvarchar(max)') as [name],
   Nodeset.anchor.value('metadata[1]/@capsule', 'nvarchar(max)') as [capsule],
   Nodeset.anchor.value('@mnemonic', 'nvarchar(max)') as [mnemonic],
   Nodeset.anchor.value('@descriptor', 'nvarchar(max)') as [descriptor],
   Nodeset.anchor.value('@identity', 'nvarchar(max)') as [identity],
   Nodeset.anchor.value('metadata[1]/@generator', 'nvarchar(max)') as [generator],
   Nodeset.anchor.value('count(attribute)', 'int') as [numberOfAttributes]
FROM
   [_Schema] S
CROSS APPLY
   S.[schema].nodes('/schema/anchor') as Nodeset(anchor);
GO

IF EXISTS (SELECT * FROM sys.views WHERE name = '_Knot')
DROP VIEW [_Knot]
GO

CREATE VIEW [_Knot]
AS
SELECT
   S.version,
   S.activation,
   Nodeset.knot.value('concat(@mnemonic, "_", @descriptor)', 'nvarchar(max)') as [name],
   Nodeset.knot.value('metadata[1]/@capsule', 'nvarchar(max)') as [capsule],
   Nodeset.knot.value('@mnemonic', 'nvarchar(max)') as [mnemonic],
   Nodeset.knot.value('@descriptor', 'nvarchar(max)') as [descriptor],
   Nodeset.knot.value('@identity', 'nvarchar(max)') as [identity],
   Nodeset.knot.value('metadata[1]/@generator', 'nvarchar(max)') as [generator],
   Nodeset.knot.value('@dataRange', 'nvarchar(max)') as [dataRange]
FROM
   [_Schema] S
CROSS APPLY
   S.[schema].nodes('/schema/knot') as Nodeset(knot);
GO

IF EXISTS (SELECT * FROM sys.views WHERE name = '_Attribute')
DROP VIEW [_Attribute]
GO

CREATE VIEW [_Attribute]
AS
SELECT
   S.version,
   S.activation,
   ParentNodeset.anchor.value('concat(@mnemonic, "_")', 'nvarchar(max)') +
   Nodeset.attribute.value('concat(@mnemonic, "_")', 'nvarchar(max)') +
   ParentNodeset.anchor.value('concat(@descriptor, "_")', 'nvarchar(max)') +
   Nodeset.attribute.value('@descriptor', 'nvarchar(max)') as [name],
   Nodeset.attribute.value('metadata[1]/@capsule', 'nvarchar(max)') as [capsule],
   Nodeset.attribute.value('@mnemonic', 'nvarchar(max)') as [mnemonic],
   Nodeset.attribute.value('@descriptor', 'nvarchar(max)') as [descriptor],
   ParentNodeset.anchor.value('@mnemonic', 'nvarchar(max)') as [anchorMnemonic],
   ParentNodeset.anchor.value('@descriptor', 'nvarchar(max)') as [anchorDescriptor],
   ParentNodeset.anchor.value('@identity', 'nvarchar(max)') as [anchorIdentity],
   Nodeset.attribute.value('@dataRange', 'nvarchar(max)') as [dataRange],
   Nodeset.attribute.value('@knotRange', 'nvarchar(max)') as [knotRange],
   Nodeset.attribute.value('@timeRange', 'nvarchar(max)') as [timeRange]
FROM
   [_Schema] S
CROSS APPLY
   S.[schema].nodes('/schema/anchor') as ParentNodeset(anchor)
OUTER APPLY
   ParentNodeset.anchor.nodes('attribute') as Nodeset(attribute);
GO

IF EXISTS (SELECT * FROM sys.views WHERE name = '_Tie')
DROP VIEW [_Tie]
GO

CREATE VIEW [_Tie]
AS
SELECT
   S.version,
   S.activation,
   REPLACE(Nodeset.tie.query('
      for $role in *[local-name() = "anchorRole" or local-name() = "knotRole"]
      return concat($role/@type, "_", $role/@role)
   ').value('.', 'nvarchar(max)'), ' ', '_') as [name],
   Nodeset.tie.value('metadata[1]/@capsule', 'nvarchar(max)') as [capsule],
   Nodeset.tie.value('count(anchorRole) + count(knotRole)', 'int') as [numberOfRoles],
   Nodeset.tie.query('
      for $role in *[local-name() = "anchorRole" or local-name() = "knotRole"]
      return string($role/@role)
   ').value('.', 'nvarchar(max)') as [roles],
   Nodeset.tie.value('count(anchorRole)', 'int') as [numberOfAnchors],
   Nodeset.tie.query('
      for $role in anchorRole
      return string($role/@type)
   ').value('.', 'nvarchar(max)') as [anchors],
   Nodeset.tie.value('count(knotRole)', 'int') as [numberOfKnots],
   Nodeset.tie.query('
      for $role in knotRole
      return string($role/@type)
   ').value('.', 'nvarchar(max)') as [knots],
   Nodeset.tie.value('count(*[local-name() = "anchorRole" or local-name() = "knotRole"][@identifier = "true"])', 'int') as [numberOfIdentifiers],
   Nodeset.tie.query('
      for $role in *[local-name() = "anchorRole" or local-name() = "knotRole"][@identifier = "true"]
      return string($role/@type)
   ').value('.', 'nvarchar(max)') as [identifiers],
   Nodeset.tie.value('@timeRange', 'nvarchar(max)') as [timeRange]
FROM
   [_Schema] S
CROSS APPLY
   S.[schema].nodes('/schema/tie') as Nodeset(tie);
GO

IF EXISTS (SELECT * FROM sys.objects WHERE name = '_Evolution' and type LIKE '%F%')
DROP FUNCTION [_Evolution];
GO

CREATE FUNCTION _Evolution (
      @timepoint AS DATETIME2(7)
)
RETURNS TABLE
RETURN
SELECT
   V.[version],
   ISNULL(S.[name], T.[name]) AS [name],
   ISNULL(V.[activation], T.[create_date]) AS [activation],
   CASE
      WHEN S.[name] is null THEN
         CASE
            WHEN T.[create_date] > (
               SELECT
                  ISNULL(MAX([activation]), @timepoint)
               FROM
                  [_Schema]
               WHERE
                  [activation] <= @timepoint
            ) THEN 'Future'
            ELSE 'Past'
         END
      WHEN T.[name] is null THEN 'Missing'
      ELSE 'Present'
   END AS Existence
FROM (
   SELECT
      MAX([version]) as [version],
      MAX([activation]) as [activation]
   FROM
      [_Schema]
   WHERE
      [activation] <= @timepoint
) V
JOIN (
   SELECT
      [name],
      [version]
   FROM
      [_Anchor] a
   UNION ALL
   SELECT
      [name],
      [version]
   FROM
      [_Knot] k
   UNION ALL
   SELECT
      [name],
      [version]
   FROM
      [_Attribute] b
   UNION ALL
   SELECT
      [name],
      [version]
   FROM
      [_Tie] t
) S
ON
   S.[version] = V.[version]
FULL OUTER JOIN (
   SELECT
      [name],
      [create_date]
   FROM
      sys.tables
   WHERE
      [type] like '%U%'
   AND
      LEFT([name], 1) <> '_'
) T
ON
      S.[name] = T.[name]
GO


------------------------------------ LOAD DATA ---------------------------------------

insert into TIC_Ticker (
    TIC_ID,
    TIC_Ticker
) values
(1, 'XOM');

insert into lCO_Company (
    TIC_Ticker,
    CO_TIC_RecordedAt,
    CO_TIC_ErasedAt
) values
('XOM', '2011-02-01', '9999-12-31');

insert into RAT_Rating (
    RAT_ID,
    RAT_Rating
) values
(1, 'A'), (2, 'B'), (3, 'C'), (4, 'D'), (5, 'E');

insert into lCU_Customer (
    CU_NAM_Customer_Name,
    CU_NAM_RecordedAt,
    CU_NAM_ErasedAt
) values
('Bridgewater Associates', '2011-01-01', '9999-12-31');

insert into lCU_Customer (
    CU_ID,
    RAT_Rating,
    CU_RAT_ChangedAt,
    CU_RAT_RecordedAt,
    CU_RAT_ErasedAt
) values
(1, 'D', '2011-03-20', '2011-03-21', '9999-12-31'),
(1, 'E', '2011-03-18', '2011-03-21', '9999-12-31'),
(1, 'A', '2011-03-18', '2011-03-18', '2011-03-19'),
(1, 'D', '2011-03-17', '2011-03-21', '9999-12-31'),
(1, 'D', '2011-03-17', '2011-03-20', '2011-03-21'),
(1, 'A', '2011-03-17', '2011-03-17', '2011-03-18'),
(1, 'B', '2011-03-16', '2011-03-18', '2011-03-19'),
(1, 'C', '2011-03-15', '2011-03-20', '9999-12-31'),
(1, 'C', '2011-03-15', '2011-03-19', '2011-03-20');

-- This SP creates the given number of identities.
exec kPO_Position 1;

insert into lPO_Position (
    PO_ID,
    PO_QUA_Position_Quantity,
    PO_QUA_ChangedAt,
    PO_QUA_RecordedAt,
    PO_QUA_ErasedAt
) values
(1, 1,      '2011-03-17', '2011-03-17', '2011-03-18'),
(1, 20,     '2011-03-16', '2011-03-18', '2011-03-19'),
(1, 300,    '2011-03-15', '2011-03-19', '2011-03-20'),
(1, 4000,   '2011-03-17', '2011-03-20', '2011-03-21'),
(1, 50000,  '2011-03-18', '2011-03-21', '9999-12-31'),
(1, 300,    '2011-03-15', '2011-03-20', '9999-12-31'),
(1, 4000,   '2011-03-17', '2011-03-21', '9999-12-31');

insert into CU_is_PO_holding (
    CU_ID_is,
    PO_ID_holding,
    CU_is_PO_holding_RecordedAt,
    CU_is_PO_holding_ErasedAt
) values
(1, 1, '2011-03-15', '9999-12-31');

insert into PO_for_CO_the (
    PO_ID_for,
    CO_ID_the,
    PO_for_CO_the_RecordedAt,
    PO_for_CO_the_ErasedAt
) values
(1, 1, '2011-03-15', '9999-12-31');

----------------------------------- QUERY DATA ---------------------------------------
-- using latest changing latest recording
--------------------------------------------------------------------------------------
select
    cu.CU_NAM_Customer_Name,
    cu.RAT_Rating,
    po.PO_QUA_Position_Quantity,
    co.TIC_Ticker
from
    llCU_Customer cu
join
    llCU_is_PO_holding cupo
on
    cupo.CU_ID_is = cu.CU_ID
join
    llPO_Position po
on
    po.PO_ID = cupo.PO_ID_holding
join
    llPO_for_CO_the poco
on
    poco.PO_ID_for = po.PO_ID
join
    llCO_Company co
on
    co.CO_ID = poco.CO_ID_the;

--------------------------------------------------------------------------------------
-- using point in changing point in recording
--------------------------------------------------------------------------------------
select
    cu.CU_NAM_Customer_Name,
    cu.RAT_Rating,
    po.PO_QUA_Position_Quantity,
    co.TIC_Ticker
from
    ppCU_Customer('2011-03-16', '2011-03-19') cu
join
    llCU_is_PO_holding cupo
on
    cupo.CU_ID_is = cu.CU_ID
join
    ppPO_Position('2011-03-16', '2011-03-19') po
on
    po.PO_ID = cupo.PO_ID_holding
join
    llPO_for_CO_the poco
on
    poco.PO_ID_for = po.PO_ID
join
    llCO_Company co
on
    co.CO_ID = poco.CO_ID_the;

--------------------------------------------------------------------------------------
-- a pattern for a temporally dependent join (the above are independent)
--------------------------------------------------------------------------------------
select
    cu.CU_NAM_Customer_Name,
    cu.RAT_Rating,
    po.PO_QUA_Position_Quantity,
    po.PO_QUA_ChangedAt,
    co.TIC_Ticker
from
    ppCU_Customer('2011-03-16', '2011-03-19') cu
join
    llCU_is_PO_holding cupo
on
    cupo.CU_ID_is = cu.CU_ID
cross apply
    ppPO_Position((
        select
            min(PO_QUA_ChangedAt)
        from
            PO_QUA_Position_Quantity PO_QUA
        where
            PO_QUA.PO_ID = cupo.PO_ID_holding
        ),
        '9999-12-30'
    ) po
join
    llPO_for_CO_the poco
on
    poco.PO_ID_for = po.PO_ID
join
    llCO_Company co
on
    co.CO_ID = poco.CO_ID_the
where
    po.PO_ID = cupo.PO_ID_holding;

--------------------------------------------------------------------------------------
-- This model will also handle a situation where for
-- example a position was wrongfully associated with
-- a company or customer, in which situation you can
-- use the pp-views on the ties to "time-travel".
--------------------------------------------------------------------------------------