-- Migration: Fix user_id Type Inconsistency
-- Date: 2026-01-22
-- Description: Change user_id from TEXT to UUID in rep_weekly_snapshots and call_quality_scores
-- Risk: Low (new tables with limited data)

-- ============================================
-- IMPORTANT: Run pre-migration checks FIRST!
-- ============================================
-- These queries should return 0 rows. If they return data, clean it up before proceeding.
--
-- SELECT user_id FROM rep_weekly_snapshots
-- WHERE user_id !~ '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$';
--
-- SELECT user_id FROM call_quality_scores
-- WHERE user_id !~ '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$';

-- ============================================
-- Step 1: Drop existing indexes (they reference old type)
-- ============================================
DROP INDEX IF EXISTS idx_rep_weekly_user_week;
DROP INDEX IF EXISTS idx_rep_weekly_unique;
DROP INDEX IF EXISTS idx_call_scores_user;

-- ============================================
-- Step 2: Alter column types
-- ============================================
-- rep_weekly_snapshots
ALTER TABLE rep_weekly_snapshots
ALTER COLUMN user_id TYPE UUID USING user_id::uuid;

-- call_quality_scores
ALTER TABLE call_quality_scores
ALTER COLUMN user_id TYPE UUID USING user_id::uuid;

-- ============================================
-- Step 3: Recreate indexes
-- ============================================
CREATE INDEX idx_rep_weekly_user_week ON rep_weekly_snapshots(user_id, week_start DESC);
CREATE UNIQUE INDEX idx_rep_weekly_unique ON rep_weekly_snapshots(user_id, week_start);
CREATE INDEX idx_call_scores_user ON call_quality_scores(user_id, created_at DESC);

-- ============================================
-- Step 4: Verify (run this to confirm)
-- ============================================
-- SELECT column_name, data_type
-- FROM information_schema.columns
-- WHERE table_name IN ('rep_weekly_snapshots', 'call_quality_scores')
-- AND column_name = 'user_id';
-- Should show: uuid for both tables
