"""Add Account billing details and generate Payout invoice numbers

Revision ID: c61bb7759df7
Revises: 3599a43930ce
Create Date: 2025-06-04 15:06:32.396008

"""

import sqlalchemy as sa
from alembic import op

from polar.kit.address import AddressType

# Polar Custom Imports

# revision identifiers, used by Alembic.
revision = "c61bb7759df7"
down_revision = "3599a43930ce"
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("accounts", sa.Column("billing_name", sa.String(), nullable=True))
    op.add_column(
        "accounts",
        sa.Column("billing_address", AddressType(astext_type=sa.Text()), nullable=True),
    )
    op.add_column(
        "accounts", sa.Column("billing_additional_info", sa.Text(), nullable=True)
    )
    op.add_column("accounts", sa.Column("billing_notes", sa.Text(), nullable=True))

    op.execute(
        """
        WITH numbered_payouts AS (
            SELECT
                p.id AS payout_id,
                ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY p.created_at) AS payout_sequence
            FROM
                payouts p
        )
        UPDATE payouts
        SET invoice_number = 'POLAR-' || LPAD(np.payout_sequence::text, 4, '0')
        FROM numbered_payouts np
        WHERE payouts.id = np.payout_id;
        """
    )

    op.alter_column(
        "payouts", "invoice_number", existing_type=sa.VARCHAR(), nullable=False
    )
    op.drop_column("payouts", "invoice_notes")

    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column(
        "payouts",
        sa.Column("invoice_notes", sa.TEXT(), autoincrement=False, nullable=True),
    )
    op.alter_column(
        "payouts", "invoice_number", existing_type=sa.VARCHAR(), nullable=True
    )
    op.drop_column("accounts", "billing_notes")
    op.drop_column("accounts", "billing_additional_info")
    op.drop_column("accounts", "billing_address")
    op.drop_column("accounts", "billing_name")
    # ### end Alembic commands ###
