import datetime
import uuid

import pytest
from sqlalchemy import Date, func, select
from sqlalchemy.exc import IntegrityError, NoResultFound

from app.common.collections.types import EmailAnswer, SingleChoiceFromListAnswer, TextSingleLineAnswer
from app.common.data.interfaces import collections
from app.common.data.interfaces.collections import (
    AddAnotherDependencyException,
    AddAnotherNotValidException,
    DataSourceItemReferenceDependencyException,
    DependencyOrderException,
    GroupContainsAddAnotherException,
    IncompatibleDataTypeException,
    NestedGroupDisplayTypeSamePageException,
    NestedGroupException,
    SectionComponentDependencyException,
    _add_submission_event,
    _validate_and_sync_component_references,
    _validate_and_sync_expression_references,
    _validate_reference,
    add_component_condition,
    add_component_validation,
    components_in_same_group_and_on_same_page,
    create_collection,
    create_form,
    create_group,
    create_question,
    delete_all_collection_preview_submissions,
    delete_collection,
    delete_collection_preview_submissions_created_by_user,
    delete_form,
    delete_question,
    get_all_submissions_with_mode_for_collection,
    get_collection,
    get_collection_by_slug,
    get_collections_by_status_excluding_draft_grants,
    get_collections_with_dates_near_today_excluding_draft_grants,
    get_expression,
    get_expression_by_id,
    get_form_by_id,
    get_group_by_id,
    get_overdue_open_collections_excluding_draft_grants,
    get_question_by_id,
    get_referenced_data_source_items_by_managed_expression,
    get_submission,
    get_submission_list_for_collection,
    get_submissions_by_grant_recipient_collection,
    group_name_exists,
    is_component_dependency_order_valid,
    move_component_down,
    move_component_up,
    move_form_down,
    move_form_up,
    raise_if_component_or_section_has_any_dependencies,
    raise_if_data_source_item_reference_dependency,
    raise_if_group_questions_depend_on_each_other,
    raise_if_nested_group_creation_not_valid_here,
    remove_question_expression,
    reset_all_test_submissions,
    reset_test_submission,
    update_collection,
    update_group,
    update_question,
    update_question_expression,
    update_submission_data,
)
from app.common.data.interfaces.exceptions import (
    CollectionChronologyError,
    DuplicateValueError,
    GrantMustBeLiveError,
    InvalidReferenceInExpression,
    StateTransitionError,
)
from app.common.data.models import (
    Collection,
    ComponentReference,
    DataSource,
    DataSourceItem,
    DataSourceOrganisationItem,
    Expression,
    Form,
    Group,
    Question,
    Submission,
    SubmissionEvent,
)
from app.common.data.submission_data_manager import SubmissionDataManager
from app.common.data.types import (
    CollectionStatusEnum,
    CollectionType,
    ConditionsOperator,
    DataSourceSchema,
    DataSourceSchemaColumn,
    DataSourceType,
    ExpressionType,
    GrantRecipientModeEnum,
    GrantStatusEnum,
    ManagedExpressionsEnum,
    MultilineTextInputRows,
    NumberInputWidths,
    NumberTypeEnum,
    QuestionDataOptions,
    QuestionDataType,
    QuestionPresentationOptions,
    RoleEnum,
    SubmissionAssessmentStatusEnum,
    SubmissionEventType,
    SubmissionModeEnum,
    SubmissionStatusEnum,
)
from app.common.expressions import EvaluationStatement, ExpressionContext
from app.common.expressions.custom import CustomExpression
from app.common.expressions.managed import AnyOf, Between, GreaterThan, LessThan, Specifically
from app.common.expressions.references import ExpressionReference, InterpolationStatement
from app.common.forms.helpers import components_in_valid_add_another_combination
from app.common.helpers.collections import SubmissionHelper
from app.metrics import MetricEventName
from tests.models import FactoryAnswer


class TestGetCollection:
    def test_get_collection(self, db_session, factories):
        collection = factories.collection.create()
        from_db = get_collection(collection_id=collection.id)
        assert from_db is not None

    def test_get_collection_with_grant_id(self, db_session, factories):
        collection = factories.collection.create()

        assert get_collection(collection_id=collection.id, grant_id=collection.grant_id) is not None

        with pytest.raises(NoResultFound):
            get_collection(collection_id=collection.id, grant_id=uuid.uuid4())

    def test_get_collection_with_type(self, db_session, factories):
        collection = factories.collection.create()

        assert get_collection(collection_id=collection.id, type_=CollectionType.MONITORING_REPORT) is collection

        # TODO: Extend with a test on another collection type when we extend the CollectionType enum.

    def test_get_collection_with_full_schema_data_source_references_no_n_plus_one(
        self, db_session, factories, track_sql_queries
    ):
        grant = factories.grant.create()
        collection = factories.collection.create(grant=grant)
        factories.grant_recipient.create(grant=grant)
        data_source = factories.data_source.create(
            grant=grant,
            collection=collection,
            type=DataSourceType.GRANT_RECIPIENT,
            create_gr_org_items=True,
        )
        factories.question.create(
            form__collection=collection,
            hint=InterpolationStatement(
                "You were allocated " + ExpressionReference.from_data_source_column(data_source, "c_allocation").wrapped
            ),
        )

        with track_sql_queries() as one_data_source_queries:
            from_db = get_collection(collection_id=collection.id, with_full_schema=True)
            for ds in from_db.data_sources:
                assert ds.depended_on_by_columns
                assert ds.organisation_items

        baseline_queries = len(one_data_source_queries)

        for i in range(2):
            other_data_source = factories.data_source.create(
                grant=grant,
                collection=collection,
                type=DataSourceType.GRANT_RECIPIENT,
                name=f"Other data source {i}",
                create_gr_org_items=True,
            )
            factories.question.create(
                form__collection=collection,
                hint=InterpolationStatement(
                    "You were allocated "
                    + ExpressionReference.from_data_source_column(other_data_source, "c_allocation").wrapped
                ),
            )
        db_session.commit()

        with track_sql_queries() as three_data_source_queries:
            from_db = get_collection(collection_id=collection.id, with_full_schema=True)
            for ds in from_db.data_sources:
                assert ds.depended_on_by_columns
                assert ds.organisation_items

        assert len(three_data_source_queries) == baseline_queries


class TestGetCollectionBySlug:
    def test_get_collection_by_slug(self, factories):
        collection = factories.collection.create(slug="my-collection")
        result = get_collection_by_slug(grant_id=collection.grant_id, slug="my-collection")

        assert result.id is collection.id

    def test_get_collection_by_slug_not_found(self, factories):
        collection = factories.collection.create(slug="my-collection")

        with pytest.raises(NoResultFound):
            get_collection_by_slug(grant_id=collection.grant_id, slug="not-a-real-slug")

        with pytest.raises(NoResultFound):
            get_collection_by_slug(grant_id=uuid.uuid4(), slug="my-collection")


class TestCreateCollection:
    def test_create_collection(self, db_session, factories):
        g = factories.grant.create()
        u = factories.user.create()
        collection = create_collection(name="test collection", user=u, grant=g, type_=CollectionType.MONITORING_REPORT)
        assert collection is not None
        assert collection.id is not None
        assert collection.slug == "test-collection"

        from_db = db_session.get(Collection, collection.id)
        assert from_db is not None

    @pytest.mark.parametrize(
        "type_, expected_requires_certification",
        [
            (CollectionType.MONITORING_REPORT, True),
            (CollectionType.APPLICATION, False),
        ],
    )
    def test_create_collection_requires_certification_by_default_based_on_type(
        self, db_session, factories, type_, expected_requires_certification
    ):
        g = factories.grant.create()
        u = factories.user.create()

        collection = create_collection(name="test collection", user=u, grant=g, type_=type_)

        assert collection.requires_certification is expected_requires_certification

    def test_create_collection_name_is_unique_per_grant(self, db_session, factories):
        grants = factories.grant.create_batch(2)
        u = factories.user.create()

        # Check collection created initially
        create_collection(name="test_collection", user=u, grant=grants[0], type_=CollectionType.MONITORING_REPORT)

        # Check same name in a different grant is allowed
        collection_same_name_different_grant = create_collection(
            name="test_collection", user=u, grant=grants[1], type_=CollectionType.MONITORING_REPORT
        )
        assert collection_same_name_different_grant.id is not None

        # Check same name in the same grant is not allowed
        with pytest.raises(DuplicateValueError):
            create_collection(name="test_collection", user=u, grant=grants[0], type_=CollectionType.MONITORING_REPORT)


class TestUpdateCollection:
    def test_update_collection_name(self, db_session, factories):
        collection = factories.collection.create(name="Original Name")

        updated_collection = update_collection(collection, name="Updated Name")

        assert updated_collection.name == "Updated Name"
        assert updated_collection.slug == "updated-name"

        from_db = db_session.get(Collection, collection.id)
        assert from_db.name == "Updated Name"
        assert from_db.slug == "updated-name"

    def test_update_collection_reporting_period_dates(self, db_session, factories):
        collection = factories.collection.create(
            reporting_period_start_date=None,
            reporting_period_end_date=None,
            submission_period_start_date=datetime.date(2025, 1, 1),
            submission_period_end_date=datetime.date(2025, 1, 31),
        )

        start_date = datetime.date(2024, 1, 1)
        end_date = datetime.date(2024, 12, 31)

        updated_collection = update_collection(
            collection, reporting_period_start_date=start_date, reporting_period_end_date=end_date
        )

        assert updated_collection.reporting_period_start_date == start_date
        assert updated_collection.reporting_period_end_date == end_date

        from_db = db_session.get(Collection, collection.id)
        assert from_db.reporting_period_start_date == start_date
        assert from_db.reporting_period_end_date == end_date

    def test_update_collection_submission_period_dates(self, db_session, factories):
        collection = factories.collection.create(
            reporting_period_start_date=datetime.date(2024, 1, 1),
            reporting_period_end_date=datetime.date(2024, 12, 31),
            submission_period_start_date=None,
            submission_period_end_date=None,
        )

        start_date = datetime.date(2025, 1, 1)
        end_date = datetime.date(2025, 1, 31)

        updated_collection = update_collection(
            collection, submission_period_start_date=start_date, submission_period_end_date=end_date
        )

        assert updated_collection.submission_period_start_date == start_date
        assert updated_collection.submission_period_end_date == end_date

        from_db = db_session.get(Collection, collection.id)
        assert from_db.submission_period_start_date == start_date
        assert from_db.submission_period_end_date == end_date

    def test_update_collection_all_fields(self, db_session, factories):
        collection = factories.collection.create(
            name="Original Name",
            reporting_period_start_date=None,
            reporting_period_end_date=None,
            submission_period_start_date=None,
            submission_period_end_date=None,
        )

        updated_collection = update_collection(
            collection,
            name="New Name",
            reporting_period_start_date=datetime.date(2024, 1, 1),
            reporting_period_end_date=datetime.date(2024, 12, 31),
            submission_period_start_date=datetime.date(2025, 1, 1),
            submission_period_end_date=datetime.date(2025, 1, 31),
        )

        assert updated_collection.name == "New Name"
        assert updated_collection.slug == "new-name"
        assert updated_collection.reporting_period_start_date == datetime.date(2024, 1, 1)
        assert updated_collection.reporting_period_end_date == datetime.date(2024, 12, 31)
        assert updated_collection.submission_period_start_date == datetime.date(2025, 1, 1)
        assert updated_collection.submission_period_end_date == datetime.date(2025, 1, 31)

    def test_update_collection_only_name(self, db_session, factories):
        collection = factories.collection.create(
            name="Original Name",
            reporting_period_start_date=datetime.date(2024, 1, 1),
            reporting_period_end_date=datetime.date(2024, 12, 31),
            submission_period_start_date=datetime.date(2025, 1, 1),
            submission_period_end_date=datetime.date(2025, 1, 31),
        )

        updated_collection = update_collection(collection, name="New Name")

        assert updated_collection.name == "New Name"
        assert updated_collection.reporting_period_start_date == datetime.date(2024, 1, 1)
        assert updated_collection.reporting_period_end_date == datetime.date(2024, 12, 31)
        assert updated_collection.submission_period_start_date == datetime.date(2025, 1, 1)
        assert updated_collection.submission_period_end_date == datetime.date(2025, 1, 31)

    def test_update_collection_reporting_period_start_none_end_set_raises_error(self, db_session, factories):
        collection = factories.collection.create()

        with pytest.raises(CollectionChronologyError) as exc_info:
            update_collection(
                collection, reporting_period_start_date=None, reporting_period_end_date=datetime.date(2024, 12, 31)
            )

        assert "must both be unset or both be set" in str(exc_info.value)

    def test_update_collection_reporting_period_start_set_end_none_raises_error(self, db_session, factories):
        collection = factories.collection.create()

        with pytest.raises(CollectionChronologyError) as exc_info:
            update_collection(
                collection, reporting_period_start_date=datetime.date(2024, 1, 1), reporting_period_end_date=None
            )

        assert "must both be unset or both be set" in str(exc_info.value)

    def test_update_collection_submission_period_start_none_end_set_raises_error(self, db_session, factories):
        collection = factories.collection.create(
            reporting_period_start_date=datetime.date(2024, 1, 1), reporting_period_end_date=datetime.date(2024, 12, 31)
        )

        with pytest.raises(CollectionChronologyError) as exc_info:
            update_collection(
                collection, submission_period_start_date=None, submission_period_end_date=datetime.date(2025, 1, 31)
            )

        assert "must both be unset or both be set" in str(exc_info.value)

    def test_update_collection_submission_period_start_set_end_none_raises_error(self, db_session, factories):
        collection = factories.collection.create(
            reporting_period_start_date=datetime.date(2024, 1, 1), reporting_period_end_date=datetime.date(2024, 12, 31)
        )

        with pytest.raises(CollectionChronologyError) as exc_info:
            update_collection(
                collection, submission_period_start_date=datetime.date(2025, 1, 1), submission_period_end_date=None
            )

        assert "must both be unset or both be set" in str(exc_info.value)

    def test_update_collection_clear_reporting_period_dates(self, db_session, factories):
        collection = factories.collection.create(
            reporting_period_start_date=datetime.date(2024, 1, 1),
            reporting_period_end_date=datetime.date(2024, 12, 31),
            submission_period_start_date=datetime.date(2025, 1, 1),
            submission_period_end_date=datetime.date(2025, 1, 31),
        )

        updated_collection = update_collection(
            collection, reporting_period_start_date=None, reporting_period_end_date=None
        )

        assert updated_collection.reporting_period_start_date is None
        assert updated_collection.reporting_period_end_date is None
        assert updated_collection.submission_period_start_date == datetime.date(2025, 1, 1)
        assert updated_collection.submission_period_end_date == datetime.date(2025, 1, 31)

        from_db = db_session.get(Collection, collection.id)
        assert from_db.reporting_period_start_date is None
        assert from_db.reporting_period_end_date is None

    def test_update_collection_clear_submission_period_dates(self, db_session, factories):
        collection = factories.collection.create(
            reporting_period_start_date=datetime.date(2024, 1, 1),
            reporting_period_end_date=datetime.date(2024, 12, 31),
            submission_period_start_date=datetime.date(2025, 1, 1),
            submission_period_end_date=datetime.date(2025, 1, 31),
        )

        updated_collection = update_collection(
            collection, submission_period_start_date=None, submission_period_end_date=None
        )

        assert updated_collection.reporting_period_start_date == datetime.date(2024, 1, 1)
        assert updated_collection.reporting_period_end_date == datetime.date(2024, 12, 31)
        assert updated_collection.submission_period_start_date is None
        assert updated_collection.submission_period_end_date is None

        from_db = db_session.get(Collection, collection.id)
        assert from_db.submission_period_start_date is None
        assert from_db.submission_period_end_date is None

    def test_update_collection_clear_all_dates(self, db_session, factories):
        collection = factories.collection.create(
            reporting_period_start_date=datetime.date(2024, 1, 1),
            reporting_period_end_date=datetime.date(2024, 12, 31),
            submission_period_start_date=datetime.date(2025, 1, 1),
            submission_period_end_date=datetime.date(2025, 1, 31),
        )

        updated_collection = update_collection(
            collection,
            reporting_period_start_date=None,
            reporting_period_end_date=None,
            submission_period_start_date=None,
            submission_period_end_date=None,
        )

        assert updated_collection.reporting_period_start_date is None
        assert updated_collection.reporting_period_end_date is None
        assert updated_collection.submission_period_start_date is None
        assert updated_collection.submission_period_end_date is None

    def test_update_collection_reporting_period_start_after_end_raises_error(self, db_session, factories):
        collection = factories.collection.create(
            submission_period_start_date=datetime.date(2025, 1, 1),
            submission_period_end_date=datetime.date(2025, 1, 31),
        )

        with pytest.raises(CollectionChronologyError) as exc_info:
            update_collection(
                collection,
                reporting_period_start_date=datetime.date(2024, 12, 31),
                reporting_period_end_date=datetime.date(2024, 1, 1),
            )

        assert "reporting_period_start_date must be before reporting_period_end_date" in str(exc_info.value)

    def test_update_collection_reporting_period_start_equals_end_raises_error(self, db_session, factories):
        collection = factories.collection.create(
            submission_period_start_date=datetime.date(2025, 1, 1),
            submission_period_end_date=datetime.date(2025, 1, 31),
        )

        with pytest.raises(CollectionChronologyError) as exc_info:
            update_collection(
                collection,
                reporting_period_start_date=datetime.date(2024, 6, 15),
                reporting_period_end_date=datetime.date(2024, 6, 15),
            )

        assert "reporting_period_start_date must be before reporting_period_end_date" in str(exc_info.value)

    def test_update_collection_submission_period_start_after_end_raises_error(self, db_session, factories):
        collection = factories.collection.create(
            reporting_period_start_date=datetime.date(2024, 1, 1), reporting_period_end_date=datetime.date(2024, 12, 31)
        )

        with pytest.raises(CollectionChronologyError) as exc_info:
            update_collection(
                collection,
                submission_period_start_date=datetime.date(2025, 1, 31),
                submission_period_end_date=datetime.date(2025, 1, 1),
            )

        assert "submission_period_start_date must be before submission_period_end_date" in str(exc_info.value)

    def test_update_collection_submission_period_start_equals_end_raises_error(self, db_session, factories):
        collection = factories.collection.create(
            reporting_period_start_date=datetime.date(2024, 1, 1), reporting_period_end_date=datetime.date(2024, 12, 31)
        )

        with pytest.raises(CollectionChronologyError) as exc_info:
            update_collection(
                collection,
                submission_period_start_date=datetime.date(2025, 1, 15),
                submission_period_end_date=datetime.date(2025, 1, 15),
            )

        assert "submission_period_start_date must be before submission_period_end_date" in str(exc_info.value)

    def test_update_collection_reporting_end_after_submission_start_is_allowed(self, db_session, factories):
        collection = factories.collection.create()

        update_collection(
            collection,
            reporting_period_start_date=datetime.date(2024, 1, 1),
            reporting_period_end_date=datetime.date(2024, 12, 31),
            submission_period_start_date=datetime.date(2024, 6, 1),
            submission_period_end_date=datetime.date(2024, 6, 30),
        )

        db_session.refresh(collection)

        assert collection.reporting_period_end_date == datetime.date(2024, 12, 31)
        assert collection.submission_period_start_date == datetime.date(2024, 6, 1)

    def test_update_collection_reporting_end_equals_submission_start_is_allowed(self, db_session, factories):
        collection = factories.collection.create()

        update_collection(
            collection,
            reporting_period_start_date=datetime.date(2024, 1, 1),
            reporting_period_end_date=datetime.date(2024, 12, 31),
            submission_period_start_date=datetime.date(2024, 12, 31),
            submission_period_end_date=datetime.date(2025, 1, 31),
        )

        db_session.refresh(collection)

        assert collection.reporting_period_end_date == datetime.date(2024, 12, 31)
        assert collection.submission_period_start_date == datetime.date(2024, 12, 31)

    def test_update_collection_name_duplicate_raises_error(self, db_session, factories):
        grant = factories.grant.create()
        factories.collection.create(name="Collection One", grant=grant)
        collection2 = factories.collection.create(name="Collection Two", grant=grant)

        with pytest.raises(DuplicateValueError):
            update_collection(collection2, name="Collection One")

    def test_update_collection_enable_multiple_submissions(self, db_session, factories):
        collection = factories.collection.create()
        question = factories.question.create(form__collection=collection, data_type=QuestionDataType.TEXT_SINGLE_LINE)

        updated = update_collection(
            collection,
            allow_multiple_submissions=True,
            multiple_submissions_are_managed_by_service=False,
            submission_name_question_id=question.id,
            submission_guidance="  Some guidance  ",
        )

        assert updated.allow_multiple_submissions is True
        assert updated.multiple_submissions_are_managed_by_service is False
        assert updated.submission_name_question_id == question.id
        assert updated.submission_guidance == "Some guidance"

    def test_update_collection_enable_multiple_submissions_managed_by_service(self, db_session, factories):
        collection = factories.collection.create()
        question = factories.question.create(form__collection=collection, data_type=QuestionDataType.TEXT_SINGLE_LINE)

        updated = update_collection(
            collection,
            allow_multiple_submissions=True,
            multiple_submissions_are_managed_by_service=True,
            submission_name_question_id=question.id,
            submission_guidance=None,
        )

        assert updated.allow_multiple_submissions is True
        assert updated.multiple_submissions_are_managed_by_service is True
        assert updated.submission_name_question_id == question.id
        assert updated.submission_guidance is None

    def test_update_collection_enable_multiple_submissions_stores_blank_guidance_as_none(self, db_session, factories):
        collection = factories.collection.create()
        question = factories.question.create(form__collection=collection, data_type=QuestionDataType.TEXT_SINGLE_LINE)

        updated = update_collection(
            collection,
            allow_multiple_submissions=True,
            multiple_submissions_are_managed_by_service=False,
            submission_name_question_id=question.id,
            submission_guidance="   ",
        )

        assert updated.submission_guidance is None

    @pytest.mark.parametrize(
        "partial_settings",
        [
            {"allow_multiple_submissions": True},
            {"multiple_submissions_are_managed_by_service": True},
            {"submission_name_question_id": None},
            {"submission_guidance": "Some guidance"},
            {"allow_multiple_submissions": True, "multiple_submissions_are_managed_by_service": False},
            {"allow_multiple_submissions": False, "submission_name_question_id": None, "submission_guidance": None},
        ],
    )
    def test_update_collection_multiple_submission_settings_raise_unless_all_provided(
        self, db_session, factories, partial_settings
    ):
        collection = factories.collection.create()

        with pytest.raises(ValueError, match="must all be provided together"):
            update_collection(collection, **partial_settings)

    def test_update_collection_enable_multiple_submissions_without_question_raises(self, db_session, factories):
        collection = factories.collection.create()

        with pytest.raises(ValueError, match="submission_name_question_id must be provided"):
            update_collection(
                collection,
                allow_multiple_submissions=True,
                multiple_submissions_are_managed_by_service=False,
                submission_name_question_id=None,
                submission_guidance=None,
            )

    def test_update_collection_enable_public_sign_Up(self, db_session, factories):
        collection = factories.collection.create()

        updated = update_collection(collection, allow_public_sign_up=True)

        assert updated.allow_public_sign_up is True

    def test_update_collection_disable_public_sign_up(self, db_session, factories):
        collection = factories.collection.create(allow_public_sign_up=True)

        updated = update_collection(collection, allow_public_sign_up=False)

        assert updated.allow_public_sign_up is False

    def test_update_collection_disable_multiple_submissions_clears_settings(self, db_session, factories):
        question = factories.question.create(data_type=QuestionDataType.TEXT_SINGLE_LINE)
        collection = question.form.collection
        collection.allow_multiple_submissions = True
        collection.submission_name_question_id = question.id
        collection.multiple_submissions_are_managed_by_service = True
        collection.submission_guidance = "Existing guidance"
        db_session.commit()

        updated = update_collection(
            collection,
            allow_multiple_submissions=False,
            multiple_submissions_are_managed_by_service=False,
            submission_name_question_id=None,
            submission_guidance=None,
        )

        assert updated.allow_multiple_submissions is False
        assert updated.multiple_submissions_are_managed_by_service is False
        assert updated.submission_name_question_id is None
        assert updated.submission_guidance is None

    @pytest.mark.parametrize(
        "setting", ["multiple_submissions_are_managed_by_service", "submission_name_question_id", "submission_guidance"]
    )
    def test_update_collection_disable_multiple_submissions_raises_for_enabled_values(
        self, db_session, factories, setting
    ):
        collection = factories.collection.create()
        question = factories.question.create(form__collection=collection, data_type=QuestionDataType.TEXT_SINGLE_LINE)
        enabled_values = {
            "multiple_submissions_are_managed_by_service": True,
            "submission_name_question_id": question.id,
            "submission_guidance": "Some guidance",
        }
        settings = {
            "allow_multiple_submissions": False,
            "multiple_submissions_are_managed_by_service": False,
            "submission_name_question_id": None,
            "submission_guidance": None,
        } | {setting: enabled_values[setting]}

        with pytest.raises(ValueError, match="allow_multiple_submissions cannot be set to False"):
            update_collection(collection, **settings)

    def test_update_collection_disable_multiple_submissions_raises_when_submissions_exist(self, db_session, factories):
        question = factories.question.create(data_type=QuestionDataType.TEXT_SINGLE_LINE)
        collection = question.form.collection
        collection.allow_multiple_submissions = True
        collection.submission_name_question_id = question.id
        db_session.flush()
        factories.submission.create(
            collection=collection,
            mode=SubmissionModeEnum.LIVE,
            answers=[FactoryAnswer(question, TextSingleLineAnswer("Project A"))],
        )

        with pytest.raises(ValueError, match="Cannot disable multiple submissions"):
            update_collection(
                collection,
                allow_multiple_submissions=False,
                multiple_submissions_are_managed_by_service=False,
                submission_name_question_id=None,
                submission_guidance=None,
            )

    def test_update_collection_disable_multiple_submissions_allows_preview_submissions(self, db_session, factories):
        question = factories.question.create(data_type=QuestionDataType.TEXT_SINGLE_LINE)
        collection = question.form.collection
        collection.allow_multiple_submissions = True
        collection.submission_name_question_id = question.id
        db_session.flush()
        factories.submission.create(
            collection=collection,
            mode=SubmissionModeEnum.PREVIEW,
            answers=[FactoryAnswer(question, TextSingleLineAnswer("Project A"))],
        )

        update_collection(
            collection,
            allow_multiple_submissions=False,
            multiple_submissions_are_managed_by_service=False,
            submission_name_question_id=None,
            submission_guidance=None,
        )

        assert collection.allow_multiple_submissions is False

    def test_update_collection_submission_name_question_raises_for_nonexistent_id(self, db_session, factories):
        collection = factories.collection.create()

        with pytest.raises(ValueError, match="submission_name_question_id must be a question ID"):
            update_collection(
                collection,
                allow_multiple_submissions=True,
                multiple_submissions_are_managed_by_service=False,
                submission_name_question_id=uuid.uuid4(),
                submission_guidance=None,
            )

    def test_update_collection_submission_name_question_raises_for_group(self, db_session, factories):
        collection = factories.collection.create()
        group = factories.group.create(form__collection=collection)

        with pytest.raises(ValueError, match="submission_name_question_id must be a question ID"):
            update_collection(
                collection,
                allow_multiple_submissions=True,
                multiple_submissions_are_managed_by_service=False,
                submission_name_question_id=group.id,
                submission_guidance=None,
            )

    def test_update_collection_change_submission_name_question_raises_when_live_submissions_exist(
        self, db_session, factories
    ):
        question = factories.question.create(data_type=QuestionDataType.TEXT_SINGLE_LINE)
        other_question = factories.question.create(form=question.form, data_type=QuestionDataType.TEXT_SINGLE_LINE)
        collection = question.form.collection
        collection.allow_multiple_submissions = True
        collection.submission_name_question_id = question.id
        db_session.flush()
        factories.submission.create(
            collection=collection,
            mode=SubmissionModeEnum.LIVE,
            answers=[FactoryAnswer(question, TextSingleLineAnswer("Project A"))],
        )

        with pytest.raises(ValueError, match="Cannot change the submission name question"):
            update_collection(
                collection,
                allow_multiple_submissions=True,
                multiple_submissions_are_managed_by_service=False,
                submission_name_question_id=other_question.id,
                submission_guidance=None,
            )

    def test_update_collection_change_submission_name_question_raises_when_test_submissions_exist(
        self, db_session, factories
    ):
        question = factories.question.create(data_type=QuestionDataType.TEXT_SINGLE_LINE)
        other_question = factories.question.create(form=question.form, data_type=QuestionDataType.TEXT_SINGLE_LINE)
        collection = question.form.collection
        collection.allow_multiple_submissions = True
        collection.submission_name_question_id = question.id
        db_session.flush()
        factories.submission.create(
            collection=collection,
            mode=SubmissionModeEnum.TEST,
            answers=[FactoryAnswer(question, TextSingleLineAnswer("Project A"))],
        )

        with pytest.raises(ValueError, match="Cannot change the submission name question"):
            update_collection(
                collection,
                allow_multiple_submissions=True,
                multiple_submissions_are_managed_by_service=False,
                submission_name_question_id=other_question.id,
                submission_guidance=None,
            )

    def test_update_collection_same_submission_name_question_allowed_when_submissions_exist(
        self, db_session, factories
    ):
        question = factories.question.create(data_type=QuestionDataType.TEXT_SINGLE_LINE)
        collection = question.form.collection
        collection.allow_multiple_submissions = True
        collection.submission_name_question_id = question.id
        db_session.flush()
        factories.submission.create(
            collection=collection,
            mode=SubmissionModeEnum.LIVE,
            answers=[FactoryAnswer(question, TextSingleLineAnswer("Project A"))],
        )

        updated = update_collection(
            collection,
            allow_multiple_submissions=True,
            multiple_submissions_are_managed_by_service=False,
            submission_name_question_id=question.id,
            submission_guidance=None,
        )

        assert updated.submission_name_question_id == question.id

    def test_update_collection_change_submission_name_question_allows_preview_submissions(self, db_session, factories):
        question = factories.question.create(data_type=QuestionDataType.TEXT_SINGLE_LINE)
        other_question = factories.question.create(form=question.form, data_type=QuestionDataType.TEXT_SINGLE_LINE)
        collection = question.form.collection
        collection.allow_multiple_submissions = True
        collection.submission_name_question_id = question.id
        db_session.flush()
        factories.submission.create(
            collection=collection,
            mode=SubmissionModeEnum.PREVIEW,
            answers=[FactoryAnswer(question, TextSingleLineAnswer("Project A"))],
        )

        updated = update_collection(
            collection,
            allow_multiple_submissions=True,
            multiple_submissions_are_managed_by_service=False,
            submission_name_question_id=other_question.id,
            submission_guidance=None,
        )

        assert updated.submission_name_question_id == other_question.id

    def test_update_collection_change_submission_name_question_allowed_when_no_submissions(self, db_session, factories):
        question = factories.question.create(data_type=QuestionDataType.TEXT_SINGLE_LINE)
        other_question = factories.question.create(form=question.form, data_type=QuestionDataType.TEXT_SINGLE_LINE)
        collection = question.form.collection
        collection.allow_multiple_submissions = True
        collection.submission_name_question_id = question.id
        db_session.commit()

        updated = update_collection(
            collection,
            allow_multiple_submissions=True,
            multiple_submissions_are_managed_by_service=False,
            submission_name_question_id=other_question.id,
            submission_guidance=None,
        )

        assert updated.submission_name_question_id == other_question.id

    def test_update_collection_without_arguments(self, db_session, factories):
        collection = factories.collection.create(
            name="Original Name",
            reporting_period_start_date=datetime.date(2024, 1, 1),
            reporting_period_end_date=datetime.date(2024, 12, 31),
        )

        updated_collection = update_collection(collection)

        assert updated_collection.name == "Original Name"
        assert updated_collection.reporting_period_start_date == datetime.date(2024, 1, 1)
        assert updated_collection.reporting_period_end_date == datetime.date(2024, 12, 31)

    @pytest.mark.parametrize(
        "from_status, to_status",
        (
            (CollectionStatusEnum.DRAFT, CollectionStatusEnum.SCHEDULED),
            (CollectionStatusEnum.SCHEDULED, CollectionStatusEnum.DRAFT),
            (CollectionStatusEnum.SCHEDULED, CollectionStatusEnum.OPEN),
            (CollectionStatusEnum.OPEN, CollectionStatusEnum.CLOSED),
        ),
    )
    def test_valid_status_transition(self, db_session, factories, from_status, to_status):
        grant = factories.grant.create(status=GrantStatusEnum.LIVE)
        collection = factories.collection.create(
            grant=grant,
            status=from_status,
            reporting_period_start_date=datetime.date(2024, 1, 1),
            reporting_period_end_date=datetime.date(2024, 12, 31),
            submission_period_start_date=datetime.date(2025, 1, 1),
            submission_period_end_date=datetime.date(2025, 1, 31),
        )

        # Required for DRAFT->SCHEDULED and SCHEDULED->LIVE transition
        grant_recipient = factories.grant_recipient.create(grant=grant)
        user = factories.user.create()
        factories.user_role.create(
            user=user,
            organisation=grant_recipient.organisation,
            grant=grant,
            permissions=[RoleEnum.MEMBER, RoleEnum.DATA_PROVIDER],
        )

        updated_collection = update_collection(collection, status=to_status)

        assert updated_collection.status == to_status

        from_db = db_session.get(Collection, collection.id)
        assert from_db.status == to_status

    @pytest.mark.parametrize(
        "from_status, to_status",
        (
            (CollectionStatusEnum.DRAFT, CollectionStatusEnum.OPEN),
            (CollectionStatusEnum.DRAFT, CollectionStatusEnum.CLOSED),
            (CollectionStatusEnum.SCHEDULED, CollectionStatusEnum.CLOSED),
            (CollectionStatusEnum.OPEN, CollectionStatusEnum.DRAFT),
            (CollectionStatusEnum.OPEN, CollectionStatusEnum.SCHEDULED),
            (CollectionStatusEnum.CLOSED, CollectionStatusEnum.DRAFT),
            (CollectionStatusEnum.CLOSED, CollectionStatusEnum.SCHEDULED),
            (CollectionStatusEnum.CLOSED, CollectionStatusEnum.OPEN),
        ),
    )
    def test_invalid_status_transition_raises_state_transition_error(
        self, db_session, factories, from_status, to_status
    ):
        collection = factories.collection.create(
            status=from_status,
            reporting_period_start_date=datetime.date(2024, 1, 1),
            reporting_period_end_date=datetime.date(2024, 12, 31),
            submission_period_start_date=datetime.date(2025, 1, 1),
            submission_period_end_date=datetime.date(2025, 1, 31),
        )

        with pytest.raises(StateTransitionError) as exc_info:
            update_collection(collection, status=to_status)

        assert exc_info.value.from_state == from_status.value
        assert exc_info.value.to_state == to_status.value
        assert exc_info.value.model == "Collection"

    def test_draft_to_scheduled_requires_live_grant(self, db_session, factories):
        grant = factories.grant.create(status=GrantStatusEnum.DRAFT)
        collection = factories.collection.create(
            grant=grant,
            status=CollectionStatusEnum.DRAFT,
            reporting_period_start_date=datetime.date(2024, 1, 1),
            reporting_period_end_date=datetime.date(2024, 12, 31),
            submission_period_start_date=datetime.date(2025, 1, 1),
            submission_period_end_date=datetime.date(2025, 1, 31),
        )

        with pytest.raises(GrantMustBeLiveError):
            update_collection(collection, status=CollectionStatusEnum.SCHEDULED)

    @pytest.mark.freeze_time("2025-01-10 12:00:00")
    def test_opening_a_scheduled_report_only_after_submission_start_period(self, db_session, factories):
        grant = factories.grant.create(status=GrantStatusEnum.LIVE)
        collection = factories.collection.create(
            grant=grant,
            status=CollectionStatusEnum.SCHEDULED,
            reporting_period_start_date=datetime.date(2024, 1, 1),
            reporting_period_end_date=datetime.date(2024, 12, 31),
            submission_period_start_date=datetime.date(2025, 1, 1),
            submission_period_end_date=datetime.date(2025, 1, 31),
        )

        # Required for DRAFT->SCHEDULED and SCHEDULED->OPEN transition
        grant_recipient = factories.grant_recipient.create(grant=grant)
        user = factories.user.create()
        factories.user_role.create(
            user=user,
            organisation=grant_recipient.organisation,
            grant=grant,
            permissions=[RoleEnum.MEMBER, RoleEnum.DATA_PROVIDER],
        )

        updated_collection = update_collection(collection, status=CollectionStatusEnum.OPEN)

        assert updated_collection.status == CollectionStatusEnum.OPEN

        from_db = db_session.get(Collection, collection.id)
        assert from_db.status == CollectionStatusEnum.OPEN

    @pytest.mark.parametrize(
        "missing_date_field",
        (
            "submission_period_start_date",
            "submission_period_end_date",
        ),
    )
    def test_draft_to_scheduled_requires_submission_dates(self, db_session, factories, missing_date_field):
        grant = factories.grant.create(status=GrantStatusEnum.LIVE)
        date_kwargs = {
            "reporting_period_start_date": datetime.date(2024, 1, 1),
            "reporting_period_end_date": datetime.date(2024, 12, 31),
            "submission_period_start_date": datetime.date(2025, 1, 1),
            "submission_period_end_date": datetime.date(2025, 1, 31),
        }
        date_kwargs[missing_date_field] = None

        collection = factories.collection.create(grant=grant, status=CollectionStatusEnum.DRAFT, **date_kwargs)

        with pytest.raises(CollectionChronologyError) as exc_info:
            update_collection(collection, status=CollectionStatusEnum.SCHEDULED)

        assert "submission period dates must be set" in str(exc_info.value)

    @pytest.mark.freeze_time("2025-01-30 10:00:00")
    def test_update_collection_close_collection_before_submission_end_date_raises_error(self, db_session, factories):
        collection = factories.collection.create(
            status=CollectionStatusEnum.OPEN,
            reporting_period_start_date=datetime.date(2024, 1, 1),
            reporting_period_end_date=datetime.date(2024, 12, 31),
            submission_period_start_date=datetime.date(2025, 1, 1),
            submission_period_end_date=datetime.date(2025, 1, 31),
        )

        with pytest.raises(CollectionChronologyError) as exc_info:
            update_collection(
                collection,
                status=CollectionStatusEnum.CLOSED,
            )

        assert (
            f"You cannot close the report for submissions before the submission period "
            f"end date of {collection.submission_period_end_date}"
        ) in str(exc_info.value)

    @pytest.mark.freeze_time("2025-03-30 10:00:00")
    def test_close_collection_updates_submission_status(self, db_session, factories):
        collection = factories.collection.create(
            status=CollectionStatusEnum.OPEN,
            reporting_period_start_date=datetime.date(2024, 1, 1),
            reporting_period_end_date=datetime.date(2024, 12, 31),
            submission_period_start_date=datetime.date(2025, 1, 1),
            submission_period_end_date=datetime.date(2025, 1, 31),
        )

        factories.question.create(form__collection=collection)

        submission_not_started = factories.submission.create(collection=collection)
        assert submission_not_started.status == SubmissionStatusEnum.NOT_STARTED

        update_collection(
            collection,
            status=CollectionStatusEnum.CLOSED,
        )

        from_db = db_session.get(Submission, submission_not_started.id)
        assert from_db.status == SubmissionStatusEnum.NOT_SUBMITTED

    def test_update_reminder_email_business_days(self, db_session, factories):
        collection = factories.collection.create(reminder_email_business_days_before_closing=5)

        updated = update_collection(collection, reminder_email_business_days_before_closing=3)

        assert updated.reminder_email_business_days_before_closing == 3

        from_db = db_session.get(Collection, collection.id)
        assert from_db.reminder_email_business_days_before_closing == 3

    def test_update_reminder_email_business_days_not_provided_leaves_unchanged(self, db_session, factories):
        collection = factories.collection.create(reminder_email_business_days_before_closing=7)

        update_collection(collection, name="New Name")

        from_db = db_session.get(Collection, collection.id)
        assert from_db.reminder_email_business_days_before_closing == 7


class TestGetSubmission:
    def test_get_submission(self, db_session, factories):
        submission = factories.submission.create()
        from_db = get_submission(submission_id=submission.id)
        assert from_db is not None

    def test_get_submission_for_grant_recipient(self, db_session, factories):
        grant_recipient = factories.grant_recipient.create()
        grant_recipient2 = factories.grant_recipient.create()
        submission = factories.submission.create(grant_recipient=grant_recipient)
        from_db = get_submission(submission_id=submission.id, grant_recipient_id=grant_recipient.id)
        assert from_db is not None

        with pytest.raises(NoResultFound):
            get_submission(submission_id=submission.id, grant_recipient_id=grant_recipient2.id)

    def test_get_submissions_by_grant_recipient_collection_returns_single_submission(self, db_session, factories):
        grant_recipient = factories.grant_recipient.create()
        collection = factories.collection.create(grant=grant_recipient.grant)
        submission = factories.submission.create(
            collection=collection, grant_recipient=grant_recipient, mode=SubmissionModeEnum.LIVE
        )

        from_db = get_submissions_by_grant_recipient_collection(
            grant_recipient=grant_recipient, collection_id=collection.id
        )

        assert len(from_db) == 1
        assert from_db[0].id == submission.id

    def test_get_submissions_by_grant_recipient_collection_returns_multiple_submissions(self, db_session, factories):
        grant_recipient = factories.grant_recipient.create()
        collection = factories.collection.create(grant=grant_recipient.grant)
        submission_1 = factories.submission.create(
            collection=collection, grant_recipient=grant_recipient, mode=SubmissionModeEnum.LIVE
        )
        submission_2 = factories.submission.create(
            collection=collection, grant_recipient=grant_recipient, mode=SubmissionModeEnum.LIVE
        )

        from_db = get_submissions_by_grant_recipient_collection(
            grant_recipient=grant_recipient, collection_id=collection.id
        )

        assert len(from_db) == 2
        assert {s.id for s in from_db} == {submission_1.id, submission_2.id}

    def test_get_submissions_by_grant_recipient_collection_returns_empty_list_for_other_recipient(
        self, db_session, factories
    ):
        grant_recipient = factories.grant_recipient.create()
        grant_recipient2 = factories.grant_recipient.create(grant=grant_recipient.grant)
        collection = factories.collection.create(grant=grant_recipient.grant)
        factories.submission.create(
            collection=collection, grant_recipient=grant_recipient, mode=SubmissionModeEnum.LIVE
        )

        from_db = get_submissions_by_grant_recipient_collection(
            grant_recipient=grant_recipient2, collection_id=collection.id
        )

        assert from_db == []

    def test_get_submission_with_full_schema(self, db_session, factories, track_sql_queries):
        submission = factories.submission.create()
        submission_id = submission.id
        forms = factories.form.create_batch(3, collection=submission.collection)
        for form in forms:
            factories.question.create_batch(3, form=form)

        with track_sql_queries() as queries:
            from_db = get_submission(submission_id=submission_id, with_full_schema=True)
        assert from_db is not None

        # Expected queries:
        # * Load the submission with collection and forms(sections)
        # * Load the submission events
        # * Load the submission data_sources
        # * For all forms, load the top-level components and their expressions
        # * For all forms, load every component, their expressions, their nested components+expressions
        # * For all forms, load every component's set of component references
        assert len(queries) == 6

        # Iterate over all the related models; check that no further SQL queries are emitted. The count is just a noop.
        count = 0
        with track_sql_queries() as queries:
            for f in from_db.collection.forms:
                for q in f._all_components:
                    for _e in q.expressions:
                        count += 1
                    for _ocr in q.owned_component_references:
                        count += 1

        assert queries == []


class TestGetFormById:
    def test_get_form(self, db_session, factories, track_sql_queries):
        form = factories.form.create()

        # fetching the form directly
        from_db = get_form_by_id(form_id=form.id)
        assert from_db.id == form.id

    def test_get_form_with_all_questions(self, db_session, factories, track_sql_queries):
        form = factories.form.create()
        question_one = factories.question.create(form=form)
        question_two = factories.question.create(form=form)
        factories.expression.create_batch(5, question=question_one, type_=ExpressionType.CONDITION, statement="")
        factories.expression.create_batch(5, question=question_two, type_=ExpressionType.CONDITION, statement="")

        # fetching the form and eagerly loading all questions and their expressions
        from_db = get_form_by_id(form_id=form.id, with_all_questions=True)

        # check we're not sending off more round trips to the database when interacting with the ORM
        count = 0
        with track_sql_queries() as queries:
            for q in from_db.cached_questions:
                for _e in q.expressions:
                    count += 1

        assert count == 10 and queries == []

    def test_get_form_with_all_questions_does_not_n_plus_1_on_component_references(
        self, db_session, factories, track_sql_queries
    ):
        form = factories.form.create()
        form_id = form.id
        independent_question = factories.question.create(form=form, name="allocation amount")
        factories.question.create_batch(
            5,
            form=form,
            hint=InterpolationStatement(
                "Referencing " + ExpressionReference.from_question(independent_question).wrapped
            ),
        )

        with track_sql_queries() as queries:
            from_db = get_form_by_id(form_id=form_id, with_all_questions=True)

        # Expected queries:
        # * Load the form
        # * Load all flat components and their expressions
        # * Load any nested components and their expressions
        # * Load every component's set of owned component references
        # * Load the form's top-level components and their expressions (redundant, but left with the ORM)
        assert len(queries) == 5

        count = 0
        with track_sql_queries() as queries:
            for component in from_db.cached_all_components:
                for _ocr in component.owned_component_references:
                    count += 1

        assert count == 5 and len(queries) == 0

    def test_get_form_with_grant(self, db_session, factories, track_sql_queries):
        form = factories.form.create()

        from_db = get_form_by_id(form_id=form.id, grant_id=form.collection.grant_id)

        with track_sql_queries() as queries:
            # access the grant; should be no more queries as eagerly loaded
            _ = from_db.collection.grant

        assert len(queries) == 0


def test_create_form(db_session, factories):
    collection = factories.collection.create()
    form = create_form(title="Test Form", collection=collection)
    assert form is not None
    assert form.id is not None
    assert form.title == "Test Form"
    assert form.order == 0
    assert form.slug == "test-form"


def test_form_name_unique_in_collection(db_session, factories):
    collection = factories.collection.create()
    form = create_form(title="test form", collection=collection)
    assert form

    with pytest.raises(DuplicateValueError):
        create_form(title="test form", collection=collection)


def test_move_form_up_down(db_session, factories):
    form1 = factories.form.create()
    form2 = factories.form.create(collection=form1.collection)

    assert form1
    assert form2

    assert form1.order == 0
    assert form2.order == 1

    # Move form 2 up
    move_form_up(form2)

    assert form1.order == 1
    assert form2.order == 0

    # Move form 2 down
    move_form_down(form2)

    assert form1.order == 0
    assert form2.order == 1


def test_get_question(db_session, factories):
    q = factories.question.create()
    from_db = get_question_by_id(question_id=q.id)
    assert from_db is not None


def test_get_group(db_session, factories):
    g = factories.group.create()
    from_db = get_group_by_id(group_id=g.id)
    assert from_db is not None


class TestCreateGroup:
    def test_create_group(self, db_session, factories):
        form = factories.form.create()
        group = create_group(
            form=form,
            text=InterpolationStatement("Test Group"),
        )

        assert group is not None
        assert form.components[0] == group

    def test_create_group_presentation_options(self, db_session, factories):
        form = factories.form.create()
        group = create_group(
            form=form,
            text=InterpolationStatement("Test Group"),
            presentation_options=QuestionPresentationOptions(show_questions_on_the_same_page=True),
        )

        assert group is not None
        assert form.components[0] == group
        assert group.presentation_options.show_questions_on_the_same_page is True

    def test_create_nested_components(self, db_session, factories, track_sql_queries, app, monkeypatch):
        form = factories.form.create()

        group = create_group(
            form=form,
            text=InterpolationStatement("Test Group"),
        )

        create_question(
            form=form,
            text=InterpolationStatement("Top Level Question"),
            hint=InterpolationStatement("Top Level Question Hint"),
            name="Top Level Question Name",
            data_type=QuestionDataType.TEXT_SINGLE_LINE,
            expression_context=ExpressionContext(),
        )

        depth = 2
        monkeypatch.setitem(app.config, "MAX_NESTED_GROUP_LEVELS", depth)

        def add_sub_group(parent, current_depth):
            # todo: separate tests to only cover one thing - the separate read from db here should be
            #       covered separately where a create_batch can be used to keep the tests fast
            for i in range(2):
                create_question(
                    form=form,
                    text=InterpolationStatement(f"Sub Question {current_depth} {i}"),
                    hint=InterpolationStatement(f"Sub Question Hint {current_depth} {i}"),
                    name=f"Sub Question Name {current_depth} {i}",
                    data_type=QuestionDataType.TEXT_SINGLE_LINE,
                    expression_context=ExpressionContext(),
                    parent=parent,
                )
            sub_group = create_group(
                form=form, text=InterpolationStatement(f"Sub Group {current_depth}"), parent=parent
            )
            if current_depth < depth:
                add_sub_group(sub_group, current_depth + 1)

        add_sub_group(group, 1)

        assert group is not None

        with track_sql_queries() as queries:
            from_db = get_form_by_id(form_id=form.id, with_all_questions=True)

        # we can get information on all the expressions and questions in the form with
        # no subsequent queries (at any level of nesting)
        qids = []
        eids = []
        with track_sql_queries() as queries:

            def iterate_components(components):
                for component in components:
                    for expression in component.expressions:
                        eids.append(expression.id)
                    if isinstance(component, Question):
                        qids.append(component.id)
                    elif isinstance(component, Group):
                        qids.append(component.id)
                        iterate_components(component.components)

            iterate_components(from_db.components)

        assert queries == []

        # the forms components are limited to ones with a direct relationship and no parents
        assert len(from_db.components) == 2
        assert len(from_db.cached_questions) == 5

    def test_cannot_create_nested_groups_with_show_questions_on_the_same_page(self, db_session, factories):
        form = factories.form.create()
        parent_group = create_group(
            form=form,
            text=InterpolationStatement("Test group top"),
            presentation_options=QuestionPresentationOptions(show_questions_on_the_same_page=True),
        )

        with pytest.raises(NestedGroupDisplayTypeSamePageException):
            create_group(
                form=form,
                text=InterpolationStatement("Test group child"),
                parent=parent_group,
            )

    def test_cannot_create_nested_group_with_more_than_max_levels_of_nesting(self, app, db_session, factories):
        assert app.config["MAX_NESTED_GROUP_LEVELS"] == 1, (
            "If changing the max level of nested groups, ensure you add tests to that level of nesting"
        )
        form = factories.form.create()
        grand_parent_group = create_group(
            form=form,
            text=InterpolationStatement("Level 1"),
        )
        parent_group = create_group(
            form=form,
            text=InterpolationStatement("Level 2"),
            parent=grand_parent_group,
        )

        with pytest.raises(NestedGroupException):
            create_group(
                form=form,
                text=InterpolationStatement("Child group Level 3"),
                parent=parent_group,
            )


class TestGroupNameExists:
    def test_group_name_exists(self, db_session, factories):
        form = factories.form.create()
        form2 = factories.form.create()
        assert group_name_exists("Test group", form_id=form.id) is False
        assert group_name_exists("Test group", form_id=form2.id) is False

        factories.group.create(name="Test group", form=form)

        assert group_name_exists("Test group", form_id=form.id) is True
        assert group_name_exists("Test group", form_id=form2.id) is False

    def test_group_name_exists_checks_across_slug_namespace(self, db_session, factories):
        question = factories.question.create(text="Test group", slug="test-group")

        assert group_name_exists("Test group", form_id=question.form.id) is True
        assert group_name_exists("Test group", form_id=uuid.uuid4()) is False
        assert group_name_exists("Test Group", form_id=question.form.id) is True
        assert group_name_exists("Test-group", form_id=question.form.id) is True
        assert group_name_exists("Test-Group", form_id=question.form.id) is True
        assert group_name_exists("Test Group 1", form_id=question.form.id) is False


class TestUpdateGroup:
    def test_update_group(self, db_session, factories):
        group = factories.group.create(
            text="Test group",
            presentation_options=QuestionPresentationOptions(show_questions_on_the_same_page=True),
            add_another=False,
        )

        assert group.presentation_options.show_questions_on_the_same_page is True

        updated_group = update_group(
            group, expression_context=ExpressionContext(), name="Updated test group", add_another=True
        )

        assert updated_group.name == "Updated test group"
        assert updated_group.text == "Updated test group"
        assert updated_group.slug == "updated-test-group"
        assert updated_group.add_another is True

        updated_group = update_group(
            group,
            expression_context=ExpressionContext(),
            presentation_options=QuestionPresentationOptions(show_questions_on_the_same_page=False),
        )

        assert updated_group.presentation_options.show_questions_on_the_same_page is False

    def test_update_group_unique_overlap(self, db_session, factories):
        form = factories.form.create()
        create_group(form=form, text=InterpolationStatement("Overlap group name"))
        group = create_group(
            form=form,
            text=InterpolationStatement("Test group"),
        )

        with pytest.raises(DuplicateValueError):
            update_group(
                group,
                expression_context=ExpressionContext(),
                name="Overlap group name",
            )

    def test_update_group_with_nested_groups_cant_enable_same_page(self, db_session, factories):
        form = factories.form.create()
        parent_group = create_group(
            form=form,
            text=InterpolationStatement("Test group top"),
            presentation_options=QuestionPresentationOptions(show_questions_on_the_same_page=False),
        )
        create_group(
            form=form,
            text=InterpolationStatement("Test group child"),
            parent=parent_group,
        )

        with pytest.raises(NestedGroupDisplayTypeSamePageException):
            update_group(
                parent_group,
                expression_context=ExpressionContext(),
                presentation_options=QuestionPresentationOptions(show_questions_on_the_same_page=True),
            )
        assert parent_group.presentation_options.show_questions_on_the_same_page is False

    def test_update_group_with_question_dependencies_cant_enable_same_page(self, db_session, factories):
        form = factories.form.create()
        group = create_group(
            form=form,
            text=InterpolationStatement("Test group"),
            presentation_options=QuestionPresentationOptions(show_questions_on_the_same_page=False),
        )
        user = factories.user.create()
        q1 = factories.question.create(form=form, parent=group)
        _ = factories.question.create(
            form=form,
            parent=group,
            expressions=[
                Expression.from_evaluatable_expression(
                    GreaterThan(subject_reference=ExpressionReference.from_question(q1), minimum_value=100),
                    ExpressionType.CONDITION,
                    user,
                )
            ],
        )

        with pytest.raises(DependencyOrderException):
            update_group(
                group,
                expression_context=ExpressionContext(),
                presentation_options=QuestionPresentationOptions(show_questions_on_the_same_page=True),
            )
        assert group.presentation_options.show_questions_on_the_same_page is False

    def test_update_group_with_question_dependencies_can_disable_same_page(self, db_session, factories):
        form = factories.form.create()
        group = create_group(
            form=form,
            text=InterpolationStatement("Test group"),
            presentation_options=QuestionPresentationOptions(show_questions_on_the_same_page=True),
        )
        user = factories.user.create()
        q1 = factories.question.create(form=form, parent=group)
        q2 = factories.question.create(
            form=form,
            parent=group,
        )

        q2.expressions.append(
            Expression.from_evaluatable_expression(
                GreaterThan(subject_reference=ExpressionReference.from_question(q1), minimum_value=100),
                ExpressionType.CONDITION,
                user,
            )
        )

        update_group(
            group,
            expression_context=ExpressionContext(),
            presentation_options=QuestionPresentationOptions(show_questions_on_the_same_page=False),
        )
        assert group.presentation_options.show_questions_on_the_same_page is False

    def test_update_group_cannot_disable_same_page_when_group_has_validations(self, db_session, factories):
        from app.common.data.interfaces.collections import GroupHasValidationsCannotBeOnePerPageException

        form = factories.form.create()
        group = create_group(
            form=form,
            text=InterpolationStatement("Test group"),
            presentation_options=QuestionPresentationOptions(show_questions_on_the_same_page=True),
        )
        question = factories.question.create(form=form, parent=group, data_type=QuestionDataType.NUMBER)
        user = factories.user.create()
        add_component_validation(
            group,
            user,
            CustomExpression(
                custom_expression=EvaluationStatement(f"(({question.safe_qid})) > 0"),
                custom_message=InterpolationStatement("Must be positive"),
            ),
        )

        with pytest.raises(GroupHasValidationsCannotBeOnePerPageException):
            update_group(
                group,
                expression_context=ExpressionContext(),
                presentation_options=QuestionPresentationOptions(show_questions_on_the_same_page=False),
            )
        assert group.presentation_options.show_questions_on_the_same_page is True

    def test_update_group_with_guidance_fields(self, db_session, factories):
        form = factories.form.create()
        group = create_group(
            form=form,
            text=InterpolationStatement("Test Question Name"),
        )

        assert group.guidance_heading is None
        assert group.guidance_body is None
        assert group.add_another_guidance_body is None

        updated_group = update_group(
            group=group,
            expression_context=ExpressionContext(),
            guidance_heading="How to answer this question",
            guidance_body=InterpolationStatement("This is detailed guidance with **markdown** formatting."),
            add_another_guidance_body=InterpolationStatement("What to expect when filling in this groups answers"),
        )

        assert updated_group.guidance_heading == "How to answer this question"
        assert updated_group.guidance_body == "This is detailed guidance with **markdown** formatting."
        assert updated_group.add_another_guidance_body == "What to expect when filling in this groups answers"

    def test_update_group_with_add_another_guidance_reference(self, db_session, factories):
        form = factories.form.create()
        q1 = factories.question.create(
            form=form,
        )
        group = create_group(form=form, text=InterpolationStatement("Test Question Name"), add_another=True)

        assert group.guidance_heading is None
        assert group.guidance_body is None
        assert group.add_another_guidance_body is None

        updated_group = update_group(
            group=group,
            expression_context=ExpressionContext.build_expression_context(form.collection, "interpolation", None, None),
            guidance_heading="How to answer this question",
            guidance_body=InterpolationStatement("This is detailed guidance with **markdown** formatting."),
            add_another_guidance_body=InterpolationStatement(f"Reference to first question (({q1.safe_qid}))"),
        )

        assert updated_group.add_another_guidance_body == f"Reference to first question (({q1.safe_qid}))"

    def test_update_group_with_add_another_presentation_options(self, db_session, factories):
        form = factories.form.create()
        group = create_group(form=form, text=InterpolationStatement("Test group name"))
        q1 = factories.question.create(parent=group, form=group.form)
        q2 = factories.question.create(parent=group, form=group.form)

        assert group.presentation_options.add_another_summary_line_question_ids is None

        updated_group = update_group(
            group=group,
            expression_context=ExpressionContext(),
            presentation_options=QuestionPresentationOptions(add_another_summary_line_question_ids=[q1.id, q2.id]),
        )

        assert updated_group.presentation_options.add_another_summary_line_question_ids == [q1.id, q2.id]

    def test_update_group_with_presentation_options_dont_override_existing(self, db_session, factories):
        form = factories.form.create()
        group = create_group(
            form=form,
            text=InterpolationStatement("Test group name"),
            presentation_options=QuestionPresentationOptions(
                add_another_summary_line_question_ids=[], show_questions_on_the_same_page=False
            ),
        )
        q1 = factories.question.create(parent=group, form=group.form)

        updated_group = update_group(
            group=group,
            expression_context=ExpressionContext(),
            presentation_options=QuestionPresentationOptions(add_another_summary_line_question_ids=[q1.id]),
        )

        assert updated_group.presentation_options.show_questions_on_the_same_page is False
        assert updated_group.presentation_options.add_another_summary_line_question_ids == [q1.id]

        updated_group = update_group(
            group=group,
            expression_context=ExpressionContext(),
            presentation_options=QuestionPresentationOptions(show_questions_on_the_same_page=True),
        )

        assert updated_group.presentation_options.show_questions_on_the_same_page is True
        assert updated_group.presentation_options.add_another_summary_line_question_ids == [q1.id]

    def test_update_group_containing_add_another_cant_be_add_another(self, db_session, factories):
        group = factories.group.create(add_another=False)
        factories.question.create(form=group.form, parent=group, add_another=True)

        with pytest.raises(GroupContainsAddAnotherException):
            update_group(group, expression_context=ExpressionContext(), add_another=True)
        assert group.add_another is False

    def test_update_group_inside_add_another_cant_be_add_another(self, db_session, factories):
        group = factories.group.create(add_another=True)
        factories.question.create(form=group.form, parent=group)
        group2 = factories.group.create(add_another=False, parent=group)

        with pytest.raises(AddAnotherNotValidException):
            update_group(group2, expression_context=ExpressionContext(), add_another=True)
        assert group2.add_another is False

    def test_update_group_containing_depended_on_questions_cant_be_add_another(self, db_session, factories):
        group = factories.group.create(add_another=False)
        q1 = factories.question.create(form=group.form, parent=group)
        q2 = factories.question.create(
            form=group.form,
        )
        add_component_validation(
            component=q2,
            user=factories.user.create(),
            evaluatable_expression=GreaterThan(
                subject_reference=ExpressionReference.from_question(q1), minimum_value=100
            ),
        )

        with pytest.raises(AddAnotherDependencyException) as e:
            update_group(group, expression_context=ExpressionContext(), add_another=True)
        assert group.add_another is False
        assert e.value.component == group
        assert e.value.referenced_question == q1

    def test_update_group_containing_questions_that_depend_on_each_other_can_be_add_another(
        self, db_session, factories
    ):
        group = factories.group.create(add_another=False)
        q1 = factories.question.create(form=group.form, parent=group)
        q2 = factories.question.create(form=group.form, parent=group)
        add_component_validation(
            component=q2,
            user=factories.user.create(),
            evaluatable_expression=GreaterThan(
                subject_reference=ExpressionReference.from_question(q1), minimum_value=100
            ),
        )

        update_group(group, expression_context=ExpressionContext(), add_another=True)
        assert group.add_another is True

    def test_synced_component_references(self, db_session, factories, mocker):
        form = factories.form.create()
        user = factories.user.create()
        q1 = factories.question.create(form=form, data_type=QuestionDataType.NUMBER)
        group = create_group(
            form=form,
            text=InterpolationStatement("Test group"),
            presentation_options=QuestionPresentationOptions(show_questions_on_the_same_page=True),
        )
        add_component_condition(
            group,
            user,
            GreaterThan(subject_reference=ExpressionReference.from_question(q1), minimum_value=100),
        )

        spy_validate1 = mocker.spy(collections, "_validate_and_sync_component_references")
        spy_validate2 = mocker.spy(collections, "_validate_and_sync_expression_references")

        update_group(
            group,
            expression_context=ExpressionContext(),
        )

        assert spy_validate1.call_count == 1
        assert spy_validate2.call_count == 1  # Called once for each expression

    def test_update_group_with_conditions_operator(self, db_session, factories):
        group = factories.group.create(
            text="Test group",
            conditions_operator=ConditionsOperator.ALL,
        )

        assert group.conditions_operator == ConditionsOperator.ALL

        updated_group = update_group(
            group,
            expression_context=ExpressionContext(),
            conditions_operator=ConditionsOperator.ANY,
        )

        assert updated_group.conditions_operator == ConditionsOperator.ANY

        # Test updating back to ALL
        updated_group = update_group(
            group,
            expression_context=ExpressionContext(),
            conditions_operator=ConditionsOperator.ALL,
        )

        assert updated_group.conditions_operator == ConditionsOperator.ALL


class TestCreateQuestion:
    @pytest.mark.parametrize(
        "question_type",
        [
            QuestionDataType.TEXT_SINGLE_LINE,
            QuestionDataType.EMAIL,
            QuestionDataType.URL,
        ],
    )
    def test_simple_types(self, db_session, factories, question_type):
        form = factories.form.create()
        question = create_question(
            form=form,
            text=InterpolationStatement("Test Question"),
            hint=InterpolationStatement("Test Hint"),
            name="Test Question Name",
            data_type=question_type,
            expression_context=ExpressionContext(),
        )
        assert question is not None
        assert question.id is not None
        assert question.text == "Test Question"
        assert question.hint == "Test Hint"
        assert question.name == "Test Question Name"
        assert question.data_type == question_type
        assert question.order == 0
        assert question.slug == "test-question"
        assert question.data_source is None
        assert question.data_options == QuestionDataOptions(number_type=None)

    def test_integer(self, db_session, factories):
        form = factories.form.create()
        question = create_question(
            form=form,
            text=InterpolationStatement("Test Question"),
            hint=InterpolationStatement("Test Hint"),
            name="Test Question Name",
            data_type=QuestionDataType.NUMBER,
            expression_context=ExpressionContext(),
            presentation_options=QuestionPresentationOptions(prefix="£", suffix="kg", width=NumberInputWidths.HUNDREDS),
            data_options=QuestionDataOptions(number_type=NumberTypeEnum.INTEGER),
        )
        assert question is not None
        assert question.id is not None
        assert question.text == "Test Question"
        assert question.hint == "Test Hint"
        assert question.name == "Test Question Name"
        assert question.data_type == QuestionDataType.NUMBER
        assert question.order == 0
        assert question.slug == "test-question"
        assert question.data_source is None
        assert question.prefix == "£"
        assert question.suffix == "kg"
        assert question.width == "govuk-input--width-3"
        assert question.data_options.number_type == NumberTypeEnum.INTEGER

    def test_decimal(self, db_session, factories):
        form = factories.form.create()
        question = create_question(
            form=form,
            text=InterpolationStatement("Test Question"),
            hint=InterpolationStatement("Test Hint"),
            name="Test Question Name",
            data_type=QuestionDataType.NUMBER,
            expression_context=ExpressionContext(),
            presentation_options=QuestionPresentationOptions(prefix="£", suffix="kg", width=NumberInputWidths.HUNDREDS),
            data_options=QuestionDataOptions(number_type=NumberTypeEnum.DECIMAL, max_decimal_places=2),
        )
        assert question is not None
        assert question.id is not None
        assert question.text == "Test Question"
        assert question.hint == "Test Hint"
        assert question.name == "Test Question Name"
        assert question.data_type == QuestionDataType.NUMBER
        assert question.order == 0
        assert question.slug == "test-question"
        assert question.data_source is None
        assert question.prefix == "£"
        assert question.suffix == "kg"
        assert question.width == "govuk-input--width-3"
        assert question.data_options.number_type == NumberTypeEnum.DECIMAL
        assert question.data_options.max_decimal_places == 2

    def test_text_multi_line(self, db_session, factories):
        form = factories.form.create()
        question = create_question(
            form=form,
            text=InterpolationStatement("Test Question"),
            hint=InterpolationStatement("Test Hint"),
            name="Test Question Name",
            data_type=QuestionDataType.TEXT_MULTI_LINE,
            expression_context=ExpressionContext(),
            presentation_options=QuestionPresentationOptions(rows=MultilineTextInputRows.SMALL, word_limit=500),
        )
        assert question is not None
        assert question.id is not None
        assert question.text == "Test Question"
        assert question.hint == "Test Hint"
        assert question.name == "Test Question Name"
        assert question.data_type == QuestionDataType.TEXT_MULTI_LINE
        assert question.order == 0
        assert question.slug == "test-question"
        assert question.data_source is None
        assert question.rows == 3
        assert question.word_limit == 500
        assert question.data_options == QuestionDataOptions(number_type=None)

    def test_yes_no(self, db_session, factories):
        form = factories.form.create()
        question = create_question(
            form=form,
            text=InterpolationStatement("Test Question"),
            hint=InterpolationStatement("Test Hint"),
            name="Test Question Name",
            data_type=QuestionDataType.YES_NO,
            expression_context=ExpressionContext(),
        )
        assert question is not None
        assert question.id is not None
        assert question.text == "Test Question"
        assert question.hint == "Test Hint"
        assert question.name == "Test Question Name"
        assert question.data_type == QuestionDataType.YES_NO
        assert question.order == 0
        assert question.slug == "test-question"
        assert question.data_source is None
        assert question.data_options == QuestionDataOptions(number_type=None)

    def test_radios(self, db_session, factories):
        form = factories.form.create()
        question = create_question(
            form=form,
            text=InterpolationStatement("Test Question"),
            hint=InterpolationStatement("Test Hint"),
            name="Test Question Name",
            data_type=QuestionDataType.RADIOS,
            expression_context=ExpressionContext(),
            items=["one", "two", "three"],
            presentation_options=QuestionPresentationOptions(last_data_source_item_is_distinct_from_others=True),
        )
        assert question is not None
        assert question.id is not None
        assert question.text == "Test Question"
        assert question.hint == "Test Hint"
        assert question.name == "Test Question Name"
        assert question.data_type == QuestionDataType.RADIOS
        assert question.order == 0
        assert question.slug == "test-question"
        assert question.data_source is not None
        assert [item.key for item in question.data_source.items] == ["one", "two", "three"]
        assert question.presentation_options.last_data_source_item_is_distinct_from_others is True
        assert question.data_options == QuestionDataOptions(number_type=None)

    def test_checkboxes(self, db_session, factories):
        form = factories.form.create()
        question = create_question(
            form=form,
            text=InterpolationStatement("Test Question"),
            hint=InterpolationStatement("Test Hint"),
            name="Test Question Name",
            data_type=QuestionDataType.CHECKBOXES,
            expression_context=ExpressionContext(),
            items=["one", "two", "three"],
            presentation_options=QuestionPresentationOptions(last_data_source_item_is_distinct_from_others=True),
        )
        assert question is not None
        assert question.id is not None
        assert question.text == "Test Question"
        assert question.hint == "Test Hint"
        assert question.name == "Test Question Name"
        assert question.data_type == QuestionDataType.CHECKBOXES
        assert question.order == 0
        assert question.slug == "test-question"
        assert question.data_source is not None
        assert [item.key for item in question.data_source.items] == ["one", "two", "three"]
        assert question.presentation_options.last_data_source_item_is_distinct_from_others is True
        assert question.data_options == QuestionDataOptions(number_type=None)

    def test_date(self, db_session, factories):
        form = factories.form.create()
        question = create_question(
            form=form,
            text=InterpolationStatement("Test Question"),
            hint=InterpolationStatement("Test Hint"),
            name="Test Question Name",
            data_type=QuestionDataType.DATE,
            expression_context=ExpressionContext(),
        )
        assert question is not None
        assert question.id is not None
        assert question.text == "Test Question"
        assert question.hint == "Test Hint"
        assert question.name == "Test Question Name"
        assert question.data_type == QuestionDataType.DATE
        assert question.order == 0
        assert question.slug == "test-question"
        assert question.data_source is None
        assert question.data_options == QuestionDataOptions(number_type=None)

    def test_file_upload(self, db_session, factories):
        form = factories.form.create()
        question = create_question(
            form=form,
            text=InterpolationStatement("Test Question"),
            hint=InterpolationStatement("Test Hint"),
            name="Test Question Name",
            data_type=QuestionDataType.FILE_UPLOAD,
            expression_context=ExpressionContext(),
        )
        assert question is not None
        assert question.id is not None
        assert question.text == "Test Question"
        assert question.hint == "Test Hint"
        assert question.name == "Test Question Name"
        assert question.data_type == QuestionDataType.FILE_UPLOAD
        assert question.order == 0
        assert question.slug == "test-question"
        assert question.data_source is None
        assert question.data_options == QuestionDataOptions(number_type=None)

    def test_break_if_new_question_types_added(self):
        assert len(QuestionDataType) == 10, "Add a new test above if adding a new question type"

    def test_question_requires_data_type(self, db_session, factories):
        form = factories.form.create()
        with pytest.raises(IntegrityError) as e:
            create_question(
                form=form,
                text=InterpolationStatement("Test Question"),
                hint=InterpolationStatement("Test Hint"),
                name="Test Question Name",
                data_type=None,
                expression_context=ExpressionContext(),
            )
        assert "ck_component_type_question_requires_data_type" in str(e.value)

    def test_question_associated_with_group(self, db_session, factories):
        form = factories.form.create()
        group = factories.group.create(form=form, order=0)
        question = create_question(
            form=form,
            text=InterpolationStatement("Test Question"),
            hint=InterpolationStatement("Test Hint"),
            name="Test Question Name",
            data_type=QuestionDataType.TEXT_SINGLE_LINE,
            expression_context=ExpressionContext(),
            parent=group,
        )
        assert question.parent == group
        assert question.order == 0

    def test_validates_component_references(self, db_session, factories, mocker):
        form = factories.form.create()
        spy_validate1 = mocker.spy(collections, "_validate_and_sync_component_references")
        spy_validate2 = mocker.spy(collections, "_validate_and_sync_expression_references")

        create_question(
            form=form,
            text=InterpolationStatement("Test Question"),
            hint=InterpolationStatement("Test Hint"),
            name="Test Question Name",
            data_type=QuestionDataType.TEXT_SINGLE_LINE,
            expression_context=ExpressionContext(),
        )

        assert spy_validate1.call_count == 1
        assert spy_validate2.call_count == 0  # No expressions to validate


class TestUpdateQuestion:
    @pytest.mark.parametrize(
        "question_type",
        [
            QuestionDataType.TEXT_SINGLE_LINE,
            QuestionDataType.EMAIL,
            QuestionDataType.URL,
        ],
    )
    def test_simple_types(self, db_session, factories, question_type):
        form = factories.form.create()
        question = create_question(
            form=form,
            text=InterpolationStatement("Test Question"),
            hint=InterpolationStatement("Test Hint"),
            name="Test Question Name",
            data_type=question_type,
            expression_context=ExpressionContext(),
        )
        assert question is not None
        assert question.data_source is None

        updated_question = update_question(
            question=question,
            expression_context=ExpressionContext(),
            text=InterpolationStatement("Updated Question"),
            hint=InterpolationStatement("Updated Hint"),
            name="Updated Question Name",
        )

        assert updated_question.text == "Updated Question"
        assert updated_question.hint == "Updated Hint"
        assert updated_question.name == "Updated Question Name"
        assert updated_question.data_type == question_type
        assert updated_question.slug == "updated-question"
        assert updated_question.data_source is None

    def test_number(self, db_session, factories):
        form = factories.form.create()
        question = create_question(
            form=form,
            text=InterpolationStatement("Test Question"),
            hint=InterpolationStatement("Test Hint"),
            name="Test Question Name",
            data_type=QuestionDataType.NUMBER,
            expression_context=ExpressionContext(),
            presentation_options=QuestionPresentationOptions(prefix="£", suffix="kg", width=NumberInputWidths.HUNDREDS),
            data_options=QuestionDataOptions(number_type=NumberTypeEnum.INTEGER),
        )
        assert question is not None
        assert question.data_options.number_type == NumberTypeEnum.INTEGER
        assert question.data_options.max_decimal_places is None

        updated_question = update_question(
            question=question,
            expression_context=ExpressionContext(),
            text=InterpolationStatement("Updated Question"),
            hint=InterpolationStatement("Updated Hint"),
            name="Updated Question Name",
            presentation_options=QuestionPresentationOptions(
                prefix="$", suffix="lbs", width=NumberInputWidths.MILLIONS
            ),
            data_options=QuestionDataOptions(number_type=NumberTypeEnum.DECIMAL, max_decimal_places=2),
        )

        assert updated_question.text == "Updated Question"
        assert updated_question.hint == "Updated Hint"
        assert updated_question.name == "Updated Question Name"
        assert updated_question.data_type == QuestionDataType.NUMBER
        assert updated_question.slug == "updated-question"
        assert updated_question.prefix == "$"
        assert updated_question.suffix == "lbs"
        assert updated_question.width == "govuk-input--width-5"
        assert question.data_options.number_type == NumberTypeEnum.DECIMAL
        assert question.data_options.max_decimal_places == 2

    def test_text_multi_line(self, db_session, factories):
        form = factories.form.create()
        question = create_question(
            form=form,
            text=InterpolationStatement("Test Question"),
            hint=InterpolationStatement("Test Hint"),
            name="Test Question Name",
            data_type=QuestionDataType.TEXT_MULTI_LINE,
            expression_context=ExpressionContext(),
            presentation_options=QuestionPresentationOptions(rows=MultilineTextInputRows.SMALL, word_limit=500),
        )
        assert question is not None

        updated_question = update_question(
            question=question,
            expression_context=ExpressionContext(),
            text=InterpolationStatement("Updated Question"),
            hint=InterpolationStatement("Updated Hint"),
            name="Updated Question Name",
            presentation_options=QuestionPresentationOptions(rows=MultilineTextInputRows.LARGE, word_limit=None),
        )

        assert updated_question.text == "Updated Question"
        assert updated_question.hint == "Updated Hint"
        assert updated_question.name == "Updated Question Name"
        assert updated_question.data_type == QuestionDataType.TEXT_MULTI_LINE
        assert updated_question.slug == "updated-question"
        assert updated_question.rows == 10
        assert updated_question.word_limit is None

    def test_yes_no(self, db_session, factories):
        form = factories.form.create()
        question = create_question(
            form=form,
            text=InterpolationStatement("Test Question"),
            hint=InterpolationStatement("Test Hint"),
            name="Test Question Name",
            data_type=QuestionDataType.YES_NO,
            expression_context=ExpressionContext(),
        )
        assert question is not None

        updated_question = update_question(
            question=question,
            expression_context=ExpressionContext(),
            text=InterpolationStatement("Updated Question"),
            hint=InterpolationStatement("Updated Hint"),
            name="Updated Question Name",
        )

        assert updated_question.text == "Updated Question"
        assert updated_question.hint == "Updated Hint"
        assert updated_question.name == "Updated Question Name"
        assert updated_question.data_type == QuestionDataType.YES_NO
        assert updated_question.slug == "updated-question"

    def test_radios(self, db_session, factories):
        form = factories.form.create()
        question = create_question(
            form=form,
            text=InterpolationStatement("Test Question"),
            hint=InterpolationStatement("Test Hint"),
            name="Test Question Name",
            data_type=QuestionDataType.RADIOS,
            expression_context=ExpressionContext(),
            items=["option 1", "option 2", "option 3"],
            presentation_options=QuestionPresentationOptions(last_data_source_item_is_distinct_from_others=False),
        )
        assert question is not None
        assert question.data_source_items == "option 1\noption 2\noption 3"
        item_ids = [item.id for item in question.data_source.items]
        assert question.presentation_options.last_data_source_item_is_distinct_from_others is False

        updated_question = update_question(
            question=question,
            expression_context=ExpressionContext(),
            text=InterpolationStatement("Updated Question"),
            hint=InterpolationStatement("Updated Hint"),
            name="Updated Question Name",
            items=["option 3", "option 4", "option-1"],
            presentation_options=QuestionPresentationOptions(last_data_source_item_is_distinct_from_others=True),
        )

        assert updated_question.text == "Updated Question"
        assert updated_question.hint == "Updated Hint"
        assert updated_question.name == "Updated Question Name"
        assert updated_question.data_type == QuestionDataType.RADIOS
        assert updated_question.slug == "updated-question"

        # last data source item setting removes it from this helper property
        assert updated_question.data_source_items == "option 3\noption 4"

        # Test that data source item IDs for existing/updated items are retained; new options are created.
        assert updated_question.data_source.items[0].id == item_ids[2]
        assert updated_question.data_source.items[1].id not in item_ids
        assert updated_question.data_source.items[2].id == item_ids[0]

        # The dropped item has been deleted
        assert db_session.get(DataSourceItem, item_ids[1]) is None

        assert question.presentation_options.last_data_source_item_is_distinct_from_others is True

    def test_update_radios_question_options_errors_on_referenced_data_items(self, db_session, factories):
        form = factories.form.create()
        user = factories.user.create()
        referenced_question = create_question(
            form=form,
            text=InterpolationStatement("Test Question"),
            hint=InterpolationStatement("Test Hint"),
            name="Test Question Name",
            data_type=QuestionDataType.RADIOS,
            expression_context=ExpressionContext(),
            items=["option 1", "option 2", "option 3"],
        )
        assert referenced_question is not None
        assert referenced_question.data_source_items == "option 1\noption 2\noption 3"

        items = referenced_question.data_source.items
        anyof_expression = AnyOf(
            subject_reference=ExpressionReference.from_question(referenced_question),
            items=[{"key": items[1].key, "label": items[1].label}],
        )

        first_dependent_question = factories.question.create(form=form)
        add_component_condition(first_dependent_question, user, anyof_expression)

        second_dependent_question = factories.question.create(form=form)
        add_component_condition(second_dependent_question, user, anyof_expression)

        with pytest.raises(DataSourceItemReferenceDependencyException) as error:
            update_question(
                question=referenced_question,
                expression_context=ExpressionContext(),
                text=InterpolationStatement("Updated Question"),
                hint=InterpolationStatement("Updated Hint"),
                name="Updated Question Name",
                items=["option 3", "option 4", "option-1"],
            )
        assert referenced_question == error.value.question_being_edited
        assert len(error.value.data_source_item_dependency_map) == 2
        assert (
            first_dependent_question and second_dependent_question in error.value.data_source_item_dependency_map.keys()
        )

    def test_update_checkboxes_question_options_errors_on_referenced_data_items(self, db_session, factories):
        form = factories.form.create()
        user = factories.user.create()
        referenced_question = create_question(
            form=form,
            text=InterpolationStatement("Test Question"),
            hint=InterpolationStatement("Test Hint"),
            name="Test Question Name",
            data_type=QuestionDataType.CHECKBOXES,
            expression_context=ExpressionContext(),
            items=["option 1", "option 2", "option 3"],
        )
        assert referenced_question is not None
        assert referenced_question.data_source_items == "option 1\noption 2\noption 3"

        items = referenced_question.data_source.items
        specifically_expression = Specifically(
            subject_reference=ExpressionReference.from_question(referenced_question),
            item={"key": items[1].key, "label": items[1].label},
        )

        first_dependent_question = factories.question.create(form=form)
        add_component_condition(first_dependent_question, user, specifically_expression)

        second_dependent_question = factories.question.create(form=form)
        add_component_condition(second_dependent_question, user, specifically_expression)

        with pytest.raises(DataSourceItemReferenceDependencyException) as error:
            update_question(
                question=referenced_question,
                expression_context=ExpressionContext(),
                text=InterpolationStatement("Updated Question"),
                hint=InterpolationStatement("Updated Hint"),
                name="Updated Question Name",
                items=["option 3", "option 4", "option-1"],
            )
        assert referenced_question == error.value.question_being_edited
        assert len(error.value.data_source_item_dependency_map) == 2
        assert (
            first_dependent_question and second_dependent_question in error.value.data_source_item_dependency_map.keys()
        )

    def test_checkboxes(self, db_session, factories):
        form = factories.form.create()
        question = create_question(
            form=form,
            text=InterpolationStatement("Test Question"),
            hint=InterpolationStatement("Test Hint"),
            name="Test Question Name",
            data_type=QuestionDataType.CHECKBOXES,
            expression_context=ExpressionContext(),
            items=["option 1", "option 2", "option 3"],
            presentation_options=QuestionPresentationOptions(last_data_source_item_is_distinct_from_others=False),
        )
        assert question is not None
        assert question.data_source_items == "option 1\noption 2\noption 3"
        item_ids = [item.id for item in question.data_source.items]
        assert question.presentation_options.last_data_source_item_is_distinct_from_others is False

        updated_question = update_question(
            question=question,
            expression_context=ExpressionContext(),
            text=InterpolationStatement("Updated Question"),
            hint=InterpolationStatement("Updated Hint"),
            name="Updated Question Name",
            items=["option 3", "option 4", "option-1"],
            presentation_options=QuestionPresentationOptions(last_data_source_item_is_distinct_from_others=True),
        )

        assert updated_question.text == "Updated Question"
        assert updated_question.hint == "Updated Hint"
        assert updated_question.name == "Updated Question Name"
        assert updated_question.data_type == QuestionDataType.CHECKBOXES
        assert updated_question.slug == "updated-question"

        # last data source item setting removes it from this helper property
        assert updated_question.data_source_items == "option 3\noption 4"

        # Test that data source item IDs for existing/updated items are retained; new options are created.
        assert updated_question.data_source.items[0].id == item_ids[2]
        assert updated_question.data_source.items[1].id not in item_ids
        assert updated_question.data_source.items[2].id == item_ids[0]

        # The dropped item has been deleted
        assert db_session.get(DataSourceItem, item_ids[1]) is None

        assert question.presentation_options.last_data_source_item_is_distinct_from_others is True

    def test_date(self, db_session, factories):
        form = factories.form.create()
        question = create_question(
            form=form,
            text=InterpolationStatement("Test Question"),
            hint=InterpolationStatement("Test Hint"),
            name="Test Question Name",
            data_type=QuestionDataType.DATE,
            expression_context=ExpressionContext(),
            items=None,
            presentation_options=QuestionPresentationOptions(),
        )
        assert question is not None
        assert question.data_source_items is None
        assert question.presentation_options is not None
        assert question.slug == "test-question"

        updated_question = update_question(
            question=question,
            expression_context=ExpressionContext(),
            text=InterpolationStatement("Updated Question"),
            hint=InterpolationStatement("Updated Hint"),
            name="Updated Question Name",
        )

        assert updated_question.text == "Updated Question"
        assert updated_question.hint == "Updated Hint"
        assert updated_question.name == "Updated Question Name"
        assert updated_question.data_type == QuestionDataType.DATE
        assert updated_question.slug == "updated-question"

    def test_file_upload(self, db_session, factories):
        form = factories.form.create()
        question = create_question(
            form=form,
            text=InterpolationStatement("Test Question"),
            hint=InterpolationStatement("Test Hint"),
            name="Test Question Name",
            data_type=QuestionDataType.FILE_UPLOAD,
            expression_context=ExpressionContext(),
        )
        assert question is not None

        updated_question = update_question(
            question=question,
            expression_context=ExpressionContext(),
            text=InterpolationStatement("Updated Question"),
            hint=InterpolationStatement("Updated Hint"),
            name="Updated Question Name",
        )

        assert updated_question.text == "Updated Question"
        assert updated_question.hint == "Updated Hint"
        assert updated_question.name == "Updated Question Name"
        assert updated_question.data_type == QuestionDataType.FILE_UPLOAD
        assert updated_question.slug == "updated-question"

    def test_break_if_new_question_types_added(self):
        assert len(QuestionDataType) == 10, "Add a new test above if adding a new question type"

    def test_update_question_with_guidance_fields(self, db_session, factories):
        form = factories.form.create()
        question = create_question(
            form=form,
            text=InterpolationStatement("Test Question"),
            hint=InterpolationStatement("Test Hint"),
            name="Test Question Name",
            data_type=QuestionDataType.TEXT_SINGLE_LINE,
            expression_context=ExpressionContext(),
        )

        updated_question = update_question(
            question=question,
            expression_context=ExpressionContext(),
            guidance_heading="How to answer this question",
            guidance_body=InterpolationStatement("This is detailed guidance with **markdown** formatting."),
        )

        assert updated_question.guidance_heading == "How to answer this question"
        assert updated_question.guidance_body == "This is detailed guidance with **markdown** formatting."

    def test_update_question_guidance_optional_parameters(self, db_session, factories):
        form = factories.form.create()
        question = create_question(
            form=form,
            text=InterpolationStatement("Test Question"),
            hint=InterpolationStatement("Test Hint"),
            name="Test Question Name",
            data_type=QuestionDataType.TEXT_SINGLE_LINE,
            expression_context=ExpressionContext(),
        )

        question.guidance_heading = "Initial heading"
        question.guidance_body = InterpolationStatement("Initial body")

        updated_question = update_question(
            question=question,
            expression_context=ExpressionContext(),
            text=InterpolationStatement("Updated Question Text"),
        )

        assert updated_question.text == "Updated Question Text"
        assert updated_question.guidance_heading == "Initial heading"
        assert updated_question.guidance_body == "Initial body"

    def test_update_question_clear_guidance_fields(self, db_session, factories):
        form = factories.form.create()
        question = create_question(
            form=form,
            text=InterpolationStatement("Test Question"),
            hint=InterpolationStatement("Test Hint"),
            name="Test Question Name",
            data_type=QuestionDataType.TEXT_SINGLE_LINE,
            expression_context=ExpressionContext(),
        )

        question.guidance_heading = "Initial heading"
        question.guidance_body = InterpolationStatement("Initial body")

        updated_question = update_question(
            question=question,
            expression_context=ExpressionContext(),
            guidance_heading=None,
            guidance_body=None,
        )

        assert updated_question.guidance_heading is None
        assert updated_question.guidance_body is None

    def test_validates_component_and_expression_references(self, db_session, factories, mocker):
        form = factories.form.create()
        user = factories.user.create()
        question = create_question(
            form=form,
            text=InterpolationStatement("Test Question"),
            hint=InterpolationStatement("Test Hint"),
            name="Test Question Name",
            data_type=QuestionDataType.NUMBER,
            expression_context=ExpressionContext(),
        )
        add_component_validation(
            question,
            user,
            GreaterThan(subject_reference=ExpressionReference.from_question(question), minimum_value=0, inclusive=True),
        )

        spy_validate1 = mocker.spy(collections, "_validate_and_sync_component_references")
        spy_validate2 = mocker.spy(collections, "_validate_and_sync_expression_references")

        update_question(
            question=question,
            expression_context=ExpressionContext(),
            guidance_heading=None,
            guidance_body=None,
        )

        assert spy_validate1.call_count == 1
        assert spy_validate2.call_count == 1  # called once for each expression on the question

    def test_update_question_with_conditions_operator(self, db_session, factories):
        form = factories.form.create()
        question = create_question(
            form=form,
            text=InterpolationStatement("Test Question"),
            hint=InterpolationStatement("Test Hint"),
            name="Test Question Name",
            data_type=QuestionDataType.TEXT_SINGLE_LINE,
            expression_context=ExpressionContext(),
        )

        # Default should be ALL
        assert question.conditions_operator == ConditionsOperator.ALL

        # Update to ANY
        updated_question = update_question(
            question=question,
            expression_context=ExpressionContext(),
            conditions_operator=ConditionsOperator.ANY,
        )

        assert updated_question.conditions_operator == ConditionsOperator.ANY

        # Update back to ALL
        updated_question = update_question(
            question=question,
            expression_context=ExpressionContext(),
            conditions_operator=ConditionsOperator.ALL,
        )

        assert updated_question.conditions_operator == ConditionsOperator.ALL


class TestMoveComponent:
    def test_move_question_up_down(db_session, factories):
        form = factories.form.create()
        q1 = factories.question.create(form=form)
        q2 = factories.question.create(form=form)
        q3 = factories.question.create(form=form)

        assert q1
        assert q2
        assert q3

        assert q1.order == 0
        assert q2.order == 1
        assert q3.order == 2

        move_component_up(q2)

        assert q1.order == 1
        assert q2.order == 0
        assert q3.order == 2

        move_component_down(q1)

        assert q1.order == 2
        assert q2.order == 0
        assert q3.order == 1

    def test_move_question_with_dependencies_through_reference(db_session, factories):
        form = factories.form.create()
        q1, q2 = factories.question.create_batch(2, form=form)
        q3 = factories.question.create(form=form, text=f"Reference to (({q2.safe_qid}))")

        # q3 can't move above its dependency q2
        with pytest.raises(DependencyOrderException) as e:
            move_component_up(q3)
        assert e.value.question == q3
        assert e.value.depends_on_question == q2

        # q2 can't move below q3 which depends on it
        with pytest.raises(DependencyOrderException) as e:
            move_component_down(q2)
        assert e.value.question == q3
        assert e.value.depends_on_question == q2

        # q1 can freely move up and down as it has no dependencies
        move_component_down(q1)
        move_component_down(q1)
        move_component_up(q1)
        move_component_up(q1)

        # q2 can move up as q3 can still depend on it
        move_component_up(q2)

    def test_move_question_with_dependencies_through_expression(db_session, factories):
        form = factories.form.create()
        user = factories.user.create()
        q1, q2 = factories.question.create_batch(2, form=form)
        q3 = factories.question.create(
            form=form,
            expressions=[
                Expression.from_evaluatable_expression(
                    GreaterThan(subject_reference=ExpressionReference.from_question(q2), minimum_value=3000),
                    ExpressionType.CONDITION,
                    user,
                )
            ],
        )

        # q3 can't move above its dependency q2
        with pytest.raises(DependencyOrderException) as e:
            move_component_up(q3)
        assert e.value.question == q3
        assert e.value.depends_on_question == q2

        # q2 can't move below q3 which depends on it
        with pytest.raises(DependencyOrderException) as e:
            move_component_down(q2)
        assert e.value.question == q3
        assert e.value.depends_on_question == q2

        # q1 can freely move up and down as it has no dependencies
        move_component_down(q1)
        move_component_down(q1)
        move_component_up(q1)
        move_component_up(q1)

        # q2 can move up as q3 can still depend on it
        move_component_up(q2)

    # you can't move a group above questions that it itself depends on
    def test_move_group_with_dependencies_through_reference(db_session, factories):
        form = factories.form.create()
        q1, q2 = factories.question.create_batch(2, form=form)
        group = factories.group.create(
            form=form, guidance_heading="test", guidance_body=f"Reference to (({q2.safe_qid}))"
        )

        # group can't move above its dependency q2
        with pytest.raises(DependencyOrderException) as e:
            move_component_up(group)
        assert e.value.question == group
        assert e.value.depends_on_question == q2

        # q2 can't move below group which depends on it
        with pytest.raises(DependencyOrderException) as e:
            move_component_down(q2)
        assert e.value.question == group
        assert e.value.depends_on_question == q2

        # q1 can freely move up and down as it has no dependencies
        move_component_down(q1)
        move_component_down(q1)
        move_component_up(q1)
        move_component_up(q1)

    def test_move_group_with_dependencies_through_expression(db_session, factories):
        form = factories.form.create()
        user = factories.user.create()
        q1, q2 = factories.question.create_batch(2, form=form)
        group = factories.group.create(
            form=form,
            expressions=[
                Expression.from_evaluatable_expression(
                    GreaterThan(subject_reference=ExpressionReference.from_question(q2), minimum_value=3000),
                    ExpressionType.CONDITION,
                    user,
                )
            ],
        )

        # group can't move above its dependency q2
        with pytest.raises(DependencyOrderException) as e:
            move_component_up(group)
        assert e.value.question == group
        assert e.value.depends_on_question == q2

        # q2 can't move below group which depends on it
        with pytest.raises(DependencyOrderException) as e:
            move_component_down(q2)
        assert e.value.question == group
        assert e.value.depends_on_question == q2

        # q1 can freely move up and down as it has no dependencies
        move_component_down(q1)
        move_component_down(q1)
        move_component_up(q1)
        move_component_up(q1)

    def test_move_group_with_child_dependencies(db_session, factories):
        form = factories.form.create()
        user = factories.user.create()
        q1 = factories.question.create(form=form)
        group = factories.group.create(form=form)
        _ = factories.question.create(
            form=form,
            parent=group,
            expressions=[
                Expression.from_evaluatable_expression(
                    GreaterThan(subject_reference=ExpressionReference.from_question(q1), minimum_value=3000),
                    ExpressionType.CONDITION,
                    user,
                )
            ],
        )

        # you can't move a group above a question that something in the group depends on
        with pytest.raises(DependencyOrderException) as e:
            move_component_up(group)
        assert e.value.question == group
        assert e.value.depends_on_question == q1

        with pytest.raises(DependencyOrderException) as e:
            move_component_down(q1)
        assert e.value.question == group
        assert e.value.depends_on_question == q1

    def test_move_question_with_group_dependencies(db_session, factories):
        form = factories.form.create()
        user = factories.user.create()
        group = factories.group.create(form=form)
        nested_q1 = factories.question.create(form=form, parent=group)
        q2 = factories.question.create(
            form=form,
            expressions=[
                Expression.from_evaluatable_expression(
                    GreaterThan(subject_reference=ExpressionReference.from_question(nested_q1), minimum_value=3000),
                    ExpressionType.CONDITION,
                    user,
                )
            ],
        )

        # you can't move a question above a group that it depends on a question in
        with pytest.raises(DependencyOrderException) as e:
            move_component_up(q2)
        assert e.value.question == q2
        assert e.value.depends_on_question == group

        with pytest.raises(DependencyOrderException) as e:
            move_component_down(group)
        assert e.value.question == q2
        assert e.value.depends_on_question == group

    def test_move_group_with_group_dependencies(db_session, factories):
        form = factories.form.create()
        user = factories.user.create()
        group = factories.group.create(form=form)
        nested_q1 = factories.question.create(form=form, parent=group)
        group2 = factories.group.create(form=form)
        _ = factories.question.create(
            form=form,
            parent=group2,
            expressions=[
                Expression.from_evaluatable_expression(
                    GreaterThan(subject_reference=ExpressionReference.from_question(nested_q1), minimum_value=3000),
                    ExpressionType.CONDITION,
                    user,
                )
            ],
        )

        # you can't move a group above a question in a group that it depends on
        with pytest.raises(DependencyOrderException) as e:
            move_component_up(group2)
        assert e.value.question == group2
        assert e.value.depends_on_question == group

        with pytest.raises(DependencyOrderException) as e:
            move_component_down(group)
        assert e.value.question == group2
        assert e.value.depends_on_question == group


class TestNestedGroupDependencies:
    def test_move_nested_group_with_previous_question_dependencies(db_session, factories):
        form = factories.form.create()
        user = factories.user.create()
        parent_group = factories.group.create(form=form)
        q1_in_parent_group = factories.question.create(form=form, parent=parent_group)
        child_group = factories.group.create(form=form, parent=parent_group)
        factories.question.create(
            form=form,
            expressions=[
                Expression.from_evaluatable_expression(
                    GreaterThan(
                        subject_reference=ExpressionReference.from_question(q1_in_parent_group),
                        minimum_value=3000,
                    ),
                    ExpressionType.CONDITION,
                    user,
                )
            ],
            parent=child_group,
        )

        # you can't move a nested group above a question it depends on
        with pytest.raises(DependencyOrderException) as e:
            move_component_up(child_group)
        assert e.value.question == child_group
        assert e.value.depends_on_question == q1_in_parent_group

        with pytest.raises(DependencyOrderException) as e:
            move_component_down(q1_in_parent_group)
        assert e.value.question == child_group
        assert e.value.depends_on_question == q1_in_parent_group

    def test_move_question_with_dependencies_on_nested_group(db_session, factories):
        form = factories.form.create()
        user = factories.user.create()
        parent_group = factories.group.create(form=form)
        child_group = factories.group.create(form=form, parent=parent_group)
        q1_in_child_group = factories.question.create(
            form=form,
            parent=child_group,
        )
        q2_in_parent_group = factories.question.create(
            form=form,
            parent=parent_group,
            expressions=[
                Expression.from_evaluatable_expression(
                    GreaterThan(
                        subject_reference=ExpressionReference.from_question(q1_in_child_group),
                        minimum_value=3000,
                    ),
                    ExpressionType.CONDITION,
                    user,
                )
            ],
        )

        # you can't move a nested group below a quetsion that depends on it
        with pytest.raises(DependencyOrderException) as e:
            move_component_down(child_group)
        assert e.value.question == q2_in_parent_group
        assert e.value.depends_on_question == child_group

        with pytest.raises(DependencyOrderException) as e:
            move_component_up(q2_in_parent_group)
        assert e.value.question == q2_in_parent_group
        assert e.value.depends_on_question == child_group

    def test_delete_depended_on_nested_group(db_session, factories):
        form = factories.form.create()
        user = factories.user.create()
        parent_group = factories.group.create(form=form)
        child_group = factories.group.create(form=form, parent=parent_group)
        q1_in_child_group = factories.question.create(
            form=form,
            parent=child_group,
        )
        q2_in_parent_group = factories.question.create(
            form=form,
            parent=parent_group,
            expressions=[
                Expression.from_evaluatable_expression(
                    GreaterThan(
                        subject_reference=ExpressionReference.from_question(q1_in_child_group),
                        minimum_value=3000,
                    ),
                    ExpressionType.CONDITION,
                    user,
                )
            ],
        )
        # you can't delete a nested group that a quetsion depends on
        with pytest.raises(DependencyOrderException) as e:
            delete_question(child_group)
        assert e.value.question == q2_in_parent_group
        assert e.value.depends_on_question == child_group

    def test_delete_question_depended_on_by_nested_group(db_session, factories):
        form = factories.form.create()
        user = factories.user.create()
        parent_group = factories.group.create(form=form)
        q1_in_parent_group = factories.question.create(form=form, parent=parent_group)
        child_group = factories.group.create(form=form, parent=parent_group)
        q1_in_child_group = factories.question.create(
            form=form,
            expressions=[
                Expression.from_evaluatable_expression(
                    GreaterThan(
                        subject_reference=ExpressionReference.from_question(q1_in_parent_group),
                        minimum_value=3000,
                    ),
                    ExpressionType.CONDITION,
                    user,
                )
            ],
            parent=child_group,
        )

        # You can't delete a question that is depended on by a question in a nested group
        with pytest.raises(DependencyOrderException) as e:
            delete_question(q1_in_parent_group)
        assert e.value.question == q1_in_child_group
        assert e.value.depends_on_question == q1_in_parent_group


class TestDependencyExceptionHelpers:
    def test_raise_if_nested_group_creation_not_valid_here(db_session, factories):
        form = factories.form.create()
        parent_group = factories.group.create(form=form)
        child_group = factories.group.create(form=form, parent=parent_group)

        raise_if_nested_group_creation_not_valid_here(parent_group)
        with pytest.raises(NestedGroupException):
            raise_if_nested_group_creation_not_valid_here(child_group)

    def test_raise_if_question_has_any_dependencies(db_session, factories):
        form = factories.form.create()
        user = factories.user.create()
        q1 = factories.question.create(form=form)
        q2 = factories.question.create(
            form=form,
            expressions=[
                Expression.from_evaluatable_expression(
                    GreaterThan(subject_reference=ExpressionReference.from_question(q1), minimum_value=1000),
                    ExpressionType.CONDITION,
                    user,
                )
            ],
        )

        assert raise_if_component_or_section_has_any_dependencies(q2) is None

        with pytest.raises(DependencyOrderException) as e:
            raise_if_component_or_section_has_any_dependencies(q1)
        assert e.value.question == q2
        assert e.value.depends_on_question == q1

    def test_raise_if_group_has_any_dependencies(db_session, factories):
        form = factories.form.create()
        user = factories.user.create()
        group = factories.group.create(form=form)
        nested_question = factories.question.create(parent=group, form=form)
        q2 = factories.question.create(
            form=form,
            expressions=[
                Expression.from_evaluatable_expression(
                    GreaterThan(
                        subject_reference=ExpressionReference.from_question(nested_question), minimum_value=1000
                    ),
                    ExpressionType.CONDITION,
                    user,
                )
            ],
        )

        with pytest.raises(DependencyOrderException) as e:
            raise_if_component_or_section_has_any_dependencies(nested_question)

        with pytest.raises(DependencyOrderException) as e:
            raise_if_component_or_section_has_any_dependencies(group)

        assert e.value.question == q2
        assert e.value.depends_on_question == group  # ty: ignore[unresolved

    def test_raise_if_section_has_any_dependencies(db_session, factories):
        collection = factories.collection.create()
        user = factories.user.create()
        section_with_depended_on_question = factories.form.create(collection=collection)
        dependent_section = factories.form.create(collection=collection)

        depended_on_question = factories.question.create(form=section_with_depended_on_question)
        dependent_question = factories.question.create(
            form=dependent_section,
            expressions=[
                Expression.from_evaluatable_expression(
                    GreaterThan(
                        subject_reference=ExpressionReference.from_question(depended_on_question), minimum_value=1000
                    ),
                    ExpressionType.CONDITION,
                    user,
                )
            ],
        )

        assert raise_if_component_or_section_has_any_dependencies(dependent_section) is None

        with pytest.raises(SectionComponentDependencyException) as e:
            raise_if_component_or_section_has_any_dependencies(section_with_depended_on_question)
        assert e.value.form == section_with_depended_on_question
        assert [ref.component for ref in e.value.component_references] == [dependent_question]

    def test_raise_if_group_questions_depend_on_each_other(db_session, factories):
        form = factories.form.create()
        user = factories.user.create()
        group = factories.group.create(form=form)
        q1 = factories.question.create(parent=group, form=form, data_type=QuestionDataType.NUMBER)
        q2 = factories.question.create(
            parent=group,
            form=form,
            expressions=[
                Expression.from_evaluatable_expression(
                    GreaterThan(subject_reference=ExpressionReference.from_question(q1), minimum_value=1000),
                    ExpressionType.CONDITION,
                    user,
                )
            ],
        )

        with pytest.raises(DependencyOrderException) as e:
            raise_if_group_questions_depend_on_each_other(group)
        assert e.value.question == q2
        assert e.value.depends_on_question == q1

    def test_raise_if_radios_data_source_item_reference_dependency(db_session, factories):
        form = factories.form.create()
        user = factories.user.create()
        referenced_question = create_question(
            form=form,
            text=InterpolationStatement("Test Question"),
            hint=InterpolationStatement("Test Hint"),
            name="Test Question Name",
            data_type=QuestionDataType.RADIOS,
            expression_context=ExpressionContext(),
            items=["option 1", "option 2", "option 3"],
            presentation_options=QuestionPresentationOptions(last_data_source_item_is_distinct_from_others=True),
        )
        items = referenced_question.data_source.items
        anyof_expression = AnyOf(
            subject_reference=ExpressionReference.from_question(referenced_question),
            items=[{"key": items[0].key, "label": items[0].label}],
        )

        dependent_question = factories.question.create(form=form)
        add_component_condition(dependent_question, user, anyof_expression)
        items_to_delete = [referenced_question.data_source.items[0], referenced_question.data_source.items[1]]
        with pytest.raises(DataSourceItemReferenceDependencyException) as error:
            raise_if_data_source_item_reference_dependency(referenced_question, items_to_delete)

        assert referenced_question == error.value.question_being_edited
        assert len(error.value.data_source_item_dependency_map) == 1
        assert dependent_question in error.value.data_source_item_dependency_map.keys()
        assert referenced_question.presentation_options.last_data_source_item_is_distinct_from_others is True

    def test_raise_if_checkboxes_data_source_item_reference_dependency(db_session, factories):
        form = factories.form.create()
        user = factories.user.create()
        referenced_question = create_question(
            form=form,
            text=InterpolationStatement("Test Question"),
            hint=InterpolationStatement("Test Hint"),
            name="Test Question Name",
            data_type=QuestionDataType.CHECKBOXES,
            expression_context=ExpressionContext(),
            items=["option 1", "option 2", "option 3"],
            presentation_options=QuestionPresentationOptions(last_data_source_item_is_distinct_from_others=True),
        )
        items = referenced_question.data_source.items
        specifically_expression = Specifically(
            subject_reference=ExpressionReference.from_question(referenced_question),
            item={"key": items[0].key, "label": items[0].label},
        )

        dependent_question = factories.question.create(form=form)
        add_component_condition(dependent_question, user, specifically_expression)
        items_to_delete = [referenced_question.data_source.items[0], referenced_question.data_source.items[1]]
        with pytest.raises(DataSourceItemReferenceDependencyException) as error:
            raise_if_data_source_item_reference_dependency(referenced_question, items_to_delete)

        assert referenced_question == error.value.question_being_edited
        assert len(error.value.data_source_item_dependency_map) == 1
        assert dependent_question in error.value.data_source_item_dependency_map.keys()
        assert referenced_question.presentation_options.last_data_source_item_is_distinct_from_others is True


class TestUpdateSubmissionData:
    def test_update_submission_data_single_question(self, db_session, factories):
        question = factories.question.create()
        submission = factories.submission.create(collection=question.form.collection)

        assert submission.data_manager.get(question) is None
        submission.data_manager.set(question, TextSingleLineAnswer("User submitted data"))

        assert SubmissionDataManager(submission._data).get(question) is None, (
            "The answer should not be set on the submission instance until the interface is called"
        )
        update_submission_data(submission)

        assert submission.data_manager.get(question) == TextSingleLineAnswer("User submitted data")

        submission.data_manager.set(question, TextSingleLineAnswer("User edited data"))
        assert SubmissionDataManager(submission._data).get(question) == TextSingleLineAnswer("User submitted data"), (
            "The answer should not be updated on the submission instance until the interface is called"
        )
        update_submission_data(submission)

        assert submission.data_manager.get(question) == TextSingleLineAnswer("User edited data")

    def test_update_submission_data_add_another_group_first_answer(self, db_session, factories):
        form = factories.form.create()
        group = factories.group.create(form=form, add_another=True)
        question1 = factories.question.create(form=form, parent=group)
        question2 = factories.question.create(form=form, parent=group)
        submission = factories.submission.create(collection=form.collection)

        q1_data1 = TextSingleLineAnswer("Group 1 - Question 1 - Answer 1")
        submission.data_manager.set(question1, q1_data1, add_another_index=0)

        update_submission_data(submission)

        assert submission.data_manager.get(question1, add_another_index=0) == q1_data1
        assert submission.data_manager.get(question2, add_another_index=0) is None

        q2_data1 = TextSingleLineAnswer("Group 1 - Question 2 - Answer 1")
        submission.data_manager.set(question2, q2_data1, add_another_index=0)

        update_submission_data(submission)
        assert submission.data_manager.get(question1, add_another_index=0) == q1_data1
        assert submission.data_manager.get(question2, add_another_index=0) == q2_data1

    def test_update_submission_data_add_another_group_edit_only_answer(self, db_session, factories):
        form = factories.form.create()
        group = factories.group.create(form=form, add_another=True)
        question1 = factories.question.create(form=form, parent=group)
        question2 = factories.question.create(form=form, parent=group)
        submission = factories.submission.create(collection=form.collection)

        q1_data1 = TextSingleLineAnswer("Group 1 - Question 1 - Answer 1")
        q2_data1 = TextSingleLineAnswer("Group 1 - Question 2 - Answer 1")
        submission.data_manager.set(question1, q1_data1, add_another_index=0)
        submission.data_manager.set(question2, q2_data1, add_another_index=0)

        update_submission_data(submission)

        q1_data1_updated = TextSingleLineAnswer("Group 1 - Question 1 - Updated 1")
        q2_data1_updated = TextSingleLineAnswer("Group 1 - Question 2 - Updated 1")
        submission.data_manager.set(question1, q1_data1_updated, add_another_index=0)
        submission.data_manager.set(question2, q2_data1_updated, add_another_index=0)

        update_submission_data(submission)

        assert submission.data_manager.get(question1, add_another_index=0) == q1_data1_updated
        assert submission.data_manager.get(question2, add_another_index=0) == q2_data1_updated

    def test_update_submission_data_add_another_group_another_answer(self, db_session, factories):
        form = factories.form.create()
        group = factories.group.create(form=form, add_another=True)
        question1 = factories.question.create(form=form, parent=group)
        question2 = factories.question.create(form=form, parent=group)
        q1_data1 = TextSingleLineAnswer("Group 1 - Question 1 - Answer 1")
        q2_data1 = TextSingleLineAnswer("Group 1 - Question 2 - Answer 1")
        q1_data2 = TextSingleLineAnswer("Group 1 - Question 1 - Answer 2")
        q2_data2 = TextSingleLineAnswer("Group 1 - Question 2 - Answer 2")
        submission = factories.submission.create(
            collection=form.collection,
            answers=[
                FactoryAnswer(question1, q1_data1, add_another_index=0),
                FactoryAnswer(question2, q2_data1, add_another_index=0),
            ],
        )

        submission.data_manager.set(question1, q1_data2, add_another_index=1)
        submission.data_manager.set(question2, q2_data2, add_another_index=1)
        update_submission_data(submission)

        assert submission.data_manager.get(question1, add_another_index=0) == q1_data1
        assert submission.data_manager.get(question2, add_another_index=0) == q2_data1
        assert submission.data_manager.get(question1, add_another_index=1) == q1_data2
        assert submission.data_manager.get(question2, add_another_index=1) == q2_data2

    def test_update_submission_data_add_another_group_edit_another_answer(self, db_session, factories):
        form = factories.form.create()
        group = factories.group.create(form=form, add_another=True)
        question1 = factories.question.create(form=form, parent=group)
        question2 = factories.question.create(form=form, parent=group)
        submission = factories.submission.create(
            collection=form.collection,
            answers=[
                FactoryAnswer(question1, TextSingleLineAnswer("Group 1 - Question 1 - Answer 1"), add_another_index=0),
                FactoryAnswer(question2, TextSingleLineAnswer("Group 1 - Question 2 - Answer 1"), add_another_index=0),
                FactoryAnswer(question1, TextSingleLineAnswer("Group 1 - Question 1 - Answer 2"), add_another_index=1),
                FactoryAnswer(question2, TextSingleLineAnswer("Group 1 - Question 2 - Answer 2"), add_another_index=1),
            ],
        )

        submission.data_manager.set(
            question1, TextSingleLineAnswer("Group 1 - Question 1 - Updated 1"), add_another_index=0
        )
        submission.data_manager.set(
            question2, TextSingleLineAnswer("Group 1 - Question 2 - Updated 1"), add_another_index=0
        )
        submission.data_manager.set(
            question1, TextSingleLineAnswer("Group 1 - Question 1 - Updated 2"), add_another_index=1
        )

        update_submission_data(submission)

        assert submission.data_manager.get(question1, add_another_index=0) == TextSingleLineAnswer(
            "Group 1 - Question 1 - Updated 1"
        )
        assert submission.data_manager.get(question2, add_another_index=0) == TextSingleLineAnswer(
            "Group 1 - Question 2 - Updated 1"
        )
        assert submission.data_manager.get(question1, add_another_index=1) == TextSingleLineAnswer(
            "Group 1 - Question 1 - Updated 2"
        )
        assert submission.data_manager.get(question2, add_another_index=1) == TextSingleLineAnswer(
            "Group 1 - Question 2 - Answer 2"
        )

    def test_update_submission_data_many_add_another_entries(self, db_session, factories):
        collection = factories.collection.create(
            create_completed_submissions_add_another_nested_group__test=1,
            create_completed_submissions_add_another_nested_group__use_random_data=False,
        )
        submission = collection.test_submissions[0]
        form = collection.forms[0]
        add_another_name_question = form.cached_questions[2]
        add_another_email_question = form.cached_questions[3]

        submission.data_manager.set(
            add_another_name_question, TextSingleLineAnswer("Updated 3rd name"), add_another_index=2
        )
        submission.data_manager.set(
            add_another_email_question, TextSingleLineAnswer("Updated_4th_email@stuff.com"), add_another_index=3
        )
        submission.data_manager.set(
            add_another_name_question, TextSingleLineAnswer("Added name 6"), add_another_index=5
        )
        submission.data_manager.set(
            add_another_email_question, TextSingleLineAnswer("Added_email_6@email.com"), add_another_index=5
        )
        update_submission_data(submission)

        for i in [0, 1, 3, 4]:
            assert submission.data_manager.get(add_another_name_question, add_another_index=i) == TextSingleLineAnswer(
                f"test name {i}"
            )

        for i in [0, 1, 2, 4]:
            assert submission.data_manager.get(add_another_email_question, add_another_index=i) == EmailAnswer(
                f"test_user_{i}@email.com"
            )

        assert submission.data_manager.get(add_another_name_question, add_another_index=2) == TextSingleLineAnswer(
            "Updated 3rd name"
        )
        assert submission.data_manager.get(add_another_email_question, add_another_index=3) == EmailAnswer(
            "Updated_4th_email@stuff.com"
        )

        assert submission.data_manager.get(add_another_name_question, add_another_index=5) == TextSingleLineAnswer(
            "Added name 6"
        )
        assert submission.data_manager.get(add_another_email_question, add_another_index=5) == EmailAnswer(
            "Added_email_6@email.com"
        )


class TestAddSubmissionEvent:
    def test_add_submission_event(self, db_session, factories):
        user = factories.user.create()
        form = factories.form.create()
        submission = factories.submission.create(collection=form.collection)
        db_session.add(submission)

        _add_submission_event(
            submission,
            user=user,
            event_type=SubmissionEventType.FORM_RUNNER_FORM_COMPLETED,
            related_entity_id=form.id,
        )
        _add_submission_event(submission, user=user, event_type=SubmissionEventType.SUBMISSION_SUBMITTED)

        # pull it back out of the database to also check all of the serialisation/ enums are mapped appropriately
        from_db = get_submission(submission.id, with_full_schema=True)

        assert len(from_db.events) == 2
        assert from_db.events[0].event_type == SubmissionEventType.FORM_RUNNER_FORM_COMPLETED
        assert from_db.events[0].related_entity_id == form.id
        assert from_db.events[0].data == {}

        assert from_db.events[1].event_type == SubmissionEventType.SUBMISSION_SUBMITTED
        assert from_db.events[1].related_entity_id == submission.id
        assert from_db.events[1].data == {}

    def test_add_certification_and_submission_event(self, db_session, factories):
        user = factories.user.create()
        form = factories.form.create()
        submission = factories.submission.create(collection=form.collection)
        db_session.add(submission)

        _add_submission_event(
            submission,
            user=user,
            event_type=SubmissionEventType.FORM_RUNNER_FORM_COMPLETED,
            related_entity_id=form.id,
        )
        _add_submission_event(
            submission,
            user=user,
            event_type=SubmissionEventType.SUBMISSION_SENT_FOR_CERTIFICATION,
        )
        _add_submission_event(
            submission,
            user=user,
            event_type=SubmissionEventType.SUBMISSION_APPROVED_BY_CERTIFIER,
        )
        _add_submission_event(submission, user=user, event_type=SubmissionEventType.SUBMISSION_SUBMITTED)
        _add_submission_event(
            submission,
            user=user,
            event_type=SubmissionEventType.SUBMISSION_DECLINED_BY_CERTIFIER,
            declined_reason="inaccurate data",
        )

        # pull it back out of the database to also check all of the serialisation/ enums are mapped appropriately
        from_db = get_submission(submission.id, with_full_schema=True)

        assert len(from_db.events) == 5
        assert from_db.events[0].event_type == SubmissionEventType.FORM_RUNNER_FORM_COMPLETED
        assert from_db.events[0].related_entity_id == form.id
        assert from_db.events[0].data == {}

        assert from_db.events[1].event_type == SubmissionEventType.SUBMISSION_SENT_FOR_CERTIFICATION
        assert from_db.events[1].related_entity_id == submission.id
        assert from_db.events[1].data == {}

        assert from_db.events[2].event_type == SubmissionEventType.SUBMISSION_APPROVED_BY_CERTIFIER
        assert from_db.events[2].related_entity_id == submission.id
        assert from_db.events[2].data == {}

        assert from_db.events[3].event_type == SubmissionEventType.SUBMISSION_SUBMITTED
        assert from_db.events[3].related_entity_id == submission.id
        assert from_db.events[3].data == {}

        assert from_db.events[4].event_type == SubmissionEventType.SUBMISSION_DECLINED_BY_CERTIFIER
        assert from_db.events[4].related_entity_id == submission.id
        assert from_db.events[4].data == {"declined_reason": "inaccurate data"}

    def test_reopen_submission_event(self, db_session, factories):
        user = factories.user.create()
        form = factories.form.create()
        submission = factories.submission.create(collection=form.collection)
        test_data = {
            "q_123": "First answer",
            "q_234": "Second answer",
        }
        submission._data = test_data
        db_session.add(submission)

        _add_submission_event(
            submission,
            user=user,
            event_type=SubmissionEventType.SUBMISSION_REOPENED,
            reopened_reason="Test reason",
            submission_data=submission._data,
        )
        from_db = get_submission(submission.id, with_full_schema=True)

        assert len(from_db.events) == 1
        assert from_db.events[0].event_type == SubmissionEventType.SUBMISSION_REOPENED
        assert from_db.events[0].related_entity_id == submission.id
        assert from_db.events[0].data == {"reopened_reason": "Test reason", "submission_data": test_data}

    def test_reopen_submission_event_with_helpers(self, db_session, factories):

        from app.common.collections.forms import build_question_form

        user = factories.user.create()
        form = factories.form.create()
        q1 = factories.question.create(id=uuid.UUID("d696aebc-49d2-4170-a92f-b6ef42994294"), form=form)
        submission = factories.submission.create(collection=form.collection)

        helper = SubmissionHelper(submission)
        q_form = build_question_form(
            [q1], evaluation_context=ExpressionContext(), interpolation_context=ExpressionContext()
        )(q_d696aebc49d24170a92fb6ef42994294="q1 answer")
        helper.submit_answer_for_question(q1.id, q_form, submission.created_by)

        db_session.add(submission)

        _add_submission_event(
            submission,
            user=user,
            event_type=SubmissionEventType.SUBMISSION_REOPENED,
            reopened_reason="Test reason",
            submission_data=submission._data,
        )
        from_db = get_submission(submission.id, with_full_schema=True)

        assert len(from_db.events) == 1
        assert from_db.events[0].event_type == SubmissionEventType.SUBMISSION_REOPENED
        assert from_db.events[0].related_entity_id == submission.id
        assert from_db.events[0].data == {
            "reopened_reason": "Test reason",
            "submission_data": {"d696aebc-49d2-4170-a92f-b6ef42994294": "q1 answer"},
        }

        from app.common.helpers.submission_events import SubmissionEventHelper

        event_helper = SubmissionEventHelper(submission)
        state = event_helper.submission_state
        assert state.reopened_reason == "Test reason"
        assert state.requested_or_allowed_changes_by == user

    @pytest.mark.parametrize(
        "event_type,is_form_event,exp_metric",
        [
            (
                SubmissionEventType.SUBMISSION_SENT_FOR_CERTIFICATION,
                False,
                MetricEventName.SUBMISSION_SENT_FOR_CERTIFICATION,
            ),
            (SubmissionEventType.SUBMISSION_SUBMITTED, False, MetricEventName.SUBMISSION_SUBMITTED),
            (
                SubmissionEventType.FORM_RUNNER_FORM_RESET_TO_IN_PROGRESS,
                True,
                MetricEventName.SECTION_RESET_TO_IN_PROGRESS,
            ),
            (SubmissionEventType.FORM_RUNNER_FORM_COMPLETED, True, MetricEventName.SECTION_MARKED_COMPLETE),
            (
                SubmissionEventType.FORM_RUNNER_FORM_RESET_BY_CERTIFIER,
                True,
                MetricEventName.SECTION_RESET_TO_IN_PROGRESS_BY_CERTIFIER,
            ),
            (SubmissionEventType.SUBMISSION_APPROVED_BY_CERTIFIER, False, MetricEventName.SUBMISSION_CERTIFIED),
            (
                SubmissionEventType.SUBMISSION_DECLINED_BY_CERTIFIER,
                False,
                MetricEventName.SUBMISSION_CERTIFICATION_DECLINED,
            ),
            (SubmissionEventType.SUBMISSION_REOPENED, False, MetricEventName.SUBMISSION_REOPENED),
            (SubmissionEventType.SUBMISSION_CHANGES_REQUESTED, True, MetricEventName.SUBMISSION_CHANGES_REQUESTED),
        ],
    )
    def test_add_submission_event_metrics(
        self, db_session, factories, mock_sentry_metrics, event_type, is_form_event, exp_metric
    ):

        user = factories.user.create()
        form = factories.form.create()
        submission = factories.submission.create(collection=form.collection)

        _add_submission_event(
            submission,
            user=user,
            related_entity_id=form.id if is_form_event else submission.id,
            event_type=event_type,
        )

        assert mock_sentry_metrics.call_count == 1
        assert mock_sentry_metrics.call_args[0] == (exp_metric, 1)


def test_get_collection_with_full_schema(db_session, factories, track_sql_queries):
    collection = factories.collection.create()
    forms = factories.form.create_batch(3, collection=collection)
    for form in forms:
        factories.question.create_batch(3, form=form)

    with track_sql_queries() as queries:
        from_db = get_collection(collection_id=collection.id, with_full_schema=True)
    assert from_db is not None

    # Expected queries:
    # * Initial queries for collection and user
    # * Load the forms
    # * Load the question (component)
    # * Load any of the questions (components)
    # * Load the data sources
    assert len(queries) == 6

    # No additional queries when inspecting the ORM model
    count = 0
    with track_sql_queries() as queries:
        for f in from_db.forms:
            for _q in f.cached_questions:
                count += 1

    assert queries == []


class TestIsComponentDependencyOrderValid:
    def test_with_nested_group_order(self, db_session, factories):
        form = factories.form.create()
        question = factories.question.create(form=form)
        group = factories.group.create(form=form)
        nested_question = factories.question.create(form=form, parent=group)

        assert is_component_dependency_order_valid(nested_question, question) is True

    def test_dependency_in_earlier_form_is_valid(self, db_session, factories):
        collection = factories.collection.create()
        earlier_form = factories.form.create(collection=collection)
        later_form = factories.form.create(collection=collection)
        dependency = factories.question.create(form=earlier_form)
        component = factories.question.create(form=later_form)

        assert is_component_dependency_order_valid(component, dependency) is True

    def test_dependency_in_same_form_but_later_position_is_invalid(self, db_session, factories):
        form = factories.form.create()
        component = factories.question.create(form=form)
        dependency = factories.question.create(form=form)

        assert is_component_dependency_order_valid(component, dependency) is False

    def test_dependency_in_later_form_is_invalid(self, db_session, factories):
        collection = factories.collection.create()
        earlier_form = factories.form.create(collection=collection)
        later_form = factories.form.create(collection=collection)
        dependency = factories.question.create(form=later_form)
        component = factories.question.create(form=earlier_form)

        assert is_component_dependency_order_valid(component, dependency) is False


class TestExpressions:
    def test_add_question_condition(self, db_session, factories):
        q0 = factories.question.create()
        question = factories.question.create(form=q0.form)
        user = factories.user.create()

        # configured by the user interface
        managed_expression = GreaterThan(minimum_value=3000, subject_reference=ExpressionReference.from_question(q0))

        add_component_condition(question, user, managed_expression)

        # check the serialisation and deserialisation is as expected
        from_db = get_question_by_id(question.id)

        assert len(from_db.expressions) == 1
        assert from_db.expressions[0].type_ == ExpressionType.CONDITION
        assert from_db.expressions[0].statement == f"{q0.safe_qid} > Decimal('3000')"

        # check the serialised context lines up with the values in the managed expression
        assert from_db.expressions[0].managed_name == ManagedExpressionsEnum.GREATER_THAN

        with pytest.raises(DuplicateValueError):
            add_component_condition(question, user, managed_expression)

    def test_add_question_condition_custom(self, db_session, factories):
        form = factories.form.create()
        q1, q2, q3 = factories.question.create_batch(3, form=form, data_type=QuestionDataType.NUMBER)
        user = factories.user.create()

        # configured by the user interface
        custom_expr = CustomExpression(
            custom_expression=EvaluationStatement(f"(({q1.safe_qid})) > (({q2.safe_qid}))"),
            custom_message=InterpolationStatement("Failed condition"),
        )

        add_component_condition(q3, user, custom_expr)

        # check the serialisation and deserialisation is as expected
        from_db = get_question_by_id(q3.id)

        assert len(from_db.expressions) == 1
        assert from_db.expressions[0].type_ == ExpressionType.CONDITION
        assert from_db.expressions[0].statement == f"(({q1.safe_qid})) > (({q2.safe_qid}))"

        # check the serialised context lines up with the values in the managed expression
        assert from_db.expressions[0].managed_name is None
        assert from_db.expressions[0].is_custom

    def test_add_question_condition_custom_raises_incompatible_data_type(self, db_session, factories):
        form = factories.form.create()
        q1, q2, q3 = factories.question.create_batch(3, form=form)
        user = factories.user.create()

        # configured by the user interface
        custom_expr = CustomExpression(
            custom_expression=f"(({q1.safe_qid})) > (({q2.safe_qid}))", custom_message="Failed condition"
        )
        with pytest.raises(IncompatibleDataTypeException):
            add_component_condition(q3, user, custom_expr)

    def test_add_group_condition_custom_raises_incompatible_data_type(self, db_session, factories):
        form = factories.form.create()
        q1, q2, q3 = factories.question.create_batch(3, form=form)
        group = factories.group.create(form=form)
        user = factories.user.create()

        # configured by the user interface
        custom_expr = CustomExpression(
            custom_expression=f"(({q1.safe_qid})) > (({q2.safe_qid}))", custom_message="Failed condition"
        )
        with pytest.raises(IncompatibleDataTypeException):
            add_component_condition(group, user, custom_expr)

    def test_add_condition_raises_if_same_page(self, db_session, factories):
        group = factories.group.create(
            presentation_options=QuestionPresentationOptions(show_questions_on_the_same_page=True)
        )
        q0 = factories.question.create(form=group.form, parent=group, data_type=QuestionDataType.NUMBER)
        q1 = factories.question.create(form=group.form, parent=group)
        user = factories.user.create()

        managed_expression = GreaterThan(minimum_value=3000, subject_reference=ExpressionReference.from_question(q0))

        with pytest.raises(InvalidReferenceInExpression):
            add_component_condition(q1, user, managed_expression)

        # check that the ORM has been rolled back and invalidated any changes from the interface
        assert q1.expressions == []

    def test_add_radios_question_condition(self, db_session, factories):
        q0 = factories.question.create(data_type=QuestionDataType.RADIOS)
        question = factories.question.create(form=q0.form)
        items = q0.data_source.items
        user = factories.user.create()

        # configured by the user interface
        managed_expression = AnyOf(
            subject_reference=ExpressionReference.from_question(q0),
            items=[{"key": items[0].key, "label": items[0].label}, {"key": items[1].key, "label": items[1].label}],
        )

        add_component_condition(question, user, managed_expression)

        from_db = get_question_by_id(question.id)

        assert len(from_db.expressions) == 1
        assert from_db.expressions[0].type_ == ExpressionType.CONDITION
        assert from_db.expressions[0].managed_name == ManagedExpressionsEnum.ANY_OF
        assert q0.safe_qid and items[0].key and items[1].key in from_db.expressions[0].statement

        assert len(from_db.expressions[0].component_references) == 2
        assert (
            from_db.expressions[0].component_references[0].depends_on_data_source_item_id == q0.data_source.items[0].id
        )
        assert (
            from_db.expressions[0].component_references[1].depends_on_data_source_item_id == q0.data_source.items[1].id
        )

        with pytest.raises(DuplicateValueError):
            add_component_condition(question, user, managed_expression)

    def test_add_checkboxes_question_condition(self, db_session, factories):
        q0 = factories.question.create(data_type=QuestionDataType.CHECKBOXES)
        question = factories.question.create(form=q0.form)
        items = q0.data_source.items
        user = factories.user.create()

        # configured by the user interface
        managed_expression = Specifically(
            subject_reference=ExpressionReference.from_question(q0),
            item={"key": items[0].key, "label": items[0].label},
        )

        add_component_condition(question, user, managed_expression)

        from_db = get_question_by_id(question.id)

        assert len(from_db.expressions) == 1
        assert from_db.expressions[0].type_ == ExpressionType.CONDITION
        assert from_db.expressions[0].managed_name == ManagedExpressionsEnum.SPECIFICALLY
        assert q0.safe_qid and items[0].key in from_db.expressions[0].statement

        assert len(from_db.expressions[0].component_references) == 1
        assert (
            from_db.expressions[0].component_references[0].depends_on_data_source_item_id == q0.data_source.items[0].id
        )

        with pytest.raises(DuplicateValueError):
            add_component_condition(question, user, managed_expression)

    def test_add_question_condition_blocks_on_order(self, db_session, factories):
        user = factories.user.create()
        q1 = factories.question.create()
        g1 = factories.group.create(form=q1.form)
        q2 = factories.question.create(form=q1.form)

        with pytest.raises(DependencyOrderException) as e:
            add_component_condition(
                q1,
                user,
                GreaterThan(minimum_value=1000, subject_reference=ExpressionReference.from_question(q2)),
            )
        assert e.value.form_error_message == f"You cannot use {q2.name} because it comes after this question"

        with pytest.raises(DependencyOrderException) as e:
            add_component_condition(
                g1,
                user,
                GreaterThan(minimum_value=1000, subject_reference=ExpressionReference.from_question(q2)),
            )
        assert e.value.form_error_message == f"You cannot use {q2.name} because it comes after this question group"

    def test_add_question_condition_blocks_on_add_another_question(self, db_session, factories):
        user = factories.user.create()
        q1 = factories.question.create(add_another=True)
        q2 = factories.question.create(form=q1.form)

        with pytest.raises(AddAnotherDependencyException) as e:
            add_component_condition(
                q2,
                user,
                GreaterThan(minimum_value=1000, subject_reference=ExpressionReference.from_question(q1)),
            )
        assert e.value.form_error_message == f"You cannot reference {q1.name} because it can be answered more than once"

    def test_add_question_condition_blocks_on_add_another_question_outside_group(self, db_session, factories):
        user = factories.user.create()
        q1 = factories.question.create(add_another=True)
        g1 = factories.group.create(form=q1.form, add_another=True)
        q2 = factories.question.create(form=q1.form, parent=g1)

        with pytest.raises(AddAnotherDependencyException) as e:
            add_component_condition(
                q2,
                user,
                GreaterThan(minimum_value=1000, subject_reference=ExpressionReference.from_question(q1)),
            )
        assert e.value.form_error_message == f"You cannot reference {q1.name} because it can be answered more than once"

    def test_add_question_condition_succeeds_add_another_question_inside_same_group(self, db_session, factories):
        user = factories.user.create()
        q1 = factories.question.create(add_another=True)
        g1 = factories.group.create(form=q1.form, add_another=True)
        q2 = factories.question.create(form=q1.form, parent=g1)
        q3 = factories.question.create(form=q1.form, parent=g1)

        add_component_condition(
            q3, user, GreaterThan(minimum_value=1000, subject_reference=ExpressionReference.from_question(q2))
        )
        assert len(q3.expressions) == 1

    def test_add_component_validation(self, db_session, factories, mock_sentry_metrics):
        question = factories.question.create()
        user = factories.user.create()

        # configured by the user interface
        managed_expression = GreaterThan(
            minimum_value=3000, subject_reference=ExpressionReference.from_question(question)
        )

        add_component_validation(question, user, managed_expression)

        # check the serialisation and deserialisation is as expected
        from_db = get_question_by_id(question.id)

        assert len(from_db.expressions) == 1
        assert from_db.expressions[0].type_ == ExpressionType.VALIDATION
        assert from_db.expressions[0].statement == f"{question.safe_qid} > Decimal('3000')"

        # check the serialised context lines up with the values in the managed expression
        assert from_db.expressions[0].managed_name == ManagedExpressionsEnum.GREATER_THAN

        assert mock_sentry_metrics.call_count == 1
        assert mock_sentry_metrics.call_args[0] == (MetricEventName.VALIDATION_CREATED_MANAGED, 1)

    def test_add_component_validation_custom(self, db_session, factories, mock_sentry_metrics):
        form = factories.form.create()
        q1, q2 = factories.question.create_batch(2, form=form, data_type=QuestionDataType.NUMBER)
        user = factories.user.create()

        # configured by the user interface
        custom_expression = CustomExpression(
            custom_expression=EvaluationStatement(f"(({q2.safe_qid})) >= (({q1.safe_qid}))"),
            custom_message=InterpolationStatement(f"The answer must be greater than or equal to (({q1.safe_qid}))"),
        )

        add_component_validation(q2, user, custom_expression)

        # check the serialisation and deserialisation is as expected
        from_db = get_question_by_id(q2.id)

        assert len(from_db.expressions) == 1
        assert from_db.expressions[0].type_ == ExpressionType.VALIDATION
        assert from_db.expressions[0].statement == f"(({q2.safe_qid})) >= (({q1.safe_qid}))"

        # check the serialised context lines up with the values in the managed expression
        assert from_db.expressions[0].is_managed is False
        assert from_db.expressions[0].is_custom is True
        assert mock_sentry_metrics.call_count == 1
        assert mock_sentry_metrics.call_args[0] == (MetricEventName.VALIDATION_CREATED_CUSTOM, 1)

    def test_add_component_validation_to_same_page_group_with_internal_reference(self, db_session, factories):
        from app.common.data.types import QuestionPresentationOptions

        db_form = factories.form.create()
        group = factories.group.create(
            form=db_form,
            name="Spend totals",
            presentation_options=QuestionPresentationOptions(show_questions_on_the_same_page=True),
        )
        capital = factories.question.create(
            form=db_form,
            parent=group,
            data_type=QuestionDataType.NUMBER,
        )
        revenue = factories.question.create(
            form=db_form,
            parent=group,
            data_type=QuestionDataType.NUMBER,
        )
        user = factories.user.create()

        custom_expression = CustomExpression(
            custom_expression=EvaluationStatement(f"(({capital.safe_qid})) + (({revenue.safe_qid})) <= 1000"),
            custom_message=InterpolationStatement("Capital plus revenue must not exceed 1000"),
        )

        add_component_validation(group, user, custom_expression)

        from_db = get_group_by_id(group.id)
        assert len(from_db.validations) == 1
        validation = from_db.validations[0]
        assert validation.type_ == ExpressionType.VALIDATION
        referenced_ids = {ref.depends_on_component_id for ref in validation.component_references}
        assert referenced_ids == {capital.id, revenue.id}

    def test_add_component_condition_on_group_cannot_reference_questions_inside_group(self, db_session, factories):
        from app.common.data.types import QuestionPresentationOptions

        db_form = factories.form.create()
        group = factories.group.create(
            form=db_form,
            name="Spend totals",
            presentation_options=QuestionPresentationOptions(show_questions_on_the_same_page=True),
        )
        inside_question = factories.question.create(
            form=db_form,
            parent=group,
            data_type=QuestionDataType.NUMBER,
        )
        user = factories.user.create()

        with pytest.raises(DependencyOrderException):
            add_component_condition(
                group,
                user,
                GreaterThan(subject_reference=ExpressionReference.from_question(inside_question), minimum_value=0),
            )

    def test_add_component_validation_on_group_rejects_reference_to_question_after_group(self, db_session, factories):
        from app.common.data.types import QuestionPresentationOptions

        db_form = factories.form.create()
        group = factories.group.create(
            form=db_form,
            name="Spend totals",
            presentation_options=QuestionPresentationOptions(show_questions_on_the_same_page=True),
        )
        factories.question.create(
            form=db_form,
            parent=group,
            data_type=QuestionDataType.NUMBER,
        )
        question_after_group = factories.question.create(
            form=db_form,
            name="Later question",
            data_type=QuestionDataType.NUMBER,
        )
        user = factories.user.create()

        custom_expression = CustomExpression(
            custom_expression=EvaluationStatement(f"(({question_after_group.safe_qid})) > 0"),
            custom_message=InterpolationStatement("Must be positive"),
        )

        with pytest.raises(DependencyOrderException):
            add_component_validation(group, user, custom_expression)

        from_db = get_group_by_id(group.id)
        assert from_db.validations == []

    def test_add_component_validation_on_group_rejects_wrong_data_type(self, db_session, factories):
        from app.common.data.types import QuestionPresentationOptions

        db_form = factories.form.create()
        text_question = factories.question.create(
            form=db_form,
            name="text question",
        )
        group = factories.group.create(
            form=db_form,
            name="Spend totals",
            presentation_options=QuestionPresentationOptions(show_questions_on_the_same_page=True),
        )
        factories.question.create(
            form=db_form,
            parent=group,
            data_type=QuestionDataType.NUMBER,
        )
        user = factories.user.create()

        custom_expression = CustomExpression(
            custom_expression=f"(({text_question.safe_qid})) > 0",
            custom_message="Must be positive",
        )

        with pytest.raises(IncompatibleDataTypeException):
            add_component_validation(group, user, custom_expression)

    def test_add_component_validation_custom_raises_incompatible_data_type(
        self, db_session, factories, mock_sentry_metrics
    ):
        form = factories.form.create()
        q1, q2 = factories.question.create_batch(2, form=form)
        user = factories.user.create()

        # configured by the user interface
        custom_expression = CustomExpression(
            custom_expression=f"(({q2.safe_qid})) >= (({q1.safe_qid}))",
            custom_message=f"The answer must be greater than or equal to (({q1.safe_qid}))",
        )

        with pytest.raises(IncompatibleDataTypeException):
            add_component_validation(q2, user, custom_expression)

    def test_update_expression(self, db_session, factories):
        q0 = factories.question.create()
        question = factories.question.create(form=q0.form)
        user = factories.user.create()
        managed_expression = GreaterThan(minimum_value=3000, subject_reference=ExpressionReference.from_question(q0))

        add_component_condition(question, user, managed_expression)

        updated_expression = GreaterThan(minimum_value=5000, subject_reference=ExpressionReference.from_question(q0))

        update_question_expression(question.expressions[0], updated_expression)

        assert question.expressions[0].statement == f"{q0.safe_qid} > Decimal('5000')"

    def test_update_expression_custom(self, db_session, factories):
        form = factories.form.create()
        q1, q2 = factories.question.create_batch(2, form=form, data_type=QuestionDataType.NUMBER)
        user = factories.user.create()

        # configured by the user interface
        custom_expression = CustomExpression(
            custom_expression=EvaluationStatement(f"(({q2.safe_qid})) >= (({q1.safe_qid}))"),
            custom_message=InterpolationStatement(f"The answer must be greater than or equal to (({q1.safe_qid}))"),
        )

        add_component_validation(q2, user, custom_expression)

        updated_expression = CustomExpression(
            custom_expression=EvaluationStatement(f"(({q2.safe_qid})) >= (({q1.safe_qid})) + 6"),
            custom_message=InterpolationStatement(f"The answer must be greater than or equal to (({q1.safe_qid})) + 6"),
        )

        update_question_expression(q2.expressions[0], updated_expression)

        assert q2.expressions[0].statement == f"(({q2.safe_qid})) >= (({q1.safe_qid})) + 6"

    def test_update_anyof_expression(self, db_session, factories):
        q0 = factories.question.create(data_type=QuestionDataType.RADIOS)
        question = factories.question.create(form=q0.form)
        items = q0.data_source.items
        user = factories.user.create()

        managed_expression = AnyOf(
            subject_reference=ExpressionReference.from_question(q0),
            items=[{"key": items[0].key, "label": items[0].label}, {"key": items[1].key, "label": items[1].label}],
        )

        add_component_condition(question, user, managed_expression)

        updated_expression = AnyOf(
            subject_reference=ExpressionReference.from_question(q0),
            items=[{"key": items[2].key, "label": items[2].label}],
        )

        update_question_expression(question.expressions[0], updated_expression)

        from_db = get_question_by_id(question.id)

        assert len(from_db.expressions) == 1
        assert from_db.expressions[0].type_ == ExpressionType.CONDITION
        assert from_db.expressions[0].managed_name == ManagedExpressionsEnum.ANY_OF
        assert q0.safe_qid and items[2].key in from_db.expressions[0].statement

        assert len(from_db.expressions[0].component_references) == 1
        assert (
            from_db.expressions[0].component_references[0].depends_on_data_source_item_id == q0.data_source.items[2].id
        )

    def test_update_specifically_expression(self, db_session, factories):
        q0 = factories.question.create(data_type=QuestionDataType.CHECKBOXES)
        question = factories.question.create(form=q0.form)
        items = q0.data_source.items
        user = factories.user.create()

        managed_expression = Specifically(
            subject_reference=ExpressionReference.from_question(q0),
            item={"key": items[0].key, "label": items[0].label},
        )

        add_component_condition(question, user, managed_expression)

        updated_expression = Specifically(
            subject_reference=ExpressionReference.from_question(q0),
            item={"key": items[1].key, "label": items[1].label},
        )

        update_question_expression(question.expressions[0], updated_expression)

        from_db = get_question_by_id(question.id)

        assert len(from_db.expressions) == 1
        assert from_db.expressions[0].type_ == ExpressionType.CONDITION
        assert from_db.expressions[0].managed_name == ManagedExpressionsEnum.SPECIFICALLY
        assert q0.safe_qid and items[1].key in from_db.expressions[0].statement

        assert len(from_db.expressions[0].component_references) == 1
        assert (
            from_db.expressions[0].component_references[0].depends_on_data_source_item_id == q0.data_source.items[1].id
        )

    def test_update_expression_errors_on_validation_overlap(self, db_session, factories):
        question = factories.question.create()
        user = factories.user.create()
        gt_expression = GreaterThan(minimum_value=3000, subject_reference=ExpressionReference.from_question(question))

        add_component_validation(question, user, gt_expression)

        lt_expression = LessThan(maximum_value=5000, subject_reference=ExpressionReference.from_question(question))

        add_component_validation(question, user, lt_expression)
        lt_db_expression = next(
            db_expr for db_expr in question.expressions if db_expr.managed_name == lt_expression._key
        )

        with pytest.raises(DuplicateValueError):
            update_question_expression(lt_db_expression, gt_expression)

    def test_remove_expression(self, db_session, factories):
        user = factories.user.create()
        question = factories.question.create()
        question.expressions = [
            Expression.from_evaluatable_expression(
                GreaterThan(subject_reference=ExpressionReference.from_question(question), minimum_value=3000),
                ExpressionType.VALIDATION,
                user,
            ),
        ]
        db_session.flush()

        assert len(question.expressions) == 1
        expression_id = question.expressions[0].id

        remove_question_expression(question, question.expressions[0])

        assert len(question.expressions) == 0

        with pytest.raises(NoResultFound, match="No row was found when one was required"):
            get_expression(expression_id)

    def test_get_expression(self, db_session, factories):
        expression = factories.expression.create(statement="", type_=ExpressionType.VALIDATION)

        db_expr = get_expression(expression.id)
        assert db_expr is expression

    def test_get_expression_missing(self, db_session, factories):
        factories.expression.create(statement="", type_=ExpressionType.VALIDATION)

        with pytest.raises(NoResultFound):
            get_expression(uuid.uuid4())

    def test_get_expression_by_id(self, db_session, factories, track_sql_queries):
        question = factories.question.create(data_type=QuestionDataType.NUMBER)
        user = factories.user.create()
        managed_expression = GreaterThan(
            minimum_value=100, subject_reference=ExpressionReference.from_question(question)
        )
        add_component_validation(question, user, managed_expression)

        expression_id = question.expressions[0].id
        db_session.expunge_all()  # Clear SQLAlchemy cache to force queries to be emitted again
        with track_sql_queries() as queries:
            retrieved_expression = get_expression_by_id(expression_id)

        assert len(queries) == 1

        assert retrieved_expression.id == expression_id
        assert retrieved_expression.type_ == ExpressionType.VALIDATION
        assert retrieved_expression.managed_name == "Greater than"

        with track_sql_queries() as queries:
            assert retrieved_expression.question.form.collection.grant is not None

        assert len(queries) == 0

    def test_get_expression_by_id_missing(self, db_session, factories):
        question = factories.question.create(data_type=QuestionDataType.NUMBER)
        user = factories.user.create()
        managed_expression = GreaterThan(
            minimum_value=100, subject_reference=ExpressionReference.from_question(question)
        )
        add_component_validation(question, user, managed_expression)

        with pytest.raises(NoResultFound):
            get_expression_by_id(uuid.uuid4())

    def test_get_referenced_data_source_items_by_anyof_managed_expression(self, db_session, factories):
        referenced_question = factories.question.create(data_type=QuestionDataType.RADIOS)
        items = referenced_question.data_source.items
        managed_expression = AnyOf(
            subject_reference=ExpressionReference.from_question(referenced_question),
            items=[{"key": items[0].key, "label": items[0].label}, {"key": items[1].key, "label": items[1].label}],
        )
        referenced_data_source_items = get_referenced_data_source_items_by_managed_expression(managed_expression)
        assert len(referenced_data_source_items) == 2
        assert referenced_data_source_items[0] in referenced_question.data_source.items

    def test_get_referenced_data_source_items_by_specifically_managed_expression(self, db_session, factories):
        referenced_question = factories.question.create(data_type=QuestionDataType.CHECKBOXES)
        items = referenced_question.data_source.items
        managed_expression = Specifically(
            subject_reference=ExpressionReference.from_question(referenced_question),
            item={"key": items[0].key, "label": items[0].label},
        )
        referenced_data_source_items = get_referenced_data_source_items_by_managed_expression(managed_expression)
        assert len(referenced_data_source_items) == 1
        assert referenced_data_source_items[0] == referenced_question.data_source.items[0]


class TestDeleteCollection:
    def test_delete(self, db_session, factories):
        collection = factories.collection.create()
        assert db_session.get(Collection, collection.id) is not None

        delete_collection(collection)

        assert db_session.get(Collection, collection.id) is None

    def test_delete_cascades_downstream(self, db_session, factories):
        collection = factories.collection.create()
        forms = factories.form.create_batch(2, collection=collection)
        questions = []
        for form in forms:
            questions.extend(factories.question.create_batch(2, form=form))

        delete_collection(collection)

        for form in forms:
            assert db_session.get(Form, form.id) is None

        for question in questions:
            assert db_session.get(Question, question.id) is None

    def test_can_delete_with_test_submissions(self, db_session, factories):
        collection = factories.collection.create(create_completed_submissions_conditional_question__test=True)

        assert collection.test_submissions
        assert not collection.live_submissions

        delete_collection(collection)

    def test_cannot_delete_if_live_submissions(self, db_session, factories):
        collection = factories.collection.create(create_completed_submissions_conditional_question__live=True)

        assert not collection.test_submissions
        assert collection.live_submissions

        with pytest.raises(ValueError):
            delete_collection(collection)

    def test_can_delete_with_submission_name_question_set(self, db_session, factories):
        collection = factories.collection.create(allow_multiple_submissions=True)
        form = factories.form.create(collection=collection)
        question = factories.question.create(form=form)
        collection.submission_name_question_id = question.id
        db_session.flush()

        delete_collection(collection)

        assert db_session.get(Collection, collection.id) is None
        assert db_session.get(Question, question.id) is None

    def test_delete_collection_cascades_data_sources(self, db_session, factories):
        collection = factories.collection.create()
        form = factories.form.create(collection=collection)
        question = factories.question.create(form=form, data_type=QuestionDataType.RADIOS)

        delete_collection(collection)

        assert db_session.get(Collection, collection.id) is None
        assert db_session.get(DataSource, question.data_source.id) is None
        for item in question.data_source.items:
            assert db_session.get(DataSourceItem, item.id) is None

    def test_delete_collection_cascades_grant_recipient_data_set(self, db_session, factories):
        grant = factories.grant.create()
        collection = factories.collection.create(grant=grant)
        factories.grant_recipient.create_batch(2, grant=grant)
        data_source = factories.data_source.create(
            grant=grant,
            collection=collection,
            type=DataSourceType.GRANT_RECIPIENT,
            create_gr_org_items=True,
        )
        org_item_ids = [item.id for item in data_source.organisation_items]
        assert org_item_ids

        delete_collection(collection)

        assert db_session.get(Collection, collection.id) is None
        assert db_session.get(DataSource, data_source.id) is None
        for org_item_id in org_item_ids:
            assert db_session.get(DataSourceOrganisationItem, org_item_id) is None

    def test_delete_collection_cascades_grant_recipient_data_set_with_column_reference(self, db_session, factories):
        grant = factories.grant.create()
        collection = factories.collection.create(grant=grant)
        data_source = factories.data_source.create(
            grant=grant,
            collection=collection,
            type=DataSourceType.GRANT_RECIPIENT,
        )
        form = factories.form.create(collection=collection)
        question = factories.question.create(
            form=form,
            text=f"Your allocation is (({data_source.safe_did}.c_allocation))",
        )
        _validate_and_sync_component_references(
            question,
            ExpressionContext.build_expression_context(collection=collection, mode="interpolation"),
        )
        db_session.flush()

        delete_collection(collection)

        assert db_session.get(Collection, collection.id) is None
        assert db_session.get(DataSource, data_source.id) is None


class TestDeleteForm:
    def test_delete(self, db_session, factories):
        form1 = factories.form.create()
        question1 = factories.question.create(form=form1)
        form2 = factories.form.create(collection=form1.collection)
        question2 = factories.question.create(form=form2)

        delete_form(form1)

        assert db_session.get(Form, form1.id) is None
        assert db_session.get(Question, question1.id) is None
        assert db_session.get(Form, form2.id) is form2
        assert db_session.get(Question, question2.id) is question2

    def test_form_reordering(self, db_session, factories):
        collection = factories.collection.create()
        forms = factories.form.create_batch(5, collection=collection)

        assert [f.order for f in collection.forms] == [0, 1, 2, 3, 4]
        assert collection.forms == [forms[0], forms[1], forms[2], forms[3], forms[4]]

        delete_form(forms[2])

        assert [f.order for f in collection.forms] == [0, 1, 2, 3]
        assert collection.forms == [forms[0], forms[1], forms[3], forms[4]]

    def test_delete_form_with_data_sources(self, db_session, factories):
        form = factories.form.create()
        question = factories.question.create(form=form, data_type=QuestionDataType.RADIOS)

        delete_form(form)

        assert db_session.get(Form, form.id) is None
        assert db_session.get(DataSource, question.data_source.id) is None
        for item in question.data_source.items:
            assert db_session.get(DataSourceItem, item.id) is None


class TestDeleteQuestion:
    def test_delete(self, db_session, factories):
        form = factories.form.create()
        question1 = factories.question.create(form=form)
        question2 = factories.question.create(form=form)
        question3 = factories.question.create(form=form)

        delete_question(question2)

        assert db_session.get(Question, question1.id) is question1
        assert db_session.get(Question, question2.id) is None
        assert db_session.get(Question, question3.id) is question3

    def test_form_reordering(self, db_session, factories):
        form = factories.form.create()
        questions = factories.question.create_batch(5, form=form)

        assert [q.order for q in form.cached_questions] == [0, 1, 2, 3, 4]
        assert form.cached_questions == [questions[0], questions[1], questions[2], questions[3], questions[4]]

        delete_question(questions[2])
        form.clear_caches()

        assert [q.order for q in form.cached_questions] == [0, 1, 2, 3]
        assert form.cached_questions == [questions[0], questions[1], questions[3], questions[4]]

    def test_delete_group(self, db_session, factories):
        form = factories.form.create()
        question1 = factories.question.create(form=form, order=0)
        group = factories.group.create(form=form, order=1)
        group_questions = factories.question.create_batch(3, form=form, parent=group)
        question2 = factories.question.create(form=form, order=2)

        assert form.components == [question1, group, question2]
        assert form.cached_questions == [question1, *[q for q in group_questions], question2]

        delete_question(group)
        form.clear_caches()

        assert db_session.get(Group, group.id) is None
        assert db_session.get(Question, group_questions[0].id) is None

        assert form.components == [question1, question2]
        assert form.cached_questions == [question1, question2]

    def test_nested_question_in_group(self, db_session, factories):
        form = factories.form.create()
        group = factories.group.create(form=form)
        questions = factories.question.create_batch(5, form=form, parent=group)

        assert [c.order for c in form.components] == [0]
        assert [q.order for q in group.cached_questions] == [0, 1, 2, 3, 4]
        assert form.cached_questions == [questions[0], questions[1], questions[2], questions[3], questions[4]]

        delete_question(questions[2])
        form.clear_caches()
        group.clear_caches()

        assert [c.order for c in form.components] == [0]
        assert [q.order for q in group.cached_questions] == [0, 1, 2, 3]
        assert form.cached_questions == [questions[0], questions[1], questions[3], questions[4]]

        assert db_session.get(Question, questions[2].id) is None
        assert db_session.get(Question, questions[0].id) is not None

    def test_delete_question_with_data_source(self, db_session, factories):
        form = factories.form.create()
        question = factories.question.create(form=form, data_type=QuestionDataType.RADIOS)

        delete_question(question)

        assert db_session.get(Question, question.id) is None
        assert db_session.get(DataSource, question.data_source.id) is None
        for item in question.data_source.items:
            assert db_session.get(DataSourceItem, item.id) is None


class TestDeleteCollectionSubmissions:
    def test_delete_preview_collection_submissions_created_by_user(self, db_session, factories):
        collection = factories.collection.create(
            create_completed_submissions_each_question_type__preview=3,
            create_completed_submissions_each_question_type__live=3,
        )
        user = collection.preview_submissions[0].created_by

        for submission in collection.preview_submissions:
            factories.submission_event.create(submission=submission, created_by=submission.created_by)
        for submission in collection.live_submissions:
            factories.submission_event.create(submission=submission, created_by=submission.created_by)

        collection.live_submissions[0].created_by = user
        collection.live_submissions[0].events[0].created_by = user

        collection.preview_submissions[1].created_by = user
        collection.preview_submissions[1].events[0].created_by = user

        factories.submission_event.create(submission=collection.preview_submissions[0], created_by=user)

        preview_submissions_from_db = (
            db_session.query(Submission).where(Submission.mode == SubmissionModeEnum.PREVIEW).all()
        )
        live_submissions_from_db = db_session.query(Submission).where(Submission.mode == SubmissionModeEnum.LIVE).all()
        users_submissions_from_db = db_session.query(Submission).where(Submission.created_by == user).all()
        submission_events_from_db = db_session.query(SubmissionEvent).all()

        assert len(preview_submissions_from_db) == 3
        assert len(live_submissions_from_db) == 3
        assert len(users_submissions_from_db) == 3
        assert len(submission_events_from_db) == 7

        delete_collection_preview_submissions_created_by_user(collection=collection, created_by_user=user)

        preview_submissions_from_db = (
            db_session.query(Submission).where(Submission.mode == SubmissionModeEnum.PREVIEW).all()
        )
        live_submissions_from_db = db_session.query(Submission).where(Submission.mode == SubmissionModeEnum.LIVE).all()
        users_submissions_from_db = db_session.query(Submission).where(Submission.created_by == user).all()
        submission_events_from_db = db_session.query(SubmissionEvent).all()

        # Check that only the specified user's two test submissions & associated SubmissionEvents for that user were
        # deleted, and no live submission was deleted
        assert len(preview_submissions_from_db) == 1
        assert len(live_submissions_from_db) == 3
        assert len(users_submissions_from_db) == 1
        assert len(submission_events_from_db) == 4

        for submission in preview_submissions_from_db:
            assert submission.created_by is not user


class TestReferenceValidation:
    def test_components_in_same_group_and_on_same_page(self, factories):
        group = factories.group.create(
            presentation_options=QuestionPresentationOptions(show_questions_on_the_same_page=True)
        )
        q1 = factories.question.create(form=group.form, parent=group, data_type=QuestionDataType.NUMBER)
        q2 = factories.question.create(form=group.form, parent=group, data_type=QuestionDataType.NUMBER)
        q3 = factories.question.create(form=group.form, data_type=QuestionDataType.NUMBER)

        assert components_in_same_group_and_on_same_page(q1, q2) is True
        assert components_in_same_group_and_on_same_page(q1, q3) is False

    def test_components_in_valid_add_another_combination(self, factories, subtests):
        q1 = factories.question.create(add_another=True)
        g1 = factories.group.create(form=q1.form, add_another=True)
        g1_q1 = factories.question.create(form=q1.form, parent=g1)
        g1_q2 = factories.question.create(form=q1.form, parent=g1)
        q2 = factories.question.create(form=q1.form)
        q3 = factories.question.create(form=q1.form)
        g2 = factories.group.create(form=q1.form, add_another=True)
        g2_q1 = factories.question.create(form=q1.form, parent=g2)

        with subtests.test("add_another referencing previous non-add_another"):
            assert (
                components_in_valid_add_another_combination(
                    g2_q1,
                    [
                        q2,
                    ],
                )
                is True
            )
        with subtests.test("non-add_another referencing previous add_another"):
            assert (
                components_in_valid_add_another_combination(
                    q2,
                    [
                        g1_q1,
                    ],
                )
                is False
            )
        with subtests.test("add_another in group referencing previous add_another from outside group"):
            assert components_in_valid_add_another_combination(g1_q1, [q1, None]) is False
        with subtests.test("add_another in group referencing previous add_another in same group"):
            assert components_in_valid_add_another_combination(g1_q1, [g1_q2]) is True
        with subtests.test("add_another in group referencing previous add_another in same group, with None"):
            assert components_in_valid_add_another_combination(g1_q1, [g1_q2, None]) is True
        with subtests.test("non-add_another referencing previous add_anothers in same group as each other"):
            assert components_in_valid_add_another_combination(q3, [g1_q1, g1_q2]) is False
        with subtests.test("non-add_another referencing previous add_anothers in same group as each other, with None"):
            assert components_in_valid_add_another_combination(q3, [g1_q1, g1_q2, None]) is False

        with subtests.test("non-add_another referencing previous non-add_another"):
            assert components_in_valid_add_another_combination(q2, [q3]) is True

        with subtests.test("add_another referencing previous add_another from different group"):
            assert components_in_valid_add_another_combination(g1_q2, [g2_q1]) is False

        with subtests.test("add_another referencing multiple previous add_anothers from different group"):
            assert components_in_valid_add_another_combination(g2_q1, [g1_q1, g1_q2]) is False


class TestValidateAndSyncExpressionReferences:
    def test_creates_component_reference_for_managed_expression(self, db_session, factories):
        user = factories.user.create()
        referenced_question = factories.question.create(data_type=QuestionDataType.NUMBER)
        dependent_question = factories.question.create(form=referenced_question.form)

        expression = Expression.from_evaluatable_expression(
            GreaterThan(subject_reference=ExpressionReference.from_question(referenced_question), minimum_value=100),
            ExpressionType.CONDITION,
            user,
        )
        dependent_question.expressions.append(expression)
        db_session.add(expression)
        db_session.flush()

        assert len(expression.component_references) == 0

        _validate_and_sync_expression_references(expression)

        assert len(expression.component_references) == 1
        reference = expression.component_references[0]
        assert reference.component == dependent_question
        assert reference.expression == expression
        assert reference.depends_on_component == referenced_question

    def test_managed_validation_referencing_only_itself_creates_no_references(self, db_session, factories):
        user = factories.user.create()
        question = factories.question.create(data_type=QuestionDataType.NUMBER)

        expression = Expression.from_evaluatable_expression(
            GreaterThan(subject_reference=ExpressionReference.from_question(question), minimum_value=0, inclusive=True),
            ExpressionType.VALIDATION,
            user,
        )
        question.expressions.append(expression)
        db_session.add(expression)
        db_session.flush()

        _validate_and_sync_expression_references(expression)

        assert expression.component_references == []

    def test_replaces_existing_component_references(self, db_session, factories):
        user = factories.user.create()
        referenced_question = factories.question.create(data_type=QuestionDataType.NUMBER)
        dependent_question = factories.question.create(form=referenced_question.form)

        managed_expression = GreaterThan(
            subject_reference=ExpressionReference.from_question(referenced_question), minimum_value=100
        )
        expression = Expression.from_evaluatable_expression(managed_expression, ExpressionType.CONDITION, user)
        dependent_question.expressions.append(expression)
        db_session.add(expression)

        existing_reference = ComponentReference(
            depends_on_component=referenced_question, component=dependent_question, expression=expression
        )
        expression.component_references = [existing_reference]
        db_session.add(existing_reference)
        db_session.flush()

        original_reference_id = existing_reference.id

        _validate_and_sync_expression_references(expression)
        db_session.flush()

        assert len(expression.component_references) == 1
        new_reference = expression.component_references[0]
        assert new_reference.id != original_reference_id
        assert new_reference.depends_on_component == referenced_question
        assert new_reference.component == dependent_question
        assert new_reference.expression == expression

    def test_creates_references_to_data_source_items(self, db_session, factories):
        user = factories.user.create()
        referenced_question = factories.question.create(data_type=QuestionDataType.RADIOS)
        dependent_question = factories.question.create(form=referenced_question.form)

        managed_expression = Specifically(
            subject_reference=ExpressionReference.from_question(referenced_question),
            item={
                "key": referenced_question.data_source.items[0].key,
                "label": referenced_question.data_source.items[0].label,
            },
        )
        expression = Expression.from_evaluatable_expression(managed_expression, ExpressionType.CONDITION, user)
        dependent_question.expressions.append(expression)
        db_session.add(expression)
        assert len(expression.component_references) == 0

        _validate_and_sync_expression_references(expression)
        db_session.flush()

        assert len(expression.component_references) == 1

    def test_creates_references_to_referenced_questions(self, db_session, factories):
        user = factories.user.create()
        form = factories.form.create()
        first_referenced_question = factories.question.create(form=form, data_type=QuestionDataType.NUMBER)
        second_referenced_question = factories.question.create(form=form, data_type=QuestionDataType.NUMBER)
        depends_on_question = factories.question.create(form=form, data_type=QuestionDataType.NUMBER)
        target_question = factories.question.create(form=form)

        expression = Expression.from_evaluatable_expression(
            Between(
                subject_reference=ExpressionReference.from_question(depends_on_question),
                minimum_value=None,
                minimum_expression=ExpressionReference.from_question(first_referenced_question),
                maximum_value=None,
                maximum_expression=ExpressionReference.from_question(second_referenced_question),
            ),
            ExpressionType.CONDITION,
            user,
        )
        target_question.expressions.append(expression)
        db_session.add(expression)
        db_session.flush()

        assert len(expression.component_references) == 0

        # This shouldn't raise an IncompatibleDataTypeException as the question evaluated by the managed expression
        # matches the data types of the questions used as reference values. The target_question (the one with the
        # expression attached) is irrelevant in terms of question data type.
        _validate_and_sync_expression_references(expression)

        assert len(expression.component_references) == 3
        referenced_components = {ref.depends_on_component for ref in expression.component_references}
        assert referenced_components == {depends_on_question, first_referenced_question, second_referenced_question}

    def test_creates_references_for_valid_add_another_question(self, db_session, factories):
        user = factories.user.create()
        form = factories.form.create()
        group = factories.group.create(form=form, add_another=True)
        first_referenced_question = factories.question.create(
            form=form, data_type=QuestionDataType.NUMBER, parent=group
        )
        target_question = factories.question.create(form=form, data_type=QuestionDataType.NUMBER, parent=group)

        expression = Expression.from_evaluatable_expression(
            GreaterThan(
                subject_reference=ExpressionReference.from_question(target_question),
                minimum_value=None,
                minimum_expression=ExpressionReference.from_question(first_referenced_question),
            ),
            ExpressionType.VALIDATION,
            user,
        )
        target_question.expressions.append(expression)
        db_session.add(expression)
        db_session.flush()

        assert len(expression.component_references) == 0

        _validate_and_sync_expression_references(expression)
        assert len(expression.component_references) == 1
        referenced_components = {ref.depends_on_component for ref in expression.component_references}
        assert referenced_components == {first_referenced_question}
        assert all(ref.component == target_question for ref in expression.component_references)

    def test_raises_dependency_order_exception(self, db_session, factories):
        user = factories.user.create()
        form = factories.form.create()
        first_referenced_question = factories.question.create(form=form, data_type=QuestionDataType.NUMBER)
        target_question = factories.question.create(form=form, data_type=QuestionDataType.NUMBER)
        second_referenced_question = factories.question.create(form=form, data_type=QuestionDataType.NUMBER)

        expression = Expression.from_evaluatable_expression(
            Between(
                subject_reference=ExpressionReference.from_question(target_question),
                minimum_value=None,
                minimum_expression=ExpressionReference.from_question(first_referenced_question),
                maximum_value=None,
                maximum_expression=ExpressionReference.from_question(second_referenced_question),
            ),
            ExpressionType.CONDITION,
            user,
        )
        target_question.expressions.append(expression)
        db_session.add(expression)
        db_session.flush()

        assert len(expression.component_references) == 0

        with pytest.raises(DependencyOrderException):
            _validate_and_sync_expression_references(expression)

    def test_raises_incompatible_data_type_exception(self, db_session, factories):
        user = factories.user.create()
        form = factories.form.create()
        first_referenced_question = factories.question.create(form=form, data_type=QuestionDataType.NUMBER)
        second_referenced_question = factories.question.create(form=form, data_type=QuestionDataType.DATE)
        target_question = factories.question.create(form=form, data_type=QuestionDataType.NUMBER)
        conditional_question = factories.question.create(form=form, data_type=QuestionDataType.TEXT_SINGLE_LINE)

        expression = Expression.from_evaluatable_expression(
            Between(
                subject_reference=ExpressionReference.from_question(target_question),
                minimum_value=None,
                minimum_expression=ExpressionReference.from_question(first_referenced_question),
                maximum_value=None,
                maximum_expression=ExpressionReference.from_question(second_referenced_question),
            ),
            ExpressionType.CONDITION,
            user,
        )
        conditional_question.expressions.append(expression)
        db_session.add(expression)
        db_session.flush()

        assert len(expression.component_references) == 0

        with pytest.raises(IncompatibleDataTypeException):
            _validate_and_sync_expression_references(expression)

    def test_raises_add_another_exception_on_question(self, db_session, factories):
        user = factories.user.create()
        form = factories.form.create()
        first_referenced_question = factories.question.create(
            form=form, data_type=QuestionDataType.NUMBER, add_another=True
        )
        second_referenced_question = factories.question.create(form=form, data_type=QuestionDataType.NUMBER)
        target_question = factories.question.create(form=form, data_type=QuestionDataType.NUMBER)
        conditional_question = factories.question.create(form=form)
        expression = Expression.from_evaluatable_expression(
            Between(
                subject_reference=ExpressionReference.from_question(target_question),
                minimum_value=None,
                minimum_expression=ExpressionReference.from_question(first_referenced_question),
                maximum_value=None,
                maximum_expression=ExpressionReference.from_question(second_referenced_question),
            ),
            ExpressionType.CONDITION,
            user,
        )
        conditional_question.expressions.append(expression)
        db_session.add(expression)
        db_session.flush()

        assert len(expression.component_references) == 0

        with pytest.raises(AddAnotherDependencyException):
            _validate_and_sync_expression_references(expression)

    def test_raises_add_another_exception_on_different_group(self, db_session, factories):
        user = factories.user.create()
        form = factories.form.create()
        first_referenced_question = factories.question.create(
            form=form, data_type=QuestionDataType.NUMBER, add_another=True
        )
        second_referenced_question = factories.question.create(form=form, data_type=QuestionDataType.NUMBER)
        target_question = factories.question.create(form=form, data_type=QuestionDataType.NUMBER, add_another=True)

        conditional_question = factories.question.create(form=form)
        expression = Expression.from_evaluatable_expression(
            Between(
                subject_reference=ExpressionReference.from_question(target_question),
                minimum_value=None,
                minimum_expression=ExpressionReference.from_question(first_referenced_question),
                maximum_value=None,
                maximum_expression=ExpressionReference.from_question(second_referenced_question),
            ),
            ExpressionType.CONDITION,
            user,
        )
        conditional_question.expressions.append(expression)
        db_session.add(expression)
        db_session.flush()

        assert len(expression.component_references) == 0

        with pytest.raises(AddAnotherDependencyException):
            _validate_and_sync_expression_references(expression)

    def test_raises_add_another_exception_referencing_from_outside_group(self, db_session, factories):
        user = factories.user.create()
        form = factories.form.create()
        group = factories.group.create(add_another=True, form=form)
        first_referenced_question = factories.question.create(
            parent=group, form=form, data_type=QuestionDataType.NUMBER
        )
        second_referenced_question = factories.question.create(
            parent=group, form=form, data_type=QuestionDataType.NUMBER
        )
        target_question = factories.question.create(form=form, data_type=QuestionDataType.NUMBER)

        expression = Expression.from_evaluatable_expression(
            GreaterThan(
                subject_reference=ExpressionReference.from_question(second_referenced_question),
                minimum_value=None,
                minimum_expression=ExpressionReference.from_question(first_referenced_question),
            ),
            ExpressionType.CONDITION,
            user,
        )
        target_question.expressions.append(expression)
        db_session.add(expression)
        db_session.flush()

        assert len(expression.component_references) == 0

        with pytest.raises(AddAnotherDependencyException):
            _validate_and_sync_expression_references(expression)

    def test_creates_component_references_for_custom_validation_expression(self, db_session, factories):
        user = factories.user.create()
        form = factories.form.create()
        q0, q1, q2, q3 = factories.question.create_batch(4, form=form, data_type=QuestionDataType.NUMBER)

        expression = Expression.from_evaluatable_expression(
            CustomExpression(
                custom_expression=EvaluationStatement(f"(({q3.safe_qid})) <= (({q1.safe_qid})) + (({q2.safe_qid}))"),
                custom_message=InterpolationStatement(
                    f"The answer must be less than (({q1.safe_qid}))+(({q2.safe_qid}))"
                    f" and a random reference to (({q0.safe_qid}))"
                ),
            ),
            ExpressionType.VALIDATION,
            user,
        )
        q3.expressions.append(expression)
        db_session.add(expression)
        db_session.flush()

        assert len(expression.component_references) == 0

        _validate_and_sync_expression_references(expression)

        assert len(expression.component_references) == 3

        assert all(ref.component == q3 for ref in expression.component_references)
        assert all(ref.expression == expression for ref in expression.component_references)
        assert all(ref.depends_on_component in {q0, q1, q2} for ref in expression.component_references)

    def test_creates_component_references_for_calculated_condition(self, db_session, factories):
        user = factories.user.create()
        form = factories.form.create()
        q0, q1, q2, q3 = factories.question.create_batch(4, form=form, data_type=QuestionDataType.NUMBER)

        expression = Expression.from_evaluatable_expression(
            CustomExpression(
                custom_expression=EvaluationStatement(f"(({q0.safe_qid})) <= (({q1.safe_qid})) + (({q2.safe_qid}))"),
            ),
            ExpressionType.CONDITION,
            user,
        )
        q3.expressions.append(expression)
        db_session.add(expression)
        db_session.flush()

        assert len(expression.component_references) == 0

        _validate_and_sync_expression_references(expression)

        assert len(expression.component_references) == 3

        assert all(ref.component == q3 for ref in expression.component_references)
        assert all(ref.expression == expression for ref in expression.component_references)
        assert all(ref.depends_on_component in {q0, q1, q2} for ref in expression.component_references)

    def test_creates_column_reference_for_reference_aware_field(self, db_session, factories):
        user = factories.user.create()
        grant = factories.grant.create()
        collection = factories.collection.create(grant=grant)
        data_source = factories.data_source.create(
            grant=grant,
            collection=collection,
            type=DataSourceType.GRANT_RECIPIENT,
        )
        form = factories.form.create(collection=collection)
        question = factories.question.create(form=form, data_type=QuestionDataType.NUMBER)

        expression = Expression.from_evaluatable_expression(
            GreaterThan(
                subject_reference=ExpressionReference.from_question(question),
                minimum_value=None,
                minimum_expression=ExpressionReference.from_data_source_column(data_source, "c_allocation"),
            ),
            ExpressionType.VALIDATION,
            user,
        )
        question.expressions.append(expression)
        db_session.add(expression)
        db_session.flush()

        _validate_and_sync_expression_references(expression)
        db_session.flush()

        assert len(expression.component_references) == 1
        ref = expression.component_references[0]
        assert ref.component == question
        assert ref.expression == expression
        assert ref.depends_on_component is None
        assert ref.depends_on_data_source == data_source
        assert ref.depends_on_column_name == "c_allocation"

    def test_creates_column_references_for_both_between_bounds(self, db_session, factories):
        user = factories.user.create()
        grant = factories.grant.create()
        collection = factories.collection.create(grant=grant)
        data_source = factories.data_source.create(
            grant=grant,
            collection=collection,
            type=DataSourceType.GRANT_RECIPIENT,
            schema=DataSourceSchema.model_validate(
                {
                    "c_min": DataSourceSchemaColumn(
                        data_type=QuestionDataType.NUMBER,
                        presentation_options=QuestionPresentationOptions(prefix="£"),
                        data_options=QuestionDataOptions(number_type=NumberTypeEnum.INTEGER),
                        original_column_name="Min",
                    ),
                    "c_max": DataSourceSchemaColumn(
                        data_type=QuestionDataType.NUMBER,
                        presentation_options=QuestionPresentationOptions(prefix="£"),
                        data_options=QuestionDataOptions(number_type=NumberTypeEnum.INTEGER),
                        original_column_name="Max",
                    ),
                }
            ),
        )
        form = factories.form.create(collection=collection)
        question = factories.question.create(form=form, data_type=QuestionDataType.NUMBER)

        expression = Expression.from_evaluatable_expression(
            Between(
                subject_reference=ExpressionReference.from_question(question),
                minimum_value=None,
                minimum_expression=ExpressionReference.from_data_source_column(data_source, "c_min"),
                maximum_value=None,
                maximum_expression=ExpressionReference.from_data_source_column(data_source, "c_max"),
            ),
            ExpressionType.VALIDATION,
            user,
        )
        question.expressions.append(expression)
        db_session.add(expression)
        db_session.flush()

        _validate_and_sync_expression_references(expression)
        db_session.flush()

        assert {ref.depends_on_column_name for ref in expression.component_references} == {"c_min", "c_max"}
        assert all(ref.depends_on_data_source == data_source for ref in expression.component_references)
        assert all(ref.expression == expression for ref in expression.component_references)

    def test_mixes_question_and_column_references_in_one_field(self, db_session, factories):
        user = factories.user.create()
        grant = factories.grant.create()
        collection = factories.collection.create(grant=grant)
        data_source = factories.data_source.create(
            grant=grant,
            collection=collection,
            type=DataSourceType.GRANT_RECIPIENT,
        )
        form = factories.form.create(collection=collection)
        min_question = factories.question.create(form=form, data_type=QuestionDataType.NUMBER)
        target_question = factories.question.create(form=form, data_type=QuestionDataType.NUMBER)

        expression = Expression.from_evaluatable_expression(
            Between(
                subject_reference=ExpressionReference.from_question(target_question),
                minimum_value=None,
                minimum_expression=ExpressionReference.from_question(min_question),
                maximum_value=None,
                maximum_expression=ExpressionReference.from_data_source_column(data_source, "c_allocation"),
            ),
            ExpressionType.VALIDATION,
            user,
        )
        target_question.expressions.append(expression)
        db_session.add(expression)
        db_session.flush()

        _validate_and_sync_expression_references(expression)
        db_session.flush()

        component_refs = [ref for ref in expression.component_references if ref.depends_on_component_id]
        column_refs = [ref for ref in expression.component_references if ref.depends_on_data_source_id]
        assert {ref.depends_on_component for ref in component_refs} == {min_question}
        assert {ref.depends_on_column_name for ref in column_refs} == {"c_allocation"}

    def test_rejects_column_reference_to_missing_column(self, db_session, factories):
        user = factories.user.create()
        grant = factories.grant.create()
        collection = factories.collection.create(grant=grant)
        data_source = factories.data_source.create(
            grant=grant,
            collection=collection,
            type=DataSourceType.GRANT_RECIPIENT,
        )
        form = factories.form.create(collection=collection)
        question = factories.question.create(form=form, data_type=QuestionDataType.NUMBER)

        expression = Expression.from_evaluatable_expression(
            GreaterThan(
                subject_reference=ExpressionReference.from_question(question),
                minimum_value=None,
                minimum_expression=ExpressionReference.from_data_source_column(data_source, "c_missing"),
            ),
            ExpressionType.VALIDATION,
            user,
        )
        question.expressions.append(expression)
        db_session.add(expression)
        db_session.flush()

        with pytest.raises(InvalidReferenceInExpression):
            _validate_and_sync_expression_references(expression)


class TestValidateAndSyncComponentReferences:
    def test_creates_references_for_supported_fields(self, db_session, factories):
        text_question = factories.question.create()
        hint_question = factories.question.create(form=text_question.form)
        guidance_body_question = factories.question.create(form=text_question.form)
        add_another_guidance_body_question = factories.question.create(form=text_question.form)
        dependent_question = factories.question.create(
            form=text_question.form,
            text=f"Reference to (({text_question.safe_qid}))",
            hint=f"Reference to (({hint_question.safe_qid}))",
            guidance_body=InterpolationStatement(f"Reference to (({guidance_body_question.safe_qid}))"),
            add_another_guidance_body=InterpolationStatement(
                f"Reference to (({add_another_guidance_body_question.safe_qid}))"
            ),
        )

        # The factories create component references automatically; this will generally be the desirable behaviour
        # for tests.
        db_session.query(ComponentReference).delete()

        initial_refs = db_session.query(ComponentReference).filter_by(component=dependent_question).all()
        assert len(initial_refs) == 0

        _validate_and_sync_component_references(
            dependent_question,
            ExpressionContext.build_expression_context(
                collection=dependent_question.form.collection, mode="interpolation"
            ),
        )

        refs = db_session.query(ComponentReference).filter_by(component=dependent_question).all()
        assert {ref.depends_on_component for ref in refs} == {
            text_question,
            hint_question,
            guidance_body_question,
            add_another_guidance_body_question,
        }

    def test_handles_multiple_interpolations(self, db_session, factories):
        ref_question1 = factories.question.create()
        ref_question2 = factories.question.create(form=ref_question1.form)
        dependent_question = factories.question.create(
            form=ref_question1.form, text=f"Compare (({ref_question1.safe_qid})) with (({ref_question2.safe_qid}))"
        )

        _validate_and_sync_component_references(
            dependent_question,
            ExpressionContext.build_expression_context(
                collection=dependent_question.form.collection, mode="interpolation"
            ),
        )
        db_session.flush()

        refs = db_session.query(ComponentReference).filter_by(component=dependent_question).all()
        assert {ref.depends_on_component for ref in refs} == {ref_question1, ref_question2}

    def test_handles_expression_references(self, db_session, factories):
        user = factories.user.create()
        referenced_question = factories.question.create(data_type=QuestionDataType.NUMBER)
        dependent_question = factories.question.create(form=referenced_question.form)

        managed_expression = GreaterThan(
            subject_reference=ExpressionReference.from_question(referenced_question), minimum_value=100
        )
        expression = Expression.from_evaluatable_expression(managed_expression, ExpressionType.CONDITION, user)
        dependent_question.expressions.append(expression)
        db_session.add(expression)
        db_session.flush()

        _validate_and_sync_component_references(
            dependent_question,
            ExpressionContext.build_expression_context(
                collection=dependent_question.form.collection, mode="interpolation"
            ),
        )
        db_session.flush()

        refs = db_session.query(ComponentReference).filter_by(component=dependent_question).all()
        assert len(refs) == 1
        assert refs[0].depends_on_component == referenced_question
        assert refs[0].expression == expression

    def test_throws_error_on_referencing_later_question_in_form(self, db_session, factories):
        dependent_question = factories.question.create()
        referenced_question = factories.question.create(form=dependent_question.form, data_type=QuestionDataType.NUMBER)
        dependent_question.text = InterpolationStatement(f"Reference to (({referenced_question.safe_qid}))")

        with pytest.raises(DependencyOrderException):
            _validate_and_sync_component_references(
                dependent_question,
                ExpressionContext.build_expression_context(
                    collection=dependent_question.form.collection, mode="interpolation"
                ),
            )

    def test_throws_error_on_referencing_same_question_in_form(self, db_session, factories):
        question = factories.question.create()
        question.text = InterpolationStatement(f"Reference to (({question.safe_qid}))")

        with pytest.raises(DependencyOrderException):
            _validate_and_sync_component_references(
                question,
                ExpressionContext.build_expression_context(collection=question.form.collection, mode="interpolation"),
            )

    def test_throws_error_on_unknown_references(self, db_session, factories):
        dependent_question = factories.question.create()

        # Set the text with an invalid reference after creation so that ComponentReferences aren't created; they'd error
        dependent_question.text = InterpolationStatement("Reference to ((some.non.question.ref)) here")

        with pytest.raises(InvalidReferenceInExpression):
            _validate_and_sync_component_references(
                dependent_question,
                ExpressionContext.build_expression_context(
                    collection=dependent_question.form.collection, mode="interpolation"
                ),
            )

        refs = db_session.query(ComponentReference).filter_by(component=dependent_question).all()
        assert len(refs) == 0

    def test_removes_existing_references_before_creating_new_ones(self, db_session, factories):
        old_referenced_question = factories.question.create()
        new_referenced_question = factories.question.create(form=old_referenced_question.form)
        dependent_question = factories.question.create(
            form=old_referenced_question.form, text=f"Reference to (({old_referenced_question.safe_qid}))"
        )

        refs = db_session.query(ComponentReference).filter_by(component=dependent_question).all()
        old_referenced_id = refs[0].id
        assert len(refs) == 1
        assert refs[0].depends_on_component == old_referenced_question

        dependent_question.text = InterpolationStatement(f"Now references (({new_referenced_question.safe_qid}))")

        _validate_and_sync_component_references(
            dependent_question,
            ExpressionContext.build_expression_context(
                collection=dependent_question.form.collection, mode="interpolation"
            ),
        )
        db_session.flush()

        # Old reference should be deleted
        old_ref = db_session.get(ComponentReference, old_referenced_id)
        assert old_ref is None

        # New one should exist
        refs = db_session.query(ComponentReference).filter_by(component=dependent_question).all()
        assert len(refs) == 1
        assert refs[0].depends_on_component == new_referenced_question

    def test_works_with_groups(self, db_session, factories):
        form = factories.form.create()
        referenced_question = factories.question.create(form=form)
        group = factories.group.create(form=form, text=f"Group referencing ((({referenced_question.safe_qid})))")

        _validate_and_sync_component_references(
            group,
            ExpressionContext.build_expression_context(
                collection=referenced_question.form.collection, mode="interpolation"
            ),
        )
        db_session.flush()

        refs = db_session.query(ComponentReference).filter_by(component=group).all()
        assert len(refs) == 1
        assert refs[0].depends_on_component == referenced_question

    def test_allows_reference_to_question_in_earlier_form(self, db_session, factories):
        collection = factories.collection.create()
        earlier_form = factories.form.create(collection=collection)
        later_form = factories.form.create(collection=collection)
        referenced_question = factories.question.create(form=earlier_form)
        dependent_question = factories.question.create(
            form=later_form, text=f"Reference to (({referenced_question.safe_qid}))"
        )

        db_session.query(ComponentReference).delete()

        _validate_and_sync_component_references(
            dependent_question,
            ExpressionContext.build_expression_context(collection=collection, mode="interpolation"),
        )
        db_session.flush()

        refs = db_session.query(ComponentReference).filter_by(component=dependent_question).all()
        assert len(refs) == 1
        assert refs[0].depends_on_component == referenced_question

    def test_throws_error_on_referencing_question_in_later_form(self, db_session, factories):
        collection = factories.collection.create()
        earlier_form = factories.form.create(collection=collection)
        later_form = factories.form.create(collection=collection)
        referenced_question = factories.question.create(form=later_form)
        dependent_question = factories.question.create(form=earlier_form)
        dependent_question.text = InterpolationStatement(f"Reference to (({referenced_question.safe_qid}))")

        with pytest.raises(DependencyOrderException):
            _validate_and_sync_component_references(
                dependent_question,
                ExpressionContext.build_expression_context(collection=collection, mode="interpolation"),
            )

    def test_raises_referencing_question_in_same_page_group(self, db_session, factories):
        form = factories.form.create()
        group = factories.group.create(
            form=form, presentation_options=QuestionPresentationOptions(show_questions_on_the_same_page=True)
        )

        referenced_question = factories.question.create(form=form, parent=group)
        dependent_question = factories.question.create(form=form, parent=group)
        dependent_question.text = InterpolationStatement(f"Reference to (({referenced_question.safe_qid}))")
        with pytest.raises(InvalidReferenceInExpression):
            _validate_and_sync_component_references(
                dependent_question,
                ExpressionContext.build_expression_context(
                    collection=referenced_question.form.collection, mode="interpolation"
                ),
            )

    def test_creates_reference_for_data_source_column_in_text(self, db_session, factories):
        grant = factories.grant.create()
        collection = factories.collection.create(grant=grant)
        data_source = factories.data_source.create(
            grant=grant,
            collection=collection,
            type=DataSourceType.GRANT_RECIPIENT,
        )
        form = factories.form.create(collection=collection)
        question = factories.question.create(form=form)

        question.text = InterpolationStatement(f"Your allocation is (({data_source.safe_did}.c_allocation))")

        _validate_and_sync_component_references(
            question,
            ExpressionContext.build_expression_context(collection=collection, mode="interpolation"),
        )
        db_session.flush()

        refs = db_session.query(ComponentReference).filter_by(component=question).all()
        assert len(refs) == 1
        assert refs[0].depends_on_component is None
        assert refs[0].depends_on_data_source == data_source
        assert refs[0].depends_on_column_name == "c_allocation"

    def test_regenerates_column_references_after_edit(self, db_session, factories):
        grant = factories.grant.create()
        collection = factories.collection.create(grant=grant)
        data_source = factories.data_source.create(
            grant=grant,
            collection=collection,
            type=DataSourceType.GRANT_RECIPIENT,
            schema=DataSourceSchema.model_validate(
                {
                    "c_allocation": DataSourceSchemaColumn(
                        data_type=QuestionDataType.NUMBER,
                        presentation_options=QuestionPresentationOptions(prefix="£"),
                        data_options=QuestionDataOptions(number_type=NumberTypeEnum.INTEGER),
                        original_column_name="Allocation",
                    ),
                    "c_theme": DataSourceSchemaColumn(
                        data_type=QuestionDataType.TEXT_SINGLE_LINE,
                        presentation_options=QuestionPresentationOptions(),
                        data_options=QuestionDataOptions(),
                        original_column_name="Theme",
                    ),
                }
            ),
        )
        form = factories.form.create(collection=collection)
        question = factories.question.create(form=form)

        question.text = InterpolationStatement(f"Allocation: (({data_source.safe_did}.c_allocation))")
        _validate_and_sync_component_references(
            question,
            ExpressionContext.build_expression_context(collection=collection, mode="interpolation"),
        )
        db_session.flush()

        refs = db_session.query(ComponentReference).filter_by(component=question).all()
        assert {ref.depends_on_column_name for ref in refs} == {"c_allocation"}

        question.text = InterpolationStatement(f"Theme: (({data_source.safe_did}.c_theme))")
        _validate_and_sync_component_references(
            question,
            ExpressionContext.build_expression_context(collection=collection, mode="interpolation"),
        )
        db_session.flush()

        refs = db_session.query(ComponentReference).filter_by(component=question).all()
        assert {ref.depends_on_column_name for ref in refs} == {"c_theme"}

    def test_rejects_reference_to_unknown_column(self, db_session, factories):
        grant = factories.grant.create()
        collection = factories.collection.create(grant=grant)
        data_source = factories.data_source.create(
            grant=grant,
            collection=collection,
            type=DataSourceType.GRANT_RECIPIENT,
        )
        form = factories.form.create(collection=collection)
        question = factories.question.create(form=form)

        question.text = InterpolationStatement(f"(({data_source.safe_did}.c_nonexistent))")

        with pytest.raises(InvalidReferenceInExpression):
            _validate_and_sync_component_references(
                question,
                ExpressionContext.build_expression_context(collection=collection, mode="interpolation"),
            )


class TestGetSubmissions:
    def test_get_all_submissions_with_mode_for_collection_mode(self, db_session, factories):
        collection = factories.collection.create(create_submissions__live=3, create_submissions__test=1)

        submission_results = list(
            get_all_submissions_with_mode_for_collection(
                collection_id=collection.id,
                submission_mode=SubmissionModeEnum.LIVE,
            )
        )
        assert len(submission_results) == 3

    def test_get_all_submissions_with_mode_for_collection_mode_and_grant_recipient_id(self, db_session, factories):
        collection = factories.collection.create(create_submissions__live=3, create_submissions__test=1)
        live_submissions = collection.live_submissions
        gr_id = live_submissions[0].grant_recipient.id
        live_submissions[1].grant_recipient_id = gr_id

        submission_results = list(
            get_all_submissions_with_mode_for_collection(
                collection_id=collection.id, submission_mode=SubmissionModeEnum.LIVE, grant_recipient_ids=[gr_id]
            )
        )
        assert len(submission_results) == 2

    def test_get_all_submissions_with_mode_for_collection_mode_and_multiple_grant_recipient_ids(
        self, db_session, factories
    ):
        collection = factories.collection.create(create_submissions__live=3, create_submissions__test=1)
        live_submissions = collection.live_submissions
        gr_id1 = live_submissions[0].grant_recipient.id
        gr_id2 = live_submissions[1].grant_recipient.id

        submission_results = list(
            get_all_submissions_with_mode_for_collection(
                collection_id=collection.id,
                submission_mode=SubmissionModeEnum.LIVE,
                grant_recipient_ids=[gr_id1, gr_id2],
            )
        )
        assert len(submission_results) == 2

    def test_get_all_submissions_with_mode_for_collection_grant_recipient(self, db_session, factories):
        collection = factories.collection.create(create_submissions__live=2, create_submissions__test=1)
        live_submissions = collection.live_submissions
        gr_id = live_submissions[0].grant_recipient.id

        assert live_submissions[1].grant_recipient_id != gr_id

        submission_results = list(
            get_all_submissions_with_mode_for_collection(
                collection_id=collection.id, submission_mode=SubmissionModeEnum.LIVE, grant_recipient_ids=[gr_id]
            )
        )
        assert len(submission_results) == 1

    def test_get_all_submissions_with_mode_for_collection_with_users(self, db_session, factories, track_sql_queries):
        collection = factories.collection.create(create_submissions__live=2, create_submissions__test=1)
        collection_id = collection.id

        # check when we don't request all users - we should expect additional queries
        from_db = get_all_submissions_with_mode_for_collection(
            collection_id=collection_id,
            submission_mode=SubmissionModeEnum.LIVE,
            with_full_schema=False,
            with_users=False,
        )

        # Iterate over all the related models; check that no further SQL queries are emitted. The count is just a noop.
        count = 0
        with track_sql_queries() as queries:
            for submission in from_db:
                _ = submission.created_by
                count += 1

        assert len(queries) == count

        # check with requesting all users - no additional queries
        with track_sql_queries() as queries:
            from_db = get_all_submissions_with_mode_for_collection(
                collection_id=collection_id,
                submission_mode=SubmissionModeEnum.LIVE,
                with_full_schema=False,
                with_users=True,
            )
        assert from_db is not None

        # Expected queries:
        # * Fetch the submissions join the users
        assert len(queries) == 1

        # Iterate over all the related models; check that no further SQL queries are emitted. The count is just a noop.
        count = 0
        with track_sql_queries() as queries:
            for submission in from_db:
                _ = submission.created_by
                count += 1

        assert queries == []

    def test_get_all_submissions_with_mode_for_collection_with_users_with_full_schema_throws(
        self, db_session, factories
    ):
        collection = factories.collection.create()

        with pytest.raises(ValueError, match="Only one of with_full_schema or with_users should be set"):
            get_all_submissions_with_mode_for_collection(
                collection_id=collection.id,
                submission_mode=SubmissionModeEnum.LIVE,
                with_full_schema=True,
                with_users=True,
            )

    def test_get_all_submissions_with_mode_for_collection_with_full_schema_no_n_plus_one(
        self, db_session, factories, track_sql_queries
    ):
        collection = factories.collection.create(create_submissions__live=2)
        form = factories.form.create(collection=collection)
        anchor = factories.question.create(form=form)
        for i in range(2):
            factories.question.create(
                form=form, text=f"Top-level ref {i} (({ExpressionReference.from_question(anchor)}))"
            )
        group = factories.group.create(form=form)
        for i in range(2):
            factories.question.create(
                form=form, parent=group, text=f"Nested ref {i} (({ExpressionReference.from_question(anchor)}))"
            )

        with track_sql_queries() as iterate_queries:
            submissions = get_all_submissions_with_mode_for_collection(
                collection_id=collection.id,
                submission_mode=SubmissionModeEnum.LIVE,
                with_full_schema=True,
            )
            for submission in submissions:
                for f in submission.collection.forms:
                    for component in f._all_components:
                        for _ref in component.owned_component_references:
                            pass

        baseline_queries = len(iterate_queries)

        factories.submission.create(collection=collection, mode=SubmissionModeEnum.LIVE)
        anchor2 = factories.question.create(form=form)
        for i in range(2):
            factories.question.create(form=form, text=f"Extra ref {i} (({ExpressionReference.from_question(anchor2)}))")

        db_session.expire_all()

        with track_sql_queries() as iterate_queries:
            submissions = get_all_submissions_with_mode_for_collection(
                collection_id=collection.id,
                submission_mode=SubmissionModeEnum.LIVE,
                with_full_schema=True,
            )
            for submission in submissions:
                for f in submission.collection.forms:
                    for component in f._all_components:
                        for _ref in component.owned_component_references:
                            pass

        assert len(iterate_queries) == baseline_queries

    def test_get_all_submissions_with_mode_for_collection_with_full_schema_no_n_plus_one_for_event_users(
        self, db_session, factories, track_sql_queries
    ):
        collection = factories.collection.create()
        for _ in range(2):
            submission = factories.submission.create(collection=collection, mode=SubmissionModeEnum.LIVE)
            factories.submission_event.create(submission=submission)
            factories.submission_event.create(submission=submission)

        db_session.expire_all()
        with track_sql_queries() as baseline_queries:
            submissions = get_all_submissions_with_mode_for_collection(
                collection_id=collection.id,
                submission_mode=SubmissionModeEnum.LIVE,
                with_full_schema=True,
            )
            for submission in submissions:
                for event in submission.events:
                    _ = event.created_by
        baseline = len(baseline_queries)

        submission = factories.submission.create(collection=collection, mode=SubmissionModeEnum.LIVE)
        factories.submission_event.create(submission=submission)
        factories.submission_event.create(submission=submission)

        db_session.expire_all()
        with track_sql_queries() as iterate_queries:
            submissions = get_all_submissions_with_mode_for_collection(
                collection_id=collection.id,
                submission_mode=SubmissionModeEnum.LIVE,
                with_full_schema=True,
            )
            for submission in submissions:
                for event in submission.events:
                    _ = event.created_by

        assert len(iterate_queries) == baseline


class TestGetSubmissionListForCollection:
    def test_single_submission_collection_returns_a_row_per_grant_recipient(self, db_session, factories):
        collection = factories.collection.create()
        recipient_with_submission = factories.grant_recipient.create(
            grant=collection.grant, organisation__name="Acme Corp"
        )
        factories.grant_recipient.create(grant=collection.grant, organisation__name="Beta Ltd")
        submission = factories.submission.create(
            collection=collection,
            mode=SubmissionModeEnum.LIVE,
            grant_recipient=recipient_with_submission,
        )

        rows = get_submission_list_for_collection(collection=collection, submission_mode=SubmissionModeEnum.LIVE)

        rows_by_org = {row.organisation_name: row for row in rows}
        assert set(rows_by_org) == {"Acme Corp", "Beta Ltd"}

        started = rows_by_org["Acme Corp"]
        assert started.submission_id == submission.id
        assert started.status == submission.status
        assert started.name is None

        not_started = rows_by_org["Beta Ltd"]
        assert not_started.submission_id is None
        assert not_started.status is None
        assert not_started.last_updated_at_utc is None
        assert not_started.name is None

    def test_multi_submission_collection_returns_a_row_per_submission(self, db_session, factories):
        collection = factories.collection.create(allow_multiple_submissions=True)
        grant_recipient = factories.grant_recipient.create(grant=collection.grant, organisation__name="Acme Corp")
        first = factories.submission.create(
            collection=collection, mode=SubmissionModeEnum.LIVE, grant_recipient=grant_recipient
        )
        second = factories.submission.create(
            collection=collection, mode=SubmissionModeEnum.LIVE, grant_recipient=grant_recipient
        )

        rows = get_submission_list_for_collection(collection=collection, submission_mode=SubmissionModeEnum.LIVE)

        assert {row.submission_id for row in rows} == {first.id, second.id}
        assert all(row.organisation_name == "Acme Corp" for row in rows)

    @pytest.mark.parametrize(
        "offset_days, expected_overdue",
        [
            pytest.param(-1, True, id="day-after-end-date-is-overdue"),
            pytest.param(0, False, id="end-date-itself-is-not-overdue"),
            pytest.param(1, False, id="before-end-date-is-not-overdue"),
        ],
    )
    def test_optimised_hybrid_property_is_overdue_is_inclusive_of_end_date(
        self, db_session, factories, offset_days, expected_overdue
    ):
        # uses the actual database under test time to line up and test the hybrid property
        # note as the test runs in the same transaction it should always be consistent
        london_today = db_session.scalar(select(func.timezone("Europe/London", func.now()).cast(Date)))
        collection = factories.collection.create(
            allow_multiple_submissions=True,
            submission_period_end_date=london_today + datetime.timedelta(days=offset_days),
        )
        grant_recipient = factories.grant_recipient.create(grant=collection.grant, organisation__name="Acme Corp")
        submission = factories.submission.create(
            collection=collection, mode=SubmissionModeEnum.LIVE, grant_recipient=grant_recipient
        )

        rows = get_submission_list_for_collection(collection=collection, submission_mode=SubmissionModeEnum.LIVE)

        row = next(row for row in rows if row.submission_id == submission.id)
        assert row.is_overdue is expected_overdue

    def test_multi_submission_collection_returns_a_row_for_recipients_with_no_submissions(self, db_session, factories):
        collection = factories.collection.create(allow_multiple_submissions=True)
        recipient_with_submission = factories.grant_recipient.create(
            grant=collection.grant, organisation__name="Acme Corp"
        )
        factories.grant_recipient.create(grant=collection.grant, organisation__name="Beta Ltd")
        submission = factories.submission.create(
            collection=collection,
            mode=SubmissionModeEnum.LIVE,
            grant_recipient=recipient_with_submission,
        )

        rows = get_submission_list_for_collection(collection=collection, submission_mode=SubmissionModeEnum.LIVE)

        rows_by_org = {row.organisation_name: row for row in rows}
        assert set(rows_by_org) == {"Acme Corp", "Beta Ltd"}

        started = rows_by_org["Acme Corp"]
        assert started.submission_id == submission.id
        assert started.status == submission.status

        not_started = rows_by_org["Beta Ltd"]
        assert not_started.submission_id is None
        assert not_started.name is None
        assert not_started.status is None
        assert not_started.last_updated_at_utc is None

    def test_single_submission_collection_orders_rows_by_organisation_name(self, db_session, factories):
        collection = factories.collection.create()
        for org_name in ["Charlie Ltd", "Alpha Ltd", "Bravo Ltd"]:
            factories.grant_recipient.create(grant=collection.grant, organisation__name=org_name)

        rows = get_submission_list_for_collection(collection=collection, submission_mode=SubmissionModeEnum.LIVE)

        assert [row.organisation_name for row in rows] == ["Alpha Ltd", "Bravo Ltd", "Charlie Ltd"]

    def test_multi_submission_collection_orders_rows_by_organisation_name_then_submission_name(
        self, db_session, factories
    ):
        question = factories.question.create(
            form__collection__allow_multiple_submissions=True,
            data_type=QuestionDataType.TEXT_SINGLE_LINE,
        )
        collection = question.form.collection
        collection.submission_name_question_id = question.id
        db_session.flush()
        acme = factories.grant_recipient.create(grant=collection.grant, organisation__name="Acme Corp")
        beta = factories.grant_recipient.create(grant=collection.grant, organisation__name="Beta Ltd")
        for grant_recipient, submission_name in [
            (beta, "Project Z"),
            (acme, "Project B"),
            (acme, "Project A"),
        ]:
            factories.submission.create(
                collection=collection,
                mode=SubmissionModeEnum.LIVE,
                grant_recipient=grant_recipient,
                answers=[FactoryAnswer(question, TextSingleLineAnswer(submission_name))],
            )

        rows = get_submission_list_for_collection(collection=collection, submission_mode=SubmissionModeEnum.LIVE)

        assert [(row.organisation_name, row.name) for row in rows] == [
            ("Acme Corp", "Project A"),
            ("Acme Corp", "Project B"),
            ("Beta Ltd", "Project Z"),
        ]

    def test_uses_submission_name_question_answer_for_name(self, db_session, factories):
        question = factories.question.create(
            form__collection__allow_multiple_submissions=True,
            data_type=QuestionDataType.TEXT_SINGLE_LINE,
        )
        collection = question.form.collection
        collection.submission_name_question_id = question.id
        db_session.flush()
        grant_recipient = factories.grant_recipient.create(grant=collection.grant)
        factories.submission.create(
            collection=collection,
            mode=SubmissionModeEnum.LIVE,
            grant_recipient=grant_recipient,
            answers=[FactoryAnswer(question, TextSingleLineAnswer("Alpha Project"))],
        )

        summaries = get_submission_list_for_collection(collection=collection, submission_mode=SubmissionModeEnum.LIVE)

        assert len(summaries) == 1
        assert summaries[0].name == "Alpha Project"

    def test_uses_radios_name_question_label_for_name(self, db_session, factories):
        question = factories.question.create(
            form__collection__allow_multiple_submissions=True,
            data_type=QuestionDataType.RADIOS,
        )
        collection = question.form.collection
        collection.submission_name_question_id = question.id
        db_session.flush()
        grant_recipient = factories.grant_recipient.create(grant=collection.grant)
        factories.submission.create(
            collection=collection,
            mode=SubmissionModeEnum.LIVE,
            grant_recipient=grant_recipient,
            answers=[FactoryAnswer(question, SingleChoiceFromListAnswer(key="region-north", label="North region"))],
        )

        summaries = get_submission_list_for_collection(collection=collection, submission_mode=SubmissionModeEnum.LIVE)

        assert len(summaries) == 1
        assert summaries[0].name == "North region"

    def test_falls_back_to_reference_when_name_question_unanswered(self, db_session, factories):
        question = factories.question.create(
            form__collection__allow_multiple_submissions=True,
            data_type=QuestionDataType.TEXT_SINGLE_LINE,
        )
        collection = question.form.collection
        collection.submission_name_question_id = question.id
        db_session.flush()
        grant_recipient = factories.grant_recipient.create(grant=collection.grant)
        submission = factories.submission.create(
            collection=collection, mode=SubmissionModeEnum.LIVE, grant_recipient=grant_recipient
        )

        summaries = get_submission_list_for_collection(collection=collection, submission_mode=SubmissionModeEnum.LIVE)

        assert len(summaries) == 1
        assert summaries[0].name == submission.reference

    def test_only_returns_rows_for_the_requested_mode(self, db_session, factories):
        collection = factories.collection.create(allow_multiple_submissions=True)
        live_recipient = factories.grant_recipient.create(grant=collection.grant)
        test_recipient = factories.grant_recipient.create(grant=collection.grant, mode=GrantRecipientModeEnum.TEST)
        live_submission = factories.submission.create(
            collection=collection, mode=SubmissionModeEnum.LIVE, grant_recipient=live_recipient
        )
        factories.submission.create(collection=collection, mode=SubmissionModeEnum.TEST, grant_recipient=test_recipient)

        rows = get_submission_list_for_collection(collection=collection, submission_mode=SubmissionModeEnum.LIVE)

        assert [row.submission_id for row in rows] == [live_submission.id]

    def test_last_updated_at_utc_reflects_latest_event(self, db_session, factories):
        collection = factories.collection.create()
        grant_recipient = factories.grant_recipient.create(grant=collection.grant)
        submission = factories.submission.create(
            collection=collection, mode=SubmissionModeEnum.LIVE, grant_recipient=grant_recipient
        )
        latest_event_at = datetime.datetime(2030, 1, 1, 12, 0, 0)
        factories.submission_event.create(submission=submission, created_at_utc=datetime.datetime(2029, 1, 1, 12, 0, 0))
        factories.submission_event.create(submission=submission, created_at_utc=latest_event_at)

        rows = get_submission_list_for_collection(collection=collection, submission_mode=SubmissionModeEnum.LIVE)

        assert rows[0].last_updated_at_utc == latest_event_at

    def test_constant_query_count_regardless_of_submission_count(self, db_session, factories, track_sql_queries):
        question = factories.question.create(
            form__collection__allow_multiple_submissions=True,
            data_type=QuestionDataType.TEXT_SINGLE_LINE,
        )
        collection = question.form.collection
        collection.submission_name_question_id = question.id
        db_session.flush()

        def _create_submission(name: str) -> None:
            grant_recipient = factories.grant_recipient.create(grant=collection.grant)
            submission = factories.submission.create(
                collection=collection,
                mode=SubmissionModeEnum.LIVE,
                grant_recipient=grant_recipient,
                answers=[FactoryAnswer(question, TextSingleLineAnswer(name))],
            )
            factories.submission_event.create(submission=submission)
            factories.submission_event.create(submission=submission)

        _create_submission("First")

        db_session.expire_all()
        with track_sql_queries() as baseline_queries:
            for summary in get_submission_list_for_collection(
                collection=collection, submission_mode=SubmissionModeEnum.LIVE
            ):
                _ = (summary.name, summary.organisation_name, summary.last_updated_at_utc)
        baseline = len(baseline_queries)

        for index in range(3):
            _create_submission(f"Extra {index}")

        db_session.expire_all()
        with track_sql_queries() as iterate_queries:
            summaries = get_submission_list_for_collection(
                collection=collection, submission_mode=SubmissionModeEnum.LIVE
            )
            for summary in summaries:
                _ = (summary.name, summary.organisation_name, summary.last_updated_at_utc)

        assert len(summaries) == 4
        assert len(iterate_queries) == baseline

    def test_single_submission_collection_constant_query_count(self, db_session, factories, track_sql_queries):
        collection = factories.collection.create()

        def _create_recipient_with_submission() -> None:
            grant_recipient = factories.grant_recipient.create(grant=collection.grant)
            submission = factories.submission.create(
                collection=collection, mode=SubmissionModeEnum.LIVE, grant_recipient=grant_recipient
            )
            factories.submission_event.create(submission=submission)
            factories.submission_event.create(submission=submission)

        _create_recipient_with_submission()
        factories.grant_recipient.create(grant=collection.grant)

        db_session.expire_all()
        with track_sql_queries() as baseline_queries:
            for row in get_submission_list_for_collection(
                collection=collection, submission_mode=SubmissionModeEnum.LIVE
            ):
                _ = (row.name, row.organisation_name, row.status, row.last_updated_at_utc)
        baseline = len(baseline_queries)

        for _ in range(3):
            _create_recipient_with_submission()
            factories.grant_recipient.create(grant=collection.grant)

        db_session.expire_all()
        with track_sql_queries() as iterate_queries:
            rows = get_submission_list_for_collection(collection=collection, submission_mode=SubmissionModeEnum.LIVE)
            for row in rows:
                _ = (row.name, row.organisation_name, row.status, row.last_updated_at_utc)

        assert len(rows) == 8
        assert len(iterate_queries) == baseline

    def test_is_assessed_and_assessment_status_default_for_unassessed_submission(self, factories):
        collection = factories.collection.create()
        grant_recipient = factories.grant_recipient.create(grant=collection.grant)
        factories.submission.create(
            collection=collection, mode=SubmissionModeEnum.LIVE, grant_recipient=grant_recipient
        )

        rows = get_submission_list_for_collection(collection=collection, submission_mode=SubmissionModeEnum.LIVE)

        assert rows[0].is_assessed is False
        assert rows[0].assessment_status == SubmissionAssessmentStatusEnum.NOT_STARTED

    @pytest.mark.parametrize(
        "assessment_status",
        [SubmissionAssessmentStatusEnum.MARKED_AS_APPROVED, SubmissionAssessmentStatusEnum.MARKED_AS_REJECTED],
    )
    def test_is_assessed_and_assessment_status_for_assessed_submission(self, db_session, factories, assessment_status):
        collection = factories.collection.create()
        grant_recipient = factories.grant_recipient.create(grant=collection.grant)
        submission = factories.submission.create(
            collection=collection, mode=SubmissionModeEnum.LIVE, grant_recipient=grant_recipient
        )
        submission.assessment_status = assessment_status
        db_session.flush()

        rows = get_submission_list_for_collection(collection=collection, submission_mode=SubmissionModeEnum.LIVE)

        assert rows[0].is_assessed is True
        assert rows[0].assessment_status == assessment_status

    def test_is_assessed_and_assessment_status_are_none_when_no_submission(self, factories):
        collection = factories.collection.create()
        factories.grant_recipient.create(grant=collection.grant)

        rows = get_submission_list_for_collection(collection=collection, submission_mode=SubmissionModeEnum.LIVE)

        assert rows[0].is_assessed is None
        assert rows[0].assessment_status is None


class TestResetTestSubmission:
    def test_reset_test_submission_only_deletes_specified_submission(self, db_session, factories):
        collection = factories.collection.create(create_submissions__test=3)
        test_submissions = collection.test_submissions
        submission_to_delete = test_submissions[0]
        submission_to_delete_id = submission_to_delete.id

        for submission in test_submissions:
            factories.submission_event.create(submission=submission, created_by=submission.created_by)

        reset_test_submission(submission_to_delete)

        submissions_from_db = db_session.query(Submission).where(Submission.mode == SubmissionModeEnum.TEST).all()
        events_from_db = db_session.query(SubmissionEvent).all()

        assert len(submissions_from_db) == 2
        assert len(events_from_db) == 2
        assert submission_to_delete_id not in [s.id for s in submissions_from_db]

    def test_reset_test_submission_raises_error_for_non_test_submission(self, db_session, factories):
        collection = factories.collection.create(create_submissions__live=1)

        # Attempt to reset a live submission should raise ValueError
        with pytest.raises(ValueError, match="Can only reset submissions in TEST mode"):
            reset_test_submission(collection.live_submissions[0])


class TestResetAllTestSubmissions:
    def test_deletes_all_test_submissions_and_their_events(self, db_session, factories):
        collection = factories.collection.create(create_submissions__test=3)
        test_submissions = collection.test_submissions
        test_submission_ids = [s.id for s in test_submissions]

        for submission in test_submissions:
            factories.submission_event.create(submission=submission, created_by=submission.created_by)

        reset_all_test_submissions(collection)

        remaining_submissions = db_session.query(Submission).where(Submission.id.in_(test_submission_ids)).all()
        remaining_events = (
            db_session.query(SubmissionEvent).where(SubmissionEvent.submission_id.in_(test_submission_ids)).all()
        )

        assert len(remaining_submissions) == 0
        assert len(remaining_events) == 0

    def test_does_not_delete_live_submissions(self, db_session, factories):
        collection = factories.collection.create(create_submissions__test=2, create_submissions__live=2)

        for submission in collection.test_submissions + collection.live_submissions:
            factories.submission_event.create(submission=submission, created_by=submission.created_by)

        live_submission_ids = [s.id for s in collection.live_submissions]

        reset_all_test_submissions(collection)

        remaining_test = db_session.query(Submission).where(Submission.mode == SubmissionModeEnum.TEST).all()
        remaining_live = db_session.query(Submission).where(Submission.mode == SubmissionModeEnum.LIVE).all()
        remaining_live_events = (
            db_session.query(SubmissionEvent).where(SubmissionEvent.submission_id.in_(live_submission_ids)).all()
        )

        assert len(remaining_test) == 0
        assert len(remaining_live) == 2
        assert len(remaining_live_events) == 2


class TestDeleteAllCollectionPreviewSubmissions:
    def test_deletes_all_preview_submissions_and_their_events(self, db_session, factories):
        collection = factories.collection.create(create_submissions__preview=3)
        preview_submission_ids = [s.id for s in collection.preview_submissions]

        for submission in collection.preview_submissions:
            factories.submission_event.create(submission=submission, created_by=submission.created_by)

        delete_all_collection_preview_submissions(collection)

        remaining_submissions = db_session.query(Submission).where(Submission.id.in_(preview_submission_ids)).all()
        remaining_events = (
            db_session.query(SubmissionEvent).where(SubmissionEvent.submission_id.in_(preview_submission_ids)).all()
        )

        assert len(remaining_submissions) == 0
        assert len(remaining_events) == 0

    def test_does_not_delete_live_or_test_submissions_or_other_collections_submissions(self, db_session, factories):
        collection = factories.collection.create(
            create_submissions__preview=2, create_submissions__test=1, create_submissions__live=2
        )
        other_collection = factories.collection.create(create_submissions__preview=2)

        for submission in (
            collection.preview_submissions
            + collection.test_submissions
            + collection.live_submissions
            + other_collection.preview_submissions
        ):
            factories.submission_event.create(submission=submission, created_by=submission.created_by)

        delete_all_collection_preview_submissions(collection)

        remaining_preview = db_session.query(Submission).where(Submission.mode == SubmissionModeEnum.PREVIEW).all()
        remaining_test = db_session.query(Submission).where(Submission.mode == SubmissionModeEnum.TEST).all()
        remaining_live = db_session.query(Submission).where(Submission.mode == SubmissionModeEnum.LIVE).all()
        remaining_events = db_session.query(SubmissionEvent).all()

        assert {s.collection_id for s in remaining_preview} == {other_collection.id}
        assert len(remaining_preview) == 2
        assert len(remaining_test) == 1
        assert len(remaining_live) == 2
        assert len(remaining_events) == 5


class TestValidateReference:
    def test_validate_reference_allowed(self, factories):
        form = factories.form.create()
        q1, q2, q3 = factories.question.create_batch(3, form=form, data_type=QuestionDataType.NUMBER)

        expression_context = ExpressionContext.build_expression_context(form.collection, "interpolation", None, None)

        assert (
            _validate_reference(
                reference=ExpressionReference(q1.safe_qid),
                attached_to_component=q2,
                expression_context=expression_context,
                expression_type=ExpressionType.VALIDATION,
                field_name_for_error_message="field",
                question_to_test=None,
            )
            == q1.safe_qid
        )
        assert (
            _validate_reference(
                reference=ExpressionReference(q1.safe_qid),
                attached_to_component=q2,
                expression_context=expression_context,
                expression_type=ExpressionType.CONDITION,
                field_name_for_error_message="field",
                question_to_test=None,
            )
            == q1.safe_qid
        )

    def test_validate_reference_allowed_in_non_expression(self, factories):
        q1 = factories.question.create()
        g = factories.group.create(form=q1.form, add_another=True)
        assert (
            _validate_reference(
                reference=ExpressionReference(q1.safe_qid),
                attached_to_component=g,
                expression_context=ExpressionContext.build_expression_context(
                    q1.form.collection, "interpolation", None, None
                ),
                expression_type=None,
                field_name_for_error_message="field",
                question_to_test=None,
            )
            == q1.safe_qid
        )

    def test_validate_reference_ordering_enforced_in_non_expression(self, factories):
        q1 = factories.question.create()
        g = factories.group.create(form=q1.form, add_another=True)
        q2 = factories.question.create(form=q1.form)
        with pytest.raises(DependencyOrderException):
            _validate_reference(
                reference=ExpressionReference(q2.safe_qid),
                attached_to_component=g,
                expression_context=ExpressionContext.build_expression_context(
                    q1.form.collection, "interpolation", None, None
                ),
                expression_type=None,
                field_name_for_error_message="field",
                question_to_test=None,
            )
        g1_q1 = factories.question.create(parent=g)
        with pytest.raises(DependencyOrderException):
            _validate_reference(
                reference=ExpressionReference(g1_q1.safe_qid),
                attached_to_component=g,
                expression_context=ExpressionContext.build_expression_context(
                    q1.form.collection, "interpolation", None, None
                ),
                expression_type=None,
                field_name_for_error_message="field",
                question_to_test=None,
            )

    def test_validate_reference_invalid_reference_to_self(self, factories):
        form = factories.form.create()
        q1, q2, q3 = factories.question.create_batch(3, form=form, data_type=QuestionDataType.NUMBER)

        expression_context = ExpressionContext.build_expression_context(form.collection, "interpolation", None, None)

        with pytest.raises(DependencyOrderException):
            _validate_reference(
                reference=ExpressionReference(q2.safe_qid),
                attached_to_component=q2,
                expression_context=expression_context,
                expression_type=ExpressionType.VALIDATION,
                field_name_for_error_message="field",
                question_to_test=None,
            )
        with pytest.raises(DependencyOrderException):
            _validate_reference(
                reference=ExpressionReference(q2.safe_qid),
                attached_to_component=q2,
                expression_context=expression_context,
                expression_type=ExpressionType.CONDITION,
                field_name_for_error_message="custom_expression",
                question_to_test=None,
            )

    def test_validate_reference_valid_reference_to_self(self, factories):
        form = factories.form.create()
        q1, q2, q3 = factories.question.create_batch(3, form=form, data_type=QuestionDataType.NUMBER)

        expression_context = ExpressionContext.build_expression_context(form.collection, "interpolation", None, None)

        assert (
            _validate_reference(
                reference=ExpressionReference(q2.safe_qid),
                attached_to_component=q2,
                expression_context=expression_context,
                expression_type=ExpressionType.VALIDATION,
                field_name_for_error_message="custom_expression",
                question_to_test=None,
            )
            == q2.safe_qid
        )

    def test_validate_reference_to_self_in_custom_validation(self, factories):
        form = factories.form.create()
        group = factories.group.create(
            form=form, presentation_options=QuestionPresentationOptions(show_questions_on_the_same_page=True)
        )
        q1 = factories.question.create(form=form, parent=group, data_type=QuestionDataType.NUMBER)

        expression_context = ExpressionContext.build_expression_context(form.collection, "interpolation", None, None)

        assert (
            _validate_reference(
                reference=ExpressionReference(q1.safe_qid),
                attached_to_component=q1,
                expression_context=expression_context,
                expression_type=ExpressionType.VALIDATION,
                field_name_for_error_message="custom_expression",
                question_to_test=None,
            )
            == q1.safe_qid
        )

    def test_custom_expression_bad_order_validation(self, factories):
        form = factories.form.create()
        q1, q2, q3 = factories.question.create_batch(3, form=form, data_type=QuestionDataType.NUMBER)
        context = ExpressionContext.build_expression_context(form.collection, mode="interpolation")
        with pytest.raises(DependencyOrderException) as e:
            _validate_reference(
                reference=ExpressionReference(q3.safe_qid),
                attached_to_component=q2,
                expression_context=context,
                expression_type=ExpressionType.VALIDATION,
                field_name_for_error_message="custom_expression",
                question_to_test=None,
            )
        assert f"You cannot use {q3.name} because it comes after this question" in e.value.form_error_message
        assert e.value.depends_on_question.id == q3.id
        assert e.value.question.id == q2.id

    def test_expression_bad_order_condition(self, factories):
        form = factories.form.create()
        q1, q2, q3 = factories.question.create_batch(3, form=form, data_type=QuestionDataType.NUMBER)
        context = ExpressionContext.build_expression_context(form.collection, mode="interpolation")
        with pytest.raises(DependencyOrderException) as e:
            _validate_reference(
                reference=ExpressionReference(q3.safe_qid),
                attached_to_component=q2,
                expression_context=context,
                expression_type=ExpressionType.CONDITION,
                field_name_for_error_message="custom_expression",
                question_to_test=None,
            )
        assert f"You cannot use {q3.name} because it comes after this question" in e.value.form_error_message
        assert e.value.depends_on_question.id == q3.id
        assert e.value.question.id == q2.id

        with pytest.raises(DependencyOrderException) as e:
            _validate_reference(
                reference=ExpressionReference(q1.safe_qid),
                attached_to_component=q2,
                expression_context=context,
                expression_type=ExpressionType.CONDITION,
                field_name_for_error_message="field",
                question_to_test=q3,
            )
        assert "Cannot reference a later question" in str(e)
        assert e.value.depends_on_question.id == q3.id
        assert e.value.question.id == q2.id

    def test_custom_expression_bad_reference(self, factories):
        form = factories.form.create()
        q1, q2, q3 = factories.question.create_batch(3, form=form, data_type=QuestionDataType.NUMBER)
        context = ExpressionContext.build_expression_context(form.collection, mode="interpolation")
        with pytest.raises(InvalidReferenceInExpression) as e:
            _validate_reference(
                reference=ExpressionReference("q_bad_ref"),
                attached_to_component=q2,
                expression_context=context,
                expression_type=ExpressionType.VALIDATION,
                field_name_for_error_message="custom_expression",
                question_to_test=None,
            )
        assert "You cannot use ((q_bad_ref)) because it does not exist" in str(e)
        assert e.value.field_name == "custom_expression"
        assert e.value.bad_reference == "((q_bad_ref))"

    def test_custom_expression_wrong_data_type_validation(self, factories):
        form = factories.form.create()
        q1 = factories.question.create(form=form)
        q2 = factories.question.create(form=form, data_type=QuestionDataType.NUMBER)
        context = ExpressionContext.build_expression_context(form.collection, mode="interpolation")
        with pytest.raises(IncompatibleDataTypeException) as e:
            _validate_reference(
                reference=ExpressionReference(q1.safe_qid),
                attached_to_component=q2,
                expression_context=context,
                expression_type=ExpressionType.VALIDATION,
                field_name_for_error_message="custom_expression",
                question_to_test=None,
            )
        assert "Reference is not valid due to incompatible data types" in e.value.form_error_message
        assert e.value.field_name == "custom_expression"
        assert e.value.depends_on_question == q1

    def test_validate_reference_wrong_data_type_condition_referenced_question(self, factories):
        form = factories.form.create()
        q1 = factories.question.create(form=form, data_type=QuestionDataType.TEXT_SINGLE_LINE)
        q2, q3 = factories.question.create_batch(2, form=form, data_type=QuestionDataType.NUMBER)
        context = ExpressionContext.build_expression_context(form.collection, mode="interpolation")
        with pytest.raises(IncompatibleDataTypeException) as e:
            _validate_reference(
                reference=ExpressionReference(q2.safe_qid),
                attached_to_component=q3,
                expression_context=context,
                expression_type=ExpressionType.CONDITION,
                field_name_for_error_message="test",
                question_to_test=q1,
            )
        assert "Reference is not valid due to incompatible data types" in e.value.form_error_message
        assert e.value.field_name == "test"
        assert e.value.depends_on_question == q2

    def test_validate_reference_non_number_in_calculated_condition(self, factories):
        form = factories.form.create()
        q1 = factories.question.create(form=form, data_type=QuestionDataType.TEXT_SINGLE_LINE)
        q2 = factories.question.create(form=form, data_type=QuestionDataType.NUMBER)
        context = ExpressionContext.build_expression_context(form.collection, mode="interpolation")
        with pytest.raises(IncompatibleDataTypeException) as e:
            _validate_reference(
                reference=ExpressionReference(q1.safe_qid),
                attached_to_component=q2,
                expression_context=context,
                expression_type=ExpressionType.CONDITION,
                field_name_for_error_message="custom_expression",
                question_to_test=None,
            )
        assert "Reference is not valid due to incompatible data types" in e.value.form_error_message
        assert e.value.field_name == "custom_expression"
        assert e.value.depends_on_question == q1

    def test_invalid_add_another_reference(self, factories):
        form = factories.form.create()
        group = factories.group.create(form=form, add_another=True)
        g1_q1 = factories.question.create(form=form, parent=group, data_type=QuestionDataType.NUMBER)
        g1_q2 = factories.question.create(form=form, parent=group, data_type=QuestionDataType.NUMBER)
        q1 = factories.question.create(form=form, data_type=QuestionDataType.NUMBER)
        q2 = factories.question.create(form=form, data_type=QuestionDataType.NUMBER)

        expression_context = ExpressionContext.build_expression_context(form.collection, "interpolation", None, None)

        with pytest.raises(AddAnotherDependencyException):
            _validate_reference(
                reference=ExpressionReference(g1_q1.safe_qid),
                attached_to_component=q1,
                expression_context=expression_context,
                expression_type=ExpressionType.VALIDATION,
                field_name_for_error_message="field",
                question_to_test=None,
            )
        with pytest.raises(AddAnotherDependencyException):
            _validate_reference(
                reference=ExpressionReference(g1_q1.safe_qid),
                attached_to_component=q1,
                expression_context=expression_context,
                expression_type=None,
                field_name_for_error_message="field",
                question_to_test=None,
            )

        with pytest.raises(AddAnotherDependencyException):
            _validate_reference(
                reference=ExpressionReference(q1.safe_qid),
                attached_to_component=q2,
                expression_context=expression_context,
                expression_type=ExpressionType.VALIDATION,
                field_name_for_error_message="field",
                question_to_test=g1_q1,
            )

        with pytest.raises(DependencyOrderException):
            _validate_reference(
                reference=ExpressionReference(g1_q2.safe_qid),
                attached_to_component=g1_q1,
                expression_context=expression_context,
                expression_type=ExpressionType.VALIDATION,
                field_name_for_error_message="field",
                question_to_test=None,
            )

        with pytest.raises(AddAnotherDependencyException):
            _validate_reference(
                reference=ExpressionReference(g1_q2.safe_qid),
                attached_to_component=q2,
                expression_context=expression_context,
                expression_type=ExpressionType.CONDITION,
                field_name_for_error_message="field",
                question_to_test=g1_q1,
            )

    def test_valid_add_another_reference(self, factories):
        form = factories.form.create()
        q1 = factories.question.create(form=form, data_type=QuestionDataType.NUMBER)
        group = factories.group.create(form=form, add_another=True)
        g1_q1 = factories.question.create(form=form, parent=group, data_type=QuestionDataType.NUMBER)
        g1_q2 = factories.question.create(form=form, parent=group, data_type=QuestionDataType.NUMBER)

        expression_context = ExpressionContext.build_expression_context(form.collection, "interpolation", None, None)

        _validate_reference(
            reference=ExpressionReference(q1.safe_qid),
            attached_to_component=g1_q1,
            expression_context=expression_context,
            expression_type=ExpressionType.VALIDATION,
            field_name_for_error_message="field",
            question_to_test=None,
        )

        _validate_reference(
            reference=ExpressionReference(g1_q1.safe_qid),
            attached_to_component=g1_q2,
            expression_context=expression_context,
            expression_type=ExpressionType.CONDITION,
            field_name_for_error_message="field",
            question_to_test=q1,
        )

    def test_invalid_same_page_group(self, factories):
        form = factories.form.create()
        group = factories.group.create(
            form=form, presentation_options=QuestionPresentationOptions(show_questions_on_the_same_page=True)
        )
        q1, q2 = factories.question.create_batch(2, form=form, parent=group, data_type=QuestionDataType.NUMBER)

        expression_context = ExpressionContext.build_expression_context(form.collection, "interpolation", None, None)

        with pytest.raises(InvalidReferenceInExpression):
            assert (
                _validate_reference(
                    reference=ExpressionReference(q1.safe_qid),
                    attached_to_component=q2,
                    expression_context=expression_context,
                    expression_type=ExpressionType.VALIDATION,
                    field_name_for_error_message="field",
                    question_to_test=None,
                )
                == q1.safe_qid
            )

    def test_invalid_data_type_group_validation(self, factories):
        form = factories.form.create()
        q1 = factories.question.create(form=form, data_type=QuestionDataType.TEXT_SINGLE_LINE)
        group = factories.group.create(form=form)

        expression_context = ExpressionContext.build_expression_context(form.collection, "interpolation", None, None)

        with pytest.raises(IncompatibleDataTypeException):
            _validate_reference(
                reference=ExpressionReference(q1.safe_qid),
                attached_to_component=group,
                expression_context=expression_context,
                expression_type=ExpressionType.VALIDATION,
                field_name_for_error_message="custom_expression",
                question_to_test=None,
            )


class TestAddComponentValidationCustomExpression:
    def test_create_custom_validation_expression_valid(self, factories):
        form = factories.form.create()
        q1, q2, q3 = factories.question.create_batch(3, form=form, data_type=QuestionDataType.NUMBER)
        user = factories.user.create()
        custom_expr = CustomExpression(
            custom_expression=EvaluationStatement(f"(({q3.safe_qid})) <= (({q1.safe_qid})) + (({q2.safe_qid}))"),
            custom_message=InterpolationStatement("Q3 must be less than Q1+Q2"),
        )

        collections.add_component_validation(component=q3, user=user, evaluatable_expression=custom_expr)

        q3_from_db = get_question_by_id(q3.id)
        assert len(q3_from_db.expressions) == 1
        assert len(q3_from_db.owned_component_references) == 2


class TestGetCollectionsByStatusExcludingDraftGrants:
    def test_returns_collections_matching_single_status(self, db_session, factories):
        grant = factories.grant.create(status=GrantStatusEnum.LIVE)
        open_collection = factories.collection.create(grant=grant, status=CollectionStatusEnum.OPEN)
        factories.collection.create(grant=grant, status=CollectionStatusEnum.DRAFT)

        result = get_collections_by_status_excluding_draft_grants([CollectionStatusEnum.OPEN])

        assert len(result) == 1
        assert result[0].id == open_collection.id

    def test_returns_collections_matching_multiple_statuses(self, db_session, factories):
        grant = factories.grant.create(status=GrantStatusEnum.LIVE)
        open_collection = factories.collection.create(grant=grant, status=CollectionStatusEnum.OPEN)
        scheduled_collection = factories.collection.create(grant=grant, status=CollectionStatusEnum.SCHEDULED)
        factories.collection.create(grant=grant, status=CollectionStatusEnum.DRAFT)

        result = get_collections_by_status_excluding_draft_grants(
            [CollectionStatusEnum.OPEN, CollectionStatusEnum.SCHEDULED]
        )

        assert len(result) == 2
        result_ids = {c.id for c in result}
        assert open_collection.id in result_ids
        assert scheduled_collection.id in result_ids

    def test_returns_empty_when_no_match(self, db_session, factories):
        grant = factories.grant.create(status=GrantStatusEnum.LIVE)
        factories.collection.create(grant=grant, status=CollectionStatusEnum.DRAFT)

        result = get_collections_by_status_excluding_draft_grants([CollectionStatusEnum.CLOSED])

        assert result == []

    def test_excludes_collections_from_draft_grants(self, db_session, factories):
        draft_grant = factories.grant.create(status=GrantStatusEnum.DRAFT)
        factories.collection.create(grant=draft_grant, status=CollectionStatusEnum.OPEN)

        result = get_collections_by_status_excluding_draft_grants([CollectionStatusEnum.OPEN])

        assert result == []

    def test_includes_collections_from_onboarding_grants(self, db_session, factories):
        grant = factories.grant.create(status=GrantStatusEnum.ONBOARDING)
        collection = factories.collection.create(grant=grant, status=CollectionStatusEnum.OPEN)

        result = get_collections_by_status_excluding_draft_grants([CollectionStatusEnum.OPEN])

        assert len(result) == 1
        assert result[0].id == collection.id

    def test_eagerly_loads_grant(self, db_session, factories, track_sql_queries):
        grant = factories.grant.create(name="Test Grant", status=GrantStatusEnum.LIVE)
        factories.collection.create(grant=grant, status=CollectionStatusEnum.OPEN)

        with track_sql_queries() as queries:
            result = get_collections_by_status_excluding_draft_grants([CollectionStatusEnum.OPEN])
            _ = result[0].grant.name

        assert result[0].grant.name == "Test Grant"
        assert len(queries) == 1


class TestGetOverdueOpenCollectionsExcludingDraftGrants:
    def test_returns_open_collections_past_end_date(self, db_session, factories):
        grant = factories.grant.create(status=GrantStatusEnum.LIVE)
        overdue = factories.collection.create(
            grant=grant,
            status=CollectionStatusEnum.OPEN,
            submission_period_end_date=datetime.date(2020, 1, 1),
        )

        result = get_overdue_open_collections_excluding_draft_grants()

        assert len(result) == 1
        assert result[0].id == overdue.id

    def test_excludes_open_collections_with_future_end_date(self, db_session, factories):
        grant = factories.grant.create(status=GrantStatusEnum.LIVE)
        factories.collection.create(
            grant=grant,
            status=CollectionStatusEnum.OPEN,
            submission_period_end_date=datetime.date(2099, 12, 31),
        )

        result = get_overdue_open_collections_excluding_draft_grants()

        assert result == []

    def test_excludes_closed_collections(self, db_session, factories):
        grant = factories.grant.create(status=GrantStatusEnum.LIVE)
        factories.collection.create(
            grant=grant,
            status=CollectionStatusEnum.CLOSED,
            submission_period_end_date=datetime.date(2020, 1, 1),
        )

        result = get_overdue_open_collections_excluding_draft_grants()

        assert result == []

    def test_excludes_open_collections_without_end_date(self, db_session, factories):
        grant = factories.grant.create(status=GrantStatusEnum.LIVE)
        factories.collection.create(
            grant=grant,
            status=CollectionStatusEnum.OPEN,
            submission_period_end_date=None,
        )

        result = get_overdue_open_collections_excluding_draft_grants()

        assert result == []

    def test_excludes_collections_from_draft_grants(self, db_session, factories):
        draft_grant = factories.grant.create(status=GrantStatusEnum.DRAFT)
        factories.collection.create(
            grant=draft_grant,
            status=CollectionStatusEnum.OPEN,
            submission_period_end_date=datetime.date(2020, 1, 1),
        )

        result = get_overdue_open_collections_excluding_draft_grants()

        assert result == []


@pytest.mark.freeze_time("2026-06-15 12:00:00")
class TestGetCollectionsWithDatesNearTodayExcludingDraftGrants:
    def test_returns_collections_with_start_date_in_future_window(self, db_session, factories):
        grant = factories.grant.create(status=GrantStatusEnum.LIVE)
        collection = factories.collection.create(
            grant=grant,
            submission_period_start_date=datetime.date(2026, 6, 18),
        )

        result = get_collections_with_dates_near_today_excluding_draft_grants(past_days=7, future_days=7)

        assert len(result) == 1
        assert result[0].id == collection.id

    def test_returns_collections_with_end_date_in_past_window(self, db_session, factories):
        grant = factories.grant.create(status=GrantStatusEnum.LIVE)
        collection = factories.collection.create(
            grant=grant,
            status=CollectionStatusEnum.OPEN,
            submission_period_end_date=datetime.date(2026, 6, 10),
        )

        result = get_collections_with_dates_near_today_excluding_draft_grants(past_days=7, future_days=7)

        assert len(result) == 1
        assert result[0].id == collection.id

    def test_excludes_collections_outside_window(self, db_session, factories):
        grant = factories.grant.create(status=GrantStatusEnum.LIVE)
        factories.collection.create(
            grant=grant,
            submission_period_start_date=datetime.date(2026, 7, 1),
            submission_period_end_date=datetime.date(2026, 7, 31),
        )

        result = get_collections_with_dates_near_today_excluding_draft_grants(past_days=7, future_days=7)

        assert result == []

    def test_excludes_collections_from_draft_grants(self, db_session, factories):
        draft_grant = factories.grant.create(status=GrantStatusEnum.DRAFT)
        factories.collection.create(
            grant=draft_grant,
            submission_period_start_date=datetime.date(2026, 6, 18),
        )

        result = get_collections_with_dates_near_today_excluding_draft_grants(past_days=7, future_days=7)

        assert result == []

    def test_respects_past_days_parameter(self, db_session, factories):
        grant = factories.grant.create(status=GrantStatusEnum.LIVE)
        factories.collection.create(
            grant=grant,
            status=CollectionStatusEnum.OPEN,
            submission_period_end_date=datetime.date(2026, 6, 10),
        )

        result_with_past = get_collections_with_dates_near_today_excluding_draft_grants(past_days=7, future_days=7)
        result_without_past = get_collections_with_dates_near_today_excluding_draft_grants(past_days=0, future_days=7)

        assert len(result_with_past) == 1
        assert result_without_past == []
