var MINISTRY_GRUOPS = [1,2,3,4,5,6]
var source = SpreadsheetApp.getActiveSpreadsheet();

function create_new_month() {
  var sheet_name = "test"
  var new_sheet = source.getSheetByName(sheet_name)
  // Creates new month sheet
  if (! new_sheet) {
    new_sheet = create_new_sheet(sheet_name)
  }

  new_sheet.getDataRange().clearContent();
  new_sheet.clearFormats();

  // Get publishers groups
  var publishers = source.getSheetByName("LISTA GŁOSICIELI")
  var dataRange = publishers.getDataRange()
  var dataRangeRows = dataRange.getNumRows()
  var dataRangeValues = dataRange.getValues()

  var PUBLISHERS_GROUP = []

  var all_pioneers = []
  var all_aux_pioneers = []
  var all_disableds = []
  var row_start = 1;
  var row_end = 0;

  for (var nr = 0; nr < MINISTRY_GRUOPS.length; nr++ ) {
    var whole_group = get_group_by_number(dataRangeValues, MINISTRY_GRUOPS[nr])
    var group = whole_group['publishers']
    var publishers_flat = []

    group.forEach(function(current_value) {
      publishers_flat.push(current_value[0])
    });


    var group_data = group
    var group_size = group_data.length
    row_end += group_size

    var nrs = []
    for (var i = 1; i < group_size + 1; i++) {
      nrs.push([i])
    }

    // nagłowek
    var header =  [["", "GRUPA " + (nr + 1), "PUBLIKACJE", "FILMY", "GODZINY", "ODWIEDZINY", "STUDIA"]]
    var header_range = new_sheet.getRange("A" + row_start + ":G" + (row_start))
    var summary_range = new_sheet.getRange("A" + row_start + ":G" + (row_start))
    var avg_range = new_sheet.getRange("A" + row_start + ":G" + (row_start))
    header_range.setValues(header).setBackground("#000").setFontColor("#FFF").setFontWeight("bold").setHorizontalAlignment("center")
    // dane ze służby
    row_start += 1
    row_end += 1
    new_sheet.getRange("A" + row_start + ":A" + row_end).setValues(nrs)
    new_sheet.getRange("B" + row_start + ":B" + row_end).setValues(group_data)
    var drs = row_start
    var dre = row_end
    // suma
    row_start += 1
    row_end += 1
    var summary_range = new_sheet.getRange("B" + row_end + ":G" + row_end)
    summary_range.setValues([["SUMA", r2s(c2rng("C",drs, dre)), r2s(c2rng("D",drs, dre)), r2s(c2rng("E",drs, dre)), r2s(c2rng("F",drs, dre)), r2s(c2rng("G",drs, dre))]])
    summary_range.setFontWeight("bold")
    // średnia
    row_start += 1
    row_end += 1
    var avg_range = new_sheet.getRange("B" + row_end + ":G" + row_end)
    avg_range.setValues([["ŚREDNIA", r2a(c2rng("C",drs, dre)), r2a(c2rng("D",drs, dre)), r2a(c2rng("E",drs, dre)), r2a(c2rng("F",drs, dre)), r2a(c2rng("G",drs, dre))]])
    avg_range.setFontWeight("bold")
    // ustawienie pionierów, nieczynnych itp

    set_publishers_special_attr(new_sheet, publishers_flat, whole_group['pioneers'], "#AED6F1", drs)
    set_publishers_special_attr(new_sheet, publishers_flat, whole_group['aux_pioneers'], "#A3E4D7", drs)
    set_publishers_special_attr(new_sheet, publishers_flat, whole_group['disableds'], "#FADBD8", drs)
    all_pioneers = all_pioneers.concat(whole_group['pioneers'])
    all_aux_pioneers = all_aux_pioneers.concat(whole_group['aux_pioneers'])
    all_disableds = all_disableds.concat(whole_group['disableds'])

    row_start += group_size
  }

    // koncowe formatowanie
    new_sheet.setColumnWidth(1, 30)
    new_sheet.autoResizeColumn(2)
    new_sheet.getRange("C:G").setHorizontalAlignment("center")

    //statystyki służby
    Logger.log(all_pioneers)
    var all_group_values = new_sheet.getRange("A:G").getValues()
    var pioneers_stats = []
    var aux_pioneer_stats = []

    for (var i=0; i < all_group_values.length; i++ ){
        var row = all_group_values[i]
        if ( all_pioneers.indexOf(row[1]) > -1 ) {
            pioneers_stats.push("(B:B=\""+row[1]+"\")")
        }
    }

    for (var i=0; i < all_group_values.length; i++ ){
        var row = all_group_values[i]
        if ( all_aux_pioneers.indexOf(row[1]) > -1 ) {
            aux_pioneer_stats.push("(B:B=\""+row[1]+"\")")
        }
    }

    var stats_header = new_sheet.getRange("I1:O1")
    var stats_header_values = [["PIONIERZY", "LICZBA", "PUBLIKACJE", "FILMY", "GODZINY", "ODWIEDZINY", "STUDIA" ]]
    stats_header.setValues(stats_header_values).setBackground("#BB8FCE").setFontColor("#000").setFontWeight("bold").setHorizontalAlignment("center")
    var legend = new_sheet.getRange("I2:I3")
    var legend_values = [["STALI"], ["POMOCNCZY"]]
    legend.setValues(legend_values).setBackground("#FFF").setFontColor("#000").setFontWeight("bold").setHorizontalAlignment("center")
    new_sheet.autoResizeColumn(9)

    var stats_columns = ["C", "D", "E" , "F", "G"]
    var filters_pioneer = [all_pioneers.length]
    var filter_aux_pioneer = [all_aux_pioneers.length]
    for (var i=0; i < stats_columns.length; i++) {
       var columt_letter = stats_columns[i]
       var stmt = pioneers_stats.join("+")
       var filter = "=SUM(FILTER("+ columt_letter + ":" + columt_letter + ";" + stmt + "))"
       filters_pioneer.push(filter)
       var stmt = aux_pioneer_stats.join("+")
       var filter = "=SUM(FILTER("+ columt_letter + ":" + columt_letter + ";" + stmt + "))"
       filter_aux_pioneer.push(filter)
       Logger.log(filters_pioneer)
    }
    new_sheet.getRange("J2:O2").setValues([filters_pioneer]).setHorizontalAlignment("center")
    new_sheet.getRange("J3:O3").setValues([filter_aux_pioneer]).setHorizontalAlignment("center")

    var stats_header = new_sheet.getRange("I6:O6")
    var stats_header_values = [["GŁOSICIELE", "LICZBA", "PUBLIKACJE", "FILMY", "GODZINY", "ODWIEDZINY", "STUDIA" ]]
    stats_header.setValues(stats_header_values).setBackground("#AED6F1").setFontColor("#000").setFontWeight("bold").setHorizontalAlignment("center")
    var legend = new_sheet.getRange("I7:I7")
    var legend_values = [["RAZEM"]]
    legend.setValues(legend_values).setBackground("#FFF").setFontColor("#000").setFontWeight("bold").setHorizontalAlignment("center")


    var stats_header = new_sheet.getRange("I9:O9")
    var stats_header_values = [["", "LICZBA", "PUBLIKACJE", "FILMY", "GODZINY", "ODWIEDZINY", "STUDIA" ]]
    stats_header.setValues(stats_header_values).setBackground("#D0ECE7").setFontColor("#000").setFontWeight("bold").setHorizontalAlignment("center")
    var legend = new_sheet.getRange("I10:I10")
    var legend_values = [["ZBÓR"]]
    legend.setValues(legend_values).setBackground("#FFF").setFontColor("#000").setFontWeight("bold").setHorizontalAlignment("center")

}



function set_publishers_special_attr(sheet, publishers_flat_list, custom_publishers, color, position_start) {
    for (var i=0; i < custom_publishers.length; i++) {
      var pos = publishers_flat_list.indexOf(custom_publishers[i])
      if ( pos > -1 ) {
           sheet.getRange("B" + (position_start + pos) + ":B" + (position_start + pos)).setBackground(color)
        }
    }
}

function create_new_sheet(sheet_name) {
  return source.insertSheet(sheet_name)
}