"""Add SubscriptionProductPrice

Revision ID: bb928a32f068
Revises: 00fc15c4d5f4
Create Date: 2025-03-06 14:23:09.531350

"""

import sqlalchemy as sa
from alembic import op

# Polar Custom Imports

# revision identifiers, used by Alembic.
revision = "bb928a32f068"
down_revision = "00fc15c4d5f4"
branch_labels: tuple[str] | None = None
depends_on: tuple[str] | None = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        "subscription_product_prices",
        sa.Column("subscription_id", sa.Uuid(), nullable=False),
        sa.Column("product_price_id", sa.Uuid(), nullable=False),
        sa.Column("amount", sa.Integer(), nullable=True),
        sa.Column("id", sa.Uuid(), nullable=False),
        sa.Column("created_at", sa.TIMESTAMP(timezone=True), nullable=False),
        sa.Column("modified_at", sa.TIMESTAMP(timezone=True), nullable=True),
        sa.Column("deleted_at", sa.TIMESTAMP(timezone=True), nullable=True),
        sa.ForeignKeyConstraint(
            ["product_price_id"],
            ["product_prices.id"],
            name=op.f("subscription_product_prices_product_price_id_fkey"),
            ondelete="cascade",
        ),
        sa.ForeignKeyConstraint(
            ["subscription_id"],
            ["subscriptions.id"],
            name=op.f("subscription_product_prices_subscription_id_fkey"),
            ondelete="cascade",
        ),
        sa.PrimaryKeyConstraint(
            "subscription_id",
            "product_price_id",
            "id",
            name=op.f("subscription_product_prices_pkey"),
        ),
    )
    op.create_index(
        op.f("ix_subscription_product_prices_created_at"),
        "subscription_product_prices",
        ["created_at"],
        unique=False,
    )
    op.create_index(
        op.f("ix_subscription_product_prices_deleted_at"),
        "subscription_product_prices",
        ["deleted_at"],
        unique=False,
    )
    op.create_index(
        op.f("ix_subscription_product_prices_modified_at"),
        "subscription_product_prices",
        ["modified_at"],
        unique=False,
    )

    op.execute("""
        INSERT INTO subscription_product_prices (subscription_id, product_price_id, id, created_at, amount)
        SELECT
            subscriptions.id,
            subscriptions.price_id,
            uuid_generate_v4(),
            subscriptions.created_at,
            CASE
                WHEN product_prices.amount_type = 'fixed' THEN product_prices.price_amount
                WHEN product_prices.amount_type = 'custom' THEN subscriptions.amount
                ELSE NULL
            END
        FROM subscriptions
        JOIN product_prices ON subscriptions.price_id = product_prices.id
    """)

    op.drop_constraint(
        "subscriptions_price_id_fkey", "subscriptions", type_="foreignkey"
    )
    op.drop_column("subscriptions", "price_id")
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column(
        "subscriptions",
        sa.Column("price_id", sa.UUID(), autoincrement=False, nullable=True),
    )
    op.create_foreign_key(
        "subscriptions_price_id_fkey",
        "subscriptions",
        "product_prices",
        ["price_id"],
        ["id"],
        ondelete="CASCADE",
    )

    op.execute("""
        UPDATE subscriptions
        SET price_id = (
            SELECT product_price_id
            FROM subscription_product_prices
            WHERE subscription_product_prices.subscription_id = subscriptions.id
        )
    """)

    op.alter_column("subscriptions", "price_id", nullable=False)

    op.drop_index(
        op.f("ix_subscription_product_prices_modified_at"),
        table_name="subscription_product_prices",
    )
    op.drop_index(
        op.f("ix_subscription_product_prices_deleted_at"),
        table_name="subscription_product_prices",
    )
    op.drop_index(
        op.f("ix_subscription_product_prices_created_at"),
        table_name="subscription_product_prices",
    )
    op.drop_table("subscription_product_prices")
    # ### end Alembic commands ###
