-- Database Seeding Script for Beszerzes Project Management System
-- This script creates essential roles, permissions, and user assignments
-- Run this script on production database after migrations

-- 1. Insert basic roles
INSERT IGNORE INTO Role (name) VALUES
('ROOT'),
('Felhasználó');

-- 2. Insert permissions
INSERT IGNORE INTO Permission (name) VALUES
-- Customer permissions
('READ_CUSTOMER'),
('CREATE_CUSTOMER'),
('UPDATE_CUSTOMER'),
('DELETE_CUSTOMER'),

-- Project permissions
('READ_PROJECT'),
('CREATE_PROJECT'),
('UPDATE_PROJECT'),
('DELETE_PROJECT'),

-- Admin permissions
('ADMIN_ACCESS'),
('READ_USER'),
('CREATE_USER'),
('UPDATE_USER'),
('DELETE_USER'),
('READ_ROLE'),
('CREATE_ROLE'),
('UPDATE_ROLE'),
('DELETE_ROLE'),
('READ_PERMISSION'),
('CREATE_PERMISSION'),
('UPDATE_PERMISSION'),
('DELETE_PERMISSION'),

-- Worksheet permissions
('READ_WORKSHEET'),
('CREATE_WORKSHEET'),
('UPDATE_WORKSHEET'),
('DELETE_WORKSHEET'),

-- Financial permissions
('READ_FINANCE'),
('CREATE_FINANCE'),
('UPDATE_FINANCE'),
('DELETE_FINANCE');

-- 3. Assign all permissions to ROOT role
INSERT IGNORE INTO RolePermission (roleId, permissionId)
SELECT r.id, p.id
FROM Role r, Permission p
WHERE r.name = 'ROOT';

-- 4. Assign basic read permissions to Felhasználó role
INSERT IGNORE INTO RolePermission (roleId, permissionId)
SELECT r.id, p.id
FROM Role r, Permission p
WHERE r.name = 'Felhasználó'
AND p.name IN ('READ_CUSTOMER', 'READ_PROJECT', 'READ_WORKSHEET');

-- 5. Assign ROOT role to admin user Lzsolt (Azonosito = 6)
INSERT IGNORE INTO KezeloRole (kezeloId, roleId)
SELECT 6, r.id
FROM Role r
WHERE r.name = 'ROOT';

-- Verify the seeding was successful
SELECT 'Roles created:' as message;
SELECT id, name FROM Role ORDER BY name;

SELECT 'Permissions created:' as message;
SELECT COUNT(*) as permission_count FROM Permission;

SELECT 'User Lzsolt roles:' as message;
SELECT k.User_name, k.Teljes_nev, 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.Azonosito = 6;

SELECT 'ROOT role permissions count:' as message;
SELECT COUNT(*) as permission_count
FROM RolePermission rp
JOIN Role r ON rp.roleId = r.id
WHERE r.name = 'ROOT';