-- =====================================================================
-- 2026_sprint2_doctor_queue_nursing.sql — اسپرینت ۲
-- میز پزشک/دارو + صف «کانتر پرویزی» + درصد پرستار + مصرفیِ پروسیجر
-- افزایشی، اجرای مجدد امن. Rollback در پایین. دیتابیس: shadicli_nurse
-- =====================================================================
SET NAMES utf8mb4;

-- ۱) درصدِ پیش‌فرضِ هر پرستار به‌ازای هر پروسیجر
CREATE TABLE IF NOT EXISTS `cw_procedure_nurse_share` (
  `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `procedure_id` INT UNSIGNED NOT NULL,
  `nurse_id`     INT UNSIGNED NOT NULL,
  `default_pct`  DECIMAL(5,2) NOT NULL DEFAULT 0,
  `created_at`   DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_proc_nurse` (`procedure_id`,`nurse_id`),
  KEY `idx_proc` (`procedure_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ۲) مصرفیِ هر پروسیجر (BOM چندقلمی per پروسیجر)
CREATE TABLE IF NOT EXISTS `cw_procedure_bom` (
  `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `procedure_id` INT UNSIGNED NOT NULL,
  `item_id`      INT UNSIGNED NOT NULL,
  `default_qty`  DECIMAL(10,2) NOT NULL DEFAULT 1,
  `warehouse_id` INT UNSIGNED DEFAULT NULL,
  `is_required`  TINYINT NOT NULL DEFAULT 1,
  `created_at`   DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_proc_item` (`procedure_id`,`item_id`),
  KEY `idx_proc` (`procedure_id`),
  KEY `idx_item` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ۳) ستون درصدِ واقعیِ هر پرستار در هر فرایند (افزایشی، با چک وجود)
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='record_nurses' AND COLUMN_NAME='share_pct');
SET @s := IF(@c=0, 'ALTER TABLE `record_nurses` ADD COLUMN `share_pct` DECIMAL(5,2) NULL DEFAULT NULL', 'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- ۴) صفِ «کانتر پرویزی» روی cw_visits: شماره نوبت + زمان‌های صدا/ورود
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_visits' AND COLUMN_NAME='queue_number');
SET @s := IF(@c=0, 'ALTER TABLE `cw_visits` ADD COLUMN `queue_number` INT NULL DEFAULT NULL', 'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_visits' AND COLUMN_NAME='t_called');
SET @s := IF(@c=0, 'ALTER TABLE `cw_visits` ADD COLUMN `t_called` DATETIME NULL DEFAULT NULL', 'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_visits' AND COLUMN_NAME='t_in_room');
SET @s := IF(@c=0, 'ALTER TABLE `cw_visits` ADD COLUMN `t_in_room` DATETIME NULL DEFAULT NULL', 'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- ۵) حذفِ نرمِ پروسیجر (آماده‌سازی برای الحاقیه‌ی ویرایش/تعویض/حذف)
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='record_procedures' AND COLUMN_NAME='deleted_at');
SET @s := IF(@c=0, 'ALTER TABLE `record_procedures` ADD COLUMN `deleted_at` DATETIME NULL DEFAULT NULL', 'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='record_procedures' AND COLUMN_NAME='delete_reason');
SET @s := IF(@c=0, 'ALTER TABLE `record_procedures` ADD COLUMN `delete_reason` VARCHAR(300) NULL DEFAULT NULL', 'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
-- گاردِ «BOM کسر شد» تا دوبار کسر نشود
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='record_procedures' AND COLUMN_NAME='bom_consumed_at');
SET @s := IF(@c=0, 'ALTER TABLE `record_procedures` ADD COLUMN `bom_consumed_at` DATETIME NULL DEFAULT NULL', 'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- ۶) پرچم داروی ترکیبی روی cw_medications (افزایشی)
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_medications' AND COLUMN_NAME='is_compound');
SET @s := IF(@c=0, 'ALTER TABLE `cw_medications` ADD COLUMN `is_compound` TINYINT NOT NULL DEFAULT 0', 'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_medications' AND COLUMN_NAME='compound_json');
SET @s := IF(@c=0, 'ALTER TABLE `cw_medications` ADD COLUMN `compound_json` TEXT NULL DEFAULT NULL', 'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- =====================================================================
-- ROLLBACK (جداگانه اجرا کنید در صورت نیاز):
-- ALTER TABLE `cw_medications` DROP COLUMN `compound_json`, DROP COLUMN `is_compound`;
-- ALTER TABLE `record_procedures` DROP COLUMN `delete_reason`, DROP COLUMN `deleted_at`;
-- ALTER TABLE `cw_visits` DROP COLUMN `t_in_room`, DROP COLUMN `t_called`, DROP COLUMN `queue_number`;
-- ALTER TABLE `record_nurses` DROP COLUMN `share_pct`;
-- DROP TABLE IF EXISTS `cw_procedure_bom`;
-- DROP TABLE IF EXISTS `cw_procedure_nurse_share`;
-- =====================================================================