-- ImageHub: link organ-segmentation masks to their parent image file (Phase D).
-- A mask file (file_kind='segmentation') points at the image it segments via a
-- self-referential parent_file_id (e.g. an organ mask of ct.nii.gz); organ_label
-- names the organ. Regular files stay file_kind='image'. Idempotent (ADD COLUMN IF
-- NOT EXISTS) so the startup runner can apply it to volumes that predate it.
-- Apply after 017_imagehub_datasets.sql (no semicolons inside comments — the runner
-- splitter is naive):
--   docker exec -i initiative-postgres psql -U initiative -d initiatives < be0/migrations/018_imagehub_segmentation_links.sql

ALTER TABLE imagehub_dataset_files
  ADD COLUMN IF NOT EXISTS file_kind TEXT NOT NULL DEFAULT 'image' CHECK (file_kind IN ('image','segmentation'));

ALTER TABLE imagehub_dataset_files
  ADD COLUMN IF NOT EXISTS parent_file_id UUID REFERENCES imagehub_dataset_files(id) ON DELETE CASCADE;

ALTER TABLE imagehub_dataset_files
  ADD COLUMN IF NOT EXISTS organ_label TEXT NOT NULL DEFAULT '';

-- List all masks of an image efficiently.
CREATE INDEX IF NOT EXISTS idx_imagehub_dataset_files_parent
  ON imagehub_dataset_files (parent_file_id);