-- Add "free" plan option and freemium helper utilities

-- Add the new enum value if it does not already exist
ALTER TYPE public.subscription_plan ADD VALUE IF NOT EXISTS 'free';

-- Helper to determine if freemium seats are still available
CREATE OR REPLACE FUNCTION public.is_freemium_available() RETURNS boolean
    LANGUAGE plpgsql
    SECURITY DEFINER
AS $$
DECLARE
  freemium_user_count integer;
BEGIN
  SELECT COUNT(*) INTO freemium_user_count
  FROM public.profiles
  WHERE plan = 'free';

  IF freemium_user_count >= 100 THEN
    RETURN FALSE;
  END IF;

  IF DATE_PART('year', NOW()) >= 2026 THEN
    RETURN FALSE;
  END IF;

  RETURN TRUE;
END;
$$;

COMMENT ON FUNCTION public.is_freemium_available() IS
'Returns true when less than 100 users currently have the free plan and the current year is before 2026';

ALTER FUNCTION public.is_freemium_available() OWNER TO postgres;

-- Update the new-user trigger to auto-assign the free plan when slots remain
CREATE OR REPLACE FUNCTION public.handle_new_user() RETURNS trigger
    LANGUAGE plpgsql
    SECURITY DEFINER
AS $$
  DECLARE 
    name TEXT;
    first_name TEXT;
    last_name TEXT;
    is_temp BOOLEAN;
    email_prefix TEXT;
    assigned_plan public.subscription_plan := 'none';
  BEGIN   
  -- Extract first_name and last_name from metadata
  first_name = NEW.raw_user_meta_data ->> 'first_name';
  last_name = NEW.raw_user_meta_data ->> 'last_name';
  
  -- If first_name is not provided, extract it from email (part before @)
  IF first_name IS NULL OR first_name = '' THEN
    email_prefix = SPLIT_PART(NEW.email, '@', 1);
    first_name = email_prefix;
  END IF;
  
  -- Determine the full name
  IF NEW.raw_user_meta_data ->> 'name' IS NOT NULL
  THEN
    name = NEW.raw_user_meta_data ->> 'name';
    -- If name is provided but not first/last, try to split it
    IF first_name IS NULL AND last_name IS NULL AND name IS NOT NULL THEN
      first_name = SPLIT_PART(name, ' ', 1);
      IF ARRAY_LENGTH(STRING_TO_ARRAY(name, ' '), 1) > 1 THEN
        last_name = SUBSTRING(name FROM LENGTH(SPLIT_PART(name, ' ', 1)) + 2);
      END IF;
    END IF;
  ELSE
    name = CONCAT(first_name, ' ', last_name);
  END IF;

  -- Check if the role is 'invited_user' in app_metadata
  IF COALESCE(NEW.raw_user_meta_data->>'role', '') = 'invited_user'
  THEN
    is_temp = TRUE;
  ELSE
    is_temp = FALSE;
  END IF;

  -- Assign free plan when freemium slots are available and user is not temporary
  IF NOT is_temp AND public.is_freemium_available() THEN
    assigned_plan := 'free';
  END IF;

  INSERT INTO public.profiles (id, name, email, avatar_url, first_name, last_name, is_temporary, plan)
  VALUES (NEW.id, name, NEW.email, NEW.raw_user_meta_data ->> 'avatar_url', first_name, last_name, is_temp, assigned_plan);
  
  RETURN NEW;
END;
$$;

COMMENT ON FUNCTION public.handle_new_user() IS
'Trigger function that creates a profile when a new user is created. Sets is_temporary=true for users with app_metadata.role=invited_user, extracts first name from email when missing, and assigns the free plan while freemium is available.';

ALTER FUNCTION public.handle_new_user() OWNER TO postgres;
