`db/drops.sql`

73 lines
-- Daily-drops subscription service.

DROP TABLE IF EXISTS deliveries CASCADE;
DROP TABLE IF EXISTS drop_items CASCADE;
DROP TABLE IF EXISTS drops CASCADE;
DROP TABLE IF EXISTS subscribers CASCADE;

CREATE TABLE subscribers (
  id                 SERIAL PRIMARY KEY,
  email              TEXT UNIQUE NOT NULL,
  name               TEXT,
  token              TEXT UNIQUE NOT NULL,               -- portal + unsubscribe link
  tier               TEXT NOT NULL DEFAULT 'trial',      -- 'trial' | 'standard' | 'pro' | 'comp'
  status             TEXT NOT NULL DEFAULT 'active',     -- 'active' | 'paused' | 'cancelled'
  trial_drops_left   INT DEFAULT 3,
  stripe_customer_id TEXT,
  stripe_sub_id      TEXT,
  timezone           TEXT DEFAULT 'America/Los_Angeles',
  signed_up_at       TIMESTAMPTZ DEFAULT NOW(),
  last_delivered_at  TIMESTAMPTZ,
  cancelled_at       TIMESTAMPTZ,
  notes              TEXT
);

CREATE INDEX idx_subs_status ON subscribers(status);
CREATE INDEX idx_subs_tier   ON subscribers(tier);

CREATE TABLE drops (
  id             SERIAL PRIMARY KEY,
  drop_date      DATE UNIQUE NOT NULL,
  subject        TEXT NOT NULL,
  intro          TEXT,                        -- 1-2 sentence hook
  outro          TEXT,                        -- closing note / CTA
  status         TEXT NOT NULL DEFAULT 'draft', -- 'draft' | 'published' | 'sent'
  composed_by    TEXT DEFAULT 'qwen',
  created_at     TIMESTAMPTZ DEFAULT NOW(),
  published_at   TIMESTAMPTZ,
  sent_at        TIMESTAMPTZ
);

CREATE INDEX idx_drops_status ON drops(status);

-- Flexible join: drop_items can point at items (expired marks) OR brand_candidates (unregistered).
CREATE TABLE drop_items (
  id             SERIAL PRIMARY KEY,
  drop_id        INT NOT NULL REFERENCES drops(id) ON DELETE CASCADE,
  position       INT NOT NULL,
  source_kind    TEXT NOT NULL,  -- 'item' | 'brand'
  source_id      INT NOT NULL,   -- id in items or brand_candidates
  tier_gate      TEXT DEFAULT 'standard',  -- 'standard' = all tiers see it, 'pro' = only pro sees full version
  headline       TEXT NOT NULL,
  blurb          TEXT NOT NULL,
  cta_text       TEXT,
  cta_url        TEXT,
  pro_extra      TEXT,           -- pro-only additional content (SWOT preview, etc.)
  UNIQUE (drop_id, position)
);

CREATE TABLE deliveries (
  id             SERIAL PRIMARY KEY,
  drop_id        INT NOT NULL REFERENCES drops(id) ON DELETE CASCADE,
  subscriber_id  INT NOT NULL REFERENCES subscribers(id) ON DELETE CASCADE,
  sent_at        TIMESTAMPTZ DEFAULT NOW(),
  delivered_via  TEXT,           -- 'resend' | 'smtp' | 'file' | 'manual'
  opened_at      TIMESTAMPTZ,
  bounced        BOOLEAN DEFAULT FALSE,
  error          TEXT,
  UNIQUE (drop_id, subscriber_id)
);

CREATE INDEX idx_deliveries_drop ON deliveries(drop_id);
CREATE INDEX idx_deliveries_sub  ON deliveries(subscriber_id);