<?php

declare(strict_types=1);

require_once __DIR__ . '/module_hub_helpers.php';
require_once __DIR__ . '/auth.php';

if (!function_exists('v3_db')) {
    function v3_db(): ?mysqli
    {
        return hub_db();
    }
}

if (!function_exists('v3_admin_id')) {
    function v3_admin_id(): int
    {
        return function_exists('current_admin_id') ? current_admin_id() : 0;
    }
}

if (!function_exists('v3_exec')) {
    function v3_exec(?mysqli $db, string $sql): bool
    {
        if (!$db) return false;
        try {
            return (bool)$db->query($sql);
        } catch (Throwable $e) {
            return false;
        }
    }
}

if (!function_exists('v3_setting_set')) {
    function v3_setting_set(?mysqli $db, string $key, string $value, string $group = 'v3'): bool
    {
        if (!$db || $key === '') return false;
        $stmt = $db->prepare("INSERT INTO settings (setting_key, setting_value, setting_group, created_at, updated_at) VALUES (?, ?, ?, NOW(), NOW()) ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value), setting_group = VALUES(setting_group), updated_at = NOW()");
        if (!$stmt) return false;
        $stmt->bind_param('sss', $key, $value, $group);
        $ok = $stmt->execute();
        $stmt->close();
        return (bool)$ok;
    }
}


if (!function_exists('v3_setting_get')) {
    function v3_setting_get(?mysqli $db, string $key, string $default = ''): string
    {
        if (!$db || $key === '') return $default;
        $stmt = $db->prepare('SELECT setting_value FROM settings WHERE setting_key = ? LIMIT 1');
        if (!$stmt) return $default;
        $stmt->bind_param('s', $key);
        $stmt->execute();
        $row = $stmt->get_result()->fetch_assoc() ?: null;
        $stmt->close();
        return $row ? (string)($row['setting_value'] ?? $default) : $default;
    }
}

if (!function_exists('v3_generate_referral_code')) {
    function v3_generate_referral_code(?mysqli $db, int $customerId): string
    {
        if ($db) {
            $stmt = $db->prepare('SELECT phone FROM customers WHERE id = ? LIMIT 1');
            if ($stmt) {
                $stmt->bind_param('i', $customerId);
                $stmt->execute();
                $row = $stmt->get_result()->fetch_assoc() ?: null;
                $stmt->close();
                $phone = preg_replace('/\D+/', '', (string)($row['phone'] ?? '')) ?: '';
                if ($phone !== '') {
                    return $phone;
                }
            }
        }
        $prefix = 'NAY';
        for ($i = 0; $i < 8; $i++) {
            $code = $prefix . strtoupper(substr(md5($customerId . '-' . uniqid((string)$customerId, true)), 0, 7));
            if (!$db) return $code;
            $stmt = $db->prepare('SELECT id FROM customers WHERE referral_code = ? LIMIT 1');
            if (!$stmt) return $code;
            $stmt->bind_param('s', $code);
            $stmt->execute();
            $res = $stmt->get_result();
            $exists = $res instanceof mysqli_result ? $res->fetch_assoc() : null;
            $stmt->close();
            if (!$exists) {
                return $code;
            }
        }
        return $prefix . strtoupper(substr(sha1((string)$customerId . microtime(true)), 0, 8));
    }
}

if (!function_exists('v3_seed_customer_referral_codes')) {
    function v3_seed_customer_referral_codes(?mysqli $db): int
    {
        if (!$db || !hub_table_exists($db, 'customers')) return 0;
        $count = 0;
        $rows = hub_fetch_all($db, "SELECT id FROM customers WHERE COALESCE(referral_code,'') = '' ORDER BY id ASC");
        foreach ($rows as $row) {
            $id = (int)($row['id'] ?? 0);
            if ($id <= 0) continue;
            $code = v3_generate_referral_code($db, $id);
            $stmt = $db->prepare('UPDATE customers SET referral_code = ? WHERE id = ?');
            if ($stmt) {
                $stmt->bind_param('si', $code, $id);
                if ($stmt->execute()) {
                    $count++;
                }
                $stmt->close();
            }
        }
        return $count;
    }
}

if (!function_exists('v3_customer_name_map')) {
    function v3_customer_name_map(?mysqli $db): array
    {
        $map = [];
        if (!$db || !hub_table_exists($db, 'customers')) return $map;
        $rows = hub_fetch_all($db, "SELECT id, full_name, phone FROM customers ORDER BY full_name ASC");
        foreach ($rows as $row) {
            $label = trim((string)($row['full_name'] ?? ''));
            $phone = trim((string)($row['phone'] ?? ''));
            $map[(int)$row['id']] = $label . ($phone !== '' ? ' — ' . $phone : '');
        }
        return $map;
    }
}


if (!function_exists('v3_wallet_has_column')) {
    function v3_wallet_has_column(?mysqli $db, string $column): bool
    {
        return hub_column_exists($db, 'customer_wallet_transactions', $column);
    }
}

if (!function_exists('v3_referrals_has_column')) {
    function v3_referrals_has_column(?mysqli $db, string $column): bool
    {
        return hub_column_exists($db, 'customer_referrals', $column);
    }
}

if (!function_exists('v3_create_wallet_tx')) {
    function v3_create_wallet_tx(?mysqli $db, int $customerId, string $direction, float $amount, string $sourceType = 'admin', string $sourceRef = '', string $note = '', ?int $adminId = null): bool
    {
        if (!$db || $customerId <= 0 || $amount <= 0) return false;
        $direction = strtolower($direction) === 'debit' ? 'debit' : 'credit';

        $db->begin_transaction();
        try {
            $stmt = $db->prepare('SELECT wallet_balance FROM customers WHERE id = ? LIMIT 1 FOR UPDATE');
            if (!$stmt) throw new RuntimeException('prepare');
            $stmt->bind_param('i', $customerId);
            $stmt->execute();
            $res = $stmt->get_result();
            $row = $res instanceof mysqli_result ? $res->fetch_assoc() : null;
            $stmt->close();
            if (!$row) throw new RuntimeException('customer');

            $current = (float)($row['wallet_balance'] ?? 0);
            $signedAmount = $direction === 'debit' ? -abs($amount) : abs($amount);
            $next = $current + $signedAmount;

            $stmt = $db->prepare('UPDATE customers SET wallet_balance = ? WHERE id = ?');
            if (!$stmt) throw new RuntimeException('update');
            $stmt->bind_param('di', $next, $customerId);
            $stmt->execute();
            $stmt->close();

            $adminId = $adminId ?? v3_admin_id();

            $columns = ['customer_id', 'amount', 'balance_after', 'source_ref'];
            $placeholders = ['?', '?', '?', '?'];
            $types = 'idds';
            $values = [$customerId, abs($amount), $next, $sourceRef];

            if (v3_wallet_has_column($db, 'direction')) {
                $columns[] = 'direction';
                $placeholders[] = '?';
                $types .= 's';
                $values[] = $direction;
            }
            if (v3_wallet_has_column($db, 'source_type')) {
                $columns[] = 'source_type';
                $placeholders[] = '?';
                $types .= 's';
                $values[] = $sourceType;
            }
            if (v3_wallet_has_column($db, 'note')) {
                $columns[] = 'note';
                $placeholders[] = '?';
                $types .= 's';
                $values[] = $note;
            }
            if (v3_wallet_has_column($db, 'created_by_admin_id')) {
                $columns[] = 'created_by_admin_id';
                $placeholders[] = '?';
                $types .= 'i';
                $values[] = $adminId;
            }
            if (v3_wallet_has_column($db, 'txn_type')) {
                $columns[] = 'txn_type';
                $placeholders[] = '?';
                $types .= 's';
                $values[] = $direction;
            }
            if (v3_wallet_has_column($db, 'source')) {
                $columns[] = 'source';
                $placeholders[] = '?';
                $types .= 's';
                $values[] = $sourceType;
            }
            if (v3_wallet_has_column($db, 'notes')) {
                $columns[] = 'notes';
                $placeholders[] = '?';
                $types .= 's';
                $values[] = $note;
            }

            $sql = 'INSERT INTO customer_wallet_transactions (' . implode(', ', $columns) . ', created_at) VALUES (' . implode(', ', $placeholders) . ', NOW())';
            $stmt = $db->prepare($sql);
            if (!$stmt) throw new RuntimeException('insert tx');
            $stmt->bind_param($types, ...$values);
            $stmt->execute();
            $stmt->close();

            $db->commit();
            return true;
        } catch (Throwable $e) {
            $db->rollback();
            return false;
        }
    }
}

if (!function_exists('v3_create_loyalty_tx')) {
    function v3_create_loyalty_tx(?mysqli $db, int $customerId, int $points, string $type = 'credit', string $source = 'referral', string $note = ''): bool
    {
        if (!$db || $customerId <= 0 || $points <= 0 || !hub_table_exists($db, 'customer_loyalty_transactions')) return false;
        $type = strtolower($type) === 'debit' ? 'debit' : 'credit';

        $db->begin_transaction();
        try {
            $stmt = $db->prepare('SELECT points_balance FROM customers WHERE id = ? LIMIT 1 FOR UPDATE');
            if (!$stmt) throw new RuntimeException('prepare');
            $stmt->bind_param('i', $customerId);
            $stmt->execute();
            $res = $stmt->get_result();
            $row = $res instanceof mysqli_result ? $res->fetch_assoc() : null;
            $stmt->close();
            if (!$row) throw new RuntimeException('customer');

            $current = (int)($row['points_balance'] ?? 0);
            $next = $type === 'debit' ? ($current - $points) : ($current + $points);

            $stmt = $db->prepare('UPDATE customers SET points_balance = ? WHERE id = ?');
            if (!$stmt) throw new RuntimeException('update');
            $stmt->bind_param('ii', $next, $customerId);
            $stmt->execute();
            $stmt->close();

            $adminId = v3_admin_id();
            $txnType = $type === 'debit' ? 'referral_debit' : 'referral_reward';
            $rewardTitle = $source;
            $stmt = $db->prepare('INSERT INTO customer_loyalty_transactions (customer_id, order_id, admin_id, txn_type, points_delta, balance_after, redemption_type, reward_title, notes, created_at) VALUES (?, NULL, ?, ?, ?, ?, ?, ?, ?, NOW())');
            if (!$stmt) throw new RuntimeException('insert tx');
            $redemptionType = 'amount';
            $stmt->bind_param('iisiisss', $customerId, $adminId, $txnType, $points, $next, $redemptionType, $rewardTitle, $note);
            $stmt->execute();
            $stmt->close();

            $db->commit();
            return true;
        } catch (Throwable $e) {
            $db->rollback();
            return false;
        }
    }
}

if (!function_exists('v3_assign_customer_tier')) {
    function v3_assign_customer_tier(?mysqli $db, int $customerId, int $tierId, string $note = ''): bool
    {
        if (!$db || $customerId <= 0 || $tierId <= 0) return false;
        $adminId = v3_admin_id();
        $stmt = $db->prepare('UPDATE customers SET tier_id = ? WHERE id = ?');
        if (!$stmt) return false;
        $stmt->bind_param('ii', $tierId, $customerId);
        $ok = $stmt->execute();
        $stmt->close();
        if (!$ok) return false;

        $stmt = $db->prepare('INSERT INTO customer_tier_history (customer_id, tier_id, assigned_by_admin_id, note, created_at) VALUES (?, ?, ?, ?, NOW())');
        if ($stmt) {
            $stmt->bind_param('iiis', $customerId, $tierId, $adminId, $note);
            $stmt->execute();
            $stmt->close();
        }
        return true;
    }
}

if (!function_exists('v3_sync_customer_order_stats')) {
    function v3_sync_customer_order_stats(?mysqli $db, ?int $customerId = null): int
    {
        if (!$db || !hub_table_exists($db, 'customers') || !hub_table_exists($db, 'orders')) {
            return 0;
        }

        $customerIds = [];
        if ($customerId && $customerId > 0) {
            $customerIds[] = $customerId;
        } else {
            $rows = hub_fetch_all($db, 'SELECT id FROM customers');
            foreach ($rows as $row) {
                $customerIds[] = (int)($row['id'] ?? 0);
            }
        }

        $updated = 0;
        foreach ($customerIds as $cid) {
            if ($cid <= 0) {
                continue;
            }
            $stmt = $db->prepare("SELECT COUNT(*) AS total_orders,
                                         COALESCE(SUM(total_amount), 0) AS total_spent,
                                         MAX(COALESCE(delivered_at, updated_at, created_at)) AS last_delivered_order_at
                                  FROM orders
                                  WHERE customer_id = ? AND CASE
                                        WHEN order_status IN ('cancelled','returned') THEN 'cancelled'
                                        WHEN order_status IN ('delivered','completed') THEN 'delivered'
                                        WHEN order_status IN ('ready','out_for_delivery','shipped') THEN 'ready'
                                        WHEN order_status = 'postponed' THEN 'postponed'
                                        ELSE 'pending' END = 'delivered'");
            if (!$stmt) {
                continue;
            }
            $stmt->bind_param('i', $cid);
            $stmt->execute();
            $stats = $stmt->get_result()->fetch_assoc() ?: [];
            $stmt->close();

            $totalOrders = (int)($stats['total_orders'] ?? 0);
            $totalSpent = round((float)($stats['total_spent'] ?? 0), 2);
            $lastDelivered = !empty($stats['last_delivered_order_at']) ? (string)$stats['last_delivered_order_at'] : null;

            $updateStmt = $db->prepare('UPDATE customers SET total_orders = ?, total_spent = ?, last_delivered_order_at = ? WHERE id = ? LIMIT 1');
            if ($updateStmt) {
                $updateStmt->bind_param('idsi', $totalOrders, $totalSpent, $lastDelivered, $cid);
                $updateStmt->execute();
                $updateStmt->close();
                $updated++;
            }
        }

        return $updated;
    }
}

if (!function_exists('v3_sync_customer_tiers')) {
    function v3_sync_customer_tiers(?mysqli $db): int
    {
        if (!$db) return 0;
        v3_sync_customer_order_stats($db);
        $tiers = hub_fetch_all($db, "SELECT id, min_points, min_total_spent, min_total_orders, sort_order FROM customer_tiers WHERE is_active = 1 ORDER BY min_points ASC, min_total_spent ASC, min_total_orders ASC, sort_order ASC, id ASC");
        if (!$tiers) return 0;
        $customers = hub_fetch_all($db, "SELECT id, COALESCE(tier_points,0) AS tier_points, COALESCE(total_spent,0) AS total_spent, COALESCE(total_orders,0) AS total_orders, tier_id, last_delivered_order_at FROM customers");
        $changes = 0;
        $inactivityDays = max(0, (int)v3_setting_get($db, 'tier_inactivity_days', '0'));
        foreach ($customers as $customer) {
            $effectivePoints = (int)($customer['tier_points'] ?? 0);
            if ($inactivityDays > 0 && !empty($customer['last_delivered_order_at'])) {
                $lastDelivered = strtotime((string)$customer['last_delivered_order_at']);
                if ($lastDelivered && $lastDelivered < strtotime('-' . $inactivityDays . ' days')) {
                    $effectivePoints = max(0, (int)floor($effectivePoints / 2));
                }
            }
            $matchId = 0;
            $totalSpent = (float)($customer['total_spent'] ?? 0);
            $totalOrders = (int)($customer['total_orders'] ?? 0);
            foreach ($tiers as $tier) {
                $minPoints = (int)($tier['min_points'] ?? 0);
                $minSpent = (float)($tier['min_total_spent'] ?? 0);
                $minOrders = (int)($tier['min_total_orders'] ?? 0);
                if ($effectivePoints >= $minPoints && $totalSpent >= $minSpent && $totalOrders >= $minOrders) {
                    $matchId = (int)$tier['id'];
                }
            }
            if ($matchId > 0 && (int)($customer['tier_id'] ?? 0) !== $matchId) {
                if (v3_assign_customer_tier($db, (int)$customer['id'], $matchId, 'مزامنة تلقائية حسب Tier Points')) {
                    $changes++;
                }
            }
        }
        return $changes;
    }
}

if (!function_exists('v3_ensure_schema')) {
    function v3_ensure_schema(?mysqli $db): array
    {
        if (!$db) {
            return ['ok' => false, 'notes' => ['تعذر الاتصال بقاعدة البيانات.']];
        }
        $notes = [];
        $ok = true;

        $queries = [
            "CREATE TABLE IF NOT EXISTS customer_tiers (
                id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                title VARCHAR(120) NOT NULL,
                slug VARCHAR(120) NOT NULL,
                description TEXT NULL,
                benefits_text TEXT NULL,
                min_total_spent DECIMAL(12,2) NOT NULL DEFAULT 0,
                min_total_orders INT NOT NULL DEFAULT 0,
                min_points INT NOT NULL DEFAULT 0,
                extra_discount_percent DECIMAL(6,2) NOT NULL DEFAULT 0,
                threshold_promotion_ids TEXT NULL,
                color_hex VARCHAR(20) NOT NULL DEFAULT '#8B5CF6',
                sort_order INT NOT NULL DEFAULT 0,
                is_active TINYINT(1) NOT NULL DEFAULT 1,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                UNIQUE KEY uq_customer_tiers_slug (slug)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
            "CREATE TABLE IF NOT EXISTS customer_tier_history (
                id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                customer_id INT UNSIGNED NOT NULL,
                tier_id INT UNSIGNED NOT NULL,
                assigned_by_admin_id INT UNSIGNED NULL,
                note VARCHAR(255) NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                KEY idx_cth_customer (customer_id),
                KEY idx_cth_tier (tier_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
            "CREATE TABLE IF NOT EXISTS customer_wallet_transactions (
                id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                customer_id INT UNSIGNED NOT NULL,
                direction ENUM('credit','debit') NOT NULL DEFAULT 'credit',
                amount DECIMAL(12,2) NOT NULL DEFAULT 0,
                balance_after DECIMAL(12,2) NOT NULL DEFAULT 0,
                source_type VARCHAR(50) NOT NULL DEFAULT 'admin',
                source_ref VARCHAR(100) NULL,
                note TEXT NULL,
                created_by_admin_id INT UNSIGNED NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                KEY idx_cwt_customer (customer_id),
                KEY idx_cwt_created (created_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
            "CREATE TABLE IF NOT EXISTS customer_referrals (
                id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                referrer_customer_id INT UNSIGNED NOT NULL,
                referred_customer_id INT UNSIGNED NOT NULL,
                referral_code VARCHAR(50) NOT NULL,
                status ENUM('pending','converted','rewarded','cancelled') NOT NULL DEFAULT 'pending',
                reward_points INT NOT NULL DEFAULT 0,
                reward_wallet_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
                note VARCHAR(255) NULL,
                rewarded_at DATETIME NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                UNIQUE KEY uq_referral_pair (referrer_customer_id, referred_customer_id),
                KEY idx_referral_code (referral_code),
                KEY idx_referral_status (status)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        ];

        foreach ($queries as $sql) {
            if (!v3_exec($db, $sql)) {
                $ok = false;
            }
        }

        $customerAlterSql = [];
        if (!hub_column_exists($db, 'customers', 'referral_code')) {
            $customerAlterSql[] = "ALTER TABLE customers ADD COLUMN referral_code VARCHAR(50) NULL AFTER province_id";
        }
        if (!hub_column_exists($db, 'customers', 'referred_by_customer_id')) {
            $customerAlterSql[] = "ALTER TABLE customers ADD COLUMN referred_by_customer_id INT UNSIGNED NULL AFTER referral_code";
        }
        if (!hub_column_exists($db, 'customers', 'wallet_balance')) {
            $customerAlterSql[] = "ALTER TABLE customers ADD COLUMN wallet_balance DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER referred_by_customer_id";
        }
        if (!hub_column_exists($db, 'customers', 'tier_id')) {
            $customerAlterSql[] = "ALTER TABLE customers ADD COLUMN tier_id INT UNSIGNED NULL AFTER wallet_balance";
        }
        if (!hub_column_exists($db, 'customers', 'tier_points')) {
            $customerAlterSql[] = "ALTER TABLE customers ADD COLUMN tier_points INT NOT NULL DEFAULT 0 AFTER tier_id";
        }
        if (!hub_column_exists($db, 'customers', 'last_delivered_order_at')) {
            $customerAlterSql[] = "ALTER TABLE customers ADD COLUMN last_delivered_order_at DATETIME NULL AFTER tier_points";
        }
        if (!hub_column_exists($db, 'customers', 'tier_cycle_started_at')) {
            $customerAlterSql[] = "ALTER TABLE customers ADD COLUMN tier_cycle_started_at DATETIME NULL AFTER last_delivered_order_at";
        }
        foreach ($customerAlterSql as $sql) {
            if (!v3_exec($db, $sql)) {
                $ok = false;
            }
        }

        if (hub_table_exists($db, 'customer_tiers')) {
            if (!hub_column_exists($db, 'customer_tiers', 'extra_discount_percent')) {
                @v3_exec($db, "ALTER TABLE customer_tiers ADD COLUMN extra_discount_percent DECIMAL(6,2) NOT NULL DEFAULT 0 AFTER min_points");
            }
            if (!hub_column_exists($db, 'customer_tiers', 'threshold_promotion_ids')) {
                @v3_exec($db, "ALTER TABLE customer_tiers ADD COLUMN threshold_promotion_ids TEXT NULL AFTER extra_discount_percent");
            }
        }

        if (hub_table_exists($db, 'orders')) {
            if (!hub_column_exists($db, 'orders', 'tier_points_earned')) {
                @v3_exec($db, "ALTER TABLE orders ADD COLUMN tier_points_earned INT NOT NULL DEFAULT 0 AFTER loyalty_points_earned");
            }
            if (!hub_column_exists($db, 'orders', 'tier_points_awarded')) {
                @v3_exec($db, "ALTER TABLE orders ADD COLUMN tier_points_awarded TINYINT(1) NOT NULL DEFAULT 0 AFTER tier_points_earned");
            }
        }

        if (hub_table_exists($db, 'customer_wallet_transactions')) {
            if (!hub_column_exists($db, 'customer_wallet_transactions', 'direction')) {
                @v3_exec($db, "ALTER TABLE customer_wallet_transactions ADD COLUMN direction ENUM('credit','debit') NULL AFTER customer_id");
            }
            if (!hub_column_exists($db, 'customer_wallet_transactions', 'source_type')) {
                @v3_exec($db, "ALTER TABLE customer_wallet_transactions ADD COLUMN source_type VARCHAR(50) NULL AFTER balance_after");
            }
            if (!hub_column_exists($db, 'customer_wallet_transactions', 'note')) {
                @v3_exec($db, "ALTER TABLE customer_wallet_transactions ADD COLUMN note TEXT NULL AFTER source_ref");
            }
            if (!hub_column_exists($db, 'customer_wallet_transactions', 'created_by_admin_id')) {
                @v3_exec($db, "ALTER TABLE customer_wallet_transactions ADD COLUMN created_by_admin_id INT UNSIGNED NULL AFTER note");
            }
            @v3_exec($db, "UPDATE customer_wallet_transactions SET direction = CASE WHEN LOWER(COALESCE(txn_type,'')) IN ('debit','withdraw','withdrawal','refund_debit','chargeback') OR amount < 0 THEN 'debit' ELSE 'credit' END WHERE direction IS NULL");
            @v3_exec($db, "UPDATE customer_wallet_transactions SET source_type = source WHERE (source_type IS NULL OR source_type = '') AND source IS NOT NULL");
            @v3_exec($db, "UPDATE customer_wallet_transactions SET note = notes WHERE (note IS NULL OR note = '') AND notes IS NOT NULL");
        }

        if (hub_table_exists($db, 'customer_referrals')) {
            if (!hub_column_exists($db, 'customer_referrals', 'note')) {
                @v3_exec($db, "ALTER TABLE customer_referrals ADD COLUMN note VARCHAR(255) NULL AFTER reward_wallet_amount");
            }
            if (!hub_column_exists($db, 'customer_referrals', 'notes')) {
                @v3_exec($db, "ALTER TABLE customer_referrals ADD COLUMN notes VARCHAR(255) NULL AFTER note");
            }
            @v3_exec($db, "UPDATE customer_referrals SET note = notes WHERE (note IS NULL OR note = '') AND notes IS NOT NULL");
            @v3_exec($db, "UPDATE customer_referrals SET notes = note WHERE (notes IS NULL OR notes = '') AND note IS NOT NULL");
        }

        @v3_exec($db, "ALTER TABLE customers ADD UNIQUE KEY uq_customers_referral_code (referral_code)");
        @v3_exec($db, "ALTER TABLE customers ADD KEY idx_customers_tier_id (tier_id)");
        @v3_exec($db, "ALTER TABLE customers ADD KEY idx_customers_referred_by (referred_by_customer_id)");

        if ($ok) {
            $seeded = v3_seed_customer_referral_codes($db);
            if ($seeded > 0) {
                $notes[] = 'تم تثبيت معرفات إحالة بالاعتماد على الهاتف لـ ' . $seeded . ' زبون.';
            }
        }

        return ['ok' => $ok, 'notes' => $notes];
    }
}
