-- =====================================================================
-- FCQC Digital Workforce Platform — Extension Migration
-- Covers: forced password change, employee salary data, attendance
-- devices, and permissions for the deferred HR modules
-- (Payroll, Training, Performance, Contractors, Assets, Visitors, AI).
-- Idempotent. Column adds are guarded via information_schema.
-- =====================================================================

SET @db = DATABASE();
SET @s := IF(@c = 0,
  'ALTER TABLE users ADD COLUMN must_change_password TINYINT(1) NOT NULL DEFAULT 0',
  'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
SET @s := IF(@c = 0,
  'ALTER TABLE employees ADD COLUMN basic_salary DECIMAL(12,2) NOT NULL DEFAULT 0, ADD COLUMN allowances DECIMAL(12,2) NOT NULL DEFAULT 0, ADD COLUMN deductions DECIMAL(12,2) NOT NULL DEFAULT 0',
  'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- ----------------------------------------------------------------
-- 4. Module permissions + grants (roles 1=admin, 2=quality_manager)
-- ----------------------------------------------------------------
INSERT INTO permissions (code, label, group_name, description) VALUES
('payroll.view','View Payroll','Payroll','View payroll runs and pay lines'),
('payroll.manage','Manage Payroll','Payroll','Create and approve payroll runs'),
('training.view','View Training','Training','View employee training records'),
('training.manage','Manage Training','Training','Record employee training'),
('performance.view','View Performance','Performance','View performance scores'),
('performance.manage','Manage Performance','Performance','Record performance scores'),
('contractors.view','View HR Contractors','HR Contractors','View contractors and their workers'),
('contractors.manage','Manage HR Contractors','HR Contractors','Manage contractors and workers'),
('assets.view','View Employee Assets','Employee Assets','View asset catalog and assignments'),
('assets.manage','Manage Employee Assets','Employee Assets','Manage assets and assignments'),
('visitors.view','View Visitors','Visitors','View visitor logs'),
('visitors.manage','Manage Visitors','Visitors','Register and manage visitors'),
('hr.ai','View AI Workforce Insights','HR','View AI workforce analytics'),
('hr.devices','Manage Attendance Devices','HR','Register and manage attendance devices')
ON DUPLICATE KEY UPDATE label = VALUES(label), group_name = VALUES(group_name);

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM app_roles r
CROSS JOIN permissions p
WHERE r.id IN (1, 2)
  AND p.code IN ('hr.view','hr.manage','hr.attendance','hr.approve','hr.ai','hr.devices',
                 'payroll.view','payroll.manage','training.view','training.manage',
                 'performance.view','performance.manage','contractors.view','contractors.manage',
                 'assets.view','assets.manage','visitors.view','visitors.manage')
ON DUPLICATE KEY UPDATE permission_id = VALUES(permission_id);
