-- Enhance welcome tour tracking with detailed analytics
-- This adds comprehensive onboarding progress and engagement tracking

-- Enhanced tour progress tracking
ALTER TABLE profiles 
ADD COLUMN IF NOT EXISTS welcome_tour_started_at TIMESTAMP WITH TIME ZONE;

ALTER TABLE profiles 
ADD COLUMN IF NOT EXISTS welcome_tour_current_step INTEGER DEFAULT 0;

ALTER TABLE profiles 
ADD COLUMN IF NOT EXISTS welcome_tour_steps_viewed INTEGER[] DEFAULT '{}';

-- Tour outcome tracking
ALTER TABLE profiles 
ADD COLUMN IF NOT EXISTS welcome_tour_completion_type TEXT CHECK (welcome_tour_completion_type IN ('completed', 'skipped', 'abandoned'));

ALTER TABLE profiles 
ADD COLUMN IF NOT EXISTS welcome_tour_selected_plan TEXT;

ALTER TABLE profiles 
ADD COLUMN IF NOT EXISTS welcome_tour_selected_billing TEXT CHECK (welcome_tour_selected_billing IN ('month', 'year'));

-- Engagement metrics
ALTER TABLE profiles 
ADD COLUMN IF NOT EXISTS welcome_tour_total_time_seconds INTEGER DEFAULT 0;

ALTER TABLE profiles 
ADD COLUMN IF NOT EXISTS welcome_tour_video_watched BOOLEAN DEFAULT false;

-- Tour version for A/B testing
ALTER TABLE profiles 
ADD COLUMN IF NOT EXISTS welcome_tour_version TEXT DEFAULT 'v1';

-- Create optimized indexes for analytics
CREATE INDEX IF NOT EXISTS idx_profiles_welcome_tour_analytics 
ON profiles (welcome_tour_completed, welcome_tour_completion_type, welcome_tour_selected_plan);

CREATE INDEX IF NOT EXISTS idx_profiles_welcome_tour_progress 
ON profiles (welcome_tour_completed, welcome_tour_current_step) 
WHERE welcome_tour_completed = false;

CREATE INDEX IF NOT EXISTS idx_profiles_welcome_tour_engagement 
ON profiles (welcome_tour_video_watched, welcome_tour_total_time_seconds) 
WHERE welcome_tour_completed = true;

-- Add comprehensive comments
COMMENT ON COLUMN profiles.welcome_tour_started_at IS 'When the user first opened the welcome tour';
COMMENT ON COLUMN profiles.welcome_tour_current_step IS 'Current step in the tour (0-based index)';
COMMENT ON COLUMN profiles.welcome_tour_steps_viewed IS 'Array of step indices the user has viewed';
COMMENT ON COLUMN profiles.welcome_tour_completion_type IS 'How the tour ended: completed, skipped, or abandoned';
COMMENT ON COLUMN profiles.welcome_tour_selected_plan IS 'Plan selected during tour (free, explorer, storyteller, professional)';
COMMENT ON COLUMN profiles.welcome_tour_selected_billing IS 'Billing interval selected during tour (month, year)';
COMMENT ON COLUMN profiles.welcome_tour_total_time_seconds IS 'Total time spent in the tour in seconds';
COMMENT ON COLUMN profiles.welcome_tour_video_watched IS 'Whether the user watched the demo video';
COMMENT ON COLUMN profiles.welcome_tour_version IS 'Version of the tour shown for A/B testing';

-- Create a function to get tour analytics
CREATE OR REPLACE FUNCTION get_tour_analytics()
RETURNS TABLE (
  total_users BIGINT,
  tour_started BIGINT,
  tour_completed BIGINT,
  tour_skipped BIGINT,
  tour_abandoned BIGINT,
  completion_rate NUMERIC,
  avg_time_seconds NUMERIC,
  video_watch_rate NUMERIC,
  most_popular_plan TEXT,
  monthly_vs_annual_ratio NUMERIC
) AS $$
BEGIN
  RETURN QUERY
  WITH tour_stats AS (
    SELECT 
      COUNT(*) as total_users,
      COUNT(CASE WHEN welcome_tour_started_at IS NOT NULL THEN 1 END) as tour_started,
      COUNT(CASE WHEN welcome_tour_completion_type = 'completed' THEN 1 END) as tour_completed,
      COUNT(CASE WHEN welcome_tour_completion_type = 'skipped' THEN 1 END) as tour_skipped,
      COUNT(CASE WHEN welcome_tour_completion_type = 'abandoned' THEN 1 END) as tour_abandoned,
      AVG(CASE WHEN welcome_tour_total_time_seconds > 0 THEN welcome_tour_total_time_seconds END) as avg_time,
      COUNT(CASE WHEN welcome_tour_video_watched = true THEN 1 END)::NUMERIC / NULLIF(COUNT(CASE WHEN welcome_tour_started_at IS NOT NULL THEN 1 END), 0) as video_rate,
      MODE() WITHIN GROUP (ORDER BY welcome_tour_selected_plan) as popular_plan,
      COUNT(CASE WHEN welcome_tour_selected_billing = 'month' THEN 1 END)::NUMERIC / NULLIF(COUNT(CASE WHEN welcome_tour_selected_billing = 'year' THEN 1 END), 0) as billing_ratio
    FROM profiles
    WHERE created_at >= NOW() - INTERVAL '30 days' -- Last 30 days
  )
  SELECT 
    s.total_users,
    s.tour_started,
    s.tour_completed,
    s.tour_skipped,
    s.tour_abandoned,
    CASE WHEN s.tour_started > 0 THEN s.tour_completed::NUMERIC / s.tour_started * 100 ELSE 0 END as completion_rate,
    s.avg_time,
    s.video_rate * 100 as video_watch_rate,
    s.popular_plan,
    s.billing_ratio
  FROM tour_stats s;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Grant access to the analytics function
GRANT EXECUTE ON FUNCTION get_tour_analytics() TO authenticated;

-- Create a function to get step-by-step analytics
CREATE OR REPLACE FUNCTION get_step_analytics()
RETURNS TABLE (
  step_number INTEGER,
  views BIGINT,
  drop_off_rate NUMERIC
) AS $$
BEGIN
  RETURN QUERY
  WITH step_data AS (
    SELECT 
      unnest(welcome_tour_steps_viewed) as step_num,
      COUNT(*) as total_started
    FROM profiles 
    WHERE welcome_tour_started_at IS NOT NULL
    GROUP BY step_num
  ),
  total_started AS (
    SELECT COUNT(*) as total FROM profiles WHERE welcome_tour_started_at IS NOT NULL
  )
  SELECT 
    sd.step_num,
    COUNT(*) as views,
    (ts.total - COUNT(*))::NUMERIC / ts.total * 100 as drop_rate
  FROM step_data sd
  CROSS JOIN total_started ts
  GROUP BY sd.step_num, ts.total
  ORDER BY sd.step_num;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Grant access to the step analytics function
GRANT EXECUTE ON FUNCTION get_step_analytics() TO authenticated;