"""Remove all hash-mime-types

Revision ID: 1dde0213e9fe
Revises: d216ad75177d
Create Date: 2023-10-19 11:26:06.627048

"""

import sqlalchemy
from alembic import op

# revision identifiers, used by Alembic.
revision = "1dde0213e9fe"
down_revision = "d216ad75177d"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    conn = op.get_bind()
    with conn.begin():
        # Remove mime-types with the pattern  "boefje/{boefje_id}-%", i.e. all mime-types containing parameter hashes

        conn.execute(
            sqlalchemy.text(
                """
WITH filtered AS (
    SELECT m.id, array_agg(m.mime_type) AS mime_types FROM (
        SELECT raw.id, boefje_id, unnest(mime_types) AS mime_type
        FROM raw_file raw JOIN boefje_meta b ON boefje_meta_id = b.id
    ) m

    WHERE m.mime_type NOT LIKE concat('boefje/', m.boefje_id, '-%')
    AND m.mime_type != m.boefje_id
    GROUP BY m.id
)
UPDATE raw_file r SET mime_types = filtered.mime_types FROM filtered WHERE r.id = filtered.id;"""
            )
        )


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    pass
    # ### end Alembic commands ###
