class AnalysisReportsController < ApplicationController
  before_action :set_analysis_report
  before_action :set_ancestors

  include ExcelSanitizable

  def download_report
    @sections_report = @analysis_report.sections_report
    @insights_report = @analysis_report.insights_report

    require "axlsx"

    p = Axlsx::Package.new

    # ─── SECTIONS SHEETS ──────────────────────────────────────────────────────
    if @sections_report.present?
      respondents = @sections_report.respondents
                                    .includes(:transcript_sections)
                                    .order(:first_name)

      section_data = MasterSection
                     .where(id: @sections_report.section_ids)
                     .order(:position)
                     .pluck(:id, :name)

      highlight_themes = @sections_report.respondent_category.highlight_themes
      respondents_with_highlights = @sections_report.respondents.distinct
                                                    .joins(:transcript_highlights)
                                                    .where(transcript_highlights: { highlight_theme_id: highlight_themes.select(:id) })

      sections_with_highlights = section_data.collect(&:first) &
                                 TranscriptHighlight.where(highlight_theme: highlight_themes).pluck(:master_section_id)

      highlight_table_data = MasterSection.where(id: sections_with_highlights).order(:position).map do |section|
        {
          section:,
          themes_data: highlight_themes.order(:position).map do |theme|
            {
              theme:,
              highlights_by_respondent: respondents_with_highlights.map do |respondent|
                {
                  respondent_id: respondent.id,
                  highlights: respondent.transcript_highlights
                              .includes(:highlight_theme, :master_section)
                              .where(highlight_theme: theme, master_section: section)
                }
              end
            }
          end
        }
      end

      section_header_row    = ["Section"] + respondents.map(&:name)
      highlight_header_row  = ["Section | Theme"] + respondents_with_highlights.map(&:name)

      section_rows   = [section_header_row]
      summary_rows   = [section_header_row]
      aggregate_rows = [["Section", "Aggregate Summary"]]
      highlight_rows = [highlight_header_row]

      section_data.each do |master_section_id, section_name|
        s_text = sanitize_and_truncate_for_excel(section_name)

        section_row  = [s_text]
        summary_row  = [s_text]
        aggr_row     = [s_text]

        respondents.each do |r|
          sections = r.transcript_sections.where(master_section_id:).to_a

          if sections.empty?
            section_row << " NOT FOUND "
            summary_row << " NOT FOUND "
          else
            combined_content = sections.each_with_index.map { |s, i| sections.length > 1 ? "▶ Excerpt #{i + 1}\n#{s.content}" : s.content }.join("\n\n")
            combined_summary = sections.each_with_index.map { |s, i| sections.length > 1 ? "▶ Excerpt #{i + 1}\n#{s.ai_summary}" : s.ai_summary }.join("\n\n")

            section_row << sanitize_and_truncate_for_excel(combined_content)
            summary_row << sanitize_and_truncate_for_excel(combined_summary)
          end
        end

        aggr_summary = SectionSummary.where(master_section_id:, all_data: false, groupable: @sections_report).last
        aggr_row << (aggr_summary ? sanitize_and_truncate_for_excel(aggr_summary.summary) : " NOT FOUND ")

        section_rows   << section_row
        summary_rows   << summary_row
        aggregate_rows << aggr_row
      end

      highlight_table_data.each do |hs|
        hs[:themes_data].each do |theme_data|
          row = ["#{hs[:section].name} | #{theme_data[:theme].theme}"]
          theme_data[:highlights_by_respondent].each do |rh|
            if rh[:highlights].blank?
              row << "NOT FOUND"
            else
              rh[:highlights].each { |h| row << sanitize_and_truncate_for_excel(h.verbose_to_text) }
            end
          end
          highlight_rows << row
        end
      end

      p.workbook.add_worksheet(name: "Sections") do |sheet|
        style = sheet.styles.add_style(alignment: { wrap_text: true, vertical: :top })
        section_rows.each { |row| sheet.add_row row, style: style }
        sheet.column_widths 30, 100
      end

      p.workbook.add_worksheet(name: "Section Summaries") do |sheet|
        style = sheet.styles.add_style(alignment: { wrap_text: true, vertical: :top })
        summary_rows.each { |row| sheet.add_row row, style: style }
        sheet.column_widths 30, 100
      end

      p.workbook.add_worksheet(name: "Section Aggregate Summaries") do |sheet|
        style = sheet.styles.add_style(alignment: { wrap_text: true, vertical: :top })
        aggregate_rows.each { |row| sheet.add_row row, style: style }
        sheet.column_widths 30, 100
      end

      p.workbook.add_worksheet(name: "Highlights") do |sheet|
        style = sheet.styles.add_style(alignment: { wrap_text: true, vertical: :top })
        highlight_rows.each { |row| sheet.add_row row, style: style }
        sheet.column_widths 30, 100
      end
    end

    # ─── INSIGHTS SHEETS ──────────────────────────────────────────────────────
    if @insights_report.present?
      respondents = Respondent
                    .includes(:interview_insights)
                    .joins(:interview_insights)
                    .where(interview_insights: { insights_report_id: @insights_report.id })
                    .order(:first_name)
                    .distinct

      header_row = ["Question"] + respondents.map(&:name)

      insight_rows      = [header_row]
      insight_summ_rows = [header_row]
      insight_aggr_rows = [%w[Question Summary]]

      @insights_report.interview_questions.includes(:insight_summaries).find_each do |question|
        q_text = sanitize_and_truncate_for_excel(question.question)

        question_row = [q_text]
        summary_row  = [q_text]
        aggr_row     = [
          q_text,
          sanitize_and_truncate_for_excel(
            question.insight_summaries.select { |s| s.groupable == @insights_report }.last&.summary
          )
        ]

        respondents.each do |r|
          insight = r.interview_insights
                     .select { |x| x.interview_question_id == question.id && x.insights_report_id == @insights_report.id }
                     .last

          if insight
            question_row << sanitize_and_truncate_for_excel("\"#{insight.sources}\"")
            summary_row  << sanitize_and_truncate_for_excel(insight.ai_summary)
          else
            question_row << " NOT FOUND "
            summary_row  << " NOT FOUND "
          end
        end

        insight_rows      << question_row
        insight_summ_rows << summary_row
        insight_aggr_rows << aggr_row
      end

      p.workbook.add_worksheet(name: "Insights") do |sheet|
        style = sheet.styles.add_style(alignment: { wrap_text: true, vertical: :top })
        insight_rows.each { |row| sheet.add_row row, style: style }
        sheet.column_widths 30, 100
      end

      p.workbook.add_worksheet(name: "Insight Summaries") do |sheet|
        style = sheet.styles.add_style(alignment: { wrap_text: true, vertical: :top })
        insight_summ_rows.each { |row| sheet.add_row row, style: style }
        sheet.column_widths 30, 100
      end

      p.workbook.add_worksheet(name: "Insight Aggregate Summaries") do |sheet|
        style = sheet.styles.add_style(alignment: { wrap_text: true, vertical: :top })
        insight_aggr_rows.each { |row| sheet.add_row row, style: style }
        sheet.column_widths 30, 100
      end
    end

    excel_data = p.to_stream.read
    send_data excel_data,
              filename: "#{@analysis_report.name.parameterize}_analysis_report.xlsx",
              type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
  end

  def queue_report_generation
    @analysis_report.queue_report_generation!(queue_insight_generation: @analysis_report.requires_insight_regeneration, user_id: current_user.id)
    redirect_to analysis_reports_project_respondent_category_path(@project, @respondent_category),
                notice: "Report Generation Queued"
  end

  def show
    @sections_report = @analysis_report.sections_report
    @insights_report = @analysis_report.insights_report
    @themes_report   = @analysis_report.themes_report

    # Preload section summaries into memory hashes to prevent N+1 in the view
    @section_summaries_by_id = SectionSummary.where(all_data: false, groupable: @sections_report)
                                             .group_by(&:master_section_id)

    # ==========================================
    # INSIGHTS REPORT DATA
    # ==========================================
    # Preload insight summaries using the tuple grouping
    @insight_summaries_by_id = InsightSummary.where(all_data: false, groupable: @insights_report)
                                             .group_by { |s| [s.interview_question_id, s.master_section_id] }

    @insights_respondents = @analysis_report.respondents.includes(:project, :respondent_category, :tags, :interview_insights)
                                            .joins(:interview_insights)
                                            .where(interview_insights: { insights_report_id: @insights_report.id })
                                            .order(:first_name)
                                            .distinct

    # Build a structured memory hash for the view
    all_report_insights = InterviewInsight.includes(:interview_question, :master_section)
                                          .where(insights_report_id: @insights_report.id)

    @insights_grouped_data = all_report_insights.group_by(&:master_section).map do |section, insights|
      {
        section: section,
        questions_data: insights.group_by(&:interview_question).map do |question, q_insights|
          {
            question: question,
            row_id: "#{question.id}_#{section&.id || 'legacy'}",
            insights_by_respondent: q_insights.index_by(&:respondent_id),
            summary: @insight_summaries_by_id[[question.id, section&.id]]&.last
          }
        end.sort_by { |qd| qd[:question].position || 0 } # rubocop:disable Style/MultilineBlockChain
      }
    end.sort_by { |g| g[:section]&.position || -1 } # -1 pushes legacy (nil) to the top # rubocop:disable Style/MultilineBlockChain

    # Single source of truth for all reports
    @respondents = @analysis_report.respondents.includes(:transcript_sections, :tags).order(:first_name)

    # ==========================================
    # SECTIONS REPORT DATA
    # ==========================================
    sections_master_sections = MasterSection.where(id: @sections_report.section_ids)
                                            .order(:position)
                                            .pluck(:id)

    @sections_report_sections = @analysis_report.respondents.joins(:transcript_sections)
                                                .where(transcript_sections: { master_section_id: sections_master_sections })
                                                .select("transcript_sections.name, transcript_sections.master_section_id")
                                                .group("transcript_sections.name, transcript_sections.master_section_id")
                                                .order(Arel.sql("array_position(ARRAY[#{sections_master_sections.join(',')}], transcript_sections.master_section_id)"))
                                                .pluck("transcript_sections.name", "transcript_sections.master_section_id")

    # ==========================================
    # THEMES REPORT DATA
    # ==========================================
    themes_master_sections = MasterSection.where(id: @themes_report.section_ids)
                                          .order(:position)
                                          .pluck(:id)

    @highlight_themes = @themes_report.respondent_category.highlight_themes

    # Deriving the specific respondent subsets from our global @respondents pool
    @respondents_with_highlights = @respondents.joins(:transcript_highlights)
                                               .where.not(transcript_highlights: { master_section_id: nil })
                                               .where(transcript_highlights: { highlight_theme_id: @highlight_themes.select(:id) })
                                               .distinct

    @respondents_with_highlights_raw = @respondents.joins(:transcript_highlights)
                                                   .where(transcript_highlights: {
                                                            master_section_id: nil,
                                                            highlight_theme_id: @highlight_themes.select(:id)
                                                          })
                                                   .distinct

    @respondents_with_highlights_unthemed = @respondents.joins(:transcript_highlights)
                                                        .where.not(transcript_highlights: { master_section_id: nil })
                                                        .where(transcript_highlights: { highlight_theme_id: nil })
                                                        .distinct

    sections_with_unthemed_highlights = themes_master_sections & TranscriptHighlight.where(highlight_theme_id: nil)
                                                                                    .where.not(master_section_id: nil)
                                                                                    .pluck(:master_section_id)

    sections_with_highlights = themes_master_sections & TranscriptHighlight.where(highlight_theme: @highlight_themes)
                                                                           .where.not(master_section_id: nil)
                                                                           .pluck(:master_section_id)

    # 1. RAW HIGHLIGHTS (Grouped in memory)
    all_raw_highlights = TranscriptHighlight.includes(:highlight_theme)
                                            .where(
                                              respondent_id: @respondents_with_highlights_raw.map(&:id),
                                              master_section_id: nil,
                                              highlight_theme: @highlight_themes
                                            )
                                            .order(created_at: :asc)
                                            .group_by { |th| [th.highlight_theme_id, th.respondent_id] }

    @raw_highlight_data = @highlight_themes.order(:position).map do |theme|
      highlights_by_respondent = @respondents_with_highlights_raw.map do |respondent|
        highlights = all_raw_highlights[[theme.id, respondent.id]] || []
        next if highlights.blank?

        { respondent_id: respondent.id, highlights: }
      end.compact

      next unless highlights_by_respondent.any?

      { theme:, highlights_by_respondent: }
    end.compact

    # 2. THEMED HIGHLIGHTS (Grouped in memory)
    all_themed_highlights = TranscriptHighlight.includes(:highlight_theme, :master_section)
                                               .where(
                                                 respondent_id: @respondents_with_highlights.map(&:id),
                                                 highlight_theme: @highlight_themes,
                                                 master_section_id: sections_with_highlights
                                               )
                                               .order(created_at: :asc)
                                               .group_by { |th| [th.master_section_id, th.highlight_theme_id, th.respondent_id] }

    @highlight_table_data = MasterSection.where(id: sections_with_highlights).order(:position).map do |section|
      theme_data = @highlight_themes.order(:position).map do |theme|
        highlights_by_respondent = @respondents_with_highlights.map do |respondent|
          highlights = all_themed_highlights[[section.id, theme.id, respondent.id]] || []
          next if highlights.blank?

          { respondent_id: respondent.id, highlights: }
        end.compact

        next unless highlights_by_respondent.any?

        { theme:, highlights_by_respondent: }
      end.compact

      next unless theme_data.any?

      { section:, themes_data: theme_data }
    end.compact

    # 3. UNTHEMED HIGHLIGHTS (Grouped in memory)
    all_unthemed_highlights = TranscriptHighlight.includes(:master_section)
                                                 .where(
                                                   respondent_id: @respondents_with_highlights_unthemed.map(&:id),
                                                   highlight_theme_id: nil,
                                                   master_section_id: sections_with_unthemed_highlights
                                                 )
                                                 .order(created_at: :asc)
                                                 .group_by { |th| [th.master_section_id, th.respondent_id] }

    @unthemed_highlight_table_data = MasterSection.where(id: sections_with_unthemed_highlights).order(:position).map do |section|
      highlights_by_respondent = @respondents_with_highlights_unthemed.map do |respondent|
        highlights = all_unthemed_highlights[[section.id, respondent.id]] || []
        next if highlights.blank?

        { respondent_id: respondent.id, highlights: }
      end.compact

      next unless highlights_by_respondent.any?

      { section:, highlights_by_respondent: }
    end.compact
  end

  def destroy
    @analysis_report.destroy

    respond_to do |format|
      format.html do
        redirect_to analysis_reports_project_respondent_category_path(@project, @respondent_category),
                    notice: "Report deleted."
      end
      format.json { head :no_content }
    end
  end

  private

  def set_ancestors
    @project = Project.find(params[:project_id])
    @respondent_category = RespondentCategory.find(params[:respondent_category_id])
  end

  # Use callbacks to share common setup or constraints between actions.
  def set_analysis_report
    @analysis_report = AnalysisReport.find(params[:id])
  end
end