Bitemporal Test Suite

delete [AC_NAM_Actor_Name_Annex];
delete [AC_NAM_Actor_Name_Posit];
DBCC CHECKIDENT ('AC_NAM_Actor_Name_Posit', RESEED, 555);

delete [AC_Actor];
DBCC CHECKIDENT ('AC_Actor', RESEED, 1);

-- first row
insert into lAC_Actor (
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
)
values (
    'Roenbaeck',
    '2020-01-01',
    '2021-02-03',
    1,
    42
);

select * from AC_Actor;
select * from AC_NAM_Actor_Name_Posit;
select * from AC_NAM_Actor_Name;
select
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
from
    lAC_Actor;

-- duplicate
insert into lAC_Actor (
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
)
values (
    2,
    'Roenbaeck',
    '2020-01-01',
    '2021-02-03',
    1,
    42
);

select * from AC_Actor;
select * from AC_NAM_Actor_Name_Posit;
select * from AC_NAM_Actor_Name;
select
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
from
    lAC_Actor;

-- reassertion
insert into lAC_Actor (
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
)
values (
    2,
    'Roenbaeck',
    '2020-01-01',
    '2022-03-04',
    1,
    42
);

select * from AC_Actor;
select * from AC_NAM_Actor_Name_Posit;
select * from AC_NAM_Actor_Name;
select
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
from
    lAC_Actor;

-- restatement (date clash)
insert into lAC_Actor (
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
)
values (
    2,
    'Roenbaeck',
    '2021-02-03',
    '2021-02-03',
    1,
    42
);

select * from AC_Actor;
select * from AC_NAM_Actor_Name_Posit;
select * from AC_NAM_Actor_Name;
select
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
from
    lAC_Actor;

-- restatement again (no date clash)
insert into lAC_Actor (
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
)
values (
    2,
    'Roenbaeck',
    '2021-02-03',
    '2022-03-04',
    1,
    42
);

select * from AC_Actor;
select * from AC_NAM_Actor_Name_Posit;
select * from AC_NAM_Actor_Name;
select
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
from
    lAC_Actor;


-- deactivate non-existing
insert into lAC_Actor (
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
)
values (
    2,
    'Roenbaeck',
    '2021-02-03',
    '2022-03-04',
    0,
    42
);

select * from AC_Actor;
select * from AC_NAM_Actor_Name_Posit;
select * from AC_NAM_Actor_Name;
select
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
from
    lAC_Actor;

-- deactivate existing
insert into lAC_Actor (
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
)
values (
    2,
    'Roenbaeck',
    '2020-01-01',
    '2022-03-04',
    0,
    42
);

select * from AC_Actor;
select * from AC_NAM_Actor_Name_Posit;
select * from AC_NAM_Actor_Name;
select
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
from
    lAC_Actor;

-- prestatement
insert into lAC_Actor (
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
)
values (
    2,
    'Roenbaeck',
    '1972-08-20',
    '2023-04-05',
    1,
    42
);

select * from AC_Actor;
select * from AC_NAM_Actor_Name_Posit;
select * from AC_NAM_Actor_Name;
select
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
from
    lAC_Actor;

-- prestatement again (with later positing time)
insert into lAC_Actor (
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
)
values (
    2,
    'Roenbaeck',
    '1972-02-13',
    '2024-05-06',
    1,
    42
);

select * from AC_Actor;
select * from AC_NAM_Actor_Name_Posit;
select * from AC_NAM_Actor_Name;
select
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
from
    lAC_Actor;

-- prestatement again (with earlier positing time)
insert into lAC_Actor (
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
)
values (
    2,
    'Roenbaeck',
    '1971-01-01',
    '1971-01-01',
    1,
    42
);

select * from AC_Actor;
select * from AC_NAM_Actor_Name_Posit;
select * from AC_NAM_Actor_Name;
select
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
from
    lAC_Actor;

-- reactivate existing with clashing postiting time
-- this would become a restatement...
insert into lAC_Actor (
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
)
values (
    2,
    'Roenbaeck',
    '2020-01-01',
    '2023-04-05',
    1,
    42
);

select * from AC_Actor;
select * from AC_NAM_Actor_Name_Posit;
select * from AC_NAM_Actor_Name;
select
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
from
    lAC_Actor;

-- reactivate existing later time
-- also becomes a restatement...
insert into lAC_Actor (
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
)
values (
    2,
    'Roenbaeck',
    '2020-01-01',
    '2024-05-06',
    1,
    42
);

select * from AC_Actor;
select * from AC_NAM_Actor_Name_Posit;
select * from AC_NAM_Actor_Name;
select
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
from
    lAC_Actor;

-- reassert existing with later positing
-- just a reassertion then
insert into lAC_Actor (
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
)
values (
    2,
    'Roenbaeck',
    '1972-02-13',
    '2024-05-31',
    1,
    42
);

select * from AC_Actor;
select * from AC_NAM_Actor_Name_Posit;
select * from AC_NAM_Actor_Name;
select
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
from
    lAC_Actor;

-- reassert existing with earlier positing
-- just a reassertion then
insert into lAC_Actor (
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
)
values (
    2,
    'Roenbaeck',
    '1972-02-13',
    '1972-02-13',
    1,
    42
);

select * from AC_Actor;
select * from AC_NAM_Actor_Name_Posit;
select * from AC_NAM_Actor_Name;
select
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
from
    lAC_Actor;


-- deactivate the oldest
insert into lAC_Actor (
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
)
values (
    2,
    'Roenbaeck',
    '1971-01-01',
    '2021-01-01',
    0,
    42
);

select * from AC_Actor;
select * from AC_NAM_Actor_Name_Posit;
select * from AC_NAM_Actor_Name;
select
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
from
    lAC_Actor;

-- deactivate a deactivated

insert into lAC_Actor (
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
)
values (
    2,
    'Roenbaeck',
    '2020-01-01',
    '2025-01-01',
    0,
    42
);

select * from AC_Actor;
select * from AC_NAM_Actor_Name_Posit;
select * from AC_NAM_Actor_Name;
select
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
from
    lAC_Actor;

-- activate a deactivated
insert into lAC_Actor (
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
)
values (
    2,
    'Roenbaeck',
    '2020-01-01',
    '2026-01-01',
    1,
    42
);

select * from AC_Actor;
select * from AC_NAM_Actor_Name_Posit;
select * from AC_NAM_Actor_Name;
select
    AC_ID,
    AC_NAM_Actor_Name,
    AC_NAM_ChangedAt,
    AC_NAM_PositedAt,
    AC_NAM_Reliability,
    Metadata_AC
from
    lAC_Actor;