<commit_before>module CsvGenerator

  PATH = '/tmp/'
  #BACKER_CSV_LOCATION = File.join('/usr/local/app/diaspora/', 'backer_list.csv')
  BACKER_CSV_LOCATION = File.join('/home/ilya/workspace/diaspora/', 'backer_list.csv')
  WAITLIST_LOCATION = File.join(Rails.root, 'config', 'mailing_list.csv')
  OFFSET_LOCATION = File.join(Rails.root, 'config', 'email_offset')

  def self.all_active_users
    file = self.filename("all_active_users")
    sql = <<SQL
      SELECT email AS '%EMAIL%' 
        #{self.output_syntax(file)}
        FROM users where username IS NOT NULL
SQL
    ActiveRecord::Base.connection.execute(sql)
  end

  def self.all_inactive_invited_users
    file = self.filename("all_inactive_invited_users.csv")
    sql = <<SQL
      SELECT invitations.identifier AS '%EMAIL%', users.invitation_token AS '%TOKEN%'
        #{self.output_syntax(file)}
        FROM invitations
        JOIN users ON
          users.id=invitations.recipient_id
          WHERE users.username IS NULL
            AND invitations.service='email'
SQL
    ActiveRecord::Base.connection.execute(sql)
  end

  def self.generate_csvs
    #`mkdir /tmp/csvs`
    self.backers_recent_login
    self.backers_old_login
    self.backers_never_login
    self.non_backers_recent_login
    self.non_backers_old_login
    self.non_backers_never_login
  end

  def self.backers_recent_login
    file = self.filename("v1_backers_recent_login.csv")
    sql = self.select_fragment(file, "#{self.has_email} AND #{self.backer_email_condition} AND #{self.recent_login_query}")

    ActiveRecord::Base.connection.execute(sql)
  end

  def self.backers_old_login
    file = self.filename("v2_backers_old_login.csv")
    sql = self.select_fragment(file, "#{self.has_email} AND #{self.backer_email_condition} AND #{self.old_login_query}")

    ActiveRecord::Base.connection.execute(sql)
  end

  def self.backers_never_login
    file = self.filename("v3_backers_never_login.csv")
    sql = <<SQL
          SELECT '%EMAIL%','%NAME%','%INVITATION_LINK%'
          UNION
            SELECT `users`.email AS '%EMAIL%',
                    'friend of Diaspora*' AS '%NAME%',
                IF(`users`.invitation_token, CONCAT( 'https://joindiaspora.com/users/invitation/accept?invitation_token=', `users`.invitation_token) ,NULL) AS '%INVITATION_LINK%'
                #{self.output_syntax(file)}
             FROM `users`
            WHERE #{self.has_email} AND #{self.backer_email_condition} AND #{self.never_login_query};
SQL

    ActiveRecord::Base.connection.execute(sql)
  end

  def self.non_backers_recent_login
    file = self.filename("v4_non_backers_recent_login.csv")
    sql = self.select_fragment(file, "#{self.has_email} AND #{self.non_backer_email_condition} AND #{self.recent_login_query}")

    ActiveRecord::Base.connection.execute(sql)
  end

  def self.non_backers_old_login
    file = self.filename("v5_non_backers_old_login.csv")
    sql = self.select_fragment(file, "#{self.has_email} AND #{self.non_backer_email_condition} AND #{self.old_login_query}")

    ActiveRecord::Base.connection.execute(sql)
  end

  def self.non_backers_never_login
    file = self.filename("v6_non_backers_never_login.csv")
    sql = <<SQL
          SELECT '%EMAIL%','%NAME%','%INVITATION_LINK%'
          UNION
            SELECT `users`.email AS '%EMAIL%',
                    'friend of Diaspora*' AS '%NAME%',
                IF(`users`.invitation_token, CONCAT( 'https://joindiaspora.com/users/invitation/accept?invitation_token=', `users`.invitation_token) ,NULL) AS '%INVITATION_LINK%'
                #{self.output_syntax(file)}
             FROM `users`
            WHERE #{self.has_email} AND #{self.non_backer_email_condition} AND #{self.never_login_query};
SQL
    ActiveRecord::Base.connection.execute(sql)
  end

  # ---------------- QUERY METHODS & NOTES -------------------------
  def self.select_fragment(file, where_clause)
    #    #
    #
    #
    sql = <<SQL
          SELECT '%EMAIL%','%NAME%','%INVITATION_LINK%'
          UNION
            SELECT `users`.email AS '%EMAIL%',
                   IF( `profiles`.full_name IS NOT NULL AND `profiles`.full_name != "",
                                               `profiles`.full_name, 'friend of Diaspora*') AS '%NAME%',
                IF(`users`.invitation_token, CONCAT( 'https://joindiaspora.com/users/invitation/accept?invitation_token=', `users`.invitation_token) ,NULL) AS '%INVITATION_LINK%'
                #{self.output_syntax(file)}
             FROM `users`
             JOIN `people` ON `users`.id = `people`.owner_id JOIN `profiles` ON `people`.id = `profiles`.person_id
            WHERE #{where_clause};
SQL
  end

  def self.has_email
    '`users`.`email` IS NOT NULL AND `users`.`email` != ""'
  end

  def self.backer_email_condition
    b_emails = self.backer_emails
    b_emails.map!{|a| "'#{a}'"}
    "`users`.`email` IN (#{query_string_from_array(b_emails[1..b_emails.length])})" 
  end

  def self.non_backer_email_condition
    b_emails = self.backer_emails
    b_emails.map!{|a| "'#{a}'"}
    "`users`.`email` NOT IN (#{query_string_from_array(b_emails[1..b_emails.length])})" 
  end

  def self.recent_login_query
    "(last_sign_in_at > #{(Time.now - 1.month).iso8601})"
  end

  def self.old_login_query
    "(last_sign_in_at < #{(Time.now - 1.month).iso8601})"
  end<commit_msg>added the check for invitation token<commit_after>module CsvGenerator

  PATH = '/tmp/'
  #BACKER_CSV_LOCATION = File.join('/usr/local/app/diaspora/', 'backer_list.csv')
  BACKER_CSV_LOCATION = File.join('/home/ilya/workspace/diaspora/', 'backer_list.csv')
  WAITLIST_LOCATION = File.join(Rails.root, 'config', 'mailing_list.csv')
  OFFSET_LOCATION = File.join(Rails.root, 'config', 'email_offset')

  def self.all_active_users
    file = self.filename("all_active_users")
    sql = <<SQL
      SELECT email AS '%EMAIL%' 
        #{self.output_syntax(file)}
        FROM users where username IS NOT NULL
SQL
    ActiveRecord::Base.connection.execute(sql)
  end

  def self.all_inactive_invited_users
    file = self.filename("all_inactive_invited_users.csv")
    sql = <<SQL
      SELECT invitations.identifier AS '%EMAIL%', users.invitation_token AS '%TOKEN%'
        #{self.output_syntax(file)}
        FROM invitations
        JOIN users ON
          users.id=invitations.recipient_id
          WHERE users.username IS NULL
            AND invitations.service='email'
SQL
    ActiveRecord::Base.connection.execute(sql)
  end

  def self.generate_csvs
    #`mkdir /tmp/csvs`
    self.backers_recent_login
    self.backers_old_login
    self.backers_never_login
    self.non_backers_recent_login
    self.non_backers_old_login
    self.non_backers_never_login
  end

  def self.backers_recent_login
    file = self.filename("v1_backers_recent_login.csv")
    sql = self.select_fragment(file, "#{self.has_email} AND #{self.backer_email_condition} AND #{self.recent_login_query}")

    ActiveRecord::Base.connection.execute(sql)
  end

  def self.backers_old_login
    file = self.filename("v2_backers_old_login.csv")
    sql = self.select_fragment(file, "#{self.has_email} AND #{self.backer_email_condition} AND #{self.old_login_query}")

    ActiveRecord::Base.connection.execute(sql)
  end

  def self.backers_never_login
    file = self.filename("v3_backers_never_login.csv")
    sql = <<SQL
          SELECT '%EMAIL%','%NAME%','%INVITATION_LINK%'
          UNION
            SELECT `users`.email AS '%EMAIL%',
                    'friend of Diaspora*' AS '%NAME%',
                IF(`users`.invitation_token, CONCAT( 'https://joindiaspora.com/users/invitation/accept?invitation_token=', `users`.invitation_token) ,NULL) AS '%INVITATION_LINK%'
                #{self.output_syntax(file)}
             FROM `users`
            WHERE #{self.has_email} AND #{self.has_invitation_token} AND #{self.backer_email_condition} AND #{self.never_login_query};
SQL

    ActiveRecord::Base.connection.execute(sql)
  end

  def self.non_backers_recent_login
    file = self.filename("v4_non_backers_recent_login.csv")
    sql = self.select_fragment(file, "#{self.has_email} AND #{self.non_backer_email_condition} AND #{self.recent_login_query}")

    ActiveRecord::Base.connection.execute(sql)
  end

  def self.non_backers_old_login
    file = self.filename("v5_non_backers_old_login.csv")
    sql = self.select_fragment(file, "#{self.has_email} AND #{self.non_backer_email_condition} AND #{self.old_login_query}")

    ActiveRecord::Base.connection.execute(sql)
  end

  def self.non_backers_never_login
    file = self.filename("v6_non_backers_never_login.csv")
    sql = <<SQL
          SELECT '%EMAIL%','%NAME%','%INVITATION_LINK%'
          UNION
            SELECT `users`.email AS '%EMAIL%',
                    'friend of Diaspora*' AS '%NAME%',
                IF(`users`.invitation_token, CONCAT( 'https://joindiaspora.com/users/invitation/accept?invitation_token=', `users`.invitation_token) ,NULL) AS '%INVITATION_LINK%'
                #{self.output_syntax(file)}
             FROM `users`
            WHERE #{self.has_email} AND #{self.has_invitation_token} AND #{self.non_backer_email_condition} AND #{self.never_login_query};
SQL
    ActiveRecord::Base.connection.execute(sql)
  end

  # ---------------- QUERY METHODS & NOTES -------------------------
  def self.select_fragment(file, where_clause)
    #    #
    #
    #
    sql = <<SQL
          SELECT '%EMAIL%','%NAME%','%INVITATION_LINK%'
          UNION
            SELECT `users`.email AS '%EMAIL%',
                   IF( `profiles`.full_name IS NOT NULL AND `profiles`.full_name != "",
                                               `profiles`.full_name, 'friend of Diaspora*') AS '%NAME%',
                IF(`users`.invitation_token, CONCAT( 'https://joindiaspora.com/users/invitation/accept?invitation_token=', `users`.invitation_token) ,NULL) AS '%INVITATION_LINK%'
                #{self.output_syntax(file)}
             FROM `users`
             JOIN `people` ON `users`.id = `people`.owner_id JOIN `profiles` ON `people`.id = `profiles`.person_id
            WHERE #{where_clause};
SQL
  end

  def self.has_invitation_token
    '`users`.`invitation_token` IS NOT NULL AND `users`.`invitation_token` != ""'
  end
  
  def self.has_email
    '`users`.`email` IS NOT NULL AND `users`.`email` != ""'
  end

  def self.backer_email_condition
    b_emails = self.backer_emails
    b_emails.map!{|a| "'#{a}'"}
    "`users`.`email` IN (#{query_string_from_array(b_emails[1..b_emails.length])})" 
  end

  def self.non_backer_email_condition
    b_emails = self.backer_emails
    b_emails.map!{|a| "'#{a}'"}
    "`users`.`email` NOT IN (#{query_string_from_array(b_emails[1..b_emails.length])})" 
  end

  def self.recent_login_query
    "(last_sign_in_at > SUBDATE(NOW(), INTERVAL 31 DAY))"
  end

  def self.old_login_query
    "(last_sign_in_at < SUBDATE(NOW(), INTERVAL 31 DAY))"
  end
{'fraction_non_alphanumeric': 0.13770642201834862, 'fraction_numerical': 0.0027522935779816515, 'mean_word_length': 3.17502872462658, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 5, 'https://': 6, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4471282', 'n_tokens_mistral': 3782, 'n_tokens_neox': 3629, 'n_words': 619}