## Procedures for panamaTree database
# SOURCE ~/data/treedatabase/TreeSpeciesPanama/PanTreeProcedures.sql


DELIMITER //

/* Create a table for a single family for display in online manuscript. I initially created the table in TreeTables.php. 
 usestatus must be a double-quoted string having within a comma- and single-quote-delimited vector of status options.
 Family can be set % because LIKE is used in query.
CALL panamaTree.familyDisplay("Actinidiaceae","'good','morphospecies'"); */
/* Altered 30 Apr 2020 to add returntable option. It now requires the 3rd argument. */
DROP PROCEDURE IF EXISTS panamaTree.familyDisplay //
CREATE PROCEDURE panamaTree.familyDisplay(IN family VARCHAR(32), IN usestatus CHAR(100), IN returntable BOOL)
BEGIN
   
	SET @binomialqry=CONCAT("SELECT Latin AS Latin,SpeciesID,Author,Status FROM panamaTree.viewFullLatin 
    		                 WHERE Family LIKE '", family, "' AND status IN (", usestatus, ") AND infra IS NULL");
	SET @subspqry=CONCAT("SELECT FullLatin AS Latin,SpeciesID,Author,Status FROM panamaTree.viewFullLatin 
						  WHERE Family LIKE '", family, "' AND status IN (", usestatus, ") AND infra IS NOT NULL");
	DROP TABLE IF EXISTS onetempfam;
    SET @fullqry=CONCAT("CREATE TEMPORARY TABLE onetempfam (", @binomialqry, ") UNION (", @subspqry, ") ORDER BY latin");
    # SET @fullqry=CONCAT("CREATE TEMPORARY TABLE onetempfam (", @binomialqry, ")  ORDER BY latin");
	
	PREPARE query FROM @fullqry; EXECUTE query;
	# SELECT @fullqry;
	
    ALTER TABLE onetempfam ADD COLUMN MaxHt DECIMAL(3,1);
    ALTER TABLE onetempfam ADD COLUMN countries INT, ADD COLUMN N INT, ADD COLUMN Npan INT, ADD COLUMN sites INT, ADD COLUMN density DECIMAL(7,4);
	ALTER TABLE onetempfam ADD COLUMN extent DECIMAL(8,3), ADD COLUMN extentBIEN DECIMAL(8,3), ADD COLUMN extentTrop DECIMAL(8,3), ADD COLUMN elevMin INT, ADD COLUMN elevMax INT;
    /* ALTER TABLE onetempfam ADD COLUMN IUCNpre ENUM('VU','CE','E','LC','DD'), ADD COLUMN IUCNprop ENUM('VU','CE','E','LC','DD'); */
	ALTER TABLE onetempfam ADD INDEX(SpeciesID);
	UPDATE onetempfam tmp JOIN panamaTree.RangeSize rs USING(SpeciesID) 
      SET tmp.countries=rs.countries, tmp.sites=rs.plots+rs.sites, tmp.N=rs.N, tmp.Npan=rs.Npan, tmp.density=rs.dens, tmp.elevMin=rs.ElevMin, tmp.elevMax=rs.ElevMax;
	UPDATE onetempfam tmp JOIN panamaTree.RangeSize rs USING(SpeciesID) 
      SET tmp.extent=rs.extent, tmp.extentTrop=rs.extentTrop, tmp.extentBIEN=rs.extentBIEN;
	/* UPDATE onetempfam tmp JOIN panamaTree.RangeSize rs USING(SpeciesID) SET tmp.IUCNpre=rs.IUCNpre; */
	UPDATE onetempfam tmp JOIN panamaTree.MaxHt rs USING(SpeciesID) SET tmp.MaxHt=rs.ht;
	
	IF(returntable = 1)
	  THEN 
	    ALTER TABLE onetempfam DROP COLUMN SpeciesID, DROP COLUMN Status, DROP COLUMN Author;
	    SELECT * FROM onetempfam;
	END IF;
	
END //

/* Create a table for a single family for display in online manuscript. I initially created the table in TreeTables.php. 
 usestatus must be a double-quoted string having within a comma- and single-quote-delimited vector of status options.
 Family can be set % because LIKE is used in query.
CALL panamaTree.familyNotes("Actinidiaceae"); */
DROP PROCEDURE IF EXISTS panamaTree.familyNotes //
CREATE PROCEDURE panamaTree.familyNotes(IN family VARCHAR(32))
BEGIN

	SET @binomialqry=CONCAT("SELECT SpeciesID,Latin,Status,Notes FROM panamaTree.viewFullLatin WHERE Family LIKE '", family, "' AND infra IS NULL");
	SET @subspqry=CONCAT("SELECT SpeciesID,FullLatin AS Latin,Status,Notes FROM panamaTree.viewFullLatin WHERE Family LIKE '", family, "' AND infra IS NOT NULL");
	DROP TABLE IF EXISTS onetempfam;
    SET @fullqry=CONCAT("CREATE TEMPORARY TABLE onetempfam (", @binomialqry, ") UNION (", @subspqry, ") ORDER BY latin");
	
	PREPARE query FROM @fullqry; EXECUTE query;
	
	UPDATE onetempfam SET status='syn' WHERE status LIKE 'syn%';
	ALTER TABLE onetempfam ADD COLUMN ValidLatin VARCHAR(97) AFTER Latin;
    /* The confusing issue is synonyms in limited number of countries. This only sets valid names for Panama */
    UPDATE onetempfam OTF JOIN panamaTree.viewSyn vN ON OTF.SpeciesID=vN.OldSpeciesID AND (Country IS NULL OR Country='' OR Country='Panama') 
      SET OTF.ValidLatin=vN.ValidLatin;
	
	SELECT * FROM onetempfam;
	
END //

/* Create a table for a single family for display in online manuscript. I initially created the table in TreeTables.php. 
CALL panamaTree.familyInvalid("Actinidiaceae"); */
DROP PROCEDURE IF EXISTS panamaTree.familyInvalid //
CREATE PROCEDURE panamaTree.familyInvalid(IN family VARCHAR(32))
BEGIN

    DROP TABLE IF EXISTS panamaTree.tempInvalid;
    SET @usestatus="'exclude','syn','synrol','synadd','introducida'";
    SET @invalidqry=CONCAT("CREATE TEMPORARY TABLE panamaTree.tempInvalid SELECT SpeciesID,Latin,Status,Notes FROM panamaTree.viewFullLatin WHERE Family='", 
                            family, "' AND Status IN(", @usestatus, ")");    
	PREPARE invquery FROM @invalidqry; EXECUTE invquery;

	UPDATE panamaTree.tempInvalid SET status='syn' WHERE Status LIKE 'syn%';
	ALTER TABLE panamaTree.tempInvalid ADD COLUMN ValidLatin VARCHAR(97) AFTER Latin;
    UPDATE panamaTree.tempInvalid OTF JOIN panamaTree.viewSyn vN ON OTF.SpeciesID=vN.OldSpeciesID AND (Country IS NULL OR Country='' OR Country='Panama') 
      SET OTF.ValidLatin=vN.ValidLatin;

	SELECT * FROM panamaTree.tempInvalid;
    
END //

/* This is aimed at an online table giving every name, valid and otherwise. It has a table of every Latin name (excluding subspecies and morphospecies) joined with valid names where needed. It makes use of familyDisplay and viewName. 
Then abandoned May 2020. This is far easier in R.
*/
-- DROP PROCEDURE IF EXISTS panamaTree.fullOutputTable //
-- CREATE PROCEDURE panamaTree.fullOutputTable(IN returntable BOOL)
-- BEGIN

    -- SET @usestatus="'good','exclude','syn','synrol','synadd','introducida'";
    -- SET @wild="'%'";
    -- SET @qry=CONCAT('CALL familyDisplay(', @wild, ',"', @usestatus ,'", 0)');
	-- PREPARE fullquery FROM @qry; EXECUTE fullquery;
    -- # SELECT @qry;
    
    -- ALTER TABLE onetempfam ADD COLUMN ValidLatin VARCHAR(97) AFTER Latin;
    -- ALTER TABLE onetempfam ADD COLUMN Family VARCHAR(32) FIRST;
    
    -- UPDATE onetempfam OTF JOIN panamaTree.viewName vN ON OTF.SpeciesID=vN.SpeciesID AND (Country IS NULL OR Country='' OR Country='Panama') SET OTF.ValidLatin=vN.ValidLatin;
	-- UPDATE onetempfam OTF JOIN viewLatin vL ON OTF.SpeciesID=vL.SpeciesID SET OTF.Family=vL.Family;

	-- /* Because familyDisplay includes subspecies, and I want it to, but here I don't want them. Rather than rewriting familyDisplay, I make this fix then select DISTINCT */
	-- /* Take note that the table has synrol with no valid name. */
	-- UPDATE onetempfam OTF JOIN viewLatin vL ON OTF.SpeciesID=vL.SpeciesID AND OTF.Latin<>vL.Latin SET OTF.Latin=vL.Latin;
    -- IF(returntable=1)
      -- THEN SELECT DISTINCT * FROM onetempfam;      
    -- END IF;
    
-- END //


/* Create a view in panamaTree that collects genus and species into a Latin name, with family, status, speciesID. The CREATE VIEW step is inside a procedure so that it can be done once and never repeated. The column FullLatin is NOT NULL only when there are subspecies. A second view, viewFullLatin, includes notes and references.*/
/* Mysql 8.0 installed April 2019. I had to add quotes around Rank in these queries, otherwise it failed. In Mysql 5 it worked without quotes. Old versions of the dumps will fail unless this is corrected. 
But I ended up purging mysql 8.0 and reverting to 5.7. Other differences in queries were appearing. */
DROP PROCEDURE IF EXISTS panamaTree.createViewLatin //
CREATE PROCEDURE panamaTree.createViewLatin()
BEGIN

    DROP VIEW IF EXISTS panamaTree.viewLatin;
    CREATE VIEW panamaTree.viewLatin AS 
      SELECT S.SpeciesID, Family, 
             CONCAT(Genus,' ',SpeciesName) AS Latin, 
             TRIM(CONCAT(Genus,' ',SpeciesName,' ',InfraSpecificLevel,' ',SubSpeciesName)) AS FullLatin, 
             Genus, SpeciesName AS Species, SubSpeciesID, InfraSpecificLevel 'Rank', SubSpeciesName Infra, 
             S.Mnemonic Mnem, SS.Mnemonic AS SubMnem,
             S.Status AS Status, SS.Status AS SubStatus
      FROM panamaTree.Species S JOIN APG.Genus G USING(GenusID) JOIN 
           APG.Family F ON G.FamilyID=F.FamilyID LEFT JOIN 
           panamaTree.SubSpecies SS ON SS.SpeciesID=S.SpeciesID;

    DROP VIEW IF EXISTS panamaTree.viewFullLatin;
    CREATE VIEW panamaTree.viewFullLatin AS 
      SELECT S.SpeciesID, Family, 
             CONCAT(Genus,' ',SpeciesName) AS Latin, 
             TRIM(CONCAT(Genus,' ',SpeciesName,' ',InfraSpecificLevel,' ',SubSpeciesName)) AS FullLatin, 
             Genus, SpeciesName AS Species, SubSpeciesID, InfraSpecificLevel 'Rank', SubSpeciesName Infra, S.Authority AS Author,
             S.Mnemonic Mnem, SS.Mnemonic AS SubMnem,
             S.Status AS Status, SS.Status AS SubStatus, S.Expert AS Expert, 
             S.Notes AS Notes, SS.Notes AS SubNotes, S.Reference AS SpRef, SS.Reference AS SubRef
      FROM panamaTree.Species S JOIN APG.Genus G USING(GenusID) JOIN 
           APG.Family F ON G.FamilyID=F.FamilyID LEFT JOIN 
           panamaTree.SubSpecies SS ON SS.SpeciesID=S.SpeciesID;

END //

/* A view for synonyms. Very useful since two different names involved. */
DROP PROCEDURE IF EXISTS panamaTree.createViewSynonym //
CREATE PROCEDURE panamaTree.createViewSynonym()
BEGIN

    DROP VIEW IF EXISTS panamaTree.viewSyn;
    SET @valpart=CONCAT("(SELECT SynID, S.SpeciesID, vL.SpeciesID AS ValidSpeciesID, Genus AS ValidGenus, Species AS ValidSpecies, Latin AS ValidLatin, ",
                        "Family AS ValidFamily, country FROM ",
                        " panamaTree.Synonym S LEFT JOIN panamaTree.viewLatin vL ON S.ValidSpeciesID=vL.SpeciesID ORDER BY S.SpeciesID) AS Val");
    SET @synpart=CONCAT("(SELECT S.SpeciesID,vL.SpeciesID AS OldSpeciesID, Latin AS OldLatin, Family AS OldFamily, country ", 
                        " FROM panamaTree.Synonym S LEFT JOIN panamaTree.viewLatin vL ON S.SpeciesID=vL.SpeciesID ORDER BY S.SpeciesID) AS Syn");
    SET @fullqry=CONCAT("SELECT DISTINCT SynID,OldLatin,ValidGenus,ValidSpecies,ValidLatin,country,OldFamily,ValidFamily,OldSpeciesID,"
                        "ValidSpeciesID AS ValidSpeciesID FROM ", @synpart, " JOIN ", @valpart, " USING(SpeciesID,country)");
    # SELECT @valpart; 
    # SELECT @synpart;
    # SELECT @fullqry;
    SET @createqry=CONCAT("CREATE VIEW panamaTree.viewSyn AS ", @fullqry);
    PREPARE query FROM @createqry; EXECUTE query;

END //

/* Started this is Costa Rica, then laptop crashed. I rescued it from hard drive. But I essentially started over after adding all Rolando's synonyms, but realizing there are many that lack a valid name in Panama. So the complete list of useful names comes as all valid plus all names appearing as synonyms in the synonym table. 
But I abandoned. With some names synonomous only in certain countries, this is far more complicated than a single query.
DROP PROCEDURE IF EXISTS panamaTree.NameLookup //
CREATE PROCEDURE panamaTree.NameLookup()
BEGIN

    DROP VIEW IF EXISTS panamaTree.viewName;
    SET @valpart="SELECT DISTINCT vL.SpeciesID, vS.ValidSpeciesID, Latin, ValidLatin, Status, Country FROM viewLatin vL ";
    SET @joinpart="LEFT JOIN viewSyn vS ON vL.SpeciesID=vS.OldSpeciesID";

    SET @fullqry=CONCAT(@valpart, @joinpart);
    SET @createqry=CONCAT("CREATE VIEW panamaTree.viewName AS ", @fullqry);
    PREPARE query FROM @createqry; EXECUTE query;

END //
*/

/* This ingenious idea recreates the stagePanamaTree.complete table using the updated panamaTree database, as a new table inside panamaTree. This will allow editing of that table using the existing web app.
*/
DROP PROCEDURE IF EXISTS panamaTree.newStageTable //
CREATE PROCEDURE panamaTree.newStageTable()
BEGIN

    DROP TABLE IF EXISTS panamaTree.stage;
    CREATE TABLE panamaTree.stage LIKE stagePanamaTree.complete;
    ALTER TABLE panamaTree.stage ADD COLUMN SpeciesID INT AFTER id;
    ALTER TABLE panamaTree.stage ADD COLUMN updated BOOL DEFAULT 0;
    
    INSERT INTO panamaTree.stage (SpeciesID,familia,genero,especie,`rank`,infra,autor,codigo,expert,notes,ref,status)
       SELECT SpeciesID,Family,Genus,Species,`Rank`,Infra,Author,Mnem,Expert,Notes,SpRef,Status FROM viewFullLatin;

    UPDATE panamaTree.stage Stg JOIN panamaTree.Synonym Syn USING(SpeciesID) JOIN viewLatin V ON V.SpeciesID=Syn.ValidSpeciesID 
       SET Stg.ValidGen=V.Genus, Stg.ValidSp=V.Species;

    UPDATE panamaTree.stage Stg JOIN panamaTree.Species S USING(SpeciesID) SET Stg.Year_added=S.Year_added, Stg.Year_removed=S.Year_removed;
       
END //


DELIMITER ;


/* These all moved to R. Harder than they look.
A convenience to grab speciesID for a single Latin name, using viewLatin, IF returnit=0, it only sets the variable @tempLocatedID to the required speciesID. If returnit=1, it also returns that @tempLocatedID. 
DROP PROCEDURE IF EXISTS panamaTree.findSpecies //
CREATE PROCEDURE panamaTree.findSpecies(IN searchLatin VARCHAR(50),IN returnit INT)
BEGIN
    
    SET @qry=CONCAT("SET @tempLocatedID = (SELECT SpeciesID FROM panamaTree.viewLatin WHERE Latin='", searchLatin, "')");
    PREPARE query FROM @qry; EXECUTE query;
    IF( returnit = 1)
     THEN SELECT @locatedID;
    END IF;
    
END //

 A convenience to grab all synonyms for a single submitted valid Latin name. All synonyms must be found. If the name submitted does not appear as valid in the synonym table, then nothing is returned. It might then be a non-valid name, or it might be a valid name with no synonyms.
DROP PROCEDURE IF EXISTS panamaTree.findSynonyms //
CREATE PROCEDURE panamaTree.findSynonyms(IN searchLatin VARCHAR(50))
BEGIN
    
    SET @find=CONCAT("CALL findSpecies('", searchLatin, "',0)");
    PREPARE fquery FROM @find; EXECUTE fquery;
    SELECT Latin, Family FROM panamaTree.viewLatin vL JOIN (SELECT SpeciesID FROM panamaTree.Synonym WHERE ValidSpeciesID=@tempLocatedID) AS syns USING(SpeciesID);
    
END //

 A convenience to find the valid name for a given name submitted. If the submitted name is valid, it returns it. Otherwise, it finds the valid name. If the name is not known (nowhere in Species table), nothing is returned. 
DROP PROCEDURE IF EXISTS panamaTree.findValid //
CREATE PROCEDURE panamaTree.findValid(IN searchLatin VARCHAR(50))
BEGIN
    
    SET @find=CONCAT("CALL findSpecies('", searchLatin, "',0)");
    PREPARE lquery FROM @find; EXECUTE lquery;
    SET @status=(SELECT status FROM panamaTree.viewLatin WHERE SpeciesID=@tempLocatedID);
    IF( @status='good' )
      THEN SELECT searchLatin;
    ELSE
      SELECT Latin, Family FROM panamaTree.viewLatin vL JOIN 
       (SELECT ValidSpeciesID FROM panamaTree.Synonym WHERE SpeciesID=@tempLocatedID) AS syns ON vL.SpeciesID=ValidSpeciesID;
    END IF;
        
END //

*/
