-- DeleteEmptyEvents-RM7.sql
/* 2023-05-08 Tom Holden ve3meo
Lists EventID, RIN, and Name of Event for those with no data, citation, media
Use the RIN to look up Person in RM, inspect and manually delete the event.
OR 
Batch delete by executing the statement in the comments at the bottom
*/
DROP VIEW IF EXISTS ListEmptyEvents
;
CREATE TEMP VIEW ListEmptyEvents
AS 
SELECT 
  E.EventID  -- the key for deleting
  ,E.OwnerID AS RIN  -- record number of person
  ,F.Name AS Event  -- name of Fact Type
FROM EventTable E
JOIN FactTypeTable F ON E.EventType=F.FactTypeID
AND E.OwnerType = 0 -- event for individual
--AND F.Name IN ('Burial','Death')
AND E.Date LIKE '.'  -- no Date
AND E.PlaceID=0  -- no Place
AND E.Details LIKE ''  -- no Description
AND E.Note LIKE ''  -- no Note
WHERE
 E.EventID NOT IN  -- no Citation
  (SELECT DISTINCT C.OwnerID
   FROM CitationTable C
   WHERE C.OwnerType=2) -- event Citation 
AND
 E.EventID NOT IN  -- no Media
  (SELECT DISTINCT M.OwnerID
   FROM MediaLinkTable M
   WHERE M.OwnerType=2) -- event Media
;

SELECT * FROM 
ListEmptyEvents
;

/* OPTIONAL BULK DELETION
Use SQLite manager's selective execution control or remove comment demarcations

DELETE FROM EventTable
WHERE EventID IN
(SELECT DISTINCT EventID FROM ListEmptyEvents)
;

*/