-- ============================================================================
--  2026_sprint30i_phase4_completion.sql — فاز ۴ (تکمیلِ جامع)
--  ADDITIVE / idempotent / بدونِ DELIMITER / سازگار با cPanel / بدونِ حذفِ مخرب.
--  گزارشِ تکراری‌ها پیش از هر UNIQUE، و remediationِ متنِ اختراعیِ 30h.
-- ============================================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- helper macro pattern: ADD COLUMN اگر نبود
-- ─────────────────────────── cw_services: منبعِ الزامِ رضایت و aftercare ───────────────────────────
SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_services' AND COLUMN_NAME='requires_consent');
SET @s:=IF(@c=0,"ALTER TABLE `cw_services` ADD COLUMN `requires_consent` TINYINT(1) 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_services' AND COLUMN_NAME='consent_template_key');
SET @s:=IF(@c=0,"ALTER TABLE `cw_services` ADD COLUMN `consent_template_key` VARCHAR(40) 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_services' AND COLUMN_NAME='requires_aftercare');
SET @s:=IF(@c=0,"ALTER TABLE `cw_services` ADD COLUMN `requires_aftercare` TINYINT(1) 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_services' AND COLUMN_NAME='aftercare_template_key');
SET @s:=IF(@c=0,"ALTER TABLE `cw_services` ADD COLUMN `aftercare_template_key` VARCHAR(40) 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_services' AND COLUMN_NAME='aftercare_url');
SET @s:=IF(@c=0,"ALTER TABLE `cw_services` ADD COLUMN `aftercare_url` 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_services' AND COLUMN_NAME='aftercare_delay_min');
SET @s:=IF(@c=0,"ALTER TABLE `cw_services` ADD COLUMN `aftercare_delay_min` INT NOT NULL DEFAULT 0",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- ─────────────────────────── cw_performed_services: snapshotِ الزام، رضایت، zones، device، aftercare ───────────────────────────
SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_performed_services' AND COLUMN_NAME='consent_required');
SET @s:=IF(@c=0,"ALTER TABLE `cw_performed_services` ADD COLUMN `consent_required` TINYINT(1) 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_performed_services' AND COLUMN_NAME='consent_id');
SET @s:=IF(@c=0,"ALTER TABLE `cw_performed_services` ADD COLUMN `consent_id` BIGINT 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_performed_services' AND COLUMN_NAME='zones_json');
SET @s:=IF(@c=0,"ALTER TABLE `cw_performed_services` ADD COLUMN `zones_json` TEXT 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_performed_services' AND COLUMN_NAME='device_id');
SET @s:=IF(@c=0,"ALTER TABLE `cw_performed_services` ADD COLUMN `device_id` 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_performed_services' AND COLUMN_NAME='aftercare_status');
SET @s:=IF(@c=0,"ALTER TABLE `cw_performed_services` ADD COLUMN `aftercare_status` VARCHAR(16) DEFAULT NULL",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- ─────────────────────────── cw_consents: اتصالِ per-service + نسخهٔ قالب + وضعیت‌های دقیق ───────────────────────────
SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_consents' AND COLUMN_NAME='performed_service_id');
SET @s:=IF(@c=0,"ALTER TABLE `cw_consents` ADD COLUMN `performed_service_id` BIGINT UNSIGNED DEFAULT NULL",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
SET @c:=(SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_consents' AND INDEX_NAME='idx_perf');
SET @s:=IF(@c=0,"ALTER TABLE `cw_consents` ADD KEY `idx_perf` (`performed_service_id`)",'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_consents' AND COLUMN_NAME='template_version');
SET @s:=IF(@c=0,"ALTER TABLE `cw_consents` ADD COLUMN `template_version` INT 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_consents' AND COLUMN_NAME='ack');
SET @s:=IF(@c=0,"ALTER TABLE `cw_consents` ADD COLUMN `ack` TINYINT(1) 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_consents' AND COLUMN_NAME='signature_data');
SET @s:=IF(@c=0,"ALTER TABLE `cw_consents` ADD COLUMN `signature_data` MEDIUMTEXT 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_consents' AND COLUMN_NAME='witness_by');
SET @s:=IF(@c=0,"ALTER TABLE `cw_consents` ADD COLUMN `witness_by` INT UNSIGNED DEFAULT NULL",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
-- گسترشِ ENUMِ status به وضعیت‌های دقیق (required|presented|signed|refused|revoked|override_waived)
SET @s:="ALTER TABLE `cw_consents` MODIFY COLUMN `status` ENUM('required','presented','signed','refused','revoked','override_waived','waived') NOT NULL DEFAULT 'required'";
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- ─────────────────────────── cw_consent_templates: نسخه‌بندی و تأیید ───────────────────────────
SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_consent_templates' AND COLUMN_NAME='version');
SET @s:=IF(@c=0,"ALTER TABLE `cw_consent_templates` ADD COLUMN `version` INT 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_consent_templates' AND COLUMN_NAME='approved_by');
SET @s:=IF(@c=0,"ALTER TABLE `cw_consent_templates` ADD COLUMN `approved_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_consent_templates' AND COLUMN_NAME='approved_at');
SET @s:=IF(@c=0,"ALTER TABLE `cw_consent_templates` ADD COLUMN `approved_at` DATETIME 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_consent_templates' AND COLUMN_NAME='updated_at');
SET @s:=IF(@c=0,"ALTER TABLE `cw_consent_templates` ADD COLUMN `updated_at` DATETIME DEFAULT NULL",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- تاریخچهٔ نسخه‌های قالب (append-only)
CREATE TABLE IF NOT EXISTS `cw_consent_template_history` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `template_key` VARCHAR(40) NOT NULL, `version` INT NOT NULL,
  `title` VARCHAR(180) DEFAULT NULL, `body` MEDIUMTEXT DEFAULT NULL,
  `action` VARCHAR(20) NOT NULL, `by_user` INT UNSIGNED DEFAULT NULL, `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`), KEY `idx_key` (`template_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- تاریخچهٔ اقداماتِ رضایت (append-only): present/sign/refuse/revoke/override
CREATE TABLE IF NOT EXISTS `cw_consent_history` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `consent_id` BIGINT UNSIGNED NOT NULL, `action` VARCHAR(20) NOT NULL,
  `from_status` VARCHAR(20) DEFAULT NULL, `to_status` VARCHAR(20) DEFAULT NULL,
  `detail` VARCHAR(255) DEFAULT NULL, `by_user` INT UNSIGNED DEFAULT NULL, `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`), KEY `idx_consent` (`consent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────── Aftercare: تحویل per-performed-service ───────────────────────────
CREATE TABLE IF NOT EXISTS `cw_aftercare_deliveries` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `performed_service_id` BIGINT UNSIGNED DEFAULT NULL,
  `legacy_rp_id` INT UNSIGNED DEFAULT NULL,          -- نگاشتِ امنِ رکوردِ قدیمی
  `patient_id` INT UNSIGNED NOT NULL, `visit_id` INT UNSIGNED DEFAULT NULL,
  `service_id` INT UNSIGNED DEFAULT NULL, `stage` VARCHAR(20) NOT NULL DEFAULT 'primary',
  `template_key` VARCHAR(40) DEFAULT NULL, `link_url` VARCHAR(255) DEFAULT NULL,
  `status` ENUM('pending','queued','sent','failed','delivered','manual') NOT NULL DEFAULT 'pending',
  `retry_count` INT NOT NULL DEFAULT 0, `last_error` VARCHAR(255) DEFAULT NULL,
  `sent_at` DATETIME DEFAULT NULL, `source` VARCHAR(20) DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL, `created_at` DATETIME NOT NULL, `updated_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`), KEY `idx_perf` (`performed_service_id`), KEY `idx_patient` (`patient_id`), KEY `idx_status` (`status`),
  UNIQUE KEY `uniq_perf_stage` (`performed_service_id`,`stage`)   -- ضدِ ارسالِ تکراری per-service/stage
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────── رضایت‌سنجی (satisfaction eligibility) ───────────────────────────
CREATE TABLE IF NOT EXISTS `cw_satisfaction_eligibility` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `performed_service_id` BIGINT UNSIGNED DEFAULT NULL, `patient_id` INT UNSIGNED NOT NULL,
  `visit_id` INT UNSIGNED DEFAULT NULL, `eligible_at` DATETIME DEFAULT NULL,
  `status` VARCHAR(16) NOT NULL DEFAULT 'eligible', `result` VARCHAR(30) DEFAULT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`), KEY `idx_patient` (`patient_id`),
  UNIQUE KEY `uniq_perf` (`performed_service_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────── ادغامِ بیمار: ممیزی + آرشیو ───────────────────────────
CREATE TABLE IF NOT EXISTS `cw_patient_merges` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `survivor_id` INT UNSIGNED NOT NULL, `duplicate_id` INT UNSIGNED NOT NULL,
  `reason` VARCHAR(255) NOT NULL, `remap` MEDIUMTEXT DEFAULT NULL,   -- JSON: جدول→تعداد ردیفِ بازمَپ‌شده
  `snapshot` MEDIUMTEXT DEFAULT NULL,                                 -- JSON: کپیِ رکوردِ تکراری پیش از آرشیو
  `by_user` INT UNSIGNED DEFAULT NULL, `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`), KEY `idx_survivor` (`survivor_id`), KEY `idx_dup` (`duplicate_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='cw_patients' AND COLUMN_NAME='merged_into');
SET @s:=IF(@c=0,"ALTER TABLE `cw_patients` ADD COLUMN `merged_into` 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_patients' AND COLUMN_NAME='archived_at');
SET @s:=IF(@c=0,"ALTER TABLE `cw_patients` ADD COLUMN `archived_at` DATETIME DEFAULT NULL",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- ─────────────────────────── zones روی plan items (منبعِ حقیقتِ ساختاریافته) ───────────────────────────
SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_consult_plan_items' AND COLUMN_NAME='zones_json');
SET @s:=IF(@c=0,"ALTER TABLE `cw_consult_plan_items` ADD COLUMN `zones_json` TEXT 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_consult_plan_items' AND COLUMN_NAME='requires_consent');
SET @s:=IF(@c=0,"ALTER TABLE `cw_consult_plan_items` ADD COLUMN `requires_consent` TINYINT(1) DEFAULT NULL",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- ─────────────────────────── remediation: متنِ اختراعیِ 30h ───────────────────────────
-- فقط اگر قالبِ 'general' دقیقاً همان متنِ سیستمی و ویرایش‌نشده باشد، غیرفعال و متن پاک می‌شود (محتوای ویرایش‌شدهٔ کلینیک دست‌نخورده می‌ماند).
UPDATE `cw_consent_templates`
   SET `body`=NULL, `is_active`=0, `updated_at`=NOW()
 WHERE `template_key`='general'
   AND `body`='اینجانب با آگاهی از روند، فواید و عوارضِ احتمالیِ خدمتِ درخواستی، رضایتِ خود را برای انجامِ آن اعلام می‌کنم.';

SET FOREIGN_KEY_CHECKS = 1;
-- ROLLBACK (دستی): جدول‌های جدید را DROP و ستون‌های افزوده را DROP COLUMN کنید (هیچ حذفِ داده‌ای خودکار نیست).