# pylint: disable=invalid-name
"""Initial

Revision ID: b0b160c2f187
Revises:
Create Date: 2020-07-07 21:54:00.388624+00:00

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = "b0b160c2f187"
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        "file",
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.Column("id", sa.String(length=200), nullable=False),
        sa.Column("name", sa.String(length=250), nullable=False),
        sa.Column("contents", sa.Text(), nullable=False),
        sa.Column("uploaded_at", sa.DateTime(), nullable=False),
        sa.Column("processing_started_at", sa.DateTime(), nullable=True),
        sa.Column("processing_completed_at", sa.DateTime(), nullable=True),
        sa.Column("processing_error", sa.Text(), nullable=True),
        sa.PrimaryKeyConstraint("id", name=op.f("file_pkey")),
    )
    op.create_table(
        "organization",
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.Column("id", sa.String(length=200), nullable=False),
        sa.Column("name", sa.String(length=200), nullable=False),
        sa.PrimaryKeyConstraint("id", name=op.f("organization_pkey")),
    )
    op.create_table(
        "user",
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.Column("id", sa.String(length=200), nullable=False),
        sa.Column("email", sa.String(length=200), nullable=False),
        sa.Column("external_id", sa.String(length=200), nullable=True),
        sa.PrimaryKeyConstraint("id", name=op.f("user_pkey")),
        sa.UniqueConstraint("email", name=op.f("user_email_key")),
        sa.UniqueConstraint("external_id", name=op.f("user_external_id_key")),
    )
    op.create_table(
        "audit_administration",
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.Column("organization_id", sa.String(length=200), nullable=False),
        sa.Column("user_id", sa.String(length=200), nullable=False),
        sa.ForeignKeyConstraint(
            ["organization_id"],
            ["organization.id"],
            name=op.f("audit_administration_organization_id_fkey"),
            ondelete="cascade",
        ),
        sa.ForeignKeyConstraint(
            ["user_id"],
            ["user.id"],
            name=op.f("audit_administration_user_id_fkey"),
            ondelete="cascade",
        ),
        sa.PrimaryKeyConstraint(
            "organization_id", "user_id", name=op.f("audit_administration_pkey")
        ),
    )
    op.create_table(
        "election",
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.Column("id", sa.String(length=200), nullable=False),
        sa.Column("audit_name", sa.String(length=200), nullable=False),
        sa.Column("election_name", sa.String(length=200), nullable=True),
        sa.Column("state", sa.String(length=100), nullable=True),
        sa.Column("election_date", sa.Date(), nullable=True),
        sa.Column("election_type", sa.String(length=200), nullable=True),
        sa.Column("meeting_date", sa.Date(), nullable=True),
        sa.Column("risk_limit", sa.Integer(), nullable=True),
        sa.Column("random_seed", sa.String(length=100), nullable=True),
        sa.Column("online", sa.Boolean(), nullable=False),
        sa.Column("is_multi_jurisdiction", sa.Boolean(), nullable=False),
        sa.Column("organization_id", sa.String(length=200), nullable=True),
        sa.Column("frozen_at", sa.DateTime(), nullable=True),
        sa.Column("jurisdictions_file_id", sa.String(length=200), nullable=True),
        sa.ForeignKeyConstraint(
            ["jurisdictions_file_id"],
            ["file.id"],
            name=op.f("election_jurisdictions_file_id_fkey"),
            ondelete="set null",
        ),
        sa.ForeignKeyConstraint(
            ["organization_id"],
            ["organization.id"],
            name=op.f("election_organization_id_fkey"),
            ondelete="cascade",
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("election_pkey")),
        sa.UniqueConstraint(
            "organization_id",
            "audit_name",
            name=op.f("election_organization_id_audit_name_key"),
        ),
    )
    op.create_table(
        "contest",
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.Column("id", sa.String(length=200), nullable=False),
        sa.Column("election_id", sa.String(length=200), nullable=False),
        sa.Column("name", sa.String(length=200), nullable=False),
        sa.Column("is_targeted", sa.Boolean(), nullable=False),
        sa.Column("total_ballots_cast", sa.Integer(), nullable=False),
        sa.Column("num_winners", sa.Integer(), nullable=False),
        sa.Column("votes_allowed", sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(
            ["election_id"],
            ["election.id"],
            name=op.f("contest_election_id_fkey"),
            ondelete="cascade",
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("contest_pkey")),
    )
    op.create_table(
        "jurisdiction",
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.Column("id", sa.String(length=200), nullable=False),
        sa.Column("election_id", sa.String(length=200), nullable=False),
        sa.Column("name", sa.String(length=200), nullable=False),
        sa.Column("manifest_num_ballots", sa.Integer(), nullable=True),
        sa.Column("manifest_num_batches", sa.Integer(), nullable=True),
        sa.Column("manifest_file_id", sa.String(length=200), nullable=True),
        sa.ForeignKeyConstraint(
            ["election_id"],
            ["election.id"],
            name=op.f("jurisdiction_election_id_fkey"),
            ondelete="cascade",
        ),
        sa.ForeignKeyConstraint(
            ["manifest_file_id"],
            ["file.id"],
            name=op.f("jurisdiction_manifest_file_id_fkey"),
            ondelete="set null",
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("jurisdiction_pkey")),
        sa.UniqueConstraint(
            "election_id", "name", name=op.f("jurisdiction_election_id_name_key")
        ),
    )
    op.create_table(
        "round",
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.Column("id", sa.String(length=200), nullable=False),
        sa.Column("election_id", sa.String(length=200), nullable=False),
        sa.Column("round_num", sa.Integer(), nullable=False),
        sa.Column("ended_at", sa.DateTime(), nullable=True),
        sa.ForeignKeyConstraint(
            ["election_id"],
            ["election.id"],
            name=op.f("round_election_id_fkey"),
            ondelete="cascade",
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("round_pkey")),
        sa.UniqueConstraint(
            "election_id", "round_num", name=op.f("round_election_id_round_num_key")
        ),
    )
    op.create_table(
        "audit_board",
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.Column("id", sa.String(length=200), nullable=False),
        sa.Column("jurisdiction_id", sa.String(length=200), nullable=False),
        sa.Column("round_id", sa.String(length=200), nullable=True),
        sa.Column("name", sa.String(length=200), nullable=True),
        sa.Column("member_1", sa.String(length=200), nullable=True),
        sa.Column(
            "member_1_affiliation",
            sa.Enum(
                "DEMOCRAT",
                "REPUBLICAN",
                "LIBERTARIAN",
                "INDEPENDENT",
                "OTHER",
                name="affiliation",
            ),
            nullable=True,
        ),
        sa.Column("member_2", sa.String(length=200), nullable=True),
        sa.Column(
            "member_2_affiliation",
            sa.Enum(
                "DEMOCRAT",
                "REPUBLICAN",
                "LIBERTARIAN",
                "INDEPENDENT",
                "OTHER",
                name="affiliation",
            ),
            nullable=True,
        ),
        sa.Column("passphrase", sa.String(length=1000), nullable=True),
        sa.Column("signed_off_at", sa.DateTime(), nullable=True),
        sa.ForeignKeyConstraint(
            ["jurisdiction_id"],
            ["jurisdiction.id"],
            name=op.f("audit_board_jurisdiction_id_fkey"),
            ondelete="cascade",
        ),
        sa.ForeignKeyConstraint(
            ["round_id"],
            ["round.id"],
            name=op.f("audit_board_round_id_fkey"),
            ondelete="cascade",
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("audit_board_pkey")),
        sa.UniqueConstraint(
            "jurisdiction_id",
            "round_id",
            "name",
            name=op.f("audit_board_jurisdiction_id_round_id_name_key"),
        ),
        sa.UniqueConstraint("passphrase", name=op.f("audit_board_passphrase_key")),
    )
    op.create_table(
        "batch",
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.Column("id", sa.String(length=200), nullable=False),
        sa.Column("jurisdiction_id", sa.String(length=200), nullable=False),
        sa.Column("name", sa.String(length=200), nullable=False),
        sa.Column("num_ballots", sa.Integer(), nullable=False),
        sa.Column("storage_location", sa.String(length=200), nullable=True),
        sa.Column("tabulator", sa.String(length=200), nullable=True),
        sa.ForeignKeyConstraint(
            ["jurisdiction_id"],
            ["jurisdiction.id"],
            name=op.f("batch_jurisdiction_id_fkey"),
            ondelete="cascade",
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("batch_pkey")),
        sa.UniqueConstraint(
            "jurisdiction_id", "name", name=op.f("batch_jurisdiction_id_name_key")
        ),
    )
    op.create_table(
        "contest_choice",
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.Column("id", sa.String(length=200), nullable=False),
        sa.Column("contest_id", sa.String(length=200), nullable=False),
        sa.Column("name", sa.String(length=200), nullable=False),
        sa.Column("num_votes", sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(
            ["contest_id"],
            ["contest.id"],
            name=op.f("contest_choice_contest_id_fkey"),
            ondelete="cascade",
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("contest_choice_pkey")),
    )
    op.create_table(
        "contest_jurisdiction",
        sa.Column("contest_id", sa.String(length=200), nullable=False),
        sa.Column("jurisdiction_id", sa.String(length=200), nullable=False),
        sa.ForeignKeyConstraint(
            ["contest_id"],
            ["contest.id"],
            name=op.f("contest_jurisdiction_contest_id_fkey"),
            ondelete="cascade",
        ),
        sa.ForeignKeyConstraint(
            ["jurisdiction_id"],
            ["jurisdiction.id"],
            name=op.f("contest_jurisdiction_jurisdiction_id_fkey"),
            ondelete="cascade",
        ),
    )
    op.create_table(
        "jurisdiction_administration",
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.Column("user_id", sa.String(length=200), nullable=False),
        sa.Column("jurisdiction_id", sa.String(length=200), nullable=False),
        sa.ForeignKeyConstraint(
            ["jurisdiction_id"],
            ["jurisdiction.id"],
            name=op.f("jurisdiction_administration_jurisdiction_id_fkey"),
            ondelete="cascade",
        ),
        sa.ForeignKeyConstraint(
            ["user_id"],
            ["user.id"],
            name=op.f("jurisdiction_administration_user_id_fkey"),
            ondelete="cascade",
        ),
        sa.PrimaryKeyConstraint(
            "user_id", "jurisdiction_id", name=op.f("jurisdiction_administration_pkey")
        ),
    )
    op.create_table(
        "round_contest",
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.Column("round_id", sa.String(length=200), nullable=False),
        sa.Column("contest_id", sa.String(length=200), nullable=False),
        sa.Column("sample_size_options", sa.String(length=1000), nullable=True),
        sa.Column("end_p_value", sa.Float(), nullable=True),
        sa.Column("is_complete", sa.Boolean(), nullable=True),
        sa.Column("sample_size", sa.Integer(), nullable=True),
        sa.ForeignKeyConstraint(
            ["contest_id"],
            ["contest.id"],
            name=op.f("round_contest_contest_id_fkey"),
            ondelete="cascade",
        ),
        sa.ForeignKeyConstraint(
            ["round_id"],
            ["round.id"],
            name=op.f("round_contest_round_id_fkey"),
            ondelete="cascade",
        ),
        sa.PrimaryKeyConstraint(
            "round_id", "contest_id", name=op.f("round_contest_pkey")
        ),
    )
    op.create_table(
        "round_contest_result",
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.Column("round_id", sa.String(length=200), nullable=False),
        sa.Column("contest_id", sa.String(length=200), nullable=False),
        sa.Column("contest_choice_id", sa.String(length=200), nullable=False),
        sa.Column("result", sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(
            ["contest_choice_id"],
            ["contest_choice.id"],
            name=op.f("round_contest_result_contest_choice_id_fkey"),
            ondelete="cascade",
        ),
        sa.ForeignKeyConstraint(
            ["contest_id"],
            ["contest.id"],
            name=op.f("round_contest_result_contest_id_fkey"),
            ondelete="cascade",
        ),
        sa.ForeignKeyConstraint(
            ["round_id", "contest_id"],
            ["round_contest.round_id", "round_contest.contest_id"],
            name=op.f("round_contest_result_round_id_contest_id_fkey"),
            ondelete="cascade",
        ),
        sa.ForeignKeyConstraint(
            ["round_id"],
            ["round.id"],
            name=op.f("round_contest_result_round_id_fkey"),
            ondelete="cascade",
        ),
        sa.PrimaryKeyConstraint(
            "round_id", "contest_choice_id", name=op.f("round_contest_result_pkey")
        ),
    )
    op.create_table(
        "sampled_ballot",
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.Column("id", sa.String(length=200), nullable=False),
        sa.Column("batch_id", sa.String(length=200), nullable=False),
        sa.Column("ballot_position", sa.Integer(), nullable=False),
        sa.Column("audit_board_id", sa.String(length=200), nullable=True),
        sa.Column(
            "status",
            sa.Enum("NOT_AUDITED", "AUDITED", "NOT_FOUND", name="ballotstatus"),
            nullable=False,
        ),
        sa.ForeignKeyConstraint(
            ["audit_board_id"],
            ["audit_board.id"],
            name=op.f("sampled_ballot_audit_board_id_fkey"),
            ondelete="cascade",
        ),
        sa.ForeignKeyConstraint(
            ["batch_id"],
            ["batch.id"],
            name=op.f("sampled_ballot_batch_id_fkey"),
            ondelete="cascade",
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("sampled_ballot_pkey")),
        sa.UniqueConstraint(
            "batch_id",
            "ballot_position",
            name=op.f("sampled_ballot_batch_id_ballot_position_key"),
        ),
    )
    op.create_table(
        "ballot_interpretation",
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.Column("ballot_id", sa.String(length=200), nullable=False),
        sa.Column("contest_id", sa.String(length=200), nullable=False),
        sa.Column(
            "interpretation",
            sa.Enum("BLANK", "CANT_AGREE", "VOTE", name="interpretation"),
            nullable=False,
        ),
        sa.Column("comment", sa.Text(), nullable=True),
        sa.Column("contest_choice_id", sa.String(length=200), nullable=True,),
        sa.ForeignKeyConstraint(
            ["ballot_id"],
            ["sampled_ballot.id"],
            name=op.f("ballot_interpretation_ballot_id_fkey"),
            ondelete="cascade",
        ),
        sa.ForeignKeyConstraint(
            ["contest_id"],
            ["contest.id"],
            name=op.f("ballot_interpretation_contest_id_fkey"),
            ondelete="cascade",
        ),
        sa.ForeignKeyConstraint(
            ["contest_choice_id"],
            ["contest_choice.id"],
            name=op.f("ballot_interpretation_contest_choice_id_fkey"),
            ondelete="cascade",
        ),
        sa.PrimaryKeyConstraint(
            "ballot_id", "contest_id", name=op.f("ballot_interpretation_pkey")
        ),
    )
    op.create_table(
        "sampled_ballot_draw",
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.Column("ballot_id", sa.String(length=200), nullable=False),
        sa.Column("round_id", sa.String(length=200), nullable=False),
        sa.Column("ticket_number", sa.String(length=200), nullable=False),
        sa.ForeignKeyConstraint(
            ["ballot_id"],
            ["sampled_ballot.id"],
            name=op.f("sampled_ballot_draw_ballot_id_fkey"),
            ondelete="cascade",
        ),
        sa.ForeignKeyConstraint(
            ["round_id"],
            ["round.id"],
            name=op.f("sampled_ballot_draw_round_id_fkey"),
            ondelete="cascade",
        ),
        sa.PrimaryKeyConstraint(
            "ballot_id",
            "round_id",
            "ticket_number",
            name=op.f("sampled_ballot_draw_pkey"),
        ),
    )
    # ### end Alembic commands ###


def downgrade():
    pass  # pragma: no cover
    # ### commands auto generated by Alembic - please adjust! ###
    # op.drop_table("ballot_interpretation_contest_choice")
    # op.drop_table("sampled_ballot_draw")
    # op.drop_table("ballot_interpretation")
    # op.drop_table("sampled_ballot")
    # op.drop_table("round_contest_result")
    # op.drop_table("round_contest")
    # op.drop_table("jurisdiction_administration")
    # op.drop_table("contest_jurisdiction")
    # op.drop_table("contest_choice")
    # op.drop_table("batch")
    # op.drop_table("audit_board")
    # op.drop_table("round")
    # op.drop_table("jurisdiction")
    # op.drop_table("contest")
    # op.drop_table("election")
    # op.drop_table("audit_administration")
    # op.drop_table("user")
    # op.drop_table("organization")
    # op.drop_table("file")
    # ### end Alembic commands ###
