/* Copy residence fact for individual to census fact as long as no census fact for person and date exists already */
/* 
   CHANGE THE IN STATEMENT NUMBERS TO MATCH RECORDS IN YOUR OWN DATABASE - Needs to be done in 3 places
*/
with datecte as(
select date as edate,sortdate from EventTable where EventID in (60,64,63,52,79,42,43,45,1201,1302)
)
/* get existing census events */
,censuscte as(
select EventID, OwnerID, cen.Date as cDate, cen.SortDate
FROM
  EventTable cen INNER JOIN datecte d
  ON d.sortdate = cen.sortdate
WHERE cen.EventType = 18 and cen.OwnerType = 0
)

INSERT INTO EventTable

SELECT
  NULL AS EventID,
  18 as EventType,
  E.OwnerType, E.OwnerID, E.FamilyID, E.PlaceID, E.SiteID,
  E.DATE, E.SortDate,
  1 AS IsPrimary,
  E.IsPrivate, E.Proof, E.STATUS, E.EditDate,
  e.Sentence AS Sentence,
  e.Details,
  e.Note
FROM
  EventTable E INNER JOIN datecte d
  ON d.sortdate = e.sortdate
  LEFT OUTER JOIN censuscte c
  ON e.OwnerID = c.OwnerID AND e.SortDate = c.SortDate
WHERE E.EventType = 29 and E.OwnerType = 0 and c.eventID is null;

/* ********************************************** */
/* Now do citations */
with datecte as(
select date as edate,sortdate from EventTable where EventID in (60,64,63,52,79,42,43,45,1201,1302) 
)
/* get census citations */
,evcte as(
SELECT
  EventID,
  E1.OwnerType, E1.OwnerID, 
  E1.DATE, E1.SortDate
FROM
  EventTable E1 INNER JOIN datecte d
  ON d.sortdate = e1.sortdate
WHERE E1.EventType = 18 and E1.OwnerType = 0
)
/* get residence citations */
,rescte as(
SELECT
  EventID,
  E2.OwnerType, E2.OwnerID, 
  E2.DATE, E2.SortDate
FROM
  EventTable E2 INNER JOIN datecte d
  ON d.sortdate = e2.sortdate
WHERE E2.EventType = 29 and E2.OwnerType = 0
)
/* get matched residence and census event pairs */
,joincte as(
SELECT
  jc.EventID as cenEventID,
  jc.OwnerType, jc.OwnerID, 
  jc.DATE, jc.SortDate,
  jr.eventID as resEventID
 From evcte jc INNER JOIN rescte jr
 ON jc.OwnerType = jr.OwnerType AND jc.OwnerID = jr.OwnerID AND jc.SortDate = jr.SortDate
)
/* get existing census citations */
,citcte as(
SELECT 
CitationID, ct.OwnerType, SourceID, ct.OwnerID
FROM CitationTable ct
INNER JOIN joincte cj
ON cj.cenEventID = ct.OwnerID
WHERE ct.OwnerType = 2
)

INSERT INTO CitationTable

SELECT 
NULL as CitationID,
      t.OwnerType, 
	  t.SourceID,
      j.cenEventID, 
      'PDX' as Quality,
		IsPrivate,
		Comments,
		ActualText,
		RefNumber,
		Flags,
		Fields
FROM CitationTable t
INNER JOIN joincte j
ON j.resEventID = t.OwnerID
LEFT OUTER JOIN citcte cit
ON t.SourceID = cit.SourceID and t.OwnerID = cit.OwnerID
WHERE t.OwnerType = 2 and cit.CitationID is NULL;

/* ********************************************** */
/* Now do Media links */
with datecte as(
select date as edate,sortdate from EventTable where EventID in (60,64,63,52,79,42,43,45,1201,1302) 
)
/* get census events */
,evcte as(
SELECT
  EventID,
  E1.OwnerType, E1.OwnerID, 
  E1.DATE, E1.SortDate
FROM
  EventTable E1 INNER JOIN datecte d
  ON d.sortdate = e1.sortdate
WHERE E1.EventType = 18 and E1.OwnerType = 0
)
/* get residence events */
,rescte as(
SELECT
  EventID,
  E2.OwnerType, E2.OwnerID, 
  E2.DATE, E2.SortDate
FROM
  EventTable E2 INNER JOIN datecte d
  ON d.sortdate = e2.sortdate
WHERE E2.EventType = 29 and E2.OwnerType = 0
)
/* get matched residence and census event pairs with their citations */
,joincte as(
SELECT
  jc.EventID as cenEventID,
  jc.OwnerType, jc.OwnerID, 
  jc.DATE, jc.SortDate,
  jr.eventID as resEventID,
  rescit.CitationID as ResCitID,
  cencit.CitationID as CenCitID
 From evcte jc INNER JOIN rescte jr
 ON jc.OwnerType = jr.OwnerType AND jc.OwnerID = jr.OwnerID AND jc.SortDate = jr.SortDate
 INNER JOIN CitationTable rescit
 ON jr.eventID = rescit.OwnerID AND rescit.OwnerType = 2
 INNER JOIN CitationTable cencit
 ON jc.eventID = cencit.OwnerID AND rescit.OwnerType = 2
)
/* Get media links already in */
,medcte as(
select LinkID, ml.MediaID, ml.OwnerType, ml.OwnerID 
FROM MediaLinkTable ml
INNER JOIN joincte
ON  ml.OwnerID = joincte.CenCitID
where ml.ownertype = 4  -- citation media and ownerid = citationid
)
INSERT INTO MediaLinkTable
SELECT NULL as LinkID, m.MediaID, m.OwnerType, cencitID, m.IsPrimary, Include1, Include2, Include3, Include4, SortOrder, RectLeft, RectTop, RectRight, RectBottom, Note, Caption, RefNumber, m.Date, m.SortDate, Description
FROM MediaLinkTable m
INNER JOIN joincte j
ON m.OwnerID = j.rescitid 
left outer join medcte
on m.LinkID = medcte.LinkID
where m.ownertype = 4  
and medcte.LinkID IS NULL