"""add status fields to payment_request_links

Adds HPP link lifecycle columns to payment_requests_links:
  - status         : "PENDING" | "USED" | "REVOKED" | "EXPIRED"
  - used_at        : timestamp when customer completed payment via this link
  - revoked_at     : timestamp when merchant revoked the link
  - click_count    : number of times the link URL has been opened
  - last_clicked_at: timestamp of the most recent click

The existing is_expired boolean column is retained for backward
compatibility; application code should keep it in sync with status.

Revision ID: b4c5d6e7f8a9
Revises: a3b4c5d6e7f8
Create Date: 2026-03-10 00:03:00.000000

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa
from sqlalchemy import inspect as sa_inspect


# revision identifiers, used by Alembic.
revision: str = 'b4c5d6e7f8a9'
down_revision: Union[str, Sequence[str], None] = 'a3b4c5d6e7f8'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    conn = op.get_bind()
    existing_columns = {c['name'] for c in sa_inspect(conn).get_columns('payment_requests_links')}

    # ── 1. status ─────────────────────────────────────────────────────────────
    if 'status' not in existing_columns:
        op.add_column(
            'payment_requests_links',
            sa.Column(
                'status',
                sa.String(length=20),
                nullable=False,
                server_default='PENDING',
            ),
        )
        op.execute(
            """
            UPDATE payment_requests_links
            SET status = 'EXPIRED'
            WHERE is_expired = true
            """
        )

    # ── 2. used_at ────────────────────────────────────────────────────────────
    if 'used_at' not in existing_columns:
        op.add_column(
            'payment_requests_links',
            sa.Column('used_at', sa.DateTime(timezone=True), nullable=True),
        )

    # ── 3. revoked_at ─────────────────────────────────────────────────────────
    if 'revoked_at' not in existing_columns:
        op.add_column(
            'payment_requests_links',
            sa.Column('revoked_at', sa.DateTime(timezone=True), nullable=True),
        )

    # ── 4. click_count ────────────────────────────────────────────────────────
    if 'click_count' not in existing_columns:
        op.add_column(
            'payment_requests_links',
            sa.Column(
                'click_count',
                sa.Integer(),
                nullable=False,
                server_default=sa.text('0'),
            ),
        )

    # ── 5. last_clicked_at ───────────────────────────────────────────────────
    if 'last_clicked_at' not in existing_columns:
        op.add_column(
            'payment_requests_links',
            sa.Column('last_clicked_at', sa.DateTime(timezone=True), nullable=True),
        )


def downgrade() -> None:
    op.drop_column('payment_requests_links', 'last_clicked_at')
    op.drop_column('payment_requests_links', 'click_count')
    op.drop_column('payment_requests_links', 'revoked_at')
    op.drop_column('payment_requests_links', 'used_at')
    op.drop_column('payment_requests_links', 'status')