#!/usr/bin/env bash

set -e

scope="test"
if [ $scope == "test" ] 
then
    bot_url="https://open.feishu.cn/open-apis/bot/v2/hook/6aed0f4e-f7f1-4223-b5a3-d503e7100587"
    temp_file_sql="/tmp/temp_file_sql"
    tmp_json_file="/tmp/resulofsomething.json"
    mysql_command="mysql -ustaging -htidb.e23bdccc.cb17aaf2.us-west-2.prod.aws.tidbcloud.com -P4000 -pZqfjTO5G_CJx%AdiqFuyVS6P staging_central"
    sleep_time="10"
elif [ $scope == "poc" ]
then
    bot_url="https://open.feishu.cn/open-apis/bot/v2/hook/31da37e4-7a81-470f-b773-96dc09afb7e6"
    temp_file_sql="/tmp/temp_file_sql"
    tmp_json_file="/tmp/resulofsomething.json"
    mysql_command="mysql -h metadb-tidb -P 4000 dbaas"
    sleep_time="900"
elif [ $scope == "ondemand" ]
then
    bot_url="https://open.feishu.cn/open-apis/bot/v2/hook/a82ed720-25b1-4a6a-93aa-f8a4d0d4f44c"
    temp_file_sql="/tmp/temp_file_sql_od"
    tmp_json_file="/tmp/resulofsomething_od.json"
    mysql_command="mysql -h metadb-tidb -P 4000 dbaas"
    sleep_time="900"
elif [ $scope == "devtier" ]
then
    bot_url="https://open.feishu.cn/open-apis/bot/v2/hook/a82ed720-25b1-4a6a-93aa-f8a4d0d4f44c"
    temp_file_sql="/tmp/temp_file_sql_od"
    tmp_json_file="/tmp/resulofsomething_od.json"
    mysql_command="mysql -h metadb-tidb -P 4000 dbaas"
    sleep_time="43200"
fi

daily_report_bot_url="https://open.feishu.cn/open-apis/bot/v2/hook/6aed0f4e-f7f1-4223-b5a3-d503e7100587"

ondemand_bot_url=""

daily_report_file=""


countrycode_file="/tmp/fileforcountrycode_poc"

appendRegionInfo() {
# Generate country_code file
cat > $countrycode_file << EOF
    AD,EMEA
    AE,EMEA
    AF,EMEA
    AG,NA
    AI,NA
    AL,EMEA
    AM,EMEA
    AO,EMEA
    AQ,EMEA
    AR,NA
    AS,APAC
    AT,EMEA
    AU,APAC
    AW,NA
    AX,EMEA
    AZ,EMEA
    BA,EMEA
    BB,NA
    BD,APAC
    BE,EMEA
    BF,EMEA
    BG,EMEA
    BH,EMEA
    BI,EMEA
    BJ,EMEA
    BL,EMEA
    BM,NA
    BN,APAC
    BO,NA
    BR,NA
    BS,NA
    BT,APAC
    BV,EMEA
    BW,EMEA
    BY,EMEA
    BZ,NA
    CA,NA
    CC,APAC
    CD,EMEA
    CF,EMEA
    CG,EMEA
    CH,EMEA
    CI,EMEA
    CK,EMEA
    CL,NA
    CM,EMEA
    CN,CO
    CO,NA
    CR,NA
    CU,NA
    CV,EMEA
    CW,EMEA
    CX,APAC
    CY,EMEA
    CZ,EMEA
    DE,EMEA
    DJ,EMEA
    DK,EMEA
    DM,NA
    DO,NA
    DZ,EMEA
    EC,NA
    EE,EMEA
    EG,EMEA
    EH,EMEA
    ER,EMEA
    ES,EMEA
    ET,EMEA
    FI,EMEA
    FJ,APAC
    FK,NA
    FM,APAC
    FO,EMEA
    FR,EMEA
    GA,EMEA
    GB,EMEA
    GD,NA
    GE,EMEA
    GF,EMEA
    GG,EMEA
    GH,EMEA
    GI,EMEA
    GL,EMEA
    GM,EMEA
    GN,EMEA
    GP,EMEA
    GQ,EMEA
    GR,EMEA
    GS,NA
    GT,NA
    GU,APAC
    GW,EMEA
    GY,NA
    HK,APAC
    HM,EMEA
    HN,NA
    HR,EMEA
    HT,NA
    HU,EMEA
    ID,APAC
    IE,EMEA
    IL,EMEA
    IM,EMEA
    IN,APAC
    IO,APAC
    IQ,EMEA
    IR,EMEA
    IS,EMEA
    IT,EMEA
    JE,EMEA
    JM,NA
    JO,EMEA
    JP,JP
    KE,EMEA
    KG,EMEA
    KH,APAC
    KI,APAC
    KM,EMEA
    KN,NA
    KP,APAC
    KR,APAC
    KW,EMEA
    KY,NA
    KZ,EMEA
    LA,APAC
    LB,EMEA
    LC,NA
    LI,EMEA
    LK,APAC
    LR,EMEA
    LS,EMEA
    LT,EMEA
    LU,EMEA
    LV,EMEA
    LY,EMEA
    MA,EMEA
    MC,EMEA
    MD,EMEA
    ME,EMEA
    MF,EMEA
    MG,EMEA
    MH,APAC
    MK,EMEA
    ML,EMEA
    MM,APAC
    MN,APAC
    MO,APAC
    MP,APAC
    MQ,EMEA
    MR,EMEA
    MS,NA
    MT,EMEA
    MU,EMEA
    MV,APAC
    MW,EMEA
    MX,NA
    MY,APAC
    MZ,EMEA
    NA,EMEA
    NC,EMEA
    NE,EMEA
    NF,APAC
    NG,EMEA
    NI,NA
    NL,EMEA
    NO,EMEA
    NP,APAC
    NR,APAC
    NU,APAC
    NZ,APAC
    OM,EMEA
    PA,NA
    PE,NA
    PF,EMEA
    PG,APAC
    PH,APAC
    PK,APAC
    PL,EMEA
    PM,EMEA
    PN,APAC
    PR,NA
    PS,EMEA
    PT,EMEA
    PW,APAC
    PY,NA
    QA,EMEA
    RE,EMEA
    RO,EMEA
    RS,EMEA
    RU,EMEA
    RW,EMEA
    SA,EMEA
    SB,APAC
    SC,EMEA
    SD,EMEA
    SE,EMEA
    SG,APAC
    SH,EMEA
    SI,EMEA
    SK,EMEA
    SL,EMEA
    SM,EMEA
    SN,EMEA
    SO,EMEA
    SR,NA
    SS,EMEA
    ST,EMEA
    SV,NA
    SX,EMEA
    SY,EMEA
    SZ,EMEA
    TC,NA
    TD,EMEA
    TF,EMEA
    TG,EMEA
    TH,APAC
    TJ,EMEA
    TK,APAC
    TL,APAC
    TM,EMEA
    TN,EMEA
    TO,APAC
    TR,EMEA
    TT,NA
    TV,APAC
    TW,APAC
    TZ,EMEA
    UA,EMEA
    UG,EMEA
    UM,APAC
    US,NA
    UY,NA
    UZ,EMEA
    VA,EMEA
    VC,NA
    VE,NA
    VG,NA
    VI,NA
    VN,APAC
    VU,APAC
    WF,EMEA
    WS,APAC
    YE,EMEA
    YT,EMEA
    ZA,EMEA
    ZM,EMEA
    ZW,EMEA
EOF

#head -n 5 $countrycode_file

while  IFS= read -r  line
do
    country=$(echo "$line" | cut -f1)
    if [ -z "$country" ];
    then
        region_country="null"
    else
        region_country=$(grep -i "$country," $countrycode_file | sed 's/^[ \t]*//g' | awk -F ',' '{print $2 "-" $1}')
        if [ -z "$region_country" ];
        then 
            region_country="null-$country"
        fi
    fi
    echo "$line" | awk -F "\t" -v OFS="\t" "{\$1=\"$region_country\";print \$0 }"   
done
}

preprocess() {
    while IFS= read -r line
    do
        echo "$line" | sed "s/TIMESTAMP_TAG_LEFT/'$1'/" | sed "s/TIMESTAMP_TAG_RIGHT/'$2'/"
    done
}

# Dev tier user metrics - 1h
devtier_new_cluster_sql="select 
    t.country, t.id as tenant_id, t.name as tenant_name, t.alias as tenant_alias, c.id as cluster_id, c.status as cluster_status
from 
    tenants t join clusters c on t.id = c.tenant_id 
where
    (not lower(t.name) like '%pingcap%' ) and t.plan = 'free_trial' and t.status = 'active' And c.deploy_type = 'shared'
    and c.created_at >= TIMESTAMP_TAG_LEFT and c.created_at < TIMESTAMP_TAG_RIGHT
    and c.status not in ('cleared', 'deleted', 'deletecomplete', 'unknown') and t.kind = 'external';"

# a shared cluster finish a import during the time, message if needed 
devtier_import_finished_sql="select  
    t.country, t.id as tenant_id, t.name as tenant_name, t.alias as tenant_alias, c.id as cluster_id,c.name as cluster_name,
    c.status as clusters_status,i.id as import_id,i.status as status,i.total_size as total_size,i.completed_tables as completed_tables
    from 
        imports i join clusters c ON c.id = i.cluster_id JOIN tenants t ON c.tenant_id = t.id 
    WHERE 
        (not lower(t.name) like '%pingcap%' ) and t.kind = 'external' and
        c.deploy_type = 'shared' and t.status = 'active' 
        and c.status <> 'cleared' and i.finished_at >= TIMESTAMP_TAG_LEFT and i.finished_at < TIMESTAMP_TAG_RIGHT;"

# 7 days
# imported in 30 days 
#devtier_import_finished_30day_sql="select  
#    t.country, t.id as tenant_id, t.name as tenant_name, t.alias as tenant_alias, c.id as cluster_id,c.name as cluster_name,
#    c.status as clusters_status,i.id as import_id,i.status as status,i.total_size as total_size,i.completed_tables as completed_tables
#    from 
#        imports i join clusters c ON c.id = i.cluster_id JOIN tenants t ON c.tenant_id = t.id 
#    WHERE 
#        (not lower(t.name) like '%pingcap%' ) and t.kind = 'external'
#        and c.deploy_type = 'shared' and t.status = 'active' 
#        and c.status <> 'cleared' And i.finished_at > date_add(now(),interval - 60 day)
#        and t.country not in ('CN','JP','SG','ID');"

# cluster not deleted and created before x days
devtier_cluster_exist_7day_sql="select 
    t.country, t.id as tenant_id, t.name as tenant_name, t.alias as tenant_alias, c.id as cluster_id, c.status, c.name as cluster_name, t.email
from 
    tenants t join clusters c on t.id = c.tenant_id 
where t.country <> ''
limit 1;"
where
    (not lower(t.name) like '%pingcap%' ) and t.plan = 'free_trial' and t.status = 'active' And c.deploy_type = 'shared'
    and c.created_at < date_add(now(),interval - 13 day) and c.created_at > date_add(now(),interval - 14 day)
    and t.kind = 'external' and c.status not in ('cleared', 'deleted', 'deletecomplete', 'unknown');"

i="0"
old_ts="$(date -u '+%Y-%m-%d %T')"

while true; do
    new_ts="$(date -u '+%Y-%m-%d %T')"

    printf %s '{"msg_type" : "interactive", "card": {"config":{"wide_screen_mode": true},' > $tmp_json_file
    printf %s '"header": {"title": {"tag": "plain_text", "content": "Dev tier User Behavior"}, "template": "green"},'>> $tmp_json_file 
    printf %s '"elements": [{"tag": "markdown", "content":"'>> $tmp_json_file

    first_appeared="false"

    # dev tier bot
    #echo $devtier_import_finished_30day_sql | preprocess "$old_ts" "$new_ts" | $mysql_command | appendRegionInfo | awk -F "\t" 'NR!=1{printf "**[%s]** The import (%s) [status: **%s**, completed_tables: **%s**, total_size: **%s**] of cluster (%s) [status: %s] of tenant **[%s]** (%s) **finished**.\\n", $1, $8, $9, $11, $10, $5, $7, $3, $2}' > $temp_file_sql
    #echo $devtier_cluster_exist_7day_sql | preprocess "$old_ts" "$new_ts" | $mysql_command | appendRegionInfo | awk -F "\t" 'NR!=1{printf "**[%s]** 7 days active dev tier cluster **[%s]**(%s) created by tenant **[%s]**(%s).\\n", $1, $5, $6, $3, $2}' >> $temp_file_sql
    #echo $devtier_import_finished_sql_devtier | preprocess "$old_ts" "$new_ts" | $mysql_command | appendRegionInfo | awk -F "\t" 'NR!=1{printf "**[%s]** New import (%s) [status: **%s**, completed_tables: **%s**, total_size: **%s**] of cluster (%s) [status: %s] of tenant **[%s]** (%s) **finished**.\\n", $1, $8, $9, $11, $10, $5, $7, $3, $2}' >> $temp_file_sql
    #echo $devtier_new_cluster_sql | preprocess "$old_ts" "$new_ts" | $mysql_command | appendRegionInfo | awk -F "\t" 'NR!=1{printf "**[%s]** New dev tier cluster **[%s]**(%s) created by tenant **[%s]**(%s).\\n", $1, $5, $6, $3, $2}' >> $temp_file_sql

    echo $devtier_new_cluster_sql | preprocess "$old_ts" "$new_ts" | $mysql_command | appendRegionInfo | awk -F "\t" 'NR!=1{printf "**[%s]** (1) New dev tier cluster **[%s]**(%s) created by tenant **[%s]**(%s).\\n", $1, $5, $6, $3, $2}' > $temp_file_sql
    echo $devtier_import_finished_sql | preprocess "$old_ts" "$new_ts" | $mysql_command | appendRegionInfo | awk -F "\t" 'NR!=1{printf "**[%s]** (2) New import (%s) [status: **%s**, completed_tables: **%s**, total_size: **%s**] of cluster (%s) [status: %s] of tenant **[%s]** (%s) **finished**.\\n", $1, $8, $9, $11, $10, $5, $7, $3, $2}' >> $temp_file_sql
    echo $devtier_cluster_exist_7day_sql | preprocess "$old_ts" "$new_ts" | $mysql_command | appendRegionInfo | awk -F "\t" 'NR!=1{printf "**[%s]** (3) 14 days active dev tier cluster **[%s]**(%s) was created by tenant **[%s]**(%s).\\n", $1, $5, $6, $3, $2}' >> $temp_file_sql
    
    if [ -s "$temp_file_sql" ]
    then
        if [ $first_appeared == "true" ] 
        then
            printf %s '\n --------------\n' >> $tmp_json_file
        else
            first_appeared="true"
        fi
        #printf %s '**7 days updates:**\n' >> $tmp_json_file
        cat $temp_file_sql | sed 's/\\n/\\n\\n/g' >> $tmp_json_file
    fi

    # rm $temp_file_sql

    printf %s '"}]}}' >> $tmp_json_file
    cat $tmp_json_file
    curl -XPOST -H "Content-Type: application/json" $bot_url  -d @$tmp_json_file
    
    old_ts=$new_ts

    i=$(($i + 1))
    printf %s '{"msg_type":"text","content":{"text":"dev tier loop: ' > $tmp_json_file
    printf %s "$i" >> $tmp_json_file
    printf %s ' timestamp: ' >> $tmp_json_file
    printf %s "$old_ts" >> $tmp_json_file
    printf %s '"}}' >> $tmp_json_file


    curl -XPOST -H "Content-Type: application/json" $daily_report_bot_url  -d @$tmp_json_file

    #if [[ "$date" != "$(date +%D)" ]] 
    #then 
    #    printf %s '{"msg_type" : "interactive", "card": {"config":{"wide_screen_mode": true},' > $tmp_json_file
    #    printf %s "\"header\": {\"title\": {\"tag\": \"plain_text\", \"content\": \"$date Daily Tenant Report\"}, \"template\": \"green\"},">> $tmp_json_file 
    #    printf %s '"elements": [{"tag": "markdown", "content":"<at id=all></at>\n'>> $tmp_json_file

    #    echo $daily_report_tenant_sql | $mysql_command | awk -F "\t" 'NR!=1{printf "**id**: %s\\n**name:** %s\\n**alias:** %s\\n**email:** %s\\n**country:** %s\\n**status:** %s\\n**plan:** %s\\n**bind_credit_card_at:** %s\\n**kind:** %s\\n**created_at:** %s\\n**updated_at:** %s\\n\\n", $1, $2, $3, $4,$5,$6,$7,$8,$9,$10,$11}' | tr -d '\n' >> $tmp_json_file

    #    printf %s '"}]}}' >> $tmp_json_file
    #    curl -XPOST -H "Content-Type: application/json" $daily_report_bot_url  -d @$tmp_json_file

    #    date="$(date +%D)"
    #fi

    sleep $sleep_time
done