CREATE DEFINER=`root`@`localhost` PROCEDURE `forms_data_columns`(
	IN `_name` VARCHAR(255),
	IN `_action` VARCHAR(10)
)
LANGUAGE SQL
DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
COMMENT ''
BEGIN
	SELECT a.TABLE_NAME AS dataTable, a.COLUMN_NAME AS dataName, a.DATA_TYPE AS dataType, a.COLUMN_COMMENT AS dataMetaInfo, REGEXP_SUBSTR(a.COLUMN_COMMENT, '(?<=\:)[A-z]*') dataItemName,
	a.COLUMN_KEY AS dataKeyType, REGEXP_SUBSTR(a.COLUMN_COMMENT, '.{1,5}(?=\:)') dataActions,
	nullif(REGEXP_SUBSTR(a.COLUMN_COMMENT, '(?<=R\:\")[A-z]*(?=\")'), '') dataReferenceColumn,
	(
		SELECT CONCAT('(select ', dataReferenceColumn, ' from ', REGEXP_SUBSTR(f.REF_NAME, '(?<=\/)[A-z]*'), ' as sec where sec.', g.REF_COL_NAME, ' = pri.', dataName, ') as ', dataItemName, '_text') 
		FROM information_schema.INNODB_SYS_FOREIGN f JOIN information_schema.INNODB_SYS_FOREIGN_COLS g ON f.ID = g.ID
		WHERE f.N_COLS = 1 AND f.FOR_NAME = CONCAT(DATABASE(), '/', dataTable) AND g.FOR_COL_NAME = dataName LIMIT 1
	) AS dataReferenceQuery
	FROM information_schema.`COLUMNS` a
	WHERE table_schema = DATABASE() AND TABLE_NAME = (SELECT b.table_name FROM information_schema.`TABLES` b WHERE b.TABLE_COMMENT REGEXP CONCAT('N:', _name, '\,.*'))
	AND COLUMN_COMMENT REGEXP CONCAT('^.*[', _action, '].*\:.*');
	
	SELECT CONCAT(
		'select ',
		GROUP_CONCAT(CONCAT('pri.', dataName, ' as ', dataItemName, if(dataReferenceQuery IS NOT NULL, CONCAT(', ', dataReferenceQuery), '')) SEPARATOR ', '),
		' FROM ', dataTable, ' as pri WHERE ', GROUP_CONCAT(concat(if(REGEXP_SUBSTR(dataActions, '.*K.*') != '', CONCAT('pri.', dataName), null)), ' = ?' SEPARATOR ' and ')
	) AS `query` FROM (
		SELECT a.TABLE_NAME AS dataTable, a.COLUMN_NAME AS dataName, a.DATA_TYPE AS dataType, a.COLUMN_COMMENT AS dataMetaInfo, REGEXP_SUBSTR(a.COLUMN_COMMENT, '(?<=\:)[A-z]*') dataItemName,
		a.COLUMN_KEY AS dataKeyType, REGEXP_SUBSTR(a.COLUMN_COMMENT, '.{1,5}(?=\:)') dataActions,
		nullif(REGEXP_SUBSTR(a.COLUMN_COMMENT, '(?<=R\:\")[A-z]*(?=\")'), '') dataReferenceColumn,
		(
			SELECT CONCAT('(select ', dataReferenceColumn, ' from ', REGEXP_SUBSTR(f.REF_NAME, '(?<=\/)[A-z]*'), ' as sec where sec.', g.REF_COL_NAME, ' = pri.', dataName, ') as ', dataItemName, '_text') 
			FROM information_schema.INNODB_SYS_FOREIGN f JOIN information_schema.INNODB_SYS_FOREIGN_COLS g ON f.ID = g.ID
			WHERE f.N_COLS = 1 AND f.FOR_NAME = CONCAT(DATABASE(), '/', dataTable) AND g.FOR_COL_NAME = dataName LIMIT 1
		) AS dataReferenceQuery
		FROM information_schema.`COLUMNS` a
		WHERE table_schema = DATABASE() AND TABLE_NAME = (SELECT b.table_name FROM information_schema.`TABLES` b WHERE b.TABLE_COMMENT REGEXP CONCAT('N:', _name, '\,.*'))
		AND COLUMN_COMMENT REGEXP CONCAT('^.*[', _action, '].*\:.*')) lateral
	GROUP BY dataTable;	
END