"""Only one validation per mission, user and actor

Revision ID: 1a89721126f7
Revises: fa96dfc8237d
Create Date: 2021-10-14 11:22:01.124488

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = "1a89721126f7"
down_revision = "fa96dfc8237d"
branch_labels = None
depends_on = None


def upgrade():
    op.execute(
        """
        WITH validation_duplicates AS (
            SELECT
                id,
                ROW_NUMBER() OVER (PARTITION BY user_id, mission_id, submitter_id ORDER BY reception_time DESC) AS rn
            FROM mission_validation
        )
        DELETE FROM mission_validation mv
        USING validation_duplicates vd
        WHERE mv.id = vd.id AND vd.rn >= 2
        """
    )
    op.execute(
        """
        ALTER TABLE mission_validation ADD CONSTRAINT only_one_validation_per_submitter_mission_and_user
        EXCLUDE USING GIST (
            mission_id WITH =,
            submitter_id WITH =,
            user_id WITH =
        )
        """
    )


def downgrade():
    op.drop_constraint(
        "only_one_validation_per_submitter_mission_and_user",
        "mission_validation",
    )

[((20, 4, 32, 5), 'alembic.op.execute', 'op.execute', ({(21, 8, 31, 11): '"""\n        WITH validation_duplicates AS (\n            SELECT\n                id,\n                ROW_NUMBER() OVER (PARTITION BY user_id, mission_id, submitter_id ORDER BY reception_time DESC) AS rn\n            FROM mission_validation\n        )\n        DELETE FROM mission_validation mv\n        USING validation_duplicates vd\n        WHERE mv.id = vd.id AND vd.rn >= 2\n        """'}, {}), '(\n    """\n        WITH validation_duplicates AS (\n            SELECT\n                id,\n                ROW_NUMBER() OVER (PARTITION BY user_id, mission_id, submitter_id ORDER BY reception_time DESC) AS rn\n            FROM mission_validation\n        )\n        DELETE FROM mission_validation mv\n        USING validation_duplicates vd\n        WHERE mv.id = vd.id AND vd.rn >= 2\n        """\n    )', False, 'from alembic import op\n'), ((33, 4, 42, 5), 'alembic.op.execute', 'op.execute', ({(34, 8, 41, 11): '"""\n        ALTER TABLE mission_validation ADD CONSTRAINT only_one_validation_per_submitter_mission_and_user\n        EXCLUDE USING GIST (\n            mission_id WITH =,\n            submitter_id WITH =,\n            user_id WITH =\n        )\n        """'}, {}), '(\n    """\n        ALTER TABLE mission_validation ADD CONSTRAINT only_one_validation_per_submitter_mission_and_user\n        EXCLUDE USING GIST (\n            mission_id WITH =,\n            submitter_id WITH =,\n            user_id WITH =\n        )\n        """\n    )', False, 'from alembic import op\n'), ((46, 4, 49, 5), 'alembic.op.drop_constraint', 'op.drop_constraint', ({(47, 8, 47, 60): '"""only_one_validation_per_submitter_mission_and_user"""', (48, 8, 48, 28): '"""mission_validation"""'}, {}), "('only_one_validation_per_submitter_mission_and_user',\n    'mission_validation')", False, 'from alembic import op\n')]