-- Add permissions system to client portal

-- Add permissions column to tblclient
ALTER TABLE tblclient 
ADD COLUMN permissions TEXT NULL COMMENT 'JSON array of allowed modules',
ADD COLUMN userRole ENUM('admin','user') DEFAULT 'user',
ADD COLUMN createdByClientId INT NULL COMMENT 'Which client admin created this user';

-- Create client users table for multiple users per client
CREATE TABLE IF NOT EXISTS tblclientusers (
    userId INT AUTO_INCREMENT PRIMARY KEY,
    clientId INT NOT NULL,
    userName VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    userRole ENUM('admin','user') DEFAULT 'user',
    permissions TEXT NULL COMMENT 'JSON array of allowed modules: enrollment, calls, reimbursement, tasks, documents, reports',
    isActive ENUM('Y','N') DEFAULT 'Y',
    createdDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    createdBy INT NULL,
    modifiedDate DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    modifiedBy INT NULL,
    FOREIGN KEY (clientId) REFERENCES tblclient(clientId) ON DELETE CASCADE,
    INDEX idx_email (email),
    INDEX idx_clientId (clientId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Update tblclientotp to support client users
ALTER TABLE tblclientotp
ADD COLUMN userId INT NULL COMMENT 'For client users',
ADD INDEX idx_userId (userId);

-- Sample permissions structure (JSON):
-- {
--   "enrollment": true,
--   "calls": true,
--   "reimbursement": true,
--   "tasks": false,
--   "documents": true,
--   "reports": false
-- }
