import time
from itertools import chain

import openpyxl
from django.contrib import messages
from django.contrib.auth.decorators import login_required
from django.contrib.auth.mixins import LoginRequiredMixin
from django.db.models import Q, F, Avg, Sum, OuterRef, Subquery, Count, IntegerField, Value, Case, When, QuerySet
from django.db.models.functions import Coalesce
from django.http import HttpResponse
from django.shortcuts import render, redirect
# Create your views here.
from django.urls import reverse_lazy
from django.utils import timezone
from django.views import View
from django.views.generic import FormView
from django_filters.views import FilterView
from openpyxl import Workbook

from accounts.models import FifaAccount, FifaAccountBackupCode, FifaAccountSearch, CloseWebAppSnipes
from customrolepermissions.mixins import RequiredPermissionViewMixin
from customrolepermissions.permissions import has_permission_exactly
from futplus.celery_conf import app
from trade.consts import ConsoleTradeOneQualityName
from trade.dashboard_cards import ConsoleTradeQualityGold1ReportChartDashboardCard, \
    ConsoleTradeQualityGold0ReportChartDashboardCard, \
    ConsoleTradeQualitySilver1ReportChartDashboardCard, ConsoleTradeQualityBronze1ReportChartDashboardCard, \
    DailyInvestTradeGenerateChartDashboardCard
from trade.filters import ConsoleTradeQualityReportFilterSet, DailyInvestTradeGenerateReportFilterSet, \
    ConsoleInvestTradeFilterSet, ConsoleInvestChangeQualityFilterSet
from trade.forms import StartSinglePlayerTradeForm, ConsoleTradeOneSetQualityForm, ConsoleTradeOneChangeQualityForm, \
    WebTradeAddAccountForm, ConsoleInvestChangeQualityForm
from trade.models import TradeOrder, TradeWorker, TradeLog, SinglePlayerTradeOrder, SinglePlayerTradeWorker, \
    ConsoleTradeOneHistory, ConsoleTradeOneQuality, WebTradeInfo, WebTradeHistory, ConsoleTradeInvestDailyProfitLog
from trade.tables import TradeOrdersTable, SinglePlayerTradeOrdersTable, WebTradeAddAccountTable, \
    ConsoleInvestTradeTable, ConsoleInvestChangeQualityProTable
from utils import number_convertor_to_milion
from utils.dashboard_cards import DashboardLayout, DashboardRow
from utils.views import GenericListView, GenericFormView, GenericFilterListView


@login_required
def trade_add_file(request):
    if request.method == 'POST':
        sbc_file = request.FILES.get('file')
        trade_order = TradeOrder.objects.create(creator=request.user, file_name=sbc_file.name, status=1)
        wb_obj = openpyxl.load_workbook(sbc_file)
        sheet_obj = wb_obj.active
        max_row = sheet_obj.max_row
        for i in range(2, max_row + 1):
            ea_email = sheet_obj.cell(row=i, column=1).value
            ea_pass = sheet_obj.cell(row=i, column=2).value
            app_code = sheet_obj.cell(row=i, column=3).value
            try:
                backup_code = sheet_obj.cell(row=i, column=4).value or ''
            except:
                backup_code = ''
            try:
                running_platform = sheet_obj.cell(row=i, column=5).value or 'web'
            except:
                running_platform = 'web'

            if ea_email:
                fifa_account, created = FifaAccount.objects.get_or_create(
                    user_name=ea_email
                )
                if fifa_account.platform in ['', None]:
                    fifa_account.platform = 'ps'
                fifa_account.creator = request.user
                fifa_account.password = ea_pass
                fifa_account.save()

                account_backup, created = FifaAccountBackupCode.objects.get_or_create(fifa_account=fifa_account)
                account_backup.app_code = app_code
                account_backup.backup_code = backup_code
                account_backup.save()
                TradeWorker.objects.get_or_create(
                    trade_order=trade_order, fifa_account=fifa_account,
                    last_run_time=timezone.localtime() - timezone.timedelta(seconds=60 * 75),
                    running_platform=running_platform
                )

        messages.success(request, "Trade File Added")
        return render(request, 'sbc/add_sbc_file.html')
    else:
        return render(request, 'sbc/add_sbc_file.html',
                      {'page_header': 'Add Trade File',
                       'columns': ['Email', 'Password', 'App Code', 'Backup Code', 'Running Platform']}
                      )


class TradeOrdersListView(RequiredPermissionViewMixin, GenericListView):
    required_permission__ = 'trade_workers__read'
    model = TradeOrder
    table_class = TradeOrdersTable

    def is_rel(self):
        return has_permission_exactly(self.request.user, self.required_permission__rel)


@login_required
def trade_orders_excel(request, order_id):
    order = TradeOrder.objects.get(id=order_id)
    response = HttpResponse(content_type='application/vnd.ms-excel')
    response['Content-Disposition'] = f'attachment; filename="download_{int(time.time())}_{order.file_name}"'
    wb = Workbook()
    ws = wb.active
    worker_list = TradeWorker.objects.filter(trade_order__id=order_id).order_by('id')
    ws.append(
        ['user name', 'password', 'platform', 'app code', 'backup code', 'search number', 'credit',
         'active', 'has error', 'error description', 'start coin', 'last coin'])
    for item in worker_list:
        ws.append([item.fifa_account.user_name, item.fifa_account.password, item.fifa_account.platform,
                   item.fifa_account.account_backup_code.last().app_code,
                   item.fifa_account.account_backup_code.last().backup_code,
                   item.fifa_account.account_search.filter(
                       search_time__gt=timezone.localtime() - timezone.timedelta(hours=24)).count(),
                   item.fifa_account.credit, item.fifa_account.active, item.has_error, item.error_description,
                   item.start_coin, item.last_update_coin
                   ])
    wb.save(response)
    return response


class CancelOrder(LoginRequiredMixin, View):
    def get(self, request, *args, **kwargs):
        order = TradeOrder.objects.get(id=kwargs.get('order_id'))
        order.status = '4'
        order.save()
        # trade_workers = TradeWorker.objects.filter(trade_order=order, is_done=False, has_error=False)
        trade_workers = TradeWorker.objects.filter(trade_order=order)
        for trade_worker in trade_workers:
            if trade_worker.has_error == 0:
                trade_worker.has_error = 1
                trade_worker.error_description = 'manual deactivated'
                trade_worker.save()
            app.control.revoke(trade_worker.task_id, terminate=True, signal='SIGKILL')
            trade_worker.fifa_account.active = False
            trade_worker.fifa_account.login_status = 0
            trade_worker.fifa_account.driver = False
            trade_worker.fifa_account.save()

        messages.add_message(self.request, messages.SUCCESS, 'Successful stopped')
        return redirect(reverse_lazy('trade-orders-list'))


@login_required
def get_trades_excel(request, order_id):
    order = TradeOrder.objects.get(id=order_id)
    response = HttpResponse(content_type='application/vnd.ms-excel')
    response['Content-Disposition'] = f'attachment; filename="download_{int(time.time())}_{order.file_name}"'
    wb = Workbook()
    ws = wb.active
    worker_list = TradeWorker.objects.filter(trade_order__id=order_id).order_by('id')
    trades = TradeLog.objects.filter(trade_worker__in=worker_list).order_by('-id')[:5000]
    ws.append(
        ['user name', 'bid time', 'bid price', 'bid success', 'player asset id', 'sell time', 'sell price'])
    for item in trades:
        ws.append([item.trade_worker.fifa_account.user_name,
                   str(item.create_time), item.bid_price, item.bid_success,
                   item.player_card.asset_id, str(item.sell_time), item.sell_price
                   ])
    wb.save(response)
    return response


class SinglePlayerTradeOrdersListView(RequiredPermissionViewMixin, GenericListView):
    required_permission__ = 'trade_workers__read'
    model = SinglePlayerTradeOrder
    table_class = SinglePlayerTradeOrdersTable

    def is_rel(self):
        return has_permission_exactly(self.request.user, self.required_permission__rel)


@login_required
def single_player_trade_orders_excel(request, order_id):
    order = SinglePlayerTradeOrder.objects.get(id=order_id)
    response = HttpResponse(content_type='application/vnd.ms-excel')
    response['Content-Disposition'] = f'attachment; filename="download_{int(time.time())}_{order.file_name}"'
    wb = Workbook()
    ws = wb.active
    worker_list = SinglePlayerTradeWorker.objects.filter(trade_order__id=order_id).order_by('id')
    ws.append(
        ['player card', 'user name', 'password', 'platform', 'app code', 'backup code', 'search number', 'credit',
         'active', 'has error', 'error description', 'start coin', 'last coin'])
    for item in worker_list:
        if item.used_fifa_accounts.all():
            fifa_accounts = item.used_fifa_accounts.all()
        else:
            fifa_accounts = list(chain(item.unused_fifa_accounts.all(), item.used_fifa_accounts.all()))
        for fifa_account in fifa_accounts:
            ws.append([str(item.player_card), fifa_account.user_name, fifa_account.password, fifa_account.platform,
                       fifa_account.account_backup_code.last().app_code,
                       fifa_account.account_backup_code.last().backup_code,
                       fifa_account.account_search.filter(
                           search_time__gt=timezone.localtime() - timezone.timedelta(hours=24)).count(),
                       fifa_account.credit, fifa_account.active, item.has_error, item.error_description,
                       # item.start_coin, item.last_update_coin
                       ])
    wb.save(response)
    return response


class SinglePlayerCancelOrder(LoginRequiredMixin, View):
    def get(self, request, *args, **kwargs):
        order = SinglePlayerTradeOrder.objects.get(id=kwargs.get('order_id'))
        order.status = '4'
        order.save()
        # trade_workers = TradeWorker.objects.filter(trade_order=order, is_done=False, has_error=False)
        trade_workers = SinglePlayerTradeWorker.objects.filter(trade_order=order)
        for trade_worker in trade_workers:
            if trade_worker.has_error == 0:
                trade_worker.has_error = 1
                trade_worker.error_description = 'manual deactivated'
                trade_worker.save()
            app.control.revoke(trade_worker.task_id, terminate=True, signal='SIGKILL')
            fifa_accounts = list(chain(trade_worker.unused_fifa_accounts.all(), trade_worker.used_fifa_accounts.all()))
            for fifa_account in fifa_accounts:
                fifa_account.active = False
                fifa_account.login_status = 0
                fifa_account.driver = False
                fifa_account.save()

        messages.add_message(self.request, messages.SUCCESS, 'Successful stopped')
        return redirect(reverse_lazy('single-player-trade-orders-list'))


@login_required
def get_single_player_trades_excel(request, order_id):
    order = SinglePlayerTradeOrder.objects.get(id=order_id)
    response = HttpResponse(content_type='application/vnd.ms-excel')
    response['Content-Disposition'] = f'attachment; filename="download_{int(time.time())}_{order.file_name}"'
    wb = Workbook()
    ws = wb.active
    worker_list = SinglePlayerTradeWorker.objects.filter(trade_order__id=order_id).order_by('id')
    trades = TradeLog.objects.filter(single_player_trade_worker__in=worker_list).order_by('-id')[:5000]
    ws.append([
        'user name', 'bid time', 'bid price', 'bid success', 'player asset id', 'sell time', 'sell price',
        'trade_id', 'start_price', 'buy_now_price', 'expires', 'status',
    ])
    for item in trades:
        ws.append([item.fifa_account.user_name, str(item.create_time), item.bid_price, item.bid_success,
                   item.player_card.asset_id, str(item.sell_time), item.sell_price,
                   item.trade_id, item.start_price, item.buy_now_price, str(item.expires), item.status
                   ])
    wb.save(response)
    return response


class StartSinglePlayerTrade(LoginRequiredMixin, FormView):
    template_name = 'generic_views/form_view.html'
    form_class = StartSinglePlayerTradeForm
    success_url = reverse_lazy('single-player-trade-orders-list')

    def form_valid(self, form):
        fifa_accounts = form.cleaned_data['fifa_accounts']
        player_card = form.cleaned_data['player_card']
        max_bid_price = form.cleaned_data['max_bid_price']
        sell_start_price = form.cleaned_data['sell_start_price']
        start_sell_now_price = form.cleaned_data['start_sell_now_price']
        end_sell_now_price = form.cleaned_data['end_sell_now_price']
        trade_order = SinglePlayerTradeOrder.objects.create(
            creator=self.request.user,
            file_name='manual_%s.xlsx' % timezone.localtime().strftime('%Y-%m-%d_%H-%M-%S'),
            status=1
        )
        trade_worker = SinglePlayerTradeWorker.objects.create(
            trade_order=trade_order,
            player_card=player_card,
            max_bid_price=max_bid_price,
            sell_start_price=sell_start_price,
            start_sell_now_price=start_sell_now_price,
            end_sell_now_price=end_sell_now_price,
            last_run_time=timezone.localtime() - timezone.timedelta(seconds=60 * 75),
        )
        trade_worker.unused_fifa_accounts.set(fifa_accounts)
        return super().form_valid(form)


class ConsoleTradeOneSetQualityView(LoginRequiredMixin, RequiredPermissionViewMixin, FormView):
    required_permission__all = 'trade_workers__read__all'
    template_name = 'generic_views/form_view.html'
    form_class = ConsoleTradeOneSetQualityForm
    success_url = reverse_lazy('console-trade-one-set-quality')

    def form_valid(self, form):
        start_number = form.cleaned_data['start_number']
        end_number = form.cleaned_data['end_number']
        quality_name = form.cleaned_data['quality_name']
        stock_greater = form.cleaned_data.get('stock_greater', -1)
        stock_less = form.cleaned_data.get('stock_less', -1)
        selected_accounts = FifaAccount.objects.filter(console__name__in=list(range(start_number, end_number)))
        console_trade_histories = ConsoleTradeOneHistory.objects.filter(
            # Q(bought_items_managed=True) | Q(list_try_count__gte=1),
            fifa_account__id__in=selected_accounts.values_list('id', flat=True),
            # list_try_count__gt=1
        ).annotate(
            stock=Case(
                When(quality_name='bronze1', then=F('transfer_list_items_count') + F('club_bronze1_items')),
                When(quality_name='silver0', then=F('transfer_list_items_count') + F('club_silver0_items')),
                When(quality_name='silver1', then=F('transfer_list_items_count') + F('club_silver1_items')),
                When(quality_name='gold0', then=F('transfer_list_items_count') + F('club_gold0_items')),
                When(quality_name='gold1', then=F('transfer_list_items_count') + F('club_gold1_items')),
                When(quality_name='position_modifier',
                     then=F('transfer_list_items_count') + F('club_position_modifier_items')),
                default=0),
        )
        if stock_greater >= 0:
            accounts_2 = console_trade_histories.filter(
                id__in=console_trade_histories.order_by(
                    'fifa_account', '-create_time'
                ).distinct('fifa_account').values_list('id', flat=True)
            ).filter(
                stock__gt=int(stock_greater)
            ).values_list('fifa_account__id', flat=True)
            selected_accounts = selected_accounts.filter(id__in=accounts_2)
        if stock_less >= 0:
            accounts_3 = console_trade_histories.filter(
                id__in=console_trade_histories.order_by(
                    'fifa_account', '-create_time'
                ).distinct('fifa_account').values_list('id', flat=True)
            ).filter(
                stock__lt=int(stock_less)
            ).values_list('fifa_account__id', flat=True)
            selected_accounts = selected_accounts.filter(id__in=accounts_3)
        not_running_trade_accounts = selected_accounts.filter(
            Q(is_running_console_trade_one=False) | Q(console_trade_one_quality=quality_name)
        ).update(
            console_trade_one_quality=quality_name, run_console_trade_one=True, run_console_invest_trade_one=True)
        running_trade_accounts = selected_accounts.filter(
            is_running_console_trade_one=True
        ).exclude(
            console_trade_one_quality=quality_name
        ).update(
            console_trade_one_quality_new=quality_name
        )
        messages.add_message(self.request, messages.SUCCESS,
                             f'quality for {not_running_trade_accounts} updated and '
                             f' {running_trade_accounts} waiting to change new quality')

        return super().form_valid(form)


class ConsoleTradeOneChangeQualityView(RequiredPermissionViewMixin, GenericFormView):
    required_permission__all = 'trade_workers__read__all'
    template_name = 'generic_views/form_view.html'
    form_class = ConsoleTradeOneChangeQualityForm
    success_url = reverse_lazy('console-trade-one-change-quality')
    title = 'Trade Change Quality'

    def page_header(self):
        return 'Change Quality'

    def get_card_data(self):
        cards_data = []
        last_24_history = ConsoleTradeOneHistory.objects.filter(
            credit_after__gt=0, bid_count__gt=10,
            create_time__gt=timezone.localtime() - timezone.timedelta(hours=24)
        )
        last_24_history_count = last_24_history.count()
        last_24_history = last_24_history.annotate(
            profit=F('credit_after') - F('credit_before')).aggregate(
            sum_profit=Sum('profit')).get('sum_profit') or 0
        avg_last_24_history = ConsoleTradeOneHistory.objects.filter(
            credit_after__gt=0, bid_count__gte=150,
            create_time__gt=timezone.localtime() - timezone.timedelta(hours=24)
        ).annotate(
            profit=F('credit_after') - F('credit_before')).aggregate(
            avg_profit=Avg('profit')).get('avg_profit') or 0

        all_history = ConsoleTradeOneHistory.objects.filter(
            # quality_name=quality_name,
            credit_after__gt=0, bid_count__gt=10,
        )
        all_history_count = all_history.count()
        all_history = all_history.annotate(
            profit=F('credit_after') - F('credit_before')).aggregate(
            sum_profit=Sum('profit')).get('sum_profit') or 0
        avg_all_history = ConsoleTradeOneHistory.objects.filter(
            credit_after__gt=0, bid_count__gt=10,
        ).annotate(
            profit=F('credit_after') - F('credit_before')).aggregate(
            avg_profit=Avg('profit')).get('avg_profit') or 0

        cards_data.append({'name': f'All',
                           'info': f'{number_convertor_to_milion(all_history)}({round(avg_all_history)})-{all_history_count}',
                           'box_css_class': 'fw-bold'})
        cards_data.append({'name': f'All 24 H',
                           'info': f'{number_convertor_to_milion(last_24_history)}({round(avg_last_24_history)})-{last_24_history_count}',
                           'box_css_class': 'fw-bold'})
        for quality_name in ['gold1', 'gold0', 'silver1', 'bronze1']:
            last_24_history = ConsoleTradeOneHistory.objects.filter(
                quality_name=quality_name,
                credit_after__gt=0, bid_count__gt=10,
                create_time__gt=timezone.localtime() - timezone.timedelta(hours=24)
            )
            last_24_history_count = last_24_history.count()
            last_24_history = last_24_history.annotate(
                profit=F('credit_after') - F('credit_before')).aggregate(
                sum_profit=Sum('profit')).get('sum_profit') or 0
            avg_last_24_history = ConsoleTradeOneHistory.objects.filter(
                quality_name=quality_name,
                credit_after__gt=0, bid_count__gte=150,
                create_time__gt=timezone.localtime() - timezone.timedelta(hours=24)
            ).annotate(
                profit=F('credit_after') - F('credit_before')).aggregate(
                avg_profit=Avg('profit')).get('avg_profit') or 0

            all_history = ConsoleTradeOneHistory.objects.filter(
                quality_name=quality_name,
                credit_after__gt=0, bid_count__gt=10,
            )
            all_history_count = all_history.count()
            all_history = all_history.annotate(
                profit=F('credit_after') - F('credit_before')).aggregate(
                sum_profit=Sum('profit')).get('sum_profit') or 0
            avg_all_history = ConsoleTradeOneHistory.objects.filter(
                quality_name=quality_name,
                credit_after__gt=0, bid_count__gt=10,
            ).annotate(
                profit=F('credit_after') - F('credit_before')).aggregate(
                avg_profit=Avg('profit')).get('avg_profit') or 0

            cards_data.append({'name': f'{quality_name}',
                               'info': f'{number_convertor_to_milion(all_history)}({round(avg_all_history)})-{all_history_count}',
                               'box_css_class': 'fw-bold'})
            cards_data.append({'name': f'{quality_name} 24 H',
                               'info': f'{number_convertor_to_milion(last_24_history)}({round(avg_last_24_history)})-{last_24_history_count}',
                               'box_css_class': 'fw-bold'})
        return cards_data

    def form_valid(self, form):
        pre_quality = form.cleaned_data['pre_quality']
        new_quality = form.cleaned_data['new_quality']
        stock_greater = form.cleaned_data.get('stock_greater', -1)
        stock_less = form.cleaned_data.get('stock_less', -1)
        selected_accounts = FifaAccount.objects.filter(console_trade_one_quality=pre_quality)
        console_trade_histories = ConsoleTradeOneHistory.objects.filter(
            # Q(bought_items_managed=True) | Q(list_try_count__gte=1),
            fifa_account__id__in=selected_accounts.values_list('id', flat=True),
            # list_try_count__gt=1
        ).annotate(
            stock=Case(
                When(quality_name='bronze1', then=F('transfer_list_items_count') + F('club_bronze1_items')),
                When(quality_name='silver0', then=F('transfer_list_items_count') + F('club_silver0_items')),
                When(quality_name='silver1', then=F('transfer_list_items_count') + F('club_silver1_items')),
                When(quality_name='gold0', then=F('transfer_list_items_count') + F('club_gold0_items')),
                When(quality_name='gold1', then=F('transfer_list_items_count') + F('club_gold1_items')),
                When(quality_name='position_modifier',
                     then=F('transfer_list_items_count') + F('club_position_modifier_items')),
                default=0),
        )
        if stock_greater >= 0:
            accounts_2 = console_trade_histories.filter(
                id__in=console_trade_histories.order_by(
                    'fifa_account', '-create_time'
                ).distinct('fifa_account').values_list('id', flat=True)
            ).filter(
                stock__gt=int(stock_greater)
            ).values_list('fifa_account__id', flat=True)
            selected_accounts = selected_accounts.filter(id__in=accounts_2)
        if stock_less >= 0:
            accounts_3 = console_trade_histories.filter(
                id__in=console_trade_histories.order_by(
                    'fifa_account', '-create_time'
                ).distinct('fifa_account').values_list('id', flat=True)
            ).filter(
                stock__lt=int(stock_less)
            ).values_list('fifa_account__id', flat=True)
            selected_accounts = selected_accounts.filter(id__in=accounts_3)
        not_running_trade_accounts = selected_accounts.filter(
            Q(is_running_console_trade_one=False) | Q(console_trade_one_quality=new_quality)
        ).update(
            console_trade_one_quality=new_quality, run_console_trade_one=True, run_console_invest_trade_one=True)
        running_trade_accounts = selected_accounts.filter(
            is_running_console_trade_one=True
        ).exclude(
            console_trade_one_quality=new_quality
        ).update(
            console_trade_one_quality_new=new_quality
        )
        messages.add_message(self.request, messages.SUCCESS,
                             f'quality for {not_running_trade_accounts} updated and '
                             f' {running_trade_accounts} waiting to change new quality')

        return super().form_valid(form)


class ConsoleInvestChangeQualityProView(RequiredPermissionViewMixin, GenericFilterListView, FormView):
    required_permission__all = 'trade_workers__read__all'
    filterset_class = ConsoleInvestChangeQualityFilterSet
    form_class = ConsoleInvestChangeQualityForm
    success_url = reverse_lazy('console-trade-one-change-quality-pro')
    title = 'Change Quality Pro'
    model = FifaAccount
    table_class = ConsoleInvestChangeQualityProTable

    def get_card_data(self):
        cards_data = []
        query = self.get_queryset()
        cards_data.append({'name': 'Filtered Result',
                           'info': f"{query.count()}",
                           'box_css_class': 'fw-bold'})
        trade_qualities = ((None, 'None quality'), ) + ConsoleTradeOneQualityName
        for trade_quality in trade_qualities:
            query_count = query.filter(console_trade_one_quality=trade_quality[0]).count()
            cards_data.append({'name': f'{trade_quality[1]}',
                               'info': f"{query_count}",
                               'box_css_class': 'fw-bold'})
        active_trade = query.filter(
            run_console_trade_one=True
        ).count()
        cards_data.append({'name': 'Active Trade',
                           'info': f"{active_trade}",
                           'box_css_class': 'fw-bold'})
        return cards_data

    def get_queryset(self) -> QuerySet:
        latest_related = ConsoleTradeOneHistory.objects.filter(
            fifa_account__id=OuterRef('pk')
        )[:1]
        self.queryset = self.model.objects.filter(
            Q(delete_console_reason=None) | Q(delete_console_reason=''),
            console__name__in=list(range(0, 9000)), console__is_active=True,
        ).annotate(
            last_trade_history_id=Subquery(latest_related.values('id')),
            last_history_create_time=Subquery(latest_related.values('create_time')),
            last_history_current_sell_price=Subquery(latest_related.values('current_sell_price')),
            club_bronze1_items=Subquery(latest_related.values('club_bronze1_items')),
            club_silver0_items=Subquery(latest_related.values('club_silver0_items')),
            club_silver1_items=Subquery(latest_related.values('club_silver1_items')),
            club_gold0_items=Subquery(latest_related.values('club_gold0_items')),
            club_gold1_items=Subquery(latest_related.values('club_gold1_items')),
            club_position_modifier_items=Subquery(latest_related.values('club_position_modifier_items')),
            transfer_list_items_count=Subquery(latest_related.values('transfer_list_items_count')),
        ).annotate(
            current_club_transfer_cards=Case(
                When(console_trade_one_quality=Value('bronze1'),
                     then=F('club_bronze1_items') + F('transfer_list_items_count')),
                When(console_trade_one_quality=Value('silver0'),
                     then=F('club_silver0_items') + F('transfer_list_items_count')),
                When(console_trade_one_quality=Value('silver1'),
                     then=F('club_silver1_items') + F('transfer_list_items_count')),
                When(console_trade_one_quality=Value('gold0'),
                     then=F('club_gold0_items') + F('transfer_list_items_count')),
                When(console_trade_one_quality=Value('gold1'),
                     then=F('club_gold1_items') + F('transfer_list_items_count')),
                When(console_trade_one_quality=Value('position_modifier'),
                     then=F('club_position_modifier_items') + F('transfer_list_items_count')),
            ),
        )
        return super().get_queryset()

    def page_header(self):
        return 'Change Quality'

    def form_valid(self, form):
        new_quality = form.cleaned_data['new_quality']
        query = self.get_queryset()
        update_new_quality_count = query.update(console_trade_one_quality_new=new_quality,
                                                run_console_invest_trade_one=True, run_console_trade_one=True)
        messages.add_message(self.request, messages.SUCCESS,
                             f'Set New Quality for : {update_new_quality_count}')
        return super().form_valid(form)


class ConsoleTradeQualityReportView(LoginRequiredMixin, RequiredPermissionViewMixin, FilterView):
    template_name = 'generic_views/filter_list_view.html'
    required_permission__all = 'manager_commands'
    page_header = 'Console Trade Quality Report'
    model = ConsoleTradeOneQuality
    filterset_class = ConsoleTradeQualityReportFilterSet

    dashboard_layout = DashboardLayout(
        DashboardRow(
            ConsoleTradeQualityGold1ReportChartDashboardCard,
            ConsoleTradeQualityGold0ReportChartDashboardCard,
            ConsoleTradeQualitySilver1ReportChartDashboardCard,
            ConsoleTradeQualityBronze1ReportChartDashboardCard,

            css_class='row maximizable align-center',
            data_equalizer='',
            data_equalize_on='large',
            data_equalize_by_row='true',
        ),
    )

    def get_context_data(self, **kwargs):
        if 'dashboard_layout' not in kwargs:
            kwargs['dashboard_layout'] = self.dashboard_layout.construct(self.request, **self.kwargs)

        if 'filter' not in kwargs:
            kwargs['filter'] = self.get_filterset(self.get_filterset_class())

        kwargs['global_row_expanded'] = 'expanded'
        return super().get_context_data(**kwargs)

    def get_queryset(self):
        return None


class WebTradAddAccountView(RequiredPermissionViewMixin, GenericListView, FormView):
    required_permission__all = 'trade_workers__read__all'
    template_name = 'generic_views/form_list_view.html'
    form_class = WebTradeAddAccountForm
    success_url = reverse_lazy('web-trade-add-account')
    title = 'Web Trade Add Account'
    table_class = WebTradeAddAccountTable
    model = WebTradeInfo

    def page_header(self):
        return 'Add Account'

    def get_card_data(self):
        cards_data = []
        cards_data.append({'name': 'Sum Credit',
                           'info': f"{number_convertor_to_milion(sum(self.get_queryset().filter(deleted=False).values_list('fifa_account__credit', flat=True)))}",
                           'box_css_class': 'fw-bold'})
        return cards_data

    def get_table_data(self):
        query = self.get_queryset()
        query = query.filter(
            deleted=False
        ).annotate(
            search_count=Coalesce(Subquery(FifaAccountSearch.objects.filter(
                fifa_account__id=OuterRef('fifa_account__id'),
                search_time__gt=timezone.localtime() - timezone.timedelta(hours=24)
            ).values('fifa_account').annotate(
                acc_search_count=Count('id', output_field=IntegerField())
            ).values('acc_search_count'), output_field=IntegerField()), Value(0)),
        )
        data_list = []
        for item in query:
            bid_price = 0
            sell_price = 0
            last_update = None
            create_time = None
            quality_name = None
            bid_count = 0
            win_count = 0
            passed_since_bid = None
            last_trade_history = WebTradeHistory.objects.filter(web_trade_info=item).last()
            if last_trade_history:
                bid_price = last_trade_history.bid_price
                sell_price = last_trade_history.sell_price
                last_update = timezone.localtime(last_trade_history.update_time).strftime('%Y/%m/%d %H:%M')
                create_time = last_trade_history.create_time
                quality_name = last_trade_history.quality_name
                bid_count = last_trade_history.bid_count
                win_count = last_trade_history.win_count
                passed_since_last_bid = (timezone.localtime() - last_trade_history.create_time)
                hours, remainder = divmod(passed_since_last_bid.seconds, 3600)
                minutes, seconds = divmod(remainder, 60)
                passed_since_bid = '{} H:{} M'.format(int(hours), int(minutes))
            data_list.append({
                'id': item.id,
                'fifa_account': item.fifa_account,
                'credit': item.fifa_account.credit,
                'quality': quality_name,
                'search': item.search_count,
                'bid_price': bid_price,
                'sell_price': sell_price,
                'last_update': last_update,
                'create_time': create_time,
                'trade_one_state': 'deactive' if item.run_trade else 'active',
                'trade_one_text': 'DeActive' if item.run_trade else 'Active',
                'bid_count': bid_count,
                'win_count': win_count,
                'passed_since_bid': passed_since_bid,
            })
        return data_list

    def form_valid(self, form):
        selected_quality = form.cleaned_data.get('quality')
        accounts_file = form.files.get('accounts_file')
        wb_obj = openpyxl.load_workbook(accounts_file)
        sheet_obj = wb_obj.active
        max_row = sheet_obj.max_row
        for i in range(2, max_row + 1):
            ea_email = sheet_obj.cell(row=i, column=1).value
            if ea_email:
                fifa_account = FifaAccount.objects.get(user_name=ea_email)
                if hasattr(fifa_account, 'webtradeinfo'):
                    trade_info = fifa_account.webtradeinfo
                else:
                    trade_info = WebTradeInfo.objects.create(fifa_account=fifa_account)
                trade_info.quality_name = selected_quality.name
                trade_info.deleted = False
                trade_info.save()
        return super().form_valid(form)


class WebTradeAction(RequiredPermissionViewMixin, LoginRequiredMixin, View):
    required_permission__all = 'trade_workers__read__all'
    model = WebTradeInfo

    def get(self, request, *args, **kwargs):
        data = self.request.GET
        trade_info_id = data.get('web_trade_id')
        action = data.get('action')
        state = data.get('state')
        trade_info = WebTradeInfo.objects.get(id=trade_info_id)
        if action == 'web_trade_one' and state == 'active':
            trade_info.run_trade = True
            trade_info.save()
        elif action == 'web_trade_one' and state == 'deactive':
            trade_info.run_trade = False
            trade_info.save()
        return redirect('web-trade-add-account')


class DailyInvestTradeGenerateReportView(LoginRequiredMixin, RequiredPermissionViewMixin, FilterView):
    template_name = 'generic_views/filter_list_view.html'
    required_permission__all = 'daily_invest_trade__read__all'
    page_header = 'Daily Invest Generated'
    model = ConsoleTradeInvestDailyProfitLog
    filterset_class = DailyInvestTradeGenerateReportFilterSet
    fix_paginate_by = None

    dashboard_layout = DashboardLayout(
        DashboardRow(
            DailyInvestTradeGenerateChartDashboardCard,

            css_class='row maximizable small-up-1 medium-up-1 large-up-1 align-center',
            data_equalizer='',
            data_equalize_on='large',
            data_equalize_by_row='true',
        ),
    )

    @property
    def paginate_by(self):
        if self.fix_paginate_by:
            return self.fix_paginate_by
        per_page = self.request.GET.get('per_page')
        if not per_page:
            per_page = self.request.session.get('per_page')
        try:
            per_page = int(per_page)
        except (ValueError, TypeError):
            per_page = 10
        self.request.session['per_page'] = per_page
        return per_page

    def get_context_data(self, **kwargs):
        if 'dashboard_layout' not in kwargs:
            kwargs['dashboard_layout'] = self.dashboard_layout.construct(self.request, **self.kwargs)

        if 'filter' not in kwargs:
            kwargs['filter'] = self.get_filterset(self.get_filterset_class())

        kwargs['global_row_expanded'] = 'expanded'
        return super().get_context_data(**kwargs)

    def get_queryset(self):
        return None


class ConsoleInvestTradeListView(RequiredPermissionViewMixin, GenericFilterListView):
    template_name = 'generic_views/filter_list_view.html'
    required_permission__all = 'manager_commands'
    model = ConsoleTradeOneHistory
    table_class = ConsoleInvestTradeTable
    filterset_class = ConsoleInvestTradeFilterSet
    export_key = 'console_invest_trade'

    def get_queryset(self):
        query = super().get_queryset()
        query = query.filter(id__in=ConsoleTradeOneHistory.objects.filter(
            fifa_account__run_console_invest_trade_one=True
        ).order_by('fifa_account', '-id').distinct('fifa_account').values('id'))
        query = query.filter(
            Q(fifa_account__delete_console_reason=None) | Q(fifa_account__delete_console_reason='')
        ).annotate(
            current_club_transfer_cards=Case(
                When(fifa_account__console_trade_one_quality=Value('bronze1'),
                     then=F('club_bronze1_items') + F('transfer_list_items_count')),
                When(fifa_account__console_trade_one_quality=Value('silver0'),
                     then=F('club_silver0_items') + F('transfer_list_items_count')),
                When(fifa_account__console_trade_one_quality=Value('silver1'),
                     then=F('club_silver1_items') + F('transfer_list_items_count')),
                When(fifa_account__console_trade_one_quality=Value('gold0'),
                     then=F('club_gold0_items') + F('transfer_list_items_count')),
                When(fifa_account__console_trade_one_quality=Value('gold1'),
                     then=F('club_gold1_items') + F('transfer_list_items_count')),
                When(fifa_account__console_trade_one_quality=Value('position_modifier'),
                     then=F('club_position_modifier_items') + F('transfer_list_items_count')),

            ),
            current_cards_profit=Case(
                When(fifa_account__console_trade_one_quality=Value('bronze1'),
                     then=(F('club_bronze1_items') + F('transfer_list_items_count')) * Value(250)),
                When(fifa_account__console_trade_one_quality=Value('silver0'),
                     then=(F('club_silver0_items') + F('transfer_list_items_count')) * Value(250)),
                When(fifa_account__console_trade_one_quality=Value('silver1'),
                     then=(F('club_silver1_items') + F('transfer_list_items_count')) * Value(350)),
                When(fifa_account__console_trade_one_quality=Value('gold0'),
                     then=(F('club_gold0_items') + F('transfer_list_items_count')) * Value(400)),
                When(fifa_account__console_trade_one_quality=Value('gold1'),
                     then=(F('club_gold1_items') + F('transfer_list_items_count')) * Value(700)),
                When(fifa_account__console_trade_one_quality=Value('position_modifier'),
                     then=(F('club_position_modifier_items') + F('transfer_list_items_count')) * Value(1300)),
                default=Value(0)
            ),
            discharges=Coalesce(Subquery(CloseWebAppSnipes.objects.filter(
                insert_time__gt=OuterRef('create_time'),
                first_account__id=OuterRef('fifa_account__id'),
            ).annotate(
                moved_price=F('player_price') - F('player_min_price_from_futbin')
            ).values('first_account').annotate(sum_move_price=Sum('moved_price')).values('sum_move_price'),
                                         output_field=IntegerField()), Value(0)),
            salary=F('current_cards_profit') + F('current_credit'),
            profit=Case(When(credit_before__gt=0, then=F('salary') - F('credit_before') + F('discharges')),
                        default=0),
        )
        return query