-- Function to generate voucher serial number
CREATE OR REPLACE FUNCTION generate_voucher_serial()
RETURNS TEXT AS $$
DECLARE
  serial_num TEXT;
  exists_check BOOLEAN;
BEGIN
  LOOP
    -- Generate format: FU-YYYY-XXXXXX (FU = Fanaka University)
    serial_num := 'FU-' || EXTRACT(YEAR FROM NOW()) || '-' || 
                  LPAD(FLOOR(RANDOM() * 999999 + 1)::TEXT, 6, '0');
    
    -- Check if serial number already exists
    SELECT EXISTS(SELECT 1 FROM public.vouchers WHERE serial_number = serial_num) INTO exists_check;
    
    -- If doesn't exist, break the loop
    IF NOT exists_check THEN
      EXIT;
    END IF;
  END LOOP;
  
  RETURN serial_num;
END;
$$ LANGUAGE plpgsql;

-- Function to generate voucher PIN
CREATE OR REPLACE FUNCTION generate_voucher_pin()
RETURNS TEXT AS $$
BEGIN
  -- Generate 8-digit PIN
  RETURN LPAD(FLOOR(RANDOM() * 99999999 + 1)::TEXT, 8, '0');
END;
$$ LANGUAGE plpgsql;

-- Function to handle new user registration
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  INSERT INTO public.profiles (id, email, full_name)
  VALUES (
    NEW.id,
    NEW.email,
    COALESCE(NEW.raw_user_meta_data ->> 'full_name', '')
  )
  ON CONFLICT (id) DO NOTHING;

  RETURN NEW;
END;
$$;

-- Trigger for new user registration
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW
  EXECUTE FUNCTION public.handle_new_user();

-- Function to update timestamps
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Add update triggers to tables
CREATE TRIGGER update_profiles_updated_at
  BEFORE UPDATE ON public.profiles
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_applications_updated_at
  BEFORE UPDATE ON public.applications
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_payments_updated_at
  BEFORE UPDATE ON public.payments
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_admin_users_updated_at
  BEFORE UPDATE ON public.admin_users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();