# 🏥 SHA Integration Setup(HOSIPOA HMIS)

This guide provides step-by-step instructions for integrating SHA into your health system(HOSIPOA), including required Composer packages and database schema changes.

---

## IMPORTANT: Steps to Take When Going Live

### Generating DHA Keys (Windows, Linux, macOS)

You will need OpenSSL installed on your system. Most Linux and macOS systems have it pre-installed. On Windows, you can use Git Bash or install OpenSSL separately.

### 1. Generate a 2048-bit RSA private key

Open your terminal (Command Prompt, PowerShell, Git Bash, or macOS Terminal) and run:

openssl genpkey -algorithm RSA -out private_key.pem -pkeyopt rsa_keygen_bits:2048

This will create a file named private_key.pem in your current directory. Keep this file secure.

### 2. Extract the corresponding public key
Run the following command in the same terminal:

openssl rsa -pubout -in private_key.pem -out public_key.pem

This will generate public_key.pem, which you can share with the DHA system as required.

# **Additional Notes:**

Ensure you run these commands in a directory where you have write permissions.

Do not share your private key (private_key.pem) with anyone.

These steps work the same on Windows (Git Bash), Linux, and macOS.

## 📦 Step 1: Install Required Composer Library

Install `phpseclib` for RSA and AES encryption/decryption:

  composer require phpseclib/phpseclib:^3.0

## 📦 Step 2: Install Required Composer Library(Only if you want to update your database with the sha spreadsheet surgical services data)

Install `phpoffice` for php spreadsheet

  composer require phpoffice/phpspreadsheet

## THIS IS A MUST OTHERWISE PAYROLL WILL NOT OUTPUT
Install `mpdf` for payslip pdf

  composer require mpdf/mpdf

---

## Etims First

ALTER TABLE `tbl_patient_billable_items`
ADD COLUMN `etimsSubmitted` VARCHAR(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT '0',
ADD COLUMN `isCreditNoteSubmitted` TINYINT(1) NOT NULL DEFAULT 0 AFTER `etimsSubmitted`,
ADD COLUMN `etimsSubmittedBy` INT(11) NULL DEFAULT NULL AFTER `isCreditNoteSubmitted`,
ADD COLUMN `creditNoteSubmittedBy` INT(11) NULL DEFAULT NULL AFTER `etimsSubmittedBy`,
ADD COLUMN `mediator_id` VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL AFTER `creditNoteSubmittedBy`,
ADD COLUMN `consumableStoreID` INT(11) NULL DEFAULT NULL AFTER `mediator_id`,
ADD COLUMN `billItemDiscount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER `consumableStoreID`,
ADD COLUMN `totalPayableAfterDiscount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER `billItemDiscount`,
ADD COLUMN `originalQuantity` INT(11) NULL DEFAULT NULL AFTER `totalPayableAfterDiscount`,
ADD COLUMN `originalAmount` DECIMAL(10,2) NULL DEFAULT NULL AFTER `originalQuantity`,
ADD COLUMN `isOPBillItem` INT(11) NOT NULL DEFAULT 1 AFTER `originalAmount`;

ALTER TABLE `invms_stkitem`
ADD COLUMN `etimsItemCode` VARCHAR(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
ADD COLUMN `country_code` VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL AFTER `etimsItemCode`,
ADD COLUMN `itemtype_code` VARCHAR(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL AFTER `country_code`,
ADD COLUMN `taxtype_code` VARCHAR(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL AFTER `itemtype_code`,
ADD COLUMN `packaging_code` VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL AFTER `taxtype_code`,
ADD COLUMN `unit_code` VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL AFTER `packaging_code`,
ADD COLUMN `procedureclassification_code` VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL AFTER `unit_code`;

ALTER TABLE `lab_specimen_tests`
ADD COLUMN `etimsItemCode` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL AFTER `testCategoryTypeID`,
ADD COLUMN `itemClassification` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL AFTER `etimsItemCode`,
ADD COLUMN `countryID` VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL AFTER `itemClassification`;


ALTER TABLE `setup_procedures`
ADD COLUMN `country_code` VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL ,
ADD COLUMN `procedureclassification_code` VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL AFTER `country_code`,
ADD COLUMN `etimsItemCode` VARCHAR(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL AFTER `procedureclassification_code`,
ADD COLUMN `age_restriction_label` VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL AFTER `etimsItemCode`;

### Cont

-- ==============================================
-- Table structure for table `etims_invoices`
-- ==============================================

CREATE TABLE `etims_invoices` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `etimsInvoiceNo` VARCHAR(50) NOT NULL UNIQUE,
  `traderInvoiceNo` VARCHAR(50) NOT NULL UNIQUE,
  `totalAmount` DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  `totalTaxableAmount` DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  `totalTaxAmount` DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  `paymentType` VARCHAR(10) NOT NULL,
  `salesTypeCode` VARCHAR(5) NOT NULL,
  `receiptTypeCode` VARCHAR(5) NOT NULL,
  `salesStatusCode` VARCHAR(5) NOT NULL,
  `salesDate` DATETIME NOT NULL,
  `currency` VARCHAR(5) NOT NULL DEFAULT 'KES',
  `invoiceVerificationUrl` TEXT NOT NULL,
  `scuReceiptNo` VARCHAR(50) NOT NULL,
  `customerPin` VARCHAR(50) DEFAULT NULL,
  `signature` VARCHAR(100) NOT NULL,
  `internalData` TEXT NOT NULL,
  `customerName` VARCHAR(255) NOT NULL,
  `scdcId` VARCHAR(50) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB 
  DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_general_ci;

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

-- ==============================================
-- Table structure for table `etims_credit_notes`
-- ==============================================

CREATE TABLE `etims_credit_notes` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `creditNoteNo` VARCHAR(50) NOT NULL UNIQUE,
  `traderInvoiceNo` VARCHAR(50) NOT NULL,
  `traderOrgInvoiceNo` VARCHAR(50) NOT NULL,
  `totalAmount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `totalTaxableAmount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `totalTaxAmount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `paymentType` VARCHAR(10) NOT NULL,
  `salesTypeCode` VARCHAR(5) NOT NULL,
  `receiptTypeCode` VARCHAR(5) NOT NULL,
  `salesStatusCode` VARCHAR(5) NOT NULL DEFAULT '02',
  `salesDate` DATETIME NOT NULL,
  `currency` VARCHAR(10) NOT NULL DEFAULT 'KES',
  `invoiceVerificationUrl` TEXT DEFAULT NULL,
  `scuReceiptNo` VARCHAR(50) DEFAULT NULL,
  `customerPin` VARCHAR(20) DEFAULT NULL,
  `signature` TEXT DEFAULT NULL,
  `internalData` TEXT DEFAULT NULL,
  `customerName` VARCHAR(255) NOT NULL,
  `scdcId` VARCHAR(50) DEFAULT NULL,
  `createdAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB 
  DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_general_ci;

-- ==============================================
-- Indexes
-- ==============================================
CREATE INDEX `idx_traderInvoiceNo` ON `etims_credit_notes` (`traderInvoiceNo`);
CREATE INDEX `idx_salesDate` ON `etims_credit_notes` (`salesDate`);

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

# **NB: FOLLOW THE SQL STEPS EXACTLY AS THEY ARE. DO NOT SKIP ANY STEP**

## 🗃 Step 3: Update Your Database Schema

### 1. Add SHA Eligibility Columns to `tbl_registered_patients`

ALTER TABLE tbl_registered_patients
ADD COLUMN raw_json_response JSON DEFAULT NULL COMMENT '🧾 Full raw JSON received from DHA';

ALTER TABLE tbl_registered_patients 
ADD COLUMN iseligible TINYINT(1) DEFAULT 0 COMMENT '✅ 1 if patient is eligible under SHA, 0 otherwise';

ALTER TABLE tbl_registered_patients 
ADD COLUMN cr_number VARCHAR(50) DEFAULT NULL COMMENT '🆔 Client Registry Number from SHA',
ADD COLUMN coverage_valid_until DATETIME DEFAULT NULL COMMENT '📅 Coverage end date for SHA eligibility';

ALTER TABLE tbl_registered_patients ADD COLUMN is_dependant TINYINT(1) DEFAULT 0;

ALTER TABLE tbl_registered_patients
ADD UNIQUE (cr_number);

### 2.👨‍⚕️ Create `tbl_practitioner_details` Table

CREATE TABLE tbl_practitioner_details (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  raw_json JSON NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES tbl_users(id)
);

### 3. 📋 Add DHA Metadata Columns to `setup_procedures`

ALTER TABLE setup_procedures
ADD COLUMN needs_pre_auth ENUM('Yes', 'No') NOT NULL DEFAULT 'No',
ADD COLUMN service_type ENUM('Outpatient', 'Inpatient', 'Both') NOT NULL DEFAULT 'Outpatient',
ADD COLUMN service_gender ENUM('Male', 'Female', 'Both') NOT NULL DEFAULT 'Both',
ADD COLUMN category_of_health_services ENUM('Inpatient', 'Outpatient','None') DEFAULT NULL COMMENT 'Define if procedure is for Inpatient, Outpatient or None',
ADD COLUMN age_restriction_code VARCHAR(50) DEFAULT 'all';

### 4. 🏥 Add DHA Metadata Columns to `tbl_facilites`

ALTER TABLE tbl_facilites
ADD COLUMN dha_facility_code VARCHAR(20) NULL,
ADD COLUMN dha_found TINYINT(1) NULL,
ADD COLUMN dha_approved TINYINT(1) NULL,
ADD COLUMN dha_facility_level VARCHAR(100) NULL,
ADD COLUMN dha_operational_status VARCHAR(100) NULL,
ADD COLUMN dha_license_expiry_date DATE NULL;

### 5. Create `tbl_patient_dependants` Table

CREATE TABLE `tbl_patient_dependants` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `principal_patient_id` INT NOT NULL,  -- FK to tbl_registered_patients.id
  `principal_cr_number` VARCHAR(50) DEFAULT NULL, -- e.g. CRxxxx from SHA
  `dependant_cr_number` VARCHAR(50) DEFAULT NULL, -- unique SHA CR ID
  `first_name` VARCHAR(100) NOT NULL,
  `middle_name` VARCHAR(100) DEFAULT NULL,
  `last_name` VARCHAR(100) NOT NULL,
  `gender` ENUM('Male', 'Female', 'Other') DEFAULT NULL,
  `dob` DATE DEFAULT NULL,
  `relationship` VARCHAR(50) DEFAULT NULL,  -- e.g. Child, Spouse
  `raw_json` TEXT DEFAULT NULL,             -- Full SHA response for reference
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  FOREIGN KEY (`principal_patient_id`) REFERENCES `tbl_registered_patients`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  UNIQUE KEY `uq_dependant_cr` (`dependant_cr_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Step 1: Add the columns
ALTER TABLE tbl_patient_dependants 
ADD COLUMN identification_type VARCHAR(100) DEFAULT NULL,
ADD COLUMN identification_number VARCHAR(100) DEFAULT NULL,
ADD COLUMN dependant_patient_id INT(11) NOT NULL UNIQUE AFTER dependant_cr_number;

-- Step 2: Add the foreign key constraint
ALTER TABLE tbl_patient_dependants 
ADD CONSTRAINT fk_dependant_patient 
FOREIGN KEY (dependant_patient_id) REFERENCES tbl_registered_patients(id);

### 6. Create `tbl_sha_claims_submission`

CREATE TABLE tbl_sha_claim_submissions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    mediator_id VARCHAR(100) NOT NULL UNIQUE,
    patient_id INT NOT NULL,
    id_number VARCHAR(50) NOT NULL,
    visit_reference VARCHAR(100) NOT NULL,
    claim_id VARCHAR(100) NOT NULL,
    submitted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    response_json TEXT
);

ALTER TABLE tbl_sha_claim_submissions
ADD COLUMN last_retry_attempt DATETIME NULL AFTER submitted_at,
ADD COLUMN claim_status VARCHAR(50) AFTER response_json,
ADD COLUMN claim_outcome VARCHAR(100) DEFAULT NULL AFTER claim_status;

ALTER TABLE tbl_sha_claim_submissions
ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER response_json,
ADD COLUMN is_approved TINYINT(1) DEFAULT 0,
ADD UNIQUE (visit_reference);

ALTER TABLE theatre_request_list ADD COLUMN shaPreAuthSent TINYINT(1) DEFAULT 0;

### 7. Create `tbl_sha_inpatientpreauthclaims`

CREATE TABLE tbl_sha_inpatientpreauthclaims (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    mediator_id VARCHAR(100) NOT NULL UNIQUE,
    patient_id INT NOT NULL,
    id_number VARCHAR(50) NOT NULL,
    visit_reference VARCHAR(100) NOT NULL,
    claim_id VARCHAR(100) NOT NULL,
    preauth_claim_id VARCHAR(100) DEFAULT NULL,
    submitted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_retry_attempt DATETIME NULL,
    response_json TEXT,
    claim_status VARCHAR(50),
    claim_outcome VARCHAR(100) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_approved TINYINT(1) DEFAULT 0,
    UNIQUE (visit_reference)
);

### 8. Create `tbl_sha_outpatientpreauthclaims`

CREATE TABLE tbl_sha_outpatientpreauthclaims (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    mediator_id VARCHAR(100) NOT NULL UNIQUE,
    patient_id INT NOT NULL,
    id_number VARCHAR(50) NOT NULL,
    visit_reference VARCHAR(100) NOT NULL,
    claim_id VARCHAR(100) NOT NULL,
    preauth_claim_id VARCHAR(100) DEFAULT NULL,
    submitted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_retry_attempt DATETIME NULL,
    response_json TEXT,
    claim_status VARCHAR(50),
    claim_outcome VARCHAR(100) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_approved TINYINT(1) DEFAULT 0,
    UNIQUE (visit_reference)
);

### 9. Create `tbl_sha_outpatientpreauthclaims_submissions`

CREATE TABLE tbl_sha_outpatientpreauthclaims_submissions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    mediator_id VARCHAR(100) NOT NULL UNIQUE,
    patient_id INT NOT NULL,
    id_number VARCHAR(50) NOT NULL,
    visit_reference VARCHAR(100) NOT NULL,
    claim_id VARCHAR(100) NOT NULL,
    shaPreAuthSent TINYINT(1) DEFAULT 0,
    submitted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    response_json TEXT
);

ALTER TABLE tbl_sha_outpatientpreauthclaims_submissions
ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER response_json,
ADD COLUMN is_approved TINYINT(1) DEFAULT 0,
ADD COLUMN last_retry_attempt DATETIME NULL AFTER submitted_at,
ADD COLUMN claim_status VARCHAR(50) AFTER response_json,
ADD COLUMN claim_outcome VARCHAR(100) DEFAULT NULL AFTER claim_status,
ADD UNIQUE (visit_reference);

### 10. Create `tbl_sha_preauthclaims_submissions`

CREATE TABLE tbl_sha_preauthclaims_submissions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    mediator_id VARCHAR(100) NOT NULL UNIQUE,
    patient_id INT NOT NULL,
    id_number VARCHAR(50) NOT NULL,
    visit_reference VARCHAR(100) NOT NULL UNIQUE,
    claim_id VARCHAR(100) NOT NULL,
    submitted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    response_json TEXT
);

ALTER TABLE tbl_sha_preauthclaims_submissions
ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER response_json,
ADD COLUMN is_approved TINYINT(1) DEFAULT 0,
ADD COLUMN last_retry_attempt DATETIME NULL AFTER submitted_at,
ADD COLUMN claim_status VARCHAR(50) AFTER response_json,
ADD COLUMN claim_outcome VARCHAR(100) DEFAULT NULL AFTER claim_status,
ADD UNIQUE (visit_reference);

### 11. Create `tbl_sha_fhir_submissions`

CREATE TABLE tbl_sha_fhir_submissions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    mediator_id VARCHAR(100) NOT NULL UNIQUE,
    patient_id INT NOT NULL,
    id_number VARCHAR(50) NOT NULL,
    visit_reference VARCHAR(100) NOT NULL,
    claim_id VARCHAR(100) NOT NULL,
    submitted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    response_json TEXT,
    sent_flag TINYINT(1) DEFAULT 0
);

### 12. Create `tbl_facility_levels`

CREATE TABLE tbl_facility_levels (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

INSERT INTO tbl_facility_levels (id, name) VALUES
(1, 'Level 1'),
(2, 'Level 2'),
(3, 'Level 3'),
(4, 'Level 4'),
(5, 'Level 5'),
(6, 'Level 6');

### 13. Create setup_procedure_tariffs

CREATE TABLE setup_procedure_tariffs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    procedure_id INT NOT NULL,
    facility_level_id INT NOT NULL,
    tariff_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (procedure_id) REFERENCES setup_procedures(id),
    FOREIGN KEY (facility_level_id) REFERENCES tbl_facility_levels(id),
    UNIQUE (procedure_id, facility_level_id)
);

### 14. Create tbl_uploaded_documents

CREATE TABLE `tbl_uploaded_documents` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `patient_id` INT NOT NULL,
    `visit_reference` VARCHAR(50) NOT NULL,
    `document_name` VARCHAR(255) NOT NULL,
    `document_path` VARCHAR(500) NOT NULL,
    `uploaded_by` INT DEFAULT NULL,
    `upload_timestamp` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `document_type` VARCHAR(50) DEFAULT 'PreAuth',  -- e.g. 'PreAuth', 'Claim', etc.
    `status` VARCHAR(50) DEFAULT 'active',          -- optional: active/archived/deleted
    FOREIGN KEY (`patient_id`) REFERENCES `tbl_registered_patients`(`id`) ON DELETE CASCADE
);

### 15. Create lab_test_tariffs

CREATE TABLE `lab_test_tariffs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `test_id` INT NOT NULL,
  `facility_level_id` INT NOT NULL,
  `tariff_amount` DECIMAL(10,2) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`test_id`) REFERENCES `lab_specimen_tests`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`facility_level_id`) REFERENCES `tbl_facility_levels`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `uq_labtest_facility_level` (`test_id`, `facility_level_id`)
);

### 16. Create invms_stkitem_tariffs

CREATE TABLE invms_stkitem_tariffs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_id INT NOT NULL,
    facility_level_id INT NOT NULL,
    tariff_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (item_id) REFERENCES invms_stkitem(id),
    FOREIGN KEY (facility_level_id) REFERENCES tbl_facility_levels(id),
    UNIQUE (item_id, facility_level_id)
);

### 17. Create Table tbl_mobile_payment_transactions

CREATE TABLE `tbl_mobile_payment_transactions` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `userID` INT(11) NOT NULL,
  `visitID` VARCHAR(50) NOT NULL DEFAULT '',
  `transactingCurrency` VARCHAR(19) DEFAULT 'KES',
  `txnReference` VARCHAR(19) NOT NULL,
  `txnAmount` DECIMAL(20,2) NOT NULL,
  `txnDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `MERCHANT_TRANSACTION_ID` VARCHAR(255) NOT NULL,
  `txnStatus` INT(11) NOT NULL DEFAULT 0,
  `txnStatusName` VARCHAR(30) NOT NULL DEFAULT 'PENDING',
  `userPhone` VARCHAR(34) DEFAULT NULL,
  `UIP` VARCHAR(224) DEFAULT NULL,
  `UPC` VARCHAR(224) DEFAULT NULL,
  `transactionTypeID` INT(11) DEFAULT 0,
  `isSynced` INT(11) NOT NULL DEFAULT 0,
  `mpesaReferenceID` VARCHAR(80) DEFAULT NULL,
  `mpesaIPNStatus` VARCHAR(50) DEFAULT NULL,
  `autoVerified` TINYINT(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_txnReference` (`txnReference`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

#### 17.1

ALTER TABLE tbl_mobile_payment_transactions
ADD payerName VARCHAR(255) DEFAULT NULL,
ADD COLUMN isStrayTransaction TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = Offline (STK Manual), 0 = STK Push',
ADD COLUMN billReferenceNumber VARCHAR(255) NULL;

ALTER TABLE tbl_mobile_payment_transactions DROP INDEX uniq_txnReference;

### 18 . Modify setup_facility_departments

ALTER TABLE setup_facility_departments
ADD COLUMN type_code VARCHAR(50) DEFAULT NULL,
ADD COLUMN type_display VARCHAR(255) DEFAULT NULL;

-- TRIAGE & DOCTORS
UPDATE setup_facility_departments SET type_code = '185389009', type_display = 'Triage'
WHERE departmentName = 'TRIAGE & DOCTORS';

-- DOCTORS
UPDATE setup_facility_departments SET type_code = '408443003', type_display = 'General medical service'
WHERE departmentName = 'DOCTORS';

-- DENTAL
UPDATE setup_facility_departments SET type_code = '408440000', type_display = 'Dental service'
WHERE departmentName = 'DENTAL';

-- MCH
UPDATE setup_facility_departments SET type_code = '408475000', type_display = 'Maternal health clinic service'
WHERE departmentName = 'MCH';

-- OPTICAL/OPTHALMOLOGIST
UPDATE setup_facility_departments SET type_code = '284548002', type_display = 'Ophthalmology service'
WHERE departmentName = 'OPTICAL/OPTHALMOLOGIST';

-- RADIOLOGY
UPDATE setup_facility_departments SET type_code = '408478003', type_display = 'Radiology service'
WHERE departmentName = 'RADIOLOGY';

-- MALE WARD
UPDATE setup_facility_departments SET type_code = '225728007', type_display = 'Male ward'
WHERE departmentName = 'MALE WARD';

-- LABORATORY
UPDATE setup_facility_departments SET type_code = '408459003', type_display = 'Laboratory service'
WHERE departmentName = 'LABORATORY';

-- PHARMACY
UPDATE setup_facility_departments SET type_code = '440655000', type_display = 'Pharmacy service'
WHERE departmentName = 'PHARMACY';

-- ULTRASOUND
UPDATE setup_facility_departments SET type_code = '408472002', type_display = 'Diagnostic ultrasound service'
WHERE departmentName = 'ULTRASOUND';

-- THEATRE/ SURGICAL
UPDATE setup_facility_departments SET type_code = '409967002', type_display = 'Surgical service'
WHERE departmentName = 'THEATRE/ SURGICAL';

-- CASUALTY
UPDATE setup_facility_departments SET type_code = '225728007', type_display = 'Emergency department (casualty)'
WHERE departmentName = 'CASUALTY';

-- PEADS WARD
UPDATE setup_facility_departments SET type_code = '225728002', type_display = 'Pediatric ward'
WHERE departmentName = 'PEADS WARD';

-- PEADS ANNEX
UPDATE setup_facility_departments SET type_code = '225728002', type_display = 'Pediatric ward'
WHERE departmentName = 'PEADS ANNEX';

-- MATERNITY WARD ONE
UPDATE setup_facility_departments SET type_code = '225728003', type_display = 'Maternity ward'
WHERE departmentName = 'MATERNITY WARD ONE';

-- MATERNITY
UPDATE setup_facility_departments SET type_code = '408476004', type_display = 'Maternity service'
WHERE departmentName = 'MATERNITY';

-- NUTRITION
UPDATE setup_facility_departments SET type_code = '408470000', type_display = 'Dietetic service'
WHERE departmentName = 'NUTRITION';

-- DIALYSIS
UPDATE setup_facility_departments SET type_code = '310131000000108', type_display = 'Renal dialysis service'
WHERE departmentName = 'DIALYSIS';

-- PHYSIOTHERAPY
UPDATE setup_facility_departments SET type_code = '408454008', type_display = 'Physiotherapy service'
WHERE departmentName = 'PHYSIOTHERAPY';

-- INPATIENT
UPDATE setup_facility_departments SET type_code = '225728001', type_display = 'Inpatient service'
WHERE departmentName = 'INPATIENT';

-- CCC
UPDATE setup_facility_departments SET type_code = '736253002', type_display = 'HIV care clinic service'
WHERE departmentName = 'CCC';

### 19. Alter tbl_disease_symptoms

ALTER TABLE tbl_disease_symptoms
ADD COLUMN snomedCode VARCHAR(20) NULL,
ADD COLUMN snomed_display_term VARCHAR(255) NULL;

### Important #1

ALTER TABLE invms_stkitem 
ADD COLUMN IF NOT EXISTS ciel_code VARCHAR(50) NULL,
ADD COLUMN IF NOT EXISTS rxnorm_code VARCHAR(50) NULL;

UPDATE setup_procedures 
SET procedureName = REPLACE(procedureName, '\\\'', '\'') 
WHERE procedureName LIKE '%\\\'%';

### Suggested Indexing

CREATE INDEX idx_principal_id ON tbl_patient_dependants(principal_patient_id);
CREATE INDEX idx_dependant_cr ON tbl_patient_dependants(dependant_cr_number);

-- Critical joins and filters
CREATE INDEX idx_visit_isinpatient_closed_payerid ON visit_db(isInpatient, closed, payer_id);
CREATE INDEX idx_patient_insurance_schemeid ON tbl_patient_insurances(id, insuranceSchemeID);
CREATE INDEX idx_insurance_schemes_firmid ON tbl_insurance_schemes(id, insuranceFirmID);

-- Searchable fields
CREATE INDEX idx_registered_patients_search ON tbl_registered_patients(patientIPOPNumber, firstName, lastName, middleName);

-- Optional: for etims lookup
CREATE INDEX idx_billable_items_refcode_submitted ON tbl_patient_billable_items(billReferenceCode, etimsSubmittedBy, creditNoteSubmittedBy);

### **Note**: Indexes were added to optimize SHA Claims loading performance, especially after implementing logic to exclude procedures requiring pre-authorization from this interface.

CREATE INDEX idx_fib_billingStatus ON finance_insurance_bills(billingStatus);
CREATE INDEX idx_fib_insuranceID ON finance_insurance_bills(insuranceID);
CREATE INDEX idx_fib_insuranceSchemeID ON finance_insurance_bills(insuranceSchemeID);
CREATE INDEX idx_fib_patientID ON finance_insurance_bills(patientID);
CREATE INDEX idx_fib_visitIDReference ON finance_insurance_bills(visitIDReference);
CREATE INDEX idx_fib_dateCreated ON finance_insurance_bills(dateCreated);
CREATE INDEX idx_fib_dateCancelled ON finance_insurance_bills(dateCancelled);
CREATE INDEX idx_fib_dateDispatched ON finance_insurance_bills(dateDispatched);
CREATE INDEX idx_fib_dateDeclined ON finance_insurance_bills(dateDeclined);
CREATE INDEX idx_fib_datePaid ON finance_insurance_bills(datePaid);
CREATE INDEX idx_rp_id ON tbl_registered_patients(id);
CREATE INDEX idx_rp_insuranceMemberNumber ON tbl_registered_patients(insuranceMemberNumber);
CREATE INDEX idx_gif_id ON tbl_insurance_firms(id);
CREATE INDEX idx_tis_id ON tbl_insurance_schemes(id);

CREATE INDEX idx_sha_visit_reference ON tbl_sha_claim_submissions(visit_reference);
CREATE INDEX idx_pbi_reference_item ON tbl_patient_billable_items(billReferenceCode, isProcedureItem, billItemName);
CREATE INDEX idx_sp_procedure_name_pre_auth ON setup_procedures(procedureName, needs_pre_auth);

---

## ✅ Summary

This setup ensures:

* You can securely decrypt patient records using RSA & AES.
* Facilities and practitioners can be enriched with DHA data.
* SHA eligibility status and coverage validity are tracked directly in the patient table.

Make sure to:

* Use secure `.pem` keys for decryption.
* Validate and sanitize all incoming data.
* Test API credentials and tokens in **UAT** before moving to **Production**.

---

# SMART,SLADE,M-PESA,& EMAIL(DB Changes)

### Step 1 Alter table *tbl_sys_config*

ALTER TABLE tbl_sys_config 
ADD COLUMN mpesa_bill_no VARCHAR(50) DEFAULT NULL,
ADD COLUMN mpesa_passkey TEXT DEFAULT NULL,
ADD COLUMN mpesa_consumer_key TEXT DEFAULT NULL,
ADD COLUMN mpesa_consumer_secret TEXT DEFAULT NULL,
ADD COLUMN mail_driver VARCHAR(50) DEFAULT NULL,
ADD COLUMN mail_host VARCHAR(100) DEFAULT NULL,
ADD COLUMN mail_port VARCHAR(10) DEFAULT NULL,
ADD COLUMN mail_username VARCHAR(100) DEFAULT NULL,
ADD COLUMN mail_password VARCHAR(100) DEFAULT NULL,
ADD COLUMN mail_encryption VARCHAR(20) DEFAULT NULL,
ADD COLUMN mail_from_address VARCHAR(100) DEFAULT NULL,
ADD COLUMN mail_from_name VARCHAR(100) DEFAULT NULL,
ADD COLUMN at_username VARCHAR(255),
ADD COLUMN at_api_key TEXT,
ADD COLUMN at_api_url VARCHAR(255),
ADD COLUMN at_sender_id VARCHAR(100),
ADD COLUMN sms_cooldown_months INT DEFAULT 2,
ADD COLUMN sms_cooldown_days INT DEFAULT 30,
ADD COLUMN insurance_rate_type VARCHAR(20) NULL,
ADD COLUMN insurance_flat_rate DECIMAL(10,2) NULL,
ADD COLUMN insurance_percent_rate DECIMAL(10,2) NULL,
ADD COLUMN cash_rate_type VARCHAR(20) NULL,
ADD COLUMN cash_flat_rate DECIMAL(10,2) NULL,
ADD COLUMN cash_percent_rate DECIMAL(10,2) NULL,
ADD COLUMN etims_status TINYINT(1) NOT NULL DEFAULT 0;

### Step 2

ALTER TABLE invms_stkitem 
MODIFY country_code varchar(10) NULL,
MODIFY itemtype_code varchar(50) NULL,
MODIFY taxtype_code varchar(50) NULL,
MODIFY packaging_code varchar(100) NULL,
MODIFY unit_code varchar(100) NULL,
MODIFY procedureclassification_code varchar(100) NULL;

### Step 3

ALTER TABLE tbl_users ADD COLUMN pin VARCHAR(100) DEFAULT NULL;

#### Step 3.1

#### For Older Users Without PINs, Set a Default PIN (e.g., '0000')

UPDATE tbl_users
SET pin = '$2y$10$Rexqyhk0sFq6bldC7oKdLeV9uPzx7PuK6vZHU/em990o2czYRQkNS'
WHERE pin IS NULL OR pin = '';

ALTER TABLE tbl_users 
ADD COLUMN last_password_sms_sent_at DATETIME NULL AFTER phone,
ADD COLUMN last_pin_sms_sent_at DATETIME NULL AFTER last_password_sms_sent_at;

ALTER TABLE `tbl_users`
ADD COLUMN `isSpecialist` TINYINT(1) DEFAULT 0 AFTER `pin`,
ADD COLUMN `consultantCategoryID` INT DEFAULT NULL AFTER `isSpecialist`,
ADD COLUMN `userSpecialtyID` INT DEFAULT NULL AFTER `consultantCategoryID`,
ADD COLUMN `calendarColor` VARCHAR(20) DEFAULT NULL AFTER `userSpecialtyID`,
ADD COLUMN `initials` VARCHAR(10) DEFAULT NULL AFTER `calendarColor`,
ADD COLUMN `userTypeID` INT DEFAULT NULL AFTER `initials`;

ALTER TABLE hr_company_employees 
ADD COLUMN salarySuspended TINYINT(1) DEFAULT 0,
ADD COLUMN isSpecialist TINYINT(1) DEFAULT 0,
ADD COLUMN employeeType VARCHAR(50) DEFAULT NULL;

### Step 4

CREATE TABLE IF NOT EXISTS password_resets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  userID INT NOT NULL,
  token VARCHAR(64) NOT NULL,
  expires_at DATETIME NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (userID) REFERENCES tbl_users(id) ON DELETE CASCADE
);

### Step 5

CREATE TABLE tbl_sent_sms (
    id INT AUTO_INCREMENT PRIMARY KEY,
    recipient_name VARCHAR(255),
    phone_number VARCHAR(20),
    message TEXT,
    sender_id VARCHAR(50),
    cost VARCHAR(20),
    sent_by INT,
    sent_on DATETIME DEFAULT CURRENT_TIMESTAMP
);

### Step 6(Removed)

### Step 7

CREATE TABLE tbl_sms_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    recipient VARCHAR(20),
    message TEXT,
    status VARCHAR(20),
    receipt_no VARCHAR(50),
    sent_at DATETIME,
    log_response TEXT,
    cost VARCHAR(20) DEFAULT '0.00',
    message_id VARCHAR(100) DEFAULT NULL
);

### Step 8 (removed)

### Step 9

### CREATE TABLE com_outbound_email_tracker

ALTER TABLE com_sms_recipients 
ADD COLUMN recipientsEmailAddress VARCHAR(255) AFTER recipientsPhoneNumber;

CREATE TABLE `com_outbound_email_tracker` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `emailReference` varchar(120) DEFAULT NULL,
  `emailSubject` varchar(255) DEFAULT NULL,
  `emailText` mediumtext,
  `recipientGroupID` int(11) DEFAULT NULL,
  `createdBYUID` int(11) NOT NULL,
  `UIP` varchar(225) DEFAULT NULL,
  `UPC` varchar(225) DEFAULT NULL,
  `isActive` int(2) NOT NULL DEFAULT '1',
  `datePosted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `startDate` date DEFAULT NULL,
  `templateID` int(11) DEFAULT '0',
  `smsStatus` varchar(45) DEFAULT 'PENDING',
  `totalRecipients` int(11) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

### Step 10

CREATE TABLE `com_processed_outbound_email` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `emailRecipientName` varchar(255) DEFAULT NULL,
  `emailRecipientPhone` varchar(20) DEFAULT NULL,
  `emailMainReference` varchar(120) DEFAULT NULL,
  `emailSubject` varchar(500) DEFAULT NULL,
  `emailMainText` mediumtext,
  `emailTrackerID` int(11) DEFAULT NULL,
  `processedBYUID` int(11) NOT NULL,
  `processedbyUIP` varchar(225) DEFAULT NULL,
  `processedbyUPC` varchar(225) DEFAULT NULL,
  `isActive` int(2) NOT NULL DEFAULT '1',
  `dateProcessed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `isSent` int(2) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

### Step 11

CREATE TABLE smart_api_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    visit_id INT,
    session_id VARCHAR(100),
    global_id VARCHAR(100),
    raw_response JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE tbl_insurance_schemes
ADD COLUMN isOnSladeSystem INT(1) NOT NULL DEFAULT 0 AFTER isOnSmartSystem,
ADD COLUMN schemeProceduresCopayAmount DECIMAL(18,2) AFTER schemeProceduresCoverAmount;

### Step 12(Removed)

### Step 13

ALTER TABLE tbl_insurance_schemes
ADD COLUMN schemeProceduresCopayType ENUM('fixed','percent') DEFAULT 'fixed' AFTER schemeProceduresCoverAmount;

CREATE TABLE visit_payers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    visitID INT NOT NULL,
    visitReference CHAR(122) NOT NULL,
    payerID INT NOT NULL,
    splitAmount DECIMAL(12,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

### Step 14(Removed)

### Step 15

CREATE TABLE `smart_api_connections` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `smartItemID` INT(11) DEFAULT NULL,
  `Smart_File` BLOB DEFAULT NULL,
  `Exchange_File` BLOB DEFAULT NULL,
  `Global_ID` VARCHAR(50) DEFAULT NULL,
  `UIP` TEXT DEFAULT NULL,
  `UID` INT(11) DEFAULT NULL,
  `UPC` TEXT DEFAULT NULL,
  `dateCreated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `isActive` INT(11) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

#### Step 15.1

ALTER TABLE smart_api_connections
ADD COLUMN patientIPOPNumber VARCHAR(50) NOT NULL AFTER Global_ID,
ADD COLUMN visitIDReference VARCHAR(50) DEFAULT NULL AFTER patientIPOPNumber,
ADD COLUMN isCardRerun TINYINT(1) DEFAULT 0 AFTER visitIDReference,
ADD COLUMN isClaimSubmitted TINYINT(1) DEFAULT 0 AFTER isCardRerun,
ADD COLUMN dateUpdated TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

ALTER TABLE smart_api_connections 
ADD COLUMN RawXML_File LONGTEXT NULL AFTER Exchange_File;

### Step 16

ALTER TABLE visit_db
ADD COLUMN isCancelled INT(11) NOT NULL DEFAULT 0,
ADD COLUMN isCardRerun TINYINT(1) NOT NULL DEFAULT 0 AFTER smartEntryID,
ADD COLUMN isClaimSubmitted TINYINT(1) NOT NULL DEFAULT 0 AFTER isCardRerun;

### Step 17

CREATE TABLE tbl_patient_benefit_pools (
    id INT AUTO_INCREMENT PRIMARY KEY,
    patient_ipop_number VARCHAR(50) NOT NULL UNIQUE, -- enforce uniqueness
    pool_id INT NOT NULL,          -- benefit.id
    pool_nr VARCHAR(10) NOT NULL,  -- benefit.pool_nr
    pool_desc VARCHAR(255),        -- benefit.pool_desc
    amount DECIMAL(12,2),
    copay_type ENUM('FIXED', 'PERCENTAGE') NOT NULL DEFAULT 'FIXED',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);

### Step 18

CREATE TABLE slade_visits_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    member_id VARCHAR(50) NOT NULL,
    otp_code VARCHAR(20) NOT NULL,
    cover_id VARCHAR(50) DEFAULT NULL,
    response_json JSON NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

### Step 19

CREATE TABLE slade_validaton_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    beneficiary_id VARCHAR(50) NOT NULL,   -- replaces member_id
    otp VARCHAR(20) NOT NULL,              -- replaces otp_code
    beneficiary_contact VARCHAR(50) DEFAULT NULL,
    cover_id VARCHAR(50) DEFAULT NULL,
    payload_json JSON DEFAULT NULL,
    response_json JSON NOT NULL,
    step VARCHAR(50) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

### Step 20

CREATE TABLE `tbl_insurance_partners` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `insurancePartnerName` varchar(225) DEFAULT NULL,
  `insurancePartnerTelephone` varchar(34) DEFAULT NULL,
  `insurancePartnerEmailID` varchar(120) DEFAULT NULL,
  `insurancePartnerTown` varchar(120) DEFAULT NULL,
  `insurancePartnerLocation` varchar(120) DEFAULT NULL,
  `insurancePartnerStreet` varchar(120) DEFAULT NULL,
  `insurancePartnerBuilding` varchar(120) DEFAULT NULL,
  `insurancePartnerAddress` varchar(255) DEFAULT NULL,
  `insurancePartnerOfficeNumber` varchar(120) DEFAULT NULL,
  `insurancePartnerLogo` varchar(255) DEFAULT NULL,
  `UIP` varchar(120) DEFAULT NULL,
  `UPC` varchar(255) DEFAULT NULL,
  `UID` int(11) DEFAULT NULL,
  `isActive` int(4) NOT NULL DEFAULT 1,
  `datePosted` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci AUTO_INCREMENT=1;

### Step 21

ALTER TABLE visit_db 
ADD billingSystem INT NULL,
ADD CONSTRAINT fk_visit_insurance_partner
    FOREIGN KEY (billingSystem) REFERENCES tbl_insurance_partners(id)
    ON DELETE SET NULL 
    ON UPDATE CASCADE;
    
ALTER TABLE invms_stkitem 
ADD COLUMN BuyingPrice DECIMAL(15,2) NOT NULL DEFAULT 0;

### Step 22(Removed)

### Step 23

CREATE TABLE tbl_slade_claims (
    id INT AUTO_INCREMENT PRIMARY KEY,
    patient_id INT NOT NULL,                  -- Patient identifier from POST
    visit_id CHAR(122) NOT NULL UNIQUE,       -- Visit ID or reference from POST
    payer_id INT DEFAULT NULL,                -- Payer/insurer from POST
    claim_payload JSON,                       -- Original JSON payload sent
    claim_response JSON,                      -- Raw API response
    claim_invoice_payload LONGTEXT NULL,      -- Invoice payload JSON
    claim_invoice_response LONGTEXT NULL,     -- Invoice response JSON
    isClaimSubmitted TINYINT(1) DEFAULT 0,
    isClaimAttachmentSubmitted TINYINT(1) DEFAULT 0,
    isClaimInvoiceSubmitted TINYINT(1) DEFAULT 0,
    isInvoiceAttachmentSubmitted TINYINT(1) DEFAULT 0,
    claim_reference VARCHAR(100) DEFAULT NULL, -- Slade API claim UUID
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP 
               ON UPDATE CURRENT_TIMESTAMP
);

### Step 24

CREATE TABLE tbl_smart_claims (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id BIGINT NOT NULL,                   -- Smart's session_id
    claim_status VARCHAR(50) NOT NULL,            -- e.g. Pending, Billed, Cancelled
    claim_status_flag TINYINT(1) NOT NULL,        -- 1 = Billed, 2 = Pending, 3 = Cancelled
    payer_name VARCHAR(255) NOT NULL,             -- e.g. Jubilee Health Insurance Limited
    patient_number VARCHAR(255) NOT NULL,         -- e.g. SAMWEL NYANDORO
    visit_number VARCHAR(100) NOT NULL,           -- e.g. CH026PID28059D04Sep2025
    scheme_name VARCHAR(255) NOT NULL,            -- e.g. AGANO
    amount DECIMAL(12,2) NOT NULL,                -- e.g. 7400
    invoice_number VARCHAR(100) NOT NULL,         -- e.g. 050153/2025
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_smart_claim (session_id, invoice_number)
);

ALTER TABLE tbl_slade_claims
ADD COLUMN invoice_id VARCHAR(100) DEFAULT NULL AFTER claim_invoice_response;

### Step 25(Removed)

### Step 26

CREATE TABLE tbl_partograph_entries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    patient_id INT NOT NULL,
    cervical_dilation DECIMAL(3,1) NOT NULL,
    fetal_head_descent TINYINT NOT NULL,
    contraction_frequency TINYINT NOT NULL,
    contraction_duration INT NOT NULL,
    maternal_pulse TINYINT NOT NULL,
    bp_systolic TINYINT NOT NULL,
    bp_diastolic TINYINT NOT NULL,
    maternal_temp DECIMAL(3,1) NOT NULL,
    urine_output VARCHAR(50) NOT NULL,
    urine_protein VARCHAR(50) NOT NULL,
    fetal_heart_rate TINYINT NOT NULL,
    amniotic_fluid_status VARCHAR(50) NOT NULL,
    fetal_movements VARCHAR(50) NOT NULL,
    drugs_administered TEXT,
    entered_by INT DEFAULT NULL,
    date_recorded TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX (patient_id)
);

ALTER TABLE `tbl_partograph_entries`
  ADD COLUMN `record_time` DATETIME NULL,
  ADD COLUMN `contraction_strength` VARCHAR(20) NULL,
  ADD COLUMN `moulding` VARCHAR(10) NULL,
  ADD COLUMN `respiratory_rate` INT NULL,
  ADD COLUMN `urine_acetone` VARCHAR(20) NULL,
  ADD COLUMN `fhr_decelerations` VARCHAR(20) NULL,
  ADD COLUMN `oxytocin_dose` VARCHAR(100) NULL,
  ADD COLUMN `iv_fluids` VARCHAR(100) NULL;

### Step 27

ALTER TABLE visit_db
ADD COLUMN sladeAuthorizationToken VARCHAR(225) NULL AFTER smartEntryID;

ALTER TABLE theatre_laboratory_preops 
ADD COLUMN theatreNotes TEXT NULL AFTER otherNotes;

### Generate and store latest WHO ICD10 CODES

## Step 1
Download the claML using the below link

[WHO ICD-10 Download Page](https://icdcdn.who.int/icd10/index.html)

Select only the one labelled `ICD-10 2019 version (including COVID-19 updates) ClaML more info`

## Step 2

After downloading the zip file, extract and you'll find a file named `icd102019en.xml`

Copy the file to your root project folder in our case we copy it to `hos5 folder`

## Step 3 Create this table if missing

CREATE TABLE tbl_icd10_diseases (
    id INT AUTO_INCREMENT PRIMARY KEY,
    diagnosisDesc VARCHAR(500) NOT NULL,
    code VARCHAR(20) NOT NULL,
    isActive INT(11) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

## Step 5 Now we create a php engine to get the codes and their names

In your project root folder in our case `hos5` create a php file and name it `icd10.php`

In the php file paste this code there

<?php
libxml_use_internal_errors(true);

// DB Connection
include_once(__DIR__ . '/sys/config/db-cred.inc.php');
global $C;
$conn = new mysqli($C['DB_HOST'], $C['DB_USER'], $C['DB_PASS'], $C['DB_NAME']);
if ($conn->connect_error) {
    echo json_encode(['status' => 'error', 'message' => 'Database connection failed']);
    exit;
}

$xml = simplexml_load_file('icd102019en.xml');
if (!$xml) {
    echo "Failed to load XML\n";
    exit;
}

$namespaces = $xml->getNamespaces(true);
$nsPrefix = '';
if (isset($namespaces[''])) {
    $nsPrefix = 'claml';
    $xml->registerXPathNamespace($nsPrefix, $namespaces['']);
}

// Prepare queries
$checkStmt = $conn->prepare("SELECT id FROM tbl_icd10_diseases WHERE code = ? LIMIT 1");
$insertStmt = $conn->prepare("INSERT INTO tbl_icd10_diseases (diagnosisDesc, code, isActive) VALUES (?, ?, 1)");

// Look for Class elements where kind="category"
$path = $nsPrefix ? "//{$nsPrefix}:Class[@kind='category']" : "//Class[@kind='category']";
$countInserted = 0;
$countSkipped  = 0;

foreach ($xml->xpath($path) as $class) {
    $code = (string)$class['code'];

    // Get preferred label
    $rubrics = $nsPrefix
        ? $class->xpath("{$nsPrefix}:Rubric[@kind='preferred']/" . "{$nsPrefix}:Label")
        : $class->xpath("Rubric[@kind='preferred']/Label");

    $label = !empty($rubrics) ? trim((string)$rubrics[0]) : '';

    if ($code !== '' && $label !== '') {
        // Check if code already exists
        $checkStmt->bind_param("s", $code);
        $checkStmt->execute();
        $checkStmt->store_result();

        if ($checkStmt->num_rows > 0) {
            $countSkipped++;
            continue;
        }

        // Insert new record
        $insertStmt->bind_param("ss", $label, $code);
        if ($insertStmt->execute()) {
            $countInserted++;
        }
    }
}

$checkStmt->close();
$insertStmt->close();
$conn->close();

echo "Inserted {$countInserted} new ICD10 records.  
Skipped {$countSkipped} duplicate records.";
?>

Save the file and head over to your browser and hit this url , in our case `hos5.manager.co.ke/icd10`

**Note**: You must get this message in order to conclude that the operation was successful `Inserted 11243 new ICD10 records. Skipped 0 duplicate records.`

### Theatre sqls
### Step 1

DROP TABLE theatre_checklist_items;

-- Table structure for table `theatre_checklist_items`
CREATE TABLE `theatre_checklist_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subjectName` varchar(255) DEFAULT NULL,
  `checklistItemName` varchar(555) DEFAULT NULL,
  `checkListTypeID` int(11) DEFAULT NULL,
  `isActive` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping initial data for table `theatre_checklist_items`
INSERT INTO `theatre_checklist_items` (`id`, `subjectName`, `checklistItemName`, `checkListTypeID`, `isActive`) VALUES
(1, NULL, 'ID Bands In Place * 2 and Correct', 1, 1),
(2, NULL, 'Consent Form Labelled, Signed & Understood', 1, 1),
(3, NULL, 'Thromboprophylaxis', 1, 1),
(4, NULL, 'Last Food & Drink', 1, 1),
(5, NULL, 'Allergies (Include Food/ Latex/ Medicine etc) Correct Arm Band & Labels', 1, 1),
(6, NULL, 'MRSA Status', 1, 1),
(7, NULL, 'Correct Notes with Drug Chart, Fluid Chart & Labels', 1, 1),
(8, NULL, 'Most Recent Investigations - X-Rays, Scans, Blood etc', 1, 1),
(9, NULL, 'Any Previous Surgery - Metalwork/ Pacemaker', 1, 1),
(10, NULL, 'Any Property to Accompany Patient', 1, 1),
(11, NULL, 'Any Medication to Accompany Patient', 1, 1),
(12, NULL, 'Glasses or Contact Lenses', 1, 1),
(13, NULL, 'Dentures Removed', 1, 1),
(14, NULL, 'Loose Teeth, Caps, Crowns', 1, 1),
(15, NULL, 'Jewellery Removed or Taped', 1, 1),
(16, NULL, 'Comments', 1, 1),
(17, 'Before induction of anaesthesia', 'Patient has confirmed - Identity, Site, Procedure & Consent', 2, 1),
(18, 'Before induction of anaesthesia', 'Site Marked', 2, 1),
(19, 'Before induction of anaesthesia', 'Anaesthesia Safety Check Completed', 2, 1),
(20, 'Before induction of anaesthesia', 'Pulse Oximeter on Patient & Functioning', 2, 1),
(21, 'Before induction of anaesthesia', 'Difficult Airway/ Aspiration Risk', 2, 1),
(22, 'Before Skin Incision', 'Confirm Team Members have introduced themselves by Name and Role', 2, 1),
(23, 'Before Skin Incision', 'Surgical, Anaesthesia Professional and Nurses Confirm - Patient, Site and Procedure', 2, 1),
(24, 'Anticipated Critical Events', 'Surgical Reviews - Critical or Expected Steps, Operative Duration, Anticipated Blood Loss', 2, 1),
(25, 'Anticipated Critical Events', 'Nursing Team Reviews - Has Sterility', 2, 1),
(26, 'Anticipated Critical Events', 'Has Antibiotic Prophylaxis been administered within last 60 Minutes', 2, 1),
(27, 'Anticipated Critical Events', 'Is Essential Imaging Displayed', 2, 1),
(28, 'Sign Out - Before Leaving Operating Room', 'Nurse verbally confirms with the team', 3, 1),
(29, 'Sign Out - Before Leaving Operating Room', 'Name of the Procedure Recorded', 3, 1),
(30, 'Sign Out - Before Leaving Operating Room', 'Instruments, Sponge & Needles Counts are correct', 3, 1),
(31, 'Sign Out - Before Leaving Operating Room', 'How the Specimen is Labelled (Including Patient Name)', 3, 1),
(32, 'Sign Out - Before Leaving Operating Room', 'Equipment malfunction to be addressed', 3, 1),
(33, 'Sign Out - Before Leaving Operating Room', 'Surgeon, Anaesthesia Professional & Nurses review the Key concerns for recovery and management of patient', 3, 1),
(34, 'Anaesthesia Professional Log', 'ECG', 4, 1),
(35, 'Anaesthesia Professional Log', 'Pulse Oximeter', 4, 1),
(36, 'Anaesthesia Professional Log', 'Blood Pressure', 4, 1),
(37, 'Anaesthesia Professional Log', 'Oxygen Saturation', 4, 1);

### Step 2(Removed)

### Step 3

-- Table structure for table `theatre_pre_op_checklist_log`
CREATE TABLE `theatre_pre_op_checklist_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `patientUID` int(11) DEFAULT NULL,
  `visitIDRef` varchar(120) DEFAULT NULL,
  `A1` varchar(550) DEFAULT NULL,
  `A2` varchar(550) DEFAULT NULL,
  `A3` varchar(550) DEFAULT NULL,
  `A4` varchar(550) DEFAULT NULL,
  `A5` varchar(550) DEFAULT NULL,
  `A6` varchar(550) DEFAULT NULL,
  `A7` varchar(550) DEFAULT NULL,
  `A8` varchar(550) DEFAULT NULL,
  `A9` varchar(550) DEFAULT NULL,
  `A10` varchar(550) DEFAULT NULL,
  `A11` varchar(550) DEFAULT NULL,
  `A12` varchar(550) DEFAULT NULL,
  `A13` varchar(550) DEFAULT NULL,
  `A14` varchar(550) DEFAULT NULL,
  `A15` varchar(550) DEFAULT NULL,
  `A16` varchar(120) DEFAULT NULL,
  `lastFoodDate` date DEFAULT NULL,
  `lastFoodTime` varchar(20) DEFAULT NULL,
  `lastWaterDate` date DEFAULT NULL,
  `lastWaterTime` varchar(20) DEFAULT NULL,
  `isActive` tinyint(1) NOT NULL DEFAULT 1,
  `UID` int(11) DEFAULT NULL,
  `UIP` varchar(120) DEFAULT NULL,
  `UPC` varchar(220) DEFAULT NULL,
  `dateAdded` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping initial data for table `theatre_pre_op_checklist_log`
INSERT INTO `theatre_pre_op_checklist_log` 
(`id`, `patientUID`, `visitIDRef`, `A1`, `A2`, `A3`, `A4`, `A5`, `A6`, `A7`, `A8`, `A9`, `A10`, `A11`, `A12`, `A13`, `A14`, `A15`, `A16`, 
 `lastFoodDate`, `lastFoodTime`, `lastWaterDate`, `lastWaterTime`, 
 `isActive`, `UID`, `UIP`, `UPC`, `dateAdded`) 
VALUES
(1, 59, 'VMH64-06032025', 
 'NO', 'NO', 'HIGH', '', 'NO', 'LOW', 'NO', 'YES', 'NO', 'YES', 'YES', 'NO', 'NO', 'YES', 'YES', 
 'Dolore temporibus vo', 
 '0000-00-00', '00:00', 
 '0000-00-00', '00:00', 
 1, 1, '::1', 
 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/133.0.0.0 Safari/537.36', 
 '2025-03-06 09:51:27');

### Step 4

-- Table structure for table `theatre_post_op_checklist_log`
CREATE TABLE `theatre_post_op_checklist_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `patientUID` int(11) DEFAULT NULL,
  `visitIDRef` varchar(120) DEFAULT NULL,
  `A1` text DEFAULT NULL,
  `A2` text DEFAULT NULL,
  `A3` text DEFAULT NULL,
  `A4` text DEFAULT NULL,
  `A5` text DEFAULT NULL,
  `isActive` tinyint(1) NOT NULL DEFAULT 1,
  `UID` int(11) DEFAULT NULL,
  `UIP` varchar(120) DEFAULT NULL,
  `UPC` varchar(220) DEFAULT NULL,
  `dateAdded` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

### Step 5

-- Table structure for table `theatre_intra_op_checklist_log`
CREATE TABLE `theatre_intra_op_checklist_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `patientUID` int(11) DEFAULT NULL,
  `visitIDRef` varchar(120) DEFAULT NULL,
  `A1` varchar(550) DEFAULT NULL,
  `A3` varchar(550) DEFAULT NULL,
  `A4` varchar(550) DEFAULT NULL,
  `A5` varchar(550) DEFAULT NULL,
  `A6` varchar(550) DEFAULT NULL,
  `A7` varchar(550) DEFAULT NULL,
  `A8` varchar(550) DEFAULT NULL,
  `A9` varchar(550) DEFAULT NULL,
  `A10` varchar(550) DEFAULT NULL,
  `A11` varchar(550) DEFAULT NULL,
  `A12` varchar(550) DEFAULT NULL,
  `A13` varchar(550) DEFAULT NULL,
  `isActive` tinyint(1) NOT NULL DEFAULT 1,
  `UID` int(11) DEFAULT NULL,
  `UIP` varchar(120) DEFAULT NULL,
  `UPC` varchar(220) DEFAULT NULL,
  `dateAdded` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

### Step 6

CREATE TABLE `theatre_anaesthetist_vitals_log` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `patientUID` INT(11) DEFAULT NULL,
  `visitIDRef` VARCHAR(120) DEFAULT NULL,
  `ECG` TEXT DEFAULT NULL,
  `bloodPressure` TEXT DEFAULT NULL,
  `pulseOximeter` TEXT DEFAULT NULL,
  `oxygenSaturation` TEXT DEFAULT NULL,
  `loggedDate` DATE DEFAULT NULL,
  `loggedTime` VARCHAR(220) DEFAULT NULL,
  `isActive` TINYINT(1) NOT NULL DEFAULT 1,
  `UID` INT(11) DEFAULT NULL,
  `UIP` VARCHAR(120) DEFAULT NULL,
  `UPC` VARCHAR(220) DEFAULT NULL,
  `dateAdded` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB 
  DEFAULT CHARSET=latin1 
  COLLATE=latin1_swedish_ci;

### Step 7

ALTER TABLE `eval_nurses_queue` 
  ADD COLUMN `opticalVisionPHRight` VARCHAR(10) NULL AFTER `opticalAutoRefrLeft`,
  ADD COLUMN `opticalVisionPHLeft`  VARCHAR(10) NULL AFTER `opticalVisionPHRight`;
  
  ALTER TABLE `eval_nurses_queue` 
  ADD COLUMN `opticalFinalPrescription` TEXT NULL AFTER `opticalGeneralComments`;
  
  ALTER TABLE `eval_nurses_queue`
  ADD COLUMN `opticalEyeLidsRight` TEXT NULL AFTER `opticalFinalPrescription`,
  ADD COLUMN `opticalEyeLidsLeft` TEXT NULL AFTER `opticalEyeLidsRight`,
  ADD COLUMN `opticalConjunctivaRight` TEXT NULL AFTER `opticalEyeLidsLeft`,
  ADD COLUMN `opticalConjunctivaLeft` TEXT NULL AFTER `opticalConjunctivaRight`,
  ADD COLUMN `opticalCorneaRight` TEXT NULL AFTER `opticalConjunctivaLeft`,
  ADD COLUMN `opticalCorneaLeft` TEXT NULL AFTER `opticalCorneaRight`,
  ADD COLUMN `opticalAChamberRight` TEXT NULL AFTER `opticalCorneaLeft`,
  ADD COLUMN `opticalAChamberLeft` TEXT NULL AFTER `opticalAChamberRight`,
  ADD COLUMN `opticalPupilRight` TEXT NULL AFTER `opticalAChamberLeft`,
  ADD COLUMN `opticalPupilLeft` TEXT NULL AFTER `opticalPupilRight`,
  ADD COLUMN `opticalLensRight` TEXT NULL AFTER `opticalPupilLeft`,
  ADD COLUMN `opticalLensLeft` TEXT NULL AFTER `opticalLensRight`,
  ADD COLUMN `opticalFundusRight` TEXT NULL AFTER `opticalLensLeft`,
  ADD COLUMN `opticalFundusLeft` TEXT NULL AFTER `opticalFundusRight`;
  
  ALTER TABLE `eval_nurses_queue`
  DROP COLUMN `opticalEyeLidsRight`,
  DROP COLUMN `opticalEyeLidsLeft`,
  DROP COLUMN `opticalConjunctivaRight`,
  DROP COLUMN `opticalConjunctivaLeft`,
  DROP COLUMN `opticalCorneaRight`,
  DROP COLUMN `opticalCorneaLeft`,
  DROP COLUMN `opticalAChamberRight`,
  DROP COLUMN `opticalAChamberLeft`,
  DROP COLUMN `opticalPupilRight`,
  DROP COLUMN `opticalPupilLeft`,
  DROP COLUMN `opticalLensRight`,
  DROP COLUMN `opticalLensLeft`,
  DROP COLUMN `opticalFundusRight`,
  DROP COLUMN `opticalFundusLeft`;

### Step 8

ALTER TABLE `eval_patient_history`
  ADD COLUMN `opticalEyeLidsRight` TEXT NULL AFTER `docPatientAdvice`,
  ADD COLUMN `opticalEyeLidsLeft` TEXT NULL AFTER `opticalEyeLidsRight`,
  ADD COLUMN `opticalConjunctivaRight` TEXT NULL AFTER `opticalEyeLidsLeft`,
  ADD COLUMN `opticalConjunctivaLeft` TEXT NULL AFTER `opticalConjunctivaRight`,
  ADD COLUMN `opticalCorneaRight` TEXT NULL AFTER `opticalConjunctivaLeft`,
  ADD COLUMN `opticalCorneaLeft` TEXT NULL AFTER `opticalCorneaRight`,
  ADD COLUMN `opticalAChamberRight` TEXT NULL AFTER `opticalCorneaLeft`,
  ADD COLUMN `opticalAChamberLeft` TEXT NULL AFTER `opticalAChamberRight`,
  ADD COLUMN `opticalPupilRight` TEXT NULL AFTER `opticalAChamberLeft`,
  ADD COLUMN `opticalPupilLeft` TEXT NULL AFTER `opticalPupilRight`,
  ADD COLUMN `opticalLensRight` TEXT NULL AFTER `opticalPupilLeft`,
  ADD COLUMN `opticalLensLeft` TEXT NULL AFTER `opticalLensRight`,
  ADD COLUMN `opticalFundusRight` TEXT NULL AFTER `opticalLensLeft`,
  ADD COLUMN `opticalFundusLeft` TEXT NULL AFTER `opticalFundusRight`;

### Step 9

ALTER TABLE `tbl_sys_config`
ADD COLUMN `stampStatus` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0 = Disabled, 1 = Enabled' ;

CREATE TABLE tbl_monthly_tickets (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    ticketMonth CHAR(7) NOT NULL, -- e.g. '2025-09'
    ticketNumber INT NOT NULL,
    UNIQUE KEY unique_ticket (ticketMonth, ticketNumber)
);

### Step 10(Removed)
### Step 11

DROP TABLE IF EXISTS `prmgt_payroll_nssf_deductions`;

-- Create table
CREATE TABLE `prmgt_payroll_nssf_deductions` (
  `id` INT(10) 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,
  `deduction_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `addedby` TINYINT(3) UNSIGNED NOT NULL,
  `employeeDepartmentName` VARCHAR(120) DEFAULT '',
  `basicSalary` DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  `grossSalary` DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  `totalAllowances` DECIMAL(18,2) NOT NULL DEFAULT 0.00
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Triggers
DELIMITER $$

CREATE TRIGGER `prmgt_payroll_nssf_deductions_AFTER_INSERT`
AFTER INSERT ON `prmgt_payroll_nssf_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`, `cat_show_on_slip`, `paySlipItemTitle`)
    VALUES
        (NEW.department_id, NEW.employee_id, NEW.month_id, NEW.year_id, 7, 'NSSF', NEW.deduction_amount, NEW.id, NEW.addedby, 'YES', 'NSSF DEDUCTION');
END
$$

CREATE TRIGGER `prmgt_payroll_nssf_deductions_AFTER_UPDATE`
AFTER UPDATE ON `prmgt_payroll_nssf_deductions`
FOR EACH ROW
BEGIN
    DECLARE nssfExist INT DEFAULT 0;

    SELECT 1 INTO nssfExist
    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 = 7
    LIMIT 1;

    IF nssfExist != 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, 7, 'NSSF', NEW.deduction_amount, NEW.id, NEW.addedby);
    ELSE
        UPDATE `prmgt_employee_salgenerated`
        SET `cat_amount` = NEW.deduction_amount
        WHERE `cat_id` = 7
          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
ALTER TABLE `prmgt_payroll_nssf_deductions`
  ADD PRIMARY KEY (`id`);

-- Auto-increment
ALTER TABLE `prmgt_payroll_nssf_deductions`
  MODIFY `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;

COMMIT;

### Step 12

DROP TABLE IF EXISTS `prmgt_payroll_paye_deductions`;

-- Create table
CREATE TABLE `prmgt_payroll_paye_deductions` (
  `id` BIGINT(20) UNSIGNED NOT NULL,
  `employee_id` INT(11) DEFAULT NULL,
  `employee_name` VARCHAR(255) DEFAULT '',
  `payroll_number` VARCHAR(120) DEFAULT '',
  `department_id` INT(11) DEFAULT NULL,
  `month_id` INT(11) DEFAULT NULL,
  `year_id` INT(11) DEFAULT NULL,
  `basicPay` DECIMAL(14,2) DEFAULT NULL,
  `deduction_amount` DECIMAL(18,2) DEFAULT 0.00,
  `payeAfterRelief` DECIMAL(18,2) DEFAULT 0.00,
  `addedby` INT(10) UNSIGNED NOT NULL,
  `employeeDepartmentName` VARCHAR(80) DEFAULT '',
  `absenteeism` DECIMAL(18,2) DEFAULT 0.00,
  `NHIFAmount` DECIMAL(18,2) DEFAULT 0.00,
  `NSSFAmount` DECIMAL(18,2) DEFAULT 0.00,
  `taxableIncome` DECIMAL(18,2) DEFAULT 0.00,
  `taxableAllowances` DECIMAL(18,2) DEFAULT 0.00,
  `payeRefund` DECIMAL(18,2) NOT NULL DEFAULT 0.00
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Triggers
DELIMITER $$

CREATE TRIGGER `prmgt_payroll_paye_deductions_AFTER_INSERT`
AFTER INSERT ON `prmgt_payroll_paye_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`, `cat_show_on_slip`, `paySlipItemTitle`)
    VALUES
        (NEW.department_id, NEW.employee_id, NEW.month_id, NEW.year_id, 9, 'PAYE', NEW.deduction_amount, NEW.id, NEW.addedby, 'YES', 'PAYE DEDUCTIONS');
END
$$

CREATE TRIGGER `prmgt_payroll_paye_deductions_AFTER_UPDATE`
AFTER UPDATE ON `prmgt_payroll_paye_deductions`
FOR EACH ROW
BEGIN
    DECLARE payeExist INT DEFAULT 0;

    SELECT 1 INTO payeExist
    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 = 9
    LIMIT 1;

    IF payeExist != 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, 9, 'PAYE', NEW.deduction_amount, NEW.id, NEW.addedby);
    ELSE
        UPDATE `prmgt_employee_salgenerated`
        SET `cat_amount` = NEW.deduction_amount
        WHERE `cat_id` = 9
          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
ALTER TABLE `prmgt_payroll_paye_deductions`
  ADD PRIMARY KEY (`id`);

-- Auto-increment
ALTER TABLE `prmgt_payroll_paye_deductions`
  MODIFY `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;

COMMIT;

### Step 13

DROP TABLE IF EXISTS `prmgt_payroll_housinglevy_deductions`;

-- Create table
CREATE TABLE `prmgt_payroll_housinglevy_deductions` (
  `id` BIGINT(20) UNSIGNED NOT NULL,
  `employee_id` INT(11) DEFAULT NULL,
  `employee_name` VARCHAR(255) DEFAULT '',
  `payroll_number` VARCHAR(120) DEFAULT '',
  `department_id` INT(11) DEFAULT NULL,
  `month_id` INT(11) DEFAULT NULL,
  `year_id` INT(11) DEFAULT NULL,
  `basicPay` DECIMAL(14,2) DEFAULT NULL,
  `deduction_amount` DECIMAL(18,2) DEFAULT 0.00,
  `addedby` INT(10) UNSIGNED NOT NULL,
  `employeeDepartmentName` VARCHAR(80) DEFAULT '',
  `absenteeism` DECIMAL(18,2) DEFAULT 0.00,
  `NHIFAmount` DECIMAL(18,2) DEFAULT 0.00,
  `NSSFAmount` DECIMAL(18,2) DEFAULT 0.00,
  `taxableIncome` DECIMAL(18,2) DEFAULT 0.00,
  `taxableAllowances` DECIMAL(18,2) DEFAULT 0.00
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Triggers
DELIMITER $$

CREATE TRIGGER `prmgt_payroll_housinglevy_deductions_AFTER_INSERT`
AFTER INSERT ON `prmgt_payroll_housinglevy_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`, `cat_show_on_slip`, `paySlipItemTitle`)
    VALUES
        (NEW.department_id, NEW.employee_id, NEW.month_id, NEW.year_id, 14, 'HOUSING LEVY', NEW.deduction_amount, NEW.id, NEW.addedby, 'YES', 'HOUSING LEVY DEDUCTION');
END
$$

CREATE TRIGGER `prmgt_payroll_housinglevy_deductions_AFTER_UPDATE`
AFTER UPDATE ON `prmgt_payroll_housinglevy_deductions`
FOR EACH ROW
BEGIN
    DECLARE hlExist INT DEFAULT 0;

    SELECT 1 INTO hlExist
    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 = 14
    LIMIT 1;

    IF hlExist != 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, 14, 'HOUSING LEVY', NEW.deduction_amount, NEW.id, NEW.addedby);
    ELSE
        UPDATE `prmgt_employee_salgenerated`
        SET `cat_amount` = NEW.deduction_amount
        WHERE `cat_id` = 14
          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
ALTER TABLE `prmgt_payroll_housinglevy_deductions`
  ADD PRIMARY KEY (`id`);

-- Auto-increment
ALTER TABLE `prmgt_payroll_housinglevy_deductions`
  MODIFY `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;

COMMIT;

### Step 14

DROP TABLE IF EXISTS `prmgt_payroll_nhif_deductions`;

-- --------------------------------------------------------
-- Table structure for table `prmgt_payroll_nhif_deductions`
-- --------------------------------------------------------
CREATE TABLE `prmgt_payroll_nhif_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_nhif_deductions`
-- --------------------------------------------------------
DELIMITER $$

CREATE TRIGGER `prmgt_payroll_nhif_deductions_AFTER_INSERT`
AFTER INSERT ON `prmgt_payroll_nhif_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,
        6,
        'SHA',
        NEW.deduction_amount,
        NEW.id,
        NEW.addedby,
        'SHA DEDUCTION'
    );
END$$

CREATE TRIGGER `prmgt_payroll_nhif_deductions_AFTER_UPDATE`
AFTER UPDATE ON `prmgt_payroll_nhif_deductions`
FOR EACH ROW
BEGIN
    DECLARE nhifExist INT DEFAULT 0;

    SELECT 1 INTO nhifExist
    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 = 6
    LIMIT 1;

    IF nhifExist != 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,
            6,
            'SHA',
            NEW.deduction_amount,
            NEW.id,
            NEW.addedby
        );
    ELSE
        UPDATE `prmgt_employee_salgenerated`
        SET `cat_amount` = NEW.deduction_amount
        WHERE `cat_id` = 6
          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_nhif_deductions`
-- --------------------------------------------------------
ALTER TABLE `prmgt_payroll_nhif_deductions`
  ADD PRIMARY KEY (`id`);

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

COMMIT;

### Step 15

DROP TABLE IF EXISTS `prmgt_payroll_advances_deductions`;

--
-- Table structure for table `prmgt_payroll_advances_deductions`
--
CREATE TABLE `prmgt_payroll_advances_deductions` (
  `id` int(11) NOT NULL,
  `deduction_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `department_id` tinyint(3) UNSIGNED NOT NULL,
  `deductionName` varchar(45) DEFAULT NULL,
  `deductiondate` datetime NOT NULL,
  `month_id` int(11) NOT NULL,
  `year_id` int(11) NOT NULL,
  `deductionamount` decimal(10,2) NOT NULL,
  `istaxable` varchar(45) NOT NULL,
  `ispayable` varchar(45) NOT NULL,
  `showonpayslip` varchar(45) NOT NULL,
  `isActive` int(11) NOT NULL DEFAULT 0,
  `isDeleted` int(11) NOT NULL DEFAULT 0,
  `addedby` varchar(45) NOT NULL,
  `dateAdded` timestamp NOT NULL DEFAULT current_timestamp(),
  `updatedBYUID` varchar(45) DEFAULT NULL,
  `dateUpdated` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Triggers for table `prmgt_payroll_advances_deductions`
--
DELIMITER $$
CREATE TRIGGER `prmgt_payroll_advances_deductions_AFTER_UPDATE` 
AFTER UPDATE ON `prmgt_payroll_advances_deductions` 
FOR EACH ROW 
BEGIN
    DECLARE advanceExist INT DEFAULT 0;
    DECLARE advanceName VARCHAR(40);
    DECLARE perVal INT;
    DECLARE perAmt FLOAT;

    IF NEW.isActive = 1 AND NEW.isDeleted = 0 THEN
        -- Check if already exists
        SELECT 1 INTO advanceExist 
        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 = 4
        LIMIT 1;

        -- Get reference name
        SELECT advanceTxnReference INTO advanceName 
        FROM prmgt_staff_advance_disbursments 
        WHERE id = NEW.id 
        LIMIT 1;

        -- Get advance percent setup
        SELECT perValue INTO perVal 
        FROM prmgt_advancepercent_setup 
        WHERE perType = 'ADVANCE' 
        LIMIT 1;

        -- Calculate percentage adjustment
        SET perAmt = NEW.deductionamount + ((perVal/100) * NEW.deductionamount);

        IF advanceExist != 1 THEN
            INSERT INTO `prmgt_employee_salgenerated` 
                (`department_id`, `employee_id`, `month_id`, `year_id`, 
                 `cat_id`, `cat_name`, `cat_amount`, `cat_show_on_slip`, 
                 `item_id`, `addedby`, `paySlipItemTitle`)
            VALUES 
                (NEW.department_id, NEW.employee_id, NEW.month_id, NEW.year_id, 
                 4, CONCAT('Advance REF: ', advanceName), NEW.deductionamount, NEW.showonpayslip, 
                 NEW.id, NEW.addedby, 'ADVANCE DEDUCTION');
        ELSE
            UPDATE `prmgt_employee_salgenerated` 
            SET `cat_amount` = NEW.deductionamount, 
                `cat_show_on_slip` = NEW.showonpayslip
            WHERE `cat_id` = 4 
              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;
    ELSE
        -- If inactive or deleted, remove from salgenerated
        DELETE FROM `prmgt_employee_salgenerated` 
        WHERE `cat_id` = 4 
          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_advances_deductions`
--
ALTER TABLE `prmgt_payroll_advances_deductions`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for table `prmgt_payroll_advances_deductions`
--
ALTER TABLE `prmgt_payroll_advances_deductions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

COMMIT;

### Step 16

DROP TABLE IF EXISTS `prmgt_payroll_paye_refund`;

--
-- Table structure for table `prmgt_payroll_paye_refund`
--
CREATE TABLE `prmgt_payroll_paye_refund` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `employee_id` int(11) DEFAULT NULL,
  `employee_name` varchar(255) DEFAULT '',
  `payroll_number` varchar(120) DEFAULT '',
  `department_id` int(11) DEFAULT NULL,
  `month_id` int(11) DEFAULT NULL,
  `year_id` int(11) DEFAULT NULL,
  `payeAmount` decimal(18,2) DEFAULT 0.00,
  `payeRefund` decimal(18,2) DEFAULT 0.00,
  `isActive` int(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Triggers for table `prmgt_payroll_paye_refund`
--
DELIMITER $$
CREATE TRIGGER `prmgt_payroll_paye_refund_AFTER_INSERT` 
AFTER INSERT ON `prmgt_payroll_paye_refund` 
FOR EACH ROW 
BEGIN
    IF NEW.isActive = 1 THEN
        INSERT INTO `prmgt_employee_salgenerated` 
            (`department_id`, `employee_id`, `month_id`, `year_id`, 
             `cat_id`, `cat_name`, `cat_amount`, `item_id`,  
             `cat_show_on_slip`, `paySlipItemTitle`)
        VALUES 
            (NEW.department_id, NEW.employee_id, NEW.month_id, NEW.year_id, 
             13, 'PAYE REFUND', NEW.payeRefund, NEW.id, 
             'YES', 'PAYE REFUND');
    END IF;
END
$$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER `prmgt_payroll_paye_refund_AFTER_UPDATE` 
AFTER UPDATE ON `prmgt_payroll_paye_refund` 
FOR EACH ROW 
BEGIN
    DECLARE payeExist INT DEFAULT 0;

    -- Check if record already exists in salgenerated
    SELECT 1 INTO payeExist 
    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 = 13
    LIMIT 1;

    IF NEW.isActive = 1 THEN
        IF payeExist != 1 THEN 
            INSERT INTO `prmgt_employee_salgenerated` 
                (`department_id`, `employee_id`, `month_id`, `year_id`, 
                 `cat_id`, `cat_name`, `cat_amount`, `item_id`, 
                 `cat_show_on_slip`, `paySlipItemTitle`)
            VALUES 
                (NEW.department_id, NEW.employee_id, NEW.month_id, NEW.year_id, 
                 13, 'PAYE REFUND', NEW.payeRefund, NEW.id, 
                 'YES', 'PAYE REFUND');
        ELSE
            UPDATE `prmgt_employee_salgenerated` 
            SET `cat_amount` = NEW.payeRefund
            WHERE `cat_id` = 13 
              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;
    ELSE
        -- If refund is deactivated, remove it from salgenerated
        DELETE FROM `prmgt_employee_salgenerated`
        WHERE `cat_id` = 13 
          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_paye_refund`
--
ALTER TABLE `prmgt_payroll_paye_refund`
  ADD PRIMARY KEY (`id`);

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

COMMIT;

### Step 17

DROP TABLE IF EXISTS `prmgt_payroll_shares_deductions`;

--
-- Table structure for table `prmgt_payroll_shares_deductions`
--
CREATE TABLE `prmgt_payroll_shares_deductions` (
  `id` int(11) NOT NULL,
  `shareID` int(11) NOT NULL,
  `shareTypeID` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `departmentID` int(11) NOT NULL,
  `shareDeductionDate` date NOT NULL,
  `month_id` int(11) NOT NULL,
  `year_id` int(11) NOT NULL,
  `deductionamount` decimal(10,2) NOT NULL,
  `showonpayslip` varchar(45) NOT NULL,
  `isActive` int(11) NOT NULL DEFAULT 0,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `addedby` varchar(45) NOT NULL,
  `dateadded` timestamp NOT NULL DEFAULT current_timestamp(),
  `updatedby` varchar(45) DEFAULT NULL,
  `dateupdated` timestamp NULL DEFAULT NULL,
  `shareTypeName` varchar(120) NOT NULL DEFAULT '',
  `saccoID` int(11) NOT NULL,
  `employeeNames` varchar(180) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Trigger for table `prmgt_payroll_shares_deductions`
--
DELIMITER $$
CREATE TRIGGER `prmgt_payroll_shares_deductions_AFTER_UPDATE` 
AFTER UPDATE ON `prmgt_payroll_shares_deductions` 
FOR EACH ROW 
BEGIN
    DECLARE shareExist INT DEFAULT 0;
    DECLARE payShareName VARCHAR(40);
    DECLARE payShareCurrentAmount DECIMAL(13,2) DEFAULT 0;

    -- Only process active, non-deleted entries
    IF NEW.isActive = 1 AND NEW.isDeleted = 0 THEN
        SELECT shareTypeCode INTO payShareName 
        FROM prmgt_sacco_share_types 
        WHERE id = NEW.shareTypeID;

        SELECT 1 INTO shareExist 
        FROM prmgt_employee_salgenerated 
        WHERE month_id = NEW.month_id 
          AND employee_id = NEW.employee_id 
          AND department_id = NEW.departmentID 
          AND year_id = NEW.year_id 
          AND item_id = NEW.shareID 
          AND cat_id = 12
        LIMIT 1;

        SELECT cat_amount INTO payShareCurrentAmount 
        FROM prmgt_employee_salgenerated 
        WHERE month_id = NEW.month_id 
          AND employee_id = NEW.employee_id 
          AND department_id = NEW.departmentID 
          AND year_id = NEW.year_id  
          AND item_id = NEW.shareID 
          AND cat_id = 12
        LIMIT 1;

        IF shareExist != 1 THEN
            INSERT INTO `prmgt_employee_salgenerated` 
                (`subCategoryID`, `employee_id`, `department_id`, `month_id`, `year_id`, 
                 `cat_id`, `cat_name`, `cat_amount`, `addedby`, `item_id`, 
                 `cat_show_on_slip`, `paySlipItemTitle`)
            VALUES 
                (NEW.saccoID, NEW.employee_id, NEW.departmentID, NEW.month_id, NEW.year_id, 
                 12, NEW.shareTypeName, NEW.deductionamount, NEW.addedby, NEW.shareID, 
                 'YES', 'SHARE DEDUCTIONS');

            UPDATE sacco_member_shares 
            SET totalPaidAmount = totalPaidAmount + NEW.deductionamount  
            WHERE id = NEW.shareID AND employeeID = NEW.employee_id;

        ELSE
            UPDATE `prmgt_employee_salgenerated` 
            SET `subCategoryID` = NEW.saccoID, 
                `cat_amount` = NEW.deductionamount, 
                `cat_name` = NEW.shareTypeName 
            WHERE cat_id = 12 
              AND item_id = NEW.shareID 
              AND employee_id = NEW.employee_id 
              AND department_id = NEW.departmentID 
              AND month_id = NEW.month_id 
              AND year_id = NEW.year_id;

            UPDATE sacco_member_shares 
            SET totalPaidAmount = totalPaidAmount - payShareCurrentAmount 
            WHERE id = NEW.shareID AND employeeID = NEW.employee_id;

            UPDATE sacco_member_shares 
            SET totalPaidAmount = totalPaidAmount + NEW.deductionamount 
            WHERE id = NEW.shareID AND employeeID = NEW.employee_id;
        END IF;

    ELSE 
        -- Remove from salgenerated if not active or deleted
        DELETE FROM `prmgt_employee_salgenerated` 
        WHERE cat_id = 12 
          AND item_id = NEW.shareID 
          AND employee_id = NEW.employee_id 
          AND month_id = NEW.month_id 
          AND year_id = NEW.year_id;

        UPDATE sacco_member_shares 
        SET totalPaidAmount = totalPaidAmount - payShareCurrentAmount 
        WHERE id = NEW.shareID AND employeeID = NEW.employee_id;
    END IF;
END
$$
DELIMITER ;

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

--
-- AUTO_INCREMENT for table `prmgt_payroll_shares_deductions`
--
ALTER TABLE `prmgt_payroll_shares_deductions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

COMMIT;

### Step 18

DROP TABLE IF EXISTS `prmgt_employee_allowances`;

--
-- Table structure for table `prmgt_employee_allowances`
--
CREATE TABLE `prmgt_employee_allowances` (
  `id` bigint(20) NOT NULL,
  `department_id` int(11) DEFAULT NULL,
  `allowance_id` int(11) DEFAULT NULL,
  `employee_id` int(11) DEFAULT NULL,
  `empallowdate` date DEFAULT NULL,
  `month_id` int(11) DEFAULT NULL,
  `year_id` int(11) DEFAULT NULL,
  `allowanceamount` decimal(10,2) DEFAULT NULL,
  `istaxable` varchar(45) DEFAULT NULL,
  `ispayable` varchar(45) DEFAULT NULL,
  `showonpayslip` varchar(45) DEFAULT NULL,
  `isActive` int(11) DEFAULT 0,
  `isDeleted` int(11) DEFAULT 0,
  `addedby` varchar(45) DEFAULT NULL,
  `dateAdded` timestamp NULL DEFAULT current_timestamp(),
  `updatedBYUID` int(11) DEFAULT NULL,
  `dateUpdated` timestamp DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Trigger for table `prmgt_employee_allowances`
--
DELIMITER $$
CREATE TRIGGER `prmgt_employee_allowances_AFTER_UPDATE` 
AFTER UPDATE ON `prmgt_employee_allowances` 
FOR EACH ROW 
BEGIN
    DECLARE allowanceExist INT DEFAULT 0;
    DECLARE allowanceName VARCHAR(40);
    DECLARE grossExists INT DEFAULT 0;
    DECLARE grossAmount DECIMAL(18,2) DEFAULT 0.00;

    IF NEW.isActive = 1 AND NEW.isDeleted = 0 THEN 

        -- Get allowance description
        SELECT callw_description INTO allowanceName 
        FROM prmgt_company_allowances_benefits 
        WHERE id = NEW.allowance_id 
        LIMIT 1;

        -- Check if allowance already exists in salgenerated
        SELECT 1 INTO allowanceExist 
        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 = 2 
          AND subCategoryID = NEW.allowance_id
        LIMIT 1;

        -- Calculate total taxable allowances
        SELECT SUM(cat_amount) INTO grossAmount 
        FROM prmgt_employee_salgenerated 
        WHERE month_id = NEW.month_id 
          AND employee_id = NEW.employee_id 
          AND year_id = NEW.year_id 
          AND cat_id = 2;

        -- Check if gross salary record exists
        SELECT 1 INTO grossExists 
        FROM prmgt_payroll_gross_salary 
        WHERE monthID = NEW.month_id 
          AND employeeID = NEW.employee_id 
          AND yearID = NEW.year_id
        LIMIT 1;

        IF allowanceExist != 1 THEN
            INSERT INTO `prmgt_employee_salgenerated` 
                (subCategoryID, department_id, employee_id, month_id, year_id, 
                 cat_id, cat_name, cat_amount, cat_taxable, cat_show_on_slip, 
                 addedby, item_id, paySlipItemTitle)
            VALUES 
                (NEW.allowance_id, NEW.department_id, NEW.employee_id, NEW.month_id, NEW.year_id, 
                 2, allowanceName, NEW.allowanceamount, NEW.istaxable, NEW.showonpayslip, 
                 NEW.addedby, NEW.id, 'SALARY ALLOWANCES');

            IF grossExists != 1 THEN
                INSERT INTO `prmgt_payroll_gross_salary` 
                    (employeeID, departmentID, monthID, yearID, totalTaxableAllowances, addedByUID)
                VALUES 
                    (NEW.employee_id, NEW.department_id, NEW.month_id, NEW.year_id, grossAmount, NEW.addedby);
            ELSE
                UPDATE `prmgt_payroll_gross_salary` 
                SET totalTaxableAllowances = grossAmount 
                WHERE monthID = NEW.month_id AND employeeID = NEW.employee_id AND yearID = NEW.year_id;
            END IF;

        ELSE
            UPDATE `prmgt_employee_salgenerated` 
            SET cat_amount = NEW.allowanceamount, 
                cat_taxable = NEW.istaxable, 
                cat_show_on_slip = NEW.showonpayslip
            WHERE cat_id = 2 AND item_id = NEW.id AND employee_id = NEW.employee_id 
              AND month_id = NEW.month_id AND year_id = NEW.year_id 
              AND subCategoryID = NEW.allowance_id;

            UPDATE `prmgt_payroll_gross_salary` 
            SET totalTaxableAllowances = grossAmount 
            WHERE monthID = NEW.month_id AND employeeID = NEW.employee_id AND yearID = NEW.year_id;
        END IF;

    ELSE
        DELETE FROM `prmgt_employee_salgenerated` 
        WHERE cat_id = 2 AND item_id = NEW.id AND employee_id = NEW.employee_id 
          AND month_id = NEW.month_id AND year_id = NEW.year_id 
          AND subCategoryID = NEW.allowance_id; 
    END IF;
END
$$
DELIMITER ;

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

--
-- AUTO_INCREMENT for table `prmgt_employee_allowances`
--
ALTER TABLE `prmgt_employee_allowances`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=80;

COMMIT;

### Step 19

DROP TABLE IF EXISTS `prmgt_employee_deductions`;

--
-- Table structure for table `prmgt_employee_deductions`
--
CREATE TABLE `prmgt_employee_deductions` (
  `id` int(11) NOT NULL,
  `department_id` int(11) DEFAULT NULL,
  `deduction_id` int(11) DEFAULT NULL,
  `employee_id` int(11) DEFAULT NULL,
  `empdeductiondate` date DEFAULT NULL,
  `month_id` int(11) DEFAULT NULL,
  `year_id` int(11) DEFAULT NULL,
  `deductionamount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `istaxable` varchar(45) DEFAULT NULL,
  `showonpayslip` varchar(45) DEFAULT NULL,
  `isActive` int(11) DEFAULT 0,
  `isDeleted` int(11) DEFAULT 0,
  `addedby` varchar(45) DEFAULT NULL,
  `dateadded` timestamp NULL DEFAULT current_timestamp(),
  `updatedBYUID` varchar(45) DEFAULT NULL,
  `dateUpdated` timestamp NULL DEFAULT NULL,
  `employeeDepartmentName` varchar(120) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Trigger for table `prmgt_employee_deductions`
--
DELIMITER $$
CREATE TRIGGER `prmgt_employee_deductions_AFTER_UPDATE` 
AFTER UPDATE ON `prmgt_employee_deductions` 
FOR EACH ROW 
BEGIN
    DECLARE deductionExist INT DEFAULT 0;
    DECLARE deductionName VARCHAR(40);
    DECLARE grossExists INT DEFAULT 0;
    DECLARE grossAmount DECIMAL(18,2) DEFAULT 0.00;

    IF NEW.isActive = 1 AND NEW.isDeleted = 0 THEN

        -- Get deduction description
        SELECT cded_description INTO deductionName 
        FROM prmgt_company_deductions 
        WHERE id = NEW.deduction_id 
        LIMIT 1;

        -- Check if deduction already exists in salgenerated
        SELECT 1 INTO deductionExist 
        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 = 3 
          AND subCategoryID = NEW.deduction_id
        LIMIT 1;

        -- Calculate total deductions
        SELECT SUM(cat_amount) INTO grossAmount 
        FROM prmgt_employee_salgenerated 
        WHERE month_id = NEW.month_id 
          AND employee_id = NEW.employee_id 
          AND year_id = NEW.year_id 
          AND cat_id = 3;

        -- Check if gross salary record exists
        SELECT 1 INTO grossExists 
        FROM prmgt_payroll_gross_salary 
        WHERE monthID = NEW.month_id 
          AND employeeID = NEW.employee_id 
          AND yearID = NEW.year_id
        LIMIT 1;

        IF deductionExist != 1 THEN
            INSERT INTO prmgt_employee_salgenerated 
                (subCategoryID, department_id, employee_id, month_id, year_id, 
                 cat_id, cat_name, cat_amount, addedby, item_id, paySlipItemTitle)
            VALUES 
                (NEW.deduction_id, NEW.department_id, NEW.employee_id, NEW.month_id, NEW.year_id, 
                 3, deductionName, NEW.deductionamount, NEW.addedby, NEW.id, 'SALARY DEDUCTIONS');

            IF grossExists != 1 THEN
                INSERT INTO prmgt_payroll_gross_salary 
                    (employeeID, departmentID, monthID, yearID, totalDeductions, addedByUID)
                VALUES 
                    (NEW.employee_id, NEW.department_id, NEW.month_id, NEW.year_id, grossAmount, NEW.addedby);
            ELSE
                UPDATE prmgt_payroll_gross_salary 
                SET totalDeductions = grossAmount 
                WHERE monthID = NEW.month_id AND employeeID = NEW.employee_id AND yearID = NEW.year_id;
            END IF;

        ELSE
            UPDATE prmgt_employee_salgenerated 
            SET cat_amount = NEW.deductionamount
            WHERE cat_id = 3 AND item_id = NEW.id AND employee_id = NEW.employee_id 
              AND month_id = NEW.month_id AND year_id = NEW.year_id 
              AND subCategoryID = NEW.deduction_id;

            UPDATE prmgt_payroll_gross_salary 
            SET totalDeductions = grossAmount 
            WHERE monthID = NEW.month_id AND employeeID = NEW.employee_id AND yearID = NEW.year_id;
        END IF;

    ELSE
        DELETE FROM prmgt_employee_salgenerated 
        WHERE cat_id = 3 AND item_id = NEW.id AND employee_id = NEW.employee_id 
          AND month_id = NEW.month_id AND year_id = NEW.year_id 
          AND subCategoryID = NEW.deduction_id;
    END IF;
END
$$
DELIMITER ;

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

--
-- AUTO_INCREMENT for table `prmgt_employee_deductions`
--
ALTER TABLE `prmgt_employee_deductions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

COMMIT;

### Step 20

DROP TABLE IF EXISTS `prmgt_staff_master_roll`;

-- Table structure for table `prmgt_staff_master_roll`
CREATE TABLE `prmgt_staff_master_roll` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `payrollNo` varchar(65) DEFAULT NULL,
  `employeeID` int(11) DEFAULT NULL,
  `employeeName` varchar(255) DEFAULT NULL,
  `basicSalary` decimal(12,0) DEFAULT 0,
  `casualSalary` decimal(12,0) DEFAULT 0,
  `overtimeAmount` decimal(12,0) DEFAULT 0,
  `grossSalary` decimal(12,0) DEFAULT 0,
  `nssf` decimal(12,0) DEFAULT 0,
  `nhif` decimal(12,0) DEFAULT 0,
  `housinglevy` decimal(12,0) DEFAULT 0,
  `paye` decimal(12,0) DEFAULT 0,
  `payeRefund` decimal(12,0) DEFAULT 0,
  `advanceAmount` decimal(12,0) DEFAULT 0,
  `bbfAmount` decimal(12,0) DEFAULT 0,
  `kenindiaSacco` decimal(12,0) DEFAULT 0,
  `saccoOne` decimal(12,0) DEFAULT 0,
  `totalDeduction` decimal(12,0) DEFAULT 0,
  `netPay` decimal(12,0) DEFAULT 0,
  `monthID` varchar(8) DEFAULT NULL,
  `yearID` varchar(8) DEFAULT NULL,
  `medicalAllowance` decimal(12,0) DEFAULT 0,
  `houseAllowance` decimal(12,0) DEFAULT 0,
  `actingAllowance` decimal(12,0) DEFAULT 0,
  `travelAllowance` decimal(12,0) DEFAULT 0,
  `saccoTwo` decimal(12,0) DEFAULT 0,
  `jobTitle` varchar(100) DEFAULT NULL,
  `abenteeismAmount` decimal(12,0) DEFAULT 0,
  `totalRelief` decimal(18,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

### Step 21

ALTER TABLE `visit_db`
ADD COLUMN `isAllowedPharmacy` TINYINT(1) NOT NULL DEFAULT 0
AFTER `ticketNumber`,
 ADD COLUMN pharmacyApprovedBy INT NULL AFTER `isAllowedPharmacy`,
 ADD COLUMN pharmacyActionTime DATETIME NULL AFTER `pharmacyApprovedBy`;
 
 ALTER TABLE `invms_stores`
ADD COLUMN `isReceivingStore` ENUM('Yes','No') NOT NULL DEFAULT 'No' AFTER `isActive`;

### Step 22(Removed)
### Step 23(Removed)
### Step 24

ALTER TABLE `eval_nurses_queue`
ADD COLUMN `opticalFinalRE_SPHERE` VARCHAR(20) NULL AFTER `opticalFinalPrescription`,
ADD COLUMN `opticalFinalRE_CYL` VARCHAR(20) NULL AFTER `opticalFinalRE_SPHERE`,
ADD COLUMN `opticalFinalRE_AXIS` VARCHAR(20) NULL AFTER `opticalFinalRE_CYL`,
ADD COLUMN `opticalFinalRE_ADD` VARCHAR(20) NULL AFTER `opticalFinalRE_AXIS`,
ADD COLUMN `opticalFinalRE_VA` VARCHAR(20) NULL AFTER `opticalFinalRE_ADD`,

ADD COLUMN `opticalFinalLE_SPHERE` VARCHAR(20) NULL AFTER `opticalFinalRE_VA`,
ADD COLUMN `opticalFinalLE_CYL` VARCHAR(20) NULL AFTER `opticalFinalLE_SPHERE`,
ADD COLUMN `opticalFinalLE_AXIS` VARCHAR(20) NULL AFTER `opticalFinalLE_CYL`,
ADD COLUMN `opticalFinalLE_ADD` VARCHAR(20) NULL AFTER `opticalFinalLE_AXIS`,
ADD COLUMN `opticalFinalLE_VA` VARCHAR(20) NULL AFTER `opticalFinalLE_ADD`;

### Step 25

DROP TABLE IF EXISTS `hr_leave_applications`;

CREATE TABLE `hr_leave_applications` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `employeeid` INT(11) NOT NULL,
  `leave_type` INT(11) NOT NULL,
  `start_date` DATE NOT NULL,
  `end_date` DATE DEFAULT NULL,
  `start_time` TIME NOT NULL DEFAULT '08:00:00',
  `end_time` TIME NOT NULL DEFAULT '17:00:00',
  `workingdays` DECIMAL(4,1) UNSIGNED NOT NULL DEFAULT 0.0,
  `handover_employee` INT(11) NOT NULL,
  `notes` TEXT NOT NULL,
  `application_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `supervisor_approval` TINYINT(1) DEFAULT 0,
  `supervisor_notes` TEXT DEFAULT NULL,
  `supervisor_approval_date` DATETIME DEFAULT NULL,
  `hr_approval` TINYINT(1) DEFAULT 0,
  `hr_approval_date` DATETIME DEFAULT NULL,
  `updatedby` INT(11) DEFAULT NULL,
  `dateedited` DATETIME DEFAULT NULL,
  `isActive` TINYINT(3) UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB 
  DEFAULT CHARSET=latin1 
  COLLATE=latin1_swedish_ci;

### Step 26

ALTER TABLE tbl_sys_config
ADD COLUMN sha_agent_id VARCHAR(255) DEFAULT NULL,
ADD COLUMN sha_consumer_key VARCHAR(255) DEFAULT NULL,
ADD COLUMN sha_consumer_secret VARCHAR(255) DEFAULT NULL,
ADD COLUMN sha_username VARCHAR(255) DEFAULT NULL,
ADD COLUMN sha_password VARCHAR(255) DEFAULT NULL,
ADD COLUMN sha_pem_key LONGTEXT DEFAULT NULL,
ADD COLUMN sha_pem_key_expiry DATETIME  NULL,
ADD COLUMN sha_privatepem_key LONGTEXT DEFAULT NULL,
ADD COLUMN sha_privatepem_key_expiry DATETIME  NULL,
ADD COLUMN sha_app_url VARCHAR(255) DEFAULT NULL;

ALTER TABLE finance_bill_split_requests
ADD COLUMN shaRebateAmount DECIMAL(12,2) NOT NULL DEFAULT 0,
ADD COLUMN shaRebateApplied TINYINT(1) NOT NULL DEFAULT 0;

ALTER TABLE visit_payers ADD COLUMN payerType VARCHAR(50) DEFAULT NULL AFTER splitAmount;

### Step 27(Removed)
### Step 28(Removed)
### Step 29(REMOVED) go to step 30

### Step 30

-- Drop table if it exists (optional)
DROP TABLE IF EXISTS `setup_asset_rooms`;
-- Create table
CREATE TABLE `setup_asset_rooms` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `roomFacilityID` INT(11) DEFAULT NULL,
  `floorID` INT(11) DEFAULT NULL,
  `roomName` VARCHAR(225) DEFAULT NULL,
  `roomCode` VARCHAR(225) DEFAULT NULL,
  `UIP` VARCHAR(120) DEFAULT NULL,
  `UPC` VARCHAR(120) DEFAULT NULL,
  `UID` INT(11) NOT NULL DEFAULT 1,
  `isActive` INT(11) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB 
  DEFAULT CHARSET=latin1 
  COLLATE=latin1_swedish_ci 
  AUTO_INCREMENT=1;
COMMIT;

### Step 31

-- Drop table if it exists (optional)
DROP TABLE IF EXISTS `tbl_company_brands`;

-- Create table
CREATE TABLE `tbl_company_brands` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `brandCode` VARCHAR(120) DEFAULT '',
  `brandName` VARCHAR(250) DEFAULT '',
  `brandDescription` VARCHAR(250) DEFAULT '',
  `isActive` INT(11) DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB 
  DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_general_ci 
  AUTO_INCREMENT=1;

COMMIT;

### Step 32

-- Drop table if it exists (optional)
DROP TABLE IF EXISTS `tbl_models`;

-- Create table
CREATE TABLE `tbl_models` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `companyBrandID` INT(11) DEFAULT NULL,
  `modelCode` VARCHAR(120) DEFAULT '',
  `modelName` VARCHAR(250) DEFAULT '',
  `modelDescription` VARCHAR(250) DEFAULT '',
  `isActive` INT(11) DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB 
  DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_general_ci 
  AUTO_INCREMENT=1;

COMMIT;

### Step 33

-- -----------------------------------------------------
-- Table structure for `fass_sub_categories`
-- -----------------------------------------------------

-- Drop table if it exists (optional)
DROP TABLE IF EXISTS `fass_sub_categories`;

CREATE TABLE `fass_sub_categories` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `categoryID` INT(11) DEFAULT NULL,
  `faCode` VARCHAR(120) DEFAULT '',
  `subCategoryName` VARCHAR(250) DEFAULT '',
  `subCategoryDescription` VARCHAR(250) DEFAULT '',
  `isActive` TINYINT(1) DEFAULT 1,
  `faValuationTypeID` INT(11) DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB 
  DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_general_ci 
  AUTO_INCREMENT=1;

COMMIT;

[HEAD OVER TO PAGE 2](https://hackmd.io/@--LognktRUmsG6YHux0Jjw/B1-IqySCge)

[Download SQL Folder(For Other Modules) And Import](https://drive.google.com/uc?export=download&id=1_Ib8bGhT9sPMdPRiDWvNm7UXVsrORxFn)
**Author:** Samwel