dewey-oracle/app/migrations/add_multi_user.py

"""
Migration script to add multi-user support.

This script:
1. Creates the User table
2. Creates a default admin user from environment variables
3. Migrates existing ListeningSession and Recommendation data to the default user
"""

import os
import sys
import asyncio
from datetime import datetime
from sqlalchemy import text, inspect
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker

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

from models import Base, User
from config import get_settings


async def run_migration():
    """Run the multi-user migration."""
    settings = get_settings()

    # Convert sqlite:/// to sqlite+aiosqlite:///
    db_url = settings.database_url.replace("sqlite:///", "sqlite+aiosqlite:///")

    engine = create_async_engine(db_url, echo=True)
    async_session = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

    async with engine.begin() as conn:
        # Check if migration has already been run
        result = await conn.execute(text(
            "SELECT name FROM sqlite_master WHERE type='table' AND name='users'"
        ))
        if result.fetchone():
            print("Migration already run - users table exists")
            return

        print("Starting multi-user migration...")

        # Step 1: Backup existing data
        print("Backing up existing listening_sessions...")
        sessions_backup = await conn.execute(text(
            "SELECT * FROM listening_sessions"
        ))
        sessions_data = sessions_backup.fetchall()
        sessions_columns = sessions_backup.keys()

        print("Backing up existing recommendations...")
        recs_backup = await conn.execute(text(
            "SELECT * FROM recommendations"
        ))
        recs_data = recs_backup.fetchall()
        recs_columns = recs_backup.keys()

        # Step 2: Create User table
        print("Creating users table...")
        await conn.execute(text("""
            CREATE TABLE users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username VARCHAR NOT NULL UNIQUE,
                email VARCHAR NOT NULL UNIQUE,
                hashed_password VARCHAR NOT NULL,
                abs_url VARCHAR NOT NULL,
                abs_api_token VARCHAR NOT NULL,
                display_name VARCHAR,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                last_login DATETIME,
                is_active BOOLEAN DEFAULT 1
            )
        """))

        await conn.execute(text(
            "CREATE INDEX ix_users_username ON users (username)"
        ))
        await conn.execute(text(
            "CREATE INDEX ix_users_email ON users (email)"
        ))

        # Step 3: Create default admin user
        print("Creating default admin user...")

        # Get credentials from environment
        abs_url = settings.abs_url if hasattr(settings, 'abs_url') else os.getenv('ABS_URL', '')
        abs_token = settings.abs_api_token if hasattr(settings, 'abs_api_token') else os.getenv('ABS_API_TOKEN', '')

        if not abs_url or not abs_token:
            print("WARNING: No ABS_URL or ABS_API_TOKEN found in environment")
            print("Creating placeholder admin user - update credentials in settings")
            abs_url = "http://localhost:13378"
            abs_token = "PLACEHOLDER_TOKEN"

        # For now, use a simple hash - will be replaced with proper bcrypt later
        # Password is "admin123" - user should change this immediately
        from passlib.hash import bcrypt
        default_password = bcrypt.hash("admin123")

        await conn.execute(text("""
            INSERT INTO users
            (username, email, hashed_password, abs_url, abs_api_token, display_name, created_at, is_active)
            VALUES
            (:username, :email, :password, :abs_url, :abs_token, :display_name, :created_at, :is_active)
        """), {
            "username": "admin",
            "email": "admin@localhost",
            "password": default_password,
            "abs_url": abs_url,
            "abs_token": abs_token,
            "display_name": "Admin User",
            "created_at": datetime.now(),
            "is_active": True
        })

        admin_id = (await conn.execute(text("SELECT last_insert_rowid()"))).scalar()
        print(f"Created admin user with ID: {admin_id}")

        # Step 4: Drop and recreate listening_sessions table
        print("Recreating listening_sessions table with user_id...")
        await conn.execute(text("DROP TABLE listening_sessions"))

        await conn.execute(text("""
            CREATE TABLE listening_sessions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                book_id VARCHAR NOT NULL,
                progress FLOAT DEFAULT 0.0,
                current_time FLOAT DEFAULT 0.0,
                is_finished BOOLEAN DEFAULT 0,
                started_at DATETIME,
                finished_at DATETIME,
                last_update DATETIME DEFAULT CURRENT_TIMESTAMP,
                rating INTEGER,
                FOREIGN KEY (user_id) REFERENCES users (id)
            )
        """))

        await conn.execute(text(
            "CREATE INDEX ix_listening_sessions_user_id ON listening_sessions (user_id)"
        ))

        # Step 5: Migrate listening_sessions data
        if sessions_data:
            print(f"Migrating {len(sessions_data)} listening sessions to admin user...")
            for row in sessions_data:
                row_dict = dict(zip(sessions_columns, row))
                await conn.execute(text("""
                    INSERT INTO listening_sessions
                    (user_id, book_id, progress, current_time, is_finished,
                     started_at, finished_at, last_update, rating)
                    VALUES
                    (:user_id, :book_id, :progress, :current_time, :is_finished,
                     :started_at, :finished_at, :last_update, :rating)
                """), {
                    "user_id": admin_id,
                    "book_id": row_dict.get("book_id"),
                    "progress": row_dict.get("progress", 0.0),
                    "current_time": row_dict.get("current_time", 0.0),
                    "is_finished": row_dict.get("is_finished", False),
                    "started_at": row_dict.get("started_at"),
                    "finished_at": row_dict.get("finished_at"),
                    "last_update": row_dict.get("last_update"),
                    "rating": row_dict.get("rating")
                })

        # Step 6: Drop and recreate recommendations table
        print("Recreating recommendations table with user_id...")
        await conn.execute(text("DROP TABLE recommendations"))

        await conn.execute(text("""
            CREATE TABLE recommendations (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                title VARCHAR NOT NULL,
                author VARCHAR,
                description TEXT,
                reason TEXT,
                genres VARCHAR,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                dismissed BOOLEAN DEFAULT 0,
                FOREIGN KEY (user_id) REFERENCES users (id)
            )
        """))

        await conn.execute(text(
            "CREATE INDEX ix_recommendations_user_id ON recommendations (user_id)"
        ))

        # Step 7: Migrate recommendations data
        if recs_data:
            print(f"Migrating {len(recs_data)} recommendations to admin user...")
            for row in recs_data:
                row_dict = dict(zip(recs_columns, row))
                await conn.execute(text("""
                    INSERT INTO recommendations
                    (user_id, title, author, description, reason, genres, created_at, dismissed)
                    VALUES
                    (:user_id, :title, :author, :description, :reason, :genres, :created_at, :dismissed)
                """), {
                    "user_id": admin_id,
                    "title": row_dict.get("title"),
                    "author": row_dict.get("author"),
                    "description": row_dict.get("description"),
                    "reason": row_dict.get("reason"),
                    "genres": row_dict.get("genres"),
                    "created_at": row_dict.get("created_at"),
                    "dismissed": row_dict.get("dismissed", False)
                })

        print("Migration completed successfully!")
        print("\nDEFAULT ADMIN CREDENTIALS:")
        print("  Username: admin")
        print("  Password: admin123")
        print("  Email: admin@localhost")
        print("\nIMPORTANT: Change the admin password after first login!")

    await engine.dispose()


if __name__ == "__main__":
    asyncio.run(run_migration())