#!/usr/bin/env python3
"""Migration script to add user_note and references columns to reports table."""
import sys
import os

# Add the parent directory to the path
sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))

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

    app = create_app()
    with app.app_context():
        inspector = db.inspect(db.engine)
        columns = [c['name'] for c in inspector.get_columns('reports')]
        print('Current columns:', columns)

        if 'user_note' not in columns:
            with db.engine.connect() as conn:
                conn.execute(text('ALTER TABLE reports ADD COLUMN user_note TEXT'))
                conn.commit()
            print('✓ Added user_note column')
        else:
            print('✓ user_note column already exists')

        if 'references' not in columns:
            with db.engine.connect() as conn:
                conn.execute(text('ALTER TABLE reports ADD COLUMN references JSON'))
                conn.commit()
            print('✓ Added references column')
        else:
            print('✓ references column already exists')

        print('\nMigration completed successfully!')

except ImportError as e:
    print(f"Error: {e}")
    print("\nPlease run this using the Flask CLI:")
    print("  flask shell < migrate_add_references.py")
    print("\nOr execute SQL directly:")
    print("  ALTER TABLE reports ADD COLUMN user_note TEXT;")
    print("  ALTER TABLE reports ADD COLUMN references JSON;")