-- =====================================================================
-- STEP 2.2 - Patient UHID generation upgrade
-- Safe to run multiple times. Adds nothing that could change or
-- collide with any UHID/Patient ID already assigned.
-- =====================================================================

SET @db := DATABASE();

-- Ensure `uhid` column exists (normally already added by
-- tax_invoice_upgrade.sql; defensive re-check only, no-op if present)
SET @q := IF(
  (SELECT COUNT(*) FROM information_schema.columns
   WHERE table_schema=@db AND table_name='patient' AND column_name='uhid') = 0,
  'ALTER TABLE `patient` ADD COLUMN `uhid` varchar(50) NOT NULL DEFAULT '''' AFTER `patient_code`',
  'SELECT 1'
);
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- Widen uhid to fit longer custom prefixes configured in Hospital
-- Settings (e.g. "HOSPITAL-2026-000001"). Widening is always safe and
-- never touches existing values.
ALTER TABLE `patient` MODIFY COLUMN `uhid` varchar(50) NOT NULL DEFAULT '';

-- Non-unique lookup index for fast prefix/year lookups during
-- generation and for search screens. (Not a UNIQUE key: many existing
-- rows currently share the blank default '' until they are billed or
-- re-saved, so a UNIQUE constraint would fail against current data.
-- Uniqueness for newly generated UHIDs is enforced in the application
-- layer at generation time, the same way `patient_code` already is.)
SET @q := IF(
  (SELECT COUNT(*) FROM information_schema.statistics
   WHERE table_schema=@db AND table_name='patient' AND index_name='uhid_idx') = 0,
  'ALTER TABLE `patient` ADD KEY `uhid_idx` (`uhid`)',
  'SELECT 1'
);
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- Defensive: make sure the `uhid_prefix` Hospital Setting exists so the
-- generator always has a value to read. INSERT IGNORE never overwrites
-- a prefix already configured by the admin (e.g. changed to "PAT").
INSERT IGNORE INTO `settings` (`type`,`description`) VALUES ('uhid_prefix','UHID');