-- Migration 007: Advanced Hierarchical Shoe Sizing
-- Run: psql -d sneakerpicks -f db/migrations/007_advanced_sizes.sql

BEGIN;

-- ============================================================
-- USER SIZE PREFERENCES (hierarchical: brand + optional subtype)
-- ============================================================

CREATE TABLE IF NOT EXISTS user_size_preferences (
  id            SERIAL PRIMARY KEY,
  user_id       UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  brand_slug    TEXT NOT NULL,        -- e.g. 'nike', 'adidas' (= brand_normalized from products)
  category_slug TEXT,                 -- NULL = brand-level pref; 'nike/air-jordan' = model family pref
  size          TEXT NOT NULL,        -- EU size as string (e.g. '44', '44.5')
  created_at    TIMESTAMPTZ DEFAULT now(),
  updated_at    TIMESTAMPTZ DEFAULT now(),
  -- Allow per-brand OR per-subtype, but not duplicates
  UNIQUE(user_id, brand_slug, COALESCE(category_slug, ''))
);

CREATE INDEX IF NOT EXISTS idx_usp_user ON user_size_preferences(user_id);
CREATE INDEX IF NOT EXISTS idx_usp_user_brand ON user_size_preferences(user_id, brand_slug);
CREATE INDEX IF NOT EXISTS idx_usp_category ON user_size_preferences(category_slug);

-- ============================================================
-- WATCHLIST SIZE OVERRIDE (per-item)
-- ============================================================

ALTER TABLE watchlist ADD COLUMN IF NOT EXISTS size_override TEXT;

COMMENT ON TABLE user_size_preferences IS 'Hierarchical size preferences: default shoe_size < per-brand < per-subtype';
COMMENT ON COLUMN user_size_preferences.category_slug IS 'Null for brand-level, e.g. "nike/air-jordan" for subtype';

COMMIT;
