import datetime

import openpyxl
import pytz
from django.db.models import Count
from django.utils import timezone

from accounts.models import ConsoleWorkTime, Console, FifaAccountRequest, FifaAccount
from sbc.models import SBCWorker


# consoles_work_time
def update_consoles_work_time():
    for iie in ConsoleWorkTime.objects.all().order_by('console__name', 'start_time').select_related('console'):
        start_time = (datetime.datetime.combine(datetime.date.today(), iie.start_time) + datetime.timedelta(minutes=5)).time().strftime('%H:%M:%S')
        end_time = (datetime.datetime.combine(datetime.date.today(), iie.end_time) + datetime.timedelta(minutes=5)).time().strftime('%H:%M:%S')
        print(iie.console.name, ',', start_time, ',',end_time)


def update_console_end_time():
    for item in ConsoleWorkTime.objects.all():
        print(item.console)
        item.end_time = datetime.datetime.combine(datetime.date.today(), item.start_time) + timezone.timedelta(hours=4)
        item.save(update_fields=['end_time'])


def create_console_work_time():
    aa = [
        #  [console_name , work start hour ( start from 0 to 7 , not more )]
        [119, 0],
        [116, 1],
        [117, 2],
    ]
    saved_consoles_work_time = list(ConsoleWorkTime.objects.all().values_list('console__name', flat=True))
    for iie in aa:
        console_first_name = iie[0]
        start_hour = iie[1]
        console_list = Console.objects.filter(name__in=[console_first_name])
        for console in console_list:
            print(console, ' - ', start_hour)
            if console.name in saved_consoles_work_time:
                print(f'console {console} has already work time , so continue')
                continue
            for ii in [start_hour, start_hour + 8, start_hour + 16]:
                start_time = datetime.datetime.now().replace(hour=ii, minute=0, second=0,
                                                             microsecond=0) - datetime.timedelta(minutes=5)
                ConsoleWorkTime.objects.create(
                    console=console,
                    start_time=start_time.time(),
                    end_time=(start_time + datetime.timedelta(minutes=180)).time()
                )
#

# fifa account requests
def get_list_of_account_requests(accounts_str, last_hours=12):
    report_file_name = 'fifa_requests_tmp.xlsx'
    accounts_name_list = accounts_str # example  '''deloris_cook2@outlook.com \n deloris_cook2@outlook.com'''
    from openpyxl.workbook import Workbook
    wb = Workbook()
    default_sheet = wb.active
    wb.remove(default_sheet)  # حذف شیت پیش‌فرض
    for fifa_account_user_name in accounts_name_list.split('\n'):
        print('searching for account : ', fifa_account_user_name)
        if not fifa_account_user_name:
            continue
        fifa_account = FifaAccount.objects.get(user_name=fifa_account_user_name)
        a = FifaAccountRequest.objects.using('logs').filter(
            fifa_account_id=fifa_account.id,
            create_time__gt=timezone.localtime() - timezone.timedelta(hours=last_hours)
        ).values('link').annotate(count=Count('link')).order_by('-count')
        sheet_name = fifa_account_user_name[:31].replace('/', '_')  # max length = 31
        ws = wb.create_sheet(title=sheet_name)

        ws.append(["Link", "Count"])

        total_count = 0
        for item in a:
            ws.append([item['link'], item['count']])
            total_count += item['count']

        ws.append(["TOTAL", total_count])
        wb.save(report_file_name)
    print(f'save file : {report_file_name}')
#


# sbc workers work time
def get_list_of_sbc_workers_times(console_name_list=[]):
    console_names = console_name_list
    wb = openpyxl.Workbook()
    wb.remove(wb.active)
    for console_name in console_names:
        print(console_name)
        workers = SBCWorker.objects.filter(
            running_platform='console_web_pc',
            fifa_account__console__name=console_name, start_time__gt=timezone.localtime() - timezone.timedelta(hours=48)
        ).order_by('last_run_time').select_related('fifa_account')
        sheet_name = str(console_name)
        ws = wb.create_sheet(title=sheet_name)
        ws.append(['Create Time', 'Last Run Time', 'Username', 'Start Time', 'End Time'])
        for ii in workers:
            create_time = ii.create_time.astimezone(pytz.timezone("Asia/Tehran")).strftime('%Y/%m/%d %H:%M')
            start_time = ii.start_time.astimezone(pytz.timezone("Asia/Tehran")).strftime('%Y/%m/%d %H:%M')
            last_run_time = ii.last_run_time.astimezone(pytz.timezone("Asia/Tehran")).strftime('%Y/%m/%d %H:%M')
            end_time = ii.end_time.astimezone(pytz.timezone("Asia/Tehran")).strftime('%Y/%m/%d %H:%M')
            user_name = ii.fifa_account.user_name
            platform = ii.fifa_account.platform
            ws.append([create_time, last_run_time, user_name, start_time, end_time, platform])
    print('saving file : ', 'sbc_workers_times.xlsx')
    wb.save('sbc_workers_times.xlsx')