drizzle/0002_nosy_guardian.sql

Linked repository file from this build.

CREATE INDEX "inbox_events_recipient_time_idx" ON "inbox_events" USING btree ("staff_id","created_at");--> statement-breakpoint
CREATE INDEX "inbox_events_recipient_read_idx" ON "inbox_events" USING btree ("staff_id","read_at");--> statement-breakpoint
CREATE INDEX "review_index_examiner_state_idx" ON "review_index" USING btree ("examiner","state");--> statement-breakpoint
CREATE INDEX "review_index_program_month_idx" ON "review_index" USING btree ("program","sample_month");--> statement-breakpoint
CREATE INDEX "review_touches_staff_time_idx" ON "review_touches" USING btree ("staff_id","touched_at");--> statement-breakpoint
INSERT INTO review_index (review_number, case_number, client_name, examiner, supervisor, program, sample_month, state, outcome, appointment, version)
SELECT DISTINCT ON (review_number)
 review_number, review->>'caseNumber', review->>'clientName', review->>'examiner',
 CASE WHEN review->>'examiner' IN ('101','102','103','104') THEN '201'
      WHEN review->>'examiner' IN ('105','106','107','108') THEN '202'
      ELSE review->>'supervisor' END,
 review->>'program', review->>'sampleMonth',
 review->>'state',
 review->>'outcome', review->>'appointment', version
FROM snap_review_snapshots ORDER BY review_number, version DESC
ON CONFLICT (review_number) DO NOTHING;
--> statement-breakpoint
INSERT INTO inbox_events (id, review_number, staff_id, kind, actor_role, created_at)
SELECT 'backfill-' || review_number || '-' || recipient.staff_id, review_number, recipient.staff_id,
 'distributed', 'admin', CURRENT_TIMESTAMP
FROM review_index CROSS JOIN LATERAL (SELECT DISTINCT unnest(ARRAY[examiner, supervisor]) AS staff_id) recipient
WHERE state = 'new_active' AND sample_month = to_char(CURRENT_DATE, 'YYYY-MM') AND recipient.staff_id <> ''
ON CONFLICT (id) DO NOTHING;