# pylint: disable=invalid-name
"""SampledBallotDraw.contest_id

Revision ID: 5b7164faeda5
Revises: fa342e75506b
Create Date: 2020-07-07 18:02:22.237055+00:00

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = "5b7164faeda5"
down_revision = "fa342e75506b"
branch_labels = None
depends_on = None


def upgrade():
    # Add SampledBallotDraw.contest_id
    op.add_column("sampled_ballot_draw", sa.Column("contest_id", sa.String(length=200)))
    op.create_foreign_key(
        op.f("sampled_ballot_draw_contest_id_fkey"),
        "sampled_ballot_draw",
        "contest",
        ["contest_id"],
        ["id"],
        ondelete="cascade",
    )

    # Fill in the targeted contest id for existing SampledBallotDraws
    op.execute(
        """
        UPDATE sampled_ballot_draw
        SET contest_id = c.id
        FROM (
            SELECT distinct(election_id), id
            FROM contest
            WHERE contest.is_targeted
        ) c
        JOIN election ON election.id = c.election_id
        JOIN round ON election.id = round.election_id
        WHERE round.id = sampled_ballot_draw.round_id
        """
    )

    # Make it required
    op.alter_column("sampled_ballot_draw", "contest_id", nullable=False)

    # Update the primary key
    op.drop_constraint(op.f("sampled_ballot_draw_pkey"), "sampled_ballot_draw")
    op.create_primary_key(
        op.f("sampled_ballot_draw_pkey"),
        "sampled_ballot_draw",
        ["ballot_id", "round_id", "contest_id", "ticket_number"],
    )


def downgrade():  # pragma: no cover
    pass
    # # ### commands auto generated by Alembic - please adjust! ###
    # op.drop_constraint(op.f('sampled_ballot_draw_contest_id_fkey'), 'sampled_ballot_draw', type_='foreignkey')
    # op.drop_column('sampled_ballot_draw', 'contest_id')
    # ### end Alembic commands ###
