-- Deploy guix-data-service:git_branch_id to pg

BEGIN;

ALTER TABLE git_branches RENAME TO git_branches_old;

CREATE TABLE git_branches (
    id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name character varying NOT NULL,
    git_repository_id integer NOT NULL REFERENCES git_repositories (id),
    CONSTRAINT git_repository_id_name_unique UNIQUE (git_repository_id, name)
);

CREATE TABLE git_commits (
    id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    commit character varying NOT NULL,
    git_branch_id integer NOT NULL REFERENCES git_branches (id),
    datetime timestamp without time zone NOT NULL
);

INSERT INTO git_branches (name, git_repository_id)
  SELECT DISTINCT name, git_repository_id
  FROM git_branches_old;

INSERT INTO git_commits (commit, git_branch_id, datetime)
  SELECT commit, git_branches.id, datetime
  FROM git_branches_old
  INNER JOIN git_branches
    ON git_branches_old.name = git_branches.name
   AND git_branches_old.git_repository_id = git_branches.git_repository_id;

COMMIT;