-- ImageHub: persist the relative folder path of each uploaded file (Option B — real folders inside
-- a dataset). Until now logical_path was basename-flattened, so an uploaded directory structure
-- (e.g. the nnU-Net imagesTr/labelsTr layout) was lost once files reached MinIO. folder_path keeps
-- the relative directory so the dataset browser can render a real folder tree and the structure
-- round-trips. The working-file natural key moves from (dataset_id, logical_path) to
-- (dataset_id, folder_path, logical_path) so two files sharing a basename in different folders no
-- longer collide and silently merge. Existing rows default folder_path to the empty string, so the
-- new key stays unique wherever the old one was. Idempotent.
-- Apply after 025 (no semicolons inside comments or string literals — the runner splitter is naive):
--   docker exec -i initiative-postgres psql -U initiative -d initiatives < be0/migrations/026_imagehub_file_folder_path.sql

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

DROP INDEX IF EXISTS uq_imagehub_dataset_files_path;

CREATE UNIQUE INDEX IF NOT EXISTS uq_imagehub_dataset_files_folder_path
  ON imagehub_dataset_files (dataset_id, folder_path, logical_path);

CREATE INDEX IF NOT EXISTS idx_imagehub_dataset_files_folder
  ON imagehub_dataset_files (dataset_id, folder_path);