-- ============================================================================
--  2026_sprint30d_finance_corrections.sql — فاز ۳ (تکمیل و اصلاحاتِ بحرانی)
-- ----------------------------------------------------------------------------
--  ۱۰۰٪ ADDITIVE و idempotent. بدونِ DELIMITER/stored-procedure (سازگار با cPanel).
--  config.php دست‌نخورده. اجرای مجدد امن.
--  محتوا: لجرِ چندروشیِ پرداخت، فلگ‌های آزادسازی/کیف‌پول/BOM/بیعانه، ردیابیِ جلسه،
--  لینک‌دهیِ کاملِ تسک‌های تماس + جدولِ تاریخچهٔ تسک، تنظیماتِ سیاست.
-- ============================================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ── لجرِ چندروشیِ پرداخت ──
CREATE TABLE IF NOT EXISTS `cw_payment_splits` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `payment_id` BIGINT UNSIGNED NOT NULL,
  `method`     VARCHAR(24)  NOT NULL,
  `amount`     BIGINT       NOT NULL DEFAULT 0,
  `ref`        VARCHAR(120) DEFAULT NULL,
  `note`       VARCHAR(190) DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME     NOT NULL,
  PRIMARY KEY (`id`), KEY `idx_payment` (`payment_id`), KEY `idx_method` (`method`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── جدولِ تاریخچهٔ اقداماتِ تسک (append-only) ──
CREATE TABLE IF NOT EXISTS `cw_call_task_history` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `task_id`    BIGINT UNSIGNED NOT NULL,
  `action`     VARCHAR(24)  NOT NULL,
  `detail`     VARCHAR(255) DEFAULT NULL,
  `result`     VARCHAR(30)  DEFAULT NULL,
  `by_user`    INT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME     NOT NULL,
  PRIMARY KEY (`id`), KEY `idx_task` (`task_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_payments' AND COLUMN_NAME='released');
SET @s:=IF(@c=0,"ALTER TABLE `cw_payments` ADD COLUMN `released` TINYINT(1) NOT NULL DEFAULT 0 AFTER `status`",'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_payments' AND COLUMN_NAME='released_by');
SET @s:=IF(@c=0,"ALTER TABLE `cw_payments` ADD COLUMN `released_by` INT UNSIGNED DEFAULT NULL AFTER `released`",'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_payments' AND COLUMN_NAME='released_at');
SET @s:=IF(@c=0,"ALTER TABLE `cw_payments` ADD COLUMN `released_at` DATETIME DEFAULT NULL AFTER `released_by`",'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_payments' AND COLUMN_NAME='release_override');
SET @s:=IF(@c=0,"ALTER TABLE `cw_payments` ADD COLUMN `release_override` TINYINT(1) NOT NULL DEFAULT 0 AFTER `released_at`",'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_payments' AND COLUMN_NAME='override_reason');
SET @s:=IF(@c=0,"ALTER TABLE `cw_payments` ADD COLUMN `override_reason` VARCHAR(255) DEFAULT NULL AFTER `release_override`",'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_payments' AND COLUMN_NAME='wallet_applied');
SET @s:=IF(@c=0,"ALTER TABLE `cw_payments` ADD COLUMN `wallet_applied` BIGINT NOT NULL DEFAULT 0 AFTER `override_reason`",'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_payments' AND COLUMN_NAME='deposit_amount');
SET @s:=IF(@c=0,"ALTER TABLE `cw_payments` ADD COLUMN `deposit_amount` BIGINT NOT NULL DEFAULT 0 AFTER `wallet_applied`",'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_payments' AND COLUMN_NAME='bom_consumed');
SET @s:=IF(@c=0,"ALTER TABLE `cw_payments` ADD COLUMN `bom_consumed` TINYINT(1) NOT NULL DEFAULT 0 AFTER `deposit_amount`",'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 NULL AFTER `final_price`",'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_purchased');
SET @s:=IF(@c=0,"ALTER TABLE `cw_payment_items` ADD COLUMN `sessions_purchased` INT DEFAULT NULL AFTER `sessions_total`",'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_call_tasks' AND COLUMN_NAME='visit_id');
SET @s:=IF(@c=0,"ALTER TABLE `cw_call_tasks` ADD COLUMN `visit_id` INT UNSIGNED DEFAULT NULL AFTER `patient_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_call_tasks' AND COLUMN_NAME='plan_id');
SET @s:=IF(@c=0,"ALTER TABLE `cw_call_tasks` ADD COLUMN `plan_id` INT UNSIGNED DEFAULT NULL AFTER `visit_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_call_tasks' AND COLUMN_NAME='plan_item_id');
SET @s:=IF(@c=0,"ALTER TABLE `cw_call_tasks` ADD COLUMN `plan_item_id` INT UNSIGNED DEFAULT NULL AFTER `plan_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_call_tasks' AND COLUMN_NAME='source_dept');
SET @s:=IF(@c=0,"ALTER TABLE `cw_call_tasks` ADD COLUMN `source_dept` VARCHAR(30) DEFAULT NULL AFTER `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_call_tasks' AND COLUMN_NAME='source_record');
SET @s:=IF(@c=0,"ALTER TABLE `cw_call_tasks` ADD COLUMN `source_record` VARCHAR(60) DEFAULT NULL AFTER `source_dept`",'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_call_tasks' AND COLUMN_NAME='result');
SET @s:=IF(@c=0,"ALTER TABLE `cw_call_tasks` ADD COLUMN `result` VARCHAR(30) DEFAULT NULL AFTER `source_record`",'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_call_tasks' AND COLUMN_NAME='call_attempts');
SET @s:=IF(@c=0,"ALTER TABLE `cw_call_tasks` ADD COLUMN `call_attempts` INT NOT NULL DEFAULT 0 AFTER `result`",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- ── تنظیماتِ سیاست ──
INSERT IGNORE INTO `cw_settings` (`k`,`v`,`updated_at`) VALUES
 ('allow_treatment_before_payment','0',NOW()),
 ('session_billing_default','single',NOW());

SET FOREIGN_KEY_CHECKS = 1;
-- ============================================================================
--  ROLLBACK (دستی، با احتیاط): DROP TABLE cw_payment_splits, cw_call_task_history;
--  و در صورتِ نیاز حذفِ ستون‌های افزوده به cw_payments/cw_payment_items/cw_call_tasks.
-- ============================================================================