-- STEP 3 PART 5 -- Token Generation & Doctor Queue upgrade.
-- Run this ONCE after hmsci.sql, hospital_master_setup_upgrade.sql and
-- step3_part4_opd_checkin_upgrade.sql have already been applied (this part builds directly on
-- the `status` and `checkin_timestamp` columns added by the Part 4 script).
--
-- This file only ADDS what token generation / the doctor waiting queue genuinely needs.
-- Nothing existing is removed, renamed, or overwritten, and no appointment row is touched.
-- The change is compatibility-safe (only runs if not already applied), so it is safe to re-run
-- this file.
--
-- What this does, and why:
--
-- appointment.token_number: the per-doctor, per-day, check-in-order token shown to reception,
--    the doctor's waiting queue, and the patient. It is intentionally a plain nullable column on
--    the EXISTING appointment table -- not a new token/queue table -- per this project's rule of
--    reusing the existing appointment table and never creating a parallel appointment/queue
--    module. NULL means "no token assigned yet" (Scheduled/Confirmed/Cancelled/No-show all stay
--    NULL forever; only a Checked-in or later appointment ever gets a value). Existing rows get
--    NULL, so no existing appointment record is affected.

SET @db := DATABASE();

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

-- 2) Composite index to make "highest token for this doctor today" and "this doctor's queue,
--    ordered by token" lookups fast. Non-unique: a hard uniqueness constraint on
--    (doctor_id, appointment_timestamp, token_number) was considered (see project notes / final
--    response for this part) but appointment_timestamp is a per-appointment slot time, not a
--    per-day bucket, so it cannot itself be the day key for a unique constraint, and enforcing
--    uniqueness would risk rejecting legitimate concurrent check-ins outright instead of
--    serializing them. The index below is purely for read performance; it adds no constraint and
--    cannot reject or alter any data, so it is always safe to add.
SET @q := IF(
  (SELECT COUNT(*) FROM information_schema.STATISTICS
     WHERE TABLE_SCHEMA=@db AND TABLE_NAME='appointment' AND INDEX_NAME='idx_doctor_token') = 0,
  'ALTER TABLE `appointment` ADD INDEX `idx_doctor_token` (`doctor_id`, `token_number`)',
  'SELECT 1'
);
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;