-- Migration 005: User preferences, Deal DNA, brand follows
-- Run: docker exec supabase-db psql -U supabase_admin -d sneakerpicks -f /tmp/005_user_preferences.sql

-- Extend users table with preference columns
ALTER TABLE users ADD COLUMN IF NOT EXISTS name TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS shoe_size TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS gender_preference TEXT DEFAULT 'all';
ALTER TABLE users ADD COLUMN IF NOT EXISTS alert_threshold_pct INT DEFAULT 15;
ALTER TABLE users ADD COLUMN IF NOT EXISTS alert_frequency TEXT DEFAULT 'instant';
ALTER TABLE users ADD COLUMN IF NOT EXISTS notify_back_in_stock BOOLEAN DEFAULT true;
ALTER TABLE users ADD COLUMN IF NOT EXISTS notify_weekly_digest BOOLEAN DEFAULT true;
ALTER TABLE users ADD COLUMN IF NOT EXISTS notify_new_releases BOOLEAN DEFAULT false;
ALTER TABLE users ADD COLUMN IF NOT EXISTS paused_until TIMESTAMPTZ;
ALTER TABLE users ADD COLUMN IF NOT EXISTS budget_max INT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ DEFAULT NOW();

-- Brand follows with per-brand alert thresholds
CREATE TABLE IF NOT EXISTS user_brand_follows (
  id SERIAL PRIMARY KEY,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  brand_slug TEXT NOT NULL,
  brand_name TEXT NOT NULL,
  alert_threshold_pct INT DEFAULT 15,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(user_id, brand_slug)
);

-- Grant permissions
GRANT ALL ON TABLE user_brand_follows TO sneakerpicks;
GRANT ALL ON SEQUENCE user_brand_follows_id_seq TO sneakerpicks;

SELECT 'Migration 005 complete' as status;
