#!/usr/bin/env python3
"""
Add process service columns to cases table
"""
from app import app
from models import db

with app.app_context():
    print("Adding process service columns to cases table...")

    # Add columns using raw SQL
    from sqlalchemy import text

    try:
        with db.engine.connect() as conn:
            # Check if columns already exist
            result = conn.execute(text("SHOW COLUMNS FROM cases LIKE 'process_server_name'"))
            if result.fetchone():
                print("✓ Columns already exist")
            else:
                # Add the columns
                conn.execute(text("ALTER TABLE cases ADD COLUMN process_server_name VARCHAR(200)"))
                conn.execute(text("ALTER TABLE cases ADD COLUMN service_date DATETIME"))
                conn.execute(text("ALTER TABLE cases ADD COLUMN person_served VARCHAR(200)"))
                conn.execute(text("ALTER TABLE cases ADD COLUMN documents_served TEXT"))
                conn.execute(text("ALTER TABLE cases ADD COLUMN service_notes TEXT"))
                conn.execute(text("ALTER TABLE cases ADD COLUMN proof_of_service_path VARCHAR(500)"))
                conn.commit()
                print("✅ Process service columns added successfully!")
    except Exception as e:
        print(f"❌ Error: {e}")