-- ============================================================================
--  2026_sprint30e_flags_history.sql — فاز ۳ تکمیل: پرچم‌های بیمار بدونِ حذف + تاریخچه
--  ۱۰۰٪ ADDITIVE / idempotent. بدونِ DELIMITER/stored-procedure (سازگار با cPanel).
--  حذف نمی‌کنیم — غیرفعال می‌کنیم و تاریخچه نگه می‌داریم.
-- ============================================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ستون‌های cw_patient_flags (گاردشده)
SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_patient_flags' AND COLUMN_NAME='active');
SET @s:=IF(@c=0,"ALTER TABLE `cw_patient_flags` ADD COLUMN `active` TINYINT(1) NOT NULL DEFAULT 1",'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_patient_flags' AND COLUMN_NAME='note');
SET @s:=IF(@c=0,"ALTER TABLE `cw_patient_flags` ADD COLUMN `note` VARCHAR(255) 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_patient_flags' AND COLUMN_NAME='deactivated_by');
SET @s:=IF(@c=0,"ALTER TABLE `cw_patient_flags` ADD COLUMN `deactivated_by` INT UNSIGNED 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_patient_flags' AND COLUMN_NAME='deactivated_at');
SET @s:=IF(@c=0,"ALTER TABLE `cw_patient_flags` ADD COLUMN `deactivated_at` DATETIME DEFAULT NULL",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- تاریخچهٔ پرچم‌ها (افزودن/غیرفعال/فعال‌سازیِ مجدد) — هرگز بازنویسی نمی‌شود
CREATE TABLE IF NOT EXISTS `cw_patient_flag_log` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` INT UNSIGNED NOT NULL,
  `flag_key`   VARCHAR(40)  NOT NULL,
  `action`     VARCHAR(16)  NOT NULL,
  `note`       VARCHAR(255) DEFAULT NULL,
  `by_user`    INT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_patient` (`patient_id`),
  KEY `idx_flag` (`flag_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
-- ============================================================================
--  ROLLBACK (دستی): DROP TABLE IF EXISTS `cw_patient_flag_log`;
--   ALTER TABLE `cw_patient_flags` DROP COLUMN `active`, DROP COLUMN `note`, DROP COLUMN `deactivated_by`, DROP COLUMN `deactivated_at`;
-- ============================================================================