-- Vapi Tasks table for voice assistant task delegation
-- Stores tasks created from inbound calls to Rivet voice assistant

CREATE TABLE IF NOT EXISTS vapi_tasks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),

  -- Call metadata from Vapi
  call_id TEXT UNIQUE,
  transcript TEXT NOT NULL,
  caller_phone TEXT,
  caller_name TEXT,
  duration_seconds INTEGER,
  vapi_metadata JSONB,  -- Full Vapi webhook payload

  -- Task details (parsed from transcript)
  task_type TEXT NOT NULL,  -- call_lead, send_message, update_lead, create_callback, get_report, custom
  task_params JSONB,        -- { leadId, message, phone, date, etc }
  task_description TEXT NOT NULL,
  priority TEXT DEFAULT 'normal',  -- urgent, normal, low

  -- Execution tracking
  status TEXT DEFAULT 'pending',  -- pending, executing, completed, failed
  started_at TIMESTAMPTZ,
  completed_at TIMESTAMPTZ,
  result JSONB,
  error_message TEXT,
  retry_count INTEGER DEFAULT 0,

  -- Notification tracking
  notification_sent BOOLEAN DEFAULT FALSE,
  notification_method TEXT,  -- sms, call, none
  notified_at TIMESTAMPTZ,
  notification_result JSONB
);

-- Indexes for common queries
CREATE INDEX IF NOT EXISTS idx_vapi_tasks_status ON vapi_tasks(status) WHERE status IN ('pending', 'executing');
CREATE INDEX IF NOT EXISTS idx_vapi_tasks_created ON vapi_tasks(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_vapi_tasks_call_id ON vapi_tasks(call_id);
CREATE INDEX IF NOT EXISTS idx_vapi_tasks_caller_phone ON vapi_tasks(caller_phone);

-- Add comments for documentation
COMMENT ON TABLE vapi_tasks IS 'Stores tasks created from Vapi voice assistant calls';
COMMENT ON COLUMN vapi_tasks.task_type IS 'Type of task: call_lead, send_message, update_lead, create_callback, get_report, custom';
COMMENT ON COLUMN vapi_tasks.status IS 'Execution status: pending, executing, completed, failed';
COMMENT ON COLUMN vapi_tasks.priority IS 'Task priority: urgent (call notification), normal (SMS), low (no notification)';
