`db/migrations/018_paper_threads.sql`

111 lines
-- 018 · "This Paper" peer-installer commentary (UX idea #3)
--
-- Tribal wallcovering knowledge — install quirks per paper (drop %, paste
-- type, soak time, seam matching) — lives in 3,000 working installers' heads
-- worldwide, in private Slacks and IG DMs. Make it the public-facing
-- infrastructure of the directory and capture the whole surface.
--
-- Threads are seeded by ops + claimed by craft (any installer can add a new
-- paper). Comments are PUBLIC-READABLE but only verified installer members
-- can WRITE — keeps the conversation craft-quality, gives designers a
-- selection signal (which installers contribute), and makes the directory
-- the canonical reference for "how do you install <paper>."
--
-- Reversible:
--   DROP TABLE paper_comments;
--   DROP TABLE paper_threads;

BEGIN;

CREATE TABLE IF NOT EXISTS paper_threads (
  id           SERIAL PRIMARY KEY,
  slug         TEXT NOT NULL UNIQUE,
                -- url-safe identifier — generated from brand + paper_name
                -- e.g. "de-gournay-earlham", "fromental-bois", "maya-romanoff-ajiro"
  brand        TEXT NOT NULL,
  paper_name   TEXT NOT NULL,
  sku          TEXT,
                -- manufacturer SKU if known — multiple SKUs may map to one thread
                -- (different colorways of the same paper share install quirks)
  paste_type   TEXT,
                -- typical paste recommendation — strippable | clay | wheat | varies
  category     TEXT,
                -- silk | grasscloth | chinoiserie | mural | metallic | flock | foil | embroidered | varies
  description  TEXT,
                -- 1-2 paragraph intro to the paper, written by ops or by the
                -- first contributing installer. Edited only by ops + admins.
  seeded_by    TEXT,
                -- nph_ops | <installer_slug> — provenance for the thread
  comment_count INTEGER NOT NULL DEFAULT 0,
                -- denormalized counter, incremented on insert into paper_comments
                -- (kept fresh by trigger below)
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_paper_threads_brand ON paper_threads(brand, paper_name);
CREATE INDEX IF NOT EXISTS idx_paper_threads_category ON paper_threads(category);
CREATE INDEX IF NOT EXISTS idx_paper_threads_count ON paper_threads(comment_count DESC);

CREATE TABLE IF NOT EXISTS paper_comments (
  id            SERIAL PRIMARY KEY,
  thread_id     INTEGER NOT NULL REFERENCES paper_threads(id) ON DELETE CASCADE,
  installer_id  INTEGER NOT NULL REFERENCES installers(id) ON DELETE CASCADE,
                -- installer who wrote it — gates write via requireInstaller +
                -- (later) installer_members.role IN ('owner','member')
  body          TEXT NOT NULL,
  helpful_count INTEGER NOT NULL DEFAULT 0,
  flagged       BOOLEAN NOT NULL DEFAULT false,
                -- ops-flagged for review — hidden from public
  edited_at     TIMESTAMPTZ,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_paper_comments_thread ON paper_comments(thread_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_paper_comments_installer ON paper_comments(installer_id, created_at DESC);

-- Keep comment_count fresh — light-weight trigger.
CREATE OR REPLACE FUNCTION paper_threads_recount() RETURNS trigger AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE paper_threads SET comment_count = comment_count + 1,
                              updated_at   = now()
     WHERE id = NEW.thread_id;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE paper_threads SET comment_count = GREATEST(0, comment_count - 1),
                              updated_at   = now()
     WHERE id = OLD.thread_id;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_paper_comments_count ON paper_comments;
CREATE TRIGGER trg_paper_comments_count
  AFTER INSERT OR DELETE ON paper_comments
  FOR EACH ROW EXECUTE FUNCTION paper_threads_recount();

-- Seed 5 example threads — canonical luxury wallcovering papers that almost
-- every working installer has an opinion on. These give the section
-- immediate content on launch; the description copy is short and factual
-- so it doesn't pre-empt installer contributions.
INSERT INTO paper_threads (slug, brand, paper_name, paste_type, category, description, seeded_by) VALUES
  ('de-gournay-earlham', 'de Gournay', 'Earlham', 'wheat', 'chinoiserie',
   'Hand-painted chinoiserie on hand-dyed silk. Panel-based (not roll-based) — each panel comes pre-cut to ceiling height. Wheat paste applied to the wall, not the paper. Soak-time is critical: too short and the silk slips during alignment; too long and the ink bleeds. Standard panel width ~91 cm.',
   'nph_ops'),
  ('fromental-bois', 'Fromental', 'Bois', 'clay', 'chinoiserie',
   'Hand-painted on silk with embroidered detail elements on select skus. Like de Gournay, sold as panels — but Fromental panels accept clay paste better than wheat in humid environments. Embroidery sections require a soft-roller pass with light pressure during the dry-down phase.',
   'nph_ops'),
  ('phillip-jeffries-manila-hemp', 'Phillip Jeffries', 'Manila Hemp', 'clay', 'grasscloth',
   'The benchmark grasscloth — natural variation between panels is the design, not the defect. Reverse-hang every other strip to balance shade variation. Clay paste is mandatory; vinyl paste causes staining. Trim selvedge with a straightedge — pre-trimmed bolts are rare and inconsistent.',
   'nph_ops'),
  ('maya-romanoff-ajiro', 'Maya Romanoff', 'Ajiro', 'strippable', 'specialty',
   'Origami-folded wood veneer on cloth backing. The fold pattern means every cut must be measured TWICE — there is no "average" repeat. Strippable paste only; clay attacks the veneer adhesive. Practice on a sacrificial panel before going to the wall.',
   'nph_ops'),
  ('schumacher-vassily', 'Schumacher', 'Vassily', 'clay', 'metallic',
   'Metallic ground with screen-printed pattern. Two installer pitfalls: (1) the metallic ground reads differently under warm vs. cool light — always confirm orientation matches the installer-supplied sample; (2) clay paste smudges easily, so wipe seams immediately and never reuse the wiping cloth.',
   'nph_ops')
ON CONFLICT (slug) DO NOTHING;

COMMIT;