"""Update file table path

Revision ID: c29facfe716b
Revises: c69f45358db4
Create Date: 2024-10-20 17:02:35.241684

"""

from alembic import op
import sqlalchemy as sa
import json
from sqlalchemy.sql import table, column
from sqlalchemy import String, Text, JSON, and_

revision = 'c29facfe716b'
down_revision = 'c69f45358db4'
branch_labels = None
depends_on = None


def upgrade():
    # 1. Add the `path` column to the "file" table.
    op.add_column('file', sa.Column('path', sa.Text(), nullable=True))

    # 2. Convert the `meta` column from Text/JSONField to `JSON()`
    # Use Alembic's default batch_op for dialect compatibility.
    with op.batch_alter_table('file', schema=None) as batch_op:
        batch_op.alter_column(
            'meta',
            type_=sa.JSON(),
            existing_type=sa.Text(),
            existing_nullable=True,
            nullable=True,
            postgresql_using='meta::json',
        )

    # 3. Migrate legacy data from `meta` JSONField
    # Fetch and process `meta` data from the table, add values to the new `path` column as necessary.
    # We will use SQLAlchemy core bindings to ensure safety across different databases.

    file_table = table('file', column('id', String), column('meta', JSON), column('path', Text))

    # Create connection to the database
    connection = op.get_bind()

    # Get the rows where `meta` has a path and `path` column is null (new column)
    # Loop through each row in the result set to update the path
    results = connection.execute(
        sa.select(file_table.c.id, file_table.c.meta).where(
            and_(file_table.c.path.is_(None), file_table.c.meta.isnot(None))
        )
    ).fetchall()

    # Iterate over each row to extract and update the `path` from `meta` column
    for row in results:
        if 'path' in row.meta:
            # Extract the `path` field from the `meta` JSON
            path = row.meta.get('path')

            # Update the `file` table with the new `path` value
            connection.execute(file_table.update().where(file_table.c.id == row.id).values({'path': path}))


def downgrade():
    # 1. Remove the `path` column
    op.drop_column('file', 'path')

    # 2. Revert the `meta` column back to Text/JSONField
    with op.batch_alter_table('file', schema=None) as batch_op:
        batch_op.alter_column('meta', type_=sa.Text(), existing_type=sa.JSON(), existing_nullable=True)
