-- De-polymorphize grants into per-type tables with real foreign keys. owner/member
-- already left grants for tenant_members (026); what remains is non-membership
-- role@path capability, split by principal type.

-- user_grants: a user must be a member of the tenant to hold a role grant, so the
-- FK targets tenant_members (not users). Revoking membership cascades grants away.
CREATE TABLE user_grants (
    tenant  TEXT NOT NULL,
    user TEXT NOT NULL,
    role    TEXT NOT NULL,
    path    TEXT NOT NULL DEFAULT '',
    system  INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (tenant, user, role, path),
    FOREIGN KEY (tenant, user) REFERENCES tenant_members(tenant, user) ON DELETE CASCADE
) STRICT;

CREATE TABLE group_grants (
    tenant     TEXT NOT NULL,
    group_path TEXT NOT NULL,
    role       TEXT NOT NULL,
    path       TEXT NOT NULL DEFAULT '',
    system     INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (tenant, group_path, role, path),
    FOREIGN KEY (tenant, group_path) REFERENCES groups(tenant, path) ON DELETE CASCADE
) STRICT;

CREATE TABLE bot_grants (
    tenant TEXT NOT NULL,
    bot TEXT NOT NULL,
    role   TEXT NOT NULL,
    path   TEXT NOT NULL DEFAULT '',
    system INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (tenant, bot, role, path),
    FOREIGN KEY (bot) REFERENCES bots(id) ON DELETE CASCADE
) STRICT;

-- Migrate existing grants by type. user grants are guarded on membership existing;
-- a non-member user grant violates the new invariant and is dropped rather than
-- failing the migration (should not occur — membership was already a precondition).
INSERT INTO user_grants (tenant, user, role, path, system)
SELECT g.tenant, g.principal, g.role, g.path, g.system
FROM grants g
WHERE g.type = 'user'
  AND EXISTS (SELECT 1 FROM tenant_members m WHERE m.tenant = g.tenant AND m.user = g.principal);

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

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

DROP TABLE grants;