CREATE TABLE IF NOT EXISTS decision_number_counter (
  period_key VARCHAR(7) PRIMARY KEY,
  last_value INTEGER NOT NULL DEFAULT 0,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION next_decision_number(p_now TIMESTAMPTZ DEFAULT NOW())
RETURNS VARCHAR(50)
LANGUAGE plpgsql
AS $$
DECLARE
  v_period_key VARCHAR(7);
  v_next_value INTEGER;
BEGIN
  v_period_key := TO_CHAR(p_now, 'YYYY_MM');

  INSERT INTO decision_number_counter (period_key, last_value, updated_at)
  VALUES (v_period_key, 1, NOW())
  ON CONFLICT (period_key)
  DO UPDATE SET
    last_value = decision_number_counter.last_value + 1,
    updated_at = NOW()
  RETURNING decision_number_counter.last_value INTO v_next_value;

  RETURN 'BES-' || v_period_key || '_' || LPAD(v_next_value::TEXT, 4, '0');
END;
$$;

UPDATE decision
SET decision_number = next_decision_number(created_at)
WHERE decision_number IS NULL
   OR TRIM(decision_number) = '';

ALTER TABLE decision
  ALTER COLUMN decision_number SET DEFAULT next_decision_number();

ALTER TABLE decision
  ALTER COLUMN decision_number SET NOT NULL;