`migrations/015_community.sql`

61 lines
-- Community + DM threads. Channels: client_public / lawyer_public /
-- dm_client_lawyer / dm_client_client / dm_lawyer_lawyer.

CREATE TABLE IF NOT EXISTS threads (
  id BIGSERIAL PRIMARY KEY,
  channel TEXT NOT NULL CHECK (channel IN (
    'client_public','lawyer_public',
    'dm_client_lawyer','dm_client_client','dm_lawyer_lawyer'
  )),
  topic TEXT,
  organization_id BIGINT REFERENCES organizations(id) ON DELETE SET NULL,
  professional_id BIGINT REFERENCES professionals(id) ON DELETE SET NULL,
  participant_user_ids BIGINT[] NOT NULL DEFAULT '{}',
  created_by BIGINT REFERENCES app_users(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  last_message_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  message_count INT NOT NULL DEFAULT 0,
  closed_at TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS idx_threads_channel_recent ON threads (channel, last_message_at DESC);
CREATE INDEX IF NOT EXISTS idx_threads_participants ON threads USING GIN (participant_user_ids);
CREATE INDEX IF NOT EXISTS idx_threads_org ON threads (organization_id) WHERE organization_id IS NOT NULL;

CREATE TABLE IF NOT EXISTS messages (
  id BIGSERIAL PRIMARY KEY,
  thread_id BIGINT NOT NULL REFERENCES threads(id) ON DELETE CASCADE,
  user_id BIGINT NOT NULL REFERENCES app_users(id) ON DELETE SET NULL,
  body TEXT NOT NULL,
  posted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  edited_at TIMESTAMPTZ,
  hidden_at TIMESTAMPTZ,
  hidden_reason TEXT
);
CREATE INDEX IF NOT EXISTS idx_messages_thread ON messages (thread_id, posted_at);
CREATE INDEX IF NOT EXISTS idx_messages_user ON messages (user_id, posted_at DESC);

-- Reads/seen tracker so we can show unread counts in UI.
CREATE TABLE IF NOT EXISTS thread_reads (
  thread_id BIGINT NOT NULL REFERENCES threads(id) ON DELETE CASCADE,
  user_id BIGINT NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
  last_read_message_id BIGINT REFERENCES messages(id) ON DELETE SET NULL,
  last_read_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (thread_id, user_id)
);

-- Bump thread metadata on each message insert
CREATE OR REPLACE FUNCTION bump_thread_on_message() RETURNS TRIGGER AS $$
BEGIN
  UPDATE threads
    SET last_message_at = NEW.posted_at,
        message_count = message_count + 1
    WHERE id = NEW.thread_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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