Add models & migrations for form questions

5 files changed, 410 insertions, 0 deletions

diff --git a/backend/constants.py b/backend/constants.py
index eb0c68f..4bb051c 100644
--- a/backend/constants.py
+++ b/backend/constants.py
@@ -56,6 +56,12 @@ QUESTION_TYPES = [
     "vote",
 ]
 
+
+class TextType(Enum):
+    SHORT_TEXT = "short_text"
+    TEXT_AREA = "text_area"
+
+
 REQUIRED_QUESTION_TYPE_DATA = {
     "radio": {
         "options": list,
diff --git a/backend/models/orm/form_questions.py b/backend/models/orm/form_questions.py
new file mode 100644
index 0000000..cde1d0b
--- /dev/null
+++ b/backend/models/orm/form_questions.py
@@ -0,0 +1,198 @@
+"""Discord members who have admin access."""
+
+from typing import ClassVar
+
+from sqlalchemy import Enum, ForeignKey, Text
+from sqlalchemy.dialects.postgresql import ARRAY
+from sqlalchemy.orm import Mapped, mapped_column, relationship
+
+from backend.constants import TextType
+
+from .base import Base
+
+
+class FormQuestion(Base):
+    __tablename__ = "form_questions"
+
+    question_id: Mapped[int] = mapped_column(primary_key=True)
+    form_id: Mapped[int] = mapped_column(ForeignKey("forms.form_id"))
+    name: Mapped[str]
+    type: Mapped[str]
+    required: Mapped[bool]
+
+    __mapper_args__: ClassVar = {"polymorphic_identity": "form_questions", "polymorphic_on": "type"}
+
+    def __repr__(self):
+        return f"{self.__class__.__name__}({self.name!r})"
+
+
+class QuestionWithOptions:
+    options: Mapped[list[str]] = mapped_column(
+        ARRAY(Text),
+        nullable=False,
+        use_existing_column=True,
+    )
+
+
+class FormRadioQuestion(QuestionWithOptions, FormQuestion):
+    """A radio question type."""
+
+    __tablename__ = "form_radio_questions"
+
+    radio_question_id: Mapped[int] = mapped_column(
+        ForeignKey("form_questions.question_id"),
+        primary_key=True,
+    )
+
+    __mapper_args__: ClassVar = {
+        "polymorphic_load": "selectin",
+        "polymorphic_identity": "form_radio_questions",
+    }
+
+
+class FormCheckboxQuestion(QuestionWithOptions, FormQuestion):
+    """A radio question type."""
+
+    __tablename__ = "form_checkbox_questions"
+
+    checkbox_question_id: Mapped[int] = mapped_column(
+        ForeignKey("form_questions.question_id"),
+        primary_key=True,
+    )
+
+    __mapper_args__: ClassVar = {
+        "polymorphic_load": "selectin",
+        "polymorphic_identity": "form_checkbox_questions",
+    }
+
+
+class FormRangeQuestion(QuestionWithOptions, FormQuestion):
+    """A range question type."""
+
+    __tablename__ = "form_range_questions"
+
+    range_question_id: Mapped[int] = mapped_column(
+        ForeignKey("form_questions.question_id"),
+        primary_key=True,
+    )
+
+    __mapper_args__: ClassVar = {
+        "polymorphic_load": "selectin",
+        "polymorphic_identity": "form_range_questions",
+    }
+
+
+class FormVoteQuestion(QuestionWithOptions, FormQuestion):
+    """A vote question type."""
+
+    __tablename__ = "form_vote_questions"
+
+    range_question_id: Mapped[int] = mapped_column(
+        ForeignKey("form_questions.question_id"),
+        primary_key=True,
+    )
+
+    __mapper_args__: ClassVar = {
+        "polymorphic_load": "selectin",
+        "polymorphic_identity": "form_vote_questions",
+    }
+
+
+class FormSelectQuestion(QuestionWithOptions, FormQuestion):
+    """A select question type."""
+
+    __tablename__ = "form_select_questions"
+
+    select_question_id: Mapped[int] = mapped_column(
+        ForeignKey("form_questions.question_id"),
+        primary_key=True,
+    )
+
+    __mapper_args__: ClassVar = {
+        "polymorphic_load": "selectin",
+        "polymorphic_identity": "form_select_questions",
+    }
+
+
+class FormTextQuestion(FormQuestion):
+    """A text question type."""
+
+    __tablename__ = "form_text_questions"
+
+    text_question_id: Mapped[int] = mapped_column(
+        ForeignKey("form_questions.question_id"),
+        primary_key=True,
+    )
+    text_type: Mapped[TextType] = mapped_column(Enum(TextType))
+
+    __mapper_args__: ClassVar = {
+        "polymorphic_load": "selectin",
+        "polymorphic_identity": "form_text_questions",
+    }
+
+
+class FormCodeQuestion(FormQuestion):
+    """A code question type."""
+
+    __tablename__ = "form_code_questions"
+
+    code_question_id: Mapped[int] = mapped_column(
+        ForeignKey("form_questions.question_id"),
+        primary_key=True,
+    )
+    language: Mapped[str]
+    allow_failure: Mapped[bool]
+    unit_tests: Mapped[list["FormCodeQuestionTest"]] = relationship(
+        cascade="all, delete",
+        passive_deletes=True,
+    )
+
+    __mapper_args__: ClassVar = {
+        "polymorphic_load": "selectin",
+        "polymorphic_identity": "form_code_questions",
+    }
+
+
+class FormCodeQuestionTest(Base):
+    """Unit tests for a given code question."""
+
+    __tablename__ = "form_code_question_tests"
+    test_id: Mapped[int] = mapped_column(primary_key=True)
+    code_question_id: Mapped[int] = mapped_column(
+        ForeignKey("form_code_questions.code_question_id")
+    )
+    name: Mapped[str]
+    code: Mapped[str]
+
+
+class FormSectionQuestion(FormQuestion):
+    """A section question type."""
+
+    __tablename__ = "form_section_questions"
+
+    section_question_id: Mapped[int] = mapped_column(
+        ForeignKey("form_questions.question_id"),
+        primary_key=True,
+    )
+    text: Mapped[str]
+
+    __mapper_args__: ClassVar = {
+        "polymorphic_load": "selectin",
+        "polymorphic_identity": "form_section_questions",
+    }
+
+
+class FormTimezoneQuestion(FormQuestion):
+    """A timezone question type."""
+
+    __tablename__ = "form_timezone_questions"
+
+    timezone_question_id: Mapped[int] = mapped_column(
+        ForeignKey("form_questions.question_id"),
+        primary_key=True,
+    )
+
+    __mapper_args__: ClassVar = {
+        "polymorphic_load": "selectin",
+        "polymorphic_identity": "form_timezone_questions",
+    }
diff --git a/backend/models/orm/forms.py b/backend/models/orm/forms.py
index 489b71b..71119c7 100644
--- a/backend/models/orm/forms.py
+++ b/backend/models/orm/forms.py
@@ -1,5 +1,7 @@
 """All forms that can have submissions."""
 
+from typing import TYPE_CHECKING
+
 import sqlalchemy.dialects.postgresql as pg
 from sqlalchemy import ForeignKey
 from sqlalchemy.orm import Mapped, mapped_column, relationship
@@ -9,6 +11,9 @@ from backend.constants import FormFeatures
 
 from .base import Base
 
+if TYPE_CHECKING:
+    from . import FormQuestion
+
 
 class Form(Base):
     """A form that users can submit responses to."""
@@ -22,6 +27,11 @@ class Form(Base):
     description: Mapped[str] = mapped_column(Text, nullable=False)
     submission_text: Mapped[str | None] = mapped_column(Text, nullable=True)
 
+    form_questions: Mapped[list["FormQuestion"]] = relationship(
+        cascade="all, delete",
+        passive_deletes=True,
+    )
+
     webhook_url: Mapped[str | None] = mapped_column(Text, nullable=True)
     webhook_message: Mapped[str | None] = mapped_column(Text, nullable=True)
 
diff --git a/migrations/versions/1721493967-787116206e6d_add_form_question_models.py b/migrations/versions/1721493967-787116206e6d_add_form_question_models.py
new file mode 100644
index 0000000..17acd61
--- /dev/null
+++ b/migrations/versions/1721493967-787116206e6d_add_form_question_models.py
@@ -0,0 +1,160 @@
+"""
+Add form question models.
+
+Revision ID: 787116206e6d
+Revises: a9ea4b71d23a
+Create Date: 2024-07-20 16:46:07.590445+00:00
+"""
+
+import sqlalchemy as sa
+from alembic import op
+from sqlalchemy.dialects import postgresql
+
+# revision identifiers, used by Alembic.
+revision = "787116206e6d"
+down_revision = "a9ea4b71d23a"
+branch_labels = None
+depends_on = None
+
+
+def upgrade() -> None:
+    """Apply this migration."""
+    # ### commands auto generated by Alembic - please adjust! ###
+    op.create_table(
+        "form_questions",
+        sa.Column("question_id", sa.Integer(), nullable=False),
+        sa.Column("name", sa.String(), nullable=False),
+        sa.Column("type", sa.String(), nullable=False),
+        sa.Column("required", sa.Boolean(), nullable=False),
+        sa.PrimaryKeyConstraint("question_id", name=op.f("form_questions_pk")),
+    )
+    op.create_table(
+        "form_checkbox_questions",
+        sa.Column("checkbox_question_id", sa.Integer(), nullable=False),
+        sa.Column("options", postgresql.ARRAY(sa.Text()), nullable=False),
+        sa.ForeignKeyConstraint(
+            ["checkbox_question_id"],
+            ["form_questions.question_id"],
+            name=op.f("form_checkbox_questions_checkbox_question_id_form_questions_fk"),
+        ),
+        sa.PrimaryKeyConstraint("checkbox_question_id", name=op.f("form_checkbox_questions_pk")),
+    )
+    op.create_table(
+        "form_code_questions",
+        sa.Column("code_question_id", sa.Integer(), nullable=False),
+        sa.Column("language", sa.String(), nullable=False),
+        sa.Column("allow_failure", sa.Boolean(), nullable=False),
+        sa.ForeignKeyConstraint(
+            ["code_question_id"],
+            ["form_questions.question_id"],
+            name=op.f("form_code_questions_code_question_id_form_questions_fk"),
+        ),
+        sa.PrimaryKeyConstraint("code_question_id", name=op.f("form_code_questions_pk")),
+    )
+    op.create_table(
+        "form_radio_questions",
+        sa.Column("radio_question_id", sa.Integer(), nullable=False),
+        sa.Column("options", postgresql.ARRAY(sa.Text()), nullable=False),
+        sa.ForeignKeyConstraint(
+            ["radio_question_id"],
+            ["form_questions.question_id"],
+            name=op.f("form_radio_questions_radio_question_id_form_questions_fk"),
+        ),
+        sa.PrimaryKeyConstraint("radio_question_id", name=op.f("form_radio_questions_pk")),
+    )
+    op.create_table(
+        "form_range_questions",
+        sa.Column("range_question_id", sa.Integer(), nullable=False),
+        sa.Column("options", postgresql.ARRAY(sa.Text()), nullable=False),
+        sa.ForeignKeyConstraint(
+            ["range_question_id"],
+            ["form_questions.question_id"],
+            name=op.f("form_range_questions_range_question_id_form_questions_fk"),
+        ),
+        sa.PrimaryKeyConstraint("range_question_id", name=op.f("form_range_questions_pk")),
+    )
+    op.create_table(
+        "form_section_questions",
+        sa.Column("section_question_id", sa.Integer(), nullable=False),
+        sa.Column("text", sa.String(), nullable=False),
+        sa.ForeignKeyConstraint(
+            ["section_question_id"],
+            ["form_questions.question_id"],
+            name=op.f("form_section_questions_section_question_id_form_questions_fk"),
+        ),
+        sa.PrimaryKeyConstraint("section_question_id", name=op.f("form_section_questions_pk")),
+    )
+    op.create_table(
+        "form_select_questions",
+        sa.Column("select_question_id", sa.Integer(), nullable=False),
+        sa.Column("options", postgresql.ARRAY(sa.Text()), nullable=False),
+        sa.ForeignKeyConstraint(
+            ["select_question_id"],
+            ["form_questions.question_id"],
+            name=op.f("form_select_questions_select_question_id_form_questions_fk"),
+        ),
+        sa.PrimaryKeyConstraint("select_question_id", name=op.f("form_select_questions_pk")),
+    )
+    op.create_table(
+        "form_text_questions",
+        sa.Column("text_question_id", sa.Integer(), nullable=False),
+        sa.Column("text_type", sa.Enum("SHORT_TEXT", "TEXT_AREA", name="texttype"), nullable=False),
+        sa.ForeignKeyConstraint(
+            ["text_question_id"],
+            ["form_questions.question_id"],
+            name=op.f("form_text_questions_text_question_id_form_questions_fk"),
+        ),
+        sa.PrimaryKeyConstraint("text_question_id", name=op.f("form_text_questions_pk")),
+    )
+    op.create_table(
+        "form_timezone_questions",
+        sa.Column("timezone_question_id", sa.Integer(), nullable=False),
+        sa.ForeignKeyConstraint(
+            ["timezone_question_id"],
+            ["form_questions.question_id"],
+            name=op.f("form_timezone_questions_timezone_question_id_form_questions_fk"),
+        ),
+        sa.PrimaryKeyConstraint("timezone_question_id", name=op.f("form_timezone_questions_pk")),
+    )
+    op.create_table(
+        "form_vote_questions",
+        sa.Column("range_question_id", sa.Integer(), nullable=False),
+        sa.Column("options", postgresql.ARRAY(sa.Text()), nullable=False),
+        sa.ForeignKeyConstraint(
+            ["range_question_id"],
+            ["form_questions.question_id"],
+            name=op.f("form_vote_questions_range_question_id_form_questions_fk"),
+        ),
+        sa.PrimaryKeyConstraint("range_question_id", name=op.f("form_vote_questions_pk")),
+    )
+    op.create_table(
+        "form_code_question_tests",
+        sa.Column("test_id", sa.Integer(), nullable=False),
+        sa.Column("code_question_id", sa.Integer(), nullable=False),
+        sa.Column("name", sa.String(), nullable=False),
+        sa.Column("code", sa.String(), nullable=False),
+        sa.ForeignKeyConstraint(
+            ["code_question_id"],
+            ["form_code_questions.code_question_id"],
+            name=op.f("form_code_question_tests_code_question_id_form_code_questions_fk"),
+        ),
+        sa.PrimaryKeyConstraint("test_id", name=op.f("form_code_question_tests_pk")),
+    )
+    # ### end Alembic commands ###
+
+
+def downgrade() -> None:
+    """Revert this migration."""
+    # ### commands auto generated by Alembic - please adjust! ###
+    op.drop_table("form_code_question_tests")
+    op.drop_table("form_vote_questions")
+    op.drop_table("form_timezone_questions")
+    op.drop_table("form_text_questions")
+    op.drop_table("form_select_questions")
+    op.drop_table("form_section_questions")
+    op.drop_table("form_range_questions")
+    op.drop_table("form_radio_questions")
+    op.drop_table("form_code_questions")
+    op.drop_table("form_checkbox_questions")
+    op.drop_table("form_questions")
+    # ### end Alembic commands ###
diff --git a/migrations/versions/1721564095-bef2f206168e_link_form_questions_to_forms.py b/migrations/versions/1721564095-bef2f206168e_link_form_questions_to_forms.py
new file mode 100644
index 0000000..e84ecf4
--- /dev/null
+++ b/migrations/versions/1721564095-bef2f206168e_link_form_questions_to_forms.py
@@ -0,0 +1,36 @@
+"""
+Link form questions to forms.
+
+Revision ID: bef2f206168e
+Revises: 787116206e6d
+Create Date: 2024-07-21 12:14:55.545648+00:00
+"""
+
+import sqlalchemy as sa
+from alembic import op
+
+# revision identifiers, used by Alembic.
+revision = "bef2f206168e"
+down_revision = "787116206e6d"
+branch_labels = None
+depends_on = None
+
+
+def upgrade() -> None:
+    """Apply this migration."""
+    # ### commands auto generated by Alembic - please adjust! ###
+    op.add_column("form_questions", sa.Column("form_id", sa.Integer(), nullable=False))
+    op.create_foreign_key(
+        op.f("form_questions_form_id_forms_fk"), "form_questions", "forms", ["form_id"], ["form_id"]
+    )
+    # ### end Alembic commands ###
+
+
+def downgrade() -> None:
+    """Revert this migration."""
+    # ### commands auto generated by Alembic - please adjust! ###
+    op.drop_constraint(
+        op.f("form_questions_form_id_forms_fk"), "form_questions", type_="foreignkey"
+    )
+    op.drop_column("form_questions", "form_id")
+    # ### end Alembic commands ###