-- Deploy guix-build-coordinator:create_systems to sqlite

PRAGMA foreign_keys = OFF;

BEGIN;

CREATE TABLE systems (
  id INTEGER PRIMARY KEY,
  system TEXT NOT NULL
);

INSERT INTO systems (system)
  SELECT system FROM derivations
  UNION
  SELECT system FROM build_allocation_agent_requested_systems;



CREATE TABLE derivations_new (
       id INTEGER PRIMARY KEY ASC,
       name TEXT NOT NULL,
       system_id INTEGER NOT NULL REFERENCES systems (id),
       fixed_output BOOLEAN
       CHECK (fixed_output IN (0,1))
);

INSERT INTO derivations_new
  SELECT derivations.id, name, systems.id, fixed_output
  FROM derivations
  INNER JOIN systems ON derivations.system = systems.system;

DROP TABLE derivations;
ALTER TABLE derivations_new RENAME TO derivations;

CREATE UNIQUE INDEX derivations_name_idx ON derivations (name);



CREATE TABLE build_allocation_agent_requested_systems_new (
       agent_id INTEGER NOT NULL REFERENCES agents (id),
       system_id INTEGER NOT NULL,
       PRIMARY KEY (agent_id, system_id)
);

INSERT INTO build_allocation_agent_requested_systems_new
  SELECT agent_id, systems.id
  FROM build_allocation_agent_requested_systems
  INNER JOIN systems
    ON build_allocation_agent_requested_systems.system = systems.system;

DROP TABLE build_allocation_agent_requested_systems;
ALTER TABLE build_allocation_agent_requested_systems_new
  RENAME TO build_allocation_agent_requested_systems;



PRAGMA foreign_key_check;

COMMIT;

PRAGMA foreign_keys = ON;