#!/usr/bin/env bash
# SENDGRID-OPS-012 — Campaign run for Wed 2026-09-30 08:00 America/Chicago
#
# Usage (on production, from ~/marketing.linderlake.com):
#   bash sendgrid-ops/ops012-sept30.sh prepare    # backup → clean → sync → lists → Single Sends (NOT scheduled)
#   bash sendgrid-ops/ops012-sept30.sh schedule   # after human review: PUT /schedule + verify each SS
#
# Safety:
#   - `prepare` never schedules anything (SKIP_SCHEDULE=1). Nothing can be sent by it.
#   - `schedule` only calls PUT /marketing/singlesends/{id}/schedule with a future send_at.
#     It never calls the send-now endpoint.
#   - Any error stops the run (set -euo pipefail). Everything is logged under sendgrid-ops/logs/ops012/.
set -euo pipefail

MODE="${1:-}"
PROJECT_DIR="${PROJECT_DIR:-$HOME/marketing.linderlake.com}"
cd "$PROJECT_DIR"

# ---------------------------------------------------------------- config
# shellcheck disable=SC1091
source ./0-config_var_setup.sh

export PROTOTYPE_NAME="${PROTOTYPE_NAME:-Right-of-way and field support for 2027 projects}"
export CAMPAIGN_NAME="${CAMPAIGN_NAME_OVERRIDE:-Linderlake - Right-of-way and field support - 2026-09-30}"
export START_SEND_AT="2026-09-30 08:00:00"
export TIMEZONE="America/Chicago"
export INTERVAL_MINUTES=15
export CONTACTS_PER_LIST="${CONTACTS_PER_LIST:-2000}"
export SKIP_SCHEDULE=1

STAFFING_GROUP_ID=33991   # "Linderlake Staffing" — group hit by the 2026-09-16 scanner-unsubscribe incident
LOG_DIR="$PROJECT_DIR/sendgrid-ops/logs/ops012"
mkdir -p "$LOG_DIR"
TS="$(date '+%Y%m%d_%H%M%S')"
SG="https://api.sendgrid.com/v3"
AUTH=(-H "Authorization: Bearer $SENDGRID_API_KEY")

log()  { echo "[$(date '+%F %T')] $*"; }
die()  { echo "STOP: $*" >&2; exit 1; }

sg_get() { curl -sS --connect-timeout 10 --max-time 60 --retry 2 "${AUTH[@]}" "$1"; }

# ------------------------------------------------ prototype resolution
resolve_prototype() {
  local url="$SG/marketing/singlesends?page_size=100" page=0 out="$LOG_DIR/prototype_search_$TS.json"
  : > "$out"
  while [[ -n "$url" && "$url" != "null" ]]; do
    page=$((page + 1)); (( page > 60 )) && die "pagination runaway while searching prototype"
    local resp; resp="$(sg_get "$url")"
    echo "$resp" | jq -c --arg n "$PROTOTYPE_NAME" '.result[] | select(.name == $n) | {id,name,status,updated_at}' >> "$out"
    url="$(echo "$resp" | jq -r '._metadata.next // empty')"
  done
  local n; n="$(wc -l < "$out" | tr -d ' ')"
  [[ "$n" == "1" ]] || { cat "$out" >&2; die "expected exactly 1 Single Send named '$PROTOTYPE_NAME', found $n"; }
  PROTOTYPE_SINGLE_SEND_ID="$(jq -r '.id' "$out")"
  export PROTOTYPE_SINGLE_SEND_ID
}

validate_prototype() {
  local f="$LOG_DIR/prototype_$TS.json"
  sg_get "$SG/marketing/singlesends/$PROTOTYPE_SINGLE_SEND_ID" > "$f"
  local status subject sender group html design unsub
  status="$(jq -r '.status // empty' "$f")"
  subject="$(jq -r '.email_config.subject // empty' "$f")"
  sender="$(jq -r '.email_config.sender_id // empty' "$f")"
  group="$(jq -r '.email_config.suppression_group_id // empty' "$f")"
  unsub="$(jq -r '.email_config.custom_unsubscribe_url // empty' "$f")"
  html="$(jq -r '(.email_config.html_content // "") | length' "$f")"
  design="$(jq -r '.email_config.design_id // empty' "$f")"

  log "PROTOTYPE id=$PROTOTYPE_SINGLE_SEND_ID status=$status"
  log "  subject=$subject"
  log "  sender_id=$sender suppression_group_id=$group custom_unsubscribe_url=${unsub:-<none>}"
  log "  html_length=$html design_id=${design:-<none>}"

  [[ "$status" == "draft" ]]  || die "prototype is not a draft (status=$status)"
  [[ -n "$subject" ]]         || die "prototype has no subject"
  [[ -n "$sender" ]]          || die "prototype has no sender_id"
  [[ -n "$group" || -n "$unsub" ]] || die "prototype has no suppression group / unsubscribe"
  (( html > 0 )) || [[ -n "$design" ]] || die "prototype has no content"
  if [[ "$group" == "$STAFFING_GROUP_ID" && "${ALLOW_STAFFING_GROUP:-0}" != "1" ]]; then
    die "prototype uses suppression group $STAFFING_GROUP_ID (Linderlake Staffing) — unsubscribe link still has no confirmation page (2026-09-16 incident). Re-run with ALLOW_STAFFING_GROUP=1 only if intended."
  fi
}

# ------------------------------------------------------------ prepare
do_prepare() {
  log "=== OPS-012 PREPARE — campaign '$CAMPAIGN_NAME' start $START_SEND_AT $TIMEZONE ==="

  # Preflight: tooling, script identity, future start date
  for c in curl jq mysql php python3; do command -v "$c" >/dev/null || die "missing $c"; done
  grep -q 'SKIP_SCHEDULE' ./3-create_single_sends_from_prototype.sh \
    || die "3-create_single_sends_from_prototype.sh lacks SKIP_SCHEDULE gate (OPS-010 patch not deployed)"
  md5sum ./2-create_dynamic_sendgrid_lists.sh ./3-create_single_sends_from_prototype.sh
  [[ -f sendgrid-ops/linderlake_audience_source.txt ]] && md5sum sendgrid-ops/linderlake_audience_source.txt
  python3 - <<'PY' || exit 1
from datetime import datetime
from zoneinfo import ZoneInfo
start = datetime(2026, 9, 30, 8, 0, tzinfo=ZoneInfo("America/Chicago"))
now = datetime.now(ZoneInfo("America/Chicago"))
if start <= now:
    raise SystemExit(f"STOP: start {start} is not in the future (now {now})")
print(f"START_OK {start.isoformat()} = {start.astimezone(ZoneInfo('UTC')).isoformat()}")
PY

  log "--- resolve + validate prototype"
  resolve_prototype
  validate_prototype

  log "--- 93 full backup";            ./93-fullbackup_sendgrid.sh
  log "--- 90 import suppressions";    bash ./90-import_suppressions.sh
  log "--- 97 clean dead contacts";    bash ./97-clean_sendgrid_dead_contacts.sh --execute
  log "--- 0 delete temporary lists";  php ./0-delete_sendgrid_temporary_lists.php
  log "--- 1 sync SendGrid → MySQL";   ./1-sync_sendgrid_mysql.sh
  log "--- 2 create dynamic lists";    ./2-create_dynamic_sendgrid_lists.sh
  cp /tmp/sendgrid_dynamic_lists/created_lists.tsv "$LOG_DIR/lists_$TS.tsv" 2>/dev/null || true
  mkdir -p "$LOG_DIR/chunks_$TS" && cp -r /tmp/sendgrid_dynamic_lists/chunks/. "$LOG_DIR/chunks_$TS/" || true

  log "--- 3 create Single Sends (SKIP_SCHEDULE=1 → nothing is armed)"
  ./3-create_single_sends_from_prototype.sh
  cp /tmp/sendgrid_single_sends/single_sends_created.tsv "$LOG_DIR/manifest.tsv"

  local n; n="$(($(wc -l < "$LOG_DIR/manifest.tsv") - 1))"
  log "=== PREPARE DONE — $n Single Sends created as DRAFT ==="
  log "Planned window:"
  awk -F'\t' 'NR==2{print "  first: "$6} END{print "  last:  "$6}' "$LOG_DIR/manifest.tsv"
  log "Review them in SendGrid, then run:  bash sendgrid-ops/ops012-sept30.sh schedule"
}

# ------------------------------------------------------------ schedule
do_schedule() {
  local manifest="$LOG_DIR/manifest.tsv" results="$LOG_DIR/schedule_results_$TS.tsv"
  [[ -f "$manifest" ]] || die "no manifest at $manifest — run 'prepare' first"
  log "=== OPS-012 SCHEDULE from $manifest ==="
  echo -e "seq\tsingle_send_id\texpected_send_at\tput_http\tstatus\tactual_send_at\tresult" > "$results"

  local now_utc; now_utc="$(date -u '+%Y-%m-%dT%H:%M:%SZ')"
  local ok=0
  while IFS=$'\t' read -r seq id name list_id list_name send_at count _rest; do
    [[ "$seq" == "seq" ]] && continue
    [[ "$send_at" > "$now_utc" ]] || die "seq=$seq send_at=$send_at is not in the future"

    if [[ "${SKIP_MISSING:-0}" == "1" ]]; then
      local lh; lh="$(curl -sS -o /dev/null -w '%{http_code}' "${AUTH[@]}" "$SG/marketing/lists/$list_id?contact_sample=false")"
      if [[ "$lh" == "404" ]]; then
        log "[$seq] MISSING_LIST_SKIPPED $id (list $list_id is 404) — left as draft"
        echo -e "$seq\t$id\t$send_at\t-\tdraft\t-\tMISSING_LIST_SKIPPED" >> "$results"; continue
      fi
    fi
    local cur; cur="$(sg_get "$SG/marketing/singlesends/$id")"
    [[ "$(echo "$cur" | jq -r '.send_to.list_ids[0] // empty')" == "$list_id" ]] || die "seq=$seq list mismatch on $id"
    local st; st="$(echo "$cur" | jq -r '.status')"
    if [[ "$st" == "scheduled" ]]; then
      [[ "$(echo "$cur" | jq -r '.send_at')" == "$send_at" ]] || die "seq=$seq already scheduled at a different time"
      echo -e "$seq\t$id\t$send_at\t-\tscheduled\t$send_at\tALREADY_OK" >> "$results"; ok=$((ok+1)); continue
    fi

    local body="$LOG_DIR/put_${seq}_$TS.json" http
    http="$(curl -sS -o "$body" -w '%{http_code}' -X PUT "${AUTH[@]}" \
      -H "Content-Type: application/json" "$SG/marketing/singlesends/$id/schedule" \
      --data "$(jq -n --arg s "$send_at" '{send_at:$s}')")"
    if [[ "$http" == "429" ]]; then log "[$seq] rate limited, retrying in 30s"; sleep 30
      http="$(curl -sS -o "$body" -w '%{http_code}' -X PUT "${AUTH[@]}" \
        -H "Content-Type: application/json" "$SG/marketing/singlesends/$id/schedule" \
        --data "$(jq -n --arg s "$send_at" '{send_at:$s}')")"
    fi
    local v; v="$(sg_get "$SG/marketing/singlesends/$id")"
    local vs va; vs="$(echo "$v" | jq -r '.status')"; va="$(echo "$v" | jq -r '.send_at')"
    if [[ "$vs" == "scheduled" && "$va" == "$send_at" ]]; then
      echo -e "$seq\t$id\t$send_at\t$http\t$vs\t$va\tSCHEDULED_OK" >> "$results"; ok=$((ok+1))
      log "[$seq] SCHEDULED_OK $id $send_at"
    else
      echo -e "$seq\t$id\t$send_at\t$http\t$vs\t$va\tFAIL" >> "$results"
      echo "PUT response body:" >&2; cat "$body" >&2; echo >&2
      echo "Single Send state:" >&2; echo "$v" | jq '{status,send_at,send_to,email_config:{subject:.email_config.subject,sender_id:.email_config.sender_id,suppression_group_id:.email_config.suppression_group_id}}' >&2
      die "seq=$seq verify failed (http=$http status=$vs send_at=$va)"
    fi
    sleep 1
  done < "$manifest"

  log "=== SCHEDULE DONE — $ok Single Sends scheduled. Results: $results ==="
}


# ------------------------------------------------------------ check (read-only)
do_check() {
  local manifest="$LOG_DIR/manifest.tsv"
  [[ -f "$manifest" ]] || die "no manifest at $manifest"
  echo -e "seq\tlist_http\tlist_name\tlist_contacts\tss_status\tss_send_at"
  while IFS=$'\t' read -r seq id name list_id list_name send_at count _rest; do
    [[ "$seq" == "seq" ]] && continue
    local lf="$LOG_DIR/check_list_${seq}.json" lh
    lh="$(curl -sS -o "$lf" -w '%{http_code}' "${AUTH[@]}" "$SG/marketing/lists/$list_id?contact_sample=false")"
    local ss; ss="$(sg_get "$SG/marketing/singlesends/$id")"
    echo -e "$seq\t$lh\t$(jq -r '.name // "-"' "$lf")\t$(jq -r '.contact_count // "-"' "$lf")\t$(echo "$ss" | jq -r '.status')\t$(echo "$ss" | jq -r '.send_at')"
  done < "$manifest"
  echo; echo "--- lists named like this run still in SendGrid:"
  local run; run="$(awk -F'\t' 'NR==2{print $5}' "$manifest" | sed -E 's/_[0-9]+$//')"
  sg_get "$SG/marketing/lists?page_size=1000" | jq -r --arg r "$run" '.result[] | select(.name | startswith($r)) | "\(.name)\t\(.id)\t\(.contact_count)"' | sort -t_ -k6n
  echo; echo "--- crontab entries touching sendgrid/lists:"
  crontab -l 2>/dev/null | grep -iE 'sendgrid|list|delete|clean|sync|9[0-9]-|0-delete|1-sync' || echo "(none)"
}


# ------------------------------------------------------------ repair
# Recreates lists that vanished after `prepare` (HTTP 404) from the exact chunk
# files 2-create used, waits for the import job, repoints the draft Single Send
# to the new list and updates manifest + dynamic_sendgrid_lists. Never schedules.
do_repair() {
  local manifest="$LOG_DIR/manifest.tsv" chunks="${CHUNKS_DIR_OVERRIDE:-/tmp/sendgrid_dynamic_lists/chunks}"
  [[ -f "$manifest" ]] || die "no manifest at $manifest"
  [[ -d "$chunks" ]] || die "chunk dir $chunks is gone — cannot rebuild lists with the same contacts"
  cp "$manifest" "$LOG_DIR/manifest.before_repair_$TS.tsv"
  local fixed=0
  while IFS=$'\t' read -r seq id name list_id list_name send_at count _rest; do
    [[ "$seq" == "seq" ]] && continue
    local lh; lh="$(curl -sS -o /dev/null -w '%{http_code}' "${AUTH[@]}" "$SG/marketing/lists/$list_id?contact_sample=false")"
    [[ "$lh" == "404" ]] || continue

    local ss; ss="$(sg_get "$SG/marketing/singlesends/$id")"
    [[ "$(echo "$ss" | jq -r '.status')" == "draft" ]] || die "seq=$seq SS $id is not draft — refusing to touch it"

    local chunk; chunk="$chunks/chunk_$(printf '%04d' $((seq - 1)))"
    [[ -f "$chunk" ]] || die "seq=$seq missing $chunk"
    local n; n="$(wc -l < "$chunk" | tr -d ' ')"
    [[ "$n" == "$count" ]] || die "seq=$seq chunk has $n lines, manifest expects $count"

    log "[$seq] list $list_name ($list_id) is 404 — recreating from $(basename "$chunk") ($n contacts)"
    local cr new_list
    cr="$(curl -sS -X POST "${AUTH[@]}" -H "Content-Type: application/json" "$SG/marketing/lists" \
          --data "$(jq -n --arg n "$list_name" '{name:$n}')")"
    new_list="$(echo "$cr" | jq -r '.id // empty')"
    [[ -n "$new_list" ]] || { echo "$cr" >&2; die "seq=$seq could not create list"; }

    local payload="$LOG_DIR/repair_payload_${seq}.json"
    python3 - "$chunk" "$new_list" "$payload" <<'PY'
import json, sys
chunk, lid, out = sys.argv[1:4]
contacts = []
for line in open(chunk, encoding="utf-8"):
    p = line.rstrip("\n").split("\t")
    if p and p[0].strip():
        contacts.append({"email": p[0].strip().lower(),
                         "first_name": p[1].strip() if len(p) > 1 else "",
                         "last_name": p[2].strip() if len(p) > 2 else ""})
json.dump({"list_ids": [lid], "contacts": contacts}, open(out, "w"), ensure_ascii=False)
PY
    local job; job="$(curl -sS -X PUT "${AUTH[@]}" -H "Content-Type: application/json" \
          "$SG/marketing/contacts" --data-binary "@$payload" | jq -r '.job_id // empty')"
    [[ -n "$job" ]] || die "seq=$seq contact import not accepted"

    local st="" i
    for i in $(seq 1 60); do
      st="$(sg_get "$SG/marketing/contacts/imports/$job" | jq -r '.status // empty')"
      [[ "$st" == "completed" || "$st" == "failed" || "$st" == "errored" ]] && break
      sleep 10
    done
    [[ "$st" == "completed" ]] || die "seq=$seq import job $job status=$st"

    local c=0
    for i in $(seq 1 30); do
      c="$(sg_get "$SG/marketing/lists/$new_list?contact_sample=false" | jq -r '.contact_count // 0')"
      (( c >= n )) && break
      sleep 20
    done
    (( c > 0 )) || die "seq=$seq new list $new_list still empty"
    log "[$seq] new list $new_list contact_count=$c (expected $n)"

    local pr; pr="$(curl -sS -X PATCH "${AUTH[@]}" -H "Content-Type: application/json" \
          "$SG/marketing/singlesends/$id" \
          --data "$(jq -n --arg l "$new_list" '{send_to:{list_ids:[$l],segment_ids:[],all:false}}')")"
    [[ "$(echo "$pr" | jq -r '.send_to.list_ids[0] // empty')" == "$new_list" ]] || { echo "$pr" >&2; die "seq=$seq PATCH failed"; }

    awk -F'\t' -v OFS='\t' -v s="$seq" -v l="$new_list" '$1==s{$4=l} {print}' "$manifest" > "$manifest.tmp" && mv "$manifest.tmp" "$manifest"
    mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" "$MYSQL_DB" \
      -e "UPDATE dynamic_sendgrid_lists SET sendgrid_list_id='$new_list' WHERE sendgrid_list_id='$list_id';" || true
    log "[$seq] REPAIRED SS $id → list $new_list"
    fixed=$((fixed + 1))
  done < "$manifest"
  log "=== REPAIR DONE — $fixed lists recreated ==="
}


# ------------------------------------------------------------ rebuild
# Reconstructs the contacts of the vanished lists as:
#   E (same eligibility SQL 2-create ran, copied verbatim from the production script)
#   minus members of the surviving lists (SendGrid export)
# Writes one chunk per missing seq into $LOG_DIR/rebuild_chunks/, then runs do_repair on them.
# Hard stops unless every count reconciles exactly with the manifest.
do_rebuild() {
  local manifest="$LOG_DIR/manifest.tsv" W="$LOG_DIR/rebuild_$TS"
  mkdir -p "$W"
  [[ -f "$manifest" ]] || die "no manifest"

  log "--- 1) classify lists (alive / missing)"
  : > "$W/alive.tsv"; : > "$W/missing.tsv"
  while IFS=$'\t' read -r seq id name list_id list_name send_at count _rest; do
    [[ "$seq" == "seq" ]] && continue
    local lh; lh="$(curl -sS -o /dev/null -w '%{http_code}' "${AUTH[@]}" "$SG/marketing/lists/$list_id?contact_sample=false")"
    case "$lh" in
      200) echo -e "$seq\t$list_id\t$count" >> "$W/alive.tsv" ;;
      404) echo -e "$seq\t$list_id\t$count" >> "$W/missing.tsv" ;;
      *)   die "seq=$seq list HTTP $lh" ;;
    esac
  done < "$manifest"
  local total alive_n miss_n
  total="$(awk -F'\t' 'NR>1{s+=$7} END{print s}' "$manifest")"
  alive_n="$(awk -F'\t' '{s+=$3} END{print s+0}' "$W/alive.tsv")"
  miss_n="$(awk -F'\t' '{s+=$3} END{print s+0}' "$W/missing.tsv")"
  log "manifest total=$total alive_lists=$(wc -l < "$W/alive.tsv") ($alive_n) missing_lists=$(wc -l < "$W/missing.tsv") ($miss_n)"
  (( miss_n > 0 )) || { log "nothing missing"; return 0; }

  log "--- 2) regenerate eligible set E with the production SQL (verbatim)"
  {
    echo 'set -euo pipefail'
    echo "COMPANY='LINDERLAKE'"
    echo "AUDIENCE_SOURCE_FILE='$PROJECT_DIR/sendgrid-ops/linderlake_audience_source.txt'"
    echo "CONTACTS_TSV='$W/eligible.tsv'"
    sed -n '/^mysql --local-infile=1/,/^SQL$/p' ./2-create_dynamic_sendgrid_lists.sh
  } > "$W/eligible.sh"
  grep -q 'ORDER BY RAND' "$W/eligible.sh" || die "could not extract eligibility SQL from 2-create"
  bash "$W/eligible.sh"
  local e_n; e_n="$(cut -f1 "$W/eligible.tsv" | sort -u | wc -l | tr -d ' ')"
  log "E = $e_n unique emails"
  [[ "$e_n" == "$total" ]] || die "E=$e_n differs from manifest total=$total (MySQL changed since prepare?)"

  log "--- 3) export members of the surviving lists from SendGrid"
  local ids; ids="$(cut -f2 "$W/alive.tsv" | jq -R . | jq -sc .)"
  local ex; ex="$(curl -sS -X POST "${AUTH[@]}" -H "Content-Type: application/json" \
        "$SG/marketing/contacts/exports" --data "{\"list_ids\":$ids,\"file_type\":\"csv\"}" | jq -r '.id // empty')"
  [[ -n "$ex" ]] || die "export not accepted"
  local st="" i resp
  for i in $(seq 1 90); do
    resp="$(sg_get "$SG/marketing/contacts/exports/$ex")"; st="$(echo "$resp" | jq -r '.status')"
    [[ "$st" == "ready" || "$st" == "failure" ]] && break; sleep 10
  done
  [[ "$st" == "ready" ]] || die "export $ex status=$st"
  local k=0 u
  for u in $(echo "$resp" | jq -r '.urls[]'); do
    k=$((k+1)); curl -sS -o "$W/export_$k.bin" "$u"
  done
  python3 - "$W" <<'PY'
import csv, glob, gzip, io, sys
w = sys.argv[1]; emails = set()
for f in sorted(glob.glob(f"{w}/export_*.bin")):
    raw = open(f, "rb").read()
    if raw[:2] == b"\x1f\x8b": raw = gzip.decompress(raw)
    for row in csv.DictReader(io.StringIO(raw.decode("utf-8-sig"))):
        e = (row.get("EMAIL") or row.get("email") or "").strip().lower()
        if e: emails.add(e)
open(f"{w}/alive_members.txt", "w").write("\n".join(sorted(emails)) + "\n")
print(len(emails))
PY
  local a_n; a_n="$(grep -c . "$W/alive_members.txt")"
  log "surviving-list members = $a_n (expected $alive_n)"
  [[ "$a_n" == "$alive_n" ]] || die "surviving members $a_n != expected $alive_n"

  log "--- 4) R = E − surviving members"
  python3 - "$W" <<'PY'
import sys
w = sys.argv[1]
alive = set(l.strip() for l in open(f"{w}/alive_members.txt") if l.strip())
out, seen, inter = [], set(), 0
for line in open(f"{w}/eligible.tsv", encoding="utf-8"):
    e = line.split("\t")[0].strip().lower()
    if not e or e in seen: continue
    seen.add(e)
    if e in alive: inter += 1
    else: out.append(line if line.endswith("\n") else line + "\n")
open(f"{w}/remainder.tsv", "w", encoding="utf-8").writelines(out)
print(inter, len(out))
PY
  local r_n; r_n="$(wc -l < "$W/remainder.tsv" | tr -d ' ')"
  log "R = $r_n (expected $miss_n)"
  [[ "$r_n" == "$miss_n" ]] || die "remainder $r_n != missing total $miss_n"

  log "--- 5) split R into one chunk per missing seq"
  mkdir -p "$W/chunks"
  local off=1
  while IFS=$'\t' read -r seq lid cnt; do
    sed -n "${off},$((off + cnt - 1))p" "$W/remainder.tsv" > "$W/chunks/chunk_$(printf '%04d' $((seq - 1)))"
    off=$((off + cnt))
  done < "$W/missing.tsv"
  ls -l "$W/chunks"

  log "--- 6) recreate lists + repoint drafts"
  CHUNKS_DIR_OVERRIDE="$W/chunks" do_repair
}


# ------------------------------------------------------------ reset (full rebuild)
# Unschedules + deletes the Single Sends of this run (never the prototype),
# deletes all Campaign_LINDERLAKE_Random_* lists, then recreates lists and
# Single Sends (as drafts) with the production scripts. Requires CONFIRM=RESET.
# 90/97/1-sync are NOT re-run: they completed in `prepare` on 2026-09-28.
do_reset() {
  [[ "${CONFIRM:-}" == "RESET" ]] || die "reset needs CONFIRM=RESET"
  local manifest="$LOG_DIR/manifest.tsv"
  [[ -f "$manifest" ]] || die "no manifest"
  cp "$manifest" "$LOG_DIR/manifest.before_reset_$TS.tsv"
  resolve_prototype
  validate_prototype

  log "--- 1) unschedule + delete this run's Single Sends"
  while IFS=$'\t' read -r seq id _rest; do
    [[ "$seq" == "seq" ]] && continue
    [[ "$id" == "$PROTOTYPE_SINGLE_SEND_ID" ]] && die "manifest contains the prototype id — refusing"
    local st; st="$(sg_get "$SG/marketing/singlesends/$id" | jq -r '.status // "gone"')"
    if [[ "$st" == "scheduled" ]]; then
      curl -sS -o /dev/null -X DELETE "${AUTH[@]}" "$SG/marketing/singlesends/$id/schedule"
    fi
    [[ "$st" == "gone" ]] || curl -sS -o /dev/null -X DELETE "${AUTH[@]}" "$SG/marketing/singlesends/$id"
    local after; after="$(curl -sS -o /dev/null -w '%{http_code}' "${AUTH[@]}" "$SG/marketing/singlesends/$id")"
    [[ "$after" == "404" ]] || die "seq=$seq SS $id still exists after delete (HTTP $after)"
    log "[$seq] deleted SS $id (was $st)"
    sleep 1
  done < "$manifest"

  log "--- 2) delete temporary lists";   php ./0-delete_sendgrid_temporary_lists.php
  log "--- 3) create dynamic lists";     ./2-create_dynamic_sendgrid_lists.sh
  cp /tmp/sendgrid_dynamic_lists/created_lists.tsv "$LOG_DIR/lists_$TS.tsv" 2>/dev/null || true
  mkdir -p "$LOG_DIR/chunks_$TS" && cp -r /tmp/sendgrid_dynamic_lists/chunks/. "$LOG_DIR/chunks_$TS/" || true
  log "--- 4) create Single Sends (SKIP_SCHEDULE=1)"
  ./3-create_single_sends_from_prototype.sh
  cp /tmp/sendgrid_single_sends/single_sends_created.tsv "$manifest"
  log "=== RESET DONE — $(($(wc -l < "$manifest") - 1)) drafts. Chunks kept in $LOG_DIR/chunks_$TS ==="
}


# ------------------------------------------------------------ recreate
# Rebuilds draft Single Sends for the lists of the pre-reset manifest that
# still exist (HTTP 200), keeping each original seq, name and send_at slot.
# Uses the same clone+PATCH calls as 3-create. Never schedules.
do_recreate() {
  local src="${SRC_MANIFEST:-$(ls -t "$LOG_DIR"/manifest.before_reset_*.tsv 2>/dev/null | head -1)}"
  local manifest="$LOG_DIR/manifest.tsv"
  [[ -f "$src" ]] || die "no pre-reset manifest found"
  log "source manifest: $src"
  resolve_prototype
  validate_prototype
  echo -e "seq\tsingle_send_id\tsingle_send_name\tlist_id\tlist_name\tsend_at_utc\tcontact_count" > "$manifest.new"
  local made=0 skipped=0
  while IFS=$'\t' read -r seq old_id name list_id list_name send_at count _rest; do
    [[ "$seq" == "seq" ]] && continue
    local lf="$LOG_DIR/recreate_list_${seq}.json" lh
    lh="$(curl -sS -o "$lf" -w '%{http_code}' "${AUTH[@]}" "$SG/marketing/lists/$list_id?contact_sample=false")"
    if [[ "$lh" != "200" ]]; then log "[$seq] list $list_id HTTP $lh — skipped"; skipped=$((skipped+1)); continue; fi
    local actual; actual="$(jq -r '.contact_count // 0' "$lf")"
    (( actual >= count )) || die "seq=$seq list $list_id has $actual contacts, expected $count"

    local new_id; new_id="$(curl -sS -X POST "${AUTH[@]}" -H "Content-Type: application/json" \
        "$SG/marketing/singlesends/$PROTOTYPE_SINGLE_SEND_ID" \
        --data "$(jq -n --arg n "$name" '{name:$n}')" | jq -r '.id // empty')"
    [[ -n "$new_id" ]] || die "seq=$seq clone failed"
    local pr; pr="$(curl -sS -X PATCH "${AUTH[@]}" -H "Content-Type: application/json" \
        "$SG/marketing/singlesends/$new_id" \
        --data "$(jq -n --arg n "$name" --arg l "$list_id" '{name:$n,send_to:{list_ids:[$l],segment_ids:[],all:false}}')")"
    [[ "$(echo "$pr" | jq -r '.send_to.list_ids[0] // empty')" == "$list_id" ]] || { echo "$pr" >&2; die "seq=$seq PATCH failed"; }
    echo -e "$seq\t$new_id\t$name\t$list_id\t$list_name\t$send_at\t$count" >> "$manifest.new"
    log "[$seq] DRAFT_OK $new_id → $list_name ($actual) slot $send_at"
    made=$((made+1)); sleep 1
  done < "$src"
  mv "$manifest.new" "$manifest"
  log "=== RECREATE DONE — $made drafts, $skipped skipped (missing list) ==="
}

# ------------------------------------------------------------ diag (read-only)
do_diag() {
  echo "--- MySQL"
  mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" "$MYSQL_DB" -t -e "
    SELECT COUNT(*) contacts, SUM(status='active') active, SUM(unsubscribed=0) not_unsub FROM contacts;
    SELECT company_name, COUNT(*) memberships FROM contact_list_memberships GROUP BY company_name;
    SELECT suppression_type, COUNT(*) n FROM sendgrid_suppressions GROUP BY suppression_type;
    SELECT table_name, update_time, table_rows FROM information_schema.tables WHERE table_schema=DATABASE() ORDER BY update_time DESC;
    SELECT * FROM backup_runs ORDER BY id DESC LIMIT 3;
    SELECT * FROM suppression_import_runs ORDER BY 1 DESC LIMIT 3;
    SELECT run_id, COUNT(*) lists, SUM(contact_count) contacts, MIN(created_at) FROM dynamic_sendgrid_lists GROUP BY run_id ORDER BY 4 DESC LIMIT 3;" 2>&1
  echo "--- processes"
  ps -u "$(id -un)" -o pid,lstart,cmd | grep -E 'sendgrid|sync|recover|clean|import|php|mysql' | grep -v grep || echo "(none)"
  echo "--- project files modified since 2026-09-28 18:00 (excluding ops012 logs)"
  find "$PROJECT_DIR" -type f -newermt "2026-09-28 18:00" ! -path '*/logs/ops012/*' 2>/dev/null | head -30
}

case "$MODE" in
  # No process substitution: /dev/fd is not available on the production host.
  prepare)  do_prepare  2>&1 | tee -a "$LOG_DIR/prepare_$TS.log" ;;
  schedule) do_schedule 2>&1 | tee -a "$LOG_DIR/schedule_$TS.log" ;;
  check)    do_check ;;
  repair)   do_repair 2>&1 | tee -a "$LOG_DIR/repair_$TS.log" ;;
  rebuild)  do_rebuild 2>&1 | tee -a "$LOG_DIR/rebuild_$TS.log" ;;
  reset)    do_reset 2>&1 | tee -a "$LOG_DIR/reset_$TS.log" ;;
  recreate) do_recreate 2>&1 | tee -a "$LOG_DIR/recreate_$TS.log" ;;
  diag)     do_diag ;;
  *) echo "usage: $0 prepare|schedule|check|repair|rebuild|reset|recreate|diag"; exit 2 ;;
esac