scripts/migrate_users.py

#!/usr/bin/env python3
"""
migrate_users.py

Migrates users from SQLite (users.db) to PostgreSQL (TimescaleDB).
SAFE operation: Reads from SQLite, Inserts into Postgres. Does not delete SQLite data.
"""

import sqlite3
import psycopg2
import os
import sys
import logging
from datetime import datetime

# Set up logging
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)

# Paths
SQLITE_DB_PATH = 'users.db'

# Configuration (mirrors setup_timescaledb.py logic)
def get_postgres_connection():
    try:
        sys.path.insert(0, os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))
        try:
            import config
            host = getattr(config, 'DB_HOST', 'localhost')
            port = getattr(config, 'DB_PORT', 5432)
            dbname = getattr(config, 'DB_NAME', 'morphguard')
            user = getattr(config, 'DB_USER', 'morphguard')
            password = getattr(config, 'DB_PASSWORD', 'morphguard')
        except ImportError:
            # Fallback to env vars or strict defaults matching deploy scripts
            host = os.environ.get('MORPHGUARD_DB_HOST', 'localhost')
            port = int(os.environ.get('MORPHGUARD_DB_PORT', 5432))
            dbname = os.environ.get('MORPHGUARD_DB_NAME', 'morphguard')
            user = os.environ.get('MORPHGUARD_DB_USER', 'morphguard')
            password = os.environ.get('MORPHGUARD_DB_PASS', 'morphguard')

        logger.info(f"Connecting to Postgres at {host}:{port} db={dbname} user={user}...")
        conn = psycopg2.connect(
            host=host, port=port, dbname=dbname, user=user, password=password
        )
        return conn
    except Exception as e:
        logger.error(f"Failed to connect to Postgres: {e}")
        return None

def migrate():
    if not os.path.exists(SQLITE_DB_PATH):
        logger.error(f"SQLite database not found at {SQLITE_DB_PATH}")
        return

    # Connect to SQLite
    try:
        sqlite_conn = sqlite3.connect(SQLITE_DB_PATH)
        sqlite_conn.row_factory = sqlite3.Row
        sqlite_cur = sqlite_conn.cursor()
        logger.info("Connected to SQLite")
    except Exception as e:
        logger.error(f"Failed to connect to SQLite: {e}")
        return

    # Connect to Postgres
    pg_conn = get_postgres_connection()
    if not pg_conn:
        return
    pg_cur = pg_conn.cursor()

    try:
        # Fetch all users from SQLite
        sqlite_cur.execute("SELECT * FROM users")
        users = sqlite_cur.fetchall()
        logger.info(f"Found {len(users)} users in SQLite database.")

        count = 0
        for user in users:
            # Map fields
            # SQLite: id, username, password_hash, role, email, created_at (REAL), last_login (REAL), active (INT)
            # Postgres: id (SERIAL), username, password_hash, role, email, created_at (TIMESTAMPTZ), last_login (TIMESTAMPTZ), active (BOOL)
            
            username = user['username']
            
            # Check if user exists in Postgres
            pg_cur.execute("SELECT id FROM users WHERE username = %s", (username,))
            if pg_cur.fetchone():
                logger.info(f"Skipping existing user: {username}")
                continue

            password_hash = user['password_hash']
            role = user['role']
            email = user['email'] if 'email' in user.keys() else None
            
            # Handle timestamps (SQLite stores as float timestamp)
            created_at_ts = user['created_at']
            last_login_ts = user['last_login']
            
            created_at = datetime.fromtimestamp(created_at_ts) if created_at_ts else datetime.now()
            last_login = datetime.fromtimestamp(last_login_ts) if last_login_ts else None
            
            active = bool(user['active'])

            # Insert into Postgres
            pg_cur.execute("""
                INSERT INTO users (username, password_hash, role, email, created_at, last_login, active)
                VALUES (%s, %s, %s, %s, %s, %s, %s)
            """, (username, password_hash, role, email, created_at, last_login, active))
            
            count += 1
        
        pg_conn.commit()
        logger.info(f"Successfully migrated {count} users.")

    except Exception as e:
        pg_conn.rollback()
        logger.error(f"Migration failed: {e}")
    finally:
        sqlite_conn.close()
        pg_conn.close()

if __name__ == "__main__":
    migrate()