"""Add image_cache table for locally-stored images fetched from the web."""

from alembic import op

revision = "0016"
down_revision = "0015"


def upgrade() -> None:
    op.execute("""
        CREATE TABLE IF NOT EXISTS image_cache (
            id          SERIAL PRIMARY KEY,
            url_hash    TEXT UNIQUE NOT NULL,
            original_url TEXT NOT NULL,
            source_domain TEXT,
            title       TEXT,
            content_type TEXT NOT NULL DEFAULT 'image/jpeg',
            file_size   INTEGER,
            file_ext    TEXT NOT NULL DEFAULT 'jpg',
            fetched_at  TIMESTAMP WITH TIME ZONE DEFAULT NOW()
        )
    """)
    op.execute(
        "CREATE INDEX IF NOT EXISTS ix_image_cache_url_hash ON image_cache(url_hash)"
    )


def downgrade() -> None:
    op.execute("DROP INDEX IF EXISTS ix_image_cache_url_hash")
    op.execute("DROP TABLE IF EXISTS image_cache")