-- Re-encryption requires application key material and cannot be performed by SQL.
-- Refuse rollback once plaintext credentials have been written.
CREATE TABLE chorus_plaintext_credentials_down_guard (
    id TINYINT NOT NULL,
    CONSTRAINT chk_chorus_plaintext_credentials_down_guard CHECK (id = 0)
) ENGINE=InnoDB;

DELETE FROM chorus_plaintext_credentials_down_guard;
INSERT INTO chorus_plaintext_credentials_down_guard (id)
SELECT 1 WHERE EXISTS (SELECT 1 FROM provider_credentials);

DELETE rule
FROM sys_menu_api_rule rule
JOIN sys_api api ON api.id = rule.sys_api_id
WHERE api.handle IN ('chorus.providers.credential.get', 'chorus.health.list', 'chorus.users.update');

DELETE FROM sys_casbin_rule
WHERE ptype = 'p'
  AND v0 = 'chorus_operator'
  AND (
      (v1 = '/api/v1/chorus/providers/:id/credential' AND v2 = 'GET') OR
      (v1 = '/api/v1/chorus/connectivity-checks' AND v2 = 'GET') OR
      (v1 = '/api/v1/chorus/users/:id' AND v2 = 'PUT')
  );

DELETE FROM sys_api WHERE handle IN ('chorus.providers.credential.get', 'chorus.health.list', 'chorus.users.update');

DELETE role_menu
FROM sys_role_menu role_menu
JOIN sys_menu menu ON menu.menu_id = role_menu.menu_id
WHERE menu.path = '/chorus/health';

DELETE FROM sys_menu WHERE path = '/chorus/health';
UPDATE sys_menu SET sort = 50 WHERE path = '/chorus/generations';
UPDATE sys_menu SET sort = 60 WHERE path = '/chorus/users';

ALTER TABLE provider_credentials
    DROP CHECK chk_provider_credentials_api_key,
    DROP COLUMN api_key,
    ADD COLUMN api_key_enc JSON NOT NULL AFTER credential_version,
    ADD COLUMN key_id VARCHAR(128) NOT NULL AFTER api_key_enc,
    ADD CONSTRAINT chk_provider_credentials_envelope CHECK (
        JSON_TYPE(api_key_enc) = 'OBJECT' AND
        JSON_CONTAINS_PATH(api_key_enc, 'all', '$.version', '$.key_id', '$.nonce', '$.ciphertext')
    );

DROP TABLE chorus_plaintext_credentials_down_guard;