client/alembic/versions/d7c8af95bc8e_initial.py

"""Initial schema, squashed from old head 9ba8d7524871 (per #1500).

Revision ID: d7c8af95bc8e
Revises:
Create Date: 2022-06-09 16:41:11.913336

"""

import sqlalchemy as sa
from alembic import op

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


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        "sources",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("uuid", sa.String(length=36), nullable=False),
        sa.Column("journalist_designation", sa.String(length=255), nullable=False),
        sa.Column("document_count", sa.Integer(), server_default=sa.text("0"), nullable=False),
        sa.Column(
            "is_flagged", sa.Boolean(name="is_flagged"), server_default=sa.text("0"), nullable=True
        ),
        sa.Column("public_key", sa.Text(), nullable=True),
        sa.Column("fingerprint", sa.String(length=64), nullable=True),
        sa.Column("interaction_count", sa.Integer(), server_default=sa.text("0"), nullable=False),
        sa.Column(
            "is_starred", sa.Boolean(name="is_starred"), server_default=sa.text("0"), nullable=True
        ),
        sa.Column("last_updated", sa.DateTime(), nullable=True),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_sources")),
        sa.UniqueConstraint("uuid", name=op.f("uq_sources_uuid")),
    )
    op.create_table(
        "users",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("uuid", sa.String(length=36), nullable=False),
        sa.Column("username", sa.String(length=255), nullable=False),
        sa.Column("firstname", sa.String(length=64), nullable=True),
        sa.Column("lastname", sa.String(length=64), nullable=True),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_users")),
        sa.UniqueConstraint("uuid", name=op.f("uq_users_uuid")),
    )
    op.create_table(
        "replysendstatuses",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("name", sa.String(length=36), nullable=False),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_replysendstatuses")),
        sa.UniqueConstraint("name", name=op.f("uq_replysendstatuses_name")),
    )
    op.create_table(
        "draftreplies",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("uuid", sa.String(length=36), nullable=False),
        sa.Column("timestamp", sa.DateTime(), nullable=False),
        sa.Column("source_id", sa.Integer(), nullable=False),
        sa.Column("journalist_id", sa.Integer(), nullable=True),
        sa.Column("file_counter", sa.Integer(), nullable=False),
        sa.Column("content", sa.Text(), nullable=True),
        sa.Column("send_status_id", sa.Integer(), nullable=True),
        sa.UniqueConstraint("uuid", name=op.f("uq_draftreplies_uuid")),
        sa.ForeignKeyConstraint(
            ["source_id"], ["sources.id"], name=op.f("fk_draftreplies_source_id_sources")
        ),
        sa.ForeignKeyConstraint(
            ["journalist_id"], ["users.id"], name=op.f("fk_draftreplies_journalist_id_users")
        ),
        sa.ForeignKeyConstraint(
            ["send_status_id"],
            ["replysendstatuses.id"],
            name=op.f("fk_draftreplies_send_status_id_replysendstatuses"),
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_draftreplies")),
    )
    op.create_table(
        "downloaderrors",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("name", sa.String(length=36), nullable=False),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_downloaderrors")),
        sa.UniqueConstraint("name", name=op.f("uq_downloaderrors_name")),
    )
    op.create_table(
        "files",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("uuid", sa.String(length=36), nullable=False),
        sa.Column("filename", sa.String(length=255), nullable=False),
        sa.Column("file_counter", sa.Integer(), nullable=False),
        sa.Column("size", sa.Integer(), nullable=False),
        sa.Column("download_url", sa.String(length=255), nullable=False),
        sa.Column(
            "is_downloaded",
            sa.Boolean(name="is_downloaded"),
            server_default=sa.text("0"),
            nullable=False,
        ),
        sa.Column(
            "is_decrypted",
            sa.Boolean(name="is_decrypted"),
            sa.CheckConstraint(
                "CASE WHEN is_downloaded = 0 THEN is_decrypted IS NULL ELSE 1 END",
                name="files_compare_is_downloaded_vs_is_decrypted",
            ),
            nullable=True,
        ),
        sa.Column("download_error_id", sa.Integer(), nullable=True),
        sa.Column(
            "is_read", sa.Boolean(name="is_read"), server_default=sa.text("0"), nullable=False
        ),
        sa.Column("source_id", sa.Integer(), nullable=False),
        sa.Column("last_updated", sa.DateTime(), nullable=False),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_files")),
        sa.UniqueConstraint("source_id", "file_counter", name="uq_messages_source_id_file_counter"),
        sa.UniqueConstraint("uuid", name=op.f("uq_files_uuid")),
        sa.ForeignKeyConstraint(
            ["download_error_id"],
            ["downloaderrors.id"],
            name=op.f("fk_files_download_error_id_downloaderrors"),
        ),
        sa.ForeignKeyConstraint(
            ["source_id"], ["sources.id"], name=op.f("fk_files_source_id_sources")
        ),
    )
    op.create_table(
        "messages",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("uuid", sa.String(length=36), nullable=False),
        sa.Column("filename", sa.String(length=255), nullable=False),
        sa.Column("file_counter", sa.Integer(), nullable=False),
        sa.Column("size", sa.Integer(), nullable=False),
        sa.Column("download_url", sa.String(length=255), nullable=False),
        sa.Column(
            "is_downloaded",
            sa.Boolean(name="is_downloaded"),
            server_default=sa.text("0"),
            nullable=False,
        ),
        sa.Column(
            "is_decrypted",
            sa.Boolean(name="is_decrypted"),
            sa.CheckConstraint(
                "CASE WHEN is_downloaded = 0 THEN is_decrypted IS NULL ELSE 1 END",
                name="messages_compare_is_downloaded_vs_is_decrypted",
            ),
            nullable=True,
        ),
        sa.Column("download_error_id", sa.Integer(), nullable=True),
        sa.Column(
            "is_read", sa.Boolean(name="is_read"), server_default=sa.text("0"), nullable=False
        ),
        sa.Column(
            "content",
            sa.Text(),
            sa.CheckConstraint(
                "CASE WHEN is_downloaded = 0 THEN content IS NULL ELSE 1 END",
                name="ck_message_compare_download_vs_content",
            ),
            nullable=True,
        ),
        sa.Column("source_id", sa.Integer(), nullable=False),
        sa.Column("last_updated", sa.DateTime(), nullable=False),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_messages")),
        sa.UniqueConstraint("source_id", "file_counter", name="uq_messages_source_id_file_counter"),
        sa.UniqueConstraint("uuid", name=op.f("uq_messages_uuid")),
        sa.ForeignKeyConstraint(
            ["download_error_id"],
            ["downloaderrors.id"],
            name=op.f("fk_messages_download_error_id_downloaderrors"),
        ),
        sa.ForeignKeyConstraint(
            ["source_id"], ["sources.id"], name=op.f("fk_messages_source_id_sources")
        ),
    )
    op.create_table(
        "replies",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("uuid", sa.String(length=36), nullable=False),
        sa.Column("source_id", sa.Integer(), nullable=False),
        sa.Column("filename", sa.String(length=255), nullable=False),
        sa.Column("file_counter", sa.Integer(), nullable=False),
        sa.Column("size", sa.Integer(), nullable=True),
        sa.Column(
            "content",
            sa.Text(),
            sa.CheckConstraint(
                "CASE WHEN is_downloaded = 0 THEN content IS NULL ELSE 1 END",
                name="replies_compare_download_vs_content",
            ),
            nullable=True,
        ),
        sa.Column(
            "is_decrypted",
            sa.Boolean(name="is_decrypted"),
            sa.CheckConstraint(
                "CASE WHEN is_downloaded = 0 THEN is_decrypted IS NULL ELSE 1 END",
                name="replies_compare_is_downloaded_vs_is_decrypted",
            ),
            nullable=True,
        ),
        sa.Column("is_downloaded", sa.Boolean(name="is_downloaded"), nullable=True),
        sa.Column("download_error_id", sa.Integer(), nullable=True),
        sa.Column("journalist_id", sa.Integer(), nullable=True),
        sa.Column("last_updated", sa.DateTime(), nullable=False),
        sa.ForeignKeyConstraint(
            ["download_error_id"],
            ["downloaderrors.id"],
            name=op.f("fk_replies_download_error_id_downloaderrors"),
        ),
        sa.ForeignKeyConstraint(
            ["journalist_id"], ["users.id"], name=op.f("fk_replies_journalist_id_users")
        ),
        sa.ForeignKeyConstraint(
            ["source_id"], ["sources.id"], name=op.f("fk_replies_source_id_sources")
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_replies")),
        sa.UniqueConstraint("source_id", "file_counter", name="uq_messages_source_id_file_counter"),
        sa.UniqueConstraint("uuid", name=op.f("uq_replies_uuid")),
    )
    op.create_table(
        "seen_files",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("file_id", sa.Integer(), nullable=False),
        sa.Column("journalist_id", sa.Integer(), nullable=True),
        sa.ForeignKeyConstraint(
            ["file_id"], ["files.id"], name=op.f("fk_seen_files_file_id_files")
        ),
        sa.ForeignKeyConstraint(
            ["journalist_id"], ["users.id"], name=op.f("fk_seen_files_journalist_id_users")
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_seen_files")),
        sa.UniqueConstraint("file_id", "journalist_id", name=op.f("uq_seen_files_file_id")),
    )
    op.create_table(
        "seen_messages",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("message_id", sa.Integer(), nullable=False),
        sa.Column("journalist_id", sa.Integer(), nullable=True),
        sa.ForeignKeyConstraint(
            ["journalist_id"], ["users.id"], name=op.f("fk_seen_messages_journalist_id_users")
        ),
        sa.ForeignKeyConstraint(
            ["message_id"], ["messages.id"], name=op.f("fk_seen_messages_message_id_messages")
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_seen_messages")),
        sa.UniqueConstraint(
            "message_id", "journalist_id", name=op.f("uq_seen_messages_message_id")
        ),
    )
    op.create_table(
        "seen_replies",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("reply_id", sa.Integer(), nullable=False),
        sa.Column("journalist_id", sa.Integer(), nullable=True),
        sa.ForeignKeyConstraint(
            ["journalist_id"], ["users.id"], name=op.f("fk_seen_replies_journalist_id_users")
        ),
        sa.ForeignKeyConstraint(
            ["reply_id"], ["replies.id"], name=op.f("fk_seen_replies_reply_id_replies")
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_seen_replies")),
        sa.UniqueConstraint("reply_id", "journalist_id", name=op.f("uq_seen_replies_reply_id")),
    )
    op.create_table(
        "deletedconversation",
        sa.Column("uuid", sa.String(length=36), nullable=False),
        sa.PrimaryKeyConstraint("uuid", name=op.f("pk_deletedconversation")),
    )
    op.create_table(
        "deletedsource",
        sa.Column("uuid", sa.String(length=36), nullable=False),
        sa.PrimaryKeyConstraint("uuid", name=op.f("pk_deletedsource")),
    )
    # ### end Alembic commands ###

    # Set enum values:
    conn = op.get_bind()

    # downloaderrors (from original Alembic version 7f682532afa2)
    conn.execute(
        """
        INSERT INTO downloaderrors
        ('name')
        VALUES
        ('CHECKSUM_ERROR'),
        ('DECRYPTION_ERROR');
    """
    )

    # replysendstatuses (from original Alembic version 86b01b6290da)
    conn.execute(
        """
        INSERT INTO replysendstatuses
        ('name')
        VALUES
        ('PENDING'),
        ('FAILED');
    """
    )


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table("seen_replies")
    op.drop_table("seen_messages")
    op.drop_table("seen_files")
    op.drop_table("replies")
    op.drop_table("messages")
    op.drop_table("files")
    op.drop_table("draftreplies")
    op.drop_table("users")
    op.drop_table("sources")
    op.drop_table("replysendstatuses")
    op.drop_table("downloaderrors")
    op.drop_table("deletedsource")
    op.drop_table("deletedconversation")
    # ### end Alembic commands ###