/* Copyright 2005-9 International Rice Research Institute (IRRI) and 
*  Centro Internacional de Mejoramiento de Maiz y Trigo
*
* All rights reserved.
*
* This SQL script is free software: you can redistribute it and/or modify
* it under the terms of the GNU Lesser General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
* GNU Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public License
* along with this script.  If not, see url www.gnu.org/licenses
*/

-- create FACTORS table
--
-- mhabito june2008
--

DELIMITER $$

DROP PROCEDURE IF EXISTS create_passport$$

CREATE PROCEDURE create_passport()
BEGIN
 DECLARE noMoreRows INT DEFAULT 0;
 DECLARE a_fname VARCHAR(255);
 DECLARE a_ftype VARCHAR(10);

 DECLARE cursor_aname CURSOR FOR SELECT distinct trim(fname) from udflds where ftype ='PASSPORT' order by fname;
 
 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET noMoreRows = 1;
 
 DROP TABLE IF EXISTS passport;
 
 CREATE TABLE passport (gid int not null); 

 OPEN cursor_aname;
 REPEAT
 	FETCH cursor_aname INTO a_fname;
	IF NOT noMoreRows THEN
            SET @qry = concat('ALTER TABLE passport ADD COLUMN `',trim(a_fname),'` VARCHAR(255)');
 		
 		PREPARE stmt1 FROM @qry;
 		EXECUTE stmt1;
 		DEALLOCATE PREPARE stmt1; 
 	END IF;
 UNTIL noMoreRows END REPEAT;
 CLOSE cursor_aname;

insert into passport (gid)
select distinct a.gid from
germplsm as g inner join atributs as a on g.gid = a.gid 
order by a.gid;

--
-- create indices to speed up queries
-- 
CREATE INDEX gid on passport (gid);



END$$

DELIMITER ;

-- populate FACTORS table
--
-- mhabito june2008
--

DELIMITER $$

DROP PROCEDURE IF EXISTS populate_passport $$

CREATE PROCEDURE `populate_passport`(IN xdatabase varchar(50))
BEGIN
 DECLARE noMoreRows INT DEFAULT 0;
 DECLARE a_fname VARCHAR(255);
 DECLARE cursor_fname CURSOR FOR SELECT trim(column_name) from information_schema.columns where table_schema = xdatabase and table_name = 'passport' and column_name <> 'gid' order by column_name;
 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET noMoreRows = 1;
 OPEN cursor_fname;
 REPEAT
 FETCH cursor_fname into a_fname;
 IF NOT noMoreRows THEN
 SET @qry = CONCAT('update passport as p,atributs as a INNER JOIN udflds as u ON a.atype = u.fldno LEFT JOIN scaledis as s ON u.scaleid = s.scaleid AND a.aval = s.value ',
      ' set p.`',trim(a_fname),'` = IF(s.valdesc is null,trim(a.aval),trim(s.valdesc)) where trim(u.fname) LIKE "',trim(a_fname),'%','" and a.gid = p.gid');
 SELECT @qry;
 PREPARE stmt1 FROM @qry;
 EXECUTE stmt1;
 DEALLOCATE PREPARE stmt1; 
 END IF;
 UNTIL noMoreRows END REPEAT;
 CLOSE cursor_fname;
 END$$
DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS activate_create_passport $$

CREATE PROCEDURE activate_create_passport(IN xdatabase varchar(50))
BEGIN
	CALL create_passport();
	CALL populate_passport(xdatabase);
END $$
DELIMITER ;