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

CREATE TABLE public.blob_deletion_queue
(
  blob_id    UUID PRIMARY KEY         NOT NULL,
  media_type VARCHAR(100)             NOT NULL,
  byte_size  INT4                     NOT NULL,
  buffer_id  VARCHAR(64)              NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL,
  deleted_at TIMESTAMP WITH TIME ZONE NOT NULL,
  CHECK (updated_at >= created_at),
  CHECK (deleted_at >= updated_at)
);

ALTER TABLE files
  DROP CONSTRAINT file_blob___fk;
ALTER TABLE files
  ADD CONSTRAINT file_blob___fk FOREIGN KEY (blob_id) REFERENCES blobs (blob_id) ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE game_resources
  DROP CONSTRAINT game_resource_file__fk;
ALTER TABLE game_resources
  ADD CONSTRAINT game_resource_file__fk FOREIGN KEY (file_id) REFERENCES files (drive_item_id) ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE games
  ADD CONSTRAINT game_icon_file__fk FOREIGN KEY (icon_file_id) REFERENCES files (drive_item_id) ON DELETE RESTRICT ON UPDATE CASCADE;