-- Resource access grants: which user/team may use a gated resource (portal, integration config).

CREATE TABLE IF NOT EXISTS resource_grants (
    resource_grant_id  BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    resource_type      VARCHAR(64)  NOT NULL,
    resource_id        VARCHAR(255) NOT NULL DEFAULT '',
    principal_type     VARCHAR(32)  NOT NULL,
    principal_id       BIGINT       NOT NULL,
    permission         VARCHAR(32)  NOT NULL,
    granted_by_user_id BIGINT,
    created_at         TIMESTAMP    NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS uk_resource_grant
    ON resource_grants (resource_type, resource_id, principal_type, principal_id, permission);

CREATE INDEX IF NOT EXISTS idx_resource_grants_lookup
    ON resource_grants (resource_type, resource_id);

CREATE INDEX IF NOT EXISTS idx_resource_grants_principal
    ON resource_grants (principal_type, principal_id);
