CREATE DEFINER=`proc_user7`@`localhost` PROCEDURE `survey_report`(IN survey_id varchar(10))
    SQL SECURITY INVOKER
BEGIN

declare tname varchar(24) default concat('survey_survey_',survey_id); -- COLLATE utf8mb4_unicode_ci;

declare counter int default 0;

set @counter = 0;

  select
      @counter :=  @counter+1 AS newindex,
      survey_answers_lookup.id as id,
      survey_answers_lookup.survey_table_row_id,
      survey_answers_lookup.qid,
      question,
    -- Types can be: ! * 1 E F K L M N Q R S T X Y
    if (type IN ('S','K'),
       (select answer
           from survey_answers_lookup
              where qid not in (select qid from survey_answers)
              and id = @counter
        ),
        (select answer
           from survey_answers
              where survey_questions.qid = survey_answers.qid
              and survey_answers_lookup.qid = survey_questions.qid
              and survey_answers_lookup.answer = survey_answers.code
         )
     ) as answer
      from survey_questions
      join survey_answers_lookup on survey_answers_lookup.qid = survey_questions.qid
      where survey_questions.sid = survey_id
      order by survey_answers_lookup.id;
END

'1', '1', '1', '740', 'How would you rate our product's quality?', 'Excellent'
'2', '2', '1', '741', 'Do you think our product helps your business?', 'Yes'
'3', '3', '1', '742', 'One a scale of 1-10, how would you rate the value of our product?', '5.0000000000'
'4', '4', '1', '745', 'Will you recommend our product?', 'Yes'
'5', '5', '1', '748', 'Please enter your email address or phone number if you would like someone to contact you.', '1@email.com'
'6', '6', '2', '740', 'How would you rate our product's quality?', 'Very good'
'7', '7', '2', '741', 'Do you think our product helps your business?', 'No'
'8', '8', '2', '742', 'One a scale of 1-10, how would you rate the value of our product?', '8.0000000000'
'9', '9', '2', '745', 'Will you recommend our product?', 'No'
'10', '10', '2', '748', 'Please enter your email address or phone number if you would like someone to contact you.', '2@email.com'
'11', '11', '3', '740', 'How would you rate our product's quality?', 'Poor'
'12', '12', '3', '741', 'Do you think our product helps your business?', 'No'
'13', '13', '3', '742', 'One a scale of 1-10, how would you rate the value of our product?', '10.0000000000'
'14', '14', '3', '745', 'Will you recommend our product?', 'No'
'15', '15', '3', '748', 'Please enter your email address or phone number if you would like someone to contact you.', '3@email.com'

select column_name, substring(column_name, 11+1, 3) as qid
    from information_schema.columns
     where table_name = 'survey_survey_144477'
       and column_name regexp 'X';

'144477X148X740', '740'
'144477X148X741', '741'
'144477X148X742SQ001', '742'
'144477X148X745', '745'
'144477X148X748', '748'

CREATE DEFINER=`proc_user7`@`localhost` PROCEDURE `proc_get_survey_answers`(IN survey_id varchar(10))
    SQL SECURITY INVOKER
BEGIN

declare col_name varchar (25);
declare qid int;
declare done int default false;
declare tname varchar(24) default concat('survey_survey_',survey_id);
declare counter int default 0;

-- col_name = 144477X148X742SQ001
-- qid = 742

declare cur1 cursor for
    select column_name, substring(column_name, 11+1, 3) as qid
       from information_schema.columns
       where table_name = 'survey_survey_144477'
          and column_name regexp 'X';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SET done = FALSE;

open cur1;
   read_loop: LOOP
   FETCH cur1 INTO col_name, qid;

   --   SET @sql = CONCAT('SELECT ', col_name, ' FROM survey_survey_144477 ');
   --   PREPARE stmt FROM @sql;
   --   EXECUTE stmt;
   --   DEALLOCATE PREPARE stmt;

       select
          @counter :=  @counter+1 AS newindex,
          question,
          qid,
        if (type IN ('S','K'),
           (select col_name
               from survey_survey_144477
                  where qid not in (select qid from survey_answers) -- ERROR
                  and id = @counter
            ),
            (select answer
               from survey_answers
                  where survey_questions.qid = survey_answers.qid
                  and qid = survey_questions.qid
                  -- and @sql = survey_answers.code
                  -- and survey_survey_144477.col_name = survey_answers.code -- this needs to be survey_survey_144477.144477X148X742SQ001 =
                  -- and 'survey_survey_144477.144477X148X740' = survey_answers.code
             )
         ) as answer
          from survey_questions
         -- join survey_survey_144477 survey_survey_144477 on survey_survey_144477.qid = survey_questions.qid -- I dont think this will work
          where survey_questions.sid = survey_id;
          -- order by survey_survey_144477.id;
   IF done THEN
      LEAVE read_loop;
   END IF;
   SELECT col_name, qid;
      END LOOP;
   CLOSE cur1;

END

NULL, 'Please enter your email address or phone number if you would like someone to contact you.', '748', NULL
NULL, 'How would you rate our product's quality?', '748', NULL
NULL, 'Do you think our product helps your business?', '748', NULL
NULL, 'One a scale of 1-10, how would you rate the value of our product?', '748', NULL
NULL, 'Will you recommend our product?', '748', NULL

CREATE DEFINER=`proc_user7`@`localhost` PROCEDURE `createOrReplaceOutputTable`(IN survey_id varchar(10))
    SQL SECURITY INVOKER
BEGIN

declare col_name varchar (25);
declare qid int;
declare done int default false;
declare tname varchar(24) default concat('survey_survey_',survey_id);
declare counter int default 0;

-- col_name = 144477X148X742SQ001
-- qid = 742

declare cur1 cursor for
    select column_name, substring(column_name, 11+1, 3) as qid
       from information_schema.columns
       where table_name = 'survey_survey_144477'
       and column_name regexp 'X';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
set done = FALSE;

open cur1;
   read_loop: LOOP
   FETCH cur1 INTO col_name, qid;

 -- SELECT id, col_name FROM survey_survey_144477;
 --    UNION
 --  SELECT qid, col_name;
 --  select  col_name, qid;


   IF done THEN
      LEAVE read_loop;
   END IF;
   SELECT col_name, qid;
      END LOOP;
   CLOSE cur1;

'144477X148X748', '748'


describe survey_survey_144477;
+---------------------+----------------+------+-----+---------+----------------+
| Field               | Type           | Null | Key | Default | Extra          |
+---------------------+----------------+------+-----+---------+----------------+
| id                  | int(11)        | NO   | PRI | NULL    | auto_increment |
| token               | varchar(35)    | YES  |     | NULL    |                |
| submitdate          | datetime       | YES  |     | NULL    |                |
| lastpage            | int(11)        | YES  |     | NULL    |                |
| startlanguage       | varchar(20)    | NO   |     | NULL    |                |
| ipaddr              | text           | YES  |     | NULL    |                |
| refurl              | text           | YES  |     | NULL    |                |
| 144477X148X740      | varchar(5)     | YES  |     | NULL    |                |
| 144477X148X741      | varchar(5)     | YES  |     | NULL    |                |
| 144477X148X742SQ001 | decimal(30,10) | YES  |     | NULL    |                |
| 144477X148X745      | varchar(5)     | YES  |     | NULL    |                |
| 144477X148X748      | text           | YES  |     | NULL    |                |
+---------------------+----------------+------+-----+---------+----------------+

describe survey_questions ;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| qid            | int(11)      | NO   | PRI | NULL    | auto_increment |
| parent_qid     | int(11)      | NO   | MUL | 0       |                |
| sid            | int(11)      | NO   | MUL | 0       |                |
| gid            | int(11)      | NO   | MUL | 0       |                |
| type           | varchar(1)   | NO   | MUL | T       |                |
| title          | varchar(20)  | NO   |     |         |                |
| question       | mediumtext   | NO   |     | NULL    |                |
| preg           | mediumtext   | YES  |     | NULL    |                |
| help           | mediumtext   | YES  |     | NULL    |                |
| other          | varchar(1)   | NO   |     | N       |                |
| mandatory      | varchar(1)   | YES  |     | NULL    |                |
| question_order | int(11)      | NO   |     | NULL    |                |
| language       | varchar(20)  | NO   | PRI | en      |                |
| scale_id       | int(11)      | NO   |     | 0       |                |
| same_default   | int(11)      | NO   |     | 0       |                |
| relevance      | mediumtext   | YES  |     | NULL    |                |
| modulename     | varchar(255) | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+

describe survey_survey_144477;
+---------------------+----------------+------+-----+---------+----------------+
| Field               | Type           | Null | Key | Default | Extra          |
+---------------------+----------------+------+-----+---------+----------------+
| id                  | int(11)        | NO   | PRI | NULL    | auto_increment |
| token               | varchar(35)    | YES  |     | NULL    |                |
| submitdate          | datetime       | YES  |     | NULL    |                |
| lastpage            | int(11)        | YES  |     | NULL    |                |
| startlanguage       | varchar(20)    | NO   |     | NULL    |                |
| ipaddr              | text           | YES  |     | NULL    |                |
| refurl              | text           | YES  |     | NULL    |                |
| 144477X148X740      | varchar(5)     | YES  |     | NULL    |                |
| 144477X148X741      | varchar(5)     | YES  |     | NULL    |                |
| 144477X148X742SQ001 | decimal(30,10) | YES  |     | NULL    |                |
| 144477X148X745      | varchar(5)     | YES  |     | NULL    |                |
| 144477X148X748      | text           | YES  |     | NULL    |                |
+---------------------+----------------+------+-----+---------+----------------+

describe survey_questions ;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| qid            | int(11)      | NO   | PRI | NULL    | auto_increment |
| parent_qid     | int(11)      | NO   | MUL | 0       |                |
| sid            | int(11)      | NO   | MUL | 0       |                |
| gid            | int(11)      | NO   | MUL | 0       |                |
| type           | varchar(1)   | NO   | MUL | T       |                |
| title          | varchar(20)  | NO   |     |         |                |
| question       | mediumtext   | NO   |     | NULL    |                |
| preg           | mediumtext   | YES  |     | NULL    |                |
| help           | mediumtext   | YES  |     | NULL    |                |
| other          | varchar(1)   | NO   |     | N       |                |
| mandatory      | varchar(1)   | YES  |     | NULL    |                |
| question_order | int(11)      | NO   |     | NULL    |                |
| language       | varchar(20)  | NO   | PRI | en      |                |
| scale_id       | int(11)      | NO   |     | 0       |                |
| same_default   | int(11)      | NO   |     | 0       |                |
| relevance      | mediumtext   | YES  |     | NULL    |                |
| modulename     | varchar(255) | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+

describe survey_answers_lookup;
+---------------------+------------+------+-----+---------+----------------+
| Field               | Type       | Null | Key | Default | Extra          |
+---------------------+------------+------+-----+---------+----------------+
| id                  | int(11)    | NO   | PRI | NULL    | auto_increment |
| sid                 | int(11)    | NO   |     | 0       |                |
| qid                 | int(11)    | NO   |     | 0       |                |
| survey_table_row_id | int(11)    | NO   |     | 0       |                |
| answer              | mediumtext | NO   |     | NULL    |                |
+---------------------+------------+------+-----+---------+----------------+