`db/migrations/005_community.sql`

104 lines
-- Phase 4 — community threads + private DMs.
-- Apply: psql -d doctor_professional_directory -f db/migrations/005_community.sql

BEGIN;

-- ─── threads ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS threads (
  id              bigserial PRIMARY KEY,
  title           text NOT NULL,
  body            text,
  city            text,
  zip             text,
  organization_id bigint REFERENCES organizations(id) ON DELETE SET NULL,   -- optional anchor to a practice
  category        text NOT NULL DEFAULT 'general'
                   CHECK (category IN ('general','recommendations','specialty','insurance','pediatrics','eldercare','mental_health','dental','optometry','question','rant')),
  created_by_user_id bigint REFERENCES users(id) ON DELETE SET NULL,
  hidden_by_admin boolean NOT NULL DEFAULT false,
  pinned          boolean NOT NULL DEFAULT false,
  reply_count     integer NOT NULL DEFAULT 0,
  last_post_at    timestamptz NOT NULL DEFAULT now(),
  created_at      timestamptz NOT NULL DEFAULT now(),
  updated_at      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_threads_visible ON threads (last_post_at DESC) WHERE hidden_by_admin = false;
CREATE INDEX IF NOT EXISTS idx_threads_city    ON threads (city) WHERE city IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_threads_org     ON threads (organization_id) WHERE organization_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_threads_cat     ON threads (category);

DROP TRIGGER IF EXISTS threads_set_updated_at ON threads;
CREATE TRIGGER threads_set_updated_at BEFORE UPDATE ON threads
  FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp();

-- ─── thread_posts ─────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS thread_posts (
  id              bigserial PRIMARY KEY,
  thread_id       bigint NOT NULL REFERENCES threads(id) ON DELETE CASCADE,
  author_user_id  bigint REFERENCES users(id) ON DELETE SET NULL,
  body            text NOT NULL,
  hidden_by_admin boolean NOT NULL DEFAULT false,
  created_at      timestamptz NOT NULL DEFAULT now(),
  updated_at      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_posts_thread ON thread_posts (thread_id, created_at);
CREATE INDEX IF NOT EXISTS idx_posts_author ON thread_posts (author_user_id) WHERE author_user_id IS NOT NULL;

DROP TRIGGER IF EXISTS posts_set_updated_at ON thread_posts;
CREATE TRIGGER posts_set_updated_at BEFORE UPDATE ON thread_posts
  FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp();

-- Bump thread.last_post_at + reply_count on every new post.
CREATE OR REPLACE FUNCTION trigger_bump_thread_on_post() RETURNS trigger AS $$
BEGIN
  UPDATE threads
     SET last_post_at = NEW.created_at,
         reply_count  = reply_count + 1,
         updated_at   = now()
   WHERE id = NEW.thread_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS posts_bump_thread ON thread_posts;
CREATE TRIGGER posts_bump_thread AFTER INSERT ON thread_posts
  FOR EACH ROW EXECUTE FUNCTION trigger_bump_thread_on_post();

-- ─── dm_pairs ─────────────────────────────────────────────────────────────
-- Canonical pair representation: user_a_id < user_b_id (always).
CREATE TABLE IF NOT EXISTS dm_pairs (
  id              bigserial PRIMARY KEY,
  user_a_id       bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  user_b_id       bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  last_message_at timestamptz,
  created_at      timestamptz NOT NULL DEFAULT now(),
  CHECK (user_a_id < user_b_id),
  UNIQUE (user_a_id, user_b_id)
);
CREATE INDEX IF NOT EXISTS idx_dmp_a ON dm_pairs (user_a_id, last_message_at DESC NULLS LAST);
CREATE INDEX IF NOT EXISTS idx_dmp_b ON dm_pairs (user_b_id, last_message_at DESC NULLS LAST);

-- ─── messages ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS messages (
  id              bigserial PRIMARY KEY,
  dm_pair_id      bigint NOT NULL REFERENCES dm_pairs(id) ON DELETE CASCADE,
  author_user_id  bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  body            text NOT NULL,
  sent_at         timestamptz NOT NULL DEFAULT now(),
  read_at         timestamptz
);
CREATE INDEX IF NOT EXISTS idx_msgs_pair ON messages (dm_pair_id, sent_at);
CREATE INDEX IF NOT EXISTS idx_msgs_unread ON messages (dm_pair_id) WHERE read_at IS NULL;

CREATE OR REPLACE FUNCTION trigger_bump_pair_on_msg() RETURNS trigger AS $$
BEGIN
  UPDATE dm_pairs SET last_message_at = NEW.sent_at WHERE id = NEW.dm_pair_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS msgs_bump_pair ON messages;
CREATE TRIGGER msgs_bump_pair AFTER INSERT ON messages
  FOR EACH ROW EXECUTE FUNCTION trigger_bump_pair_on_msg();

COMMIT;