#!/bin/bash

CONFIG_FILE="/etc/myapp/db.conf"

# Check if the config file exists and source it
if [ -f "$CONFIG_FILE" ]; then
    . /etc/myapp/db.conf
    echo "Configuration loaded from $CONFIG_FILE"
else
    echo "Error: Config file not found at $CONFIG_FILE"
    exit 1
fi

DBUSER="$DB_USER";
DBPASS="$DB_PASSWORD";
DBHOST="$DB_HOST";

DB_OLD="byzfunder_dev"
DB_NEW="byzfunder_devtest"

DBCONN="--host=${DBHOST} --user=${DBUSER} --password=${DBPASS}";

MYSQL_CREATE='set foreign_key_checks = 0';
MYSQL_INSERT='set foreign_key_checks = 0';

echo "Begin database clone (may take a while)";

    DATABASE_COLLATION=$(echo "SELECT @@character_set_database, @@collation_database;" | mysql $DBCONN $DB_OLD | tail -n 1 | awk '{ print $2 }');
    echo "DROP DATABASE IF EXISTS ${DB_NEW}; CREATE DATABASE ${DB_NEW} COLLATE ${DATABASE_COLLATION}" | mysql ${DBCONN};
    [ $? -ne 0 ] && exit $?;


echo -e "\n -- Select source table schemas";
MYSQL_TABLES=$(echo "SHOW FULL TABLES WHERE Table_Type = 'BASE TABLE' AND Tables_in_byzfunder_dev NOT IN ('bank_statement_line','underwriting_worksheets_banks_details','partner_api_deal_submissions', 'files', 'ext_api_tokens', 'unicourt_access_token', 'experian_token', 'temp_aprd')" | mysql $DBCONN $DB_OLD | tail -n +2 | awk '{ print $1 }');

[ $? -ne 0 ] && exit $?;
COUNT=0
for TABLE in $MYSQL_TABLES; do
    echo -n "${TABLE}";
    TABLE="\`${TABLE}\`";

    CREATE_SQL=$(echo "SHOW CREATE TABLE ${TABLE}" | mysql -B -r ${DBCONN} ${DB_OLD} |tail -n +2 | cut -f 2- | sed -e "s/NOT NULL DEFAULT '0000-00-00 00:00:00'/NOT NULL/g" | sed -e "s/NOT NULL DEFAULT '0000-00-00'/NOT NULL/g");
    MYSQL_CREATE="${MYSQL_CREATE}; ${CREATE_SQL}";

    [ $? -ne 0 ] && echo && exit $?;

    echo -e "\t [ok]";

    COUNT=$((COUNT+1))
done;
echo "-- Total ${COUNT} tables will be cloned ..."

MYSQL_CREATE="${MYSQL_CREATE}; set foreign_key_checks = 1";
MYSQL_INSERT="${MYSQL_INSERT}; set foreign_key_checks = 1";

[ $COUNT -gt 1 ] && echo " ]";

echo "-- Create destination table schemas";
echo "${MYSQL_CREATE};" | mysql $DBCONN $DB_NEW

[ $? -ne 0 ] && exit $?;

echo "-- Insert source data into destination tables";
# echo "${MYSQL_INSERT};" | mysql $DBCONN $DB_NEW

COUNT=0
for TABLE in $MYSQL_TABLES; do
    echo -n "${TABLE}";
    TABLE="\`${TABLE}\`";
    MYSQL_INSERT="";
    
    
        MYSQL_INSERT='set foreign_key_checks = 0';

        MYSQL_INSERT="${MYSQL_INSERT}; ALTER TABLE ${DB_NEW}.${TABLE} DISABLE KEYS";

        #if $((COUNT > 318)); then
            for i in $(seq 0 225); do
                MYSQL_INSERT="${MYSQL_INSERT}; INSERT IGNORE INTO ${DB_NEW}.${TABLE} SELECT * FROM ${DB_OLD}.${TABLE} LIMIT 20000 OFFSET $((i * 20000))";
            done;

            MYSQL_INSERT="${MYSQL_INSERT}; ALTER TABLE ${DB_NEW}.${TABLE} ENABLE KEYS";
            MYSQL_INSERT="${MYSQL_INSERT}; set foreign_key_checks = 1";
            echo "${MYSQL_INSERT};" | mysql $DBCONN $DB_NEW
            

            [ $? -ne 0 ] && echo && exit $?;

            echo -e "\t [ok]"; 
        #fi 
   
        COUNT=$((COUNT+1))
    
done;

[ $? -ne 0 ] && exit $?;

echo "Done.";