<commit_before>namespace :db do
  task insert_discussion_topics: :environment do
    # Insert into course_discussion_topics with
    # actable_type='Course::Assessment::SubmissionQuestion'
    #
    # Pending staff reply flag will NOT be set correctly after this.
    ActsAsTenant.without_tenant do
      SLICE_SIZE = 10_000
      connection = ActiveRecord::Base.connection

      # Get tuples of course_id, submission_question_id, submission_id, question_id, and the
      # maximum created and updated times of the Answer based discussion topics.
      #
      # submission_id and question_id are used to group submission_questions where there are
      # multiple answers acting as old discussion topics.
      course_sq_tuples = connection.exec_query(<<-SQL)
        SELECT cac.course_id AS course_id, casq.id AS sq_id, casq.submission_id AS submission_id,
          casq.question_id AS question_id, MAX(cdt.created_at) AS created_at,
          MAX(cdt.updated_at) AS updated_at
        FROM course_assessment_submission_questions casq
        INNER JOIN course_assessment_submissions cas
          ON cas.id = casq.submission_id
        INNER JOIN course_assessments ca
          ON ca.id = cas.assessment_id
        INNER JOIN course_assessment_tabs cat
          ON cat.id = ca.tab_id
        INNER JOIN course_assessment_categories cac
          ON cac.id = cat.category_id
        LEFT JOIN course_assessment_answers caa
          ON (caa.submission_id = casq.submission_id AND caa.question_id = casq.question_id)
        LEFT JOIN course_discussion_topics cdt
          ON (cdt.actable_id = caa.id AND cdt.actable_type = 'Course::Assessment::Answer')
        GROUP BY (cac.course_id, casq.id, casq.submission_id, casq.question_id)
      SQL

      course_sq_tuples.each_slice(SLICE_SIZE) do |csq_tuples|
        course_ids = csq_tuples.map { |x| x['course_id'] }
        sq_ids = csq_tuples.map { |x| x['sq_id'] }

        # Get created and updated times from the Answer discussion topic if available,
        # else use NOW()
        created_at = csq_tuples.map { |x| x['created_at'] }
        updated_at = csq_tuples.map { |x| x['updated_at'] }
        created_at = created_at.map { |x| x ? "'#{x}'" : 'NOW()' }
        updated_at = updated_at.map { |x| x ? "'#{x}'" : 'NOW()' }

        actable_type_strings = ["'Course::Assessment::SubmissionQuestion'"] * SLICE_SIZE
        pending_staff_reply = [false] * SLICE_SIZE
        combined_arr = sq_ids.zip(actable_type_strings, course_ids, pending_staff_reply, created_at,
                                  updated_at)
        discussion_topic_values = combined_arr.map { |x| '(' + x.join(',') + ')'}.join(',')
        connection.exec_query(<<-SQL)
          INSERT INTO course_discussion_topics
                        (actable_id,
                         actable_type,
                         course_id,
                         pending_staff_reply,
                         created_at,
                         updated_at)
          VALUES #{discussion_topic_values}
        SQL
      end
    end
  end
end
<commit_msg>Handle duplicates when bulk inserting discussion topics.<commit_after>namespace :db do
  task insert_discussion_topics: :environment do
    # Insert into course_discussion_topics with
    # actable_type='Course::Assessment::SubmissionQuestion'
    #
    # Pending staff reply flag will NOT be set correctly after this.
    ActsAsTenant.without_tenant do
      SLICE_SIZE = 10_000
      connection = ActiveRecord::Base.connection

      # Get tuples of course_id, submission_question_id, submission_id, question_id, and the
      # maximum created and updated times of the Answer based discussion topics.
      #
      # submission_id and question_id are used to group submission_questions where there are
      # multiple answers acting as old discussion topics.
      course_sq_tuples = connection.exec_query(<<-SQL)
        SELECT cac.course_id AS course_id, casq.id AS sq_id, casq.submission_id AS submission_id,
          casq.question_id AS question_id, MAX(cdt.created_at) AS created_at,
          MAX(cdt.updated_at) AS updated_at
        FROM course_assessment_submission_questions casq
        INNER JOIN course_assessment_submissions cas
          ON cas.id = casq.submission_id
        INNER JOIN course_assessments ca
          ON ca.id = cas.assessment_id
        INNER JOIN course_assessment_tabs cat
          ON cat.id = ca.tab_id
        INNER JOIN course_assessment_categories cac
          ON cac.id = cat.category_id
        LEFT JOIN course_assessment_answers caa
          ON (caa.submission_id = casq.submission_id AND caa.question_id = casq.question_id)
        LEFT JOIN course_discussion_topics cdt
          ON (cdt.actable_id = caa.id AND cdt.actable_type = 'Course::Assessment::Answer')
        GROUP BY (cac.course_id, casq.id, casq.submission_id, casq.question_id)
      SQL
      byebug

      # DROP UNIQUE index
      connection.exec_query(<<-SQL)
        DROP INDEX index_course_discussion_topics_on_actable_type_and_actable_id
      SQL

      course_sq_tuples.each_slice(SLICE_SIZE) do |csq_tuples|
        course_ids = csq_tuples.map { |x| x['course_id'] }
        sq_ids = csq_tuples.map { |x| x['sq_id'] }

        # Get created and updated times from the Answer discussion topic if available,
        # else use NOW()
        created_at = csq_tuples.map { |x| x['created_at'] }
        updated_at = csq_tuples.map { |x| x['updated_at'] }
        created_at = created_at.map { |x| x ? "'#{x}'" : 'NOW()' }
        updated_at = updated_at.map { |x| x ? "'#{x}'" : 'NOW()' }

        actable_type_strings = ["'Course::Assessment::SubmissionQuestion'"] * SLICE_SIZE
        pending_staff_reply = [false] * SLICE_SIZE
        combined_arr = sq_ids.zip(actable_type_strings, course_ids, pending_staff_reply, created_at,
                                  updated_at)
        discussion_topic_values = combined_arr.map { |x| '(' + x.join(',') + ')'}.join(',')
        byebug
        connection.exec_query(<<-SQL)
          INSERT INTO course_discussion_topics
                        (actable_id,
                         actable_type,
                         course_id,
                         pending_staff_reply,
                         created_at,
                         updated_at)
          VALUES #{discussion_topic_values}
        SQL
      end

      # Remove duplicates
      connection.exec_query(<<-SQL)
        DELETE FROM course_discussion_topics a
        USING (SELECT MIN(ctid) AS ctid, actable_type, actable_id
                FROM course_discussion_topics GROUP BY (actable_type, actable_id)
                HAVING COUNT(*) > 1) b
        WHERE a.actable_type = b.actable_type AND a.actable_id = b.actable_id
          AND a.ctid <> b.ctid
      SQL
      
      # Add back unique index
      connection.exec_query(<<-SQL)
        CREATE UNIQUE INDEX index_course_discussion_topics_on_actable_type_and_actable_id
        ON course_discussion_topics USING btree
        (actable_type, actable_id)
      SQL
    end
  end
end

{'fraction_non_alphanumeric': 0.07841472045293701, 'fraction_numerical': 0.001556970983722576, 'mean_word_length': 2.4603330068560236, 'pattern_counts': {'":': 0, '<': 18, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2128357', 'n_tokens_mistral': 2240, 'n_tokens_neox': 2137, 'n_words': 518}