class Xlsxrb::WorksheetBuilder

# File lib/xlsxrb/worksheet_builder.rb, line 16
def initialize(name, strict_excel_mode: true, state: :visible)
  @name = name
  @strict_excel_mode = strict_excel_mode
  @state = state ? state.to_sym : :visible
  @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 = []
  @print_area = nil
  @print_titles = nil
end

# File lib/xlsxrb/worksheet_builder.rb, line 224
def <<(values)
  row(values)
  self
end

# File lib/xlsxrb/worksheet_builder.rb, line 444
def add_sparkline(location: nil, range: nil, type: nil, **opts)
  loc = location || opts.delete(:location_ref)
  rng = range || opts.delete(:data_ref) || opts.delete(:data)
  raise ArgumentError, "Missing required location: parameter for sparkline" unless loc
  raise ArgumentError, "Missing required range: parameter for sparkline" unless rng

  mapped_type = case type&.to_s
                when "win_loss" then "stacked"
                when nil, "" then nil
                else type.to_s
                end
  sparkline_group(sparklines: [{ data_ref: rng.to_s, location_ref: loc.to_s }], type: mapped_type, **opts)
end

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

# File lib/xlsxrb/worksheet_builder.rb, line 757
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?
  facade_meta[:print_area] = @print_area if @print_area
  facade_meta[:print_titles] = @print_titles if @print_titles

  hl_hash = {}
  @hyperlinks.each do |h|
    ref = h[:cell].to_s.upcase
    entry = {}
    entry[:url] = h[:url] if h[:url]
    entry[:display] = h[:display] if h[:display]
    entry[:tooltip] = h[:tooltip] if h[:tooltip]
    entry[:location] = h[:location] if h[:location]
    hl_hash[ref] = entry unless entry.empty?
  end

  cm_hash = {}
  @comments.each do |c|
    ref = (c[:ref] || c[:cell]).to_s.upcase
    cm_hash[ref] = c
  end

  rows = if hl_hash.empty? && cm_hash.empty?
           @rows
         else
           @rows.map do |row|
             new_cells = row.cells.map do |cell|
               hl = hl_hash[cell.ref]
               cm = cm_hash[cell.ref]
               if hl || cm
                 cell.with(hyperlink: hl || cell.hyperlink, comment: cm || cell.comment)
               else
                 cell
               end
             end
             row.with(cells: new_cells)
           end
         end

  Elements::Worksheet.new(
    name: @name, rows: rows, columns: @columns, charts: @charts,
    conditional_formatting: @conditional_formats,
    data_validations: @data_validations,
    unmapped_data: facade_meta.empty? ? {} : { facade: facade_meta },
    state: @state,
    hyperlinks: hl_hash,
    comments: @comments,
    print_area: @print_area,
    print_titles: @print_titles,
    sparkline_groups: @sparkline_groups
  )
end

# File lib/xlsxrb/worksheet_builder.rb, line 269
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 244
def column(index, width: nil, hidden: false, custom_width: false, outline_level: nil, style: 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|
    unmapped = style ? { style_index: style } : {}
    @columns << Elements::Column.new(
      index: idx,
      width: width,
      hidden: hidden,
      custom_width: custom_width || !width.nil?,
      outline_level: outline_level,
      style_index: style,
      unmapped_data: unmapped
    )
  end
end

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

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

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

# File lib/xlsxrb/worksheet_builder.rb, line 492
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 555
def header_footer(**opts)
  @header_footer.merge!(opts)
end

# File lib/xlsxrb/worksheet_builder.rb, line 288
def hyperlink(cell, url = nil, display: nil, tooltip: nil, location: nil)
  if url.is_a?(Elements::Hyperlink)
    link = { cell: cell }
    link[:url] = url.url if url.url
    link[:display] = url.display || display if url.display || display
    link[:tooltip] = url.tooltip || tooltip if url.tooltip || tooltip
    link[:location] = url.location || location if url.location || location
    @hyperlinks << link
    return
  end
  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 684
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 475
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 747
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 737
def page_break_row(row_num)
  @row_breaks << row_num
end

# File lib/xlsxrb/worksheet_builder.rb, line 533
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 545
def page_setup(**opts)
  @page_setup.merge!(opts)
end

# File lib/xlsxrb/worksheet_builder.rb, line 389
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 582
def print_area(range = nil)
  return @print_area if range.nil?

  @print_area = range
end

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

# File lib/xlsxrb/worksheet_builder.rb, line 601
def print_titles(rows: nil, cols: nil)
  return @print_titles if rows.nil? && cols.nil?

  @print_titles = { rows: rows, cols: cols }
end

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

# File lib/xlsxrb/worksheet_builder.rb, line 645
def repeat_columns(first, last = nil)
  cols_str = if first.is_a?(String)
               first.include?(":") ? first : "#{first}:#{last || first}"
             else
               first_name = Utils.col_index_to_name(first)
               last_name = if last.is_a?(Integer)
                             Utils.col_index_to_name(last)
                           elsif last
                             last.to_s
                           else
                             first_name
                           end
               "#{first_name}:#{last_name}"
             end
  @print_titles = (@print_titles || {}).merge(cols: cols_str)
end

# File lib/xlsxrb/worksheet_builder.rb, line 621
def repeat_rows(first, last = nil)
  rows_str = if first.is_a?(String)
               first.include?(":") ? first : "#{first}:#{last || first}"
             else
               last ||= first
               "#{first + 1}:#{last + 1}"
             end
  @print_titles = (@print_titles || {}).merge(rows: rows_str)
end

# File lib/xlsxrb/worksheet_builder.rb, line 95
def row(values, styles: nil, height: nil, hidden: false, custom_height: false, collapsed: false, outline_level: nil)
  row_index = @rows.size
  DslHelpers.validate_row_bounds!(row_index, height, strict_excel_mode: @strict_excel_mode)

  values = DslHelpers.normalize_row_values(values)
  styles = DslHelpers.normalize_row_styles(styles)

  # 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
    sb = @styles[style_name]
    fmt_code = if sb&.num_fmt_id
                 sb.num_fmt_id.is_a?(String) ? sb.num_fmt_id : Ooxml::Utils::BUILTIN_NUM_FMT_CODES[sb.num_fmt_id]
               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,
                           raw_value: val.cached_value&.to_s,
                           format_code: fmt_code
                         )
                       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,
                           raw_value: val[:value]&.to_s,
                           format_code: fmt_code
                         )
                       else
                         Elements::Cell.new(
                           row_index: row_index,
                           column_index: col_index,
                           value: val,
                           style_index: style_name,
                           raw_value: val&.to_s,
                           format_code: fmt_code
                         )
                       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?,
    collapsed: collapsed,
    outline_level: outline_level
  )
end

# File lib/xlsxrb/worksheet_builder.rb, line 517
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 702
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 716
def sheet_properties(name, value)
  @sheet_properties[name] = value
end

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

# File lib/xlsxrb/worksheet_builder.rb, line 335
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 419
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 505
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 67
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 372
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 346
def validate_data(sqref, **opts)
  @data_validations << opts.merge(sqref: sqref)
end