<?php
declare(strict_types=1);

/**
 * Central Side Product Sync Helper
 * Path: central/app/helpers/product_sync_helper.php
 *
 * Purpose:
 * - Queue only product/client related central data for product-side sync.
 * - Columns are aligned with smartlif_zivo (27).sql.
 * - Product/client isolation is enforced by product_id/client_id filters.
 * - Sensitive product API secrets are used only for queue authentication and are not included in sync payload datasets.
 *
 * Required central table:
 * - client_product_sync_queue
 */

if (!defined('ZIVO_PRODUCT_SYNC_SCHEMA_VERSION')) {
    define('ZIVO_PRODUCT_SYNC_SCHEMA_VERSION', 5);
}

function zivoProductSyncConfig(): array
{
    return [
        'central_banks' => [
            'scope' => 'product',
            'mode' => 'many',
            'table_candidates' => ['central_banks'],
            'required' => false,
            'order' => ['sort_order ASC', 'bank_name ASC', 'bank_id ASC'],
            'limit' => 1000,
            'filters' => [
                ['column' => 'status', 'operator' => '=', 'value' => 'active', 'optional' => true],
            ],
            'columns' => [
                'bank_id',
                'bank_name',
                'bank_type',
                'status',
                'sort_order',
                'created_at',
                'updated_at',
            ],
        ],

        'platform_products' => [
            'scope' => 'product',
            'mode' => 'one',
            'table_candidates' => ['platform_products'],
            'required' => true,
            'order' => [],
            'limit' => 1,
            'filters' => [
                ['column' => 'product_id', 'operator' => '=', 'context' => 'product_id', 'optional' => false],
                ['column' => 'status', 'operator' => '=', 'value' => 'active', 'optional' => true],
            ],
            'columns' => [
                'product_id',
                'product_name',
                'product_code',
                'product_domain',
                'status',
                'created_at',
                'updated_at',
            ],
        ],

        'clients' => [
            'scope' => 'client',
            'mode' => 'one',
            'table_candidates' => ['clients'],
            'required' => true,
            'order' => [],
            'limit' => 1,
            'filters' => [
                ['column' => 'client_id', 'operator' => '=', 'context' => 'client_id', 'optional' => false],
                ['column' => 'product_id', 'operator' => '=', 'context' => 'product_id', 'optional' => false],
            ],
            'columns' => [
                'client_id',
                'client_uid',
                'client_code',
                'client_name',
                'client_slug',
                'product_id',
                'trade_license_no',
                'owner_name',
                'owner_contact',
                'owner_email',
                'owner_nid',
                'client_contact',
                'client_email',
                'client_address',
                'client_logo',
                'owner_photo',
                'owner_document',
                'district',
                'referred_by_member_id',
                'status',
                'verify_status',
                'sync_status',
                'last_synced_at',
                'created_by',
                'created_at',
                'updated_at',
                'created_source',
                'created_by_platform_user_id',
                'created_by_employee_id',
                'created_by_referral_member_id',
            ],
        ],

        'client_subscriptions' => [
            'scope' => 'client',
            'mode' => 'many',
            'table_candidates' => ['client_subscriptions'],
            'required' => false,
            'order' => ['subscription_id DESC'],
            'limit' => 50,
            'filters' => [
                ['column' => 'client_id', 'operator' => '=', 'context' => 'client_id', 'optional' => false],
                ['column' => 'product_id', 'operator' => '=', 'context' => 'product_id', 'optional' => false],
            ],
            'columns' => [
                'subscription_id',
                'invoice_no',
                'client_id',
                'product_id',
                'package_id',
                'subscription_action',
                'previous_subscription_id',
                'subscription_type',
                'start_date',
                'previous_expire_date',
                'expire_date',
                'gross_amount',
                'discount_amount',
                'payable_amount',
                'paid_amount',
                'due_amount',
                'current_subscription',
                'assigned_by_type',
                'assigned_by_id',
                'assigned_at',
                'approved_by_type',
                'approved_by_id',
                'approved_at',
                'payment_status',
                'invoice_status',
                'status',
                'admin_notes',
                'created_by',
                'updated_by',
                'created_at',
                'updated_at',
                'cancelled_at',
                'cancelled_by_type',
                'cancelled_by_id',
                'cancel_reason',
            ],
        ],

        'active_client_subscription' => [
            'scope' => 'client',
            'mode' => 'one',
            'table_candidates' => ['client_subscriptions'],
            'required' => false,
            'order' => ["current_subscription = 'yes' DESC", 'subscription_id DESC'],
            'limit' => 1,
            'filters' => [
                ['column' => 'client_id', 'operator' => '=', 'context' => 'client_id', 'optional' => false],
                ['column' => 'product_id', 'operator' => '=', 'context' => 'product_id', 'optional' => false],
            ],
            'columns' => [
                'subscription_id',
                'invoice_no',
                'client_id',
                'product_id',
                'package_id',
                'subscription_action',
                'previous_subscription_id',
                'subscription_type',
                'start_date',
                'previous_expire_date',
                'expire_date',
                'gross_amount',
                'discount_amount',
                'payable_amount',
                'paid_amount',
                'due_amount',
                'current_subscription',
                'assigned_by_type',
                'assigned_by_id',
                'assigned_at',
                'approved_by_type',
                'approved_by_id',
                'approved_at',
                'payment_status',
                'invoice_status',
                'status',
                'admin_notes',
                'created_by',
                'updated_by',
                'created_at',
                'updated_at',
                'cancelled_at',
                'cancelled_by_type',
                'cancelled_by_id',
                'cancel_reason',
            ],
        ],

        'client_subscription_price_override' => [
            'scope' => 'client',
            'mode' => 'many',
            'table_candidates' => ['client_subscription_price_override'],
            'required' => false,
            'order' => ['override_id DESC'],
            'limit' => 100,
            'filters' => [
                ['column' => 'client_id', 'operator' => '=', 'context' => 'client_id', 'optional' => false],
                ['column' => 'product_id', 'operator' => '=', 'context' => 'product_id', 'optional' => false],
            ],
            'columns' => [
                'override_id',
                'client_id',
                'product_id',
                'package_id',
                'offer_type',
                'price_mode',
                'fixed_price',
                'discount_percent',
                'valid_from',
                'valid_until',
                'status',
                'created_by_type',
                'created_by_id',
                'created_at',
                'updated_at',
            ],
        ],

        'client_payment_history' => [
            'scope' => 'client',
            'mode' => 'many',
            'table_candidates' => ['client_payment_history'],
            'required' => false,
            'order' => ['payment_id DESC'],
            'limit' => 50,
            'filters' => [
                ['column' => 'client_id', 'operator' => '=', 'context' => 'client_id', 'optional' => false],
                ['column' => 'product_id', 'operator' => '=', 'context' => 'product_id', 'optional' => false],
            ],
            'columns' => [
                'payment_id',
                'client_id',
                'product_id',
                'subscription_id',
                'package_id',
                'invoice_id',
                'license_id',
                'invoice_no',
                'company_payment_account_id',
                'payment_type',
                'payment_status',
                'amount',
                'discount_amount',
                'payable_amount',
                'paid_amount',
                'due_amount',
                'transaction_id',
                'sender_number',
                'receiver_number',
                'payment_date',
                'note',
                'approval_source',
                'approved_by',
                'approved_at',
                'created_by',
                'created_at',
                'updated_at',
            ],
        ],

        'client_permission_pages' => [
            'scope' => 'product',
            'mode' => 'many',
            'table_candidates' => ['client_permission_pages'],
            'required' => false,
            'order' => ['short_order ASC', 'id ASC'],
            'limit' => 3000,
            'filters' => [
                ['column' => 'product_id', 'operator' => '=', 'context' => 'product_id', 'optional' => false],
                ['column' => 'status', 'operator' => '=', 'value' => 'active', 'optional' => true],
            ],
            'columns' => [
                'id',
                'product_id',
                'module_name',
                'page_name',
                'page_keyword',
                'short_order',
                'status',
                'created_at',
                'updated_at',
                'added_by',
            ],
        ],

        'platform_company_payment_accounts' => [
            'scope' => 'product',
            'mode' => 'many',
            'table_candidates' => ['platform_company_payment_accounts'],
            'required' => false,
            'order' => ['company_payment_account_id ASC'],
            'limit' => 500,
            'filters' => [
                ['column' => 'status', 'operator' => '=', 'value' => 'active', 'optional' => true],
            ],
            'columns' => [
                'company_payment_account_id',
                'account_category',
                'bank_id',
                'mobile_provider',
                'account_name',
                'account_number',
                'branch_name',
                'routing_number',
                'account_type',
                'status',
                'created_at',
                'updated_at',
            ],
        ],

        'platform_invoices' => [
            'scope' => 'client',
            'mode' => 'many',
            'table_candidates' => ['platform_invoices'],
            'required' => false,
            'order' => ['invoice_id DESC'],
            'limit' => 50,
            'filters' => [
                ['column' => 'client_id', 'operator' => '=', 'context' => 'client_id', 'optional' => false],
                ['column' => 'product_id', 'operator' => '=', 'context' => 'product_id', 'optional' => false],
            ],
            'columns' => [
                'invoice_id',
                'invoice_no',
                'client_id',
                'product_id',
                'license_id',
                'subscription_id',
                'package_id',
                'invoice_type',
                'billing_cycle',
                'invoice_date',
                'due_date',
                'period_start',
                'period_end',
                'gross_amount',
                'discount_amount',
                'payable_amount',
                'paid_amount',
                'due_amount',
                'status',
                'created_by_type',
                'created_by_id',
                'created_at',
                'updated_at',
            ],
        ],

        'platform_licenses' => [
            'scope' => 'client',
            'mode' => 'many',
            'table_candidates' => ['platform_licenses'],
            'required' => false,
            'order' => ['license_id DESC'],
            'limit' => 20,
            'filters' => [
                ['column' => 'client_id', 'operator' => '=', 'context' => 'client_id', 'optional' => false],
                ['column' => 'product_id', 'operator' => '=', 'context' => 'product_id', 'optional' => false],
            ],
            'columns' => [
                'license_id',
                'client_id',
                'product_id',
                'package_id',
                'subscription_id',
                'license_key',
                'licensed_domain',
                'start_date',
                'expire_date',
                'max_user',
                'max_branch',
                'status',
                'last_checked_at',
                'created_at',
                'updated_at',
            ],
        ],

        'active_platform_license' => [
            'scope' => 'client',
            'mode' => 'one',
            'table_candidates' => ['platform_licenses'],
            'required' => false,
            'order' => ['license_id DESC'],
            'limit' => 1,
            'filters' => [
                ['column' => 'client_id', 'operator' => '=', 'context' => 'client_id', 'optional' => false],
                ['column' => 'product_id', 'operator' => '=', 'context' => 'product_id', 'optional' => false],
                ['column' => 'status', 'operator' => '=', 'value' => 'active', 'optional' => true],
            ],
            'columns' => [
                'license_id',
                'client_id',
                'product_id',
                'package_id',
                'subscription_id',
                'license_key',
                'licensed_domain',
                'start_date',
                'expire_date',
                'max_user',
                'max_branch',
                'status',
                'last_checked_at',
                'created_at',
                'updated_at',
            ],
        ],

        'platform_package_master' => [
            'scope' => 'product',
            'mode' => 'many',
            'table_candidates' => ['platform_package_master'],
            'required' => false,
            'order' => ['sort_order ASC', 'package_id ASC'],
            'limit' => 1000,
            'filters' => [
                ['column' => 'product_id', 'operator' => '=', 'context' => 'product_id', 'optional' => false],
                ['column' => 'status', 'operator' => '=', 'value' => 'active', 'optional' => true],
            ],
            'columns' => [
                'package_id',
                'product_id',
                'package_name',
                'package_slug',
                'duration_days',
                'max_branch',
                'max_user',
                'monthly_price',
                'yearly_price',
                'is_trial',
                'sort_order',
                'status',
                'created_at',
            ],
        ],

        'platform_package_features' => [
            'scope' => 'product',
            'mode' => 'many',
            'table_candidates' => ['platform_package_features'],
            'required' => false,
            'order' => ['package_id ASC', 'feature_id ASC'],
            'limit' => 10000,
            'filters' => [
                ['column' => 'product_id', 'operator' => '=', 'context' => 'product_id', 'optional' => false],
            ],
            'columns' => [
                'feature_id',
                'package_id',
                'product_id',
                'feature_key',
                'feature_name',
                'access_status',
                'created_at',
            ],
        ],

        'platform_package_feature_master' => [
            'scope' => 'product',
            'mode' => 'many',
            'table_candidates' => ['platform_package_feature_master'],
            'required' => false,
            'order' => ['sort_order ASC', 'feature_master_id ASC'],
            'limit' => 10000,
            'filters' => [
                ['column' => 'product_id', 'operator' => '=', 'context' => 'product_id', 'optional' => false],
                ['column' => 'status', 'operator' => '=', 'value' => 'active', 'optional' => true],
            ],
            'columns' => [
                'feature_master_id',
                'product_id',
                'feature_key',
                'feature_name',
                'feature_group',
                'sort_order',
                'status',
                'created_at',
                'updated_at',
            ],
        ],

        'platform_email_configuration' => [
            'scope' => 'product',
            'mode' => 'many',
            'table_candidates' => ['platform_email_configuration'],
            'required' => false,
            'order' => ['sn DESC'],
            'limit' => 20,
            'filters' => [
                ['column' => 'product_id', 'operator' => '=', 'context' => 'product_id', 'optional' => false],
            ],
            'columns' => [
                'sn',
                'product_id',
                'host',
                'username',
                'password',
                'setfrom',
                'type',
            ],
        ],

        'platform_sms_configuration' => [
            'scope' => 'product',
            'mode' => 'many',
            'table_candidates' => ['platform_sms_configuration'],
            'required' => false,
            'order' => ['sn DESC'],
            'limit' => 20,
            'filters' => [
                ['column' => 'product_id', 'operator' => '=', 'context' => 'product_id', 'optional' => false],
                ['column' => 'status', 'operator' => '=', 'value' => 'active', 'optional' => true],
            ],
            'columns' => [
                'sn',
                'product_id',
                'type',
                'api_key',
                'status',
                'url',
                'sender_id',
            ],
        ],

        'platform_notifications' => [
            'scope' => 'client',
            'mode' => 'many',
            'table_candidates' => ['platform_notifications'],
            'required' => false,
            'order' => ['notification_id DESC'],
            'limit' => 100,
            'filters' => [
                ['column' => 'notification_scope', 'operator' => '=', 'value' => 'product', 'optional' => true],
                ['column' => 'product_id', 'operator' => '=', 'context' => 'product_id', 'optional' => false],
                ['column' => 'client_id', 'operator' => '=', 'context' => 'client_id', 'optional' => false],
            ],
            'columns' => [
                'notification_id',
                'notification_scope',
                'product_id',
                'client_id',
                'title',
                'message',
                'notification_type',
                'action_url',
                'reference_type',
                'reference_id',
                'created_by_type',
                'created_by_id',
                'is_synced_to_product',
                'synced_at',
                'created_at',
            ],
        ],

        'platform_notification_receivers' => [
            'scope' => 'client',
            'mode' => 'many',
            'table_candidates' => ['platform_notification_receivers'],
            'required' => false,
            'order' => ['receiver_row_id DESC'],
            'limit' => 200,
            'filters' => [
                ['column' => 'product_id', 'operator' => '=', 'context' => 'product_id', 'optional' => false],
                ['column' => 'client_id', 'operator' => '=', 'context' => 'client_id', 'optional' => false],
                ['column' => 'receiver_type', 'operator' => '=', 'value' => 'client', 'optional' => true],
            ],
            'columns' => [
                'receiver_row_id',
                'notification_id',
                'receiver_type',
                'receiver_id',
                'product_id',
                'client_id',
                'is_read',
                'read_at',
                'created_at',
            ],
        ],
    ];
}

function zivoTableExists(mysqli $con, string $table): bool
{
    static $cache = [];

    if (array_key_exists($table, $cache)) {
        return $cache[$table];
    }

    $stmt = $con->prepare("
        SELECT COUNT(*) AS total
        FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = ?
    ");
    $stmt->bind_param('s', $table);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc();
    $stmt->close();

    return $cache[$table] = ((int)($row['total'] ?? 0) > 0);
}

function zivoResolveDatasetTable(mysqli $con, array $datasetConfig): ?string
{
    foreach ((array)($datasetConfig['table_candidates'] ?? []) as $table) {
        $table = trim((string)$table);

        if ($table !== '' && zivoTableExists($con, $table)) {
            return $table;
        }
    }

    $table = trim((string)($datasetConfig['table'] ?? ''));

    if ($table !== '' && zivoTableExists($con, $table)) {
        return $table;
    }

    return null;
}

function zivoTableColumns(mysqli $con, string $table): array
{
    static $cache = [];

    if (isset($cache[$table])) {
        return $cache[$table];
    }

    $stmt = $con->prepare("
        SELECT COLUMN_NAME
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = ?
        ORDER BY ORDINAL_POSITION ASC
    ");
    $stmt->bind_param('s', $table);
    $stmt->execute();
    $rows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    $stmt->close();

    $columns = [];

    foreach ($rows as $row) {
        $columns[] = (string)$row['COLUMN_NAME'];
    }

    $cache[$table] = $columns;

    return $columns;
}

function zivoColumnExists(mysqli $con, string $table, string $column): bool
{
    return in_array($column, zivoTableColumns($con, $table), true);
}

function zivoBacktick(string $identifier): string
{
    return '`' . str_replace('`', '', $identifier) . '`';
}

function zivoSafeColumns(mysqli $con, string $table, array $requestedColumns): array
{
    $actualColumns = zivoTableColumns($con, $table);
    $columns = array_values(array_unique(array_intersect($requestedColumns, $actualColumns)));

    if (!$columns) {
        return ['*'];
    }

    return array_map('zivoBacktick', $columns);
}

function zivoInferBindType($value): string
{
    if (is_int($value)) {
        return 'i';
    }

    if (is_float($value)) {
        return 'd';
    }

    return 's';
}

function zivoBuildDatasetWhere(mysqli $con, string $table, array $datasetConfig, array $context): array
{
    $whereParts = ['1 = 1'];
    $types = '';
    $params = [];

    foreach ((array)($datasetConfig['filters'] ?? []) as $filter) {
        $column = trim((string)($filter['column'] ?? ''));
        $operator = strtoupper(trim((string)($filter['operator'] ?? '=')));
        $optional = (bool)($filter['optional'] ?? true);

        if ($column === '') {
            continue;
        }

        if (!zivoColumnExists($con, $table, $column)) {
            if (!$optional) {
                throw new RuntimeException("Required sync column missing: {$table}.{$column}");
            }

            continue;
        }

        if (!in_array($operator, ['=', '!=', '<>', '>', '>=', '<', '<=', 'LIKE'], true)) {
            throw new RuntimeException('Unsafe sync filter operator: ' . $operator);
        }

        if (array_key_exists('context', $filter)) {
            $contextKey = (string)$filter['context'];

            if (!array_key_exists($contextKey, $context)) {
                if (!$optional) {
                    throw new RuntimeException('Missing sync context parameter: ' . $contextKey);
                }

                continue;
            }

            $value = $context[$contextKey];
        } else {
            $value = $filter['value'] ?? null;
        }

        if ($value === null || $value === '') {
            if (!$optional) {
                throw new RuntimeException("Required sync filter value empty for {$table}.{$column}");
            }

            continue;
        }

        $whereParts[] = zivoBacktick($column) . " {$operator} ?";
        $types .= zivoInferBindType($value);
        $params[] = $value;
    }

    return [implode(' AND ', $whereParts), $types, $params];
}

function zivoBuildDatasetOrder(mysqli $con, string $table, array $datasetConfig): string
{
    $safeOrders = [];

    foreach ((array)($datasetConfig['order'] ?? []) as $order) {
        $order = trim((string)$order);

        if ($order === '') {
            continue;
        }

        if (preg_match("~^([a-zA-Z0-9_]+)\\s+(ASC|DESC)$~i", $order, $m)) {
            if (zivoColumnExists($con, $table, $m[1])) {
                $safeOrders[] = zivoBacktick($m[1]) . ' ' . strtoupper($m[2]);
            }

            continue;
        }

        if (preg_match("~^([a-zA-Z0-9_]+)\\s*=\\s*'([^']*)'\\s+(ASC|DESC)$~i", $order, $m)) {
            if (zivoColumnExists($con, $table, $m[1])) {
                $safeOrders[] = zivoBacktick($m[1]) . " = '" . str_replace("'", "''", $m[2]) . "' " . strtoupper($m[3]);
            }
        }
    }

    return implode(', ', $safeOrders);
}

function zivoFetchConfiguredDataset(mysqli $con, string $datasetKey, array $datasetConfig, array $context)
{
    $table = zivoResolveDatasetTable($con, $datasetConfig);

    if ($table === null) {
        if (!empty($datasetConfig['required'])) {
            $candidates = implode(', ', (array)($datasetConfig['table_candidates'] ?? [$datasetConfig['table'] ?? $datasetKey]));
            throw new RuntimeException('Missing required sync table. Candidates: ' . $candidates);
        }

        return ($datasetConfig['mode'] ?? 'many') === 'one' ? null : [];
    }

    $columns = implode(', ', zivoSafeColumns($con, $table, (array)($datasetConfig['columns'] ?? [])));
    [$where, $types, $params] = zivoBuildDatasetWhere($con, $table, $datasetConfig, $context);
    $order = zivoBuildDatasetOrder($con, $table, $datasetConfig);
    $limit = max(0, (int)($datasetConfig['limit'] ?? 0));

    $sql = 'SELECT ' . $columns . ' FROM ' . zivoBacktick($table) . ' WHERE ' . $where;

    if ($order !== '') {
        $sql .= ' ORDER BY ' . $order;
    }

    if ($limit > 0) {
        $sql .= ' LIMIT ' . $limit;
    }

    $stmt = $con->prepare($sql);

    if ($types !== '') {
        $stmt->bind_param($types, ...$params);
    }

    $stmt->execute();
    $result = $stmt->get_result();

    if (($datasetConfig['mode'] ?? 'many') === 'one') {
        $row = $result->fetch_assoc();
        $stmt->close();

        return $row ?: null;
    }

    $rows = $result->fetch_all(MYSQLI_ASSOC);
    $stmt->close();

    return $rows;
}

function zivoFetchOne(mysqli $con, string $sql, string $types = '', array $params = []): ?array
{
    $stmt = $con->prepare($sql);

    if ($types !== '') {
        $stmt->bind_param($types, ...$params);
    }

    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc();
    $stmt->close();

    return $row ?: null;
}

function zivoNormalizeApiUrl(string $productDomain): string
{
    $productDomain = trim($productDomain);

    if ($productDomain === '') {
        throw new RuntimeException('Product domain is empty.');
    }

    if (!preg_match('~^https?://~i', $productDomain)) {
        $productDomain = 'https://' . $productDomain;
    }

    return rtrim($productDomain, '/') . '/api/central/product_sync_receiver.php';
}

function zivoResolveProductApiSecret(array $product): string
{
    $secret = trim((string)($product['api_secret'] ?? ''));

    if ($secret === '') {
        $secret = trim((string)($product['product_api_secret'] ?? ''));
    }

    if ($secret === '') {
        throw new RuntimeException('Product API secret is empty.');
    }

    return $secret;
}

function zivoGetClientProductId(mysqli $con, int $clientId): int
{
    $client = zivoFetchOne(
        $con,
        'SELECT client_id, product_id FROM clients WHERE client_id = ? LIMIT 1',
        'i',
        [$clientId]
    );

    if (!$client) {
        throw new RuntimeException('Client not found for sync.');
    }

    $productId = (int)($client['product_id'] ?? 0);

    if ($productId <= 0) {
        throw new RuntimeException('Client product_id is empty.');
    }

    return $productId;
}

function zivoGetProductForSync(mysqli $con, int $productId): array
{
    $product = zivoFetchOne(
        $con,
        "SELECT * FROM platform_products WHERE product_id = ? AND status = 'active' LIMIT 1",
        'i',
        [$productId]
    );

    if (!$product) {
        throw new RuntimeException('Active product not found for sync.');
    }

    return $product;
}

function zivoBuildCentralToProductPayload(mysqli $con, int $productId, ?int $clientId = null, string $syncAction = 'upsert'): array
{
    $context = [
        'product_id' => $productId,
        'client_id' => $clientId,
    ];

    $payload = [
        'schema_version' => ZIVO_PRODUCT_SYNC_SCHEMA_VERSION,
        'generated_at' => date('Y-m-d H:i:s'),
        'sync_source' => 'central',
        'sync_action' => $syncAction,
        'product_id' => $productId,
        'client_id' => $clientId,
        'datasets' => [],
        'sync_scope' => [
                'product_id' => $productId,
                'client_id' => $clientId,
                'package_feature_package_ids' => [],
        ],
    ];

    foreach (zivoProductSyncConfig() as $datasetKey => $datasetConfig) {
        $scope = (string)($datasetConfig['scope'] ?? 'product');

        if ($scope === 'client' && (!$clientId || $clientId <= 0)) {
            continue;
        }

        $payload['datasets'][$datasetKey] = zivoFetchConfiguredDataset(
            $con,
            (string)$datasetKey,
            $datasetConfig,
            $context
        );
    }
    
    
    if (isset($payload['datasets']['platform_package_master']) && is_array($payload['datasets']['platform_package_master'])) {
            $packageIds = [];
            $packageRows = $payload['datasets']['platform_package_master'];

            if (isset($packageRows['package_id'])) {
                $packageRows = [$packageRows];
            }
        
            foreach ($packageRows as $row) {
                if (is_array($row) && !empty($row['package_id'])) {
                    $packageIds[] = (int)$row['package_id'];
                }
            }
        
            $payload['sync_scope']['package_feature_package_ids'] = array_values(array_unique($packageIds));
        }

    return $payload;
}

function zivoBuildClientProductPayload(mysqli $con, int $clientId, string $syncAction = 'upsert_client'): array
{
    $productId = zivoGetClientProductId($con, $clientId);

    return zivoBuildCentralToProductPayload($con, $productId, $clientId, $syncAction);
}

function zivoBuildProductMasterPayload(mysqli $con, int $productId, string $syncAction = 'upsert_product_master'): array
{
    zivoGetProductForSync($con, $productId);

    return zivoBuildCentralToProductPayload($con, $productId, null, $syncAction);
}

function zivoInsertProductSyncQueue(mysqli $con, int $productId, ?int $clientId, string $syncAction, array $payload): int
{
    if (!zivoTableExists($con, 'client_product_sync_queue')) {
        throw new RuntimeException('Missing table: client_product_sync_queue.');
    }

    $product = zivoGetProductForSync($con, $productId);
    $apiUrl = zivoNormalizeApiUrl((string)($product['product_domain'] ?? ''));
    $apiSecret = zivoResolveProductApiSecret($product);

    $payloadJson = json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);

    if ($payloadJson === false) {
        throw new RuntimeException('Failed to encode sync payload: ' . json_last_error_msg());
    }

    $clientIdForDb = $clientId ?: 0;

    $stmt = $con->prepare("
        INSERT INTO client_product_sync_queue
            (client_id, product_id, sync_action, api_url, api_secret, payload_json, status, retry_count, created_at)
        VALUES (?, ?, ?, ?, ?, ?, 'pending', 0, NOW())
    ");
    $stmt->bind_param('iissss', $clientIdForDb, $productId, $syncAction, $apiUrl, $apiSecret, $payloadJson);
    $stmt->execute();

    $queueId = (int)$stmt->insert_id;
    $stmt->close();

    return $queueId;
}

function zivoQueueClientProductSync(mysqli $con, int $clientId, string $syncAction = 'upsert_client'): int
{
    $productId = zivoGetClientProductId($con, $clientId);
    $payload = zivoBuildCentralToProductPayload($con, $productId, $clientId, $syncAction);

    return zivoInsertProductSyncQueue($con, $productId, $clientId, $syncAction, $payload);
}

function zivoQueueProductMasterSync(mysqli $con, int $productId, string $syncAction = 'upsert_product_master'): int
{
    $payload = zivoBuildProductMasterPayload($con, $productId, $syncAction);

    return zivoInsertProductSyncQueue($con, $productId, null, $syncAction, $payload);
}

function zivoQueueAllClientsOfProductSync(mysqli $con, int $productId, string $syncAction = 'upsert_client'): array
{
    $stmt = $con->prepare('SELECT client_id FROM clients WHERE product_id = ? ORDER BY client_id ASC');
    $stmt->bind_param('i', $productId);
    $stmt->execute();

    $rows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    $stmt->close();

    $queuedClients = [];

    foreach ($rows as $row) {
        $clientId = (int)$row['client_id'];
        $queuedClients[$clientId] = zivoQueueClientProductSync($con, $clientId, $syncAction);
    }

    return $queuedClients;
}

function zivoQueueFullProductResync(mysqli $con, int $productId): array
{
    return [
        'product_master_queue_id' => zivoQueueProductMasterSync($con, $productId, 'full_product_master_resync'),
        'client_queue_ids' => zivoQueueAllClientsOfProductSync($con, $productId, 'full_client_resync'),
    ];
}

function zivoQueueClientDataResync(mysqli $con, int $clientId): int
{
    return zivoQueueClientProductSync($con, $clientId, 'client_data_resync');
}

function zivoQueueProductSettingsResync(mysqli $con, int $productId): int
{
    return zivoQueueProductMasterSync($con, $productId, 'product_settings_resync');
}

function zivoQueueProductPackageResync(mysqli $con, int $productId): int
{
    return zivoQueueProductMasterSync($con, $productId, 'product_package_resync');
}