-- Create user_behavior_profile table for efficient behavior aggregation
-- Tracks brand + subtype (model) preference at compound level for precise personalization
CREATE TABLE user_behavior_profile (
  user_id             UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  viewed_brands       JSONB DEFAULT '{}',       -- {"nike": 12, "adidas": 4, ...} — brand-level rollup
  viewed_subtypes     JSONB DEFAULT '{}',       -- {"nike": {"air_max_90": 8, "air_force_1": 2}, "adidas": {"nmd": 3}} — brand->model nesting
  clicked_brands      JSONB DEFAULT '{}',       -- affiliate clicks (high intent) {"nike": 3}
  clicked_subtypes    JSONB DEFAULT '{}',       -- affiliate clicks on subtypes {"nike": {"air_max_90": 2}} — brand->model nesting
  viewed_price_buckets JSONB DEFAULT '{}',      -- {"0-100": 2, "100-150": 8, "150-200": 5, "200+": 1}
  viewed_genders      JSONB DEFAULT '{}',       -- {"heren": 12, "dames": 0, "unisex": 3}
  last_viewed_ids     INT[]  DEFAULT '{}',      -- rolling last 20 product IDs
  total_views         INT    DEFAULT 0,
  total_clicks        INT    DEFAULT 0,
  updated_at          TIMESTAMPTZ DEFAULT NOW()
);

-- Add user_id to page_views for attribution
ALTER TABLE page_views ADD COLUMN user_id UUID REFERENCES users(id) ON DELETE SET NULL;
CREATE INDEX idx_page_views_user_id ON page_views(user_id);

-- Add quiz answers to users table for cold-start personalization
ALTER TABLE users ADD COLUMN quiz_brand TEXT;
ALTER TABLE users ADD COLUMN quiz_budget INT;
ALTER TABLE users ADD COLUMN quiz_size TEXT;
ALTER TABLE users ADD COLUMN quiz_style TEXT;

-- Add user_id to click_events for behavioral tracking
ALTER TABLE click_events ADD COLUMN user_id UUID REFERENCES users(id) ON DELETE SET NULL;
CREATE INDEX idx_click_events_user_id ON click_events(user_id);

-- Expand user_taste_profile to include subtypes alongside brands
ALTER TABLE user_taste_profile ADD COLUMN preferred_subtypes JSONB DEFAULT '[]';
-- Format: [{"subtype": "nike_air_max_90", "score": 15}, {"subtype": "adidas_nmd", "score": 8}]
