#!/bin/bash

# MAIN #

today="$(date +'%Y-%m-%d')"
echo - Start -;
# today="2023-04-08"
echo $today."\r\n"
dateconvert=$(date -d "$today" "+%s")
dbprefix="my04"

databases=`mysql -e "SHOW DATABASES like '$dbprefix%';" | tr -d "| " | grep -v Database`
SQL_EXISTS=$(printf 'SHOW TABLES LIKE "%s"' "kwsp")
ON_KWSP=$(printf 'SELECT 1 FROM %s limit 1' "kwsp")


for dbname in $databases; do
    if [[ "$dbname" != "information_schema" ]] && [[ "$dbname" != _* ]] ; then
       
            if [[ $(mysql -u root -e "$SQL_EXISTS" $dbname) ]]
            then
            	if [[ $(mysql -u root -e "$ON_KWSP" $dbname) ]]
                then
                    echo "$dbname\r\n"
                    ON_KWSP2=$(printf 'ALTER TABLE `kwsp` CHANGE `jadual` `jadual` VARCHAR(64) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;' "kwsp")
                    answer=$(mysql -u root -e "$ON_KWSP2" $dbname)
                    # echo $answer."\r\n"
                    
                    # s=("SOCSO-2024-OKT.csv" "SOCSO-60-OKT-24.csv" "SOCSO-SIP-2024.csv");
                    s=("SOCSO-60-SKBBK-062026.csv" "SOCSO-SKBBK-062026.csv");
                    for n in ${s[@]}; 
                    do
                        
                        type=SOCSO
                        if [[ $n == *"SIP"* ]]; then
                          type="SOCSO_SIP"
                        fi

                        if [[ $n == *"SKBBK"* ]]; then
                          type="SOCSO_SKBBK"
                        fi
                        # echo "$n"
                        # echo "$type"
                        # open file


                        while IFS="," read -r jadual description income_min income_max employer staff total || [ -n "$jadual" ]
                        do
                            echo "Jadual $jadual"
                            echo "Description $description"
                            echo "min $income_min"
                            echo "max $income_max"
                            echo "employer $employer"
                            echo "staff $staff"
                            echo "total $total"
                            echo ""
                            echo ""
                            echo "$dbname"

                            
                            socso=$(mysql -N -s -u root -e "SELECT count(*) FROM type WHERE grp='epftable' and prm='$jadual';" $dbname)
                            # echo $cols."\r\n"
                            if [[ $socso<=0 ]]
                            then
                            tambah=$(mysql -u root -e "INSERT INTO type(prm,grp,code,ts,des) VALUES('$jadual','epftable','$type',now(),'$description');" $dbname)
                            fi

                            if [[ $socso==1 ]]
                            then
                            tambah=$(mysql -u root -e "update type set code='$type',des='$description' where prm='$jadual' and grp='epftable';" $dbname)
                            fi

                            $del=$(mysql -N -s -u root -e "DELETE FROM kwsp WHERE jadual='$jadual' and income_min='$income_min' and income_max='$income_max';" $dbname);

                            cols=$(mysql -N -s -u root -e "SELECT count(*) FROM kwsp WHERE jadual='$jadual' and income_min='$income_min' and income_max='$income_max';" $dbname)
                            # echo $cols."\r\n"
                            if [[ $cols<=0 ]]
                            then
                            ret=$(mysql -u root -e "INSERT INTO kwsp(jadual,income_min,income_max,employer,staff,total) VALUES('$jadual','$income_min','$income_max','$employer','$staff','$total');" $dbname)
                            echo "$ret : add INFO socso : $jadual"
                            fi

                            if [[ $cols==1 ]]
                            then
                            ret=$(mysql -u root -e "update kwsp set employer='$employer', staff='$staff', total='$total' where jadual='$jadual' and income_min='$income_min' and income_max='$income_max';" $dbname)
                            echo "$ret : update INFO socso : $jadual"
                            fi


                        done < <(tail -n +2 $n)
                    done
                fi
            fi
       
    fi
done
echo - End -
# END #