-- STEP 3 PART 4 -- OPD Check-in upgrade.
-- Run this ONCE after hmsci.sql and hospital_master_setup_upgrade.sql have already been applied
-- (and after whatever change previously introduced the 'Confirmed' appointment status used by
-- Step 3 Part 2/3 -- that change is not present as a script in this project, so this file does
-- not assume anything about it beyond "appointment.status already holds text values").
--
-- This file only ADDS what OPD check-in genuinely needs. Nothing existing is removed, renamed,
-- or overwritten, and no appointment row is touched. All changes are compatibility-safe
-- (only run if not already applied), so it is safe to re-run this file.
--
-- What this does, and why:
--
-- 1) appointment.status: in hmsci.sql this column is a restrictive
--        ENUM('Scheduled','Completed','Cancelled','No-show')
--    which does not even list 'Confirmed' (already used by the existing receptionist/doctor
--    code), let alone the new 'Checked-in' value this part introduces. Rather than editing the
--    ENUM list again for every future status (Waiting, In Consultation, Completed, ... still to
--    come), this widens the column ONCE to a plain VARCHAR(20). All existing values are valid
--    strings and are preserved exactly as-is by this MODIFY -- no data is changed or lost.
--    If the column has already been widened (by whoever added 'Confirmed'), this is a no-op.
--
-- 2) appointment.checkin_timestamp: no existing column can safely double as "the moment the
--    patient was checked in" -- appointment_timestamp is the BOOKED slot time, not the arrival
--    time, and overwriting it would destroy the original booking time. This adds one small,
--    nullable column for it. Existing rows get NULL (meaning "not checked in"), so no existing
--    appointment record is affected. Only appointments that go through the new check-in action
--    will ever have a value here.

SET @db := DATABASE();

-- 1) Widen appointment.status to VARCHAR if it is still the restrictive ENUM from hmsci.sql.
SET @q := IF(
  (SELECT DATA_TYPE FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA=@db AND TABLE_NAME='appointment' AND COLUMN_NAME='status') = 'enum',
  'ALTER TABLE `appointment` MODIFY COLUMN `status` VARCHAR(20) NOT NULL DEFAULT ''Scheduled''',
  'SELECT 1'
);
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- 2) Add the check-in timestamp 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='checkin_timestamp') = 0,
  'ALTER TABLE `appointment` ADD COLUMN `checkin_timestamp` INT(11) DEFAULT NULL AFTER `status`',
  'SELECT 1'
);
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;