use crate::coex::assets_managing_team::*;
use crate::coex::order_team::*;
use crate::coin_health_check_team::request_others::{ExchangeInfo, TradeFee};
use crate::database_control::*;
use crate::decimal_funcs::{decimal, decimal_add, decimal_div, decimal_mul, decimal_sub};
use crate::signal_association::signal_decision::*;
use crate::strategy_team::AllData;
use hmac_sha256::Hash;
use reqwest::{Client, ClientBuilder};
use rust_decimal::{prelude::FromPrimitive, prelude::ToPrimitive, Decimal, RoundingStrategy};
use rust_decimal_macros::dec;
use sqlx::FromRow;
use std::collections::HashMap;

#[derive(Debug, FromRow)]
struct IsTradableInfo {
    is_tradable: u8,
}

#[derive(Debug, FromRow)]
struct PreSuggestedCoin {
    id: u64,
    symbol: String,
    close_time: i64,
    suggested_price: Decimal,
    current_price: Decimal,
    stoploss: Decimal,
    target_price: Decimal,
    registered_server_epoch: i64,
    profit_percent: f64,
    minimum_profit_percent: f64,
    maximum_profit_percent: f64,
    registerer: u16,
    is_long: u8,
}

impl DBlist for PreSuggestedCoin {
    fn new() -> PreSuggestedCoin {
        let a = PreSuggestedCoin {
            id: 0,
            symbol: String::new(),
            close_time: 0,
            suggested_price: Decimal::new(0, 8),
            current_price: Decimal::new(0, 8),
            stoploss: Decimal::new(0, 8),
            target_price: Decimal::new(0, 8),
            registered_server_epoch: 0,
            profit_percent: 0.0,
            minimum_profit_percent: 0.0,
            maximum_profit_percent: 0.0,
            registerer: 0,
            is_long: 0,
        };
        a
    }
}

#[derive(Debug, FromRow)]
pub struct SuggestedCoin {
    pub id: u64,
    pub symbol: String,
    pub suggested_price: Decimal,
    pub stoploss: Decimal,
    pub target_price: Decimal,
    pub close_time: i64,
    pub registered_server_epoch: i64,
    pub registerer: u16,
    pub is_long: u8,
    pub already_buy: u8,
}

impl DBlist for SuggestedCoin {
    fn new() -> SuggestedCoin {
        let a = SuggestedCoin {
            id: 0,
            symbol: String::new(),
            suggested_price: Decimal::new(0, 8),
            stoploss: Decimal::new(0, 8),
            target_price: Decimal::new(0, 8),
            close_time: 0,
            registered_server_epoch: 0,
            registerer: 0,
            is_long: 0,
            already_buy: 0,
        };
        a
    }
}

#[derive(Debug, FromRow)]
pub struct ServerEpoch {
    server_epoch: u64,
}

#[derive(Debug, FromRow)]
struct UnitTradeUsdt {
    unit_trade_usdt: Decimal,
}

#[derive(Debug, FromRow)]
struct CurrentPrice {
    price: Decimal,
}

#[derive(Debug, FromRow, Clone)]
pub struct ScoreboardList {
    pub id: u64,
    pub total_number_of_coin: u32,
    pub pos_profit_number: u32,
    pub neg_profit_number: u32,
    pub total_used_usdt: f64,
    pub pos_used_usdt: f64,
    pub neg_used_usdt: f64,
    pub total_pos_profit_usdt: f64,
    pub total_neg_profit_usdt: f64,
    pub maximum_total_pos_profit_usdt: f64,
    pub avg_neg_profit_percent: f64,
    pub pos_liquidation_signal: u32,
    pub neg_liquidation_signal: u32,
}

impl DBlist for ScoreboardList {
    fn new() -> ScoreboardList {
        let a = ScoreboardList {
            id: 0,
            total_number_of_coin: 0,
            pos_profit_number: 0,
            neg_profit_number: 0,
            total_used_usdt: 0.0,
            pos_used_usdt: 0.0,
            neg_used_usdt: 0.0,
            total_pos_profit_usdt: 0.0,
            total_neg_profit_usdt: 0.0,
            maximum_total_pos_profit_usdt: 0.0,
            avg_neg_profit_percent: 0.0,
            pos_liquidation_signal: 0,
            neg_liquidation_signal: 0,
        };
        a
    }
}

#[derive(Debug, FromRow)]
pub struct EvaluationList {
    id: u64,
    strategist: u16,
    invested_usdt: Decimal,
    usdt_profit: Decimal,
    profit_percent: f64,
}

#[derive(Debug, FromRow)]
pub struct IndexList {
    pub server_epoch: u64,
    pub total_24h_change_profit_index: f64,
    pub usdt_24h_change_profit_index: f64,
    pub total_price_down_dist_index: f64,
}

impl DBlist for IndexList {
    fn new() -> IndexList {
        let a = IndexList {
            server_epoch: 0,
            total_24h_change_profit_index: 0.0,
            usdt_24h_change_profit_index: 0.0,
            total_price_down_dist_index: 0.0,
        };
        a
    }
}

#[derive(Debug, FromRow)]
pub struct FilteredIndexList {
    pub server_epoch: u64,
    pub total_24h_change_profit_index: f64,
    pub usdt_24h_change_profit_index: f64,
    pub total_price_down_dist_index: f64,
    pub total_price_down_dist_index_maximum: f64,
    pub total_price_down_dist_index_flag: i32,
}

impl DBlist for FilteredIndexList {
    fn new() -> FilteredIndexList {
        let a = FilteredIndexList {
            server_epoch: 0,
            total_24h_change_profit_index: 0.0,
            usdt_24h_change_profit_index: 0.0,
            total_price_down_dist_index: 0.0,
            total_price_down_dist_index_maximum: 0.0,
            total_price_down_dist_index_flag: 0,
        };
        a
    }
}

#[derive(Debug, FromRow, Clone)]
pub struct MarketCapIndex {
    pub market_cap_index: f64,
    pub minimum: f64,
    pub maximum: f64,
    pub transition_point: f64,
    pub liquidation_signal: i8,
    pub negative_buy_signal: i8,
    pub transition_buy_signal: i8,
}

impl DBlist for MarketCapIndex {
    fn new() -> MarketCapIndex {
        let a = MarketCapIndex {
            market_cap_index: 0.0,
            minimum: 0.0,
            maximum: 0.0,
            transition_point: 0.0,
            liquidation_signal: 0,
            negative_buy_signal: 0,
            transition_buy_signal: 0,
        };
        a
    }
}

// buy coin
pub async fn buy_coin(
    exchange_info_map: &HashMap<String, ExchangeInfo>,
    trade_fee_map: &HashMap<String, TradeFee>,
) -> Result<(), Box<dyn std::error::Error + Send + Sync>> {
    let mut suggested_coin = get_suggested_coin_list().await;

    if !suggested_coin.is_empty() && exchange_info_map.len() != 0 && trade_fee_map.len() != 0 {
        let server_epoch = get_server_epoch().await;
        let mut filtered_suggested_coin_vec: Vec<&SuggestedCoin> = Vec::new();
        let mut is_exist_delete_symbol: bool = false;
        let mut delete_condition = String::from("WHERE ");

        // filtering symbols to buy
        for element in &suggested_coin {
            if element.already_buy == 0 && server_epoch - element.registered_server_epoch <= 300_000
            // 300_000 (300 secs = 5 mins)
            {
                filtered_suggested_coin_vec.push(element);
            } else if server_epoch - element.registered_server_epoch >= 5_400_000 {
                // 30mins * 3
                delete_condition.push_str("id = ");
                delete_condition.push_str(element.id.to_string().as_str());
                delete_condition.push_str(" OR ");
                is_exist_delete_symbol = true;
            }
        }
        set_is_tradable().await;
        let mut is_tradable = get_is_tradable().await;

        if is_tradable == true && !filtered_suggested_coin_vec.is_empty() {
            let unit_trade_usdt = get_unit_trade_usdt().await;
            let client = ClientBuilder::new()
                .timeout(tokio::time::Duration::from_millis(3000))
                .build()
                .unwrap();
            for element in &filtered_suggested_coin_vec {
                if is_tradable == true {
                    if exchange_info_map.contains_key(&element.symbol)
                        && trade_fee_map.contains_key(&element.symbol)
                    {
                        let exchange_info = exchange_info_map.get(&element.symbol).unwrap();
                        let lot_step_size = exchange_info.stepsize;
                        let tick_size = exchange_info.ticksize;
                        let base_commission_precision = exchange_info.base_commission_precision;
                        let trade_fee = trade_fee_map.get(&element.symbol).unwrap().takercommission;

                        // buy the suggested coin and transfer it into [buy_ordered_coin_list]
                        let mut base_qty_ordered = Decimal::new(0, 8);
                        base_qty_ordered = decimal_div(unit_trade_usdt, element.suggested_price)
                            .round_dp_with_strategy(
                                lot_step_size.normalize().scale(),
                                RoundingStrategy::ToZero,
                            );

                        let mut base_qty_fee_adjusted = Decimal::new(0, 8);
                        base_qty_fee_adjusted =
                            decimal_mul(base_qty_ordered, decimal_sub(dec!(1), trade_fee))
                                .round_dp_with_strategy(
                                    base_commission_precision,
                                    RoundingStrategy::ToZero,
                                );

                        let mut used_usdt = Decimal::new(0, 8);
                        used_usdt = decimal_mul(base_qty_ordered, element.suggested_price)
                            .round_dp_with_strategy(
                                tick_size.normalize().scale(),
                                RoundingStrategy::ToZero,
                            );
                        let expected_pure_profit_percent: f64 = decimal_sub(
                            decimal_div(
                                decimal_mul(base_qty_fee_adjusted, element.suggested_price)
                                    .round_dp_with_strategy(
                                        tick_size.normalize().scale(),
                                        RoundingStrategy::ToZero,
                                    ),
                                used_usdt,
                            ),
                            dec!(1),
                        )
                        .to_f64()
                        .unwrap()
                            * 100.0;

                        // order the symbol based on base_qty_ordered and current_price
                        limit_order_buy(
                            element,
                            exchange_info_map,
                            trade_fee,
                            TimeInForce::Gtc,
                            element.suggested_price,
                            base_qty_ordered,
                            used_usdt,
                            &base_qty_fee_adjusted.to_string(),
                            &client,
                        )
                        .await;

                        set_is_tradable().await;
                        is_tradable = get_is_tradable().await;

                        let update_table_name = String::from("suggested_coin_list");
                        let update_condition = vec![(String::from("id"), element.id.to_string())];
                        let update_values: Vec<(String, String)> =
                            vec![(String::from("already_buy"), 1.to_string())];
                        update_record2(&update_table_name, &update_values, &update_condition).await;
                    }
                } else {
                    break;
                }
            }
        }
        // delete the coin record in [suggested_coin_list]
        if is_exist_delete_symbol == true {
            delete_condition.pop();
            delete_condition.pop();
            delete_condition.pop();
            delete_condition.pop();
            delete_condition.push(';');
            let suggested_coin_list_table_name = String::from("suggested_coin_list");
            delete_record(&suggested_coin_list_table_name, &delete_condition)
                .await
                .unwrap();
        }
    }

    Ok(())
}

// buy coin for test
pub async fn buy_coin_for_test(
    client: &Client,
    exchange_info_map: &HashMap<String, ExchangeInfo>,
    trade_fee_map: &HashMap<String, TradeFee>,
) -> Result<(), Box<dyn std::error::Error + Send + Sync>> {
    let mut suggested_coin = get_suggested_coin_list().await;
    let mut delete_condition = String::from("WHERE ");

    let unit_trade_usdt = get_unit_trade_usdt().await;

    let server_epoch = get_server_epoch().await;
    let mut filtered_suggested_coin_vec: Vec<&SuggestedCoin> = Vec::new();

    for element in &suggested_coin {
        filtered_suggested_coin_vec.push(element);
    }

    let available_usdt = get_available_usdt().await;
    let vec_len = Decimal::new(filtered_suggested_coin_vec.len() as i64, 0);
    let weighted_unit_usdt =
        (((available_usdt / vec_len).trunc() / unit_trade_usdt).trunc() * unit_trade_usdt).trunc();
    let mut used_usdt = Decimal::new(0, 8);
    let mut base_qty_ordered = Decimal::new(0, 8);
    let mut base_qty_fee_adjusted = Decimal::new(0, 8);
    let mut expected_pure_profit_percent = 0.0;

    let insert_table_name = String::from("buy_ordered_coin_list");
    for element in &filtered_suggested_coin_vec {
        if exchange_info_map.contains_key(&element.symbol)
            && trade_fee_map.contains_key(&element.symbol)
        {
            let lot_step_size = exchange_info_map.get(&element.symbol).unwrap().stepsize;
            let tick_size = exchange_info_map.get(&element.symbol).unwrap().ticksize;
            let base_commission_precision = exchange_info_map
                .get(&element.symbol)
                .unwrap()
                .base_commission_precision;
            let trade_fee = trade_fee_map.get(&element.symbol).unwrap().takercommission;

            // buy the suggested coin and transfer it into [buy_ordered_coin_list]
            // let price_index = coin_price_vec.iter().position(|x| *x.symbol == element.symbol);
            // let current_price: Decimal = rust_decimal::prelude::FromPrimitive::from_f64(coin_price_vec[price_index.unwrap()].current_price).unwrap();
            // let order_price = current_price;
            let order_price = element.suggested_price;

            base_qty_ordered = decimal_div(unit_trade_usdt, order_price).round_dp_with_strategy(
                lot_step_size.normalize().scale(),
                RoundingStrategy::ToZero,
            );
            base_qty_fee_adjusted = decimal_mul(base_qty_ordered, decimal_sub(dec!(1), trade_fee))
                .round_dp_with_strategy(base_commission_precision, RoundingStrategy::ToZero);
            used_usdt = decimal_mul(base_qty_ordered, order_price)
                .round_dp_with_strategy(tick_size.normalize().scale(), RoundingStrategy::ToZero);
            expected_pure_profit_percent = decimal_sub(
                decimal_div(
                    decimal_mul(base_qty_fee_adjusted, order_price).round_dp_with_strategy(
                        tick_size.normalize().scale(),
                        RoundingStrategy::ToZero,
                    ),
                    used_usdt,
                ),
                dec!(1),
            )
            .to_f64()
            .unwrap()
                * 100.0;

            // order the symbol based on base_qty_ordered and current_price
            limit_order_buy(
                element,
                exchange_info_map,
                trade_fee,
                TimeInForce::Gtc,
                order_price,
                base_qty_ordered,
                used_usdt,
                &base_qty_fee_adjusted.to_string(),
                &client,
            )
            .await;
            println!("                  buy coin 완료");
        }
    }

    Ok(())
}
// monitoring the price change of pre-suggested coins before suggesting them and move them when conditions are agree
pub async fn monitoring_pre_suggested_coins(
    coin_price_map: &HashMap<String, f64>,
) -> Result<(), Box<dyn std::error::Error + Send + Sync>> {
    let select_table_name = String::from("pre_suggested_coin_list");
    let pre_suggested_coin_list = select_pre_suggested_coin_list().await;

    if !pre_suggested_coin_list.is_empty() {
        // update current_price, profit_percent, minimum_profit_percent, maximum_profit_percent
        let mut price = Decimal::new(0, 8);
        let mut profit_percent = 0.0;
        let mut minimum_profit_percent = 0.0;
        let mut maximum_profit_percent = 0.0;

        let mut update_elements: Vec<(String, String)> = Vec::new();
        let mut update_condition: Vec<(String, String)> = Vec::new();

        for element in pre_suggested_coin_list {
            if let Some(current_price) = coin_price_map.get(&element.symbol) {
                price = rust_decimal::prelude::FromPrimitive::from_f64(*current_price).unwrap();
                profit_percent = ((price - element.suggested_price) / element.suggested_price)
                    .to_f64()
                    .unwrap()
                    * 100.0;
                maximum_profit_percent = element.maximum_profit_percent;
                minimum_profit_percent = element.minimum_profit_percent;

                if profit_percent > 0.0 {
                    minimum_profit_percent = 0.0;
                    if profit_percent > maximum_profit_percent {
                        maximum_profit_percent = profit_percent;
                    }
                } else if profit_percent < 0.0 {
                    maximum_profit_percent = 0.0;
                    if profit_percent < minimum_profit_percent {
                        minimum_profit_percent = profit_percent;
                    }
                } else {
                    minimum_profit_percent = 0.0;
                    maximum_profit_percent = 0.0;
                }

                update_elements.clear();
                update_condition.clear();

                update_elements.push((String::from("current_price"), price.to_string()));
                update_elements.push((String::from("profit_percent"), profit_percent.to_string()));
                update_elements.push((
                    String::from("minimum_profit_percent"),
                    minimum_profit_percent.to_string(),
                ));
                update_elements.push((
                    String::from("maximum_profit_percent"),
                    maximum_profit_percent.to_string(),
                ));

                update_condition.push((String::from("id"), element.id.to_string()));

                // update for each symbol listed in [pre_suggested_coin_list]
                update_record3(&select_table_name, &update_elements, &update_condition).await;
            }
        }

        // move coins into [suggested_coin_list] if conditions are agree
        let pre_suggested_coin_list = select_pre_suggested_coin_list().await;
        let insert_table_name = String::from("suggested_coin_list");
        let mut insert_columns = vec![
            "symbol",
            "suggested_price",
            "stoploss",
            "target_price",
            "close_time",
            "registered_server_epoch",
            "registerer",
            "is_long",
            "already_buy",
        ];

        let mut insert_values: Vec<String> = Vec::new();
        let mut delete_condition = String::new();

        for element in pre_suggested_coin_list {
            // insertion and delete
            if (element.minimum_profit_percent > -0.01
                && element.minimum_profit_percent < 0.0
                && element.profit_percent >= element.minimum_profit_percent * 0.5)
                || (element.minimum_profit_percent > -0.02
                    && element.minimum_profit_percent <= -0.01
                    && element.profit_percent >= element.minimum_profit_percent * 0.7)
                || (element.minimum_profit_percent > -0.03
                    && element.minimum_profit_percent <= -0.02
                    && element.profit_percent >= element.minimum_profit_percent * 0.83)
                || (element.minimum_profit_percent > -0.04
                    && element.minimum_profit_percent <= -0.03
                    && element.profit_percent >= element.minimum_profit_percent * 0.95)
                || (element.minimum_profit_percent <= -0.04
                    && element.profit_percent >= element.minimum_profit_percent * 0.98)
                || (element.maximum_profit_percent >= 0.0)
            {
                insert_values.clear();
                insert_values.push(element.symbol.clone()); // symbol
                insert_values.push(element.current_price.to_string()); // suggested_price
                insert_values.push(element.stoploss.to_string()); // stoploss
                insert_values.push(element.target_price.to_string()); // target_price
                insert_values.push(element.close_time.to_string()); // close_time
                insert_values.push(get_server_epoch().await.to_string()); // registered_server_epoch
                insert_values.push(element.registerer.to_string()); // registerer
                insert_values.push(element.is_long.to_string()); // is_long
                insert_values.push(0.to_string()); // already_buy

                insert_one_record(&insert_table_name, &insert_columns, &insert_values).await;
                delete_condition.clear();
                delete_condition = String::from("WHERE id=");
                delete_condition.push_str(element.id.to_string().as_str());
                delete_record(&select_table_name, &delete_condition).await;
            }
        }
    }

    Ok(())
}

pub async fn monitoring_scoreboard(
    all_data: &AllData,
) -> Result<(), Box<dyn std::error::Error + Send + Sync>> {
    let update_table_name = String::from("scoreboard");
    let mut update_values: Vec<(String, String)> = Vec::new(); // for short
    let mut update_values2: Vec<(String, String)> = Vec::new(); // for long
    let mut update_condition: Vec<(String, String)> = Vec::new();
    let mut update_condition2: Vec<(String, String)> = Vec::new();

    let filled_buy_orders = select_filled_buy_orders(0).await?;

    if filled_buy_orders.is_empty() {
        // initialization of table
        update_values.push((String::from("total_number_of_coin"), 0.to_string()));
        update_values.push((String::from("pos_profit_number"), 0.to_string()));
        update_values.push((String::from("neg_profit_number"), 0.to_string()));
        update_values.push((String::from("total_used_usdt"), 0.0.to_string()));
        update_values.push((String::from("pos_used_usdt"), 0.0.to_string()));
        update_values.push((String::from("neg_used_usdt"), 0.0.to_string()));
        update_values.push((String::from("total_pos_profit_usdt"), 0.0.to_string()));
        update_values.push((String::from("total_neg_profit_usdt"), 0.0.to_string()));
        update_values.push((
            String::from("maximum_total_pos_profit_usdt"),
            0.0.to_string(),
        ));
        update_values.push((String::from("avg_neg_profit_percent"), 0.0.to_string()));
        update_values.push((String::from("pos_liquidation_signal"), 0.to_string()));
        update_values.push((String::from("neg_liquidation_signal"), 0.to_string()));

        update_values2.push((String::from("total_number_of_coin"), 0.to_string()));
        update_values2.push((String::from("pos_profit_number"), 0.to_string()));
        update_values2.push((String::from("neg_profit_number"), 0.to_string()));
        update_values2.push((String::from("total_used_usdt"), 0.0.to_string()));
        update_values2.push((String::from("pos_used_usdt"), 0.0.to_string()));
        update_values2.push((String::from("neg_used_usdt"), 0.0.to_string()));
        update_values2.push((String::from("total_pos_profit_usdt"), 0.0.to_string()));
        update_values2.push((String::from("total_neg_profit_usdt"), 0.0.to_string()));
        update_values2.push((
            String::from("maximum_total_pos_profit_usdt"),
            0.0.to_string(),
        ));
        update_values2.push((String::from("avg_neg_profit_percent"), 0.0.to_string()));
        update_values2.push((String::from("pos_liquidation_signal"), 0.to_string()));
        update_values2.push((String::from("neg_liquidation_signal"), 0.to_string()));
    } else {
        let scoreboard_list = select_scoreboard().await;
        let mut count_short_coins = 0;
        let mut count_long_coins = 0;
        let server_epoch = get_server_epoch().await;
        let mut timer_check = false;

        for element in &filled_buy_orders {
            if element.used_usdt >= dec!(10.0) {
                if element.is_long == 0 {
                    count_short_coins += 1;
                } else {
                    count_long_coins += 1;
                }
            }
        }
        // variables for id 1 (for short)
        let mut pos_profit_number1 = 0;
        let mut neg_profit_number1 = 0;
        let mut total_used_usdt1 = 0.0;
        let mut pos_used_usdt1 = 0.0;
        let mut neg_used_usdt1 = 0.0;
        let mut total_pos_profit_usdt1 = 0.0;
        let mut total_neg_profit_usdt1 = 0.0;
        let mut sum_neg_profit_percent1 = 0.0;
        let mut avg_neg_profit_percent1 = 0.0;
        let mut sum_pos_profit_percent1 = 0.0;
        // variables for id 2 (for long)
        // let mut pos_profit_number2 = 0;
        // let mut neg_profit_number2 = 0;
        // let mut total_used_usdt2 = 0.0;
        // let mut pos_used_usdt2 = 0.0;
        // let mut neg_used_usdt2 = 0.0;
        // let mut current_total_pos_profit_usdt2 = 0.0;
        // let mut current_total_neg_profit_usdt2 = 0.0;
        // let mut sum_neg_profit_percent2 = 0.0;
        // let mut avg_neg_profit_percent2 = 0.0;

        for element in filled_buy_orders {
            if element.used_usdt >= dec!(10.0) {
                if element.is_long == 0 {
                    if element.pure_profit_percent > 0.0 {
                        pos_profit_number1 += 1;
                        total_pos_profit_usdt1 += element.expected_usdt_profit;
                        pos_used_usdt1 += element.used_usdt.to_f64().unwrap();
                        sum_pos_profit_percent1 += element.pure_profit_percent;
                    } else {
                        neg_profit_number1 += 1;
                        total_neg_profit_usdt1 += element.expected_usdt_profit;
                        neg_used_usdt1 += element.used_usdt.to_f64().unwrap();
                        sum_neg_profit_percent1 += element.pure_profit_percent;
                    }
                    total_used_usdt1 += element.used_usdt.to_f64().unwrap();
                }

                if server_epoch - element.transact_time > 180_000 {
                    timer_check = true;
                }
            }

            // else {
            //     if element.pure_profit_percent > 0.0 {
            //         pos_profit_number2 += 1;
            //         current_total_pos_profit_usdt2 = element.expected_usdt_profit + current_total_pos_profit_usdt2;
            //         pos_used_usdt2 = pos_used_usdt2 + element.used_usdt.to_f64().unwrap();
            //     } else {
            //         neg_profit_number2 += 1;
            //         current_total_neg_profit_usdt2 = element.expected_usdt_profit + current_total_neg_profit_usdt2;
            //         neg_used_usdt2 = neg_used_usdt2 + element.used_usdt.to_f64().unwrap();
            //         sum_neg_profit_percent2 = element.pure_profit_percent + sum_neg_profit_percent2;
            //     }
            //     total_used_usdt2 += element.used_usdt.to_f64().unwrap();
            // }
        }

        // set update values for id 1 (for short)
        update_values.push((
            String::from("total_number_of_coin"),
            count_short_coins.to_string(),
        ));
        update_values.push((
            String::from("pos_profit_number"),
            pos_profit_number1.to_string(),
        ));
        update_values.push((
            String::from("neg_profit_number"),
            neg_profit_number1.to_string(),
        ));
        update_values.push((
            String::from("total_used_usdt"),
            total_used_usdt1.to_string(),
        ));
        update_values.push((String::from("pos_used_usdt"), pos_used_usdt1.to_string()));
        update_values.push((String::from("neg_used_usdt"), neg_used_usdt1.to_string()));
        update_values.push((
            String::from("total_pos_profit_usdt"),
            total_pos_profit_usdt1.to_string(),
        ));
        update_values.push((
            String::from("total_neg_profit_usdt"),
            total_neg_profit_usdt1.to_string(),
        ));

        if total_pos_profit_usdt1
            > scoreboard_list
                .first()
                .unwrap()
                .maximum_total_pos_profit_usdt
        {
            update_values.push((
                String::from("maximum_total_pos_profit_usdt"),
                total_pos_profit_usdt1.to_string(),
            ));
        } else if pos_profit_number1 == 0 {
            update_values.push((
                String::from("maximum_total_pos_profit_usdt"),
                0.0.to_string(),
            ));
        }

        if neg_profit_number1 != 0 {
            avg_neg_profit_percent1 = sum_neg_profit_percent1 / (neg_profit_number1 as f64);
            update_values.push((
                String::from("avg_neg_profit_percent"),
                avg_neg_profit_percent1.to_string(),
            ));
        } else {
            update_values.push((String::from("avg_neg_profit_percent"), 0.0.to_string()));
        }

        // calculate average amplitude of BTCUSDT to set the limit of liquidations
        let mut base_profit_percent = 0.0;
        let mut target_profit_percent = 0.0;

        let symbol = String::from("BTCUSDT");
        if let Some(rt_price_1m_vec) = all_data.rt_price_1m_vec.get(&symbol) {
            let mut opclo_1m_vec = rt_price_1m_vec.clone();
            let opclo_sample_length: usize = 5; // 10 candle samsples
            opclo_1m_vec.pop();
            opclo_1m_vec.reverse();
            opclo_1m_vec.truncate(opclo_sample_length);
            opclo_1m_vec.reverse();

            let mut sum_amplitude_candles = 0.0;
            let mut sum_ratio_amp_body = 0.0;
            for element in &opclo_1m_vec {
                sum_amplitude_candles += ((element.high_price / element.low_price) - 1.0) * 100.0;
                sum_ratio_amp_body += (element.close_price - element.open_price).abs()
                    / (element.high_price - element.low_price);
            }
            let average_amplitude = sum_amplitude_candles / opclo_sample_length as f64; // percent unit
            let average_ratio_amp_body = sum_ratio_amp_body / opclo_sample_length as f64;

            let mut amplitude_variance = 0.0;
            for element in &opclo_1m_vec {
                amplitude_variance += ((((element.high_price / element.low_price) - 1.0) * 100.0)
                    - average_amplitude)
                    .powi(2);
            }
            amplitude_variance = amplitude_variance / (opclo_sample_length - 1) as f64;
            let standard_deviation_amplitude = amplitude_variance.sqrt();

            base_profit_percent = (average_amplitude * average_ratio_amp_body)
                + (standard_deviation_amplitude * (1.0 - average_ratio_amp_body));

            if pos_profit_number1 != 0 {
                target_profit_percent = (pos_profit_number1 as f64 * base_profit_percent)
                    + ((base_profit_percent / 2.0)
                        * ((pos_profit_number1 as f64).powi(2) - pos_profit_number1 as f64));
            }

            // if (count_short_coins != 0) && (pos_profit_number1 >= 2) &&
            // ((sum_pos_profit_percent1 >= target_profit_percent && pos_profit_number1 >= neg_profit_number1)
            // || (sum_pos_profit_percent1 >= target_profit_percent * 0.8 && neg_profit_number1 != 0)) {
            //     println!("pos1 liq. target: {:.3}, sum_pos: {:.3}, pos_num: {}, neg_num: {}", target_profit_percent, sum_pos_profit_percent1, pos_profit_number1, neg_profit_number1);
            //     // update_values.push((String::from("pos_liquidation_signal"), 1.to_string()));
            // } else if ((neg_profit_number1 >= 1 && total_pos_profit_usdt1 > 0.0) && (sum_pos_profit_percent1 / (neg_profit_number1 + 1) as f64 <= sum_neg_profit_percent1.abs() )) {
            //     println!("pos2 liq. target: {:.3}, sum_pos: {:.3}, pos_num: {}, neg_num: {}", target_profit_percent, sum_pos_profit_percent1, pos_profit_number1, neg_profit_number1);
            //     // update_values.push((String::from("pos_liquidation_signal"), 1.to_string()));
            // } else if ((neg_profit_number1 >= 1) && (avg_neg_profit_percent1 <= base_profit_percent * -1.0 / (neg_profit_number1 as f64)))
            // // || (timer_check == true && count_short_coins != 0 && sum_pos_profit_percent1 + sum_neg_profit_percent1 < base_profit_percent * -0.1)
            // {
            //     println!("neg liq. avg_neg:{:.3}, base:{:.3}, pos_num: {}, neg_num: {}", avg_neg_profit_percent1, base_profit_percent * -1.0 / (neg_profit_number1 as f64), pos_profit_number1, neg_profit_number1);
            //     update_values.push((String::from("neg_liquidation_signal"), 1.to_string()));
            // } else if scoreboard_list.first().unwrap().pos_liquidation_signal == 1 && count_short_coins == 0 {
            //     update_values.push((String::from("pos_liquidation_signal"), 0.to_string()));
            // } else if scoreboard_list.first().unwrap().neg_liquidation_signal == 1 && count_short_coins == 0 {
            //     update_values.push((String::from("neg_liquidation_signal"), 0.to_string()));
            // }
        }

        // // set update values for id 2 (for long)
        // update_values2.push((String::from("total_number_of_coin"), count_long_coins.to_string()));
        // update_values2.push((String::from("current_pos_profit_number"), pos_profit_number2.to_string()));
        // update_values2.push((String::from("current_neg_profit_number"), neg_profit_number2.to_string()));

        // if scoreboard_list.last().unwrap().current_pos_profit_number != scoreboard_list.last().unwrap().previous_pos_profit_number {
        //     update_values2.push((String::from("previous_pos_profit_number"), pos_profit_number2.to_string().to_string()));
        //     update_values2.push((String::from("maximum_total_pos_profit_usdt"), current_total_pos_profit_usdt2.to_string()));
        // }
        // if scoreboard_list.last().unwrap().current_neg_profit_number != scoreboard_list.last().unwrap().previous_neg_profit_number {
        //     update_values2.push((String::from("previous_neg_profit_number"), neg_profit_number2.to_string().to_string()));
        // }

        // update_values2.push((String::from("total_used_usdt"), total_used_usdt2.to_string()));
        // update_values2.push((String::from("pos_used_usdt"), pos_used_usdt2.to_string()));
        // update_values2.push((String::from("neg_used_usdt"), neg_used_usdt2.to_string()));
        // update_values2.push((String::from("current_total_pos_profit_usdt"), current_total_pos_profit_usdt2.to_string()));
        // update_values2.push((String::from("current_total_neg_profit_usdt"), current_total_neg_profit_usdt2.to_string()));

        // if current_total_pos_profit_usdt2 > scoreboard_list.last().unwrap().maximum_total_pos_profit_usdt {
        //     update_values2.push((String::from("maximum_total_pos_profit_usdt"), current_total_pos_profit_usdt2.to_string()));
        // }

        // if neg_profit_number2 != 0 {
        //     avg_neg_profit_percent2 = sum_neg_profit_percent2 / (neg_profit_number2 as f64);
        // }
        // update_values2.push((String::from("avg_neg_profit_percent"), avg_neg_profit_percent2.to_string()));

        // if (pos_used_usdt2 >= neg_used_usdt2) &&
        //     (current_total_pos_profit_usdt2 <= scoreboard_list.last().unwrap().maximum_total_pos_profit_usdt * 0.85) &&
        //     (scoreboard_list.last().unwrap().maximum_total_pos_profit_usdt > pos_used_usdt2 * 0.005) &&
        //     (current_total_pos_profit_usdt2 > current_total_neg_profit_usdt2.abs() * 2.0) {
        //     update_values2.push((String::from("pos_liquidation_signal"), 1.to_string()));
        // } else if scoreboard_list.last().unwrap().pos_liquidation_signal == 1 && count_short_coins == 0 {
        //     update_values2.push((String::from("pos_liquidation_signal"), 0.to_string()));
        // } else if (pos_used_usdt2 < neg_used_usdt2) && (neg_profit_number2 > 1) && (scoreboard_list.last().unwrap().avg_neg_profit_percent <= -0.5) && (market_cap_list.negative_buy_signal == 0) {
        //     update_values2.push((String::from("neg_liquidation_signal"), 1.to_string()));
        // } else if scoreboard_list.last().unwrap().neg_liquidation_signal == 1 && count_short_coins == 0 {
        //     update_values2.push((String::from("neg_liquidation_signal"), 0.to_string()));
        // }
    }

    update_condition.push((String::from("id"), 1.to_string()));
    // update_condition2.push((String::from("id"), 2.to_string()));
    update_record3(&update_table_name, &update_values, &update_condition)
        .await
        .unwrap();
    // update_record3(&update_table_name, &update_values2, &update_condition2).await.unwrap();

    Ok(())
}

// update profit_percent in [achievement_evaluation]
pub async fn update_profit_percent() {
    let select_table_name = String::from("achievement_evaluation");
    let select_columns = String::from("*");
    let select_condition = None;
    let evaluation_list = EvaluationList {
        id: 0,
        strategist: 0,
        invested_usdt: Decimal::new(0, 8),
        usdt_profit: Decimal::new(0, 8),
        profit_percent: 0.0,
    };
    let strategists = select_record(
        &select_table_name,
        &select_columns,
        &select_condition,
        &evaluation_list,
    )
    .await
    .unwrap();

    for element in strategists {
        let mut profit_percent = 0.0;
        let mut update_value: Vec<(String, String)> = Vec::new();
        let mut update_condition: Vec<(String, String)> = Vec::new();

        if !element.invested_usdt.is_zero() {
            profit_percent = (decimal_sub(
                decimal_div(element.usdt_profit, element.invested_usdt),
                dec!(1),
            ))
            .round_dp(2)
            .to_f64()
            .unwrap()
                * 100.0;
            if profit_percent.is_nan() {
                profit_percent = 0.0;
            }
        }

        update_value.clear();
        update_value.push((String::from("profit_percent"), profit_percent.to_string()));
        update_condition.clear();
        update_condition.push((String::from("id"), element.id.to_string()));
        update_record3(&select_table_name, &update_value, &update_condition)
            .await
            .unwrap();
    }
}

// bring the list of pre-suggested coins from [pre_suggested_coin_list]
async fn select_pre_suggested_coin_list() -> Vec<PreSuggestedCoin> {
    let select_table_name = String::from("pre_suggested_coin_list");
    let select_columns = String::from("*");
    let select_condition = None;
    let select_data_structure = PreSuggestedCoin::new();
    let pre_suggested_coin_list = try_select_record(
        &select_table_name,
        &select_columns,
        &select_condition,
        &select_data_structure,
    )
    .await
    .unwrap(); // unwrap부분을 오류전파로 고치기
    pre_suggested_coin_list
}

// select a suggested coin having the lowest id in [suggested_coin_list]
async fn get_suggested_coin_list() -> Vec<SuggestedCoin> {
    let table_name = String::from("suggested_coin_list");
    let columns = String::from("*");
    let condition = None;
    let mut suggested_coin = SuggestedCoin::new();

    // let select_result = try_select_record(&table_name, &columns, &condition, &suggested_coin)
    let select_result = select_record(&table_name, &columns, &condition, &suggested_coin)
        .await
        .unwrap();

    select_result
}

pub async fn get_server_epoch() -> i64 {
    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 as i64
}

async fn select_buy_ordered_coins() -> Vec<BuyOrderedCoinList> {
    let table_name = String::from("buy_ordered_coin_list");
    let columns = String::from("*");
    let condition = None;
    let mut ordered_coin = BuyOrderedCoinList::new();

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

pub async fn select_indices() -> IndexList {
    let mut index_list = IndexList::new();
    let select_table_name = String::from("indices");
    let select_columns_name = String::from("*");
    let select_condition = None;
    let mut select_result = select_record(
        &select_table_name,
        &select_columns_name,
        &select_condition,
        &index_list,
    )
    .await
    .unwrap();
    let result_vec = select_result.first().unwrap();
    index_list.server_epoch = result_vec.server_epoch;
    index_list.total_24h_change_profit_index = result_vec.total_24h_change_profit_index;
    index_list.usdt_24h_change_profit_index = result_vec.usdt_24h_change_profit_index;
    index_list.total_price_down_dist_index = result_vec.total_price_down_dist_index;

    index_list
}

pub async fn select_filtered_indices() -> FilteredIndexList {
    let mut index_list = FilteredIndexList::new();
    let select_table_name = String::from("filtered_indices");
    let select_columns_name = String::from("*");
    let select_condition = None;
    let mut select_result = select_record(
        &select_table_name,
        &select_columns_name,
        &select_condition,
        &index_list,
    )
    .await
    .unwrap();
    let result_vec = select_result.first().unwrap();
    index_list.server_epoch = result_vec.server_epoch;
    index_list.total_24h_change_profit_index = result_vec.total_24h_change_profit_index;
    index_list.usdt_24h_change_profit_index = result_vec.usdt_24h_change_profit_index;
    index_list.total_price_down_dist_index = result_vec.total_price_down_dist_index;
    index_list.total_price_down_dist_index_maximum = result_vec.total_price_down_dist_index_maximum;
    index_list.total_price_down_dist_index_flag = result_vec.total_price_down_dist_index_flag;

    index_list
}

pub async fn select_marketcap() -> Vec<MarketCapIndex> {
    let mut market_cap_list = MarketCapIndex::new();
    let select_table_name = String::from("market_cap_index");
    let select_columns_name = String::from("*");
    let select_condition = None;
    let mut select_result = select_record(
        &select_table_name,
        &select_columns_name,
        &select_condition,
        &market_cap_list,
    )
    .await
    .unwrap();

    select_result
}

pub async fn select_scoreboard() -> Vec<ScoreboardList> {
    let select_table_name = String::from("scoreboard");
    let select_columns = String::from("*");
    let select_condition = None;
    let mut select_data_structure = ScoreboardList::new();
    let scoreboard_list = select_record(
        &select_table_name,
        &select_columns,
        &select_condition,
        &select_data_structure,
    )
    .await
    .unwrap();

    scoreboard_list
}
