"""Add nullable indexed external_id and source columns to grocery_item.

The Swiftly API exposes a stable ``id`` per product (e.g. ``"46556"``).
Persisting it as ``grocery_item.external_id`` lets the scraper UPSERT by
``(source, external_id)`` rather than create duplicates on every run.
``source`` distinguishes overlapping IDs across future banners (e.g. a
second Save Mart store using the same Swiftly tenant). See R3-0 notes in
``.agent/phase-summaries/r3-0-summary.md``.

Revision ID: 0005
Revises: 0004
Create Date: 2026-05-05
"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


revision: str = '0005'
down_revision: Union[str, None] = '0004'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.add_column(
        "grocery_item",
        sa.Column("external_id", sa.String(length=100), nullable=True),
    )
    op.add_column(
        "grocery_item",
        sa.Column(
            "source", sa.String(length=50), nullable=True,
        ),
    )
    op.create_index(
        "ix_grocery_item_source_external_id",
        "grocery_item",
        ["source", "external_id"],
        unique=False,
    )


def downgrade() -> None:
    op.drop_index("ix_grocery_item_source_external_id", table_name="grocery_item")
    op.drop_column("grocery_item", "source")
    op.drop_column("grocery_item", "external_id")