boursecu_server/database/sql/gholaktala_upgrade.sql

-- =============================================================================
-- gholaktala_upgrade.sql — ارتقای DB قلک طلا برای برنچ ماژولار
-- =============================================================================
-- قبل از اجرا:
--   1) بکاپ: mysqldump DB_NAME > backup.sql
--   2) USE `نام_دیتابیس`;
--
-- اصول:
--   • فقط ADD / CREATE IF NOT EXISTS — هیچ DROP نیست
--   • دادهٔ موجود (مشتری، کیف، سفارش، settings، const) حفظ می‌شود
--   • ته‌حساب: جداول قدیمی tahesab_* دست نخورده — جدول tahesab ساخته نمی‌شود
--   • ستون‌های legacy قلک طلا نگه داشته می‌شوند: sum_cash, permission, description, auto_buy, ...
--
-- بعد از این فایل حتماً merge settings/const:
--   mysql ... < database/sql/deploy_settings_const_preserve_value_sync_can_edit.sql
--   یا: bash database/sql/run_gholaktala_upgrade.sh
--
-- سپس:
--   php artisan migrate
--   php artisan config:clear
-- =============================================================================

SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
SET @db = DATABASE();

-- =============================================================================
-- ۱) جداول جدید ماژولار (بدون ته‌حساب)
-- =============================================================================

CREATE TABLE IF NOT EXISTS `addresses` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `city` varchar(255) DEFAULT NULL,
  `province` varchar(255) DEFAULT NULL,
  `phone` varchar(15) DEFAULT NULL,
  `address` varchar(2028) DEFAULT NULL,
  `zip` varchar(15) DEFAULT NULL,
  `customer_id` int unsigned DEFAULT NULL,
  `default_address` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `major_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `files` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `original_name` varchar(100) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `ext` varchar(40) DEFAULT NULL,
  `src` varchar(1024) DEFAULT NULL,
  `size` int unsigned DEFAULT NULL,
  `user_id` int unsigned DEFAULT NULL,
  `document_id` int unsigned DEFAULT NULL,
  `type` tinyint unsigned DEFAULT NULL,
  `has_thumb` tinyint(1) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `customers_settings` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `agent_id` int DEFAULT NULL,
  `customer_id` int DEFAULT NULL,
  `product_id` int DEFAULT NULL,
  `group` varchar(255) DEFAULT NULL,
  `lvl` int DEFAULT NULL,
  `profit_buy` int DEFAULT NULL,
  `profit_sell` int DEFAULT NULL,
  `auto_buy` int DEFAULT NULL,
  `auto_sell` int DEFAULT NULL,
  `credit_buy` int DEFAULT NULL,
  `credit_sell` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `is_active` varchar(255) DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `categories` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `show_in_filter` tinyint(1) NOT NULL DEFAULT 1,
  `show_in_select` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` smallint unsigned NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `categories_name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `price_sync_logs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `source` varchar(32) NOT NULL,
  `source_url` varchar(512) DEFAULT NULL,
  `liquid_gold_price` bigint unsigned NOT NULL DEFAULT 0,
  `products_updated` smallint unsigned NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `price_sync_log_items` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `log_id` bigint unsigned NOT NULL,
  `product_id` bigint unsigned DEFAULT NULL,
  `product_title` varchar(255) DEFAULT NULL,
  `reading_method` varchar(128) DEFAULT NULL,
  `source_name` varchar(255) DEFAULT NULL,
  `base_price` bigint unsigned NOT NULL DEFAULT 0,
  `base_profit` bigint unsigned NOT NULL DEFAULT 0,
  `offset_percent` decimal(8,2) NOT NULL DEFAULT 0.00,
  `stored_price` bigint unsigned NOT NULL DEFAULT 0,
  `previous_price` bigint unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `price_sync_log_items_log_id_index` (`log_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- ۲) wallets + transactions (رفع /customer/me)
-- =============================================================================

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=@db AND TABLE_NAME='wallets')>0
    AND (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='wallets' AND COLUMN_NAME='product_id')=0,
    IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='wallets' AND COLUMN_NAME='fund_id')>0,
        'ALTER TABLE `wallets` ADD COLUMN `product_id` INT NULL DEFAULT NULL AFTER `fund_id`',
        'ALTER TABLE `wallets` ADD COLUMN `product_id` INT NULL DEFAULT NULL'),
    'SELECT ''skip wallets.product_id'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=@db AND TABLE_NAME='wallets')>0
    AND (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='wallets' AND COLUMN_NAME='number')=0,
    IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='wallets' AND COLUMN_NAME='product_id')>0,
        'ALTER TABLE `wallets` ADD COLUMN `number` INT NOT NULL DEFAULT 0 AFTER `product_id`',
        'ALTER TABLE `wallets` ADD COLUMN `number` INT NOT NULL DEFAULT 0'),
    'SELECT ''skip wallets.number'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=@db AND TABLE_NAME='transactions')>0
    AND (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='transactions' AND COLUMN_NAME='title')=0,
    'ALTER TABLE `transactions` ADD COLUMN `title` VARCHAR(255) NULL DEFAULT NULL',
    'SELECT ''skip transactions.title'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=@db AND TABLE_NAME='transactions')>0
    AND (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='transactions' AND COLUMN_NAME='unit')=0,
    IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='transactions' AND COLUMN_NAME='title')>0,
        'ALTER TABLE `transactions` ADD COLUMN `unit` VARCHAR(255) NULL DEFAULT NULL AFTER `title`',
        'ALTER TABLE `transactions` ADD COLUMN `unit` VARCHAR(255) NULL DEFAULT NULL'),
    'SELECT ''skip transactions.unit'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- =============================================================================
-- ۳) customers (ستون‌های جدید — sum_cash قدیمی نگه داشته می‌شود)
-- =============================================================================

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=@db AND TABLE_NAME='customers')>0
    AND (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='customers' AND COLUMN_NAME='gold')=0,
    IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='customers' AND COLUMN_NAME='sum_gold')>0,
        'ALTER TABLE `customers` ADD COLUMN `gold` DECIMAL(10,4) NULL DEFAULT NULL AFTER `sum_gold`',
        'ALTER TABLE `customers` ADD COLUMN `gold` DECIMAL(10,4) NULL DEFAULT NULL'),
    'SELECT ''skip customers.gold'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=@db AND TABLE_NAME='customers')>0
    AND (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='customers' AND COLUMN_NAME='total_referrals')=0,
    IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='customers' AND COLUMN_NAME='referral_to_id')>0,
        'ALTER TABLE `customers` ADD COLUMN `total_referrals` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `referral_to_id`',
        'ALTER TABLE `customers` ADD COLUMN `total_referrals` INT UNSIGNED NOT NULL DEFAULT 0'),
    'SELECT ''skip customers.total_referrals'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=@db AND TABLE_NAME='customers')>0
    AND (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='customers' AND COLUMN_NAME='moshtari_code')=0,
    'ALTER TABLE `customers` ADD COLUMN `moshtari_code` INT NULL DEFAULT NULL',
    'SELECT ''skip customers.moshtari_code'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='customers' AND COLUMN_NAME='referral_id')>0
    AND (SELECT CHARACTER_MAXIMUM_LENGTH FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='customers' AND COLUMN_NAME='referral_id' LIMIT 1) < 12,
    'ALTER TABLE `customers` MODIFY COLUMN `referral_id` VARCHAR(12) NOT NULL',
    'SELECT ''skip customers.referral_id widen'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='customers' AND COLUMN_NAME='referral_to_id')>0
    AND (SELECT CHARACTER_MAXIMUM_LENGTH FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='customers' AND COLUMN_NAME='referral_to_id' LIMIT 1) < 12,
    'ALTER TABLE `customers` MODIFY COLUMN `referral_to_id` VARCHAR(12) NULL DEFAULT NULL',
    'SELECT ''skip customers.referral_to_id widen'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- =============================================================================
-- ۴) users (permission قدیمی نگه — gold جدید اضافه)
-- =============================================================================

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=@db AND TABLE_NAME='users')>0
    AND (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='users' AND COLUMN_NAME='gold')=0,
    'ALTER TABLE `users` ADD COLUMN `gold` INT NULL DEFAULT NULL',
    'SELECT ''skip users.gold'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- =============================================================================
-- ۵) products — ستون‌های ماژولار (auto_buy/kalhor_name/... قدیمی حفظ)
-- =============================================================================

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=@db AND TABLE_NAME='products')>0
    AND (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='products' AND COLUMN_NAME='amount')=0,
    'ALTER TABLE `products` ADD COLUMN `amount` FLOAT NOT NULL DEFAULT 1',
    'SELECT ''skip products.amount'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='products' AND COLUMN_NAME='metal_id')=0,
    'ALTER TABLE `products` ADD COLUMN `metal_id` INT NULL DEFAULT NULL',
    'SELECT ''skip products.metal_id'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='products' AND COLUMN_NAME='group_type')=0,
    'ALTER TABLE `products` ADD COLUMN `group_type` VARCHAR(255) NULL DEFAULT NULL',
    'SELECT ''skip products.group_type'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='products' AND COLUMN_NAME='price')=0,
    'ALTER TABLE `products` ADD COLUMN `price` BIGINT NOT NULL DEFAULT 0',
    'SELECT ''skip products.price'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='products' AND COLUMN_NAME='price_profit')=0,
    'ALTER TABLE `products` ADD COLUMN `price_profit` INT NULL DEFAULT NULL',
    'SELECT ''skip products.price_profit'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='products' AND COLUMN_NAME='price_offset_percent')=0,
    'ALTER TABLE `products` ADD COLUMN `price_offset_percent` DECIMAL(8,2) NOT NULL DEFAULT 0',
    'SELECT ''skip products.price_offset_percent'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='products' AND COLUMN_NAME='show_in_assets')=0,
    'ALTER TABLE `products` ADD COLUMN `show_in_assets` TINYINT(1) NOT NULL DEFAULT 0',
    'SELECT ''skip products.show_in_assets'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='products' AND COLUMN_NAME='use_last_price_if_zero')=0,
    'ALTER TABLE `products` ADD COLUMN `use_last_price_if_zero` TINYINT(1) NOT NULL DEFAULT 0',
    'SELECT ''skip products.use_last_price_if_zero'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='products' AND COLUMN_NAME='auto_open_at')=0,
    'ALTER TABLE `products` ADD COLUMN `auto_open_at` TIME NULL DEFAULT NULL',
    'SELECT ''skip products.auto_open_at'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='products' AND COLUMN_NAME='auto_close_at')=0,
    'ALTER TABLE `products` ADD COLUMN `auto_close_at` TIME NULL DEFAULT NULL',
    'SELECT ''skip products.auto_close_at'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='products' AND COLUMN_NAME='override_trading_hours')=0,
    'ALTER TABLE `products` ADD COLUMN `override_trading_hours` TINYINT(1) NOT NULL DEFAULT 0',
    'SELECT ''skip products.override_trading_hours'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='products' AND COLUMN_NAME='percent_fee_buy')=0,
    'ALTER TABLE `products` ADD COLUMN `percent_fee_buy` FLOAT NULL DEFAULT NULL',
    'SELECT ''skip products.percent_fee_buy'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='products' AND COLUMN_NAME='percent_fee_sell')=0,
    'ALTER TABLE `products` ADD COLUMN `percent_fee_sell` FLOAT NULL DEFAULT NULL',
    'SELECT ''skip products.percent_fee_sell'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='products' AND COLUMN_NAME='order_limit_enabled')=0,
    'ALTER TABLE `products` ADD COLUMN `order_limit_enabled` TINYINT(1) NOT NULL DEFAULT 0',
    'SELECT ''skip products.order_limit_enabled'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='products' AND COLUMN_NAME='min_order_cash')=0,
    'ALTER TABLE `products` ADD COLUMN `min_order_cash` BIGINT UNSIGNED NULL DEFAULT NULL',
    'SELECT ''skip products.min_order_cash'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='products' AND COLUMN_NAME='max_order_cash')=0,
    'ALTER TABLE `products` ADD COLUMN `max_order_cash` BIGINT UNSIGNED NULL DEFAULT NULL',
    'SELECT ''skip products.max_order_cash'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='products' AND COLUMN_NAME='cash_fee_buy')=0,
    'ALTER TABLE `products` ADD COLUMN `cash_fee_buy` DECIMAL(14,4) NULL DEFAULT NULL',
    'SELECT ''skip products.cash_fee_buy'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='products' AND COLUMN_NAME='cash_fee_buy_is_percent')=0,
    'ALTER TABLE `products` ADD COLUMN `cash_fee_buy_is_percent` TINYINT(1) NOT NULL DEFAULT 0',
    'SELECT ''skip products.cash_fee_buy_is_percent'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='products' AND COLUMN_NAME='cash_fee_sell')=0,
    'ALTER TABLE `products` ADD COLUMN `cash_fee_sell` DECIMAL(14,4) NULL DEFAULT NULL',
    'SELECT ''skip products.cash_fee_sell'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='products' AND COLUMN_NAME='cash_fee_sell_is_percent')=0,
    'ALTER TABLE `products` ADD COLUMN `cash_fee_sell_is_percent` TINYINT(1) NOT NULL DEFAULT 0',
    'SELECT ''skip products.cash_fee_sell_is_percent'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='products' AND COLUMN_NAME='bubble')=0,
    'ALTER TABLE `products` ADD COLUMN `bubble` TINYINT NOT NULL DEFAULT 0',
    'SELECT ''skip products.bubble'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='products' AND COLUMN_NAME='direct_price')=0,
    'ALTER TABLE `products` ADD COLUMN `direct_price` TINYINT NULL DEFAULT NULL',
    'SELECT ''skip products.direct_price'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='products' AND COLUMN_NAME='app_price')=0,
    'ALTER TABLE `products` ADD COLUMN `app_price` TINYINT(1) NOT NULL DEFAULT 0',
    'SELECT ''skip products.app_price'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='products' AND COLUMN_NAME='trade_by_quantity')=0,
    'ALTER TABLE `products` ADD COLUMN `trade_by_quantity` TINYINT(1) NOT NULL DEFAULT 0',
    'SELECT ''skip products.trade_by_quantity'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='products' AND COLUMN_NAME='has_physical_delivery')=0,
    'ALTER TABLE `products` ADD COLUMN `has_physical_delivery` TINYINT(1) NOT NULL DEFAULT 0',
    'SELECT ''skip products.has_physical_delivery'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='products' AND COLUMN_NAME='min_physical_delivery_quantity')=0,
    'ALTER TABLE `products` ADD COLUMN `min_physical_delivery_quantity` DECIMAL(12,3) NULL DEFAULT NULL',
    'SELECT ''skip products.min_physical_delivery_quantity'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- =============================================================================
-- ۶) orders
-- =============================================================================

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='orders' AND COLUMN_NAME='provider_id')=0,
    'ALTER TABLE `orders` ADD COLUMN `provider_id` BIGINT UNSIGNED NULL AFTER `id`, ADD INDEX `orders_provider_id_index` (`provider_id`)',
    'SELECT ''skip orders.provider_id'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='orders' AND COLUMN_NAME='total_price_montzar')=0,
    IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='orders' AND COLUMN_NAME='total_price')>0,
        'ALTER TABLE `orders` ADD COLUMN `total_price_montzar` DECIMAL(20,4) NULL DEFAULT NULL AFTER `total_price`',
        'ALTER TABLE `orders` ADD COLUMN `total_price_montzar` DECIMAL(20,4) NULL DEFAULT NULL'),
    'SELECT ''skip orders.total_price_montzar'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='orders' AND COLUMN_NAME='fee_asset')=0,
    IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='orders' AND COLUMN_NAME='service_charge_price')>0,
        'ALTER TABLE `orders` ADD COLUMN `fee_asset` VARCHAR(8) NULL DEFAULT NULL AFTER `service_charge_price`',
        'ALTER TABLE `orders` ADD COLUMN `fee_asset` VARCHAR(8) NULL DEFAULT NULL'),
    'SELECT ''skip orders.fee_asset'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- =============================================================================
-- ۷) withdraws (description قدیمی حفظ — extra_note جدید)
-- =============================================================================

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='withdraws' AND COLUMN_NAME='extra_note')=0,
    IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='withdraws' AND COLUMN_NAME='note')>0,
        'ALTER TABLE `withdraws` ADD COLUMN `extra_note` TEXT NULL DEFAULT NULL AFTER `note`',
        'ALTER TABLE `withdraws` ADD COLUMN `extra_note` TEXT NULL DEFAULT NULL'),
    'SELECT ''skip withdraws.extra_note'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='withdraws' AND COLUMN_NAME='address_id')=0,
    'ALTER TABLE `withdraws` ADD COLUMN `address_id` BIGINT NULL DEFAULT NULL',
    'SELECT ''skip withdraws.address_id'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- =============================================================================
-- ۸) settings / const — فقط schema (merge داده در فایل بعدی)
-- =============================================================================

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='settings' AND COLUMN_NAME='type')=0,
    IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='settings' AND COLUMN_NAME='description')>0,
        'ALTER TABLE `settings` ADD COLUMN `type` VARCHAR(50) NULL DEFAULT NULL AFTER `description`',
        'ALTER TABLE `settings` ADD COLUMN `type` VARCHAR(50) NULL DEFAULT NULL'),
    'SELECT ''skip settings.type'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='const' AND COLUMN_NAME='value_json')=0,
    'ALTER TABLE `const` ADD COLUMN `value_json` LONGTEXT NULL DEFAULT NULL',
    'SELECT ''skip const.value_json'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

ALTER TABLE `settings`
  MODIFY COLUMN `description` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
  MODIFY COLUMN `title_fa` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL;

ALTER TABLE `const`
  MODIFY COLUMN `title_fa` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL;

-- =============================================================================
-- ۹) backfill داده — محصولات قلک طلا (گوی/مکعب)
-- =============================================================================

UPDATE `products`
SET
  `metal_id` = COALESCE(`metal_id`, 1),
  `group_type` = COALESCE(NULLIF(`group_type`, ''), `title`),
  `amount` = CASE WHEN `amount` IS NULL OR `amount` = 0 THEN 1 ELSE `amount` END,
  `price` = COALESCE(`price`, 0),
  `show_in_assets` = COALESCE(`show_in_assets`, 1),
  `active` = COALESCE(`active`, 1),
  `show` = COALESCE(`show`, 1)
WHERE `deleted_at` IS NULL;

INSERT IGNORE INTO `categories` (`name`, `active`, `show_in_filter`, `show_in_select`, `sort_order`, `created_at`)
SELECT DISTINCT `group_type`, 1, 1, 1, 0, NOW()
FROM `products`
WHERE `group_type` IS NOT NULL AND `group_type` <> '';

INSERT IGNORE INTO `categories` (`name`, `active`, `show_in_filter`, `show_in_select`, `sort_order`, `created_at`) VALUES
('گوی', 1, 1, 1, 1, NOW()),
('مکعب', 1, 1, 1, 2, NOW());

-- =============================================================================
-- ۱۰) InnoDB (اختیاری ولی توصیه‌شده)
-- =============================================================================

SET @sql = IF(
    (SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA=@db AND TABLE_NAME='products' LIMIT 1) = 'MyISAM',
    'ALTER TABLE `products` ENGINE=InnoDB',
    'SELECT ''skip products engine'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA=@db AND TABLE_NAME='settings' LIMIT 1) = 'MyISAM',
    'ALTER TABLE `settings` ENGINE=InnoDB',
    'SELECT ''skip settings engine'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- =============================================================================
SELECT 'gholaktala_upgrade.sql schema done — now run deploy_settings_const_preserve_value_sync_can_edit.sql' AS next_step;