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 objnam AS bnnam, hm
          FROM waterway.dqr_bottleneck_stats
          WHERE cc = {{ cc }} ORDER BY hm, objnam;
      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 objnam, srcnt, fwacnt
            FROM waterway.dqr_bottleneck_stats
            WHERE cc = {{ cc }} AND month = {{ d }}
            ORDER BY hm, objnam;
        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) }}'