#!/usr/bin/env python3
"""
Migration: Add service_location column to cases table
"""

import sqlite3
import os
import sys

def migrate():
    db_path = os.path.join(os.path.dirname(os.path.dirname(__file__)), 'database', 'vera_medical.db')
    
    if not os.path.exists(db_path):
        print(f"Database not found at {db_path}")
        return False
    
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    try:
        # Check if column already exists
        cursor.execute("PRAGMA table_info(cases)")
        columns = [row[1] for row in cursor.fetchall()]
        
        if 'service_location' in columns:
            print("Column 'service_location' already exists in cases table")
            return True
        
        # Add service_location column
        print("Adding service_location column to cases table...")
        cursor.execute("""
            ALTER TABLE cases 
            ADD COLUMN service_location TEXT
        """)
        
        conn.commit()
        print("✓ Successfully added service_location column to cases table")
        return True
        
    except Exception as e:
        print(f"✗ Error during migration: {e}")
        conn.rollback()
        return False
    finally:
        conn.close()

if __name__ == '__main__':
    success = migrate()
    sys.exit(0 if success else 1)