<?php
/**
 * Copyright (C) 2026 Plugixa (https://plugixa.com)
 * License: GNU General Public License v2 or later
 * License URI: http://www.gnu.org/licenses/gpl-2.0.txt
 */

namespace PlugixaRecipe\Api;

// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.SlowDBQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom-table data layer: table identifiers are built with $wpdb->prefix and are never user input; all request values are bound via $wpdb->prepare(); ORDER BY / LIMIT fragments come out of internal allow-list maps; content reads go through PlugixaRecipe\Cache (the static sniff cannot trace caching through the wrapper); meta_key/meta_value are dedicated plugin columns, not WP_Query.

defined( 'ABSPATH' ) || exit;

use PlugixaRecipe\Cache;
use WP_REST_Request;
use WP_REST_Response;
use WP_Error;

/**
 * Recipes REST Controller
 */
class RecipesController extends RestController {

    public function __construct() {
        parent::__construct();
        $this->rest_base = 'recipes';
    }

    public function register_routes(): void {
        register_rest_route($this->namespace, '/' . $this->rest_base, [
            [
                'methods'             => 'GET',
                'callback'            => [$this, 'get_items'],
                'permission_callback' => [$this, 'check_permission'],
                'args'                => $this->get_collection_params(),
            ],
            [
                'methods'             => 'POST',
                'callback'            => [$this, 'create_item'],
                'permission_callback' => [$this, 'check_permission'],
            ],
        ]);

        register_rest_route($this->namespace, '/' . $this->rest_base . '/(?P<id>\d+)', [
            [
                'methods'             => 'GET',
                'callback'            => [$this, 'get_item'],
                'permission_callback' => [$this, 'check_permission'],
            ],
            [
                'methods'             => 'PUT',
                'callback'            => [$this, 'update_item'],
                'permission_callback' => [$this, 'check_permission'],
            ],
            [
                'methods'             => 'DELETE',
                'callback'            => [$this, 'delete_item'],
                'permission_callback' => [$this, 'check_permission'],
            ],
        ]);

        register_rest_route($this->namespace, '/' . $this->rest_base . '/(?P<id>\d+)/status', [
            [
                'methods'             => 'PATCH',
                'callback'            => [$this, 'update_status'],
                'permission_callback' => [$this, 'check_permission'],
            ],
        ]);

        register_rest_route($this->namespace, '/' . $this->rest_base . '/reorder', [
            [
                'methods'             => 'POST',
                'callback'            => [$this, 'reorder'],
                'permission_callback' => [$this, 'check_permission'],
            ],
        ]);

        register_rest_route($this->namespace, '/' . $this->rest_base . '/bulk', [
            [
                'methods'             => 'DELETE',
                'callback'            => [$this, 'bulk_delete'],
                'permission_callback' => [$this, 'check_permission'],
            ],
        ]);

        register_rest_route($this->namespace, '/' . $this->rest_base . '/bulk-action', [
            [
                'methods'             => 'POST',
                'callback'            => [$this, 'bulk_action'],
                'permission_callback' => [$this, 'check_permission'],
            ],
        ]);

        register_rest_route($this->namespace, '/' . $this->rest_base . '/(?P<id>\d+)/duplicate', [
            [
                'methods'             => 'POST',
                'callback'            => [$this, 'duplicate_item'],
                'permission_callback' => [$this, 'check_permission'],
            ],
        ]);
    }

    /**
     * Get recipes list with filters, pagination, sorting, and pivot joins.
     */
    public function get_items($request): WP_REST_Response|WP_Error {
        global $wpdb;
        $prefix  = $wpdb->prefix . 'plugixarecipe_';
        $filters = $this->get_filters($request);
        $pagination = $this->get_pagination($request);
        $sort = $this->get_sort($request);

        $where = ['1=1'];
        $join  = '';
        $values = [];

        // Search filter
        if (!empty($filters['search'])) {
            $search = '%' . $wpdb->esc_like($filters['search']) . '%';
            $where[] = '(r.title LIKE %s OR r.description LIKE %s OR r.author_name LIKE %s)';
            $values[] = $search;
            $values[] = $search;
            $values[] = $search;
        }

        // Status filter
        if (!empty($filters['status'])) {
            $where[] = 'r.status = %s';
            $values[] = $filters['status'];
        }

        // Category filter
        if (!empty($filters['category_id'])) {
            if ($filters['category_id'] === 'uncategorized' || $filters['category_id'] === '0') {
                $join .= " LEFT JOIN {$prefix}recipe_categories rc_filter ON r.id = rc_filter.recipe_id";
                $where[] = 'rc_filter.category_id IS NULL';
            } else {
                $join .= " INNER JOIN {$prefix}recipe_categories rc_filter ON r.id = rc_filter.recipe_id AND rc_filter.category_id = %d";
                $values[] = (int) $filters['category_id'];
            }
        }

        // Cuisine filter
        if (!empty($filters['cuisine_id'])) {
            $join .= " INNER JOIN {$prefix}recipe_cuisines rcu_filter ON r.id = rcu_filter.recipe_id AND rcu_filter.cuisine_id = %d";
            $values[] = (int) $filters['cuisine_id'];
        }

        // Course filter
        if (!empty($filters['course_id'])) {
            $join .= " INNER JOIN {$prefix}recipe_courses rco_filter ON r.id = rco_filter.recipe_id AND rco_filter.course_id = %d";
            $values[] = (int) $filters['course_id'];
        }

        // Difficulty filter
        if (!empty($filters['difficulty'])) {
            $where[] = 'r.difficulty = %s';
            $values[] = $filters['difficulty'];
        }

        // Featured filter
        if (isset($filters['is_featured']) && $filters['is_featured'] !== '') {
            $where[] = 'r.is_featured = %d';
            $values[] = (int) $filters['is_featured'];
        }

        // Min rating filter
        if (!empty($filters['min_rating'])) {
            $where[] = 'r.rating_average >= %f';
            $values[] = (float) $filters['min_rating'];
        }

        // Diet flags
        $diet_flags = ['is_vegetarian', 'is_vegan', 'is_gluten_free', 'is_dairy_free', 'is_keto', 'is_low_carb'];
        foreach ($diet_flags as $flag) {
            if (isset($filters[$flag]) && $filters[$flag] !== '') {
                $where[] = "r.{$flag} = %d";
                $values[] = (int) $filters[$flag];
            }
        }

        $where_clause = implode(' AND ', $where);

        // Allowed sort columns
        $allowed_orderby = ['title', 'created_at', 'modified_at', 'display_order', 'rating_average', 'view_count', 'total_time', 'published_at'];
        $orderby = in_array($sort['orderby'], $allowed_orderby) ? $sort['orderby'] : 'created_at';
        $order = $sort['order'] === 'ASC' ? 'ASC' : 'DESC';

        // Count query (read-through cached; key = SQL + bound values, which together
        // capture every filter/search/status/taxonomy/diet input that changes the result).
        $count_sql = "SELECT COUNT(DISTINCT r.id) FROM {$prefix}recipes r {$join} WHERE {$where_clause}";
        $total = (int) Cache::remember('recipes', 'count|' . $count_sql . '|' . wp_json_encode($values), function () use ($wpdb, $count_sql, $values) {
            if (!empty($values)) {
                return (int) $wpdb->get_var($wpdb->prepare($count_sql, ...$values));
            }
            return (int) $wpdb->get_var($count_sql);
        });

        // Main query (read-through cached; key = SQL + bound values, which now also
        // include the sort column/direction via the SQL and the limit/offset values).
        $sql = "SELECT DISTINCT r.* FROM {$prefix}recipes r {$join} WHERE {$where_clause} ORDER BY r.{$orderby} {$order} LIMIT %d OFFSET %d";
        $values[] = $pagination['limit'];
        $values[] = $pagination['offset'];

        $recipes = Cache::remember('recipes', 'items|' . $sql . '|' . wp_json_encode($values), function () use ($wpdb, $sql, $values) {
            if (!empty($values)) {
                return $wpdb->get_results($wpdb->prepare($sql, ...$values), ARRAY_A);
            }
            return $wpdb->get_results($sql, ARRAY_A);
        });

        // Enrich each recipe with pivot data and image
        foreach ($recipes as &$recipe) {
            $recipe = $this->enrich_recipe_list_item($recipe);
        }

        return $this->success([
            'items' => $recipes,
            'total' => $total,
            'limit' => $pagination['limit'],
            'offset' => $pagination['offset'],
        ]);
    }

    /**
     * Get single recipe with full relations.
     */
    public function get_item($request): WP_REST_Response|WP_Error {
        global $wpdb;
        $prefix = $wpdb->prefix . 'plugixarecipe_';
        $id = (int) $request->get_param('id');

        // Read-through cached record; the enrichment below (image URLs, taxonomy joins)
        // stays live so a category/cuisine/course rename is never served stale.
        $recipe = Cache::remember('recipes', "item:{$id}", function () use ($wpdb, $prefix, $id) {
            return $wpdb->get_row(
                $wpdb->prepare("SELECT * FROM {$prefix}recipes WHERE id = %d", $id),
                ARRAY_A
            );
        });

        if (!$recipe) {
            return $this->error(__('Recipe not found.', 'plugixarecipe'), 404);
        }

        // Image
        if ($recipe['image_id']) {
            $recipe['image_url'] = wp_get_attachment_image_url((int) $recipe['image_id'], 'large');
            $recipe['image_thumbnail'] = wp_get_attachment_image_url((int) $recipe['image_id'], 'thumbnail');
        }

        // Gallery
        if (!empty($recipe['gallery'])) {
            $gallery_ids = json_decode($recipe['gallery'], true);
            $recipe['gallery_urls'] = [];
            $recipe['gallery_ids']  = [];
            if (is_array($gallery_ids)) {
                $recipe['gallery_ids'] = array_map('intval', $gallery_ids);
                foreach ($gallery_ids as $gid) {
                    $recipe['gallery_urls'][] = [
                        'id'        => (int) $gid,
                        'url'       => wp_get_attachment_image_url((int) $gid, 'large'),
                        'thumbnail' => wp_get_attachment_image_url((int) $gid, 'thumbnail'),
                    ];
                }
            }
        }

        // Decode JSON array fields
        $recipe['tags'] = !empty($recipe['tags']) ? json_decode($recipe['tags'], true) : [];
        $recipe['equipment'] = !empty($recipe['equipment']) ? json_decode($recipe['equipment'], true) : [];

        // Categories
        $recipe['categories'] = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT c.* FROM {$prefix}categories c
                INNER JOIN {$prefix}recipe_categories rc ON c.id = rc.category_id
                WHERE rc.recipe_id = %d ORDER BY rc.display_order ASC",
                $id
            ),
            ARRAY_A
        );

        // Cuisines
        $recipe['cuisines'] = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT cu.* FROM {$prefix}cuisines cu
                INNER JOIN {$prefix}recipe_cuisines rcu ON cu.id = rcu.cuisine_id
                WHERE rcu.recipe_id = %d ORDER BY rcu.display_order ASC",
                $id
            ),
            ARRAY_A
        );

        // Courses
        $recipe['courses'] = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT co.* FROM {$prefix}courses co
                INNER JOIN {$prefix}recipe_courses rco ON co.id = rco.course_id
                WHERE rco.recipe_id = %d",
                $id
            ),
            ARRAY_A
        );

        // Scalar IDs for form binding
        $recipe['category_id']  = !empty($recipe['categories']) ? (int) $recipe['categories'][0]['id'] : 0;
        $recipe['category_ids'] = array_map('intval', array_column($recipe['categories'], 'id'));
        $recipe['cuisine_id']   = !empty($recipe['cuisines'])  ? (int) $recipe['cuisines'][0]['id']  : 0;
        $recipe['course_id']    = !empty($recipe['courses'])   ? (int) $recipe['courses'][0]['id']   : 0;

        // Ingredients (child rows of the recipe -> cached under the 'recipes' entity)
        $recipe['ingredients'] = Cache::remember('recipes', "item:{$id}:ingredients", function () use ($wpdb, $prefix, $id) {
            return $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT * FROM {$prefix}ingredients WHERE recipe_id = %d ORDER BY group_order ASC, sort_order ASC",
                    $id
                ),
                ARRAY_A
            );
        });

        // Instructions (child rows of the recipe -> cached under the 'recipes' entity)
        $recipe['instructions'] = Cache::remember('recipes', "item:{$id}:instructions", function () use ($wpdb, $prefix, $id) {
            return $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT * FROM {$prefix}instructions WHERE recipe_id = %d ORDER BY group_order ASC, sort_order ASC",
                    $id
                ),
                ARRAY_A
            );
        });
        foreach ($recipe['instructions'] as &$inst) {
            $inst['text']  = $inst['instruction_text'];
            $inst['title'] = $inst['title'] ?? '';
            if ($inst['image_id']) {
                $inst['image_url'] = wp_get_attachment_image_url((int) $inst['image_id'], 'medium');
            }
        }

        // Nutrition (child row of the recipe -> cached under the 'recipes' entity)
        $recipe['nutrition'] = Cache::remember('recipes', "item:{$id}:nutrition", function () use ($wpdb, $prefix, $id) {
            return $wpdb->get_row(
                $wpdb->prepare("SELECT * FROM {$prefix}nutrition WHERE recipe_id = %d", $id),
                ARRAY_A
            );
        });

        /**
         * Filter the recipe returned to the admin app.
         *
         * The premium overlay attaches the data belonging to its own features (the
         * recipe's custom-field values). The free build ships none of that machinery, so
         * nothing is attached and the editor simply renders no extra tabs.
         *
         * @param array<string,mixed> $recipe
         * @param int                 $id
         */
        $recipe = apply_filters('plugixarecipe_prepare_recipe_admin', $recipe, $id);

        return $this->success($recipe);
    }

    /**
     * Create a new recipe.
     */
    public function create_item($request): WP_REST_Response|WP_Error {
        global $wpdb;
        $prefix = $wpdb->prefix . 'plugixarecipe_';
        $params = $request->get_json_params();
        $now    = current_time('mysql');

        $title = sanitize_text_field($params['title'] ?? '');
        if (empty($title)) {
            return $this->error(__('Title is required.', 'plugixarecipe'));
        }

        $description_check = wp_kses_post($params['description'] ?? '');
        if (empty(trim(wp_strip_all_tags($description_check)))) {
            return $this->error(__('Description is required.', 'plugixarecipe'));
        }

        $image_id_check = !empty($params['image_id']) ? (int) $params['image_id'] : 0;
        if ($image_id_check < 1) {
            return $this->error(__('Recipe image is required.', 'plugixarecipe'));
        }

        $category_ids_check = $params['category_ids'] ?? [];
        if (empty($category_ids_check) || !is_array($category_ids_check) || count(array_filter($category_ids_check)) < 1) {
            return $this->error(__('At least one category is required.', 'plugixarecipe'));
        }

        $difficulty_check = sanitize_text_field($params['difficulty'] ?? '');
        if (empty($difficulty_check) || !in_array($difficulty_check, ['easy', 'medium', 'hard', 'expert'], true)) {
            return $this->error(__('Difficulty is required.', 'plugixarecipe'));
        }

        $servings_check = isset($params['servings']) ? (int) $params['servings'] : 0;
        if ($servings_check < 1) {
            return $this->error(__('Servings must be at least 1.', 'plugixarecipe'));
        }

        if (!isset($params['prep_time']) || $params['prep_time'] === '' || $params['prep_time'] === null) {
            return $this->error(__('Prep time is required.', 'plugixarecipe'));
        }

        if (!isset($params['cook_time']) || $params['cook_time'] === '' || $params['cook_time'] === null) {
            return $this->error(__('Cook time is required.', 'plugixarecipe'));
        }

        $ingredients_check = $params['ingredients'] ?? [];
        $has_ingredient = false;
        if (is_array($ingredients_check)) {
            foreach ($ingredients_check as $ing) {
                if (!empty(trim($ing['name'] ?? ''))) { $has_ingredient = true; break; }
            }
        }
        if (!$has_ingredient) {
            return $this->error(__('At least one ingredient is required.', 'plugixarecipe'));
        }

        $instructions_check = $params['instructions'] ?? [];
        $has_instruction = false;
        if (is_array($instructions_check)) {
            foreach ($instructions_check as $inst) {
                if (!empty(trim($inst['text'] ?? ''))) { $has_instruction = true; break; }
            }
        }
        if (!$has_instruction) {
            return $this->error(__('At least one instruction is required.', 'plugixarecipe'));
        }

        $data = [
            'title'            => $title,
            'slug'             => sanitize_title($params['slug'] ?? '') ?: sanitize_title($title),
            'description'      => wp_kses_post($params['description'] ?? ''),
            'excerpt'          => wp_kses_post($params['excerpt'] ?? ''),
            'notes'            => wp_kses_post($params['notes'] ?? ''),
            'tips'             => wp_kses_post($params['tips'] ?? ''),
            'image_id'         => !empty($params['image_id']) ? (int) $params['image_id'] : null,
            'gallery'          => !empty($params['gallery']) ? wp_json_encode($params['gallery']) : null,
            'tags'             => !empty($params['tags']) ? wp_json_encode($params['tags']) : null,
            'equipment'        => !empty($params['equipment']) ? wp_json_encode($params['equipment']) : null,
            'video_url'        => esc_url_raw($params['video_url'] ?? ''),
            'servings'         => (int) ($params['servings'] ?? 4),
            'servings_unit'    => sanitize_text_field($params['servings_unit'] ?? 'servings'),
            'yield_amount'     => sanitize_text_field($params['yield_amount'] ?? ''),
            'yield_unit'       => sanitize_text_field($params['yield_unit'] ?? ''),
            'prep_time'        => isset($params['prep_time']) ? (int) $params['prep_time'] : null,
            'cook_time'        => isset($params['cook_time']) ? (int) $params['cook_time'] : null,
            'rest_time'        => isset($params['rest_time']) ? (int) $params['rest_time'] : null,
            'difficulty'       => sanitize_text_field($params['difficulty'] ?? 'medium'),
            'cost'             => sanitize_text_field($params['cost'] ?? 'moderate'),
            'season'           => sanitize_text_field($params['season'] ?? ''),
            'status'           => sanitize_text_field($params['status'] ?? 'draft'),
            'is_featured'      => (int) ($params['is_featured'] ?? 0),
            'is_editor_pick'   => (int) ($params['is_editor_pick'] ?? 0),
            'display_order'    => (int) ($params['display_order'] ?? 0),
            'source'           => sanitize_text_field($params['source'] ?? ''),
            'source_url'       => esc_url_raw($params['source_url'] ?? ''),
            'author_name'      => sanitize_text_field($params['author_name'] ?? ''),
            'is_vegetarian'    => (int) ($params['is_vegetarian'] ?? 0),
            'is_vegan'         => (int) ($params['is_vegan'] ?? 0),
            'is_gluten_free'   => (int) ($params['is_gluten_free'] ?? 0),
            'is_dairy_free'    => (int) ($params['is_dairy_free'] ?? 0),
            'is_keto'          => (int) ($params['is_keto'] ?? 0),
            'is_low_carb'      => (int) ($params['is_low_carb'] ?? 0),
            'is_nut_free'      => (int) ($params['is_nut_free'] ?? 0),
            'is_paleo'         => (int) ($params['is_paleo'] ?? 0),
            'is_sugar_free'    => (int) ($params['is_sugar_free'] ?? 0),
            'is_halal'         => (int) ($params['is_halal'] ?? 0),
            'is_kosher'        => (int) ($params['is_kosher'] ?? 0),
            'language_code'    => sanitize_text_field($params['language_code'] ?? ''),
            'translation_group' => sanitize_text_field($params['translation_group'] ?? ''),
            'seo_title'        => isset($params['seo_title']) ? sanitize_text_field($params['seo_title']) : null,
            'seo_description'  => isset($params['seo_description']) ? sanitize_textarea_field($params['seo_description']) : null,
            'seo_keywords'     => isset($params['seo_keywords']) ? sanitize_text_field($params['seo_keywords']) : null,
            'created_at'       => $now,
            'created_by'       => get_current_user_id(),
        ];

        if ($data['status'] === 'published' && empty($data['published_at'])) {
            $data['published_at'] = $now;
        }

        // Ensure unique slug
        $data['slug'] = $this->ensure_unique_slug($data['slug'], $prefix . 'recipes');

        $wpdb->insert($prefix . 'recipes', $data);
        $recipe_id = $wpdb->insert_id;

        if (!$recipe_id) {
            return $this->error(__('Failed to create recipe.', 'plugixarecipe'), 500);
        }

        // Sync pivot tables
        $this->sync_categories($recipe_id, $params['category_ids'] ?? []);
        $this->sync_cuisines($recipe_id, $params['cuisine_ids'] ?? []);
        $this->sync_courses($recipe_id, $params['course_ids'] ?? []);

        // Save ingredients
        if (!empty($params['ingredients'])) {
            $this->save_ingredients($recipe_id, $params['ingredients']);
        }

        // Save instructions
        if (!empty($params['instructions'])) {
            $this->save_instructions($recipe_id, $params['instructions']);
        }

        // Save nutrition
        if (!empty($params['nutrition'])) {
            $this->save_nutrition($recipe_id, $params['nutrition']);
        }

        /**
         * Fires after a recipe is created or updated, with the full request payload.
         *
         * The premium overlay hooks this to persist its own per-recipe data (custom-field
         * values) and to run the SEO analysis when that is switched on. The free build has
         * nothing hooked here, so it neither stores nor analyses any of it.
         *
         * @param int                 $recipe_id
         * @param array<string,mixed> $params
         */
        do_action('plugixarecipe_save_recipe', $recipe_id, $params);

        // Recipe + pivot rows changed -> also invalidates category/cuisine/course counts.
        // Must run BEFORE get_item() below so the response is read fresh, not from cache.
        Cache::flush('recipes', 'categories', 'cuisines', 'courses');

        // Return the full recipe
        $request->set_param('id', $recipe_id);
        return $this->get_item($request);
    }

    /**
     * Update a recipe.
     */
    public function update_item($request): WP_REST_Response|WP_Error {
        global $wpdb;
        $prefix = $wpdb->prefix . 'plugixarecipe_';
        $id     = (int) $request->get_param('id');
        $params = $request->get_json_params();
        $now    = current_time('mysql');

        $existing = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$prefix}recipes WHERE id = %d", $id), ARRAY_A);
        if (!$existing) {
            return $this->error(__('Recipe not found.', 'plugixarecipe'), 404);
        }

        $data = [];

        $text_fields = ['title', 'slug', 'difficulty', 'cost', 'season', 'status', 'source', 'source_url', 'author_name', 'servings_unit', 'yield_amount', 'yield_unit', 'video_url', 'language_code', 'translation_group', 'seo_title', 'seo_keywords'];
        foreach ($text_fields as $field) {
            if (isset($params[$field])) {
                if (in_array($field, ['source_url', 'video_url'])) {
                    $data[$field] = esc_url_raw($params[$field]);
                } else {
                    $data[$field] = sanitize_text_field($params[$field]);
                }
            }
        }

        $html_fields = ['description', 'excerpt', 'notes', 'tips', 'seo_description'];
        foreach ($html_fields as $field) {
            if (isset($params[$field])) {
                $data[$field] = wp_kses_post($params[$field]);
            }
        }

        $int_fields = ['image_id', 'servings', 'prep_time', 'cook_time', 'rest_time', 'is_featured', 'is_editor_pick', 'display_order', 'is_vegetarian', 'is_vegan', 'is_gluten_free', 'is_dairy_free', 'is_keto', 'is_low_carb', 'is_nut_free', 'is_paleo', 'is_sugar_free', 'is_halal', 'is_kosher'];
        foreach ($int_fields as $field) {
            if (isset($params[$field])) {
                $data[$field] = $params[$field] !== null && $params[$field] !== '' ? (int) $params[$field] : null;
            }
        }

        if (isset($params['gallery'])) {
            $data['gallery'] = !empty($params['gallery']) ? wp_json_encode($params['gallery']) : null;
        }
        if (isset($params['tags'])) {
            $data['tags'] = !empty($params['tags']) ? wp_json_encode($params['tags']) : null;
        }
        if (isset($params['equipment'])) {
            $data['equipment'] = !empty($params['equipment']) ? wp_json_encode($params['equipment']) : null;
        }

        // Handle published_at on status change
        if (isset($data['status']) && $data['status'] === 'published' && $existing['status'] !== 'published') {
            $data['published_at'] = $now;
        }

        // Normalize slug: always slug-sanitized, falling back to the (new or
        // existing) title when cleared so a recipe never loses its permalink.
        if (isset($data['slug'])) {
            $data['slug'] = sanitize_title($data['slug']);
            if ($data['slug'] === '') {
                $data['slug'] = sanitize_title($data['title'] ?? $existing['title']);
            }
        }

        // Ensure unique slug if changed
        if (isset($data['slug']) && $data['slug'] !== $existing['slug']) {
            $data['slug'] = $this->ensure_unique_slug($data['slug'], $prefix . 'recipes', $id);
        }

        $data['modified_at'] = $now;
        $data['modified_by'] = get_current_user_id();

        $wpdb->update($prefix . 'recipes', $data, ['id' => $id]);

        // Sync pivot tables
        if (isset($params['category_ids'])) {
            $this->sync_categories($id, $params['category_ids']);
        }
        if (isset($params['cuisine_ids'])) {
            $this->sync_cuisines($id, $params['cuisine_ids']);
        }
        if (isset($params['course_ids'])) {
            $this->sync_courses($id, $params['course_ids']);
        }

        // Save ingredients
        if (isset($params['ingredients'])) {
            $this->save_ingredients($id, $params['ingredients']);
        }

        // Save instructions
        if (isset($params['instructions'])) {
            $this->save_instructions($id, $params['instructions']);
        }

        // Save nutrition
        if (isset($params['nutrition'])) {
            $this->save_nutrition($id, $params['nutrition']);
        }

        /** This filter is documented in create_item(). */
        do_action('plugixarecipe_save_recipe', $id, $params);

        // Recipe + pivot rows changed -> also invalidates category/cuisine/course counts.
        // Must run BEFORE get_item() below so the response is read fresh, not from cache.
        Cache::flush('recipes', 'categories', 'cuisines', 'courses');

        return $this->get_item($request);
    }

    /**
     * Delete a recipe and all associated data.
     */
    public function delete_item($request): WP_REST_Response|WP_Error {
        global $wpdb;
        $prefix = $wpdb->prefix . 'plugixarecipe_';
        $id = (int) $request->get_param('id');

        $exists = $wpdb->get_var($wpdb->prepare("SELECT id FROM {$prefix}recipes WHERE id = %d", $id));
        if (!$exists) {
            return $this->error(__('Recipe not found.', 'plugixarecipe'), 404);
        }

        // Delete related data
        $wpdb->delete($prefix . 'recipe_categories', ['recipe_id' => $id]);
        $wpdb->delete($prefix . 'recipe_cuisines', ['recipe_id' => $id]);
        $wpdb->delete($prefix . 'recipe_courses', ['recipe_id' => $id]);
        $wpdb->delete($prefix . 'ingredients', ['recipe_id' => $id]);
        $wpdb->delete($prefix . 'instructions', ['recipe_id' => $id]);
        $wpdb->delete($prefix . 'nutrition', ['recipe_id' => $id]);
        $wpdb->delete($prefix . 'reviews', ['recipe_id' => $id]);
        $wpdb->delete($prefix . 'favorites', ['recipe_id' => $id]);
        $wpdb->delete($prefix . 'collection_recipes', ['recipe_id' => $id]);

        /**
         * Fires before a recipe row is deleted, while its id is still valid.
         *
         * The premium overlay hooks this to clean up the rows it owns in its own tables.
         * The free build has none, so nothing is hooked.
         *
         * @param int $id
         */
        do_action('plugixarecipe_delete_recipe', $id);

        $wpdb->delete($prefix . 'recipes', ['id' => $id]);

        // Recipe + pivot rows gone (category/cuisine/course counts change), and the
        // recipe's reviews + favorites rows were removed above.
        Cache::flush('recipes', 'categories', 'cuisines', 'courses', 'reviews', 'favorites');

        return $this->success(null, __('Recipe deleted.', 'plugixarecipe'));
    }

    /**
     * Update recipe status.
     */
    public function update_status($request): WP_REST_Response|WP_Error {
        global $wpdb;
        $prefix = $wpdb->prefix . 'plugixarecipe_';
        $id = (int) $request->get_param('id');
        $params = $request->get_json_params();
        $status = sanitize_text_field($params['status'] ?? '');

        $allowed = ['draft', 'pending', 'published', 'archived'];
        if (!in_array($status, $allowed)) {
            return $this->error(__('Invalid status.', 'plugixarecipe'));
        }

        $existing = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$prefix}recipes WHERE id = %d", $id), ARRAY_A);
        if (!$existing) {
            return $this->error(__('Recipe not found.', 'plugixarecipe'), 404);
        }

        $data = [
            'status'      => $status,
            'modified_at' => current_time('mysql'),
            'modified_by' => get_current_user_id(),
        ];

        if ($status === 'published' && $existing['status'] !== 'published') {
            $data['published_at'] = current_time('mysql');
        }

        $wpdb->update($prefix . 'recipes', $data, ['id' => $id]);

        // Status/published_at only; pivot rows (and therefore taxonomy counts) are untouched.
        Cache::flush('recipes');

        return $this->success(['id' => $id, 'status' => $status], __('Status updated.', 'plugixarecipe'));
    }

    /**
     * Reorder recipes.
     */
    public function reorder($request): WP_REST_Response|WP_Error {
        global $wpdb;
        $prefix = $wpdb->prefix . 'plugixarecipe_';
        $params = $request->get_json_params();
        $items  = $params['items'] ?? [];
        $category_id = $params['category_id'] ?? null;

        if (empty($items) || !is_array($items)) {
            return $this->error(__('Items are required.', 'plugixarecipe'));
        }

        if ($category_id) {
            // Per-category ordering via pivot table
            foreach ($items as $item) {
                $wpdb->update(
                    $prefix . 'recipe_categories',
                    ['display_order' => (int) $item['display_order']],
                    ['recipe_id' => (int) $item['id'], 'category_id' => (int) $category_id]
                );
            }
        } else {
            foreach ($items as $item) {
                $wpdb->update(
                    $prefix . 'recipes',
                    ['display_order' => (int) $item['display_order']],
                    ['id' => (int) $item['id']]
                );
            }
        }

        // Ordering changed (recipe display_order, or the per-category pivot order).
        Cache::flush('recipes');

        return $this->success(null, __('Order updated.', 'plugixarecipe'));
    }

    /**
     * Bulk delete recipes.
     */
    public function bulk_delete($request): WP_REST_Response|WP_Error {
        global $wpdb;
        $prefix = $wpdb->prefix . 'plugixarecipe_';
        $params = $request->get_json_params();
        $ids    = array_map('intval', $params['ids'] ?? []);

        if (empty($ids)) {
            return $this->error(__('No IDs provided.', 'plugixarecipe'));
        }

        $placeholders = implode(',', array_fill(0, count($ids), '%d'));

        // Delete related data for all IDs.
        $tables = ['recipe_categories', 'recipe_cuisines', 'recipe_courses', 'ingredients', 'instructions', 'nutrition', 'reviews', 'favorites', 'collection_recipes'];

        /**
         * Filter the child tables cascaded when recipes are deleted.
         *
         * Every table here is keyed by recipe_id. The premium overlay appends the tables
         * it owns; the free build has none.
         *
         * @param string[] $tables Table names, without the plugin prefix.
         */
        $tables = apply_filters('plugixarecipe_recipe_child_tables', $tables);

        foreach ($tables as $table) {
            $wpdb->query($wpdb->prepare(
                "DELETE FROM {$prefix}{$table} WHERE recipe_id IN ({$placeholders})",
                ...$ids
            ));
        }

        $deleted = $wpdb->query($wpdb->prepare(
            "DELETE FROM {$prefix}recipes WHERE id IN ({$placeholders})",
            ...$ids
        ));

        // Recipes + pivot rows gone (category/cuisine/course counts change), and the
        // deleted recipes' reviews + favorites rows were removed above.
        Cache::flush('recipes', 'categories', 'cuisines', 'courses', 'reviews', 'favorites');

        return $this->success(['deleted' => $deleted], __('Recipes deleted.', 'plugixarecipe'));
    }

    /**
     * Bulk action (publish, draft, feature, unfeature).
     */
    public function bulk_action($request): WP_REST_Response|WP_Error {
        global $wpdb;
        $prefix = $wpdb->prefix . 'plugixarecipe_';
        $params = $request->get_json_params();
        $ids    = array_map('intval', $params['ids'] ?? []);
        $action = sanitize_text_field($params['action'] ?? '');

        if (empty($ids) || empty($action)) {
            return $this->error(__('IDs and action are required.', 'plugixarecipe'));
        }

        $placeholders = implode(',', array_fill(0, count($ids), '%d'));
        $now = current_time('mysql');
        $user_id = get_current_user_id();

        switch ($action) {
            case 'publish':
                $wpdb->query($wpdb->prepare(
                    "UPDATE {$prefix}recipes SET status = 'published', published_at = COALESCE(published_at, %s), modified_at = %s, modified_by = %d WHERE id IN ({$placeholders})",
                    $now, $now, $user_id, ...$ids
                ));
                break;

            case 'draft':
                $wpdb->query($wpdb->prepare(
                    "UPDATE {$prefix}recipes SET status = 'draft', modified_at = %s, modified_by = %d WHERE id IN ({$placeholders})",
                    $now, $user_id, ...$ids
                ));
                break;

            case 'feature':
                $wpdb->query($wpdb->prepare(
                    "UPDATE {$prefix}recipes SET is_featured = 1, modified_at = %s, modified_by = %d WHERE id IN ({$placeholders})",
                    $now, $user_id, ...$ids
                ));
                break;

            case 'unfeature':
                $wpdb->query($wpdb->prepare(
                    "UPDATE {$prefix}recipes SET is_featured = 0, modified_at = %s, modified_by = %d WHERE id IN ({$placeholders})",
                    $now, $user_id, ...$ids
                ));
                break;

            default:
                return $this->error(__('Invalid action.', 'plugixarecipe'));
        }

        // Status / is_featured only; pivot rows (and therefore taxonomy counts) are untouched.
        Cache::flush('recipes');

        /* translators: %s: the bulk action that was applied (e.g. publish, draft, delete). */
        return $this->success(null, sprintf(__('Bulk %s completed.', 'plugixarecipe'), $action));
    }

    /**
     * Duplicate a recipe with all related data.
     */
    public function duplicate_item($request): WP_REST_Response|WP_Error {
        global $wpdb;
        $prefix = $wpdb->prefix . 'plugixarecipe_';
        $id = (int) $request->get_param('id');
        $now = current_time('mysql');
        $user_id = get_current_user_id();

        $recipe = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$prefix}recipes WHERE id = %d", $id), ARRAY_A);
        if (!$recipe) {
            return $this->error(__('Recipe not found.', 'plugixarecipe'), 404);
        }

        // Remove auto-generated / unique fields
        unset($recipe['id'], $recipe['total_time'], $recipe['view_count'], $recipe['rating_average'], $recipe['rating_count'], $recipe['seo_score'], $recipe['published_at']);

        $recipe['title']       = $recipe['title'] . ' (Copy)';
        $recipe['slug']        = $this->ensure_unique_slug(sanitize_title($recipe['title']), $prefix . 'recipes');
        $recipe['status']      = 'draft';
        $recipe['is_featured'] = 0;
        $recipe['created_at']  = $now;
        $recipe['created_by']  = $user_id;
        $recipe['modified_at'] = null;
        $recipe['modified_by'] = null;

        $wpdb->insert($prefix . 'recipes', $recipe);
        $new_id = $wpdb->insert_id;

        if (!$new_id) {
            return $this->error(__('Failed to duplicate recipe.', 'plugixarecipe'), 500);
        }

        // Copy categories pivot
        $wpdb->query($wpdb->prepare(
            "INSERT INTO {$prefix}recipe_categories (recipe_id, category_id, display_order)
            SELECT %d, category_id, display_order FROM {$prefix}recipe_categories WHERE recipe_id = %d",
            $new_id, $id
        ));

        // Copy cuisines pivot
        $wpdb->query($wpdb->prepare(
            "INSERT INTO {$prefix}recipe_cuisines (recipe_id, cuisine_id, display_order)
            SELECT %d, cuisine_id, display_order FROM {$prefix}recipe_cuisines WHERE recipe_id = %d",
            $new_id, $id
        ));

        // Copy courses pivot
        $wpdb->query($wpdb->prepare(
            "INSERT INTO {$prefix}recipe_courses (recipe_id, course_id)
            SELECT %d, course_id FROM {$prefix}recipe_courses WHERE recipe_id = %d",
            $new_id, $id
        ));

        // Copy ingredients
        $wpdb->query($wpdb->prepare(
            "INSERT INTO {$prefix}ingredients (recipe_id, group_name, group_order, sort_order, quantity, unit, name, notes, link_url)
            SELECT %d, group_name, group_order, sort_order, quantity, unit, name, notes, link_url FROM {$prefix}ingredients WHERE recipe_id = %d",
            $new_id, $id
        ));

        // Copy instructions
        $wpdb->query($wpdb->prepare(
            "INSERT INTO {$prefix}instructions (recipe_id, group_name, group_order, sort_order, title, instruction_text, image_id, video_url, duration)
            SELECT %d, group_name, group_order, sort_order, title, instruction_text, image_id, video_url, duration FROM {$prefix}instructions WHERE recipe_id = %d",
            $new_id, $id
        ));

        // Copy nutrition
        $wpdb->query($wpdb->prepare(
            "INSERT INTO {$prefix}nutrition (recipe_id, serving_size, calories, total_fat, saturated_fat, trans_fat, cholesterol, sodium, total_carbohydrate, dietary_fiber, total_sugars, protein, vitamin_a, vitamin_c, calcium, iron, potassium, source)
            SELECT %d, serving_size, calories, total_fat, saturated_fat, trans_fat, cholesterol, sodium, total_carbohydrate, dietary_fiber, total_sugars, protein, vitamin_a, vitamin_c, calcium, iron, potassium, source FROM {$prefix}nutrition WHERE recipe_id = %d",
            $new_id, $id
        ));

        /**
         * Fires after a recipe has been duplicated, once its children have been copied.
         *
         * The premium overlay hooks this to copy the rows it owns in its own tables.
         *
         * @param int $new_id The new (duplicated) recipe id.
         * @param int $id     The recipe it was copied from.
         */
        do_action('plugixarecipe_duplicate_recipe', $new_id, $id);

        // New recipe + copied pivot rows -> also invalidates category/cuisine/course counts.
        // Must run BEFORE get_item() below so the response is read fresh, not from cache.
        Cache::flush('recipes', 'categories', 'cuisines', 'courses');

        $request->set_param('id', $new_id);
        return $this->get_item($request);
    }

    // --- Helper methods ---

    /**
     * Enrich a recipe list item with categories, cuisines, courses, image URL.
     */
    private function enrich_recipe_list_item(array $recipe): array {
        global $wpdb;
        $prefix = $wpdb->prefix . 'plugixarecipe_';
        $id = (int) $recipe['id'];

        // Image
        if (!empty($recipe['image_id'])) {
            $recipe['image_url'] = wp_get_attachment_image_url((int) $recipe['image_id'], 'medium');
            $recipe['image_thumbnail'] = wp_get_attachment_image_url((int) $recipe['image_id'], 'thumbnail');
        }

        // Categories
        $recipe['categories'] = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT c.id, c.name, c.slug FROM {$prefix}categories c
                INNER JOIN {$prefix}recipe_categories rc ON c.id = rc.category_id
                WHERE rc.recipe_id = %d ORDER BY rc.display_order ASC",
                $id
            ),
            ARRAY_A
        );

        // Cuisines
        $recipe['cuisines'] = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT cu.id, cu.name, cu.slug FROM {$prefix}cuisines cu
                INNER JOIN {$prefix}recipe_cuisines rcu ON cu.id = rcu.cuisine_id
                WHERE rcu.recipe_id = %d ORDER BY rcu.display_order ASC",
                $id
            ),
            ARRAY_A
        );

        // Courses
        $recipe['courses'] = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT co.id, co.name, co.slug FROM {$prefix}courses co
                INNER JOIN {$prefix}recipe_courses rco ON co.id = rco.course_id
                WHERE rco.recipe_id = %d",
                $id
            ),
            ARRAY_A
        );

        // Flattened names and IDs for list display and form binding
        $recipe['category_name'] = !empty($recipe['categories'])
            ? implode(', ', array_column($recipe['categories'], 'name'))
            : null;
        $recipe['cuisine_name']  = $recipe['cuisines'][0]['name'] ?? null;
        $recipe['course_name']   = $recipe['courses'][0]['name'] ?? null;
        $recipe['category_id']   = !empty($recipe['categories']) ? (int) $recipe['categories'][0]['id'] : 0;
        $recipe['category_ids']  = array_map('intval', array_column($recipe['categories'], 'id'));
        $recipe['cuisine_id']    = !empty($recipe['cuisines'])  ? (int) $recipe['cuisines'][0]['id']  : 0;
        $recipe['course_id']     = !empty($recipe['courses'])   ? (int) $recipe['courses'][0]['id']   : 0;

        // Frontend expects average_rating, DB has rating_average
        $recipe['average_rating'] = (float) ($recipe['rating_average'] ?? 0);

        return $recipe;
    }

    private function sync_categories(int $recipe_id, array $category_ids): void {
        global $wpdb;
        $prefix = $wpdb->prefix . 'plugixarecipe_';
        $wpdb->delete($prefix . 'recipe_categories', ['recipe_id' => $recipe_id]);
        foreach ($category_ids as $i => $cid) {
            $wpdb->insert($prefix . 'recipe_categories', [
                'recipe_id'     => $recipe_id,
                'category_id'   => (int) $cid,
                'display_order' => $i,
            ]);
        }
    }

    private function sync_cuisines(int $recipe_id, array $cuisine_ids): void {
        global $wpdb;
        $prefix = $wpdb->prefix . 'plugixarecipe_';
        $wpdb->delete($prefix . 'recipe_cuisines', ['recipe_id' => $recipe_id]);
        foreach ($cuisine_ids as $i => $cid) {
            $wpdb->insert($prefix . 'recipe_cuisines', [
                'recipe_id'     => $recipe_id,
                'cuisine_id'    => (int) $cid,
                'display_order' => $i,
            ]);
        }
    }

    private function sync_courses(int $recipe_id, array $course_ids): void {
        global $wpdb;
        $prefix = $wpdb->prefix . 'plugixarecipe_';
        $wpdb->delete($prefix . 'recipe_courses', ['recipe_id' => $recipe_id]);
        foreach ($course_ids as $cid) {
            $wpdb->insert($prefix . 'recipe_courses', [
                'recipe_id' => $recipe_id,
                'course_id' => (int) $cid,
            ]);
        }
    }

    private function save_ingredients(int $recipe_id, array $ingredients): void {
        global $wpdb;
        $prefix = $wpdb->prefix . 'plugixarecipe_';
        $wpdb->delete($prefix . 'ingredients', ['recipe_id' => $recipe_id]);

        foreach ($ingredients as $ing) {
            $wpdb->insert($prefix . 'ingredients', [
                'recipe_id'   => $recipe_id,
                'group_name'  => sanitize_text_field($ing['group_name'] ?? ''),
                'group_order' => (int) ($ing['group_order'] ?? 0),
                'sort_order'  => (int) ($ing['sort_order'] ?? 0),
                'quantity'    => sanitize_text_field($ing['quantity'] ?? ''),
                'unit'        => sanitize_text_field($ing['unit'] ?? ''),
                'name'        => sanitize_text_field($ing['name'] ?? ''),
                'notes'       => sanitize_text_field($ing['notes'] ?? ''),
                'link_url'    => esc_url_raw($ing['link_url'] ?? ''),
            ]);
        }
    }

    private function save_instructions(int $recipe_id, array $instructions): void {
        global $wpdb;
        $prefix = $wpdb->prefix . 'plugixarecipe_';
        $wpdb->delete($prefix . 'instructions', ['recipe_id' => $recipe_id]);

        foreach ($instructions as $inst) {
            $wpdb->insert($prefix . 'instructions', [
                'recipe_id'        => $recipe_id,
                'group_name'       => sanitize_text_field($inst['group_name'] ?? ''),
                'group_order'      => (int) ($inst['group_order'] ?? 0),
                'sort_order'       => (int) ($inst['sort_order'] ?? 0),
                'title'            => sanitize_text_field($inst['title'] ?? ''),
                'instruction_text' => wp_kses_post($inst['instruction_text'] ?? $inst['text'] ?? ''),
                'image_id'         => !empty($inst['image_id']) ? (int) $inst['image_id'] : null,
                'video_url'        => esc_url_raw($inst['video_url'] ?? ''),
                'duration'         => isset($inst['duration']) ? (int) $inst['duration'] : null,
            ]);
        }
    }

    private function save_nutrition(int $recipe_id, array $nutrition): void {
        global $wpdb;
        $prefix = $wpdb->prefix . 'plugixarecipe_';

        $existing = $wpdb->get_var($wpdb->prepare("SELECT id FROM {$prefix}nutrition WHERE recipe_id = %d", $recipe_id));

        $data = [
            'recipe_id'          => $recipe_id,
            'serving_size'       => sanitize_text_field($nutrition['serving_size'] ?? ''),
            'calories'           => isset($nutrition['calories']) ? (float) $nutrition['calories'] : null,
            'total_fat'          => isset($nutrition['total_fat']) ? (float) $nutrition['total_fat'] : null,
            'saturated_fat'      => isset($nutrition['saturated_fat']) ? (float) $nutrition['saturated_fat'] : null,
            'trans_fat'          => isset($nutrition['trans_fat']) ? (float) $nutrition['trans_fat'] : null,
            'cholesterol'        => isset($nutrition['cholesterol']) ? (float) $nutrition['cholesterol'] : null,
            'sodium'             => isset($nutrition['sodium']) ? (float) $nutrition['sodium'] : null,
            'total_carbohydrate' => isset($nutrition['total_carbohydrate']) ? (float) $nutrition['total_carbohydrate'] : null,
            'dietary_fiber'      => isset($nutrition['dietary_fiber']) ? (float) $nutrition['dietary_fiber'] : null,
            'total_sugars'       => isset($nutrition['total_sugars']) ? (float) $nutrition['total_sugars'] : null,
            'protein'            => isset($nutrition['protein']) ? (float) $nutrition['protein'] : null,
            'vitamin_a'          => isset($nutrition['vitamin_a']) ? (float) $nutrition['vitamin_a'] : null,
            'vitamin_c'          => isset($nutrition['vitamin_c']) ? (float) $nutrition['vitamin_c'] : null,
            'calcium'            => isset($nutrition['calcium']) ? (float) $nutrition['calcium'] : null,
            'iron'               => isset($nutrition['iron']) ? (float) $nutrition['iron'] : null,
            'potassium'          => isset($nutrition['potassium']) ? (float) $nutrition['potassium'] : null,
            'source'             => sanitize_text_field($nutrition['source'] ?? 'manual'),
        ];

        if ($existing) {
            $wpdb->update($prefix . 'nutrition', $data, ['recipe_id' => $recipe_id]);
        } else {
            $wpdb->insert($prefix . 'nutrition', $data);
        }
    }

    private function ensure_unique_slug(string $slug, string $table, int $exclude_id = 0): string {
        global $wpdb;
        $original = $slug;
        $counter  = 1;

        while (true) {
            if ($exclude_id) {
                $exists = $wpdb->get_var($wpdb->prepare("SELECT id FROM {$table} WHERE slug = %s AND id != %d", $slug, $exclude_id));
            } else {
                $exists = $wpdb->get_var($wpdb->prepare("SELECT id FROM {$table} WHERE slug = %s", $slug));
            }
            if (!$exists) {
                break;
            }
            $slug = $original . '-' . (++$counter);
        }

        return $slug;
    }
}
