<?php
// includes/recipes_sync.php
if (!defined('ABSPATH')) exit;

/**
 * Convert ISO8601 like 2026-01-08T20:32:21Z to MySQL DATETIME (GMT). Returns null if invalid.
 */
function allspice_iso_to_mysql_gmt(?string $iso): ?string {
    $iso = trim((string)$iso);
    if ($iso === '') return null;
    try {
        $dt = new DateTimeImmutable($iso);
        $utc = $dt->setTimezone(new DateTimeZone('UTC'));
        return $utc->format('Y-m-d H:i:s');
    } catch (Throwable $e) {
        return null;
    }
}

/**
 * Upsert recipe rows into wp_allspice_recipes.
 * - import_status ("imported")
 * - last_checked (ISO8601)
 * - recipe_id
 * - wp_post_id (optional)
 * - status ("recipe")
 * - url
 * - url_key
 */
function allspice_upsert_recipes(array $items): int {
    global $wpdb;
    $table = allspice_recipes_table_ident(); // validated & backticked
    $now = gmdate('Y-m-d H:i:s');
    $processed = 0;
    foreach ($items as $it) {
        if (!is_array($it)) continue;
        $url_key = isset($it['url_key']) ? trim((string)$it['url_key']) : '';
        if ($url_key === '') continue;
        $url = array_key_exists('url', $it) ? (string)$it['url'] : null;
        $recipe_id = array_key_exists('recipe_id', $it) ? (string)$it['recipe_id'] : null;
        $wp_post_id = array_key_exists('wp_post_id', $it) ? (string)$it['wp_post_id'] : null; // STRING
        $status = array_key_exists('status', $it) ? (string)$it['status'] : null;
        $import_status = array_key_exists('import_status', $it) ? (string)$it['import_status'] : null;
        $last_checked_gmt = null;
        if (array_key_exists('last_checked', $it)) {
            $last_checked_gmt = allspice_iso_to_mysql_gmt($it['last_checked']);
        }
        $url = ($url !== null && trim($url) === '') ? null : $url;
        $recipe_id = ($recipe_id !== null && trim($recipe_id) === '') ? null : $recipe_id;
        $wp_post_id = ($wp_post_id !== null && trim($wp_post_id) === '') ? null : $wp_post_id;
        $status = ($status !== null && trim($status) === '') ? null : $status;
        $import_status = ($import_status !== null && trim($import_status) === '') ? null : $import_status;
        $ingredients_json = null;
        if (array_key_exists('ingredients', $it) && is_array($it['ingredients'])) {
            $ingredients_json = wp_json_encode($it['ingredients'], JSON_UNESCAPED_SLASHES);
        }
        $time_text = array_key_exists('time', $it) ? trim((string)$it['time']) : null;
        $time_text = ($time_text !== null && $time_text === '') ? null : $time_text;
        $recipe_title = array_key_exists('recipe_title', $it) ? trim((string)$it['recipe_title']) : null;
        $recipe_title = ($recipe_title !== null && $recipe_title === '') ? null : $recipe_title;
        $meta_description = array_key_exists('meta_description', $it) ? trim((string)$it['meta_description']) : null;
        $meta_description = ($meta_description !== null && $meta_description === '') ? null : $meta_description;
        $meta_description_valid = array_key_exists('meta_description_valid', $it) ? $it['meta_description_valid'] : null;
        $meta_description_valid = ($meta_description_valid === null) ? null : (int)(bool)$meta_description_valid;
        $takeaways_json = null;
        if (array_key_exists('takeaways', $it) && is_array($it['takeaways'])) {
            $takeaways_json = wp_json_encode($it['takeaways'], JSON_UNESCAPED_SLASHES);
        }
        $faqs_json = null;
        if (array_key_exists('faqs', $it) && is_array($it['faqs'])) {
            $faqs_json = wp_json_encode($it['faqs'], JSON_UNESCAPED_SLASHES);
        }
        $servings = array_key_exists('servings', $it) ? $it['servings'] : null;
        $servings = (is_numeric($servings) ? (int)round((float)$servings) : null);
        $calories = array_key_exists('calories', $it) ? $it['calories'] : null;
        $calories = (is_numeric($calories) ? (int)round((float)$calories) : null);

        if (function_exists('allspice_recipes_table_ensure_columns')) {
            allspice_recipes_table_ensure_columns();
        }
        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table identifier cannot be parameterized; ident helper validated/allowlisted.
        $sql = $wpdb->prepare(
            "INSERT INTO {$table}
                (url_key, url, recipe_id, wp_post_id, status, import_status, last_checked_gmt, synced_at_gmt,
                 ingredients_json, time_text, recipe_title, meta_description, meta_description_valid, takeaways_json, faqs_json, servings, calories)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s,
                        %s, %s, %s, %s, %s, %s, %s, %s, %s)
             ON DUPLICATE KEY UPDATE
                url = COALESCE(VALUES(url), url),
                recipe_id = COALESCE(VALUES(recipe_id), recipe_id),
                wp_post_id = COALESCE(VALUES(wp_post_id), wp_post_id),
                status = COALESCE(VALUES(status), status),
                import_status = COALESCE(VALUES(import_status), import_status),
                last_checked_gmt = COALESCE(VALUES(last_checked_gmt), last_checked_gmt),
                synced_at_gmt = VALUES(synced_at_gmt),
                ingredients_json = COALESCE(VALUES(ingredients_json), ingredients_json),
                time_text = COALESCE(VALUES(time_text), time_text),
                recipe_title = COALESCE(VALUES(recipe_title), recipe_title),
                meta_description = COALESCE(VALUES(meta_description), meta_description),
                meta_description_valid = COALESCE(VALUES(meta_description_valid), meta_description_valid),
                takeaways_json = COALESCE(VALUES(takeaways_json), takeaways_json),
                faqs_json = COALESCE(VALUES(faqs_json), faqs_json),
                servings = COALESCE(VALUES(servings), servings),
                calories = COALESCE(VALUES(calories), calories)",
            $url_key,
            $url,
            $recipe_id,
            $wp_post_id,
            $status,
            $import_status,
            $last_checked_gmt,
            $now,
            $ingredients_json,
            $time_text,
            $recipe_title,
            $meta_description,
            $meta_description_valid === null ? null : (string)$meta_description_valid,
            $takeaways_json,
            $faqs_json,
            ($servings === null ? null : (string)$servings),
            ($calories === null ? null : (string)$calories)
        );
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table write.
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching -- Writes don't benefit from caching; higher-level caches are invalidated separately.
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- $sql prepared above.
        // phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared SQL; table identifier allowlisted in allspice_recipes_table_ident().
        $ok = $wpdb->query($sql);
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
        // phpcs:enable PluginCheck.Security.DirectDB.UnescapedDBParameter
        if ($ok !== false) $processed++;
    }
    return $processed;
}

/**
* Local recipe count
*/
function allspice_recipes_count(): int {
    global $wpdb;
    if (!is_object($wpdb)) return 0;
    if (!allspice_recipes_table_exists()) return 0;
    $cache_key = allspice_recipes_count_cache_key();
    $group = allspice_db_cache_group();
    $cached = wp_cache_get($cache_key, $group);
    if ($cached !== false) return (int) $cached;
    $table = allspice_recipes_table_ident(); // validated & backticked
    // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table identifier cannot be parameterized; ident helper is validated/allowlisted.
    $sql = $wpdb->prepare(
        "SELECT COUNT(*) FROM {$table} WHERE status = %s AND import_status = %s",
        'recipe',
        'imported'
    );
    // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table write.
    // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching -- Writes don't benefit from caching; higher-level caches are invalidated separately.
    // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- $sql prepared above.
    // phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared SQL; table identifier allowlisted in allspice_recipes_table_ident().
    $count = (int) $wpdb->get_var($sql);
    // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery
    // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching
    // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
    // phpcs:enable PluginCheck.Security.DirectDB.UnescapedDBParameter
    wp_cache_set($cache_key, $count, $group, 300);
    return $count;
}

function allspice_recipes_count_cache_key(): string {
    $raw = function_exists('allspice_recipes_table_name_raw') ? allspice_recipes_table_name_raw() : 'allspice_recipes';
    return 'recipes:count:' . md5($raw);
}