-- Add handled_by column to communications table for rep performance tracking
ALTER TABLE communications ADD COLUMN IF NOT EXISTS handled_by UUID;

-- Create index for efficient querying by handler
CREATE INDEX IF NOT EXISTS idx_communications_handled_by
ON communications(handled_by)
WHERE handled_by IS NOT NULL;

-- Backfill from metadata.handled_by (for calls)
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}$';

-- Backfill from metadata.user_id (for SMS)
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}$';
