-- Migration: Call Log Analytics
-- Description: Add columns for LLM-based call log analysis with MCP tool integration
-- Date: 2025-12-01

-- ============================================================================
-- STEP 1: Create analytics_status enum type
-- ============================================================================

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'analytics_status_enum') THEN
    CREATE TYPE analytics_status_enum AS ENUM ('pending', 'running', 'completed', 'failed');
  END IF;
END $$;

-- ============================================================================
-- STEP 2: Add analytics columns to call_logs table
-- ============================================================================

-- Analytics status column
ALTER TABLE call_logs ADD COLUMN IF NOT EXISTS analytics_status analytics_status_enum DEFAULT 'pending';

-- Related data columns (JSONB for flexibility)
ALTER TABLE call_logs ADD COLUMN IF NOT EXISTS related_customers JSONB;
ALTER TABLE call_logs ADD COLUMN IF NOT EXISTS related_orders JSONB;
ALTER TABLE call_logs ADD COLUMN IF NOT EXISTS related_products JSONB;

-- Full LLM response storage
ALTER TABLE call_logs ADD COLUMN IF NOT EXISTS llm_analytics_json JSONB;

-- Error tracking
ALTER TABLE call_logs ADD COLUMN IF NOT EXISTS analytics_error TEXT;

-- Timing columns
ALTER TABLE call_logs ADD COLUMN IF NOT EXISTS analytics_started_at TIMESTAMPTZ;
ALTER TABLE call_logs ADD COLUMN IF NOT EXISTS analytics_completed_at TIMESTAMPTZ;

-- ============================================================================
-- STEP 3: Create indexes for analytics queries
-- ============================================================================

CREATE INDEX IF NOT EXISTS idx_call_logs_analytics_status
  ON call_logs(analytics_status);

CREATE INDEX IF NOT EXISTS idx_call_logs_analytics_pending
  ON call_logs(analytics_status)
  WHERE analytics_status = 'pending';

-- ============================================================================
-- STEP 4: Create function to fetch call_outcome_enum values dynamically
-- ============================================================================

CREATE OR REPLACE FUNCTION get_call_outcome_enum_values()
RETURNS TEXT[] AS $$
  SELECT array_agg(enumlabel::text ORDER BY enumsortorder)
  FROM pg_enum
  WHERE enumtypid = 'call_outcome_enum'::regtype;
$$ LANGUAGE sql STABLE;

-- Grant execute permission
GRANT EXECUTE ON FUNCTION get_call_outcome_enum_values() TO service_role;
GRANT EXECUTE ON FUNCTION get_call_outcome_enum_values() TO anon;
GRANT EXECUTE ON FUNCTION get_call_outcome_enum_values() TO authenticated;

-- ============================================================================
-- STEP 5: Add comments for documentation
-- ============================================================================

COMMENT ON COLUMN call_logs.analytics_status IS 'Status of LLM analytics processing: pending, running, completed, failed';
COMMENT ON COLUMN call_logs.related_customers IS 'JSON array of customers related to the call, identified by LLM analysis';
COMMENT ON COLUMN call_logs.related_orders IS 'JSON array of orders related to the call, identified by LLM analysis';
COMMENT ON COLUMN call_logs.related_products IS 'JSON array of products mentioned in the call, identified by LLM analysis';
COMMENT ON COLUMN call_logs.llm_analytics_json IS 'Full JSON response from the LLM analytics processing';
COMMENT ON COLUMN call_logs.analytics_error IS 'Error message if analytics processing failed';
COMMENT ON COLUMN call_logs.analytics_started_at IS 'Timestamp when analytics processing started';
COMMENT ON COLUMN call_logs.analytics_completed_at IS 'Timestamp when analytics processing completed';

COMMENT ON FUNCTION get_call_outcome_enum_values() IS 'Returns array of valid call_outcome_enum values for dynamic system prompt generation';
