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

/**
 * Categories REST Controller
 */
class CategoriesController extends RestController {

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

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

    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[] = '(c.name LIKE %s OR c.description LIKE %s)';
            $values[] = $search;
            $values[] = $search;
        }

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

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

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

        // Count
        $count_sql    = "SELECT COUNT(*) FROM {$prefix}categories c WHERE {$where_clause}";
        $count_values = $values;
        $total = (int) Cache::remember('categories', 'count|' . $count_sql . '|' . wp_json_encode($count_values), function () use ($wpdb, $count_sql, $count_values) {
            return !empty($count_values)
                ? (int) $wpdb->get_var($wpdb->prepare($count_sql, ...$count_values))
                : (int) $wpdb->get_var($count_sql);
        });

        // Main query with recipe_count
        $sql = "SELECT c.*, COUNT(rc.recipe_id) AS recipe_count
                FROM {$prefix}categories c
                LEFT JOIN {$prefix}recipe_categories rc ON c.id = rc.category_id
                WHERE {$where_clause}
                GROUP BY c.id
                ORDER BY c.{$orderby} {$order}
                LIMIT %d OFFSET %d";
        $values[] = $pagination['limit'];
        $values[] = $pagination['offset'];

        $items = Cache::remember('categories', 'items|' . $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'];
            if (!empty($item['image_id'])) {
                $item['image_url'] = wp_get_attachment_image_url((int) $item['image_id'], 'thumbnail');
            }
        }

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

    public function get_item($request): WP_REST_Response|WP_Error {
        global $wpdb;
        $prefix = $wpdb->prefix . 'plugixarecipe_';
        $id = (int) $request->get_param('id');

        $item = Cache::remember('categories', "item:{$id}", function () use ($wpdb, $prefix, $id) {
            return $wpdb->get_row(
                $wpdb->prepare("SELECT * FROM {$prefix}categories WHERE id = %d", $id),
                ARRAY_A
            );
        });

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

        $item['recipe_count'] = (int) Cache::remember('categories', "item-recipe-count:{$id}", function () use ($wpdb, $prefix, $id) {
            return (int) $wpdb->get_var(
                $wpdb->prepare("SELECT COUNT(*) FROM {$prefix}recipe_categories WHERE category_id = %d", $id)
            );
        });

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

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

    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['name'] ?? '');
        if (empty($name)) {
            return $this->error(__('Name is required.', 'plugixarecipe'));
        }

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

        $data = [
            'name'              => $name,
            'slug'              => $slug,
            'description'       => sanitize_textarea_field($params['description'] ?? ''),
            'image_id'          => !empty($params['image_id']) ? (int) $params['image_id'] : null,
            'display_order'     => (int) ($params['display_order'] ?? 0),
            'language_code'     => sanitize_text_field($params['language_code'] ?? ''),
            'translation_group' => sanitize_text_field($params['translation_group'] ?? ''),
            'created_at'        => current_time('mysql'),
        ];

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

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

        Cache::flush('categories', 'recipes');

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

    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}categories WHERE id = %d", $id), ARRAY_A);
        if (!$existing) {
            return $this->error(__('Category not found.', 'plugixarecipe'), 404);
        }

        $data = [];

        if (isset($params['name'])) {
            $data['name'] = sanitize_text_field($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 . 'categories', $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['display_order'])) {
            $data['display_order'] = (int) $params['display_order'];
        }
        if (isset($params['language_code'])) {
            $data['language_code'] = sanitize_text_field($params['language_code']);
        }
        if (isset($params['translation_group'])) {
            $data['translation_group'] = sanitize_text_field($params['translation_group']);
        }

        if (empty($data)) {
            return $this->error(__('No fields to update.', 'plugixarecipe'));
        }

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

        Cache::flush('categories', 'recipes');

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

    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}categories WHERE id = %d", $id));
        if (!$exists) {
            return $this->error(__('Category not found.', 'plugixarecipe'), 404);
        }

        // Remove pivot entries
        $wpdb->delete($prefix . 'recipe_categories', ['category_id' => $id]);
        $wpdb->delete($prefix . 'categories', ['id' => $id]);

        Cache::flush('categories', 'recipes');

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

    public function reorder($request): WP_REST_Response|WP_Error {
        global $wpdb;
        $prefix = $wpdb->prefix . 'plugixarecipe_';
        $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) {
            $wpdb->update(
                $prefix . 'categories',
                ['display_order' => (int) $item['display_order']],
                ['id' => (int) $item['id']]
            );
        }

        Cache::flush('categories', 'recipes');

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

    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'));
        $wpdb->query($wpdb->prepare("DELETE FROM {$prefix}recipe_categories WHERE category_id IN ($placeholders)", ...$ids));
        $wpdb->query($wpdb->prepare("DELETE FROM {$prefix}categories WHERE id IN ($placeholders)", ...$ids));

        Cache::flush('categories', 'recipes');

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

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