import csv
import datetime
import os
import zipfile
from dataclasses import dataclass
from datetime import timedelta
from io import BytesIO, StringIO
from typing import TYPE_CHECKING, Any, Literal, Sequence, TypedDict, cast
from uuid import UUID

import boto3
import markupsafe
from flask import abort, current_app, flash, make_response, redirect, send_file, url_for
from flask.typing import ResponseReturnValue
from flask_admin import AdminIndexView, BaseView, expose
from sqlalchemy import text

from app.common.data.interfaces.collections import (
    get_collection,
    get_collections_by_status_excluding_draft_grants,
    get_collections_with_dates_near_today_excluding_draft_grants,
    get_overdue_open_collections_excluding_draft_grants,
    update_collection,
)
from app.common.data.interfaces.data_analysis import get_unique_users_count_for_live_grant_recipients
from app.common.data.interfaces.data_sets import get_referenced_grant_recipient_data_sources_for_collection
from app.common.data.interfaces.exceptions import (
    CollectionChronologyError,
    GrantMustBeLiveError,
    GrantRecipientUsersRequiredError,
    StateTransitionError,
)
from app.common.data.interfaces.grant_recipients import (
    create_grant_recipients,
    get_grant_recipient_data_providers,
    get_grant_recipient_data_providers_count,
    get_grant_recipients,
    get_grant_recipients_count,
    get_grant_recipients_with_outstanding_submissions_for_collection,
)
from app.common.data.interfaces.grants import get_all_grants, get_grant, update_grant
from app.common.data.interfaces.organisations import get_organisation_count, get_organisations, upsert_organisations
from app.common.data.interfaces.user import (
    add_permissions_to_user,
    get_certifiers_by_organisation,
    get_grant_override_certifiers_by_organisation,
    get_user,
    get_user_by_email,
    get_users_with_permission,
    remove_permissions_from_user,
    upsert_user_by_email,
)
from app.common.data.types import (
    PRE_AWARD_COLLECTIONS,
    CollectionAdminEmailTypeEnum,
    CollectionStatusEnum,
    GrantRecipientModeEnum,
    GrantStatusEnum,
    OrganisationModeEnum,
    RoleEnum,
    SubmissionEventType,
    SubmissionModeEnum,
    TimelineEvent,
    TraceLevelEnum,
)
from app.common.filters import format_date
from app.common.forms import GenericSubmitForm
from app.common.helpers.collections import SubmissionHelper
from app.common.helpers.dates import subtract_business_days
from app.common.helpers.feature_flags import FeatureFlags, SessionFeatureFlag
from app.common.helpers.request_tracing import (
    REQUEST_TRACING_COOKIE_NAME,
    REQUEST_TRACING_TTL,
    encode_levels,
    get_tracing_levels,
)
from app.deliver_grant_funding.admin.forms import (
    PlatformAdminAddSingleDataProviderForm,
    PlatformAdminAddTestGrantRecipientUserForm,
    PlatformAdminBulkCreateGrantRecipientsForm,
    PlatformAdminBulkCreateOrganisationsForm,
    PlatformAdminCreateCertifiersForm,
    PlatformAdminCreateGrantOverrideCertifiersForm,
    PlatformAdminCreateGrantRecipientDataProvidersForm,
    PlatformAdminForceTracingForm,
    PlatformAdminMakeCollectionLiveForm,
    PlatformAdminMakeGrantLiveForm,
    PlatformAdminMarkAsOnboardingForm,
    PlatformAdminRevokeCertifiersForm,
    PlatformAdminRevokeGrantOverrideCertifiersForm,
    PlatformAdminRevokeGrantRecipientUsersForm,
    PlatformAdminScheduleCollectionForm,
    PlatformAdminSelectCollectionForm,
    PlatformAdminSelectGrantForCollectionLifecycleForm,
    PlatformAdminSetCollectionReportingDatesForm,
    PlatformAdminSetCollectionSubmissionDatesForm,
    PlatformAdminSetPrivacyPolicyForm,
    PlatformAdminSetReminderDaysForm,
    PlatformAdminToggleFeatureFlagForm,
)
from app.deliver_grant_funding.admin.mixins import (
    FlaskAdminPlatformAdminAccessibleMixin,
    FlaskAdminPlatformAdminDataAnalystAccessibleMixin,
    FlaskAdminPlatformAdminGrantLifecycleManagerAccessibleMixin,
    FlaskAdminPlatformMemberAccessibleMixin,
)
from app.extensions import auto_commit_after_request, db, notification_service

if TYPE_CHECKING:
    from app.common.data.models import Grant, Organisation
    from app.common.data.models_user import User


class PlatformAdminIndexView(FlaskAdminPlatformMemberAccessibleMixin, AdminIndexView):
    @expose("/")
    def index(self) -> Any:
        today = datetime.date.today()
        seven_days_ago = today - datetime.timedelta(days=7)
        seven_days_ahead = today + datetime.timedelta(days=7)

        live_grants = get_all_grants(statuses=[GrantStatusEnum.LIVE])
        onboarding_grants = get_all_grants(statuses=[GrantStatusEnum.ONBOARDING])
        scheduled_collections = get_collections_by_status_excluding_draft_grants([CollectionStatusEnum.SCHEDULED])
        open_collections = get_collections_by_status_excluding_draft_grants([CollectionStatusEnum.OPEN])
        overdue_collections = get_overdue_open_collections_excluding_draft_grants()

        nearby_collections = get_collections_with_dates_near_today_excluding_draft_grants(past_days=7, future_days=7)
        timeline_events: list[TimelineEvent] = []
        for collection in nearby_collections:
            if (
                collection.submission_period_start_date
                and seven_days_ago <= collection.submission_period_start_date <= seven_days_ahead
            ):
                timeline_events.append(
                    {
                        "date": collection.submission_period_start_date,
                        "type": "opening",
                        "collection": collection,
                        "is_today": collection.submission_period_start_date == today,
                        "is_past": collection.submission_period_start_date < today,
                    }
                )

            if collection.submission_period_end_date:
                send_overdue_emails_at = collection.submission_period_end_date + timedelta(days=1)

                if seven_days_ago <= send_overdue_emails_at <= seven_days_ahead:
                    timeline_events.append(
                        {
                            "date": send_overdue_emails_at,
                            "type": "closing",
                            "collection": collection,
                            "is_today": send_overdue_emails_at == today,
                            "is_past": send_overdue_emails_at < today,
                        }
                    )

        for collection in [*open_collections, *scheduled_collections]:
            if not collection.submission_period_end_date:
                continue
            reminder_date = subtract_business_days(
                collection.submission_period_end_date,
                collection.reminder_email_business_days_before_closing,
            )
            if seven_days_ago <= reminder_date <= seven_days_ahead:
                timeline_events.append(
                    {
                        "date": reminder_date,
                        "type": "reminder",
                        "collection": collection,
                        "is_today": reminder_date == today,
                        "is_past": reminder_date < today,
                    }
                )

        timeline_events.sort(key=lambda e: e["date"])

        return self.render(
            "deliver_grant_funding/admin/dashboard.html",
            live_grants=live_grants,
            onboarding_grants=onboarding_grants,
            scheduled_collections=scheduled_collections,
            open_collections=open_collections,
            overdue_collections=overdue_collections,
            timeline_events=timeline_events,
        )


class PlatformAdminCollectionLifecycleView(FlaskAdminPlatformAdminGrantLifecycleManagerAccessibleMixin, BaseView):
    @expose("/", methods=["GET", "POST"])
    def index(self) -> Any:
        form = PlatformAdminSelectGrantForCollectionLifecycleForm(grants=get_all_grants())
        if form.validate_on_submit():
            grant = get_grant(form.grant_id.data, with_all_collections=True)
            if len(grant.collections) == 1:
                return redirect(
                    url_for("collection_lifecycle.tasklist", grant_id=grant.id, collection_id=grant.collections[0].id)
                )
            else:
                return redirect(url_for("collection_lifecycle.select_collection", grant_id=grant.id))

        return self.render("deliver_grant_funding/admin/select-grant-for-collection-lifecycle.html", form=form)

    @expose("/<uuid:grant_id>/select-collection", methods=["GET", "POST"])
    def select_collection(self, grant_id: UUID) -> Any:
        grant = get_grant(grant_id, with_all_collections=True)
        form = PlatformAdminSelectCollectionForm(collections=grant.collections)
        if form.validate_on_submit():
            return redirect(
                url_for("collection_lifecycle.tasklist", grant_id=grant.id, collection_id=form.collection_id.data)
            )

        return self.render(
            "deliver_grant_funding/admin/select-collection-for-collection-lifecycle.html", form=form, grant=grant
        )

    @expose("/<uuid:grant_id>/<uuid:collection_id>")
    def tasklist(self, grant_id: UUID, collection_id: UUID) -> Any:
        grant = get_grant(grant_id, with_all_collections=True)
        collection = get_collection(collection_id, grant_id=grant_id)
        organisation_count = get_organisation_count()
        certifiers_count = len(get_users_with_permission(RoleEnum.CERTIFIER, grant_id=None))
        grant_recipients_count = get_grant_recipients_count(grant=grant)
        grant_recipient_users_count, recipients_missing_data_providers = get_grant_recipient_data_providers_count(
            grant=grant
        )
        grant_override_certifiers_count = len(
            get_users_with_permission(
                RoleEnum.CERTIFIER, grant_id=grant_id, organisation_mode=OrganisationModeEnum.LIVE
            )
        )

        # Test entity counts
        test_users_count, _ = get_grant_recipient_data_providers_count(grant=grant, mode=GrantRecipientModeEnum.TEST)

        return self.render(
            "deliver_grant_funding/admin/collection-lifecycle-tasklist.html",
            grant=grant,
            collection=collection,
            organisation_count=organisation_count,
            certifiers_count=certifiers_count,
            grant_recipients_count=grant_recipients_count,
            grant_recipient_users_count=grant_recipient_users_count,
            grant_override_certifiers_count=grant_override_certifiers_count,
            test_users_count=test_users_count,
        )

    @expose("/<uuid:grant_id>/<uuid:collection_id>/make-live", methods=["GET", "POST"])
    @auto_commit_after_request
    def make_live(self, grant_id: UUID, collection_id: UUID) -> Any:
        grant = get_grant(grant_id)
        collection = get_collection(collection_id, grant_id=grant_id)

        if grant.status == GrantStatusEnum.LIVE:
            flash(f"{grant.name} is already live.")
            return redirect(url_for("collection_lifecycle.tasklist", grant_id=grant.id, collection_id=collection.id))

        form = PlatformAdminMakeGrantLiveForm()
        if form.validate_on_submit():
            try:
                update_grant(grant, status=GrantStatusEnum.LIVE)
                flash(f"{grant.name} is now live.", "success")
                return redirect(
                    url_for("collection_lifecycle.tasklist", grant_id=grant.id, collection_id=collection.id)
                )
            except StateTransitionError:
                form.form_errors.append("Unable to make grant live")

        return self.render(
            "deliver_grant_funding/admin/confirm-make-grant-live.html", form=form, grant=grant, collection=collection
        )

    @expose("/<uuid:grant_id>/<uuid:collection_id>/mark-as-onboarding", methods=["GET", "POST"])
    @auto_commit_after_request
    def mark_as_onboarding(self, grant_id: UUID, collection_id: UUID) -> Any:
        grant = get_grant(grant_id)
        collection = get_collection(collection_id, grant_id=grant_id)

        if grant.status in [GrantStatusEnum.ONBOARDING, GrantStatusEnum.LIVE]:
            flash(f"{grant.name} is already marked as onboarding.")
            return redirect(url_for("collection_lifecycle.tasklist", grant_id=grant.id, collection_id=collection.id))

        form = PlatformAdminMarkAsOnboardingForm()
        if form.validate_on_submit():
            update_grant(grant, status=GrantStatusEnum.ONBOARDING)
            flash(f"{grant.name} is now marked as onboarding.", "success")
            return redirect(url_for("collection_lifecycle.tasklist", grant_id=grant.id, collection_id=collection.id))

        return self.render(
            "deliver_grant_funding/admin/confirm-make-grant-active-onboarding.html",
            form=form,
            grant=grant,
            collection=collection,
        )

    @expose("/<uuid:grant_id>/<uuid:collection_id>/set-privacy-policy", methods=["GET", "POST"])
    @auto_commit_after_request
    def set_privacy_policy(self, grant_id: UUID, collection_id: UUID) -> Any:
        grant = get_grant(grant_id)
        collection = get_collection(collection_id, grant_id=grant_id)

        form = PlatformAdminSetPrivacyPolicyForm(obj=grant)
        if form.validate_on_submit():
            update_grant(grant, privacy_policy_markdown=form.privacy_policy_markdown.data)
            flash(f"Privacy policy updated for {grant.name}.", "success")
            return redirect(url_for("collection_lifecycle.tasklist", grant_id=grant.id, collection_id=collection.id))

        return self.render(
            "deliver_grant_funding/admin/set-privacy-policy.html",
            form=form,
            grant=grant,
            collection=collection,
        )

    @expose("/<uuid:grant_id>/<uuid:collection_id>/set-up-organisations", methods=["GET", "POST"])
    @auto_commit_after_request
    def set_up_organisations(self, grant_id: UUID, collection_id: UUID) -> Any:
        grant = get_grant(grant_id)
        collection = get_collection(collection_id, grant_id=grant_id)
        form = PlatformAdminBulkCreateOrganisationsForm()
        if form.validate_on_submit():
            organisations = form.get_normalised_organisation_data()

            upsert_organisations(organisations, cascade_to_test_mode_organisations=True)

            flash(
                f"Created or updated {len(organisations)} organisations and {len(organisations)} test organisations.",
                "success",
            )
            return redirect(url_for("collection_lifecycle.tasklist", grant_id=grant.id, collection_id=collection.id))

        return self.render(
            "deliver_grant_funding/admin/set-up-organisations.html",
            form=form,
            grant=grant,
            collection=collection,
            delta_service_desk_url=current_app.config["DELTA_SERVICE_DESK_URL"],
        )

    @expose("/<uuid:grant_id>/<uuid:collection_id>/set-up-global-certifiers", methods=["GET", "POST"])
    @auto_commit_after_request
    def set_up_global_certifiers(self, grant_id: UUID, collection_id: UUID) -> Any:
        grant = get_grant(grant_id)
        collection = get_collection(collection_id, grant_id=grant_id)
        organisations = get_organisations(can_manage_grants=False)
        certifiers_by_org = get_certifiers_by_organisation()

        form = PlatformAdminCreateCertifiersForm(organisations=organisations)
        if form.validate_on_submit():
            certifiers_data = form.get_normalised_certifiers_data()

            organisation_names_to_ids = {organisation.name: organisation.id for organisation in organisations}
            count = 0
            for org_name, full_name, email_address in certifiers_data:
                org_id = organisation_names_to_ids.get(org_name)
                if org_id:
                    user = upsert_user_by_email(email_address=email_address, name=full_name)
                    add_permissions_to_user(user=user, permissions=[RoleEnum.CERTIFIER], organisation_id=org_id)
                    count += 1

            flash(f"Created or updated {count} certifier(s).", "success")
            return redirect(url_for("collection_lifecycle.tasklist", grant_id=grant.id, collection_id=collection.id))

        return self.render(
            "deliver_grant_funding/admin/set-up-global-certifiers.html",
            form=form,
            grant=grant,
            collection=collection,
            certifiers_by_org=certifiers_by_org,
            delta_service_desk_url=current_app.config["DELTA_SERVICE_DESK_URL"],
        )

    @expose("/<uuid:grant_id>/<uuid:collection_id>/revoke-global-certifiers", methods=["GET", "POST"])
    @auto_commit_after_request
    def revoke_global_certifiers(self, grant_id: UUID, collection_id: UUID) -> Any:
        grant = get_grant(grant_id)
        collection = get_collection(collection_id, grant_id=grant_id)

        organisations = get_organisations()
        certifiers_by_org = get_certifiers_by_organisation()
        form = PlatformAdminRevokeCertifiersForm(organisations=organisations)

        if form.validate_on_submit():
            organisation_id = UUID(form.organisation_id.data)
            assert form.email.data
            email = form.email.data

            user = get_user_by_email(email)
            if not user:
                flash(f"User with email '{email}' does not exist.", "error")
            else:
                certifiers = get_users_with_permission(
                    RoleEnum.CERTIFIER, organisation_id=organisation_id, grant_id=None
                )
                if user not in certifiers:
                    flash(
                        f"User '{user.name}' ({email}) is not a global certifier for the selected organisation.",
                        "error",
                    )
                else:
                    remove_permissions_from_user(
                        user=user,
                        permissions=[RoleEnum.CERTIFIER],
                        organisation_id=organisation_id,
                        grant_id=None,
                    )
                    flash(
                        f"Successfully revoked certifier access for {user.name} ({email}).",
                        "success",
                    )
                    return redirect(
                        url_for(
                            "collection_lifecycle.revoke_global_certifiers",
                            grant_id=grant.id,
                            collection_id=collection.id,
                        )
                    )

        return self.render(
            "deliver_grant_funding/admin/revoke-global-certifiers.html",
            form=form,
            grant=grant,
            collection=collection,
            certifiers_by_org=certifiers_by_org,
        )

    @expose("/<uuid:grant_id>/<uuid:collection_id>/override-grant-certifiers", methods=["GET", "POST"])
    @auto_commit_after_request
    def override_grant_certifiers(self, grant_id: UUID, collection_id: UUID) -> Any:
        grant = get_grant(grant_id)
        collection = get_collection(collection_id, grant_id=grant_id)
        grant_recipients = get_grant_recipients(grant=grant, with_data_providers=True, with_organisations=True)

        certifiers_by_org = get_grant_override_certifiers_by_organisation(
            grant_id=grant_id, organisation_mode=OrganisationModeEnum.LIVE
        )

        form = PlatformAdminCreateGrantOverrideCertifiersForm(grant_recipients=grant_recipients)

        if form.validate_on_submit():
            organisation_id = UUID(form.organisation_id.data)
            assert form.full_name.data
            full_name = form.full_name.data
            assert form.email.data
            email_address = form.email.data

            user = upsert_user_by_email(email_address=email_address, name=full_name)
            add_permissions_to_user(
                user=user,
                permissions=[RoleEnum.CERTIFIER],
                organisation_id=organisation_id,
                grant_id=grant_id,
            )

            flash(
                f"Successfully added {full_name} ({email_address}) as a grant-specific certifier.",
                "success",
            )
            return redirect(
                url_for(
                    "collection_lifecycle.override_grant_certifiers",
                    grant_id=grant.id,
                    collection_id=collection.id,
                )
            )

        return self.render(
            "deliver_grant_funding/admin/override-grant-certifiers.html",
            form=form,
            grant=grant,
            collection=collection,
            certifiers_by_org=certifiers_by_org,
        )

    @expose("/<uuid:grant_id>/<uuid:collection_id>/revoke-grant-override-certifiers", methods=["GET", "POST"])
    @auto_commit_after_request
    def revoke_grant_override_certifiers(self, grant_id: UUID, collection_id: UUID) -> Any:
        grant = get_grant(grant_id)
        collection = get_collection(collection_id, grant_id=grant_id)
        grant_recipients = get_grant_recipients(grant=grant, with_data_providers=True, with_organisations=True)

        certifiers_by_org = get_grant_override_certifiers_by_organisation(grant_id=grant_id)

        form = PlatformAdminRevokeGrantOverrideCertifiersForm(grant_recipients=grant_recipients)

        if form.validate_on_submit():
            organisation_id = UUID(form.organisation_id.data)
            assert form.email.data
            email = form.email.data

            user = get_user_by_email(email)
            if not user:
                flash(f"User with email '{email}' does not exist.", "error")
            else:
                certifiers = get_users_with_permission(
                    RoleEnum.CERTIFIER, organisation_id=organisation_id, grant_id=grant_id
                )
                if user not in certifiers:
                    flash(
                        f"User '{user.name}' ({email}) is not a grant-specific certifier "
                        "for the selected organisation.",
                        "error",
                    )
                else:
                    remove_permissions_from_user(
                        user=user,
                        permissions=[RoleEnum.CERTIFIER],
                        organisation_id=organisation_id,
                        grant_id=grant_id,
                    )
                    flash(
                        f"Successfully revoked grant-specific certifier access for {user.name} ({email}).",
                        "success",
                    )
                    return redirect(
                        url_for(
                            "collection_lifecycle.revoke_grant_override_certifiers",
                            grant_id=grant.id,
                            collection_id=collection.id,
                        )
                    )

        return self.render(
            "deliver_grant_funding/admin/revoke-grant-override-certifiers.html",
            form=form,
            grant=grant,
            collection=collection,
            certifiers_by_org=certifiers_by_org,
        )

    @expose("/<uuid:grant_id>/<uuid:collection_id>/set-up-grant-recipients", methods=["GET", "POST"])
    @auto_commit_after_request
    def set_up_grant_recipients(self, grant_id: UUID, collection_id: UUID) -> Any:
        grant = get_grant(grant_id)
        collection = get_collection(collection_id, grant_id=grant_id)
        organisations = get_organisations(can_manage_grants=False)
        existing_grant_recipients = get_grant_recipients(grant=grant, with_data_providers=True, with_organisations=True)
        form = PlatformAdminBulkCreateGrantRecipientsForm(
            organisations=organisations,
            existing_grant_recipients=existing_grant_recipients,
            collection_type=collection.type,
        )

        if form.validate_on_submit():
            create_grant_recipients(grant=grant, organisation_ids=form.recipients.data, status=form.status.data)

            live_organisations = get_organisations(mode=OrganisationModeEnum.LIVE, with_ids=form.recipients.data)
            test_organisations = get_organisations(
                mode=OrganisationModeEnum.TEST,
                with_external_ids=list(set(org.external_id for org in live_organisations)),
            )
            test_organisation_ids = [org.id for org in test_organisations]
            create_grant_recipients(
                grant=grant,
                organisation_ids=test_organisation_ids,
                status=form.status.data,
                mode=GrantRecipientModeEnum.TEST,
            )

            # Set up grant team members as data providers/certifiers for the test grant recipients
            for test_organisation in test_organisations:
                for grant_team_member in grant.grant_team_users:
                    add_permissions_to_user(
                        grant_team_member,
                        permissions=[RoleEnum.DATA_PROVIDER, RoleEnum.CERTIFIER],
                        organisation_id=test_organisation.id,
                        grant_id=grant.id,
                    )
            recipients_count = len(form.recipients.data or [])
            flash(
                (
                    f"Created {recipients_count} grant recipients"
                    f" and {recipients_count} test grant recipients. "
                    f"All existing grant team members have been"
                    f" set up as data providers/certifiers for the test grant recipients."
                ),
                "success",
            )
            return redirect(url_for("collection_lifecycle.tasklist", grant_id=grant.id, collection_id=collection.id))

        return self.render(
            "deliver_grant_funding/admin/set-up-grant-recipients.html",
            grant=grant,
            collection=collection,
            grant_recipients=existing_grant_recipients,
            form=form,
        )

    @expose("/<uuid:grant_id>/<uuid:collection_id>/add-individual-data-providers", methods=["GET", "POST"])
    @auto_commit_after_request
    def add_individual_data_providers(self, grant_id: UUID, collection_id: UUID) -> Any:
        grant = get_grant(grant_id)
        collection = get_collection(collection_id, grant_id=grant_id)
        grant_recipients = get_grant_recipients(grant=grant, with_data_providers=True, with_organisations=True)
        data_providers_by_grant_recipient = {gr: gr.data_providers for gr in grant_recipients}

        form = PlatformAdminAddSingleDataProviderForm(collection=collection, grant_recipients=grant_recipients)
        if form.validate_on_submit():
            grant_recipient = next(gr for gr in grant_recipients if str(gr.id) == form.grant_recipient.data)
            user = upsert_user_by_email(email_address=form.email_address.data, name=form.full_name.data)
            add_permissions_to_user(
                user,
                permissions=[RoleEnum.DATA_PROVIDER],
                organisation_id=grant_recipient.organisation_id,
                grant_id=grant.id,
            )

            if form.send_notification_email.data:
                notification_service.send_access_report_opened(
                    email_address=user.email,
                    collection=collection,
                    grant_recipient=grant_recipient,
                    submission_helpers=[
                        SubmissionHelper(s) for s in grant_recipient.submissions if s.collection == collection
                    ],
                )
                flash(f"Successfully added {user.name} as a data provider and sent notification email.", "success")
            else:
                flash(f"Successfully added {user.name} as a data provider.", "success")

            return redirect(
                url_for(
                    "collection_lifecycle.tasklist",
                    grant_id=grant.id,
                    collection_id=collection.id,
                )
            )
        return self.render(
            "deliver_grant_funding/admin/add-individual-data-provider.html",
            form=form,
            grant=grant,
            collection=collection,
            data_providers_by_grant_recipient=data_providers_by_grant_recipient,
        )

    @expose("/<uuid:grant_id>/<uuid:collection_id>/add-bulk-data-providers", methods=["GET", "POST"])
    @auto_commit_after_request
    def add_bulk_data_providers(self, grant_id: UUID, collection_id: UUID) -> Any:
        grant = get_grant(grant_id)
        collection = get_collection(collection_id, grant_id=grant_id)
        grant_recipients = get_grant_recipients(grant=grant, with_data_providers=True, with_organisations=True)
        data_providers_by_grant_recipient = {gr: gr.data_providers for gr in grant_recipients}
        form = PlatformAdminCreateGrantRecipientDataProvidersForm(grant_recipients=grant_recipients)
        if form.validate_on_submit():
            grant_recipient_names_to_ids = {gr.organisation.name: gr.organisation.id for gr in grant_recipients}
            users_data = form.get_normalised_users_data()

            for org_name, full_name, email_address in users_data:
                org_id = grant_recipient_names_to_ids[org_name]
                user = upsert_user_by_email(email_address=email_address, name=full_name)
                add_permissions_to_user(
                    user, permissions=[RoleEnum.DATA_PROVIDER], organisation_id=org_id, grant_id=grant.id
                )

            noun = "data provider" if len(users_data) == 1 else "data providers"
            flash(
                f"Successfully set up {len(users_data)} grant recipient {noun}.",
                "success",
            )

            return redirect(
                url_for(
                    "collection_lifecycle.tasklist",
                    grant_id=grant.id,
                    collection_id=collection.id,
                )
            )

        return self.render(
            "deliver_grant_funding/admin/add-bulk-data-providers.html",
            form=form,
            grant=grant,
            collection=collection,
            data_providers_by_grant_recipient=data_providers_by_grant_recipient,
        )

    @expose("/<uuid:grant_id>/<uuid:collection_id>/set-up-test-grant-recipient-users", methods=["GET", "POST"])
    @auto_commit_after_request
    def set_up_test_grant_recipient_users(self, grant_id: UUID, collection_id: UUID) -> Any:
        grant = get_grant(grant_id)
        collection = get_collection(collection_id, grant_id=grant_id)

        # Get TEST grant recipients with their current data providers
        grant_recipients = get_grant_recipients(
            grant=grant, with_data_providers=True, with_organisations=True, mode=GrantRecipientModeEnum.TEST
        )

        orgs = get_organisations(can_manage_grants=True)
        if not orgs or len(orgs) > 1:
            raise Exception("Journey requires and only supports one managing organisation (MHCLG)")

        mhclg = orgs[0]
        mhclg_members = list(get_users_with_permission(RoleEnum.MEMBER, organisation_id=mhclg.id, grant_id=None))
        platform_admins = list(get_users_with_permission(RoleEnum.ADMIN, organisation_id=None, grant_id=None))
        mhclg_users = list(set(mhclg_members + platform_admins))

        # Initialize form with dropdown choices
        form = PlatformAdminAddTestGrantRecipientUserForm(
            grant_recipients=grant_recipients,
            mhclg_users=mhclg_users,
        )

        # Prepare data for template (existing users table)
        data_providers_by_grant_recipient = {gr: gr.data_providers for gr in grant_recipients}

        if form.validate_on_submit():
            grant_recipient_id = UUID(form.grant_recipient.data)
            grant_recipient = next(gr for gr in grant_recipients if gr.id == grant_recipient_id)

            user_id = UUID(form.mhclg_user.data)
            user = next(u for u in mhclg_users if u.id == user_id)

            # Add DATA_PROVIDER and CERTIFIER permissions
            add_permissions_to_user(
                user,
                permissions=[RoleEnum.DATA_PROVIDER, RoleEnum.CERTIFIER],
                organisation_id=grant_recipient.organisation_id,
                grant_id=grant.id,
            )

            # Flash success message
            flash(
                f"Added {user.name} as a data provider for {grant_recipient.organisation.name}",
                "success",
            )

            # Redirect to same page (clears form and shows updated existing users table)
            return redirect(
                url_for(
                    ".set_up_test_grant_recipient_users",
                    grant_id=grant_id,
                    collection_id=collection_id,
                )
            )

        return self.render(
            "deliver_grant_funding/admin/set-up-test-grant-recipient-users.html",
            grant=grant,
            collection=collection,
            form=form,
            data_providers_by_grant_recipient=data_providers_by_grant_recipient,
            grant_recipients=grant_recipients,
        )

    @expose("/<uuid:grant_id>/<uuid:collection_id>/revoke-grant-recipient-data-providers", methods=["GET", "POST"])
    @auto_commit_after_request
    def revoke_grant_recipient_data_providers(self, grant_id: UUID, collection_id: UUID) -> Any:
        grant = get_grant(grant_id)
        collection = get_collection(collection_id, grant_id=grant_id)

        grant_recipients_data_providers = get_grant_recipient_data_providers(grant)
        form = PlatformAdminRevokeGrantRecipientUsersForm(
            grant_recipients_data_providers=grant_recipients_data_providers
        )

        if form.validate_on_submit():
            revoked_count = 0
            assert form.grant_recipients_data_providers.data
            for user_role_id in form.grant_recipients_data_providers.data:
                user_id_str, org_id_str = user_role_id.split("|")
                user_id = UUID(user_id_str)
                org_id = UUID(org_id_str)

                if (
                    remove_permissions_from_user(
                        get_user(user_id),
                        permissions=[RoleEnum.MEMBER, RoleEnum.DATA_PROVIDER],
                        organisation_id=org_id,
                        grant_id=grant.id,
                    )
                    is None
                ):
                    revoked_count += 1

            if revoked_count > 0:
                data_provider = "data provider" if revoked_count == 1 else "data providers"
                flash(
                    f"Successfully revoked access for {revoked_count} {data_provider}.",
                    "success",
                )
            else:
                flash("No data providers were revoked.", "error")

            return redirect(
                url_for(
                    "collection_lifecycle.add_bulk_data_providers",
                    grant_id=grant.id,
                    collection_id=collection.id,
                )
            )

        return self.render(
            "deliver_grant_funding/admin/revoke-grant-recipient-data-providers.html",
            form=form,
            grant=grant,
            collection=collection,
        )

    @expose("/<uuid:grant_id>/<uuid:collection_id>/set-reporting-dates", methods=["GET", "POST"])
    @auto_commit_after_request
    def set_reporting_dates(self, grant_id: UUID, collection_id: UUID) -> Any:
        grant = get_grant(grant_id)
        collection = get_collection(collection_id, grant_id=grant_id)

        if collection.status != CollectionStatusEnum.DRAFT:
            flash(
                f"You cannot set reporting dates for {collection.name} because it is not in draft status.",
                "error",
            )
            return redirect(url_for("collection_lifecycle.tasklist", grant_id=grant.id, collection_id=collection.id))

        if collection.type in PRE_AWARD_COLLECTIONS:
            flash(
                f"You cannot set reporting dates for {collection.name} because it is not a monitoring report.",
                "error",
            )
            return redirect(url_for("collection_lifecycle.tasklist", grant_id=grant.id, collection_id=collection.id))

        form = PlatformAdminSetCollectionReportingDatesForm(obj=collection)

        if form.validate_on_submit():
            update_collection(
                collection,
                reporting_period_start_date=form.reporting_period_start_date.data,
                reporting_period_end_date=form.reporting_period_end_date.data,
            )

            flash(f"Updated reporting dates for {collection.name}.", "success")

            return redirect(url_for("collection_lifecycle.tasklist", grant_id=grant.id, collection_id=collection.id))

        return self.render(
            "deliver_grant_funding/admin/set-collection-dates.html",
            title="Set reporting dates",
            form=form,
            grant=grant,
            collection=collection,
        )

    @expose("/<uuid:grant_id>/<uuid:collection_id>/set-submission-dates", methods=["GET", "POST"])
    @auto_commit_after_request
    def set_submission_dates(self, grant_id: UUID, collection_id: UUID) -> Any:
        grant = get_grant(grant_id)
        collection = get_collection(collection_id, grant_id=grant_id)

        if collection.status != CollectionStatusEnum.DRAFT:
            flash(
                f"You cannot set submission dates for {collection.name} because it is not in draft status.",
                "error",
            )
            return redirect(url_for("collection_lifecycle.tasklist", grant_id=grant.id, collection_id=collection.id))

        form = PlatformAdminSetCollectionSubmissionDatesForm(obj=collection)

        if form.validate_on_submit():
            update_collection(
                collection,
                submission_period_start_date=form.submission_period_start_date.data,
                submission_period_end_date=form.submission_period_end_date.data,
            )
            flash(f"Updated submission dates for {collection.name}.", "success")
            return redirect(url_for("collection_lifecycle.tasklist", grant_id=grant.id, collection_id=collection.id))

        return self.render(
            "deliver_grant_funding/admin/set-collection-dates.html",
            title="Set submission dates",
            form=form,
            grant=grant,
            collection=collection,
        )

    @expose("/<uuid:grant_id>/<uuid:collection_id>/set-reminder-days", methods=["GET", "POST"])
    @auto_commit_after_request
    def set_reminder_days(self, grant_id: UUID, collection_id: UUID) -> Any:
        grant = get_grant(grant_id)
        collection = get_collection(collection_id, grant_id=grant_id)

        form = PlatformAdminSetReminderDaysForm(obj=collection)

        if form.validate_on_submit():
            update_collection(
                collection,
                reminder_email_business_days_before_closing=form.reminder_email_business_days_before_closing.data,
            )
            flash(f"Updated reminder email setting for {collection.name}.", "success")
            return redirect(url_for("collection_lifecycle.tasklist", grant_id=grant.id, collection_id=collection.id))

        return self.render(
            "deliver_grant_funding/admin/set-reminder-days.html",
            form=form,
            grant=grant,
            collection=collection,
        )

    @expose("/<uuid:grant_id>/<uuid:collection_id>/schedule-collection", methods=["GET", "POST"])
    @auto_commit_after_request
    def schedule_collection(self, grant_id: UUID, collection_id: UUID) -> Any:
        grant = get_grant(grant_id)
        collection = get_collection(collection_id, grant_id=grant_id)
        _, recipients_missing_data_providers = get_grant_recipient_data_providers_count(grant)

        form = PlatformAdminScheduleCollectionForm(collection=collection)
        if form.validate_on_submit():
            try:
                update_collection(collection, status=CollectionStatusEnum.SCHEDULED)
                flash(
                    f"{collection.name} is now locked and form designers cannot make any more changes.",
                    "success",
                )
                return redirect(
                    url_for("collection_lifecycle.tasklist", grant_id=grant.id, collection_id=collection.id)
                )
            except StateTransitionError as e:
                form.form_errors.append(
                    f"{collection.name} can only be scheduled from the 'draft' state; it is currently {e.from_state}",
                )
            except (GrantMustBeLiveError, GrantRecipientUsersRequiredError, CollectionChronologyError) as e:
                form.form_errors.append(str(e))

        data_sources_with_missing_data = get_referenced_grant_recipient_data_sources_for_collection(
            collection.id, only_with_missing_data=True
        )
        data_set_names_with_missing_data = [
            data_source.name for data_source in data_sources_with_missing_data if data_source.name
        ]

        return self.render(
            "deliver_grant_funding/admin/confirm-schedule-collection.html",
            form=form,
            grant=grant,
            collection=collection,
            recipients_missing_data_providers=recipients_missing_data_providers,
            data_set_names_with_missing_data=data_set_names_with_missing_data,
        )

    @expose("/<uuid:grant_id>/<uuid:collection_id>/make-collection-live", methods=["GET", "POST"])
    @auto_commit_after_request
    def make_collection_live(self, grant_id: UUID, collection_id: UUID) -> Any:
        grant = get_grant(grant_id)
        collection = get_collection(collection_id, grant_id=grant_id)

        grant_recipients_count = get_grant_recipients_count(grant)
        data_providers_count, recipients_missing_data_providers = get_grant_recipient_data_providers_count(grant)

        data_sources_with_missing_data = get_referenced_grant_recipient_data_sources_for_collection(
            collection.id, only_with_missing_data=True, with_organisation_items=True
        )
        live_grant_recipients = get_grant_recipients(grant, with_organisations=True)
        missing_data_organisations = {
            organisation
            for data_source in data_sources_with_missing_data
            for organisation in data_source.get_missing_data_organisations(live_grant_recipients)
        }
        missing_data_organisation_names = sorted(organisation.name for organisation in missing_data_organisations)

        form = PlatformAdminMakeCollectionLiveForm(
            collection=collection,
            grant_recipients_count=grant_recipients_count,
            data_providers_count=data_providers_count,
            recipients_missing_data_providers=recipients_missing_data_providers,
            missing_data_organisation_names=missing_data_organisation_names,
        )
        if form.validate_on_submit():
            try:
                update_collection(collection, status=CollectionStatusEnum.OPEN)
                flash(
                    (
                        f"{markupsafe.escape(collection.name)} is now live and grant recipients can start making "
                        f"submissions. "
                        "<strong>You must now send emails to grant recipient users to let them know the "
                        f"{collection.type.constants.singular} is open for submissions.</strong>"
                    ),
                    "success",
                )
                return redirect(
                    url_for("collection_lifecycle.tasklist", grant_id=grant.id, collection_id=collection.id)
                )
            except StateTransitionError as e:
                form.form_errors.append(
                    f"{collection.name} can only be made live from the 'scheduled' state; "
                    f"it is currently {e.from_state}",
                )
            except (GrantMustBeLiveError, GrantRecipientUsersRequiredError, CollectionChronologyError) as e:
                form.form_errors.append(str(e))

        return self.render(
            "deliver_grant_funding/admin/confirm-make-collection-live.html",
            form=form,
            grant=grant,
            collection=collection,
        )

    @expose("/<uuid:grant_id>/<uuid:collection_id>/send-emails-to-data-providers/<email_type>", methods=["GET"])
    def send_emails_to_recipients(
        self, grant_id: UUID, collection_id: UUID, email_type: CollectionAdminEmailTypeEnum
    ) -> Any:
        grant = get_grant(grant_id)
        collection = get_collection(collection_id, grant_id=grant_id)

        notify_service_id = current_app.config["GOVUK_NOTIFY_SERVICE_ID"]
        match email_type:
            case CollectionAdminEmailTypeEnum.COLLECTION_OPEN_NOTIFICATION:
                if collection.multiple_submissions_are_managed_by_service:
                    notify_template_id = current_app.config[
                        "GOVUK_NOTIFY_GRANT_RECIPIENT_MANAGED_MULTI_SUBMISSION_REPORT_NOTIFICATION_TEMPLATE_ID"
                    ]
                else:
                    notify_template_id = current_app.config[
                        "GOVUK_NOTIFY_GRANT_RECIPIENT_REPORT_NOTIFICATION_TEMPLATE_ID"
                    ]
            case CollectionAdminEmailTypeEnum.DEADLINE_REMINDER:
                if not collection.status == CollectionStatusEnum.OPEN:
                    return abort(404)

                if collection.multiple_submissions_are_managed_by_service:
                    notify_template_id = current_app.config[
                        "GOVUK_NOTIFY_GRANT_RECIPIENT_MANAGED_MULTI_SUBMISSION_REPORT_DEADLINE_REMINDER_TEMPLATE_ID"
                    ]
                else:
                    notify_template_id = current_app.config[
                        "GOVUK_NOTIFY_GRANT_RECIPIENT_REPORT_DEADLINE_REMINDER_TEMPLATE_ID"
                    ]
            case CollectionAdminEmailTypeEnum.COLLECTION_OVERDUE:
                if collection.status != CollectionStatusEnum.OPEN or not collection.is_overdue:
                    return abort(404)
                if collection.multiple_submissions_are_managed_by_service:
                    notify_template_id = current_app.config[
                        "GOVUK_NOTIFY_GRANT_RECIPIENT_MANAGED_MULTI_SUBMISSION_REPORT_OVERDUE_TEMPLATE_ID"
                    ]
                else:
                    notify_template_id = current_app.config["GOVUK_NOTIFY_GRANT_RECIPIENT_REPORT_OVERDUE_TEMPLATE_ID"]
            case CollectionAdminEmailTypeEnum.COLLECTION_CLOSED_NOTIFICATION:
                if collection.status != CollectionStatusEnum.CLOSED:
                    return abort(404)
                if collection.multiple_submissions_are_managed_by_service:
                    notify_template_id = current_app.config[
                        "GOVUK_NOTIFY_GRANT_RECIPIENT_MANAGED_MULTI_SUBMISSION_REPORT_CLOSED_TEMPLATE_ID"
                    ]
                else:
                    notify_template_id = current_app.config["GOVUK_NOTIFY_GRANT_RECIPIENT_REPORT_CLOSED_TEMPLATE_ID"]
            case _:
                return abort(404)

        return self.render(
            "deliver_grant_funding/admin/send-emails-to-data-providers.html",
            grant=grant,
            collection=collection,
            notify_template_url=f"https://www.notifications.service.gov.uk/services/{notify_service_id}/send/{notify_template_id}/csv",
            email_type=email_type,
        )

    @expose(
        "/<uuid:grant_id>/<uuid:collection_id>/send-emails-to-data-providers/download-csv/<email_type>", methods=["GET"]
    )
    def download_data_providers_csv(
        self, grant_id: UUID, collection_id: UUID, email_type: CollectionAdminEmailTypeEnum
    ) -> Any:
        grant = get_grant(grant_id)
        collection = get_collection(collection_id, grant_id=grant_id)

        assert collection.submission_period_end_date

        csv_output = StringIO()
        csv_writer = csv.DictWriter(
            csv_output,
            fieldnames=[
                "email_address",
                "grant_name",
                "collection_type_noun",
                "organisation_name",
                "submission_name",
                "submission_deadline",
                "grant_submission_url",
                "is_test_data",
                "requires_certification",
                "submissions",
                "unsubmitted_submissions",
            ],
        )
        csv_writer.writeheader()
        email_recipients = set()

        match email_type:
            case CollectionAdminEmailTypeEnum.COLLECTION_OPEN_NOTIFICATION:
                grant_recipients = get_grant_recipients(grant=grant, with_data_providers=True, with_organisations=True)
                email_recipients = {
                    (data_provider, grant_recipient)
                    for grant_recipient in grant_recipients
                    for data_provider in grant_recipient.data_providers
                }
            case (
                CollectionAdminEmailTypeEnum.DEADLINE_REMINDER
                | CollectionAdminEmailTypeEnum.COLLECTION_OVERDUE
                | CollectionAdminEmailTypeEnum.COLLECTION_CLOSED_NOTIFICATION
            ):
                grant_recipients = get_grant_recipients_with_outstanding_submissions_for_collection(
                    grant, collection_id=collection.id, with_data_providers=True, with_certifiers=True
                )
                email_recipients = {
                    (recipient_user, grant_recipient)
                    for grant_recipient in grant_recipients
                    for recipient_user in grant_recipient.data_providers + list(grant_recipient.certifiers)
                }
            case _:
                return abort(404)
        for email_recipient, grant_recipient in sorted(email_recipients, key=lambda u: u[0].email):
            submission_name = collection.name
            submission_deadline = format_date(collection.submission_period_end_date)

            grant_submission_url = url_for(
                "access_grant_funding.route_to_submission",
                organisation_id=grant_recipient.organisation.id,
                grant_id=grant_recipient.grant.id,
                collection_id=collection.id,
                _external=True,
            )
            submissions = [SubmissionHelper(s) for s in grant_recipient.submissions if s.collection_id == collection.id]

            # WARN: this needs to tie up with the generated data from the Notification service
            csv_writer.writerow(
                {
                    "email_address": email_recipient.email,
                    "grant_name": grant.name,
                    "collection_type_noun": collection.type.constants.singular,
                    "organisation_name": grant_recipient.organisation.name,
                    "submission_name": submission_name,
                    "submission_deadline": submission_deadline,
                    "grant_submission_url": grant_submission_url,
                    "is_test_data": "yes" if grant_recipient.mode == GrantRecipientModeEnum.TEST else "no",
                    "requires_certification": "yes" if collection.requires_certification else "no",
                    "submissions": (
                        "\n".join(sorted(f"* {submission.submission_name}" for submission in submissions))
                        if collection.multiple_submissions_are_managed_by_service
                        else ""
                    ),
                    "unsubmitted_submissions": (
                        "\n".join(
                            sorted(
                                (
                                    f"* {submission.submission_name}"
                                    for submission in submissions
                                    if not submission.is_submitted
                                )
                            )
                        )
                        if collection.multiple_submissions_are_managed_by_service
                        else ""
                    ),
                }
            )

        csv_bytes = BytesIO(csv_output.getvalue().encode("utf-8"))
        csv_bytes.seek(0)

        filename = (
            f"grant-recipients-{grant.name.lower().replace(' ', '-')}-{collection.name.lower().replace(' ', '-')}.csv"
        )

        return send_file(csv_bytes, mimetype="text/csv", as_attachment=True, download_name=filename, max_age=1)

    @expose("/<uuid:grant_id>/<uuid:collection_id>/close-collection", methods=["GET", "POST"])
    @auto_commit_after_request
    def close_collection(self, grant_id: UUID, collection_id: UUID) -> Any:
        grant = get_grant(grant_id)
        collection = get_collection(collection_id, grant_id=grant_id)

        form = GenericSubmitForm()
        if form.validate_on_submit():
            try:
                update_collection(collection, status=CollectionStatusEnum.CLOSED)
                flash(
                    (
                        f"{markupsafe.escape(collection.name)} is now closed and grant recipients can make no more "
                        f"changes. "
                        "<strong>You must now send emails to grant recipient users to let them know the "
                        f"{collection.type.constants.singular} is closed.</strong>"
                    ),
                    "success",
                )
                return redirect(
                    url_for("collection_lifecycle.tasklist", grant_id=grant.id, collection_id=collection.id)
                )
            except StateTransitionError as e:
                form.form_errors.append(
                    f"{collection.name} can only be closed from the 'open' state; it is currently {e.from_state}",
                )
            except CollectionChronologyError as e:
                form.form_errors.append(str(e))

        return self.render(
            "deliver_grant_funding/admin/confirm-close-collection.html",
            form=form,
            grant=grant,
            collection=collection,
        )


class PlatformAdminDataAnalysisView(FlaskAdminPlatformAdminDataAnalystAccessibleMixin, BaseView):
    @expose("/")
    def index(self) -> Any:
        # Get stats for live grant recipients
        unique_users_count = get_unique_users_count_for_live_grant_recipients()
        certifier_users_count = get_unique_users_count_for_live_grant_recipients(with_permissions=[RoleEnum.CERTIFIER])
        data_provider_users_count = get_unique_users_count_for_live_grant_recipients(
            with_permissions=[RoleEnum.DATA_PROVIDER]
        )

        return self.render(
            "deliver_grant_funding/admin/data-analysis.html",
            unique_users_count=unique_users_count,
            certifier_users_count=certifier_users_count,
            data_provider_users_count=data_provider_users_count,
        )

    @expose("/certification-events.csv")
    def download_certification_events_csv(self) -> Any:
        result = db.session.execute(
            text(
                """
                SELECT
                    submission.reference::text AS "Submission reference",
                    MAX(submission_event.created_at_utc) AS "Sent for certification at",
                    COUNT(submission_event.created_at_utc) AS "Number of times sent for certification"
                 FROM submission
                 JOIN submission_event ON submission.id = submission_event.submission_id
                 WHERE submission.mode::text = :submission_mode
                 AND submission_event.event_type = :certification_event_name
                 GROUP BY submission.reference
                """
            ),
            {
                "submission_mode": SubmissionModeEnum.LIVE.name,
                "certification_event_name": SubmissionEventType.SUBMISSION_SENT_FOR_CERTIFICATION.name,
            },
        )

        csv_output = StringIO()
        csv_writer = csv.writer(csv_output)
        csv_writer.writerow(result.keys())
        csv_writer.writerows(result.fetchall())

        csv_bytes = BytesIO(csv_output.getvalue().encode("utf-8-sig"))
        csv_bytes.seek(0)

        return send_file(
            csv_bytes,
            mimetype="text/csv",
            as_attachment=True,
            download_name="certification-events.csv",
        )


class PlatformAdminDeveloperToolsView(FlaskAdminPlatformAdminAccessibleMixin, BaseView):
    @expose("/", methods=["GET", "POST"])
    def index(self) -> Any:
        current_levels = get_tracing_levels()
        form = PlatformAdminForceTracingForm(levels=[level.value for level in current_levels])

        if form.validate_on_submit():
            selected = [TraceLevelEnum(value) for value in (form.levels.data or [])]
            response = make_response(redirect(url_for("developer_tools.index")))
            if selected:
                token = encode_levels(selected, current_app.config["SECRET_KEY"])
                response.set_cookie(
                    REQUEST_TRACING_COOKIE_NAME,
                    token,
                    max_age=REQUEST_TRACING_TTL,
                    httponly=True,
                    secure=True,
                    samesite="Lax",
                )
            else:
                response.delete_cookie(REQUEST_TRACING_COOKIE_NAME)
            return response

        return self.render(
            "deliver_grant_funding/admin/developer_tools.html",
            form=form,
            current_levels=current_levels,
        )

    @expose("/stop", methods=["POST"])
    def stop(self) -> Any:
        form = GenericSubmitForm()
        if form.validate_on_submit():
            response = make_response(redirect(url_for("developer_tools.index")))
            response.delete_cookie(REQUEST_TRACING_COOKIE_NAME)
            return response

        return redirect(url_for("developer_tools.index"))


class PlatformAdminFeatureFlagsView(FlaskAdminPlatformMemberAccessibleMixin, BaseView):
    @expose("/")
    def index(self) -> Any:
        return self.render(
            "deliver_grant_funding/admin/feature-flags.html",
            flags=FeatureFlags.all(),
        )

    @expose("/toggle/<flag_name>", methods=["GET", "POST"])
    def toggle(self, flag_name: str) -> Any:
        if not hasattr(FeatureFlags, flag_name):
            abort(404)

        flag = getattr(FeatureFlags, flag_name)
        if not isinstance(flag, SessionFeatureFlag):
            abort(404)

        form = PlatformAdminToggleFeatureFlagForm()
        if form.validate_on_submit():
            desired = form.enabled.data == "on"
            if desired != flag.is_enabled:
                flag.toggle()

            return redirect(url_for("feature_flags.index"))

        return self.render(
            "deliver_grant_funding/admin/toggle-feature-flag.html",
            flag=flag,
            flag_name=flag_name,
            form=form,
        )


@dataclass(frozen=True)
class DeltaS151Officer:
    organisation_code: str
    organisation_name: str
    officer_type: Literal["s151-officer", "deputy-s151-officer", "delegated-s151-officer"]
    email: str
    name: str
    delegated_access_groups: str
    last_modified_date: datetime.datetime


class OrgCertifierRow(TypedDict):
    organisation: Organisation
    our_certifiers: Sequence[User]
    delta_officers: Sequence[DeltaS151Officer]
    has_diff: bool


class DelegatedEntry(TypedDict):
    email: str
    name: str
    delegated_access_groups: str
    our_grants: list[Grant]


class DelegatedRow(TypedDict):
    organisation: Organisation
    entries: list[DelegatedEntry]


class _FSGrantCertifier(TypedDict):
    user: User
    grants: list[Grant]


def _load_delta_s151_officers() -> tuple[list[DeltaS151Officer], datetime.datetime]:
    path_or_key = current_app.config["DELTA_S151_CSV_PATH_OR_KEY"]
    if path_or_key.startswith("s3://"):
        bucket, _, key = path_or_key.removeprefix("s3://").partition("/")
        s3_object = boto3.client("s3").get_object(Bucket=bucket, Key=key)
        last_updated_at = s3_object["LastModified"]
        zip_bytes = s3_object["Body"].read()
        with zipfile.ZipFile(BytesIO(zip_bytes)) as archive:
            csv_name = next(n for n in archive.namelist() if n.endswith(".csv"))
            csv_text = archive.read(csv_name).decode("utf-8-sig")
    else:
        last_updated_at = datetime.datetime.fromtimestamp(os.path.getmtime(path_or_key), tz=datetime.UTC)
        with open(path_or_key, encoding="utf-8-sig") as f:
            csv_text = f.read()

    reader = csv.DictReader(csv_text.splitlines())
    officers = [
        DeltaS151Officer(
            organisation_code=row["organisation-code"],
            organisation_name=row["organisation-name"],
            officer_type=cast(
                Literal["s151-officer", "deputy-s151-officer", "delegated-s151-officer"],
                row["officer-type"],
            ),
            email=row["officer-user-email"],
            name=row["officer-name"],
            delegated_access_groups=row["delegated-access-groups"],
            last_modified_date=datetime.datetime.fromisoformat(row["last-modified-date"]),
        )
        for row in reader
        if row["officer-type"] in ("s151-officer", "deputy-s151-officer", "delegated-s151-officer")
        and row["status"] == "approved"
        and row["account-status"] == "enabled"
    ]
    return officers, last_updated_at


def _build_org_certifier_rows(
    delta_officers: list[DeltaS151Officer],
    certifiers_by_org: dict[Organisation, Sequence[User]],
) -> list[OrgCertifierRow]:
    delta_by_org_code: dict[str, list[DeltaS151Officer]] = {}
    for officer in delta_officers:
        if officer.officer_type == "delegated-s151-officer":
            continue
        delta_by_org_code.setdefault(officer.organisation_code, []).append(officer)

    rows: list[OrgCertifierRow] = []
    for organisation, our_certifiers in certifiers_by_org.items():
        delta_for_org = delta_by_org_code.get(organisation.external_id, [])
        our_emails = {user.email.lower() for user in our_certifiers}
        delta_emails = {officer.email.lower() for officer in delta_for_org}
        rows.append(
            OrgCertifierRow(
                organisation=organisation,
                our_certifiers=sorted(our_certifiers, key=lambda u: u.email),
                delta_officers=sorted(
                    delta_for_org, key=lambda o: (0 if o.officer_type == "s151-officer" else 1, o.email)
                ),
                has_diff=our_emails != delta_emails,
            )
        )
    rows.sort(key=lambda row: row["organisation"].name)
    return rows


def _build_delegated_rows(
    delta_officers: list[DeltaS151Officer],
    certifiers_by_org: dict[Organisation, Sequence[User]],
) -> list[DelegatedRow]:
    fs_grant_certifiers_by_org: dict[Organisation, dict[str, _FSGrantCertifier]] = {}
    for grant in get_all_grants():
        override_certifiers = get_grant_override_certifiers_by_organisation(
            grant_id=grant.id, organisation_mode=OrganisationModeEnum.LIVE
        )
        for fs_org, users in override_certifiers.items():
            for user in users:
                email = user.email.lower()
                entry = fs_grant_certifiers_by_org.setdefault(fs_org, {}).setdefault(
                    email, {"user": user, "grants": []}
                )
                entry["grants"].append(grant)

    org_by_external_id = {org.external_id: org for org in certifiers_by_org}

    delta_delegated_by_org: dict[Organisation, dict[str, DeltaS151Officer]] = {}
    for officer in delta_officers:
        if officer.officer_type != "delegated-s151-officer":
            continue
        organisation = org_by_external_id.get(officer.organisation_code)
        if organisation is None:
            continue
        delta_delegated_by_org.setdefault(organisation, {})[officer.email.lower()] = officer

    rows: list[DelegatedRow] = []
    for organisation in certifiers_by_org:
        delta_map = delta_delegated_by_org.get(organisation, {})
        fs_map = fs_grant_certifiers_by_org.get(organisation, {})
        entries = []
        for email in sorted(set(delta_map) | set(fs_map)):
            delta_officer = delta_map.get(email)
            fs_data = fs_map.get(email)
            if delta_officer is not None:
                name = delta_officer.name
                delegated_access_groups = delta_officer.delegated_access_groups
            else:
                assert fs_data is not None
                name = fs_data["user"].name
                delegated_access_groups = ""
            entries.append(
                DelegatedEntry(
                    email=email,
                    name=name,
                    delegated_access_groups=delegated_access_groups,
                    our_grants=sorted(fs_data["grants"], key=lambda g: g.name) if fs_data else [],
                )
            )
        rows.append(DelegatedRow(organisation=organisation, entries=entries))
    rows.sort(key=lambda row: row["organisation"].name)
    return rows


class PlatformAdminDeltaCertifiersView(FlaskAdminPlatformAdminGrantLifecycleManagerAccessibleMixin, BaseView):
    @expose("/", methods=["GET"])
    def index(self) -> ResponseReturnValue:
        delta_officers, delta_last_updated_at = _load_delta_s151_officers()
        certifiers_by_org = get_certifiers_by_organisation()

        return self.render(
            "deliver_grant_funding/admin/delta-certifiers.html",
            org_rows=_build_org_certifier_rows(delta_officers, certifiers_by_org),
            delegated_rows=_build_delegated_rows(delta_officers, certifiers_by_org),
            delta_last_updated_at=delta_last_updated_at,
        )
