-- Gold Jewellery Software - User Management
-- Migration 004: adds a display username to users, and a dedicated
-- user.manage permission so User Management can be gated separately
-- from general Settings access.
USE gold_jewellery;

-- Username is a separate display/identifier field from email; login
-- still uses email (see App\Service\AuthService) — this does not change
-- how people sign in, it only adds a friendlier handle shown in the UI.
ALTER TABLE users ADD COLUMN IF NOT EXISTS username VARCHAR(100) NULL AFTER name;

-- Backfill existing users with a username derived from their email's
-- local part, so the column is never blank for pre-existing accounts.
UPDATE users
SET username = SUBSTRING_INDEX(email, '@', 1)
WHERE username IS NULL OR username = '';

ALTER TABLE users ADD UNIQUE KEY uq_users_username (username);

INSERT INTO permissions (slug, description) VALUES
 ('user.manage', 'Create, edit, deactivate users, and reset passwords')
ON DUPLICATE KEY UPDATE description = VALUES(description);

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r CROSS JOIN permissions p
WHERE r.name = 'ADMIN' AND p.slug = 'user.manage'
ON DUPLICATE KEY UPDATE role_id = role_id;