from typing import Dict

import threading
import json
from psycopg2 import pool

from db_config import db_config
from qwin_chat_bot_domain import GameType, GameCategory

CUSTOMER_SQL = ''' select c.* from fo_customer c 
where c.customer_pk = %s;
'''

def customer_fetcher(item: Dict):
    return {
        'id': item[0],
        'login': item[2],
        'email': item[4],
        'reg_confirm': item[7],
        'blocked': item[8],
        'valid': item[9],
        'withdraw_allowed': item[11],
        "c_date": item[12]
    }

CUSTOMER_ID_SQL = ''' select c.customer_pk from fo_customer c 
where c.login = %s;
'''

def customer_id_fetcher(item: Dict):
    return item[0]

CUSTOMER_ACTVE_BONUS_SQL = ''' select b.*,
(select sum(cg.staked) from casino_game cg 
where cg.is_closed = true and cg.tournament_id is null and 
cg.customer_acc_fk = b.customer_acc_fk and cg.staked < 100000 and cg.c_date > b.c_date)
from fo_customer_bonus b
inner join fo_customer_account ca on ca.customer_acc_pk = b.customer_acc_fk
where ca.customer_fk = %s and b.is_active = true
'''

def active_bonus_fetcher(item: Dict) -> Dict:
    return {
        'bonus_amount': '{:.2f}'.format(float(item[3]) / QWIN_B_RATE),
        'bonus_type': item[4],
        'wager_factor': item[5],
        'win_factor': item[6],
        'bonus_code': item[7],
        'bonus_confirmed': item[8],
        'bonus_active': item[9],
        'confirm_date': item[11],
        'player_staked':to_qwinb(item[12]),
        'to_stake': '{:.2f}'.format((float(item[3]) * item[5] - float(item[12])) / QWIN_B_RATE)
    }
    

LAST_GAME_SQL = '''select cg.*, cdg.*
from casino_game cg
inner join {} cdg on cdg.game_pk = cg.game_pk
inner join fo_customer_account ca on ca.customer_acc_pk = cg.customer_acc_fk
where ca.customer_fk = %s and cg.game_type_fk = %s
order by cg.c_date desc limit 1;
'''

GAME_SQL = '''select cg.*, cdg.*
from casino_game cg
inner join {} cdg on cdg.game_pk = cg.game_pk
inner join fo_customer_account ca on ca.customer_acc_pk = cg.customer_acc_fk
where ca.customer_fk = %s and cg.game_pk = %s limit 1;
'''

def slot_fetcher(item: Dict) -> Dict:
    return {
            'id': item[0],
            'game_type_id': item[1],
            'user_id': item[2],
            'currency_id': item[5],
            'start_balance': to_qwinb(item[6]),
            'end_balance': to_qwinb(item[7]),
            'staked': to_qwinb(item[8]),
            'returned': to_qwinb(item[9]),
            'finished': item[10],
            'closed': item[11],
            'c_date': item[13],
            'f_date': item[14],
            'free_spins': item[16],
            'lines': item[18],
            'coins_per_line': item[19],
            'coin_value': to_qwinb(item[8] / (item[18] * item[19])),
            'states': json.loads(item[20])['states'],
            'config_id': item[21]
    }

QWIN_B_RATE = 10000.0

def to_qwinb(value):
    return '{:.2f}'.format(float(value) / QWIN_B_RATE)
    

fetchers = {
    GameCategory.BLACKJACK: None,
    GameCategory.SLOT: slot_fetcher
}

class QwinCasinoDao:
    
    _instance = None
    _lock = threading.Lock()
    
    def __new__(cls, *args, **kwargs):
        with cls._lock:
            if not cls._instance:
                cls._instance = super(QwinCasinoDao, cls).__new__(cls)
                
        return cls._instance
    
    def __getstate__(self):
        state = self.__dict__.copy()
        state.pop('_lock', None)
        return state
    
    def __setstate__(self, state):
        self.__dict__.update(state)
        self._lock = threading.Lock()
    
    def __init__(self):
        self.config = db_config()
        connection_params = self.config['postgresql']
        
        self.pool = pool.SimpleConnectionPool(int(self.config['pool']['min']), 
                                              int(self.config['pool']['max']), 
                                              **connection_params)
        
    def get_customer(self, user_id: str):
        connection = self.pool.getconn()
        cursor = connection.cursor()
        cursor.execute(CUSTOMER_SQL, (int(user_id), ))
        result = cursor.fetchall()
        
        cursor.close()
        self.pool.putconn(connection)
        
        if result:
            return customer_fetcher(result[0])
        
        return None
    
    def get_customer_id(self, user_login: str):
        connection = self.pool.getconn()
        cursor = connection.cursor()
        cursor.execute(CUSTOMER_ID_SQL, (user_login, ))
        result = cursor.fetchall()
        
        cursor.close()
        self.pool.putconn(connection)
        
        if result:
            return customer_id_fetcher(result[0])
        
        return None
    
    def get_customer_active_bonus(self, user_id):
        connection = self.pool.getconn()
        cursor = connection.cursor()
        cursor.execute(CUSTOMER_ACTVE_BONUS_SQL, (int(user_id), ))
        result = cursor.fetchall()
        
        cursor.close()
        self.pool.putconn(connection)
        
        if result:
            return active_bonus_fetcher(result[0])
        
        return None
    
    def get_last_game(self, user_id: str, game_id: str, game_type: GameType) -> Dict:
        game_category = game_type.category
        
        sql_query = LAST_GAME_SQL.format(game_category.table)
        
        connection = self.pool.getconn()
        cursor = connection.cursor()
        cursor.execute(sql_query, (int(user_id), game_type.id))
        result = cursor.fetchall()
        
        cursor.close()
        self.pool.putconn(connection)
        
        print(result)
        
        fetcher = fetchers[game_category]
        return [fetcher(item) for item in result]
    
    def get_game(self, user_id: str, game_type: GameType, game_id: int) -> Dict:
        game_category = game_type.category
        
        sql_query = GAME_SQL.format(game_category.table)
        
        connection = self.pool.getconn()
        cursor = connection.cursor()
        cursor.execute(sql_query, (int(user_id), game_id))
        result = cursor.fetchall()
        
        cursor.close()
        self.pool.putconn(connection)
        
        print(result)
        
        fetcher = fetchers[game_category]
        return [fetcher(item) for item in result]