-- Pacific Logistics · slice 25 · Staff ID card support
--
-- Additions needed for the printed staff ID card (Iter 4 add-on):
--   * drivers.blood_group    — printed on the back of the card
--
-- Grants for the new endpoints:
--   * Drivers/staffId        — HR + Admin can print
--   * Verify/index           — public (token-less), for guards scanning QR


DELIMITER $$
DROP PROCEDURE IF EXISTS pl_add_col_if_missing_25 $$
CREATE PROCEDURE pl_add_col_if_missing_25(IN tbl VARCHAR(64), IN col VARCHAR(64), IN spec VARCHAR(500))
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.COLUMNS
         WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = tbl AND COLUMN_NAME = col
    ) THEN
        SET @s = CONCAT('ALTER TABLE ', tbl, ' ADD COLUMN ', col, ' ', spec);
        PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;
    END IF;
END $$
DELIMITER ;

CALL pl_add_col_if_missing_25('drivers', 'blood_group', 'VARCHAR(3) NULL AFTER photo_url');

DROP PROCEDURE pl_add_col_if_missing_25;


-- Grants — Drivers/staffId is Admin + HR + Fleet
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
(10, 'Drivers', 'staffId', 1, NOW()),
(20, 'Drivers', 'staffId', 1, NOW()),
(22, 'Drivers', 'staffId', 1, NOW()),
(1,  'Drivers', 'staffId', 1, NOW());
-- Verify is public (no perm gating; Router::isPublic check).
