-- =====================================================================
-- PART 4 -- OPD BILLING (PART 1)
-- =====================================================================
-- Run this AFTER step6_clinical_ipd_upgrade.sql (which creates `patient_encounter`) and after
-- step4_part1_pricing_master_architecture.sql (which creates `doctor_charges`), and after
-- step5_complete_billing_upgrade.sql / accounting_billing_upgrade.sql (invoice/payment/ledger).
--
-- WHAT THIS FILE DOES NOT TOUCH (already correct -- reused, not rebuilt):
--   patient_encounter   -- Step 6. This IS the OPD Visit record for this part (Patient, UHID via
--                          patient join, Doctor, Department, encounter_type='OPD', visit_type,
--                          checkin_time). No second "opd_visit" table is created.
--   doctor_charges       -- Step 4 Part 1. OPD Consultation Charge pricing master. Not touched.
--   invoice / billing_invoice_item          -- Invoice / Invoice Item. Created via the existing
--                                              Financial_service::save_invoice(), not a new table.
--   payment / payment_allocation / patient_receipts / patient_financial_ledger
--                                            -- Payment / Receipt / Financial Ledger, via the
--                                               existing Financial_service::payment().
--
-- WHAT THIS FILE ADDS (genuinely missing):
--   patient_encounter.invoice_id  -- links an OPD Visit to the invoice raised for its
--                                     consultation charge, so the OPD Bill screen can look up
--                                     "which visit is this invoice for" and vice versa.
--
-- Nothing is created, dropped, renamed, reset or deleted. Every change is guarded by an
-- information_schema existence check (same pattern already used by
-- step3_part4_opd_checkin_upgrade.sql and hospital_master_setup_upgrade.sql), so this file is
-- safe to re-run.

SET @db := DATABASE();

-- 1) patient_encounter.invoice_id: nullable link to the OPD consultation invoice.
--    NULL for any pre-existing encounter row (nothing before this part had a linked invoice).
SET @q := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA=@db AND TABLE_NAME='patient_encounter' AND COLUMN_NAME='invoice_id') = 0,
  'ALTER TABLE `patient_encounter` ADD COLUMN `invoice_id` INT(11) DEFAULT NULL AFTER `department_id`',
  'SELECT 1'
);
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- 2) Index for the invoice_id -> encounter lookup the OPD Bill screen performs.
SET @q := IF(
  (SELECT COUNT(*) FROM information_schema.STATISTICS
     WHERE TABLE_SCHEMA=@db AND TABLE_NAME='patient_encounter' AND INDEX_NAME='ix_encounter_invoice') = 0,
  'ALTER TABLE `patient_encounter` ADD KEY `ix_encounter_invoice` (`invoice_id`)',
  'SELECT 1'
);
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;