database/fix_json_columns.py

#!/usr/bin/env python3
"""
Fix JSON columns in the database
"""
import sys
import os
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from sqlalchemy import create_engine, text
from config.database import DATABASE_URL
import logging

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

def fix_json_columns():
    """Fix JSON columns to accept empty values"""
    engine = create_engine(DATABASE_URL)
    
    with engine.connect() as conn:
        trans = conn.begin()
        
        try:
            # Update therapy_goals column to have a default empty JSON array
            logger.info("Fixing JSON columns...")
            
            # First update existing empty strings to NULL
            conn.execute(text("UPDATE patients SET therapy_goals = NULL WHERE therapy_goals = ''"))
            
            # If we're using PostgreSQL, update column type to handle JSON properly
            if 'postgresql' in DATABASE_URL:
                # Alter column to be JSON with default
                conn.execute(text("""
                    ALTER TABLE patients 
                    ALTER COLUMN therapy_goals TYPE JSON USING therapy_goals::json,
                    ALTER COLUMN therapy_goals SET DEFAULT '[]'::json
                """))
                
                conn.execute(text("""
                    ALTER TABLE patients 
                    ALTER COLUMN target_sounds SET DEFAULT '[]'::json
                """))
            
            trans.commit()
            logger.info("JSON columns fixed successfully!")
            
        except Exception as e:
            trans.rollback()
            logger.error(f"Failed to fix JSON columns: {str(e)}")
            raise

if __name__ == "__main__":
    fix_json_columns()