-- =====================================================================
-- STEP 2.1 - Patient Registration Form upgrade
-- Adds ONLY the columns that do not already exist on `patient`.
-- Safe to run multiple times (checks information_schema before ADD).
-- Run this against the same database used by hmsci.sql.
-- =====================================================================

SET @db := DATABASE();

-- id_proof_type (pairs with existing `aadhar_no` column, reused as the
-- generic "ID Proof Number" field so no duplicate number column is added)
SET @q := IF(
  (SELECT COUNT(*) FROM information_schema.columns
   WHERE table_schema=@db AND table_name='patient' AND column_name='id_proof_type') = 0,
  'ALTER TABLE `patient` ADD COLUMN `id_proof_type` varchar(30) NOT NULL DEFAULT '''' AFTER `aadhar_no`',
  'SELECT 1'
);
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- referral_source
SET @q := IF(
  (SELECT COUNT(*) FROM information_schema.columns
   WHERE table_schema=@db AND table_name='patient' AND column_name='referral_source') = 0,
  'ALTER TABLE `patient` ADD COLUMN `referral_source` varchar(50) NOT NULL DEFAULT '''' AFTER `current_medication`',
  'SELECT 1'
);
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- status (Active / Inactive) - same enum convention already used for
-- receptionist/doctor/nurse/pharmacist/laboratorist status columns
SET @q := IF(
  (SELECT COUNT(*) FROM information_schema.columns
   WHERE table_schema=@db AND table_name='patient' AND column_name='status') = 0,
  'ALTER TABLE `patient` ADD COLUMN `status` enum(''Active'',''Inactive'') NOT NULL DEFAULT ''Active'' AFTER `referral_source`',
  'SELECT 1'
);
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- Backfill status for any pre-existing rows so old patients show as Active
UPDATE `patient` SET `status` = 'Active' WHERE `status` = '' OR `status` IS NULL;