<?php
return [
    "DROP PROCEDURE if exists `bulk_process_test`;",

    "CREATE  PROCEDURE `bulk_process_test`(IN `filepath` VARCHAR(255))
    BEGIN

        DECLARE cols TEXT DEFAULT '';
      DECLARE email VARCHAR(255);
      DECLARE Service_Name VARCHAR(255);
      DECLARE Data_of_Service DATE;
      DECLARE Place_of_Service_Type VARCHAR(255);
      DECLARE Provider_Type VARCHAR(255);
      DECLARE Conversion_Factor_Name VARCHAR(255);
      DECLARE LocalityName VARCHAR(255);
      DECLARE HCPCS VARCHAR(255);
      DECLARE modifier VARCHAR(255);
      DECLARE Units INT;
      DECLARE Total_Payment FLOAT;
      -- Creating the temporary table
      CREATE TEMPORARY TABLE temp_omfs_source (omfs INT);
      -- Reading the CSV file and getting the column names
      SET @vstr = CONCAT('LOAD DATA INFILE ', QUOTE(COALESCE(filepath, '')), ' INTO TABLE temp_omfs_source
                        FIELDS TERMINATED BY '',''
                        ENCLOSED BY ''''
                        LINES TERMINATED BY ''\n''
                        IGNORE 1 ROWS');

      -- Adding the columns to the temporary table
      SET cols = (SELECT GROUP_CONCAT(COLUMN_NAME) FROM information_schema.COLUMNS
                  WHERE TABLE_NAME = 'temp_omfs_source');

      SET @sql = CONCAT('ALTER TABLE temp_omfs_source ADD (', cols, ');');
      PREPARE stmt FROM @sql;
      EXECUTE stmt;

      ALTER TABLE temp_omfs_source ADD omfs INT;

      UPDATE temp_omfs_source SET omfs = 'Testing';

      -- Returning the output
      SELECT omfs FROM temp_omfs_source;
      END "


];
