"""Add openly-licensed imagery, and drop barcodes that are not barcodes

Some shops put a sequential internal SKU in the barcode field. Those pass a
length test, so two shops numbering their own products from 1 would "match"
unrelated bottles. Barcodes are now check-digit validated on collection; this
clears the invalid ones already stored so they cannot join anything.

Revision ID: e5f6a7b8c9d0
Revises: d4e5f6a7b8c9
"""
from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op

revision: str = "e5f6a7b8c9d0"
down_revision: str | None = "d4e5f6a7b8c9"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def _valid(gtin: str) -> bool:
    if not gtin.isdigit() or not 8 <= len(gtin) <= 14:
        return False
    body, check = gtin[:-1], int(gtin[-1])
    total = sum(
        int(char) * (3 if index % 2 == 0 else 1)
        for index, char in enumerate(reversed(body))
    )
    return (10 - total % 10) % 10 == check


def upgrade() -> None:
    op.add_column("products", sa.Column("image_url", sa.String(600), nullable=True))
    op.add_column("products", sa.Column("thumb_url", sa.String(600), nullable=True))
    op.add_column("products", sa.Column("image_source", sa.String(80), nullable=True))
    op.add_column(
        "products",
        sa.Column("image_checked", sa.Boolean(), nullable=False, server_default=sa.false()),
    )

    connection = op.get_bind()
    rows = connection.execute(
        sa.text("SELECT id, gtin FROM products WHERE gtin IS NOT NULL")
    ).fetchall()
    bad = [row.id for row in rows if not _valid(row.gtin)]
    if bad:
        connection.execute(
            sa.text("UPDATE products SET gtin = NULL WHERE id = ANY(:ids)"), {"ids": bad}
        )


def downgrade() -> None:
    op.drop_column("products", "image_checked")
    op.drop_column("products", "image_source")
    op.drop_column("products", "thumb_url")
    op.drop_column("products", "image_url")