-- Backfill handled_by column from metadata for existing communications
-- Run this once to fix historical data

-- First, let's see what we're working with
SELECT
  COUNT(*) as total_comms,
  COUNT(handled_by) as with_handled_by_column,
  COUNT(metadata->>'handled_by') as with_handled_by_metadata,
  COUNT(metadata->>'user_id') as with_user_id_metadata
FROM communications;

-- Update communications where handled_by is null but metadata has it
-- Only update if the value looks like a UUID (36 chars with dashes)
UPDATE communications
SET handled_by = (metadata->>'handled_by')::uuid
WHERE handled_by IS NULL
  AND metadata->>'handled_by' IS NOT NULL
  AND metadata->>'handled_by' ~ '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$';

-- Also try user_id from metadata (used in SMS logging)
UPDATE communications
SET handled_by = (metadata->>'user_id')::uuid
WHERE handled_by IS NULL
  AND metadata->>'user_id' IS NOT NULL
  AND metadata->>'user_id' ~ '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$';

-- Verify the update
SELECT
  COUNT(*) as total_comms,
  COUNT(handled_by) as with_handled_by_column,
  COUNT(*) FILTER (WHERE handled_by IS NULL AND (metadata->>'handled_by' IS NOT NULL OR metadata->>'user_id' IS NOT NULL)) as still_missing
FROM communications;
