354 lines
9.9 KiB
MySQL
354 lines
9.9 KiB
MySQL
|
-- ============================================================================
|
||
|
|
-- Quick Test Script for Multi-Client Hierarchy
|
||
|
|
-- Run this after migration to test the new features
|
||
|
|
-- ============================================================================
|
||
|
|
|
||
|
|
-- Prerequisites: Database migration already done (tables created)
|
||
|
|
|
||
|
|
-- ============================================================================
|
||
|
|
-- STEP 1: Create Super Admin
|
||
|
|
-- ============================================================================
|
||
|
|
UPDATE users
|
||
|
|
SET is_super_admin = true
|
||
|
|
WHERE id = 1; -- Ganti dengan ID admin Anda
|
||
|
|
|
||
|
|
SELECT id, username, email, is_super_admin
|
||
|
|
FROM users
|
||
|
|
WHERE is_super_admin = true;
|
||
|
|
|
||
|
|
-- ============================================================================
|
||
|
|
-- STEP 2: Create Parent Client (Polda Metro)
|
||
|
|
-- ============================================================================
|
||
|
|
DO $$
|
||
|
|
DECLARE
|
||
|
|
v_parent_id UUID;
|
||
|
|
BEGIN
|
||
|
|
-- Create parent
|
||
|
|
INSERT INTO clients (id, name, description, client_type, is_active, created_at, updated_at)
|
||
|
|
VALUES (
|
||
|
|
gen_random_uuid(),
|
||
|
|
'Polda Metro Jaya',
|
||
|
|
'Parent client untuk wilayah Jakarta',
|
||
|
|
'parent_client',
|
||
|
|
true,
|
||
|
|
NOW(),
|
||
|
|
NOW()
|
||
|
|
)
|
||
|
|
RETURNING id INTO v_parent_id;
|
||
|
|
|
||
|
|
RAISE NOTICE 'Parent Client ID: %', v_parent_id;
|
||
|
|
|
||
|
|
-- Store for next steps
|
||
|
|
CREATE TEMP TABLE IF NOT EXISTS temp_client_ids (
|
||
|
|
client_type VARCHAR,
|
||
|
|
client_id UUID,
|
||
|
|
client_name VARCHAR
|
||
|
|
);
|
||
|
|
|
||
|
|
INSERT INTO temp_client_ids VALUES ('parent', v_parent_id, 'Polda Metro Jaya');
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
-- ============================================================================
|
||
|
|
-- STEP 3: Create Sub-Clients (Polres)
|
||
|
|
-- ============================================================================
|
||
|
|
DO $$
|
||
|
|
DECLARE
|
||
|
|
v_parent_id UUID;
|
||
|
|
v_sub1_id UUID;
|
||
|
|
v_sub2_id UUID;
|
||
|
|
v_sub3_id UUID;
|
||
|
|
BEGIN
|
||
|
|
-- Get parent ID
|
||
|
|
SELECT client_id INTO v_parent_id
|
||
|
|
FROM temp_client_ids
|
||
|
|
WHERE client_type = 'parent';
|
||
|
|
|
||
|
|
-- Create Polres Jakarta Pusat
|
||
|
|
INSERT INTO clients (id, name, description, client_type, parent_client_id, max_users, is_active, created_at, updated_at)
|
||
|
|
VALUES (
|
||
|
|
gen_random_uuid(),
|
||
|
|
'Polres Jakarta Pusat',
|
||
|
|
'Sub-client Jakarta Pusat',
|
||
|
|
'sub_client',
|
||
|
|
v_parent_id,
|
||
|
|
100,
|
||
|
|
true,
|
||
|
|
NOW(),
|
||
|
|
NOW()
|
||
|
|
)
|
||
|
|
RETURNING id INTO v_sub1_id;
|
||
|
|
|
||
|
|
-- Create Polres Jakarta Barat
|
||
|
|
INSERT INTO clients (id, name, description, client_type, parent_client_id, max_users, is_active, created_at, updated_at)
|
||
|
|
VALUES (
|
||
|
|
gen_random_uuid(),
|
||
|
|
'Polres Jakarta Barat',
|
||
|
|
'Sub-client Jakarta Barat',
|
||
|
|
'sub_client',
|
||
|
|
v_parent_id,
|
||
|
|
150,
|
||
|
|
true,
|
||
|
|
NOW(),
|
||
|
|
NOW()
|
||
|
|
)
|
||
|
|
RETURNING id INTO v_sub2_id;
|
||
|
|
|
||
|
|
-- Create Polres Jakarta Selatan
|
||
|
|
INSERT INTO clients (id, name, description, client_type, parent_client_id, max_users, is_active, created_at, updated_at)
|
||
|
|
VALUES (
|
||
|
|
gen_random_uuid(),
|
||
|
|
'Polres Jakarta Selatan',
|
||
|
|
'Sub-client Jakarta Selatan',
|
||
|
|
'sub_client',
|
||
|
|
v_parent_id,
|
||
|
|
120,
|
||
|
|
true,
|
||
|
|
NOW(),
|
||
|
|
NOW()
|
||
|
|
)
|
||
|
|
RETURNING id INTO v_sub3_id;
|
||
|
|
|
||
|
|
INSERT INTO temp_client_ids VALUES ('sub1', v_sub1_id, 'Polres Jakarta Pusat');
|
||
|
|
INSERT INTO temp_client_ids VALUES ('sub2', v_sub2_id, 'Polres Jakarta Barat');
|
||
|
|
INSERT INTO temp_client_ids VALUES ('sub3', v_sub3_id, 'Polres Jakarta Selatan');
|
||
|
|
|
||
|
|
RAISE NOTICE 'Sub-Client 1 ID: %', v_sub1_id;
|
||
|
|
RAISE NOTICE 'Sub-Client 2 ID: %', v_sub2_id;
|
||
|
|
RAISE NOTICE 'Sub-Client 3 ID: %', v_sub3_id;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
-- ============================================================================
|
||
|
|
-- STEP 4: View Client Hierarchy
|
||
|
|
-- ============================================================================
|
||
|
|
SELECT
|
||
|
|
c.id,
|
||
|
|
c.name,
|
||
|
|
c.client_type,
|
||
|
|
c.parent_client_id,
|
||
|
|
pc.name as parent_name,
|
||
|
|
(SELECT COUNT(*) FROM clients WHERE parent_client_id = c.id) as sub_client_count
|
||
|
|
FROM clients c
|
||
|
|
LEFT JOIN clients pc ON c.parent_client_id = pc.id
|
||
|
|
ORDER BY c.client_type, c.name;
|
||
|
|
|
||
|
|
-- ============================================================================
|
||
|
|
-- STEP 5: Create Manager User with Multi-Client Access
|
||
|
|
-- ============================================================================
|
||
|
|
DO $$
|
||
|
|
DECLARE
|
||
|
|
v_manager_id INT;
|
||
|
|
v_parent_id UUID;
|
||
|
|
BEGIN
|
||
|
|
-- Create manager user (adjust as needed)
|
||
|
|
INSERT INTO users (
|
||
|
|
username,
|
||
|
|
email,
|
||
|
|
fullname,
|
||
|
|
user_role_id,
|
||
|
|
user_level_id,
|
||
|
|
is_super_admin,
|
||
|
|
is_active,
|
||
|
|
created_at,
|
||
|
|
updated_at
|
||
|
|
)
|
||
|
|
VALUES (
|
||
|
|
'regional.manager',
|
||
|
|
'manager@example.com',
|
||
|
|
'Regional Manager',
|
||
|
|
1, -- Adjust role ID
|
||
|
|
1, -- Adjust level ID
|
||
|
|
false,
|
||
|
|
true,
|
||
|
|
NOW(),
|
||
|
|
NOW()
|
||
|
|
)
|
||
|
|
RETURNING id INTO v_manager_id;
|
||
|
|
|
||
|
|
-- Get parent client ID
|
||
|
|
SELECT client_id INTO v_parent_id
|
||
|
|
FROM temp_client_ids
|
||
|
|
WHERE client_type = 'parent';
|
||
|
|
|
||
|
|
-- Grant access to parent with sub-clients included
|
||
|
|
INSERT INTO user_client_access (
|
||
|
|
user_id,
|
||
|
|
client_id,
|
||
|
|
access_type,
|
||
|
|
can_manage,
|
||
|
|
can_delegate,
|
||
|
|
include_sub_clients,
|
||
|
|
is_active,
|
||
|
|
created_at,
|
||
|
|
updated_at
|
||
|
|
)
|
||
|
|
VALUES (
|
||
|
|
v_manager_id,
|
||
|
|
v_parent_id,
|
||
|
|
'admin',
|
||
|
|
true,
|
||
|
|
true,
|
||
|
|
true, -- ✨ This gives access to all sub-clients automatically!
|
||
|
|
true,
|
||
|
|
NOW(),
|
||
|
|
NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
RAISE NOTICE 'Manager User ID: %', v_manager_id;
|
||
|
|
RAISE NOTICE 'Manager has access to parent and ALL sub-clients';
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
-- ============================================================================
|
||
|
|
-- STEP 6: Create Regular User (Single Client Access)
|
||
|
|
-- ============================================================================
|
||
|
|
DO $$
|
||
|
|
DECLARE
|
||
|
|
v_user_id INT;
|
||
|
|
v_sub1_id UUID;
|
||
|
|
BEGIN
|
||
|
|
-- Get sub-client ID
|
||
|
|
SELECT client_id INTO v_sub1_id
|
||
|
|
FROM temp_client_ids
|
||
|
|
WHERE client_type = 'sub1';
|
||
|
|
|
||
|
|
-- Create regular user
|
||
|
|
INSERT INTO users (
|
||
|
|
username,
|
||
|
|
email,
|
||
|
|
fullname,
|
||
|
|
user_role_id,
|
||
|
|
user_level_id,
|
||
|
|
client_id, -- Primary client
|
||
|
|
is_super_admin,
|
||
|
|
is_active,
|
||
|
|
created_at,
|
||
|
|
updated_at
|
||
|
|
)
|
||
|
|
VALUES (
|
||
|
|
'regular.user',
|
||
|
|
'user@example.com',
|
||
|
|
'Regular User',
|
||
|
|
2, -- Adjust role ID
|
||
|
|
2, -- Adjust level ID
|
||
|
|
v_sub1_id,
|
||
|
|
false,
|
||
|
|
true,
|
||
|
|
NOW(),
|
||
|
|
NOW()
|
||
|
|
)
|
||
|
|
RETURNING id INTO v_user_id;
|
||
|
|
|
||
|
|
RAISE NOTICE 'Regular User ID: % (only access to %)', v_user_id, v_sub1_id;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
-- ============================================================================
|
||
|
|
-- STEP 7: Verify Setup
|
||
|
|
-- ============================================================================
|
||
|
|
|
||
|
|
-- Show all clients with hierarchy
|
||
|
|
SELECT
|
||
|
|
CASE
|
||
|
|
WHEN c.parent_client_id IS NULL THEN c.name
|
||
|
|
ELSE ' └─ ' || c.name
|
||
|
|
END as hierarchy,
|
||
|
|
c.client_type,
|
||
|
|
c.max_users,
|
||
|
|
(SELECT COUNT(*) FROM users WHERE client_id = c.id) as user_count,
|
||
|
|
c.is_active
|
||
|
|
FROM clients c
|
||
|
|
ORDER BY COALESCE(c.parent_client_id, c.id), c.name;
|
||
|
|
|
||
|
|
-- Show user access summary
|
||
|
|
SELECT
|
||
|
|
u.id,
|
||
|
|
u.username,
|
||
|
|
u.fullname,
|
||
|
|
u.is_super_admin,
|
||
|
|
u.client_id as primary_client_id,
|
||
|
|
c.name as primary_client_name,
|
||
|
|
(
|
||
|
|
SELECT COUNT(*)
|
||
|
|
FROM user_client_access
|
||
|
|
WHERE user_id = u.id AND is_active = true
|
||
|
|
) as multi_client_access_count
|
||
|
|
FROM users u
|
||
|
|
LEFT JOIN clients c ON u.client_id = c.id
|
||
|
|
WHERE u.is_active = true
|
||
|
|
ORDER BY u.is_super_admin DESC, u.id;
|
||
|
|
|
||
|
|
-- Show detailed multi-client access
|
||
|
|
SELECT
|
||
|
|
u.username,
|
||
|
|
c.name as client_name,
|
||
|
|
c.client_type,
|
||
|
|
uca.access_type,
|
||
|
|
uca.can_manage,
|
||
|
|
uca.can_delegate,
|
||
|
|
uca.include_sub_clients,
|
||
|
|
CASE
|
||
|
|
WHEN uca.include_sub_clients = true
|
||
|
|
THEN (SELECT COUNT(*) FROM clients WHERE parent_client_id = c.id AND is_active = true)
|
||
|
|
ELSE 0
|
||
|
|
END as accessible_sub_clients
|
||
|
|
FROM user_client_access uca
|
||
|
|
JOIN users u ON uca.user_id = u.id
|
||
|
|
JOIN clients c ON uca.client_id = c.id
|
||
|
|
WHERE uca.is_active = true
|
||
|
|
ORDER BY u.username, c.name;
|
||
|
|
|
||
|
|
-- ============================================================================
|
||
|
|
-- STEP 8: Test Queries
|
||
|
|
-- ============================================================================
|
||
|
|
|
||
|
|
-- Test: Get accessible client IDs for manager
|
||
|
|
-- (This simulates what the application does)
|
||
|
|
WITH RECURSIVE accessible_clients AS (
|
||
|
|
-- Direct access
|
||
|
|
SELECT uca.client_id, uca.include_sub_clients
|
||
|
|
FROM user_client_access uca
|
||
|
|
WHERE uca.user_id = (SELECT id FROM users WHERE username = 'regional.manager' LIMIT 1)
|
||
|
|
AND uca.is_active = true
|
||
|
|
|
||
|
|
UNION
|
||
|
|
|
||
|
|
-- Sub-clients if include_sub_clients = true
|
||
|
|
SELECT c.id, false
|
||
|
|
FROM clients c
|
||
|
|
INNER JOIN accessible_clients ac ON c.parent_client_id = ac.client_id
|
||
|
|
WHERE ac.include_sub_clients = true
|
||
|
|
AND c.is_active = true
|
||
|
|
)
|
||
|
|
SELECT
|
||
|
|
c.id,
|
||
|
|
c.name,
|
||
|
|
c.client_type
|
||
|
|
FROM accessible_clients ac
|
||
|
|
JOIN clients c ON ac.client_id = c.id
|
||
|
|
ORDER BY c.client_type, c.name;
|
||
|
|
|
||
|
|
-- ============================================================================
|
||
|
|
-- Cleanup (Optional)
|
||
|
|
-- ============================================================================
|
||
|
|
-- DROP TABLE IF EXISTS temp_client_ids;
|
||
|
|
|
||
|
|
-- ============================================================================
|
||
|
|
-- NOTES
|
||
|
|
-- ============================================================================
|
||
|
|
/*
|
||
|
|
After running this script, you should have:
|
||
|
|
|
||
|
|
1. Super Admin user (ID 1)
|
||
|
|
2. Parent Client: "Polda Metro Jaya"
|
||
|
|
3. Sub-Clients:
|
||
|
|
- Polres Jakarta Pusat
|
||
|
|
- Polres Jakarta Barat
|
||
|
|
- Polres Jakarta Selatan
|
||
|
|
4. Manager User with access to ALL clients (via include_sub_clients)
|
||
|
|
5. Regular User with access to 1 client only
|
||
|
|
|
||
|
|
Next steps:
|
||
|
|
- Test via API endpoints
|
||
|
|
- Create articles in different clients
|
||
|
|
- Test filtering and access control
|
||
|
|
- Monitor performance
|
||
|
|
*/
|