-- 005_beta_10x_features
-- Adds columns and indexes for: re-engagement flow, weekly reflection, feedback loop

-- Re-engagement: track last user response and dormancy
ALTER TABLE users ADD COLUMN IF NOT EXISTS last_responded_at TIMESTAMPTZ;
ALTER TABLE users ADD COLUMN IF NOT EXISTS dormant_since TIMESTAMPTZ;

-- Backfill last_responded_at from existing message data
UPDATE users u
SET last_responded_at = sub.last_in
FROM (
  SELECT m.user_id, MAX(m.created_at) AS last_in
  FROM messages m
  WHERE m.direction = 'in'
  GROUP BY m.user_id
) sub
WHERE u.id = sub.user_id
  AND u.last_responded_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_users_last_responded ON users(last_responded_at);
CREATE INDEX IF NOT EXISTS idx_users_dormant ON users(dormant_since) WHERE dormant_since IS NOT NULL;

-- Index for weekly reflection lookups
CREATE INDEX IF NOT EXISTS idx_memory_events_user_op_created
  ON memory_events(user_id, operation, created_at DESC);
