-- Migration: Add VAPI integration fields to call_logs table
-- Description: Adds fields to store VAPI webhook data including messages, analysis, and recording URLs
-- Date: 2025-11-20
-- Related Issue: #104

-- Add VAPI-specific columns to call_logs table
ALTER TABLE call_logs
ADD COLUMN IF NOT EXISTS vapi_call_id TEXT,
ADD COLUMN IF NOT EXISTS messages JSONB,
ADD COLUMN IF NOT EXISTS analysis JSONB,
ADD COLUMN IF NOT EXISTS stereo_recording_url TEXT,
ADD COLUMN IF NOT EXISTS pcap_url TEXT,
ADD COLUMN IF NOT EXISTS log_url TEXT,
ADD COLUMN IF NOT EXISTS vapi_timestamp BIGINT;

-- Add index on vapi_call_id for faster lookups
CREATE INDEX IF NOT EXISTS idx_call_logs_vapi_call_id ON call_logs(vapi_call_id);

-- Add index on vapi_timestamp for time-based queries
CREATE INDEX IF NOT EXISTS idx_call_logs_vapi_timestamp ON call_logs(vapi_timestamp);

-- Add comments to document the new fields
COMMENT ON COLUMN call_logs.vapi_call_id IS 'Unique identifier from VAPI for this call';
COMMENT ON COLUMN call_logs.messages IS 'Array of conversation messages from VAPI (bot/user/system/tool_calls)';
COMMENT ON COLUMN call_logs.analysis IS 'VAPI analysis object containing summary and successEvaluation';
COMMENT ON COLUMN call_logs.stereo_recording_url IS 'URL to stereo recording from VAPI';
COMMENT ON COLUMN call_logs.pcap_url IS 'URL to SIP packet capture file from VAPI';
COMMENT ON COLUMN call_logs.log_url IS 'URL to VAPI call logs';
COMMENT ON COLUMN call_logs.vapi_timestamp IS 'VAPI event timestamp in milliseconds since epoch';

-- Log completion
DO $$
BEGIN
  RAISE NOTICE 'Migration completed: VAPI integration fields added to call_logs table';
  RAISE NOTICE 'Added columns: vapi_call_id, messages, analysis, stereo_recording_url, pcap_url, log_url, vapi_timestamp';
  RAISE NOTICE 'Added indexes: idx_call_logs_vapi_call_id, idx_call_logs_vapi_timestamp';
END $$;
