set @old_cvr = '46837428';
set @new_cvr = '12345678';

use followup;

begin;

-- Duplicate from BRS2_TreatmentRelationFollowup
insert into BRS2_TreatmentRelationFollowup (
    nextCheck,
    queryableCvr,
    externalReferenceId,
    uid,
    doctorOrganisation,
    hospitalOrganisation,
    ean,
    patientCpr,
    healthProfessionalCpr,
    relationLookupStart,
    relationLookupEnd,
    timeLimit,
    acceptableRelations,
    followupRelations,
    authorisationIdentifier,
    serviceProviderName,
    serviceProviderVersion,
    serviceProviderVendor,
    created,
    sor
)
select
    nextCheck,
    @new_cvr, -- the new cvr
    externalReferenceId,
    uuid(), -- new uid
    doctorOrganisation,
    hospitalOrganisation,
    ean,
    patientCpr,
    healthProfessionalCpr,
    relationLookupStart,
    relationLookupEnd,
    timeLimit,
    acceptableRelations,
    followupRelations,
    authorisationIdentifier,
    serviceProviderName,
    serviceProviderVersion,
    serviceProviderVendor,
    created,
    sor
from
    BRS2_TreatmentRelationFollowup
where
    queryableCvr = @old_cvr;

-- Duplicate from BRS2_Followup
-- We need a relation between old and new serial numbers later on, when duplicating into the BRS2_Notification table, as that table refers into BRS2_Followup through the followupSerialNumber-column.
-- We achieve this by creating a temp table of the followups that need to be duplicated, and generate fresh uid's there. The contents of the temp table are then inserted into BRS2_Followup. This allows us to relate serial numbers through the chain <old serial> <-> <old uid> <-> <new uid> <-> <new serial>.

-- Create the temp table
create temporary table BRS2_Followup_tmp select * from BRS2_Followup where queryableCvr = @old_cvr;
alter table BRS2_Followup_tmp add column newUid varchar(36);
update BRS2_Followup_tmp set newUid = uuid();

-- Insert into into BRS2_Followup (from the temp table)
insert into BRS2_Followup (
    queryableCvr,
    externalReferenceId,
    uid, -- The uid we just generated. 
    doctorOrganisation,
    hospitalOrganisation,
    ean,
    patientCpr,
    healthProfessionalCpr,
    relationLookupStart,
    relationLookupEnd,
    timeLimit,
    acceptableRelations,
    followupRelations,
    authorisationIdentifier,
    serviceProviderName,
    serviceProviderVersion,
    serviceProviderVendor,
    created,
    errorCount,
    nextSync,
    sor
)
select
    @new_cvr, -- the new cvr
    externalReferenceId,
    newUid, -- The new uid we just generated
    doctorOrganisation,
    hospitalOrganisation,
    ean,
    patientCpr,
    healthProfessionalCpr,
    relationLookupStart,
    relationLookupEnd,
    timeLimit,
    acceptableRelations,
    followupRelations,
    authorisationIdentifier,
    serviceProviderName,
    serviceProviderVersion,
    serviceProviderVendor,
    created,
    errorCount,
    nextSync,
    sor
from
    BRS2_Followup_tmp;

commit;

use register_notifications;

begin;

-- Duplicate from BRS2_TreatmentRelationFollowup
insert into BRS2_Notification (
    externalReferenceId,
    queryableCvr,
    creationTimestamp,
    doctorOrganisation,
    hospitalOrganisation,
    ean,
    patientCpr,
    healthProfessionalCpr,
    relationLookupStart,
    relationLookupEnd,
    timeLimit,
    acceptableRelations,
    actualRelations,
    followupSerialNumber,
    followupRelations,
    authorisationIdentifier,
    serviceProviderName,
    serviceProviderVersion,
    serviceProviderVendor,
    uid,
    sor
)
select
    n.externalReferenceId,
    @new_cvr,
    n.creationTimestamp,
    n.doctorOrganisation,
    n.hospitalOrganisation,
    n.ean,
    n.patientCpr,
    n.healthProfessionalCpr,
    n.relationLookupStart,
    n.relationLookupEnd,
    n.timeLimit,
    n.acceptableRelations,
    n.actualRelations,
    coalesce(f.pk, n.followupSerialNumber),
    n.followupRelations,
    n.authorisationIdentifier,
    n.serviceProviderName,
    n.serviceProviderVersion,
    n.serviceProviderVendor,
    uuid(),
    n.sor
from
    BRS2_Notification n
    left join followup.BRS2_Followup_tmp ft on
        n.followupSerialNumber = ft.pk
    inner join followup.BRS2_Followup f on
        ft.newUid = f.uid
where
    n.queryableCvr = @old_cvr;

commit;