drop procedure if exists prcFindAll;

delimiter $$

CREATE PROCEDURE prcFindAll( IN MasterTableName VARCHAR( 28 ) , IN `search` TEXT, DatabaseName varchar(28) )
BEGIN 
       DECLARE finished 				INT DEFAULT FALSE ; 
       DECLARE columnName 				VARCHAR ( 28 ) ; 
       DECLARE stmtFields 				TEXT ; 
	   DECLARE TableCount 				INT;
	   DECLARE strTableName 			varchar(28);
	   DECLARE oldTableName 			varchar(28);
	   DECLARE intTableID				INT;
	   DECLARE intTableType				INT;
	   DECLARE oldTableType				INT;
	   DECLARE strPrimaryColumnName 	varchar(28);
	   DECLARE strSecondaryTableName 	varchar(28);
	   DECLARE strSecondaryColumnName 	varchar(28);
	   DECLARE strThirdTableName 		varchar(28);
	   DECLARE strThirdColumnName 		varchar(28);
	   DECLARE strFourthColumnName 		varchar(28);
	   DECLARE strJoinQuery				varchar(5000);
	   
	   DECLARE columnNames 	CURSOR FOR 
           SELECT DISTINCT TableType, `information_schema`.`COLUMNS`.Table_name, Column_Name FROM `information_schema`.`COLUMNS` 
		   INNER JOIN tblMasterTables 
           ON `information_schema`.`COLUMNS`.Table_Name = tblMasterTables.Table_Name
			-- OR `information_schema`.`COLUMNS`.Table_Name = tblMasterRecords.SecondaryTableName
			-- OR `information_schema`.`COLUMNS`.Table_Name = tblMasterRecords.ThirdTableName
		   WHERE Column_Name not like '%\_ID' 
			AND Table_Schema = DatabaseName
		   GROUP BY TableType, `information_schema`.`COLUMNS`.Table_Name, Column_Name	
		   ORDER BY TableType, `information_schema`.`COLUMNS`.Table_Name, Column_Name ; 
       DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = TRUE; 
	   -- For testing debugging only SELECT NOW();

	   DROP TEMPORARY TABLE IF EXISTS tblMasterRecords;
	   CREATE TEMPORARY TABLE tblMasterRecords (TableType INT, PrimaryTableName varchar(28), PrimaryColumnName varchar(28), SecondaryTableName varchar(28), SecondaryColumnName varchar(28), ThirdTableName varchar(28), ThirdColumnName varchar(28), FourthColumnName varchar(28));
		
	   INSERT tblMasterRecords(TableType, PrimaryTableName, PrimaryColumnName, SecondaryTableName, SecondaryColumnName, ThirdTableName, ThirdColumnName, FourthColumnName)
		   VALUES (1, MasterTableName, '', '', '', '', '', '');
			
	   SET intTableID = CAST(RIGHT(MasterTableName, (LENGTH(MasterTableName) - INSTR(MasterTableName,'_' ) )) AS UNSIGNED);

	   INSERT tblMasterRecords(TableType, PrimaryTableName, PrimaryColumnName, SecondaryTableName, SecondaryColumnName, ThirdTableName, ThirdColumnName, FourthColumnName)
		   SELECT 2, MasterTableName, CONCAT(MasterTableName, '_', CollectionFieldID), CONCAT('Collection_',COLLECTIONFIELDLOOKUPCOLLECTIONID ), 
				CONCAT('Collection_',COLLECTIONFIELDLOOKUPCOLLECTIONID,'_ID'), '', '', ''
		   FROM CollectionTaxonomy 
		   WHERE COLLECTIONID = intTableID
			AND COLLECTIONFIELDLOOKUPCOLLECTIONID IS NOT NULL AND CONCAT('Collection_',COLLECTIONFIELDLOOKUPCOLLECTIONID ) != MasterTableName;
	   INSERT tblMasterRecords(TableType, PrimaryTableName, PrimaryColumnName, SecondaryTableName, SecondaryColumnName, ThirdTableName, ThirdColumnName, FourthColumnName)
		   SELECT 3, MasterTableName, CONCAT(MasterTableName, '_ID'), CONCAT('Collection_',CollectionXrefsSave), 
				CONCAT('Collection_',CollectionXrefsSave,'_1'), CONCAT('Collection_',CollectionXrefs), CONCAT('Collection_',CollectionXrefs, '_ID'), CONCAT('Collection_', CollectionXrefsSave, '_2')
		   FROM CollectionSettings_5 
		   WHERE COLLECTIONID = intTableID
			AND COLLECTIONXREFS IS NOT NULL
			AND COLLECTIONxREFS <> intTableID;
	   INSERT tblMasterRecords(TableType, PrimaryTableName, PrimaryColumnName, SecondaryTableName, SecondaryColumnName, ThirdTableName, ThirdColumnName, FourthColumnName) 
		   SELECT 	4, MasterTableName, CONCAT(MasterTableName, '_ID'),CONCAT('Collection_',COLLECTIONComplexXREF), 
					CONCAT('Collection_',COLLECTIONComplexXref, '_1'),'','',''
		   FROM CollectionSettings_6 
		   WHERE COLLECTIONID = intTableID
			AND COLLECTIONComplexXREF IS NOT NULL;
	   
	   DROP TEMPORARY TABLE IF EXISTS tblMasterTables;
	   CREATE TEMPORARY TABLE tblMasterTables (TableType INT, Table_Name varchar(28));
	   INSERT tblMasterTables(TableType, Table_Name)
	   SELECT TableType, 
			CASE TableType
				WHEN 1 THEN PrimaryTableName
				WHEN 2 THEN SecondaryTableName
				WHEN 3 THEN ThirdTableName
				WHEN 4 THEN SecondaryTableName
			END
	   FROM tblMasterRecords;
	   -- For testing debugging only		SELECT * FROM tblMasterRecords;
	   -- For testing debugging only		SELECT * FROM tblMasterTables;

	   DROP TEMPORARY TABLE IF EXISTS tblSearchData;
	   CREATE TEMPORARY TABLE tblSearchData (Table_Name varchar(28), TableRowID int);
       SET stmtFields = '' ; 
	   SET oldTableName = '';
	   SET @stmtQuery = '';
       OPEN columnNames ; 
   		   readColumns: LOOP 
			  FETCH columnNames INTO intTableType, strTableName, ColumnName; 
			  IF finished THEN 
					CASE oldTableType
						WHEN 1 THEN BEGIN END;
						WHEN 2 THEN
 							 SELECT	PrimaryColumnName, 
									SecondaryTableName,
									SecondaryColumnName,
									ThirdTableName, 
									ThirdColumnName,
									FourthColumnName
							 INTO 	strPrimaryColumnName,
									strSecondaryTableName,
									strSecondaryColumnName,
									strThirdTableName,
									strThirdColumnName,
									strFourthColumnName
							FROM tblMasterRecords 
							WHERE TableType = oldTableType
								AND SecondaryTableName = oldTableName
							LIMIT 0,1;
						WHEN 3 THEN
							 SELECT	PrimaryColumnName, 
									SecondaryTableName,
									SecondaryColumnName,
									ThirdTableName, 
									ThirdColumnName,
									FourthColumnName
							 INTO 	strPrimaryColumnName,
									strSecondaryTableName,
									strSecondaryColumnName,
									strThirdTableName,
									strThirdColumnName,
									strFourthColumnName
							FROM tblMasterRecords 
							WHERE TableType = oldTableType
								AND ThirdTableName = oldTableName
							LIMIT 0,1;
						WHEN 4 THEN 
							 SELECT	PrimaryColumnName, 
									SecondaryTableName,
									SecondaryColumnName,
									ThirdTableName, 
									ThirdColumnName,
									FourthColumnName
							 INTO 	strPrimaryColumnName,
									strSecondaryTableName,
									strSecondaryColumnName,
									strThirdTableName,
									strThirdColumnName,
									strFourthColumnName
							FROM tblMasterRecords 
							WHERE TableType = oldTableType
								AND SecondaryTableName = oldTableName
							LIMIT 0,1;
						ELSE 
							BEGIN
					        END;	
					END CASE;
					 SET @stmtQuery := 'INSERT tblSearchData (Table_Name, TableRowID) '  ;
					 SET @stmtQuery := CONCAT(@stmtQuery, ' SELECT ''', MasterTableName,''', ', MasterTableName, '_ID FROM ' , MasterTableName);
					 SET strJoinQuery := fnGetJoinString(	oldTableType, 
															MasterTableName, 
															strPrimaryColumnName,
															strSecondaryTableName,
															strSecondaryColumnName,
															strThirdTableName,
															strThirdColumnName,
															strFourthColumnName
														);
					 -- For testing debugging only SELECT strJoinQuery;
					 SET @stmtQuery := CONCAT(@stmtQuery, strJoinQuery, ' WHERE ' , stmtFields, ';' ) ; 
					 PREPARE stmt FROM @stmtQuery ; 
					 EXECUTE stmt ; 
					 LEAVE readColumns ; 
			  END IF; 
			  IF oldTableName = '' THEN 
				SET oldTableName = strTableName;
				SET oldTableType = intTableType;
			  END IF;
			  IF oldTableName <> strTableName or oldTableType <> intTableType THEN
					 SET @stmtQuery := 'INSERT tblSearchData (Table_Name, TableRowID) '  ;
					 SET @stmtQuery := CONCAT(@stmtQuery, ' SELECT ''', MasterTableName,''', ', MasterTableName, '_ID FROM ' , MasterTableName);
					 -- For testing debugging only SELECT oldTableType;
					CASE oldTableType
						WHEN 1 THEN BEGIN END;
						WHEN 2 THEN
 							 SELECT	PrimaryColumnName, 
									SecondaryTableName,
									SecondaryColumnName,
									ThirdTableName, 
									ThirdColumnName,
									FourthColumnName
							 INTO 	strPrimaryColumnName,
									strSecondaryTableName,
									strSecondaryColumnName,
									strThirdTableName,
									strThirdColumnName,
									strFourthColumnName
							FROM tblMasterRecords 
							WHERE TableType = oldTableType
								AND SecondaryTableName = oldTableName
							LIMIT 0,1;
						WHEN 3 THEN
							 SELECT	PrimaryColumnName, 
									SecondaryTableName,
									SecondaryColumnName,
									ThirdTableName, 
									ThirdColumnName,
									FourthColumnName
							 INTO 	strPrimaryColumnName,
									strSecondaryTableName,
									strSecondaryColumnName,
									strThirdTableName,
									strThirdColumnName,
									strFourthColumnName
							FROM tblMasterRecords 
							WHERE TableType = oldTableType
								AND ThirdTableName = oldTableName
							LIMIT 0,1;
						WHEN 4 THEN 
							 SELECT	PrimaryColumnName, 
									SecondaryTableName,
									SecondaryColumnName,
									ThirdTableName, 
									ThirdColumnName,
									FourthColumnName
							 INTO 	strPrimaryColumnName,
									strSecondaryTableName,
									strSecondaryColumnName,
									strThirdTableName,
									strThirdColumnName,
									strFourthColumnName
							FROM tblMasterRecords 
							WHERE TableType = oldTableType
								AND SecondaryTableName = oldTableName
							LIMIT 0,1;
						ELSE 
							BEGIN
					        END;
					END CASE;
							-- For testing debugging only SELECT oldTableType, 
							-- For testing debugging only 		MasterTableName, 
							-- For testing debugging only strPrimaryColumnName,
							-- For testing debugging only strSecondaryTableName,
							-- For testing debugging only strSecondaryColumnName,
							-- For testing debugging only strThirdTableName,
							-- For testing debugging only strThirdColumnName,
							-- For testing debugging only strFourthColumnName;
					 SET strJoinQuery := fnGetJoinString(	oldTableType, 
															MasterTableName, 
															strPrimaryColumnName,
															strSecondaryTableName,
															strSecondaryColumnName,
															strThirdTableName,
															strThirdColumnName,
															strFourthColumnName
														);
					 -- For testing debugging only SELECT strJoinQuery;
					 SET @stmtQuery := CONCAT(@stmtQuery, strJoinQuery, ' WHERE ' , stmtFields, ';' ) ; 
				-- For testing debugging only SELECT @stmtQuery;
				SET stmtFields = '';
				SET oldTableName = strTableName;
				SET oldTableType = intTableType;
				PREPARE stmt FROM @stmtQuery ; 
				EXECUTE stmt ; 
			  END IF;
			  SET stmtFields = CONCAT( 
				 stmtFields , IF ( LENGTH( stmtFields ) > 0 , ' OR' , ''  ) , 
						 ' ', strTableName ,'.' , columnName , ' LIKE ''%' , search , '%''' 
									) ; 
		   END LOOP;
		
		CLOSE columnNames ; 
		SELECT Table_Name, TableRowID 
		FROM tblSearchData
		GROUP BY TABLE_Name, TableRowID;
		-- For testing debugging only SELECT NOW();
END$$
DELIMITER ; 
