-- =====================================================================
-- MINI 3 -- Lab/Investigation Order: link to existing OPD/IPD Encounter
-- =====================================================================
-- Safe to run any number of times: only ADDS a column if it is genuinely
-- missing (checked via information_schema first). Never drops, truncates,
-- or deletes anything. Does not touch patient_encounter, lab_orders'
-- existing columns, or any other table.
--
-- WHY: The Mini 3 workflow is Patient -> existing OPD/IPD Encounter (table
-- `patient_encounter`, already built in step6_clinical_ipd_upgrade.sql) ->
-- Lab/Investigation Order. `lab_orders` currently has no way to record
-- which encounter/visit a lab order belongs to, so this adds ONE nullable
-- reference column -- nothing else. NULL is allowed on purpose: some lab
-- orders (e.g. a walk-in test with no OPD/IPD visit) legitimately have no
-- encounter, exactly the same way `lab_orders.doctor_id` is already
-- optional for walk-in referrals.
-- =====================================================================

SET @db := DATABASE();

SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'lab_orders' AND COLUMN_NAME = 'encounter_id');
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `lab_orders` ADD COLUMN `encounter_id` INT(11) DEFAULT NULL AFTER `patient_id`', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'lab_orders' AND INDEX_NAME = 'ix_lab_orders_encounter');
SET @sql := IF(@idx_exists = 0, 'ALTER TABLE `lab_orders` ADD KEY `ix_lab_orders_encounter` (`encounter_id`)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- README:
-- * No foreign key is added, matching how lab_orders.patient_id / doctor_id
--   are also plain indexed integers today (no FKs anywhere in this schema) --
--   consistent with the existing style, not a new pattern.
-- * lab_orders.status keeps its existing values (Ordered/Processing/
--   Completed/Cancelled) -- see Mini 3 report for why a new 'Billed' status
--   was deliberately NOT added here (it would collide with the sample-
--   collection workflow that already transitions Ordered -> Processing).