"""remove the Repository model

Revision ID: 7c88b9cd96aa
Revises: cd34fdfafdaf
Create Date: 2024-05-22 06:48:40.788139

"""

import sqlalchemy as sa
from alembic import op

# revision identifiers, used by Alembic.
revision = "7c88b9cd96aa"
down_revision = "cd34fdfafdaf"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###

    non_local = op.get_bind().execute(
        "SELECT * from plugin_state join repository r on repository_pk = r.pk where r.id != 'LOCAL'"
    )
    if len(non_local.fetchall()) > 0:
        raise Exception("Cannot perform migration: remove plugin_states that refer to nonlocal repositories first.")

    op.drop_constraint("unique_plugin_per_repo_per_org", "plugin_state", type_="unique")
    op.create_unique_constraint("unique_plugin_id_per_org", "plugin_state", ["plugin_id", "organisation_pk"])

    op.drop_constraint("plugin_state_repository_pk_fkey", "plugin_state", type_="foreignkey")
    op.drop_column("plugin_state", "repository_pk")
    op.drop_table("organisation_repository")
    op.drop_table("repository")

    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        "repository",
        sa.Column(
            "pk",
            sa.INTEGER(),
            server_default=sa.text("nextval('repository_pk_seq'::regclass)"),
            autoincrement=True,
            nullable=False,
        ),
        sa.Column("id", sa.VARCHAR(length=32), autoincrement=False, nullable=False),
        sa.Column("name", sa.VARCHAR(length=64), autoincrement=False, nullable=False),
        sa.Column("base_url", sa.VARCHAR(length=128), autoincrement=False, nullable=False),
        sa.PrimaryKeyConstraint("pk", name="repository_pkey"),
        sa.UniqueConstraint("id", name="repository_id"),
        postgresql_ignore_search_path=False,
    )
    op.create_table(
        "organisation_repository",
        sa.Column("organisation_pk", sa.INTEGER(), autoincrement=False, nullable=False),
        sa.Column("repository_pk", sa.INTEGER(), autoincrement=False, nullable=False),
        sa.ForeignKeyConstraint(
            ["organisation_pk"], ["organisation.pk"], name="organisation_repository_organisation_pk_fkey"
        ),
        sa.ForeignKeyConstraint(
            ["repository_pk"], ["repository.pk"], name="organisation_repository_repository_pk_fkey"
        ),
    )

    # Add repository_pk column
    op.add_column("plugin_state", sa.Column("repository_pk", sa.INTEGER(), autoincrement=False, nullable=True))
    op.create_foreign_key(
        "plugin_state_repository_pk_fkey", "plugin_state", "repository", ["repository_pk"], ["pk"], ondelete="CASCADE"
    )

    conn = op.get_bind()
    with conn.begin():
        conn.execute(
            "INSERT INTO repository (pk, id, name, base_url) values (1, 'LOCAL', 'Local Plugin Repository', 'http://dev/null')"
        )
        conn.execute("UPDATE plugin_state set repository_pk = 1")

    op.alter_column("plugin_state", "repository_pk", nullable=False)
    op.drop_constraint("unique_plugin_id_per_org", "plugin_state", type_="unique")
    op.create_unique_constraint(
        "unique_plugin_per_repo_per_org", "plugin_state", ["plugin_id", "organisation_pk", "repository_pk"]
    )

    # ### end Alembic commands ###
