-- UCR annual-renewal reminder tracking (mirrors IFTA): per-carrier touch number,
-- last-touch timestamp, and "I already did it" self-filed suppression.
-- Reset each year by build_ucr_annual_campaign.py.
--   ucr_reminded_at  : timestamp of the most recent UCR touch
--   ucr_touch_no     : highest touch number sent this cycle (1=30bd,2=12bd,3=4bd)
--   ucr_self_filed_at: clicked "I already registered" -> stop reminding this cycle

ALTER TABLE fmcsa_carriers
  ADD COLUMN IF NOT EXISTS ucr_reminded_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS ucr_touch_no SMALLINT,
  ADD COLUMN IF NOT EXISTS ucr_self_filed_at TIMESTAMPTZ;

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_fmcsa_carriers_ucr_touch
  ON fmcsa_carriers (ucr_touch_no)
  WHERE carrier_operation = 'A';