-- =============================================================================
-- ARTH ERP / TextilePro — User login & module permissions migration
-- =============================================================================
-- Import this file in phpMyAdmin or: mysql -u user -p database_name < 001_user_access.sql
--
-- Safe to re-run: skips steps that already exist.
-- Requires: users, roles, permissions, role_permissions, hr_employees tables.
-- =============================================================================

SET NAMES utf8mb4;
SET time_zone = '+00:00';

-- -----------------------------------------------------------------------------
-- 1. users.employee_id — link login to employee
-- -----------------------------------------------------------------------------

SET @db = DATABASE();

SET @sql = IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'users' AND COLUMN_NAME = 'employee_id') = 0,
  'ALTER TABLE users ADD COLUMN employee_id INT UNSIGNED NULL AFTER full_name',
  'SELECT ''users.employee_id already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
  (SELECT COUNT(*) FROM information_schema.STATISTICS
   WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'users' AND INDEX_NAME = 'uq_users_employee') = 0,
  'ALTER TABLE users ADD UNIQUE KEY uq_users_employee (employee_id)',
  'SELECT ''uq_users_employee already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
  (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
   WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'users' AND CONSTRAINT_NAME = 'fk_users_employee') = 0,
  'ALTER TABLE users ADD CONSTRAINT fk_users_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE SET NULL',
  'SELECT ''fk_users_employee already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- -----------------------------------------------------------------------------
-- 2. New permission
-- -----------------------------------------------------------------------------

INSERT INTO permissions (name, code) VALUES
  ('Manage users', 'admin.users.manage')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- -----------------------------------------------------------------------------
-- 3. Module roles (one role per ERP module)
-- -----------------------------------------------------------------------------

INSERT INTO roles (name, code) VALUES
  ('Module: Inventory', 'mod_inventory'),
  ('Module: Sales', 'mod_sales'),
  ('Module: HRMS', 'mod_hr'),
  ('Module: Accounts', 'mod_accounts'),
  ('Module: Dispatch', 'mod_dispatch'),
  ('Module: Analytics', 'mod_analytics'),
  ('Module: Administration', 'mod_admin')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- -----------------------------------------------------------------------------
-- 4. Module role → permission mapping
-- -----------------------------------------------------------------------------

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.code = 'mod_inventory' AND p.code IN ('inventory.view', 'inventory.manage')
ON DUPLICATE KEY UPDATE role_id = role_id;

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.code = 'mod_sales' AND p.code IN ('sales.view', 'sales.manage')
ON DUPLICATE KEY UPDATE role_id = role_id;

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.code = 'mod_hr' AND p.code IN ('hr.view', 'hr.manage')
ON DUPLICATE KEY UPDATE role_id = role_id;

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.code = 'mod_accounts' AND p.code IN ('accounts.view', 'accounts.manage')
ON DUPLICATE KEY UPDATE role_id = role_id;

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.code = 'mod_dispatch' AND p.code IN ('dispatch.view', 'dispatch.manage')
ON DUPLICATE KEY UPDATE role_id = role_id;

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.code = 'mod_analytics' AND p.code = 'analytics.view'
ON DUPLICATE KEY UPDATE role_id = role_id;

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.code = 'mod_admin' AND p.code IN ('admin.users.view', 'admin.users.manage')
ON DUPLICATE KEY UPDATE role_id = role_id;

-- -----------------------------------------------------------------------------
-- 5. Admin role — ensure manage-users permission
-- -----------------------------------------------------------------------------

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.code = 'admin' AND p.code = 'admin.users.manage'
ON DUPLICATE KEY UPDATE role_id = role_id;

-- -----------------------------------------------------------------------------
-- Done
-- -----------------------------------------------------------------------------

SELECT '001_user_access migration completed successfully.' AS status;