-- Add first-class automation trigger semantics without breaking existing metric rules.
ALTER TABLE vehicle_alert_rule
  ADD COLUMN trigger_type VARCHAR(24) NOT NULL DEFAULT 'metric' AFTER description,
  ADD COLUMN fence_name VARCHAR(80) NOT NULL DEFAULT '' AFTER trigger_type,
  ADD COLUMN fence_longitude DOUBLE NOT NULL DEFAULT 0 AFTER fence_name,
  ADD COLUMN fence_latitude DOUBLE NOT NULL DEFAULT 0 AFTER fence_longitude,
  ADD COLUMN fence_radius_m DOUBLE NOT NULL DEFAULT 0 AFTER fence_latitude;

ALTER TABLE vehicle_alert_event
  ADD COLUMN trigger_type VARCHAR(24) NOT NULL DEFAULT 'metric' AFTER severity;

CREATE INDEX idx_alert_rule_trigger_type ON vehicle_alert_rule(trigger_type, enabled);
CREATE INDEX idx_alert_event_trigger_type ON vehicle_alert_event(trigger_type, triggered_at);

-- Preserve the business meaning of rules that existed before trigger_type.
UPDATE vehicle_alert_rule
SET trigger_type = CASE
  WHEN metric = 'freshness_sec' THEN 'offline'
  WHEN metric = 'speed_kmh' AND operator IN ('lt','lte') AND duration_sec >= 300 THEN 'stationary'
  ELSE 'metric'
END
WHERE trigger_type = 'metric';