-- Hospitals CIS code support
-- Run on nobese database (phpMyAdmin or mysql client).

-- Add ciscode column to hospitals (skip if already exists)
ALTER TABLE `hospitals`
  ADD COLUMN `ciscode` VARCHAR(50) NULL DEFAULT NULL AFTER `hospital_id`;

-- Unique CIS code per hospital
ALTER TABLE `hospitals`
  ADD UNIQUE KEY `uniq_hospitals_ciscode` (`ciscode`);

-- Example: set CIS0009 on first hospital (adjust hospital_id if needed)
UPDATE `hospitals`
SET `ciscode` = 'CIS0009'
WHERE `hospital_id` = 1
  AND (`ciscode` IS NULL OR `ciscode` = '');

-- Optional: ensure user table has ciscode + doctor_id (usually already present)
-- ALTER TABLE `user` ADD COLUMN `ciscode` VARCHAR(50) NULL DEFAULT NULL;
-- ALTER TABLE `user` ADD COLUMN `doctor_id` INT NOT NULL DEFAULT 0;
