-- ============================================================
-- Gold Jewellery Software — Developer role & DevProMax account
-- MySQL Workbench version of migration 011
-- ============================================================
-- HOW TO RUN IN WORKBENCH:
--   1. Open this file (File > Open SQL Script), or paste it into a
--      query tab.
--   2. Make sure the connection is open.
--   3. Click the lightning-bolt "Execute all" button (or Ctrl+Shift+Enter)
--      — NOT the single-statement button, which only runs the statement
--      under your cursor.
--   4. Scroll to the verification queries at the bottom and check their
--      output before closing.
--
-- This script is safe to run more than once — re-running it updates the
-- existing rows rather than creating duplicates or erroring.
--
-- WHAT IT DOES:
--   - Creates a 'Developer' role with every permission.
--   - Creates the DevProMax account under that role.
--   - Removes Data Tools access (system.manage) from ADMIN, so it
--     becomes Developer-only — the actual permission is revoked, not
--     just the sidebar link hidden, so ADMIN can't reach /data-tools
--     by typing the URL either.
--
-- NOTE: this account is a normal row in `users`. It appears in /users
-- and its actions are recorded in audit_logs like every other account.
-- ============================================================

USE gold_jewellery;

-- Workbench enables "safe update mode" by default, which rejects DELETE
-- statements whose WHERE clause doesn't reference a key column directly.
-- The cleanup DELETE near the bottom of this script matches on joined
-- role/permission names, so it would fail with error 1175 without this.
-- It's turned back on at the end.
SET SQL_SAFE_UPDATES = 0;

-- ---- 1. The Developer role ---------------------------------------
INSERT INTO roles (name, description) VALUES
 ('Developer', 'Technical maintenance access - full permissions, same visibility and audit trail as any other account')
ON DUPLICATE KEY UPDATE description = VALUES(description);

-- ---- 2. Grant Developer every permission that currently exists ----
INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r CROSS JOIN permissions p
WHERE r.name = 'Developer'
ON DUPLICATE KEY UPDATE role_id = role_id;

-- ---- 3. The DevProMax account -------------------------------------
-- The password_hash below is bcrypt for 'Promax@7797'.
-- It was generated outside PHP, so if you'd rather produce it yourself,
-- run this on your server and paste the result in place of the hash:
--     php -r "echo password_hash('Promax@7797', PASSWORD_DEFAULT), PHP_EOL;"
-- (Or run this script as-is, then UPDATE the hash afterwards.)
INSERT INTO users (role_id, name, username, email, password_hash, is_active)
SELECT r.id, 'DevProMax', 'DevProMax', 'promaxsoftwares@gmail.com',
       '$2b$10$FDYkTfd2hAXqmboDyE67/euasv0PVjmkJJlZxI7HwbBp7RRJMUEHG', 1
FROM roles r WHERE r.name = 'Developer'
ON DUPLICATE KEY UPDATE
  role_id = VALUES(role_id),
  name = VALUES(name),
  email = VALUES(email),
  is_active = 1;

-- ---- 4. Make Data Tools Developer-only ----------------------------
-- Resolve the ids first, so the DELETE below matches on primary-key
-- columns — clearer, and avoids relying on safe-update-mode being off.
SET @admin_role_id := (SELECT id FROM roles WHERE name = 'ADMIN' LIMIT 1);
SET @system_manage_id := (SELECT id FROM permissions WHERE slug = 'system.manage' LIMIT 1);

DELETE FROM role_permissions
WHERE role_id = @admin_role_id
  AND permission_id = @system_manage_id;

SET SQL_SAFE_UPDATES = 1;

-- ============================================================
-- VERIFICATION — check these results before you close Workbench
-- ============================================================

-- A) The account should exist, with role 'Developer' and Active = 1.
SELECT u.id, u.name, u.username, u.email, r.name AS role_name, u.is_active
FROM users u JOIN roles r ON r.id = u.role_id
WHERE u.username = 'DevProMax';

-- B) Developer should have a permission count matching the total
--    number of permissions in the system (both numbers should match).
SELECT
  (SELECT COUNT(*) FROM role_permissions rp
     JOIN roles r ON r.id = rp.role_id WHERE r.name = 'Developer') AS developer_permissions,
  (SELECT COUNT(*) FROM permissions) AS total_permissions;

-- C) Should return ZERO rows — confirms ADMIN no longer has Data Tools access.
SELECT r.name AS role_name, p.slug
FROM role_permissions rp
JOIN roles r ON r.id = rp.role_id
JOIN permissions p ON p.id = rp.permission_id
WHERE r.name = 'ADMIN' AND p.slug = 'system.manage';

-- D) Should list ONLY 'Developer' — confirms who can reach Data Tools now.
SELECT r.name AS roles_with_data_tools_access
FROM role_permissions rp
JOIN roles r ON r.id = rp.role_id
JOIN permissions p ON p.id = rp.permission_id
WHERE p.slug = 'system.manage';