db_utils.py

# db_utils.py
import aiosqlite
import logging
from datetime import datetime, timezone
from typing import Optional

from config import DATABASE_NAME

logger = logging.getLogger("db_utils")

# --- Initialization ---
async def initialize_database():
    """Sets up all necessary tables in the database."""
    async with aiosqlite.connect(DATABASE_NAME) as db:
        await db.execute("""
            CREATE TABLE IF NOT EXISTS users (
                user_id INTEGER PRIMARY KEY,
                username TEXT,
                first_name TEXT,
                last_seen TIMESTAMP NOT NULL,
                is_active INTEGER DEFAULT 1
            )
        """)
        await db.commit()
        logger.info(f"All tables initialized in database '{DATABASE_NAME}'.")

# --- User Tracking Functions ---
async def add_or_update_user_db(user_id: int, username: Optional[str], first_name: Optional[str]):
    """Add new user or update existing user's info and activity."""
    async with aiosqlite.connect(DATABASE_NAME) as db:
        await db.execute(
            """
            INSERT INTO users (user_id, username, first_name, last_seen, is_active)
            VALUES (?, ?, ?, ?, 1)
            ON CONFLICT(user_id) DO UPDATE SET
                username=excluded.username,
                first_name=excluded.first_name,
                last_seen=excluded.last_seen,
                is_active=1
            """,
            (user_id, username, first_name, datetime.now(timezone.utc))
        )
        await db.commit()