-- Migration script to add permissions system to existing tblclient table
-- Run this if you already have the client portal installed

-- Add userRole column if it doesn't exist
ALTER TABLE `tblclient` 
ADD COLUMN IF NOT EXISTS `userRole` enum('admin','user') DEFAULT 'user' COMMENT 'User role for permissions' 
AFTER `pspId`;

-- Add permissions column if it doesn't exist
ALTER TABLE `tblclient` 
ADD COLUMN IF NOT EXISTS `permissions` TEXT DEFAULT NULL COMMENT 'JSON encoded module permissions' 
AFTER `userRole`;

-- Set all existing users as admin with full permissions
UPDATE `tblclient` 
SET `userRole` = 'admin',
    `permissions` = '{"enrollment":true,"calls":true,"reimbursement":true,"tasks":true,"documents":true,"reports":true}'
WHERE `userRole` IS NULL OR `permissions` IS NULL;

-- Sample: Create a regular user with limited permissions
-- INSERT INTO `tblclient` (clientName, email, pspId, userRole, permissions, isActive, createdBy) 
-- VALUES ('John Doe', 'john@example.com', 1, 'user', 
--         '{"enrollment":true,"calls":true,"reimbursement":false,"tasks":false,"documents":false,"reports":false}', 
--         'Y', 1);
