-- ============================================================================
--  2026_sprint30g_item_link.sql — فاز ۳: پیوندِ واقعیِ آیتمِ فاکتور ↔ خدمتِ انجام‌شده
--  ۱۰۰٪ ADDITIVE / idempotent / بدونِ DELIMITER (سازگار با cPanel).
-- ============================================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- cw_performed_services.payment_item_id (+ ایندکس + یکتاییِ ضدِتکرارِ خدمتِ فعال به‌ازای هر آیتمِ فاکتور)
SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_performed_services' AND COLUMN_NAME='payment_item_id');
SET @s:=IF(@c=0,"ALTER TABLE `cw_performed_services` ADD COLUMN `payment_item_id` INT 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_performed_services' AND INDEX_NAME='idx_payment_item');
SET @s:=IF(@c=0,"ALTER TABLE `cw_performed_services` ADD KEY `idx_payment_item` (`payment_item_id`)",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
-- یکتایی: هر payment_item فقط یک خدمت می‌سازد.
-- اگر مقادیرِ تکراری وجود داشته باشد، مایگریشن به‌جای ادامهٔ خاموش، با پیامِ خوانا شکست می‌خورد.
-- گزارشِ تکراری‌ها (دستی):
--   SELECT payment_item_id, COUNT(*) c FROM cw_performed_services
--    WHERE payment_item_id IS NOT NULL GROUP BY payment_item_id HAVING c>1;
SET @c:=(SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_performed_services' AND INDEX_NAME='uniq_payment_item');
SET @dup:=(SELECT COUNT(*) FROM (SELECT payment_item_id FROM cw_performed_services WHERE payment_item_id IS NOT NULL GROUP BY payment_item_id HAVING COUNT(*)>1) t);
-- اگر تکراری هست و ایندکس هنوز نیست → توقفِ مایگریشن با خطای خوانا (بدونِ حذفِ خودکارِ سابقهٔ پزشکی)
SET @s:=IF(@c=0 AND @dup>0,
    'SELECT `DUPLICATE_payment_item_id_EXISTS__remediate_duplicates_before_running_migration` FROM cw_performed_services LIMIT 1',
    'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
-- در نبودِ تکراری، ایندکسِ یکتا افزوده می‌شود
SET @s:=IF(@c=0 AND @dup=0,"ALTER TABLE `cw_performed_services` ADD UNIQUE KEY `uniq_payment_item` (`payment_item_id`)",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- cw_payment_items.plan_item_id (تا آیتمِ فاکتور لینکِ پلن را حمل کند) + sessions_total اگر نبود
SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_payment_items' AND COLUMN_NAME='plan_item_id');
SET @s:=IF(@c=0,"ALTER TABLE `cw_payment_items` ADD COLUMN `plan_item_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_payment_items' AND INDEX_NAME='idx_plan_item');
SET @s:=IF(@c=0,"ALTER TABLE `cw_payment_items` ADD KEY `idx_plan_item` (`plan_item_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_payment_items' AND COLUMN_NAME='sessions_total');
SET @s:=IF(@c=0,"ALTER TABLE `cw_payment_items` ADD COLUMN `sessions_total` INT DEFAULT 1",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- record_procedures.performed_service_id (نگاشتِ سیستمِ قدیمیِ تایمر به خدمتِ پیپرلس)
SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='record_procedures' AND COLUMN_NAME='performed_service_id');
SET @s:=IF(@c=0,"ALTER TABLE `record_procedures` ADD COLUMN `performed_service_id` INT 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='record_procedures' AND INDEX_NAME='idx_performed_service');
SET @s:=IF(@c=0,"ALTER TABLE `record_procedures` ADD KEY `idx_performed_service` (`performed_service_id`)",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
-- یکتاییِ نگاشت: هر cw_performed_services حداکثر به یک record_procedure متصل می‌شود (NULLها آزادند).
-- گزارشِ تکراری‌ها (دستی):
--   SELECT performed_service_id, COUNT(*) c FROM record_procedures
--    WHERE performed_service_id IS NOT NULL GROUP BY performed_service_id HAVING c>1;
SET @c:=(SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='record_procedures' AND INDEX_NAME='uniq_performed_service');
SET @dup:=(SELECT COUNT(*) FROM (SELECT performed_service_id FROM record_procedures WHERE performed_service_id IS NOT NULL GROUP BY performed_service_id HAVING COUNT(*)>1) t);
SET @s:=IF(@c=0 AND @dup>0,
    'SELECT `DUPLICATE_performed_service_id_MAPPING_EXISTS__remediate_before_migration` FROM record_procedures LIMIT 1',
    'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
SET @s:=IF(@c=0 AND @dup=0,"ALTER TABLE `record_procedures` ADD UNIQUE KEY `uniq_performed_service` (`performed_service_id`)",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET FOREIGN_KEY_CHECKS = 1;
-- ROLLBACK (دستی):
--   ALTER TABLE `cw_performed_services` DROP INDEX `uniq_payment_item`, DROP INDEX `idx_payment_item`, DROP COLUMN `payment_item_id`;
--   ALTER TABLE `cw_payment_items` DROP INDEX `idx_plan_item`, DROP COLUMN `plan_item_id`, DROP COLUMN `sessions_total`;
--   ALTER TABLE `record_procedures` DROP INDEX `idx_performed_service`, DROP COLUMN `performed_service_id`;