56 lines
2.4 KiB
SQL
56 lines
2.4 KiB
SQL
CREATE TABLE IF NOT EXISTS user_permissions (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT NOT NULL,
|
|
page VARCHAR(50) NOT NULL,
|
|
can_view TINYINT(1) DEFAULT 0,
|
|
can_add TINYINT(1) DEFAULT 0,
|
|
can_edit TINYINT(1) DEFAULT 0,
|
|
can_delete TINYINT(1) DEFAULT 0,
|
|
UNIQUE KEY (user_id, page),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Seed existing users with default permissions based on their current roles
|
|
-- For Super Admins: everything
|
|
INSERT IGNORE INTO user_permissions (user_id, page, can_view, can_add, can_edit, can_delete)
|
|
SELECT id, p.page, 1, 1, 1, 1
|
|
FROM users
|
|
CROSS JOIN (
|
|
SELECT 'admin.php' as page UNION SELECT 'pos.php' UNION SELECT 'orders.php' UNION SELECT 'lab.php' UNION
|
|
SELECT 'customers.php' UNION SELECT 'reports.php' UNION SELECT 'items.php' UNION SELECT 'branches.php' UNION
|
|
SELECT 'users.php' UNION SELECT 'profile.php' UNION SELECT 'company_profile.php' UNION
|
|
SELECT 'order_details.php' UNION SELECT 'receipt.php'
|
|
) p
|
|
WHERE role = 'super_admin';
|
|
|
|
-- For Branch Managers: most things, but not company-wide settings or user management (usually)
|
|
-- Following the existing role logic from header.php
|
|
INSERT IGNORE INTO user_permissions (user_id, page, can_view, can_add, can_edit, can_delete)
|
|
SELECT id, p.page, 1, 1, 1, 1
|
|
FROM users
|
|
CROSS JOIN (
|
|
SELECT 'admin.php' as page UNION SELECT 'pos.php' UNION SELECT 'orders.php' UNION SELECT 'lab.php' UNION
|
|
SELECT 'customers.php' UNION SELECT 'reports.php' UNION SELECT 'items.php' UNION SELECT 'branches.php' UNION
|
|
SELECT 'profile.php' UNION SELECT 'order_details.php' UNION SELECT 'receipt.php'
|
|
) p
|
|
WHERE role = 'branch_manager';
|
|
|
|
-- For Cashiers:
|
|
INSERT IGNORE INTO user_permissions (user_id, page, can_view, can_add, can_edit, can_delete)
|
|
SELECT id, p.page, 1, 1, 1, 1
|
|
FROM users
|
|
CROSS JOIN (
|
|
SELECT 'admin.php' as page UNION SELECT 'pos.php' UNION SELECT 'orders.php' UNION SELECT 'lab.php' UNION
|
|
SELECT 'customers.php' UNION SELECT 'profile.php' UNION SELECT 'order_details.php' UNION SELECT 'receipt.php'
|
|
) p
|
|
WHERE role = 'cashier';
|
|
|
|
-- For Limited Viewers:
|
|
INSERT IGNORE INTO user_permissions (user_id, page, can_view, can_add, can_edit, can_delete)
|
|
SELECT id, p.page, 1, 0, 0, 0
|
|
FROM users
|
|
CROSS JOIN (
|
|
SELECT 'admin.php' as page UNION SELECT 'profile.php'
|
|
) p
|
|
WHERE role = 'limited_viewer';
|