-- Param Hospital TAX INVOICE upgrade.
-- Run this ONCE after hmsci.sql, upgrade_existing_database.sql and reception_billing_ward_master.sql.
-- It only ADDS new columns/settings needed for the GST tax-invoice style bill; nothing existing is removed.
-- All ALTERs below are compatibility-safe: they only run if the column/table does not already exist,
-- so it is safe to re-run this file.

SET @db := DATABASE();

-- ---------- settings: hospital / GST identity used on the invoice header ----------
-- INSERT IGNORE so existing values configured from the admin panel are never overwritten.
INSERT IGNORE INTO `settings` (`type`,`description`) VALUES
('gstin','22ABCDE1234F1Z5'),
('pan','ABCDE1234F'),
('hospital_reg_no','CG/RAI/2024/123456'),
('hospital_website','www.paramhospital.com'),
('upi_id','paramhospital@upi'),
('place_of_supply','Chhattisgarh (22)');

-- ---------- patient: permanent UHID ----------
SET @q := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='patient' AND column_name='uhid')=0,
  'ALTER TABLE patient ADD COLUMN uhid varchar(30) NOT NULL DEFAULT '''' AFTER patient_code', 'SELECT 1');
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- ---------- patient_receipts: tax invoice fields ----------
SET @q := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='patient_receipts' AND column_name='invoice_number')=0,
  'ALTER TABLE patient_receipts ADD COLUMN invoice_number varchar(60) NOT NULL DEFAULT '''' AFTER receipt_number', '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='patient_receipts' AND column_name='reference_no')=0,
  'ALTER TABLE patient_receipts ADD COLUMN reference_no varchar(60) NOT NULL DEFAULT '''' AFTER invoice_number', '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='patient_receipts' AND column_name='sac_code')=0,
  'ALTER TABLE patient_receipts ADD COLUMN sac_code varchar(20) NOT NULL DEFAULT '''999319''' AFTER service_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='patient_receipts' AND column_name='qty')=0,
  'ALTER TABLE patient_receipts ADD COLUMN qty int(11) NOT NULL DEFAULT 1 AFTER sac_code', '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='patient_receipts' AND column_name='unit')=0,
  'ALTER TABLE patient_receipts ADD COLUMN unit varchar(20) NOT NULL DEFAULT '''Nos''' AFTER qty', '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='patient_receipts' AND column_name='discount')=0,
  'ALTER TABLE patient_receipts ADD COLUMN discount decimal(10,2) NOT NULL DEFAULT 0 AFTER amount', '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='patient_receipts' AND column_name='gst_rate')=0,
  'ALTER TABLE patient_receipts ADD COLUMN gst_rate decimal(5,2) NOT NULL DEFAULT 18.00 AFTER discount', '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='patient_receipts' AND column_name='payment_mode')=0,
  'ALTER TABLE patient_receipts ADD COLUMN payment_mode varchar(30) NOT NULL DEFAULT '''Cash''' AFTER gst_rate', '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='patient_receipts' AND column_name='transaction_id')=0,
  'ALTER TABLE patient_receipts ADD COLUMN transaction_id varchar(100) NOT NULL DEFAULT '''' AFTER payment_mode', '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='patient_receipts' AND column_name='bank_upi_app')=0,
  'ALTER TABLE patient_receipts ADD COLUMN bank_upi_app varchar(100) NOT NULL DEFAULT '''' AFTER transaction_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='patient_receipts' AND column_name='opd_ipd')=0,
  'ALTER TABLE patient_receipts ADD COLUMN opd_ipd varchar(10) NOT NULL DEFAULT '''OPD''' AFTER bank_upi_app', '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='patient_receipts' AND column_name='visit_type')=0,
  'ALTER TABLE patient_receipts ADD COLUMN visit_type varchar(30) NOT NULL DEFAULT '''New Visit''' AFTER opd_ipd', '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='patient_receipts' AND column_name='admission_no')=0,
  'ALTER TABLE patient_receipts ADD COLUMN admission_no varchar(50) NOT NULL DEFAULT '''-''' AFTER visit_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='patient_receipts' AND column_name='ward_room_bed')=0,
  'ALTER TABLE patient_receipts ADD COLUMN ward_room_bed varchar(100) NOT NULL DEFAULT '''-''' AFTER admission_no', '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='patient_receipts' AND column_name='insurance_tpa')=0,
  'ALTER TABLE patient_receipts ADD COLUMN insurance_tpa varchar(150) NOT NULL DEFAULT '''-''' AFTER ward_room_bed', '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='patient_receipts' AND column_name='payment_status')=0,
  'ALTER TABLE patient_receipts ADD COLUMN payment_status varchar(20) NOT NULL DEFAULT '''Paid''' AFTER insurance_tpa', 'SELECT 1');
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- Group multiple line items (e.g. Registration Fee + Consultation Fee) issued together into ONE tax invoice.
SET @q := IF((SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=@db AND table_name='patient_receipts' AND index_name='invoice_number_idx')=0,
  'ALTER TABLE patient_receipts ADD KEY invoice_number_idx (invoice_number)', 'SELECT 1');
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;