"""initial migration to postgres

Revision ID: dbcc4e3ff1e0
Revises: 
Create Date: 2022-10-31 00:06:59.633944

"""
import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects import postgresql

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


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.execute(
        """
        CREATE AGGREGATE jsonb_merge(jsonb) (
            SFUNC = 'jsonb_concat',
            STYPE = jsonb,
            INITCOND = '{}'
        )"""
    )

    op.create_table(
        "aggregate_elements",
        sa.Column("item_hash", sa.String(), nullable=False),
        sa.Column("key", sa.String(), nullable=False),
        sa.Column("owner", sa.String(), nullable=False),
        sa.Column("content", postgresql.JSONB(astext_type=sa.Text()), nullable=False),
        sa.Column("creation_datetime", sa.TIMESTAMP(timezone=True), nullable=False),
        sa.PrimaryKeyConstraint("item_hash"),
    )
    op.create_index(
        "ix_key_owner", "aggregate_elements", ["key", "owner"], unique=False
    )
    op.create_index(
        "ix_time_desc",
        "aggregate_elements",
        [sa.text("creation_datetime DESC")],
        unique=False,
    )
    op.create_table(
        "chain_txs",
        sa.Column("hash", sa.String(), nullable=False),
        sa.Column("chain", sa.String(), nullable=False),
        sa.Column("height", sa.Integer(), nullable=False),
        sa.Column("datetime", sa.TIMESTAMP(timezone=True), nullable=False),
        sa.Column("publisher", sa.String(), nullable=False),
        sa.Column("protocol", sa.String(), nullable=False),
        sa.Column("protocol_version", sa.Integer(), nullable=False),
        sa.Column("content", postgresql.JSONB(astext_type=sa.Text()), nullable=False),
        sa.PrimaryKeyConstraint("hash"),
    )
    op.create_table(
        "chains_sync_status",
        sa.Column("chain", sa.String(), nullable=False),
        sa.Column("height", sa.Integer(), nullable=False),
        sa.Column("last_update", sa.TIMESTAMP(timezone=True), nullable=False),
        sa.PrimaryKeyConstraint("chain"),
    )
    op.create_table(
        "files",
        sa.Column("hash", sa.String(), nullable=False),
        sa.Column("size", sa.BigInteger(), nullable=True),
        sa.Column("type", sa.String(), nullable=False),
        sa.PrimaryKeyConstraint("hash"),
    )
    op.create_table(
        "file_pins",
        sa.Column("id", sa.BigInteger(), nullable=False),
        sa.Column("file_hash", sa.String(), nullable=False),
        sa.Column("created", sa.TIMESTAMP(timezone=True), nullable=False),
        sa.Column("type", sa.String(), nullable=False),
        sa.Column("tx_hash", sa.String(), nullable=True),
        sa.Column("owner", sa.String(), nullable=True),
        sa.Column("item_hash", sa.String(), nullable=True),
        sa.Column("ref", sa.String(), nullable=True),
        sa.ForeignKeyConstraint(
            ["file_hash"],
            ["files.hash"],
        ),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("item_hash"),
    )
    op.create_table(
        "forgotten_messages",
        sa.Column("item_hash", sa.String(), nullable=False),
        sa.Column("type", sa.String(), nullable=False),
        sa.Column("chain", sa.String(), nullable=False),
        sa.Column("sender", sa.String(), nullable=False),
        sa.Column("signature", sa.String(), nullable=False),
        sa.Column("item_type", sa.String(), nullable=False),
        sa.Column("time", sa.TIMESTAMP(timezone=True), nullable=False),
        sa.Column("channel", sa.String(), nullable=True),
        sa.Column("forgotten_by", sa.ARRAY(sa.String()), nullable=False),
        sa.PrimaryKeyConstraint("item_hash"),
    )
    op.create_index(
        op.f("ix_forgotten_messages_channel"),
        "forgotten_messages",
        ["channel"],
        unique=False,
    )
    op.create_index(
        op.f("ix_forgotten_messages_sender"),
        "forgotten_messages",
        ["sender"],
        unique=False,
    )
    op.create_index(
        op.f("ix_forgotten_messages_time"), "forgotten_messages", ["time"], unique=False
    )
    op.create_table(
        "message_status",
        sa.Column("item_hash", sa.String(), nullable=False),
        sa.Column("status", sa.String(), nullable=False),
        sa.Column("reception_time", sa.TIMESTAMP(timezone=True), nullable=False),
        sa.PrimaryKeyConstraint("item_hash"),
    )
    op.create_table(
        "messages",
        sa.Column("item_hash", sa.String(), nullable=False),
        sa.Column("type", sa.String(), nullable=False),
        sa.Column("chain", sa.String(), nullable=False),
        sa.Column("sender", sa.String(), nullable=False),
        sa.Column("signature", sa.String(), nullable=False),
        sa.Column("item_type", sa.String(), nullable=False),
        sa.Column("item_content", sa.String(), nullable=True),
        sa.Column("content", postgresql.JSONB(astext_type=sa.Text()), nullable=False),
        sa.Column("time", sa.TIMESTAMP(timezone=True), nullable=False),
        sa.Column("channel", sa.String(), nullable=True),
        sa.Column("size", sa.Integer(), nullable=False),
        sa.PrimaryKeyConstraint("item_hash"),
    )
    op.create_index(op.f("ix_messages_channel"), "messages", ["channel"], unique=False)
    op.create_index(op.f("ix_messages_sender"), "messages", ["sender"], unique=False)
    op.create_index(op.f("ix_messages_time"), "messages", ["time"], unique=False)
    op.create_table(
        "peers",
        sa.Column("peer_id", sa.String(), nullable=False),
        sa.Column("peer_type", sa.String(), nullable=False),
        sa.Column("address", sa.String(), nullable=False),
        sa.Column("source", sa.String(), nullable=False),
        sa.Column("last_seen", sa.TIMESTAMP(timezone=True), nullable=False),
        sa.PrimaryKeyConstraint("peer_id", "peer_type"),
    )
    op.create_table(
        "posts",
        sa.Column("item_hash", sa.String(), nullable=False),
        sa.Column("owner", sa.String(), nullable=False),
        sa.Column("type", sa.String(), nullable=True),
        sa.Column("ref", sa.String(), nullable=True),
        sa.Column("amends", sa.String(), nullable=True),
        sa.Column("channel", sa.String(), nullable=True),
        sa.Column("content", postgresql.JSONB(astext_type=sa.Text()), nullable=False),
        sa.Column("creation_datetime", sa.TIMESTAMP(timezone=True), nullable=False),
        sa.Column("latest_amend", sa.String(), nullable=True),
        sa.ForeignKeyConstraint(
            ["amends"],
            ["posts.item_hash"],
        ),
        sa.PrimaryKeyConstraint("item_hash"),
    )
    op.create_index(op.f("ix_posts_amends"), "posts", ["amends"], unique=False)
    op.create_index(op.f("ix_posts_owner"), "posts", ["owner"], unique=False)
    op.create_index(op.f("ix_posts_type"), "posts", ["type"], unique=False)
    op.create_table(
        "rejected_messages",
        sa.Column("item_hash", sa.String(), nullable=False),
        sa.Column("message", postgresql.JSONB(astext_type=sa.Text()), nullable=False),
        sa.Column("reason", sa.String(), nullable=False),
        sa.Column("traceback", sa.String(), nullable=True),
        sa.PrimaryKeyConstraint("item_hash"),
    )
    op.create_table(
        "aggregates",
        sa.Column("key", sa.String(), nullable=False),
        sa.Column("owner", sa.String(), nullable=False),
        sa.Column("content", postgresql.JSONB(astext_type=sa.Text()), nullable=False),
        sa.Column("creation_datetime", sa.TIMESTAMP(timezone=True), nullable=False),
        sa.Column("last_revision_hash", sa.String(), nullable=False),
        sa.Column("dirty", sa.Boolean(), nullable=False),
        sa.ForeignKeyConstraint(
            ["last_revision_hash"],
            ["aggregate_elements.item_hash"],
        ),
        sa.PrimaryKeyConstraint("key", "owner"),
    )
    op.create_index("ix_aggregates_owner", "aggregates", ["owner"], unique=False)
    op.create_table(
        "file_tags",
        sa.Column("tag", sa.String(), nullable=False),
        sa.Column("owner", sa.String(), nullable=False),
        sa.Column("file_hash", sa.String(), nullable=False),
        sa.Column("last_updated", sa.TIMESTAMP(timezone=True), nullable=False),
        sa.ForeignKeyConstraint(
            ["file_hash"],
            ["files.hash"],
        ),
        sa.PrimaryKeyConstraint("tag"),
    )
    op.create_index(
        op.f("ix_file_tags_file_hash"),
        "file_tags",
        ["file_hash"],
        unique=False,
    )
    op.create_table(
        "message_confirmations",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("item_hash", sa.String(), nullable=False),
        sa.Column("tx_hash", sa.String(), nullable=False),
        sa.ForeignKeyConstraint(
            ["item_hash"],
            ["messages.item_hash"],
        ),
        sa.ForeignKeyConstraint(["tx_hash"], ["chain_txs.hash"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("item_hash", "tx_hash"),
    )
    op.create_index(
        op.f("ix_message_confirmations_item_hash"),
        "message_confirmations",
        ["item_hash"],
        unique=False,
    )
    op.create_table(
        "pending_messages",
        sa.Column("id", sa.BigInteger(), nullable=False),
        sa.Column("item_hash", sa.String(), nullable=False),
        sa.Column("type", sa.String(), nullable=False),
        sa.Column("chain", sa.String(), nullable=False),
        sa.Column("sender", sa.String(), nullable=False),
        sa.Column("signature", sa.String(), nullable=False),
        sa.Column("item_type", sa.String(), nullable=False),
        sa.Column("item_content", sa.String(), nullable=True),
        sa.Column("content", postgresql.JSONB(astext_type=sa.Text()), nullable=True),
        sa.Column("time", sa.TIMESTAMP(timezone=True), nullable=False),
        sa.Column("channel", sa.String(), nullable=True),
        sa.Column("reception_time", sa.TIMESTAMP(timezone=True), nullable=False),
        sa.Column("check_message", sa.Boolean(), nullable=False),
        sa.Column("retries", sa.Integer(), nullable=False),
        sa.Column("tx_hash", sa.String(), nullable=True),
        sa.Column("fetched", sa.Boolean, nullable=False),
        sa.ForeignKeyConstraint(
            ["tx_hash"],
            ["chain_txs.hash"],
        ),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(
        "ix_retries_time",
        "pending_messages",
        [sa.text("retries ASC"), sa.text("time ASC")],
        unique=False,
    )
    op.create_table(
        "pending_txs",
        sa.Column("tx_hash", sa.String(), nullable=False),
        sa.ForeignKeyConstraint(
            ["tx_hash"],
            ["chain_txs.hash"],
        ),
        sa.PrimaryKeyConstraint("tx_hash"),
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    op.execute("DROP AGGREGATE jsonb_merge(jsonb)")

    op.drop_table("pending_txs")
    op.drop_index("ix_retries_time", table_name="pending_messages")
    op.drop_table("pending_messages")
    op.drop_index(
        op.f("ix_message_confirmations_item_hash"), table_name="message_confirmations"
    )
    op.drop_table("message_confirmations")
    op.drop_index(op.f("ix_file_tags_file_hash"), table_name="file_tags")
    op.drop_table("file_tags")
    op.drop_index("ix_aggregates_owner", table_name="aggregates")
    op.drop_table("aggregates")
    op.drop_table("rejected_messages")
    op.drop_index(op.f("ix_posts_type"), table_name="posts")
    op.drop_index(op.f("ix_posts_owner"), table_name="posts")
    op.drop_index(op.f("ix_posts_amends"), table_name="posts")
    op.drop_table("posts")
    op.drop_table("peers")
    op.drop_index(op.f("ix_messages_time"), table_name="messages")
    op.drop_index(op.f("ix_messages_sender"), table_name="messages")
    op.drop_index(op.f("ix_messages_channel"), table_name="messages")
    op.drop_table("messages")
    op.drop_table("message_status")
    op.drop_index(op.f("ix_forgotten_messages_time"), table_name="forgotten_messages")
    op.drop_index(op.f("ix_forgotten_messages_sender"), table_name="forgotten_messages")
    op.drop_index(
        op.f("ix_forgotten_messages_channel"), table_name="forgotten_messages"
    )
    op.drop_table("forgotten_messages")
    op.drop_table("file_pins")
    op.drop_table("files")
    op.drop_table("chains_sync_status")
    op.drop_table("chain_txs")
    op.drop_index("ix_time_desc", table_name="aggregate_elements")
    op.drop_index("ix_key_owner", table_name="aggregate_elements")
    op.drop_table("aggregate_elements")
    # ### end Alembic commands ###
