### Step 34

ALTER TABLE invms_store_items
ADD COLUMN minimumStock DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER ItemStoreQty,
ADD COLUMN reOrderQuantity DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER minimumStock;

ALTER TABLE hr_leave_types
ADD COLUMN effective_from DATE NULL AFTER description,
ADD COLUMN effective_to DATE NULL AFTER effective_from;

### Step 35(Removed)
### Step 36

-- Drop table if it exists
DROP TABLE IF EXISTS `tbl_supplier_invoices`;

-- --------------------------------------------------------
-- Table structure for table `tbl_supplier_invoices`
-- --------------------------------------------------------
CREATE TABLE `tbl_supplier_invoices` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `invoiceTypeID` INT(11) DEFAULT 1,
  `supplierID` INT(11) DEFAULT NULL,
  `invoiceDate` DATE DEFAULT NULL,
  `invoiceNumber` VARCHAR(50) DEFAULT NULL,
  `invoiceNarration` TEXT DEFAULT NULL,
  `invoiceAmount` DECIMAL(20,2) DEFAULT 0.00,
  `debitAccount` VARCHAR(20) DEFAULT NULL,
  `creditAccount` VARCHAR(20) DEFAULT NULL,
  `dateLogged` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP(),
  `UID` INT(11) DEFAULT NULL,
  `UIP` VARCHAR(250) DEFAULT NULL,
  `UPC` VARCHAR(250) DEFAULT NULL,
  `isPaid` INT(11) DEFAULT 0,
  `isActive` INT(11) NOT NULL DEFAULT 1,
  `amountPaid` DECIMAL(22,2) DEFAULT 0.00,
  `balancePayable` DECIMAL(22,2) DEFAULT 0.00,
  `monthID` INT(11) DEFAULT NULL,
  `yearID` INT(11) DEFAULT NULL,
  `isConfirmed` INT(11) DEFAULT 0,
  `expensePayerID` INT(11) DEFAULT NULL,
  `dateAllocated` DATETIME DEFAULT NULL,
  `Remarks` TEXT DEFAULT NULL,
  `WriteOffAmnt` DECIMAL(20,2) DEFAULT 0.00,
  `AllocatedByUID` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Trigger for table `tbl_supplier_invoices`
-- --------------------------------------------------------
DELIMITER $$
CREATE TRIGGER `acc_supplier_invoices_AFTER_UPDATE` 
AFTER UPDATE ON `tbl_supplier_invoices` 
FOR EACH ROW 
BEGIN
    DECLARE postingType INT;
    DECLARE DBpostDate DATE;
    DECLARE realPostDate DATE;

    -- Get posting setup
    SELECT pickToday, postingDate 
    INTO postingType, DBpostDate 
    FROM acc_postingdate_setup 
    WHERE systemModule = 'EXPENSES';

    -- Determine posting date
    CASE postingType
        WHEN 1 THEN
            SET realPostDate = CURDATE();
        WHEN 0 THEN
            SET realPostDate = DBpostDate;
        ELSE
            SET realPostDate = CURDATE();
    END CASE;

    -- If invoice is confirmed, create GL entries
    IF NEW.isConfirmed = 1 THEN
        INSERT INTO `acc_supplier_invoices_gl_entries`
            (`supplierID`, `referenceNumber`, `glAccountNo`, `postingDate`, 
             `amount`, `debitAmount`, `description`, `invoiceID`, `txnDate`, `userId`)
        VALUES
            (NEW.supplierID, NEW.invoiceNumber, NEW.debitAccount, realPostDate, 
             NEW.invoiceAmount, NEW.invoiceAmount, NEW.invoiceNarration, 
             NEW.id, NEW.invoiceDate, NEW.UID);

        INSERT INTO `acc_supplier_invoices_gl_entries`
            (`supplierID`, `referenceNumber`, `glAccountNo`, `postingDate`, 
             `amount`, `creditAmount`, `description`, `invoiceID`, `txnDate`, `userId`)
        VALUES
            (NEW.supplierID, NEW.invoiceNumber, NEW.creditAccount, realPostDate, 
             NEW.invoiceAmount, NEW.invoiceAmount, NEW.invoiceNarration, 
             NEW.id, NEW.invoiceDate, NEW.UID);
    END IF;
END$$
DELIMITER ;

COMMIT;

### Step 37

-- --------------------------------------------------------
-- Table structure for table `tbl_patient_feedback`
-- --------------------------------------------------------

CREATE TABLE `tbl_patient_feedback` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `dateVisited` DATE DEFAULT NULL,
  `emailID` VARCHAR(150) DEFAULT NULL,
  `phoneNumber` VARCHAR(40) DEFAULT NULL,
  `howDidYouHear` VARCHAR(100) DEFAULT NULL,
  `overallServices` VARCHAR(100) DEFAULT NULL,
  `whatCanWeImproveOn` VARCHAR(255) DEFAULT NULL,
  `UID` INT(11) DEFAULT NULL,
  `dateLogged` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_UID` (`UID`)
) ENGINE=InnoDB 
  DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_general_ci 
  ROW_FORMAT=DYNAMIC;

COMMIT;

### Step 38

ALTER TABLE invms_stkitem
ADD COLUMN pharmaPOSSellingPrice DECIMAL(18,2) NOT NULL DEFAULT 0.00
AFTER cashSellingPrice;

UPDATE invms_stkitem
SET pharmaPOSSellingPrice = cashSellingPrice;

### Step 39

DROP TABLE IF EXISTS `acc_topup_petty_cash`;
DELIMITER $$

CREATE TABLE `acc_topup_petty_cash` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `expenseTypeID` int(11) NOT NULL,
  `expenseTypeName` varchar(225) NOT NULL,
  `expenseToUID` varchar(120) NOT NULL DEFAULT '',
  `glAccountID` varchar(45) NOT NULL,
  `balancingGlAccount` varchar(45) DEFAULT NULL,
  `expenseActivityCategoryID` int(11) NOT NULL,
  `expenseAmount` decimal(18,2) NOT NULL,
  `createdByUID` int(11) NOT NULL,
  `dateAdded` timestamp NOT NULL DEFAULT current_timestamp(),
  `isConfirmed` int(11) NOT NULL DEFAULT 0,
  `isDeleted` int(11) NOT NULL DEFAULT 0,
  `budgetActivityID` int(11) NOT NULL,
  `txnDate` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
$$

CREATE TRIGGER `acc_topup_petty_cash_ONINSERT`
AFTER INSERT ON `acc_topup_petty_cash`
FOR EACH ROW
BEGIN
    DECLARE postingType INT;
    DECLARE DBpostDate DATE;
    DECLARE realPostDate DATE;

    -- Get posting setup for module EXPENSES
    SELECT pickToday, postingDate
    INTO postingType, DBpostDate
    FROM acc_postingdate_setup
    WHERE systemModule = 'EXPENSES';

    CASE postingType
        WHEN 1 THEN
            SET realPostDate = CURDATE();
        WHEN 0 THEN
            SET realPostDate = DBpostDate;
        ELSE
            SET realPostDate = CURDATE();
    END CASE;

    -- Debit entry
    INSERT INTO `acc_pettycash_gl_entries`
        (`glAccountNo`, `postingDate`, `expTypeId`, `expTypeName`, `expActivityId`, `amount`, `debitAmount`, `userId`)
    VALUES
        (NEW.glAccountID, realPostDate, NEW.expenseTypeID, NEW.expenseTypeName, NEW.expenseActivityCategoryID, NEW.expenseAmount, NEW.expenseAmount, NEW.createdByUID);

    -- Credit entry
    INSERT INTO `acc_pettycash_gl_entries`
        (`glAccountNo`, `postingDate`, `expTypeId`, `expTypeName`, `expActivityId`, `amount`, `creditAmount`, `userId`)
    VALUES
        (NEW.balancingGlAccount, realPostDate, NEW.expenseTypeID, NEW.expenseTypeName, NEW.expenseActivityCategoryID, NEW.expenseAmount, NEW.expenseAmount, NEW.createdByUID);

    -- Update imprest account balance
    UPDATE acc_imprest_account
    SET AmountToppedUp = NEW.expenseAmount,
        currentAmount = currentAmount + NEW.expenseAmount;
END$$
DELIMITER ;

COMMIT;

### Step 40

DROP TABLE IF EXISTS `acc_pettycash_gl_entries`;
DELIMITER $$

CREATE TABLE `acc_pettycash_gl_entries` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `glAccountNo` varchar(45) DEFAULT NULL,
  `postingDate` date DEFAULT NULL,
  `expTypeId` int(11) DEFAULT NULL,
  `expTypeName` varchar(255) DEFAULT NULL,
  `expActivityId` int(11) DEFAULT NULL,
  `amount` double(12,2) DEFAULT 0.00,
  `debitAmount` double(12,2) DEFAULT 0.00,
  `creditAmount` double(12,2) DEFAULT 0.00,
  `description` varchar(255) DEFAULT NULL,
  `accountNo` varchar(45) DEFAULT NULL,
  `userId` int(11) DEFAULT NULL,
  `dateAdded` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
$$

CREATE TRIGGER `acc_pettcash_gl_ONINSERT`
AFTER INSERT ON `acc_pettycash_gl_entries`
FOR EACH ROW
BEGIN
    INSERT INTO `acc_gl_entries`
    (
        `transactionNo`,
        `glAccountNo`,
        `postingDate`,
        `amount`,
        `debitAmount`,
        `creditAmount`,
        `description`,
        `transactionDate`,
        `systemModule`,
        `userId`,
        `userName`
    )
    VALUES
    (
        NEW.id,
        NEW.glAccountNo,
        NEW.postingDate,
        NEW.amount,
        NEW.debitAmount,
        NEW.creditAmount,
        'PETTY-CASH',
        NOW(),
        'PETTY-CASH',
        NEW.userId,
        'System'
    );
END$$
DELIMITER ;

COMMIT;

### Step 41-49

ALTER TABLE invms_stkitem 
ADD COLUMN `isSHAPay` TINYINT(1) NOT NULL DEFAULT 0 
AFTER `isActive`;

ALTER TABLE setup_procedures 
ADD COLUMN `isSHAPay` TINYINT(1) NOT NULL DEFAULT 0 
AFTER `isActive`;

ALTER TABLE lab_specimen_tests 
ADD COLUMN `isSHAPay` TINYINT(1) NOT NULL DEFAULT 0 
AFTER `isActive`;


ALTER TABLE `eval_patient_history`
ADD COLUMN `SOAPNotes` TEXT NULL AFTER `patientTreatmentPlan`;


ALTER TABLE inpatient_last_office 
MODIFY COLUMN DischargeNotes TEXT NULL;

ALTER TABLE inpatient_lastoffice_postmortem 
MODIFY COLUMN Notes TEXT NULL;


ALTER TABLE `tbl_registered_patients`
ADD COLUMN `isIncomingReferral` ENUM('Yes','No') DEFAULT 'No' AFTER `nearestschool`,
ADD COLUMN `referralFacility` TEXT NULL AFTER `isIncomingReferral`;


ALTER TABLE finance_bill_split_requests 
ADD COLUMN splitRemark VARCHAR(255) DEFAULT NULL;


ALTER TABLE visit_db ADD COLUMN hasRebateFlag TINYINT(1) DEFAULT 0;

### Step 50

CREATE TABLE tbl_tsheet_drug_prescriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    patientUID INT NOT NULL,
    visitCode VARCHAR(50) NOT NULL,
    facilityID INT NOT NULL,
    departmentID INT NOT NULL,
    schemeID INT DEFAULT 0,
    paymentOptionID INT DEFAULT 0,
    classificationID INT,
    drugID INT,
    drugName VARCHAR(255),
    dose VARCHAR(255),
    administrationRoute VARCHAR(100),
    datetime DATETIME,
    createdByUID INT,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    isSyncedToPharmacy TINYINT(1) DEFAULT 0,
    INDEX (patientUID),
    INDEX (visitCode)
)

### Step 51

CREATE TABLE `tbl_patient_sickoff` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `patientUID` INT(11) NOT NULL,
    `visitID` VARCHAR(255) NOT NULL,
    `leaveDays` INT(3) NOT NULL,
    `startDate` DATE NOT NULL,
    `endDate` DATE NOT NULL,
    `followupDate` DATE DEFAULT NULL,
    `otherInfo` TEXT,
    `createdBy` INT(11) NOT NULL,
    `createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

### Step 52

CREATE TABLE IF NOT EXISTS system_audit_trail (
  id INT AUTO_INCREMENT PRIMARY KEY,
  actionTaken VARCHAR(255) NOT NULL,
  actionBYUID INT NOT NULL,
  actionDate DATETIME DEFAULT CURRENT_TIMESTAMP,
  visitReference VARCHAR(50) DEFAULT NULL,
  ipAddress VARCHAR(50) DEFAULT NULL,
  browserInfo VARCHAR(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

### Step 53-60

ALTER TABLE billing_tbl_transactions 
ADD COLUMN paymentStatus TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0 = Pending, 1 = Completed' AFTER balanceDue;

UPDATE billing_tbl_transactions 
SET paymentStatus = 1;

ALTER TABLE billing_tbl_transactions 
ADD INDEX idx_paymentStatus (paymentStatus);

ALTER TABLE pharma_drug_sales_transactions 
ADD COLUMN paymentStatus TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0 = Pending, 1 = Completed' AFTER txnBalanceDue;

UPDATE pharma_drug_sales_transactions 
SET paymentStatus = 1;

ALTER TABLE pharma_drug_sales_transactions 
ADD COLUMN isDispensed TINYINT(1) NOT NULL DEFAULT 0 
COMMENT '0 = Not Dispensed, 1 = Dispensed' 
AFTER paymentStatus;

UPDATE pharma_drug_sales_transactions 
SET isDispensed = 1;

ALTER TABLE billing_direct_bill_txns 
ADD COLUMN paymentStatus TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0 = Pending, 1 = Completed' AFTER txnBalanceDue;


UPDATE billing_direct_bill_txns 
SET paymentStatus = 1;

### Step 61

ALTER TABLE `eval_patient_history` 
ADD COLUMN `ProblemListDiagnosis` TEXT NOT NULL DEFAULT 'NA',
ADD COLUMN `CurrentMedications` TEXT NOT NULL DEFAULT 'NA',
ADD COLUMN `Comorbidities` TEXT NOT NULL DEFAULT 'NA',
ADD COLUMN `Assessment` TEXT NOT NULL DEFAULT 'NA',
ADD COLUMN `Plan` TEXT NOT NULL DEFAULT 'NA';

### Step 62

CREATE TABLE billing_tbl_patient_deposits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    patientID INT NOT NULL UNIQUE,  -- only one deposit record per patient
    depositReference VARCHAR(50),
    txnDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    depositAmount DECIMAL(12,2) DEFAULT 0.00,  -- total deposited
    appliedAmount DECIMAL(12,2) DEFAULT 0.00,  -- total consumed
    balanceAmount DECIMAL(12,2) GENERATED ALWAYS AS (depositAmount - appliedAmount) STORED,
    paymentOptionID INT NULL,
    processedByUID INT NULL,
    txnNarration TEXT NULL,
    isRefunded TINYINT(1) DEFAULT 0,
    INDEX (patientID)
);

### Step 63

CREATE TABLE billing_tbl_patient_deposit_txns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    patientID INT NOT NULL,
    depositID INT NOT NULL,
    txnReference VARCHAR(100),
    txnType ENUM('DEPOSIT', 'USE', 'REFUND') DEFAULT 'DEPOSIT',
    txnAmount DECIMAL(15,2) DEFAULT 0.00,
    txnDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    processedBYUID INT,
    txnNarration TEXT,
    FOREIGN KEY (depositID) REFERENCES billing_tbl_patient_deposits(id)
);

### Step 64- 71

ALTER TABLE tbl_sys_config
ADD COLUMN sha_recipient_email_reminder VARCHAR(255) NULL AFTER sha_privatepem_key_expiry;

ALTER TABLE tbl_patient_billable_items 
ADD COLUMN scheduledDateTime DATETIME NULL AFTER billDepartmentID;


ALTER TABLE inpatient_doctors_prescriptions 
ADD COLUMN scheduledDateTime DATETIME NULL;


ALTER TABLE eval_patients_prescriptions 
ADD COLUMN scheduledDateTime DATETIME NULL;



ALTER TABLE inpatient_kardex_notes 
ADD COLUMN scheduledDateTime DATETIME NULL;


ALTER TABLE tbl_patient_billable_items 
ADD COLUMN rtEyePower VARCHAR(255) NULL,
ADD COLUMN lftEyePower VARCHAR(255) NULL;


ALTER TABLE tbl_purchaseorders
ADD COLUMN storeID INT NOT NULL AFTER id;

### Step 72

-- Drop the table if it already exists (optional)
DROP TABLE IF EXISTS `tbl_non_profile_purchaseorders`;

-- Create table
CREATE TABLE `tbl_non_profile_purchaseorders` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `storeID` INT(11) DEFAULT 1,
  `poReferenceNumber` VARCHAR(100) DEFAULT NULL,
  `poDate` DATE DEFAULT NULL,
  `supplierID` INT(11) DEFAULT NULL,
  `status` VARCHAR(45) DEFAULT 'CREATED',
  `raisedby` VARCHAR(45) DEFAULT NULL,
  `dateraised` TIMESTAMP NULL DEFAULT NULL,
  `approvedby` VARCHAR(45) DEFAULT NULL,
  `dateapproved` TIMESTAMP NULL DEFAULT NULL,
  `approvecomments` TINYTEXT,
  `dateadded` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `UIP` VARCHAR(120) DEFAULT NULL,
  `UPC` VARCHAR(255) DEFAULT NULL,
  `poDetails` TEXT DEFAULT NULL,
  `amspo_isapproved` INT(11) DEFAULT 0,
  `amspo_ispaid` INT(11) DEFAULT 0,
  `amspo_isdeclined` INT(11) DEFAULT 0,
  `requestingStoreName` VARCHAR(225) DEFAULT NULL,
  `requestDeclinedBYUID` INT(11) DEFAULT 0,
  `requestingStoreID` INT(11) NOT NULL DEFAULT 0,
  `currentApprovalLevel` INT(11) DEFAULT 1,
  `poType` INT(11) NOT NULL DEFAULT 1 COMMENT '1 = Pharma, 2 = Non-Pharma',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB 
  DEFAULT CHARSET=latin1 
  COLLATE=latin1_swedish_ci;

COMMIT;

### Step 73

-- Drop table if it already exists (optional for recreation)
DROP TABLE IF EXISTS `tbl_non_profile_purchaseorders_items`;

-- Create table
CREATE TABLE `tbl_non_profile_purchaseorders_items` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `itemID` INT(11) DEFAULT NULL,
  `itemQty` VARCHAR(45) DEFAULT NULL,
  `unitBuyingPrice` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `sellingPrice` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `poReference` VARCHAR(110) DEFAULT NULL,
  `itemName` VARCHAR(255) DEFAULT NULL,
  `purchaseOrderID` INT(11) DEFAULT NULL,
  `requestingDepartmentName` VARCHAR(255) DEFAULT NULL,
  `updatedByUID` INT(11) DEFAULT NULL,
  `UIP` VARCHAR(120) DEFAULT NULL,
  `UPC` VARCHAR(255) DEFAULT NULL,
  `dateUpdated` DATETIME DEFAULT NULL,
  `supplierID` INT(11) DEFAULT NULL,
  `VAT` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  `DiscountPercent` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  `receivedQuantity` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `batchNumber` VARCHAR(120) DEFAULT NULL,
  `expiryDate` DATE DEFAULT NULL,
  `totalNetAmount` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `receiptDate` DATE DEFAULT NULL,
  `receiptSupplierID` INT(11) DEFAULT NULL,
  `receivingStoreID` INT(11) DEFAULT NULL,
  `receiptInvoiceNumber` VARCHAR(60) DEFAULT NULL,
  `receiptTransportCosts` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `receiptPackagingCosts` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `receiptsConfirmed` INT(11) NOT NULL DEFAULT 0,
  `receiptsConfirmedByUID` INT(11) DEFAULT NULL,
  `receiptsConfirmedByUIP` VARCHAR(150) DEFAULT NULL,
  `receiptsConfirmedByPCName` VARCHAR(220) DEFAULT NULL,
  `isActive` INT(11) DEFAULT 1,
  `dateTimeDeleted` DATETIME DEFAULT NULL,
  `deletedBYUID` INT(11) DEFAULT NULL,
  `deletionUIP` VARCHAR(220) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `purchaseOrderID_idx` (`purchaseOrderID`),
  KEY `supplierID_idx` (`supplierID`)
) ENGINE=InnoDB 
  DEFAULT CHARSET=latin1 
  COLLATE=latin1_swedish_ci;

COMMIT;

### Step 74-79.5

ALTER TABLE `tbl_store_measurement_units`
ADD COLUMN `storeTypeID` INT(11) NULL AFTER `id`;

ALTER TABLE tbl_supplier_category 
MODIFY COLUMN Percentageshare DECIMAL(5,2) DEFAULT 0.00;

ALTER TABLE invms_stkitem
ADD COLUMN currentBuyingPrice DECIMAL(20,2) NULL DEFAULT 0.00 AFTER reOrderLevel,
ADD COLUMN isSoldAsAPack INT(11) NULL DEFAULT 0 AFTER currentBuyingPrice,
ADD COLUMN packageQuantity INT(11) NULL DEFAULT 0 AFTER isSoldAsAPack,
ADD COLUMN unitPricePerItem DECIMAL(20,2) NULL DEFAULT 0.00 AFTER packageQuantity,
ADD COLUMN totalPricePerPack DECIMAL(20,2) NULL DEFAULT 0.00 AFTER unitPricePerItem,
ADD COLUMN packageType INT(11) NOT NULL DEFAULT 1 AFTER totalPricePerPack,
ADD COLUMN minimumStock DECIMAL(10,2) NULL DEFAULT 0.00 AFTER packageType,
ADD COLUMN reOrderQuantity DECIMAL(10,2) NULL DEFAULT 5.00 AFTER minimumStock;


ALTER TABLE tbl_patient_billable_items MODIFY supplier_id VARCHAR(255) NOT NULL DEFAULT '0';
ALTER TABLE pharma_individual_drugs_tracker MODIFY supplierID VARCHAR(255) NOT NULL DEFAULT '0';

UPDATE tbl_patient_billable_items AS t
JOIN (
    SELECT 
        id,
        finalTxnRef,
        ROW_NUMBER() OVER (PARTITION BY finalTxnRef ORDER BY id) AS rn
    FROM tbl_patient_billable_items
) AS dups ON t.id = dups.id
SET t.finalTxnRef = CONCAT(dups.finalTxnRef, '_', dups.rn)
WHERE dups.rn > 1;

ALTER TABLE tbl_patient_billable_items 
ADD UNIQUE INDEX uniq_finalTxnRef (finalTxnRef);

UPDATE billing_tbl_transactions AS t
JOIN (
    SELECT 
        id,
        txnReference,
        ROW_NUMBER() OVER (PARTITION BY txnReference ORDER BY id) AS rn
    FROM billing_tbl_transactions
) AS dups ON t.id = dups.id
SET t.txnReference = CONCAT(dups.txnReference, '_', dups.rn)
WHERE dups.rn > 1;

ALTER TABLE billing_tbl_transactions 
ADD UNIQUE INDEX uniq_txnReference (txnReference);

ALTER TABLE tbl_cash_refunds ADD COLUMN refundType VARCHAR(10) DEFAULT 'full';

ALTER TABLE tbl_patient_billable_items
ADD COLUMN isCashRefunded INT(11) NOT NULL DEFAULT 0 COMMENT 'Item Paid for but then Cash Refund Requested for by Client',
ADD COLUMN partialRefundAmount DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 'Amount refunded in case of a partial refund';

### Step 80

-- -----------------------------------------------------
-- Table structure for table `acc_approved_cash_refunds`
-- -----------------------------------------------------

CREATE TABLE `acc_approved_cash_refunds` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `tblCashRefundID` INT(11) DEFAULT NULL COMMENT 'ID from tbl_cash_refunds',
  `refundItemName` VARCHAR(225) NOT NULL DEFAULT '' COMMENT 'Refunded Item Name',
  `refundItemID` INT(11) NOT NULL COMMENT 'Billable Item ID',
  `patientID` INT(11) NOT NULL COMMENT 'Linked Patient ID',
  `approvedRefundAmount` DECIMAL(25,2) NOT NULL COMMENT 'Approved refund amount',
  `UID` INT(11) NOT NULL COMMENT 'User ID approving refund',
  `dateAdded` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation time',
  `UPC` VARCHAR(225) DEFAULT '' COMMENT 'User device/browser info',
  `UIP` VARCHAR(225) DEFAULT '' COMMENT 'User IP address',
  `debitAmount` DECIMAL(25,2) NOT NULL DEFAULT 0.00 COMMENT 'Debit amount for GL entry',
  `creditAmount` DECIMAL(25,2) NOT NULL DEFAULT 0.00 COMMENT 'Credit amount for GL entry',
  `glAccountNumber` VARCHAR(50) DEFAULT NULL COMMENT 'Linked GL account number',
  `transactionNo` VARCHAR(50) DEFAULT NULL COMMENT 'Transaction reference number',
  `postingDate` DATE DEFAULT NULL COMMENT 'Posting date to GL',
  PRIMARY KEY (`id`),
  KEY `idx_tblCashRefundID` (`tblCashRefundID`),
  KEY `idx_patientID` (`patientID`),
  KEY `idx_transactionNo` (`transactionNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
COMMENT='Approved cash refunds linked to GL entries';

-- -----------------------------------------------------
-- Trigger to auto-post refund entry into acc_gl_entries
-- -----------------------------------------------------
DELIMITER $$

CREATE TRIGGER `acc_approved_cash_refunds_AFTER_INSERT`
AFTER INSERT ON `acc_approved_cash_refunds`
FOR EACH ROW
BEGIN
    INSERT INTO `acc_gl_entries` (
        `transactionNo`,
        `glAccountNo`,
        `postingDate`,
        `amount`,
        `debitAmount`,
        `creditAmount`,
        `description`,
        `transactionDate`,
        `systemModule`,
        `userId`,
        `userName`
    )
    VALUES (
        NEW.`transactionNo`,
        NEW.`glAccountNumber`,
        NEW.`postingDate`,
        NEW.`approvedRefundAmount`,
        NEW.`debitAmount`,
        NEW.`creditAmount`,
        NEW.`refundItemName`,
        NEW.`postingDate`,
        'CASH-REFUND',
        NEW.`UID`,
        'System'
    );
END$$

DELIMITER ;

### Steps 81- 82.2

ALTER TABLE billing_tbl_transactions
ADD COLUMN refundedAmount DECIMAL(12,2) NOT NULL DEFAULT 0.00
COMMENT 'Total amount refunded to patient for this transaction';


ALTER TABLE tbl_patient_billable_items
ADD COLUMN dateTimeModified DATETIME DEFAULT NULL COMMENT 'Timestamp when item was last modified due to partial refund or adjustment',
ADD COLUMN billModifiedByUID INT(11) DEFAULT NULL COMMENT 'User ID of the person who last modified this billing item';


ALTER TABLE tbl_cash_refunds ADD COLUMN amountRefundRequested DECIMAL(12,2) NULL AFTER AmountPaid;

ALTER TABLE invms_stkitem 
ADD COLUMN `isSpecialDrug` TINYINT(1) NOT NULL DEFAULT 0 
AFTER `isActive`;

### Step 83

CREATE TABLE `reconcilled_invoices` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `insuranceID` int(11) DEFAULT 0,
  `UID` int(11) DEFAULT 1,
  `UIP` varchar(120) DEFAULT NULL,
  `UPC` varchar(220) DEFAULT NULL,
  `dateReconcilled` timestamp NULL DEFAULT current_timestamp(),
  `claimNumber` varchar(50) DEFAULT NULL,
  `invoiceNumber` varchar(50) DEFAULT NULL,
  `memberNumber` varchar(50) DEFAULT NULL,
  `policyNumber` varchar(50) DEFAULT NULL,
  `memberName` varchar(220) DEFAULT NULL,
  `invoiceAmount` decimal(18,2) DEFAULT 0.00,
  `clearedAmount` decimal(18,2) DEFAULT 0.00,
  `unClearedAmount` decimal(18,2) DEFAULT 0.00,
  `writeOffAmount` decimal(11,2) NOT NULL DEFAULT 0.00,
  `writeOffReason` text DEFAULT NULL,
  `insuranceRejectionReason` varchar(1000) DEFAULT NULL,
  `isActive` int(11) DEFAULT 1,
  `patientUID` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB 
  DEFAULT CHARSET=latin1 
  COLLATE=latin1_swedish_ci;
COMMIT;

### Step 84

CREATE TABLE `reconcilliation_staging_tbl` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `insuranceID` int(11) DEFAULT 0,
  `UID` int(11) DEFAULT 1,
  `UIP` varchar(120) DEFAULT NULL,
  `UPC` varchar(220) DEFAULT NULL,
  `dateReconcilled` timestamp NULL DEFAULT current_timestamp(),
  `claimNumber` varchar(250) DEFAULT NULL,
  `invoiceNumber` varchar(250) DEFAULT NULL,
  `memberNumber` varchar(250) DEFAULT NULL,
  `policyNumber` varchar(250) DEFAULT NULL,
  `chequeEFTNumber` varchar(250) DEFAULT NULL,
  `memberName` varchar(220) DEFAULT NULL,
  `chequePaymentDate` varchar(120) DEFAULT NULL,
  `invoiceAmount` decimal(18,2) DEFAULT 0.00,
  `clearedAmount` decimal(18,2) DEFAULT 0.00,
  `unClearedAmount` decimal(18,2) DEFAULT 0.00,
  `invoiceDate` varchar(120) DEFAULT NULL,
  `isActive` int(11) DEFAULT 1,
  `recordExists` int(11) NOT NULL DEFAULT 1,
  `excelReferenceNumber` varchar(220) DEFAULT NULL,
  `excelFileName` varchar(220) DEFAULT NULL,
  `patientID` int(11) NOT NULL DEFAULT 0,
  `systemInvoiceAmount` decimal(18,2) NOT NULL DEFAULT 0.00,
  `processingSearchOption` int(11) NOT NULL DEFAULT 0,
  `comments` mediumtext DEFAULT NULL,
  `payerChequeEFTTransactionNo` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB 
  DEFAULT CHARSET=latin1 
  COLLATE=latin1_swedish_ci;
COMMIT;

### Step 85

DROP TABLE IF EXISTS `tbl_faclities_configs`;

CREATE TABLE `tbl_faclities_configs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `configFacilityID` int(11) DEFAULT 1,
  `configIsCashPrepaid` int(11) DEFAULT 1,
  `configHasOptical` int(11) DEFAULT 0,
  `configHasDental` int(11) DEFAULT 0,
  `configHasPhysiotherapy` int(11) DEFAULT 0,
  `configHasLaboratory` int(11) DEFAULT 0,
  `configHasRadiology` int(11) DEFAULT 0,
  `configHasDiagnostics` int(11) DEFAULT 0,
  `configHasPharmacy` int(11) DEFAULT 0,
  `configHasPaeds` int(11) DEFAULT 0,
  `configHasOrthopeadics` int(11) DEFAULT 0,
  `configUseQueueSpeech` int(11) DEFAULT 0,
  `configUseSingleSchemePricing` int(11) DEFAULT 0,
  `configHasRenalUnit` int(11) NOT NULL DEFAULT 0,
  `configSpeechName` varchar(255) DEFAULT NULL,
  `UIP` varchar(120) DEFAULT NULL,
  `UPC` varchar(255) DEFAULT NULL,
  `UID` int(11) DEFAULT NULL,
  `isActive` int(11) NOT NULL DEFAULT 1,
  `datePosted` timestamp NOT NULL DEFAULT current_timestamp(),
  `canAllowNegativeSale` int(11) NOT NULL DEFAULT 0,
  `configHasBiometrics` int(11) NOT NULL DEFAULT 0,
  `configHasKitchen` int(11) NOT NULL DEFAULT 0,
  `configHasMogue` int(11) NOT NULL DEFAULT 0,
  `configHasQMS` int(11) NOT NULL DEFAULT 0,
  `configHasQMSSMS` int(11) NOT NULL DEFAULT 0,
  `configIPOPCode` varchar(20) DEFAULT NULL,
  `configHasGENERALSSMS` tinyint(1) DEFAULT 0,
  `configHasTBEvaluation` tinyint(1) DEFAULT 0,
  `configHasWELCOMESSMS` tinyint(1) DEFAULT 0,
  `configHasPAYMENTSSMS` tinyint(1) DEFAULT 0,
  `configHasAPPOINTMENTSSMS` tinyint(1) DEFAULT 0,
  `configHasReceptionModule` tinyint(1) DEFAULT 0,
  `configHasBillingModule` tinyint(1) DEFAULT 0,
  `configHasDoctorsModule` tinyint(1) DEFAULT 0,
  `configHasLaboratoryModule` tinyint(1) DEFAULT 0,
  `configHasPharmacyModule` tinyint(1) DEFAULT 0,
  `configHasInpatientModule` tinyint(1) DEFAULT 0,
  `configHasProcurementModule` tinyint(1) DEFAULT 0,
  `configHasHRModule` tinyint(1) DEFAULT 0,
  `configHasPayrollModule` tinyint(1) DEFAULT 0,
  `configHasFinanceModule` tinyint(1) DEFAULT 0,
  `configHasManagement` tinyint(1) DEFAULT 0,
  `configHasLastOfficeModule` tinyint(1) DEFAULT 0,
  `configHasMaintenanceModule` tinyint(1) DEFAULT 0,
  `configHasLaundryModule` tinyint(1) DEFAULT 0,
  `configHasKitchenModule` tinyint(1) DEFAULT 0,
  `configHasNextOfKin` tinyint(1) DEFAULT 0,
  `configHasNextAddress` tinyint(1) DEFAULT 0,
  `configHasTicketing` tinyint(1) DEFAULT 0,
  `configHasSoundQueue` tinyint(1) DEFAULT 0,
  `configHasEtims` tinyint(1) DEFAULT 0,
  `configHasPriceControl` tinyint(1) DEFAULT 0,
  `configHasPharmacyPOS` tinyint(1) DEFAULT 0,
  `configHasBillingPOS` tinyint(1) DEFAULT 0,
  `configHasPOSSendingDepartment` tinyint(1) DEFAULT 0,
  `configHasSHAQueue` tinyint(1) DEFAULT 0,
  `configHasIPOPNumber` tinyint(1) DEFAULT 0,
  `configHasInsuranceProcessing` tinyint(1) DEFAULT 0,
  `configHasInsuranceLimit` tinyint(1) DEFAULT 0,
  `configHasReceiptGeneration` tinyint(1) DEFAULT 0,
  `configHasPharmacyApproval` tinyint(1) DEFAULT 0,
    `configHasSpecialistQueue` tinyint(1) DEFAULT 0,
    `configHasInaptientPharmacy` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

INSERT INTO `tbl_faclities_configs`
(`id`, `configFacilityID`, `configIsCashPrepaid`, `configHasOptical`, `configHasDental`, `configHasPhysiotherapy`,
`configHasLaboratory`, `configHasRadiology`, `configHasDiagnostics`, `configHasPharmacy`, `configHasPaeds`,
`configHasOrthopeadics`, `configUseQueueSpeech`, `configUseSingleSchemePricing`, `configHasRenalUnit`,
`configSpeechName`, `UIP`, `UPC`, `UID`, `isActive`, `datePosted`, `canAllowNegativeSale`, `configHasBiometrics`,
`configHasKitchen`, `configHasMogue`, `configHasQMS`, `configHasQMSSMS`, `configIPOPCode`, `configHasGENERALSSMS`,
`configHasTBEvaluation`, `configHasWELCOMESSMS`, `configHasPAYMENTSSMS`, `configHasAPPOINTMENTSSMS`,
`configHasReceptionModule`, `configHasBillingModule`, `configHasDoctorsModule`, `configHasLaboratoryModule`,
`configHasPharmacyModule`, `configHasInpatientModule`, `configHasProcurementModule`, `configHasHRModule`,
`configHasPayrollModule`, `configHasFinanceModule`, `configHasManagement`, `configHasLastOfficeModule`,
`configHasMaintenanceModule`, `configHasLaundryModule`, `configHasKitchenModule`, `configHasNextOfKin`,
`configHasNextAddress`, `configHasTicketing`, `configHasSoundQueue`, `configHasEtims`, `configHasPriceControl`,
`configHasPharmacyPOS`, `configHasBillingPOS`, `configHasPOSSendingDepartment`, `configHasSHAQueue`,
`configHasIPOPNumber`, `configHasInsuranceProcessing`, `configHasInsuranceLimit`, `configHasReceiptGeneration`,
`configHasPharmacyApproval`,
`configHasSpecialistQueue`,
`configHasInaptientPharmacy`)
VALUES
(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 'Daniel', NULL, NULL, 1, 1, '2025-10-03 20:43:58', 0,
 1, 1, 1, 1, 0, 'VMH', 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
 0, 0, 0, 0, 1, 1, 1, 1, 1, 0, 0, 1, 0, 0, 1,0,0);

### Step 86

ALTER TABLE tbl_patient_billable_items 
DROP INDEX IF EXISTS uniq_finalTxnRef;

ALTER TABLE billing_tbl_transactions 
DROP INDEX IF EXISTS uniq_txnReference;


UPDATE tbl_patient_billable_items AS t
JOIN (
    SELECT 
        id,
        finalTxnRef,
        ROW_NUMBER() OVER (PARTITION BY finalTxnRef ORDER BY id) AS rn
    FROM tbl_patient_billable_items
) AS dups ON t.id = dups.id
SET t.finalTxnRef = CONCAT(dups.finalTxnRef, '_', dups.rn)
WHERE dups.rn > 1;


UPDATE billing_tbl_transactions AS t
JOIN (
    SELECT 
        id,
        txnReference,
        ROW_NUMBER() OVER (PARTITION BY txnReference ORDER BY id) AS rn
    FROM billing_tbl_transactions
) AS dups ON t.id = dups.id
SET t.txnReference = CONCAT(dups.txnReference, '_', dups.rn)
WHERE dups.rn > 1;

### Consultant tables

## Step 87

CREATE TABLE `tbl_consultant_categories` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `categoryName` VARCHAR(220) NOT NULL COMMENT 'Consultant category name',
  `isActive` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1 = Active, 0 = Inactive',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB 
DEFAULT CHARSET=utf8mb4 
COLLATE=utf8mb4_general_ci 
COMMENT='Defines consultant category levels such as Consultant, Specialist, Sub-Specialist';

INSERT INTO `tbl_consultant_categories` (`categoryName`, `isActive`) VALUES
('Consultant', 1),
('Specialist', 1),
('Sub-Specialist', 1);

## Step 88

CREATE TABLE `tbl_consultant_checkins` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `departmentID` INT(11) DEFAULT NULL COMMENT 'Linked department ID',
  `consultationRoomID` INT(11) DEFAULT NULL COMMENT 'Room ID where consultation occurs',
  `roomName` INT(11) DEFAULT NULL COMMENT 'Legacy placeholder for room name',
  `userID` INT(11) DEFAULT NULL COMMENT 'Consultant user ID',
  `dateAdded` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When consultant checked in',
  `isActive` TINYINT(1) DEFAULT 1 COMMENT '1 = Active, 0 = Inactive',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB 
DEFAULT CHARSET=utf8mb4 
COLLATE=utf8mb4_general_ci 
COMMENT='Tracks consultant check-ins by department and room';

INSERT INTO `tbl_consultant_checkins` 
(`departmentID`, `consultationRoomID`, `roomName`, `userID`, `dateAdded`, `isActive`) VALUES
(2, 5, 0, 8, '2025-03-23 07:59:34', 1),
(2, 5, 0, 1, '2025-05-22 10:46:39', 1),
(2, 5, 0, 24, '2025-03-17 09:14:34', 1),
(2, 5, 0, 13, '2025-03-17 11:48:14', 1),
(36, 50, 0, 36, '2025-03-27 13:31:24', 1),
(2, 5, 0, 10, '2025-03-25 07:46:46', 1),
(2, 5, 0, 11, '2025-03-18 05:47:19', 1),
(2, 5, 0, 12, '2025-03-25 12:56:22', 1),
(2, 5, 0, 4, '2025-03-18 06:05:32', 1),
(2, 5, 0, 7, '2025-03-19 10:28:12', 1),
(2, 5, 0, 3, '2025-03-21 06:52:46', 1),
(2, 5, 0, 17, '2025-03-22 06:48:49', 1),
(2, 5, 0, 77, '2025-03-22 08:10:51', 1),
(2, 5, 0, 30, '2025-03-22 08:52:52', 1),
(2, 5, 0, 25, '2025-03-27 11:14:26', 1),
(2, 5, 0, 57, '2025-03-26 14:28:20', 1),
(1, 1, 0, 6, '2025-09-23 12:53:50', 1);

## Step 89

CREATE TABLE `tbl_consultant_matrix` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `consultantCategoryID` INT(11) DEFAULT NULL COMMENT 'Links to tbl_consultant_categories.id',
  `mainSetupProcedureID` INT(11) DEFAULT NULL COMMENT 'Links to main procedure setup table',
  `patientCategoryID` INT(11) DEFAULT NULL COMMENT 'Links to patient category table',
  `minCashRate` DECIMAL(20,2) DEFAULT 0.00 COMMENT 'Minimum rate for cash patients',
  `minInsuranceRate` DECIMAL(20,2) DEFAULT 0.00 COMMENT 'Minimum rate for insured patients',
  `maxCashRate` DECIMAL(20,2) DEFAULT 0.00 COMMENT 'Maximum rate for cash patients',
  `maxInsuranceRate` DECIMAL(20,2) DEFAULT 0.00 COMMENT 'Maximum rate for insured patients',
  `isActive` TINYINT(1) DEFAULT 1 COMMENT '1 = Active, 0 = Inactive',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB 
DEFAULT CHARSET=utf8mb4 
COLLATE=utf8mb4_general_ci 
COMMENT='Defines consultant category rate matrix by patient and procedure type';

INSERT INTO `tbl_consultant_matrix` 
(`consultantCategoryID`, `mainSetupProcedureID`, `patientCategoryID`, `minCashRate`, `minInsuranceRate`, `maxCashRate`, `maxInsuranceRate`, `isActive`) VALUES
(2, 5, 1, 3000.00, 3500.00, 3500.00, 3500.00, 1),
(3, 5, 1, 4000.00, 4500.00, 4000.00, 4500.00, 1),
(2, 5, 2, 3500.00, 3500.00, 99999.00, 99999.00, 1),
(3, 5, 2, 4500.00, 4500.00, 99999.00, 99999.00, 1);

## Step 90

CREATE TABLE `tbl_consultant_rates` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `userID` INT(11) DEFAULT NULL COMMENT 'Links to users/consultants table',
  `paymentRate` DECIMAL(20,2) DEFAULT 0.00 COMMENT 'Consultant payment rate (percentage or flat amount)',
  `isActive` TINYINT(1) DEFAULT 1 COMMENT '1 = Active, 0 = Inactive',
  `rateType` VARCHAR(20) DEFAULT 'percent' COMMENT 'Rate type: percent or flat',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB 
DEFAULT CHARSET=utf8mb4 
COLLATE=utf8mb4_general_ci 
COMMENT='Stores consultant payment rate configurations';

INSERT INTO `tbl_consultant_rates` 
(`userID`, `paymentRate`, `isActive`, `rateType`) VALUES
(400, 50.00, 1, 'percent'),
(402, 5.00, 1, 'percent'),
(403, 50.00, 1, 'flat'),
(412, 5.00, 1, 'percent');

## Step 91

CREATE TABLE `tbl_consultant_specialities` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `specialtyName` VARCHAR(220) NOT NULL COMMENT 'Name of consultant specialty',
  `categoryID` INT(11) DEFAULT NULL COMMENT 'Optional link to consultant category',
  `isActive` TINYINT(1) DEFAULT 1 COMMENT '1 = Active, 0 = Inactive',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB 
DEFAULT CHARSET=utf8mb4 
COLLATE=utf8mb4_general_ci
COMMENT='Master list of consultant medical specialties';

INSERT INTO `tbl_consultant_specialities` 
(`specialtyName`, `categoryID`, `isActive`) VALUES
('None', 1, 1),
('Obstetrics & Gynaecology', NULL, 1),
('Speech Therapist', NULL, 1),
('Orthopedics', NULL, 1),
('Paediatrics & Child Health', NULL, 1),
('Oral & Maxillofacial Surgery', NULL, 1),
('Pathologist', NULL, 1),
('Anesthesiologist', NULL, 1),
('Radiologist', NULL, 1),
('Clinical Psychologist', NULL, 1),
('Physiotherapy', NULL, 1),
('Internal Medicine', NULL, 1),
('Neurologist', NULL, 1),
('Paediatric Surgeon', NULL, 1),
('Ear,Nose & Throat', NULL, 1),
('Paediatric Neonatologist', NULL, 1),
('Urologist', NULL, 1),
('Oncologist', NULL, 1),
('Opthalmologist', NULL, 1),
('Dentist', NULL, 1),
('General Surgery', NULL, 1),
('Neuro-surgeon', NULL, 1),
('Physician', NULL, 1),
('Nutritionist', NULL, 1),
('Cardiologist', NULL, 1);

## Step 92

CREATE TABLE `tbl_consultants_payments` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `visitReference` VARCHAR(150) DEFAULT NULL COMMENT 'Unique reference for the patient visit',
  `patientCategoryTypeID` INT(11) DEFAULT NULL COMMENT 'Refers to patient category (cash, insurance, etc.)',
  `consultantCategoryTypeID` INT(11) DEFAULT NULL COMMENT 'Refers to consultant type (specialist, consultant, etc.)',
  `patientID` INT(11) DEFAULT NULL COMMENT 'Linked patient record ID',
  `consultantID` INT(11) DEFAULT NULL COMMENT 'Linked consultant/staff ID',
  `totalBillAmount` DECIMAL(20,2) NOT NULL DEFAULT 0.00 COMMENT 'Total amount billed for this consultant visit',
  `percentRate` DECIMAL(5,2) NOT NULL DEFAULT 0.00 COMMENT 'Percentage rate applicable to consultant',
  `actualPayableAmount` DECIMAL(20,2) NOT NULL DEFAULT 0.00 COMMENT 'Amount consultant should be paid',
  `facilityAmount` DECIMAL(20,2) NOT NULL DEFAULT 0.00 COMMENT 'Facility’s retained portion',
  `totalAmountPaid` DECIMAL(20,2) NOT NULL DEFAULT 0.00 COMMENT 'Total amount already paid out',
  `txnNarration` TEXT DEFAULT NULL COMMENT 'Payment narration or description',
  `isPaidOut` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = Paid, 0 = Pending',
  `isDeleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = Deleted record',
  `deletedBYUID` INT(11) DEFAULT NULL COMMENT 'User ID of who deleted record',
  `deletionComments` TEXT DEFAULT NULL COMMENT 'Reason for deletion',
  `dateTimeDeleted` DATETIME DEFAULT NULL COMMENT 'When record was deleted',
  `UIP` VARCHAR(180) DEFAULT NULL COMMENT 'User IP address',
  `UID` INT(11) DEFAULT NULL COMMENT 'User ID of record creator',
  `dateAdded` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation date',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci
COMMENT='Tracks consultant payments and payout details';

### Others

### Step 93

-- =======================================================
-- Recreate `acc_logged_expenses` table (with isDeletedByUID)
-- =======================================================

DROP TABLE IF EXISTS `acc_logged_expenses`;
DELIMITER $$

CREATE TABLE `acc_logged_expenses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `expenseTypeID` int(11) NOT NULL,
  `expenseTypeName` varchar(225) NOT NULL,
  `expenseToUID` varchar(120) NOT NULL DEFAULT '',
  `glAccountID` varchar(45) NOT NULL,
  `balancingGlAccount` varchar(45) DEFAULT NULL,
  `expenseActivityCategoryID` int(11) NOT NULL,
  `expenseDate` date NOT NULL,
  `expenseAmount` decimal(18,2) NOT NULL,
  `expenseReference` varchar(40) NOT NULL,
  `expenseNarration` tinytext NOT NULL,
  `createdByUID` int(11) NOT NULL,
  `dateAdded` timestamp NOT NULL DEFAULT current_timestamp(),
  `isConfirmed` int(11) NOT NULL DEFAULT 0,
  `isDeleted` int(11) NOT NULL DEFAULT 0,
  `isDeletedByUID` int(11) DEFAULT NULL,
  `budgetActivityID` int(11) NOT NULL,
  `isPharmacyExpense` int(11) DEFAULT 0,
  `isAllocated` int(11) DEFAULT 0,
  `allocatedAmount` decimal(20,2) DEFAULT 0.00,
  `balanceAllocation` decimal(20,2) DEFAULT 0.00,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
$$

-- =======================================================
-- Trigger: acc_logged_expenses_AFTER_UPDATE
-- =======================================================
CREATE TRIGGER `acc_logged_expenses_AFTER_UPDATE`
AFTER UPDATE ON `acc_logged_expenses`
FOR EACH ROW
BEGIN
    DECLARE postingType INT;
    DECLARE DBpostDate DATE;
    DECLARE realPostDate DATE;

    SELECT pickToday, postingDate 
    INTO postingType, DBpostDate 
    FROM acc_postingdate_setup 
    WHERE systemModule='EXPENSES';

    CASE postingType
        WHEN 1 THEN
            SET realPostDate = CURDATE();
        WHEN 0 THEN
            SET realPostDate = DBpostDate;
        ELSE
            SET realPostDate = CURDATE();
    END CASE;

    IF NEW.isConfirmed = 1 THEN
        INSERT INTO `acc_expenses_gl_entries` (
            `transactionNo`, `glAccountNo`, `postingDate`,
            `expTypeId`, `expTypeName`, `expActivityId`,
            `amount`, `debitAmount`, `description`,
            `accountNo`, `transactionDate`, `expReference`, `userId`
        )
        VALUES (
            NEW.expenseReference, NEW.glAccountID, realPostDate,
            NEW.expenseTypeID, NEW.expenseTypeName, NEW.expenseActivityCategoryID,
            NEW.expenseAmount, NEW.expenseAmount, NEW.expenseNarration,
            NEW.expenseToUID, NEW.expenseDate, NEW.expenseReference, NEW.createdByUID
        );

        INSERT INTO `acc_expenses_gl_entries` (
            `transactionNo`, `glAccountNo`, `postingDate`,
            `expTypeId`, `expTypeName`, `expActivityId`,
            `amount`, `creditAmount`, `description`,
            `accountNo`, `transactionDate`, `expReference`, `userId`
        )
        VALUES (
            NEW.expenseReference, NEW.balancingGlAccount, realPostDate,
            NEW.expenseTypeID, NEW.expenseTypeName, NEW.expenseActivityCategoryID,
            NEW.expenseAmount, NEW.expenseAmount, NEW.expenseNarration,
            NEW.expenseToUID, NEW.expenseDate, NEW.expenseReference, NEW.createdByUID
        );
    END IF;
END$$

DELIMITER ;

COMMIT;

### Others

### Step 94

-- =======================================================
-- Recreate `acc_expenses_gl_entries` table
-- =======================================================

DROP TABLE IF EXISTS `acc_expenses_gl_entries`;
DELIMITER $$

CREATE TABLE `acc_expenses_gl_entries` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `transactionNo` varchar(45) DEFAULT NULL,
  `glAccountNo` varchar(45) DEFAULT NULL,
  `postingDate` date DEFAULT NULL,
  `expTypeId` int(11) DEFAULT NULL,
  `expTypeName` varchar(255) DEFAULT NULL,
  `expActivityId` int(11) DEFAULT NULL,
  `amount` decimal(25,2) DEFAULT 0.00,
  `debitAmount` decimal(25,2) DEFAULT 0.00,
  `creditAmount` decimal(25,2) DEFAULT 0.00,
  `description` varchar(255) DEFAULT NULL,
  `accountNo` varchar(45) DEFAULT NULL,
  `transactionDate` varchar(45) DEFAULT NULL,
  `expReference` varchar(255) DEFAULT NULL,
  `userId` int(11) DEFAULT NULL,
  `dateAdded` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
$$

-- =======================================================
-- Trigger: acc_expenses_gl_entries_AfterINSERT
-- =======================================================
CREATE TRIGGER `acc_expenses_gl_entries_AfterINSERT`
AFTER INSERT ON `acc_expenses_gl_entries`
FOR EACH ROW
BEGIN
    INSERT INTO `acc_gl_entries`
    (
        `transactionNo`,
        `glAccountNo`,
        `postingDate`,
        `amount`,
        `debitAmount`,
        `creditAmount`,
        `description`,
        `transactionDate`,
        `systemModule`,
        `userId`,
        `userName`
    )
    VALUES
    (
        NEW.transactionNo,
        NEW.glAccountNo,
        NEW.postingDate,
        NEW.amount,
        NEW.debitAmount,
        NEW.creditAmount,
        NEW.description,
        NEW.transactionDate,
        'EXPENSES',
        NEW.userId,
        'System'
    );
END$$

DELIMITER ;

COMMIT;

### Step 95

-- Drop table if it exists
DROP TABLE IF EXISTS `acc_logged_income`;

-- Table structure for table `acc_logged_income`
CREATE TABLE `acc_logged_income` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `incomeTypeID` int(11) DEFAULT NULL,
  `incomeTypeName` varchar(225) DEFAULT NULL,
  `incomeToUID` varchar(120) DEFAULT '' ,
  `glAccountID` varchar(45) DEFAULT NULL,
  `balancingGlAccount` varchar(45) DEFAULT NULL,
  `incomeActivityCategoryID` int(11) DEFAULT NULL,
  `incomeDate` date DEFAULT NULL,
  `incomeAmount` decimal(18,2) DEFAULT NULL,
  `incomeReference` varchar(40) DEFAULT NULL,
  `incomeNarration` tinytext DEFAULT NULL,
  `createdByUID` int(11) DEFAULT NULL,
  `dateAdded` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `isConfirmed` int(11) DEFAULT 0,
  `isDeleted` int(11) DEFAULT 0,
  `debtorPayeeInvoice` varchar(150) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Drop trigger if it exists
DROP TRIGGER IF EXISTS `acc_logged_income_AFTERUPDATE`;

-- Trigger `acc_logged_income_AFTERUPDATE`
DELIMITER $$
CREATE TRIGGER `acc_logged_income_AFTERUPDATE` 
AFTER UPDATE ON `acc_logged_income` 
FOR EACH ROW 
BEGIN
    DECLARE postingType INT;
    DECLARE DBpostDate DATE;
    DECLARE realPostDate DATE;

    SELECT pickToday, postingDate 
    INTO postingType, DBpostDate 
    FROM acc_postingdate_setup 
    WHERE systemModule='INCOMES';

    CASE postingType
        WHEN 1 THEN SET realPostDate = CURDATE();
        WHEN 0 THEN SET realPostDate = DBpostDate;
        ELSE SET realPostDate = CURDATE();
    END CASE;

    IF NEW.isConfirmed = 1 THEN
        INSERT INTO `acc_income_gl_entries` 
            (`transactionNo`,`glAccountNo`,`postingDate`,`incomeTypeId`,`incomeTypeName`,
             `incomeActivityID`,`amount`,`debitAmount`,`description`,`accountNo`,
             `transactionDate`,`incomeReference`,`userId`) 
        VALUES 
            (NEW.incomeReference, NEW.glAccountID, realPostDate, NEW.incomeTypeID, NEW.incomeTypeName,
             NEW.incomeActivityCategoryID, NEW.incomeAmount, NEW.incomeAmount, NEW.incomeNarration,
             NEW.incomeToUID, NEW.incomeDate, NEW.incomeReference, NEW.createdByUID);

        INSERT INTO `acc_income_gl_entries` 
            (`transactionNo`,`glAccountNo`,`postingDate`,`incomeTypeId`,`incomeTypeName`,
             `incomeActivityID`,`amount`,`creditAmount`,`description`,`accountNo`,
             `transactionDate`,`incomeReference`,`userId`) 
        VALUES 
            (NEW.incomeReference, NEW.balancingGlAccount, realPostDate, NEW.incomeTypeID, NEW.incomeTypeName,
             NEW.incomeActivityCategoryID, NEW.incomeAmount, NEW.incomeAmount, NEW.incomeNarration,
             NEW.incomeToUID, NEW.incomeDate, NEW.incomeReference, NEW.createdByUID);
    END IF;
END
$$
DELIMITER ;

COMMIT;

### Step 96

-- Drop table if it exists
DROP TABLE IF EXISTS `acc_income_gl_entries`;

-- Table structure for table `acc_income_gl_entries`
CREATE TABLE `acc_income_gl_entries` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `transactionNo` varchar(45) DEFAULT NULL,
  `glAccountNo` varchar(45) DEFAULT NULL,
  `postingDate` date DEFAULT NULL,
  `incomeTypeId` int(11) DEFAULT NULL,
  `incomeTypeName` varchar(255) DEFAULT NULL,
  `incomeActivityID` int(11) DEFAULT NULL,
  `amount` double(25,2) DEFAULT 0.00,
  `debitAmount` double(25,2) DEFAULT 0.00,
  `creditAmount` double(25,2) DEFAULT 0.00,
  `description` varchar(255) DEFAULT NULL,
  `accountNo` varchar(45) DEFAULT NULL,
  `transactionDate` varchar(45) DEFAULT NULL,
  `incomeReference` varchar(255) DEFAULT NULL,
  `userId` int(11) DEFAULT NULL,
  `dateAdded` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Drop trigger if it exists
DROP TRIGGER IF EXISTS `acc_income_gl_entries_AFTERINSERT`;

-- Trigger `acc_income_gl_entries_AFTERINSERT`
DELIMITER $$
CREATE TRIGGER `acc_income_gl_entries_AFTERINSERT`
AFTER INSERT ON `acc_income_gl_entries`
FOR EACH ROW
BEGIN
    INSERT INTO `acc_gl_entries`
        (`transactionNo`,
         `glAccountNo`,
         `postingDate`,
         `amount`,
         `debitAmount`,
         `creditAmount`,
         `description`,
         `transactionDate`,
         `systemModule`,
         `userId`,
         `userName`)
    VALUES
        (NEW.transactionNo,
         NEW.glAccountNo,
         NEW.postingDate,
         NEW.amount,
         NEW.debitAmount,
         NEW.creditAmount,
         NEW.description,
         NEW.transactionDate,
         'INCOMES',
         NEW.userId,
         'System');
END
$$
DELIMITER ;

COMMIT;

### Step 97

ALTER TABLE tbl_consultant_checkins
MODIFY COLUMN roomName VARCHAR(255);

ALTER TABLE password_resets
ADD COLUMN requested_at DATETIME DEFAULT CURRENT_TIMESTAMP;

### Step 98

-- ==============================================
-- Table structure for table `lookup_nextofkin`
-- ==============================================

CREATE TABLE `lookup_nextofkin` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nextofkinname` VARCHAR(50) DEFAULT NULL,
  `isActive` TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB 
  DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_general_ci;

-- ==============================================
-- Insert initial data into `lookup_nextofkin`
-- ==============================================

INSERT INTO `lookup_nextofkin` (`id`, `nextofkinname`, `isActive`) VALUES
(1, 'SELF', 1),
(2, 'MOTHER', 1),
(3, 'FATHER', 1),
(4, 'SPOUSE', 1),
(5, 'GUARDIAN', 1),
(6, 'BROTHER', 1),
(7, 'SISTER', 1),
(8, 'UNCLE', 1),
(9, 'AUNTIE', 1),
(10, 'GRANDMOTHER', 1),
(11, 'GRANDFATHER', 1),
(12, 'OTHER', 1);

-- ==============================================
-- Set AUTO_INCREMENT start value
-- ==============================================

ALTER TABLE `lookup_nextofkin` AUTO_INCREMENT = 13;

-- ==============================================
-- Commit transaction
-- ==============================================

COMMIT;


-- ==============================================
-- Table structure for table `tbl_invoice_types`
-- ==============================================

CREATE TABLE `tbl_invoice_types` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `invoiceName` VARCHAR(250) DEFAULT NULL,
  `isActive` TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB 
  DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_general_ci;

-- ==============================================
-- Insert initial data into `tbl_invoice_types`
-- ==============================================

INSERT INTO `tbl_invoice_types` (`id`, `invoiceName`, `isActive`) VALUES
(1, 'Medical/ Pharmaceuticals', 1),
(2, 'Asset Registry Invoice', 1);

-- ==============================================
-- Set AUTO_INCREMENT start value
-- ==============================================

ALTER TABLE `tbl_invoice_types` AUTO_INCREMENT = 3;

-- ==============================================
-- Commit transaction
-- ==============================================

COMMIT;

### Step 99

ALTER TABLE tbl_supplier_invoices 
ADD COLUMN balanceAllocation DECIMAL(15,2) NULL DEFAULT 0 AFTER amountPaid;

### Step 100

-- ===================================================
-- Drop existing table and trigger if they exist
-- ===================================================
DROP TRIGGER IF EXISTS `acc_supplier_invoices_AFTER_UPDATE`;
DROP TABLE IF EXISTS `tbl_supplier_invoices`;

-- ===================================================
-- Recreate table structure
-- ===================================================
CREATE TABLE `tbl_supplier_invoices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `invoiceTypeID` int(11) DEFAULT 1,
  `supplierID` int(11) DEFAULT NULL,
  `invoiceDate` date DEFAULT NULL,
  `invoiceNumber` varchar(50) DEFAULT NULL,
  `invoiceNarration` text DEFAULT NULL,
  `invoiceAmount` decimal(20,2) DEFAULT 0.00,
  `debitAccount` varchar(20) DEFAULT NULL,
  `creditAccount` varchar(20) DEFAULT NULL,
  `dateLogged` timestamp NULL DEFAULT current_timestamp(),
  `UID` int(11) DEFAULT NULL,
  `UIP` varchar(250) DEFAULT NULL,
  `UPC` varchar(250) DEFAULT NULL,
  `isPaid` int(11) DEFAULT 0,
  `isActive` int(11) NOT NULL DEFAULT 1,
  `amountPaid` decimal(22,2) DEFAULT 0.00,
  `balanceAllocation` decimal(15,2) DEFAULT 0.00,
  `balancePayable` decimal(22,2) DEFAULT 0.00,
  `monthID` int(11) DEFAULT NULL,
  `yearID` int(11) DEFAULT NULL,
  `isConfirmed` int(11) DEFAULT 0,
  `expensePayerID` int(11) DEFAULT NULL,
  `dateAllocated` datetime DEFAULT NULL,
  `Remarks` text DEFAULT NULL,
  `WriteOffAmnt` decimal(20,2) DEFAULT 0.00,
  `AllocatedByUID` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ===================================================
-- Recreate trigger
-- ===================================================
DELIMITER $$

CREATE TRIGGER `acc_supplier_invoices_AFTER_UPDATE`
AFTER UPDATE ON `tbl_supplier_invoices`
FOR EACH ROW
BEGIN
    DECLARE postingType INT;
    DECLARE DBpostDate DATE;
    DECLARE realPostDate DATE;

    -- Get posting setup
    SELECT pickToday, postingDate 
    INTO postingType, DBpostDate 
    FROM acc_postingdate_setup 
    WHERE systemModule = 'EXPENSES'
    LIMIT 1;

    -- Determine posting date
    CASE postingType
        WHEN 1 THEN
            SET realPostDate = CURDATE();
        WHEN 0 THEN
            SET realPostDate = DBpostDate;
        ELSE
            SET realPostDate = CURDATE();
    END CASE;

    -- If invoice is confirmed, create GL entries
    IF NEW.isConfirmed = 1 THEN
        INSERT INTO `acc_supplier_invoices_gl_entries`
            (`supplierID`, `referenceNumber`, `glAccountNo`, `postingDate`,
             `amount`, `debitAmount`, `description`, `invoiceID`, `txnDate`, `userId`)
        VALUES
            (NEW.supplierID, NEW.invoiceNumber, NEW.debitAccount, realPostDate,
             NEW.invoiceAmount, NEW.invoiceAmount, NEW.invoiceNarration,
             NEW.id, NEW.invoiceDate, NEW.UID);

        INSERT INTO `acc_supplier_invoices_gl_entries`
            (`supplierID`, `referenceNumber`, `glAccountNo`, `postingDate`,
             `amount`, `creditAmount`, `description`, `invoiceID`, `txnDate`, `userId`)
        VALUES
            (NEW.supplierID, NEW.invoiceNumber, NEW.creditAccount, realPostDate,
             NEW.invoiceAmount, NEW.invoiceAmount, NEW.invoiceNarration,
             NEW.id, NEW.invoiceDate, NEW.UID);
    END IF;
END$$

DELIMITER ;

-- ===================================================
-- Commit changes
-- ===================================================
COMMIT;

### Step 101

-- ===================================================
-- Drop table if it exists
-- ===================================================
DROP TABLE IF EXISTS `acc_supplier_invoices_gl_entries`;

-- ===================================================
-- Recreate table structure
-- ===================================================
CREATE TABLE `acc_supplier_invoices_gl_entries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `supplierID` int(11) DEFAULT NULL,
  `referenceNumber` varchar(100) DEFAULT NULL,
  `glAccountNo` varchar(20) DEFAULT NULL,
  `postingDate` date DEFAULT NULL,
  `amount` decimal(20,2) DEFAULT 0.00,
  `debitAmount` decimal(20,2) DEFAULT 0.00,
  `creditAmount` decimal(20,2) DEFAULT 0.00,
  `description` text DEFAULT NULL,
  `invoiceID` int(11) DEFAULT NULL,
  `txnDate` date DEFAULT NULL,
  `userId` int(11) DEFAULT NULL,
  `dateLogged` timestamp NULL DEFAULT current_timestamp(),
  `isActive` int(11) DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `idx_supplierID` (`supplierID`),
  KEY `idx_invoiceID` (`invoiceID`),
  KEY `idx_glAccountNo` (`glAccountNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ===================================================
-- Commit changes
-- ===================================================
COMMIT;

### Step 102

ALTER TABLE tbl_faclities_configs
ADD COLUMN configHasShaPharmacyApproval TINYINT(1) DEFAULT 0;

### Step 102

ALTER TABLE invms_items_requisition
  ADD COLUMN IF NOT EXISTS UID int(11) NOT NULL,
  ADD COLUMN IF NOT EXISTS requestReference varchar(30) NOT NULL,
  ADD COLUMN IF NOT EXISTS UIP varchar(150) NOT NULL,
  ADD COLUMN IF NOT EXISTS UPC varchar(150) NOT NULL,
  ADD COLUMN IF NOT EXISTS requestDate date NOT NULL,
  ADD COLUMN IF NOT EXISTS dateCreated timestamp NOT NULL DEFAULT current_timestamp(),
  ADD COLUMN IF NOT EXISTS subject tinytext NOT NULL,
  ADD COLUMN IF NOT EXISTS requestDescription text DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS SupplierStoreID int(11) NOT NULL,
  ADD COLUMN IF NOT EXISTS requestingStoreID int(11) NOT NULL,
  ADD COLUMN IF NOT EXISTS requestedItemID int(11) NOT NULL,
  ADD COLUMN IF NOT EXISTS requestedItemName varchar(300) NOT NULL DEFAULT '',
  ADD COLUMN IF NOT EXISTS requestedItemDescription tinytext NOT NULL,
  ADD COLUMN IF NOT EXISTS requestedItemQuantity decimal(10,2) NOT NULL,
  ADD COLUMN IF NOT EXISTS requestStatusID int(2) NOT NULL DEFAULT 0 COMMENT '0 = Pending, 1 = Approved, 2 = Declined',
  ADD COLUMN IF NOT EXISTS requestStatusName varchar(30) NOT NULL DEFAULT 'PENDING',
  ADD COLUMN IF NOT EXISTS approvedByUID int(11) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS approvalDate datetime DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS cancelledByUID int(11) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS isPharmaDrug int(2) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS dateDeclined datetime DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS approvalDeclineComments tinytext DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS quantityApproved decimal(18,2) NOT NULL DEFAULT 0.00,
  ADD COLUMN IF NOT EXISTS isDeclined int(2) DEFAULT 0,
  ADD COLUMN IF NOT EXISTS declineDate datetime DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS requestDeclinedBYUID int(11) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS isCollected int(2) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS collectedByUID int(11) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS dateCollected datetime DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS currentApprovalLevel int(11) DEFAULT 1,
  ADD COLUMN IF NOT EXISTS updatedByUID int(11) DEFAULT 0,
  ADD COLUMN IF NOT EXISTS dateUpdated datetime DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS isActive int(11) DEFAULT 1,
  ADD COLUMN IF NOT EXISTS requestReferenceNumber varchar(20) DEFAULT '0',
  ADD COLUMN IF NOT EXISTS dispatchedByUID int(11) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS qtyDispatched decimal(20,2) DEFAULT 0.00,
  ADD COLUMN IF NOT EXISTS dateDispatched datetime DEFAULT NULL;

### Step 103 (Create Support Admin User)

INSERT INTO tbl_users (
    id,
    names,
    uname,
    email,
    pass,
    pin,
    isSpecialist,
    consultantCategoryID,
    userSpecialtyID,
    calendarColor,
    initials,
    userTypeID,
    group_id,
    photo,
    signature,
    last_login,
    last_passchange,
    isActive,
    date_created,
    created_by,
    phone,
    last_password_sms_sent_at,
    last_pin_sms_sent_at,
    gender,
    user_title,
    idnumber,
    accessibleFacilityIDs,
    userDepartmentID,
    userOnlineStatus,
    Loggerhash_id,
    updatedByUID,
    UpdateIPAddress
) VALUES (
    1,                                        -- id
    'Support Admin',                             -- names
    'supportadmin',                              -- uname
    '[e-mail]',                  -- email
    'd5ab8dc7ef67ca92e41d730982c5c602',         -- pass (MD5 of '9000')
    '$2y$10$Rexqyhk0sFq6bldC7oKdLeV9uPzx7PuK6vZHU/em990o2czYRQkNS', -- pin (bcrypt 0000)
    0,                                           -- isSpecialist
    NULL,                                        -- consultantCategoryID
    NULL,                                        -- userSpecialtyID
    '#000000',                                   -- calendarColor
    'SA',                                        -- initials
    NULL,                                        -- userTypeID
    1,                                           -- group_id
    NULL,                                        -- photo
    NULL,                                        -- signature
    NULL,                                        -- last_login
    NULL,                                        -- last_passchange
    1,                                           -- isActive
    NOW(),                                       -- date_created
    0,                                           -- created_by
    NULL,                                        -- phone
    NULL,                                        -- last_password_sms_sent_at
    NULL,                                        -- last_pin_sms_sent_at
    'Other',                                     -- gender
    'Admin',                                     -- user_title
    'SUP1000',                                   -- idnumber
    '1',                                         -- accessibleFacilityIDs
    1,                                           -- userDepartmentID
    0,                                           -- userOnlineStatus
    NULL,                                        -- Loggerhash_id
    0,                                           -- updatedByUID
    NULL                                         -- UpdateIPAddress
);

### Step 104 (SPECIAL COMBO)

-- Step 1: Add the new column
ALTER TABLE `acc_lookup_gltransactions`
DROP COLUMN IF EXISTS `debitGLAccount`;
ALTER TABLE `acc_lookup_gltransactions`
ADD COLUMN `debitGLAccount` VARCHAR(50) NULL AFTER `glAccount`;

-- Step 2: Populate it with the default value for all existing records
UPDATE acc_lookup_gltransactions
SET debitGLAccount = '3001';

-- Insert PHARMAOP row if it doesn't exist
INSERT INTO acc_lookup_gltransactions (systemModule, Description, glAccount, debitGLAccount)
SELECT 'PHARMAOP', 'Pharmacy OP Bills', '3601-1', '3001'
WHERE NOT EXISTS (
    SELECT 1 FROM acc_lookup_gltransactions WHERE systemModule='PHARMAOP'
);

-- Insert PHARMAIP row if it doesn't exist
INSERT INTO acc_lookup_gltransactions (systemModule, Description, glAccount, debitGLAccount)
SELECT 'PHARMAIP', 'Pharmacy IP Bills', '3601-2', '3001'
WHERE NOT EXISTS (
    SELECT 1 FROM acc_lookup_gltransactions WHERE systemModule='PHARMAIP'
);


-- Drop table if exists
DROP TABLE IF EXISTS `acc_gl_category_control_types`;

-- Create table
CREATE TABLE `acc_gl_category_control_types` (
  `id` int(11) NOT NULL,
  `primaryControlTypeID` int(11) DEFAULT NULL,
  `name` varchar(225) NOT NULL,
  `code` varchar(100) NOT NULL,
  `start_range` decimal(10,0) NOT NULL,
  `end_range` decimal(10,0) NOT NULL,
  `balance` decimal(13,2) DEFAULT NULL,
  `isCreditTxn` varchar(10) DEFAULT NULL,
  `isDebitTxn` varchar(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- Insert data
INSERT INTO `acc_gl_category_control_types` 
(`id`, `primaryControlTypeID`, `name`, `code`, `start_range`, `end_range`, `balance`, `isCreditTxn`, `isDebitTxn`) 
VALUES
(1, 5, 'NON CURRENT ASSETS', 'NCAS', 2000, 2099, 70444863.14, '-', '+'),
(2, 3, 'CURRENT LIABILITY', 'CLB', 2100, 2199, 23013204.27, '+', '-'),
(3, 2, 'REVENUE', 'REV', 3600, 3799, 24478069.43, '+', '-'),
(4, 4, 'EXPENSES', 'EXP', 3800, 4999, 27064816.27, '-', '+'),
(5, 5, 'CURRENT ASSETS', 'CURRA', 3100, 3399, -9465958.00, '-', '+'),
(8, 1, 'EQUITY', 'EQU', 7000, 7099, 101247.00, '+', '-'),
(9, 3, 'NON CURRENT LIABILITY', 'NCLIAB', 2200, 2299, 70210288.14, '+', '-'),
(10, 5, 'CURRENT ASSETS', 'CURRA', 3000, 3399, 30103032.43, '-', '+'),
(11, 4, 'TAX', 'TAX', 5000, 5099, 0.00, NULL, NULL),
(12, 4, 'DIVIDENDS', 'DIV', 6000, 6099, 0.00, NULL, NULL);

-- Add primary key
ALTER TABLE `acc_gl_category_control_types`
  ADD PRIMARY KEY (`id`);

-- Set auto_increment
ALTER TABLE `acc_gl_category_control_types`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

COMMIT;


DROP TABLE IF EXISTS `acc_pharma_drug_sales_glentries`;
-- Table: acc_pharma_drug_sales_glentries
CREATE TABLE `acc_pharma_drug_sales_glentries` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `drugSaleItemID` int(11) DEFAULT 0,
  `transactionNo` varchar(45) NOT NULL,
  `glAccountNo` varchar(45) NOT NULL,
  `postingDate` date NOT NULL,
  `amount` decimal(14,2) NOT NULL,
  `debitAmount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `creditAmount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `storeID` int(11) NOT NULL DEFAULT 2,
  `txnTypeID` int(11) NOT NULL,
  `dateAdded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `transactionDate` date NOT NULL,
  `userId` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- Trigger: after insert on acc_pharma_drug_sales_glentries
DELIMITER $$
CREATE TRIGGER `acc_pharma_drug_sales_glentries_AFTER_INSERT`
AFTER INSERT ON `acc_pharma_drug_sales_glentries`
FOR EACH ROW
BEGIN
    INSERT INTO `acc_gl_entries`
    (
        `transactionNo`,
        `glAccountNo`,
        `postingDate`,
        `amount`,
        `debitAmount`,
        `creditAmount`,
        `description`,
        `transactionDate`,
        `systemModule`,
        `userId`,
        `userName`
    )
    VALUES
    (
        NEW.transactionNo,
        NEW.glAccountNo,
        NEW.postingDate,
        NEW.amount,
        NEW.debitAmount,
        NEW.creditAmount,
        NEW.transactionNo,
        NEW.transactionDate,
        'PHARMACY-POS-BILLING',
        NEW.userId,
        'System'
    );
END
$$
DELIMITER ;

COMMIT;

-- ====================================================
-- RECREATE TABLE: acc_gl_entries
-- ====================================================

DROP TABLE IF EXISTS `acc_gl_entries`;

CREATE TABLE `acc_gl_entries` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `transactionNo` varchar(45) NOT NULL,
  `glAccountNo` varchar(45) NOT NULL,
  `postingDate` date NOT NULL,
  `amount` decimal(25,2) NOT NULL DEFAULT 0.00,
  `debitAmount` decimal(25,2) NOT NULL DEFAULT 0.00,
  `creditAmount` decimal(25,2) NOT NULL DEFAULT 0.00,
  `description` tinytext NOT NULL,
  `transactionDate` date NOT NULL,
  `systemModule` varchar(45) NOT NULL,
  `userId` int(11) NOT NULL,
  `userName` varchar(100) NOT NULL DEFAULT 'System Generated',
  `dateAdded` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;


-- ====================================================
-- TRIGGER: acc_gl_entries_AFTER_INSERT
-- ====================================================

DELIMITER $$

CREATE TRIGGER `acc_gl_entries_AFTER_INSERT`
AFTER INSERT ON `acc_gl_entries`
FOR EACH ROW
BEGIN
    DECLARE GLAccountID INT DEFAULT 0;
    DECLARE parGLAccount INT DEFAULT 0;
    DECLARE getPrimaryControlTypeID INT DEFAULT 0;
    DECLARE GLControlTypeID INT DEFAULT 0;

    -- Safely lookup account linkage
    SELECT 
        AGA.id, 
        AGA.par_id, 
        AGCT.primaryControlTypeID, 
        AGCT.id
    INTO 
        GLAccountID, 
        parGLAccount, 
        getPrimaryControlTypeID, 
        GLControlTypeID
    FROM acc_gl_accounts AGA
    JOIN acc_glaccount_setup AGS ON AGS.id = AGA.par_id
    JOIN acc_gl_category_control_types AGCT ON AGCT.id = AGS.par_id
    WHERE AGA.gl_account = NEW.glAccountNo
    LIMIT 1;

    -- Only continue if linkage found
    IF getPrimaryControlTypeID IS NOT NULL THEN
        CASE getPrimaryControlTypeID
            WHEN 1 THEN -- EQUITY
                IF NEW.debitAmount > 0 THEN
                    UPDATE acc_gl_accounts SET balance = balance - NEW.debitAmount WHERE gl_account = NEW.glAccountNo;
                    UPDATE acc_glaccount_setup SET balance = balance - NEW.debitAmount WHERE id = parGLAccount;
                    UPDATE acc_gl_category_control_types SET balance = balance - NEW.debitAmount WHERE id = GLControlTypeID;
                END IF;
                IF NEW.creditAmount > 0 THEN
                    UPDATE acc_gl_accounts SET balance = balance + NEW.creditAmount WHERE gl_account = NEW.glAccountNo;
                    UPDATE acc_glaccount_setup SET balance = balance + NEW.creditAmount WHERE id = parGLAccount;
                    UPDATE acc_gl_category_control_types SET balance = balance + NEW.creditAmount WHERE id = GLControlTypeID;
                END IF;

            WHEN 2 THEN -- REVENUE
                IF NEW.debitAmount > 0 THEN
                    UPDATE acc_gl_accounts SET balance = balance - NEW.debitAmount WHERE gl_account = NEW.glAccountNo;
                END IF;
                IF NEW.creditAmount > 0 THEN
                    UPDATE acc_gl_accounts SET balance = balance + NEW.creditAmount WHERE gl_account = NEW.glAccountNo;
                END IF;

            WHEN 3 THEN -- LIABILITY
                IF NEW.debitAmount > 0 THEN
                    UPDATE acc_gl_accounts SET balance = balance - NEW.debitAmount WHERE gl_account = NEW.glAccountNo;
                END IF;
                IF NEW.creditAmount > 0 THEN
                    UPDATE acc_gl_accounts SET balance = balance + NEW.creditAmount WHERE gl_account = NEW.glAccountNo;
                END IF;

            WHEN 4 THEN -- EXPENSE
                IF NEW.debitAmount > 0 THEN
                    UPDATE acc_gl_accounts SET balance = balance + NEW.debitAmount WHERE gl_account = NEW.glAccountNo;
                END IF;
                IF NEW.creditAmount > 0 THEN
                    UPDATE acc_gl_accounts SET balance = balance - NEW.creditAmount WHERE gl_account = NEW.glAccountNo;
                END IF;

            WHEN 5 THEN -- ASSETS
                IF NEW.debitAmount > 0 THEN
                    UPDATE acc_gl_accounts SET balance = balance + NEW.debitAmount WHERE gl_account = NEW.glAccountNo;
                END IF;
                IF NEW.creditAmount > 0 THEN
                    UPDATE acc_gl_accounts SET balance = balance - NEW.creditAmount WHERE gl_account = NEW.glAccountNo;
                END IF;

            ELSE
                -- Do nothing if unknown primary control type
                SET @msg = CONCAT('No matching case for primaryControlTypeID: ', getPrimaryControlTypeID);
            END CASE;
    END IF;
END$$

DELIMITER ;

COMMIT;



-- ====================================================
-- RECREATE TABLE: pharma_drug_sales_transactions
-- ====================================================

-- Drop table if it exists
DROP TABLE IF EXISTS `pharma_drug_sales_transactions`;
CREATE TABLE `pharma_drug_sales_transactions` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `storeID` int(10) UNSIGNED NOT NULL DEFAULT 2,
  `monthID` int(11) DEFAULT NULL,
  `yearID` int(11) DEFAULT NULL,
  `txnTypeID` int(11) DEFAULT NULL COMMENT 'Cash, Credit or Partly Cash Payment',
  `txnAmountPayable` decimal(13,2) DEFAULT NULL,
  `discountName` varchar(120) DEFAULT NULL COMMENT 'Sales to Members or Staff',
  `discountValue` decimal(10,2) DEFAULT NULL COMMENT 'Sales to Members or Staff',
  `txnAmountPaid` decimal(13,2) DEFAULT NULL,
  `discountedAmount` decimal(10,2) DEFAULT NULL COMMENT 'Sales to Members or Staff',
  `txnBalanceDue` decimal(13,2) DEFAULT NULL,
  `paymentStatus` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0 = Pending, 1 = Completed',
  `isDispensed` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0 = Not Dispensed, 1 = Dispensed',
  `finalAmountReceieved` decimal(12,2) NOT NULL DEFAULT 0.00,
  `discountTypeID` int(11) DEFAULT NULL,
  `UID` int(11) DEFAULT NULL,
  `UIP` varchar(120) DEFAULT NULL,
  `UPC` varchar(220) DEFAULT NULL,
  `totalItemsBought` int(11) DEFAULT NULL,
  `date_created` timestamp NOT NULL DEFAULT current_timestamp(),
  `txnReference` varchar(50) NOT NULL DEFAULT '' COMMENT 'Sales to Members or Staff',
  `paymentOptionName` varchar(50) NOT NULL DEFAULT '' COMMENT 'Sales to Members or Staff',
  `paymentOptionID` int(11) NOT NULL COMMENT 'Sales to Members or Staff',
  `txnNarration` tinytext NOT NULL COMMENT 'Sales to Members or Staff',
  `processedByName` varchar(100) DEFAULT NULL,
  `txnTypeName` varchar(120) DEFAULT NULL,
  `patientUID` int(11) DEFAULT 0,
  `patientName` varchar(120) DEFAULT 'General Client',
  `isSynched` int(11) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- Indexes
ALTER TABLE `pharma_drug_sales_transactions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `txn_reference` (`txnReference`);

-- Auto increment
ALTER TABLE `pharma_drug_sales_transactions`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

-- Drop triggers if exist
DROP TRIGGER IF EXISTS `PharmacyPOSTriggerOnUpdate`;
DROP TRIGGER IF EXISTS `acc_pharma_drug_sales_glentries_BFINSERT`;

-- Trigger after update
DELIMITER $$
CREATE TRIGGER `PharmacyPOSTriggerOnUpdate` AFTER UPDATE ON `pharma_drug_sales_transactions` 
FOR EACH ROW 
BEGIN
    DECLARE postingType INT;
    DECLARE DBpostDate DATE;
    DECLARE realPostDate DATE;
    DECLARE IPOPTransactionType INT; 
    DECLARE CheckEntryID INT;

    DECLARE billsPHARMAOP VARCHAR(10); 
    DECLARE billsEquityGLAccount VARCHAR(10);  

    SELECT glAccount, debitGLAccount 
      INTO billsPHARMAOP, billsEquityGLAccount 
      FROM acc_lookup_gltransactions 
     WHERE systemModule='PHARMAOP';  

    SELECT COUNT(id) 
      INTO CheckEntryID 
      FROM acc_pharma_drug_sales_glentries 
     WHERE drugSaleItemID = NEW.id 
     ORDER BY id DESC 
     LIMIT 1;

    SELECT isOPTxn 
      INTO IPOPTransactionType 
      FROM billing_tbl_transactions 
     WHERE txnReference = NEW.txnReference 
     ORDER BY id DESC 
     LIMIT 1;

    SELECT pickToday, postingDate 
      INTO postingType, DBpostDate 
      FROM acc_postingdate_setup 
     WHERE systemModule='BILLS';

    CASE postingType
        WHEN 1 THEN SET realPostDate = CURDATE();
        WHEN 0 THEN SET realPostDate = DBpostDate;
        ELSE SET realPostDate = CURDATE();
    END CASE;
    
    IF (CheckEntryID = 0 AND NEW.isSynched = 1) THEN
        INSERT INTO `acc_pharma_drug_sales_glentries`
        (`drugSaleItemID`, `transactionNo`, `glAccountNo`, `postingDate`, `amount`, `creditAmount`, `txnTypeID`, `transactionDate`, `userId`)
        VALUES
        (NEW.id, NEW.txnReference, billsPHARMAOP, realPostDate, NEW.finalAmountReceieved, NEW.finalAmountReceieved, NEW.txnTypeID, NEW.date_created, NEW.UID);

        INSERT INTO `acc_pharma_drug_sales_glentries`
        (`drugSaleItemID`, `transactionNo`, `glAccountNo`, `postingDate`, `amount`, `debitAmount`, `txnTypeID`, `transactionDate`, `userId`)
        VALUES
        (NEW.id, NEW.txnReference, billsEquityGLAccount, realPostDate, NEW.finalAmountReceieved, NEW.finalAmountReceieved, NEW.txnTypeID, NEW.date_created, NEW.UID);
    END IF;  
END
$$
DELIMITER ;

-- Trigger after insert
DELIMITER $$
CREATE TRIGGER `acc_pharma_drug_sales_glentries_BFINSERT` AFTER INSERT ON `pharma_drug_sales_transactions` 
FOR EACH ROW 
BEGIN
    DECLARE postingType INT;
    DECLARE DBpostDate DATE;
    DECLARE realPostDate DATE;
    DECLARE IPOPTransactionType INT; 
    DECLARE CheckEntryID INT;

    DECLARE billsPHARMAOP VARCHAR(10); 
    DECLARE billsEquityGLAccount VARCHAR(10);  

    SELECT glAccount, debitGLAccount 
      INTO billsPHARMAOP, billsEquityGLAccount 
      FROM acc_lookup_gltransactions 
     WHERE systemModule='PHARMAOP';  

    SELECT COUNT(id) 
      INTO CheckEntryID 
      FROM acc_pharma_drug_sales_glentries 
     WHERE drugSaleItemID = NEW.id 
     ORDER BY id DESC 
     LIMIT 1;

    SELECT isOPTxn 
      INTO IPOPTransactionType 
      FROM billing_tbl_transactions 
     WHERE txnReference = NEW.txnReference 
     ORDER BY id DESC 
     LIMIT 1;

    SELECT pickToday, postingDate 
      INTO postingType, DBpostDate 
      FROM acc_postingdate_setup 
     WHERE systemModule='BILLS';

    CASE postingType
        WHEN 1 THEN SET realPostDate = CURDATE();
        WHEN 0 THEN SET realPostDate = DBpostDate;
        ELSE SET realPostDate = CURDATE();
    END CASE;
    
    IF (CheckEntryID = 0) THEN
        INSERT INTO `acc_pharma_drug_sales_glentries`
        (`drugSaleItemID`, `transactionNo`, `glAccountNo`, `postingDate`, `amount`, `creditAmount`, `txnTypeID`, `transactionDate`, `userId`)
        VALUES
        (NEW.id, NEW.txnReference, billsPHARMAOP, realPostDate, NEW.finalAmountReceieved, NEW.finalAmountReceieved, NEW.txnTypeID, NEW.date_created, NEW.UID);

        INSERT INTO `acc_pharma_drug_sales_glentries`
        (`drugSaleItemID`, `transactionNo`, `glAccountNo`, `postingDate`, `amount`, `debitAmount`, `txnTypeID`, `transactionDate`, `userId`)
        VALUES
        (NEW.id, NEW.txnReference, billsEquityGLAccount, realPostDate, NEW.finalAmountReceieved, NEW.finalAmountReceieved, NEW.txnTypeID, NEW.date_created, NEW.UID);
    END IF;  
END
$$
DELIMITER ;

COMMIT;




-- ====================================================
-- RECREATE TABLE: acc_pharma_drug_sales_glentries
-- ====================================================

-- Drop table and trigger if they exist
DROP TRIGGER IF EXISTS `acc_pharma_drug_sales_glentries_AFTER_INSERT`;
DROP TABLE IF EXISTS `acc_pharma_drug_sales_glentries`;

-- Create table
CREATE TABLE `acc_pharma_drug_sales_glentries` (
  `id` bigint(20) NOT NULL,
  `drugSaleItemID` int(11) DEFAULT 0,
  `transactionNo` varchar(45) NOT NULL,
  `glAccountNo` varchar(45) NOT NULL,
  `postingDate` date NOT NULL,
  `amount` decimal(14,2) NOT NULL,
  `debitAmount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `creditAmount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `storeID` int(11) NOT NULL DEFAULT 2,
  `txnTypeID` int(11) NOT NULL,
  `dateAdded` timestamp NOT NULL DEFAULT current_timestamp(),
  `transactionDate` date NOT NULL,
  `userId` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- Add primary key
ALTER TABLE `acc_pharma_drug_sales_glentries`
  ADD PRIMARY KEY (`id`);

-- Set auto-increment
ALTER TABLE `acc_pharma_drug_sales_glentries`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

-- Create trigger after insert
DELIMITER $$
CREATE TRIGGER `acc_pharma_drug_sales_glentries_AFTER_INSERT` 
AFTER INSERT ON `acc_pharma_drug_sales_glentries` 
FOR EACH ROW 
BEGIN
    INSERT INTO `acc_gl_entries`
    (
        `transactionNo`,
        `glAccountNo`,
        `postingDate`,
        `amount`,
        `debitAmount`,
        `creditAmount`,
        `description`,
        `transactionDate`,
        `systemModule`,
        `userId`,
        `userName`
    )
    VALUES
    (
        NEW.transactionNo,
        NEW.glAccountNo,
        NEW.postingDate,
        NEW.amount,
        NEW.debitAmount,
        NEW.creditAmount,
        NEW.transactionNo,
        NEW.transactionDate,
        'PHARMACY-POS-BILLING',
        NEW.userId,
        'System'
    );
END
$$
DELIMITER ;

COMMIT;

### Step 105

ALTER TABLE acc_topup_petty_cash
ADD COLUMN approvedByUID INT(11) NOT NULL AFTER isConfirmed,
ADD COLUMN dateTimeApproved DATETIME NOT NULL AFTER approvedByUID;

### Step 106

ALTER TABLE visit_db ADD reopened_by INT NULL;
ALTER TABLE visit_db ADD reopened_at DATETIME NULL;
ALTER TABLE tbl_patient_checkin ADD reopened_by INT NULL;
ALTER TABLE tbl_patient_checkin ADD reopened_at DATETIME NULL;

### Step 107

-- Delete existing record with id = 1 if it exists
DELETE FROM tbl_patient_insurances
WHERE id = 1;

-- Insert new CASH record with current timestamp
INSERT INTO tbl_patient_insurances (
    id,
    insuranceID,
    insuranceSchemeID,
    insurancePatientID,
    insurancePolicyNumber,
    insurancePrincipalName,
    ContributorRelationship,
    UIP,
    UPC,
    UID,
    isActive,
    datePosted,
    insuranceMemberNumber,
    employerName
) VALUES (
    1,          -- id
    0,          -- insuranceID
    0,          -- insuranceSchemeID
    0,          -- insurancePatientID
    'CASH',     -- insurancePolicyNumber
    'CASH',     -- insurancePrincipalName
    'CASH',     -- ContributorRelationship
    NULL,       -- UIP
    NULL,       -- UPC
    6,          -- UID
    1,          -- isActive
    NOW(),      -- datePosted set to current timestamp
    NULL,       -- insuranceMemberNumber
    NULL        -- employerName
);

### Step 108

ALTER TABLE tbl_payment_option
ADD COLUMN payment_code VARCHAR(20) NOT NULL AFTER option_name;

ALTER TABLE tbl_payment_option
ADD COLUMN isActive TINYINT(1) NOT NULL DEFAULT 1 AFTER payment_code;

ALTER TABLE tbl_payment_option
ADD COLUMN uid INT(11) NOT NULL AFTER isActive,
ADD COLUMN uip VARCHAR(45) NOT NULL AFTER uid,
ADD COLUMN upc VARCHAR(255) NOT NULL AFTER uip;


ALTER TABLE billing_payment_options
ADD COLUMN paymentcategoryid INT(11) AFTER isActive;


ALTER TABLE billing_payment_options
ADD COLUMN uid INT(11) DEFAULT NULL,
ADD COLUMN uip VARCHAR(45) DEFAULT NULL,
ADD COLUMN upc VARCHAR(255) DEFAULT NULL;

ALTER TABLE system_audit_trail
MODIFY COLUMN visitReference TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
MODIFY COLUMN actionTaken TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;

ALTER TABLE eval_patient_referrals
ADD COLUMN referralDiagnosis TEXT NULL AFTER reasonForReferral;

ALTER TABLE eval_patient_history
MODIFY COLUMN docPatientAdvice TEXT;

ALTER TABLE tbl_faclities_configs
ADD COLUMN configHasDoctorNotes TINYINT(1) DEFAULT 0;

DROP TABLE IF EXISTS `facility_floors`;

CREATE TABLE `facility_floors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `facilityID` int(11) DEFAULT NULL,
  `floorName` varchar(50) NOT NULL DEFAULT '',
  `floorDescription` varchar(50) NOT NULL DEFAULT '',
  `status` int(11) NOT NULL DEFAULT 1,
  `dateadded` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `UID` int(11) NOT NULL,
  `UIP` varchar(50) NOT NULL DEFAULT '',
  `UPC` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

INSERT INTO `facility_floors` 
(`id`, `facilityID`, `floorName`, `floorDescription`, `status`, `dateadded`, `UID`, `UIP`, `UPC`) VALUES
(1, 1, 'In-patient Wing', 'In-patient Wing', 1, '2025-03-16 11:07:34', 0, '', ''),
(2, 1, 'Second Floor', 'ICU & Dialysis', 1, '2023-10-19 11:14:58', 0, '', ''),
(3, 1, 'Third Floor', 'Female & Paeds', 1, '2023-10-19 11:15:07', 0, '', ''),
(4, 1, 'Fourth Floor', 'Male Wards & Executive Suites', 1, '2023-10-19 11:15:27', 0, '', ''),
(5, 1, 'Fifth Floor', 'Fifth Floor', 1, '2023-10-14 06:15:37', 0, '', ''),
(6, 1, 'Ground Floor', 'Ground Floor', 1, '2023-10-14 06:15:37', 0, '', ''),
(7, 1, 'Morgue', 'Morgue', 1, '2025-03-26 05:42:53', 0, '', '');

ALTER TABLE `facility_floors`
  AUTO_INCREMENT = 8;

COMMIT;

### Step 109

ALTER TABLE inpatient_doctors_prescriptions
ADD COLUMN IsAdmissionDrug TINYINT(1) NOT NULL DEFAULT 0 AFTER IsDichargeDrug;

ALTER TABLE hr_company_employees 
MODIFY payroll_number VARCHAR(255) 
CHARACTER SET latin1 
COLLATE latin1_swedish_ci 
NULL;

ALTER TABLE tbl_faclities_configs
ADD COLUMN configHasInsurancePriceEditing TINYINT(1) DEFAULT 0;

ALTER TABLE `prmgt_payroll_paye_deductions`
ADD `HousingLevyAmount` DECIMAL(18,2) NULL DEFAULT 0.00 AFTER `NSSFAmount`;

### Step 110

ALTER TABLE tbl_faclities_configs
ADD COLUMN configHasSupplierManagement TINYINT(1) DEFAULT 0,
ADD COLUMN configHasMCHModule TINYINT(1) DEFAULT 0,
ADD COLUMN configHasRadiologyModule TINYINT(1) DEFAULT 0,
ADD COLUMN configHasTheatreModule TINYINT(1) DEFAULT 0;

### Step 111

ALTER TABLE tbl_faclities_configs
ADD COLUMN configHasDirectBillingServices TINYINT(1) DEFAULT 0,
ADD COLUMN configHasDirectBillingLaboratory TINYINT(1) DEFAULT 0,
ADD COLUMN configHasDirectBillingPharmacy TINYINT(1) DEFAULT 0,
ADD COLUMN configHasDirectBillingConsumables TINYINT(1) DEFAULT 0;

ALTER TABLE `tbl_insurance_firms`
ADD COLUMN `insurancesladepayercode` VARCHAR(100) NULL
AFTER `insuranceName`;

ALTER TABLE `invms_stkitem`
ADD COLUMN `isConsumable` TINYINT(1) NOT NULL DEFAULT 0
AFTER `isSHAPay`;

ALTER TABLE tbl_schemes_pricing 
ADD isConsumableItem TINYINT(1) NOT NULL DEFAULT 0 AFTER isPharmacyDrug;

ALTER TABLE billing_tbl_patient_deposits
ADD COLUMN refundedAmount DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER appliedAmount;


CREATE TABLE IF NOT EXISTS billing_tbl_deposit_audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    depositID INT NOT NULL,
    actionType VARCHAR(100) NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    narration TEXT,
    previousBalance DECIMAL(15,2),
    newBalance DECIMAL(15,2),
    performedBy INT NOT NULL,
    performedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);


ALTER TABLE tbl_sys_config
ADD COLUMN insuranceMinDeposit DECIMAL(10,2) NOT NULL DEFAULT 0,
ADD COLUMN cashMinDeposit DECIMAL(10,2) NOT NULL DEFAULT 0;


ALTER TABLE tbl_faclities_configs
ADD COLUMN configHasDoctorPriceAdjustment TINYINT(1) DEFAULT 0,
ADD COLUMN configHasPhysiotherapyPriceAdjustment TINYINT(1) DEFAULT 0,
ADD COLUMN configHasDentalPriceAdjustment TINYINT(1) DEFAULT 0,
ADD COLUMN configHasSendToDebtorsQueue TINYINT(1) DEFAULT 0;


CREATE TABLE `tbl_billing_guarantors` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `mainID` INT(11) NOT NULL,            -- visit_db.id (main visit reference)
    `totalbill` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `discount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `amountpaid` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `guarantorname` VARCHAR(255) NOT NULL,
    `idNumber` VARCHAR(50) DEFAULT NULL,
    `contact` VARCHAR(50) DEFAULT NULL,
    `Notes` TEXT,
    `UID` INT(11) NOT NULL,               -- user who created the record
    `status` VARCHAR(20) NOT NULL DEFAULT 'unpaid',  -- 'unpaid', 'partial', 'paid'
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `mainID_idx` (`mainID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


ALTER TABLE `visit_db`
ADD COLUMN `debtID` INT(11) NULL AFTER `isDebtBill`,
ADD COLUMN `debtCleared` TINYINT(1) NOT NULL DEFAULT 0 AFTER `debtID`;


ALTER TABLE tbl_patient_ward_checkin 
ADD COLUMN isDepositPaid INT(11) NULL DEFAULT 0;

ALTER TABLE tbl_registered_patients
ADD COLUMN IPNumber VARCHAR(40) 
CHARACTER SET latin1 
COLLATE latin1_swedish_ci 
NULL DEFAULT NULL AFTER patientIPOPNumber;

ALTER TABLE billing_tbl_patient_deposits
ADD COLUMN mpesaTxnCode VARCHAR(250) NULL AFTER depositReference;

ALTER TABLE `mch_medical_surgical_history`
MODIFY COLUMN `surgicalOperations` TEXT NOT NULL,
MODIFY COLUMN `diabetes` TEXT NULL,
MODIFY COLUMN `hypertension` TEXT NULL,
MODIFY COLUMN `UIP` TEXT NULL,
MODIFY COLUMN `UPC` TEXT NULL,
MODIFY COLUMN `bloodTransfusion` TEXT NULL,
MODIFY COLUMN `tuberculosis` TEXT NULL,
MODIFY COLUMN `drugAllergies` TEXT NULL,
MODIFY COLUMN `twinsHistory` TEXT NULL,
MODIFY COLUMN `tuberculosisHistory` TEXT NULL,
MODIFY COLUMN `diabetisHistory` TEXT NULL,
MODIFY COLUMN `hypertensionHistory` TEXT NULL;

ALTER TABLE hr_company_employees 
ADD COLUMN has_helb TINYINT(1) DEFAULT 0,
ADD COLUMN helb_monthly_limit double(10,2) DEFAULT 0,
ADD COLUMN has_arrears TINYINT(1) DEFAULT 0,
ADD COLUMN monthly_arrears double(10,2) DEFAULT 0,
ADD COLUMN has_locumbenefit TINYINT(1) DEFAULT 0,
ADD COLUMN locum_benefitamt double(10,2) DEFAULT 0;


ALTER TABLE `prmgt_staff_master_roll`
ADD COLUMN `helbamount` DECIMAL(10,2) DEFAULT 0 AFTER `HousingLevy`;


ALTER TABLE `prmgt_nhif_rates`
ADD COLUMN `tpercentage` DECIMAL(5,2) NULL AFTER `maxamt`,
ADD COLUMN `addedby` VARCHAR(45) NULL AFTER `tpercentage`;


ALTER TABLE prmgt_staff_master_roll
ADD COLUMN main_bank_id INT(11) NULL AFTER yearID;

### Step 112

DROP TABLE IF EXISTS `prmgt_payroll_helb_deductions`;

-- --------------------------------------------------------
-- Table structure for table `prmgt_payroll_helb_deductions`
-- --------------------------------------------------------
CREATE TABLE `prmgt_payroll_helb_deductions` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `employee_id` int(11) NOT NULL,
  `employee_name` varchar(255) NOT NULL,
  `payroll_number` varchar(120) NOT NULL,
  `department_id` int(11) NOT NULL,
  `month_id` int(11) NOT NULL,
  `year_id` int(11) NOT NULL,
  `basicPay` decimal(12,2) NOT NULL DEFAULT 0.00,
  `deduction_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `addedby` tinyint(3) UNSIGNED NOT NULL,
  `employeeDepartmentName` varchar(120) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Triggers for table `prmgt_payroll_helb_deductions`
-- --------------------------------------------------------
DELIMITER $$

CREATE TRIGGER `prmgt_payroll_helb_deductions_AFTER_INSERT`
AFTER INSERT ON `prmgt_payroll_helb_deductions`
FOR EACH ROW
BEGIN
    INSERT INTO `prmgt_employee_salgenerated` (
        `department_id`,
        `employee_id`,
        `month_id`,
        `year_id`,
        `cat_id`,
        `cat_name`,
        `cat_amount`,
        `item_id`,
        `addedby`,
        `paySlipItemTitle`
    ) VALUES (
        NEW.department_id,
        NEW.employee_id,
        NEW.month_id,
        NEW.year_id,
        15,
        'HELB',
        NEW.deduction_amount,
        NEW.id,
        NEW.addedby,
        'HELB DEDUCTION'
    );
END$$

CREATE TRIGGER `prmgt_payroll_helb_deductions_AFTER_UPDATE`
AFTER UPDATE ON `prmgt_payroll_helb_deductions`
FOR EACH ROW
BEGIN
    DECLARE helbExist INT DEFAULT 0;

    SELECT 1 INTO helbExist
    FROM prmgt_employee_salgenerated
    WHERE month_id = NEW.month_id
      AND employee_id = NEW.employee_id
      AND year_id = NEW.year_id
      AND item_id = NEW.id
      AND cat_id = 15
    LIMIT 1;

    IF helbExist != 1 THEN
        INSERT INTO `prmgt_employee_salgenerated` (
            `department_id`,
            `employee_id`,
            `month_id`,
            `year_id`,
            `cat_id`,
            `cat_name`,
            `cat_amount`,
            `item_id`,
            `addedby`
        ) VALUES (
            NEW.department_id,
            NEW.employee_id,
            NEW.month_id,
            NEW.year_id,
            15,
            'HELB',
            NEW.deduction_amount,
            NEW.id,
            NEW.addedby
        );
    ELSE
        UPDATE `prmgt_employee_salgenerated`
        SET `cat_amount` = NEW.deduction_amount
        WHERE `cat_id` = 15
          AND `item_id` = NEW.id
          AND `employee_id` = NEW.employee_id
          AND `month_id` = NEW.month_id
          AND `year_id` = NEW.year_id;
    END IF;
END$$

DELIMITER ;

-- --------------------------------------------------------
-- Indexes for table `prmgt_payroll_helb_deductions`
-- --------------------------------------------------------
ALTER TABLE `prmgt_payroll_helb_deductions`
  ADD PRIMARY KEY (`id`);

-- --------------------------------------------------------
-- AUTO_INCREMENT for table `prmgt_payroll_helb_deductions`
-- --------------------------------------------------------
ALTER TABLE `prmgt_payroll_helb_deductions`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

COMMIT;

### Step 113

DROP TABLE IF EXISTS `prmgt_payroll_arrears_earnings`;

-- --------------------------------------------------------
-- Table structure for table `prmgt_payroll_arrears_earnings`
-- --------------------------------------------------------
CREATE TABLE `prmgt_payroll_arrears_earnings` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `employee_id` int(11) NOT NULL,
  `employee_name` varchar(255) NOT NULL,
  `payroll_number` varchar(120) NOT NULL,
  `department_id` int(11) NOT NULL,
  `month_id` int(11) NOT NULL,
  `year_id` int(11) NOT NULL,
  `basicPay` decimal(12,2) NOT NULL DEFAULT 0.00,
  `earn_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `addedby` tinyint(3) UNSIGNED NOT NULL,
  `employeeDepartmentName` varchar(120) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Triggers for table `prmgt_payroll_arrears_earnings`
-- --------------------------------------------------------
DELIMITER $$

CREATE TRIGGER `prmgt_payroll_arrears_earnings_AFTER_INSERT`
AFTER INSERT ON `prmgt_payroll_arrears_earnings`
FOR EACH ROW
BEGIN
    INSERT INTO `prmgt_employee_salgenerated` (
        `department_id`,
        `employee_id`,
        `month_id`,
        `year_id`,
        `cat_id`,
        `cat_name`,
        `cat_amount`,
        `item_id`,
        `addedby`,
        `paySlipItemTitle`
    ) VALUES (
        NEW.department_id,
        NEW.employee_id,
        NEW.month_id,
        NEW.year_id,
        16,
        'ARREARS',
        NEW.earn_amount,
        NEW.id,
        NEW.addedby,
        'ARREARS EARNINGS'
    );
END$$

CREATE TRIGGER `prmgt_payroll_arrears_earnings_AFTER_UPDATE`
AFTER UPDATE ON `prmgt_payroll_arrears_earnings`
FOR EACH ROW
BEGIN
    DECLARE arrearsExist INT DEFAULT 0;

    SELECT 1 INTO arrearsExist
    FROM prmgt_employee_salgenerated
    WHERE month_id = NEW.month_id
      AND employee_id = NEW.employee_id
      AND year_id = NEW.year_id
      AND item_id = NEW.id
      AND cat_id = 16
    LIMIT 1;

    IF arrearsExist != 1 THEN
        INSERT INTO `prmgt_employee_salgenerated` (
            `department_id`,
            `employee_id`,
            `month_id`,
            `year_id`,
            `cat_id`,
            `cat_name`,
            `cat_amount`,
            `item_id`,
            `addedby`
        ) VALUES (
            NEW.department_id,
            NEW.employee_id,
            NEW.month_id,
            NEW.year_id,
            16,
            'ARREARS',
            NEW.earn_amount,
            NEW.id,
            NEW.addedby
        );
    ELSE
        UPDATE `prmgt_employee_salgenerated`
        SET `cat_amount` = NEW.earn_amount
        WHERE `cat_id` = 16
          AND `item_id` = NEW.id
          AND `employee_id` = NEW.employee_id
          AND `month_id` = NEW.month_id
          AND `year_id` = NEW.year_id;
    END IF;
END$$

DELIMITER ;

-- --------------------------------------------------------
-- Indexes for table `prmgt_payroll_arrears_earnings`
-- --------------------------------------------------------
ALTER TABLE `prmgt_payroll_arrears_earnings`
  ADD PRIMARY KEY (`id`);

-- --------------------------------------------------------
-- AUTO_INCREMENT for table `prmgt_payroll_arrears_earnings`
-- --------------------------------------------------------
ALTER TABLE `prmgt_payroll_arrears_earnings`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

COMMIT;





DROP TABLE IF EXISTS `prmgt_payroll_locum_earnings`;

-- --------------------------------------------------------
-- Table structure for table `prmgt_payroll_locum_earnings`
-- --------------------------------------------------------
CREATE TABLE `prmgt_payroll_locum_earnings` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `employee_id` int(11) NOT NULL,
  `employee_name` varchar(255) NOT NULL,
  `payroll_number` varchar(120) NOT NULL,
  `department_id` int(11) NOT NULL,
  `month_id` int(11) NOT NULL,
  `year_id` int(11) NOT NULL,
  `basicPay` decimal(12,2) NOT NULL DEFAULT 0.00,
  `locum_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `addedby` tinyint(3) UNSIGNED NOT NULL,
  `employeeDepartmentName` varchar(120) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Triggers for table `prmgt_payroll_locum_earnings`
-- --------------------------------------------------------
DELIMITER $$

CREATE TRIGGER `prmgt_payroll_locum_earnings_AFTER_INSERT`
AFTER INSERT ON `prmgt_payroll_locum_earnings`
FOR EACH ROW
BEGIN
    INSERT INTO `prmgt_employee_salgenerated` (
        `department_id`,
        `employee_id`,
        `month_id`,
        `year_id`,
        `cat_id`,
        `cat_name`,
        `cat_amount`,
        `item_id`,
        `addedby`,
        `paySlipItemTitle`
    ) VALUES (
        NEW.department_id,
        NEW.employee_id,
        NEW.month_id,
        NEW.year_id,
        17,
        'LOCUM',
        NEW.locum_amount,
        NEW.id,
        NEW.addedby,
        'LOCUM EARNINGS'
    );
END$$

CREATE TRIGGER `prmgt_payroll_locum_earnings_AFTER_UPDATE`
AFTER UPDATE ON `prmgt_payroll_locum_earnings`
FOR EACH ROW
BEGIN
    DECLARE locumExist INT DEFAULT 0;

    SELECT 1 INTO locumExist
    FROM prmgt_employee_salgenerated
    WHERE month_id = NEW.month_id
      AND employee_id = NEW.employee_id
      AND year_id = NEW.year_id
      AND item_id = NEW.id
      AND cat_id = 17
    LIMIT 1;

    IF locumExist != 1 THEN
        INSERT INTO `prmgt_employee_salgenerated` (
            `department_id`,
            `employee_id`,
            `month_id`,
            `year_id`,
            `cat_id`,
            `cat_name`,
            `cat_amount`,
            `item_id`,
            `addedby`
        ) VALUES (
            NEW.department_id,
            NEW.employee_id,
            NEW.month_id,
            NEW.year_id,
            17,
            'LOCUM',
            NEW.locum_amount,
            NEW.id,
            NEW.addedby
        );
    ELSE
        UPDATE `prmgt_employee_salgenerated`
        SET `cat_amount` = NEW.locum_amount
        WHERE `cat_id` = 17
          AND `item_id` = NEW.id
          AND `employee_id` = NEW.employee_id
          AND `month_id` = NEW.month_id
          AND `year_id` = NEW.year_id;
    END IF;
END$$

DELIMITER ;

-- --------------------------------------------------------
-- Indexes for table `prmgt_payroll_locum_earnings`
-- --------------------------------------------------------
ALTER TABLE `prmgt_payroll_locum_earnings`
  ADD PRIMARY KEY (`id`);

-- --------------------------------------------------------
-- AUTO_INCREMENT for table `prmgt_payroll_locum_earnings`
-- --------------------------------------------------------
ALTER TABLE `prmgt_payroll_locum_earnings`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

COMMIT;

### Cont

ALTER TABLE prmgt_staff_master_roll
ADD COLUMN arrearsAmount DECIMAL(10,2) DEFAULT 0 AFTER helbamount,
ADD COLUMN locumAmount DECIMAL(10,2) DEFAULT 0 AFTER arrearsAmount;



ALTER TABLE pharma_suppliers_debts
ADD COLUMN invoiceNumber VARCHAR(50) NULL AFTER supplierID,
ADD COLUMN invoiceNarration VARCHAR(255) NULL AFTER invoiceNumber,
ADD COLUMN date DATE NULL AFTER invoiceNarration;



-- Create table
CREATE TABLE `tbl_release_orders` (
  `id` int(11) NOT NULL,
  `visitID` varchar(150) DEFAULT NULL,
  `patientID` int(11) DEFAULT NULL,
  `releaseStatus` int(11) DEFAULT 0,
  `totalBillAmount` decimal(22,2) DEFAULT 0.00,
  `pendingBillAmount` decimal(22,2) DEFAULT 0.00,
  `UID` int(11) DEFAULT NULL,
  `UIP` varchar(140) DEFAULT NULL,
  `dateAdded` timestamp NULL DEFAULT current_timestamp(),
  `releasedByUID` int(11) DEFAULT NULL,
  `dateReleased` datetime DEFAULT NULL,
  `debtorBillAmount` decimal(22,2) DEFAULT 0.00,
  `releaseInformation` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Add primary key
ALTER TABLE `tbl_release_orders`
  ADD PRIMARY KEY (`id`);

-- Set AUTO_INCREMENT
ALTER TABLE `tbl_release_orders`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

COMMIT;



ALTER TABLE `visit_db`
ADD COLUMN `isReleased` INT(11) DEFAULT 0 AFTER `isInpatient`,
ADD COLUMN `isSentForRelease` INT(11) DEFAULT 0 AFTER `isReleased`,
ADD COLUMN `releaseDebtAmount` DECIMAL(22,2) DEFAULT 0.00 AFTER `isSentForRelease`;


ALTER TABLE tbl_sys_config
ADD COLUMN reportFontSize VARCHAR(20) NULL;


ALTER TABLE tbl_faclities_configs
ADD COLUMN configHasPharmacyDeleteDrug TINYINT(1) DEFAULT 0;

ALTER TABLE billing_tbl_transactions
ADD COLUMN isSentToBilling TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Flag to indicate if transaction has been sent to billing';

ALTER TABLE wards_ward_listings
ADD COLUMN CStoreID INT(3) NULL AFTER StoreID;


ALTER TABLE tbl_faclities_configs
ADD COLUMN configHasPharmacyNewPrescription TINYINT(1) DEFAULT 0;

### Step 114

### Library installation for Local Purchase Order Document

composer require dompdf/dompdf

[HEAD OVER TO PAGE 3](https://hackmd.io/@--LognktRUmsG6YHux0Jjw/Syq1i7-N-e)