"""name changes: person->user, post->message, work_package->task

Revision ID: abb47e9d145a
Revises: 73ce3675c1f5
Create Date: 2022-12-30 20:54:49.880568

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

# revision identifiers, used by Alembic.
revision = "abb47e9d145a"
down_revision = "73ce3675c1f5"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # clear DB
    op.execute("DELETE FROM journal;")
    op.execute("DELETE FROM work_package;")
    op.execute("DELETE FROM post_reaction;")
    op.execute("DELETE FROM post;")
    op.execute("DELETE FROM person_stats;")
    op.execute("DELETE FROM person;")
    op.execute("DELETE FROM text_labels;")

    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        "user",
        sa.Column("id", postgresql.UUID(as_uuid=True), server_default=sa.text("gen_random_uuid()"), nullable=False),
        sa.Column("created_date", sa.DateTime(), server_default=sa.text("CURRENT_TIMESTAMP"), nullable=False),
        sa.Column("username", sqlmodel.sql.sqltypes.AutoString(length=128), nullable=False),
        sa.Column("auth_method", sqlmodel.sql.sqltypes.AutoString(length=128), nullable=False),
        sa.Column("display_name", sqlmodel.sql.sqltypes.AutoString(length=256), nullable=False),
        sa.Column("api_client_id", sqlmodel.sql.sqltypes.GUID(), nullable=False),
        sa.ForeignKeyConstraint(
            ["api_client_id"],
            ["api_client.id"],
        ),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index("ix_user_username", "user", ["api_client_id", "username", "auth_method"], unique=True)
    op.create_table(
        "message",
        sa.Column("id", postgresql.UUID(as_uuid=True), server_default=sa.text("gen_random_uuid()"), nullable=False),
        sa.Column("created_date", sa.DateTime(), server_default=sa.text("CURRENT_TIMESTAMP"), nullable=False),
        sa.Column("payload", postgresql.JSONB(astext_type=sa.Text()), nullable=True),
        sa.Column("depth", sa.Integer(), server_default=sa.text("0"), nullable=False),
        sa.Column("children_count", sa.Integer(), server_default=sa.text("0"), nullable=False),
        sa.Column("parent_id", sqlmodel.sql.sqltypes.GUID(), nullable=True),
        sa.Column("message_tree_id", sqlmodel.sql.sqltypes.GUID(), nullable=False),
        sa.Column("task_id", sqlmodel.sql.sqltypes.GUID(), nullable=True),
        sa.Column("user_id", sqlmodel.sql.sqltypes.GUID(), nullable=True),
        sa.Column("role", sqlmodel.sql.sqltypes.AutoString(length=128), nullable=False),
        sa.Column("api_client_id", sqlmodel.sql.sqltypes.GUID(), nullable=False),
        sa.Column("frontend_message_id", sqlmodel.sql.sqltypes.AutoString(length=200), nullable=False),
        sa.Column("payload_type", sqlmodel.sql.sqltypes.AutoString(length=200), nullable=False),
        sa.Column("lang", sqlmodel.sql.sqltypes.AutoString(length=200), nullable=False),
        sa.ForeignKeyConstraint(
            ["api_client_id"],
            ["api_client.id"],
        ),
        sa.ForeignKeyConstraint(
            ["user_id"],
            ["user.id"],
        ),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index("ix_message_frontend_message_id", "message", ["api_client_id", "frontend_message_id"], unique=True)
    op.create_index(op.f("ix_message_message_tree_id"), "message", ["message_tree_id"], unique=False)
    op.create_index(op.f("ix_message_task_id"), "message", ["task_id"], unique=False)
    op.create_index(op.f("ix_message_user_id"), "message", ["user_id"], unique=False)
    op.create_table(
        "task",
        sa.Column("id", postgresql.UUID(as_uuid=True), server_default=sa.text("gen_random_uuid()"), nullable=False),
        sa.Column("created_date", sa.DateTime(), server_default=sa.text("CURRENT_TIMESTAMP"), nullable=False),
        sa.Column("expiry_date", sa.DateTime(), nullable=True),
        sa.Column("payload", postgresql.JSONB(astext_type=sa.Text()), nullable=False),
        sa.Column("done", sa.Boolean(), server_default=sa.text("false"), nullable=False),
        sa.Column("collective", sa.Boolean(), server_default=sa.text("false"), nullable=False),
        sa.Column("user_id", sqlmodel.sql.sqltypes.GUID(), nullable=True),
        sa.Column("payload_type", sqlmodel.sql.sqltypes.AutoString(length=200), nullable=False),
        sa.Column("api_client_id", sqlmodel.sql.sqltypes.GUID(), nullable=False),
        sa.Column("ack", sa.Boolean(), nullable=True),
        sa.Column("frontend_message_id", sqlmodel.sql.sqltypes.AutoString(), nullable=True),
        sa.Column("message_tree_id", sqlmodel.sql.sqltypes.GUID(), nullable=True),
        sa.Column("parent_message_id", sqlmodel.sql.sqltypes.GUID(), nullable=True),
        sa.ForeignKeyConstraint(
            ["api_client_id"],
            ["api_client.id"],
        ),
        sa.ForeignKeyConstraint(
            ["user_id"],
            ["user.id"],
        ),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(op.f("ix_task_user_id"), "task", ["user_id"], unique=False)
    op.create_table(
        "user_stats",
        sa.Column("user_id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("modified_date", sa.DateTime(), server_default=sa.text("CURRENT_TIMESTAMP"), nullable=False),
        sa.Column("leader_score", sa.Integer(), nullable=False),
        sa.Column("reactions", sa.Integer(), nullable=False),
        sa.Column("messages", sa.Integer(), nullable=False),
        sa.Column("upvotes", sa.Integer(), nullable=False),
        sa.Column("downvotes", sa.Integer(), nullable=False),
        sa.Column("task_reward", sa.Integer(), nullable=False),
        sa.Column("compare_wins", sa.Integer(), nullable=False),
        sa.Column("compare_losses", sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(
            ["user_id"],
            ["user.id"],
        ),
        sa.PrimaryKeyConstraint("user_id"),
    )
    op.create_table(
        "message_reaction",
        sa.Column("task_id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("user_id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("created_date", sa.DateTime(), server_default=sa.text("CURRENT_TIMESTAMP"), nullable=False),
        sa.Column("payload", postgresql.JSONB(astext_type=sa.Text()), nullable=False),
        sa.Column("payload_type", sqlmodel.sql.sqltypes.AutoString(length=200), nullable=False),
        sa.Column("api_client_id", sqlmodel.sql.sqltypes.GUID(), nullable=False),
        sa.ForeignKeyConstraint(
            ["api_client_id"],
            ["api_client.id"],
        ),
        sa.ForeignKeyConstraint(
            ["task_id"],
            ["task.id"],
        ),
        sa.ForeignKeyConstraint(
            ["user_id"],
            ["user.id"],
        ),
        sa.PrimaryKeyConstraint("task_id", "user_id"),
    )

    op.drop_constraint("text_labels_post_id_fkey", "text_labels", type_="foreignkey")
    op.drop_constraint("journal_post_id_fkey", "journal", type_="foreignkey")
    op.drop_constraint("journal_person_id_fkey", "journal", type_="foreignkey")

    op.drop_table("post_reaction")

    op.drop_index("ix_post_frontend_post_id", table_name="post")
    op.drop_index("ix_post_person_id", table_name="post")
    op.drop_index("ix_post_thread_id", table_name="post")
    op.drop_index("ix_post_workpackage_id", table_name="post")
    op.drop_table("post")

    op.drop_index("ix_work_package_person_id", table_name="work_package")
    op.drop_table("work_package")
    op.drop_table("person_stats")

    op.drop_index("ix_person_username", table_name="person")
    op.drop_table("person")

    op.add_column("journal", sa.Column("user_id", sqlmodel.sql.sqltypes.GUID(), nullable=True))
    op.add_column("journal", sa.Column("message_id", sqlmodel.sql.sqltypes.GUID(), nullable=True))
    op.drop_index("ix_journal_person_id", table_name="journal")
    op.create_index(op.f("ix_journal_user_id"), "journal", ["user_id"], unique=False)

    op.create_foreign_key(None, "journal", "user", ["user_id"], ["id"])
    op.create_foreign_key(None, "journal", "message", ["message_id"], ["id"])
    op.drop_column("journal", "person_id")
    op.drop_column("journal", "post_id")
    op.add_column("text_labels", sa.Column("message_id", postgresql.UUID(as_uuid=True), nullable=True))
    op.create_foreign_key(None, "text_labels", "message", ["message_id"], ["id"])
    op.drop_column("text_labels", "post_id")
    # ### end Alembic commands ###


def downgrade() -> None:
    # clear DB
    op.execute("DELETE FROM journal;")
    op.execute("DELETE FROM message_reaction;")
    op.execute("DELETE FROM task;")
    op.execute("DELETE FROM message;")
    op.execute("DELETE FROM user_stats;")
    op.execute('DELETE FROM "user";')
    op.execute("DELETE FROM text_labels;")

    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column("text_labels", sa.Column("post_id", postgresql.UUID(), autoincrement=False, nullable=True))
    op.drop_constraint("text_labels_message_id_fkey", "text_labels", type_="foreignkey")

    op.drop_column("text_labels", "message_id")
    op.add_column("journal", sa.Column("post_id", postgresql.UUID(), autoincrement=False, nullable=True))
    op.add_column("journal", sa.Column("person_id", postgresql.UUID(), autoincrement=False, nullable=True))
    op.drop_constraint("journal_message_id_fkey", "journal", type_="foreignkey")
    op.drop_constraint("journal_user_id_fkey", "journal", type_="foreignkey")

    op.drop_index(op.f("ix_journal_user_id"), table_name="journal")
    op.create_index("ix_journal_person_id", "journal", ["person_id"], unique=False)
    op.drop_column("journal", "message_id")
    op.drop_column("journal", "user_id")

    op.create_table(
        "person",
        sa.Column(
            "id", postgresql.UUID(), server_default=sa.text("gen_random_uuid()"), autoincrement=False, nullable=False
        ),
        sa.Column("username", sa.VARCHAR(length=128), autoincrement=False, nullable=False),
        sa.Column("display_name", sa.VARCHAR(length=256), autoincrement=False, nullable=False),
        sa.Column(
            "created_date",
            postgresql.TIMESTAMP(),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            autoincrement=False,
            nullable=False,
        ),
        sa.Column("api_client_id", postgresql.UUID(), autoincrement=False, nullable=False),
        sa.Column("auth_method", sa.VARCHAR(length=128), autoincrement=False, nullable=False),
        sa.ForeignKeyConstraint(["api_client_id"], ["api_client.id"], name="person_api_client_id_fkey"),
        sa.PrimaryKeyConstraint("id", name="person_pkey"),
    )
    op.create_table(
        "person_stats",
        sa.Column("person_id", postgresql.UUID(), autoincrement=False, nullable=False),
        sa.Column("leader_score", sa.INTEGER(), autoincrement=False, nullable=False),
        sa.Column(
            "modified_date",
            postgresql.TIMESTAMP(),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            autoincrement=False,
            nullable=False,
        ),
        sa.Column("reactions", sa.INTEGER(), autoincrement=False, nullable=False),
        sa.Column("posts", sa.INTEGER(), autoincrement=False, nullable=False),
        sa.Column("upvotes", sa.INTEGER(), autoincrement=False, nullable=False),
        sa.Column("downvotes", sa.INTEGER(), autoincrement=False, nullable=False),
        sa.Column("work_reward", sa.INTEGER(), autoincrement=False, nullable=False),
        sa.Column("compare_wins", sa.INTEGER(), autoincrement=False, nullable=False),
        sa.Column("compare_losses", sa.INTEGER(), autoincrement=False, nullable=False),
        sa.ForeignKeyConstraint(["person_id"], ["person.id"], name="person_stats_person_id_fkey"),
        sa.PrimaryKeyConstraint("person_id", name="person_stats_pkey"),
    )
    op.create_table(
        "work_package",
        sa.Column(
            "id", postgresql.UUID(), server_default=sa.text("gen_random_uuid()"), autoincrement=False, nullable=False
        ),
        sa.Column(
            "created_date",
            postgresql.TIMESTAMP(),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            autoincrement=False,
            nullable=False,
        ),
        sa.Column("expiry_date", postgresql.TIMESTAMP(), autoincrement=False, nullable=True),
        sa.Column("person_id", postgresql.UUID(), autoincrement=False, nullable=True),
        sa.Column("payload_type", sa.VARCHAR(length=200), autoincrement=False, nullable=False),
        sa.Column("payload", postgresql.JSONB(astext_type=sa.Text()), autoincrement=False, nullable=False),
        sa.Column("api_client_id", postgresql.UUID(), autoincrement=False, nullable=False),
        sa.Column("done", sa.BOOLEAN(), server_default=sa.text("false"), autoincrement=False, nullable=False),
        sa.Column("ack", sa.BOOLEAN(), autoincrement=False, nullable=True),
        sa.Column("frontend_ref_post_id", sa.VARCHAR(), autoincrement=False, nullable=True),
        sa.Column("thread_id", postgresql.UUID(), autoincrement=False, nullable=True),
        sa.Column("parent_post_id", postgresql.UUID(), autoincrement=False, nullable=True),
        sa.Column("collective", sa.BOOLEAN(), server_default=sa.text("false"), autoincrement=False, nullable=False),
        sa.ForeignKeyConstraint(["api_client_id"], ["api_client.id"], name="work_package_api_client_id_fkey"),
        sa.ForeignKeyConstraint(["person_id"], ["person.id"], name="work_package_person_id_fkey"),
        sa.PrimaryKeyConstraint("id", name="work_package_pkey"),
    )
    op.create_index("ix_work_package_person_id", "work_package", ["person_id"], unique=False)
    op.create_table(
        "post",
        sa.Column(
            "id", postgresql.UUID(), server_default=sa.text("gen_random_uuid()"), autoincrement=False, nullable=False
        ),
        sa.Column("parent_id", postgresql.UUID(), autoincrement=False, nullable=True),
        sa.Column("thread_id", postgresql.UUID(), autoincrement=False, nullable=False),
        sa.Column("workpackage_id", postgresql.UUID(), autoincrement=False, nullable=True),
        sa.Column("person_id", postgresql.UUID(), autoincrement=False, nullable=True),
        sa.Column("api_client_id", postgresql.UUID(), autoincrement=False, nullable=False),
        sa.Column("role", sa.VARCHAR(length=128), autoincrement=False, nullable=False),
        sa.Column("frontend_post_id", sa.VARCHAR(length=200), autoincrement=False, nullable=False),
        sa.Column(
            "created_date",
            postgresql.TIMESTAMP(),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            autoincrement=False,
            nullable=False,
        ),
        sa.Column("payload_type", sa.VARCHAR(length=200), autoincrement=False, nullable=False),
        sa.Column("payload", postgresql.JSONB(astext_type=sa.Text()), autoincrement=False, nullable=True),
        sa.Column("depth", sa.INTEGER(), server_default=sa.text("0"), autoincrement=False, nullable=False),
        sa.Column("children_count", sa.INTEGER(), server_default=sa.text("0"), autoincrement=False, nullable=False),
        sa.Column("lang", sa.VARCHAR(length=200), autoincrement=False, nullable=False),
        sa.ForeignKeyConstraint(["api_client_id"], ["api_client.id"], name="post_api_client_id_fkey"),
        sa.ForeignKeyConstraint(["person_id"], ["person.id"], name="post_person_id_fkey"),
        sa.PrimaryKeyConstraint("id", name="post_pkey"),
    )
    op.create_index("ix_post_workpackage_id", "post", ["workpackage_id"], unique=False)
    op.create_index("ix_post_thread_id", "post", ["thread_id"], unique=False)
    op.create_index("ix_post_person_id", "post", ["person_id"], unique=False)
    op.create_index("ix_post_frontend_post_id", "post", ["api_client_id", "frontend_post_id"], unique=False)

    op.create_table(
        "post_reaction",
        sa.Column("person_id", postgresql.UUID(), autoincrement=False, nullable=False),
        sa.Column(
            "created_date",
            postgresql.TIMESTAMP(),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            autoincrement=False,
            nullable=False,
        ),
        sa.Column("payload_type", sa.VARCHAR(length=200), autoincrement=False, nullable=False),
        sa.Column("payload", postgresql.JSONB(astext_type=sa.Text()), autoincrement=False, nullable=False),
        sa.Column("api_client_id", postgresql.UUID(), autoincrement=False, nullable=False),
        sa.Column("work_package_id", postgresql.UUID(), autoincrement=False, nullable=False),
        sa.ForeignKeyConstraint(["api_client_id"], ["api_client.id"], name="post_reaction_api_client_id_fkey"),
        sa.ForeignKeyConstraint(["person_id"], ["person.id"], name="post_reaction_person_id_fkey"),
        sa.ForeignKeyConstraint(["work_package_id"], ["work_package.id"], name="post_reaction_work_package_id_fkey"),
    )

    op.create_index("ix_person_username", "person", ["api_client_id", "username", "auth_method"], unique=False)
    op.create_foreign_key("text_labels_post_id_fkey", "text_labels", "post", ["post_id"], ["id"])
    op.create_foreign_key("journal_person_id_fkey", "journal", "person", ["person_id"], ["id"])
    op.create_foreign_key("journal_post_id_fkey", "journal", "post", ["post_id"], ["id"])

    op.drop_table("message_reaction")
    op.drop_table("user_stats")
    op.drop_index(op.f("ix_task_user_id"), table_name="task")
    op.drop_table("task")
    op.drop_index(op.f("ix_message_user_id"), table_name="message")
    op.drop_index(op.f("ix_message_task_id"), table_name="message")
    op.drop_index(op.f("ix_message_message_tree_id"), table_name="message")
    op.drop_index("ix_message_frontend_message_id", table_name="message")
    op.drop_table("message")
    op.drop_index("ix_user_username", table_name="user")
    op.drop_table("user")
    # ### end Alembic commands ###
