CREATE TABLE runbot_merge_stagings_commits (
    id serial NOT NULL,
    staging_id integer not null references runbot_merge_stagings (id),
    commit_id integer not null references runbot_merge_commit (id),
    repository_id integer not null references runbot_merge_repository (id)
);

CREATE TABLE runbot_merge_stagings_heads (
    id serial NOT NULL,
    staging_id integer NOT NULL REFERENCES runbot_merge_stagings (id),
    commit_id integer NOT NULL REFERENCES runbot_merge_commit (id),
    repository_id integer NOT NULL REFERENCES runbot_merge_repository (id)
);

-- some of the older stagings only have the head, not the commit,
-- add the commit
UPDATE runbot_merge_stagings
  SET heads = heads::jsonb || jsonb_build_object(
    'odoo/odoo^', heads::json->'odoo/odoo',
    'odoo/enterprise^', heads::json->'odoo/enterprise'
  )
  WHERE heads NOT ILIKE '%^%';

-- some of the stagings have heads which don't exist in the commits table,
-- because they never got a status from the runbot...
-- create fake commits so we don't lose heads
INSERT INTO runbot_merge_commit (sha, statuses, create_uid, create_date, write_uid, write_date)
    SELECT r.value, '{}', s.create_uid, s.create_date, s.create_uid, s.create_date
    FROM runbot_merge_stagings s,
         json_each_text(s.heads::json) r
ON CONFLICT DO NOTHING;

CREATE TEMPORARY TABLE staging_commits (
    id integer NOT NULL,
    repo integer NOT NULL,
    -- the staging head (may be a dedup, may be the same as commit)
    head integer NOT NULL,
    -- the staged commit
    commit integer NOT NULL
);
-- the splatting works entirely off of the staged head
-- (the one without the ^ suffix), we concat the `^` to get the corresponding
-- merge head (the actual commit to push to the branch)
INSERT INTO staging_commits (id, repo, head, commit)
    SELECT s.id, re.id AS repo, h.id AS head, c.id AS commit
    FROM runbot_merge_stagings s,
         json_each_text(s.heads::json) r,
         runbot_merge_commit h,
         runbot_merge_commit c,
         runbot_merge_repository re
    WHERE r.key NOT ILIKE '%^'
      AND re.name = r.key
      AND h.sha = r.value
      AND c.sha = s.heads::json->>(r.key || '^');

INSERT INTO runbot_merge_stagings_heads (staging_id, repository_id, commit_id)
SELECT id, repo, head FROM staging_commits;

INSERT INTO runbot_merge_stagings_commits (staging_id, repository_id, commit_id)
SELECT id, repo, commit FROM staging_commits;

ALTER TABLE runbot_merge_stagings DROP COLUMN heads;