""" One-time migration: move every melody's .bsm binary off Firebase Storage and onto local disk, updating melody.url to point at our own plain-HTTP download endpoint instead of the Firebase public URL. Background: ESP32 devices can't afford the RAM for a TLS client, so melody binaries are now served from ./storage/melody_binaries via GET /api/melodies/download/{pid} (exposed publicly as melodies.bellsystems.net/download/{pid}) instead of Firebase Storage's HTTPS-only URLs. This script backfills existing melodies created before that change — melodies created/edited after the change already use the new path automatically (via "Select Archetype" / "Build on the Fly"). What this script does, for every melody whose url points at Firebase Storage: 1. Downloads the .bsm bytes from the Firebase Storage URL 2. Writes them to ./storage/melody_binaries/{pid}.bsm 3. Updates melody.url -> http://melodies.bellsystems.net/download/{pid} 4. Updates both SQLite (melody_drafts) and Firestore (if published) Storage is keyed on pid, not melody uid: pid identifies the underlying archetype binary, and multiple melodies legitimately share one pid (each remaps the same note sequence to different bells/speed/duration). The script downloads each distinct pid's binary only once (from whichever melody it encounters first) and reuses it for every other melody sharing that pid. CAVEAT: some existing melodies share a pid despite pointing at *different* Firebase source files (observed in practice — e.g. voice-count variants like 1N_/2N_/3N_/4N_-prefixed filenames all filed under one pid). This script does NOT attempt to detect or fix that — it flags it in the output (see "WARNING: also seen with a different source file") so you can review and re-assign the correct archetype per melody afterward using the in-app playback button. This is a pre-existing PID data issue, not something safe to silently resolve here. Melodies with no pid are skipped and reported, since the new URL scheme requires one (pid is the public lookup key for the download route). Run from the backend/ directory (or scripts/ — it searches upward for .env): docker exec -it bellsystems-backend python scripts/migrate_melody_binaries_to_local.py --dry-run docker exec -it bellsystems-backend python scripts/migrate_melody_binaries_to_local.py Requires: firebase-admin, requests (both already in the backend image). """ import argparse import json import os import sqlite3 import sys from pathlib import Path import requests # Melody names may contain Greek/non-ASCII text — force UTF-8 stdout so this # doesn't crash on Windows consoles defaulting to cp1252. if hasattr(sys.stdout, "reconfigure"): sys.stdout.reconfigure(encoding="utf-8", errors="replace") # --------------------------------------------------------------------------- # .env loader — searches upward from script location for a .env file # --------------------------------------------------------------------------- def _load_env() -> dict: search = Path(__file__).resolve().parent for _ in range(4): env_file = search / ".env" if env_file.exists(): result = {} for line in env_file.read_text(encoding="utf-8").splitlines(): line = line.strip() if not line or line.startswith("#") or "=" not in line: continue key, _, val = line.partition("=") result[key.strip()] = val.strip().strip('"').strip("'") print(f"[INFO] Loaded config from {env_file}") return result search = search.parent print("[WARN] No .env file found — relying on environment variables") return {} _env = _load_env() def _cfg(key: str, default: str = "") -> str: return _env.get(key) or os.environ.get(key) or default # --------------------------------------------------------------------------- # Firebase (only needed to read Firestore for published melodies) # --------------------------------------------------------------------------- try: import firebase_admin from firebase_admin import credentials, firestore _fb_app = None def _init_firebase(sa_path: str, bucket_name: str): global _fb_app if _fb_app is not None: return cred = credentials.Certificate(sa_path) _fb_app = firebase_admin.initialize_app(cred, {"storageBucket": bucket_name}) def get_firestore(sa_path: str, bucket_name: str): _init_firebase(sa_path, bucket_name) return firestore.client() FIREBASE_AVAILABLE = True except Exception as _fb_err: print(f"[WARN] Firebase unavailable: {_fb_err}") FIREBASE_AVAILABLE = False def get_firestore(sa_path: str, bucket_name: str): return None def _is_firebase_url(url: str) -> bool: return bool(url) and ("firebasestorage" in url or "storage.googleapis.com" in url) # --------------------------------------------------------------------------- # Main migration # --------------------------------------------------------------------------- def run(dry_run: bool = False, db_path: str = "", storage_dir: str = "", base_url: str = ""): label = "[DRY-RUN]" if dry_run else "[LIVE]" db_path = db_path or _cfg("SQLITE_DB_PATH", "./data/database.db") storage_dir = Path(storage_dir or _cfg("MELODY_BINARIES_STORAGE_PATH", "./storage/melody_binaries")) base_url = base_url or _cfg("MELODY_DOWNLOAD_BASE_URL", "http://melodies.bellsystems.net/download") sa_path = _cfg("FIREBASE_SERVICE_ACCOUNT_PATH", "./firebase-service-account.json") bucket_name = _cfg("FIREBASE_STORAGE_BUCKET") print(f"\n{label} Database: {db_path}") print(f"{label} Local binary storage: {storage_dir}") print(f"{label} New base URL: {base_url}\n") firestore_db = get_firestore(sa_path, bucket_name) if FIREBASE_AVAILABLE and bucket_name else None con = sqlite3.connect(db_path) con.row_factory = sqlite3.Row rows = con.execute("SELECT * FROM melody_drafts").fetchall() candidates = [] for row in rows: data = json.loads(row["data"]) if _is_firebase_url(data.get("url", "")): candidates.append((dict(row), data)) if not candidates: print("No melodies with a Firebase Storage url found. Nothing to do.") con.close() return print(f"Found {len(candidates)} melody(ies) with a Firebase Storage binary url:\n") if not dry_run: storage_dir.mkdir(parents=True, exist_ok=True) migrated = 0 skipped_no_pid = 0 failed = 0 downloaded_pids: dict[str, bool] = {} # pid -> whether the local file is ready to use pid_source_urls: dict[str, str] = {} # pid -> first-seen Firebase source url, to detect mismatches mismatch_warnings = [] for row, data in candidates: melody_id = row["id"] pid = data.get("pid") old_url = data.get("url") name = (data.get("information") or {}).get("name", "") label_id = f"[{melody_id[:8]}]" if not pid: print(f" {label_id} SKIPPED: no pid set — the new download route requires one (name: {name})") skipped_no_pid += 1 continue print(f" {label_id} {name!r} pid={pid}") print(f" {old_url}") first_seen_url = pid_source_urls.get(pid) if first_seen_url and first_seen_url != old_url: warning = (f"pid '{pid}': melody {label_id} points at a DIFFERENT Firebase file " f"than the one already saved for this pid — only the first one encountered " f"is kept as {pid}.bsm. Verify with the playback button after migrating.") print(f" WARNING: also seen with a different source file! {warning}") mismatch_warnings.append(warning) else: pid_source_urls[pid] = old_url dest = storage_dir / f"{pid}.bsm" new_url = f"{base_url}/{pid}" if dry_run: if pid in downloaded_pids: print(f" -> pid already handled by another melody in this run, would reuse {dest}") else: print(f" -> would download and save to {dest}") print(f" -> would set url = {new_url}") downloaded_pids[pid] = True continue if pid not in downloaded_pids: try: resp = requests.get(old_url, timeout=30) resp.raise_for_status() except Exception as e: print(f" ERROR downloading binary: {e}") failed += 1 continue dest.write_bytes(resp.content) print(f" saved {len(resp.content)} bytes -> {dest}") downloaded_pids[pid] = True else: print(f" reusing already-downloaded {dest} (shared pid)") print(f" url -> {new_url}") data["url"] = new_url con.execute( "UPDATE melody_drafts SET data=? WHERE id=?", (json.dumps(data), melody_id), ) con.commit() if row["status"] == "published": if firestore_db: try: firestore_db.collection("melodies").document(melody_id).update({"url": new_url}) print(f" Firestore updated") except Exception as e: print(f" ERROR updating Firestore: {e}") else: print(f" WARNING: melody is published but Firestore unavailable!") migrated += 1 con.close() print(f"\n{'-'*60}") print(f"{label} Done. Migrated: {migrated}, skipped (no pid): {skipped_no_pid}, failed: {failed}") if mismatch_warnings: print(f"\n{len(mismatch_warnings)} pid(s) had melodies pointing at different source files " f"— please review these with the playback button:") for w in mismatch_warnings: print(f" - {w}") if dry_run: print("\nThis was a dry run. No changes were made. Run without --dry-run to apply.") if __name__ == "__main__": parser = argparse.ArgumentParser( description="Migrate melody .bsm binaries from Firebase Storage to local disk" ) parser.add_argument("--dry-run", action="store_true", help="Preview changes without writing anything") parser.add_argument("--db", default="", help="Override SQLite database path (default: read from .env)") parser.add_argument("--storage-dir", default="", help="Override local binary storage dir (default: read from .env)") parser.add_argument("--base-url", default="", help="Override download base URL (default: read from .env)") args = parser.parse_args() run(dry_run=args.dry_run, db_path=args.db, storage_dir=args.storage_dir, base_url=args.base_url)