"""Add Order.platform_fee_amount

Revision ID: dd66b4cb9a17
Revises: 098b3c536fec
Create Date: 2025-07-25 08:38:00.894046

"""

import sqlalchemy as sa
from alembic import op

# Polar Custom Imports

# revision identifiers, used by Alembic.
revision = "dd66b4cb9a17"
down_revision = "098b3c536fec"
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(
        "orders", sa.Column("platform_fee_amount", sa.Integer(), nullable=True)
    )

    op.execute(
        """
        UPDATE orders
        SET platform_fee_amount = subquery.total_amount
        FROM (
            SELECT
                order_id,
                SUM(amount) as total_amount
            FROM transactions
            WHERE platform_fee_type IS NOT NULL
            AND account_id IS NULL
            GROUP BY order_id
        ) subquery
        WHERE orders.id = subquery.order_id
        AND orders.platform_fee_amount IS NULL;
        """
    )

    op.execute(
        """
        UPDATE orders
        SET platform_fee_amount = 0
        WHERE platform_fee_amount IS NULL;
        """
    )

    op.alter_column(
        "orders",
        "platform_fee_amount",
        existing_type=sa.Integer(),
        nullable=False,
        existing_nullable=True,
    )

    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column("orders", "platform_fee_amount")
    # ### end Alembic commands ###
