import math
import psycopg
import os
from datetime import date
from datetime import timedelta
from dotenv import load_dotenv
from pprint import pprint
from tabulate import tabulate
import model


def get_table(new_member=1337627, name=''):
    print(f'new_member: {new_member}')
    text = f'!ph {new_member}'
    print(text)
    load_dotenv(verbose=True)
    use_data = False
    conn_dangercrow = psycopg.connect(
        user=os.getenv('db_dangercrow_user'),
        password=os.getenv('db_dangercrow_password'),
        host=os.getenv('db_host'),
        port=os.getenv('db_port'),
        dbname=os.getenv('db_dangercrow_database'))
    cur_dangercrow = conn_dangercrow.cursor()
    query_dangercrow_now = f"""SELECT 
            xanax.timestamp, factions.username, xanax.refills, xanax.activity, networth.networth, xanax.xantaken
        FROM xanax 
        LEFT JOIN factions
        ON xanax.id = factions.member_id
        LEFT JOIN networth
        ON xanax.id = networth.member
        WHERE xanax.id = {new_member}
        ORDER BY timestamp DESC LIMIT 1;"""
    timedelta_seven = date.today() - timedelta(days=7)
    query_dangercrow_seven = f"""SELECT
            xanax.timestamp, factions.username, xanax.refills, xanax.activity, networth.networth, xanax.xantaken
        FROM xanax 
        LEFT JOIN factions
        ON xanax.id = factions.member_id
        LEFT JOIN networth
        ON xanax.id = networth.member
        WHERE xanax.id = {new_member} 
        ORDER BY abs( xanax.timestamp - 
                      date '{timedelta_seven.strftime('%Y-%m-%d')}')
        LIMIT 1;"""
    timedelta_thirtythree = date.today() - timedelta(days=33)
    query_dangercrow_thirtythree = f"""SELECT 
            xanax.timestamp, factions.username, xanax.refills, xanax.activity, networth.networth, xanax.xantaken
        FROM xanax 
        LEFT JOIN factions
        ON xanax.id = factions.member_id
        LEFT JOIN networth
        ON xanax.id = networth.member
        WHERE xanax.id = {new_member}
        ORDER BY abs( xanax.timestamp - 
                      date '{timedelta_thirtythree.strftime('%Y-%m-%d')}')
        LIMIT 1;"""
    result_now = model.database_select(cur_dangercrow, query_dangercrow_now, debug=True)
    if not result_now:
        use_data = True
        print('No data from dangercrow database, trying the prospects database.')
        conn_data = psycopg.connect(
            user=os.getenv('db_data_user'),
            password=os.getenv('db_data_password'),
            host=os.getenv('db_host'),
            dbname=os.getenv('db_data_database'))
        cur_data = conn_data.cursor()
        query_now = f'''SELECT today, data->>'name' as name, data->>'refills' as refills, data->>'networth' as networth, data->>'xantaken' as xantaken
            FROM alldata WHERE member = {new_member}
            ORDER BY today LIMIT 1;'''
        result_now = model.database_select(cur_data, query_now, debug=True)
        if not result_now:
            return 'No data available for this player'
        query_data_seven = f'''SELECT today, data->>'name' as name, data->>'refills' as refills, data->>'networth' as networth, data->>'xantaken' as xantaken
            FROM alldata WHERE member = {new_member}
            ORDER BY abs(today - date '{timedelta_seven.strftime('%Y-%m-%d')}')
            LIMIT 1;'''
        query_data_thirtythree = f'''SELECT today, data->>'name' as name, data->>'refills' as refills, data->>'networth' as networth, data->>'xantaken' as xantaken
            FROM alldata WHERE member = {new_member}
            ORDER BY abs(today - date '{timedelta_thirtythree.strftime('%Y-%m-%d')}')
            LIMIT 1;'''
    pprint(result_now)
    if use_data:
        result_seven = model.database_select(cur_data, query_data_seven, debug=True)
        result_thirtythree = model.database_select(cur_data, query_data_thirtythree, debug=True)
    else:
        result_seven = model.database_select(cur_dangercrow, query_dangercrow_seven, debug=True)
        result_thirtythree = model.database_select(cur_dangercrow, query_dangercrow_thirtythree)
    pprint(result_seven)
    delta_seven = date.today() - result_seven[0][0]
    print(f'delta_seven: {delta_seven}')
    pprint(result_thirtythree)
    delta_thirtythree = date.today() - result_thirtythree[0][0]
    print(f'delta_thirtythree: {delta_thirtythree}')

    millnames = ['', ' K', ' M', ' B', ' T']

    def millify(n):
        n = float(n)
        millidx = max(0,min(len(millnames)-1,
                            int(math.floor(0 if n == 0 else math.log10(abs(n))/3))))

        return '{:.0f}{}'.format(n / 10**(3 * millidx), millnames[millidx])
    if result_now[0][1] is None:
        if name != '':
            result_now[0][1] = name
    table = [
        ['', 'Latest', f'Last {delta_seven.days} days', f'Last {delta_thirtythree.days} days'],
        ['date', result_now[0][0].strftime('%Y-%m-%d'), result_seven[0][0].strftime('%Y-%m-%d'), result_thirtythree[0][0].strftime('%Y-%m-%d')],
        ['name', f"{result_now[0][1]} [{new_member}]", '', ''],
        ['refills', result_now[0][2], result_now[0][2] - result_seven[0][2], result_now[0][2] - result_thirtythree[0][2]],
        ['useractivity', millify(result_now[0][3]), millify(result_now[0][3] - result_seven[0][3]), millify(result_now[0][3] - result_thirtythree[0][3])],
        ['networth', millify(result_now[0][4]), millify(result_now[0][4] - result_seven[0][4]), millify((result_now[0][4] - result_thirtythree[0][4]))],
        ['xantaken', result_now[0][5], result_now[0][5] - result_seven[0][5], result_now[0][5] - result_thirtythree[0][5]]
    ]
    return '```\n'+tabulate(table)+'\n```'


if __name__ == '__main__':
    tests = [1927067, 1337627, 2094410, 1200106, 2135411, 2703120]
    for test in tests:
        print(get_table(test))