backend/scripts/init_db.py

"""
Database initialisation script — SQLite edition.

Usage (inside the backend container):
    python -m scripts.init_db

Creates all SQLAlchemy-managed tables (users, roles, user_roles,
query_logs, evaluation_results) and seeds the default roles.
Safe to re-run — create_all uses IF NOT EXISTS semantics; role
upsert uses merge() so duplicates are ignored.
"""
import logging
import os

from sqlalchemy import create_engine, select
from sqlalchemy.orm import Session

from app.config import get_settings
from app.models import Base, Role

logger = logging.getLogger(__name__)
logging.basicConfig(level=logging.INFO, format="%(levelname)s: %(message)s")


def init_db():
    settings = get_settings()
    # Convert async URL to sync URL for the init script
    sync_url = settings.database_url.replace("sqlite+aiosqlite", "sqlite")
    logger.info("Initialising database at: %s", sync_url)

    # Ensure the directory exists (e.g. /data on HF Spaces)
    db_path = sync_url.replace("sqlite:///", "")
    db_dir = os.path.dirname(db_path)
    if db_dir:
        os.makedirs(db_dir, exist_ok=True)

    engine = create_engine(sync_url, connect_args={"check_same_thread": False})
    Base.metadata.create_all(engine)
    logger.info("Tables created (or already exist).")

    # Seed default roles
    with Session(engine) as session:
        for role_name in ("researcher", "guest"):
            existing = session.scalar(select(Role).where(Role.name == role_name))
            if existing is None:
                session.add(Role(name=role_name))
                logger.info("Seeded role: %s", role_name)
            else:
                logger.info("Role already exists: %s", role_name)
        session.commit()

    logger.info("Database initialised successfully.")


if __name__ == "__main__":
    init_db()