SET GLOBAL group_concat_max_len = 1000;

#Please set the path and name at END of file after - INTO OUTFILE "d:\c5.txt". Change in path instead of d:\c5.txt;
#also review the script once before executing

#Below 2 variables can be moved as SP parameter
SET @dbName = "columnstest";
SET @tablename = "communication";


#Script to be kept in SP 
SET @TableID = concat(@tablename,"id");

SELECT concat("DROP TABLE IF EXISTS `", @dbName, "`.`", table_data.audit_table, "`;\r",
          "CREATE TABLE `", @dbName, "`.`", table_data.audit_table, "`\r",
          "(\r",
		  "  `historytableid` BIGINT AUTO_INCREMENT,",
          column_defs, ",\r",
		  "  `ActionType` ENUM ('INSERT', 'UPDATE', 'DELETE'),\r",
          "  `DateTimestamp` DATETIME DEFAULT CURRENT_TIMESTAMP,\r",
          "  PRIMARY KEY (`historytableid`)\r"
          ")\r",
          "  ENGINE = InnoDB;\r\r",

#Altering table for converting 'ID' column name to 'TableName+ID' name
          "ALTER TABLE `", @dbName, "`.`", table_data.audit_table, "` rename column id to ",@TableNAme,"id ;\r\r",
      
#Trigger for INSERT
          "DROP TRIGGER IF EXISTS `", @dbName, "`.`", table_data.insert_trigger, "`;\r",
          "CREATE TRIGGER `", @dbName, "`.`", table_data.insert_trigger, "`\r",
          "  AFTER INSERT ON `", @dbName, "`.`", table_data.db_table, "`\r",
          "  FOR EACH ROW INSERT INTO `", @dbName, "`.`", table_data.audit_table, "`\r",
          "     (", table_data.column_names, ",`ActionType`)\r",
          "  VALUES\r", 	
          "     (", table_data.NEWcolumn_names, ",'INSERT');\r\r",
          
#Trigger for UPDATE    
          "DROP TRIGGER IF EXISTS `", @dbName, "`.`", table_data.update_trigger, "`;\r",
          "CREATE TRIGGER `", @dbName, "`.`", table_data.update_trigger, "`\r",
          "  AFTER UPDATE ON `", @dbName, "`.`", table_data.db_table, "`\r",
          "  FOR EACH ROW INSERT INTO `", @dbName, "`.`", table_data.audit_table, "`\r",
          "     (", table_data.column_names, ",`ActionType`)\r",
          "  VALUES\r",
          "     (", table_data.NEWcolumn_names, ",'UPDATE');\r\r",
          
#Trigger for DELETE   
          "DROP TRIGGER IF EXISTS `", @dbName, "`.`", table_data.delete_trigger, "`;\r",
          "CREATE TRIGGER `", @dbName, "`.`", table_data.delete_trigger, "`\r",
          "  AFTER DELETE ON `", @dbName, "`.`", table_data.db_table, "`\r",
          "  FOR EACH ROW INSERT INTO `", @dbName, "`.`", table_data.audit_table, "`\r",
          "     (", table_data.column_names, ",`ActionType`)\r",
          "  VALUES\r",
          "     (", table_data.OLDcolumn_names, ",'DELETE');\r\r"
)
FROM (
   SELECT
     table_order_key,
     table_name                                                                      										AS db_table,
     concat(table_name,"_history")                                                  										AS audit_table,
     concat("trg_",table_name, "_insert")                                            										AS insert_trigger,
     concat("trg_",table_name, "_update")                                            										AS update_trigger,
     concat("trg_",table_name, "_delete")                                            										AS delete_trigger,
     group_concat("\r  `", column_name, "` ", column_type ORDER BY column_order_key) 										AS column_defs,
     group_concat("`", CASE WHEN column_name = "ID" THEN @TableID ELSE column_name END, "`" ORDER BY column_order_key)      AS column_names,
     group_concat("NEW.", column_name, "" ORDER BY column_order_key)               											AS NEWcolumn_names,
     group_concat("OLD.", column_name, "" ORDER BY column_order_key)               											AS OLDcolumn_names
   FROM
     (
       SELECT
         information_schema.tables.table_name        AS table_name,
         information_schema.columns.column_name      AS column_name,
         information_schema.columns.column_type      AS column_type,
         information_schema.tables.create_time       AS table_order_key,
         information_schema.columns.ordinal_position AS column_order_key
       FROM information_schema.tables
         JOIN information_schema.columns
           ON information_schema.tables.table_name = information_schema.columns.table_name
       WHERE information_schema.tables.table_schema = @dbName
             AND information_schema.columns.table_schema = @dbName
             AND information_schema.tables.table_name NOT LIKE "audit\_%"
             AND information_schema.tables.table_type in ("BASE TABLE")
             AND information_schema.tables.table_name = @tablename
     ) table_column_ordering_info
   GROUP BY table_name
 ) table_data
ORDER BY table_order_key
INTO OUTFILE "d:\c5.txt";