-- 20260416_04_roles_description_metadata.sql
-- Add role metadata for Access Control UI; idempotent column adds; sync descriptions with application catalog.

SET @db := DATABASE();

SET @sql := (
  SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'roles' AND COLUMN_NAME = 'description') > 0,
    'SELECT 1',
    'ALTER TABLE roles ADD COLUMN description VARCHAR(512) NULL COMMENT ''Role summary for Access Control UI'''
  )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql2 := (
  SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'roles' AND COLUMN_NAME = 'is_custom') > 0,
    'SELECT 1',
    'ALTER TABLE roles ADD COLUMN is_custom TINYINT(1) NOT NULL DEFAULT 0 COMMENT ''1 = user-defined role'''
  )
);
PREPARE stmt2 FROM @sql2;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;

UPDATE roles SET description = 'Master access to all configurations and system features.', is_custom = 0 WHERE id = 'ROLE-SUPER-ADMIN';
UPDATE roles SET description = 'Full management capabilities, except System Map visibility.', is_custom = 0 WHERE id = 'ROLE-ADMIN';
UPDATE roles SET description = 'On-site lead managing crew members and project status.', is_custom = 0 WHERE id = 'ROLE-FOREMAN-CONST';
UPDATE roles SET description = 'Standard field worker.', is_custom = 0 WHERE id = 'ROLE-CREWMAN';
UPDATE roles SET description = 'Electrical design and technical scoping.', is_custom = 0 WHERE id = 'ROLE-ELEC-ENG';
UPDATE roles SET description = 'Lead for electrical field groups.', is_custom = 0 WHERE id = 'ROLE-ELEC-FOREMAN';
UPDATE roles SET description = 'Electrical field technician.', is_custom = 0 WHERE id = 'ROLE-ELECTRICIAN';