"""Pictures at three levels: provenance columns on brands, product lines and product variants
(Stream AW3.2; the brief `.logs/planning/streams/AW3-images.md`, Design decisions).

Schema only, every column nullable. `brands` and `product_lines` gain the seven picture
columns a product variant already half had (`image_url`, `thumb_url`, `image_source`) plus
the four new ones; `product_variants` gains the four: `image_level` (what the picture depicts:
brand, line or variant, never inferred from the table the row sits in), `image_licence`,
`image_attribution` and `image_set_at`. Typed columns rather than registry entries because a
picture decides no sameness, is shown by one rule everywhere (`services/imagery.resolve`)
and lives on three tables the attribute registry does not cover.

Why: every card and page could show only a product variant's own Open Food Facts photo; a
brand-supplied bottle shot for the line, or a brand mark, had nowhere to live and no record
of who supplied it under what licence. After the deploy: `backfill image_sources --check`,
then `backfill image_sources` (the two old provenance strings move to the controlled
vocabulary and every existing picture is stamped `variant`); the data moves are CLI commands,
never Python here.

Revision ID: aw3b1c2d3e4f
Revises: k12a1b2c3d4e
Create Date: 2026-09-19
"""

import sqlalchemy as sa

from alembic import op

revision = "aw3b1c2d3e4f"
down_revision = "k12a1b2c3d4e"
branch_labels = None
depends_on = None

#: The columns a row's picture carries, in the order they are added. The first three already
#: exist on product_variants (migration e5f6a7b8c9d0).
PICTURE = (
    ("image_url", sa.String(600)),
    ("thumb_url", sa.String(600)),
    ("image_source", sa.String(80)),
)
PROVENANCE = (
    ("image_level", sa.String(16)),
    ("image_licence", sa.String(200)),
    ("image_attribution", sa.String(400)),
    ("image_set_at", sa.DateTime(timezone=True)),
)


def upgrade() -> None:
    for table in ("brands", "product_lines"):
        for name, kind in (*PICTURE, *PROVENANCE):
            op.add_column(table, sa.Column(name, kind, nullable=True))
    for name, kind in PROVENANCE:
        op.add_column("product_variants", sa.Column(name, kind, nullable=True))


def downgrade() -> None:
    for name, _ in reversed(PROVENANCE):
        op.drop_column("product_variants", name)
    for table in ("product_lines", "brands"):
        for name, _ in reversed((*PICTURE, *PROVENANCE)):
            op.drop_column(table, name)