-- =====================================================================
-- 2026_sprint3_sms_patient.sql — اسپرینت ۳
-- پنل پیامک SMS.IR (قالب‌محور + صف تأخیری + لاگ) + پرونده‌ی سه‌بخشیِ بیمار
-- افزایشی، اجرای مجدد امن. Rollback در پایین. دیتابیس: shadicli_nurse
-- =====================================================================
SET NAMES utf8mb4;

-- ۱) قالب‌های پیامک: نگاشت کلید → Template ID پنل SMS.IR + فعال/غیرفعال
CREATE TABLE IF NOT EXISTS `cw_sms_templates` (
  `id`                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tkey`                VARCHAR(40)  NOT NULL,
  `title`               VARCHAR(150) NOT NULL,
  `provider_template_id` INT UNSIGNED DEFAULT NULL,
  `params_hint`         VARCHAR(255) DEFAULT NULL,
  `sample_text`         TEXT DEFAULT NULL,
  `is_active`           TINYINT NOT NULL DEFAULT 0,
  `sort_order`          INT NOT NULL DEFAULT 0,
  `updated_at`          DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_tkey` (`tkey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ۲) لاگِ هر ارسال (وضعیت + پاسخِ پنل)
CREATE TABLE IF NOT EXISTS `cw_sms_log` (
  `id`                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tkey`                VARCHAR(40)  DEFAULT NULL,
  `phone`               VARCHAR(20)  DEFAULT NULL,
  `provider_template_id` INT UNSIGNED DEFAULT NULL,
  `params_json`         TEXT DEFAULT NULL,
  `status`              VARCHAR(20)  NOT NULL DEFAULT 'queued',
  `provider_message_id` VARCHAR(60)  DEFAULT NULL,
  `error`               VARCHAR(255) DEFAULT NULL,
  `visit_id`            INT UNSIGNED DEFAULT NULL,
  `patient_id`          INT UNSIGNED DEFAULT NULL,
  `created_at`          DATETIME DEFAULT NULL,
  `sent_at`             DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_tkey` (`tkey`), KEY `idx_status` (`status`),
  KEY `idx_visit` (`visit_id`), KEY `idx_patient` (`patient_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ۳) صفِ ارسالِ تأخیری (نظرسنجی ۲ و ۲۴ ساعت، با کرون)
CREATE TABLE IF NOT EXISTS `cw_sms_queue` (
  `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tkey`         VARCHAR(40)  NOT NULL,
  `phone`        VARCHAR(20)  DEFAULT NULL,
  `params_json`  TEXT DEFAULT NULL,
  `visit_id`     INT UNSIGNED DEFAULT NULL,
  `patient_id`   INT UNSIGNED DEFAULT NULL,
  `run_at`       DATETIME NOT NULL,
  `status`       VARCHAR(20) NOT NULL DEFAULT 'pending',
  `attempts`     INT NOT NULL DEFAULT 0,
  `last_error`   VARCHAR(255) DEFAULT NULL,
  `created_at`   DATETIME DEFAULT NULL,
  `processed_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_due` (`status`,`run_at`),
  KEY `idx_visit` (`visit_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ۴) اسلاگِ صفحه‌ی «مراقبت بعد از درمان» روی هر پروسیجر (برای AFTERCARELINK)
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='procedures' AND COLUMN_NAME='aftercare_slug');
SET @s := IF(@c=0, 'ALTER TABLE `procedures` ADD COLUMN `aftercare_slug` VARCHAR(120) 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_patient_profile' AND COLUMN_NAME='admin_tier');
SET @s := IF(@c=0, 'ALTER TABLE `cw_patient_profile` ADD COLUMN `admin_tier` VARCHAR(20) NOT NULL DEFAULT ''normal''', '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_profile' AND COLUMN_NAME='profile_completed_at');
SET @s := IF(@c=0, 'ALTER TABLE `cw_patient_profile` ADD COLUMN `profile_completed_at` DATETIME NULL DEFAULT NULL', 'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- ۶) Seed قالب‌ها (غیرفعال تا شما Template ID پنل را وارد کنید). درجِ امن (بدون بازنویسی)
DROP TEMPORARY TABLE IF EXISTS `_seed_cw_sms_templates`;
CREATE TEMPORARY TABLE `_seed_cw_sms_templates` LIKE `cw_sms_templates`;
INSERT INTO `_seed_cw_sms_templates` (`tkey`,`title`,`params_hint`,`sample_text`,`is_active`,`sort_order`,`updated_at`) VALUES
('aftercare','مراقبت‌های بعد از درمان','FULLNAME, SERVICENAME, AFTERCARELINK','مراجعه کننده گرامی #FULLNAME#\nمراقبت های بعد از #SERVICENAME# برای دریافت نتیجه بهتر و کاهش عوارض احتمالی بسیار مهم است.\n\nلطفا راهنمای اختصاصی مراقبت های بعد از درمان خود را از طریق لینک زیر مطالعه فرمایید:\n\nshadiclinic.com/#AFTERCARELINK#',0,10,NOW()),
('survey_2h','دعوت به نظرسنجی (۲ ساعت بعد)','FULLNAME, SURVEYLINK','مراجعه کننده گرامی #FULLNAME#\nنظر شما برای ما ارزشمند است. لطفا از طریق لینک زیر در نظرسنجی شرکت فرمایید:\nshadiclinic.com/#SURVEYLINK#',0,20,NOW()),
('survey_24h','دعوت به نظرسنجی (۲۴ ساعت بعد)','FULLNAME, SURVEYLINK','مراجعه کننده گرامی #FULLNAME#\nاگر فرصت نکردید، همچنان خوشحال می‌شویم نظرتان را بدانیم:\nshadiclinic.com/#SURVEYLINK#',0,30,NOW()),
('appt_confirm','اطلاع نوبت به مراجع','FULLNAME, APPTDATE, APPTTIME','مراجعه کننده گرامی #FULLNAME#\nنوبت شما برای تاریخ #APPTDATE# ساعت #APPTTIME# در کلینیک شادی ثبت شد.',0,40,NOW()),
('callcenter_new','اطلاع رزرو سایت به کال‌سنتر','FULLNAME, SERVICENAME, APPTDATE, APPTTIME, PHONE','رزرو جدید از سایت:\n#FULLNAME# - #SERVICENAME#\n#APPTDATE# ساعت #APPTTIME#\nتماس: #PHONE#',0,50,NOW()),
('profile_complete','یادآوری تکمیل پرونده','FULLNAME, PROFILELINK','مراجعه کننده گرامی #FULLNAME#\nلطفا برای تکمیل پرونده‌ی پزشکی خود از طریق لینک زیر اقدام فرمایید:\nshadiclinic.com/#PROFILELINK#',0,60,NOW());
INSERT INTO `cw_sms_templates` (`tkey`,`title`,`params_hint`,`sample_text`,`is_active`,`sort_order`,`updated_at`)
SELECT s.`tkey`,s.`title`,s.`params_hint`,s.`sample_text`,s.`is_active`,s.`sort_order`,s.`updated_at`
FROM `_seed_cw_sms_templates` s
WHERE NOT EXISTS (SELECT 1 FROM `cw_sms_templates` t WHERE t.`tkey`=s.`tkey`);
DROP TEMPORARY TABLE `_seed_cw_sms_templates`;

-- =====================================================================
-- ROLLBACK (جداگانه اجرا کنید در صورت نیاز):
-- ALTER TABLE `cw_patient_profile` DROP COLUMN `profile_completed_at`, DROP COLUMN `admin_tier`;
-- ALTER TABLE `procedures` DROP COLUMN `aftercare_slug`;
-- DROP TABLE IF EXISTS `cw_sms_queue`;
-- DROP TABLE IF EXISTS `cw_sms_log`;
-- DROP TABLE IF EXISTS `cw_sms_templates`;
-- =====================================================================