`db/migrations/004_reviews.sql`

104 lines
-- Phase 3 — reviews, ratings, aggregation cache.
-- Apply: psql -d doctor_professional_directory -f db/migrations/004_reviews.sql

BEGIN;

-- ─── reviews ───────────────────────────────────────────────────────────────
-- One review per (reviewer, target). Patients post under role='patient'+tier='free'+;
-- doctors can also post under specific guardrails (no self-review). Reddit/Yelp/HG
-- seeded reviews use source != 'user' and have no reviewer_user_id.
CREATE TABLE IF NOT EXISTS reviews (
  id                   bigserial PRIMARY KEY,
  target_professional_id bigint REFERENCES professionals(id) ON DELETE CASCADE,
  target_organization_id bigint REFERENCES organizations(id) ON DELETE CASCADE,
  reviewer_user_id     bigint REFERENCES users(id) ON DELETE SET NULL,
  reviewer_display     text,                          -- snapshot for seeded reviews
  service_score        smallint CHECK (service_score IS NULL OR service_score BETWEEN 1 AND 5),
  price_score          smallint CHECK (price_score   IS NULL OR price_score   BETWEEN 1 AND 5),
  quality_score        smallint CHECK (quality_score IS NULL OR quality_score BETWEEN 1 AND 5),
  overall_score        smallint CHECK (overall_score IS NULL OR overall_score BETWEEN 1 AND 5),
  title                text,
  body                 text NOT NULL,
  source               text NOT NULL DEFAULT 'user'
                         CHECK (source IN ('user','reddit','yelp_seed','healthgrades_seed','nextdoor')),
  source_url           text,
  source_posted_at     timestamptz,
  suppressed_by_target boolean NOT NULL DEFAULT false,   -- patient claims "not me" / opts out their profile
  hidden_by_admin      boolean NOT NULL DEFAULT false,
  hidden_reason        text,
  go_live_at           timestamptz,                     -- 24h delay before public view (anti-brigading)
  created_at           timestamptz NOT NULL DEFAULT now(),
  updated_at           timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT either_target_review CHECK (
    (target_professional_id IS NOT NULL AND target_organization_id IS NULL) OR
    (target_professional_id IS NULL AND target_organization_id IS NOT NULL)
  )
);
CREATE INDEX IF NOT EXISTS idx_reviews_target_pro     ON reviews (target_professional_id) WHERE target_professional_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_reviews_target_org     ON reviews (target_organization_id) WHERE target_organization_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_reviews_source         ON reviews (source);
CREATE INDEX IF NOT EXISTS idx_reviews_reviewer       ON reviews (reviewer_user_id) WHERE reviewer_user_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_reviews_visible        ON reviews (target_professional_id, target_organization_id)
   WHERE suppressed_by_target = false AND hidden_by_admin = false;
-- Anti-double-review (only one user-source review per reviewer/target pair).
CREATE UNIQUE INDEX IF NOT EXISTS idx_reviews_one_per_user_pro
   ON reviews (reviewer_user_id, target_professional_id)
   WHERE source='user' AND reviewer_user_id IS NOT NULL AND target_professional_id IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_reviews_one_per_user_org
   ON reviews (reviewer_user_id, target_organization_id)
   WHERE source='user' AND reviewer_user_id IS NOT NULL AND target_organization_id IS NOT NULL;
-- Dedup of seeded reviews by source URL.
CREATE UNIQUE INDEX IF NOT EXISTS idx_reviews_source_url
   ON reviews (source_url) WHERE source_url IS NOT NULL;

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

-- ─── review_responses ─────────────────────────────────────────────────────
-- Doctor's official reply on a review. One per review.
CREATE TABLE IF NOT EXISTS review_responses (
  id              bigserial PRIMARY KEY,
  review_id       bigint NOT NULL UNIQUE REFERENCES reviews(id) ON DELETE CASCADE,
  author_user_id  bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  body            text NOT NULL,
  created_at      timestamptz NOT NULL DEFAULT now(),
  updated_at      timestamptz NOT NULL DEFAULT now()
);
DROP TRIGGER IF EXISTS review_responses_set_updated_at ON review_responses;
CREATE TRIGGER review_responses_set_updated_at BEFORE UPDATE ON review_responses
  FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp();

-- ─── review_flags (community moderation) ──────────────────────────────────
CREATE TABLE IF NOT EXISTS review_flags (
  id            bigserial PRIMARY KEY,
  review_id     bigint NOT NULL REFERENCES reviews(id) ON DELETE CASCADE,
  flagger_user_id bigint REFERENCES users(id) ON DELETE SET NULL,
  reason        text NOT NULL CHECK (reason IN ('spam','fake','off_topic','medical_advice','personal_attack','other')),
  notes         text,
  created_at    timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_review_flags_review ON review_flags (review_id);

-- ─── aggregated_ratings (denormalized) ────────────────────────────────────
-- Recomputed nightly by scripts/aggregate-ratings.js. Honors suppression + hidden.
CREATE TABLE IF NOT EXISTS aggregated_ratings (
  id                       bigserial PRIMARY KEY,
  target_professional_id   bigint UNIQUE REFERENCES professionals(id) ON DELETE CASCADE,
  target_organization_id   bigint UNIQUE REFERENCES organizations(id) ON DELETE CASCADE,
  service_avg              numeric(3,2),
  price_avg                numeric(3,2),
  quality_avg              numeric(3,2),
  overall_avg              numeric(3,2),
  n_reviews                integer NOT NULL DEFAULT 0,
  n_user_reviews           integer NOT NULL DEFAULT 0,
  n_seeded_reviews         integer NOT NULL DEFAULT 0,
  last_recomputed_at       timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT either_target_agg CHECK (
    (target_professional_id IS NOT NULL AND target_organization_id IS NULL) OR
    (target_professional_id IS NULL AND target_organization_id IS NOT NULL)
  )
);

COMMIT;