#!/usr/bin/env bash
# zero-open-order-stock: count N articles that sit on open (completed, undelivered) sales orders down to 0
# so the dashboard shows orders blocked by missing stock. ORG/WH required, N [3].
set -euo pipefail; export LANG=C.UTF-8
BASE=http://127.0.0.1:3001; ORG="${ORG:?}"; WH="${WH:?}"; N="${N:-3}"; DOCTYPE_INV=1000023; TODAY="$(date +%Y-%m-%d)"
ADMIN_PASS="$(sed -n 's/^password: //p' /root/demo-credentials.txt | head -1)"
JAR="$(mktemp)"; trap 'rm -f "$JAR"' EXIT
api() { curl -sS -b "$JAR" -c "$JAR" -X "$1" "$BASE$2" -H 'Content-Type: application/json' -d "$3"; }
api POST /api/idempiere-auth/login "$(jq -n --arg p "$ADMIN_PASS" '{userName:"demo",password:$p,selectRole:true}')" | jq -e .token >/dev/null
api POST /api/idempiere-auth/select-role '{"clientId":1000000,"roleId":1000000,"organizationId":1000000,"warehouseId":1000000,"language":"de_DE"}' >/dev/null
psql() { sudo -u postgres psql -At -F'|' -d idempiere -c "$1" 2>/dev/null; }
# one product per open order, N orders
mapfile -t PRODS < <(psql "select distinct on (o.c_order_id) l.m_product_id, o.documentno, p.value from adempiere.c_order o join adempiere.c_orderline l using(c_order_id) join adempiere.m_product p on p.m_product_id=l.m_product_id
  where o.ad_org_id=$ORG and o.issotrx='Y' and o.docstatus='CO' and l.qtydelivered=0 and not exists (select 1 from adempiere.m_inout i where i.c_order_id=o.c_order_id)
  order by o.c_order_id, l.line limit $N")
LINES=()
for r in "${PRODS[@]}"; do
  IFS='|' read -r pid doc val <<<"$r"; echo "  order $doc: $val -> 0"
  while IFS='|' read -r loc q; do
    LINES+=("$(jq -nc --argjson p "$pid" --argjson l "$loc" --argjson b "$q" '{M_Product_ID:{id:$p}, M_Locator_ID:{id:$l}, qtyCount:0, qtyBook:$b}')")
  done < <(psql "select m_locator_id, sum(qtyonhand)::int from adempiere.m_storageonhand where m_product_id=$pid group by 1 having sum(qtyonhand)<>0")
done
[ "${#LINES[@]}" -gt 0 ] || { echo "nothing to do"; exit 0; }
body="$(printf '%s\n' "${LINES[@]}" | jq -s 'to_entries | map(.value + {line: ((.key+1)*10), isActive:true})')"
res="$(api POST /api/materials/stock-takes/store "$(jq -n --argjson o "$ORG" --argjson w "$WH" --argjson d "$DOCTYPE_INV" --arg dt "$TODAY" --arg desc "Fehlbestand festgestellt ($TODAY)" --argjson lines "$body" \
  '{organizationId:$o, warehouseId:$w, docTypeId:$d, movementDate:$dt, isActive:true, description:$desc, inventoryLines:$lines}')")"
inv="$(echo "$res" | jq -r '.id // empty')"; [ -n "$inv" ] || { echo "store failed: $(echo "$res" | cut -c1-300)"; exit 1; }
da="$(api POST "/api/materials/stock-takes/$inv/document-action" '{"documentAction":"CO"}')"; echo "  inventory $inv: $(echo "$da" | jq -r '.DocStatus.identifier // .DocStatus.id // .message // "?"')"