Test debt covenants every quarter and draft the compliance certificate

id: debt-covenant-compliance-certificate
namespace: company.team

description: |
  Quarterly debt covenant test: build covenant EBITDA, net debt, interest and liquidity from
  the trial balance as the facility agreement defines them (frozen GAAP, capped add-backs),
  test every covenant against its step-down schedule, show the headroom, project the next two
  quarters on budget, size an equity cure when a covenant breaks, and draft the compliance
  certificate for the CFO to sign.

inputs:
  - id: test_date
    type: STRING
    displayName: Test date (quarter end)
    defaults: "2026-06-30"
    validator: ^\d{4}-(03-31|06-30|09-30|12-31)$

  - id: scenario
    type: SELECT
    displayName: Demo scenario
    description: >
      CLEAN: the business performs to plan. STRESS: revenue falls 32% from
      September 2026 and the leverage covenant breaks at the December step-down.
      ISSUES: a month missing from the trial balance and a month that does not
      balance, which must stop the test.
    values:
      - CLEAN
      - STRESS
      - ISSUES
    defaults: CLEAN

  - id: frozen_gaap
    type: BOOL
    displayName: Frozen GAAP (pre-IFRS 16)
    description: Most facility agreements signed before 2019 freeze the accounting.
      Lease payments are then deducted from EBITDA and lease liabilities
      excluded from net debt.
    defaults: true

  - id: addback_cap_pct
    type: FLOAT
    displayName: Add-back cap (% of EBITDA)
    description: Non-recurring items added back to EBITDA are capped at this share
      of EBITDA before add-backs.
    defaults: 10.0

  - id: equity_cure_amount
    type: FLOAT
    displayName: Equity cure amount
    description: Cash injected by the shareholders to cure a breach. Applied to net
      debt (debt cure). At most 2 cures over the life of the facility, never in
      consecutive quarters.
    defaults: 0.0

  - id: approved_by
    type: STRING
    displayName: Signed by
    description: The CFO or director who signs the compliance certificate.
    defaults: ""

variables:
  state_key: covenant_tests
  pack_dir: "covenants/{{ inputs.test_date }}"

concurrency:
  limit: 1

triggers:
  - id: quarter_end_plus_30
    type: io.kestra.plugin.core.trigger.Schedule
    description: Thirty days after each quarter end, when the certificate is usually
      due. Disabled until the trial balance is wired in.
    cron: "0 9 30 1,4,7,10 *"
    disabled: true

tasks:
  - id: state
    type: io.kestra.plugin.core.output.OutputValues
    values:
      s: "{{ kv(vars.state_key, errorOnMissing=false) ?? {'last_test': '', 'cures':
        [], 'tests': {}} | toJson }}"
      last_test: "{{ (kv(vars.state_key, errorOnMissing=false) ?? {'last_test': ''}) |
        jq('.last_test') | first }}"
      previous_quarter: "{{ inputs.test_date | date('yyyy-MM-dd') | dateAdd(1, 'DAYS')
        | dateAdd(-3, 'MONTHS') | dateAdd(-1, 'DAYS') | date('yyyy-MM-dd') }}"

  - id: order_gate
    type: io.kestra.plugin.core.flow.If
    description: Quarters are certified once and in order.
    condition: >-
      {{ outputs.state.values.last_test != ''
         and (outputs.state.values.last_test >= inputs.test_date or outputs.state.values.last_test != outputs.state.values.previous_quarter) }}
    then:
      - id: out_of_order
        type: io.kestra.plugin.core.execution.Fail
        errorMessage: >-
          {{ outputs.state.values.last_test >= inputs.test_date
             ? 'The ' ~ inputs.test_date ~ ' test is already certified (last test: ' ~ outputs.state.values.last_test ~ ').'
             : 'The ' ~ outputs.state.values.previous_quarter ~ ' test must be certified before ' ~ inputs.test_date ~ ' (last test: ' ~ outputs.state.values.last_test ~ ').' }}

  - id: test
    type: io.kestra.plugin.jdbc.duckdb.Queries
    description: >
      One DuckDB session: the monthly trial balance (demo generator or your
      export), data checks, covenant definitions, LTM figures at the test date
      and the next two quarter ends, tests against the step-down schedule,
      headroom, cure sizing and the certificate.
    outputFiles:
      - definitions
      - tests
      - forecast
      - blockers
    fetchType: FETCH_ONE
    sql: |
      SET VARIABLE test_date = DATE '{{ inputs.test_date }}';
      SET VARIABLE frozen = {{ inputs.frozen_gaap }};

      -- =======================================================================
      -- Facility terms. Replace with your agreement: covenant, direction, the
      -- level and the date it applies from (step-downs).
      -- =======================================================================
      CREATE TABLE covenant_levels (covenant VARCHAR, direction VARCHAR, level DOUBLE, applies_from DATE, clause VARCHAR);
      INSERT INTO covenant_levels VALUES
        ('Net leverage', 'MAX', 3.50, DATE '2024-01-01', '22.2(a) Net Debt to EBITDA'),
        ('Net leverage', 'MAX', 3.25, DATE '2026-06-30', '22.2(a) Net Debt to EBITDA'),
        ('Net leverage', 'MAX', 3.00, DATE '2026-12-31', '22.2(a) Net Debt to EBITDA'),
        ('Interest cover', 'MIN', 4.00, DATE '2024-01-01', '22.2(b) EBITDA to Net Finance Charges'),
        ('Liquidity', 'MIN', 10000000, DATE '2024-01-01', '22.2(c) Cash and undrawn RCF'),
        ('Capex', 'MAX', 8500000, DATE '2024-01-01', '22.2(d) Capital expenditure, financial year to date annualised');
      SET VARIABLE rcf_commitment = 15000000;

      -- =======================================================================
      -- Monthly trial balance, IFRS 16 books. P&L accounts carry the month's
      -- movement, balance sheet accounts the month-end balance. Replace with
      -- your consolidated trial balance by month.
      -- =======================================================================
      CREATE TABLE months AS
      SELECT m::DATE AS month, row_number() OVER (ORDER BY m) - 1 AS idx,
             {% if inputs.scenario == 'STRESS' %}CASE WHEN m >= DATE '2026-09-01' THEN 0.68 ELSE 1 END{% else %}1{% endif %} AS shock,
             9000000 * (1 + 0.006 * (row_number() OVER (ORDER BY m) - 1)) * (1 + 0.08 * sin(2 * pi() * (month(m) - 1) / 12)) AS planned_revenue
      FROM generate_series(DATE '2024-10-01', DATE '2027-12-01', INTERVAL 1 MONTH) g(m);

      CREATE TABLE pl AS
      SELECT month, idx, shock,
             round(planned_revenue * shock, 2) AS revenue, planned_revenue,
             round(14000 * (1 + (hash(month) % 7)::INT / 100.0), 2) AS interest_income
      FROM months;

      CREATE TABLE tb AS
      WITH f AS (
        SELECT p.*, 48000000 - 1000000 * (p.idx // 3) AS term_loan,
               CASE WHEN p.month >= DATE '2026-10-01' AND p.shock < 1 THEN 9000000 ELSE 6000000 END AS rcf_drawn,
               18000000 - 290000 * p.idx AS lease_liability,
               round(7200000 + 900000 * sin(p.idx) - CASE WHEN p.shock < 1 THEN 1200000 * least(p.idx - 23, 3) ELSE 0 END, 2) AS cash
        FROM pl p
      )
      SELECT month, account, name, kind, amount FROM (
        SELECT month, '4000' AS account, 'Revenue' AS name, 'PL' AS kind, -revenue AS amount FROM f
        UNION ALL SELECT month, '5000', 'Cost of sales', 'PL', round(revenue * 0.58, 2) FROM f
        -- Operating costs follow the plan, not the actual: they do not flex in a downturn.
        UNION ALL SELECT month, '6000', 'Operating expenses', 'PL', round(planned_revenue * 0.18 + 350000, 2) FROM f
        UNION ALL SELECT month, '6990', 'Restructuring and other non-recurring', 'PL',
               CASE month WHEN DATE '2025-11-01' THEN 1800000 WHEN DATE '2026-05-01' THEN 900000 WHEN DATE '2026-11-01' THEN 650000 ELSE 0 END FROM f
        UNION ALL SELECT month, '6800', 'Depreciation and amortisation', 'PL', 550000 FROM f
        UNION ALL SELECT month, '6810', 'Depreciation of right-of-use assets', 'PL', 360000 FROM f
        UNION ALL SELECT month, '7100', 'Interest on borrowings', 'PL', round(term_loan * 0.065 / 12 + rcf_drawn * 0.058 / 12, 2) FROM f
        UNION ALL SELECT month, '7110', 'Interest on lease liabilities', 'PL', round(lease_liability * 0.047 / 12, 2) FROM f
        UNION ALL SELECT month, '7200', 'Interest income', 'PL', -interest_income FROM f
        -- Memo: lease payments, the rent a frozen-GAAP definition deducts.
        UNION ALL SELECT month, 'M100', 'Memo: lease payments', 'MEMO', 420000 FROM f
        UNION ALL SELECT month, 'M200', 'Memo: capital expenditure', 'MEMO', round(560000 + 120000 * cos(idx), 2) FROM f
        UNION ALL SELECT month, '1000', 'Cash', 'BS', cash FROM f
        UNION ALL SELECT month, '2500', 'Term loan', 'BS', -term_loan FROM f
        UNION ALL SELECT month, '2510', 'Revolving credit facility', 'BS', -rcf_drawn FROM f
        UNION ALL SELECT month, '2600', 'Lease liabilities', 'BS', -lease_liability FROM f
        UNION ALL SELECT month, '1500', 'Other net operating assets', 'BS', 52000000 + 150000 * idx FROM f
      );
      -- As in a real trial balance, P&L accounts carry the year-to-date amount
      -- (calendar year), so every month balances on its own with equity.
      CREATE TABLE mv AS SELECT * FROM tb;
      DELETE FROM tb WHERE kind = 'PL';
      INSERT INTO tb
      SELECT m1.month, m1.account, m1.name, 'PL',
             (SELECT sum(m2.amount) FROM mv m2 WHERE m2.account = m1.account AND year(m2.month) = year(m1.month) AND m2.month <= m1.month)
      FROM mv m1 WHERE m1.kind = 'PL';
      INSERT INTO tb
      SELECT month, '3000', 'Equity and retained earnings', 'BS', -sum(amount) FROM tb WHERE kind IN ('BS', 'PL') GROUP BY month;
      {% if inputs.scenario == 'ISSUES' %}
      DELETE FROM tb WHERE month = date_trunc('month', getvariable('test_date') - INTERVAL 4 MONTH);
      UPDATE tb SET amount = amount + 125000 WHERE month = date_trunc('month', getvariable('test_date') - INTERVAL 1 MONTH) AND account = '6000';
      {% endif %}

      -- =======================================================================
      -- Data checks.
      -- =======================================================================
      CREATE TABLE blockers AS
      SELECT 'MISSING_MONTH' AS code, strftime(m.month, '%Y-%m') AS ref, 'No trial balance for this month in the test period' AS detail
      FROM (SELECT (date_trunc('month', getvariable('test_date')) - INTERVAL (i) MONTH)::DATE AS month FROM range(0, 12) t(i)) m
      WHERE m.month NOT IN (SELECT DISTINCT month FROM tb)
      UNION ALL
      SELECT 'TB_DOES_NOT_BALANCE', strftime(month, '%Y-%m'), 'Debits and credits differ by ' || round(sum(amount), 2)
      FROM tb WHERE kind IN ('BS', 'PL') AND month <= getvariable('test_date') AND month > getvariable('test_date') - INTERVAL 12 MONTH
      GROUP BY month HAVING round(sum(amount), 2) <> 0;

      -- =======================================================================
      -- Covenant figures at a quarter end, last twelve months, as the agreement
      -- defines them. Months after the test date come from the forecast (in the
      -- demo, the same generator).
      -- =======================================================================
      -- Last twelve months from year-to-date balances: YTD now + previous December - YTD a year ago.
      CREATE MACRO ytd(acc, d) AS (SELECT coalesce(sum(amount), 0) FROM tb WHERE account = acc AND month = date_trunc('month', d));
      CREATE MACRO a(acc, d) AS
        CASE WHEN acc LIKE 'M%' THEN (SELECT coalesce(sum(amount), 0) FROM tb WHERE account = acc AND month <= d AND month > d - INTERVAL 12 MONTH)
             WHEN month(d) = 12 THEN ytd(acc, d)
             ELSE ytd(acc, d) + ytd(acc, make_date(year(d) - 1, 12, 1)) - ytd(acc, d - INTERVAL 12 MONTH) END;
      CREATE MACRO bs(acc, d) AS (SELECT coalesce(sum(amount), 0) FROM tb WHERE account = acc AND month = date_trunc('month', d));

      CREATE TABLE figures AS
      WITH d AS (
        SELECT getvariable('test_date') AS test_date, 'ACTUAL' AS basis
        UNION ALL SELECT (date_trunc('month', getvariable('test_date')) + INTERVAL 4 MONTH - INTERVAL 1 DAY)::DATE, 'FORECAST'
        UNION ALL SELECT (date_trunc('month', getvariable('test_date')) + INTERVAL 7 MONTH - INTERVAL 1 DAY)::DATE, 'FORECAST'
      ), raw AS (
        SELECT d.test_date, d.basis,
               -a('4000', d.test_date) AS revenue,
               -(a('4000', d.test_date) + a('5000', d.test_date) + a('6000', d.test_date) + a('6990', d.test_date)) AS reported_ebitda,
               a('6990', d.test_date) AS non_recurring,
               a('M100', d.test_date) AS lease_payments,
               a('7100', d.test_date) + a('7200', d.test_date) AS net_finance_charges_borrowings,
               a('7110', d.test_date) AS lease_interest,
               -bs('2500', d.test_date) - bs('2510', d.test_date) AS borrowings,
               -bs('2600', d.test_date) AS lease_liabilities,
               bs('1000', d.test_date) AS cash,
               -bs('2510', d.test_date) AS rcf_drawn,
               (SELECT sum(amount) FROM tb WHERE account = 'M200' AND month <= d.test_date AND year(month) = year(d.test_date)) * 12.0 / month(d.test_date) AS capex_annualised
        FROM d
      )
      SELECT *,
             reported_ebitda + least(non_recurring, reported_ebitda * {{ inputs.addback_cap_pct }} / 100) AS ebitda_ifrs16,
             greatest(non_recurring - reported_ebitda * {{ inputs.addback_cap_pct }} / 100, 0) AS addback_disallowed,
             reported_ebitda + least(non_recurring, reported_ebitda * {{ inputs.addback_cap_pct }} / 100)
               - CASE WHEN getvariable('frozen') THEN lease_payments ELSE 0 END AS covenant_ebitda,
             borrowings + CASE WHEN getvariable('frozen') THEN 0 ELSE lease_liabilities END - cash AS net_debt,
             net_finance_charges_borrowings + CASE WHEN getvariable('frozen') THEN 0 ELSE lease_interest END AS net_finance_charges,
             cash + getvariable('rcf_commitment') - rcf_drawn AS liquidity
      FROM raw;

      -- =======================================================================
      -- Tests against the level in force at each date, with headroom and cure.
      -- Headroom for ratios: how far EBITDA can fall before the covenant breaks.
      -- =======================================================================
      CREATE TABLE tests AS
      WITH lv AS (
        SELECT f.test_date, f.basis, c.covenant, c.direction, c.clause,
               arg_max(c.level, c.applies_from) AS level
        FROM figures f JOIN covenant_levels c ON c.applies_from <= f.test_date
        GROUP BY ALL
      )
      SELECT lv.*,
             CASE lv.covenant
               WHEN 'Net leverage' THEN (f.net_debt - CASE WHEN lv.basis = 'ACTUAL' THEN {{ inputs.equity_cure_amount }} ELSE 0 END) / f.covenant_ebitda
               WHEN 'Interest cover' THEN f.covenant_ebitda / f.net_finance_charges
               WHEN 'Liquidity' THEN f.liquidity + CASE WHEN lv.basis = 'ACTUAL' THEN {{ inputs.equity_cure_amount }} ELSE 0 END
               WHEN 'Capex' THEN f.capex_annualised END AS actual,
             f.covenant_ebitda, f.net_debt, f.net_finance_charges, f.liquidity, f.capex_annualised
      FROM lv JOIN figures f USING (test_date, basis);
      ALTER TABLE tests ADD COLUMN pass BOOLEAN; ALTER TABLE tests ADD COLUMN headroom_pct DOUBLE; ALTER TABLE tests ADD COLUMN cure_needed DOUBLE;
      UPDATE tests SET
        pass = CASE direction WHEN 'MAX' THEN actual <= level ELSE actual >= level END,
        headroom_pct = round(100 * CASE covenant
          WHEN 'Net leverage' THEN 1 - actual / level
          WHEN 'Interest cover' THEN 1 - level / actual
          WHEN 'Liquidity' THEN actual / level - 1
          WHEN 'Capex' THEN 1 - actual / level END, 1),
        cure_needed = CASE WHEN covenant = 'Net leverage' AND actual > level THEN ceil(actual * covenant_ebitda - level * covenant_ebitda)
                           WHEN covenant = 'Liquidity' AND actual < level THEN ceil(level - actual) ELSE 0 END;

      COPY (SELECT test_date, basis, revenue, reported_ebitda, non_recurring, addback_disallowed, lease_payments, covenant_ebitda,
                   borrowings, lease_liabilities, cash, net_debt, net_finance_charges, liquidity, capex_annualised FROM figures ORDER BY test_date)
        TO '{{ outputFiles.definitions }}' (HEADER, DELIMITER ',');
      COPY (SELECT test_date, basis, covenant, clause, direction, level, round(actual, 4) AS actual, pass, headroom_pct, cure_needed FROM tests WHERE basis = 'ACTUAL' ORDER BY covenant)
        TO '{{ outputFiles.tests }}' (HEADER, DELIMITER ',');
      COPY (SELECT test_date, covenant, level, round(actual, 4) AS projected, pass, headroom_pct FROM tests WHERE basis = 'FORECAST' ORDER BY test_date, covenant)
        TO '{{ outputFiles.forecast }}' (HEADER, DELIMITER ',');
      COPY blockers TO '{{ outputFiles.blockers }}' (HEADER, DELIMITER ',');

      SELECT
        (SELECT format('{:,.0f}', covenant_ebitda) FROM figures WHERE basis = 'ACTUAL') AS covenant_ebitda,
        (SELECT format('{:,.0f}', reported_ebitda) FROM figures WHERE basis = 'ACTUAL') AS reported_ebitda,
        (SELECT format('{:,.0f}', addback_disallowed) FROM figures WHERE basis = 'ACTUAL') AS addback_disallowed,
        (SELECT format('{:,.0f}', net_debt) FROM figures WHERE basis = 'ACTUAL') AS net_debt,
        (SELECT string_agg(covenant || ' ' || CASE WHEN covenant IN ('Liquidity', 'Capex') THEN format('{:,.0f}', actual) ELSE round(actual, 2)::VARCHAR END || CASE direction WHEN 'MAX' THEN ' <= ' ELSE ' >= ' END
                           || CASE WHEN covenant IN ('Liquidity', 'Capex') THEN format('{:,.0f}', level) ELSE level::VARCHAR END || ' ' || CASE WHEN pass THEN 'PASS' ELSE 'FAIL' END || ' (headroom ' || headroom_pct || '%)', '; ' ORDER BY covenant)
           FROM tests WHERE basis = 'ACTUAL') AS results,
        (SELECT count(*) FROM tests WHERE basis = 'ACTUAL' AND NOT pass) AS breaches,
        (SELECT coalesce(max(cure_needed), 0)::BIGINT FROM tests WHERE basis = 'ACTUAL') AS cure_needed,
        -- A debt cure reduces net debt and adds cash: it can fix leverage and liquidity, not interest cover or capex.
        (SELECT count(*) = 0 FROM tests WHERE basis = 'ACTUAL' AND NOT pass AND covenant NOT IN ('Net leverage', 'Liquidity')) AS curable,
        (SELECT coalesce(string_agg(covenant, ', '), '') FROM tests WHERE basis = 'ACTUAL' AND NOT pass AND covenant NOT IN ('Net leverage', 'Liquidity')) AS not_curable,
        (SELECT min(headroom_pct) FROM tests WHERE basis = 'ACTUAL') AS min_headroom,
        (SELECT coalesce(string_agg(strftime(test_date, '%Y-%m-%d') || ' ' || covenant || ' ' || CASE WHEN covenant IN ('Liquidity', 'Capex') THEN format('{:,.0f}', actual) ELSE round(actual, 2)::VARCHAR END
                || ' against ' || CASE WHEN covenant IN ('Liquidity', 'Capex') THEN format('{:,.0f}', level) ELSE level::VARCHAR END || ' (headroom ' || headroom_pct || '%)', '; ' ORDER BY test_date), '')
           FROM tests WHERE basis = 'FORECAST' AND (NOT pass OR headroom_pct < 10)) AS forecast_warnings,
        (SELECT count(*) FROM tests WHERE basis = 'FORECAST' AND NOT pass) AS projected_breaches,
        (SELECT count(*) FROM blockers) AS blockers,
        (SELECT coalesce(string_agg(code || ' ' || ref || ': ' || detail, '; ' ORDER BY code, ref), '') FROM blockers) AS blocker_detail,
        -- The certificate, filled from the figures above.
        '# Compliance Certificate' || chr(10) || chr(10) ||
        'Facility agreement, clause 22.' || chr(10) || 'Test date: {{ inputs.test_date }}. Relevant period: the 12 months ending on the test date.' || chr(10) ||
        'Basis: ' || CASE WHEN getvariable('frozen') THEN 'frozen GAAP (lease payments deducted from EBITDA, lease liabilities excluded from Net Debt).' ELSE 'IFRS 16 as reported.' END || chr(10) || chr(10) ||
        'We confirm that, in respect of the relevant period:' || chr(10) || chr(10) ||
        (SELECT string_agg('- ' || clause || ': ' ||
                CASE WHEN covenant IN ('Liquidity', 'Capex') THEN 'EUR ' || format('{:,.0f}', actual) ELSE round(actual, 2) || 'x' END ||
                ' (' || CASE direction WHEN 'MAX' THEN 'maximum ' ELSE 'minimum ' END ||
                CASE WHEN covenant IN ('Liquidity', 'Capex') THEN 'EUR ' || format('{:,.0f}', level) ELSE level || 'x' END || '): ' ||
                CASE WHEN pass THEN 'complied with' ELSE 'NOT complied with' END, chr(10) ORDER BY clause) FROM tests WHERE basis = 'ACTUAL') || chr(10) || chr(10) ||
        'EBITDA EUR ' || (SELECT format('{:,.0f}', covenant_ebitda) FROM figures WHERE basis = 'ACTUAL') ||
        ', of which non-recurring items added back EUR ' || (SELECT format('{:,.0f}', least(non_recurring, reported_ebitda * {{ inputs.addback_cap_pct }} / 100)) FROM figures WHERE basis = 'ACTUAL') ||
        ' (capped at {{ inputs.addback_cap_pct }}% of EBITDA, EUR ' || (SELECT format('{:,.0f}', addback_disallowed) FROM figures WHERE basis = 'ACTUAL') || ' disallowed).' || chr(10) ||
        'Net Debt EUR ' || (SELECT format('{:,.0f}', net_debt) FROM figures WHERE basis = 'ACTUAL') ||
        CASE WHEN {{ inputs.equity_cure_amount }} > 0 THEN ', before an equity cure of EUR ' || format('{:,.0f}', {{ inputs.equity_cure_amount }}) || ' applied under clause 22.4' ELSE '' END || '.' || chr(10) || chr(10) ||
        'Signed: {{ inputs.approved_by == "" ? "(unsigned draft)" : inputs.approved_by }}' || chr(10) AS certificate;

  - id: result
    type: io.kestra.plugin.core.output.OutputValues
    values:
      r: "{{ (outputs.test.outputs | last).row | toJson }}"
      cures_used: "{{ fromJson(outputs.state.values.s).cures | length }}"
      cured_last_quarter: "{{ fromJson(outputs.state.values.s).cures contains
        outputs.state.values.previous_quarter }}"

  - id: log_test
    type: io.kestra.plugin.core.log.Log
    message: |
      Covenant test {{ inputs.test_date }} ({{ inputs.scenario }}, {{ inputs.frozen_gaap ? 'frozen GAAP' : 'IFRS 16' }}).
      {{ outputs.result.values.r | jq('"Covenant EBITDA \(.covenant_ebitda) (reported \(.reported_ebitda), add-back disallowed \(.addback_disallowed)). Net debt \(.net_debt)."') | first }}
      {{ outputs.result.values.r | jq('"\(.results)."') | first }}
      {{ outputs.result.values.r | jq('"Breaches: \(.breaches), cure needed \(.cure_needed). Forecast warnings: \(.forecast_warnings)"') | first }}
      Cures used so far: {{ outputs.result.values.cures_used }} of 2.
      {{ outputs.result.values.r | jq('"Blockers: \(.blockers). \(.blocker_detail)"') | first }}

  - id: write_certificate
    type: io.kestra.plugin.core.storage.Write
    extension: .md
    content: "{{ outputs.result.values.r | jq('.certificate') | first }}"

  - id: evidence
    type: io.kestra.plugin.core.namespace.UploadFiles
    namespace: "{{ flow.namespace }}"
    filesMap:
      "{{ render(vars.pack_dir) }}/definitions.csv": "{{ outputs.test.outputFiles.definitions }}"
      "{{ render(vars.pack_dir) }}/tests.csv": "{{ outputs.test.outputFiles.tests }}"
      "{{ render(vars.pack_dir) }}/forecast.csv": "{{ outputs.test.outputFiles.forecast }}"
      "{{ render(vars.pack_dir) }}/blockers.csv": "{{ outputs.test.outputFiles.blockers }}"
      "{{ render(vars.pack_dir) }}/compliance-certificate.md": "{{ outputs.write_certificate.uri }}"

  - id: decide
    type: io.kestra.plugin.core.flow.Switch
    value: >-
      {%- set r = outputs.result.values.r -%} {%- set cure =
      inputs.equity_cure_amount > 0 -%} {%- if (r | jq('.blockers') | first) > 0
      -%}DATA_ERROR {%- elseif cure and ((outputs.result.values.cures_used |
      number) >= 2 or outputs.result.values.cured_last_quarter == 'true')
      -%}CURE_NOT_AVAILABLE {%- elseif (r | jq('.breaches') | first) > 0
      -%}BREACH {%- elseif inputs.approved_by == '' -%}NEEDS_SIGNATURE {%- else
      -%}CERTIFY{%- endif -%}
    cases:
      CERTIFY:
        - id: record
          type: io.kestra.plugin.core.kv.Set
          key: "{{ vars.state_key }}"
          kvType: JSON
          value: >-
            {{ {'old': fromJson(outputs.state.values.s), 'r':
            fromJson(outputs.result.values.r), 'd': inputs.test_date, 'cure':
            inputs.equity_cure_amount, 'by': inputs.approved_by}
               | jq('{last_test: .d,
                      cures: (.old.cures + (if .cure > 0 then [.d] else [] end)),
                      tests: (.old.tests + {(.d): {results: .r.results, min_headroom: .r.min_headroom, equity_cure: .cure, signed_by: .by}})}') | first | toJson }}

        - id: log_certified
          type: io.kestra.plugin.core.log.Log
          message: >-
            Covenants for {{ inputs.test_date }} certified by {{
            inputs.approved_by }}{{ inputs.equity_cure_amount > 0 ? ', with an
            equity cure of ' ~ inputs.equity_cure_amount : '' }}. Send {{
            render(vars.pack_dir) }}/compliance-certificate.md to the agent. {{
            outputs.result.values.r | jq('.forecast_warnings') | first == '' ?
            '' : 'Watch: ' ~ (outputs.result.values.r | jq('.forecast_warnings')
            | first) }}

      NEEDS_SIGNATURE:
        - id: signature_required
          type: io.kestra.plugin.core.execution.Fail
          errorMessage: >-
            All covenants are met (lowest headroom {{ outputs.result.values.r |
            jq('.min_headroom') | first }}%). The draft is in {{
            render(vars.pack_dir) }}/compliance-certificate.md. Run again with
            approved_by to sign it. {{ outputs.result.values.r |
            jq('.forecast_warnings') | first == '' ? '' : 'Forecast: ' ~
            (outputs.result.values.r | jq('.forecast_warnings') | first) }}

      BREACH:
        - id: breach
          type: io.kestra.plugin.core.execution.Fail
          errorMessage: >-
            Covenant breach at {{ inputs.test_date }}: {{
            outputs.result.values.r | jq('.results') | first }}. {{
            (outputs.result.values.r | jq('.curable') | first)
               ? 'An equity cure of at least ' ~ (outputs.result.values.r | jq('.cure_needed') | first) ~ ' would remedy it'
               : 'A debt cure cannot remedy ' ~ (outputs.result.values.r | jq('.not_curable') | first) ~ ': ask the lenders for a waiver or an amendment' }}
            ({{ outputs.result.values.cures_used }} of 2 cures used{{
            outputs.result.values.cured_last_quarter == 'true' ? ', and the last
            quarter was cured, so no cure is available now' : '' }}). Notify the
            agent within the cure period.

      CURE_NOT_AVAILABLE:
        - id: cure_not_available
          type: io.kestra.plugin.core.execution.Fail
          errorMessage: >-
            An equity cure cannot be applied: {{
            outputs.result.values.cures_used }} of 2 cures used{{
            outputs.result.values.cured_last_quarter == 'true' ? ', and the
            previous quarter was already cured' : '' }}.

    defaults:
      - id: data_error
        type: io.kestra.plugin.core.execution.Fail
        errorMessage: "The trial balance cannot support a covenant test: {{
          outputs.result.values.r | jq('.blocker_detail') | first }}"