COMMENT ON SCHEMA public IS '{"version": "V004"}';

-- noinspection SqlWithoutWhere
DELETE
FROM run_results;

-- noinspection SqlWithoutWhere
DELETE
FROM runs;

CREATE FUNCTION test_run_result_scores(
  IN scores JSON
)
  RETURNS BOOLEAN
  LANGUAGE SQL
  IMMUTABLE
AS
$$
SELECT
    json_typeof($1) = 'array'
    AND 1 <= json_array_length($1)
    AND json_array_length($1) <= 2
    AND false NOT IN (
    SELECT
      (json_typeof(score.value) = 'number' AND 0 <= score.value::text::int) AS is_ok
    FROM json_array_elements($1) as score
  );
$$;

CREATE FUNCTION test_run_result_items(
  IN items JSON
)
  RETURNS BOOLEAN
  LANGUAGE SQL
  IMMUTABLE
AS
$$
SELECT
    json_typeof($1) = 'object'
    AND false NOT IN (
    SELECT
      (
          json_typeof(item_count.value) = 'number'
          AND 0 <= item_count.value::text::int
        ) AS is_ok
    FROM json_each($1) as item_count
  );
$$;

ALTER TABLE run_results
  ADD COLUMN max_level INT CHECK (max_level >= 0),
  ADD COLUMN scores JSON CHECK (test_run_result_scores(scores)),
  ADD COLUMN items JSON CHECK (test_run_result_items(items)),
  DROP COLUMN raw;