-- BizMatch Phase 2 — module 6a fix: ds_mirror_covers_from may only widen
--
-- The value had drifted to a date *later* than the mirror's own oldest row,
-- so the inbox announced "showing data from <late date>" over a table that in
-- fact reached months further back. Coverage is now written as
-- LEAST(existing, reached) (see widenCoverage() in src/nda-refresh.ts); this
-- corrects the value that is already stored, once, from the only source that
-- cannot be wrong about it — the mirror itself.
--
-- Day-truncated: the mirror holds every request of that day, so midnight is
-- the honest bound and it is never narrower than the timestamp.

WITH mirror AS (
  SELECT date_trunc('day', min(created_at) AT TIME ZONE 'UTC') AS from_at FROM ds_request
)
INSERT INTO app_meta (key, value)
SELECT 'ds_mirror_covers_from',
       to_char(from_at, 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"')
  FROM mirror
 WHERE from_at IS NOT NULL
ON CONFLICT (key) DO UPDATE
  SET value = least(app_meta.value, EXCLUDED.value), updated_at = now()
  -- Only ever widens: a stored value that already reaches further back stands.
  WHERE app_meta.value IS NULL OR app_meta.value > EXCLUDED.value;
