`db/migrations/017_magazine_addons.sql`

75 lines
-- Migration 017 — magazine layer add-ons created ad-hoc during the
-- 2026-05-06 overnight loop session: monthly cover picker, reader feedback
-- intake, sponsor inquiries persistence, coverage snapshot history.

-- Issue cover picker (iter 138 + 144 image upload)
CREATE TABLE IF NOT EXISTS magazine_issues (
  issue_month       TEXT PRIMARY KEY,                         -- 'YYYY-MM'
  cover_feature_id  BIGINT REFERENCES magazine_features(id) ON DELETE SET NULL,
  cover_caption     TEXT,
  cover_kicker      TEXT,
  cover_image_path  TEXT,                                     -- filename in data/covers/
  cover_set_at      TIMESTAMPTZ,
  published_at      TIMESTAMPTZ,
  notes             TEXT
);

-- Reader feedback intake (iter 130 + 143 George email)
CREATE TABLE IF NOT EXISTS reader_feedback (
  id           BIGSERIAL PRIMARY KEY,
  feature_id   BIGINT,                                         -- nullable; comments on whole issue ok
  kind         TEXT NOT NULL,                                  -- typo|correction|tip|complaint|praise|note
  body         TEXT NOT NULL,
  contact      TEXT,                                           -- optional reader email
  source_path  TEXT,                                           -- where the form was submitted from
  received_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  reviewed     BOOLEAN NOT NULL DEFAULT FALSE
);
CREATE INDEX IF NOT EXISTS idx_reader_feedback_received_at ON reader_feedback (received_at DESC);
CREATE INDEX IF NOT EXISTS idx_reader_feedback_unreviewed ON reader_feedback (received_at DESC) WHERE NOT reviewed;

-- Sponsor inquiries persistence (paired with /sponsor.html, iter ~127)
CREATE TABLE IF NOT EXISTS sponsor_inquiries (
  id                BIGSERIAL PRIMARY KEY,
  feature_id        BIGINT,
  biz_name          TEXT,
  contact_name      TEXT NOT NULL,
  contact_email     TEXT NOT NULL,
  contact_phone     TEXT,
  tier              TEXT,                                       -- 'spread' | 'cover' | 'ask'
  notes             TEXT,
  received_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  status            TEXT NOT NULL DEFAULT 'new',                -- new | responded | closed | spam
  email_message_id  TEXT
);
CREATE INDEX IF NOT EXISTS idx_sponsor_inquiries_received_at ON sponsor_inquiries (received_at DESC);
CREATE INDEX IF NOT EXISTS idx_sponsor_inquiries_status ON sponsor_inquiries (status, received_at DESC);

-- Daily coverage snapshots (iter 158, populates /coverage.html growth line)
CREATE TABLE IF NOT EXISTS coverage_snapshots (
  id                BIGSERIAL PRIMARY KEY,
  taken_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  total_biz         INTEGER NOT NULL,
  biz_with_features INTEGER NOT NULL,
  total_features    INTEGER NOT NULL,
  drafts            INTEGER NOT NULL,
  reviewed          INTEGER NOT NULL,
  published         INTEGER NOT NULL,
  spiked            INTEGER NOT NULL,
  feedback_total    INTEGER NOT NULL DEFAULT 0,
  feedback_unread   INTEGER NOT NULL DEFAULT 0,
  inquiries_total   INTEGER NOT NULL DEFAULT 0,
  inquiries_open    INTEGER NOT NULL DEFAULT 0,
  notes             TEXT
);
CREATE INDEX IF NOT EXISTS idx_coverage_snapshots_taken_at ON coverage_snapshots (taken_at);

-- Editor margin notes column on features (iter 149) — only add if not already there
ALTER TABLE magazine_features
  ADD COLUMN IF NOT EXISTS notes TEXT;

-- Initial empty row for current month so the cover picker has a row to PATCH
INSERT INTO magazine_issues (issue_month, cover_set_at)
SELECT to_char(now(),'YYYY-MM'), now()
ON CONFLICT (issue_month) DO NOTHING;