CREATE TABLE reviews (
  id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4() NOT NULL,
  user_id VARCHAR(64) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  target_id VARCHAR(64) NOT NULL,
  target_type VARCHAR(32) NOT NULL,
  description VARCHAR,
  rating INT NOT NULL,
  deleted_by_user_id VARCHAR(64) REFERENCES users(id) ON DELETE SET NULL,
  deleted_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,
  created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL
);

CREATE TABLE review_summaries (
  id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4() NOT NULL,
  target_id VARCHAR(64) NOT NULL,
  target_type VARCHAR(32) NOT NULL,
  avg_rating FLOAT NOT NULL,
  wilson_score FLOAT NOT NULL,
  review_count INT NOT NULL,
  created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL
);