require 'set'

module ReportParsers
  class BaseParser
    def initialize(yearly_file, spreadsheet:)
      @yearly_file = yearly_file
      @spreadsheet = spreadsheet
    end

    private

    attr_reader :yearly_file, :spreadsheet

    def sheet_exists?(sheet_name)
      spreadsheet.sheets.include?(sheet_name)
    end

    def read_cell(sheet, row, col)
      value = sheet.cell(row, col)

      # Handle merged cells: if value is nil/blank, try reading from nearby cells
      if value.blank? && sheet.respond_to?(:merged_cells)
        value = find_merged_cell_value(sheet, row, col)
      end

      value.is_a?(String) ? value.strip.presence : value
    end

    def find_merged_cell_value(sheet, row, col)
      # Try to find value in merged cell range
      # Check cells in a small radius around the target cell
      (-1..1).each do |row_offset|
        (-1..1).each do |col_offset|
          next if row_offset == 0 && col_offset == 0

          check_row = row + row_offset
          check_col = col + col_offset

          next if check_row < 1 || check_col < 1
          next if check_row > sheet.last_row || check_col > sheet.last_column

          value = sheet.cell(check_row, check_col)
          return value if value.present?
        end
      end

      nil
    end

    def find_value_by_labels(sheet, labels, row_range:, value_offset: 1)
      position = find_label_position(sheet, labels, row_range: row_range)
      return if position.nil?

      row, col = position

      max_col = sheet.last_column.to_i
      (col + value_offset).upto(max_col) do |search_col|
        value = read_cell(sheet, row, search_col)
        return value if value.present?
      end

      nil
    end

    def find_label_position(sheet, labels, row_range:)
      normalized_labels = Array(labels).map { |label| normalize_label(label) }.to_set
      max_col = sheet.last_column.to_i
      return if max_col <= 1

      row_range.each do |row|
        1.upto(max_col - 1) do |col|
          label_text = normalize_label(sheet.cell(row, col))
          next if label_text.blank?
          next unless label_matches?(label_text, normalized_labels)

          return [row, col]
        end
      end

      nil
    end

    def label_matches?(label_text, normalized_labels)
      normalized_labels.any? do |label|
        label_text == label ||
          label_text.start_with?("#{label} ") ||
          label_text.start_with?("#{label}:")
      end
    end

    def normalize_label(value)
      value.to_s
           .downcase
           .tr("\u00A0", ' ')
           .gsub(/\s+/, ' ')
           .gsub(/[«»]/, '')
           .strip
    end

    def normalize_line_name(value)
      text = value.to_s
                 .tr("\u00A0", ' ')
                 .gsub(/[⁰¹²³⁴⁵⁶⁷⁸⁹]/, '')
                 .gsub(/(?<=\p{L})\d{1,2}(?=\s|\z)/u, '')
                 .gsub(/\s+/, ' ')
                 .strip

      text.presence
    end
  end
end