#!/bin/bash

SQL[0]="
SELECT SQL_NO_CACHE zbozi_id, zbozi_cena, mnozstvi, rez_mnozstvi, COALESCE(mnozstvi - rez_mnozstvi, mnozstvi) as volne
FROM zbozi
JOIN (SELECT sum(sklad_mnozstvi) as mnozstvi, zbozi_id FROM sklad WHERE sklad_vyskladneno is null and umisteni_id = 1 GROUP BY zbozi_id) as skl USING (zbozi_id)
LEFT JOIN (SELECT SUM(polozka_mnozstvi) as rez_mnozstvi, zbozi_id
                   FROM polozka p
                   join objednavka using (objednavka_id)
                   join objednavka_stav on objednavka_stav_id = obj_stav_id
                   JOIN umisteni USING (umisteni_id)
                   WHERE umisteni_vyrizeno = 0
                   AND p.aktivni = '1'
                   and objednavka_stav_vyrizeno = 0 GROUP BY zbozi_id) as rez USING (zbozi_id)
WHERE COALESCE(mnozstvi - rez_mnozstvi, mnozstvi) > 0
"
SQL[1]="
SELECT SQL_NO_CACHE SUM(CASE WHEN (skladem.mnozstvi - (CASE WHEN rezervovane.mnozstvi IS NULL THEN 0 ELSE rezervovane.mnozstvi END)) > 0 THEN skladem.mnozstvi - (CASE WHEN rezervovane.mnozstvi IS NULL THEN 0 ELSE rezervovane.mnozstvi END) ELSE 0 END) as volne
FROM (SELECT SUM(sklad_mnozstvi) as mnozstvi, zbozi_id
     FROM sklad
     WHERE sklad_vyskladneno IS NULL AND umisteni_id = 1
     GROUP BY zbozi_id) as skladem
LEFT JOIN (SELECT SUM(p.polozka_mnozstvi) AS mnozstvi, zbozi_id
     FROM polozka p
     INNER JOIN objednavka o ON  o.objednavka_id = p.objednavka_id
     INNER JOIN objednavka_stav os ON  os.objednavka_stav_id = o.obj_stav_id
     INNER JOIN umisteni u on u.umisteni_id = p.umisteni_id
     WHERE umisteni_vyrizeno = '0'
     AND p.aktivni = '1' AND objednavka_stav_vyrizeno = '0'
     GROUP BY zbozi_id) as rezervovane ON (skladem.zbozi_id = rezervovane.zbozi_id)
"
SQL[2]="
SELECT SQL_NO_CACHE polozka.polozka_id, polozka.zbozi_id, polozka.polozka_mnozstvi, polozka.umisteni_id, polozka.objednavka_id, obj_stav_id, zbozi_dostupnost, polozka_nazev, postovne_id
FROM polozka
LEFT JOIN objednavka USING (objednavka_id)
LEFT JOIN objednavka_stav ON (objednavka.obj_stav_id = objednavka_stav.objednavka_stav_id)
LEFT JOIN umisteni ON (polozka.umisteni_id = umisteni.umisteni_id)
LEFT JOIN zbozi USING (zbozi_id)
WHERE objednavka_stav_vyrizeno !=1 and umisteni_vyrizeno!=1 and polozka_mnozstvi>0
and polozka.aktivni=1 and objednavka.aktivni=1 and polozka.zbozi_id!=13658
and objednavka_stav.objednavka_stav_id!=17 ORDER BY polozka.polozka_id ASC
"
SQL[3]="
SELECT SQL_NO_CACHE dodavatel_id, DATEDIFF(CURRENT_DATE, sklad_objednani_objednano) as pocet_dni_na_ceste, sum(sklad_objednani_mnozstvi) as kusu
FROM sklad_objednani_
WHERE sklad_objednani_temp = 0 AND sklad_objednani_objednano IS NOT NULL AND sklad_objednani_doruceno IS NULL AND sklad_objednani_aktivni = 1 AND dodavatel_id > 0
GROUP BY dodavatel_id, pocet_dni_na_ceste
"
SQL[4]="
SELECT SQL_NO_CACHE max(cv.cv_kod)
               FROM cennik_velkoobchodu cv
               JOIN cv_knv_detaily cr ON (cv.cv_kod = cr.cv_kod AND cv.dodavatel_id = 46)
               LEFT JOIN carovykod ON (carovykod_ean = cv_ean)
               WHERE cv_sparovano = 0 AND cv_zpracovano = 1 AND cv_novinka = 1 AND cv_cena_bez_dph > 0 AND cv_ean IS NOT NULL
               AND cv_ean != '' AND LENGTH(cv_ean) = 13 AND cv_ean LIKE '978%' AND LENGTH(cv_nazev) >= 3 AND carovykod_ean IS NULL
               AND skladovost > 0 AND dostupnost = 1 AND dostupnost_typ = 1 AND pocet_dni_k_doruceni = 0 AND obrazek != '' AND obrazek IS NOT NULL AND obrazek_update IS NOT NULL AND nakupka_czk > 0 AND dph IN (7,19)
               AND LENGTH(cr.vydavatel) < 64 AND LENGTH(isbn) <= 20
               GROUP BY ean
               HAVING count(ean) = 1
               LIMIT 100
"
SQL[5]="
SELECT SQL_NO_CACHE jazyk, count(*) as pocet, unifikace_jazyku_zkratka
FROM cv_knv_detaily
LEFT JOIN unifikace_jazyku ON (jazyk = unifikace_jazyku_vyraz)
WHERE jazyk != '' AND jazyk IS NOT NULL
GROUP BY jazyk, unifikace_jazyku_zkratka
HAVING count(*) > 9
ORDER BY unifikace_jazyku_zkratka IS NOT NULL,pocet DESC
"

DB_NAME="knihy_cme"

MARIA_HOST="cme-maria.cjpu7au3nbbl.eu-central-1.rds.amazonaws.com"
MARIA_PORT="3306"
MARIA_USER="rds_root"
MARIA_PASS="eeC0hei8tul"

MYSQL_HOST="cme-mysql.cjpu7au3nbbl.eu-central-1.rds.amazonaws.com"
MYSQL_PORT="3306"
MYSQL_USER="rds_root"
MYSQL_PASS="ies7uWooGhah"

POSTG_HOST="cme-postgre.cjpu7au3nbbl.eu-central-1.rds.amazonaws.com"
POSTG_PORT="5432"
POSTG_USER="rds_root"
POSTG_PASS="phur0coo4Oen"


COUNT=$1

echo "Testuji MariaDB:"
TOTAL_TIME=0
for ((i=0; i<${#SQL[@]}; i++))
do
    START=$(date +%s.%N)
    for j in $(seq 1 $COUNT)
    do
        mysql -h$MARIA_HOST -P$MARIA_PORT -u$MARIA_USER -p$MARIA_PASS $DB_NAME -e"${SQL[$i]}" > /dev/null
    done
    END=$(date +%s.%N)
    TIME=$(bc -l <<< "scale=5; ($END-$START)/$COUNT")
    TOTAL_TIME=$(bc -l <<< "$TOTAL_TIME+$TIME")
    echo "Prumerny beh prikazu $((i + 1)) po $COUNT spusteni: $TIME"
done
TOTAL_AVERAGE=$(bc -l <<< "scale=5; $TOTAL_TIME/(${#SQL[@]})")
echo "Celkovy prumerny beh prikazu: $TOTAL_AVERAGE"

echo $'\nTestuji Postgres:'
TOTAL_TIME=0
for ((i=0; i<${#SQL[@]}; i++))
do
    START=$(date +%s.%N)
    for j in $(seq 1 $COUNT)
    do
        psql=$(echo "${SQL[$i]}" | sed 's/SQL_NO_CACHE//')
        if [ $i -eq 3 ]
        then
            psql=$(sed "s/DATEDIFF(CURRENT_DATE, sklad_objednani_objednano)/DATE_PART('day', CURRENT_DATE::timestamp - sklad_objednani_objednano::timestamp)/" <<< $psql)
        fi
        PGPASSWORD=$POSTG_PASS psql -h$POSTG_HOST -p$POSTG_PORT $DB_NAME $POSTG_USER -c"$psql" > /dev/null
    done
    END=$(date +%s.%N)
    TIME=$(bc -l <<< "scale=5; ($END-$START)/$COUNT")
    TOTAL_TIME=$(bc -l <<< "$TOTAL_TIME+$TIME")
    echo "Prumerny beh prikazu $((i + 1)) po $COUNT spusteni: $TIME"
done
TOTAL_AVERAGE=$(bc -l <<< "scale=5; $TOTAL_TIME/(${#SQL[@]})")
echo "Celkovy prumerny beh prikazu: $TOTAL_AVERAGE"

echo $'\nTestuji MySQL:'
TOTAL_TIME=0
for ((i=0; i<${#SQL[@]}; i++))
do
    START=$(date +%s.%N)
    for j in $(seq 1 $COUNT)
    do
        mysql -h$MYSQL_HOST -P$MYSQL_PORT -u$MYSQL_USER -p$MYSQL_PASS $DB_NAME -e"${SQL[$i]}" > /dev/null
    done
    END=$(date +%s.%N)
    TIME=$(bc -l <<< "scale=5; ($END-$START)/$COUNT")
    TOTAL_TIME=$(bc -l <<< "$TOTAL_TIME+$TIME")
    echo "Prumerny beh prikazu $((i + 1)) po $COUNT spusteni: $TIME"
done
TOTAL_AVERAGE=$(bc -l <<< "scale=5; $TOTAL_TIME/(${#SQL[@]})")
echo "Celkovy prumerny beh prikazu: $TOTAL_AVERAGE"