class AddMasterSectionToInsightsAndSummaries < ActiveRecord::Migration[7.0]
  def up
    add_column :interview_insights, :master_section_id, :integer
    add_column :insight_summaries, :master_section_id, :integer

    add_index :interview_insights, :master_section_id
    add_index :insight_summaries, :master_section_id

    # 1. Backfill the new QuestionSection join table from existing questions
    # Use INNER JOIN to automatically filter out questions pointing to deleted master sections
    valid_questions = InterviewQuestion.joins("INNER JOIN master_sections ON master_sections.id = interview_questions.master_section_id")

    valid_questions.find_each do |question|
      QuestionSection.find_or_create_by!(
        interview_question_id: question.id,
        master_section_id: question.master_section_id
      )
    end

    # 2. Backfill existing InterviewInsights via raw SQL for performance
    # Also ensures we only backfill where the master section exists
    execute <<-SQL
      UPDATE interview_insights
      SET master_section_id = interview_questions.master_section_id
      FROM interview_questions
      INNER JOIN master_sections ON master_sections.id = interview_questions.master_section_id
      WHERE interview_insights.interview_question_id = interview_questions.id
    SQL

    # 3. Backfill existing InsightSummaries via raw SQL
    execute <<-SQL
      UPDATE insight_summaries
      SET master_section_id = interview_questions.master_section_id
      FROM interview_questions
      INNER JOIN master_sections ON master_sections.id = interview_questions.master_section_id
      WHERE insight_summaries.interview_question_id = interview_questions.id
    SQL
  end

  def down
    remove_column :interview_insights, :master_section_id
    remove_column :insight_summaries, :master_section_id
  end
end