<?php

declare(strict_types=1);

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

function mkt_db(): ?mysqli
{
    return hub_db();
}

function mkt_h($value): string
{
    return hub_h($value);
}

function mkt_money(int|float|string|null $amount): string
{
    return number_format((float)$amount, 0) . ' د.ع';
}

function mkt_flash_class(string $type): string
{
    return $type === 'error' ? 'flash flash-error' : 'flash flash-success';
}

function mkt_table_exists(?mysqli $db, string $table): bool
{
    return hub_table_exists($db, $table);
}

function mkt_column_exists(?mysqli $db, string $table, string $column): bool
{
    return hub_column_exists($db, $table, $column);
}

function mkt_schema_ready(?mysqli $db): bool
{
    if (!$db) return false;
    $required = [
        'marketing_campaigns',
        'marketing_campaign_rules',
        'marketing_campaign_rewards',
        'marketing_campaign_runs',
        'marketing_campaign_logs',
        'marketing_campaign_exclusions',
    ];
    foreach ($required as $table) {
        if (!mkt_table_exists($db, $table)) {
            return false;
        }
    }
    return mkt_column_exists($db, 'promo_codes', 'assigned_customer_id')
        && mkt_column_exists($db, 'promo_codes', 'generated_by_campaign_id')
        && mkt_column_exists($db, 'promo_codes', 'is_generated')
        && mkt_column_exists($db, 'marketing_campaigns', 'cooldown_days')
        && mkt_column_exists($db, 'marketing_campaigns', 'cooldown_scope')
        && mkt_column_exists($db, 'marketing_campaigns', 'schedule_frequency')
        && mkt_column_exists($db, 'marketing_campaigns', 'schedule_every_hours')
        && mkt_column_exists($db, 'marketing_campaigns', 'schedule_weekday')
        && mkt_column_exists($db, 'marketing_campaigns', 'next_run_at')
        && mkt_column_exists($db, 'marketing_campaigns', 'trigger_event')
        && mkt_column_exists($db, 'marketing_campaign_logs', 'matched_reason')
        && mkt_column_exists($db, 'marketing_campaign_logs', 'cooldown_applied');
}

function mkt_campaign_types(): array
{
    return [
        'win_back' => 'استرجاع الزبائن غير النشطين',
        'first_order_activation' => 'تنشيط أول طلب',
        'post_purchase_bounce_back' => 'متابعة ما بعد الشراء',
        'tier_based' => 'حملة حسب المستوى',
        'special_access' => 'حملة وصول / عرض ذكي',
    ];
}

function mkt_audience_types(): array
{
    return [
        'all' => 'كل الزبائن',
        'inactive_customers' => 'زبائن غير نشطين',
        'zero_orders' => 'زبائن بدون طلبات',
        'specific_tier' => 'زبائن مستوى معيّن',
    ];
}

function mkt_reward_types(): array
{
    return [
        'personal_coupon' => 'كوبون شخصي',
        'bonus_points' => 'نقاط إضافية',
        'in_app_notification' => 'إشعار داخل التطبيق',
    ];
}

function mkt_channel_types(): array
{
    return [
        'direct_assign' => 'إسناد مباشر فقط',
        'in_app' => 'إشعار داخل التطبيق فقط',
        'both' => 'إسناد مباشر + إشعار',
    ];
}

function mkt_status_types(): array
{
    return [
        'draft' => 'مسودة',
        'active' => 'فعالة',
        'paused' => 'موقوفة',
        'archived' => 'مؤرشفة',
    ];
}

if (!function_exists('mkt_run_mode_types')) {
function mkt_run_mode_types(): array
{
    return [
        'manual' => 'يدوي',
        'scheduled' => 'مجدول',
        'triggered' => 'عند حدث',
    ];
}
}

if (!function_exists('mkt_schedule_frequency_types')) {
function mkt_schedule_frequency_types(): array
{
    return [
        'daily' => 'يومي',
        'weekly' => 'أسبوعي',
        'every_x_hours' => 'كل عدة ساعات',
    ];
}
}

if (!function_exists('mkt_schedule_weekday_types')) {
function mkt_schedule_weekday_types(): array
{
    return [
        '0' => 'الأحد',
        '1' => 'الاثنين',
        '2' => 'الثلاثاء',
        '3' => 'الأربعاء',
        '4' => 'الخميس',
        '5' => 'الجمعة',
        '6' => 'السبت',
    ];
}
}

if (!function_exists('mkt_trigger_event_types')) {
function mkt_trigger_event_types(): array
{
    return [
        'customer_registered' => 'عند تسجيل زبون جديد',
        'first_order_created' => 'عند إنشاء أول طلب',
        'order_delivered' => 'عند تسليم طلب',
        'tier_upgraded' => 'عند ترقية المستوى',
    ];
}
}

function mkt_trigger_event_label(string $event): string
{
    $map = mkt_trigger_event_types();
    return $map[$event] ?? $event;
}

function mkt_log_status_types(): array
{
    return [
        'executed' => 'تم التنفيذ',
        'skipped' => 'تم التخطي',
        'failed' => 'فشل التنفيذ',
    ];
}

function mkt_rule_labels(): array
{
    return [
        'inactive_days' => 'أيام عدم النشاط',
        'registration_days' => 'الأيام منذ التسجيل',
        'tier_id' => 'المستوى',
        'min_total_spent' => 'الحد الأدنى للصرف',
        'min_total_orders' => 'الحد الأدنى لعدد الطلبات',
        'max_total_orders' => 'الحد الأعلى لعدد الطلبات',
        'eligible_product_id' => 'مؤهل لمنتج ذكي',
        'eligible_threshold_promotion_id' => 'ضمن جمهور عرض حد أدنى',
    ];
}

function mkt_reward_labels(): array
{
    return [
        'coupon_discount_type' => 'نوع خصم الكوبون',
        'coupon_discount_value' => 'قيمة خصم الكوبون',
        'coupon_minimum_order_amount' => 'الحد الأدنى للطلب',
        'coupon_maximum_discount_amount' => 'الحد الأعلى للخصم',
        'coupon_valid_days' => 'صلاحية الكوبون بالأيام',
        'coupon_usage_limit' => 'عدد مرات الاستخدام',
        'points_amount' => 'عدد النقاط',
        'notification_title' => 'عنوان الإشعار',
        'notification_message' => 'نص الإشعار',
    ];
}


function mkt_exclusion_labels(): array
{
    return [
        'exclude_unverified' => 'استثناء غير الموثقين',
        'exclude_recent_pending_days' => 'استثناء من لديه طلب معلّق حديث',
        'exclude_tier_id' => 'استثناء مستوى معيّن',
        'exclude_customer_ids' => 'استثناء زبائن محددين',
    ];
}

function mkt_rule_label(string $key): string
{
    $map = mkt_rule_labels();
    return $map[$key] ?? $key;
}

function mkt_reward_label(string $key): string
{
    $map = mkt_reward_labels();
    return $map[$key] ?? $key;
}

function mkt_exclusion_label(string $key): string
{
    $map = mkt_exclusion_labels();
    return $map[$key] ?? $key;
}

function mkt_log_status_label(string $status): string
{
    $map = mkt_log_status_types();
    return $map[$status] ?? $status;
}

function mkt_discount_type_label(string $type): string
{
    return $type === 'fixed' ? 'مبلغ ثابت' : 'نسبة مئوية';
}

function mkt_dynamic_placeholders(): array
{
    return [
        '{name}' => ['label' => 'اسم الزبون', 'description' => 'يعرض اسم الزبون المستهدف.'],
        '{phone}' => ['label' => 'رقم الهاتف', 'description' => 'يعرض رقم هاتف الزبون.'],
        '{campaign}' => ['label' => 'اسم الحملة', 'description' => 'يعرض اسم الحملة الحالية.'],
        '{date}' => ['label' => 'تاريخ اليوم', 'description' => 'يعرض تاريخ اليوم الحالي.'],
        '{code}' => ['label' => 'كود الكوبون', 'description' => 'يعرض الكود الشخصي المولد من الحملة.'],
        '{value}' => ['label' => 'القيمة', 'description' => 'يعرض قيمة الخصم أو النقاط حسب المكافأة.'],
        '{type}' => ['label' => 'نوع الخصم', 'description' => 'يعرض هل الخصم نسبة أو مبلغ ثابت.'],
        '{min}' => ['label' => 'الحد الأدنى', 'description' => 'يعرض الحد الأدنى للطلب للاستفادة من الكوبون.'],
        '{expiry}' => ['label' => 'تاريخ الانتهاء', 'description' => 'يعرض موعد انتهاء صلاحية الكوبون.'],
        '{points}' => ['label' => 'عدد النقاط', 'description' => 'يعرض عدد النقاط المضافة من الحملة.'],
        '{balance}' => ['label' => 'رصيد النقاط', 'description' => 'يعرض رصيد النقاط الحالي أو المتوقع.'],
        '{tier}' => ['label' => 'المستوى', 'description' => 'يعرض اسم مستوى الزبون الحالي.'],
        '{last_order}' => ['label' => 'آخر طلب', 'description' => 'يعرض تاريخ آخر طلب تم تسليمه.'],
        '{orders}' => ['label' => 'عدد الطلبات', 'description' => 'يعرض إجمالي عدد الطلبات.'],
        '{spent}' => ['label' => 'إجمالي الصرف', 'description' => 'يعرض إجمالي صرف الزبون.'],
    ];
}

function mkt_reward_rule_defaults(): array
{
    return [
        'inactive_days' => '',
        'registration_days' => '',
        'tier_id' => '',
        'min_total_spent' => '',
        'min_total_orders' => '',
        'max_total_orders' => '',
        'eligible_product_id' => '',
        'eligible_threshold_promotion_id' => '',
        'exclude_unverified' => '0',
        'exclude_recent_pending_days' => '',
        'exclude_tier_id' => '',
        'exclude_customer_ids' => '',
        'coupon_discount_type' => 'percent',
        'coupon_discount_value' => '',
        'coupon_minimum_order_amount' => '',
        'coupon_maximum_discount_amount' => '',
        'coupon_valid_days' => '7',
        'coupon_usage_limit' => '1',
        'points_amount' => '',
        'notification_title' => '',
        'notification_message' => '',
    ];
}

function mkt_default_form(): array
{
    return array_merge([
        'name' => '',
        'code' => '',
        'description' => '',
        'campaign_type' => 'win_back',
        'audience_type' => 'all',
        'reward_type' => 'personal_coupon',
        'channel_type' => 'both',
        'status' => 'draft',
        'run_mode' => 'manual',
        'trigger_event' => 'customer_registered',
        'schedule_frequency' => 'daily',
        'schedule_every_hours' => '6',
        'schedule_weekday' => (string)date('w'),
        'next_run_at' => '',
        'allow_repeat' => '0',
        'repeat_after_days' => '',
        'cooldown_days' => '0',
        'cooldown_scope' => 'same_campaign',
        'starts_at' => '',
        'ends_at' => '',
        'priority' => '100',
        'notes' => '',
    ], mkt_reward_rule_defaults());
}

function mkt_form_from_campaign(?mysqli $db, ?array $campaign): array
{
    $form = mkt_default_form();
    if (!$campaign) return $form;
    foreach ($form as $key => $value) {
        if (array_key_exists($key, $campaign) && $campaign[$key] !== null) {
            $form[$key] = (string)$campaign[$key];
        }
    }
    if (!$db || empty($campaign['id'])) return $form;
    foreach (mkt_fetch_rules($db, (int)$campaign['id']) as $rule) {
        $key = (string)($rule['rule_key'] ?? '');
        if ($key !== '') {
            $form[$key] = (string)($rule['rule_value'] ?? '');
        }
    }
    foreach (mkt_fetch_rewards($db, (int)$campaign['id']) as $reward) {
        $key = (string)($reward['reward_key'] ?? '');
        if ($key !== '') {
            $form[$key] = (string)($reward['reward_value'] ?? '');
        }
    }
    foreach (mkt_fetch_exclusions($db, (int)$campaign['id']) as $row) {
        $key = (string)($row['exclusion_key'] ?? '');
        if ($key !== '') {
            $form[$key] = (string)($row['exclusion_value'] ?? '');
        }
    }
    return $form;
}

function mkt_fetch_tiers(?mysqli $db): array
{
    if (!$db || !mkt_table_exists($db, 'customer_tiers')) return [];
    return hub_fetch_all($db, "SELECT id, title, slug, color_hex FROM customer_tiers WHERE is_active = 1 ORDER BY sort_order ASC, id ASC");
}

function mkt_fetch_products_for_access(?mysqli $db): array
{
    if (!$db || !mkt_table_exists($db, 'products')) return [];
    $nameExpr = mkt_column_exists($db, 'products', 'name')
        ? '`name`'
        : (mkt_column_exists($db, 'products', 'title') ? '`title`' : "CONCAT('منتج #', id)");
    $statusExpr = mkt_column_exists($db, 'products', 'status') ? '`status`' : '1';
    $modeExpr = mkt_column_exists($db, 'products', 'access_mode') ? '`access_mode`' : "'all'";
    $scopeExpr = mkt_column_exists($db, 'products', 'access_scope') ? '`access_scope`' : "'always'";
    $subtotalExpr = mkt_column_exists($db, 'products', 'access_min_subtotal') ? '`access_min_subtotal`' : '0';
    $sql = "SELECT `id`, {$nameExpr} AS `name`, {$statusExpr} AS `status`, {$modeExpr} AS `access_mode`, {$scopeExpr} AS `access_scope`, {$subtotalExpr} AS `access_min_subtotal` FROM `products`";
    if (mkt_column_exists($db, 'products', 'status')) {
        $sql .= ' WHERE `status` = 1';
    }
    $sql .= ' ORDER BY `id` DESC';
    return hub_fetch_all($db, $sql);
}

function mkt_fetch_threshold_promotions_for_access(?mysqli $db): array
{
    if (!$db || !mkt_table_exists($db, 'threshold_promotions')) return [];
    $titleExpr = mkt_column_exists($db, 'threshold_promotions', 'title') ? '`title`' : "CONCAT('عرض #', id)";
    $statusExpr = mkt_column_exists($db, 'threshold_promotions', 'status') ? '`status`' : '1';
    $audienceExpr = mkt_column_exists($db, 'threshold_promotions', 'audience_mode') ? '`audience_mode`' : "'all'";
    $subtotalExpr = mkt_column_exists($db, 'threshold_promotions', 'min_subtotal') ? '`min_subtotal`' : '0';
    $priorityExpr = mkt_column_exists($db, 'threshold_promotions', 'priority') ? '`priority`' : '0';
    $sql = "SELECT `id`, {$titleExpr} AS `title`, {$statusExpr} AS `status`, {$audienceExpr} AS `audience_mode`, {$subtotalExpr} AS `min_subtotal`, {$priorityExpr} AS `priority` FROM `threshold_promotions`";
    if (mkt_column_exists($db, 'threshold_promotions', 'status')) {
        $sql .= ' WHERE `status` = 1';
    }
    $sql .= ' ORDER BY `priority` ASC, `id` DESC';
    return hub_fetch_all($db, $sql);
}

function mkt_fetch_product_for_access(?mysqli $db, int $productId): ?array
{
    if ($productId <= 0) return null;
    foreach (mkt_fetch_products_for_access($db) as $row) {
        if ((int)($row['id'] ?? 0) === $productId) return $row;
    }
    return null;
}

function mkt_fetch_threshold_promotion_for_access(?mysqli $db, int $promotionId): ?array
{
    if ($promotionId <= 0) return null;
    foreach (mkt_fetch_threshold_promotions_for_access($db) as $row) {
        if ((int)($row['id'] ?? 0) === $promotionId) return $row;
    }
    return null;
}

function mkt_fetch_product_access_tier_ids(?mysqli $db, int $productId): array
{
    if (!$db || $productId <= 0 || !mkt_table_exists($db, 'product_access_tiers')) return [];
    $rows = hub_fetch_all($db, 'SELECT tier_id FROM product_access_tiers WHERE product_id = ' . $productId . ' ORDER BY tier_id ASC');
    return array_values(array_unique(array_map(static fn($row): int => (int)($row['tier_id'] ?? 0), $rows)));
}

function mkt_fetch_threshold_promotion_audience_tier_ids(?mysqli $db, int $promotionId): array
{
    if (!$db || $promotionId <= 0 || !mkt_table_exists($db, 'threshold_promotion_tiers')) return [];
    $rows = hub_fetch_all($db, 'SELECT tier_id FROM threshold_promotion_tiers WHERE promotion_id = ' . $promotionId . ' ORDER BY tier_id ASC');
    return array_values(array_unique(array_map(static fn($row): int => (int)($row['tier_id'] ?? 0), $rows)));
}

function mkt_customer_matches_product_access(array $customer, ?array $product, array $tierIds = []): bool
{
    if (!$product) return false;
    $mode = (string)($product['access_mode'] ?? 'all');
    $customerTierId = (int)($customer['tier_id'] ?? 0);
    if (!in_array($mode, ['all', 'tiers', 'subtotal', 'tiers_or_subtotal', 'tiers_and_subtotal'], true)) {
        $mode = 'all';
    }
    if ($mode === 'all') {
        return true;
    }
    if ($mode === 'tiers') {
        return !empty($tierIds) && in_array($customerTierId, $tierIds, true);
    }
    if ($mode === 'tiers_or_subtotal') {
        return !empty($tierIds) && in_array($customerTierId, $tierIds, true);
    }
    if ($mode === 'subtotal' || $mode === 'tiers_and_subtotal') {
        return false;
    }
    return false;
}

function mkt_customer_matches_threshold_promotion_audience(array $customer, ?array $promotion, array $tierIds = []): bool
{
    if (!$promotion) return false;
    $mode = (string)($promotion['audience_mode'] ?? 'all');
    $customerTierId = (int)($customer['tier_id'] ?? 0);
    if ($mode === 'tiers') {
        return !empty($tierIds) && in_array($customerTierId, $tierIds, true);
    }
    return true;
}

function mkt_fetch_stats(?mysqli $db): array
{
    if (!$db) {
        return [
            'campaigns' => 0,
            'active_campaigns' => 0,
            'scheduled_campaigns' => 0,
            'due_now_campaigns' => 0,
            'runs' => 0,
            'executed_logs' => 0,
            'generated_coupons' => 0,
            'notifications_sent' => 0,
            'points_added' => 0,
            'top_success_name' => '',
            'top_success_count' => 0,
            'top_failed_name' => '',
            'top_failed_count' => 0,
        ];
    }

    $stats = [
        'campaigns' => mkt_table_exists($db, 'marketing_campaigns') ? hub_table_count($db, 'marketing_campaigns') : 0,
        'active_campaigns' => mkt_table_exists($db, 'marketing_campaigns') ? hub_table_count($db, 'marketing_campaigns', "status = 'active'") : 0,
        'scheduled_campaigns' => mkt_table_exists($db, 'marketing_campaigns') ? hub_table_count($db, 'marketing_campaigns', "run_mode = 'scheduled'") : 0,
        'due_now_campaigns' => mkt_table_exists($db, 'marketing_campaigns') && mkt_column_exists($db, 'marketing_campaigns', 'next_run_at') ? hub_fetch_value($db, "SELECT COUNT(*) AS c FROM marketing_campaigns WHERE run_mode = 'scheduled' AND status = 'active' AND next_run_at IS NOT NULL AND next_run_at <= NOW()") : 0,
        'runs' => mkt_table_exists($db, 'marketing_campaign_runs') ? hub_table_count($db, 'marketing_campaign_runs') : 0,
        'executed_logs' => mkt_table_exists($db, 'marketing_campaign_logs') ? hub_table_count($db, 'marketing_campaign_logs', 'executed = 1') : 0,
        'generated_coupons' => mkt_column_exists($db, 'promo_codes', 'is_generated') ? hub_fetch_value($db, "SELECT COUNT(*) AS c FROM promo_codes WHERE COALESCE(is_generated,0) = 1") : 0,
        'notifications_sent' => 0,
        'points_added' => 0,
        'top_success_name' => '',
        'top_success_count' => 0,
        'top_failed_name' => '',
        'top_failed_count' => 0,
    ];

    if (mkt_table_exists($db, 'marketing_campaign_logs') && mkt_table_exists($db, 'marketing_campaigns')) {
        $stats['notifications_sent'] = (int)hub_fetch_value($db, "SELECT COUNT(*) AS c FROM marketing_campaign_logs l INNER JOIN marketing_campaigns c ON c.id = l.campaign_id WHERE l.executed = 1 AND (c.channel_type IN ('in_app','both') OR c.reward_type = 'in_app_notification')");
        $topSuccess = hub_fetch_all($db, "SELECT c.name, COUNT(*) AS c FROM marketing_campaign_logs l INNER JOIN marketing_campaigns c ON c.id = l.campaign_id WHERE l.executed = 1 GROUP BY l.campaign_id, c.name ORDER BY c DESC, l.campaign_id DESC LIMIT 1");
        if (!empty($topSuccess[0])) {
            $stats['top_success_name'] = (string)($topSuccess[0]['name'] ?? '');
            $stats['top_success_count'] = (int)($topSuccess[0]['c'] ?? 0);
        }
        $topFailed = hub_fetch_all($db, "SELECT c.name, COUNT(*) AS c FROM marketing_campaign_logs l INNER JOIN marketing_campaigns c ON c.id = l.campaign_id WHERE l.status_text = 'failed' GROUP BY l.campaign_id, c.name ORDER BY c DESC, l.campaign_id DESC LIMIT 1");
        if (!empty($topFailed[0])) {
            $stats['top_failed_name'] = (string)($topFailed[0]['name'] ?? '');
            $stats['top_failed_count'] = (int)($topFailed[0]['c'] ?? 0);
        }
    }

    if (mkt_table_exists($db, 'customer_loyalty_transactions')) {
        $stats['points_added'] = (int)hub_fetch_value($db, "SELECT COALESCE(SUM(CASE WHEN points_delta > 0 THEN points_delta ELSE 0 END),0) AS c FROM customer_loyalty_transactions WHERE txn_type = 'marketing_campaign'");
    }

    return $stats;
}

function mkt_fetch_campaigns(?mysqli $db, int $limit = 100): array
{
    if (!$db || !mkt_table_exists($db, 'marketing_campaigns')) return [];
    $limit = max(1, min(500, $limit));
    return hub_fetch_all($db, "SELECT c.*, a.name AS created_by_name,
        (SELECT COUNT(*) FROM marketing_campaign_runs r WHERE r.campaign_id = c.id) AS runs_count,
        (SELECT COUNT(*) FROM marketing_campaign_logs l WHERE l.campaign_id = c.id AND l.executed = 1) AS executed_count
        FROM marketing_campaigns c
        LEFT JOIN admins a ON a.id = c.created_by
        ORDER BY c.priority ASC, c.id DESC
        LIMIT {$limit}");
}

function mkt_fetch_campaign(?mysqli $db, int $campaignId): ?array
{
    if (!$db || $campaignId <= 0 || !mkt_table_exists($db, 'marketing_campaigns')) return null;
    $stmt = $db->prepare("SELECT * FROM marketing_campaigns WHERE id = ? LIMIT 1");
    if (!$stmt) return null;
    $stmt->bind_param('i', $campaignId);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc() ?: null;
    $stmt->close();
    return $row;
}

function mkt_fetch_rules(?mysqli $db, int $campaignId): array
{
    if (!$db || $campaignId <= 0 || !mkt_table_exists($db, 'marketing_campaign_rules')) return [];
    $stmt = $db->prepare("SELECT * FROM marketing_campaign_rules WHERE campaign_id = ? AND is_active = 1 ORDER BY sort_order ASC, id ASC");
    if (!$stmt) return [];
    $stmt->bind_param('i', $campaignId);
    $stmt->execute();
    $rows = [];
    $res = $stmt->get_result();
    while ($row = $res->fetch_assoc()) {
        $rows[] = $row;
    }
    $stmt->close();
    return $rows;
}

function mkt_fetch_rewards(?mysqli $db, int $campaignId): array
{
    if (!$db || $campaignId <= 0 || !mkt_table_exists($db, 'marketing_campaign_rewards')) return [];
    $stmt = $db->prepare("SELECT * FROM marketing_campaign_rewards WHERE campaign_id = ? AND is_active = 1 ORDER BY sort_order ASC, id ASC");
    if (!$stmt) return [];
    $stmt->bind_param('i', $campaignId);
    $stmt->execute();
    $rows = [];
    $res = $stmt->get_result();
    while ($row = $res->fetch_assoc()) {
        $rows[] = $row;
    }
    $stmt->close();
    return $rows;
}

function mkt_fetch_exclusions(?mysqli $db, int $campaignId): array
{
    if (!$db || $campaignId <= 0 || !mkt_table_exists($db, 'marketing_campaign_exclusions')) return [];
    $stmt = $db->prepare("SELECT * FROM marketing_campaign_exclusions WHERE campaign_id = ? AND is_active = 1 ORDER BY sort_order ASC, id ASC");
    if (!$stmt) return [];
    $stmt->bind_param('i', $campaignId);
    $stmt->execute();
    $rows = [];
    $res = $stmt->get_result();
    while ($row = $res->fetch_assoc()) {
        $rows[] = $row;
    }
    $stmt->close();
    return $rows;
}

function mkt_fetch_runs(?mysqli $db, int $campaignId = 0, int $limit = 50): array
{
    if (!$db || !mkt_table_exists($db, 'marketing_campaign_runs')) return [];
    $limit = max(1, min(200, $limit));
    $where = $campaignId > 0 ? 'WHERE r.campaign_id = ' . (int)$campaignId : '';
    return hub_fetch_all($db, "SELECT r.*, c.name AS campaign_name FROM marketing_campaign_runs r LEFT JOIN marketing_campaigns c ON c.id = r.campaign_id {$where} ORDER BY r.id DESC LIMIT {$limit}");
}

function mkt_fetch_logs(?mysqli $db, int $campaignId = 0, int $limit = 100): array
{
    if (!$db || !mkt_table_exists($db, 'marketing_campaign_logs')) return [];
    $limit = max(1, min(500, $limit));
    $where = $campaignId > 0 ? 'WHERE l.campaign_id = ' . (int)$campaignId : '';
    return hub_fetch_all($db, "SELECT l.*, c.name AS campaign_name, r.run_type, cu.full_name, cu.phone, t.title AS tier_title
        FROM marketing_campaign_logs l
        LEFT JOIN marketing_campaigns c ON c.id = l.campaign_id
        LEFT JOIN marketing_campaign_runs r ON r.id = l.run_id
        LEFT JOIN customers cu ON cu.id = l.customer_id
        LEFT JOIN customer_tiers t ON t.id = cu.tier_id
        {$where}
        ORDER BY l.id DESC LIMIT {$limit}");
}

function mkt_generate_internal_code(string $name): string
{
    $slug = preg_replace('/[^a-zA-Z0-9]+/', '_', strtolower(trim($name)));
    $slug = trim((string)$slug, '_');
    if ($slug === '') {
        $slug = 'campaign';
    }
    return strtoupper(substr($slug, 0, 40) . '_' . date('Ymd_His'));
}

function mkt_collect_form_data(array $source): array
{
    $form = mkt_default_form();
    foreach ($form as $key => $default) {
        $form[$key] = trim((string)($source[$key] ?? $default));
    }
    $form['allow_repeat'] = !empty($source['allow_repeat']) ? '1' : '0';
    $form['exclude_unverified'] = !empty($source['exclude_unverified']) ? '1' : '0';
    if ($form['code'] === '' && $form['name'] !== '') {
        $form['code'] = mkt_generate_internal_code($form['name']);
    }
    if ($form['cooldown_scope'] === '') {
        $form['cooldown_scope'] = 'same_campaign';
    }
    if ($form['run_mode'] === '') {
        $form['run_mode'] = 'manual';
    }
    if ($form['trigger_event'] === '') {
        $form['trigger_event'] = 'customer_registered';
    }
    if ($form['schedule_frequency'] === '') {
        $form['schedule_frequency'] = 'daily';
    }
    if ($form['schedule_weekday'] === '') {
        $form['schedule_weekday'] = (string)date('w');
    }
    if ($form['schedule_every_hours'] === '') {
        $form['schedule_every_hours'] = '6';
    }
    return $form;
}

function mkt_validate_form(?mysqli $db, array $form, int $campaignId = 0): array
{
    $errors = [];
    if (!$db) {
        $errors[] = 'اتصال قاعدة البيانات غير متوفر.';
        return $errors;
    }
    if (!mkt_schema_ready($db)) {
        $errors[] = 'نفذ ملف SQL الخاص بالمرحلة الثانية أولاً ثم أعد المحاولة.';
        return $errors;
    }
    if ($form['name'] === '') $errors[] = 'اسم الحملة مطلوب.';
    if ($form['code'] === '') $errors[] = 'الكود الداخلي مطلوب.';
    if (!array_key_exists($form['campaign_type'], mkt_campaign_types())) $errors[] = 'نوع الحملة غير صالح.';
    if (!array_key_exists($form['audience_type'], mkt_audience_types())) $errors[] = 'نوع الجمهور غير صالح.';
    if (!array_key_exists($form['reward_type'], mkt_reward_types())) $errors[] = 'نوع المكافأة غير صالح.';
    if (!array_key_exists($form['channel_type'], mkt_channel_types())) $errors[] = 'قناة التنفيذ غير صالحة.';
    if (!array_key_exists($form['status'], mkt_status_types())) $errors[] = 'حالة الحملة غير صالحة.';
    if (!array_key_exists($form['run_mode'], mkt_run_mode_types())) $errors[] = 'نوع التشغيل غير صالح.';
    if (!in_array($form['cooldown_scope'], ['same_campaign', 'same_reward_type', 'any_campaign'], true)) $errors[] = 'نطاق فترة التهدئة غير صالح.';
    if ($form['run_mode'] === 'scheduled') {
        if (!array_key_exists($form['schedule_frequency'], mkt_schedule_frequency_types())) $errors[] = 'نوع الجدولة غير صالح.';
        if ($form['schedule_frequency'] === 'every_x_hours' && (int)$form['schedule_every_hours'] <= 0) $errors[] = 'عدد الساعات مطلوب عند اختيار الجدولة كل عدة ساعات.';
        if ($form['schedule_frequency'] === 'weekly' && !array_key_exists((string)$form['schedule_weekday'], mkt_schedule_weekday_types())) $errors[] = 'اختر يوم الأسبوع للجدولة الأسبوعية.';
    }
    if ($form['run_mode'] === 'triggered' && !array_key_exists($form['trigger_event'], mkt_trigger_event_types())) {
        $errors[] = 'نوع الحدث المحفّز غير صالح.';
    }
    if ($form['audience_type'] === 'inactive_customers' && (int)$form['inactive_days'] <= 0) {
        $errors[] = 'عدد أيام عدم النشاط مطلوب لهذه الفئة.';
    }
    if ($form['audience_type'] === 'specific_tier' && (int)$form['tier_id'] <= 0) {
        $errors[] = 'اختيار المستوى مطلوب.';
    }
    if ((int)$form['min_total_orders'] > 0 && (int)$form['max_total_orders'] > 0 && (int)$form['min_total_orders'] > (int)$form['max_total_orders']) {
        $errors[] = 'الحد الأدنى لعدد الطلبات لا يمكن أن يكون أكبر من الحد الأعلى.';
    }
    if ((int)$form['eligible_product_id'] > 0 && !mkt_fetch_product_for_access($db, (int)$form['eligible_product_id'])) {
        $errors[] = 'المنتج الذكي المحدد غير موجود أو غير فعّال.';
    }
    if ((int)$form['eligible_threshold_promotion_id'] > 0 && !mkt_fetch_threshold_promotion_for_access($db, (int)$form['eligible_threshold_promotion_id'])) {
        $errors[] = 'عرض الحد الأدنى المحدد غير موجود أو غير فعّال.';
    }
    if ($form['starts_at'] !== '' && $form['ends_at'] !== '' && strtotime(str_replace('T', ' ', $form['starts_at'])) > strtotime(str_replace('T', ' ', $form['ends_at']))) {
        $errors[] = 'تاريخ البداية يجب أن يسبق تاريخ النهاية.';
    }
    if ($form['reward_type'] === 'personal_coupon') {
        if ((float)$form['coupon_discount_value'] <= 0) {
            $errors[] = 'قيمة الخصم للكوبون مطلوبة.';
        }
        if (!in_array($form['coupon_discount_type'], ['percent', 'fixed'], true)) {
            $errors[] = 'نوع خصم الكوبون غير صالح.';
        }
    }
    if ($form['reward_type'] === 'bonus_points' && (int)$form['points_amount'] <= 0) {
        $errors[] = 'عدد النقاط مطلوب.';
    }
    if ($form['reward_type'] === 'in_app_notification' && $form['notification_title'] === '' && $form['notification_message'] === '') {
        $errors[] = 'عنوان أو نص الإشعار مطلوب.';
    }
    $stmt = $db->prepare('SELECT id FROM marketing_campaigns WHERE code = ? AND id <> ? LIMIT 1');
    if ($stmt) {
        $stmt->bind_param('si', $form['code'], $campaignId);
        $stmt->execute();
        if ($stmt->get_result()->fetch_assoc()) {
            $errors[] = 'الكود الداخلي مستخدم سابقاً.';
        }
        $stmt->close();
    }
    return $errors;
}

function mkt_insert_rule(mysqli $db, int $campaignId, string $key, string $value, int $sortOrder): void
{
    $value = trim($value);
    if ($campaignId <= 0 || $key === '' || $value === '') return;
    $operator = '=';
    if (in_array($key, ['min_total_spent', 'min_total_orders'], true)) {
        $operator = '>=';
    } elseif (in_array($key, ['max_total_orders'], true)) {
        $operator = '<=';
    } elseif (in_array($key, ['eligible_product_id', 'eligible_threshold_promotion_id'], true)) {
        $operator = '=';
    }
    $stmt = $db->prepare('INSERT INTO marketing_campaign_rules (campaign_id, rule_key, operator, rule_value, secondary_value, sort_order, is_active, created_at, updated_at) VALUES (?, ?, ?, ?, NULL, ?, 1, NOW(), NOW())');
    if (!$stmt) return;
    $stmt->bind_param('isssi', $campaignId, $key, $operator, $value, $sortOrder);
    $stmt->execute();
    $stmt->close();
}

function mkt_insert_reward(mysqli $db, int $campaignId, string $key, string $value, int $sortOrder, array $meta = []): void
{
    $value = trim($value);
    if ($campaignId <= 0 || $key === '' || $value === '') return;
    $json = $meta ? json_encode($meta, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) : null;
    $stmt = $db->prepare('INSERT INTO marketing_campaign_rewards (campaign_id, reward_key, reward_value, metadata_json, sort_order, is_active, created_at, updated_at) VALUES (?, ?, ?, ?, ?, 1, NOW(), NOW())');
    if (!$stmt) return;
    $stmt->bind_param('isssi', $campaignId, $key, $value, $json, $sortOrder);
    $stmt->execute();
    $stmt->close();
}

function mkt_insert_exclusion(mysqli $db, int $campaignId, string $key, string $value, int $sortOrder): void
{
    $value = trim($value);
    if ($campaignId <= 0 || $key === '' || $value === '') return;
    $stmt = $db->prepare("INSERT INTO marketing_campaign_exclusions (campaign_id, exclusion_key, operator, exclusion_value, secondary_value, sort_order, is_active, created_at, updated_at) VALUES (?, ?, '=', ?, NULL, ?, 1, NOW(), NOW())");
    if (!$stmt) return;
    $stmt->bind_param('issi', $campaignId, $key, $value, $sortOrder);
    $stmt->execute();
    $stmt->close();
}

function mkt_save_rules_and_rewards(mysqli $db, int $campaignId, array $form): void
{
    $db->query('DELETE FROM marketing_campaign_rules WHERE campaign_id = ' . (int)$campaignId);
    $db->query('DELETE FROM marketing_campaign_rewards WHERE campaign_id = ' . (int)$campaignId);
    if (mkt_table_exists($db, 'marketing_campaign_exclusions')) {
        $db->query('DELETE FROM marketing_campaign_exclusions WHERE campaign_id = ' . (int)$campaignId);
    }

    $sort = 1;
    foreach (['inactive_days', 'registration_days', 'tier_id', 'min_total_spent', 'min_total_orders', 'max_total_orders', 'eligible_product_id', 'eligible_threshold_promotion_id'] as $ruleKey) {
        mkt_insert_rule($db, $campaignId, $ruleKey, (string)$form[$ruleKey], $sort++);
    }

    $sort = 1;
    foreach (['exclude_unverified', 'exclude_recent_pending_days', 'exclude_tier_id', 'exclude_customer_ids'] as $exclusionKey) {
        mkt_insert_exclusion($db, $campaignId, $exclusionKey, (string)$form[$exclusionKey], $sort++);
    }

    if ($form['reward_type'] === 'personal_coupon') {
        mkt_insert_reward($db, $campaignId, 'coupon_discount_type', (string)$form['coupon_discount_type'], 1);
        mkt_insert_reward($db, $campaignId, 'coupon_discount_value', (string)$form['coupon_discount_value'], 2);
        mkt_insert_reward($db, $campaignId, 'coupon_minimum_order_amount', (string)$form['coupon_minimum_order_amount'], 3);
        mkt_insert_reward($db, $campaignId, 'coupon_maximum_discount_amount', (string)$form['coupon_maximum_discount_amount'], 4);
        mkt_insert_reward($db, $campaignId, 'coupon_valid_days', (string)$form['coupon_valid_days'], 5);
        mkt_insert_reward($db, $campaignId, 'coupon_usage_limit', (string)$form['coupon_usage_limit'], 6);
        mkt_insert_reward($db, $campaignId, 'notification_title', (string)$form['notification_title'], 7);
        mkt_insert_reward($db, $campaignId, 'notification_message', (string)$form['notification_message'], 8);
    } elseif ($form['reward_type'] === 'bonus_points') {
        mkt_insert_reward($db, $campaignId, 'points_amount', (string)$form['points_amount'], 1);
        mkt_insert_reward($db, $campaignId, 'notification_title', (string)$form['notification_title'], 2);
        mkt_insert_reward($db, $campaignId, 'notification_message', (string)$form['notification_message'], 3);
    } else {
        mkt_insert_reward($db, $campaignId, 'notification_title', (string)$form['notification_title'], 1);
        mkt_insert_reward($db, $campaignId, 'notification_message', (string)$form['notification_message'], 2);
    }
}

function mkt_form_datetime_to_mysql(string $value): ?string
{
    $value = trim($value);
    if ($value === '') return null;
    if (strlen($value) === 16) {
        return str_replace('T', ' ', $value) . ':00';
    }
    return str_replace('T', ' ', $value);
}

function mkt_mysql_to_local_input(?string $value): string
{
    $value = trim((string)$value);
    if ($value === '') return '';
    return str_replace(' ', 'T', substr($value, 0, 16));
}

function mkt_datetime_ts(?string $value): ?int
{
    $value = trim((string)$value);
    if ($value === '') return null;
    $ts = strtotime($value);
    return $ts !== false ? $ts : null;
}

function mkt_next_weekday_at(int $targetWeekday, int $baseTs, string $timeString): string
{
    $targetWeekday = max(0, min(6, $targetWeekday));
    $parts = array_map('intval', explode(':', $timeString));
    $hour = $parts[0] ?? 9;
    $minute = $parts[1] ?? 0;
    $second = $parts[2] ?? 0;
    $currentWeekday = (int)date('w', $baseTs);
    $candidate = mktime($hour, $minute, $second, (int)date('n', $baseTs), (int)date('j', $baseTs), (int)date('Y', $baseTs));
    $delta = ($targetWeekday - $currentWeekday + 7) % 7;
    if ($delta === 0 && $candidate <= $baseTs) {
        $delta = 7;
    }
    if ($delta > 0) {
        $candidate = strtotime('+' . $delta . ' day', $candidate);
    }
    return date('Y-m-d H:i:s', $candidate);
}

function mkt_compute_next_run_at(array $data, ?int $fromTs = null, bool $afterExecution = false): ?string
{
    if (($data['run_mode'] ?? 'manual') !== 'scheduled') {
        return null;
    }
    $nowTs = $fromTs ?: time();
    $startTs = mkt_datetime_ts((string)($data['starts_at'] ?? ''));
    $frequency = (string)($data['schedule_frequency'] ?? 'daily');
    $timeString = $startTs ? date('H:i:s', $startTs) : date('H:i:s', $nowTs);

    if (!$afterExecution) {
        if ($startTs && $startTs > $nowTs) {
            return date('Y-m-d H:i:s', $startTs);
        }
        if ($frequency === 'weekly') {
            $weekday = array_key_exists((string)($data['schedule_weekday'] ?? ''), mkt_schedule_weekday_types()) ? (int)$data['schedule_weekday'] : (int)date('w', $nowTs);
            return mkt_next_weekday_at($weekday, $nowTs - 60, $timeString);
        }
        return date('Y-m-d H:i:s', $nowTs);
    }

    if ($frequency === 'every_x_hours') {
        $hours = max(1, (int)($data['schedule_every_hours'] ?? 1));
        return date('Y-m-d H:i:s', strtotime('+' . $hours . ' hour', $nowTs));
    }
    if ($frequency === 'weekly') {
        $weekday = array_key_exists((string)($data['schedule_weekday'] ?? ''), mkt_schedule_weekday_types()) ? (int)$data['schedule_weekday'] : (int)date('w', $nowTs);
        return mkt_next_weekday_at($weekday, $nowTs + 60, $timeString);
    }
    return date('Y-m-d H:i:s', strtotime('+1 day', $nowTs));
}

function mkt_schedule_summary(array $campaign): string
{
    if (($campaign['run_mode'] ?? 'manual') === 'triggered') {
        return 'يتشغّل عند الحدث: ' . mkt_trigger_event_label((string)($campaign['trigger_event'] ?? ''));
    }
    if (($campaign['run_mode'] ?? 'manual') !== 'scheduled') {
        return 'يدوي فقط';
    }
    $frequency = (string)($campaign['schedule_frequency'] ?? 'daily');
    if ($frequency === 'every_x_hours') {
        return 'كل ' . max(1, (int)($campaign['schedule_every_hours'] ?? 1)) . ' ساعة';
    }
    if ($frequency === 'weekly') {
        $weekdayMap = mkt_schedule_weekday_types();
        $weekday = $weekdayMap[(string)($campaign['schedule_weekday'] ?? '')] ?? 'أسبوعي';
        return 'أسبوعي / ' . $weekday;
    }
    return 'يومي';
}

function mkt_next_run_label(array $campaign): string
{
    if (($campaign['run_mode'] ?? 'manual') === 'triggered') {
        return 'بانتظار الحدث المحفّز';
    }
    if (($campaign['run_mode'] ?? 'manual') !== 'scheduled') {
        return '—';
    }
    $next = trim((string)($campaign['next_run_at'] ?? ''));
    return $next !== '' ? $next : 'بانتظار أول جدولة';
}

function mkt_fetch_due_scheduled_campaigns(?mysqli $db, int $limit = 25): array
{
    if (!$db || !mkt_table_exists($db, 'marketing_campaigns') || !mkt_column_exists($db, 'marketing_campaigns', 'next_run_at')) return [];
    $limit = max(1, min(200, $limit));
    return hub_fetch_all($db, "SELECT * FROM marketing_campaigns WHERE run_mode = 'scheduled' AND status = 'active' AND next_run_at IS NOT NULL AND next_run_at <= NOW() AND (starts_at IS NULL OR starts_at <= NOW()) AND (ends_at IS NULL OR ends_at >= NOW()) ORDER BY priority ASC, next_run_at ASC, id ASC LIMIT {$limit}");
}

function mkt_run_due_campaigns(mysqli $db, int $adminId = 0, int $limit = 25): array
{
    $campaigns = mkt_fetch_due_scheduled_campaigns($db, $limit);
    $result = ['total_due' => count($campaigns), 'ran' => 0, 'failed' => 0, 'messages' => []];
    foreach ($campaigns as $campaign) {
        [$ok, $message, $summary] = mkt_run_campaign($db, (int)$campaign['id'], $adminId, 'scheduled');
        if ($ok) {
            $result['ran']++;
            $result['messages'][] = '#' . (int)$campaign['id'] . ' ' . (string)$campaign['name'] . ' => executed=' . (int)($summary['executed'] ?? 0) . ', skipped=' . (int)($summary['skipped'] ?? 0) . ', failed=' . (int)($summary['failed'] ?? 0);
        } else {
            $result['failed']++;
            $result['messages'][] = '#' . (int)$campaign['id'] . ' ' . (string)$campaign['name'] . ' => ' . $message;
        }
    }
    return $result;
}

function mkt_create_campaign(mysqli $db, array $source, int $adminId = 0): array
{
    $form = mkt_collect_form_data($source);
    $errors = mkt_validate_form($db, $form, 0);
    if ($errors) return [false, $errors, $form, 0];

    $stmt = $db->prepare('INSERT INTO marketing_campaigns (name, code, description, campaign_type, audience_type, reward_type, channel_type, status, run_mode, trigger_event, schedule_frequency, schedule_every_hours, schedule_weekday, allow_repeat, repeat_after_days, cooldown_days, cooldown_scope, starts_at, ends_at, priority, next_run_at, notes, created_by, updated_by, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())');
    if (!$stmt) {
        return [false, ['تعذر تجهيز عملية إنشاء الحملة.'], $form, 0];
    }
    $runMode = (string)$form['run_mode'];
    $triggerEvent = $runMode === 'triggered' ? (string)$form['trigger_event'] : null;
    $scheduleFrequency = $runMode === 'scheduled' ? (string)$form['schedule_frequency'] : null;
    $scheduleEveryHours = $runMode === 'scheduled' && $form['schedule_frequency'] === 'every_x_hours' ? max(1, (int)$form['schedule_every_hours']) : null;
    $scheduleWeekday = $runMode === 'scheduled' && $form['schedule_frequency'] === 'weekly' ? (int)$form['schedule_weekday'] : null;
    $repeatAfter = $form['repeat_after_days'] === '' ? 0 : (int)$form['repeat_after_days'];
    $startsAt = mkt_form_datetime_to_mysql((string)$form['starts_at']);
    $endsAt = mkt_form_datetime_to_mysql((string)$form['ends_at']);
    $allowRepeat = (int)$form['allow_repeat'];
    $cooldownDays = max(0, (int)($form['cooldown_days'] === '' ? 0 : $form['cooldown_days']));
    $cooldownScope = (string)$form['cooldown_scope'];
    $priority = (int)$form['priority'];
    $nextRunAt = $runMode === 'scheduled' ? mkt_compute_next_run_at(['run_mode' => $runMode, 'schedule_frequency' => $scheduleFrequency, 'schedule_every_hours' => $scheduleEveryHours, 'schedule_weekday' => $scheduleWeekday, 'starts_at' => $startsAt]) : null;
    $stmt->bind_param('sssssssssssiiiiisssissii', $form['name'], $form['code'], $form['description'], $form['campaign_type'], $form['audience_type'], $form['reward_type'], $form['channel_type'], $form['status'], $runMode, $triggerEvent, $scheduleFrequency, $scheduleEveryHours, $scheduleWeekday, $allowRepeat, $repeatAfter, $cooldownDays, $cooldownScope, $startsAt, $endsAt, $priority, $nextRunAt, $form['notes'], $adminId, $adminId);
    $ok = $stmt->execute();
    $campaignId = $ok ? (int)$stmt->insert_id : 0;
    $stmt->close();
    if (!$ok || $campaignId <= 0) {
        return [false, ['تعذر حفظ الحملة.'], $form, 0];
    }
    mkt_save_rules_and_rewards($db, $campaignId, $form);
    if (function_exists('admin_log_activity')) {
        admin_log_activity('marketing.campaign.create', 'إنشاء حملة تسويقية', 'تم إنشاء حملة جديدة.', 'marketing_campaign', $campaignId, ['code' => $form['code'], 'reward_type' => $form['reward_type'], 'run_mode' => $runMode]);
    }
    return [true, [], $form, $campaignId];
}

function mkt_update_campaign(mysqli $db, int $campaignId, array $source, int $adminId = 0): array
{
    $form = mkt_collect_form_data($source);
    $errors = mkt_validate_form($db, $form, $campaignId);
    if ($errors) return [false, $errors, $form];

    $stmt = $db->prepare('UPDATE marketing_campaigns SET name = ?, code = ?, description = ?, campaign_type = ?, audience_type = ?, reward_type = ?, channel_type = ?, status = ?, run_mode = ?, trigger_event = ?, schedule_frequency = ?, schedule_every_hours = ?, schedule_weekday = ?, allow_repeat = ?, repeat_after_days = ?, cooldown_days = ?, cooldown_scope = ?, starts_at = ?, ends_at = ?, priority = ?, next_run_at = ?, notes = ?, updated_by = ?, updated_at = NOW() WHERE id = ? LIMIT 1');
    if (!$stmt) {
        return [false, ['تعذر تجهيز عملية تحديث الحملة.'], $form];
    }
    $runMode = (string)$form['run_mode'];
    $triggerEvent = $runMode === 'triggered' ? (string)$form['trigger_event'] : null;
    $scheduleFrequency = $runMode === 'scheduled' ? (string)$form['schedule_frequency'] : null;
    $scheduleEveryHours = $runMode === 'scheduled' && $form['schedule_frequency'] === 'every_x_hours' ? max(1, (int)$form['schedule_every_hours']) : null;
    $scheduleWeekday = $runMode === 'scheduled' && $form['schedule_frequency'] === 'weekly' ? (int)$form['schedule_weekday'] : null;
    $repeatAfter = $form['repeat_after_days'] === '' ? 0 : (int)$form['repeat_after_days'];
    $startsAt = mkt_form_datetime_to_mysql((string)$form['starts_at']);
    $endsAt = mkt_form_datetime_to_mysql((string)$form['ends_at']);
    $allowRepeat = (int)$form['allow_repeat'];
    $cooldownDays = max(0, (int)($form['cooldown_days'] === '' ? 0 : $form['cooldown_days']));
    $cooldownScope = (string)$form['cooldown_scope'];
    $priority = (int)$form['priority'];
    $existing = mkt_fetch_campaign($db, $campaignId) ?: [];
    $existingNext = trim((string)($existing['next_run_at'] ?? ''));
    $nextRunAt = $runMode === 'scheduled' ? ($existingNext !== '' && (string)($existing['run_mode'] ?? '') === 'scheduled' ? $existingNext : mkt_compute_next_run_at(['run_mode' => $runMode, 'schedule_frequency' => $scheduleFrequency, 'schedule_every_hours' => $scheduleEveryHours, 'schedule_weekday' => $scheduleWeekday, 'starts_at' => $startsAt])) : null;
    $stmt->bind_param('sssssssssssiiiiisssissii', $form['name'], $form['code'], $form['description'], $form['campaign_type'], $form['audience_type'], $form['reward_type'], $form['channel_type'], $form['status'], $runMode, $triggerEvent, $scheduleFrequency, $scheduleEveryHours, $scheduleWeekday, $allowRepeat, $repeatAfter, $cooldownDays, $cooldownScope, $startsAt, $endsAt, $priority, $nextRunAt, $form['notes'], $adminId, $campaignId);
    $ok = $stmt->execute();
    $stmt->close();
    if (!$ok) {
        return [false, ['تعذر تحديث الحملة.'], $form];
    }
    mkt_save_rules_and_rewards($db, $campaignId, $form);
    if (function_exists('admin_log_activity')) {
        admin_log_activity('marketing.campaign.update', 'تحديث حملة تسويقية', 'تم تحديث الحملة.', 'marketing_campaign', $campaignId, ['code' => $form['code'], 'reward_type' => $form['reward_type'], 'run_mode' => $runMode]);
    }
    return [true, [], $form];
}

function mkt_rewards_map(?mysqli $db, int $campaignId): array
{
    $map = [];
    foreach (mkt_fetch_rewards($db, $campaignId) as $row) {
        $map[(string)$row['reward_key']] = (string)($row['reward_value'] ?? '');
    }
    return $map;
}

function mkt_rules_map(?mysqli $db, int $campaignId): array
{
    $map = [];
    foreach (mkt_fetch_rules($db, $campaignId) as $row) {
        $map[(string)$row['rule_key']] = (string)($row['rule_value'] ?? '');
    }
    return $map;
}

function mkt_exclusions_map(?mysqli $db, int $campaignId): array
{
    $map = [];
    foreach (mkt_fetch_exclusions($db, $campaignId) as $row) {
        $map[(string)$row['exclusion_key']] = (string)($row['exclusion_value'] ?? '');
    }
    return $map;
}

function mkt_campaign_can_run(array $campaign): array
{
    if (($campaign['status'] ?? '') !== 'active') {
        return [false, 'الحملة ليست فعالة حالياً.'];
    }
    $now = time();
    if (!empty($campaign['starts_at']) && strtotime((string)$campaign['starts_at']) > $now) {
        return [false, 'الحملة لم تبدأ بعد.'];
    }
    if (!empty($campaign['ends_at']) && strtotime((string)$campaign['ends_at']) < $now) {
        return [false, 'الحملة منتهية.'];
    }
    return [true, ''];
}

function mkt_fetch_candidate_customers(mysqli $db, array $campaign, array $rules, array $context = []): array
{
    $where = ['c.status = 1'];
    $forcedCustomerId = (int)($context['customer_id'] ?? 0);
    $triggerEvent = (string)($context['trigger_event'] ?? ($campaign['trigger_event'] ?? ''));
    if ($forcedCustomerId > 0) {
        $where[] = 'c.id = ' . $forcedCustomerId;
    }
    if (($campaign['audience_type'] ?? '') === 'zero_orders') {
        if (!($forcedCustomerId > 0 && $triggerEvent === 'first_order_created')) {
            $where[] = 'COALESCE(c.total_orders, 0) = 0';
        }
    } elseif (($campaign['audience_type'] ?? '') === 'inactive_customers') {
        $days = max(1, (int)($rules['inactive_days'] ?? 30));
        $cutoff = date('Y-m-d H:i:s', strtotime('-' . $days . ' days'));
        $where[] = 'COALESCE(c.total_orders, 0) > 0';
        $where[] = "(c.last_delivered_order_at IS NULL OR c.last_delivered_order_at <= '" . $db->real_escape_string($cutoff) . "')";
    } elseif (($campaign['audience_type'] ?? '') === 'specific_tier') {
        $tierId = (int)($rules['tier_id'] ?? 0);
        if ($tierId > 0) {
            $where[] = 'COALESCE(c.tier_id,0) = ' . $tierId;
        } else {
            return [];
        }
    }

    $registrationDays = (int)($rules['registration_days'] ?? 0);
    if ($registrationDays > 0) {
        $cutoff = date('Y-m-d H:i:s', strtotime('-' . $registrationDays . ' days'));
        $where[] = "c.created_at <= '" . $db->real_escape_string($cutoff) . "'";
    }
    $minSpent = (float)($rules['min_total_spent'] ?? 0);
    if ($minSpent > 0) {
        $where[] = 'COALESCE(c.total_spent, 0) >= ' . $minSpent;
    }
    $minOrders = (int)($rules['min_total_orders'] ?? 0);
    if ($minOrders > 0) {
        $where[] = 'COALESCE(c.total_orders, 0) >= ' . $minOrders;
    }
    $maxOrders = (int)($rules['max_total_orders'] ?? 0);
    if ($maxOrders > 0) {
        $where[] = 'COALESCE(c.total_orders, 0) <= ' . $maxOrders;
    }

    $sql = "SELECT c.id, c.full_name, c.phone, c.is_verified, c.points_balance, c.total_orders, c.total_spent, c.tier_id, c.last_delivered_order_at, c.created_at, t.title AS tier_title
            FROM customers c
            LEFT JOIN customer_tiers t ON t.id = c.tier_id
            WHERE " . implode(' AND ', $where) . "
            ORDER BY c.id DESC";
    $customers = hub_fetch_all($db, $sql);

    $eligibleProductId = (int)($rules['eligible_product_id'] ?? 0);
    $eligibleThresholdPromotionId = (int)($rules['eligible_threshold_promotion_id'] ?? 0);
    if ($eligibleProductId <= 0 && $eligibleThresholdPromotionId <= 0) {
        return $customers;
    }

    $product = $eligibleProductId > 0 ? mkt_fetch_product_for_access($db, $eligibleProductId) : null;
    $productTierIds = $eligibleProductId > 0 ? mkt_fetch_product_access_tier_ids($db, $eligibleProductId) : [];
    $promotion = $eligibleThresholdPromotionId > 0 ? mkt_fetch_threshold_promotion_for_access($db, $eligibleThresholdPromotionId) : null;
    $promotionTierIds = $eligibleThresholdPromotionId > 0 ? mkt_fetch_threshold_promotion_audience_tier_ids($db, $eligibleThresholdPromotionId) : [];

    $filtered = [];
    foreach ($customers as $customer) {
        if ($eligibleProductId > 0 && !mkt_customer_matches_product_access($customer, $product, $productTierIds)) {
            continue;
        }
        if ($eligibleThresholdPromotionId > 0 && !mkt_customer_matches_threshold_promotion_audience($customer, $promotion, $promotionTierIds)) {
            continue;
        }
        $filtered[] = $customer;
    }

    return $filtered;
}

function mkt_last_executed_log(mysqli $db, int $campaignId, int $customerId): ?array
{
    if (!mkt_table_exists($db, 'marketing_campaign_logs')) return null;
    $stmt = $db->prepare("SELECT * FROM marketing_campaign_logs WHERE campaign_id = ? AND customer_id = ? AND executed = 1 ORDER BY id DESC LIMIT 1");
    if (!$stmt) return null;
    $stmt->bind_param('ii', $campaignId, $customerId);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc() ?: null;
    $stmt->close();
    return $row;
}

function mkt_repeat_allowed(mysqli $db, array $campaign, int $customerId): array
{
    $campaignId = (int)($campaign['id'] ?? 0);
    if ($campaignId <= 0 || $customerId <= 0) return [false, 'بيانات ناقصة.'];
    $last = mkt_last_executed_log($db, $campaignId, $customerId);
    if (!$last) return [true, ''];
    $allowRepeat = (int)($campaign['allow_repeat'] ?? 0) === 1;
    if (!$allowRepeat) {
        return [false, 'تم تنفيذ الحملة سابقًا لهذا الزبون.'];
    }
    $days = (int)($campaign['repeat_after_days'] ?? 0);
    if ($days <= 0) {
        return [true, ''];
    }
    $nextRunAt = strtotime((string)$last['executed_at'] . ' +' . $days . ' days');
    if ($nextRunAt > time()) {
        return [false, 'مدة التكرار لم تكتمل بعد.'];
    }
    return [true, ''];
}

function mkt_generate_coupon_code(mysqli $db): string
{
    do {
        $code = 'MKT' . strtoupper(substr(bin2hex(random_bytes(4)), 0, 8));
        $stmt = $db->prepare('SELECT id FROM promo_codes WHERE code = ? LIMIT 1');
        $exists = false;
        if ($stmt) {
            $stmt->bind_param('s', $code);
            $stmt->execute();
            $exists = (bool)$stmt->get_result()->fetch_assoc();
            $stmt->close();
        }
    } while ($exists);
    return $code;
}

function mkt_render_template_text(string $text, array $customer, array $campaign, array $context = []): string
{
    $customerName = trim((string)($customer['full_name'] ?? ''));
    $customerPhone = trim((string)($customer['phone'] ?? ''));
    $campaignName = trim((string)($campaign['name'] ?? ''));
    $couponTypeRaw = (string)($context['coupon_type'] ?? $context['discount_type'] ?? '');
    $couponType = $couponTypeRaw !== '' ? mkt_discount_type_label($couponTypeRaw) : 'خصم خاص';
    $couponValueRaw = (string)($context['coupon_value'] ?? $context['discount_value'] ?? '');
    $couponMinOrder = (string)($context['coupon_min_order'] ?? $context['minimum_order_amount'] ?? '');
    if ($couponMinOrder !== '' && is_numeric($couponMinOrder)) {
        $couponMinOrder = mkt_money((float)$couponMinOrder);
    }
    $couponExpiry = (string)($context['coupon_expiry_date'] ?? '');
    $pointsAmount = (string)($context['points_amount'] ?? '');
    $pointsBalance = (string)($context['points_balance'] ?? $context['balance_after'] ?? ($customer['points_balance'] ?? '0'));
    $tierName = trim((string)($customer['tier_title'] ?? $context['tier_name'] ?? ''));
    $lastOrderDate = trim((string)($customer['last_delivered_order_at'] ?? $context['last_order_date'] ?? ''));
    if ($lastOrderDate !== '' && $lastOrderDate !== '-') {
        $ts = strtotime($lastOrderDate);
        if ($ts) {
            $lastOrderDate = date('Y-m-d', $ts);
        }
    }
    $totalOrders = (string)($customer['total_orders'] ?? $context['total_orders'] ?? '0');
    $totalSpent = (string)($customer['total_spent'] ?? $context['total_spent'] ?? '0');
    if ($totalSpent !== '' && is_numeric($totalSpent)) {
        $totalSpent = mkt_money((float)$totalSpent);
    }

    $replacements = [
        '{customer_name}' => $customerName !== '' ? $customerName : 'عميلنا العزيز',
        '{name}' => $customerName !== '' ? $customerName : 'عميلنا العزيز',
        '{customer_phone}' => $customerPhone !== '' ? $customerPhone : '-',
        '{phone}' => $customerPhone !== '' ? $customerPhone : '-',
        '{campaign_name}' => $campaignName !== '' ? $campaignName : 'حملة تسويقية',
        '{campaign}' => $campaignName !== '' ? $campaignName : 'حملة تسويقية',
        '{today_date}' => date('Y-m-d'),
        '{date}' => date('Y-m-d'),
        '{coupon_code}' => (string)($context['coupon_code'] ?? ''),
        '{code}' => (string)($context['coupon_code'] ?? ''),
        '{coupon_value}' => $couponValueRaw !== '' ? $couponValueRaw : '',
        '{value}' => $couponValueRaw !== '' ? $couponValueRaw : ($pointsAmount !== '' ? $pointsAmount : ''),
        '{coupon_type}' => $couponType,
        '{type}' => $couponType,
        '{coupon_min_order}' => $couponMinOrder !== '' ? $couponMinOrder : '0 د.ع',
        '{min}' => $couponMinOrder !== '' ? $couponMinOrder : '0 د.ع',
        '{coupon_expiry_date}' => $couponExpiry !== '' ? $couponExpiry : '-',
        '{expiry}' => $couponExpiry !== '' ? $couponExpiry : '-',
        '{points_amount}' => $pointsAmount !== '' ? $pointsAmount : '0',
        '{points}' => $pointsAmount !== '' ? $pointsAmount : '0',
        '{points_balance}' => $pointsBalance !== '' ? $pointsBalance : '0',
        '{balance}' => $pointsBalance !== '' ? $pointsBalance : '0',
        '{tier_name}' => $tierName !== '' ? $tierName : 'مستوى الزبون',
        '{tier}' => $tierName !== '' ? $tierName : 'مستوى الزبون',
        '{last_order_date}' => $lastOrderDate !== '' ? $lastOrderDate : '-',
        '{last_order}' => $lastOrderDate !== '' ? $lastOrderDate : '-',
        '{total_orders}' => $totalOrders !== '' ? $totalOrders : '0',
        '{orders}' => $totalOrders !== '' ? $totalOrders : '0',
        '{total_spent}' => $totalSpent !== '' ? $totalSpent : '0 د.ع',
        '{spent}' => $totalSpent !== '' ? $totalSpent : '0 د.ع',
    ];

    return trim(strtr($text, $replacements));
}

function mkt_issue_coupon(mysqli $db, array $campaign, array $customer, array $rewards, int $adminId = 0): array
{
    $customerId = (int)($customer['id'] ?? 0);
    if ($customerId <= 0) {
        return ['ok' => false, 'message' => 'الزبون غير صالح.'];
    }
    $code = mkt_generate_coupon_code($db);
    $discountType = in_array(($rewards['coupon_discount_type'] ?? 'percent'), ['percent', 'fixed'], true) ? (string)$rewards['coupon_discount_type'] : 'percent';
    $discountValue = (float)($rewards['coupon_discount_value'] ?? 0);
    $minimumOrder = (float)($rewards['coupon_minimum_order_amount'] ?? 0);
    $maximumDiscount = trim((string)($rewards['coupon_maximum_discount_amount'] ?? ''));
    $maximumDiscountValue = $maximumDiscount === '' ? 0.0 : (float)$maximumDiscount;
    $usageLimit = max(1, (int)($rewards['coupon_usage_limit'] ?? 1));
    $validDays = max(1, (int)($rewards['coupon_valid_days'] ?? 7));
    $title = 'حملة: ' . (string)($campaign['name'] ?? 'حملة تسويقية');
    $notes = 'تم توليده من محرك التسويق #' . (int)($campaign['id'] ?? 0);
    $startAt = date('Y-m-d H:i:s');
    $endAt = date('Y-m-d H:i:s', strtotime('+' . $validDays . ' days'));
    $campaignId = (int)($campaign['id'] ?? 0);
    $stmt = $db->prepare('INSERT INTO promo_codes (title, code, discount_type, discount_value, minimum_order_amount, maximum_discount_amount, usage_limit, used_count, start_at, end_at, is_single_use_per_customer, first_order_only, status, notes, influencer_id, influencer_commission_type, influencer_commission_value, assigned_customer_id, generated_by_campaign_id, is_generated, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, 0, ?, ?, 1, 0, 1, ?, NULL, NULL, 0, ?, ?, 1, NOW(), NOW())');
    if (!$stmt) {
        return ['ok' => false, 'message' => 'تعذر إنشاء الكوبون.'];
    }
    $stmt->bind_param('sssdddisssii', $title, $code, $discountType, $discountValue, $minimumOrder, $maximumDiscountValue, $usageLimit, $startAt, $endAt, $notes, $customerId, $campaignId);
    $ok = $stmt->execute();
    $promoId = $ok ? (int)$stmt->insert_id : 0;
    $stmt->close();
    if (!$ok || $promoId <= 0) {
        return ['ok' => false, 'message' => 'فشل حفظ الكوبون.'];
    }
    return [
        'ok' => true,
        'promo_id' => $promoId,
        'coupon_code' => $code,
        'discount_value' => $discountValue,
        'coupon_value' => $discountValue,
        'discount_type' => $discountType,
        'coupon_type' => $discountType,
        'coupon_min_order' => $minimumOrder,
        'coupon_expiry_date' => date('Y-m-d', strtotime($endAt)),
        'valid_days' => $validDays,
    ];
}

function mkt_grant_points(mysqli $db, array $campaign, array $customer, array $rewards, int $adminId = 0): array
{
    $customerId = (int)($customer['id'] ?? 0);
    $points = max(1, (int)($rewards['points_amount'] ?? 0));
    if ($customerId <= 0 || $points <= 0) {
        return ['ok' => false, 'message' => 'بيانات النقاط غير صالحة.'];
    }
    $db->begin_transaction();
    try {
        $stmt = $db->prepare('UPDATE customers SET points_balance = GREATEST(0, COALESCE(points_balance, 0) + ?) WHERE id = ? LIMIT 1');
        if (!$stmt) throw new RuntimeException('update points failed');
        $stmt->bind_param('ii', $points, $customerId);
        $stmt->execute();
        $stmt->close();

        $balanceRow = hub_fetch_all($db, 'SELECT points_balance FROM customers WHERE id = ' . $customerId . ' LIMIT 1');
        $balanceAfter = (int)($balanceRow[0]['points_balance'] ?? 0);
        if (mkt_table_exists($db, 'customer_loyalty_transactions')) {
            $txnType = 'marketing_campaign';
            $redemptionType = 'amount';
            $rewardTitle = (string)($campaign['name'] ?? 'حملة تسويقية');
            $notes = 'تمت إضافته من محرك التسويق #' . (int)($campaign['id'] ?? 0);
            $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) {
                $stmt->bind_param('iisiiiss', $customerId, $adminId, $txnType, $points, $balanceAfter, $redemptionType, $rewardTitle, $notes);
                $stmt->execute();
                $stmt->close();
            }
        }
        $db->commit();
        return ['ok' => true, 'points_amount' => $points, 'balance_after' => $balanceAfter, 'points_balance' => $balanceAfter];
    } catch (Throwable $e) {
        $db->rollback();
        return ['ok' => false, 'message' => 'تعذر إضافة النقاط.'];
    }
}

function mkt_create_notification(mysqli $db, int $customerId, string $title, string $message, string $type = 'offer'): bool
{
    if ($customerId <= 0 || trim($title) === '' || trim($message) === '' || !mkt_table_exists($db, 'notifications')) {
        return false;
    }
    $stmt = $db->prepare('INSERT INTO notifications (customer_id, title, message, type, is_read, created_at) VALUES (?, ?, ?, ?, 0, NOW())');
    if (!$stmt) return false;
    $stmt->bind_param('isss', $customerId, $title, $message, $type);
    $ok = $stmt->execute();
    $stmt->close();
    return $ok;
}

function mkt_execute_reward(mysqli $db, array $campaign, array $customer, array $rules, array $rewards, int $adminId = 0): array
{
    $rewardType = (string)($campaign['reward_type'] ?? '');
    $channelType = (string)($campaign['channel_type'] ?? 'direct_assign');
    $result = ['ok' => true, 'context' => []];

    if ($rewardType === 'personal_coupon') {
        $result = mkt_issue_coupon($db, $campaign, $customer, $rewards, $adminId);
    } elseif ($rewardType === 'bonus_points') {
        $result = mkt_grant_points($db, $campaign, $customer, $rewards, $adminId);
    } elseif ($rewardType === 'in_app_notification') {
        $result = ['ok' => true, 'context' => []];
    }

    if (!$result['ok']) {
        return $result;
    }

    $context = is_array($result) ? $result : [];
    $defaultTitle = 'هدية جديدة من ' . (string)($campaign['name'] ?? 'Nay Store');
    $defaultMessage = 'تمت إضافة مكافأة جديدة إلى حسابك.';
    if ($rewardType === 'personal_coupon' && !empty($context['coupon_code'])) {
        $defaultMessage = 'مبروك! كودك الشخصي: ' . $context['coupon_code'];
    } elseif ($rewardType === 'bonus_points' && !empty($context['points_amount'])) {
        $defaultMessage = 'تمت إضافة ' . (int)$context['points_amount'] . ' نقطة إلى حسابك.';
    }

    $title = mkt_render_template_text((string)($rewards['notification_title'] ?? $defaultTitle), $customer, $campaign, $context);
    $message = mkt_render_template_text((string)($rewards['notification_message'] ?? $defaultMessage), $customer, $campaign, $context);
    if ($title === '') $title = $defaultTitle;
    if ($message === '') $message = $defaultMessage;

    if (in_array($channelType, ['in_app', 'both'], true) || $rewardType === 'in_app_notification') {
        $notifyOk = mkt_create_notification($db, (int)$customer['id'], $title, $message, 'offer');
        if (!$notifyOk) {
            return ['ok' => false, 'message' => 'فشل إنشاء الإشعار.'];
        }
    }

    $context['notification_title'] = $title;
    $context['notification_message'] = $message;
    return ['ok' => true, 'context' => $context];
}

function mkt_customer_has_recent_pending_order(mysqli $db, int $customerId, int $days): bool
{
    if ($customerId <= 0 || $days <= 0 || !mkt_table_exists($db, 'orders')) return false;
    $cutoff = date('Y-m-d H:i:s', strtotime('-' . $days . ' days'));
    $stmt = $db->prepare("SELECT id FROM orders WHERE customer_id = ? AND order_status = 'pending' AND created_at >= ? ORDER BY id DESC LIMIT 1");
    if (!$stmt) return false;
    $stmt->bind_param('is', $customerId, $cutoff);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    return (bool)$row;
}

function mkt_campaign_is_running(mysqli $db, int $campaignId): bool
{
    if ($campaignId <= 0 || !mkt_table_exists($db, 'marketing_campaign_runs')) return false;
    $stmt = $db->prepare("SELECT id FROM marketing_campaign_runs WHERE campaign_id = ? AND finished_at IS NULL AND started_at >= DATE_SUB(NOW(), INTERVAL 15 MINUTE) ORDER BY id DESC LIMIT 1");
    if (!$stmt) return false;
    $stmt->bind_param('i', $campaignId);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    return (bool)$row;
}

function mkt_cooldown_allowed(mysqli $db, array $campaign, int $customerId): array
{
    $days = max(0, (int)($campaign['cooldown_days'] ?? 0));
    if ($customerId <= 0 || $days <= 0) {
        return [true, '', 0];
    }
    $scope = (string)($campaign['cooldown_scope'] ?? 'same_campaign');
    $cutoff = date('Y-m-d H:i:s', strtotime('-' . $days . ' days'));
    if ($scope === 'any_campaign') {
        $stmt = $db->prepare("SELECT id FROM marketing_campaign_logs WHERE customer_id = ? AND executed = 1 AND executed_at >= ? ORDER BY id DESC LIMIT 1");
        if (!$stmt) return [true, '', 0];
        $stmt->bind_param('is', $customerId, $cutoff);
    } elseif ($scope === 'same_reward_type') {
        $stmt = $db->prepare("SELECT l.id FROM marketing_campaign_logs l INNER JOIN marketing_campaigns c ON c.id = l.campaign_id WHERE l.customer_id = ? AND l.executed = 1 AND l.executed_at >= ? AND c.reward_type = ? ORDER BY l.id DESC LIMIT 1");
        if (!$stmt) return [true, '', 0];
        $rewardType = (string)($campaign['reward_type'] ?? '');
        $stmt->bind_param('iss', $customerId, $cutoff, $rewardType);
    } else {
        $campaignId = (int)($campaign['id'] ?? 0);
        $stmt = $db->prepare("SELECT id FROM marketing_campaign_logs WHERE customer_id = ? AND campaign_id = ? AND executed = 1 AND executed_at >= ? ORDER BY id DESC LIMIT 1");
        if (!$stmt) return [true, '', 0];
        $stmt->bind_param('iis', $customerId, $campaignId, $cutoff);
    }
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    if ($row) return [false, 'الزبون داخل فترة التهدئة الحالية.', 1];
    return [true, '', 0];
}

function mkt_exclusion_reason(mysqli $db, array $campaign, array $customer, array $exclusions): string
{
    if ((int)($exclusions['exclude_unverified'] ?? 0) === 1 && (int)($customer['is_verified'] ?? 0) !== 1) {
        return 'الزبون غير موثق.';
    }
    $excludeTierId = (int)($exclusions['exclude_tier_id'] ?? 0);
    if ($excludeTierId > 0 && (int)($customer['tier_id'] ?? 0) === $excludeTierId) {
        return 'الزبون ضمن مستوى مستثنى.';
    }
    $excludeIdsRaw = trim((string)($exclusions['exclude_customer_ids'] ?? ''));
    if ($excludeIdsRaw !== '') {
        $ids = array_filter(array_map('intval', preg_split('/[^0-9]+/', $excludeIdsRaw)));
        if ($ids && in_array((int)($customer['id'] ?? 0), $ids, true)) {
            return 'الزبون مستثنى يدويًا من الحملة.';
        }
    }
    $recentPendingDays = (int)($exclusions['exclude_recent_pending_days'] ?? 0);
    if ($recentPendingDays > 0 && mkt_customer_has_recent_pending_order($db, (int)($customer['id'] ?? 0), $recentPendingDays)) {
        return 'لديه طلب معلق حديث.';
    }
    return '';
}

function mkt_preview_campaign(mysqli $db, int $campaignId, int $sampleSize = 8): array
{
    $campaign = mkt_fetch_campaign($db, $campaignId);
    if (!$campaign) return ['ok' => false, 'message' => 'الحملة غير موجودة.'];
    $rules = mkt_rules_map($db, $campaignId);
    $exclusions = mkt_exclusions_map($db, $campaignId);
    $customers = mkt_fetch_candidate_customers($db, $campaign, $rules, []);
    $ready = [];
    $excluded = [];
    foreach ($customers as $customer) {
        $customerId = (int)($customer['id'] ?? 0);
        if ($customerId <= 0) continue;
        $reason = mkt_exclusion_reason($db, $campaign, $customer, $exclusions);
        if ($reason === '') {
            [$allowed, $repeatReason] = mkt_repeat_allowed($db, $campaign, $customerId);
            if (!$allowed) $reason = $repeatReason;
        }
        if ($reason === '') {
            [$cooldownAllowed, $cooldownReason] = mkt_cooldown_allowed($db, $campaign, $customerId);
            if (!$cooldownAllowed) $reason = $cooldownReason;
        }
        if ($reason !== '') {
            $customer['decision_reason'] = $reason;
            $excluded[] = $customer;
        } else {
            $customer['decision_reason'] = 'جاهز للتنفيذ.';
            $ready[] = $customer;
        }
    }
    return [
        'ok' => true,
        'message' => '',
        'total_candidates' => count($customers),
        'ready_count' => count($ready),
        'excluded_count' => count($excluded),
        'ready_sample' => array_slice($ready, 0, max(1, $sampleSize)),
        'excluded_sample' => array_slice($excluded, 0, max(1, $sampleSize)),
        'rules' => $rules,
        'exclusions' => $exclusions,
    ];
}

function mkt_create_run(mysqli $db, int $campaignId, string $runType = 'manual', string $notes = ''): int
{
    $stmt = $db->prepare('INSERT INTO marketing_campaign_runs (campaign_id, run_type, started_at, finished_at, total_matched, total_executed, total_skipped, total_failed, notes, created_at, updated_at) VALUES (?, ?, NOW(), NULL, 0, 0, 0, 0, ?, NOW(), NOW())');
    if (!$stmt) return 0;
    $stmt->bind_param('iss', $campaignId, $runType, $notes);
    $ok = $stmt->execute();
    $runId = $ok ? (int)$stmt->insert_id : 0;
    $stmt->close();
    return $runId;
}

function mkt_insert_log(mysqli $db, int $campaignId, int $runId, int $customerId, bool $matched, bool $executed, string $statusText, string $skipReason = '', string $failureReason = '', array $rewardSnapshot = [], array $channelSnapshot = [], string $matchedReason = '', bool $cooldownApplied = false): void
{
    $rewardJson = $rewardSnapshot ? json_encode($rewardSnapshot, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) : null;
    $channelJson = $channelSnapshot ? json_encode($channelSnapshot, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) : null;
    $stmt = $db->prepare('INSERT INTO marketing_campaign_logs (campaign_id, run_id, customer_id, matched, executed, status_text, matched_reason, skip_reason, failure_reason, reward_snapshot_json, channel_snapshot_json, cooldown_applied, executed_at, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW(), NOW())');
    if (!$stmt) return;
    $matchedInt = $matched ? 1 : 0;
    $executedInt = $executed ? 1 : 0;
    $cooldownInt = $cooldownApplied ? 1 : 0;
    $stmt->bind_param('iiiiissssssi', $campaignId, $runId, $customerId, $matchedInt, $executedInt, $statusText, $matchedReason, $skipReason, $failureReason, $rewardJson, $channelJson, $cooldownInt);
    $stmt->execute();
    $stmt->close();
}

function mkt_finish_run(mysqli $db, int $runId, array $summary): void
{
    $stmt = $db->prepare('UPDATE marketing_campaign_runs SET finished_at = NOW(), total_matched = ?, total_executed = ?, total_skipped = ?, total_failed = ?, notes = ?, updated_at = NOW() WHERE id = ? LIMIT 1');
    if (!$stmt) return;
    $notes = (string)($summary['notes'] ?? '');
    $stmt->bind_param('iiiisi', $summary['matched'], $summary['executed'], $summary['skipped'], $summary['failed'], $notes, $runId);
    $stmt->execute();
    $stmt->close();
}

function mkt_run_campaign(mysqli $db, int $campaignId, int $adminId = 0, string $runType = 'manual', array $eventContext = []): array
{
    $campaign = mkt_fetch_campaign($db, $campaignId);
    if (!$campaign) {
        return [false, 'الحملة غير موجودة.', ['matched' => 0, 'executed' => 0, 'skipped' => 0, 'failed' => 0]];
    }
    if (mkt_campaign_is_running($db, $campaignId)) {
        return [false, 'الحملة لديها تشغيل جارٍ حاليًا. انتظر قليلًا ثم أعد المحاولة.', ['matched' => 0, 'executed' => 0, 'skipped' => 0, 'failed' => 0]];
    }
    [$canRun, $reason] = mkt_campaign_can_run($campaign);
    if (!$canRun) {
        return [false, $reason, ['matched' => 0, 'executed' => 0, 'skipped' => 0, 'failed' => 0]];
    }

    $rules = mkt_rules_map($db, $campaignId);
    $rewards = mkt_rewards_map($db, $campaignId);
    $exclusions = mkt_exclusions_map($db, $campaignId);
    $customers = mkt_fetch_candidate_customers($db, $campaign, $rules, []);
    $runId = mkt_create_run($db, $campaignId, $runType);
    if ($runId <= 0) {
        return [false, 'تعذر إنشاء سجل تشغيل الحملة.', ['matched' => 0, 'executed' => 0, 'skipped' => 0, 'failed' => 0]];
    }

    $summary = ['matched' => 0, 'executed' => 0, 'skipped' => 0, 'failed' => 0, 'notes' => ''];
    $matchedReason = $runType === 'triggered'
        ? ('تم التنفيذ عبر الحدث: ' . mkt_trigger_event_label((string)($eventContext['trigger_event'] ?? ($campaign['trigger_event'] ?? ''))))
        : 'اجتاز الشروط والاستثناءات.';
    foreach ($customers as $customer) {
        $customerId = (int)($customer['id'] ?? 0);
        if ($customerId <= 0) continue;

        $excludeReason = mkt_exclusion_reason($db, $campaign, $customer, $exclusions);
        if ($excludeReason !== '') {
            $summary['skipped']++;
            mkt_insert_log($db, $campaignId, $runId, $customerId, false, false, 'skipped', $excludeReason, '', [], ['channel_type' => (string)$campaign['channel_type']], '', false);
            continue;
        }

        [$allowed, $skipReason] = mkt_repeat_allowed($db, $campaign, $customerId);
        if (!$allowed) {
            $summary['skipped']++;
            mkt_insert_log($db, $campaignId, $runId, $customerId, false, false, 'skipped', $skipReason, '', [], ['channel_type' => (string)$campaign['channel_type']], '', false);
            continue;
        }

        [$cooldownAllowed, $cooldownReason, $cooldownApplied] = mkt_cooldown_allowed($db, $campaign, $customerId);
        if (!$cooldownAllowed) {
            $summary['skipped']++;
            mkt_insert_log($db, $campaignId, $runId, $customerId, false, false, 'skipped', $cooldownReason, '', [], ['channel_type' => (string)$campaign['channel_type']], '', (bool)$cooldownApplied);
            continue;
        }

        $summary['matched']++;
        $result = mkt_execute_reward($db, $campaign, $customer, $rules, $rewards, $adminId);
        if (!($result['ok'] ?? false)) {
            $summary['failed']++;
            mkt_insert_log($db, $campaignId, $runId, $customerId, true, false, 'failed', '', (string)($result['message'] ?? 'Execution failed'), [], ['channel_type' => (string)$campaign['channel_type']], $matchedReason, false);
            continue;
        }
        $summary['executed']++;
        mkt_insert_log($db, $campaignId, $runId, $customerId, true, true, 'executed', '', '', (array)($result['context'] ?? []), ['channel_type' => (string)$campaign['channel_type']], $matchedReason, false);
    }

    $summary['notes'] = 'المطابقون الجاهزون: ' . $summary['matched'] . ' | تم التنفيذ: ' . $summary['executed'] . ' | تم التخطي/الاستبعاد: ' . $summary['skipped'] . ' | فشل: ' . $summary['failed'];
    mkt_finish_run($db, $runId, $summary);
    $campaign['last_run_at'] = date('Y-m-d H:i:s');
    $campaign['next_run_at'] = ($campaign['run_mode'] ?? 'manual') === 'scheduled' ? mkt_compute_next_run_at($campaign, time(), true) : null;
    $stmt = $db->prepare('UPDATE marketing_campaigns SET last_run_at = NOW(), next_run_at = ?, updated_at = NOW() WHERE id = ? LIMIT 1');
    if ($stmt) {
        $nextRunAt = $campaign['next_run_at'];
        $stmt->bind_param('si', $nextRunAt, $campaignId);
        $stmt->execute();
        $stmt->close();
    }
    if (function_exists('admin_log_activity')) {
        $verb = $runType === 'scheduled' ? 'تم تشغيل الحملة تلقائيًا.' : 'تم تشغيل الحملة يدويًا.';
        admin_log_activity('marketing.campaign.run', 'تشغيل حملة تسويقية', $verb, 'marketing_campaign', $campaignId, $summary + ['run_type' => $runType]);
    }
    return [true, 'تم تشغيل الحملة بنجاح.', $summary];
}

function mkt_fetch_triggered_campaigns(?mysqli $db, string $event, int $limit = 50): array
{
    if (!$db || trim($event) === '' || !mkt_table_exists($db, 'marketing_campaigns') || !mkt_column_exists($db, 'marketing_campaigns', 'trigger_event')) return [];
    $eventSafe = $db->real_escape_string($event);
    $limit = max(1, min(200, $limit));
    return hub_fetch_all($db, "SELECT * FROM marketing_campaigns WHERE run_mode = 'triggered' AND status = 'active' AND trigger_event = '{$eventSafe}' AND (starts_at IS NULL OR starts_at <= NOW()) AND (ends_at IS NULL OR ends_at >= NOW()) ORDER BY priority ASC, id ASC LIMIT {$limit}");
}

function mkt_run_triggered_campaigns(mysqli $db, string $event, array $context = [], int $adminId = 0, int $limit = 50): array
{
    $campaigns = mkt_fetch_triggered_campaigns($db, $event, $limit);
    $result = ['event' => $event, 'due' => count($campaigns), 'ran' => 0, 'failed' => 0, 'campaign_ids' => []];
    foreach ($campaigns as $campaign) {
        $context['trigger_event'] = $event;
        [$ok] = mkt_run_campaign($db, (int)$campaign['id'], $adminId, 'triggered', $context);
        if ($ok) {
            $result['ran']++;
            $result['campaign_ids'][] = (int)$campaign['id'];
        } else {
            $result['failed']++;
        }
    }
    return $result;
}

if (!function_exists('mkt_template_kind_types')) {
function mkt_template_kind_types(): array
{
    return [
        'system' => 'نظامي',
        'custom' => 'مخصص',
    ];
}
}

function mkt_templates_schema_ready(?mysqli $db): bool
{
    if (!$db || !mkt_schema_ready($db)) return false;
    foreach (['marketing_campaign_templates', 'marketing_campaign_template_rules', 'marketing_campaign_template_rewards', 'marketing_campaign_template_exclusions'] as $table) {
        if (!mkt_table_exists($db, $table)) {
            return false;
        }
    }
    return mkt_column_exists($db, 'marketing_campaign_templates', 'trigger_event');
}

function mkt_generate_template_code(string $name = ''): string
{
    $base = preg_replace('/[^a-zA-Z0-9]+/', '_', strtolower(trim($name)));
    $base = trim((string)$base, '_');
    if ($base === '') {
        $base = 'template';
    }
    return 'TPL_' . strtoupper(substr($base, 0, 24)) . '_' . date('Ymd_His');
}

function mkt_fetch_templates(?mysqli $db, int $limit = 100): array
{
    if (!$db || !mkt_table_exists($db, 'marketing_campaign_templates')) return [];
    $limit = max(1, min(500, $limit));
    return hub_fetch_all($db, "SELECT t.*, a.name AS created_by_name,
        (SELECT COUNT(*) FROM marketing_campaign_template_rules r WHERE r.template_id = t.id) AS rules_count,
        (SELECT COUNT(*) FROM marketing_campaign_template_rewards r WHERE r.template_id = t.id) AS rewards_count
        FROM marketing_campaign_templates t
        LEFT JOIN admins a ON a.id = t.created_by
        WHERE COALESCE(t.is_active,1) = 1
        ORDER BY FIELD(t.template_kind,'system','custom'), t.id DESC
        LIMIT {$limit}");
}

function mkt_fetch_template(?mysqli $db, int $templateId): ?array
{
    if (!$db || $templateId <= 0 || !mkt_table_exists($db, 'marketing_campaign_templates')) return null;
    $stmt = $db->prepare("SELECT * FROM marketing_campaign_templates WHERE id = ? LIMIT 1");
    if (!$stmt) return null;
    $stmt->bind_param('i', $templateId);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc() ?: null;
    $stmt->close();
    return $row;
}

function mkt_fetch_template_rules(?mysqli $db, int $templateId): array
{
    if (!$db || $templateId <= 0 || !mkt_table_exists($db, 'marketing_campaign_template_rules')) return [];
    $stmt = $db->prepare("SELECT * FROM marketing_campaign_template_rules WHERE template_id = ? AND is_active = 1 ORDER BY sort_order ASC, id ASC");
    if (!$stmt) return [];
    $stmt->bind_param('i', $templateId);
    $stmt->execute();
    $rows = [];
    $res = $stmt->get_result();
    while ($row = $res->fetch_assoc()) {
        $rows[] = $row;
    }
    $stmt->close();
    return $rows;
}

function mkt_fetch_template_rewards(?mysqli $db, int $templateId): array
{
    if (!$db || $templateId <= 0 || !mkt_table_exists($db, 'marketing_campaign_template_rewards')) return [];
    $stmt = $db->prepare("SELECT * FROM marketing_campaign_template_rewards WHERE template_id = ? AND is_active = 1 ORDER BY sort_order ASC, id ASC");
    if (!$stmt) return [];
    $stmt->bind_param('i', $templateId);
    $stmt->execute();
    $rows = [];
    $res = $stmt->get_result();
    while ($row = $res->fetch_assoc()) {
        $rows[] = $row;
    }
    $stmt->close();
    return $rows;
}

function mkt_fetch_template_exclusions(?mysqli $db, int $templateId): array
{
    if (!$db || $templateId <= 0 || !mkt_table_exists($db, 'marketing_campaign_template_exclusions')) return [];
    $stmt = $db->prepare("SELECT * FROM marketing_campaign_template_exclusions WHERE template_id = ? AND is_active = 1 ORDER BY sort_order ASC, id ASC");
    if (!$stmt) return [];
    $stmt->bind_param('i', $templateId);
    $stmt->execute();
    $rows = [];
    $res = $stmt->get_result();
    while ($row = $res->fetch_assoc()) {
        $rows[] = $row;
    }
    $stmt->close();
    return $rows;
}

function mkt_template_form_from_template(?mysqli $db, ?array $template): array
{
    $form = mkt_default_form();
    if (!$template) return $form;
    foreach ($form as $key => $value) {
        if (array_key_exists($key, $template) && $template[$key] !== null) {
            $form[$key] = (string)$template[$key];
        }
    }
    if (!$db || empty($template['id'])) return $form;
    foreach (mkt_fetch_template_rules($db, (int)$template['id']) as $rule) {
        $key = (string)($rule['rule_key'] ?? '');
        if ($key !== '') {
            $form[$key] = (string)($rule['rule_value'] ?? '');
        }
    }
    foreach (mkt_fetch_template_rewards($db, (int)$template['id']) as $reward) {
        $key = (string)($reward['reward_key'] ?? '');
        if ($key !== '') {
            $form[$key] = (string)($reward['reward_value'] ?? '');
        }
    }
    foreach (mkt_fetch_template_exclusions($db, (int)$template['id']) as $row) {
        $key = (string)($row['exclusion_key'] ?? '');
        if ($key !== '') {
            $form[$key] = (string)($row['exclusion_value'] ?? '');
        }
    }
    return $form;
}

function mkt_validate_template_form(?mysqli $db, array $form, int $templateId = 0): array
{
    $errors = [];
    if (!$db) {
        $errors[] = 'اتصال قاعدة البيانات غير متوفر.';
        return $errors;
    }
    if (!mkt_templates_schema_ready($db)) {
        $errors[] = 'نفذ ملف SQL الخاص بالقوالب أولاً ثم أعد المحاولة.';
        return $errors;
    }
    if ($form['name'] === '') $errors[] = 'اسم القالب مطلوب.';
    if ($form['code'] === '') $errors[] = 'الكود الداخلي مطلوب.';
    if (!array_key_exists($form['campaign_type'], mkt_campaign_types())) $errors[] = 'نوع الحملة غير صالح.';
    if (!array_key_exists($form['audience_type'], mkt_audience_types())) $errors[] = 'نوع الجمهور غير صالح.';
    if (!array_key_exists($form['reward_type'], mkt_reward_types())) $errors[] = 'نوع المكافأة غير صالح.';
    if (!array_key_exists($form['channel_type'], mkt_channel_types())) $errors[] = 'قناة التنفيذ غير صالحة.';
    if (!array_key_exists($form['status'], mkt_status_types())) $errors[] = 'الحالة الافتراضية غير صالحة.';
    if (!array_key_exists($form['run_mode'], mkt_run_mode_types())) $errors[] = 'نوع التشغيل غير صالح.';
    if (!in_array($form['cooldown_scope'], ['same_campaign', 'same_reward_type', 'any_campaign'], true)) $errors[] = 'نطاق فترة التهدئة غير صالح.';
    if ($form['run_mode'] === 'scheduled') {
        if (!array_key_exists($form['schedule_frequency'], mkt_schedule_frequency_types())) $errors[] = 'نوع الجدولة غير صالح.';
        if ($form['schedule_frequency'] === 'every_x_hours' && (int)$form['schedule_every_hours'] <= 0) $errors[] = 'عدد الساعات مطلوب عند اختيار الجدولة كل عدة ساعات.';
        if ($form['schedule_frequency'] === 'weekly' && !array_key_exists((string)$form['schedule_weekday'], mkt_schedule_weekday_types())) $errors[] = 'اختر يوم الأسبوع للجدولة الأسبوعية.';
    }
    if ($form['run_mode'] === 'triggered' && !array_key_exists($form['trigger_event'], mkt_trigger_event_types())) {
        $errors[] = 'نوع الحدث المحفّز غير صالح.';
    }
    if ($form['audience_type'] === 'inactive_customers' && (int)$form['inactive_days'] <= 0) {
        $errors[] = 'عدد أيام عدم النشاط مطلوب لهذه الفئة.';
    }
    if ($form['audience_type'] === 'specific_tier' && (int)$form['tier_id'] <= 0) {
        $errors[] = 'اختيار المستوى مطلوب.';
    }
    if ((int)$form['min_total_orders'] > 0 && (int)$form['max_total_orders'] > 0 && (int)$form['min_total_orders'] > (int)$form['max_total_orders']) {
        $errors[] = 'الحد الأدنى لعدد الطلبات لا يمكن أن يكون أكبر من الحد الأعلى.';
    }
    if ((int)$form['eligible_product_id'] > 0 && !mkt_fetch_product_for_access($db, (int)$form['eligible_product_id'])) {
        $errors[] = 'المنتج الذكي المحدد غير موجود أو غير فعّال.';
    }
    if ((int)$form['eligible_threshold_promotion_id'] > 0 && !mkt_fetch_threshold_promotion_for_access($db, (int)$form['eligible_threshold_promotion_id'])) {
        $errors[] = 'عرض الحد الأدنى المحدد غير موجود أو غير فعّال.';
    }
    if ($form['starts_at'] !== '' && $form['ends_at'] !== '' && strtotime(str_replace('T', ' ', $form['starts_at'])) > strtotime(str_replace('T', ' ', $form['ends_at']))) {
        $errors[] = 'تاريخ البداية يجب أن يسبق تاريخ النهاية.';
    }
    if ($form['reward_type'] === 'personal_coupon') {
        if ((float)$form['coupon_discount_value'] <= 0) $errors[] = 'قيمة خصم الكوبون مطلوبة.';
        if (!in_array($form['coupon_discount_type'], ['percent', 'fixed'], true)) $errors[] = 'نوع خصم الكوبون غير صالح.';
    }
    if ($form['reward_type'] === 'bonus_points' && (int)$form['points_amount'] <= 0) $errors[] = 'عدد النقاط مطلوب.';
    if ($form['reward_type'] === 'in_app_notification' && $form['notification_title'] === '' && $form['notification_message'] === '') $errors[] = 'عنوان أو نص الإشعار مطلوب.';
    $stmt = $db->prepare('SELECT id FROM marketing_campaign_templates WHERE code = ? AND id <> ? LIMIT 1');
    if ($stmt) {
        $stmt->bind_param('si', $form['code'], $templateId);
        $stmt->execute();
        if ($stmt->get_result()->fetch_assoc()) {
            $errors[] = 'الكود الداخلي مستخدم سابقاً.';
        }
        $stmt->close();
    }
    return $errors;
}

function mkt_insert_template_rule(mysqli $db, int $templateId, string $key, string $value, int $sortOrder): void
{
    $value = trim($value);
    if ($templateId <= 0 || $key === '' || $value === '') return;
    $operator = '=';
    if (in_array($key, ['min_total_spent', 'min_total_orders'], true)) {
        $operator = '>=';
    } elseif (in_array($key, ['max_total_orders'], true)) {
        $operator = '<=';
    } elseif (in_array($key, ['eligible_product_id', 'eligible_threshold_promotion_id'], true)) {
        $operator = '=';
    }
    $stmt = $db->prepare('INSERT INTO marketing_campaign_template_rules (template_id, rule_key, operator, rule_value, secondary_value, sort_order, is_active, created_at, updated_at) VALUES (?, ?, ?, ?, NULL, ?, 1, NOW(), NOW())');
    if (!$stmt) return;
    $stmt->bind_param('isssi', $templateId, $key, $operator, $value, $sortOrder);
    $stmt->execute();
    $stmt->close();
}

function mkt_insert_template_reward(mysqli $db, int $templateId, string $key, string $value, int $sortOrder, array $meta = []): void
{
    $value = trim($value);
    if ($templateId <= 0 || $key === '' || $value === '') return;
    $json = $meta ? json_encode($meta, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) : null;
    $stmt = $db->prepare('INSERT INTO marketing_campaign_template_rewards (template_id, reward_key, reward_value, metadata_json, sort_order, is_active, created_at, updated_at) VALUES (?, ?, ?, ?, ?, 1, NOW(), NOW())');
    if (!$stmt) return;
    $stmt->bind_param('isssi', $templateId, $key, $value, $json, $sortOrder);
    $stmt->execute();
    $stmt->close();
}

function mkt_insert_template_exclusion(mysqli $db, int $templateId, string $key, string $value, int $sortOrder): void
{
    $value = trim($value);
    if ($templateId <= 0 || $key === '' || $value === '') return;
    $stmt = $db->prepare("INSERT INTO marketing_campaign_template_exclusions (template_id, exclusion_key, operator, exclusion_value, secondary_value, sort_order, is_active, created_at, updated_at) VALUES (?, ?, '=', ?, NULL, ?, 1, NOW(), NOW())");
    if (!$stmt) return;
    $stmt->bind_param('issi', $templateId, $key, $value, $sortOrder);
    $stmt->execute();
    $stmt->close();
}

function mkt_save_template_rules_and_rewards(mysqli $db, int $templateId, array $form): void
{
    $db->query('DELETE FROM marketing_campaign_template_rules WHERE template_id = ' . (int)$templateId);
    $db->query('DELETE FROM marketing_campaign_template_rewards WHERE template_id = ' . (int)$templateId);
    if (mkt_table_exists($db, 'marketing_campaign_template_exclusions')) {
        $db->query('DELETE FROM marketing_campaign_template_exclusions WHERE template_id = ' . (int)$templateId);
    }

    $sort = 1;
    foreach (['inactive_days', 'registration_days', 'tier_id', 'min_total_spent', 'min_total_orders', 'max_total_orders', 'eligible_product_id', 'eligible_threshold_promotion_id'] as $ruleKey) {
        mkt_insert_template_rule($db, $templateId, $ruleKey, (string)$form[$ruleKey], $sort++);
    }

    $sort = 1;
    foreach (['exclude_unverified', 'exclude_recent_pending_days', 'exclude_tier_id', 'exclude_customer_ids'] as $exclusionKey) {
        mkt_insert_template_exclusion($db, $templateId, $exclusionKey, (string)$form[$exclusionKey], $sort++);
    }

    if ($form['reward_type'] === 'personal_coupon') {
        mkt_insert_template_reward($db, $templateId, 'coupon_discount_type', (string)$form['coupon_discount_type'], 1);
        mkt_insert_template_reward($db, $templateId, 'coupon_discount_value', (string)$form['coupon_discount_value'], 2);
        mkt_insert_template_reward($db, $templateId, 'coupon_minimum_order_amount', (string)$form['coupon_minimum_order_amount'], 3);
        mkt_insert_template_reward($db, $templateId, 'coupon_maximum_discount_amount', (string)$form['coupon_maximum_discount_amount'], 4);
        mkt_insert_template_reward($db, $templateId, 'coupon_valid_days', (string)$form['coupon_valid_days'], 5);
        mkt_insert_template_reward($db, $templateId, 'coupon_usage_limit', (string)$form['coupon_usage_limit'], 6);
        mkt_insert_template_reward($db, $templateId, 'notification_title', (string)$form['notification_title'], 7);
        mkt_insert_template_reward($db, $templateId, 'notification_message', (string)$form['notification_message'], 8);
    } elseif ($form['reward_type'] === 'bonus_points') {
        mkt_insert_template_reward($db, $templateId, 'points_amount', (string)$form['points_amount'], 1);
        mkt_insert_template_reward($db, $templateId, 'notification_title', (string)$form['notification_title'], 2);
        mkt_insert_template_reward($db, $templateId, 'notification_message', (string)$form['notification_message'], 3);
    } else {
        mkt_insert_template_reward($db, $templateId, 'notification_title', (string)$form['notification_title'], 1);
        mkt_insert_template_reward($db, $templateId, 'notification_message', (string)$form['notification_message'], 2);
    }
}

function mkt_create_template(mysqli $db, array $source, int $adminId = 0, string $templateKind = 'custom'): array
{
    $form = mkt_collect_form_data($source);
    if ($form['code'] === '' && $form['name'] !== '') {
        $form['code'] = mkt_generate_template_code($form['name']);
    }
    $errors = mkt_validate_template_form($db, $form, 0);
    if ($errors) return [false, $errors, $form, 0];

    $stmt = $db->prepare('INSERT INTO marketing_campaign_templates (name, code, description, campaign_type, audience_type, reward_type, channel_type, status, run_mode, trigger_event, schedule_frequency, schedule_every_hours, schedule_weekday, allow_repeat, repeat_after_days, cooldown_days, cooldown_scope, starts_at, ends_at, priority, notes, template_kind, is_active, source_campaign_id, created_by, updated_by, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1, NULL, ?, ?, NOW(), NOW())');
    if (!$stmt) {
        return [false, ['تعذر تجهيز عملية حفظ القالب.'], $form, 0];
    }
    $runMode = (string)$form['run_mode'];
    $triggerEvent = $runMode === 'triggered' ? (string)$form['trigger_event'] : null;
    $scheduleFrequency = $runMode === 'scheduled' ? (string)$form['schedule_frequency'] : null;
    $scheduleEveryHours = $runMode === 'scheduled' && $form['schedule_frequency'] === 'every_x_hours' ? max(1, (int)$form['schedule_every_hours']) : null;
    $scheduleWeekday = $runMode === 'scheduled' && $form['schedule_frequency'] === 'weekly' ? (int)$form['schedule_weekday'] : null;
    $repeatAfter = $form['repeat_after_days'] === '' ? 0 : (int)$form['repeat_after_days'];
    $startsAt = mkt_form_datetime_to_mysql((string)$form['starts_at']);
    $endsAt = mkt_form_datetime_to_mysql((string)$form['ends_at']);
    $allowRepeat = (int)$form['allow_repeat'];
    $cooldownDays = max(0, (int)($form['cooldown_days'] === '' ? 0 : $form['cooldown_days']));
    $cooldownScope = (string)$form['cooldown_scope'];
    $priority = (int)$form['priority'];
    $stmt->bind_param('sssssssssssiiiiisssissii', $form['name'], $form['code'], $form['description'], $form['campaign_type'], $form['audience_type'], $form['reward_type'], $form['channel_type'], $form['status'], $runMode, $triggerEvent, $scheduleFrequency, $scheduleEveryHours, $scheduleWeekday, $allowRepeat, $repeatAfter, $cooldownDays, $cooldownScope, $startsAt, $endsAt, $priority, $form['notes'], $templateKind, $adminId, $adminId);
    $ok = $stmt->execute();
    $templateId = $ok ? (int)$stmt->insert_id : 0;
    $stmt->close();
    if (!$ok || $templateId <= 0) {
        return [false, ['تعذر حفظ القالب.'], $form, 0];
    }
    mkt_save_template_rules_and_rewards($db, $templateId, $form);
    return [true, [], $form, $templateId];
}

function mkt_update_template(mysqli $db, int $templateId, array $source, int $adminId = 0): array
{
    $form = mkt_collect_form_data($source);
    if ($form['code'] === '' && $form['name'] !== '') {
        $form['code'] = mkt_generate_template_code($form['name']);
    }
    $errors = mkt_validate_template_form($db, $form, $templateId);
    if ($errors) return [false, $errors, $form];

    $current = mkt_fetch_template($db, $templateId);
    if (!$current) {
        return [false, ['القالب غير موجود.'], $form];
    }

    $stmt = $db->prepare('UPDATE marketing_campaign_templates SET name = ?, code = ?, description = ?, campaign_type = ?, audience_type = ?, reward_type = ?, channel_type = ?, status = ?, run_mode = ?, trigger_event = ?, schedule_frequency = ?, schedule_every_hours = ?, schedule_weekday = ?, allow_repeat = ?, repeat_after_days = ?, cooldown_days = ?, cooldown_scope = ?, starts_at = ?, ends_at = ?, priority = ?, notes = ?, updated_by = ?, updated_at = NOW() WHERE id = ? LIMIT 1');
    if (!$stmt) {
        return [false, ['تعذر تجهيز عملية تحديث القالب.'], $form];
    }
    $runMode = (string)$form['run_mode'];
    $triggerEvent = $runMode === 'triggered' ? (string)$form['trigger_event'] : null;
    $scheduleFrequency = $runMode === 'scheduled' ? (string)$form['schedule_frequency'] : null;
    $scheduleEveryHours = $runMode === 'scheduled' && $form['schedule_frequency'] === 'every_x_hours' ? max(1, (int)$form['schedule_every_hours']) : null;
    $scheduleWeekday = $runMode === 'scheduled' && $form['schedule_frequency'] === 'weekly' ? (int)$form['schedule_weekday'] : null;
    $repeatAfter = $form['repeat_after_days'] === '' ? 0 : (int)$form['repeat_after_days'];
    $startsAt = mkt_form_datetime_to_mysql((string)$form['starts_at']);
    $endsAt = mkt_form_datetime_to_mysql((string)$form['ends_at']);
    $allowRepeat = (int)$form['allow_repeat'];
    $cooldownDays = max(0, (int)($form['cooldown_days'] === '' ? 0 : $form['cooldown_days']));
    $cooldownScope = (string)$form['cooldown_scope'];
    $priority = (int)$form['priority'];
    $stmt->bind_param('sssssssssssiiiiisssisii', $form['name'], $form['code'], $form['description'], $form['campaign_type'], $form['audience_type'], $form['reward_type'], $form['channel_type'], $form['status'], $runMode, $triggerEvent, $scheduleFrequency, $scheduleEveryHours, $scheduleWeekday, $allowRepeat, $repeatAfter, $cooldownDays, $cooldownScope, $startsAt, $endsAt, $priority, $form['notes'], $adminId, $templateId);
    $ok = $stmt->execute();
    $stmt->close();
    if (!$ok) {
        return [false, ['تعذر تحديث القالب.'], $form];
    }
    mkt_save_template_rules_and_rewards($db, $templateId, $form);
    return [true, [], $form];
}

function mkt_create_campaign_from_template(mysqli $db, int $templateId, int $adminId = 0): array
{
    $template = mkt_fetch_template($db, $templateId);
    if (!$template) {
        return [false, 'القالب غير موجود.', 0];
    }
    $form = mkt_template_form_from_template($db, $template);
    $form['code'] = '';
    $form['status'] = 'draft';
    $form['notes'] = trim((string)($form['notes'] ?? ''));
    $note = 'تم إنشاؤها من القالب: ' . (string)$template['name'];
    $form['notes'] = $form['notes'] !== '' ? ($form['notes'] . "\n" . $note) : $note;
    [$ok, $errors, $savedForm, $campaignId] = mkt_create_campaign($db, $form, $adminId);
    if (!$ok || $campaignId <= 0) {
        return [false, $errors ? implode(' | ', $errors) : 'تعذر إنشاء حملة من القالب.', 0];
    }
    return [true, 'تم إنشاء حملة جديدة من القالب بنجاح.', $campaignId];
}

function mkt_save_campaign_as_template(mysqli $db, int $campaignId, int $adminId = 0): array
{
    $campaign = mkt_fetch_campaign($db, $campaignId);
    if (!$campaign) {
        return [false, 'الحملة غير موجودة.', 0];
    }
    $form = mkt_form_from_campaign($db, $campaign);
    $form['code'] = '';
    $form['name'] = trim((string)$campaign['name']) . ' - قالب';
    $note = 'تم حفظه من الحملة #' . $campaignId;
    $form['notes'] = trim((string)($form['notes'] ?? ''));
    $form['notes'] = $form['notes'] !== '' ? ($form['notes'] . "\n" . $note) : $note;
    [$ok, $errors, $savedForm, $templateId] = mkt_create_template($db, $form, $adminId, 'custom');
    if (!$ok || $templateId <= 0) {
        return [false, $errors ? implode(' | ', $errors) : 'تعذر حفظ الحملة كقالب.', 0];
    }
    $stmt = $db->prepare('UPDATE marketing_campaign_templates SET source_campaign_id = ? WHERE id = ? LIMIT 1');
    if ($stmt) {
        $stmt->bind_param('ii', $campaignId, $templateId);
        $stmt->execute();
        $stmt->close();
    }
    return [true, 'تم حفظ الحملة كقالب جديد بنجاح.', $templateId];
}
