-- 0026_observability_hitl: Observability + HITL data model (autonomy roadmap §11).
--
-- 1) audit_logs read/search/retention indexes (idx_audit_action_created already
--    exists from 0001; add the time-only and target indexes).
-- 2) acp_sessions cost_usd / tokens_total columns for the run-history dashboard.
-- 3) project_members team/role model (backfill owner -> 'admin').
-- 4) notification_prefs per-user channel routing (im_webhook_url crypto-encrypted).

-- ============ 1. audit read/search/retention indexes ============
CREATE INDEX IF NOT EXISTS idx_audit_created ON audit_logs (created_at DESC);
CREATE INDEX IF NOT EXISTS idx_audit_target ON audit_logs (target_type, target_id);

-- ============ 2. acp_sessions cost rollup columns ============
-- ADD COLUMN puts new columns LAST physically; SELECT/RETURNING order must match.
ALTER TABLE acp_sessions ADD COLUMN IF NOT EXISTS cost_usd NUMERIC NOT NULL DEFAULT 0;
ALTER TABLE acp_sessions ADD COLUMN IF NOT EXISTS tokens_total BIGINT NOT NULL DEFAULT 0;

-- ============ 3. project_members team/role model ============
CREATE TABLE IF NOT EXISTS project_members (
  project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  user_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role       TEXT NOT NULL CHECK (role IN ('viewer','member','admin')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  added_by   UUID REFERENCES users(id) ON DELETE SET NULL,
  PRIMARY KEY (project_id, user_id)
);

CREATE INDEX IF NOT EXISTS idx_project_members_user ON project_members (user_id);

-- Backfill: every existing project's owner becomes a project 'admin' so the
-- current owner-or-global-admin behavior is preserved under the role model.
INSERT INTO project_members (project_id, user_id, role, added_by)
SELECT p.id, p.owner_id, 'admin', p.owner_id
FROM projects p
ON CONFLICT (project_id, user_id) DO NOTHING;

-- ============ 4. notification_prefs per-user routing ============
CREATE TABLE IF NOT EXISTS notification_prefs (
  user_id            UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  email_enabled      BOOLEAN NOT NULL DEFAULT false,
  im_enabled         BOOLEAN NOT NULL DEFAULT false,
  im_webhook_url_enc BYTEA,
  min_severity       TEXT NOT NULL DEFAULT 'warning' CHECK (min_severity IN ('info','warning','error')),
  updated_at         TIMESTAMPTZ NOT NULL DEFAULT now()
);