54 lines
2.5 KiB
MySQL
54 lines
2.5 KiB
MySQL
|
-- Migration 032: User Focus Area Weights
|
||
|
|
-- Date: 2026-03-27
|
||
|
|
-- Purpose: Allow users to set custom weights for focus areas (dynamic preferences)
|
||
|
|
|
||
|
|
-- ============================================================================
|
||
|
|
-- User Focus Area Weights (many-to-many with weights)
|
||
|
|
-- ============================================================================
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS user_focus_area_weights (
|
||
|
|
profile_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
|
||
|
|
focus_area_id UUID NOT NULL REFERENCES focus_area_definitions(id) ON DELETE CASCADE,
|
||
|
|
weight INTEGER NOT NULL DEFAULT 0 CHECK (weight >= 0 AND weight <= 100),
|
||
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMP DEFAULT NOW(),
|
||
|
|
PRIMARY KEY (profile_id, focus_area_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX idx_user_focus_weights_profile ON user_focus_area_weights(profile_id);
|
||
|
|
CREATE INDEX idx_user_focus_weights_area ON user_focus_area_weights(focus_area_id);
|
||
|
|
|
||
|
|
COMMENT ON TABLE user_focus_area_weights IS 'User-specific weights for focus areas (dynamic system)';
|
||
|
|
COMMENT ON COLUMN user_focus_area_weights.weight IS 'Relative weight (0-100) - will be normalized to percentages in UI';
|
||
|
|
|
||
|
|
-- ============================================================================
|
||
|
|
-- Migrate legacy preferences to dynamic weights
|
||
|
|
-- ============================================================================
|
||
|
|
|
||
|
|
-- For each user with legacy preferences, create weights for the 6 base areas
|
||
|
|
INSERT INTO user_focus_area_weights (profile_id, focus_area_id, weight)
|
||
|
|
SELECT
|
||
|
|
ufp.profile_id,
|
||
|
|
fad.id as focus_area_id,
|
||
|
|
CASE fad.key
|
||
|
|
WHEN 'weight_loss' THEN ufp.weight_loss_pct
|
||
|
|
WHEN 'muscle_gain' THEN ufp.muscle_gain_pct
|
||
|
|
WHEN 'strength' THEN ufp.strength_pct
|
||
|
|
WHEN 'aerobic_endurance' THEN ufp.endurance_pct
|
||
|
|
WHEN 'flexibility' THEN ufp.flexibility_pct
|
||
|
|
WHEN 'general_health' THEN ufp.health_pct
|
||
|
|
ELSE 0
|
||
|
|
END as weight
|
||
|
|
FROM user_focus_preferences ufp
|
||
|
|
CROSS JOIN focus_area_definitions fad
|
||
|
|
WHERE fad.key IN ('weight_loss', 'muscle_gain', 'strength', 'aerobic_endurance', 'flexibility', 'general_health')
|
||
|
|
AND (
|
||
|
|
(fad.key = 'weight_loss' AND ufp.weight_loss_pct > 0) OR
|
||
|
|
(fad.key = 'muscle_gain' AND ufp.muscle_gain_pct > 0) OR
|
||
|
|
(fad.key = 'strength' AND ufp.strength_pct > 0) OR
|
||
|
|
(fad.key = 'aerobic_endurance' AND ufp.endurance_pct > 0) OR
|
||
|
|
(fad.key = 'flexibility' AND ufp.flexibility_pct > 0) OR
|
||
|
|
(fad.key = 'general_health' AND ufp.health_pct > 0)
|
||
|
|
)
|
||
|
|
ON CONFLICT (profile_id, focus_area_id) DO NOTHING;
|