-- Strip legacy `key` from maintenance checklist JSON (template + logs).
-- New shape: [{ "label": "...", "required": true }]
-- Idempotent: safe to re-run.

BEGIN;

-- Asset checklist templates
UPDATE assets
SET maintenance_checklist_json = sub.cleaned
FROM (
  SELECT
    a.id,
    jsonb_agg(
      jsonb_strip_nulls(
        jsonb_build_object(
          'label', COALESCE(NULLIF(trim(elem->>'label'), ''), NULLIF(trim(elem->>'key'), '')),
          'required', COALESCE((elem->>'required')::boolean, true)
        )
      )
      ORDER BY ord
    ) AS cleaned
  FROM assets a
  CROSS JOIN LATERAL jsonb_array_elements(a.maintenance_checklist_json) WITH ORDINALITY AS t(elem, ord)
  WHERE a.maintenance_checklist_json IS NOT NULL
    AND jsonb_typeof(a.maintenance_checklist_json) = 'array'
    AND jsonb_array_length(a.maintenance_checklist_json) > 0
  GROUP BY a.id
) sub
WHERE assets.id = sub.id
  AND sub.cleaned IS NOT NULL;

-- Maintenance log results
UPDATE asset_maintenance_logs
SET checklist_json = sub.cleaned
FROM (
  SELECT
    l.id,
    jsonb_agg(
      jsonb_strip_nulls(
        jsonb_build_object(
          'label', COALESCE(NULLIF(trim(elem->>'label'), ''), NULLIF(trim(elem->>'key'), '')),
          'status', elem->>'status',
          'remarks', NULLIF(elem->>'remarks', '')
        )
      )
      ORDER BY ord
    ) AS cleaned
  FROM asset_maintenance_logs l
  CROSS JOIN LATERAL jsonb_array_elements(l.checklist_json) WITH ORDINALITY AS t(elem, ord)
  WHERE l.checklist_json IS NOT NULL
    AND jsonb_typeof(l.checklist_json) = 'array'
    AND jsonb_array_length(l.checklist_json) > 0
  GROUP BY l.id
) sub
WHERE asset_maintenance_logs.id = sub.id
  AND sub.cleaned IS NOT NULL;

COMMIT;