-- STEP 11 -- Walk-in OPD optional Lab Test / Procedure order upgrade.
-- Run this ONCE after hmsci.sql, step6_clinical_ipd_upgrade.sql,
-- mini3_lab_order_encounter_upgrade.sql and step10_walkin_prescription_link_upgrade.sql
-- have already been applied.
--
-- This file does NOT create any new table. It only adds to the EXISTING
-- `lab_orders` and `clinical_service_performed` tables -- no existing row in
-- either table is ever touched, updated, or removed. Safe to re-run any
-- number of times (every statement checks information_schema first).
--
-- Everything a Walk-in doctor's optional Lab Test / Procedure order actually
-- needs already exists on disk before this file runs:
--   * lab_orders.encounter_id                (added by mini3_lab_order_encounter_upgrade.sql)
--   * clinical_service_performed.encounter_id,
--     clinical_service_performed.charge_source_type,
--     clinical_service_performed.charge_source_id,
--     clinical_service_performed.status      (all added by step6_clinical_ipd_upgrade.sql)
-- so this migration is deliberately small: it only adds the ONE thing that
-- is genuinely missing -- a double-click / browser-refresh / resubmit guard,
-- the exact same request_token pattern already used by
-- patient_encounter.request_token, prescription.request_token,
-- payment.request_token, patient_advances.request_token and
-- refund_requests.request_token.
--
-- What this does, and why:
--
-- lab_orders.request_token: nullable, unique. Doctor::walkin_order()'s
--    "create_lab" branch (Walk-in OPD only) carries this in a hidden field so
--    a resubmit of the same already-submitted Order Lab Test form can never
--    insert the same lab_orders row twice. Existing rows all get NULL (a
--    UNIQUE key allows any number of NULLs in MySQL), so no existing lab
--    order -- created via Laboratorist::manage_lab_order() or Admin, with or
--    without an encounter_id -- is affected either way, and that existing
--    creation path keeps working exactly as before whether or not this
--    migration has been run (it degrades gracefully via field_exists()
--    checks in the controller).
--
-- clinical_service_performed.request_token: the same guard, for
--    Doctor::walkin_order()'s "create_procedure" branch (Walk-in OPD only).
--    Existing rows (recorded via Clinical::console('services')) all get
--    NULL and are completely unaffected; that existing path never posts a
--    request_token and keeps inserting exactly as before.
--
-- Neither column is ever required for the existing, non-walk-in use of
-- either table.

SET @db := DATABASE();

-- 1) lab_orders.request_token -- add only if not already present.
SET @q := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA=@db AND TABLE_NAME='lab_orders' AND COLUMN_NAME='request_token') = 0,
  'ALTER TABLE `lab_orders` ADD COLUMN `request_token` VARCHAR(64) DEFAULT NULL AFTER `encounter_id`',
  'SELECT 1'
);
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- 2) Unique index on lab_orders.request_token -- add only if not already present.
SET @q := IF(
  (SELECT COUNT(*) FROM information_schema.STATISTICS
     WHERE TABLE_SCHEMA=@db AND TABLE_NAME='lab_orders' AND INDEX_NAME='uq_lab_orders_request_token') = 0,
  'ALTER TABLE `lab_orders` ADD UNIQUE KEY `uq_lab_orders_request_token` (`request_token`)',
  'SELECT 1'
);
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- 3) clinical_service_performed.request_token -- add only if not already present.
--    (No-op if the clinical_service_performed table itself does not exist yet on this
--    install -- Doctor::walkin_order() already checks table_exists() before ever
--    reading/writing it, exactly like the rest of the Walk-in OPD/Clinical additions do.)
SET @q := IF(
  (SELECT COUNT(*) FROM information_schema.TABLES
     WHERE TABLE_SCHEMA=@db AND TABLE_NAME='clinical_service_performed') = 1
  AND
  (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA=@db AND TABLE_NAME='clinical_service_performed' AND COLUMN_NAME='request_token') = 0,
  'ALTER TABLE `clinical_service_performed` ADD COLUMN `request_token` VARCHAR(64) DEFAULT NULL AFTER `encounter_id`',
  'SELECT 1'
);
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- 4) Unique index on clinical_service_performed.request_token -- add only if not already present.
SET @q := IF(
  (SELECT COUNT(*) FROM information_schema.TABLES
     WHERE TABLE_SCHEMA=@db AND TABLE_NAME='clinical_service_performed') = 1
  AND
  (SELECT COUNT(*) FROM information_schema.STATISTICS
     WHERE TABLE_SCHEMA=@db AND TABLE_NAME='clinical_service_performed' AND INDEX_NAME='uq_csp_request_token') = 0,
  'ALTER TABLE `clinical_service_performed` ADD UNIQUE KEY `uq_csp_request_token` (`request_token`)',
  'SELECT 1'
);
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- Rollback plan: this migration only adds two nullable, uniquely-keyed columns.
-- To roll back, run:
--   ALTER TABLE `clinical_service_performed` DROP INDEX `uq_csp_request_token`, DROP COLUMN `request_token`;
--   ALTER TABLE `lab_orders` DROP INDEX `uq_lab_orders_request_token`, DROP COLUMN `request_token`;
-- No other column, row, or table is ever affected by this migration or its rollback.