#!/bin/bash
# =============================================================================
# Emit the SQL that converges a base table on the effective master data.
#
#   muli-db-seed-delta <m_table> <base_table> <key_cols> <all_cols> [old_table]
#
# Called by muli-db-seed inside the load transaction, after the old-snapshot
# table has been filled with the pre-load contents of the `_m` table. That name
# defaults to `_seed_old`; apply_group passes a numbered one because a foreign
# key group stages several tables in the same transaction.
#
# The effective value for a key is the `_u` row if the customer has one, else
# the `_m` row. That is the same precedence the legacy 0m003m produces, which
# truncates the base table and reloads it from `_m` and then `_u` so the latter
# overwrites.
#
# This converges on the same state without the truncate. A base table is live -
# the application writes to it - and a rebuild takes everything with it,
# including rows in neither `_m` nor `_u`: a contract's `allocs` carries rows
# inserted at runtime by rtbc_fns.inc as "Missing Reinserted"/seq 998 when the
# trial balance meets a missing allocation code. The legacy destroys those and
# lets the trial balance recreate them later; not destroying them avoids the
# window in between, and avoids assuming every such row is regenerable.
#
# A base row is treated as seeding's to change only if it still holds either
# what `_m` last supplied or what `_u` says. Anything else is a local edit and
# is left alone.
# =============================================================================
set -euo pipefail

M_TABLE="${1:?m table}"; BASE="${2:?base table}"; KEYS="${3:?key columns}"; COLS="${4:?all columns}"
OLD="${5:-_seed_old}"
U_TABLE="${BASE}_u"

# Key comparison uses plain `=`, not IS NOT DISTINCT FROM. The key columns come
# from the table's PRIMARY KEY and so cannot be NULL, and PostgreSQL cannot
# satisfy IS NOT DISTINCT FROM from a btree index - with it, every clause below
# degenerated to a sequential scan and calendar (93k rows) took over 25 minutes.
# Row comparison below still needs the NULL-safe form: data columns are nullable.
keyeq() { echo "$KEYS" | tr ',' '\n' | awk -v a="$1" -v b="$2" \
    '{printf "%s %s.%s = %s.%s", (NR>1?" AND":""), a,$1, b,$1}'; }
roweq() { echo "$COLS" | tr ',' '\n' | awk -v a="$1" -v b="$2" \
    '{printf "%s %s.%s IS NOT DISTINCT FROM %s.%s", (NR>1?" AND":""), a,$1, b,$1}'; }
qualify() { echo "$COLS" | tr ',' '\n' | awk -v a="$1" '{printf "%s%s.%s", (NR>1?", ":""), a, $1}'; }
setfrom() { echo "$COLS" | tr ',' '\n' | grep -vxF -f <(echo "$KEYS" | tr ',' '\n') \
    | awk -v a="$1" '{printf "%s%s=%s.%s", (NR>1?", ":""), $1, a, $1}'; }

cat <<SQL

-- ---- converge ${BASE} on the effective master data -----------------------
-- Effective value per key: ${U_TABLE} if the customer has a row, else ${M_TABLE}.
-- Materialised, not a view: it is referenced four times below, and as a view
-- each reference re-runs the UNION and its NOT EXISTS. On calendar_m (93k rows)
-- that alone took minutes. Indexed for the same reason - every clause joins on
-- the key columns.
CREATE TEMP TABLE _seed_effective AS
  SELECT $(qualify u) FROM ${U_TABLE} u
  UNION ALL
  SELECT $(qualify m) FROM ${M_TABLE} m
   WHERE NOT EXISTS (SELECT 1 FROM ${U_TABLE} u WHERE $(keyeq u m));
CREATE INDEX ON _seed_effective (${KEYS});
CREATE INDEX ON ${OLD} (${KEYS});
ANALYZE _seed_effective;
ANALYZE ${OLD};

-- 1. keys the base does not have yet: adopt the effective value.
INSERT INTO ${BASE} (${COLS})
SELECT ${COLS} FROM _seed_effective e
 WHERE NOT EXISTS (SELECT 1 FROM ${BASE} b WHERE $(keyeq e b));

-- 2. keys the base has: move it to the effective value, but only where the base
--    still holds something seeding owns - what ${M_TABLE} last supplied, or what
--    ${U_TABLE} says. This is what applies a customer override that was never
--    propagated, and equally what leaves a local edit alone.
UPDATE ${BASE} b SET $(setfrom e)
  FROM _seed_effective e
 WHERE $(keyeq b e)
   AND NOT ($(roweq b e))
   AND ( EXISTS (SELECT 1 FROM ${OLD} o WHERE $(keyeq o b) AND $(roweq b o))
      OR EXISTS (SELECT 1 FROM ${U_TABLE} u WHERE $(keyeq u b) AND $(roweq b u)) );

-- 3. keys withdrawn from both sources: remove from the base only where it still
--    holds exactly what ${M_TABLE} last supplied. A runtime-created row appears
--    in no snapshot, so no clause reaches it.
DELETE FROM ${BASE} b USING ${OLD} o
 WHERE $(keyeq b o)
   AND NOT EXISTS (SELECT 1 FROM _seed_effective e WHERE $(keyeq e b))
   AND $(roweq b o);

DROP TABLE _seed_effective;
SQL
