/*
 Simple Bitemporal Test (using Anchor Modeling)
 http://www.temporaldata.com/wp-content/uploads/2011/04/bitemporal-support-for-a-temporal-example.swf
*/

-- Partition function for two partitions, 0 and 1
CREATE PARTITION FUNCTION ReliabilityPartition (bit)
AS RANGE LEFT FOR VALUES(0, 1);

GO

-- Partition scheme using the function above
CREATE PARTITION SCHEME ReliabilityScheme
AS PARTITION ReliabilityPartition
ALL TO ([PRIMARY]);

GO

-- Anchor table
CREATE TABLE [dbo].[FI_FinancialInstrument] (
	[FI_ID] [int] IDENTITY(1,1) NOT NULL,
	[Metadata_FI] [int] NOT NULL,
	PRIMARY KEY CLUSTERED (
		[FI_ID] ASC
	)
);

GO

-- Attribute table
CREATE TABLE [dbo].[FI_RAT_FinancialInstrument_Rating] (
	[FI_ID] [int] NOT NULL,
	[FI_RAT_FinancialInstrument_Rating] [char](1) NOT NULL,
	-- Changing time (Anchor Modeling), Valid time (Snodgrass), Effective time (Johnston)
	[FI_RAT_ChangedAt] [date] NOT NULL,
	-- Recording time (Anchor Modeling), Transaction time (Snodgrass), Assertion time (Johnston)
	[FI_RAT_RecordedAt] [date] NOT NULL,
	[FI_RAT_ErasedAt] [date] NULL,
	[FI_RAT_Erased] [bit] NOT NULL DEFAULT 0,
	[Metadata_FI_RAT] [int] NOT NULL,
	PRIMARY KEY CLUSTERED (
		[FI_ID] ASC,
		[FI_RAT_ChangedAt] DESC,
		[FI_RAT_RecordedAt] DESC,
		[FI_RAT_Erased] ASC
	),
	FOREIGN KEY ([FI_ID]) REFERENCES [dbo].[FI_FinancialInstrument] ([FI_ID])
) ON ReliabilityScheme([FI_RAT_Erased]);

GO

-- Time-traveling function of the attribute
CREATE FUNCTION [dbo].[ppFI_RAT_FinancialInstrument_Rating] (
	@changingTimepoint datetime,
	@recordingTimepoint datetime	
)
RETURNS TABLE
RETURN
SELECT 
	[FI_ID],
	[FI_RAT_FinancialInstrument_Rating],
	[FI_RAT_ChangedAt],
	[FI_RAT_RecordedAt],
	[FI_RAT_ErasedAt],
	[FI_RAT_Erased],
	[Metadata_FI_RAT]
FROM 
	[dbo].[FI_RAT_FinancialInstrument_Rating] [FI_RAT]
WHERE (
		-- use the whole recorded partition
		[FI_RAT].[FI_RAT_Erased] = 0 
	OR (
			-- and in the erased partition
			[FI_RAT].[FI_RAT_Erased] = 1
		AND
			-- search for what was valid at the time
			[FI_RAT].FI_RAT_ErasedAt > @recordingTimepoint 	
	)
)
AND
   [FI_RAT].FI_RAT_ChangedAt <= @changingTimepoint
AND
   [FI_RAT].FI_RAT_RecordedAt <= @recordingTimepoint;

GO

-- Insert trigger on the table
CREATE TRIGGER [dbo].[itFI_RAT_FinancialInstrument_Rating] ON [dbo].[FI_RAT_FinancialInstrument_Rating] 
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE [FI_RAT]
SET
	FI_RAT_ErasedAt = ISNULL(i.[FI_RAT_ErasedAt], getdate()), -- DW/OLTP
	FI_RAT_Erased = 1
FROM
	[dbo].[FI_RAT_FinancialInstrument_Rating] [FI_RAT]
JOIN
	inserted i
ON
	[FI_RAT].FI_ID = i.FI_ID
AND
	[FI_RAT].FI_RAT_ChangedAt = i.FI_RAT_ChangedAt
WHERE
	i.[FI_RAT_Erased] = 1
AND
	[FI_RAT].FI_RAT_Erased = 0;

INSERT INTO	[dbo].[FI_RAT_FinancialInstrument_Rating] (
	[FI_ID],
	[FI_RAT_FinancialInstrument_Rating],
	[FI_RAT_ChangedAt],
	[FI_RAT_RecordedAt],
	[FI_RAT_ErasedAt],
	[FI_RAT_Erased],
	[Metadata_FI_RAT]
)
SELECT
	i.[FI_ID],
	i.[FI_RAT_FinancialInstrument_Rating],
	ISNULL(i.[FI_RAT_ChangedAt], getdate()), -- DW/OLTP
	ISNULL(i.[FI_RAT_RecordedAt], getdate()), -- DW/OLTP
	null,
	ISNULL(i.[FI_RAT_Erased], 0), 
	i.[Metadata_FI_RAT]
FROM
	inserted i
WHERE
	i.FI_RAT_Erased is null
OR
	i.FI_RAT_Erased = 0;
END

GO


/* 
	There are nine 'perspectives' of the information resulting
	from latest, point-in-time, and interval perspectives
	taken over the two timelines (changing and recording).
	I will show three of them here. 
*/

-- latest for changing time and latest for recording time
CREATE VIEW [dbo].[llFI_FinancialInstrument] 
AS
SELECT
   [FI].FI_ID,
   [FI].Metadata_FI,
   [FI_RAT].FI_RAT_FinancialInstrument_Rating,
   [FI_RAT].FI_RAT_ChangedAt,
   [FI_RAT].FI_RAT_RecordedAt,
   [FI_RAT].FI_RAT_Erased,
   [FI_RAT].Metadata_FI_RAT
FROM
   [dbo].[FI_FinancialInstrument] [FI]
LEFT JOIN
   [dbo].[FI_RAT_FinancialInstrument_Rating] [FI_RAT]
ON
   [FI_RAT].FI_RAT_Erased = 0 -- select partition
AND
   [FI_RAT].FI_ID = [FI].FI_ID
AND
   [FI_RAT].FI_RAT_ChangedAt = (
      SELECT
         max(sub.FI_RAT_ChangedAt)
      FROM
         [dbo].[FI_RAT_FinancialInstrument_Rating] sub
      WHERE
		 sub.FI_RAT_Erased = 0
	  AND
         sub.FI_ID = [FI].FI_ID
	);

GO

-- point-in-time for changing time and latest for recording time
CREATE FUNCTION [dbo].[plFI_FinancialInstrument] (
	@timepoint datetime
)
RETURNS TABLE
RETURN
SELECT
   [FI].FI_ID,
   [FI].Metadata_FI,
   [FI_RAT].FI_RAT_FinancialInstrument_Rating,
   [FI_RAT].FI_RAT_ChangedAt,
   [FI_RAT].FI_RAT_RecordedAt,
   [FI_RAT].FI_RAT_Erased,
   [FI_RAT].Metadata_FI_RAT
FROM
   [dbo].[FI_FinancialInstrument] [FI]
LEFT JOIN
   [dbo].[FI_RAT_FinancialInstrument_Rating] [FI_RAT]
ON
   [FI_RAT].FI_RAT_Erased = 0 -- select partition
AND
   [FI_RAT].FI_ID = [FI].FI_ID
AND
   [FI_RAT].FI_RAT_ChangedAt = (
      SELECT
         max(sub.FI_RAT_ChangedAt)
      FROM
         [dbo].[FI_RAT_FinancialInstrument_Rating] sub
      WHERE
		 sub.FI_RAT_Erased = 0
	  AND
         sub.FI_ID = [FI].FI_ID
      AND
		 sub.FI_RAT_ChangedAt <= @timepoint
	);

GO

-- point-in-time for changing time and point-in-time for recording time
CREATE FUNCTION [dbo].[ppFI_FinancialInstrument] (
	@changingTimepoint datetime,
	@recordingTimepoint datetime	
)
RETURNS TABLE
RETURN
SELECT
   [FI].FI_ID,
   [FI].Metadata_FI,
   [FI_RAT].FI_RAT_FinancialInstrument_Rating,
   [FI_RAT].FI_RAT_ChangedAt,
   [FI_RAT].FI_RAT_RecordedAt,
   [FI_RAT].FI_RAT_ErasedAt,
   [FI_RAT].FI_RAT_Erased,
   [FI_RAT].Metadata_FI_RAT
FROM
   [dbo].[FI_FinancialInstrument] [FI]
LEFT JOIN
   [dbo].[ppFI_RAT_FinancialInstrument_Rating] (
   	  @changingTimepoint, 
   	  @recordingTimepoint
   ) [FI_RAT]
ON 
   [FI_RAT].FI_ID = [FI].FI_ID
AND
   [FI_RAT].FI_RAT_ChangedAt = (
      SELECT
         max(sub.FI_RAT_ChangedAt)
      FROM
         [dbo].[ppFI_RAT_FinancialInstrument_Rating] (
   			@changingTimepoint, 
   			@recordingTimepoint
		 ) sub
      WHERE
         sub.FI_ID = [FI_RAT].FI_ID
    );

GO

-- create one financial instrument
delete from FI_FinancialInstrument;
insert into FI_FinancialInstrument (Metadata_FI) values (42);

-- we should have no information whatsoever yet
delete from FI_RAT_FinancialInstrument_Rating;
select * from FI_RAT_FinancialInstrument_Rating;
select * from llFI_FinancialInstrument;

-- DAY 1
-- insert the first ratings for the created financial instrument
insert into FI_RAT_FinancialInstrument_Rating (
	[FI_ID],
	[FI_RAT_FinancialInstrument_Rating],
	[FI_RAT_ChangedAt],
	[FI_RAT_RecordedAt],
	[Metadata_FI_RAT]
) values (1, 'A', '2011-03-17', '2011-03-17', 42);
insert into FI_RAT_FinancialInstrument_Rating (
	[FI_ID],
	[FI_RAT_FinancialInstrument_Rating],
	[FI_RAT_ChangedAt],
	[FI_RAT_RecordedAt],
	[Metadata_FI_RAT]
) values (1, '?', '2011-03-19', '2011-03-17', 42);

-- all rows in the attribute table
select * from FI_RAT_FinancialInstrument_Rating;
-- the latest view
select * from llFI_FinancialInstrument;
-- every day of the week in the test along changing time
select * from plFI_FinancialInstrument('2011-03-15');
select * from plFI_FinancialInstrument('2011-03-16');
select * from plFI_FinancialInstrument('2011-03-17');
select * from plFI_FinancialInstrument('2011-03-18');
select * from plFI_FinancialInstrument('2011-03-19');
select * from plFI_FinancialInstrument('2011-03-20');

-- DAY 2
-- Note that the following requires user knowledge of existing data
-- We need to remove what was wrong before we can insert the correction
insert into FI_RAT_FinancialInstrument_Rating (
	[FI_ID],
	[FI_RAT_ChangedAt],
	[FI_RAT_RecordedAt],
	[FI_RAT_ErasedAt],
	[FI_RAT_Erased],
	[Metadata_FI_RAT]
) values (1, '2011-03-17', '2011-03-17', '2011-03-18', 1, 43);
	
insert into FI_RAT_FinancialInstrument_Rating (
	[FI_ID],
	[FI_RAT_FinancialInstrument_Rating],
	[FI_RAT_ChangedAt],
	[FI_RAT_RecordedAt],
	[Metadata_FI_RAT]
) values (1, 'B', '2011-03-16', '2011-03-18', 43);
insert into FI_RAT_FinancialInstrument_Rating (
	[FI_ID],
	[FI_RAT_FinancialInstrument_Rating],
	[FI_RAT_ChangedAt],
	[FI_RAT_RecordedAt],
	[Metadata_FI_RAT]
) values (1, 'A', '2011-03-18', '2011-03-18', 43);

-- all rows in the attribute table
select * from FI_RAT_FinancialInstrument_Rating;
-- the latest view
select * from llFI_FinancialInstrument;
-- every day of the week in the test along changing time
select * from plFI_FinancialInstrument('2011-03-15');
select * from plFI_FinancialInstrument('2011-03-16');
select * from plFI_FinancialInstrument('2011-03-17');
select * from plFI_FinancialInstrument('2011-03-18');
select * from plFI_FinancialInstrument('2011-03-19');
select * from plFI_FinancialInstrument('2011-03-20');
-- some interesting intersections
select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-17');
select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-18');

-- DAY 3
insert into FI_RAT_FinancialInstrument_Rating (
	[FI_ID],
	[FI_RAT_ChangedAt],
	[FI_RAT_RecordedAt],
	[FI_RAT_ErasedAt],
	[FI_RAT_Erased],
	[Metadata_FI_RAT]
) values (1, '2011-03-16', '2011-03-18', '2011-03-19', 1, 44);
insert into FI_RAT_FinancialInstrument_Rating (
	[FI_ID],
	[FI_RAT_ChangedAt],
	[FI_RAT_RecordedAt],
	[FI_RAT_ErasedAt],
	[FI_RAT_Erased],
	[Metadata_FI_RAT]
) values (1, '2011-03-18', '2011-03-18', '2011-03-19', 1, 44);
insert into FI_RAT_FinancialInstrument_Rating (
	[FI_ID],
	[FI_RAT_ChangedAt],
	[FI_RAT_RecordedAt],
	[FI_RAT_ErasedAt],
	[FI_RAT_Erased],
	[Metadata_FI_RAT]
) values (1, '2011-03-19', '2011-03-17', '2011-03-19', 1, 44);
insert into FI_RAT_FinancialInstrument_Rating (
	[FI_ID],
	[FI_RAT_FinancialInstrument_Rating],
	[FI_RAT_ChangedAt],
	[FI_RAT_RecordedAt],
	[Metadata_FI_RAT]
) values (1, 'C', '2011-03-15', '2011-03-19', 44);
insert into FI_RAT_FinancialInstrument_Rating (
	[FI_ID],
	[FI_RAT_FinancialInstrument_Rating],
	[FI_RAT_ChangedAt],
	[FI_RAT_RecordedAt],
	[Metadata_FI_RAT]
) values (1, '?', '2011-03-20', '2011-03-19', 44);

-- all rows in the attribute table
select * from FI_RAT_FinancialInstrument_Rating;
-- the latest view
select * from llFI_FinancialInstrument;
-- every day of the week in the test along changing time
select * from plFI_FinancialInstrument('2011-03-15');
select * from plFI_FinancialInstrument('2011-03-16');
select * from plFI_FinancialInstrument('2011-03-17');
select * from plFI_FinancialInstrument('2011-03-18');
select * from plFI_FinancialInstrument('2011-03-19');
select * from plFI_FinancialInstrument('2011-03-20');
-- some interesting intersections
select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-17');
select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-18');
select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-19');

-- DAY 4
insert into FI_RAT_FinancialInstrument_Rating (
	[FI_ID],
	[FI_RAT_ChangedAt],
	[FI_RAT_RecordedAt],
	[FI_RAT_ErasedAt],
	[FI_RAT_Erased],
	[Metadata_FI_RAT]
) values (1, '2011-03-20', '2011-03-19', '2011-03-20', 1, 45);
insert into FI_RAT_FinancialInstrument_Rating (
	[FI_ID],
	[FI_RAT_FinancialInstrument_Rating],
	[FI_RAT_ChangedAt],
	[FI_RAT_RecordedAt],
	[Metadata_FI_RAT]
) values (1, 'D', '2011-03-17', '2011-03-20', 45);

-- all rows in the attribute table
select * from FI_RAT_FinancialInstrument_Rating;
-- the latest view
select * from llFI_FinancialInstrument;
-- every day of the week in the test along changing time
select * from plFI_FinancialInstrument('2011-03-15');
select * from plFI_FinancialInstrument('2011-03-16');
select * from plFI_FinancialInstrument('2011-03-17');
select * from plFI_FinancialInstrument('2011-03-18');
select * from plFI_FinancialInstrument('2011-03-19');
select * from plFI_FinancialInstrument('2011-03-20');
-- some interesting intersections
select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-17');
select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-18');
select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-19');
select * from ppFI_FinancialInstrument('2011-03-17', '2011-03-20');


-- DAY 5
insert into FI_RAT_FinancialInstrument_Rating (
	[FI_ID],
	[FI_RAT_FinancialInstrument_Rating],
	[FI_RAT_ChangedAt],
	[FI_RAT_RecordedAt],
	[Metadata_FI_RAT]
) values (1, 'E', '2011-03-18', '2011-03-21', 46);
insert into FI_RAT_FinancialInstrument_Rating (
	[FI_ID],
	[FI_RAT_FinancialInstrument_Rating],
	[FI_RAT_ChangedAt],
	[FI_RAT_RecordedAt],
	[Metadata_FI_RAT]
) values (1, 'D', '2011-03-20', '2011-03-21', 46);

-- all rows in the attribute table
select * from FI_RAT_FinancialInstrument_Rating;
-- the latest view
select * from llFI_FinancialInstrument;
-- every day of the week in the test along changing time
select * from plFI_FinancialInstrument('2011-03-15');
select * from plFI_FinancialInstrument('2011-03-16');
select * from plFI_FinancialInstrument('2011-03-17');
select * from plFI_FinancialInstrument('2011-03-18');
select * from plFI_FinancialInstrument('2011-03-19');
select * from plFI_FinancialInstrument('2011-03-20');
-- some interesting intersections
select * from ppFI_FinancialInstrument('2011-03-18', '2011-03-20');
select * from ppFI_FinancialInstrument('2011-03-18', '2011-03-21');
select * from ppFI_FinancialInstrument('2011-03-19', '2011-03-20');
select * from ppFI_FinancialInstrument('2011-03-19', '2011-03-21');

/* Our own simple test 

Event 1, Arriving at 2005-09-10: 
- The rating of the instrument is 'A' from 2001-01-01. 
Event 2, Arriving at 2005-10-12: 
- The rating of the instrument was actually 'B' from 2001-01-01. 
Event 3, Arriving at 2005-10-20: 
- The rating of the instrument has been set to 'F' from 2003-02-13. 
Event 4, Arriving at 2005-11-09: 
- The rating of the instrument was set to 'D' at 2002-04-10. 
Event 5, Also arriving at 2005-11-09: 
- The rating of the instrument was then changed again to 'E' at 2002-08-20. 
Event 6, Arriving at 2006-09-21:
- The rating was never set to 'F', it was an error. 

*/

-- create another financial instrument
insert into FI_FinancialInstrument (Metadata_FI) values (42);

-- EVENT 1
insert into FI_RAT_FinancialInstrument_Rating (
	[FI_ID],
	[FI_RAT_FinancialInstrument_Rating],
	[FI_RAT_ChangedAt],
	[FI_RAT_RecordedAt],
	[Metadata_FI_RAT]
) values (2, 'A', '2001-01-01', '2005-09-10', 555);

-- all rows in the attribute table
select * from FI_RAT_FinancialInstrument_Rating;
-- the latest view
select * from llFI_FinancialInstrument;
-- before and after
select * from plFI_FinancialInstrument('2000-12-31');
select * from plFI_FinancialInstrument('2001-01-02');


-- EVENT 2
insert into FI_RAT_FinancialInstrument_Rating (
	[FI_ID],
	[FI_RAT_ChangedAt],
	[FI_RAT_ErasedAt],
	[FI_RAT_Erased],
	[Metadata_FI_RAT]
) values (2, '2001-01-01', '2005-10-12', 1, 556);
insert into FI_RAT_FinancialInstrument_Rating (
	[FI_ID],
	[FI_RAT_FinancialInstrument_Rating],
	[FI_RAT_ChangedAt],
	[FI_RAT_RecordedAt],
	[Metadata_FI_RAT]
) values (2, 'B', '2001-01-01', '2005-10-12', 556);

-- all rows in the attribute table
select * from FI_RAT_FinancialInstrument_Rating;
-- the latest view
select * from llFI_FinancialInstrument;
-- before and after
select * from plFI_FinancialInstrument('2000-12-31');
select * from plFI_FinancialInstrument('2001-01-02');
-- before and after in retrospect
select * from ppFI_FinancialInstrument('2000-12-31', '2005-09-22');
select * from ppFI_FinancialInstrument('2001-01-02', '2005-09-22');


-- EVENT 3
insert into FI_RAT_FinancialInstrument_Rating (
	[FI_ID],
	[FI_RAT_FinancialInstrument_Rating],
	[FI_RAT_ChangedAt],
	[FI_RAT_RecordedAt],
	[Metadata_FI_RAT]
) values (2, 'F', '2003-02-13', '2005-10-20', 557);

-- all rows in the attribute table
select * from FI_RAT_FinancialInstrument_Rating;
-- the latest view
select * from llFI_FinancialInstrument;


-- EVENT 4
insert into FI_RAT_FinancialInstrument_Rating (
	[FI_ID],
	[FI_RAT_FinancialInstrument_Rating],
	[FI_RAT_ChangedAt],
	[FI_RAT_RecordedAt],
	[Metadata_FI_RAT]
) values (2, 'D', '2002-04-10', '2005-11-09', 558);

-- all rows in the attribute table
select * from FI_RAT_FinancialInstrument_Rating;
-- the latest view
select * from llFI_FinancialInstrument;
-- before 
select * from plFI_FinancialInstrument('2003-02-12');


-- EVENT 5
insert into FI_RAT_FinancialInstrument_Rating (
	[FI_ID],
	[FI_RAT_FinancialInstrument_Rating],
	[FI_RAT_ChangedAt],
	[FI_RAT_RecordedAt],
	[Metadata_FI_RAT]
) values (2, 'E', '2002-08-20', '2005-11-09', 559);

-- all rows in the attribute table
select * from FI_RAT_FinancialInstrument_Rating;
-- the latest view
select * from llFI_FinancialInstrument;
-- before 
select * from plFI_FinancialInstrument('2003-02-12');


-- EVENT 6
insert into FI_RAT_FinancialInstrument_Rating (
	[FI_ID],
	[FI_RAT_ChangedAt],
	[FI_RAT_ErasedAt],
	[FI_RAT_Erased],
	[Metadata_FI_RAT]
) values (2, '2003-02-13', '2006-09-21', 1, 557);

-- all rows in the attribute table
select * from FI_RAT_FinancialInstrument_Rating;
-- the latest view
select * from llFI_FinancialInstrument;
-- before 
select * from plFI_FinancialInstrument('2003-02-12');
-- latest in retrospect
select * from ppFI_FinancialInstrument(GETDATE(), '2006-09-20');
select * from ppFI_FinancialInstrument(GETDATE(), '2005-10-12');