-- Hammerhead Workspace - Patch 6: Additional roles
-- Adds Ops Manager, Office Manager, Field Team, Account Manager (and Director,
-- skipped if it already exists). Levels are slotted at the same tier as
-- "Manager" so they sort together in the role dropdown without disturbing
-- your existing CEO/Director/Manager/Employee ordering.
-- Safe to re-run.

INSERT INTO `roles` (`code`, `name`, `level`, `description`)
SELECT 'director', 'Director', COALESCE((SELECT level FROM roles WHERE code='manager' LIMIT 1), 3) - 1, 'Oversees multiple departments.'
WHERE NOT EXISTS (SELECT 1 FROM `roles` WHERE `name` = 'Director');

INSERT INTO `roles` (`code`, `name`, `level`, `description`)
SELECT 'ops_manager', 'Ops Manager', COALESCE((SELECT level FROM roles WHERE code='manager' LIMIT 1), 3), 'Manages Operations department.'
WHERE NOT EXISTS (SELECT 1 FROM `roles` WHERE `name` = 'Ops Manager');

INSERT INTO `roles` (`code`, `name`, `level`, `description`)
SELECT 'office_manager', 'Office Manager', COALESCE((SELECT level FROM roles WHERE code='manager' LIMIT 1), 3), 'Manages Office / Purchasing / Recruiting.'
WHERE NOT EXISTS (SELECT 1 FROM `roles` WHERE `name` = 'Office Manager');

INSERT INTO `roles` (`code`, `name`, `level`, `description`)
SELECT 'field_team', 'Field Team', COALESCE((SELECT level FROM roles WHERE code='manager' LIMIT 1), 3) + 1, 'Field-based team member/lead.'
WHERE NOT EXISTS (SELECT 1 FROM `roles` WHERE `name` = 'Field Team');

INSERT INTO `roles` (`code`, `name`, `level`, `description`)
SELECT 'account_manager', 'Account Manager', COALESCE((SELECT level FROM roles WHERE code='manager' LIMIT 1), 3), 'Manages client accounts.'
WHERE NOT EXISTS (SELECT 1 FROM `roles` WHERE `name` = 'Account Manager');