#!/bin/bash

# show commands being executed, per debug
# set -x

# define database connectivity
_db="xxxxxxxx"
_db_host="xxxxxxxx"
_db_user="xxxxxxx"
# _db_password="xxxxxx"

mysql_config_editor set --host=$_db_host --user=$_db_user --password
# define directory containing CSV files
_csv_directory="/path/to/csv"

# go into directory
cd $_csv_directory

# get a list of CSV files in directory
_csv_files=`ls -1 *.csv`

# loop through csv files
for _csv_file in ${_csv_files[@]}
do

  # remove file extension
  _csv_file_extensionless=`echo $_csv_file | sed 's/\(.*\)\..*/\1/'`

  # define table name
  _table_name="${_csv_file_extensionless}"

  # get header columns from CSV file
  _header_columns=`head -1 $_csv_directory/$_csv_file | tr ',' '\n' | sed 's/^"//' | sed 's/"$//' | sed 's/ /_/g'`
  _header_columns_string=`head -1 $_csv_directory/$_csv_file | sed 's/ /_/g' | sed 's/"//g'`

  echo "---> Creating table \`$_table_name\`"

  # ensure table exists
  mysql $_db << eof
    CREATE TABLE IF NOT EXISTS \`$_table_name\` (name_temp_xyz VARCHAR(20)) ENGINE=InnoDB CHARACTER SET=utf8mb4;
eof
  has_primary=false
  # loop through header columns
  for _header in ${_header_columns[@]}
  do

    # add column
    column_type="text CHARACTER SET utf8mb4"
    echo "adding \`$_header\`"
    if [[ $_header == *"_id"* ]]
    then
      column_type="integer"
    fi
    if [[ $_header == "id" ]]
    then
      column_type="integer"
      has_primary=true
    fi
    echo "-> type is $column_type"
    # echo "exec: alter table \`$_table_name\` add column \`$_header\` $column_type collate utf8_general_ci"
    mysql $_db --execute="alter table \`$_table_name\` add column \`$_header\` $column_type"
    if [[ $_header == *"_id"* ]]
    then
      mysql $_db --execute="alter table \`$_table_name\` add index ($_header)"
      echo "-> indexed"
    fi
    if [[ $_header == "id" ]]
    then
      mysql $_db --execute="alter table \`$_table_name\` add primary key ($_header)"
      echo "-> primary key"
    fi
  done


  echo "removing temp column"
  mysql $_db --execute="alter table \`$_table_name\` drop column \`name_temp_xyz\`"
  echo "importing datas..."
  mysql $_db --enable-local-infile << eof
  LOAD DATA LOCAL INFILE "$_csv_directory/$_csv_file"
  INTO TABLE $_table_name
  CHARACTER SET UTF8
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  IGNORE 1 ROWS
eof
  if [ "$has_primary" = false ] ; then
    mysql $_db --execute="ALTER TABLE \`$_table_name\` ADD \`id\` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY(id)"
    echo "added primary key"
  fi
done
exit