plateforme-tfjm2/participation/views.py

# Copyright (C) 2020 by Animath
# SPDX-License-Identifier: GPL-3.0-or-later

import asyncio
from concurrent.futures import ThreadPoolExecutor
import csv
from hashlib import sha1
from io import BytesIO
import os
import subprocess
from tempfile import mkdtemp
from typing import Any, Dict
from zipfile import ZipFile

from django.conf import settings
from django.contrib import messages
from django.contrib.auth.mixins import LoginRequiredMixin
from django.contrib.sites.models import Site
from django.core.exceptions import PermissionDenied
from django.core.mail import send_mail
from django.db import transaction
from django.db.models import F
from django.http import FileResponse, Http404, HttpResponse
from django.shortcuts import redirect
from django.template.loader import render_to_string
from django.urls import reverse_lazy
from django.utils import timezone, translation
from django.utils.crypto import get_random_string
from django.utils.decorators import method_decorator
from django.utils.timezone import localtime
from django.utils.translation import gettext_lazy as _
from django.views.decorators.csrf import csrf_exempt
from django.views.generic import CreateView, DetailView, FormView, RedirectView, TemplateView, UpdateView, View
from django.views.generic.detail import SingleObjectMixin
from django.views.generic.edit import FormMixin, ProcessFormView
from django_tables2 import MultiTableMixin, SingleTableMixin, SingleTableView
import gspread
from magic import Magic
from odf.opendocument import OpenDocumentSpreadsheet
from odf.style import Style, TableCellProperties, TableColumnProperties, TextProperties
from odf.table import CoveredTableCell, Table, TableCell, TableColumn, TableRow
from odf.text import P
from registration.models import Payment, VolunteerRegistration
from registration.tables import PaymentTable
from tfjm.lists import get_sympa_client
from tfjm.views import AdminMixin, VolunteerMixin

from .forms import AddJuryForm, JoinTeamForm, MotivationLetterForm, NoteForm, ParticipationForm, PassageForm, \
    PoolForm, RequestValidationForm, SolutionForm, TeamForm, TournamentForm, UploadNotesForm, \
    ValidateParticipationForm, WrittenReviewForm
from .models import Note, Participation, Passage, Pool, Solution, Team, Tournament, Tweak, WrittenReview
from .tables import NoteTable, ParticipationTable, PassageTable, PoolTable, TeamTable, TournamentTable


class CreateTeamView(LoginRequiredMixin, CreateView):
    """
    Display the page to create a team for new users.
    """

    model = Team
    form_class = TeamForm
    extra_context = dict(title=_("Create team"))
    template_name = "participation/create_team.html"

    def dispatch(self, request, *args, **kwargs):
        user = request.user
        if not user.is_authenticated:
            return super().handle_no_permission()
        registration = user.registration
        if not registration.participates:
            raise PermissionDenied(_("You don't participate, so you can't create a team."))
        elif registration.team:
            raise PermissionDenied(_("You are already in a team."))
        return super().dispatch(request, *args, **kwargs)

    @transaction.atomic
    def form_valid(self, form):
        """
        When a team is about to be created, the user automatically
        joins the team, a mailing list got created and the user is
        automatically subscribed to this mailing list.
        """
        ret = super().form_valid(form)
        # The user joins the team
        user = self.request.user
        registration = user.registration
        registration.team = form.instance
        registration.save()

        # Subscribe the user mail address to the team mailing list
        get_sympa_client().subscribe(user.email, f"equipe-{form.instance.trigram.lower()}", False,
                                     f"{user.first_name} {user.last_name}")

        return ret


class JoinTeamView(LoginRequiredMixin, FormView):
    """
    Participants can join a team with the access code of the team.
    """
    model = Team
    form_class = JoinTeamForm
    extra_context = dict(title=_("Join team"))
    template_name = "participation/join_team.html"

    def dispatch(self, request, *args, **kwargs):
        user = request.user
        if not user.is_authenticated:
            return super().handle_no_permission()
        registration = user.registration
        if not registration.participates:
            raise PermissionDenied(_("You don't participate, so you can't create a team."))
        elif registration.team:
            raise PermissionDenied(_("You are already in a team."))
        return super().dispatch(request, *args, **kwargs)

    @transaction.atomic
    def form_valid(self, form):
        """
        When a user joins a team, the user is automatically subscribed to
        the team mailing list.
        """
        self.object = form.instance
        ret = super().form_valid(form)

        # Join the team
        user = self.request.user
        registration = user.registration
        registration.team = form.instance
        registration.save()

        # Subscribe to the team mailing list
        get_sympa_client().subscribe(user.email, f"equipe-{form.instance.trigram.lower()}", False,
                                     f"{user.first_name} {user.last_name}")

        return ret

    def get_success_url(self):
        return reverse_lazy("participation:team_detail", args=(self.object.pk,))


class TeamListView(AdminMixin, SingleTableView):
    """
    Display the whole list of teams
    """
    model = Team
    table_class = TeamTable
    ordering = ('trigram',)


class MyTeamDetailView(LoginRequiredMixin, RedirectView):
    """
    Redirect to the detail of the team in which the user is.
    """

    def get_redirect_url(self, *args, **kwargs):
        user = self.request.user
        registration = user.registration
        if registration.participates:
            if registration.team:
                return reverse_lazy("participation:team_detail", args=(registration.team_id,))
            raise PermissionDenied(_("You are not in a team."))
        raise PermissionDenied(_("You don't participate, so you don't have any team."))


class TeamDetailView(LoginRequiredMixin, FormMixin, ProcessFormView, DetailView):
    """
    Display the detail of a team.
    """
    model = Team

    def get(self, request, *args, **kwargs):
        user = request.user
        self.object = self.get_object()
        # Ensure that the user is an admin or a volunteer or a member of the team
        if user.registration.is_admin or user.registration.participates and \
                user.registration.team and user.registration.team.pk == kwargs["pk"] \
                or user.registration.is_volunteer \
                and (self.object.participation.tournament in user.registration.interesting_tournaments
                     or self.object.participation.final
                     and Tournament.final_tournament() in user.registration.interesting_tournaments):
            return super().get(request, *args, **kwargs)
        raise PermissionDenied

    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)

        team = self.get_object()
        context["title"] = _("Detail of team {trigram}").format(trigram=self.object.trigram)
        context["request_validation_form"] = RequestValidationForm(self.request.POST or None)
        context["validation_form"] = ValidateParticipationForm(self.request.POST or None)
        # A team is complete when there are at least 4 members plus a coache that have sent their authorizations,
        # their health sheet, they confirmed their email address and under-18 people sent their parental authorization.
        context["can_validate"] = team.can_validate()

        return context

    def get_form_class(self):
        if not self.request.POST:
            return RequestValidationForm
        elif self.request.POST["_form_type"] == "RequestValidationForm":
            return RequestValidationForm
        elif self.request.POST["_form_type"] == "ValidateParticipationForm":
            return ValidateParticipationForm

    def form_valid(self, form):
        self.object = self.get_object()
        if isinstance(form, RequestValidationForm):
            return self.handle_request_validation(form)
        elif isinstance(form, ValidateParticipationForm):
            return self.handle_validate_participation(form)

    def handle_request_validation(self, form):
        """
        A team requests to be validated
        """
        if not self.request.user.registration.participates:
            form.add_error(None, _("You don't participate, so you can't request the validation of the team."))
            return self.form_invalid(form)
        if self.object.participation.valid is not None:
            form.add_error(None, _("The validation of the team is already done or pending."))
            return self.form_invalid(form)
        if not self.get_context_data()["can_validate"]:
            form.add_error(None, _("The team can't be validated: missing email address confirmations, "
                                   "authorizations, people, motivation letter or the tournament is not set."))
            return self.form_invalid(form)

        self.object.participation.valid = False
        self.object.participation.save()

        mail_context = dict(team=self.object, domain=Site.objects.first().domain)
        mail_plain = render_to_string("participation/mails/request_validation.txt", mail_context)
        mail_html = render_to_string("participation/mails/request_validation.html", mail_context)
        send_mail(f"[{settings.APP_NAME}] {_('Team validation')}", mail_plain, settings.DEFAULT_FROM_EMAIL,
                  [self.object.participation.tournament.organizers_email], html_message=mail_html)

        return super().form_valid(form)

    def handle_validate_participation(self, form):
        """
        An admin validates the team (or not)
        """
        if not self.request.user.registration.is_admin and \
                (not self.object.participation.tournament
                 or self.request.user.registration not in self.object.participation.tournament.organizers.all()):
            form.add_error(None, _("You are not an organizer of the tournament."))
            return self.form_invalid(form)
        elif self.object.participation.valid is not False:
            form.add_error(None, _("This team has no pending validation."))
            return self.form_invalid(form)

        if "validate" in self.request.POST:
            self.object.participation.valid = True
            self.object.participation.save()

            domain = Site.objects.first().domain
            for registration in self.object.participants.all():
                if settings.PAYMENT_MANAGEMENT and \
                        registration.is_student and self.object.participation.tournament.price:
                    payment = Payment.objects.get(registrations=registration, final=False)
                else:
                    payment = None
                mail_context_plain = dict(domain=domain, registration=registration, team=self.object, payment=payment,
                                          message=form.cleaned_data["message"])
                mail_context_html = dict(domain=domain, registration=registration, team=self.object, payment=payment,
                                         message=form.cleaned_data["message"].replace('\n', '<br>'))
                mail_plain = render_to_string("participation/mails/team_validated.txt", mail_context_plain)
                mail_html = render_to_string("participation/mails/team_validated.html", mail_context_html)
                registration.user.email_user(f"[{settings.APP_NAME}] {_('Team validated')}", mail_plain,
                                             html_message=mail_html)
        elif "invalidate" in self.request.POST:
            self.object.participation.valid = None
            self.object.participation.save()
            mail_context_plain = dict(team=self.object, message=form.cleaned_data["message"])
            mail_context_html = dict(team=self.object, message=form.cleaned_data["message"].replace('\n', '<br>'))
            mail_plain = render_to_string("participation/mails/team_not_validated.txt", mail_context_plain)
            mail_html = render_to_string("participation/mails/team_not_validated.html", mail_context_html)
            send_mail(f"[{settings.APP_NAME}] {_('Team not validated')}", mail_plain,
                      None, [self.object.email], html_message=mail_html)
        else:
            form.add_error(None, _("You must specify if you validate the registration or not."))
            return self.form_invalid(form)
        return super().form_valid(form)

    def get_success_url(self):
        return self.request.path


class TeamUpdateView(LoginRequiredMixin, UpdateView):
    """
    Update the detail of a team
    """
    model = Team
    form_class = TeamForm
    template_name = "participation/update_team.html"

    def dispatch(self, request, *args, **kwargs):
        user = request.user
        if not user.is_authenticated:
            return super().handle_no_permission()
        if user.registration.is_admin or user.registration.participates and \
                user.registration.team and user.registration.team.pk == kwargs["pk"] \
                or user.registration.is_volunteer \
                and (self.get_object().participation.tournament in user.registration.interesting_tournaments
                     or self.get_object().participation.final
                     and Tournament.final_tournament() in user.registration.interesting_tournaments):
            return super().dispatch(request, *args, **kwargs)
        raise PermissionDenied

    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)
        context["participation_form"] = ParticipationForm(data=self.request.POST or None,
                                                          instance=self.object.participation)
        if not self.request.user.registration.is_volunteer:
            del context["participation_form"].fields['final']
        context["title"] = _("Update team {trigram}").format(trigram=self.object.trigram)
        return context

    @transaction.atomic
    def form_valid(self, form):
        participation_form = ParticipationForm(data=self.request.POST or None, instance=self.object.participation)
        if not self.request.user.registration.is_volunteer:
            del participation_form.fields['final']
        if not participation_form.is_valid():
            return self.form_invalid(form)

        participation_form.save()
        return super().form_valid(form)


class TeamUploadMotivationLetterView(LoginRequiredMixin, UpdateView):
    """
    A team can send its motivation letter.
    """
    model = Team
    form_class = MotivationLetterForm
    template_name = "participation/upload_motivation_letter.html"
    extra_context = dict(title=_("Upload motivation letter"))

    def dispatch(self, request, *args, **kwargs):
        if not self.request.user.is_authenticated or \
                not self.request.user.registration.is_admin \
                and self.request.user.registration.team != self.get_object():
            return self.handle_no_permission()
        return super().dispatch(request, *args, **kwargs)

    @transaction.atomic
    def form_valid(self, form):
        old_instance = Team.objects.get(pk=self.object.pk)
        if old_instance.motivation_letter:
            old_instance.motivation_letter.delete()
            old_instance.save()
        return super().form_valid(form)


class MotivationLetterView(LoginRequiredMixin, View):
    """
    Display the sent motivation letter.
    """

    def get(self, request, *args, **kwargs):
        filename = kwargs["filename"]
        path = f"media/authorization/motivation_letters/{filename}"
        if not os.path.exists(path):
            raise Http404
        team = Team.objects.get(motivation_letter__endswith=filename)
        user = request.user
        if not (user.registration in team.participants.all() or user.registration.is_admin
                or user.registration.is_volunteer
                and team.participation.tournament in user.registration.organized_tournaments.all()):
            raise PermissionDenied
        # Guess mime type of the file
        mime = Magic(mime=True)
        mime_type = mime.from_file(path)
        ext = mime_type.split("/")[1].replace("jpeg", "jpg")
        # Replace file name
        true_file_name = _("Motivation letter of {team}.{ext}").format(team=str(team), ext=ext)
        return FileResponse(open(path, "rb"), content_type=mime_type, filename=true_file_name)


class TeamAuthorizationsView(LoginRequiredMixin, View):
    """
    Get as a ZIP archive all the authorizations that are sent
    """

    def dispatch(self, request, *args, **kwargs):
        user = request.user
        if not user.is_authenticated:
            return super().handle_no_permission()

        if 'team_id' in kwargs:
            team = Team.objects.get(pk=kwargs["team_id"])
            tournament = team.participation.tournament
        else:
            team = None
            tournament = Tournament.objects.get(pk=kwargs["tournament_id"])

        if user.registration.is_admin or user.registration.is_volunteer \
                and (user.registration in tournament.organizers.all()
                     or (team is not None and team.participation.final
                         and user.registration in Tournament.final_tournament().organizers)):
            return super().dispatch(request, *args, **kwargs)
        raise PermissionDenied

    def get(self, request, *args, **kwargs):
        if 'team_id' in kwargs:
            team = Team.objects.get(pk=kwargs["team_id"])
            tournament = team.participation.tournament
            teams = [team]
            filename = _("Authorizations of team {trigram}.zip").format(trigram=team.trigram)
        else:
            tournament = Tournament.objects.get(pk=kwargs["tournament_id"])
            teams = [p.team for p in tournament.participations.filter(valid=True)]
            filename = _("Authorizations of {tournament}.zip").format(tournament=tournament.name)

        magic = Magic(mime=True)
        output = BytesIO()
        zf = ZipFile(output, "w")
        for team in teams:
            team_prefix = f"{team.trigram}/" if len(teams) > 1 else ""

            for participant in team.participants.all():
                user_prefix = f"{team_prefix}{participant.user.first_name} {participant.user.last_name}/"

                if 'team_id' in kwargs or not tournament.final:
                    # Don't include the photo authorization and the parental authorization of the regional tournament
                    # in the final authorizations
                    if participant.photo_authorization \
                            and participant.photo_authorization.storage.exists(participant.photo_authorization.path):
                        mime_type = magic.from_file("media/" + participant.photo_authorization.name)
                        ext = mime_type.split("/")[1].replace("jpeg", "jpg")
                        zf.write("media/" + participant.photo_authorization.name,
                                 user_prefix + _("Photo authorization of {participant}.{ext}")
                                 .format(participant=str(participant), ext=ext))

                    if participant.is_student and participant.parental_authorization \
                            and participant.parental_authorization.storage.exists(
                                    participant.parental_authorization.path):
                        mime_type = magic.from_file("media/" + participant.parental_authorization.name)
                        ext = mime_type.split("/")[1].replace("jpeg", "jpg")
                        zf.write("media/" + participant.parental_authorization.name,
                                 user_prefix + _("Parental authorization of {participant}.{ext}")
                                 .format(participant=str(participant), ext=ext))

                if participant.is_student and participant.health_sheet \
                        and participant.health_sheet.storage.exists(participant.health_sheet.path):
                    mime_type = magic.from_file("media/" + participant.health_sheet.name)
                    ext = mime_type.split("/")[1].replace("jpeg", "jpg")
                    zf.write("media/" + participant.health_sheet.name,
                             user_prefix + _("Health sheet of {participant}.{ext}")
                             .format(participant=str(participant), ext=ext))

                if participant.is_student and participant.vaccine_sheet \
                        and participant.vaccine_sheet.storage.exists(participant.vaccine_sheet.path):
                    mime_type = magic.from_file("media/" + participant.vaccine_sheet.name)
                    ext = mime_type.split("/")[1].replace("jpeg", "jpg")
                    zf.write("media/" + participant.vaccine_sheet.name,
                             user_prefix + _("Vaccine sheet of {participant}.{ext}")
                             .format(participant=str(participant), ext=ext))

                if 'team_id' in kwargs or tournament.final:
                    # Don't include final authorizations in the regional authorizations
                    if participant.photo_authorization_final \
                            and participant.photo_authorization_final.storage.exists(
                                participant.photo_authorization_final.path):
                        mime_type = magic.from_file("media/" + participant.photo_authorization_final.name)
                        ext = mime_type.split("/")[1].replace("jpeg", "jpg")
                        zf.write("media/" + participant.photo_authorization_final.name,
                                 user_prefix + _("Photo authorization of {participant} (final).{ext}")
                                 .format(participant=str(participant), ext=ext))

                    if participant.is_student and participant.parental_authorization_final \
                            and participant.parental_authorization_final.storage.exists(
                                participant.parental_authorization_final.path):
                        mime_type = magic.from_file("media/" + participant.parental_authorization_final.name)
                        ext = mime_type.split("/")[1].replace("jpeg", "jpg")
                        zf.write("media/" + participant.parental_authorization_final.name,
                                 user_prefix + _("Parental authorization of {participant} (final).{ext}")
                                 .format(participant=str(participant), ext=ext))

            if team.motivation_letter and team.motivation_letter.storage.exists(team.motivation_letter.path):
                mime_type = magic.from_file("media/" + team.motivation_letter.name)
                ext = mime_type.split("/")[1].replace("jpeg", "jpg")
                zf.write("media/" + team.motivation_letter.name,
                         team_prefix + _("Motivation letter of {team}.{ext}")
                         .format(team=str(team), ext=ext))

        zf.close()
        response = HttpResponse(content_type="application/zip")
        response["Content-Disposition"] = f"attachment; filename=\"{filename}\""
        response.write(output.getvalue())
        return response


class TeamLeaveView(LoginRequiredMixin, TemplateView):
    """
    A team member leaves a team
    """
    template_name = "participation/team_leave.html"
    extra_context = dict(title=_("Leave team"))

    def dispatch(self, request, *args, **kwargs):
        if not request.user.is_authenticated:
            return self.handle_no_permission()
        if not request.user.registration.participates or not request.user.registration.team:
            raise PermissionDenied(_("You are not in a team."))
        if request.user.registration.team.participation.valid:
            raise PermissionDenied(_("The team is already validated or the validation is pending."))
        return super().dispatch(request, *args, **kwargs)

    @transaction.atomic()
    def post(self, request, *args, **kwargs):
        """
        When the team is left, the user is unsubscribed from the team mailing list
        and kicked from the team room.
        """
        team = request.user.registration.team
        request.user.registration.team = None
        request.user.registration.save()
        get_sympa_client().unsubscribe(request.user.email, f"equipe-{team.trigram.lower()}", False)
        if team.students.count() + team.coaches.count() == 0:
            team.delete()
        return redirect(reverse_lazy("index"))


class MyParticipationDetailView(LoginRequiredMixin, RedirectView):
    """
    Redirects to the detail view of the participation of the team.
    """

    def get_redirect_url(self, *args, **kwargs):
        user = self.request.user
        registration = user.registration
        if registration.participates:
            if registration.team:
                return reverse_lazy("participation:participation_detail", args=(registration.team.participation.id,))
            raise PermissionDenied(_("You are not in a team."))
        raise PermissionDenied(_("You don't participate, so you don't have any team."))


class ParticipationDetailView(LoginRequiredMixin, DetailView):
    """
    Display detail about the participation of a team, and manage the solution submission.
    """
    model = Participation

    def dispatch(self, request, *args, **kwargs):
        user = request.user
        if not user.is_authenticated:
            return super().handle_no_permission()
        if not self.get_object().valid:
            raise PermissionDenied(_("The team is not validated yet."))
        if user.registration.is_admin or user.registration.participates \
                and user.registration.team.participation \
                and user.registration.team.participation.pk == kwargs["pk"] \
                or user.registration.is_volunteer \
                and (self.get_object().tournament in user.registration.interesting_tournaments
                     or self.get_object().final
                     and Tournament.final_tournament() in user.registration.interesting_tournaments):
            return super().dispatch(request, *args, **kwargs)
        raise PermissionDenied

    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)

        context["title"] = lambda: _("Participation of team {trigram}").format(trigram=self.object.team.trigram)

        return context


class TournamentListView(SingleTableView):
    """
    Display the list of all tournaments.
    """
    model = Tournament
    table_class = TournamentTable


class TournamentCreateView(AdminMixin, CreateView):
    """
    Create a new tournament.
    """
    model = Tournament
    form_class = TournamentForm

    def get_success_url(self):
        return reverse_lazy("participation:tournament_detail", args=(self.object.pk,))


class TournamentUpdateView(VolunteerMixin, UpdateView):
    """
    Update tournament detail.
    """
    model = Tournament
    form_class = TournamentForm

    def dispatch(self, request, *args, **kwargs):
        if not request.user.is_authenticated or not self.request.user.registration.is_admin \
                and not (self.request.user.registration.is_volunteer
                         and self.request.user.registration.organized_tournaments.all()):
            return self.handle_no_permission()
        return super().dispatch(request, *args, **kwargs)


class TournamentDetailView(MultiTableMixin, DetailView):
    """
    Display tournament detail.
    """
    model = Tournament

    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)
        tables = context['tables']
        context["teams"] = tables[0]
        context["pools"] = tables[1]

        notes = dict()
        for participation in self.object.participations.all():
            note = sum(pool.average(participation)
                       for pool in self.object.pools.filter(participations=participation).all()
                       if pool.results_available
                       or (self.request.user.is_authenticated and self.request.user.registration.is_volunteer))
            if note:
                notes[participation] = note
        sorted_notes = sorted(notes.items(), key=lambda x: x[1], reverse=True)
        context["notes"] = sorted_notes
        context["available_notes_1"] = all(pool.results_available for pool in self.object.pools.filter(round=1).all())
        context["available_notes_2"] = all(pool.results_available for pool in self.object.pools.filter(round=2).all())
        context["available_notes_3"] = all(pool.results_available for pool in self.object.pools.filter(round=3).all())

        if settings.HAS_FINAL and not self.object.final and notes and context["available_notes_2"] \
                and not self.request.user.is_anonymous and self.request.user.registration.is_volunteer:
            context["team_selectable_for_final"] = next(participation for participation, _note in sorted_notes
                                                        if not participation.final)

        return context

    def get_tables(self):
        return [
            ParticipationTable(self.object.participations.all()),
            PoolTable(self.object.pools.all()),
        ]


class TournamentPaymentsView(VolunteerMixin, SingleTableMixin, DetailView):
    """
    Display the list of payments of a tournament.
    """
    model = Tournament
    table_class = PaymentTable
    template_name = "participation/tournament_payments.html"

    def get_context_data(self, **kwargs: Any) -> Dict[str, Any]:
        context = super().get_context_data(**kwargs)
        context["title"] = _("Payments of {tournament}").format(tournament=self.object)
        return context

    def dispatch(self, request, *args, **kwargs):
        if not request.user.is_authenticated or not self.request.user.registration.is_admin \
                and not (self.request.user.registration.is_volunteer
                         and self.get_object() in self.request.user.registration.organized_tournaments.all()):
            return self.handle_no_permission()
        return super().dispatch(request, *args, **kwargs)

    def get_table_data(self):
        if self.object.final:
            payments = Payment.objects.filter(final=True)
        else:
            payments = Payment.objects.filter(registrations__team__participation__tournament=self.get_object())
        return payments.annotate(team_id=F('registrations__team')).order_by('-valid', 'registrations__team__trigram') \
            .distinct().all()


class TournamentExportCSVView(VolunteerMixin, DetailView):
    """
    Export all team informations in a CSV file.
    """
    model = Tournament

    def get(self, request, *args, **kwargs):
        tournament = self.get_object()

        resp = HttpResponse(
            content_type='text/csv',
            headers={'Content-Disposition': f'attachment; filename="Tournoi de {tournament.name}.csv"'},
        )
        writer = csv.DictWriter(resp, ('Tournoi', 'Équipe', 'Trigramme', 'Sélectionnée',
                                       'Nom', 'Prénom', 'Email', 'Type', 'Genre', 'Date de naissance',
                                       'Adresse', 'Code postal', 'Ville', 'Pays', 'Téléphone',
                                       'Classe', 'Établissement',
                                       'Nom responsable légal⋅e', 'Téléphone responsable légal⋅e',
                                       'Email responsable légal⋅e',
                                       'Problèmes de santé', 'Contraintes de logement'))
        writer.writeheader()

        participations = tournament.participations
        if 'all' not in request.GET:
            participations = participations.filter(valid=True)
        for participation in participations.order_by('-valid', 'team__trigram').all():
            for registration in participation.team.participants \
                    .order_by('coachregistration', 'user__last_name').all():
                writer.writerow({
                    'Tournoi': tournament.name,
                    'Équipe': participation.team.name,
                    'Trigramme': participation.team.trigram,
                    'Sélectionnée': ("oui" if participation.valid else
                                     "en attente" if participation.valid is False else "non"),
                    'Nom': registration.user.last_name,
                    'Prénom': registration.user.first_name,
                    'Email': registration.user.email,
                    'Type': registration.type.capitalize(),
                    'Genre': registration.get_gender_display() if registration.is_student else '',
                    'Date de naissance': registration.birth_date if registration.is_student else '',
                    'Adresse': registration.address,
                    'Code postal': registration.zip_code,
                    'Ville': registration.city,
                    'Pays': registration.country,
                    'Téléphone': registration.phone_number,
                    'Classe': registration.get_student_class_display() if registration.is_student
                    else registration.last_degree,
                    'Établissement': registration.school if registration.is_student
                    else registration.professional_activity,
                    'Nom responsable légal⋅e': registration.responsible_name if registration.is_student else '',
                    'Téléphone responsable légal⋅e': registration.responsible_phone if registration.is_student else '',
                    'Email responsable légal⋅e': registration.responsible_email if registration.is_student else '',
                    'Problèmes de santé': registration.health_issues,
                    'Contraintes de logement': registration.housing_constraints,
                })

        return resp


class TournamentPublishNotesView(VolunteerMixin, SingleObjectMixin, RedirectView):
    """
    Publish notes of a tournament for a given round.
    """
    model = Tournament

    def dispatch(self, request, *args, **kwargs):
        if not request.user.is_authenticated:
            return self.handle_no_permission()
        tournament = self.get_object()
        reg = request.user.registration
        if not reg.is_admin and (not reg.is_volunteer or tournament not in reg.organized_tournaments.all()):
            return self.handle_no_permission()
        return super().dispatch(request, *args, **kwargs)

    def get(self, request, *args, **kwargs):
        if int(kwargs["round"]) not in (1, 2):
            raise Http404

        tournament = Tournament.objects.get(pk=kwargs["pk"])
        tournament.pools.filter(round=kwargs["round"]).update(results_available='hide' not in request.GET)
        if 'hide' not in request.GET:
            messages.success(request, _("Notes published!"))
        else:
            messages.success(request, _("Notes hidden!"))
        return super().get(request, *args, **kwargs)

    def get_redirect_url(self, *args, **kwargs):
        return reverse_lazy("participation:tournament_detail", args=(kwargs['pk'],))


class TournamentHarmonizeView(VolunteerMixin, DetailView):
    """
    Harmonize the notes of a tournament.
    """
    model = Tournament
    template_name = "participation/tournament_harmonize.html"

    def dispatch(self, request, *args, **kwargs):
        if not request.user.is_authenticated:
            return self.handle_no_permission()
        tournament = self.get_object()
        reg = request.user.registration
        if not reg.is_admin and (not reg.is_volunteer or tournament not in reg.organized_tournaments.all()):
            return self.handle_no_permission()
        if self.kwargs['round'] not in range(1, settings.NB_ROUNDS + 1):
            raise Http404
        return super().dispatch(request, *args, **kwargs)

    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)

        tournament = self.get_object()
        context['round'] = self.kwargs['round']
        context['pools'] = tournament.pools.filter(round=context["round"]).all()
        context['title'] = _("Harmonize notes of {tournament} - Day {round}") \
            .format(tournament=tournament, round=context["round"])

        notes = dict()
        for participation in self.object.participations.filter(valid=True).all():
            note = sum(pool.average(participation) for pool in context['pools'])
            tweak = sum(tweak.diff for tweak in participation.tweaks.filter(pool__in=context['pools']).all())
            notes[participation] = {'note': note, 'tweak': tweak}
        context["notes"] = sorted(notes.items(), key=lambda x: x[1]['note'], reverse=True)

        return context


class TournamentHarmonizeNoteView(VolunteerMixin, DetailView):
    model = Tournament

    def dispatch(self, request, *args, **kwargs):
        if not request.user.is_authenticated:
            return self.handle_no_permission()
        tournament = self.get_object()
        reg = request.user.registration
        if not reg.is_admin and (not reg.is_volunteer or tournament not in reg.organized_tournaments.all()):
            return self.handle_no_permission()
        if self.kwargs['round'] not in range(1, settings.NB_ROUNDS + 1) \
                or self.kwargs['action'] not in ('add', 'remove') \
                or self.kwargs['trigram'] not in [p.team.trigram
                                                  for p in tournament.participations.filter(valid=True).all()]:
            raise Http404
        return super().dispatch(request, *args, **kwargs)

    def get(self, request, *args, **kwargs):
        tournament = self.get_object()
        participation = tournament.participations.filter(valid=True).get(team__trigram=kwargs['trigram'])
        pool = tournament.pools.get(round=kwargs['round'], participations=participation)
        tweak_qs = Tweak.objects.filter(participation=participation, pool=pool)
        old_diff = tweak_qs.first().diff if tweak_qs.exists() else 0
        new_diff = old_diff + (1 if kwargs['action'] == 'add' else -1)
        if new_diff == 0:
            tweak_qs.delete()
        else:
            tweak_qs.update_or_create(defaults={'diff': new_diff},
                                      create_defaults={'diff': new_diff, 'participation': participation, 'pool': pool})

        gc = gspread.service_account_from_dict(settings.GOOGLE_SERVICE_CLIENT)
        spreadsheet = gc.open_by_key(tournament.notes_sheet_id)
        worksheet = spreadsheet.worksheet("Classement final")
        column = 3 if kwargs['round'] == 1 else 5 if kwargs['round'] == 2 else 8
        row = worksheet.find(f"{participation.team.name} ({participation.team.trigram})", in_column=1).row
        worksheet.update_cell(row, column, new_diff)

        return redirect(reverse_lazy("participation:tournament_harmonize", args=(tournament.pk, kwargs['round'],)))


class SelectTeamFinalView(VolunteerMixin, DetailView):
    model = Tournament

    def dispatch(self, request, *args, **kwargs):
        if not request.user.is_authenticated:
            return self.handle_no_permission()
        tournament = self.get_object()
        reg = request.user.registration
        if not reg.is_admin and (not reg.is_volunteer or tournament not in reg.organized_tournaments.all()):
            return self.handle_no_permission()
        participation_qs = tournament.participations.filter(pk=self.kwargs["participation_id"])
        if not participation_qs.exists():
            raise Http404
        self.participation = participation_qs.get()
        return super().dispatch(request, *args, **kwargs)

    @transaction.atomic
    def get(self, request, *args, **kwargs):
        tournament = self.get_object()
        self.participation.final = True
        self.participation.save()
        for regional_sol in self.participation.solutions.filter(final_solution=False).all():
            final_sol, _created = Solution.objects.get_or_create(participation=self.participation, final_solution=True,
                                                                 problem=regional_sol.problem)
            final_sol: Solution
            with open(regional_sol.file.path, 'rb') as f:
                final_sol.file.save(regional_sol.file.name, f)
        for registration in self.participation.team.participants.all():
            registration.send_email_final_selection()
        return redirect(reverse_lazy("participation:tournament_detail", args=(tournament.pk,)))


class SolutionUploadView(LoginRequiredMixin, FormView):
    template_name = "participation/upload_solution.html"
    form_class = SolutionForm

    def dispatch(self, request, *args, **kwargs):
        qs = Participation.objects.filter(pk=self.kwargs["pk"])
        if not qs.exists():
            raise Http404
        self.participation = qs.get()
        if not self.request.user.is_authenticated or not self.request.user.registration.is_admin \
                and not (self.request.user.registration.participates
                         and self.request.user.registration.team == self.participation.team):
            return self.handle_no_permission()
        return super().dispatch(request, *args, **kwargs)

    @transaction.atomic
    def form_valid(self, form):
        """
        When a solution is submitted, it replaces a previous solution if existing,
        otherwise it creates a new solution.
        It is discriminating whenever the team is selected for the final tournament or not.
        """
        form_sol = form.instance
        sol_qs = Solution.objects.filter(participation=self.participation,
                                         problem=form_sol.problem,
                                         final_solution=self.participation.final)

        tournament = Tournament.final_tournament() if self.participation.final else self.participation.tournament
        if timezone.now() > tournament.solution_limit and sol_qs.exists():
            form.add_error(None, _("You can't upload a solution after the deadline."))
            return self.form_invalid(form)

        # Drop previous solution if existing
        for sol in sol_qs.all():
            sol.file.delete()
            sol.save()
            sol.delete()
        form_sol.participation = self.participation
        form_sol.final_solution = self.participation.final
        form_sol.save()
        return super().form_valid(form)

    def get_success_url(self):
        return reverse_lazy("participation:participation_detail", args=(self.participation.pk,))


class PoolCreateView(AdminMixin, CreateView):
    model = Pool
    form_class = PoolForm


class PoolDetailView(LoginRequiredMixin, DetailView):
    model = Pool

    def dispatch(self, request, *args, **kwargs):
        if not request.user.is_authenticated:
            return self.handle_no_permission()
        if request.user.registration.is_admin or request.user.registration.participates \
                and request.user.registration.team \
                and request.user.registration.team.participation in self.get_object().participations.all() \
                or request.user.registration.is_volunteer \
                and self.get_object().tournament in request.user.registration.interesting_tournaments:
            return super().dispatch(request, *args, **kwargs)
        return self.handle_no_permission()

    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)

        context["passages"] = PassageTable(self.object.passages.order_by('position').all())

        if self.object.results_available or self.request.user.registration.is_volunteer:
            # Hide notes before the end of the turn
            notes = dict()
            for participation in self.object.participations.all():
                # For a 5-teams pool, notes are separated in 2 different pool objects, so we fetch them all
                all_pools = self.object.tournament.pools.filter(round=self.object.round,
                                                                letter=self.object.letter).all()
                note = sum(pool.average(participation) for pool in all_pools)
                if note:
                    notes[participation] = note
            context["notes"] = sorted(notes.items(), key=lambda x: x[1], reverse=True)

        return context


class PoolUpdateView(VolunteerMixin, UpdateView):
    model = Pool
    form_class = PoolForm

    def dispatch(self, request, *args, **kwargs):
        if not request.user.is_authenticated:
            return self.handle_no_permission()
        if request.user.registration.is_admin or request.user.registration.is_volunteer \
                and self.get_object().tournament in request.user.registration.organized_tournaments.all():
            return super().dispatch(request, *args, **kwargs)
        return self.handle_no_permission()

    def form_valid(self, form):
        ret = super().form_valid(form)
        # Update Google Sheets juries lines
        if os.getenv('GOOGLE_PRIVATE_KEY_ID', None):
            self.object.update_juries_lines_spreadsheet()
        return ret


class SolutionsDownloadView(VolunteerMixin, View):
    """
    Download all solutions or written reviews as a ZIP archive.
    """

    def dispatch(self, request, *args, **kwargs):
        if not request.user.is_authenticated:
            return self.handle_no_permission()

        reg = request.user.registration
        if reg.is_admin:
            return super().dispatch(request, *args, **kwargs)

        if 'team_id' in kwargs:
            team = Team.objects.get(pk=kwargs["team_id"])
            tournament = team.participation.tournament
            if reg.participates and reg.team == team \
                    or reg.is_volunteer and (reg in tournament.organizers.all() or team.participation.final
                                             and reg in Tournament.final_tournament().organizers):
                return super().dispatch(request, *args, **kwargs)
        elif 'tournament_id' in kwargs:
            tournament = Tournament.objects.get(pk=kwargs["tournament_id"])
            if reg.is_volunteer \
                    and (tournament in reg.organized_tournaments.all()
                         or reg.pools_presided.filter(tournament=tournament).exists()):
                return super().dispatch(request, *args, **kwargs)
        else:
            pool = Pool.objects.get(pk=kwargs["pool_id"])
            tournament = pool.tournament
            if reg.is_volunteer \
                    and (reg in tournament.organizers.all()
                         or reg in pool.juries.all()
                         or reg.pools_presided.filter(tournament=tournament).exists()):
                return super().dispatch(request, *args, **kwargs)

        return self.handle_no_permission()

    def get(self, request, *args, **kwargs):
        is_solution = 'solutions' in request.path

        if 'team_id' in kwargs:
            team = Team.objects.get(pk=kwargs["team_id"])
            solutions = Solution.objects.filter(participation=team.participation).all()
            written_reviews = WrittenReview.objects.filter(participation=team.participation).all()
            filename = _("Solutions of team {trigram}.zip") if is_solution \
                else _("Written reviews of team {trigram}.zip")
            filename = filename.format(trigram=team.trigram)

            def prefix(s: Solution | WrittenReview) -> str:
                return ""
        elif 'tournament_id' in kwargs:
            tournament = Tournament.objects.get(pk=kwargs["tournament_id"])
            sort_by = request.GET.get('sort_by', 'team').lower()

            if sort_by == 'pool':
                pools = Pool.objects.filter(tournament=tournament).all()
                solutions = []
                for pool in pools:
                    for sol in pool.solutions:
                        sol.pool = pool
                        solutions.append(sol)
                written_reviews = WrittenReview.objects.filter(passage__pool__tournament=tournament).all()
                filename = _("Solutions of {tournament}.zip") if is_solution \
                    else _("Written reviews of {tournament}.zip")
                filename = filename.format(tournament=tournament.name)

                def prefix(s: Solution | WrittenReview) -> str:
                    pool = s.pool if is_solution else s.passage.pool
                    p = f"Poule {pool.short_name}/"
                    if not is_solution:
                        p += f"Passage {s.passage.position}/"
                    return p
            else:
                if not tournament.final:
                    solutions = Solution.objects.filter(participation__tournament=tournament).all()
                else:
                    solutions = Solution.objects.filter(final_solution=True).all()
                written_reviews = WrittenReview.objects.filter(passage__pool__tournament=tournament).all()
                filename = _("Solutions of {tournament}.zip") if is_solution \
                    else _("Written reviews of {tournament}.zip")
                filename = filename.format(tournament=tournament.name)

                def prefix(s: Solution | WrittenReview) -> str:
                    return f"{s.participation.team.trigram}/" if sort_by == "team" else f"Problème {s.problem}/"
        else:
            pool = Pool.objects.get(pk=kwargs["pool_id"])
            solutions = pool.solutions
            written_reviews = WrittenReview.objects.filter(passage__pool=pool).all()
            filename = _("Solutions for pool {pool} of tournament {tournament}.zip") \
                if is_solution else _("Written reviews for pool {pool} of tournament {tournament}.zip")
            filename = filename.format(pool=pool.short_name,
                                       tournament=pool.tournament.name)

            def prefix(s: Solution | WrittenReview) -> str:
                return ""

        output = BytesIO()
        zf = ZipFile(output, "w")
        for s in (solutions if is_solution else written_reviews):
            if s.file.storage.exists(s.file.path):
                zf.write("media/" + s.file.name, prefix(s) + f"{s}.pdf")

        zf.close()
        response = HttpResponse(content_type="application/zip")
        response["Content-Disposition"] = "attachment; filename=\"{filename}\"" \
            .format(filename=filename)
        response.write(output.getvalue())
        return response


class PoolJuryView(VolunteerMixin, FormView, DetailView):
    """
    This view lets organizers set jurys for a pool, without multiplying clicks.
    """
    model = Pool
    form_class = AddJuryForm
    template_name = 'participation/pool_jury.html'

    def dispatch(self, request, *args, **kwargs):
        self.object = self.get_object()

        if request.user.is_authenticated and \
                (request.user.registration.is_admin or request.user.registration.is_volunteer
                 and (self.object.tournament in request.user.registration.organized_tournaments.all()
                      or request.user.registration == self.object.jury_president)):
            return super().dispatch(request, *args, **kwargs)

        return self.handle_no_permission()

    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)
        context['title'] = _("Jury of pool {pool} for {tournament} with teams {teams}") \
            .format(pool=f"{self.object.short_name}",
                    tournament=self.object.tournament.name,
                    teams=", ".join(participation.team.trigram for participation in self.object.participations.all()))
        return context

    @transaction.atomic
    def form_valid(self, form):
        self.object = self.get_object()

        user = form.instance
        if user.id:
            # The user already exists, so we don't recreate it
            user.refresh_from_db()
            reg = user.registration
            if reg in self.object.juries.all():
                messages.warning(self.request, _("The jury {name} is already in the pool!")
                                 .format(name=f"{user.first_name} {user.last_name}"))
                return self.form_invalid(form)
        else:
            # Save the user object first
            form.save()
            # Create associated registration object to the new user
            reg = VolunteerRegistration.objects.create(
                user=user,
                professional_activity="Juré⋅e du tournoi " + self.object.tournament.name,
            )

            reg.send_email_validation_link()

            # Generate new password for the user
            password = get_random_string(16)
            user.set_password(password)
            user.save()

            # Send welcome mail
            subject = f"[{settings.APP_NAME}] " + str(_("New jury account"))
            site = Site.objects.first()
            message = render_to_string('registration/mails/add_organizer.txt', dict(user=user,
                                                                                    inviter=self.request.user,
                                                                                    password=password,
                                                                                    domain=site.domain))
            html = render_to_string('registration/mails/add_organizer.html', dict(user=user,
                                                                                  inviter=self.request.user,
                                                                                  password=password,
                                                                                  domain=site.domain))
            user.email_user(subject, message, html_message=html)

        # Add the user in the jury
        self.object.juries.add(reg)
        self.object.save()

        # Update Google Sheets juries lines
        if os.getenv('GOOGLE_PRIVATE_KEY_ID', None):
            self.object.update_juries_lines_spreadsheet()

        # Add notification
        messages.success(self.request, _("The jury {name} has been successfully added!")
                         .format(name=f"{user.first_name} {user.last_name}"))

        return super().form_valid(form)

    def form_invalid(self, form):
        # This is useful since we have a FormView + a DetailView
        self.object = self.get_object()
        return super().form_invalid(form)

    def get_success_url(self):
        return reverse_lazy('participation:pool_jury', args=(self.kwargs['pk'],))


class PoolRemoveJuryView(VolunteerMixin, DetailView):
    model = Pool

    def dispatch(self, request, *args, **kwargs):
        self.object = self.get_object()

        if request.user.is_authenticated and \
                (request.user.registration.is_admin or request.user.registration.is_volunteer
                 and (self.object.tournament in request.user.registration.organized_tournaments.all()
                      or request.user.registration == self.object.jury_president)):
            return super().dispatch(request, *args, **kwargs)

        return self.handle_no_permission()

    def get(self, request, *args, **kwargs):
        pool = self.get_object()
        if not pool.juries.filter(pk=kwargs['jury_id']).exists():
            raise Http404
        jury = pool.juries.get(pk=kwargs['jury_id'])
        pool.juries.remove(jury)
        pool.save()
        Note.objects.filter(jury=jury, passage__pool=pool).delete()
        messages.success(request, _("The jury {name} has been successfully removed!")
                         .format(name=f"{jury.user.first_name} {jury.user.last_name}"))
        return redirect(reverse_lazy('participation:pool_jury', args=(pool.pk,)))


class PoolPresideJuryView(VolunteerMixin, DetailView):
    model = Pool

    def dispatch(self, request, *args, **kwargs):
        self.object = self.get_object()

        if request.user.is_authenticated and \
                (request.user.registration.is_admin or request.user.registration.is_volunteer
                 and (self.object.tournament in request.user.registration.organized_tournaments.all()
                      or request.user.registration == self.object.jury_president)):
            return super().dispatch(request, *args, **kwargs)

        return self.handle_no_permission()

    def get(self, request, *args, **kwargs):
        pool = self.get_object()
        if not pool.juries.filter(pk=kwargs['jury_id']).exists():
            raise Http404
        jury = pool.juries.get(pk=kwargs['jury_id'])
        pool.jury_president = jury
        pool.save()
        messages.success(request, _("The jury {name} has been successfully promoted president!")
                         .format(name=f"{jury.user.first_name} {jury.user.last_name}"))
        return redirect(reverse_lazy('participation:pool_jury', args=(pool.pk,)))


class PoolUploadNotesView(VolunteerMixin, FormView, DetailView):
    model = Pool
    form_class = UploadNotesForm
    template_name = 'participation/upload_notes.html'

    def dispatch(self, request, *args, **kwargs):
        self.object = self.get_object()

        if request.user.is_authenticated and \
                (request.user.registration.is_admin or request.user.registration.is_volunteer
                 and (self.object.tournament in request.user.registration.organized_tournaments.all()
                      or request.user.registration == self.object.jury_president)):
            return super().dispatch(request, *args, **kwargs)

        return self.handle_no_permission()

    @transaction.atomic
    def form_valid(self, form):
        pool = self.get_object()
        parsed_notes = form.cleaned_data['parsed_notes']

        for vr in parsed_notes.keys():
            if vr not in pool.juries.all():
                form.add_error('file', _("The following user is not registered as a jury:") + " " + str(vr))

        if form.errors:
            return self.form_invalid(form)

        for vr, notes in parsed_notes.items():
            notes_count = 6 + (2 if pool.participations.count() >= 4 and settings.HAS_OBSERVER else 0)
            for i, passage in enumerate(pool.passages.all()):
                note = Note.objects.get_or_create(jury=vr, passage=passage)[0]
                passage_notes = notes[notes_count * i:notes_count * (i + 1)]
                note.set_all(*list(map(int, passage_notes)))
                note.save()

        if os.getenv('GOOGLE_PRIVATE_KEY_ID', None):
            pool.update_spreadsheet()

        messages.success(self.request, _("Notes were successfully uploaded."))
        return super().form_valid(form)

    def get_success_url(self):
        return reverse_lazy('participation:pool_detail', args=(self.kwargs['pk'],))


class PoolNotesTemplateView(VolunteerMixin, DetailView):
    """
    Generate an ODS sheet to fill the notes of the pool.
    """
    model = Pool

    def dispatch(self, request, *args, **kwargs):
        self.object = self.get_object()

        if request.user.is_authenticated and \
                (request.user.registration.is_admin or request.user.registration.is_volunteer
                 and (self.object.tournament in request.user.registration.organized_tournaments.all()
                      or request.user.registration == self.object.jury_president)):
            return super().dispatch(request, *args, **kwargs)

        return self.handle_no_permission()

    def render_to_response(self, context, **response_kwargs):  # noqa: C901
        translation.activate(settings.PREFERRED_LANGUAGE_CODE)

        pool_size = self.object.passages.count()
        has_observer = self.object.participations.count() >= 4 and settings.HAS_OBSERVER
        passage_width = 6 + (2 if has_observer else 0)
        line_length = pool_size * passage_width

        def getcol(number: int) -> str:
            """
            Translates the given number to the nth column name
            """
            if number == 0:
                return ''
            return getcol((number - 1) // 26) + chr(65 + (number - 1) % 26)

        doc = OpenDocumentSpreadsheet()

        # Define styles
        style = Style(name="Contenu", family="table-cell")
        style.addElement(TableCellProperties(border="0.75pt solid #000000"))
        doc.styles.addElement(style)

        style_left = Style(name="Contenu gauche", family="table-cell")
        style_left.addElement(TableCellProperties(border="0.75pt solid #000000", borderleft="2pt solid #000000"))
        doc.styles.addElement(style_left)

        style_right = Style(name="Contenu droite", family="table-cell")
        style_right.addElement(TableCellProperties(border="0.75pt solid #000000", borderright="2pt solid #000000"))
        doc.styles.addElement(style_right)

        style_top = Style(name="Contenu haut", family="table-cell")
        style_top.addElement(TableCellProperties(border="0.75pt solid #000000", bordertop="2pt solid #000000"))
        doc.styles.addElement(style_top)

        style_topright = Style(name="Contenu haut droite", family="table-cell")
        style_topright.addElement(TableCellProperties(border="0.75pt solid #000000",
                                                      borderright="2pt solid #000000",
                                                      bordertop="2pt solid #000000"))
        doc.styles.addElement(style_topright)

        style_topleftright = Style(name="Contenu haut gauche droite", family="table-cell")
        style_topleftright.addElement(TableCellProperties(border="0.75pt solid #000000",
                                                          borderleft="2pt solid #000000",
                                                          borderright="2pt solid #000000",
                                                          bordertop="2pt solid #000000"))
        doc.styles.addElement(style_topleftright)

        style_leftright = Style(name="Contenu haut gauche droite", family="table-cell")
        style_leftright.addElement(TableCellProperties(border="0.75pt solid #000000",
                                                       borderleft="2pt solid #000000",
                                                       borderright="2pt solid #000000"))
        doc.styles.addElement(style_leftright)

        style_botleft = Style(name="Contenu bas gauche", family="table-cell")
        style_botleft.addElement(TableCellProperties(border="0.75pt solid #000000",
                                                     borderbottom="2pt solid #000000",
                                                     borderleft="2pt solid #000000"))
        doc.styles.addElement(style_botleft)

        style_bot = Style(name="Contenu bas", family="table-cell")
        style_bot.addElement(TableCellProperties(border="0.75pt solid #000000", borderbottom="2pt solid #000000"))
        doc.styles.addElement(style_bot)

        style_botright = Style(name="Contenu bas droite", family="table-cell")
        style_botright.addElement(TableCellProperties(border="0.75pt solid #000000",
                                                      borderbottom="2pt solid #000000",
                                                      borderright="2pt solid #000000"))
        doc.styles.addElement(style_botright)

        title_style = Style(name="Titre", family="table-cell")
        title_style.addElement(TextProperties(fontweight="bold"))
        title_style.addElement(TableCellProperties(border="0.75pt solid #000000"))
        doc.styles.addElement(title_style)

        title_style_left = Style(name="Titre gauche", family="table-cell")
        title_style_left.addElement(TextProperties(fontweight="bold"))
        title_style_left.addElement(TableCellProperties(border="0.75pt solid #000000",
                                                        borderleft="2pt solid #000000"))
        doc.styles.addElement(title_style_left)

        title_style_right = Style(name="Titre droite", family="table-cell")
        title_style_right.addElement(TextProperties(fontweight="bold"))
        title_style_right.addElement(TableCellProperties(border="0.75pt solid #000000",
                                                         borderright="2pt solid #000000"))
        doc.styles.addElement(title_style_right)

        title_style_leftright = Style(name="Titre gauche droite", family="table-cell")
        title_style_leftright.addElement(TextProperties(fontweight="bold"))
        title_style_leftright.addElement(TableCellProperties(border="0.75pt solid #000000",
                                                             borderleft="2pt solid #000000",
                                                             borderright="2pt solid #000000"))
        doc.styles.addElement(title_style_leftright)

        title_style_top = Style(name="Titre haut", family="table-cell")
        title_style_top.addElement(TextProperties(fontweight="bold"))
        title_style_top.addElement(TableCellProperties(border="0.75pt solid #000000",
                                                       bordertop="2pt solid #000000"))
        doc.styles.addElement(title_style_top)

        title_style_topbot = Style(name="Titre haut bas", family="table-cell")
        title_style_topbot.addElement(TextProperties(fontweight="bold"))
        title_style_topbot.addElement(TableCellProperties(border="0.75pt solid #000000",
                                                          bordertop="2pt solid #000000",
                                                          borderbottom="2pt solid #000000"))
        doc.styles.addElement(title_style_topbot)

        title_style_topleft = Style(name="Titre haut gauche", family="table-cell")
        title_style_topleft.addElement(TextProperties(fontweight="bold"))
        title_style_topleft.addElement(TableCellProperties(border="0.75pt solid #000000",
                                                           bordertop="2pt solid #000000",
                                                           borderleft="2pt solid #000000"))
        doc.styles.addElement(title_style_topleft)

        title_style_topbotleft = Style(name="Titre haut bas gauche", family="table-cell")
        title_style_topbotleft.addElement(TextProperties(fontweight="bold"))
        title_style_topbotleft.addElement(TableCellProperties(border="0.75pt solid #000000",
                                                              bordertop="2pt solid #000000",
                                                              borderbottom="2pt solid #000000",
                                                              borderleft="2pt solid #000000"))
        doc.styles.addElement(title_style_topbotleft)

        title_style_topright = Style(name="Titre haut droite", family="table-cell")
        title_style_topright.addElement(TextProperties(fontweight="bold"))
        title_style_topright.addElement(TableCellProperties(border="0.75pt solid #000000",
                                                            bordertop="2pt solid #000000",
                                                            borderright="2pt solid #000000"))
        doc.styles.addElement(title_style_topright)

        title_style_topbotright = Style(name="Titre haut bas droite", family="table-cell")
        title_style_topbotright.addElement(TextProperties(fontweight="bold"))
        title_style_topbotright.addElement(TableCellProperties(border="0.75pt solid #000000",
                                                               bordertop="2pt solid #000000",
                                                               borderbottom="2pt solid #000000",
                                                               borderright="2pt solid #000000"))
        doc.styles.addElement(title_style_topbotright)

        title_style_topleftright = Style(name="Titre haut gauche droite", family="table-cell")
        title_style_topleftright.addElement(TextProperties(fontweight="bold"))
        title_style_topleftright.addElement(TableCellProperties(border="0.75pt solid #000000",
                                                                bordertop="2pt solid #000000",
                                                                borderleft="2pt solid #000000",
                                                                borderright="2pt solid #000000"))
        doc.styles.addElement(title_style_topleftright)

        title_style_bot = Style(name="Titre bas", family="table-cell")
        title_style_bot.addElement(TextProperties(fontweight="bold"))
        title_style_bot.addElement(TableCellProperties(border="0.75pt solid #000000",
                                                       borderbottom="2pt solid #000000"))
        doc.styles.addElement(title_style_bot)

        title_style_botleft = Style(name="Titre bas gauche", family="table-cell")
        title_style_botleft.addElement(TextProperties(fontweight="bold"))
        title_style_botleft.addElement(TableCellProperties(border="0.75pt solid #000000",
                                                           borderbottom="2pt solid #000000",
                                                           borderleft="2pt solid #000000"))
        doc.styles.addElement(title_style_botleft)

        title_style_botright = Style(name="Titre bas droite", family="table-cell")
        title_style_botright.addElement(TextProperties(fontweight="bold"))
        title_style_botright.addElement(TableCellProperties(border="0.75pt solid #000000",
                                                            borderbottom="2pt solid #000000",
                                                            borderright="2pt solid #000000"))
        doc.styles.addElement(title_style_botright)

        first_col_style = Style(name="co1", family="table-column")
        first_col_style.addElement(TableColumnProperties(columnwidth="9cm", breakbefore="auto"))
        doc.automaticstyles.addElement(first_col_style)

        jury_id_style = Style(name="co_jury_id", family="table-column")
        jury_id_style.addElement(TableColumnProperties(columnwidth="1cm", breakbefore="auto"))
        doc.automaticstyles.addElement(jury_id_style)

        col_style = Style(name="co2", family="table-column")
        col_style.addElement(TableColumnProperties(columnwidth="2.6cm", breakbefore="auto"))
        doc.automaticstyles.addElement(col_style)

        table = Table(name=f"Poule {self.object.short_name}")
        doc.spreadsheet.addElement(table)

        table.addElement(TableColumn(stylename=first_col_style))
        table.addElement(TableColumn(stylename=jury_id_style))

        for i in range(line_length):
            table.addElement(TableColumn(stylename=col_style))

        # Add line for the problems for different passages
        header_pb = TableRow()
        table.addElement(header_pb)
        problems_tc = TableCell(valuetype="string", stylename=title_style_topleft)
        problems_tc.addElement(P(text=_("Problem")))
        problems_tc.setAttribute('numbercolumnsspanned', "2")
        header_pb.addElement(problems_tc)
        header_pb.addElement(CoveredTableCell())
        for passage in self.object.passages.all():
            tc = TableCell(valuetype="string", stylename=title_style_topleftright)
            tc.addElement(P(text=_("Problem #{problem}").format(problem=passage.solution_number)))
            tc.setAttribute('numbercolumnsspanned', str(passage_width))
            header_pb.addElement(tc)
            header_pb.addElement(CoveredTableCell(numbercolumnsrepeated=passage_width - 1))

        # Add roles on the second line of the table
        header_role = TableRow()
        table.addElement(header_role)
        role_tc = TableCell(valuetype="string", stylename=title_style_left)
        role_tc.addElement(P(text=_("Role")))
        role_tc.setAttribute('numbercolumnsspanned', "2")
        header_role.addElement(role_tc)
        header_role.addElement(CoveredTableCell())
        for i in range(pool_size):
            reporter_tc = TableCell(valuetype="string", stylename=title_style_left)
            reporter_tc.addElement(P(text=_("Reporter")))
            reporter_tc.setAttribute('numbercolumnsspanned', "2")
            header_role.addElement(reporter_tc)
            header_role.addElement(CoveredTableCell())

            opponent_tc = TableCell(valuetype="string", stylename=title_style)
            opponent_tc.addElement(P(text=_("Opponent")))
            opponent_tc.setAttribute('numbercolumnsspanned', "2")
            header_role.addElement(opponent_tc)
            header_role.addElement(CoveredTableCell())

            reviewer_tc = TableCell(valuetype="string",
                                    stylename=title_style if has_observer else title_style_right)
            reviewer_tc.addElement(P(text=_("Reviewer")))
            reviewer_tc.setAttribute('numbercolumnsspanned', "2")
            header_role.addElement(reviewer_tc)
            header_role.addElement(CoveredTableCell())

            if has_observer:
                observer_tc = TableCell(valuetype="string", stylename=title_style_right)
                observer_tc.addElement(P(text=_("Observer")))
                observer_tc.setAttribute('numbercolumnsspanned', "2")
                header_role.addElement(observer_tc)
                header_role.addElement(CoveredTableCell())

        # Add maximum notes on the third line
        header_notes = TableRow()
        table.addElement(header_notes)
        jury_tc = TableCell(valuetype="string", value=_("Juree"), stylename=title_style_botleft)
        jury_tc.addElement(P(text=_("Juree")))
        jury_tc.setAttribute('numbercolumnsspanned', "2")
        header_notes.addElement(jury_tc)
        header_notes.addElement(CoveredTableCell())

        for i in range(pool_size):
            reporter_w_tc = TableCell(valuetype="string", stylename=title_style_botleft)
            reporter_w_tc.addElement(P(text=f"{_('Writing')} (/{20 if settings.TFJM_APP == 'TFJM' else 10})"))
            header_notes.addElement(reporter_w_tc)

            reporter_o_tc = TableCell(valuetype="string", stylename=title_style_bot)
            reporter_o_tc.addElement(P(text=f"{_('Oral')} (/{20 if settings.TFJM_APP == 'TFJM' else 10})"))
            header_notes.addElement(reporter_o_tc)

            opponent_w_tc = TableCell(valuetype="string", stylename=title_style_bot)
            opponent_w_tc.addElement(P(text=f"{_('Writing')} (/10)"))
            header_notes.addElement(opponent_w_tc)

            opponent_o_tc = TableCell(valuetype="string", stylename=title_style_bot)
            opponent_o_tc.addElement(P(text=f"{_('Oral')} (/10)"))
            header_notes.addElement(opponent_o_tc)

            reviewer_w_tc = TableCell(valuetype="string", stylename=title_style_bot)
            reviewer_w_tc.addElement(P(text=f"{_('Writing')} (/10)"))
            header_notes.addElement(reviewer_w_tc)

            reviewer_o_tc = TableCell(valuetype="string",
                                      stylename=title_style_bot if has_observer else title_style_botright)
            reviewer_o_tc.addElement(P(text=f"{_('Oral')} (/10)"))
            header_notes.addElement(reviewer_o_tc)

            if has_observer:
                observer_w_tc = TableCell(valuetype="string", stylename=title_style_bot)
                observer_w_tc.addElement(P(text=f"{_('Writing')} (/10)"))
                header_notes.addElement(observer_w_tc)

                observer_o_tc = TableCell(valuetype="string", stylename=title_style_botright)
                observer_o_tc.addElement(P(text=f"{_('Oral')} (/10)"))
                header_notes.addElement(observer_o_tc)

        # Add a notation line for each jury
        for jury in self.object.juries.all():
            jury_row = TableRow()
            table.addElement(jury_row)

            name_tc = TableCell(valuetype="string", stylename=style_leftright)
            name_tc.addElement(P(text=f"{jury.user.first_name} {jury.user.last_name}"))
            jury_row.addElement(name_tc)
            jury_id_tc = TableCell(valuetype="float", value=jury.pk, stylename=style_leftright)
            jury_id_tc.addElement(P(text=str(jury.pk)))
            jury_row.addElement(jury_id_tc)

            for passage in self.object.passages.all():
                notes = Note.objects.get(jury=jury, passage=passage)
                for j, note in enumerate(notes.get_all()):
                    note_tc = TableCell(valuetype="float", value=note,
                                        stylename=style_right if j == passage_width - 1 else style)
                    note_tc.addElement(P(text=str(note)))
                    jury_row.addElement(note_tc)

        jury_size = self.object.juries.count()
        min_row = 4
        max_row = 4 + jury_size - 1
        min_column = 3

        # Add line for averages
        average_row = TableRow()
        table.addElement(average_row)
        average_tc = TableCell(valuetype="string", stylename=title_style_topleftright)
        average_tc.addElement(P(text=_("Average")))
        average_tc.setAttribute('numbercolumnsspanned', "2")
        average_row.addElement(average_tc)
        average_row.addElement(CoveredTableCell())
        for i, passage in enumerate(self.object.passages.all()):
            for j, note in enumerate(passage.averages):
                tc = TableCell(valuetype="float", value=note,
                               stylename=style_topright if j == passage_width - 1 else style_top)
                tc.addElement(P(text=str(note)))
                column = getcol(min_column + i * passage_width + j)
                tc.setAttribute("formula", f"of:=AVERAGEIF([.${getcol(min_column + i * passage_width)}${min_row}"
                                           f":${getcol(min_column + i * passage_width)}{max_row}]; \">0\"; "
                                           f"[.{column}${min_row}:{column}{max_row}])")
                average_row.addElement(tc)

        # Add coefficients for each note on the next line
        coeff_row = TableRow()
        table.addElement(coeff_row)
        coeff_tc = TableCell(valuetype="string", stylename=title_style_leftright)
        coeff_tc.addElement(P(text=_("Coefficient")))
        coeff_tc.setAttribute('numbercolumnsspanned', "2")
        coeff_row.addElement(coeff_tc)
        coeff_row.addElement(CoveredTableCell())
        for passage in self.object.passages.all():
            reporter_w_tc = TableCell(valuetype="float", value=passage.coeff_reporter_writing, stylename=style_left)
            reporter_w_tc.addElement(P(text=str(passage.coeff_reporter_writing)))
            coeff_row.addElement(reporter_w_tc)

            reporter_o_tc = TableCell(valuetype="float", value=passage.coeff_reporter_oral, stylename=style)
            reporter_o_tc.addElement(P(text=str(passage.coeff_reporter_oral)))
            coeff_row.addElement(reporter_o_tc)

            opponent_w_tc = TableCell(valuetype="float", value=passage.coeff_opponent_writing, stylename=style)
            opponent_w_tc.addElement(P(text=str(passage.coeff_opponent_writing)))
            coeff_row.addElement(opponent_w_tc)

            opponent_o_tc = TableCell(valuetype="float", value=passage.coeff_opponent_oral, stylename=style)
            opponent_o_tc.addElement(P(text=str(passage.coeff_opponent_oral)))
            coeff_row.addElement(opponent_o_tc)

            reviewer_w_tc = TableCell(valuetype="float", value=passage.coeff_reviewer_writing, stylename=style)
            reviewer_w_tc.addElement(P(text=str(passage.coeff_reviewer_writing)))
            coeff_row.addElement(reviewer_w_tc)

            reviewer_o_tc = TableCell(valuetype="float", value=passage.coeff_reviewer_oral,
                                      stylename=style if has_observer else style_right)
            reviewer_o_tc.addElement(P(text=str(passage.coeff_reviewer_oral)))
            coeff_row.addElement(reviewer_o_tc)

            if has_observer:
                observer_w_tc = TableCell(valuetype="float", value=passage.coeff_observer_writing, stylename=style)
                observer_w_tc.addElement(P(text=str(passage.coeff_observer_writing)))
                coeff_row.addElement(observer_w_tc)

                observer_o_tc = TableCell(valuetype="float", value=passage.coeff_observer_oral, stylename=style_right)
                observer_o_tc.addElement(P(text=str(passage.coeff_observer_oral)))
                coeff_row.addElement(observer_o_tc)

        # Add the subtotal on the next line
        subtotal_row = TableRow()
        table.addElement(subtotal_row)
        subtotal_tc = TableCell(valuetype="string", stylename=title_style_botleft)
        subtotal_tc.addElement(P(text=_("Subtotal")))
        subtotal_tc.setAttribute('numbercolumnsspanned', "2")
        subtotal_row.addElement(subtotal_tc)
        subtotal_row.addElement(CoveredTableCell())
        for i, passage in enumerate(self.object.passages.all()):
            def_w_col = getcol(min_column + passage_width * i)
            def_o_col = getcol(min_column + passage_width * i + 1)
            reporter_tc = TableCell(valuetype="float", value=passage.average_reporter, stylename=style_botleft)
            reporter_tc.addElement(P(text=str(passage.average_reporter)))
            reporter_tc.setAttribute('numbercolumnsspanned', "2")
            reporter_tc.setAttribute("formula", f"of:=[.{def_w_col}{max_row + 1}] * [.{def_w_col}{max_row + 2}]"
                                                f" + [.{def_o_col}{max_row + 1}] * [.{def_o_col}{max_row + 2}]")
            subtotal_row.addElement(reporter_tc)
            subtotal_row.addElement(CoveredTableCell())

            opp_w_col = getcol(min_column + passage_width * i + 2)
            opp_o_col = getcol(min_column + passage_width * i + 3)
            opponent_tc = TableCell(valuetype="float", value=passage.average_opponent, stylename=style_bot)
            opponent_tc.addElement(P(text=str(passage.average_opponent)))
            opponent_tc.setAttribute('numbercolumnsspanned', "2")
            opponent_tc.setAttribute("formula", f"of:=[.{opp_w_col}{max_row + 1}] * [.{opp_w_col}{max_row + 2}]"
                                                f" + [.{opp_o_col}{max_row + 1}] * [.{opp_o_col}{max_row + 2}]")
            subtotal_row.addElement(opponent_tc)
            subtotal_row.addElement(CoveredTableCell())

            rep_w_col = getcol(min_column + passage_width * i + 4)
            rep_o_col = getcol(min_column + passage_width * i + 5)
            reviewer_tc = TableCell(valuetype="float", value=passage.average_reviewer,
                                    stylename=style_bot if has_observer else style_botright)
            reviewer_tc.addElement(P(text=str(passage.average_reviewer)))
            reviewer_tc.setAttribute('numbercolumnsspanned', "2")
            reviewer_tc.setAttribute("formula", f"of:=[.{rep_w_col}{max_row + 1}] * [.{rep_w_col}{max_row + 2}]"
                                                f" + [.{rep_o_col}{max_row + 1}] * [.{rep_o_col}{max_row + 2}]")
            subtotal_row.addElement(reviewer_tc)
            subtotal_row.addElement(CoveredTableCell())

            if has_observer:
                obs_w_col = getcol(min_column + passage_width * i + 6)
                obs_o_col = getcol(min_column + passage_width * i + 7)
                observer_tc = TableCell(valuetype="float", value=passage.average_observer, stylename=style_botright)
                observer_tc.addElement(P(text=str(passage.average_observer)))
                observer_tc.setAttribute('numbercolumnsspanned', "2")
                observer_tc.setAttribute("formula", f"of:=[.{obs_w_col}{max_row + 1}] * [.{obs_w_col}{max_row + 2}]"
                                                    f" + [.{obs_o_col}{max_row + 1}] * [.{obs_o_col}{max_row + 2}]")
                subtotal_row.addElement(observer_tc)
                subtotal_row.addElement(CoveredTableCell())

        table.addElement(TableRow())

        if self.object.participations.count() == 5:
            # 5-teams pools are separated in two different objects.
            # So, displaying the ranking may don't make any sens. We don't display it for this reason.
            scores_row = TableRow()
            table.addElement(scores_row)
            score_tc = TableCell(valuetype="string")
            score_tc.addElement(P(text="Le classement d'une poule à 5 n'est pas disponible sur le tableur, "
                                       "puisque les notes de l'autre salle sont manquantes.\n"
                                       "Merci de vous fier au site, ou bien au Google Sheets."))
            scores_row.addElement(score_tc)
        else:
            # Compute the total scores in a new table
            scores_header = TableRow()
            table.addElement(scores_header)
            team_tc = TableCell(valuetype="string", stylename=title_style_topbotleft)
            team_tc.addElement(P(text=_("Team")))
            team_tc.setAttribute('numbercolumnsspanned', "2")
            scores_header.addElement(team_tc)
            problem_tc = TableCell(valuetype="string", stylename=title_style_topbot)
            problem_tc.addElement(P(text=_("Problem")))
            scores_header.addElement(problem_tc)
            total_tc = TableCell(valuetype="string", stylename=title_style_topbot)
            total_tc.addElement(P(text=_("Total")))
            scores_header.addElement(total_tc)
            rank_tc = TableCell(valuetype="string", stylename=title_style_topbotright)
            rank_tc.addElement(P(text=_("Rank")))
            scores_header.addElement(rank_tc)

            sorted_participations = sorted(self.object.participations.all(), key=lambda p: -self.object.average(p))
            for passage in self.object.passages.all():
                team_row = TableRow()
                table.addElement(team_row)

                team_tc = TableCell(valuetype="string",
                                    stylename=style_botleft if passage.position == pool_size else style_left)
                team_tc.addElement(P(text=f"{passage.reporter.team.name} ({passage.reporter.team.trigram})"))
                team_tc.setAttribute('numbercolumnsspanned', "2")
                team_row.addElement(team_tc)

                problem_tc = TableCell(valuetype="string",
                                       stylename=style_bot if passage.position == pool_size else style)
                problem_tc.addElement(P(text=_("Problem #{problem}").format(problem=passage.solution_number)))
                problem_tc.setAttribute("formula", f"of:=[.B{3 + passage_width * (passage.position - 1)}]")
                team_row.addElement(problem_tc)

                reporter_pos = passage.position - 1
                opponent_pos = self.object.passages.get(opponent=passage.reporter).position - 1
                reviewer_pos = self.object.passages.get(reviewer=passage.reporter).position - 1
                observer_pos = self.object.passages.get(observer=passage.reporter).position - 1 \
                    if has_observer else None

                score_tc = TableCell(valuetype="float", value=self.object.average(passage.reporter),
                                     stylename=style_bot if passage.position == pool_size else style)
                score_tc.addElement(P(text=self.object.average(passage.reporter)))
                formula = "of:="
                formula += getcol(min_column + reporter_pos * passage_width) + str(max_row + 3)  # Reporter
                formula += " + " + getcol(min_column + opponent_pos * passage_width + 2) + str(max_row + 3)  # Opponent
                formula += " + " + getcol(min_column + reviewer_pos * passage_width + 4) + str(max_row + 3)  # Reviewer
                if has_observer:
                    # Observer
                    formula += " + " + getcol(min_column + observer_pos * passage_width + 6) + str(max_row + 3)
                score_tc.setAttribute("formula", formula)
                team_row.addElement(score_tc)

                score_col = 'C'
                rank_tc = TableCell(valuetype="float", value=sorted_participations.index(passage.reporter) + 1,
                                    stylename=style_botright if passage.position == pool_size else style_right)
                rank_tc.addElement(P(text=str(sorted_participations.index(passage.reporter) + 1)))
                rank_tc.setAttribute("formula", f"of:=RANK([.{score_col}{max_row + 5 + passage.position}]; "
                                                f"[.{score_col}${max_row + 6}]:"
                                                f"[.{score_col}${max_row + 5 + pool_size}])")
                team_row.addElement(rank_tc)

        table.addElement(TableRow())

        # Add small instructions
        instructions_tr = TableRow()
        table.addElement(instructions_tr)
        instructions_tc = TableCell()
        instructions_tc.addElement(P(text="Merci de ne pas toucher aux noms des juré⋅es.\n"
                                          "Si nécessaire, faites les modifications sur le site\n"
                                          "et récupérez le nouveau template.\n"
                                          "N'entrez que des notes entières.\n"
                                          "Ne retirez pas de 0 : toute ligne incomplète sera ignorée.\n"
                                          "Dans le cadre de poules à 5, laissez des 0 en face des\n"
                                          "juré⋅es qui ne sont pas dans le passage souhaité,\n"
                                          "et remplissez uniquement les notes nécessaires dans le tableau.\n"
                                          "Les moyennes calculées ignorent les 0, donc pas d'inquiétude."))
        instructions_tr.addElement(instructions_tc)

        # Save the sheet in a temporary file and send it in the response
        doc.save('/tmp/notes.ods')

        return FileResponse(streaming_content=open("/tmp/notes.ods", "rb"),
                            content_type="application/vnd.oasis.opendocument.spreadsheet",
                            filename=f"{_('Notation sheet')} - {self.object.tournament.name} "
                                     f"- {_('Pool')} {self.object.short_name}.ods")


class NotationSheetTemplateView(VolunteerMixin, DetailView):
    """
    Generate a PDF from a LaTeX template for the notation papers.
    """
    model = Pool

    def dispatch(self, request, *args, **kwargs):
        self.object = self.get_object()

        if request.user.is_authenticated and \
                (request.user.registration.is_admin or request.user.registration.is_volunteer
                 and (self.object.tournament in request.user.registration.organized_tournaments.all()
                      or request.user.registration == self.object.jury_president)):
            return super().dispatch(request, *args, **kwargs)

        return self.handle_no_permission()

    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)

        passages = self.object.passages.all()

        context['passages'] = passages
        context['esp'] = passages.count() * '&'
        if self.request.user.registration in self.object.juries.all() and 'blank' not in self.request.GET:
            context['jury'] = self.request.user.registration
        context['tfjm_number'] = timezone.now().year - settings.FIRST_EDITION + 1
        return context

    def render_to_response(self, context, **response_kwargs):
        translation.activate(settings.PREFERRED_LANGUAGE_CODE)

        template_name = self.get_template_names()[0]
        tex = render_to_string(template_name, context=context, request=self.request)
        temp_dir = mkdtemp()
        with open(os.path.join(temp_dir, "texput.tex"), "w") as f:
            f.write(tex)
        process = subprocess.Popen(["pdflatex", "-interaction=nonstopmode", f"-output-directory={temp_dir}",
                                    os.path.join(temp_dir, "texput.tex"), ])
        process.wait()
        return FileResponse(streaming_content=open(os.path.join(temp_dir, "texput.pdf"), "rb"),
                            content_type="application/pdf",
                            filename=template_name.split("/")[-1][:-3] + "pdf")


class ScaleNotationSheetTemplateView(NotationSheetTemplateView):
    def get_template_names(self):
        return [f"participation/tex/scale_{settings.TFJM_APP.lower()}.tex"]


class FinalNotationSheetTemplateView(NotationSheetTemplateView):
    template_name = "participation/tex/final_sheet.tex"


class NotationSheetsArchiveView(VolunteerMixin, DetailView):
    @property
    def model(self):
        return Pool if 'pool_id' in self.kwargs else Tournament

    @property
    def pk_url_kwarg(self):
        return 'pool_id' if 'pool_id' in self.kwargs else 'tournament_id'

    def dispatch(self, request, *args, **kwargs):
        if not request.user.is_authenticated:
            return self.handle_no_permission()

        reg = request.user.registration

        if 'pool_id' in kwargs:
            pool = self.get_object()
            tournament = pool.tournament
            if reg.is_admin or reg.is_volunteer \
                    and (tournament in reg.organized_tournaments.all() or reg in pool.juries.all()):
                return super().dispatch(request, *args, **kwargs)
        else:
            tournament = self.get_object()
            if reg.is_admin or reg.is_volunteer and tournament in reg.organized_tournaments.all():
                return super().dispatch(request, *args, **kwargs)

        return self.handle_no_permission()

    def get(self, request, *args, **kwargs):
        translation.activate(settings.PREFERRED_LANGUAGE_CODE)

        if 'pool_id' in kwargs:
            pool = self.get_object()
            tournament = pool.tournament
            pools = [pool]
            filename = _("Notation sheets of pool {pool} of {tournament}.zip") \
                .format(pool=pool.short_name, tournament=tournament.name)
        else:
            tournament = self.get_object()
            pools = tournament.pools.all()
            filename = _("Notation sheets of {tournament}.zip").format(tournament=tournament.name)

        output = BytesIO()
        with ZipFile(output, "w") as zf:
            for pool in pools:
                prefix = f"{pool.short_name}/" if len(pools) > 1 else ""
                for template_name in [f"scale_{settings.TFJM_APP.lower()}", "final_sheet"]:
                    juries = list(pool.juries.all()) + [None]

                    for jury in juries:
                        if jury is not None and template_name.startswith("scale"):
                            continue

                        context = {'jury': jury, 'pool': pool,
                                   'tfjm_number': timezone.now().year - settings.FIRST_EDITION + 1}

                        passages = pool.passages.all()
                        context['passages'] = passages
                        context['esp'] = passages.count() * '&'

                        tex = render_to_string(f"participation/tex/{template_name}.tex",
                                               context=context, request=self.request)
                        temp_dir = mkdtemp()
                        with open(os.path.join(temp_dir, "texput.tex"), "w") as f:
                            f.write(tex)

                        process = subprocess.Popen(
                            ["pdflatex", "-interaction=nonstopmode", f"-output-directory={temp_dir}",
                             os.path.join(temp_dir, "texput.tex"), ])
                        process.wait()

                        sheet_name = f"Barème pour la poule {pool.short_name}" if template_name.startswith("scale") \
                            else (f"Feuille de notation pour la poule {pool.short_name}"
                                  f" - {str(jury) if jury else 'Vierge'}")

                        zf.write(os.path.join(temp_dir, "texput.pdf"),
                                 f"{prefix}{sheet_name}.pdf")

        response = HttpResponse(content_type="application/zip")
        response["Content-Disposition"] = f"attachment; filename=\"{filename}\""
        response.write(output.getvalue())
        return response


@method_decorator(csrf_exempt, name='dispatch')
class GSheetNotificationsView(View):
    async def post(self, request, *args, **kwargs):
        if not await Tournament.objects.filter(pk=kwargs['pk']).aexists():
            return HttpResponse(status=404)

        tournament = await Tournament.objects.prefetch_related('participation_set', 'pools').aget(pk=kwargs['pk'])
        now = localtime(timezone.now())
        expected_channel_id = sha1(f"{tournament.name}-{now.date()}-{request.site.domain}".encode()) \
            .hexdigest()

        if request.headers['X-Goog-Channel-ID'] != expected_channel_id:
            raise ValueError(f"Invalid channel ID: {request.headers['X-Goog-Channel-ID']}")

        if request.headers['X-Goog-Resource-State'] != 'update' \
                or 'content' not in request.headers['X-Goog-Changed'].split(','):
            return HttpResponse(status=204)

        # Run the parsing in dedicated executors since it takes time
        executor = ThreadPoolExecutor()
        async for pool in tournament.pools.prefetch_related('participations', 'passages__notes', 'juries').all():
            asyncio.get_event_loop().run_in_executor(executor, pool.parse_spreadsheet)
        asyncio.get_event_loop().run_in_executor(executor, tournament.parse_tweaks_spreadsheets)

        return HttpResponse(status=204)


class PassageDetailView(LoginRequiredMixin, DetailView):
    model = Passage

    def dispatch(self, request, *args, **kwargs):
        if not request.user.is_authenticated:
            return self.handle_no_permission()
        reg = request.user.registration
        passage = self.get_object()
        if reg.is_admin or reg.is_volunteer \
                and (self.get_object().pool.tournament in reg.organized_tournaments.all()
                     or reg in passage.pool.juries.all()
                     or reg.pools_presided.filter(tournament=passage.pool.tournament).exists()) \
                or reg.participates and reg.team \
                and reg.team.participation in [passage.reporter, passage.opponent, passage.reviewer, passage.observer]:
            return super().dispatch(request, *args, **kwargs)
        return self.handle_no_permission()

    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)
        reg = self.request.user.registration
        passage = self.object
        if reg in passage.pool.juries.all():
            context["my_note"] = Note.objects.get_or_create(passage=passage, jury=self.request.user.registration)[0]
        if reg.is_volunteer:
            notes = passage.notes.all()
            if not reg.is_admin \
                    and (reg != passage.pool.jury_president
                         or reg not in passage.pool.tournament.organizers.all()):
                notes = [note for note in notes if note.has_any_note() or note.jury == reg]
            context["notes"] = NoteTable(notes)

        if 'notes' in context and not self.request.user.registration.is_admin:
            context['notes']._sequence.remove('update')

            context['notes'].columns['reporter_writing'].column.verbose_name += f" ({passage.reporter.team.trigram})"
            context['notes'].columns['reporter_oral'].column.verbose_name += f" ({passage.reporter.team.trigram})"
            context['notes'].columns['opponent_writing'].column.verbose_name += f" ({passage.opponent.team.trigram})"
            context['notes'].columns['opponent_oral'].column.verbose_name += f" ({passage.opponent.team.trigram})"
            context['notes'].columns['reviewer_writing'].column.verbose_name += f" ({passage.reviewer.team.trigram})"
            context['notes'].columns['reviewer_oral'].column.verbose_name += f" ({passage.reviewer.team.trigram})"

        return context


class PassageUpdateView(VolunteerMixin, UpdateView):
    model = Passage
    form_class = PassageForm

    def dispatch(self, request, *args, **kwargs):
        if not request.user.is_authenticated:
            return self.handle_no_permission()

        if request.user.registration.is_admin or request.user.registration.is_volunteer \
                and (self.get_object().pool.tournament in request.user.registration.organized_tournaments.all()
                     or request.user.registration in self.get_object().pool.juries.all()):
            return super().dispatch(request, *args, **kwargs)

        return self.handle_no_permission()


class WrittenReviewUploadView(LoginRequiredMixin, FormView):
    template_name = "participation/upload_written_review.html"
    form_class = WrittenReviewForm

    def dispatch(self, request, *args, **kwargs):
        if not request.user.is_authenticated or not request.user.registration.participates:
            return self.handle_no_permission()

        qs = Passage.objects.filter(pk=self.kwargs["pk"])
        if not qs.exists():
            raise Http404
        self.participation = self.request.user.registration.team.participation
        self.passage = qs.get()

        if self.participation \
                and self.participation not in [self.passage.opponent, self.passage.reviewer, self.passage.observer]:
            return self.handle_no_permission()

        return super().dispatch(request, *args, **kwargs)

    def form_valid(self, form):
        """
        When a solution is submitted, it replaces a previous solution if existing,
        otherwise it creates a new solution.
        It is discriminating whenever the team is selected for the final tournament or not.
        """
        form_syn = form.instance
        form_syn.type = 1 if self.participation == self.passage.opponent \
            else 2 if self.participation == self.passage.reviewer else 3
        syn_qs = WrittenReview.objects.filter(participation=self.participation,
                                              passage=self.passage,
                                              type=form_syn.type).all()

        deadline = self.passage.pool.tournament.reviews_first_phase_limit if self.passage.pool.round == 1 \
            else self.passage.pool.tournament.reviews_second_phase_limit
        if syn_qs.exists() and timezone.now() > deadline:
            form.add_error(None, _("You can't upload a written review after the deadline."))
            return self.form_invalid(form)

        # Drop previous solution if existing
        for syn in syn_qs.all():
            syn.file.delete()
            syn.save()
            syn.delete()
        form_syn.participation = self.participation
        form_syn.passage = self.passage
        form_syn.save()
        return super().form_valid(form)

    def get_success_url(self):
        return reverse_lazy("participation:passage_detail", args=(self.passage.pk,))


class NoteUpdateView(VolunteerMixin, UpdateView):
    model = Note
    form_class = NoteForm

    def dispatch(self, request, *args, **kwargs):
        if not request.user.is_authenticated:
            return self.handle_no_permission()

        reg = request.user.registration
        note = self.get_object()
        if reg.is_admin or reg.is_volunteer and (note.jury == reg or note.passage.pool.jury_president == reg):
            return super().dispatch(request, *args, **kwargs)

        return self.handle_no_permission()

    def get_form(self, form_class=None):
        form = super().get_form(form_class)
        form.fields['reporter_writing'].label += f" ({self.object.passage.reporter.team.trigram})"
        form.fields['reporter_oral'].label += f" ({self.object.passage.reporter.team.trigram})"
        form.fields['opponent_writing'].label += f" ({self.object.passage.opponent.team.trigram})"
        form.fields['opponent_oral'].label += f" ({self.object.passage.opponent.team.trigram})"
        form.fields['reviewer_writing'].label += f" ({self.object.passage.reviewer.team.trigram})"
        form.fields['reviewer_oral'].label += f" ({self.object.passage.reviewer.team.trigram})"
        form.fields['observer_writing'].label += f" ({self.object.passage.observer.team.trigram})"
        form.fields['observer_oral'].label += f" ({self.object.passage.observer.team.trigram})"
        return form

    def form_valid(self, form):
        ret = super().form_valid(form)
        self.object.update_spreadsheet()
        return ret