-- Add view_product_catalog permission
-- SQL Script to add permission and assign to roles

-- 1. Insert permission (if not exists)
INSERT INTO permissions (name, description, created_at)
SELECT 'view_product_catalog', 'Akses untuk melihat dan mencari produk dalam katalog', NOW()
WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE name = 'view_product_catalog');

-- 2. Get permission ID
SET @permission_id = (SELECT id FROM permissions WHERE name = 'view_product_catalog');

-- 3. Assign to roles (if not already assigned)
INSERT INTO role_permissions (role, permission_id, created_at)
SELECT 'administrator', @permission_id, NOW()
WHERE NOT EXISTS (SELECT 1 FROM role_permissions WHERE role = 'administrator' AND permission_id = @permission_id);

INSERT INTO role_permissions (role, permission_id, created_at)
SELECT 'manager', @permission_id, NOW()
WHERE NOT EXISTS (SELECT 1 FROM role_permissions WHERE role = 'manager' AND permission_id = @permission_id);

INSERT INTO role_permissions (role, permission_id, created_at)
SELECT 'supervisor', @permission_id, NOW()
WHERE NOT EXISTS (SELECT 1 FROM role_permissions WHERE role = 'supervisor' AND permission_id = @permission_id);

INSERT INTO role_permissions (role, permission_id, created_at)
SELECT 'qc_staff', @permission_id, NOW()
WHERE NOT EXISTS (SELECT 1 FROM role_permissions WHERE role = 'qc_staff' AND permission_id = @permission_id);

-- Verify
SELECT 
    p.name as permission, 
    p.description, 
    GROUP_CONCAT(rp.role SEPARATOR ', ') as assigned_to_roles
FROM permissions p
LEFT JOIN role_permissions rp ON p.id = rp.permission_id
WHERE p.name = 'view_product_catalog'
GROUP BY p.id;