<?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;

/**
 * Collections REST Controller (collections, cookbooks, meal plans, favorites)
 */
class CollectionsController extends RestController {

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

    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'],
            ],
        ]);

        // Collection recipes management
        register_rest_route($this->namespace, '/' . $this->rest_base . '/(?P<id>\d+)/recipes', [
            [
                'methods'             => 'GET',
                'callback'            => [$this, 'get_collection_recipes'],
                'permission_callback' => [$this, 'check_permission'],
            ],
            [
                'methods'             => 'POST',
                'callback'            => [$this, 'add_recipe_to_collection'],
                'permission_callback' => [$this, 'check_permission'],
            ],
        ]);

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

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

    /**
     * Get collections list.
     */
    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'];
        $values = [];

        if (!empty($filters['search'])) {
            $search = '%' . $wpdb->esc_like($filters['search']) . '%';
            $where[] = '(col.name LIKE %s OR col.description LIKE %s)';
            $values[] = $search;
            $values[] = $search;
        }

        if (!empty($filters['type'])) {
            $where[] = 'col.type = %s';
            $values[] = $filters['type'];
        }

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

        if (!empty($filters['user_id'])) {
            $where[] = 'col.user_id = %d';
            $values[] = (int) $filters['user_id'];
        }

        if (!empty($filters['visibility'])) {
            $where[] = 'col.visibility = %s';
            $values[] = $filters['visibility'];
        }

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

        $allowed_orderby = ['name', 'created_at', 'modified_at', 'display_order', 'type'];
        $orderby = in_array($sort['orderby'], $allowed_orderby) ? $sort['orderby'] : 'created_at';
        $order   = $sort['order'] === 'ASC' ? 'ASC' : 'DESC';

        // Count — read-through object cache; key = SQL + bound values (captures every filter).
        $count_sql = "SELECT COUNT(*) FROM {$prefix}collections col WHERE {$where_clause}";
        $total = (int) Cache::remember('collections', 'count|' . $count_sql . '|' . wp_json_encode($values), function () use ($wpdb, $count_sql, $values) {
            return !empty($values)
                ? (int) $wpdb->get_var($wpdb->prepare($count_sql, ...$values))
                : (int) $wpdb->get_var($count_sql);
        });

        // Main query with recipe count
        $sql = "SELECT col.*, COUNT(cr.recipe_id) AS recipe_count
                FROM {$prefix}collections col
                LEFT JOIN {$prefix}collection_recipes cr ON col.id = cr.collection_id
                WHERE {$where_clause}
                GROUP BY col.id
                ORDER BY col.{$orderby} {$order}
                LIMIT %d OFFSET %d";
        $values[] = $pagination['limit'];
        $values[] = $pagination['offset'];

        // Read-through object cache; key = SQL + bound values (captures filters/sort/pagination).
        $items = Cache::remember('collections', 'list|' . $sql . '|' . wp_json_encode($values), function () use ($wpdb, $sql, $values) {
            return $wpdb->get_results($wpdb->prepare($sql, ...$values), ARRAY_A) ?: [];
        });

        foreach ($items as &$item) {
            $item['recipe_count'] = (int) $item['recipe_count'];
            $item['is_featured'] = (bool) $item['is_featured'];
            $item['title'] = $item['name'];
            $item['is_public'] = ($item['visibility'] ?? 'public') === 'public';
            $item['status'] = $this->map_status_to_frontend($item['status'] ?? 'published');
            if (!empty($item['user_id'])) {
                $user = get_userdata((int) $item['user_id']);
                $item['author_name'] = $user ? $user->display_name : '';
            }
            if (!empty($item['image_id'])) {
                $item['image_url'] = wp_get_attachment_image_url((int) $item['image_id'], 'medium');
            }
        }

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

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

        // Read-through object cache; key = collection id.
        $item = Cache::remember('collections', 'item|' . $id, function () use ($wpdb, $prefix, $id) {
            return $wpdb->get_row(
                $wpdb->prepare("SELECT * FROM {$prefix}collections WHERE id = %d", $id),
                ARRAY_A
            );
        });

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

        $item['is_featured'] = (bool) $item['is_featured'];
        $item['title'] = $item['name'];
        $item['is_public'] = ($item['visibility'] ?? 'public') === 'public';
        $item['status'] = $this->map_status_to_frontend($item['status'] ?? 'published');

        if (!empty($item['image_id'])) {
            $item['image_url'] = wp_get_attachment_image_url((int) $item['image_id'], 'large');
        }

        // Get recipes in collection — read-through object cache; key = collection id.
        $item['recipes'] = Cache::remember('collections', 'item_recipes|' . $id, function () use ($wpdb, $prefix, $id) {
            return $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT r.id, r.title, r.slug, r.image_id, r.prep_time, r.cook_time, r.total_time,
                            r.difficulty, r.rating_average, r.status,
                            cr.display_order, cr.day_number, cr.meal_type, cr.added_at
                    FROM {$prefix}recipes r
                    INNER JOIN {$prefix}collection_recipes cr ON r.id = cr.recipe_id
                    WHERE cr.collection_id = %d
                    ORDER BY cr.display_order ASC",
                    $id
                ),
                ARRAY_A
            ) ?: [];
        });

        foreach ($item['recipes'] as &$recipe) {
            if (!empty($recipe['image_id'])) {
                $recipe['image_url'] = wp_get_attachment_image_url((int) $recipe['image_id'], 'thumbnail');
            }
            $recipe['rating_average'] = (float) $recipe['rating_average'];
        }

        $item['recipe_count'] = count($item['recipes']);

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

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

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

        $type = sanitize_text_field($params['type'] ?? 'collection');
        $allowed_types = ['collection', 'cookbook', 'meal_plan', 'favorites'];
        if (!in_array($type, $allowed_types)) {
            $type = 'collection';
        }

        $slug = sanitize_title($params['slug'] ?? '') ?: sanitize_title($name);
        $slug = $this->ensure_unique_slug($slug, $prefix . 'collections');

        // Map frontend status (active/inactive) to DB status (published/draft)
        $status = $this->map_status_from_frontend(sanitize_text_field($params['status'] ?? 'active'));

        // Map frontend is_public (bool) to DB visibility (public/private)
        $visibility = sanitize_text_field($params['visibility'] ?? 'public');
        if (isset($params['is_public'])) {
            $visibility = $params['is_public'] ? 'public' : 'private';
        }

        $data = [
            'name'          => $name,
            'slug'          => $slug,
            'description'   => sanitize_textarea_field($params['description'] ?? ''),
            'image_id'      => !empty($params['image_id']) ? (int) $params['image_id'] : null,
            'type'          => $type,
            'status'        => $status,
            'is_featured'   => (int) ($params['is_featured'] ?? 0),
            'display_order' => (int) ($params['display_order'] ?? 0),
            'user_id'       => get_current_user_id(),
            'visibility'    => $visibility,
            'created_at'    => current_time('mysql'),
        ];

        $wpdb->insert($prefix . 'collections', $data);
        $id = $wpdb->insert_id;

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

        // Content write.
        Cache::flush('collections');

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

    /**
     * Update a collection.
     */
    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();

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

        $data = [];

        // Accept 'title' (frontend) or 'name' (DB)
        if (isset($params['title']) || isset($params['name'])) {
            $data['name'] = sanitize_text_field($params['title'] ?? $params['name']);
        }
        if (isset($params['slug'])) {
            $slug = sanitize_title($params['slug']);
            if ($slug === '') {
                $slug = sanitize_title($params['name'] ?? $existing['name']);
            }
            if ($slug !== $existing['slug']) {
                $slug = $this->ensure_unique_slug($slug, $prefix . 'collections', $id);
            }
            $data['slug'] = $slug;
        }
        if (isset($params['description'])) {
            $data['description'] = sanitize_textarea_field($params['description']);
        }
        if (isset($params['image_id'])) {
            $data['image_id'] = !empty($params['image_id']) ? (int) $params['image_id'] : null;
        }
        if (isset($params['type'])) {
            $allowed_types = ['collection', 'cookbook', 'meal_plan', 'favorites'];
            $data['type'] = in_array($params['type'], $allowed_types) ? $params['type'] : $existing['type'];
        }
        if (isset($params['status'])) {
            $data['status'] = $this->map_status_from_frontend(sanitize_text_field($params['status']));
        }
        if (isset($params['is_featured'])) {
            $data['is_featured'] = (int) $params['is_featured'];
        }
        if (isset($params['display_order'])) {
            $data['display_order'] = (int) $params['display_order'];
        }
        // Accept 'is_public' (frontend bool) or 'visibility' (DB string)
        if (isset($params['is_public'])) {
            $data['visibility'] = $params['is_public'] ? 'public' : 'private';
        } elseif (isset($params['visibility'])) {
            $data['visibility'] = sanitize_text_field($params['visibility']);
        }

        $data['modified_at'] = current_time('mysql');

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

        // Content write.
        Cache::flush('collections');

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

    /**
     * Delete a collection and its pivot entries.
     */
    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}collections WHERE id = %d", $id));
        if (!$exists) {
            return $this->error(__('Collection not found.', 'plugixarecipe'), 404);
        }

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

        // Content write.
        Cache::flush('collections');

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

    /**
     * Get recipes in a collection.
     */
    public function get_collection_recipes($request): WP_REST_Response|WP_Error {
        global $wpdb;
        $prefix = $wpdb->prefix . 'plugixarecipe_';
        $id = (int) $request->get_param('id');

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

        // Read-through object cache; key = collection id.
        $recipes = Cache::remember('collections', 'recipes|' . $id, function () use ($wpdb, $prefix, $id) {
            return $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT r.*, cr.display_order, cr.day_number, cr.meal_type, cr.added_at
                    FROM {$prefix}recipes r
                    INNER JOIN {$prefix}collection_recipes cr ON r.id = cr.recipe_id
                    WHERE cr.collection_id = %d
                    ORDER BY cr.display_order ASC",
                    $id
                ),
                ARRAY_A
            ) ?: [];
        });

        foreach ($recipes as &$recipe) {
            if (!empty($recipe['image_id'])) {
                $recipe['image_url'] = wp_get_attachment_image_url((int) $recipe['image_id'], 'medium');
            }
            $recipe['rating_average'] = (float) $recipe['rating_average'];
        }

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

    /**
     * Add a recipe to a collection.
     */
    public function add_recipe_to_collection($request): WP_REST_Response|WP_Error {
        global $wpdb;
        $prefix = $wpdb->prefix . 'plugixarecipe_';
        $id = (int) $request->get_param('id');
        $params = $request->get_json_params();
        $recipe_id = (int) ($params['recipe_id'] ?? 0);

        if (!$recipe_id) {
            return $this->error(__('Recipe ID is required.', 'plugixarecipe'));
        }

        // Verify both exist
        $collection = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$prefix}collections WHERE id = %d", $id), ARRAY_A);
        if (!$collection) {
            return $this->error(__('Collection not found.', 'plugixarecipe'), 404);
        }

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

        // Get next display_order
        $max_order = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT MAX(display_order) FROM {$prefix}collection_recipes WHERE collection_id = %d",
            $id
        ));

        $insert_data = [
            'collection_id' => $id,
            'recipe_id'     => $recipe_id,
            'display_order' => $max_order + 1,
            'added_at'      => current_time('mysql'),
        ];

        // Meal plan support: day_number and meal_type
        if ($collection['type'] === 'meal_plan') {
            if (isset($params['day_number'])) {
                $insert_data['day_number'] = (int) $params['day_number'];
            }
            if (isset($params['meal_type'])) {
                $insert_data['meal_type'] = sanitize_text_field($params['meal_type']);
            }
        }

        $result = $wpdb->insert($prefix . 'collection_recipes', $insert_data);

        if ($result === false) {
            return $this->error(__('Recipe already exists in this collection or could not be added.', 'plugixarecipe'));
        }

        // Update collection modified_at
        $wpdb->update($prefix . 'collections', ['modified_at' => current_time('mysql')], ['id' => $id]);

        // Content write.
        Cache::flush('collections');

        return $this->success($insert_data, __('Recipe added to collection.', 'plugixarecipe'));
    }

    /**
     * Remove a recipe from a collection.
     */
    public function remove_recipe_from_collection($request): WP_REST_Response|WP_Error {
        global $wpdb;
        $prefix = $wpdb->prefix . 'plugixarecipe_';
        $id = (int) $request->get_param('id');
        $recipe_id = (int) $request->get_param('recipe_id');

        $deleted = $wpdb->delete($prefix . 'collection_recipes', [
            'collection_id' => $id,
            'recipe_id'     => $recipe_id,
        ]);

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

        $wpdb->update($prefix . 'collections', ['modified_at' => current_time('mysql')], ['id' => $id]);

        // Content write.
        Cache::flush('collections');

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

    /**
     * Reorder recipes in a collection.
     */
    public function reorder_recipes($request): WP_REST_Response|WP_Error {
        global $wpdb;
        $prefix = $wpdb->prefix . 'plugixarecipe_';
        $id = (int) $request->get_param('id');
        $params = $request->get_json_params();
        $items  = $params['items'] ?? [];

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

        foreach ($items as $item) {
            $update = ['display_order' => (int) ($item['display_order'] ?? 0)];
            if (isset($item['day_number'])) {
                $update['day_number'] = (int) $item['day_number'];
            }
            if (isset($item['meal_type'])) {
                $update['meal_type'] = sanitize_text_field($item['meal_type']);
            }
            $wpdb->update($prefix . 'collection_recipes', $update, [
                'collection_id' => $id,
                'recipe_id'     => (int) $item['recipe_id'],
            ]);
        }

        // Content write.
        Cache::flush('collections');

        return $this->success(null, __('Collection recipes reordered.', 'plugixarecipe'));
    }

    /**
     * Map frontend status (active/inactive) to DB status (published/draft).
     */
    private function map_status_from_frontend(string $status): string {
        return match ($status) {
            'active'  => 'published',
            'inactive' => 'draft',
            default   => $status,
        };
    }

    /**
     * Map DB status (published/draft) to frontend status (active/inactive).
     */
    private function map_status_to_frontend(string $status): string {
        return match ($status) {
            'published' => 'active',
            'draft'     => 'inactive',
            default     => $status,
        };
    }

    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;
    }
}
