"""Add Refund.payment_id

Revision ID: ba238afd79a5
Revises: c4b8b4d63805
Create Date: 2025-12-08 13:55:45.722703

"""

import sqlalchemy as sa
from alembic import op

# Polar Custom Imports

# revision identifiers, used by Alembic.
revision = "ba238afd79a5"
down_revision = "c4b8b4d63805"
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("refunds", sa.Column("payment_id", sa.Uuid(), nullable=True))

    # Create missing payments from transactions
    op.execute(
        """
        INSERT INTO payments (
            id,
            created_at,
            processor,
            status,
            amount,
            currency,
            method,
            method_metadata,
            processor_metadata,
            processor_id,
            organization_id
        )
        SELECT
            uuid_generate_v4() AS new_id,
            t.created_at,
            t.processor,
            'succeeded' AS status,
            t.presentment_amount + t.presentment_tax_amount AS total_amount,
            t.presentment_currency,
            'card' AS payment_method,
            '{}'::jsonb AS metadata,
            '{}'::jsonb AS additional_data,
            t.charge_id,
            COALESCE(c.organization_id, p.organization_id) AS organization_id
        FROM transactions t
        LEFT JOIN customers c ON c.id = t.payment_customer_id
        LEFT JOIN pledges p ON p.id = t.pledge_id
        WHERE t.type = 'payment'
          AND NOT EXISTS (
              SELECT 1
              FROM payments py
              WHERE py.processor_id = t.charge_id
          )
          AND COALESCE(c.organization_id, p.organization_id) IS NOT NULL;
        """
    )

    # Link refunds to payments
    op.execute(
        """
        UPDATE refunds r
        SET payment_id = p.id
        FROM transactions t
        JOIN payments p ON p.processor_id = t.charge_id
        WHERE t.refund_id = r.id
        """
    )

    op.alter_column(
        "refunds",
        "payment_id",
        existing_type=sa.Uuid(),
        nullable=False,
    )

    op.create_index(
        op.f("ix_refunds_payment_id"), "refunds", ["payment_id"], unique=False
    )
    op.create_foreign_key(
        op.f("refunds_payment_id_fkey"), "refunds", "payments", ["payment_id"], ["id"]
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint(op.f("refunds_payment_id_fkey"), "refunds", type_="foreignkey")
    op.drop_index(op.f("ix_refunds_payment_id"), table_name="refunds")
    op.drop_column("refunds", "payment_id")
    # ### end Alembic commands ###
