-- =====================================================================
-- 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();

-- ----------------------------------------------------------------
-- 1. users.must_change_password (forced first-login password change)
-- ----------------------------------------------------------------
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'users' AND COLUMN_NAME = 'must_change_password');
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;

-- ----------------------------------------------------------------
-- 2. employees salary structure (drives payroll line generation)
-- ----------------------------------------------------------------
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'employees' AND COLUMN_NAME = 'basic_salary');
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;

-- ----------------------------------------------------------------
-- 3. hr_devices — face / RFID / NFC attendance device registry
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS hr_devices (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL DEFAULT 1,
  device_name VARCHAR(100) NOT NULL,
  device_type ENUM('face','rfid','nfc') NOT NULL DEFAULT 'face',
  device_token VARCHAR(80) NOT NULL,
  location VARCHAR(150) NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  last_seen_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_device_token (device_token),
  KEY idx_company (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------
-- 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);
