-- ====================================================================
-- MIGRATION SCRIPT: Permission Data Population for staging_beszer
-- Generated: 2025-09-16
-- 
-- This script populates the new permission-related tables with data
-- from beszerzes_dev, specifically setting up LZsolt user with ROOT role
-- ====================================================================

-- ====================================================================
-- IMPORTANT: This script assumes the structure migration has been run first!
-- Run migrate_dev_to_staging.sql before running this script
-- ====================================================================

-- Disable foreign key checks during data insertion

-- ====================================================================
-- SECTION 1: Populate Permission table
-- These are all the permissions from the dev database
-- ====================================================================

INSERT INTO `Permission` (`id`, `name`) VALUES
(1, 'READ_USER'),
(2, 'CREATE_USER'),
(3, 'UPDATE_USER'),
(4, 'DELETE_USER'),
(5, 'READ_ROLE'),
(6, 'CREATE_ROLE'),
(7, 'UPDATE_ROLE'),
(8, 'DELETE_ROLE'),
(9, 'ASSIGN_ROLE'),
(10, 'READ_PROJECT'),
(11, 'CREATE_PROJECT'),
(12, 'UPDATE_PROJECT'),
(13, 'DELETE_PROJECT'),
(14, 'READ_CUSTOMER'),
(15, 'CREATE_CUSTOMER'),
(16, 'UPDATE_CUSTOMER'),
(17, 'DELETE_CUSTOMER'),
(18, 'READ_WORKSHEET'),
(19, 'CREATE_WORKSHEET'),
(20, 'UPDATE_WORKSHEET'),
(21, 'DELETE_WORKSHEET'),
(22, 'READ_SUPPLY'),
(23, 'CREATE_SUPPLY'),
(24, 'UPDATE_SUPPLY'),
(25, 'DELETE_SUPPLY'),
(30, 'READ_PERMISSION'),
(31, 'UPDATE_PERMISSION'),
(32, 'DELETE_PERMISSION'),
(33, 'CREATE_PERMISSION'),
(34, 'ADMIN_ACCESS'),
(47, 'READ_WAGE'),
(48, 'READ_NOTIFICATION'),
(49, 'CREATE_WAGE')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- ====================================================================
-- SECTION 2: Populate Role table
-- These are all the roles from the dev database
-- ====================================================================

INSERT INTO `Role` (`id`, `name`) VALUES
(1, 'ROOT'),
(2, 'Felhasználó'),
(3, 'Supervisor'),
(4, 'Titkárnő'),
(5, 'Archivált')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- ====================================================================
-- SECTION 3: Populate RolePermission table
-- Assign all permissions to ROOT role (as in dev)
-- ====================================================================

-- ROOT role gets all permissions
INSERT INTO `RolePermission` (`roleId`, `permissionId`) VALUES
(1, 1),   -- ROOT -> READ_USER
(1, 2),   -- ROOT -> CREATE_USER
(1, 3),   -- ROOT -> UPDATE_USER
(1, 4),   -- ROOT -> DELETE_USER
(1, 5),   -- ROOT -> READ_ROLE
(1, 6),   -- ROOT -> CREATE_ROLE
(1, 7),   -- ROOT -> UPDATE_ROLE
(1, 8),   -- ROOT -> DELETE_ROLE
(1, 9),   -- ROOT -> ASSIGN_ROLE
(1, 10),  -- ROOT -> READ_PROJECT
(1, 11),  -- ROOT -> CREATE_PROJECT
(1, 12),  -- ROOT -> UPDATE_PROJECT
(1, 13),  -- ROOT -> DELETE_PROJECT
(1, 14),  -- ROOT -> READ_CUSTOMER
(1, 15),  -- ROOT -> CREATE_CUSTOMER
(1, 16),  -- ROOT -> UPDATE_CUSTOMER
(1, 17),  -- ROOT -> DELETE_CUSTOMER
(1, 18),  -- ROOT -> READ_WORKSHEET
(1, 19),  -- ROOT -> CREATE_WORKSHEET
(1, 20),  -- ROOT -> UPDATE_WORKSHEET
(1, 21),  -- ROOT -> DELETE_WORKSHEET
(1, 22),  -- ROOT -> READ_SUPPLY
(1, 23),  -- ROOT -> CREATE_SUPPLY
(1, 24),  -- ROOT -> UPDATE_SUPPLY
(1, 25),  -- ROOT -> DELETE_SUPPLY
(1, 30),  -- ROOT -> READ_PERMISSION
(1, 31),  -- ROOT -> UPDATE_PERMISSION
(1, 32),  -- ROOT -> DELETE_PERMISSION
(1, 33),  -- ROOT -> CREATE_PERMISSION
(1, 34),  -- ROOT -> ADMIN_ACCESS
(1, 47),  -- ROOT -> READ_WAGE
(1, 48),  -- ROOT -> READ_NOTIFICATION
(1, 49)   -- ROOT -> CREATE_WAGE
ON DUPLICATE KEY UPDATE roleId = VALUES(roleId);

-- You can add permissions for other roles here if needed
-- For example, basic permissions for Felhasználó role:
INSERT INTO `RolePermission` (`roleId`, `permissionId`) VALUES
(2, 10),  -- Felhasználó -> READ_PROJECT
(2, 14),  -- Felhasználó -> READ_CUSTOMER
(2, 18),  -- Felhasználó -> READ_WORKSHEET
(2, 22),  -- Felhasználó -> READ_SUPPLY
(2, 48)   -- Felhasználó -> READ_NOTIFICATION
ON DUPLICATE KEY UPDATE roleId = VALUES(roleId);

-- ====================================================================
-- SECTION 4: Assign ROOT role to LZsolt user
-- IMPORTANT: LZsolt has Azonosito=2 in staging (as specified)
-- ====================================================================

-- First, verify that LZsolt exists with Azonosito=2
-- If not, this will fail gracefully due to foreign key constraint
INSERT INTO `KezeloRole` (`kezeloId`, `roleId`) VALUES
(2, 1)  -- Assign ROOT role (id=1) to LZsolt (Azonosito=2)
ON DUPLICATE KEY UPDATE roleId = VALUES(roleId);

-- ====================================================================
-- SECTION 5: Additional user role assignments (optional)
-- Add other users to appropriate roles as needed
-- ====================================================================

-- Example: If you have other users that need role assignments, add them here
-- INSERT INTO `KezeloRole` (`kezeloId`, `roleId`) VALUES
-- (3, 2),  -- User with Azonosito=3 gets Felhasználó role
-- (4, 3)   -- User with Azonosito=4 gets Supervisor role
-- ON DUPLICATE KEY UPDATE roleId = VALUES(roleId);

-- ====================================================================
-- SECTION 6: Entity-level permissions (if needed)
-- Currently no entity-level permissions in dev for LZsolt
-- Add specific entity permissions here if required
-- ====================================================================

-- Example: Grant specific project permissions to a user
-- INSERT INTO `EntityPermission` (`userId`, `entityId`, `entityType`, `permission`) VALUES
-- (2, 100, 'PROJECT', 'FULL_ACCESS')
-- ON DUPLICATE KEY UPDATE permission = VALUES(permission);

-- ====================================================================
-- Re-enable foreign key checks
-- ====================================================================


-- ====================================================================
-- VERIFICATION QUERIES
-- Run these after migration to verify data integrity
-- ====================================================================

-- Verify LZsolt has ROOT role:
-- SELECT k.Azonosito, k.User_name, r.name as role_name 
-- FROM Kezelo k 
-- JOIN KezeloRole kr ON k.Azonosito = kr.kezeloId 
-- JOIN Role r ON kr.roleId = r.id 
-- WHERE k.User_name = 'LZsolt';

-- Verify ROOT role has all permissions:
-- SELECT COUNT(*) as permission_count FROM RolePermission WHERE roleId = 1;
-- Should return 33 permissions

-- Verify all permissions are loaded:
-- SELECT COUNT(*) as total_permissions FROM Permission;
-- Should return 33 permissions

-- Verify all roles are loaded:
-- SELECT * FROM Role ORDER BY id;

-- ====================================================================
-- IMPORTANT NOTES:
-- 1. This script assumes LZsolt exists in staging with Azonosito=2
-- 2. If LZsolt doesn't exist or has a different ID, update line 136
-- 3. The script uses INSERT ... ON DUPLICATE KEY UPDATE to be idempotent
-- 4. You can run this script multiple times safely
-- ====================================================================