BEGIN;

create temporary table samples_parent_hashes (id integer not null, hash text not null);
-- output of server/src/utils/makeAllPossibleCacheKeys.ts
\copy samples_parent_hashes from 'giant.csv' with csv;
create index hash_idx on samples_parent_hashes (hash);
analyze samples_parent_hashes;

WITH demucs_parent_uuids AS (
  SELECT id, SPLIT_PART(cache_key, '[', 1) parent_uuid, SPLIT_PART(SPLIT_PART(cache_key, '[', 2), ']', 1) part
  FROM samples
  WHERE cache_key LIKE '%[%]'
), derivation_inserts AS (
  INSERT INTO sample_derivations (derivation_type, sample_id, derived_from_id)
    SELECT 'demucs', p.id, parent.id
    FROM demucs_parent_uuids p
    JOIN samples parent ON parent.uuid = parent_uuid
  ON CONFLICT DO NOTHING
) INSERT INTO sample_tags (sample_id, category, value)
  SELECT id, 'Stem', INITCAP(part)
  FROM demucs_parent_uuids
  ON CONFLICT DO NOTHING;

INSERT INTO sample_derivations (derivation_type, sample_id, derived_from_id)
  SELECT 'voiceConversion', s0.id, ph.id
  FROM samples s0
  JOIN samples_parent_hashes ph ON s0.cache_key IS NOT NULL AND s0.cache_key = ph.hash
  ON CONFLICT DO NOTHING;

INSERT INTO sample_tags (sample_id, category, value)
  SELECT id, 'Type', 'Voice Conversion Output'
  FROM samples WHERE cache_key IS NOT NULL AND OCTET_LENGTH(cache_key) = 64 AND NOT cache_key LIKE '%[%]'
  ON CONFLICT DO NOTHING;

INSERT INTO sample_voice_conversion_models (sample_id, voice_conversion_model_id)
  SELECT s.id, vcm.id
  FROM samples s JOIN voice_conversion_models vcm ON (regexp_match(s.name, ' as ((?!.* as ).*)$'))[1] = vcm.name
  ON CONFLICT DO NOTHING;

COMMIT;
