--up

ALTER TABLE projects
  ADD COLUMN remix_of_id integer REFERENCES projects(id) ON DELETE SET NULL,
  ADD COLUMN remixable boolean NOT NULL DEFAULT false;

CREATE INDEX projects_user_id_id_idx ON projects(user_id, id);
CREATE INDEX projects_uuid_idx ON projects(uuid);

CREATE TABLE project_samples (
  project_id integer REFERENCES projects(id) ON DELETE CASCADE,
  sample_id integer REFERENCES samples(id) ON DELETE RESTRICT,
  created_at timestamp NOT NULL DEFAULT NOW(),
  UNIQUE (project_id, sample_id)
);
CREATE INDEX project_samples_sample_id_project_id_idx ON project_samples(sample_id, project_id);

--down

ALTER TABLE projects
  DROP COLUMN remix_of_id,
  DROP COLUMN remixable;
DROP INDEX projects_user_id_id_idx;
DROP INDEX projects_uuid_idx;
DROP TABLE project_samples;