-- ============================================================================
--  2026_sprint30n_phase4_runtime_pass5.sql — فاز ۴ / پاسِ پنجمِ رفعِ runtime
--  ADDITIVE فقط / idempotent / بدونِ DELIMITER / بدونِ stored procedure /
--  سازگار با cPanel و MariaDB/MySQL / غیرِمخرب.
--  پیش‌نیاز: پس از ...30j → 30k → 30l → 30m اجرا شود. اجرای چندباره بی‌خطر است.
--
--  اصل: هیچ بازتفسیرِ خودکارِ دادهٔ عمدی انجام نمی‌شود؛ ابهام‌ها گزارش می‌شوند.
-- ============================================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ─────────────── (۱) مراقبت: وضعیتِ «نیازمندِ آشتی» ───────────────
-- ستونِ status از نوعِ ENUM است. بدونِ افزودنِ مقدارِ جدید، تلاش برای نوشتنِ
-- 'reconciliation_required' در حالتِ non-strict به رشتهٔ خالی تبدیل می‌شد (نشتِ خاموش).
-- کد با cw_aftercare_status_supports() این را تشخیص می‌دهد و تا اجرای این مهاجرت از
-- مسیرِ پشتیبان (failed + retry_count=9999) استفاده می‌کند؛ ولی وضعیتِ صریح بهتر است.
SET @t:=(SELECT COLUMN_TYPE FROM information_schema.COLUMNS
          WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_aftercare_deliveries' AND COLUMN_NAME='status');
SET @needs:=IF(@t IS NOT NULL AND @t LIKE 'enum(%' AND @t NOT LIKE '%reconciliation_required%',1,0);
SET @s:=IF(@needs=1,
 "ALTER TABLE `cw_aftercare_deliveries` MODIFY `status`
   ENUM('pending','queued','sent','failed','delivered','manual','reconciliation_required')
   NOT NULL DEFAULT 'pending'",
 '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_aftercare_deliveries' AND COLUMN_NAME='provider_result_unknown');
SET @s:=IF(@c=0,"ALTER TABLE `cw_aftercare_deliveries` ADD COLUMN `provider_result_unknown` 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_aftercare_deliveries' AND COLUMN_NAME='reconciliation_required_at');
SET @s:=IF(@c=0,"ALTER TABLE `cw_aftercare_deliveries` ADD COLUMN `reconciliation_required_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_aftercare_deliveries' AND COLUMN_NAME='reconciliation_reason');
SET @s:=IF(@c=0,"ALTER TABLE `cw_aftercare_deliveries` ADD COLUMN `reconciliation_reason` VARCHAR(190) 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_aftercare_deliveries' AND COLUMN_NAME='invalidated_at');
SET @s:=IF(@c=0,"ALTER TABLE `cw_aftercare_deliveries` ADD COLUMN `invalidated_at` DATETIME 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_aftercare_deliveries' AND INDEX_NAME='idx_after_reconcile');
SET @s:=IF(@c=0,"ALTER TABLE `cw_aftercare_deliveries` ADD INDEX `idx_after_reconcile` (`provider_result_unknown`,`status`)",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- ─────────────── (۲) الزامِ عکسِ per-service ───────────────
-- بدونِ این ستون، گیتِ عکس روی plan_item_id بود و دو جلسه از یک آیتمِ پلن
-- الزامِ یکدیگر را برآورده/مسدود می‌کردند.
SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_photo_requirements' AND COLUMN_NAME='performed_service_id');
SET @s:=IF(@c=0,"ALTER TABLE `cw_photo_requirements` 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_photo_requirements' AND INDEX_NAME='idx_photoreq_ps');
SET @s:=IF(@c=0,"ALTER TABLE `cw_photo_requirements` ADD INDEX `idx_photoreq_ps` (`performed_service_id`,`phase`,`status`)",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- عمداً **بدونِ backfill**: نمی‌توان با اطمینان تشخیص داد هر الزامِ قدیمی متعلق به کدام
-- جلسه بوده. رکوردهای قدیمی performed_service_id=NULL می‌مانند و کد مسیرِ legacyِ
-- مستند را برایشان اجرا می‌کند (فقط الزام‌های بدونِ خدمتِ مشخص).

-- ─────────────── (۳) گزارش‌های تشخیصی ───────────────

-- (الف) تحویل‌های نیازمندِ آشتی — صفِ کارِ اپراتور
SELECT 'aftercare_reconciliation_queue' AS report, id, patient_id, performed_service_id, stage,
       status, retry_count, provider_message_id, reconciliation_reason, reconciliation_required_at, last_error
  FROM cw_aftercare_deliveries
 WHERE status='reconciliation_required' OR provider_result_unknown=1
    OR last_error LIKE 'reconciliation_required%';

-- (ب) تحویل‌های queuedِ رهاشده با lease منقضی (کاندیدای بازیابی/آشتی)
SELECT 'aftercare_stale_queued' AS report, id, patient_id, retry_count, attempt_token, claim_expires_at, updated_at
  FROM cw_aftercare_deliveries
 WHERE status='queued' AND claim_expires_at IS NOT NULL AND claim_expires_at < NOW();

-- (ج) جلسه‌های scheduledِ یتیم — بدونِ نوبت یا بدونِ تاریخ (پس از پاس ۵ نباید ساخته شوند)
SELECT 'scheduled_sessions_orphan_pass5' AS report, ps.id AS performed_service_id, ps.patient_id,
       ps.session_no, ps.scheduled_date, ps.appointment_id, COALESCE(a.status,'(بدونِ نوبت)') AS appt_status
  FROM cw_performed_services ps
  LEFT JOIN cw_appointments a ON a.id = ps.appointment_id
 WHERE ps.status='scheduled'
   AND (ps.appointment_id IS NULL OR ps.scheduled_date IS NULL OR a.id IS NULL
        OR a.status IN ('cancelled','rejected','expired','lost'));

-- (د) ناسازگاریِ تاریخِ نوبت و تاریخِ جلسه (پیش از رفعِ همگام‌سازیِ reschedule)
SELECT 'session_date_mismatch' AS report, ps.id AS performed_service_id, ps.scheduled_date,
       a.id AS appointment_id, a.appt_date, a.status AS appt_status
  FROM cw_performed_services ps
  JOIN cw_appointments a ON a.id = ps.appointment_id
 WHERE ps.status='scheduled' AND ps.scheduled_date IS NOT NULL AND a.appt_date IS NOT NULL
   AND ps.scheduled_date <> a.appt_date;

-- (هـ) جلسه‌های scheduled/queuedی که entitlementشان آزاد (released) نیست
--      (قاعده: cw_payments.released=1، یا در نصبِ بدونِ آن ستون status='paid')
SET @hasRel:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_payments' AND COLUMN_NAME='released');
SET @s:=IF(@hasRel=1,
 "SELECT 'sessions_without_released_entitlement' AS report, ps.id AS performed_service_id, ps.patient_id,
         ps.status, ps.series_payment_item_id, py.id AS payment_id, py.status AS payment_status, py.released
    FROM cw_performed_services ps
    JOIN cw_payment_items pi ON pi.id = COALESCE(ps.payment_item_id, ps.series_payment_item_id)
    JOIN cw_payments py ON py.id = pi.payment_id
   WHERE ps.status IN ('scheduled','queued') AND COALESCE(py.released,0) <> 1",
 "SELECT 'sessions_without_released_entitlement' AS report, ps.id AS performed_service_id, ps.patient_id,
         ps.status, ps.series_payment_item_id, py.id AS payment_id, py.status AS payment_status
    FROM cw_performed_services ps
    JOIN cw_payment_items pi ON pi.id = COALESCE(ps.payment_item_id, ps.series_payment_item_id)
    JOIN cw_payments py ON py.id = pi.payment_id
   WHERE ps.status IN ('scheduled','queued') AND COALESCE(py.status,'') <> 'paid'");
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- (و) الزام‌های عکسِ legacy بدونِ خدمتِ مشخص (مسیرِ fallback برایشان اجرا می‌شود)
SELECT 'photo_requirements_legacy_unlinked' AS report, id, patient_id, visit_id, plan_item_id, service_id, phase, status, created_at
  FROM cw_photo_requirements
 WHERE performed_service_id IS NULL AND status='pending';

SET FOREIGN_KEY_CHECKS = 1;
-- پایانِ مهاجرتِ 30n