use crate::coin_health_check_team::{request_candles::*, request_others::ExchangeInfo};
use crate::decimal_funcs::*;
use crate::RunningMode::*;
use crate::{database_control::*, RUNNING_MODE};
use hex::ToHex;
use hmac_sha256::HMAC;
use reqwest::{Client, ClientBuilder, Response};
use rust_decimal::{prelude::ToPrimitive, Decimal};
use rust_decimal_macros::dec;
use serde::Deserialize;
use serde_json::Value;
use sqlx::{Error, FromRow};
use std::borrow::{Borrow, BorrowMut};
use std::collections::{HashMap, HashSet};
use std::sync::Arc;
use tokio::{join, sync::Mutex, time::*};

#[derive(Debug, FromRow)]
struct AllCoinProfitChangeAvgList {
    server_epoch: u64,
    cnt: u32,
    avg_profit: f64,
}

pub async fn initialize_valid_usde_trade() -> Result<(), Box<dyn std::error::Error + Send + Sync>> {
    #[derive(Debug, FromRow)]
    struct UsdtTrades {
        symbol: String,
    }

    let mut usdt_trades = UsdtTrades {
        symbol: String::new(),
    };

    let fetch_table_name = String::from("all_24h_change");
    let column_name = String::from("symbol");
    let mut condition_build =
        String::from("WHERE symbol LIKE '%USDT' AND symbol NOT LIKE '%DOWNUSDT'");
    condition_build.push_str(" AND symbol NOT LIKE '%UPUSDT' AND firstId >= 0 AND lastId >= 0");
    condition_build.push_str(" AND bidPrice > 0");
    condition_build.push_str(" AND bidQty > 0");
    condition_build.push_str(" AND askPrice > 0");
    condition_build.push_str(" AND askQty > 0");
    // add fiat and unnessesary coins
    condition_build.push_str(" AND symbol NOT LIKE 'BUSDUSDT'");
    condition_build.push_str(" AND symbol NOT LIKE 'USTCUSDT'");
    condition_build.push_str(" AND symbol NOT LIKE 'FDUSDUSDT'");
    condition_build.push_str(" AND symbol NOT LIKE 'TUSDUSDT'");
    condition_build.push_str(" AND symbol NOT LIKE 'USDPUSDT'");
    condition_build.push_str(" AND symbol NOT LIKE 'SUSDUSDT'");
    condition_build.push_str(" AND symbol NOT LIKE 'AUDUSDT'");
    condition_build.push_str(" AND symbol NOT LIKE 'EURUSDT'");
    condition_build.push_str(" AND symbol NOT LIKE 'GBPUSDT'");
    condition_build.push_str(" AND symbol NOT LIKE 'USDCUSDT'");
    condition_build.push_str(" AND symbol NOT LIKE 'BZRXUSDT'");
    condition_build.push_str(" AND symbol NOT LIKE 'USTUSDT'");
    condition_build.push_str(" AND symbol NOT LIKE 'NBTUSDT'");
    condition_build.push_str(" AND symbol NOT LIKE 'VGXUSDT'");
    condition_build.push_str(" AND symbol NOT LIKE 'RAMPUSDT'");
    condition_build.push_str(" AND symbol NOT LIKE 'TORNUSDT'");
    condition_build.push_str(" AND symbol NOT LIKE 'BTTCUSDT'");
    condition_build.push_str(" AND symbol NOT LIKE 'BTCSTUSDT'");
    condition_build.push_str(" AND symbol NOT LIKE 'ACAUSDT'");
    condition_build.push_str(" AND symbol NOT LIKE 'ANCUSDT'");

    let condition = Some(condition_build);

    // get valid usdt trades
    let usdt_trades =
        select_record(&fetch_table_name, &column_name, &condition, &usdt_trades).await?;

    // update valid usdt trades
    let table_name = String::from("valid_usdt_trades");
    let columns = vec!["symbol"];
    let mut value_wrapper: Vec<Vec<String>> = Vec::new();
    for element in &usdt_trades {
        let mut inner_vec: Vec<String> = Vec::new();
        inner_vec.push(element.symbol.clone());
        value_wrapper.push(inner_vec);
    }
    delete_all_rows(&table_name).await?;
    insert_records(&table_name, &columns, &value_wrapper).await?;

    Ok(())
}

// filter valid USDT trades from all24hstatistics table in database
pub async fn collect_valid_usde_trade(
    valid_usdt_trade_vec: &mut HashSet<String>,
    exchange_info_vec: &HashMap<String, ExchangeInfo>,
) -> Result<(), Box<dyn std::error::Error + Send + Sync>> {
    #[derive(Debug, FromRow)]
    struct UsdtTrades {
        symbol: String,
        weightedavgprice: f64,
    }

    let mut usdt_trades = UsdtTrades {
        symbol: String::new(),
        weightedavgprice: 0.0,
    };

    let fetch_table_name = String::from("all_24h_change");
    let column_name = String::from("symbol, weightedAvgPrice");
    let mut condition_build =
        String::from("WHERE symbol LIKE '%USDT' AND symbol NOT LIKE '%DOWNUSDT'");
    condition_build.push_str(" AND symbol NOT LIKE '%UPUSDT' AND firstId >= 0 AND lastId >= 0");
    condition_build.push_str(" AND bidPrice > 0");
    condition_build.push_str(" AND bidQty > 0");
    condition_build.push_str(" AND askPrice > 0");
    condition_build.push_str(" AND askQty > 0");
    // condition_build.push_str(" AND symbol NOT IN (SELECT symbol FROM stop_usdt_trades)");
    let condition = Some(condition_build);

    // get valid usdt trades
    let usdt_trades =
        select_record(&fetch_table_name, &column_name, &condition, &usdt_trades).await?;

    // get banned usdt trades
    #[derive(Debug, FromRow)]
    struct Symbols {
        symbol: String,
    }

    let table_name = String::from("banned_usdt_trades");
    let column_name = String::from("symbol");
    let condition = None;
    let mut symbols = Symbols {
        symbol: String::new(),
    };

    let mut select_result = try_select_record(&table_name, &column_name, &condition, &symbols)
        .await
        .unwrap();
    let mut banned_usdt_trades_set: HashSet<String> = HashSet::new();
    for element in select_result {
        banned_usdt_trades_set.insert(element.symbol.clone());
    }

    // filtering usdt trades
    let mut filtered_usdt_trades: HashSet<String> = HashSet::new();
    let mut excluded_usdt_trades: HashSet<String> = HashSet::new();
    for usdt_trade in usdt_trades {
        // filtering banned usdt trades
        if banned_usdt_trades_set.contains(&usdt_trade.symbol) {
            excluded_usdt_trades.insert(usdt_trade.symbol.clone());
        } else {
            // build update values
            if let Some(value) = exchange_info_vec.get(&usdt_trade.symbol) {
                let avg_price: Decimal =
                    rust_decimal::prelude::FromPrimitive::from_f64(usdt_trade.weightedavgprice)
                        .unwrap();
                let step_size = value.stepsize;
                let step_price = decimal_mul(step_size, avg_price);
                let unit_trade_usdt =
                    crate::coex::assets_managing_team::get_unit_trade_usdt().await;

                // exclude USDT trades whose step_price is over than 1% of unit_trade_usdt
                if step_price > decimal_mul(unit_trade_usdt, dec!(0.01)) {
                    excluded_usdt_trades.insert(usdt_trade.symbol.clone());
                } else {
                    filtered_usdt_trades.insert(usdt_trade.symbol.clone());
                }
            }
        }
    }

    // update valid usdt trades
    let table_name = String::from("valid_usdt_trades");
    let columns = vec!["symbol"];
    let mut value_wrapper: Vec<Vec<String>> = Vec::new();
    for symbol in &filtered_usdt_trades {
        let mut inner_vec: Vec<String> = Vec::new();
        inner_vec.push(symbol.clone());
        value_wrapper.push(inner_vec);
    }
    *valid_usdt_trade_vec = filtered_usdt_trades;
    delete_all_rows(&table_name).await?;
    insert_records(&table_name, &columns, &value_wrapper).await?;

    // update stop usdt trades
    let table_name = String::from("stop_usdt_trades");
    let columns = vec!["symbol"];
    let mut value_wrapper: Vec<Vec<String>> = Vec::new();
    for symbol in excluded_usdt_trades {
        let mut inner_vec: Vec<String> = Vec::new();
        inner_vec.push(symbol.clone());
        value_wrapper.push(inner_vec);
    }
    delete_all_rows(&table_name).await?;
    insert_records(&table_name, &columns, &value_wrapper).await?;

    Ok(())
}

pub async fn total_24h_change_profit_index() -> Result<(), Box<dyn std::error::Error + Send + Sync>>
{
    #[derive(Debug, FromRow)]
    struct TradesProfit {
        pricechangepercent: f64,
    }
    let mut trades_profit = TradesProfit {
        pricechangepercent: 0.0,
    };
    let table_name = String::from("all_24h_change");
    let column_name = String::from("priceChangePercent");
    let condition = Some(String::from("WHERE symbol NOT LIKE '%DOWNUSDT' AND symbol NOT LIKE '%UPUSDT' AND firstId >= 0 AND lastId >= 0"));

    let select_result =
        try_select_record(&table_name, &column_name, &condition, &trades_profit).await?;

    let mut nwa = 0.0; // Normalized Weighted Average
    let mut price_down_count = 0;
    let mut pdr = 0.0; // Price Down Ratio
    let mut index = 0.0;

    for element in &select_result {
        if element.pricechangepercent > 10.0 {
            nwa += 0.0;
        } else if element.pricechangepercent <= 10.0 && element.pricechangepercent > 7.0 {
            nwa += 1.0;
        } else if element.pricechangepercent <= 7.0 && element.pricechangepercent > 5.0 {
            nwa += 2.0;
        } else if element.pricechangepercent <= 5.0 && element.pricechangepercent > 3.0 {
            nwa += 3.0;
        } else if element.pricechangepercent <= 3.0 && element.pricechangepercent > 0.00005 {
            nwa += 4.0;
        } else if element.pricechangepercent <= 0.00005 && element.pricechangepercent > -0.00005 {
            nwa += 5.0;
        } else if element.pricechangepercent <= -0.00005 && element.pricechangepercent > -3.0 {
            nwa += 6.0;
            price_down_count += 1;
        } else if element.pricechangepercent <= -3.0 && element.pricechangepercent > -5.0 {
            nwa += 7.0;
            price_down_count += 1;
        } else if element.pricechangepercent <= -5.0 && element.pricechangepercent > -7.0 {
            nwa += 8.0;
            price_down_count += 1;
        } else if element.pricechangepercent <= -7.0 && element.pricechangepercent >= -10.0 {
            nwa += 9.0;
            price_down_count += 1;
        } else {
            nwa += 10.0;
            price_down_count += 1;
        }
    }

    nwa /= select_result.len() as f64;
    pdr = price_down_count as f64 / select_result.len() as f64;
    index = ((nwa * 100.0) + (pdr * 4000.0)) / 50.0;

    let update_table_name = String::from("indices");
    let server_epoch = server_epoch().await;
    let update_values = vec![
        (
            String::from("total_24h_change_profit_index"),
            index.to_string(),
        ),
        (String::from("server_epoch"), server_epoch.to_string()),
    ];
    let update_condition = vec![(String::from("id"), String::from("1"))];

    update_record2(&update_table_name, &update_values, &update_condition).await?;
    // println!("total_24h_change_profit_index 완료");
    Ok(())
}

pub async fn usdt_24h_change_profit_index() -> Result<(), Box<dyn std::error::Error + Send + Sync>>
{
    #[derive(Debug, FromRow)]
    struct TradesProfit {
        pricechangepercent: f64,
    }
    let mut trades_profit = TradesProfit {
        pricechangepercent: 0.0,
    };
    let table_name = String::from("all_24h_change");
    let column_name = String::from("priceChangePercent");
    let condition = Some(String::from("WHERE symbol LIKE '%USDT' AND symbol NOT LIKE '%DOWNUSDT' AND symbol NOT LIKE '%UPUSDT' AND firstId >= 0 AND lastId >= 0"));

    let select_result =
        try_select_record(&table_name, &column_name, &condition, &trades_profit).await?;

    let mut nwa = 0.0; // Normalized Weighted Average
    let mut price_down_count = 0;
    let mut pdr = 0.0; // Price Down Ratio
    let mut index = 0.0;

    for element in &select_result {
        if element.pricechangepercent > 10.0 {
            nwa += 0.0;
        } else if element.pricechangepercent <= 10.0 && element.pricechangepercent > 7.0 {
            nwa += 1.0;
        } else if element.pricechangepercent <= 7.0 && element.pricechangepercent > 5.0 {
            nwa += 2.0;
        } else if element.pricechangepercent <= 5.0 && element.pricechangepercent > 3.0 {
            nwa += 3.0;
        } else if element.pricechangepercent <= 3.0 && element.pricechangepercent > 0.00005 {
            nwa += 4.0;
        } else if element.pricechangepercent <= 0.00005 && element.pricechangepercent > -0.00005 {
            nwa += 5.0;
        } else if element.pricechangepercent <= -0.00005 && element.pricechangepercent > -3.0 {
            nwa += 6.0;
            price_down_count += 1;
        } else if element.pricechangepercent <= -3.0 && element.pricechangepercent > -5.0 {
            nwa += 7.0;
            price_down_count += 1;
        } else if element.pricechangepercent <= -5.0 && element.pricechangepercent > -7.0 {
            nwa += 8.0;
            price_down_count += 1;
        } else if element.pricechangepercent <= -7.0 && element.pricechangepercent >= -10.0 {
            nwa += 9.0;
            price_down_count += 1;
        } else {
            nwa += 10.0;
            price_down_count += 1;
        }
    }

    nwa /= select_result.len() as f64;
    pdr = price_down_count as f64 / select_result.len() as f64;
    index = ((nwa * 100.0) + (pdr * 4000.0)) / 50.0;

    let update_table_name = String::from("indices");
    let server_epoch = server_epoch().await;
    let update_values = vec![
        (
            String::from("usdt_24h_change_profit_index"),
            index.to_string(),
        ),
        (String::from("server_epoch"), server_epoch.to_string()),
    ];
    let update_condition = vec![(String::from("id"), String::from("1"))];

    update_record2(&update_table_name, &update_values, &update_condition).await?;
    // println!("usdt_24h_change_profit_index 완료");
    Ok(())
}

pub async fn total_price_down_dist_index() -> Result<(), Box<dyn std::error::Error + Send + Sync>> {
    #[derive(Debug, FromRow)]
    struct TradesProfit {
        pricechangepercent: f64,
    }
    let mut trades_profit = TradesProfit {
        pricechangepercent: 0.0,
    };
    let table_name = String::from("all_24h_change");
    let column_name = String::from("priceChangePercent");
    let condition = Some(String::from("WHERE symbol NOT LIKE '%DOWNUSDT' AND symbol NOT LIKE '%UPUSDT' AND firstId >= 0 AND lastId >= 0"));

    let select_result =
        try_select_record(&table_name, &column_name, &condition, &trades_profit).await?;

    let mut wa = 0.0; // Weighted Average
    let mut total_coin_amount = 0;
    let mut index = 0.0;

    for element in &select_result {
        if element.pricechangepercent <= 0.00005 && element.pricechangepercent > -0.00005 {
            wa += 0.0;
            total_coin_amount += 1;
        } else if element.pricechangepercent <= -0.00005 && element.pricechangepercent > -3.0 {
            wa += 2.0;
            total_coin_amount += 1;
        } else if element.pricechangepercent <= -3.0 && element.pricechangepercent > -5.0 {
            wa += 4.0;
            total_coin_amount += 1;
        } else if element.pricechangepercent <= -5.0 && element.pricechangepercent > -7.0 {
            wa += 6.0;
            total_coin_amount += 1;
        } else if element.pricechangepercent <= -7.0 && element.pricechangepercent > -10.0 {
            wa += 8.0;
            total_coin_amount += 1;
        } else if element.pricechangepercent <= -10.0 {
            wa += 10.0;
            total_coin_amount += 1;
        }
    }

    wa /= total_coin_amount as f64;

    index = wa * 10.0;

    let update_table_name = String::from("indices");
    let server_epoch = server_epoch().await;
    let update_values = vec![
        (
            String::from("total_price_down_dist_index"),
            index.to_string(),
        ),
        (String::from("server_epoch"), server_epoch.to_string()),
    ];
    let update_condition = vec![(String::from("id"), String::from("1"))];

    update_record2(&update_table_name, &update_values, &update_condition).await?;

    // println!("total_price_down_dist_index 완료");
    Ok(())
}

pub async fn monitoring_all_coin_profit_change() {
    // 초기화 확인
    let select_table_name = String::from("all_coin_profit_change_avg");
    let select_columns = String::from("*");
    let select_condition = None;
    let all_coin_profit_change_avg_stuct = AllCoinProfitChangeAvgList {
        server_epoch: 0,
        cnt: 0,
        avg_profit: 0.0,
    };
    let all_coin_profit_change_avg_list = select_record(
        &select_table_name,
        &select_columns,
        &select_condition,
        &all_coin_profit_change_avg_stuct,
    )
    .await
    .unwrap();
    let cnt = all_coin_profit_change_avg_list.first().unwrap().cnt;
    let mut avg_profit = all_coin_profit_change_avg_list.first().unwrap().avg_profit;
    let count_row = count_rows(&String::from("all_coin_start_price"))
        .await
        .unwrap();

    let server_epoch = server_epoch().await;
    let timer: u64 = 20_000; // 20_000: 20secs

    // check timer on
    if server_epoch
        - all_coin_profit_change_avg_list
            .first()
            .unwrap()
            .server_epoch
        > timer
    {
        // initialize [all_coin_start_price] table
        delete_all_rows(&String::from("all_coin_start_price"))
            .await
            .expect("Failed to delete rows!");
        initial_all_coin_start_price().await;

        // initialize all values in [all_coin_profit_change_avg]
        let update_table_name = String::from("all_coin_profit_change_avg");
        let update_values = vec![
            (String::from("server_epoch"), server_epoch.to_string()),
            (String::from("cnt"), 1.to_string()),
            (String::from("avg_profit"), 0.0.to_string()),
        ];
        let update_condition = vec![(String::from("id"), String::from("1"))];
        update_record2(&update_table_name, &update_values, &update_condition)
            .await
            .unwrap();

        // delete all rows when the number of rows is 200
        let history_rows_num = count_rows(&String::from("all_coin_profit_change_history"))
            .await
            .unwrap();
        if history_rows_num == 200 {
            delete_all_rows(&String::from("all_coin_profit_change_history"))
                .await
                .expect("Failed to delete rows!");
        }

        // insert record into [all_coin_profit_change_history]
        if all_coin_profit_change_avg_list
            .first()
            .unwrap()
            .server_epoch
            != 0
        {
            let insert_table_name = String::from("all_coin_profit_change_history");
            let insert_columns = vec!["server_epoch", "avg_profit"];
            let insert_values = vec![
                server_epoch.to_string(),
                all_coin_profit_change_avg_list
                    .first()
                    .unwrap()
                    .avg_profit
                    .to_string(),
            ];
            insert_one_record(&insert_table_name, &insert_columns, &insert_values).await;
        }
    } else {
        #[derive(FromRow)]
        struct Avg {
            avg: f64,
        }
        let select_data_structure = Avg { avg: 0.0 };
        let select_table_name = String::from("(SELECT sum(total) AS result FROM (SELECT (start_price-price)/start_price AS total FROM all_coin_start_price JOIN coinprices ON all_coin_start_price.symbol = coinprices.symbol) total)");
        let mut select_column = String::from("result/");
        select_column.push_str(count_row.to_string().as_str());
        select_column.push_str(" AS avg");
        let select_condition = Some(String::from("result"));

        let new_avg = select_record(
            &select_table_name,
            &select_column,
            &select_condition,
            &select_data_structure,
        )
        .await
        .unwrap()
        .first()
        .unwrap()
        .avg;

        avg_profit = ((cnt as f64 - 1.0) / (cnt as f64)) * avg_profit + (new_avg / (cnt as f64));

        let update_table_name = String::from("all_coin_profit_change_avg");
        let update_values = vec![
            (String::from("cnt"), (cnt + 1).to_string()),
            (String::from("avg_profit"), avg_profit.to_string()),
        ];
        let update_condition = vec![(String::from("id"), String::from("1"))];
        update_record2(&update_table_name, &update_values, &update_condition)
            .await
            .unwrap();
    }

    // println!("monitoring all_coin_profit_change 완료");
}

async fn server_epoch() -> u64 {
    #[derive(Debug, FromRow)]
    struct ServerEpoch {
        server_epoch: u64,
    }

    let table_name = String::from("time");
    let columns = String::from("*");
    let condition = None;
    let mut time_info = ServerEpoch { server_epoch: 0 };

    let select_result = select_record(&table_name, &columns, &condition, &time_info)
        .await
        .unwrap();

    select_result.first().unwrap().server_epoch
}

pub async fn initial_all_coin_start_price() {
    let table_name = String::from("all_coin_start_price");
    #[derive(Debug, FromRow)]
    struct SymbolPrice {
        symbol: String,
        price: Decimal,
    }
    let fetch_table_name = String::from("valid_usdt_trades");
    let select_columns = String::from("valid_usdt_trades.symbol, coinprices.price");
    let condition = Some(String::from(
        "JOIN coinprices ON valid_usdt_trades.symbol = coinprices.symbol",
    ));
    let data_struct = SymbolPrice {
        symbol: String::new(),
        price: Decimal::new(0, 8),
    };
    let symbol_price_vec =
        select_record(&fetch_table_name, &select_columns, &condition, &data_struct)
            .await
            .expect("Failed to fetch records!");

    let insert_columns = vec!["symbol", "start_price"];
    let mut insert_values: Vec<Vec<String>> = Vec::new();
    let mut insert_inner_values: Vec<String> = Vec::new();
    let mut value_string_build = String::new();
    for element in symbol_price_vec {
        value_string_build.clear();
        value_string_build.push_str(element.symbol.as_str());
        value_string_build.push_str("\', \'");
        value_string_build.push_str(element.price.to_string().as_str());
        insert_inner_values.push(value_string_build.clone());
        insert_values.push(insert_inner_values.clone());
        insert_inner_values.clear();
    }
    insert_records(&table_name, &insert_columns, &insert_values).await;
}
