#!/usr/bin/env python3
import datetime
import hashlib
import json
import shutil
import sqlite3
import subprocess
import sys
from pathlib import Path

if len(sys.argv) != 2:
    raise SystemExit("usage: bind_plain_batch_on_200.py verified.json")

ROOT = Path("/home/vk/analytics/streamlit/radio/uploads/suno")
WORK = ROOT / "imports/black-hood-run-20261024"
verified_path = Path(sys.argv[1]).resolve()
rows = json.loads(verified_path.read_text())
metadata = json.loads((WORK / "full_metadata_correct.json").read_text())
db_path = ROOT / "db/tracks.sqlite"
backup_path = WORK / "tracks.before-plain-bind.sqlite"

def sha(path):
    h = hashlib.sha256()
    with path.open("rb") as stream:
        for chunk in iter(lambda: stream.read(1024 * 1024), b""):
            h.update(chunk)
    return h.hexdigest()

prepared = []
for row in rows:
    uid = row["id"]
    source = WORK / "plain" / f"{uid}.m4a"
    if not source.is_file() or sha(source) != row["sha256"]:
        raise RuntimeError(f"staging hash mismatch: {uid}")
    probe = subprocess.run(["ffprobe", "-v", "error", "-select_streams", "a:0", "-show_entries", "stream=codec_name", "-of", "csv=p=0", str(source)], capture_output=True, text=True)
    if probe.returncode or probe.stdout.strip() != "opus":
        raise RuntimeError(f"invalid staged audio: {uid}")
    target = ROOT / "audios" / f"{uid}.m4a"
    if target.exists() and sha(target) != row["sha256"]:
        raise RuntimeError(f"different canonical file exists: {uid}")
    if not target.exists():
        shutil.copy2(source, target)
    prepared.append((row, target))

connection = sqlite3.connect(db_path, timeout=30)
connection.row_factory = sqlite3.Row
if not backup_path.exists():
    backup = sqlite3.connect(backup_path)
    with backup:
        connection.backup(backup)
    backup.close()
    backup_path.chmod(0o600)

before = connection.execute("SELECT count(*) FROM tracks").fetchone()[0]
actions = []
connection.execute("BEGIN IMMEDIATE")
try:
    for row, target in prepared:
        uid = row["id"]
        api = metadata.get(uid) or {}
        old = connection.execute("SELECT * FROM tracks WHERE id=?", (uid,)).fetchone()
        archive = {
            "batch": "black-hood-run-20261024", "state": "plain_ready",
            "encrypted_path": str(ROOT / "encrypted" / f"{uid}.m4a.enc") if (ROOT / "encrypted" / f"{uid}.m4a.enc").is_file() else None,
            "plain_path": str(target), "plain_sha256": row["sha256"],
            "plain_bytes": row["bytes"], "codec": "opus", "decode_verified": True,
            "discovery_manifest": str(WORK / "discovery.json"),
        }
        if old is None:
            meta = dict(api); meta["archive"] = archive
            connection.execute("""INSERT INTO tracks
                (id,title,created_at,model_name,audio_url,file_path,image_url,image_path,tags,status,lyric,meta_json,added_at,hidden)
                VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,0)""", (
                uid, api.get("title"), api.get("created_at"), api.get("model_name"),
                f"https://media.voxxvaler.com/suno/audios/{uid}.m4a", target.name,
                api.get("image_url"), None, api.get("tags"), api.get("status"), api.get("lyric"),
                json.dumps(meta, ensure_ascii=False), datetime.datetime.now(datetime.timezone.utc).isoformat(),
            ))
            action = "inserted"
        else:
            meta = json.loads(old["meta_json"] or "{}"); meta["archive"] = archive
            fields = {
                "file_path": target.name,
                "audio_url": f"https://media.voxxvaler.com/suno/audios/{uid}.m4a",
                "meta_json": json.dumps(meta, ensure_ascii=False),
            }
            for key in ("created_at", "model_name", "image_url", "tags", "status", "lyric"):
                if not old[key] and api.get(key): fields[key] = api[key]
            connection.execute("UPDATE tracks SET " + ",".join(k + "=?" for k in fields) + " WHERE id=?", (*fields.values(), uid))
            action = "updated_preserving_editorial"
        actions.append({"id": uid, "action": action, "file_path": target.name})
    connection.commit()
except BaseException:
    connection.rollback(); raise
after = connection.execute("SELECT count(*) FROM tracks").fetchone()[0]
connection.close()
result = {"before": before, "after": after, "bound": len(actions), "actions": actions}
out = verified_path.with_name(verified_path.stem + "-db-result.json")
out.write_text(json.dumps(result, ensure_ascii=False, indent=2))
print(json.dumps({"before": before, "after": after, "bound": len(actions), "output": str(out)}, ensure_ascii=False))
