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';
