-- AES-GCM envelopes cannot be converted safely inside SQL. Existing credentials
-- must be handled by an explicitly authorized conversion before this migration.
CREATE TABLE chorus_plaintext_credentials_up_guard (
    id TINYINT NOT NULL,
    CONSTRAINT chk_chorus_plaintext_credentials_up_guard CHECK (id = 0)
) ENGINE=InnoDB;

DELETE FROM chorus_plaintext_credentials_up_guard;
INSERT INTO chorus_plaintext_credentials_up_guard (id)
SELECT 1 WHERE EXISTS (SELECT 1 FROM provider_credentials);
DROP TABLE chorus_plaintext_credentials_up_guard;

ALTER TABLE provider_credentials
    DROP CHECK chk_provider_credentials_envelope,
    DROP COLUMN api_key_enc,
    DROP COLUMN key_id,
    ADD COLUMN api_key VARCHAR(4096) NOT NULL AFTER credential_version,
    ADD CONSTRAINT chk_provider_credentials_api_key CHECK (CHAR_LENGTH(api_key) BETWEEN 1 AND 4096);

INSERT INTO sys_menu
    (menu_name, title, icon, path, paths, menu_type, action, permission, parent_id, no_cache, breadcrumb, component, sort, visible, is_frame)
SELECT 'chorus-health', 'Provider Health', 'ri:pulse-line', '/chorus/health', '', 'C', '', 'chorus:health:view',
       parent.menu_id, FALSE, 'Chorus / Provider Health', 'chorus/health/index', 50, '0', '0'
FROM sys_menu parent
WHERE parent.path = '/chorus'
ON DUPLICATE KEY UPDATE
    menu_name = VALUES(menu_name), title = VALUES(title), icon = VALUES(icon), parent_id = VALUES(parent_id),
    permission = VALUES(permission), breadcrumb = VALUES(breadcrumb), component = VALUES(component), sort = VALUES(sort),
    visible = VALUES(visible), is_frame = VALUES(is_frame), deleted_at = NULL;

UPDATE sys_menu child
JOIN sys_menu parent ON parent.path = '/chorus'
SET child.paths = CONCAT(parent.paths, '/', child.menu_id)
WHERE child.path = '/chorus/health';

UPDATE sys_menu SET sort = 60 WHERE path = '/chorus/generations';
UPDATE sys_menu SET sort = 70 WHERE path = '/chorus/users';

INSERT INTO sys_api (handle, title, path, type, action)
VALUES
    ('chorus.providers.credential.get', 'Read provider credential', '/api/v1/chorus/providers/:id/credential', 'BUS', 'GET'),
    ('chorus.health.list', 'List provider health', '/api/v1/chorus/connectivity-checks', 'BUS', 'GET'),
    ('chorus.users.update', 'Update portal user status', '/api/v1/chorus/users/:id', 'BUS', 'PUT')
ON DUPLICATE KEY UPDATE title = VALUES(title), type = VALUES(type), deleted_at = NULL;

INSERT IGNORE INTO sys_role_menu (role_id, menu_id)
SELECT r.role_id, m.menu_id
FROM sys_role r
JOIN sys_menu m ON m.path = '/chorus/health'
WHERE r.role_key = 'chorus_operator';

INSERT IGNORE INTO sys_menu_api_rule (menu_id, sys_api_id)
SELECT m.menu_id, a.id
FROM sys_menu m
JOIN sys_api a ON (
    (m.path = '/chorus/providers' AND a.handle = 'chorus.providers.credential.get') OR
    (m.path = '/chorus/health' AND a.handle = 'chorus.health.list') OR
    (m.path = '/chorus/users' AND a.handle = 'chorus.users.update')
);

INSERT IGNORE INTO sys_casbin_rule (ptype, v0, v1, v2, v3, v4, v5)
SELECT 'p', 'chorus_operator', path, action, '', '', ''
FROM sys_api
WHERE handle IN ('chorus.providers.credential.get', 'chorus.health.list', 'chorus.users.update');

-- The HTTP handler for this permission sets Cache-Control: no-store and
-- Pragma: no-cache. Keep this marker beside the permission seed so static
-- migration checks preserve the single-record, non-cacheable read contract.