### Step 115

DROP TABLE IF EXISTS tbl_ke_clinical_groups;

CREATE TABLE tbl_ke_clinical_groups (
    group_code VARCHAR(40) PRIMARY KEY,
    group_name VARCHAR(200) NOT NULL,
    description TEXT,
    isActive TINYINT(1) DEFAULT 1
);

INSERT INTO tbl_ke_clinical_groups (group_code, group_name, description) VALUES
('INFECTIOUS', 'Infectious & parasitic diseases', 'ICD-11 Chapter 01 (1A00-1H0Z)'),
('NEOPLASMS', 'Cancers & neoplasms', 'ICD-11 Chapter 02 (2A00-2F9Z)'),
('BLOOD_IMMUNE_METABOLIC', 'Blood, immune & metabolic disorders', 'ICD-11 Chapters 03, 04, 05'),
('MENTAL_NEURO_SLEEP', 'Mental, neurological & sleep disorders', 'ICD-11 Chapters 06, 07, 08'),
('EYE_EAR', 'Eye & ear conditions', 'ICD-11 Chapters 09, 10'),
('CARDIOVASCULAR', 'Cardiovascular diseases', 'ICD-11 Chapter 11 (BA00-BE2Z)'),
('RESPIRATORY', 'Respiratory diseases', 'ICD-11 Chapter 12 (CA00-CB7Z)'),
('DIGESTIVE_LIVER', 'Digestive & liver diseases', 'ICD-11 Chapter 13 (DA00-DE2Z)'),
('SKIN_MSK', 'Skin & musculoskeletal disorders', 'ICD-11 Chapters 14, 15'),
('GENITOURINARY_SEXUAL', 'Genitourinary & sexual health', 'ICD-11 Chapters 16, 17'),
('MATERNAL_NEONATAL_CONGENITAL', 'Maternal, neonatal & congenital conditions', 'ICD-11 Chapters 18, 19, 20'),
('INJURY_EXTERNAL_HEALTH', 'Injuries, external causes & health-service factors', 'ICD-11 Chapters 21–26');


ALTER TABLE tbl_icd11_diseases
    DROP COLUMN IF EXISTS ke_group_code,
    DROP COLUMN IF EXISTS chapter_code;


ALTER TABLE tbl_icd11_diseases
    ADD COLUMN chapter_code VARCHAR(2) AFTER disease_code,
    ADD COLUMN ke_group_code VARCHAR(40) AFTER chapter_code;
    
    ALTER TABLE tbl_icd11_diseases
    ADD INDEX idx_chapter_code (chapter_code),
    ADD INDEX idx_ke_group_code (ke_group_code);


ALTER TABLE tbl_icd11_diseases
ADD CONSTRAINT fk_ke_clinical_group
FOREIGN KEY (ke_group_code)
REFERENCES tbl_ke_clinical_groups(group_code);


SET @fk := (
    SELECT CONSTRAINT_NAME
    FROM information_schema.KEY_COLUMN_USAGE
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'tbl_icd11_diseases'
      AND REFERENCED_TABLE_NAME = 'tbl_ke_clinical_groups'
    LIMIT 1
);

SET @sql := IF(@fk IS NOT NULL,
    CONCAT('ALTER TABLE tbl_icd11_diseases DROP FOREIGN KEY ', @fk),
    'SELECT "No FK to drop"');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;



-- Update logic using ICD-11 Stem Code Prefixes
UPDATE tbl_icd11_diseases
SET chapter_code = 
CASE 
    WHEN disease_code REGEXP '^01' OR disease_code REGEXP '^1[A-H]' THEN '01'
    WHEN disease_code REGEXP '^02' OR disease_code REGEXP '^2[A-F]' THEN '02'
    WHEN disease_code REGEXP '^03' OR disease_code REGEXP '^3[A-C]' THEN '03'
    WHEN disease_code REGEXP '^04' OR disease_code REGEXP '^4[A-B]' THEN '04'
    WHEN disease_code REGEXP '^05' OR disease_code REGEXP '^5[A-D]' THEN '05'
    WHEN disease_code REGEXP '^06' OR disease_code REGEXP '^6[A-E]' THEN '06'
    WHEN disease_code REGEXP '^07' OR disease_code REGEXP '^7[A-B]' THEN '07'
    WHEN disease_code REGEXP '^08' OR disease_code REGEXP '^8[A-E]' THEN '08'
    WHEN disease_code REGEXP '^09' OR disease_code REGEXP '^9[A-E]' THEN '09'
    WHEN disease_code REGEXP '^10' OR disease_code REGEXP '^A[A-C]' THEN '10'
    WHEN disease_code REGEXP '^11' OR disease_code REGEXP '^B[A-E]' THEN '11'
    WHEN disease_code REGEXP '^12' OR disease_code REGEXP '^C[A-B]' THEN '12'
    WHEN disease_code REGEXP '^13' OR disease_code REGEXP '^D[A-E]' THEN '13'
    WHEN disease_code REGEXP '^14' OR disease_code REGEXP '^E[A-E]' THEN '14'
    WHEN disease_code REGEXP '^15' OR disease_code REGEXP '^F[A-Y]' THEN '15'
    WHEN disease_code REGEXP '^16' OR disease_code REGEXP '^G[A-E]' THEN '16'
    WHEN disease_code REGEXP '^17' OR disease_code REGEXP '^H[A-C]' THEN '17'
    WHEN disease_code REGEXP '^18' OR disease_code REGEXP '^JA' THEN '18'
    WHEN disease_code REGEXP '^19' OR disease_code REGEXP '^KA' THEN '19'
    WHEN disease_code REGEXP '^20' OR disease_code REGEXP '^LA' THEN '20'
    WHEN disease_code REGEXP '^21' OR disease_code REGEXP '^M[A-G]' THEN '21'
    WHEN disease_code REGEXP '^22' OR disease_code REGEXP '^N[A-F]' THEN '22'
    WHEN disease_code REGEXP '^23' OR disease_code REGEXP '^P[A-F]' THEN '23'
    WHEN disease_code REGEXP '^24' OR disease_code REGEXP '^Q[A-Z]' THEN '24'
    WHEN disease_code REGEXP '^25' OR disease_code REGEXP '^RA' THEN '25'
    ELSE chapter_code 
END;

-- Map Chapters to Clinical Groups
UPDATE tbl_icd11_diseases
SET ke_group_code = 
CASE 
    WHEN chapter_code = '01' THEN 'INFECTIOUS'
    WHEN chapter_code = '02' THEN 'NEOPLASMS'
    WHEN chapter_code IN ('03','04','05') THEN 'BLOOD_IMMUNE_METABOLIC'
    WHEN chapter_code IN ('06','07','08') THEN 'MENTAL_NEURO_SLEEP'
    WHEN chapter_code IN ('09','10') THEN 'EYE_EAR'
    WHEN chapter_code = '11' THEN 'CARDIOVASCULAR'
    WHEN chapter_code = '12' THEN 'RESPIRATORY'
    WHEN chapter_code = '13' THEN 'DIGESTIVE_LIVER'
    WHEN chapter_code IN ('14','15') THEN 'SKIN_MSK'
    WHEN chapter_code IN ('16','17') THEN 'GENITOURINARY_SEXUAL'
    WHEN chapter_code IN ('18','19','20') THEN 'MATERNAL_NEONATAL_CONGENITAL'
    WHEN chapter_code IN ('21','22','23','24','25','26') THEN 'INJURY_EXTERNAL_HEALTH'
    ELSE NULL
END;

### Step 116

CREATE TABLE tbl_705a_categories (
    id INT PRIMARY KEY,
    code VARCHAR(10),
    name VARCHAR(255)
);

CREATE TABLE tbl_705b_categories (
    id INT PRIMARY KEY,
    code VARCHAR(10),
    name VARCHAR(255)
);

DROP TABLE IF EXISTS tbl_moh_705a;

CREATE TABLE tbl_moh_705a (
    id INT PRIMARY KEY,
    code VARCHAR(10) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    isActive TINYINT(1) DEFAULT 1
);

INSERT INTO tbl_moh_705a (id, code, name, description) VALUES
(1, '705A01', 'Communicable diseases', 'All infectious and parasitic diseases'),
(2, '705A02', 'Non-communicable diseases', 'Chronic and lifestyle-related diseases'),
(3, '705A03', 'Injuries and trauma', 'All injuries, accidents and violence'),
(4, '705A04', 'Maternal conditions', 'Pregnancy, childbirth and puerperium'),
(5, '705A05', 'Neonatal conditions', 'Conditions originating in the neonatal period'),
(6, '705A06', 'Congenital anomalies', 'Congenital malformations and chromosomal abnormalities'),
(7, '705A07', 'Signs, symptoms and ill-defined conditions', 'R-codes and undiagnosed conditions'),
(8, '705A08', 'Other conditions', 'All other conditions not elsewhere classified');


DROP TABLE IF EXISTS tbl_moh_705b;

CREATE TABLE tbl_moh_705b (
    id INT PRIMARY KEY,
    code VARCHAR(10) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    program_area VARCHAR(100),
    description TEXT,
    isActive TINYINT(1) DEFAULT 1
);


INSERT INTO tbl_moh_705b (id, code, name, program_area, description) VALUES
(1, '705B01', 'HIV and AIDS', 'HIV', 'HIV related diseases'),
(2, '705B02', 'Tuberculosis', 'TB', 'All forms of tuberculosis'),
(3, '705B03', 'Malaria', 'Malaria', 'Confirmed and clinical malaria'),
(4, '705B04', 'Maternal health', 'RMNCAH', 'Maternal health conditions'),
(5, '705B05', 'Child health', 'RMNCAH', 'Childhood illnesses'),
(6, '705B06', 'Neonatal health', 'RMNCAH', 'Neonatal illnesses'),
(7, '705B07', 'Nutrition disorders', 'Nutrition', 'Malnutrition and nutrition-related conditions'),
(8, '705B08', 'Mental health', 'Mental Health', 'Mental and behavioural disorders'),
(9, '705B09', 'Injuries and violence', 'Injury', 'All injury and violence-related conditions'),
(10,'705B10', 'Non-communicable disease program', 'NCD', 'NCD program tracking'),
(11,'705B11', 'Environmental health', 'Environmental Health', 'Environment-related conditions'),
(12,'705B12', 'Public health surveillance', 'Surveillance', 'Epidemic-prone and surveillance conditions');


DROP TABLE IF EXISTS tbl_icd11_705a_map;

CREATE TABLE tbl_icd11_705a_map (
    disease_code VARCHAR(122) PRIMARY KEY,
    moh_705a_id INT NOT NULL,
    FOREIGN KEY (moh_705a_id) REFERENCES tbl_moh_705a(id)
);


DROP TABLE IF EXISTS tbl_icd11_705b_map;

CREATE TABLE tbl_icd11_705b_map (
    disease_code VARCHAR(122),
    moh_705b_id INT,
    PRIMARY KEY (disease_code, moh_705b_id),
    FOREIGN KEY (moh_705b_id) REFERENCES tbl_moh_705b(id)
);


INSERT INTO tbl_icd11_705a_map (disease_code, moh_705a_id)
SELECT disease_code,
CASE
    WHEN ke_group_code IN ('INFECTIOUS','RESPIRATORY','DIGESTIVE_LIVER')
        THEN 1
    WHEN ke_group_code IN ('CARDIOVASCULAR','BLOOD_IMMUNE_METABOLIC','SKIN_MSK')
        THEN 2
    WHEN ke_group_code = 'MENTAL_NEURO_SLEEP'
        THEN 2
    WHEN ke_group_code = 'INJURY_EXTERNAL_HEALTH'
        THEN 3
    WHEN chapter_code = '18'
        THEN 4
    WHEN chapter_code = '19'
        THEN 5
    WHEN chapter_code = '20'
        THEN 6
    WHEN chapter_code = '21'
        THEN 7
    ELSE 8
END
FROM tbl_icd11_diseases
WHERE disease_code IS NOT NULL
ON DUPLICATE KEY UPDATE moh_705a_id = VALUES(moh_705a_id);



INSERT IGNORE INTO tbl_icd11_705b_map
SELECT disease_code, 1
FROM tbl_icd11_diseases
WHERE disease_code REGEXP '^(B20|B21|B22|B23|B24)';

INSERT IGNORE INTO tbl_icd11_705b_map
SELECT disease_code, 2
FROM tbl_icd11_diseases
WHERE disease_code REGEXP '^A1[5-9]';

INSERT IGNORE INTO tbl_icd11_705b_map
SELECT disease_code, 3
FROM tbl_icd11_diseases
WHERE disease_code REGEXP '^B5[0-4]';

INSERT IGNORE INTO tbl_icd11_705b_map
SELECT disease_code, 4
FROM tbl_icd11_diseases
WHERE chapter_code = '18';

INSERT IGNORE INTO tbl_icd11_705b_map
SELECT disease_code, 5
FROM tbl_icd11_diseases
WHERE chapter_code IN ('01','06','12','13')
AND patientID IS NOT NULL;

INSERT IGNORE INTO tbl_icd11_705b_map
SELECT disease_code, 6
FROM tbl_icd11_diseases
WHERE chapter_code = '19';

INSERT IGNORE INTO tbl_icd11_705b_map
SELECT disease_code, 7
FROM tbl_icd11_diseases
WHERE disease_code REGEXP '^E4[0-6]';

INSERT IGNORE INTO tbl_icd11_705b_map
SELECT disease_code, 8
FROM tbl_icd11_diseases
WHERE chapter_code IN ('06','07','08');


INSERT IGNORE INTO tbl_icd11_705b_map
SELECT disease_code, 9
FROM tbl_icd11_diseases
WHERE chapter_code IN ('22','23');

INSERT IGNORE INTO tbl_icd11_705b_map
SELECT disease_code, 10
FROM tbl_icd11_diseases
WHERE ke_group_code IN ('CARDIOVASCULAR','BLOOD_IMMUNE_METABOLIC','SKIN_MSK');

INSERT IGNORE INTO tbl_icd11_705b_map
SELECT disease_code, 11
FROM tbl_icd11_diseases
WHERE disease_code REGEXP '^(J6|T5)';

INSERT IGNORE INTO tbl_icd11_705b_map
SELECT disease_code, 12
FROM tbl_icd11_diseases
WHERE ke_group_code = 'INFECTIOUS';

UPDATE tbl_icd11_diseases d
JOIN tbl_icd11_705a_map m
  ON d.disease_code = m.disease_code
SET d.diseaseID_705a = m.moh_705a_id;

UPDATE tbl_icd11_diseases d
JOIN (
    SELECT disease_code, MIN(moh_705b_id) AS moh_705b_id
    FROM tbl_icd11_705b_map
    GROUP BY disease_code
) b
ON d.disease_code = b.disease_code
SET d.diseaseID_705b = b.moh_705b_id;

### Step 117

ALTER TABLE tbl_users
ADD COLUMN isReceiveSMS TINYINT(1) NOT NULL DEFAULT 0 AFTER userOnlineStatus,
ADD COLUMN isReceiveEmail TINYINT(1) NOT NULL DEFAULT 0 AFTER isReceiveSMS;

CREATE TABLE tbl_daily_email_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    report_type VARCHAR(50) NOT NULL,
    sent_date DATE NOT NULL,
    created_at DATETIME NOT NULL,
    recipients_count INT(11) NOT NULL DEFAULT 0,
    UNIQUE KEY uq_report_day (report_type, sent_date)
);

ALTER TABLE visit_db
ADD COLUMN isPayLater TINYINT(1) NOT NULL DEFAULT 0
COMMENT '1 = Pay Later, 0 = Pay Now'
AFTER visitID;

ALTER TABLE `inpatient_discharge_notes`
ADD COLUMN `appointmentDate` DATE DEFAULT NULL AFTER `DischargeMode`;


CREATE TABLE tbl_dx_impressions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    disease_id INT, -- Links to tbl_icd11_diseases.id
    impression_text VARCHAR(255),
    isActive TINYINT DEFAULT 1
);

### Step 118

DROP TABLE IF EXISTS `pharma_drug_use_duration`;

CREATE TABLE `pharma_drug_use_duration` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `drugDurationName` varchar(255) NOT NULL DEFAULT '',
  `isActive` int(4) NOT NULL DEFAULT 1,
  `drugDurationDescription` varchar(55) DEFAULT '',
  `daysNumericalEquivalent` decimal(11,1) NOT NULL DEFAULT 0.0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

INSERT INTO `pharma_drug_use_duration`
(`drugDurationName`, `isActive`, `drugDurationDescription`, `daysNumericalEquivalent`)
VALUES
('Hour(s)',  1, 'Hour(s)', 0.0417),   -- 1 hour = 1/24 day
('Day(s)',   1, 'Day(s)',  1.0),
('Week(s)',  1, 'Week(s)', 7.0),
('Month(s)', 1, 'Month(s)', 30.0),
('Year(s)',  1, 'Year(s)',  365.0);

COMMIT;

### Step 119

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

-- Create table
CREATE TABLE `prmgt_housinglevy_rates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `minamt` decimal(10,2) DEFAULT NULL,
  `maxamt` decimal(10,2) DEFAULT NULL,
  `deduction` decimal(10,2) DEFAULT NULL,
  `rate` decimal(5,2) DEFAULT NULL,
  `addedby` int(11) DEFAULT NULL,
  `toinfinity` varchar(45) DEFAULT 'No',
  `isActive` int(11) NOT NULL DEFAULT 1,
  `uip` varchar(120) DEFAULT '',
  `upc` varchar(350) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- Insert initial data
INSERT INTO `prmgt_housinglevy_rates` 
(`id`, `minamt`, `maxamt`, `deduction`, `rate`, `addedby`, `toinfinity`, `isActive`, `uip`, `upc`) 
VALUES
(1, '1.00', '99999.00', '150.00', '1.50', NULL, 'YES', 1, '102.212.236.189', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/143.0.0.0 Safari/537.36');

-- Ensure auto_increment starts correctly
ALTER TABLE `prmgt_housinglevy_rates` AUTO_INCREMENT = 2;

COMMIT;

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

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

### Step 120

CREATE TABLE optical_prescriptions (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    patientUID INT UNSIGNED NOT NULL,        -- Unique patient ID
    visitID VARCHAR(100) NOT NULL,           -- Visit/encounter ID (varchar)
    otherInfo LONGTEXT NOT NULL,             -- WYSIWYG content (HTML)
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    createdBy INT UNSIGNED NOT NULL          -- Staff ID who created it
);

ALTER TABLE tbl_faclities_configs
ADD COLUMN configHasShowRadiologyReport TINYINT(1) DEFAULT 0,
ADD COLUMN configHasShowNutritionReport TINYINT(1) DEFAULT 0,
ADD COLUMN configHasShowOpticalReport TINYINT(1) DEFAULT 0,
ADD COLUMN configHasCangeCashToInsurance TINYINT(1) DEFAULT 0,
ADD COLUMN configHasConsultantsModule TINYINT(1) DEFAULT 0;

### Step 121

ALTER TABLE visit_db
ADD currentInsuranceSchemeID INT(11) NULL,
ADD debtID INT(11) NULL,
ADD debtCleared TINYINT(1) DEFAULT 0;

### Step 122

ALTER TABLE tbl_purchaseorders_items
ADD COLUMN isActive INT(11) DEFAULT 1,
ADD COLUMN isDeleted INT(11) DEFAULT 0,
ADD COLUMN dateTimeDeleted DATETIME NULL,
ADD COLUMN deletedBYUID INT(11) NULL,
ADD COLUMN deletionUIP VARCHAR(220) NULL;

### Step 123

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

ALTER TABLE tbl_sys_config
ADD COLUMN etims_username VARCHAR(255) NULL AFTER etims_status,
ADD COLUMN etims_password VARCHAR(255) NULL AFTER etims_username;

### Step 124

ALTER TABLE pharma_drug_use_duration
MODIFY daysNumericalEquivalent DECIMAL(11,4) NOT NULL DEFAULT 0.0;

UPDATE pharma_drug_use_duration
SET daysNumericalEquivalent = 0.0417
WHERE drugDurationName = 'Hour(s)';

UPDATE pharma_drug_use_duration
SET daysNumericalEquivalent = 1.0000
WHERE drugDurationName = 'Hour(s)';

### Step 125

CREATE TABLE etims_receipts (
    id BIGINT(20) NOT NULL AUTO_INCREMENT,

    etimsInvoiceNo VARCHAR(50) NOT NULL,
    traderInvoiceNo VARCHAR(50) NOT NULL,

    totalAmount DECIMAL(18,2) NOT NULL,
    totalTaxableAmount DECIMAL(18,2) NOT NULL,
    totalTaxAmount DECIMAL(18,2) NOT NULL,

    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,

    invoiceVerificationUrl TEXT NOT NULL,

    scuReceiptNo VARCHAR(50) NOT NULL,
    receiptNo VARCHAR(50) NOT NULL,          -- (KRA receipt number)

    customerPin VARCHAR(50) NULL,
    customerName VARCHAR(255) NOT NULL,

    signature VARCHAR(100) NOT NULL,
    internalData TEXT NOT NULL,

    scdcId VARCHAR(50) NOT NULL,

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_etimsInvoiceNo (etimsInvoiceNo),
    INDEX idx_traderInvoiceNo (traderInvoiceNo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


ALTER TABLE etims_receipts
ADD UNIQUE KEY uniq_receipt (receiptNo),
ADD UNIQUE KEY uniq_etims_invoice (etimsInvoiceNo);

### Step 126

ALTER TABLE tbl_faclities_configs
ADD COLUMN configHasCashDirectBillingServices TINYINT(1) DEFAULT 0,
ADD COLUMN configHasCashDirectBillingLaboratory TINYINT(1) DEFAULT 0,
ADD COLUMN configHasCashDirectBillingPharmacy TINYINT(1) DEFAULT 0,
ADD COLUMN configHasCashDirectBillingConsumables TINYINT(1) DEFAULT 0;

### Step 127

ALTER TABLE prmgt_staff_advance_disbursments
ADD COLUMN advanceReversalReason TEXT NULL AFTER advanceStatus,
ADD COLUMN advanceReversedBy INT(11) NULL AFTER advanceReversalReason,
ADD COLUMN advanceReversalDate DATETIME NULL AFTER advanceReversedBy;

### Step 128

ALTER TABLE prmgt_staff_master_roll ADD COLUMN totalAllowances DECIMAL(15,2) DEFAULT 0;

### Step 129

CREATE INDEX idx_dx_filter 
ON eval_patients_diagnosis
(isDeleted, diagnosisName, diagnosisTimeStamp, diagnosedPatientID);

CREATE INDEX idx_patient_filters
ON tbl_registered_patients
(id, patientDateOfirth, genderID);


CREATE INDEX idx_icd_disease
ON tbl_icd11_diseases (disease);

### Step 130

DROP TABLE IF EXISTS `prmgt_payroll_locumemployees_earnings`;

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

-- --------------------------------------------------------
-- Triggers for table `prmgt_payroll_locumemployees_earnings`
-- --------------------------------------------------------
DELIMITER $$

CREATE TRIGGER `prmgt_payroll_locumemployees_earnings_AFTER_INSERT`
AFTER INSERT ON `prmgt_payroll_locumemployees_earnings`
FOR EACH ROW
BEGIN
    INSERT INTO `prmgt_locumemployee_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,
        20,
        'LOCUM',
        NEW.locum_amount,
        NEW.id,
        NEW.addedby,
        'LOCUM EARNINGS'
    );
END$$

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

    SELECT 1 INTO locumExist
    FROM prmgt_locumemployee_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 = 20
    LIMIT 1;

    IF locumExist != 1 THEN
        INSERT INTO `prmgt_locumemployee_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,
            20,
            'LOCUM',
            NEW.locum_amount,
            NEW.id,
            NEW.addedby
        );
    ELSE
        UPDATE `prmgt_locumemployee_salgenerated`
        SET `cat_amount` = NEW.locum_amount
        WHERE `cat_id` = 20
          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_locumemployees_earnings`
-- --------------------------------------------------------
ALTER TABLE `prmgt_payroll_locumemployees_earnings`
  ADD PRIMARY KEY (`id`);

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

COMMIT;

### Step 131

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

-- Create table
CREATE TABLE `prmgt_locumemployee_salgenerated` (
  `id` bigint(50) NOT NULL AUTO_INCREMENT,
  `department_id` int(11) DEFAULT NULL,
  `employee_id` int(11) DEFAULT NULL,
  `month_id` int(11) DEFAULT NULL,
  `year_id` year(4) DEFAULT NULL,
  `cat_id` int(11) DEFAULT NULL,
  `subCategoryID` int(11) DEFAULT 0,
  `cat_name` varchar(45) DEFAULT NULL,
  `cat_amount` double(10,2) DEFAULT NULL,
  `cat_taxable` varchar(3) DEFAULT NULL,
  `cat_show_on_slip` varchar(3) DEFAULT NULL,
  `item_id` int(11) DEFAULT NULL,
  `addedby` varchar(45) DEFAULT NULL,
  `dateadded` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `paySlipItemTitle` varchar(120) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

### Step 132

DROP TABLE IF EXISTS `prmgt_locumstaff_master_roll`;

-- Table structure for table `prmgt_locumstaff_master_roll`
CREATE TABLE `prmgt_locumstaff_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,
  `grossSalary` decimal(12,0) DEFAULT 0,
  `paye` decimal(12,0) DEFAULT 0,
  `payeRefund` 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,
  `jobTitle` varchar(100) DEFAULT NULL,
  `totalRelief` decimal(18,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

### Step 133

ALTER TABLE tbl_icd10_diseases
ADD COLUMN diseaseID_705a INT(11) NULL AFTER code,
ADD COLUMN diseaseID_705b INT(11) NULL AFTER diseaseID_705a;

### Step 134

ALTER TABLE hr_company_employees
ADD COLUMN is_locum_user TINYINT NOT NULL DEFAULT 0;

### Step 135

ALTER TABLE tbl_users
ADD COLUMN lastActivity DATETIME
    DEFAULT NULL
    AFTER userOnlineStatus;

### Step 136

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

### Step 137

CREATE TABLE pharmacy_returns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    prescriptionBillID INT,
    corePresID INT,
    drugItemID INT,
    returnQty DECIMAL(10,2),
    isConfirmed TINYINT(1) DEFAULT 0,
    confirmedBy INT NULL,
    confirmedAt DATETIME NULL,
    createdBy INT,
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP
);


CREATE INDEX idx_returns_pending ON pharmacy_returns (isConfirmed, createdAt);

### Step 138

CREATE TABLE morgue_blocks (
    id INT(3) NOT NULL AUTO_INCREMENT,
    block_name VARCHAR(50) NOT NULL,
    description VARCHAR(100) NOT NULL,
    status INT(11) NOT NULL DEFAULT 1,
    dateadded TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
        ON UPDATE CURRENT_TIMESTAMP,
    uid INT(11) NOT NULL,
    uip VARCHAR(50) NOT NULL,
    upc VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
);

### Step 139

CREATE TABLE morgue_room_listings (
    id INT(3) NOT NULL AUTO_INCREMENT,
    block_id INT(3) NOT NULL,
    room_name VARCHAR(50) NOT NULL,
    room_description VARCHAR(100) NOT NULL,
    StoreID INT(3) DEFAULT NULL,
    CStoreID INT(3) DEFAULT NULL,
    gender INT(3) NOT NULL,
    visiting_policy VARCHAR(50) NOT NULL,
    status INT(11) NOT NULL DEFAULT 1,
    dateadded TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
        ON UPDATE CURRENT_TIMESTAMP(),
    uid INT(11) NOT NULL,
    uip VARCHAR(50) NOT NULL,
    upc VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
);

### Step 140

CREATE TABLE morgue_coolers (
    id INT(3) NOT NULL AUTO_INCREMENT,
    cooler_name VARCHAR(10) NOT NULL,
    cooler_type_id INT(3) NOT NULL,
    room_id INT(3) NOT NULL,
    cooler_type VARCHAR(255) NOT NULL,
    cooler_description VARCHAR(255) NOT NULL,
    status INT(11) NOT NULL DEFAULT 1,
    is_occupied INT(3) NOT NULL DEFAULT 0,
    cooler_price DECIMAL(10,0) DEFAULT 0,
    coop_price DECIMAL(10,0) DEFAULT 0,
    dateadded TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP(),
    uid INT(11) NOT NULL,
    uip VARCHAR(50) NOT NULL,
    upc VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
);

### Step 141

ALTER TABLE inpatient_last_office
ADD COLUMN morgue_block_id INT(3) AFTER Morgue_wing,
ADD COLUMN morgue_room_id INT(3) AFTER morgue_block_id,
ADD COLUMN morgue_cooler_id INT(3) AFTER morgue_room_id;

### Step 142

ALTER TABLE tbl_asset_register 
ADD COLUMN assetPurchaseValue DECIMAL(15,2) DEFAULT 0.00;

### Step 143

ALTER TABLE tbl_patient_checkin ADD COLUMN isNotified TINYINT(1) DEFAULT 0;

### Step 144

ALTER TABLE theatre_anaesthetist_vitals_log
ADD COLUMN notes TEXT NULL AFTER bloodPressure;

### Step 145 (Only install this if you are upgrading to PHP 8.3; otherwise, ignore it since the current system is running PHP 7.4.33.)

sudo apt install fonts-dejavu-core

### Step 146

ALTER TABLE tbl_faclities_configs
ADD COLUMN configHasPatientsReceiveEmail TINYINT(1) DEFAULT 0,
ADD COLUMN configHasStaffsReceiveEmail TINYINT(1) DEFAULT 0,
ADD COLUMN configHasClientSMS TINYINT(1) DEFAULT 0,
ADD COLUMN configHasStaffsReceiveSMS TINYINT(1) DEFAULT 0;

### Step 147

ALTER TABLE `acc_bank_recon`
ADD COLUMN `isPharmacyTxn` INT(11) NOT NULL DEFAULT 0 AFTER `trxType`;

### Step 148

ALTER TABLE `tbl_patient_billable_items`
ADD COLUMN `displayOnInvoice` INT(11) NOT NULL DEFAULT 1;