"""Change Transaction.dispute_id to foreign key

Revision ID: 8818b5816a78
Revises: f8635d2a8d28
Create Date: 2025-12-03 15:36:32.673593

"""

import sqlalchemy as sa
from alembic import op

# Polar Custom Imports

# revision identifiers, used by Alembic.
revision = "8818b5816a78"
down_revision = "f8635d2a8d28"
branch_labels: tuple[str] | None = None
depends_on: tuple[str] | None = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column(
        "disputes",
        sa.Column("payment_processor", sa.String(), nullable=True),
    )
    op.drop_index(op.f("ix_disputes_dispute_alert_processor_id"), table_name="disputes")
    op.drop_index(op.f("ix_disputes_payment_processor_id"), table_name="disputes")
    op.create_unique_constraint(
        op.f("disputes_dispute_alert_processor_dispute_alert_processor_id_key"),
        "disputes",
        ["dispute_alert_processor", "dispute_alert_processor_id"],
    )
    op.create_unique_constraint(
        op.f("disputes_payment_processor_payment_processor_id_key"),
        "disputes",
        ["payment_processor", "payment_processor_id"],
    )

    op.execute(
        """
        INSERT INTO disputes (
            id,
            created_at,
            status,
            amount,
            tax_amount,
            currency,
            payment_processor,
            payment_processor_id,
            order_id,
            payment_id
        )
        SELECT
            uuid_generate_v4(),
            t1.created_at,
            CASE
                WHEN t2.type = 'dispute_reversal' THEN 'won'
                ELSE 'lost'
            END,
            -t1.amount,
            -t1.tax_amount,
            t1.currency,
            'stripe',
            t1.dispute_id,
            t1.order_id,
            p.id
        FROM transactions t1
        LEFT JOIN transactions t2 ON t1.dispute_id = t2.dispute_id AND t1.id != t2.id
        JOIN payments p ON t1.charge_id = p.processor_id
        WHERE t1.type = 'dispute'
        """
    )

    op.execute(
        """
        UPDATE transactions
        SET dispute_id = d.id
        FROM disputes d
        WHERE transactions.dispute_id = d.payment_processor_id
        """
    )

    op.alter_column(
        "transactions",
        "dispute_id",
        existing_type=sa.VARCHAR(),
        type_=sa.Uuid(),
        existing_nullable=True,
        postgresql_using="dispute_id::uuid",
    )
    op.create_foreign_key(
        op.f("transactions_dispute_id_fkey"),
        "transactions",
        "disputes",
        ["dispute_id"],
        ["id"],
        ondelete="set null",
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint(
        op.f("transactions_dispute_id_fkey"), "transactions", type_="foreignkey"
    )
    op.alter_column(
        "transactions",
        "dispute_id",
        existing_type=sa.Uuid(),
        type_=sa.VARCHAR(),
        existing_nullable=True,
    )
    op.drop_constraint(
        op.f("disputes_payment_processor_payment_processor_id_key"),
        "disputes",
        type_="unique",
    )
    op.drop_constraint(
        op.f("disputes_dispute_alert_processor_dispute_alert_processor_id_key"),
        "disputes",
        type_="unique",
    )
    op.create_index(
        op.f("ix_disputes_payment_processor_id"),
        "disputes",
        ["payment_processor_id"],
        unique=True,
    )
    op.create_index(
        op.f("ix_disputes_dispute_alert_processor_id"),
        "disputes",
        ["dispute_alert_processor_id"],
        unique=True,
    )
    op.drop_column("disputes", "payment_processor")
    # ### end Alembic commands ###
