-- Denormalize frequently-accessed fields from communications.metadata
-- Improves query performance for outcome and duration filtering/aggregation

-- Add top-level columns for outcome and duration
ALTER TABLE communications ADD COLUMN IF NOT EXISTS outcome text;
ALTER TABLE communications ADD COLUMN IF NOT EXISTS call_duration_seconds integer;

-- Migrate existing data from metadata JSONB to top-level columns
UPDATE communications
SET
  outcome = metadata->>'outcome',
  call_duration_seconds = COALESCE(
    (metadata->>'duration_seconds')::integer,
    (metadata->>'recording_duration')::integer,
    (metadata->>'duration')::integer
  )
WHERE metadata IS NOT NULL
  AND (
    metadata->>'outcome' IS NOT NULL
    OR metadata->>'duration_seconds' IS NOT NULL
    OR metadata->>'recording_duration' IS NOT NULL
    OR metadata->>'duration' IS NOT NULL
  );

-- Add indexes for fast filtering
CREATE INDEX IF NOT EXISTS idx_communications_outcome ON communications(outcome) WHERE outcome IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_communications_call_duration ON communications(call_duration_seconds) WHERE call_duration_seconds IS NOT NULL;

-- Note: Code should be updated to:
-- 1. Write to both metadata AND top-level columns for backwards compatibility
-- 2. Read from top-level columns instead of metadata->>
