-- Recreate the polymorphic grants table and fold the per-type tables back in.
CREATE TABLE grants (
    tenant    TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    type      TEXT NOT NULL,
    principal TEXT NOT NULL,
    role      TEXT NOT NULL,
    path      TEXT NOT NULL DEFAULT '',
    system    INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (tenant, type, principal, role, path)
) STRICT;

CREATE INDEX idx_grants_principal ON grants(type, principal);

INSERT INTO grants (tenant, type, principal, role, path, system)
SELECT tenant, 'user', user, role, path, system FROM user_grants;

INSERT INTO grants (tenant, type, principal, role, path, system)
SELECT tenant, 'group', group_path, role, path, system FROM group_grants;

INSERT INTO grants (tenant, type, principal, role, path, system)
SELECT tenant, 'bot', bot, role, path, system FROM bot_grants;

DROP TABLE user_grants;
DROP TABLE group_grants;
DROP TABLE bot_grants;