-- Re-introduce tenant_members as the sole record of membership. owner/member
-- stop being grants: membership (and the owner bit) lives here, and access
-- resolution derives owner/member caps from this table. This inverts the prior
-- consolidation (018), where membership was a redundant parallel to owner/member
-- grants that access never read.
CREATE TABLE tenant_members (
    tenant  TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    user TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    owner   INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (tenant, user)
) STRICT;

CREATE INDEX idx_tenant_members_user ON tenant_members(user);

-- Backfill membership from existing owner/member grants, before the quota
-- triggers exist so the backfill is not quota-checked. A user holding both an
-- owner and a member grant collapses to a single owner membership.
INSERT INTO tenant_members (tenant, user, owner)
SELECT tenant, principal, MAX(role = 'owner')
FROM grants
WHERE type = 'user' AND role IN ('owner', 'member')
GROUP BY tenant, principal;

-- Move the members quota accounting from grants to tenant_members: membership
-- is now what "members_used" counts.
DROP TRIGGER ents_members_incr;
DROP TRIGGER ents_members_decr;
DROP TRIGGER quota_check_members;

CREATE TRIGGER ents_members_incr AFTER INSERT ON tenant_members BEGIN
    UPDATE entitlements SET members_used = members_used + 1 WHERE tenant = NEW.tenant;
END;
CREATE TRIGGER ents_members_decr AFTER DELETE ON tenant_members BEGIN
    UPDATE entitlements SET members_used = members_used - 1 WHERE tenant = OLD.tenant;
END;
CREATE TRIGGER quota_check_members
BEFORE INSERT ON tenant_members
WHEN NOT EXISTS (
    SELECT 1 FROM tenant_members WHERE tenant = NEW.tenant AND user = NEW.user
)
BEGIN
    SELECT RAISE(ABORT, 'quota exceeded: members')
    WHERE NOT EXISTS (
        SELECT 1 FROM entitlements WHERE tenant = NEW.tenant AND members_used < members_max
    );
END;

-- owner/member are no longer grants. The member triggers on grants are already
-- dropped, so this DELETE does not touch members_used.
DELETE FROM grants WHERE type = 'user' AND role IN ('owner', 'member');

-- Recompute members_used from the authoritative table.
UPDATE entitlements SET members_used = (
    SELECT COUNT(*) FROM tenant_members WHERE tenant = entitlements.tenant
);