#!/usr/bin/env bash
#
# Brings a Linux self-hosted server up to the current release, then applies any database scripts
# that go with it. Same protocol as the Windows launcher's ServerUpdater, same manifest, same
# signature - this is the half systemd hosts get, because they have no launcher window.
#
# USAGE
#   ./update.sh                 update, then apply database scripts
#   ./update.sh --check         say what would change, touch nothing
#
# WHERE IT BELONGS IN THE BOOT ORDER: before the services start. install.sh wires it as an
# ExecStartPre on zerog-server, so a host who only ever runs "systemctl start" still gets it.
# Running it while the services are up would replace binaries out from under them.
#
# THE THREE RULES, same as the launcher:
#   1. Never lose the host's data. Nothing outside the manifest is written or deleted, and the
#      PROTECTED list below is refused even if a manifest asks for it.
#   2. Never block the host. Every failure short of a bad signature leaves the install alone and
#      exits 0, so a server that was working keeps working when the update service is down.
#   3. Never apply something unsigned. The payload includes SQL that runs against the database.

set -uo pipefail

INSTALL_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
BASE_URL="${ZEROG_UPDATE_URL:-https://adventure.zerognova.com/updates}"
CHANNEL="server-linux"
STATE_FILE="$INSTALL_DIR/data/update-state.json"
SQL_DIR="$INSTALL_DIR/sql"
APPLIED_LOG="$SQL_DIR/applied.log"
VERSION_FILE="$INSTALL_DIR/version.txt"
STAGING="$INSTALL_DIR/data/update-staging"

# EVERYTHING THIS SCRIPT SAYS, ON DISK.
#
# It already prints to stdout, which systemd captures into the journal - but this runs as the
# service user, which usually cannot read "journalctl -u zerog-server" back. So a host looking for
# why their server will not start is told to read a log they have no permission to open, and a
# crash report has nothing to attach.
#
# The file is owned by the same user that writes it, in a directory that already belongs to this
# install, so both problems go away at once.
UPDATE_LOG="$INSTALL_DIR/data/update-last.log"

# Where automatic crash reports go. Overridable for testing against a local service, the same way
# the update URL is.
CRASH_ENDPOINT="${ZEROG_CRASH_URL:-https://adventure.zerognova.com/api/crash-report}"

# Remembers the last report so a server restarting every ten seconds does not send one every ten
# seconds. See send_crash_report.
CRASH_MARKER="$INSTALL_DIR/data/last-crash-report"
CRASH_COOLDOWN_SECONDS=900

CHECK_ONLY=0
[ "${1:-}" = "--check" ] && CHECK_ONLY=1

# Keeps the log from growing without limit on a server that restarts often. One previous copy is
# kept; nobody has ever needed the third-oldest.
rotate_update_log() {
    [ -f "$UPDATE_LOG" ] || return 0
    local size
    size=$(wc -c < "$UPDATE_LOG" 2>/dev/null || echo 0)
    [ "$size" -gt 1048576 ] 2>/dev/null || return 0
    mv -f "$UPDATE_LOG" "$UPDATE_LOG.old" 2>/dev/null || true
}

mkdir -p "$INSTALL_DIR/data" 2>/dev/null || true
rotate_update_log
printf '\n===== update.sh %s =====\n' "$(date -u '+%Y-%m-%d %H:%M:%S')Z" >> "$UPDATE_LOG" 2>/dev/null || true

# Stdout for the journal, file for the host and for crash reports. Never fails the script: a log
# that cannot be written is not a reason to refuse to start a server.
say() {
    printf '%s\n' "$*"
    printf '%s %s\n' "$(date -u '+%H:%M:%S')" "$*" >> "$UPDATE_LOG" 2>/dev/null || true
}

die_soft() { say "$*"; say "Starting with the version already installed."; exit 0; }

# Sends a crash report with the tail of this log, redacted.
#
# WHY LINUX NEEDS THIS MORE THAN WINDOWS DID. The unit is Restart=always with RestartSec=10s, so a
# server that cannot start does not stop - it retries every ten seconds, forever, on a machine
# nobody is watching. On Windows a host at least sees a dialog. Here the failure is completely
# silent unless somebody thinks to read the journal.
#
# THE COOLDOWN IS THE IMPORTANT PART, for exactly that reason. Without it a restart loop would post
# 360 reports an hour, of which the service would keep twelve and drop the rest - all saying the
# same thing. One report per distinct failure per 15 minutes says everything the flood would.
#
# NEVER AFFECTS THE OUTCOME. No exit status of this function reaches the caller, nothing waits on
# it beyond a short timeout, and a host with no network simply loses the report. A reporter that
# can delay or block a server starting is worse than no reporter.
send_crash_report() {
    local source="$1"
    local error="$2"

    [ -n "${ZEROG_DISABLE_CRASH_REPORT:-}" ] && return 0
    command -v python3 >/dev/null 2>&1 || return 0
    command -v curl >/dev/null 2>&1 || return 0

    # One report per distinct failure per cooldown. Keyed on the source and the first line of the
    # error, so a message carrying a changing path or timestamp still counts as the same failure.
    local key now last
    key="$source|$(printf '%s' "$error" | head -1 | tr -cd '[:alnum:] ._-' | cut -c1-120)"
    now=$(date +%s)

    if [ -f "$CRASH_MARKER" ]; then
        last="$(head -1 "$CRASH_MARKER" 2>/dev/null || echo 0)"
        if [ "$(tail -n +2 "$CRASH_MARKER" 2>/dev/null || true)" = "$key" ]; then
            case "$last" in
                ''|*[!0-9]*) last=0 ;;
            esac
            [ $((now - last)) -lt "$CRASH_COOLDOWN_SECONDS" ] && return 0
        fi
    fi

    { printf '%s\n%s\n' "$now" "$key" > "$CRASH_MARKER"; } 2>/dev/null || true

    local payload="$STAGING/crash.json"
    mkdir -p "$(dirname "$payload")" 2>/dev/null || true

    # THE REDACTION MATCHES THE WINDOWS LAUNCHER'S, deliberately: the same rules, so a report from
    # either platform is readable the same way and neither carries more than the other.
    #
    # Routable addresses, join codes and the account name go. Private addresses stay - which
    # address a server bound to is often the whole story, and identifies nobody.
    python3 - "$UPDATE_LOG" "$VERSION_FILE" "$source" "$error" "$payload" <<'PY' 2>/dev/null || return 0
import json, os, re, sys

log_path, version_path, source, error, out_path = sys.argv[1:6]

def read_tail(path, limit=120 * 1024):
    try:
        with open(path, "r", encoding="utf-8", errors="replace") as fh:
            fh.seek(0, os.SEEK_END)
            size = fh.tell()
            fh.seek(max(0, size - limit))
            return fh.read()
    except Exception:
        return "(no update log)"

def is_private(address):
    parts = address.split(".")
    try:
        a, b = int(parts[0]), int(parts[1])
    except Exception:
        return False
    if a in (10, 127, 0):
        return True
    if a == 192 and b == 168:
        return True
    if a == 172 and 16 <= b <= 31:
        return True
    return a == 169 and b == 254

def looks_like_ipv6(value):
    # A LOG IS MOSTLY TIMESTAMPS: "21:41:57" is valid hex with two colons, so redacting on shape
    # alone would eat the time from every line. Two colons is a clock; a real address either has
    # a "::" run or four or more colons. Loopback and link-local are kept, like private IPv4.
    if value.startswith("::1") or value.lower().startswith("fe80"):
        return False
    colons = value.count(":")
    return colons >= 2 if "::" in value else colons >= 4

def redact(text):
    try:
        user = os.environ.get("USER") or os.environ.get("LOGNAME") or ""
        if len(user) > 2:
            text = re.sub(re.escape(user), "<user>", text, flags=re.IGNORECASE)
        text = re.sub(r"\b[A-Z]{3,6}-[A-Z0-9]{2,4}-[A-Z0-9]{2,4}\b", "<join-code>", text)
        text = re.sub(r"\b\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}\b",
                      lambda m: m.group(0) if is_private(m.group(0)) else "<public-ip>", text)
        text = re.sub(r"(?<![\w:.])(?:[0-9A-Fa-f]{0,4}:){2,7}[0-9A-Fa-f]{0,4}(?![\w:.])",
                      lambda m: "<public-ip6>" if looks_like_ipv6(m.group(0)) else m.group(0), text)
        return text
    except Exception:
        # A redaction that fails must not fall back to sending the original.
        return "(the log could not be redacted, so it was not sent)"

version, channel = "unknown", "server-linux"
try:
    with open(version_path, encoding="utf-8") as fh:
        for line in fh:
            if line.startswith("version="):
                version = line.split("=", 1)[1].strip()
            elif line.startswith("channel="):
                channel = line.split("=", 1)[1].strip()
except Exception:
    pass

try:
    with open("/etc/os-release", encoding="utf-8") as fh:
        platform = next((l.split("=", 1)[1].strip().strip('"')
                         for l in fh if l.startswith("PRETTY_NAME=")), "linux")
except Exception:
    platform = "linux"

payload = {
    "source": "server-linux/" + source,
    "version": version,
    "channel": channel,
    "platform": platform,
    "error": redact(error)[:8000],
    "log": redact(read_tail(log_path)),
}

with open(out_path, "w", encoding="utf-8") as fh:
    json.dump(payload, fh)
PY

    [ -f "$payload" ] || return 0

    curl -fsS --max-time 20 -X POST -H 'Content-Type: application/json' \
        --data-binary "@$payload" "$CRASH_ENDPOINT" >/dev/null 2>&1 || true

    rm -f "$payload" 2>/dev/null || true
    return 0
}

# die_soft, but the failure is worth hearing about. Used for the ones that mean something is
# WRONG - a bad signature, a manifest we cannot parse, a missing dependency - and not for a
# service that could not be reached, which is a host being offline and would report nothing
# anyway.
die_report() {
    send_crash_report "$1" "$2"
    die_soft "$2"
}

# Records the release now installed. Written after the files are in place, never before: the
# stamp is a claim about what is on disk, and an interrupted update must not leave the install
# describing itself as a release it never finished becoming.
stamp_version() {
    [ -n "${1:-}" ] || return 0
    {
        printf '# Written by the update service. Do not edit.\n'
        printf 'version=%s\n' "$1"
        printf 'built=%s\n' "$(date -u '+%Y-%m-%d %H:%M:%S')Z"
        printf 'channel=%s\n' "$CHANNEL"
    } > "$VERSION_FILE" 2>/dev/null || say "Could not write version.txt - the version shown may be out of date."
}

# Never written, never deleted. Belt and braces: the publisher does not put these in a manifest
# in the first place, but the cost of it being wrong once is somebody's world.
is_protected() {
    case "$1" in
        config.json|data/*|logs/*|server/server.env|gateway/server.env|sql/applied.log) return 0 ;;
        # This script owns version.txt and rewrites it below. A manifest listing it too would
        # leave the file permanently mismatched against its published hash, so every run would
        # download it, rewrite it, and find it stale again: an update that never finishes.
        version.txt) return 0 ;;
        *) return 1 ;;
    esac
}

for tool in curl openssl sha256sum python3; do
    command -v "$tool" >/dev/null 2>&1 || die_soft "update.sh needs '$tool' and it is not installed."
done

# The key updates are checked against. A dedicated update key is preferred so that rotating the
# token-signing key does not silently turn updates off; the JWT public key already in every
# package is the fallback, which is what lets an existing install take its first update.
PUBKEY=""
for candidate in "$INSTALL_DIR/update-public.pem" "$INSTALL_DIR/jwt-public.pem"; do
    [ -f "$candidate" ] && { PUBKEY="$candidate"; break; }
done
[ -n "$PUBKEY" ] || die_soft "No update key in this folder, so updates are turned off for this install."

mkdir -p "$INSTALL_DIR/data" "$SQL_DIR"
rm -rf "$STAGING"; mkdir -p "$STAGING"
trap 'rm -rf "$STAGING"' EXIT

say "Checking for updates..."

curl -fsSL --max-time 60 -o "$STAGING/manifest.json" "$BASE_URL/$CHANNEL/manifest.json" \
    || die_soft "The update service could not be reached."
curl -fsSL --max-time 60 -o "$STAGING/manifest.sig.b64" "$BASE_URL/$CHANNEL/manifest.sig" \
    || die_report "unsigned" "This update is not signed, so it was not applied."

# Verified over the EXACT bytes downloaded. Anything that rewrites them - a reformat, a line
# ending change - breaks this, which is why nothing here touches the file before now.
base64 -d < "$STAGING/manifest.sig.b64" > "$STAGING/manifest.sig" 2>/dev/null \
    || die_report "bad-signature" "The update signature was not readable."

if ! openssl dgst -sha256 -verify "$PUBKEY" -signature "$STAGING/manifest.sig" "$STAGING/manifest.json" >/dev/null 2>&1; then
    # Exits 0, deliberately. Nothing has been applied, so the install is exactly as it was and
    # there is no reason it cannot run. Exiting non-zero here would let anyone who can interfere
    # with the update endpoint stop every server in the world from starting - turning a tampering
    # attempt into an outage, which is a far better prize than the tampering was.
    say "This update did not come from the 0G Nova Adventure update service, so it was ignored."
    exit 0
fi

# One python pass reads the manifest and the ledger and prints the work list. Doing it in the
# shell would mean a JSON parser written in sed, which is exactly how these scripts rot.
PLAN="$(python3 - "$STAGING/manifest.json" "$STATE_FILE" "$CHANNEL" <<'PY'
import json, sys, os

manifest_path, state_path, expected_channel = sys.argv[1], sys.argv[2], sys.argv[3]

with open(manifest_path, encoding="utf-8") as fh:
    manifest = json.load(fh)

# A signature proves the manifest is ours; it does not prove it is for this platform.
if manifest.get("channel") != expected_channel:
    print("CHANNEL\t%s" % manifest.get("channel", "?"))
    raise SystemExit(0)

state = {}
if os.path.exists(state_path):
    try:
        with open(state_path, encoding="utf-8") as fh:
            state = json.load(fh)
    except Exception:
        state = {}

applied = {name.lower() for name in state.get("appliedSql", [])}
known = state.get("knownFiles", [])
current = {f["name"] for f in manifest.get("files", [])}

# A SERVER THAT HAS NEVER UPDATED IS ALREADY CURRENT, and must not replay history.
#
# It was installed from a package whose schema.sql is a dump of the live database taken AFTER every
# script published up to that release had run - so those changes are already in its tables. Running
# them again is at best wasted time and grows with every release forever.
#
# So on the FIRST run only, every script published at or before the version this install carries is
# marked applied without being fetched. "installedVersion" is what install.sh stamped from the
# package; a script published later is genuinely new and is applied normally.
#
# ONLY ON THE FIRST RUN, and that restriction is the important half. After this, the per-script
# record below is the only thing that decides - because the version is stamped BEFORE scripts are
# applied, so a filter that kept consulting it would permanently skip a script that failed once.
# The name record retries until it succeeds; the version only ever sets the starting line.
first_run = "appliedSql" not in state

# THE SCHEMA'S OWN VERSION, not the release's. install.sh reads it out of schema.sql and writes it
# here, because it is the DUMP that decides which changes are already in these tables - a package
# can ship a schema taken well before the package was built.
#
# Absent means an unstamped schema, or an install from before stamping existed. Then nothing is
# seeded and every script is applied: slower, always correct, and the safe direction to be wrong.
schema_version = ""
try:
    schema_file = os.path.join(os.path.dirname(state_path), "schema-version.json")
    with open(schema_file, encoding="utf-8") as fh:
        schema_version = (json.load(fh).get("schemaVersion") or "").strip()
except Exception:
    schema_version = ""

seeded = []
if first_run and schema_version:
    for s in manifest.get("sqlScripts", []):
        # publishedIn is the release a script FIRST appeared in, and never changes afterwards.
        # Absent on a manifest published before the field existed - treated as pending, which is
        # again the safe direction: it re-runs, and every script is required to be idempotent.
        published_in = s.get("publishedIn") or ""
        if published_in and published_in <= schema_version:
            applied.add(s["name"].lower())
            seeded.append(s["name"])

for name in seeded:
    print("SEEDED\t%s" % name)

print("VERSION\t%s" % manifest.get("version", ""))
for f in manifest.get("files", []):
    print("FILE\t%s\t%s" % (f["name"], f["hash"]))
for s in manifest.get("sqlScripts", []):
    if s["name"].lower() not in applied:
        print("SQL\t%s\t%s" % (s["name"], s["hash"]))
for name in known:
    if name not in current:
        print("DROP\t%s" % name)
PY
)" || die_report "bad-manifest" "The update service sent something this version could not read."

if printf '%s' "$PLAN" | grep -q '^CHANNEL'; then
    say "The update service offered a '$(printf '%s' "$PLAN" | awk -F'\t' '/^CHANNEL/{print $2}')' package to a '$CHANNEL' server. Skipping."
    exit 0
fi

VERSION="$(printf '%s' "$PLAN" | awk -F'\t' '/^VERSION/{print $2}')"

# Compare hashes to find what actually differs. A version number is not consulted, which is what
# makes an interrupted update self-correcting: whatever is wrong is simply still wrong next time.
STALE=""
while IFS=$'\t' read -r kind name hash; do
    [ "$kind" = "FILE" ] || continue
    is_protected "$name" && continue
    local_file="$INSTALL_DIR/$name"
    if [ ! -f "$local_file" ] || [ "$(sha256sum "$local_file" | cut -d' ' -f1)" != "$hash" ]; then
        STALE="$STALE$name"$'\t'"$hash"$'\n'
    fi
done < <(printf '%s\n' "$PLAN")

PENDING_SQL="$(printf '%s\n' "$PLAN" | awk -F'\t' '/^SQL/{print $2"\t"$3}')"

# Scripts this install did not need because the schema it was built from already contains them.
# They are RECORDED as applied further down - without that, "appliedSql" would still be missing on
# the next run, the seed would be recomputed, and the moment a newer package moved the version
# forward they would all look pending again.
SEEDED_SQL="$(printf '%s\n' "$PLAN" | awk -F'\t' '/^SEEDED/{print $2}')"
SEEDED_COUNT=$(printf '%s' "$SEEDED_SQL" | grep -c . || true)

if [ "$SEEDED_COUNT" -gt 0 ]; then
    say "$SEEDED_COUNT database script(s) already included in this install's schema - not re-applying."

    # WRITTEN TO applied.log TOO, matching what the Windows launcher records as "IN SCHEMA".
    #
    # Without this a fresh install has an EMPTY applied.log, which reads as "no script has ever
    # run here" - indistinguishable from a server whose updates are broken. The whole point of
    # that file is that somebody can open it and see what happened, and "these were already in
    # the schema, so they were skipped" is part of what happened.
    mkdir -p "$SQL_DIR" 2>/dev/null || true
    printf '%s\n' "$SEEDED_SQL" | while IFS= read -r seeded_name; do
        [ -n "$seeded_name" ] || continue
        printf '[%s] IN SCHEMA %s\n' \
            "$(date -u '+%Y-%m-%d %H:%M:%S')Z" "$seeded_name" >> "$APPLIED_LOG" 2>/dev/null || true
    done
fi

STALE_COUNT=$(printf '%s' "$STALE" | grep -c . || true)
SQL_COUNT=$(printf '%s' "$PENDING_SQL" | grep -c . || true)

# SCRIPTS ALREADY SITTING IN sql/ COUNT AS WORK TO DO, even when the release is current.
#
# sql/README.txt promises hosts two things: that a script left there after a start is one that
# FAILED, and that they may drop their own .sql in and have it "treated exactly the same way".
# Both promises need this run to reach the apply loop below - and the early exit skipped it
# whenever nothing else had changed, which is precisely the state a host is in when they drop a
# file in and restart. A failed script was likewise only retried if some unrelated release
# happened to appear.
#
# The Windows launcher has always run its apply step unconditionally. This makes the two agree.
LOCAL_SQL=0
ls "$SQL_DIR"/*.sql >/dev/null 2>&1 && LOCAL_SQL=1

if [ "$STALE_COUNT" -eq 0 ] && [ "$SQL_COUNT" -eq 0 ] && [ "$LOCAL_SQL" -eq 0 ]; then
    say "Up to date ($VERSION)."

    # Repairs the stamp as well as confirming it. Every file already matches the current manifest,
    # so this install IS that release however it got here - including one unpacked before version
    # stamping existed, and one whose stamp somebody deleted.
    [ "$CHECK_ONLY" -eq 1 ] || stamp_version "$VERSION"
    exit 0
fi

if [ "$STALE_COUNT" -eq 0 ] && [ "$SQL_COUNT" -eq 0 ]; then
    say "Up to date ($VERSION) - applying database script(s) waiting in sql/."
else
    say "Update available: $VERSION ($STALE_COUNT file(s), $SQL_COUNT database script(s))."
fi

if [ "$CHECK_ONLY" -eq 1 ]; then
    printf '%s' "$STALE" | awk -F'\t' 'NF{print "  ~ "$1}'
    printf '%s' "$PENDING_SQL" | awk -F'\t' 'NF{print "  + sql/"$1}'
    exit 0
fi

# Everything is fetched and hash-checked BEFORE a single byte of the install is touched. A
# download that dies halfway then costs nothing: the install is still whole and the next run
# tries again.
while IFS=$'\t' read -r name hash; do
    [ -n "$name" ] || continue
    dest="$STAGING/files/$name"
    mkdir -p "$(dirname "$dest")"
    # AN IDLE TIMEOUT, NOT A TOTAL ONE. --max-time caps the whole transfer, so on a payload
    # this size it is really a cap on how SLOW the link may be: the gateway executable is
    # ~104 MB, which is 55 seconds at 1.9 MB/s and ten minutes on a poor connection that is
    # working perfectly well. --speed-time/--speed-limit abort a transfer that has genuinely
    # stalled (under 1 KB/s for 90 seconds) and let a slow one finish, which matches the
    # StallTimeout the Windows updater uses.
    curl -fsSL --speed-time 90 --speed-limit 1024 -o "$dest" "$BASE_URL/$CHANNEL/files/$name" \
        || die_soft "Could not download $name. The update was not applied."
    [ "$(sha256sum "$dest" | cut -d' ' -f1)" = "$hash" ] \
        || die_report "hash-mismatch" "$name did not match its signed hash. The update was not applied."

# '%s\n', NOT '%s'. Command substitution strips trailing newlines, so the variable's last line
# arrives with none - and "read" returns non-zero at end of input, which means the loop body never
# runs for it. The LAST entry of every list was silently skipped: the last file of every update
# never downloaded, and with a single pending database script, nothing happened at all.
#
# Found on 2026-09-09 on a fresh Linux install that reported "1 database script(s)" and then
# applied none. The two loops over $PLAN had it right all along; these three did not.
done < <(printf '%s\n' "$STALE")

while IFS=$'\t' read -r name hash; do
    [ -n "$name" ] || continue
    dest="$STAGING/sql/$name"
    mkdir -p "$(dirname "$dest")"
    curl -fsSL --max-time 120 -o "$dest" "$BASE_URL/sql/$name" \
        || die_soft "Could not download database script $name. The update was not applied."
    [ "$(sha256sum "$dest" | cut -d' ' -f1)" = "$hash" ] \
        || die_report "sql-hash-mismatch" "$name did not match its signed hash. The update was not applied."
done < <(printf '%s\n' "$PENDING_SQL")

INSTALLED=0
while IFS=$'\t' read -r name hash; do
    [ -n "$name" ] || continue
    dest="$INSTALL_DIR/$name"
    mkdir -p "$(dirname "$dest")"
    # Preserve the executable bit the publisher could not carry: a mode is not in the manifest,
    # and a server binary that arrives without +x fails in a way nobody reads as "update problem".
    was_exec=0
    [ -x "$dest" ] && was_exec=1
    mv -f "$STAGING/files/$name" "$dest"
    [ "$was_exec" -eq 1 ] && chmod +x "$dest"
    INSTALLED=$((INSTALLED + 1))
done < <(printf '%s\n' "$STALE")

# The two the package cannot know it needs until it has them.
chmod +x "$INSTALL_DIR/server/ZeroGNovaAdventure.Server" 2>/dev/null || true
chmod +x "$INSTALL_DIR/gateway/ZeroGNovaAdventure.Gateway" 2>/dev/null || true

if [ "$SQL_COUNT" -gt 0 ]; then
    mkdir -p "$SQL_DIR"
    cp -f "$STAGING"/sql/*.sql "$SQL_DIR/" 2>/dev/null || true
fi

# Only files the PREVIOUS manifest listed are candidates for removal. A file this script never
# put there was put there by the host, and is none of its business.
REMOVED=0
while IFS=$'\t' read -r kind name; do
    [ "$kind" = "DROP" ] || continue
    is_protected "$name" && continue
    if [ -f "$INSTALL_DIR/$name" ]; then
        rm -f "$INSTALL_DIR/$name" && REMOVED=$((REMOVED + 1))
    fi
done < <(printf '%s\n' "$PLAN")

stamp_version "$VERSION"

say "Updated to $VERSION: $INSTALLED file(s) written, $REMOVED removed."

# ------------------------------------------------------------------ database scripts
#
# Applied in name order, then deleted, with the name written down twice: to applied.log for a
# human, and into the state file so the next run does not fetch and re-apply a script whose file
# is gone. That second record is the one correctness depends on.

CONN="$(python3 - "$INSTALL_DIR/server/server.env" <<'PY'
import sys, os, re
path = sys.argv[1]
if not os.path.exists(path):
    raise SystemExit(0)
values = {}
with open(path, encoding="utf-8") as fh:
    for line in fh:
        line = line.strip()
        if not line or line.startswith("#") or "=" not in line:
            continue
        k, v = line.split("=", 1)
        values[k.strip()] = v.strip()
if values.get("DB_CONNECTION_STRING"):
    raise SystemExit(0)   # handled by the server itself; too many shapes to parse safely here
host = values.get("DB_HOST", "localhost")
port = values.get("DB_PORT", "5432")
name = values.get("DB_NAME", "zerog_local")
user = values.get("DB_USER", "")
pw   = values.get("DB_PASS", "")
if not user:
    raise SystemExit(0)
print("%s\t%s\t%s\t%s\t%s" % (host, port, name, user, pw))
PY
)"

APPLIED_NAMES=""
if [ -n "$CONN" ] && ls "$SQL_DIR"/*.sql >/dev/null 2>&1; then
    IFS=$'\t' read -r DB_HOST DB_PORT DB_NAME DB_USER DB_PASS <<< "$CONN"
    export PGPASSWORD="$DB_PASS"

    for script in $(ls "$SQL_DIR"/*.sql | sort); do
        base="$(basename "$script")"
        say "  applying $base"
        # ON_ERROR_STOP so a broken script aborts rather than half-applying, exactly as the SQL
        # Helper does against live.
        if psql -v ON_ERROR_STOP=1 -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -f "$script" >/dev/null; then
            printf '[%s] APPLIED   %s\n' "$(date -u '+%Y-%m-%d %H:%M:%S')Z" "$base" >> "$APPLIED_LOG"
            APPLIED_NAMES="$APPLIED_NAMES$base"$'\n'
            rm -f "$script"
        else
            # Left on disk deliberately: retried next start, and there for the host to look at,
            # which is the only way anyone finds out what broke.
            printf '[%s] FAILED    %s\n' "$(date -u '+%Y-%m-%d %H:%M:%S')Z" "$base" >> "$APPLIED_LOG"
            say "DATABASE UPDATE FAILED: $base - left in $SQL_DIR, see $APPLIED_LOG"

            # THE ONE FAILURE HERE THAT STOPS THE SERVER STARTING, and therefore the one that
            # produces a silent ten-second restart loop on a machine nobody is watching. It is
            # also almost always OUR fault - a published script that does not survive some
            # starting state we did not think of - so it is the report most worth having.
            send_crash_report "sql" "Database script $base failed to apply."

            unset PGPASSWORD
            exit 1
        fi
    done
    unset PGPASSWORD
elif ls "$SQL_DIR"/*.sql >/dev/null 2>&1; then
    say "Database scripts are waiting in $SQL_DIR but the connection details could not be read"
    say "from server/server.env. Apply them by hand, then delete them."
fi

python3 - "$STATE_FILE" "$STAGING/manifest.json" <<PY
import json, os, sys

state_path, manifest_path = sys.argv[1], sys.argv[2]
with open(manifest_path, encoding="utf-8") as fh:
    manifest = json.load(fh)

state = {}
if os.path.exists(state_path):
    try:
        with open(state_path, encoding="utf-8") as fh:
            state = json.load(fh)
    except Exception:
        state = {}

applied = set(state.get("appliedSql", []))
applied.update(n for n in """$APPLIED_NAMES""".split("\n") if n.strip())

# The scripts this install never needed, recorded so the first-run seed happens exactly once.
applied.update(n for n in """$SEEDED_SQL""".split("\n") if n.strip())

state["lastVersion"] = manifest.get("version", "")
state["knownFiles"] = [f["name"] for f in manifest.get("files", [])]
state["appliedSql"] = sorted(applied)

os.makedirs(os.path.dirname(state_path), exist_ok=True)
with open(state_path, "w", encoding="utf-8") as fh:
    json.dump(state, fh, indent=2)
PY

say "Update complete."