-- Structured display names and slug redirect infrastructure
-- Run: psql $DATABASE_URL -f db/migrations/006_display_name_and_redirects.sql

-- Add display_name: the human-readable "Brand Model Colorway" string
-- Used in page titles, product cards, and JSON-LD schema
ALTER TABLE products ADD COLUMN IF NOT EXISTS display_name TEXT;

-- Add model column if not present (raw model name from feed, separate from model_normalized)
ALTER TABLE products ADD COLUMN IF NOT EXISTS model TEXT;

-- Slug redirect table: maps old/hashed slugs to current canonical slugs
-- Enables 301 redirects for legacy URLs and slug changes after backfill
CREATE TABLE IF NOT EXISTS slug_redirects (
  old_slug TEXT PRIMARY KEY,
  new_slug TEXT NOT NULL,
  product_id INTEGER REFERENCES products(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_slug_redirects_new ON slug_redirects(new_slug);
CREATE INDEX IF NOT EXISTS idx_products_display_name ON products(display_name) WHERE display_name IS NOT NULL;
