`db/002_gallery_tiers.sql`

68 lines
-- 002_gallery_tiers.sql — The Blvd Gallery + tiered business store.
-- Designed by debate-team-fast 2026-05-12. Per-business AI-generated art
-- metadata + 3-tier subscription ladder per category.

-- Tier columns on businesses (basic free; featured/premium paid)
ALTER TABLE businesses ADD COLUMN IF NOT EXISTS tier VARCHAR(20) DEFAULT 'basic';
ALTER TABLE businesses ADD COLUMN IF NOT EXISTS stripe_customer_id VARCHAR(64);
ALTER TABLE businesses ADD COLUMN IF NOT EXISTS stripe_subscription_id VARCHAR(64);
ALTER TABLE businesses ADD COLUMN IF NOT EXISTS tier_expires_at TIMESTAMPTZ;

CREATE INDEX IF NOT EXISTS businesses_tier ON businesses(tier) WHERE tier != 'basic';

-- Gallery pieces (one per business, generated overnight)
CREATE TABLE IF NOT EXISTS gallery_pieces (
  id            SERIAL PRIMARY KEY,
  business_id   INT REFERENCES businesses(id) ON DELETE CASCADE,
  art_title     TEXT NOT NULL,
  art_medium    VARCHAR(60),          -- mural, print, sculpture, neon, ceramic, etc.
  art_year_est  INT,
  art_description TEXT,
  color_palette JSONB,                -- ["#c84b31","#f5e6c8","#2d4a3e"]
  image_url     TEXT,
  image_source  VARCHAR(20),          -- 'ai_generated', 'user_upload', 'placeholder'
  lon           DECIMAL(10,7),
  lat           DECIMAL(10,7),
  neighborhood  TEXT,                 -- Studio City | Sherman Oaks | Encino
  position_pct  NUMERIC(5,2),         -- 0.00 (east, Encino) → 100.00 (west, Studio City)
  generator     VARCHAR(40) DEFAULT 'corridor-design-system',  -- user-visible: never vendor name
  created_at    TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (business_id)
);

CREATE INDEX IF NOT EXISTS gallery_pos        ON gallery_pieces(position_pct);
CREATE INDEX IF NOT EXISTS gallery_neighbor   ON gallery_pieces(neighborhood);
CREATE INDEX IF NOT EXISTS gallery_business   ON gallery_pieces(business_id);

-- Tier pricing (per category × tier)
CREATE TABLE IF NOT EXISTS tier_pricing (
  category        VARCHAR(80) NOT NULL,
  tier            VARCHAR(20) NOT NULL,
  display_name    TEXT NOT NULL,
  price_cents     INT NOT NULL,
  stripe_price_id VARCHAR(64),
  marketing_copy  JSONB,              -- { title, subtitle, bullets[], cta }
  created_at      TIMESTAMPTZ DEFAULT NOW(),
  PRIMARY KEY (category, tier)
);

-- Per-business uploaded / paid-tier assets
CREATE TABLE IF NOT EXISTS tier_assets (
  id            SERIAL PRIMARY KEY,
  business_id   INT NOT NULL REFERENCES businesses(id) ON DELETE CASCADE,
  asset_type    VARCHAR(40),          -- logo, photo, video_url, chef_bio, bar_number, booking_url
  asset_value   TEXT,
  sort_order    INT DEFAULT 0,
  created_at    TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS tier_assets_business ON tier_assets(business_id);

-- Lead capture (premium tier feature for law firms / salons)
CREATE TABLE IF NOT EXISTS tier_leads (
  id            SERIAL PRIMARY KEY,
  business_id   INT REFERENCES businesses(id),
  lead_data     JSONB,
  emailed_at    TIMESTAMPTZ,
  created_at    TIMESTAMPTZ DEFAULT NOW()
);