-- Email Operator role: marketing/email staff with campaign CRUD but no SMTP admin or deletes.
-- Assign app_role_id via Web Settings → Roles (see RolesPermissionsPanel) or employees.app_role_id.
--
-- Manual QA checklist (Email Operator):
-- 1. Assign role to a test employee; log in as that user
-- 2. Web Settings sidebar: Company SMTPs, Sending Limits, Email Campaigns visible; Roles hidden
-- 3. Company SMTP: list + health view OK; Add/Edit/Delete hidden; probe OK, no test-send without update
-- 4. Sending Limits: fields read-only; Save hidden
-- 5. Email Campaigns: create/edit/start/pause/resume OK; Delete hidden; Analytics tab visible
-- 6. Roles matrix: email_campaigns.create requires company_smtp.read + sending_limits.read

INSERT INTO workspace_roles (name, slug, description, is_system, status)
SELECT
  'Email Operator',
  'email-operator',
  'Marketing email staff: manage campaigns, view SMTP health and sending limits (no deletes or SMTP edits)',
  0,
  'active'
WHERE NOT EXISTS (
  SELECT 1 FROM workspace_roles WHERE slug = 'email-operator' AND deleted_at IS NULL
);

SET @email_op_role_id = (
  SELECT id FROM workspace_roles WHERE slug = 'email-operator' AND deleted_at IS NULL LIMIT 1
);

DELETE FROM role_permissions WHERE role_id = @email_op_role_id;

INSERT IGNORE INTO role_permissions (role_id, module_key, action_key, allowed) VALUES
  (@email_op_role_id, 'dashboard', 'read', 1),
  (@email_op_role_id, 'profile', 'read', 1),
  (@email_op_role_id, 'profile', 'update', 1),
  (@email_op_role_id, 'web_settings', 'read', 1),
  (@email_op_role_id, 'company_smtp', 'read', 1),
  (@email_op_role_id, 'company_smtp', 'view', 1),
  (@email_op_role_id, 'sending_limits', 'read', 1),
  (@email_op_role_id, 'email_campaigns', 'read', 1),
  (@email_op_role_id, 'email_campaigns', 'create', 1),
  (@email_op_role_id, 'email_campaigns', 'update', 1),
  (@email_op_role_id, 'email_campaigns', 'view', 1);