## Corrections to panamaTree so that I permanently abandon stagePanamaTree.
# SOURCE ~/data/treedatabase/TreeSpeciesPanama/fixPanTreePostStaging.sql;

DELIMITER //

/* I botched Cryptocarya, listing C. aschersoniana as valid for itself. */
DROP PROCEDURE IF EXISTS panamaTree.fixCrytocarya //
CREATE PROCEDURE panamaTree.fixCrytocarya()
BEGIN

    SET @caID=(SELECT SpeciesID FROM panamaTree.viewLatin WHERE Latin='Cryptocarya aschersoniana');
    UPDATE panamaTree.Synonym SET country='Panama' WHERE SpeciesID=@caID;
    DELETE FROM panamaTree.Synonym WHERE SpeciesID=@caID AND ValidSpeciesID=@caID;

END //


/* Unchanged since 2019, I find codes needed when working on Tree Guide. There appear to be numerous cases where synonyms are missing codes.
Tabernaemontana glabra => STEMOB (Stemmadenia obovata) 
Aspidosperma was fucked up, since we had confusing changes for plot species, correctly cruentum */
DROP PROCEDURE IF EXISTS panamaTree.fixMnemonic //
CREATE PROCEDURE panamaTree.fixMnemonic(IN valid CHAR(100), IN newmnem CHAR(10))
BEGIN

    SET @spIDQry=CONCAT("SET @spID=(SELECT SpeciesID FROM panamaTree.viewLatin WHERE Latin='", valid, "')");
    PREPARE query FROM @spIDQry; EXECUTE query;
    SET @updateQry=CONCAT("UPDATE panamaTree.Species SET Mnemonic='", newmnem, "' WHERE SpeciesID=@spID");
    PREPARE query FROM @updateQry; EXECUTE query;
    SELECT @spIDQry, @updateQry;

END //





DELIMITER ;
