-- Hospital Master Setup upgrade (STEP 1).
-- Run this ONCE after hmsci.sql, upgrade_existing_database.sql, reception_billing_ward_master.sql
-- and tax_invoice_upgrade.sql have already been applied.
--
-- This file only ADDS new columns/settings/tables required to make the Hospital Master
-- Setup (General Settings, Department Management, Doctor Management, Doctor Schedule)
-- fully dynamic. Nothing existing is removed, renamed, or overwritten.
-- All ALTERs are compatibility-safe (only run if the column/table does not already exist),
-- so it is safe to re-run this file.

SET @db := DATABASE();

-- ---------- settings: general hospital identity not already covered ----------
-- (hospital_reg_no, gstin, pan, hospital_website already added by tax_invoice_upgrade.sql
-- and system_name/address/phone/system_email already exist in hmsci.sql -- not duplicated here)
-- INSERT IGNORE so existing values configured from the admin panel are never overwritten.
INSERT IGNORE INTO `settings` (`type`,`description`) VALUES
('emergency_contact',''),
('invoice_prefix','INV'),
('patient_prefix','PT'),
('uhid_prefix','UHID');

-- ---------- department: code, head, active/inactive status ----------
SET @q := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='department' AND column_name='code')=0,
  'ALTER TABLE department ADD COLUMN code varchar(50) NOT NULL DEFAULT '''' AFTER name', 'SELECT 1');
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

SET @q := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='department' AND column_name='department_head')=0,
  'ALTER TABLE department ADD COLUMN department_head varchar(150) NOT NULL DEFAULT '''' AFTER description', 'SELECT 1');
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

SET @q := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='department' AND column_name='status')=0,
  'ALTER TABLE department ADD COLUMN status varchar(20) NOT NULL DEFAULT ''active'' AFTER department_head', 'SELECT 1');
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- ---------- doctor: status + joining date (qualification/specialist_in/registration_number/
-- experience_years/consultation_fee/available_days/available_time already added by
-- upgrade_existing_database.sql -- not duplicated here) ----------
SET @q := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='doctor' AND column_name='status')=0,
  'ALTER TABLE doctor ADD COLUMN status varchar(20) NOT NULL DEFAULT ''active''', 'SELECT 1');
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

SET @q := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='doctor' AND column_name='joining_date')=0,
  'ALTER TABLE doctor ADD COLUMN joining_date date DEFAULT NULL', 'SELECT 1');
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- ---------- doctor_schedule: dynamic weekly availability (new table, only created if missing) ----------
CREATE TABLE IF NOT EXISTS `doctor_schedule` (
  `doctor_schedule_id` int(11) NOT NULL AUTO_INCREMENT,
  `doctor_id` int(11) NOT NULL,
  `day` varchar(20) NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `slot_duration` int(11) NOT NULL DEFAULT 15,
  `status` varchar(20) NOT NULL DEFAULT 'active',
  PRIMARY KEY (`doctor_schedule_id`),
  KEY `doctor_id_idx` (`doctor_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;