-- STEP 9 -- Walk-in OPD upgrade.
-- Run this ONCE after hmsci.sql and step6_clinical_ipd_upgrade.sql have already been applied
-- (this file only adds to the `patient_encounter` table created by Step 6 -- it does not
-- create that table itself).
--
-- This file only ADDS what Walk-in OPD's duplicate-submission guard genuinely needs. Nothing
-- existing is removed, renamed, or overwritten, and no patient_encounter row is touched. The
-- change is compatibility-safe (only runs if not already applied), so it is safe to re-run.
--
-- What this does, and why:
--
-- patient_encounter.request_token: the SAME double-click / browser-refresh / resubmit guard
--    pattern already used by payment.request_token, patient_advances.request_token and
--    refund_requests.request_token (see Financial_service.php / Financial_controls_service.php)
--    -- a nullable, unique column so that resubmitting the Walk-in OPD "Start OPD Visit" form
--    (double-click, refresh, back-button + resubmit) can never create a second appointment +
--    encounter pair for the same click. Existing/legacy encounter rows all get NULL (a UNIQUE
--    key allows any number of NULLs in MySQL), so no existing row is affected either way.
--
-- No walk-in-specific behaviour requires any other schema change: `patient_encounter` already
-- has encounter_type/visit_type columns (defaulting to 'OPD'/'WALK_IN'), a plain VARCHAR
-- `status` column (so the new 'READY_FOR_CONSULTATION' value needs no ALTER), and a UNIQUE key
-- on appointment_id already preventing a duplicate encounter per appointment. The `appointment`
-- table (status pipeline, token_number, checkin_timestamp) already has everything the walk-in
-- check-in step needs from Step 3 Parts 4-5.

SET @db := DATABASE();

-- 1) Add the request_token column, only if it does not already exist.
SET @q := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA=@db AND TABLE_NAME='patient_encounter' AND COLUMN_NAME='request_token') = 0,
  'ALTER TABLE `patient_encounter` ADD COLUMN `request_token` VARCHAR(64) DEFAULT NULL AFTER `visit_type`',
  'SELECT 1'
);
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

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

-- Rollback plan: this migration only adds one nullable, uniquely-keyed column. To roll back,
-- run: ALTER TABLE `patient_encounter` DROP INDEX `uq_encounter_request_token`, DROP COLUMN
-- `request_token`; -- no data other than this column is ever affected.