-- ============================================
-- Phase 4: Backend Improvements
-- ============================================

-- ============================================
-- 1. Profile Audit Log
-- ============================================
CREATE TABLE IF NOT EXISTS public.profile_audit_log (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  changed_by uuid NOT NULL REFERENCES auth.users(id),
  action text NOT NULL CHECK (action IN ('profile_updated', 'avatar_changed', 'email_changed', 'password_changed')),
  changes jsonb NOT NULL,
  ip_address_hash text,
  user_agent text,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_profile_audit_log_user_id ON public.profile_audit_log(user_id);
CREATE INDEX IF NOT EXISTS idx_profile_audit_log_created_at ON public.profile_audit_log(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_profile_audit_log_action ON public.profile_audit_log(action);

-- RLS policies for profile_audit_log
ALTER TABLE public.profile_audit_log ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Users can view their own audit log" ON public.profile_audit_log;
CREATE POLICY "Users can view their own audit log"
  ON public.profile_audit_log FOR SELECT
  TO authenticated
  USING (user_id = auth.uid());

DROP POLICY IF EXISTS "Moderators can view all audit logs" ON public.profile_audit_log;
CREATE POLICY "Moderators can view all audit logs"
  ON public.profile_audit_log FOR SELECT
  TO authenticated
  USING (is_moderator(auth.uid()));

DROP POLICY IF EXISTS "System can insert audit logs" ON public.profile_audit_log;
CREATE POLICY "System can insert audit logs"
  ON public.profile_audit_log FOR INSERT
  TO authenticated
  WITH CHECK (auth.uid() = changed_by);

-- ============================================
-- 2. Rate Limiting System
-- ============================================
CREATE TABLE IF NOT EXISTS public.rate_limits (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  action text NOT NULL,
  attempts integer NOT NULL DEFAULT 1,
  window_start timestamptz NOT NULL DEFAULT now(),
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (user_id, action, window_start)
);

CREATE INDEX IF NOT EXISTS idx_rate_limits_user_action ON public.rate_limits(user_id, action);
CREATE INDEX IF NOT EXISTS idx_rate_limits_window ON public.rate_limits(window_start);

-- Cleanup function for old rate limit records
CREATE OR REPLACE FUNCTION public.cleanup_rate_limits()
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  DELETE FROM public.rate_limits
  WHERE window_start < now() - interval '24 hours';
END;
$$;

-- Rate limiting check function
CREATE OR REPLACE FUNCTION public.check_rate_limit(
  p_action text,
  p_max_attempts integer,
  p_window_minutes integer
)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  v_window_start timestamptz;
  v_current_attempts integer;
  v_allowed boolean;
BEGIN
  -- Calculate window start (round down to window interval)
  v_window_start := date_trunc('minute', now()) - 
    (extract(minute from now())::integer % p_window_minutes || ' minutes')::interval;
  
  -- Get or create rate limit record
  INSERT INTO public.rate_limits (user_id, action, attempts, window_start)
  VALUES (auth.uid(), p_action, 1, v_window_start)
  ON CONFLICT (user_id, action, window_start)
  DO UPDATE SET attempts = rate_limits.attempts + 1
  RETURNING attempts INTO v_current_attempts;
  
  v_allowed := v_current_attempts <= p_max_attempts;
  
  RETURN jsonb_build_object(
    'allowed', v_allowed,
    'attempts', v_current_attempts,
    'max_attempts', p_max_attempts,
    'reset_at', v_window_start + (p_window_minutes || ' minutes')::interval
  );
END;
$$;

GRANT EXECUTE ON FUNCTION public.check_rate_limit TO authenticated;

-- ============================================
-- 3. Drop old update_profile and create enhanced version
-- ============================================
-- Drop the exact existing function signature
DROP FUNCTION IF EXISTS public.update_profile(text, text, text, text, boolean, text, text, text, text);

CREATE OR REPLACE FUNCTION public.update_profile(
  p_username text DEFAULT NULL,
  p_display_name text DEFAULT NULL,
  p_bio text DEFAULT NULL,
  p_preferred_pronouns text DEFAULT NULL,
  p_personal_location text DEFAULT NULL,
  p_avatar_url text DEFAULT NULL,
  p_avatar_image_id text DEFAULT NULL
)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  v_old_profile profiles%ROWTYPE;
  v_changes jsonb := '[]'::jsonb;
  v_change jsonb;
  v_rate_limit jsonb;
  v_user_agent text;
BEGIN
  -- Check for deactivated account
  SELECT * INTO v_old_profile FROM public.profiles WHERE user_id = auth.uid();
  
  IF v_old_profile.deactivated THEN
    RAISE EXCEPTION 'Cannot update profile: account is deactivated'
      USING ERRCODE = 'P0002';
  END IF;

  -- Check rate limit: 10 updates per 5 minutes
  v_rate_limit := public.check_rate_limit('profile_update', 10, 5);
  
  IF NOT (v_rate_limit->>'allowed')::boolean THEN
    RAISE EXCEPTION 'Rate limit exceeded. Try again at %',
      (v_rate_limit->>'reset_at')::timestamptz
    USING ERRCODE = 'P0001';
  END IF;

  -- Track changes for audit log
  IF p_username IS NOT NULL AND p_username IS DISTINCT FROM v_old_profile.username THEN
    -- Validate username format
    IF NOT (p_username ~ '^[a-z0-9_]{3,20}$') THEN
      RAISE EXCEPTION 'Invalid username format. Must be 3-20 lowercase letters, numbers, or underscores.'
        USING ERRCODE = 'P0003';
    END IF;
    
    -- Check uniqueness
    IF EXISTS (SELECT 1 FROM public.profiles WHERE username = p_username AND user_id != auth.uid()) THEN
      RAISE EXCEPTION 'Username already taken'
        USING ERRCODE = 'P0004';
    END IF;
    
    v_changes := v_changes || jsonb_build_object(
      'field', 'username',
      'old_value', v_old_profile.username,
      'new_value', p_username
    );
    
    UPDATE public.profiles 
    SET username = p_username
    WHERE user_id = auth.uid();
  END IF;

  IF p_display_name IS NOT NULL AND p_display_name IS DISTINCT FROM v_old_profile.display_name THEN
    IF length(trim(p_display_name)) < 1 OR length(trim(p_display_name)) > 50 THEN
      RAISE EXCEPTION 'Display name must be 1-50 characters'
        USING ERRCODE = 'P0005';
    END IF;
    
    v_changes := v_changes || jsonb_build_object(
      'field', 'display_name',
      'old_value', v_old_profile.display_name,
      'new_value', p_display_name
    );
    
    UPDATE public.profiles 
    SET display_name = p_display_name
    WHERE user_id = auth.uid();
  END IF;

  IF p_bio IS NOT NULL AND p_bio IS DISTINCT FROM v_old_profile.bio THEN
    IF length(p_bio) > 500 THEN
      RAISE EXCEPTION 'Bio must be 500 characters or less'
        USING ERRCODE = 'P0006';
    END IF;
    
    v_changes := v_changes || jsonb_build_object(
      'field', 'bio',
      'old_value', v_old_profile.bio,
      'new_value', p_bio
    );
    
    UPDATE public.profiles 
    SET bio = p_bio
    WHERE user_id = auth.uid();
  END IF;

  IF p_preferred_pronouns IS NOT NULL AND p_preferred_pronouns IS DISTINCT FROM v_old_profile.preferred_pronouns THEN
    IF length(p_preferred_pronouns) > 50 THEN
      RAISE EXCEPTION 'Preferred pronouns must be 50 characters or less'
        USING ERRCODE = 'P0007';
    END IF;
    
    v_changes := v_changes || jsonb_build_object(
      'field', 'preferred_pronouns',
      'old_value', v_old_profile.preferred_pronouns,
      'new_value', p_preferred_pronouns
    );
    
    UPDATE public.profiles 
    SET preferred_pronouns = p_preferred_pronouns
    WHERE user_id = auth.uid();
  END IF;

  IF p_personal_location IS NOT NULL AND p_personal_location IS DISTINCT FROM v_old_profile.personal_location THEN
    IF length(p_personal_location) > 100 THEN
      RAISE EXCEPTION 'Personal location must be 100 characters or less'
        USING ERRCODE = 'P0008';
    END IF;
    
    v_changes := v_changes || jsonb_build_object(
      'field', 'personal_location',
      'old_value', v_old_profile.personal_location,
      'new_value', p_personal_location
    );
    
    UPDATE public.profiles 
    SET personal_location = p_personal_location
    WHERE user_id = auth.uid();
  END IF;

  IF p_avatar_url IS NOT NULL AND p_avatar_url IS DISTINCT FROM v_old_profile.avatar_url THEN
    v_changes := v_changes || jsonb_build_object(
      'field', 'avatar',
      'old_value', jsonb_build_object('url', v_old_profile.avatar_url, 'image_id', v_old_profile.avatar_image_id),
      'new_value', jsonb_build_object('url', p_avatar_url, 'image_id', p_avatar_image_id)
    );
    
    UPDATE public.profiles 
    SET avatar_url = p_avatar_url,
        avatar_image_id = COALESCE(p_avatar_image_id, '')
    WHERE user_id = auth.uid();
  END IF;

  -- Insert audit log if there were changes
  IF jsonb_array_length(v_changes) > 0 THEN
    -- Try to get user agent from request headers
    BEGIN
      v_user_agent := current_setting('request.headers', true)::json->>'user-agent';
    EXCEPTION WHEN OTHERS THEN
      v_user_agent := NULL;
    END;

    INSERT INTO public.profile_audit_log (
      user_id,
      changed_by,
      action,
      changes,
      ip_address_hash,
      user_agent
    ) VALUES (
      auth.uid(),
      auth.uid(),
      CASE 
        WHEN EXISTS (SELECT 1 FROM jsonb_array_elements(v_changes) WHERE value->>'field' = 'avatar') THEN 'avatar_changed'
        ELSE 'profile_updated'
      END,
      hash_ip_address(COALESCE(current_setting('request.headers', true)::json->>'x-forwarded-for', 'unknown')),
      v_user_agent
    );
  END IF;

  RETURN jsonb_build_object(
    'success', true,
    'changes_count', jsonb_array_length(v_changes)
  );
END;
$$;

GRANT EXECUTE ON FUNCTION public.update_profile TO authenticated;

-- ============================================
-- 4. Email Change Status Function
-- ============================================
CREATE OR REPLACE FUNCTION public.get_email_change_status()
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = auth, public
AS $$
DECLARE
  v_user auth.users;
  v_result jsonb;
BEGIN
  SELECT * INTO v_user FROM auth.users WHERE id = auth.uid();
  
  IF v_user.email_change IS NULL OR v_user.email_change = '' THEN
    RETURN jsonb_build_object('has_pending_change', false);
  END IF;
  
  v_result := jsonb_build_object(
    'has_pending_change', true,
    'current_email', v_user.email,
    'new_email', v_user.email_change,
    'current_email_verified', COALESCE((v_user.email_change_confirm_status & 1) = 1, false),
    'new_email_verified', COALESCE((v_user.email_change_confirm_status & 2) = 2, false),
    'change_sent_at', v_user.email_change_sent_at
  );
  
  RETURN v_result;
END;
$$;

GRANT EXECUTE ON FUNCTION public.get_email_change_status TO authenticated;

-- ============================================
-- 5. User Preferences: Auto-save setting
-- ============================================
ALTER TABLE public.user_preferences 
ADD COLUMN IF NOT EXISTS auto_save_enabled boolean DEFAULT true;