PYTHON migrate_sms_tables.py

#!/usr/bin/env python3
"""
Migration script for SMS + Call connector tables.

Creates the following tables if they don't exist:
- sms_messages
- calls
- lead_touchpoints
- opt_outs

Usage:
    python scripts/migrate_sms_tables.py [--db-path /path/to/auth.db]

This uses SQLAlchemy's db.create_all() pattern to add missing tables
without affecting existing data.
"""
import argparse
import os
import sys

def main():
    parser = argparse.ArgumentParser(description='Create SMS/Call database tables')
    parser.add_argument('--db-path', default=None, help='Path to auth.db (default: auto-detect)')
    args = parser.parse_args()

    # Add project root to path
    project_root = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
    sys.path.insert(0, project_root)

    # Set up environment
    if args.db_path:
        os.environ['DATABASE_URL'] = f'sqlite:///{args.db_path}'
    else:
        # Default location
        db_path = os.path.join(project_root, 'instance', 'auth.db')
        if os.path.exists(db_path):
            os.environ['DATABASE_URL'] = f'sqlite:///{db_path}'
        else:
            # Check alternative location
            db_path = os.path.join(project_root, 'auth.db')
            if os.path.exists(db_path):
                os.environ['DATABASE_URL'] = f'sqlite:///{db_path}'

    # Create app context
    from app import create_app
    from app.models import db

    app = create_app()

    with app.app_context():
        # Tables to create (in order of dependencies)
        from app.models import SmsMessage, Call, LeadTouchpoint, OptOut

        tables = [SmsMessage, Call, LeadTouchpoint, OptOut]

        created = []
        skipped = []

        for model in tables:
            table_name = model.__tablename__
            # Check if table already exists
            inspector = db.inspect(db.engine)
            if table_name in inspector.get_table_names():
                skipped.append(table_name)
                print(f'  ✓ {table_name} already exists — skipped')
            else:
                db.create_all(table=[model])
                created.append(table_name)
                print(f'  + Created table: {table_name}')

        print(f'\nMigration complete: {len(created)} created, {len(skipped)} skipped')

        if created:
            print(f'  Created: {", ".join(created)}')
        if skipped:
            print(f'  Skipped: {", ".join(skipped)}')

if __name__ == '__main__':
    main()