text: """add comments table
  
  Revision ID: ff7bf9a10e57
  Revises: f2128b73734b
  Create Date: 2020-12-16 16:43:52.158234
  
  """
  from alembic import op
  import sqlalchemy as sa
  from sqlalchemy.dialects import postgresql
  from sqlalchemy.orm.session import Session
  
  # revision identifiers, used by Alembic.
  revision = "ff7bf9a10e57"
  down_revision = "f2128b73734b"
  branch_labels = None
  depends_on = None
  
  
  def _migrate_comments():
      session = Session(bind=op.get_bind())
      activity_versions = session.execute(
          """
          SELECT av1.reception_time, av1.submitter_id, av1.context->'comment', a.mission_id, av1.id
          FROM activity_version av1
          JOIN activity_version av2 ON av1.activity_id = av2.activity_id AND av1.version = av2.version + 1 AND av1.end_time IS NOT NULL AND av2.end_time IS NULL
          JOIN activity a ON av1.activity_id = a.id
          """
      )
  
      comments_to_create = [av for av in activity_versions if av[2]]
  
      deduplicated_comments = []
      for comment in comments_to_create:
          comment_already_exists = False
          for c2 in deduplicated_comments:
              if (
                  comment[3] == c2[3]
                  and comment[1] == c2[1]
                  and comment[2] == c2[2]
              ):
                  comment_already_exists = True
                  break
          if not comment_already_exists:
              deduplicated_comments.append(comment)
  
      for comment in deduplicated_comments:
          session.execute(
              """
              INSERT INTO comment(
                  creation_time,
                  reception_time,
                  submitter_id,
                  mission_id,
                  text
              )
              VALUES(
                  NOW(),
                  :reception_time,
                  :submitter_id,
                  :mission_id,
                  :text
              )
              """,
              dict(
                  submitter_id=comment[1],
                  reception_time=comment[0],
                  mission_id=comment[3],
                  text=comment[2],
              ),
          )
          session.execute(
              """
              UPDATE activity_version SET context = null WHERE id = :id
              """,
              dict(id=comment[4]),
          )
  
  
  def upgrade():
      # ### commands auto generated by Alembic - please adjust! ###
      op.create_table(
          "comment",
          sa.Column("creation_time", sa.DateTime(), nullable=False),
          sa.Column("reception_time", sa.DateTime(), nullable=False),
          sa.Column("dismissed_at", sa.DateTime(), nullable=True),
          sa.Column(
              "dismiss_context",
              postgresql.JSONB(none_as_null=True, astext_type=sa.Text()),
              nullable=True,
          ),
          sa.Column("mission_id", sa.Integer(), nullable=False),
          sa.Column("text", sa.TEXT(), nullable=False),
          sa.Column("submitter_id", sa.Integer(), nullable=False),
          sa.Column("id", sa.Integer(), nullable=False),
          sa.Column("dismiss_author_id", sa.Integer(), nullable=True),
          sa.CheckConstraint(
              "((dismissed_at is not null)::bool = (dismiss_author_id is not null)::bool)",
              name="non_nullable_dismiss_info",
          ),
          sa.ForeignKeyConstraint(
              ["dismiss_author_id"],
              ["user.id"],
          ),
          sa.ForeignKeyConstraint(
              ["mission_id"],
              ["mission.id"],
          ),
          sa.ForeignKeyConstraint(
              ["submitter_id"],
              ["user.id"],
          ),
          sa.PrimaryKeyConstraint("id"),
      )
  
      _migrate_comments()
  
      op.create_index(
          op.f("ix_comment_dismiss_author_id"),
          "comment",
          ["dismiss_author_id"],
          unique=False,
      )
      op.create_index(
          op.f("ix_comment_mission_id"), "comment", ["mission_id"], unique=False
      )
      op.create_index(
          op.f("ix_comment_submitter_id"),
          "comment",
          ["submitter_id"],
          unique=False,
      )
  
  
  def downgrade():
      # ### commands auto generated by Alembic - please adjust! ###
      op.drop_index(op.f("ix_comment_submitter_id"), table_name="comment")
      op.drop_index(op.f("ix_comment_mission_id"), table_name="comment")
      op.drop_index(op.f("ix_comment_dismiss_author_id"), table_name="comment")
      op.drop_table("comment")
      # ### end Alembic commands ###
  