-- Client Portal Database Setup
-- Run this SQL to create necessary tables for client access

-- Table for client users
CREATE TABLE IF NOT EXISTS `tblclient` (
  `clientId` int(11) NOT NULL AUTO_INCREMENT,
  `clientName` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `companyName` varchar(255) DEFAULT NULL,
  `pspId` int(11) NOT NULL COMMENT 'PSP ID to filter patient data',
  `userRole` enum('admin','user') DEFAULT 'user' COMMENT 'User role for permissions',
  `permissions` TEXT DEFAULT NULL COMMENT 'JSON encoded module permissions',
  `isActive` enum('Y','N') DEFAULT 'Y',
  `createdDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `createdBy` int(11) DEFAULT NULL,
  `modifiedDate` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `modifiedBy` int(11) DEFAULT NULL,
  PRIMARY KEY (`clientId`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table for client OTP authentication
CREATE TABLE IF NOT EXISTS `tblclientotp` (
  `otpId` int(11) NOT NULL AUTO_INCREMENT,
  `clientId` int(11) NOT NULL,
  `otp` varchar(10) NOT NULL,
  `otpFlag` tinyint(1) DEFAULT 0 COMMENT '0=unused, 1=used',
  `createdDate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`otpId`),
  KEY `clientId` (`clientId`),
  FOREIGN KEY (`clientId`) REFERENCES `tblclient`(`clientId`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table for client activity logs
CREATE TABLE IF NOT EXISTS `tblclientactivity` (
  `activityId` int(11) NOT NULL AUTO_INCREMENT,
  `clientId` int(11) NOT NULL,
  `activityType` varchar(100) NOT NULL COMMENT 'LOGIN, ADD_PATIENT, EDIT_PATIENT, etc.',
  `description` text DEFAULT NULL,
  `ipAddress` varchar(45) DEFAULT NULL,
  `createdDate` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`activityId`),
  KEY `clientId` (`clientId`),
  FOREIGN KEY (`clientId`) REFERENCES `tblclient`(`clientId`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Sample client data (update with actual values)
INSERT INTO `tblclient` (`clientName`, `email`, `companyName`, `pspId`, `isActive`) VALUES
('Demo Client', 'client@example.com', 'Demo Company', 1, 'Y');

-- Add index to tblpatientdetails for better performance
ALTER TABLE `tblpatientdetails` 
ADD INDEX `idx_pspId` (`pspId`);

-- Add client tracking columns to existing tables (optional)
ALTER TABLE `tblpatient` 
ADD COLUMN `createdByClient` int(11) DEFAULT NULL COMMENT 'Client ID if created by client portal',
ADD INDEX `idx_createdByClient` (`createdByClient`);
