CREATE TRIGGER api_key_project_access_scope_guard
BEFORE INSERT ON api_key_project_access
FOR EACH ROW
WHEN (SELECT scope_mode FROM api_keys WHERE id = NEW.api_key_id) NOT IN ('project_allow_list', 'project_deny_list')
BEGIN
    SELECT RAISE(ABORT, 'api key scope_mode does not permit project access rows');
END;

CREATE TRIGGER api_key_tag_access_scope_guard
BEFORE INSERT ON api_key_tag_access
FOR EACH ROW
WHEN (SELECT scope_mode FROM api_keys WHERE id = NEW.api_key_id) NOT IN ('tag_allow_list', 'tag_deny_list')
BEGIN
    SELECT RAISE(ABORT, 'api key scope_mode does not permit tag access rows');
END;

CREATE TRIGGER api_keys_scope_mode_transition_guard
BEFORE UPDATE OF scope_mode ON api_keys
FOR EACH ROW
BEGIN
    SELECT CASE
        WHEN NEW.scope_mode = 'all_projects' AND EXISTS (
            SELECT 1
            FROM api_key_project_access
            WHERE api_key_id = NEW.id
        ) THEN RAISE(ABORT, 'all_projects keys cannot keep project access rows')
    END;

    SELECT CASE
        WHEN NEW.scope_mode = 'all_projects' AND EXISTS (
            SELECT 1
            FROM api_key_tag_access
            WHERE api_key_id = NEW.id
        ) THEN RAISE(ABORT, 'all_projects keys cannot keep tag access rows')
    END;

    SELECT CASE
        WHEN NEW.scope_mode IN ('project_allow_list', 'project_deny_list') AND EXISTS (
            SELECT 1
            FROM api_key_tag_access
            WHERE api_key_id = NEW.id
        ) THEN RAISE(ABORT, 'project-scoped keys cannot keep tag access rows')
    END;

    SELECT CASE
        WHEN NEW.scope_mode IN ('tag_allow_list', 'tag_deny_list') AND EXISTS (
            SELECT 1
            FROM api_key_project_access
            WHERE api_key_id = NEW.id
        ) THEN RAISE(ABORT, 'tag-scoped keys cannot keep project access rows')
    END;
END;