-- Widen action_key: web_settings page actions can exceed VARCHAR(32).
-- Repair orphans before re-adding FK (truncated keys under old VARCHAR(32)).

SET @fk_exists := (
  SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
  WHERE CONSTRAINT_SCHEMA = DATABASE()
    AND TABLE_NAME = 'role_permissions'
    AND CONSTRAINT_NAME = 'fk_rp_action'
    AND CONSTRAINT_TYPE = 'FOREIGN KEY'
);
SET @sql := IF(@fk_exists > 0, 'ALTER TABLE role_permissions DROP FOREIGN KEY fk_rp_action', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

ALTER TABLE permission_actions
  MODIFY COLUMN action_key VARCHAR(64) NOT NULL;

ALTER TABLE role_permissions
  MODIFY COLUMN action_key VARCHAR(64) NOT NULL;

-- Seed any action_key present on roles but missing from the catalog
INSERT IGNORE INTO permission_actions (action_key, label, sort_order)
SELECT DISTINCT rp.action_key, rp.action_key, 900
FROM role_permissions rp
LEFT JOIN permission_actions pa ON pa.action_key = rp.action_key
WHERE pa.action_key IS NULL
  AND rp.action_key IS NOT NULL
  AND rp.action_key <> '';

-- Drop any remaining orphans that still cannot satisfy the FK
DELETE rp FROM role_permissions rp
LEFT JOIN permission_actions pa ON pa.action_key = rp.action_key
WHERE pa.action_key IS NULL;

-- Re-add FK only if missing
SET @fk_exists := (
  SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
  WHERE CONSTRAINT_SCHEMA = DATABASE()
    AND TABLE_NAME = 'role_permissions'
    AND CONSTRAINT_NAME = 'fk_rp_action'
    AND CONSTRAINT_TYPE = 'FOREIGN KEY'
);
SET @sql := IF(
  @fk_exists = 0,
  'ALTER TABLE role_permissions ADD CONSTRAINT fk_rp_action FOREIGN KEY (action_key) REFERENCES permission_actions(action_key) ON DELETE CASCADE',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;