class Xlsxrb::WorksheetBuilder

# File lib/xlsxrb/worksheet_builder.rb, line 15
def initialize(name, strict_excel_mode: true)
  @name = name
  @strict_excel_mode = strict_excel_mode
  @rows = []
  @columns = []
  @charts = []
  @styles = {} # { style_name => StyleBuilder }
  @style_index_map = {} # { style_name => xf_index } (populated at build time)
  @hyperlinks = []
  @auto_filter = nil
  @filter_columns = {}
  @sort_state = nil
  @data_validations = []
  @conditional_formats = []
  @tables = []
  @comments = []
  @sparkline_groups = []
  @merge_cells_ranges = []
  @freeze_pane = nil
  @split_pane = nil
  @selection = nil
  @page_margins = nil
  @page_setup = {}
  @header_footer = {}
  @print_options = {}
  @sheet_protection = nil
  @images = []
  @shapes = []
  @sheet_properties = {}
  @sheet_view = {}
  @row_breaks = []
  @col_breaks = []
end

# File lib/xlsxrb/worksheet_builder.rb, line 297
def auto_filter(range)
  @auto_filter = range
end

# File lib/xlsxrb/worksheet_builder.rb, line 618
def build
  facade_meta = {}
  facade_meta[:hyperlinks] = @hyperlinks unless @hyperlinks.empty?
  facade_meta[:auto_filter] = @auto_filter if @auto_filter
  facade_meta[:filter_columns] = @filter_columns unless @filter_columns.empty?
  facade_meta[:sort_state] = @sort_state if @sort_state
  facade_meta[:data_validations] = @data_validations unless @data_validations.empty?
  facade_meta[:conditional_formats] = @conditional_formats unless @conditional_formats.empty?
  facade_meta[:tables] = @tables unless @tables.empty?
  facade_meta[:pivot_tables] = @pivot_tables unless (@pivot_tables || []).empty?
  facade_meta[:comments] = @comments unless @comments.empty?
  facade_meta[:sparkline_groups] = @sparkline_groups unless @sparkline_groups.empty?
  facade_meta[:merge_cells] = @merge_cells_ranges unless @merge_cells_ranges.empty?
  facade_meta[:freeze_pane] = @freeze_pane if @freeze_pane
  facade_meta[:split_pane] = @split_pane if @split_pane
  facade_meta[:selection] = @selection if @selection
  facade_meta[:page_margins] = @page_margins if @page_margins
  facade_meta[:page_setup] = @page_setup unless @page_setup.empty?
  facade_meta[:header_footer] = @header_footer unless @header_footer.empty?
  facade_meta[:print_options] = @print_options unless @print_options.empty?
  facade_meta[:sheet_protection] = @sheet_protection if @sheet_protection
  facade_meta[:images] = @images unless @images.empty?
  facade_meta[:shapes] = @shapes unless @shapes.empty?
  facade_meta[:sheet_properties] = @sheet_properties unless @sheet_properties.empty?
  facade_meta[:sheet_view] = @sheet_view unless @sheet_view.empty?
  facade_meta[:row_breaks] = @row_breaks unless @row_breaks.empty?
  facade_meta[:col_breaks] = @col_breaks unless @col_breaks.empty?

  Elements::Worksheet.new(
    name: @name, rows: @rows, columns: @columns, charts: @charts,
    unmapped_data: facade_meta.empty? ? {} : { facade: facade_meta }
  )
end

# File lib/xlsxrb/worksheet_builder.rb, line 263
def chart(**options)
  if block_given?
    builder = ChartBuilder.new
    yield builder
    options = builder.options.merge(options)
  end
  @charts << options
end

# File lib/xlsxrb/worksheet_builder.rb, line 241
def column(index, width: nil, hidden: false, custom_width: false, outline_level: nil)
  raise ArgumentError, "Column width #{width} must be between 0 and 255 characters (Excel limitation)" if @strict_excel_mode && width && (width.negative? || width > 255)

  DslHelpers.normalize_column_indices(index).each do |idx|
    @columns << Elements::Column.new(
      index: idx,
      width: width,
      hidden: hidden,
      custom_width: custom_width || !width.nil?,
      outline_level: outline_level
    )
  end
end

# File lib/xlsxrb/worksheet_builder.rb, line 392
def comment(cell, text, author: "Author")
  @comments << { cell: cell, text: text, author: author }
end

# File lib/xlsxrb/worksheet_builder.rb, line 342
def conditional_format(sqref, **opts)
  @conditional_formats << opts.merge(sqref: sqref)
end

# File lib/xlsxrb/worksheet_builder.rb, line 308
def filter_column(col_id, filter)
  @filter_columns[col_id] = filter
end

# File lib/xlsxrb/worksheet_builder.rb, line 445
def freeze_pane(row: 0, col: 0)
  col = Elements::Cell.column_index(col)
  @freeze_pane = { row: row, col: col }
end

# File lib/xlsxrb/worksheet_builder.rb, line 508
def header_footer(**opts)
  @header_footer.merge!(opts)
end

# File lib/xlsxrb/worksheet_builder.rb, line 282
def hyperlink(cell, url = nil, display: nil, tooltip: nil, location: nil)
  link = { cell: cell }
  link[:url] = url if url
  link[:display] = display if display
  link[:tooltip] = tooltip if tooltip
  link[:location] = location if location
  @hyperlinks << link
end

# File lib/xlsxrb/worksheet_builder.rb, line 545
def image(file_data, ext: "png", from_col: 0, from_row: 0, to_col: 5, to_row: 10, **opts)
  img = { file_data: file_data, ext: ext, from_col: from_col, from_row: from_row, to_col: to_col, to_row: to_row }
  img.merge!(opts)
  @images << img
end

# File lib/xlsxrb/worksheet_builder.rb, line 428
def merge(range = nil, row: nil, col_start: nil, col_end: nil, row_start: nil, row_end: nil)
  canonical = DslHelpers.normalize_merge_range(
    range,
    row: row, col_start: col_start, col_end: col_end,
    row_start: row_start, row_end: row_end,
    strict_excel_mode: @strict_excel_mode
  )
  @merge_cells_ranges << canonical unless @merge_cells_ranges.include?(canonical)
end

# File lib/xlsxrb/worksheet_builder.rb, line 608
def page_break_col(col_index)
  col_index = Elements::Cell.column_index(col_index)
  @col_breaks << col_index
end

# File lib/xlsxrb/worksheet_builder.rb, line 598
def page_break_row(row_num)
  @row_breaks << row_num
end

# File lib/xlsxrb/worksheet_builder.rb, line 486
def page_margins(left: nil, right: nil, top: nil, bottom: nil, header: nil, footer: nil)
  @page_margins = DslHelpers.normalize_page_margins(
    left: left, right: right, top: top, bottom: bottom, header: header, footer: footer
  )
end

# File lib/xlsxrb/worksheet_builder.rb, line 498
def page_setup(**opts)
  @page_setup.merge!(opts)
end

# File lib/xlsxrb/worksheet_builder.rb, line 374
def pivot_table(source_ref, row_fields:, data_fields:, col_fields: [], dest_ref: "E1", name: nil, field_names: nil, items: nil)
  @pivot_tables ||= []
  @pivot_tables << {
    source_ref: source_ref, row_fields: row_fields,
    data_fields: data_fields, col_fields: col_fields,
    dest_ref: dest_ref, name: name,
    field_names: field_names, items: items
  }
end

# File lib/xlsxrb/worksheet_builder.rb, line 519
def print_options(name, value)
  @print_options[name] = value
end

# File lib/xlsxrb/worksheet_builder.rb, line 529
def protect_sheet(**opts)
  @sheet_protection = DslHelpers.normalize_protection_options(opts)
end

# File lib/xlsxrb/worksheet_builder.rb, line 88
def row(values, styles: nil, height: nil, hidden: false, custom_height: false, outline_level: nil)
  row_index = @rows.size
  # See: https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3
  if @strict_excel_mode
    raise ArgumentError, "Row index #{row_index} exceeds Excel limit of 1,048,576 rows" if row_index >= 1_048_576
    raise ArgumentError, "Row height #{height} must be between 0 and 409 points (Excel limitation)" if height && (height.negative? || height > 409)
  end

  if values.is_a?(Hash)
    max_col = values.keys.map { |k| Elements::Cell.column_index(k) }.max || -1
    cells_array = Array.new(max_col + 1)
    values.each do |k, v|
      idx = Elements::Cell.column_index(k)
      cells_array[idx] = v
    end
    values = cells_array
  end

  if styles.is_a?(Hash)
    expanded_styles = {}
    styles.each do |k, v|
      if k.is_a?(Range) || k.is_a?(Array)
        k.each { |idx| expanded_styles[Elements::Cell.column_index(idx)] = v }
      else
        expanded_styles[Elements::Cell.column_index(k)] = v
      end
    end
    max_col_style = expanded_styles.keys.max || -1
    styles_array = Array.new(max_col_style + 1)
    expanded_styles.each do |idx, v|
      styles_array[idx] = v
    end
    styles = styles_array
  end

  # Auto-detect Date / Time for built-in styles
  values.each_with_index do |val, idx|
    cell_style = styles.is_a?(Array) ? styles[idx] : styles

    if val.is_a?(Date) && cell_style.nil?
      style("__xlsxrb_date", number_format: "yyyy-mm-dd") unless @styles.key?("__xlsxrb_date")
      styles = [] if styles.nil?
      styles = Array.new(values.size, styles) unless styles.is_a?(Array)
      styles[idx] = "__xlsxrb_date"
    elsif val.is_a?(Time) && cell_style.nil?
      style("__xlsxrb_time", number_format: "yyyy-mm-dd hh:mm:ss") unless @styles.key?("__xlsxrb_time")
      styles = [] if styles.nil?
      styles = Array.new(values.size, styles) unless styles.is_a?(Array)
      styles[idx] = "__xlsxrb_time"
    end
  end

  max_len = values.size
  max_len = [max_len, styles.size].max if styles.is_a?(Array)
  # See: https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3
  raise ArgumentError, "Row contains #{max_len} columns, exceeding Excel limit of 16_384 columns" if @strict_excel_mode && max_len > 16_384

  cells = Array.new(max_len)
  style_lookup = styles.is_a?(Array)

  col_index = 0
  while col_index < max_len
    val = col_index < values.size ? values[col_index] : nil
    raise ArgumentError, "Invalid cell value type or value: #{val.class} for value #{val.inspect}" unless val.nil? || val.is_a?(String) || (val.is_a?(Numeric) && !(val.is_a?(Float) && (val.infinite? || val.nan?))) || val.is_a?(TrueClass) || val.is_a?(FalseClass) || val.is_a?(Date) || val.is_a?(Time) || val.is_a?(Elements::Formula) || (val.is_a?(Hash) && val.key?(:formula)) || val.is_a?(Elements::RichText) || val.is_a?(Elements::CellError) || (val.is_a?(Array) && val.first.is_a?(Hash) && (val.first.key?(:text) || val.first.key?("text")))
    # See: https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3
    raise ArgumentError, "Cell text length #{val.length} exceeds Excel limit of 32,767 characters" if @strict_excel_mode && val.is_a?(String) && val.length > 32_767

    if val.is_a?(Array) && val.first.is_a?(Hash) && (val.first.key?(:text) || val.first.key?("text"))
      # Coerce array of hashes to RichText
      runs = val.map do |run|
        text = run[:text] || run["text"]
        font = run.reject { |k| k.to_s == "text" }
        { text: text, font: font.empty? ? nil : font }.compact
      end
      val = Elements::RichText.new(runs: runs)
    end

    style_name = if style_lookup
                   col_index < styles.size ? styles[col_index] : nil
                 else
                   styles
                 end

    if style_name.is_a?(Hash)
      inline_name = "__inline_#{style_name.hash}"
      style(inline_name, **style_name) unless @styles.key?(inline_name)
      style_name = inline_name
    end
    if val.nil? && style_name.nil?
      col_index += 1
      next
    end
    # If value is a Formula object or Hash with :formula, store it as the cell's formula
    cells[col_index] = if val.is_a?(Elements::Formula)
                         Elements::Cell.new(
                           row_index: row_index,
                           column_index: col_index,
                           value: val.cached_value,
                           formula: val,
                           style_index: style_name
                         )
                       elsif val.is_a?(Hash) && val.key?(:formula)
                         f_obj = Elements::Formula.new(
                           expression: val[:formula],
                           cached_value: val[:value],
                           calculate_always: val[:calculate_always]
                         )
                         Elements::Cell.new(
                           row_index: row_index,
                           column_index: col_index,
                           value: val[:value],
                           formula: f_obj,
                           style_index: style_name
                         )
                       else
                         Elements::Cell.new(
                           row_index: row_index,
                           column_index: col_index,
                           value: val,
                           style_index: style_name
                         )
                       end
    col_index += 1
  end

  cells.compact!
  @rows << Elements::Row.new(
    index: row_index,
    cells: cells,
    height: height,
    hidden: hidden,
    custom_height: custom_height || !height.nil?,
    outline_level: outline_level
  )
end

# File lib/xlsxrb/worksheet_builder.rb, line 470
def select_cell(active_cell, sqref: nil, pane: nil)
  @selection = { active_cell: active_cell, sqref: sqref || active_cell }
  @selection[:pane] = pane if pane
end

# File lib/xlsxrb/worksheet_builder.rb, line 563
def shape(preset: "rect", text: nil, from_col: 0, from_row: 0, to_col: 5, to_row: 5, **opts)
  shape = { preset: preset, text: text, from_col: from_col, from_row: from_row, to_col: to_col, to_row: to_row }
  shape[:name] = opts.delete(:name) || "Shape #{@shapes.size + 1}"
  shape.merge!(opts)
  @shapes << shape
end

# File lib/xlsxrb/worksheet_builder.rb, line 577
def sheet_properties(name, value)
  @sheet_properties[name] = value
end

# File lib/xlsxrb/worksheet_builder.rb, line 588
def sheet_view(name, value)
  @sheet_view[name] = value
end

# File lib/xlsxrb/worksheet_builder.rb, line 320
def sort_state(ref, sort_conditions, **opts)
  @sort_state = { ref: ref, sort_conditions: sort_conditions }.merge(opts)
end

# File lib/xlsxrb/worksheet_builder.rb, line 404
def sparkline_group(sparklines:, type: nil, **opts)
  group = { sparklines: sparklines }
  group[:type] = type if type
  group.merge!(opts)
  @sparkline_groups << group
end

# File lib/xlsxrb/worksheet_builder.rb, line 458
def split_pane(x_split: 0, y_split: 0, top_left_cell: nil)
  @split_pane = { x_split: x_split, y_split: y_split, top_left_cell: top_left_cell }
end

# File lib/xlsxrb/worksheet_builder.rb, line 61
def style(name, **opts)
  style_name = name.to_s
  style_builder = StyleBuilder.new(style_name)
  style_builder.apply_options!(**opts) unless opts.empty?
  yield style_builder if block_given?
  @styles[style_name] = style_builder
  style_builder
end

# File lib/xlsxrb/worksheet_builder.rb, line 357
def table(ref, columns:, name: nil, display_name: nil, style: nil, **)
  @tables << DslHelpers.normalize_table_options(ref, columns: columns, name: name, display_name: display_name, style: style, **)
end

# File lib/xlsxrb/worksheet_builder.rb, line 331
def validate_data(sqref, **opts)
  @data_validations << opts.merge(sqref: sqref)
end