-- STEP 10 -- Walk-in OPD Prescription link upgrade.
-- Run this ONCE after hmsci.sql, step6_clinical_ipd_upgrade.sql and
-- step9_walkin_opd_upgrade.sql have already been applied (this file only adds to the
-- EXISTING `prescription` table created by hmsci.sql -- it does not create a new
-- prescription table, and no existing prescription row is ever touched or removed).
--
-- What this does, and why:
--
-- prescription.encounter_id: a nullable link from a prescription back to the EXISTING
--    patient_encounter row it was written for (Walk-in OPD, Part 3's consultation flow).
--    Nullable so every existing/legacy prescription row (booked-appointment prescriptions,
--    and every prescription written before this upgrade) gets NULL and is completely
--    unaffected -- "encounter_id IS NULL" simply means "not linked to a Walk-in OPD visit",
--    exactly as it always has been. No FOREIGN KEY is added (this codebase does not use FK
--    constraints elsewhere either), so this can never block/cascade-delete anything.
--
-- prescription.request_token: the SAME double-click / browser-refresh / resubmit guard
--    pattern already used by patient_encounter.request_token (step9_walkin_opd_upgrade.sql),
--    payment.request_token, patient_advances.request_token and refund_requests.request_token
--    -- a nullable, unique column so resubmitting the Walk-in Prescription form (double-click,
--    refresh, back-button + resubmit) can never insert the same prescription twice. Existing
--    rows all get NULL (a UNIQUE key allows any number of NULLs in MySQL), so no existing row
--    is affected either way.
--
-- Both columns are added only if not already present (safe to re-run), and neither column is
-- ever required -- Doctor::manage_prescription()'s normal (non-walk-in) Add/Edit/Delete
-- Prescription flow keeps working exactly as before whether or not this migration has been
-- run (it degrades gracefully via field_exists() checks in the controller).

SET @db := DATABASE();

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

-- 2) Plain (non-unique) index on encounter_id, only if it does not already exist -- a walk-in
--    encounter may reasonably have more than one prescription row over the same visit (e.g.
--    the doctor adds a second medication line later), so this is an index for lookups, not a
--    uniqueness constraint.
SET @q := IF(
  (SELECT COUNT(*) FROM information_schema.STATISTICS
     WHERE TABLE_SCHEMA=@db AND TABLE_NAME='prescription' AND INDEX_NAME='idx_prescription_encounter_id') = 0,
  'ALTER TABLE `prescription` ADD INDEX `idx_prescription_encounter_id` (`encounter_id`)',
  'SELECT 1'
);
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- 3) 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='prescription' AND COLUMN_NAME='request_token') = 0,
  'ALTER TABLE `prescription` 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 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='prescription' AND INDEX_NAME='uq_prescription_request_token') = 0,
  'ALTER TABLE `prescription` ADD UNIQUE KEY `uq_prescription_request_token` (`request_token`)',
  'SELECT 1'
);
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- Rollback plan: this migration only adds two nullable columns (one plain-indexed, one
-- uniquely-keyed) to the existing `prescription` table. To roll back, run:
--   ALTER TABLE `prescription` DROP INDEX `uq_prescription_request_token`, DROP COLUMN `request_token`;
--   ALTER TABLE `prescription` DROP INDEX `idx_prescription_encounter_id`, DROP COLUMN `encounter_id`;
-- No other column, row, or table is ever affected by this migration or its rollback.