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

/**
 * Shortcodes REST Controller
 */
class ShortcodesController extends RestController {

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

    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 . '/bulk', [
            [
                'methods'             => 'DELETE',
                'callback'            => [$this, 'bulk_delete'],
                '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'],
            ],
        ]);
    }

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

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

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

        $count_sql    = "SELECT COUNT(*) FROM {$prefix}shortcodes WHERE {$where_clause}";
        $count_values = $values;
        $total = (int) Cache::remember('shortcodes', '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);
        });

        $sql = "SELECT * FROM {$prefix}shortcodes WHERE {$where_clause} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d";
        $values[] = $pagination['limit'];
        $values[] = $pagination['offset'];

        $items = Cache::remember('shortcodes', '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['config'] = json_decode($item['config'], true);
            $item['view_id'] = (int) ($item['config']['view_id'] ?? 0);
            if ($item['view_id']) {
                $view_id = $item['view_id'];
                // Cached under the "views" entity: a view write invalidates this name lookup.
                $view = Cache::remember('views', "name:{$view_id}", function () use ($wpdb, $prefix, $view_id) {
                    return $wpdb->get_row($wpdb->prepare(
                        "SELECT name FROM {$prefix}views WHERE id = %d", $view_id
                    ), ARRAY_A);
                });
                $item['view_name'] = $view ? $view['name'] : '';
            } else {
                $item['view_name'] = '';
            }
        }

        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('shortcodes', "item:{$id}", function () use ($wpdb, $prefix, $id) {
            return $wpdb->get_row(
                $wpdb->prepare("SELECT * FROM {$prefix}shortcodes WHERE id = %d", $id),
                ARRAY_A
            );
        });

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

        $item['config'] = json_decode($item['config'], true);

        $item['view_id'] = (int) ($item['config']['view_id'] ?? 0);
        if ($item['view_id']) {
            $view_id = $item['view_id'];
            // Cached under the "views" entity: a view write invalidates this name lookup.
            $view = Cache::remember('views', "name:{$view_id}", function () use ($wpdb, $prefix, $view_id) {
                return $wpdb->get_row($wpdb->prepare(
                    "SELECT name FROM {$prefix}views WHERE id = %d", $view_id
                ), ARRAY_A);
            });
            $item['view_name'] = $view ? $view['name'] : '';
        } else {
            $item['view_name'] = '';
        }

        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();
        $now = current_time('mysql');

        $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 . 'shortcodes');

        $config = $params['config'] ?? [];
        if (isset($params['view_id'])) {
            $config['view_id'] = (int) $params['view_id'];
        }

        $data = [
            'name'        => $name,
            'slug'        => $slug,
            'description' => sanitize_textarea_field($params['description'] ?? ''),
            'config'      => wp_json_encode($config),
            'created_at'  => $now,
            'created_by'  => get_current_user_id(),
        ];

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

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

        Cache::flush('shortcodes');

        $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}shortcodes WHERE id = %d", $id), ARRAY_A);
        if (!$existing) {
            return $this->error(__('Shortcode 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 . 'shortcodes', $id);
            }
            $data['slug'] = $slug;
        }
        if (isset($params['description'])) {
            $data['description'] = sanitize_textarea_field($params['description']);
        }
        if (isset($params['config'])) {
            $config = $params['config'];
            if (isset($params['view_id'])) {
                $config['view_id'] = (int) $params['view_id'];
            }
            $data['config'] = wp_json_encode($config);
        } elseif (isset($params['view_id'])) {
            $existing_config = json_decode($existing['config'], true) ?: [];
            $existing_config['view_id'] = (int) $params['view_id'];
            $data['config'] = wp_json_encode($existing_config);
        }

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

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

        Cache::flush('shortcodes');

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

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

        Cache::flush('shortcodes');

        return $this->success(null, __('Shortcode deleted.', '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'));
        $deleted = $wpdb->query($wpdb->prepare(
            "DELETE FROM {$prefix}shortcodes WHERE id IN ({$placeholders})",
            ...$ids
        ));

        Cache::flush('shortcodes');

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

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

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

        $new_name = $item['name'] . ' (Copy)';
        $new_slug = $this->ensure_unique_slug(sanitize_title($new_name), $prefix . 'shortcodes');

        $wpdb->insert($prefix . 'shortcodes', [
            'name'        => $new_name,
            'slug'        => $new_slug,
            'description' => $item['description'],
            'config'      => $item['config'],
            'created_at'  => $now,
            'created_by'  => get_current_user_id(),
        ]);

        $new_id = $wpdb->insert_id;

        Cache::flush('shortcodes');

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

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