"""Remove Issue Funding-related tables

Revision ID: 8250613a3fdd
Revises: 5155c7a0d153
Create Date: 2025-04-09 16:39:20.121365

"""

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

# Polar Custom Imports

# revision identifiers, used by Alembic.
revision = "8250613a3fdd"
down_revision = "5155c7a0d153"
branch_labels: tuple[str] | None = None
depends_on: tuple[str] | None = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    conn = op.get_bind()
    inspector = sa.inspect(conn)

    op.add_column("pledges", sa.Column("issue_reference", sa.String(), nullable=True))
    op.add_column(
        "issue_rewards", sa.Column("issue_reference", sa.String(), nullable=True)
    )

    op.execute("""
        UPDATE pledges
        SET issue_reference = (
            SELECT external_lookup_key
            FROM issues
            WHERE issues.id = pledges.issue_id
        );
    """)
    op.execute("""
        UPDATE issue_rewards
        SET issue_reference = (
            SELECT external_lookup_key
            FROM issues
            WHERE issues.id = issue_rewards.issue_id
        );
    """)

    op.alter_column(
        "pledges", "issue_reference", existing_type=sa.String(), nullable=False
    )
    op.alter_column(
        "issue_rewards", "issue_reference", existing_type=sa.String(), nullable=False
    )

    op.execute("""
        DELETE FROM notifications
        WHERE type IN (
            'MaintainerPledgeCreatedNotification',
            'MaintainerPledgeConfirmationPendingNotification',
            'MaintainerPledgedIssueConfirmationPendingNotification',
            'MaintainerPledgePendingNotification',
            'MaintainerPledgedIssuePendingNotification',
            'MaintainerPledgePaidNotification',
            'RewardPaidNotification',
            'PledgerPledgePendingNotification',
            'TeamAdminMemberPledgedNotification'
        );
    """)

    op.drop_constraint(
        "issue_rewards_issue_id_github_username_key", "issue_rewards", type_="unique"
    )
    op.drop_constraint(
        "issue_rewards_issue_id_organization_id_key", "issue_rewards", type_="unique"
    )
    op.drop_constraint(
        "issue_rewards_issue_id_user_id_key", "issue_rewards", type_="unique"
    )
    op.create_unique_constraint(
        op.f("issue_rewards_issue_reference_github_username_key"),
        "issue_rewards",
        ["issue_reference", "github_username"],
    )
    op.create_unique_constraint(
        op.f("issue_rewards_issue_reference_organization_id_key"),
        "issue_rewards",
        ["issue_reference", "organization_id"],
    )
    op.create_unique_constraint(
        op.f("issue_rewards_issue_reference_user_id_key"),
        "issue_rewards",
        ["issue_reference", "user_id"],
    )
    op.create_index(
        op.f("ix_issue_rewards_issue_reference"),
        "issue_rewards",
        ["issue_reference"],
        unique=False,
    )

    if "issue_rewards_issue_id_fkey" in [
        fk["name"] for fk in inspector.get_foreign_keys("issue_rewards")
    ]:
        op.drop_constraint(
            "issue_rewards_issue_id_fkey", "issue_rewards", type_="foreignkey"
        )
    op.drop_column("issue_rewards", "issue_id")
    op.drop_constraint(
        "notifications_issue_id_fkey", "notifications", type_="foreignkey"
    )
    op.drop_column("notifications", "issue_id")

    op.drop_index("idx_issue_id_state", table_name="pledges")
    op.create_index(
        op.f("ix_pledges_issue_reference"), "pledges", ["issue_reference"], unique=False
    )
    op.create_index(
        op.f("ix_pledges_organization_id"), "pledges", ["organization_id"], unique=False
    )
    op.drop_constraint("pledges_repository_id_fkey", "pledges", type_="foreignkey")
    op.drop_constraint("pledges_issue_id_fkey", "pledges", type_="foreignkey")

    op.drop_constraint("pledges_organization_id_fkey", "pledges", type_="foreignkey")
    op.alter_column(
        "pledges", "organization_id", existing_type=sa.UUID(), nullable=True
    )
    op.execute("""
        UPDATE pledges
        SET organization_id = (
            SELECT organization_id
            FROM external_organizations
            WHERE external_organizations.id = pledges.organization_id
        );
    """)
    op.create_foreign_key(
        op.f("pledges_organization_id_fkey"),
        "pledges",
        "organizations",
        ["organization_id"],
        ["id"],
    )

    op.drop_column("pledges", "issue_id")
    op.drop_column("pledges", "repository_id")

    op.drop_index("idx_default_search", table_name="issues")
    op.drop_index("idx_issues_id_closed_at", table_name="issues")
    op.drop_index("idx_issues_positive_reactions_count", table_name="issues")
    op.drop_index("idx_issues_positive_total_engagement_count", table_name="issues")
    op.drop_index("idx_issues_title_tsv", table_name="issues", postgresql_using="gin")
    op.drop_index("ix_issues_created_at", table_name="issues")
    op.drop_index("ix_issues_deleted_at", table_name="issues")
    op.drop_index("ix_issues_external_lookup_key", table_name="issues")
    op.drop_index("ix_issues_modified_at", table_name="issues")
    op.drop_index("ix_issues_repository_id", table_name="issues")
    op.drop_table("issues")
    op.drop_index("ix_repositories_created_at", table_name="repositories")
    op.drop_index("ix_repositories_deleted_at", table_name="repositories")
    op.drop_index("ix_repositories_is_private", table_name="repositories")
    op.drop_index("ix_repositories_modified_at", table_name="repositories")
    op.drop_table("repositories")
    op.drop_index(
        "ix_external_organizations_created_at", table_name="external_organizations"
    )
    op.drop_index(
        "ix_external_organizations_deleted_at", table_name="external_organizations"
    )
    op.drop_index(
        "ix_external_organizations_modified_at", table_name="external_organizations"
    )
    op.drop_table("external_organizations")

    op.drop_constraint(
        "organizations_stripe_customer_id_key", "organizations", type_="unique"
    )
    op.drop_column("organizations", "is_teams_enabled")
    op.drop_column("organizations", "created_from_user_maintainer_upgrade")
    op.drop_column("organizations", "total_monthly_spending_limit")
    op.drop_column("organizations", "default_upfront_split_to_contributors")
    op.drop_column("organizations", "default_badge_custom_content")
    op.drop_column("organizations", "per_user_monthly_spending_limit")
    op.drop_column("organizations", "pledge_badge_show_amount")
    op.drop_column("organizations", "pledge_minimum_amount")
    op.drop_column("organizations", "billing_email")
    op.drop_column("organizations", "stripe_customer_id")
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column(
        "pledges",
        sa.Column("repository_id", sa.UUID(), autoincrement=False, nullable=False),
    )
    op.add_column(
        "pledges", sa.Column("issue_id", sa.UUID(), autoincrement=False, nullable=False)
    )
    op.drop_constraint(
        op.f("pledges_organization_id_fkey"), "pledges", type_="foreignkey"
    )
    op.create_foreign_key(
        "pledges_organization_id_fkey",
        "pledges",
        "external_organizations",
        ["organization_id"],
        ["id"],
    )
    op.create_foreign_key(
        "pledges_issue_id_fkey", "pledges", "issues", ["issue_id"], ["id"]
    )
    op.create_foreign_key(
        "pledges_repository_id_fkey",
        "pledges",
        "repositories",
        ["repository_id"],
        ["id"],
    )
    op.drop_index(op.f("ix_pledges_organization_id"), table_name="pledges")
    op.drop_index(op.f("ix_pledges_issue_reference"), table_name="pledges")
    op.create_index(
        "idx_issue_id_state", "pledges", ["issue_id", "state"], unique=False
    )
    op.drop_column("pledges", "issue_reference")
    op.add_column(
        "organizations",
        sa.Column(
            "stripe_customer_id",
            sa.VARCHAR(length=50),
            autoincrement=False,
            nullable=True,
        ),
    )
    op.add_column(
        "organizations",
        sa.Column(
            "billing_email", sa.VARCHAR(length=120), autoincrement=False, nullable=True
        ),
    )
    op.add_column(
        "organizations",
        sa.Column(
            "pledge_minimum_amount", sa.INTEGER(), autoincrement=False, nullable=False
        ),
    )
    op.add_column(
        "organizations",
        sa.Column(
            "pledge_badge_show_amount",
            sa.BOOLEAN(),
            autoincrement=False,
            nullable=False,
        ),
    )
    op.add_column(
        "organizations",
        sa.Column(
            "per_user_monthly_spending_limit",
            sa.INTEGER(),
            autoincrement=False,
            nullable=True,
        ),
    )
    op.add_column(
        "organizations",
        sa.Column(
            "default_badge_custom_content",
            sa.VARCHAR(),
            autoincrement=False,
            nullable=True,
        ),
    )
    op.add_column(
        "organizations",
        sa.Column(
            "default_upfront_split_to_contributors",
            sa.INTEGER(),
            autoincrement=False,
            nullable=True,
        ),
    )
    op.add_column(
        "organizations",
        sa.Column(
            "total_monthly_spending_limit",
            sa.INTEGER(),
            autoincrement=False,
            nullable=True,
        ),
    )
    op.add_column(
        "organizations",
        sa.Column(
            "created_from_user_maintainer_upgrade",
            sa.BOOLEAN(),
            autoincrement=False,
            nullable=False,
        ),
    )
    op.add_column(
        "organizations",
        sa.Column(
            "is_teams_enabled", sa.BOOLEAN(), autoincrement=False, nullable=False
        ),
    )
    op.create_unique_constraint(
        "organizations_stripe_customer_id_key", "organizations", ["stripe_customer_id"]
    )
    op.add_column(
        "notifications",
        sa.Column("issue_id", sa.UUID(), autoincrement=False, nullable=True),
    )
    op.create_foreign_key(
        "notifications_issue_id_fkey", "notifications", "issues", ["issue_id"], ["id"]
    )
    op.add_column(
        "issue_rewards",
        sa.Column("issue_id", sa.UUID(), autoincrement=False, nullable=False),
    )
    op.create_foreign_key(
        "issue_rewards_issue_id_fkey", "issue_rewards", "issues", ["issue_id"], ["id"]
    )
    op.drop_index(op.f("ix_issue_rewards_issue_reference"), table_name="issue_rewards")
    op.drop_constraint(
        op.f("issue_rewards_issue_reference_user_id_key"),
        "issue_rewards",
        type_="unique",
    )
    op.drop_constraint(
        op.f("issue_rewards_issue_reference_organization_id_key"),
        "issue_rewards",
        type_="unique",
    )
    op.drop_constraint(
        op.f("issue_rewards_issue_reference_github_username_key"),
        "issue_rewards",
        type_="unique",
    )
    op.create_unique_constraint(
        "issue_rewards_issue_id_user_id_key", "issue_rewards", ["issue_id", "user_id"]
    )
    op.create_unique_constraint(
        "issue_rewards_issue_id_organization_id_key",
        "issue_rewards",
        ["issue_id", "organization_id"],
    )
    op.create_unique_constraint(
        "issue_rewards_issue_id_github_username_key",
        "issue_rewards",
        ["issue_id", "github_username"],
    )
    op.drop_column("issue_rewards", "issue_reference")
    op.create_table(
        "repositories",
        sa.Column("platform", sa.VARCHAR(), autoincrement=False, nullable=False),
        sa.Column("external_id", sa.BIGINT(), autoincrement=False, nullable=False),
        sa.Column("organization_id", sa.UUID(), autoincrement=False, nullable=False),
        sa.Column("name", postgresql.CITEXT(), autoincrement=False, nullable=False),
        sa.Column("description", sa.TEXT(), autoincrement=False, nullable=True),
        sa.Column("open_issues", sa.INTEGER(), autoincrement=False, nullable=True),
        sa.Column("forks", sa.INTEGER(), autoincrement=False, nullable=True),
        sa.Column("stars", sa.INTEGER(), autoincrement=False, nullable=True),
        sa.Column("watchers", sa.INTEGER(), autoincrement=False, nullable=True),
        sa.Column("main_branch", sa.VARCHAR(), autoincrement=False, nullable=True),
        sa.Column(
            "topics",
            postgresql.JSONB(astext_type=sa.Text()),
            autoincrement=False,
            nullable=False,
        ),
        sa.Column("license", sa.VARCHAR(), autoincrement=False, nullable=True),
        sa.Column("homepage", sa.VARCHAR(), autoincrement=False, nullable=True),
        sa.Column(
            "repository_pushed_at",
            postgresql.TIMESTAMP(timezone=True),
            autoincrement=False,
            nullable=True,
        ),
        sa.Column(
            "repository_created_at",
            postgresql.TIMESTAMP(timezone=True),
            autoincrement=False,
            nullable=True,
        ),
        sa.Column(
            "repository_modified_at",
            postgresql.TIMESTAMP(timezone=True),
            autoincrement=False,
            nullable=True,
        ),
        sa.Column(
            "issues_references_synced_at",
            postgresql.TIMESTAMP(timezone=True),
            autoincrement=False,
            nullable=True,
        ),
        sa.Column(
            "profile_settings",
            postgresql.JSONB(astext_type=sa.Text()),
            autoincrement=False,
            nullable=False,
        ),
        sa.Column(
            "pledge_badge_auto_embed", sa.BOOLEAN(), autoincrement=False, nullable=False
        ),
        sa.Column(
            "pledge_badge_label", sa.VARCHAR(), autoincrement=False, nullable=False
        ),
        sa.Column("is_private", sa.BOOLEAN(), autoincrement=False, nullable=False),
        sa.Column("is_fork", sa.BOOLEAN(), autoincrement=False, nullable=True),
        sa.Column(
            "is_issues_enabled", sa.BOOLEAN(), autoincrement=False, nullable=True
        ),
        sa.Column(
            "is_projects_enabled", sa.BOOLEAN(), autoincrement=False, nullable=True
        ),
        sa.Column("is_wiki_enabled", sa.BOOLEAN(), autoincrement=False, nullable=True),
        sa.Column("is_pages_enabled", sa.BOOLEAN(), autoincrement=False, nullable=True),
        sa.Column(
            "is_downloads_enabled", sa.BOOLEAN(), autoincrement=False, nullable=True
        ),
        sa.Column("is_archived", sa.BOOLEAN(), autoincrement=False, nullable=True),
        sa.Column("is_disabled", sa.BOOLEAN(), autoincrement=False, nullable=True),
        sa.Column("id", sa.UUID(), autoincrement=False, nullable=False),
        sa.Column(
            "created_at",
            postgresql.TIMESTAMP(timezone=True),
            autoincrement=False,
            nullable=False,
        ),
        sa.Column(
            "modified_at",
            postgresql.TIMESTAMP(timezone=True),
            autoincrement=False,
            nullable=True,
        ),
        sa.Column(
            "deleted_at",
            postgresql.TIMESTAMP(timezone=True),
            autoincrement=False,
            nullable=True,
        ),
        sa.ForeignKeyConstraint(
            ["organization_id"],
            ["external_organizations.id"],
            name="repositories_organization_id_fkey",
        ),
        sa.PrimaryKeyConstraint("id", name="repositories_pkey"),
        sa.UniqueConstraint("external_id", name="repositories_external_id_key"),
        sa.UniqueConstraint(
            "organization_id", "name", name="repositories_organization_id_name_key"
        ),
        postgresql_ignore_search_path=False,
    )
    op.create_index(
        "ix_repositories_modified_at", "repositories", ["modified_at"], unique=False
    )
    op.create_index(
        "ix_repositories_is_private", "repositories", ["is_private"], unique=False
    )
    op.create_index(
        "ix_repositories_deleted_at", "repositories", ["deleted_at"], unique=False
    )
    op.create_index(
        "ix_repositories_created_at", "repositories", ["created_at"], unique=False
    )
    op.create_table(
        "external_organizations",
        sa.Column("platform", sa.VARCHAR(), autoincrement=False, nullable=False),
        sa.Column("name", postgresql.CITEXT(), autoincrement=False, nullable=False),
        sa.Column("external_id", sa.BIGINT(), autoincrement=False, nullable=False),
        sa.Column("avatar_url", sa.VARCHAR(), autoincrement=False, nullable=False),
        sa.Column("is_personal", sa.BOOLEAN(), autoincrement=False, nullable=False),
        sa.Column("installation_id", sa.INTEGER(), autoincrement=False, nullable=True),
        sa.Column(
            "installation_created_at",
            postgresql.TIMESTAMP(timezone=True),
            autoincrement=False,
            nullable=True,
        ),
        sa.Column(
            "installation_updated_at",
            postgresql.TIMESTAMP(timezone=True),
            autoincrement=False,
            nullable=True,
        ),
        sa.Column(
            "installation_suspended_at",
            postgresql.TIMESTAMP(timezone=True),
            autoincrement=False,
            nullable=True,
        ),
        sa.Column(
            "installation_suspended_by",
            sa.INTEGER(),
            autoincrement=False,
            nullable=True,
        ),
        sa.Column(
            "installation_suspender", sa.UUID(), autoincrement=False, nullable=True
        ),
        sa.Column(
            "installation_permissions",
            postgresql.JSONB(astext_type=sa.Text()),
            autoincrement=False,
            nullable=True,
        ),
        sa.Column("bio", sa.VARCHAR(), autoincrement=False, nullable=True),
        sa.Column("pretty_name", sa.VARCHAR(), autoincrement=False, nullable=True),
        sa.Column("company", sa.VARCHAR(), autoincrement=False, nullable=True),
        sa.Column("blog", sa.VARCHAR(), autoincrement=False, nullable=True),
        sa.Column("location", sa.VARCHAR(), autoincrement=False, nullable=True),
        sa.Column("email", sa.VARCHAR(), autoincrement=False, nullable=True),
        sa.Column("twitter_username", sa.VARCHAR(), autoincrement=False, nullable=True),
        sa.Column(
            "created_at",
            postgresql.TIMESTAMP(timezone=True),
            autoincrement=False,
            nullable=False,
        ),
        sa.Column(
            "modified_at",
            postgresql.TIMESTAMP(timezone=True),
            autoincrement=False,
            nullable=True,
        ),
        sa.Column(
            "deleted_at",
            postgresql.TIMESTAMP(timezone=True),
            autoincrement=False,
            nullable=True,
        ),
        sa.Column("id", sa.UUID(), autoincrement=False, nullable=False),
        sa.Column("organization_id", sa.UUID(), autoincrement=False, nullable=True),
        sa.ForeignKeyConstraint(
            ["organization_id"],
            ["organizations.id"],
            name="external_organizations_organization_id_fkey",
            ondelete="SET NULL",
        ),
        sa.PrimaryKeyConstraint("id", name="external_organizations_pkey"),
        sa.UniqueConstraint(
            "external_id", name="external_organizations_external_id_key"
        ),
        sa.UniqueConstraint(
            "installation_id", name="external_organizations_installation_id_key"
        ),
        sa.UniqueConstraint(
            "name", "platform", name="external_organizations_name_platform_key"
        ),
        sa.UniqueConstraint("name", name="external_organizations_name_key"),
        postgresql_ignore_search_path=False,
    )
    op.create_index(
        "ix_external_organizations_modified_at",
        "external_organizations",
        ["modified_at"],
        unique=False,
    )
    op.create_index(
        "ix_external_organizations_deleted_at",
        "external_organizations",
        ["deleted_at"],
        unique=False,
    )
    op.create_index(
        "ix_external_organizations_created_at",
        "external_organizations",
        ["created_at"],
        unique=False,
    )
    op.create_table(
        "issues",
        sa.Column(
            "external_lookup_key", sa.VARCHAR(), autoincrement=False, nullable=True
        ),
        sa.Column(
            "pledge_badge_embedded_at",
            postgresql.TIMESTAMP(timezone=True),
            autoincrement=False,
            nullable=True,
        ),
        sa.Column(
            "pledge_badge_ever_embedded",
            sa.BOOLEAN(),
            autoincrement=False,
            nullable=False,
        ),
        sa.Column(
            "has_pledge_badge_label", sa.BOOLEAN(), autoincrement=False, nullable=False
        ),
        sa.Column(
            "badge_custom_content", sa.VARCHAR(), autoincrement=False, nullable=True
        ),
        sa.Column(
            "github_issue_etag", sa.VARCHAR(), autoincrement=False, nullable=True
        ),
        sa.Column(
            "github_issue_fetched_at",
            postgresql.TIMESTAMP(timezone=True),
            autoincrement=False,
            nullable=True,
        ),
        sa.Column("funding_goal", sa.BIGINT(), autoincrement=False, nullable=True),
        sa.Column(
            "pledged_amount_sum", sa.BIGINT(), autoincrement=False, nullable=False
        ),
        sa.Column(
            "last_pledged_at",
            postgresql.TIMESTAMP(timezone=True),
            autoincrement=False,
            nullable=True,
        ),
        sa.Column(
            "issue_has_in_progress_relationship",
            sa.BOOLEAN(),
            server_default=sa.text("false"),
            autoincrement=False,
            nullable=False,
        ),
        sa.Column(
            "issue_has_pull_request_relationship",
            sa.BOOLEAN(),
            server_default=sa.text("false"),
            autoincrement=False,
            nullable=False,
        ),
        sa.Column(
            "positive_reactions_count",
            sa.INTEGER(),
            autoincrement=False,
            nullable=False,
        ),
        sa.Column(
            "total_engagement_count", sa.INTEGER(), autoincrement=False, nullable=False
        ),
        sa.Column(
            "needs_confirmation_solved",
            sa.BOOLEAN(),
            autoincrement=False,
            nullable=False,
        ),
        sa.Column(
            "confirmed_solved_at",
            postgresql.TIMESTAMP(timezone=True),
            autoincrement=False,
            nullable=True,
        ),
        sa.Column("confirmed_solved_by", sa.UUID(), autoincrement=False, nullable=True),
        sa.Column(
            "upfront_split_to_contributors",
            sa.INTEGER(),
            autoincrement=False,
            nullable=True,
        ),
        sa.Column("platform", sa.VARCHAR(), autoincrement=False, nullable=False),
        sa.Column("external_id", sa.BIGINT(), autoincrement=False, nullable=False),
        sa.Column("number", sa.INTEGER(), autoincrement=False, nullable=False),
        sa.Column("title", sa.VARCHAR(), autoincrement=False, nullable=False),
        sa.Column("body", sa.TEXT(), autoincrement=False, nullable=True),
        sa.Column("comments", sa.INTEGER(), autoincrement=False, nullable=True),
        sa.Column(
            "author",
            postgresql.JSONB(astext_type=sa.Text()),
            autoincrement=False,
            nullable=True,
        ),
        sa.Column(
            "author_association", sa.VARCHAR(), autoincrement=False, nullable=True
        ),
        sa.Column(
            "labels",
            postgresql.JSONB(astext_type=sa.Text()),
            autoincrement=False,
            nullable=True,
        ),
        sa.Column(
            "assignee",
            postgresql.JSONB(astext_type=sa.Text()),
            autoincrement=False,
            nullable=True,
        ),
        sa.Column(
            "assignees",
            postgresql.JSONB(astext_type=sa.Text()),
            autoincrement=False,
            nullable=True,
        ),
        sa.Column(
            "milestone",
            postgresql.JSONB(astext_type=sa.Text()),
            autoincrement=False,
            nullable=True,
        ),
        sa.Column(
            "closed_by",
            postgresql.JSONB(astext_type=sa.Text()),
            autoincrement=False,
            nullable=True,
        ),
        sa.Column(
            "reactions",
            postgresql.JSONB(astext_type=sa.Text()),
            autoincrement=False,
            nullable=True,
        ),
        sa.Column("state", sa.VARCHAR(), autoincrement=False, nullable=False),
        sa.Column("state_reason", sa.VARCHAR(), autoincrement=False, nullable=True),
        sa.Column(
            "issue_closed_at",
            postgresql.TIMESTAMP(timezone=True),
            autoincrement=False,
            nullable=True,
        ),
        sa.Column(
            "issue_created_at",
            postgresql.TIMESTAMP(timezone=True),
            autoincrement=False,
            nullable=False,
        ),
        sa.Column(
            "issue_modified_at",
            postgresql.TIMESTAMP(timezone=True),
            autoincrement=False,
            nullable=True,
        ),
        sa.Column(
            "title_tsv",
            postgresql.TSVECTOR(),
            sa.Computed(
                "to_tsvector('simple'::regconfig, (title)::text)", persisted=True
            ),
            autoincrement=False,
            nullable=False,
        ),
        sa.Column("organization_id", sa.UUID(), autoincrement=False, nullable=False),
        sa.Column("repository_id", sa.UUID(), autoincrement=False, nullable=False),
        sa.Column("id", sa.UUID(), autoincrement=False, nullable=False),
        sa.Column(
            "created_at",
            postgresql.TIMESTAMP(timezone=True),
            autoincrement=False,
            nullable=False,
        ),
        sa.Column(
            "modified_at",
            postgresql.TIMESTAMP(timezone=True),
            autoincrement=False,
            nullable=True,
        ),
        sa.Column(
            "deleted_at",
            postgresql.TIMESTAMP(timezone=True),
            autoincrement=False,
            nullable=True,
        ),
        sa.ForeignKeyConstraint(
            ["confirmed_solved_by"],
            ["users.id"],
            name="issues_confirmed_solved_by_fkey",
        ),
        sa.ForeignKeyConstraint(
            ["organization_id"],
            ["external_organizations.id"],
            name="issues_organization_id_fkey",
        ),
        sa.ForeignKeyConstraint(
            ["repository_id"], ["repositories.id"], name="issues_repository_id_fkey"
        ),
        sa.PrimaryKeyConstraint("id", name="issues_pkey"),
        sa.UniqueConstraint("external_id", name="issues_external_id_key"),
        sa.UniqueConstraint(
            "organization_id",
            "repository_id",
            "number",
            name="issues_organization_id_repository_id_number_key",
        ),
        sa.UniqueConstraint(
            "platform",
            "external_lookup_key",
            name="issues_platform_external_lookup_key_key",
        ),
    )
    op.create_index(
        "ix_issues_repository_id", "issues", ["repository_id"], unique=False
    )
    op.create_index("ix_issues_modified_at", "issues", ["modified_at"], unique=False)
    op.create_index(
        "ix_issues_external_lookup_key", "issues", ["external_lookup_key"], unique=False
    )
    op.create_index("ix_issues_deleted_at", "issues", ["deleted_at"], unique=False)
    op.create_index("ix_issues_created_at", "issues", ["created_at"], unique=False)
    op.create_index(
        "idx_issues_title_tsv",
        "issues",
        ["title_tsv"],
        unique=False,
        postgresql_using="gin",
    )
    op.create_index(
        "idx_issues_positive_total_engagement_count",
        "issues",
        ["total_engagement_count"],
        unique=False,
    )
    op.create_index(
        "idx_issues_positive_reactions_count",
        "issues",
        ["positive_reactions_count"],
        unique=False,
    )
    op.create_index(
        "idx_issues_id_closed_at", "issues", ["id", "issue_closed_at"], unique=False
    )
    op.create_index(
        "idx_default_search",
        "issues",
        [
            "pledged_amount_sum",
            "last_pledged_at",
            "total_engagement_count",
            "created_at",
        ],
        unique=False,
    )
    # ### end Alembic commands ###
