#!/usr/bin/env bash
# =============================================================================
# backfill-wi-version-snapshots.sh — One-time WI version snapshot baseline
#
# WI.LWIVERSIONSNAPSHOT (added 20260814_WI_LWIVERSIONSNAPSHOT.sql) only starts
# capturing content going forward, from the next status transition on each WI.
# Every WI already in Published/Archived status today has NO recoverable
# history — WiMasterDAL.SaveWiFull/UpdateWiFull already destroyed and replaced
# any prior content with no snapshot in between. This script does NOT recover
# that lost history (it cannot — the data is gone); it only seeds a
# **current-state baseline** so "view version" / "compare" have something to
# show for already-live WIs from day one, rather than an empty history.
#
# Deliberately calls the real GetWIFullDetail endpoint rather than
# reconstructing its JSON shape in T-SQL — the assembly logic (grouping
# StepContent rows into WiStepContainerDTO) lives in WiMasterDAL.cs, in C#,
# and re-implementing it here risks producing a snapshot shape that silently
# drifts from what the viewer actually expects.
#
# Usage:
#   GB5_API_BASE=https://<host> \
#   GB5_LOGIN_HEADER=<hand-crafted or real Login header JSON> \
#   SQLCMD_ARGS="-S <server> -d <db> -U <user> -P <pass>" \
#   bash scripts/backfill-wi-version-snapshots.sh
#
# Requires: curl, jq, sqlcmd on PATH, network access to the target API + DB.
# Safe to re-run: only WIs with zero existing LWIVERSIONSNAPSHOT rows are
# processed each time (already-backfilled WIs are skipped).
# =============================================================================

set -euo pipefail

: "${GB5_API_BASE:?Set GB5_API_BASE, e.g. https://dev.example.com}"
: "${GB5_LOGIN_HEADER:?Set GB5_LOGIN_HEADER to a valid Login header value}"
: "${SQLCMD_ARGS:?Set SQLCMD_ARGS, e.g. \"-S host -d GB5DEMO -U user -P pass\"}"

echo "Finding Published/Archived WIs with no existing snapshot..."

# WISTATUS: 3=Published, 4=Archived. Only the latest LWIVERSION row per WIID
# (MAX(LWIVERSIONID)) needs a baseline; skip WIs that already have one.
CANDIDATES=$(sqlcmd $SQLCMD_ARGS -h -1 -W -Q "
SET NOCOUNT ON;
SELECT v.WIID, v.LWIVERSIONID
FROM WI.LWIVERSION v
JOIN WI.MWI wi ON wi.WIID = v.WIID
WHERE wi.WISTATUS IN (3,4)
  AND v.LWIVERSIONID = (SELECT MAX(v2.LWIVERSIONID) FROM WI.LWIVERSION v2 WHERE v2.WIID = v.WIID)
  AND NOT EXISTS (SELECT 1 FROM WI.LWIVERSIONSNAPSHOT s WHERE s.LWIVERSIONID = v.LWIVERSIONID)
")

if [[ -z "$CANDIDATES" ]]; then
  echo "Nothing to backfill — every Published/Archived WI already has a baseline snapshot."
  exit 0
fi

COUNT=0
while read -r WIID LWIVERSIONID; do
  [[ -z "$WIID" ]] && continue
  echo "WiId=$WIID LWiVersionId=$LWIVERSIONID — fetching full detail..."

  SNAPSHOT_JSON=$(curl -sf "$GB5_API_BASE/wi/WI/GetWIFullDetail?WiId=$WIID" \
    -H "Login: $GB5_LOGIN_HEADER" | jq -c '.responseValue // .body // .')

  if [[ -z "$SNAPSHOT_JSON" || "$SNAPSHOT_JSON" == "null" ]]; then
    echo "  WARNING: empty response for WiId=$WIID — skipping" >&2
    continue
  fi

  # Escape single quotes for T-SQL string literal
  ESCAPED_JSON=${SNAPSHOT_JSON//\'/\'\'}

  sqlcmd $SQLCMD_ARGS -Q "
    INSERT INTO WI.LWIVERSIONSNAPSHOT (LWIVERSIONID, WIID, SNAPSHOTJSON, CREATEDON)
    VALUES ($LWIVERSIONID, $WIID, '$ESCAPED_JSON', GETDATE());
  "
  COUNT=$((COUNT+1))
done <<< "$CANDIDATES"

echo "Backfilled $COUNT baseline snapshot(s)."