"""Number every invoices

Revision ID: a314082ec20e
Revises: 5a4d09d23119
Create Date: 2025-05-30 16:28:08.502140

"""

import sqlalchemy as sa
from alembic import op

# Polar Custom Imports

# revision identifiers, used by Alembic.
revision = "a314082ec20e"
down_revision = "5a4d09d23119"
branch_labels: tuple[str] | None = None
depends_on: tuple[str] | None = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.execute(
        """
        UPDATE orders
        SET invoice_number = NULL
        """
    )
    op.execute(
        """
        WITH numbered_orders AS (
            SELECT
                o.id AS order_id,
                org.customer_invoice_prefix,
                ROW_NUMBER() OVER (PARTITION BY org.id ORDER BY o.created_at) AS order_sequence
            FROM
                orders o
            JOIN
                customers c ON o.customer_id = c.id
            JOIN
                organizations org ON c.organization_id = org.id
        )
        UPDATE orders
        SET invoice_number = no.customer_invoice_prefix || '-' || LPAD(no.order_sequence::text, 4, '0')
        FROM numbered_orders no
        WHERE orders.id = no.order_id;
        """
    )
    op.execute(
        """
        UPDATE organizations org
        SET customer_invoice_next_number = (
            SELECT COALESCE(COUNT(*), 0) + 1
            FROM orders o
            JOIN customers c ON o.customer_id = c.id
            WHERE c.organization_id = org.id
        )
        """
    )

    op.alter_column(
        "orders", "invoice_number", existing_type=sa.VARCHAR(), nullable=False
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column(
        "orders", "invoice_number", existing_type=sa.VARCHAR(), nullable=True
    )
    # ### end Alembic commands ###
