-- Param Hospital extension migration. Run this ONCE after hmsci.sql and upgrade_existing_database.sql.
-- It adds only the new reception, receipt and pricing features; the original dump remains unchanged.

CREATE TABLE IF NOT EXISTS `receptionist` (
  `receptionist_id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` varchar(40) NOT NULL,
  `name` varchar(150) NOT NULL, `email` varchar(150) NOT NULL, `password` varchar(255) NOT NULL,
  `phone` varchar(30) NOT NULL DEFAULT '', `address` text NOT NULL, `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL, PRIMARY KEY (`receptionist_id`), UNIQUE KEY `reception_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `patient_receipts` (
  `receipt_id` int(11) NOT NULL AUTO_INCREMENT, `receipt_number` varchar(50) NOT NULL,
  `patient_id` int(11) NOT NULL, `department` varchar(100) NOT NULL, `service_name` varchar(200) NOT NULL,
  `invoice_id` int(11) DEFAULT NULL, `payment_id` int(11) DEFAULT NULL, `amount` decimal(10,2) NOT NULL DEFAULT 0,
  `issued_by` int(11) DEFAULT NULL, `issued_at` datetime NOT NULL, PRIMARY KEY (`receipt_id`), UNIQUE KEY `receipt_number` (`receipt_number`), KEY `receipt_patient` (`patient_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ward_pricing` (
  `ward_pricing_id` int(11) NOT NULL AUTO_INCREMENT, `ward_type` varchar(100) NOT NULL,
  `daily_price` decimal(10,2) NOT NULL DEFAULT 0, `description` varchar(255) NOT NULL DEFAULT '', `active` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`ward_pricing_id`), UNIQUE KEY `ward_type` (`ward_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT IGNORE INTO `ward_pricing` (`ward_type`,`daily_price`,`description`) VALUES
('General Ward',500.00,'Shared general ward per day'),('Private Ward',2500.00,'Private room per day'),('ICU',8000.00,'Intensive care unit per day'),('PICU',9000.00,'Paediatric intensive care unit per day');

-- Compatibility-safe columns: this block also works where a previous upgrade already added a column.
SET @db := DATABASE();
SET @q := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='patient' AND column_name='patient_code')=0, 'ALTER TABLE patient ADD COLUMN patient_code varchar(30) NOT NULL DEFAULT '''' AFTER patient_id', 'SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;
SET @q := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='bed' AND column_name='ward_type')=0, 'ALTER TABLE bed ADD COLUMN ward_type varchar(100) NOT NULL DEFAULT '''' AFTER type', 'SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;
SET @q := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='bed' AND column_name='daily_price')=0, 'ALTER TABLE bed ADD COLUMN daily_price decimal(10,2) NOT NULL DEFAULT 0 AFTER ward_type', 'SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;
UPDATE `bed` SET `ward_type`=CASE LOWER(`type`) WHEN 'ward' THEN 'General Ward' WHEN 'cabin' THEN 'Private Ward' WHEN 'icu' THEN 'ICU' ELSE `type` END WHERE `ward_type`='';
UPDATE `bed` b JOIN `ward_pricing` w ON b.`ward_type`=w.`ward_type` SET b.`daily_price`=w.`daily_price` WHERE b.`daily_price`=0;

-- 55 commonly used laboratory tests. Test code is the stable key used by ordering and pricing.
-- These compatibility fields are added only for older installations that used the lab hotfix SQL.
SET @q := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='lab_tests' AND column_name='category')=0, 'ALTER TABLE lab_tests ADD COLUMN category varchar(100) NOT NULL DEFAULT '''' AFTER test_name', 'SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;
SET @q := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='lab_tests' AND column_name='container_type')=0, 'ALTER TABLE lab_tests ADD COLUMN container_type varchar(100) NOT NULL DEFAULT '''' AFTER sample_type', 'SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;
SET @q := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='lab_tests' AND column_name='unit')=0, 'ALTER TABLE lab_tests ADD COLUMN unit varchar(50) NOT NULL DEFAULT '''' AFTER price', 'SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;
SET @q := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='lab_tests' AND column_name='reference_range')=0, 'ALTER TABLE lab_tests ADD COLUMN reference_range longtext NOT NULL AFTER unit', 'SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;
SET @q := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='lab_tests' AND column_name='instructions')=0, 'ALTER TABLE lab_tests ADD COLUMN instructions longtext NOT NULL AFTER turnaround_time', 'SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;
SET @q := IF((SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=@db AND table_name='lab_tests' AND index_name='lab_test_code_unique')=0, 'ALTER TABLE lab_tests ADD UNIQUE KEY lab_test_code_unique (test_code)', 'SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;
INSERT IGNORE INTO `lab_tests` (`test_name`,`test_code`,`category`,`sample_type`,`container_type`,`department_id`,`price`,`unit`,`reference_range`,`turnaround_time`,`instructions`,`status`,`created_at`,`updated_at`) VALUES
('Complete Blood Count','CBC','Hematology','Blood','EDTA',0,350,'','Age/sex dependent','24 hours','', 'active',NOW(),NOW()),('Hemoglobin','HB','Hematology','Blood','EDTA',0,120,'g/dL','12-17','24 hours','', 'active',NOW(),NOW()),('ESR','ESR','Hematology','Blood','EDTA',0,150,'mm/hr','0-20','24 hours','', 'active',NOW(),NOW()),('Peripheral Smear','PS','Hematology','Blood','EDTA',0,250,'','As reported','24 hours','', 'active',NOW(),NOW()),('Prothrombin Time INR','PTINR','Hematology','Plasma','Citrate',0,350,'seconds','11-15','24 hours','', 'active',NOW(),NOW()),
('Blood Group ABO Rh','ABORH','Blood Bank','Blood','EDTA',0,250,'','As reported','24 hours','', 'active',NOW(),NOW()),('Fasting Blood Sugar','FBS','Biochemistry','Blood','Fluoride',0,100,'mg/dL','70-100','24 hours','Fasting 8 hours', 'active',NOW(),NOW()),('Post Prandial Blood Sugar','PPBS','Biochemistry','Blood','Fluoride',0,100,'mg/dL','Below 140','24 hours','2 hours after food', 'active',NOW(),NOW()),('HbA1c','HBA1C','Biochemistry','Blood','EDTA',0,450,'%','Below 5.7','24 hours','', 'active',NOW(),NOW()),('Random Blood Sugar','RBS','Biochemistry','Blood','Fluoride',0,100,'mg/dL','70-140','24 hours','', 'active',NOW(),NOW()),
('Liver Function Test','LFT','Biochemistry','Serum','Plain',0,700,'','As reported','24 hours','', 'active',NOW(),NOW()),('Kidney Function Test','KFT','Biochemistry','Serum','Plain',0,650,'','As reported','24 hours','', 'active',NOW(),NOW()),('Lipid Profile','LIPID','Biochemistry','Serum','Plain',0,600,'mg/dL','As reported','24 hours','Fasting 10 hours', 'active',NOW(),NOW()),('Thyroid Profile','TFT','Hormones','Serum','Plain',0,750,'','As reported','24 hours','', 'active',NOW(),NOW()),('Vitamin D Total','VITD','Immunology','Serum','Plain',0,1200,'ng/mL','30-100','48 hours','', 'active',NOW(),NOW()),
('Vitamin B12','B12','Immunology','Serum','Plain',0,900,'pg/mL','200-900','48 hours','', 'active',NOW(),NOW()),('Serum Calcium','CAL','Biochemistry','Serum','Plain',0,200,'mg/dL','8.5-10.5','24 hours','', 'active',NOW(),NOW()),('Serum Iron','IRON','Biochemistry','Serum','Plain',0,300,'ug/dL','60-170','24 hours','', 'active',NOW(),NOW()),('Ferritin','FERR','Immunology','Serum','Plain',0,650,'ng/mL','As reported','48 hours','', 'active',NOW(),NOW()),('Uric Acid','UA','Biochemistry','Serum','Plain',0,180,'mg/dL','3.5-7.2','24 hours','', 'active',NOW(),NOW()),
('Urine Routine Examination','URINE','Clinical Pathology','Urine','Sterile container',0,150,'','As reported','24 hours','Fresh sample', 'active',NOW(),NOW()),('Urine Culture Sensitivity','UCS','Microbiology','Urine','Sterile container',0,700,'','As reported','72 hours','Midstream sample', 'active',NOW(),NOW()),('Stool Routine Examination','STOOL','Clinical Pathology','Stool','Sterile container',0,180,'','As reported','24 hours','', 'active',NOW(),NOW()),('Stool Culture','SC','Microbiology','Stool','Sterile container',0,750,'','As reported','72 hours','', 'active',NOW(),NOW()),('Dengue NS1 Antigen','DNS1','Serology','Serum','Plain',0,650,'','Negative','24 hours','', 'active',NOW(),NOW()),
('Dengue IgM','DIGM','Serology','Serum','Plain',0,650,'','Negative','24 hours','', 'active',NOW(),NOW()),('Malaria Parasite','MP','Microbiology','Blood','EDTA',0,250,'','Negative','24 hours','', 'active',NOW(),NOW()),('Widal Test','WIDAL','Serology','Serum','Plain',0,250,'','As reported','24 hours','', 'active',NOW(),NOW()),('CRP Quantitative','CRP','Immunology','Serum','Plain',0,450,'mg/L','Below 6','24 hours','', 'active',NOW(),NOW()),('Rheumatoid Factor','RF','Immunology','Serum','Plain',0,400,'IU/mL','Below 14','24 hours','', 'active',NOW(),NOW()),
('HIV I & II','HIV','Serology','Serum','Plain',0,500,'','Non-reactive','24 hours','Consent required', 'active',NOW(),NOW()),('HBsAg','HBSAG','Serology','Serum','Plain',0,350,'','Non-reactive','24 hours','', 'active',NOW(),NOW()),('HCV Antibody','HCV','Serology','Serum','Plain',0,500,'','Non-reactive','24 hours','', 'active',NOW(),NOW()),('VDRL','VDRL','Serology','Serum','Plain',0,250,'','Non-reactive','24 hours','', 'active',NOW(),NOW()),('Pregnancy Test','UPT','Clinical Pathology','Urine','Sterile container',0,150,'','Negative','24 hours','', 'active',NOW(),NOW()),
('Beta hCG','BHCG','Hormones','Serum','Plain',0,700,'mIU/mL','As reported','24 hours','', 'active',NOW(),NOW()),('Troponin I','TNI','Cardiac','Serum','Plain',0,900,'ng/mL','As reported','24 hours','', 'active',NOW(),NOW()),('D-Dimer','DDIMER','Hematology','Plasma','Citrate',0,900,'ng/mL','Below 500','24 hours','', 'active',NOW(),NOW()),('Amylase','AMY','Biochemistry','Serum','Plain',0,300,'U/L','30-110','24 hours','', 'active',NOW(),NOW()),('Lipase','LIP','Biochemistry','Serum','Plain',0,450,'U/L','13-60','24 hours','', 'active',NOW(),NOW()),
('Electrolytes','ELEC','Biochemistry','Serum','Plain',0,500,'mmol/L','As reported','24 hours','', 'active',NOW(),NOW()),('Sodium','NA','Biochemistry','Serum','Plain',0,180,'mmol/L','135-145','24 hours','', 'active',NOW(),NOW()),('Potassium','K','Biochemistry','Serum','Plain',0,180,'mmol/L','3.5-5.1','24 hours','', 'active',NOW(),NOW()),('Chloride','CL','Biochemistry','Serum','Plain',0,180,'mmol/L','98-107','24 hours','', 'active',NOW(),NOW()),('Blood Urea Nitrogen','BUN','Biochemistry','Serum','Plain',0,200,'mg/dL','7-20','24 hours','', 'active',NOW(),NOW()),
('Creatinine','CREAT','Biochemistry','Serum','Plain',0,180,'mg/dL','0.6-1.3','24 hours','', 'active',NOW(),NOW()),('Bilirubin Total','BILT','Biochemistry','Serum','Plain',0,180,'mg/dL','0.3-1.2','24 hours','', 'active',NOW(),NOW()),('SGPT ALT','ALT','Biochemistry','Serum','Plain',0,180,'U/L','7-56','24 hours','', 'active',NOW(),NOW()),('SGOT AST','AST','Biochemistry','Serum','Plain',0,180,'U/L','10-40','24 hours','', 'active',NOW(),NOW()),('Alkaline Phosphatase','ALP','Biochemistry','Serum','Plain',0,180,'U/L','44-147','24 hours','', 'active',NOW(),NOW()),
('Pap Smear','PAP','Cytology','Other','Slide',0,800,'','As reported','5 days','', 'active',NOW(),NOW()),('FNAC','FNAC','Cytology','Other','Slide',0,1200,'','As reported','5 days','', 'active',NOW(),NOW()),('Biopsy Histopathology','HPE','Histopathology','Other','Formalin container',0,1800,'','As reported','7 days','', 'active',NOW(),NOW()),('Sputum AFB','AFB','Microbiology','Sputum','Sterile container',0,350,'','Negative','48 hours','Early morning sample', 'active',NOW(),NOW()),('COVID-19 RT PCR','RTPCR','Microbiology','Swab','VTM',0,900,'','Negative','24 hours','', 'active',NOW(),NOW());

-- 55 standard pharmacy items. Existing category 4 = tablets, 5 = capsules, 6 = topical, 3 = liquids.
INSERT INTO `medicine` (`name`,`medicine_category_id`,`description`,`price`,`manufacturing_company`,`status`) VALUES
('Paracetamol 500mg',4,'Pain and fever relief','2','Generic','100'),('Ibuprofen 400mg',4,'Anti-inflammatory pain relief','4','Generic','100'),('Aspirin 75mg',4,'Antiplatelet medicine','2','Generic','100'),('Cetirizine 10mg',4,'Anti-allergy tablet','3','Generic','100'),('Levocetirizine 5mg',4,'Anti-allergy tablet','4','Generic','100'),('Pantoprazole 40mg',4,'Acidity control','6','Generic','100'),('Omeprazole 20mg',5,'Acidity control','5','Generic','100'),('Ondansetron 4mg',4,'Nausea control','8','Generic','100'),('Metformin 500mg',4,'Diabetes medicine','3','Generic','100'),('Glimepiride 1mg',4,'Diabetes medicine','4','Generic','100'),
('Amlodipine 5mg',4,'Blood pressure medicine','3','Generic','100'),('Telmisartan 40mg',4,'Blood pressure medicine','8','Generic','100'),('Losartan 50mg',4,'Blood pressure medicine','5','Generic','100'),('Atorvastatin 10mg',4,'Cholesterol medicine','6','Generic','100'),('Rosuvastatin 10mg',4,'Cholesterol medicine','10','Generic','100'),('Azithromycin 500mg',4,'Antibiotic','20','Generic','100'),('Amoxicillin 500mg',5,'Antibiotic','12','Generic','100'),('Cefixime 200mg',4,'Antibiotic','18','Generic','100'),('Doxycycline 100mg',5,'Antibiotic','9','Generic','100'),('Ciprofloxacin 500mg',4,'Antibiotic','8','Generic','100'),
('ORS Sachet',3,'Oral rehydration salts','20','Generic','100'),('Zinc 20mg',4,'Supplement','4','Generic','100'),('Folic Acid 5mg',4,'Supplement','2','Generic','100'),('Iron Folic Acid',4,'Iron supplement','3','Generic','100'),('Calcium Vitamin D3',4,'Bone supplement','6','Generic','100'),('Vitamin C 500mg',4,'Vitamin supplement','3','Generic','100'),('Vitamin B Complex',4,'Vitamin supplement','4','Generic','100'),('Montelukast 10mg',4,'Allergy/asthma control','10','Generic','100'),('Salbutamol Inhaler',8,'Bronchodilator inhaler','150','Generic','100'),('Budesonide Inhaler',8,'Steroid inhaler','220','Generic','100'),
('Dextromethorphan Syrup',3,'Dry cough syrup','80','Generic','100'),('Ambroxol Syrup',3,'Wet cough syrup','75','Generic','100'),('Lactulose Syrup',3,'Constipation relief','110','Generic','100'),('Antacid Suspension',3,'Acidity relief','65','Generic','100'),('Diclofenac Gel',6,'Topical pain relief','90','Generic','100'),('Clotrimazole Cream',6,'Antifungal cream','70','Generic','100'),('Mupirocin Ointment',6,'Antibiotic ointment','110','Generic','100'),('Povidone Iodine Solution',6,'Antiseptic solution','60','Generic','100'),('Insulin Regular',9,'Diabetes injection','180','Generic','100'),('Insulin Glargine',9,'Long acting insulin','650','Generic','100'),
('Ceftriaxone 1g Injection',9,'Antibiotic injection','55','Generic','100'),('Pantoprazole Injection',9,'Acidity injection','35','Generic','100'),('Ondansetron Injection',9,'Antiemetic injection','25','Generic','100'),('Tramadol Injection',9,'Pain injection','30','Generic','100'),('Hydrocortisone Injection',9,'Steroid injection','45','Generic','100'),('Furosemide 40mg',4,'Diuretic','3','Generic','100'),('Spironolactone 25mg',4,'Diuretic','4','Generic','100'),('Clopidogrel 75mg',4,'Antiplatelet','8','Generic','100'),('Nitroglycerin 0.5mg',4,'Angina relief','5','Generic','100'),('Levetiracetam 500mg',4,'Anti-seizure medicine','20','Generic','100'),
('Phenytoin 100mg',4,'Anti-seizure medicine','5','Generic','100'),('Sertraline 50mg',4,'Antidepressant','10','Generic','100'),('Alprazolam 0.25mg',4,'Anxiety medicine','4','Generic','100'),('Tamsulosin 0.4mg',4,'Urinary symptoms','12','Generic','100'),('Tranexamic Acid 500mg',4,'Controls bleeding','8','Generic','100');