/*
IMPORTANT!!!
This has to be done with every new database.

1. Run this script
2. Run org.bgbm.mclBook.Processor with option 'r' once to update mcl-relevant references to mclflora's.
3. Rerun this script


*/


-- populate field IsMCLRelevant by determining if the reference was quoted by any 
-- entry in emOccurrence that is in an emArea
UPDATE dbo.Reference 
    SET IsMCLRelevant = 1
WHERE RefId IN (
    SELECT DISTINCT dbo.Reference.RefId
    FROM         dbo.emOccurrence INNER JOIN
                          dbo.emOccurrenceSource ON dbo.emOccurrence.OccurrenceId = dbo.emOccurrenceSource.OccurrenceFk INNER JOIN
                          dbo.PTaxon ON dbo.emOccurrence.PTNameFk = dbo.PTaxon.PTNameFk AND dbo.emOccurrence.PTRefFk = dbo.PTaxon.PTRefFk INNER JOIN
                          dbo.mclBook_RefSourceNumberRel ON dbo.emOccurrenceSource.SourceNumber = dbo.mclBook_RefSourceNumberRel.SourceNumber INNER JOIN
                          dbo.Reference ON dbo.mclBook_RefSourceNumberRel.RefFk = dbo.Reference.RefId INNER JOIN
                          dbo.emArea ON dbo.emOccurrence.AreaFk = dbo.emArea.AreaId
    WHERE     (dbo.PTaxon.PTRefFk IN (7000000, 7700000)) AND (dbo.PTaxon.InMCLArea = '1') AND (NOT (dbo.emArea.MCLCode IS NULL))
)
GO


-- all floras that are listed as MCLPreferredRefFk are considered to be an MCL Flora
-- 
UPDATE dbo.Reference 
    SET IsMCLFlora = 1
WHERE RefId IN (
    SELECT     MCLPreferredRefFk
    FROM         dbo.emOccurrence
    GROUP BY MCLPreferredRefFk
    HAVING      (NOT (MCLPreferredRefFk IS NULL))
)
GO

-- as of now, we know all used floras and are able to number them consecutively.
-- numbering will start with index 100. All flora with a lower id are considered
-- base flora

-- delete any IdInMCL that are not base floras. Especially important for the second run.
UPDATE dbo.Reference SET IdInMCL = NULL WHERE IdInMCL > 99
GO

DECLARE @floraRefId int
DECLARE @floraIdInMCL int
-- initial id for non base floras
SET @floraIdInMCL = 100

DECLARE floraCursor CURSOR FOR
    SELECT 
        RefId
    FROM v_mclBook_bibliography 
    WHERE (IsMCLFlora = 1) AND (IdInMCL IS NULL)
    ORDER BY 
        REPLACE(RefAuthorString, ' &', ',') ASC, 
        CASE WHEN NOT(MCLAdditionalSortIndex IS NULL) THEN MCLAdditionalSortIndex
		END ASC,	
        RefYear ASC, 
        CASE WHEN NOT(InTitle IS NULL) THEN InTitle
			ELSE Title
		END ASC,
		Volume ASC,
		CASE WHEN NOT(InTitle IS NULL) THEN Title 
		END ASC,
		CASE WHEN NOT(InTitle IS NULL) THEN PageString
		END ASC
    
OPEN floraCursor

FETCH NEXT FROM floraCursor INTO @floraRefId

WHILE @@FETCH_STATUS = 0
BEGIN
	UPDATE Reference SET IdInMCL = @floraIdInMCL WHERE RefId = @floraRefId
	PRINT @floraIdInMCL
	SET @floraIdInMCL = @floraIdInMCL + 1

	FETCH NEXT FROM floraCursor INTO @floraRefId
END

CLOSE floraCursor
DEALLOCATE floraCursor
GO








