"""Add original_filename column to content table.

This preserves the pristine uploaded filename even after a player edits the
content (which repoints content.filename at the edited_media/<id>/... file).
Without it the original image appears "overwritten" because the UI can no
longer tell which file was the original upload.
"""
import sys
sys.path.insert(0, '/app')

from app.app import create_app
from app.extensions import db
from sqlalchemy import text

app = create_app()

with app.app_context():
    print("Adding 'original_filename' column to content table...")
    try:
        db.session.execute(text("ALTER TABLE content ADD COLUMN original_filename VARCHAR(255)"))
        db.session.commit()
        print("✓ 'original_filename' column added to content table.")
    except Exception as e:
        if 'duplicate column' in str(e).lower() or 'already exists' in str(e).lower():
            print("✓ 'original_filename' column already exists, skipping.")
        else:
            print(f"✗ Error: {e}")
            raise

    # Backfill: for content whose filename already points at an edited_media/...
    # file, recover the pristine original from the first (v1) PlayerEdit record.
    print("Backfilling original_filename from PlayerEdit v1 records...")
    try:
        db.session.execute(text("""
            UPDATE content
            SET original_filename = (
                SELECT pe.original_name
                FROM player_edit pe
                WHERE pe.content_id = content.id
                ORDER BY pe.version ASC, pe.created_at ASC
                LIMIT 1
            )
            WHERE original_filename IS NULL
              AND filename LIKE 'edited_media/%'
              AND EXISTS (SELECT 1 FROM player_edit pe2 WHERE pe2.content_id = content.id)
        """))
        db.session.commit()
        print("✓ Backfilled original_filename from PlayerEdit records.")
    except Exception as e:
        print(f"✗ Backfill error (non-fatal): {e}")
        db.session.rollback()

    # Backfill fallback: any remaining content with a plain filename is itself
    # the original.
    print("Backfilling original_filename = filename for remaining content...")
    try:
        db.session.execute(text("""
            UPDATE content
            SET original_filename = filename
            WHERE original_filename IS NULL
              AND filename NOT LIKE 'edited_media/%'
        """))
        db.session.commit()
        print("✓ Backfilled original_filename = filename.")
    except Exception as e:
        print(f"✗ Backfill error (non-fatal): {e}")
        db.session.rollback()

    print("Migration complete.")