-- 20260416_02_seed_roles_and_constraints.sql
-- Idempotent role seeds and crew-only foreman assignment trigger.

INSERT INTO roles (id, name, class, permissions)
VALUES
  ('ROLE-SUPER-ADMIN', 'Super Admin', 'Super Admin', JSON_ARRAY('system.map.view', 'admin.global.view', 'users.manage', 'roles.manage', 'projects.assign', 'crew.manage', 'tasks.create', 'tasks.update', 'capture.create', 'timesheet.clockin')),
  ('ROLE-ADMIN', 'Administrator', 'Admin', JSON_ARRAY('admin.global.view', 'users.manage', 'roles.manage', 'projects.assign', 'crew.manage', 'tasks.create', 'tasks.update', 'capture.create', 'timesheet.clockin')),
  ('ROLE-FOREMAN-CONST', 'Crew Foreman', 'Construction Crew', JSON_ARRAY('crew.manage', 'tasks.update', 'capture.create', 'timesheet.clockin')),
  ('ROLE-CREWMAN', 'Crewman', 'Construction Crew', JSON_ARRAY('capture.create', 'timesheet.clockin')),
  ('ROLE-ELEC-ENG', 'Electrical Engineer', 'Electrical Crew', JSON_ARRAY('tasks.create', 'tasks.update', 'capture.create', 'timesheet.clockin')),
  ('ROLE-ELEC-FOREMAN', 'Electrical Foreman', 'Electrical Crew', JSON_ARRAY('crew.manage', 'tasks.update', 'capture.create', 'timesheet.clockin')),
  ('ROLE-ELECTRICIAN', 'Electrician', 'Electrical Crew', JSON_ARRAY('capture.create', 'timesheet.clockin'))
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  class = VALUES(class),
  permissions = VALUES(permissions),
  updated_at = CURRENT_TIMESTAMP;

DROP TRIGGER IF EXISTS trg_employee_manager_role_guard_insert;
DROP TRIGGER IF EXISTS trg_employee_manager_role_guard_update;

DELIMITER $$
CREATE TRIGGER trg_employee_manager_role_guard_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  DECLARE employeeRoleClass VARCHAR(50);
  DECLARE managerHasCrewManage BOOLEAN;

  IF NEW.manager_id IS NOT NULL THEN
    SELECT class INTO employeeRoleClass
    FROM roles
    WHERE id = NEW.role_id;

    IF employeeRoleClass NOT IN ('Construction Crew', 'Electrical Crew') THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Only crew roles can have manager assignment';
    END IF;

    SELECT JSON_CONTAINS(permissions, JSON_QUOTE('crew.manage')) INTO managerHasCrewManage
    FROM roles r
    INNER JOIN employees e ON e.role_id = r.id
    WHERE e.id = NEW.manager_id;

    IF managerHasCrewManage IS NULL OR managerHasCrewManage = 0 THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Assigned manager must be a foreman role';
    END IF;
  END IF;
END $$

CREATE TRIGGER trg_employee_manager_role_guard_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  DECLARE employeeRoleClass VARCHAR(50);
  DECLARE managerHasCrewManage BOOLEAN;

  IF NEW.manager_id IS NOT NULL THEN
    SELECT class INTO employeeRoleClass
    FROM roles
    WHERE id = NEW.role_id;

    IF employeeRoleClass NOT IN ('Construction Crew', 'Electrical Crew') THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Only crew roles can have manager assignment';
    END IF;

    SELECT JSON_CONTAINS(permissions, JSON_QUOTE('crew.manage')) INTO managerHasCrewManage
    FROM roles r
    INNER JOIN employees e ON e.role_id = r.id
    WHERE e.id = NEW.manager_id;

    IF managerHasCrewManage IS NULL OR managerHasCrewManage = 0 THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Assigned manager must be a foreman role';
    END IF;
  END IF;
END $$
DELIMITER ;