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

ALTER TABLE runs
  RENAME COLUMN details TO detail;
ALTER TABLE runs
  ADD COLUMN volume INT CHECK (0 <= volume AND volume <= 100);
-- noinspection SqlWithoutWhere
UPDATE runs
SET volume = 100;
ALTER TABLE runs
  ALTER COLUMN volume SET NOT NULL;

ALTER TABLE hfest_items
  ADD COLUMN is_hidden BOOLEAN;
-- noinspection SqlWithoutWhere
UPDATE hfest_items
SET is_hidden = false;
ALTER TABLE hfest_items
  ALTER COLUMN is_hidden SET NOT NULL;

-- The closure table containing all the ancestor/descendant links for the directories and files
CREATE TABLE public.run_results
(
  run_id     UUID PRIMARY KEY         NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL,
  is_victory BOOLEAN                  NOT NULL,
  raw        JSON                     NOT NULL,
  CONSTRAINT run_run_result___fk FOREIGN KEY (run_id) REFERENCES runs (run_id) ON DELETE CASCADE ON UPDATE CASCADE
);
