actions:
  # One sheet per CC
  - type: select
    statement: >
      SELECT cc FROM
        (VALUES ('AT'), ('SK'), ('HU'), ('HR'), ('RS'), ('BG'), ('RO') )
        AS t (cc);
    actions:
      - type: sheet
        source: CCTmpl
        destination: "{{ cc }}"
        vars: [last_row, column_number, offset]
        actions:
          # Header:
          - type: copy
            location: [A1, A5]
          # BN names
          - type: select
            statement: >
              SELECT DISTINCT bottleneck_id, concat(objnam, ' (', bottleneck_id, ')') AS bnnam, hm
                FROM waterway.dqr_bottleneck_stats
                WHERE cc = {{ cc }} ORDER BY hm, bottleneck_id;
            actions:
              - type: assign
                name: last_row
                expr: (row_number ?? 0) + 6
              - type: copy
                location: [A6]
                destination: A{{ last_row }}
          - type: copy
            location: [A7]
            destination: A{{ (last_row ?? 6) + 1 }}
          # Gen Months
          - type: select
            statement: >
              SELECT to_char(d, 'Month YYYY') AS month, d::date
                FROM generate_series( date_trunc('month',
                                                 now() - interval '1 day'
                                                )::date,
                                      '2019-10-01'::date,
                                      - '1 month'::interval ) d;
            actions:
              - type: assign
                name: column_number
                expr: column2num("B") + (row_number ?? 0) * 2
              - type: copy
                location: [B4, C5]
                destination: "{{ coord2cell(column_number, 4) }}"
              # BN SR-Count
              - type: select
                statement: >
                  SELECT concat(bottleneck_id, ' (', objnam, ')') AS objnam, srcnt, fwacnt
                    FROM waterway.dqr_bottleneck_stats
                    WHERE cc = {{ cc }} AND month = {{ d }}
                    ORDER BY hm, bottleneck_id;
                actions:
                  - type: assign
                    name: last_row
                    expr: (row_number ?? 0) + 6
                  - type: copy
                    location: [B6, C6]
                    destination: "{{ coord2cell(column_number, last_row) }}"
              - type: copy
                location: [B7, C7]
                destination: "{{ coord2cell(column_number, (last_row ?? 0) + 1) }}"
          - type: assign
            name: offset
            expr: (last_row ?? 0) + 3
          #--------------------------------------------------------------------------
          # GAUGES
          # Header:
          - type: copy
            location: [A9]
            destination: A{{ offset }}
          # Gauges names
          - type: select
            statement: >
              SELECT DISTINCT objname AS gnam, hm FROM waterway.dqr_gauge_stats
                WHERE cc = {{ cc }} ORDER BY hm, objname;
            actions:
              - type: assign
                name: last_row
                expr: (row_number ?? 0) + offset + 1
              - type: copy
                location: [A10]
                destination: A{{ last_row }}
          - type: copy
            location: [A11]
            destination: A{{ (last_row ?? 10) + 1 }}
          # Gen Months
          - type: select
            statement: >
              SELECT to_char(d, 'Month YYYY') AS month, d::date
                FROM generate_series( date_trunc('month',
                                                 now() - interval '1 day'
                                                )::date,
                                      '2019-10-01'::date,
                                      - '1 month'::interval ) d;
            actions:
              - type: assign
                name: column_number
                expr: column2num("B") + (row_number ?? 0)
              - type: copy
                location: [B9]
                destination: "{{ coord2cell(column_number, offset) }}"
              # BN SR-Count
              - type: select
                statement: >
                  SELECT objname, daynodata
                    FROM waterway.dqr_gauge_stats
                    WHERE cc = {{ cc }} AND month = {{ d }}
                    ORDER BY hm, objname;
                actions:
                  - type: assign
                    name: last_row
                    expr: (row_number ?? 0) + offset + 1
                  - type: copy
                    location: [B10]
                    destination: "{{ coord2cell(column_number, last_row) }}"
              - type: copy
                location: [B11]
                destination: "{{ coord2cell(column_number, (last_row ?? 10) + 1) }}"