-- =============================================================================
-- Notify event taxonomy + per-user subscriptions (Phase 9 — SUB-01, SUB-02)
-- =============================================================================
-- notify_event_keys: humanization layer for the event keys that appear in
--   pipeline_steps.config->'route_to_user'->>'event_key'. Admin-managed at
--   /admin/workflow/event-keys. NOT a gate — keys not in this table are
--   still routable; they just render with the raw key as their label.
--
-- user_event_subscriptions: opt-out matrix per (user, event_key, channel_type).
--   Row absence = enabled (D-15 default-enabled / opt-out).
-- =============================================================================

CREATE TABLE IF NOT EXISTS notify_event_keys (
  key TEXT PRIMARY KEY,
  display_label TEXT NOT NULL,
  description TEXT,
  sort_order INTEGER NOT NULL DEFAULT 0,
  is_active BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_notify_event_keys_active
  ON notify_event_keys(is_active, sort_order, key);

CREATE TABLE IF NOT EXISTS user_event_subscriptions (
  user_id      TEXT        NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
  event_key    TEXT        NOT NULL,
  channel_type VARCHAR(20) NOT NULL,
  enabled      BOOLEAN     NOT NULL DEFAULT true,
  updated_at   TIMESTAMP   NOT NULL DEFAULT NOW(),
  PRIMARY KEY (user_id, event_key, channel_type)
);

CREATE INDEX IF NOT EXISTS idx_user_event_subs_user
  ON user_event_subscriptions(user_id);

COMMENT ON TABLE notify_event_keys IS
  'Humanization layer for event keys used in notify-step route_to_user blocks. Admins curate display_label/description/sort. Not a gate — unknown keys are still routable.';
COMMENT ON TABLE user_event_subscriptions IS
  'Opt-out matrix. Row absence = enabled. Composite PK enforces one row per (user, event_key, channel_type).';

-- Seed an example row so the admin UI is not empty on first run.
INSERT INTO notify_event_keys (key, display_label, description, sort_order, is_active)
VALUES ('ticket_assigned_to_me', 'Ticket assigned to me', 'Fires when an Autotask ticket is assigned to your resource.', 10, true)
ON CONFLICT (key) DO NOTHING;