<?php
namespace SaveToWishlist\Classes\Factories;

use SaveToWishlist\Abstracts\Singleton;
use SaveToWishlist\Helpers\Helper_Functions;
use SaveToWishlist\Classes\Factories\Collection;
use SaveToWishlist\Classes\Factories\Collection_Item;
use SaveToWishlist\Classes\Integrations\Product_Search;

/**
 * Class Collections
 *
 * This class creates an instance of a collections.
 *
 * @since 1.0.0
 */
class Collections extends Singleton {

    /**
     * Helper functions.
     *
     * @var Helper_Functions
     */
    private $helper_functions;

    /**
     * Wishlist IDs skipped by the last call to delete_collections() because
     * deleting them would leave their owner with zero wishlists — and
     * auto_create_wishlist() would just recreate one on the next page load.
     *
     * @since 1.0.8
     * @var int[]
     */
    private $skipped_last_wishlist_ids = array();

    /**
     * Constructor.
     *
     * @since 1.0.0
     */
    public function __construct() {
        $this->helper_functions = Helper_Functions::instance();
    }

    /**
     * Get the table name.
     *
     * @since 1.0.0
     * @access private
     *
     * @return string
     */
    private function get_table_name() {
        global $wpdb;

        return $wpdb->prefix . 'stw_collections';
    }

    /**
     * Get the collection items table name.
     *
     * @since 1.0.0
     * @access private
     *
     * @return string
     */
    private function get_collection_items_table_name() {
        global $wpdb;

        return $wpdb->prefix . 'stw_collection_items';
    }

    /**
     * Get the collection items.
     *
     * @param array $params The parameters. Supported keys: page, posts_per_page,
     *                      search, status, user_id, is_default, is_public,
     *                      sort_column, sort_order, hide_empty_non_admin,
     *                      created_from, created_to, updated_from, updated_to.
     *
     * @since 1.0.0
     * @since 1.0.6 Added `hide_empty_non_admin` to optionally exclude wishlists
     *              with zero items unless owned by an administrator.
     * @since 1.0.6 Added date range filters (`created_from`, `created_to`,
     *              `updated_from`, `updated_to`), the
     *              `stwlite_collections_allowed_sort_columns` filter, and
     *              whitelisted `sort_column`/`sort_order` against direct
     *              interpolation.
     * @access public
     *
     * @return array
     */
    public function get_collections( $params = array() ) {
        global $wpdb;

        $table_name = $this->get_table_name();

        // Pagination.
        $page     = isset( $params['page'] ) ? intval( $params['page'] ) : 1;
        $per_page = isset( $params['posts_per_page'] ) ? intval( $params['posts_per_page'] ) : 10;

        $offset = ( $page - 1 ) * $per_page;

        $where        = '';
        $prepare_args = array();
        if ( ! empty( $params['search'] ) ) {
            $where         .= ' AND name LIKE %s';
            $prepare_args[] = '%' . $wpdb->esc_like( $params['search'] ) . '%';
        }

        if ( ! empty( $params['status'] ) ) {
            $where         .= ' AND status = %s';
            $prepare_args[] = $params['status'];
        }

        if ( ! empty( $params['user_id'] ) ) {
            $where         .= ' AND user_id = %d';
            $prepare_args[] = (int) $params['user_id'];
        }

        if ( ! empty( $params['is_default'] ) ) {
            $where         .= ' AND is_default = %d';
            $prepare_args[] = (int) $params['is_default'];
        }

        if ( ! empty( $params['is_public'] ) ) {
            $where         .= ' AND is_public = %d';
            $prepare_args[] = (int) $params['is_public'];

            // Exclude wishlists owned by the current logged-in user when filtering for public wishlists.
            if ( is_user_logged_in() ) {
                $current_user_id = get_current_user_id();
                if ( $current_user_id > 0 ) {
                    $where         .= ' AND user_id != %d';
                    $prepare_args[] = $current_user_id;
                }
            }
        }

        // Date range filters. Accept YYYY-MM-DD or full datetime; coerce one-sided dates
        // so the "to" bound covers the full day.
        $date_filters = array(
            'created_from' => array(
                'column'     => 'created_at',
                'op'         => '>=',
                'end_of_day' => false,
            ),
            'created_to'   => array(
                'column'     => 'created_at',
                'op'         => '<=',
                'end_of_day' => true,
            ),
            'updated_from' => array(
                'column'     => 'updated_at',
                'op'         => '>=',
                'end_of_day' => false,
            ),
            'updated_to'   => array(
                'column'     => 'updated_at',
                'op'         => '<=',
                'end_of_day' => true,
            ),
        );
        foreach ( $date_filters as $param => $config ) {
            if ( empty( $params[ $param ] ) ) {
                continue;
            }
            $normalized = self::normalize_date_param( $params[ $param ], $config['end_of_day'] );
            if ( null === $normalized ) {
                continue;
            }
            $where         .= " AND {$config['column']} {$config['op']} %s";
            $prepare_args[] = $normalized;
        }

        // Hide wishlists that have no items, unless owned by an administrator.
        // Auto-created empty wishlists on stores with large user bases otherwise
        // flood the admin listing; this filter lets the admin opt in to hiding them
        // while preserving admin-authored wishlists that may be intentionally empty.
        if ( ! empty( $params['hide_empty_non_admin'] ) ) {
            $items_table         = $this->get_collection_items_table_name();
            $has_items_condition = "EXISTS ( SELECT 1 FROM {$items_table} WHERE {$items_table}.collection_id = {$table_name}.id )";

            // Cache the administrator ID list — the admin set is highly stable and
            // this query (`WP_User_Query` with role=administrator) joins wp_usermeta
            // and LIKEs against wp_capabilities, an unindexed scan. Without caching
            // it would re-run on every paginated/searched/keystroke admin request.
            $cache_key = 'stwlite_admin_ids';
            $admin_ids = wp_cache_get( $cache_key, STWLITE_CACHE_GROUP );
            if ( false === $admin_ids ) {
                $admin_ids = get_users(
                    array(
                        'role'   => 'administrator',
                        'fields' => 'ID',
                    )
                );
                $admin_ids = array_filter( array_map( 'absint', (array) $admin_ids ) );
                wp_cache_set( $cache_key, $admin_ids, STWLITE_CACHE_GROUP, 5 * MINUTE_IN_SECONDS );
            }

            if ( ! empty( $admin_ids ) ) {
                $placeholders = implode( ',', array_fill( 0, count( $admin_ids ), '%d' ) );
                $where       .= " AND ( {$has_items_condition} OR {$table_name}.user_id IN ({$placeholders}) )";
                $prepare_args = array_merge( $prepare_args, $admin_ids );
            } else {
                $where .= " AND {$has_items_condition}";
            }
        }

        // Filter the where clause.
        $where = apply_filters( 'stwlite_collections_where', $where, $params );

        // Whitelist sort column and order to prevent SQL injection via direct interpolation.
        // Filterable so Pro/extension code can opt extra columns into the allow-list.
        //
        // SECURITY: Callbacks MUST return only literal column names that are safe
        // to interpolate into ORDER BY. Do not return values derived from request
        // input — that would re-open the SQL injection vector this whitelist closes.
        $allowed_sort_columns = apply_filters(
            'stwlite_collections_allowed_sort_columns',
            array( 'id', 'name', 'status', 'user_id', 'is_default', 'is_public', 'created_at', 'updated_at' )
        );
        $sort_column          = ! empty( $params['sort_column'] ) ? (string) $params['sort_column'] : 'id';
        if ( ! in_array( $sort_column, $allowed_sort_columns, true ) ) {
            $sort_column = 'id';
        }
        $sort_order = ! empty( $params['sort_order'] ) ? strtoupper( (string) $params['sort_order'] ) : 'DESC';
        if ( ! in_array( $sort_order, array( 'ASC', 'DESC' ), true ) ) {
            $sort_order = 'DESC';
        }

        $order_by = "{$sort_column} {$sort_order}";
        $order_by = apply_filters( 'stwlite_collections_order_by', $order_by );

        $query_args   = $prepare_args;
        $query_args[] = $per_page;
        $query_args[] = $offset;

        // phpcs:disable

        $items = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table_name} WHERE 1=1 {$where} ORDER BY {$order_by} LIMIT %d OFFSET %d", $query_args ) );
        $total = $wpdb->get_var(
            ! empty( $prepare_args )
                ? $wpdb->prepare( "SELECT COUNT(*) FROM {$table_name} WHERE 1=1 {$where}", $prepare_args )
                : "SELECT COUNT(*) FROM {$table_name} WHERE 1=1 {$where}"
        );

        // phpcs:enable

        // Prime WP user cache for all rows in a single query, eliminating
        // the N individual get_userdata() calls inside Collection::__construct.
        if ( ! empty( $items ) ) {
            $user_ids = array_unique( array_filter( wp_list_pluck( $items, 'user_id' ) ) );
            if ( ! empty( $user_ids ) ) {
                cache_users( $user_ids );
            }
        }

        $data = array_map(
            function ( $item ) {
                return Collection::get_instance( $item );
            },
            $items
        );

        // Filter the data.
        $data = apply_filters( 'stwlite_collections_data', $data );

        $data = array(
            'data'        => $data,
            'total'       => (int) $total,
            'currentPage' => $page,
        );

        return $data;
    }

    /**
     * Get a collection.
     *
     * @param int|string $value The collection value.
     * @param string     $field The field to use for the query.
     *
     * @since 1.0.0
     * @access public
     *
     * @return array
     */
    public function get_collection( $value = 0, $field = 'id' ) {
        global $wpdb;

        $table_name = $this->get_table_name();

        $allowed_fields = array( 'id', 'url_code', 'name' );
        if ( ! in_array( $field, $allowed_fields, true ) ) {
            $field = 'id';
        }

        // phpcs:disable

        $collection = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table_name} WHERE {$field} = %s", $value ) );
        if ( ! $collection ) {
            return null;
        }

        // phpcs:enable

        // Filter the collection.
        $collection = apply_filters( 'stwlite_collection_data', $collection );

        return $collection;
    }

    /**
     * Save a collection.
     *
     * @param array $params The parameters.
     *
     * @since 1.0.0
     * @since 1.0.2 Set the first collection as the default wishlist if no default wishlist is set.
     * @access public
     *
     * @return array
     */
    public function save_collection( $params = array() ) {
        global $wpdb;

        // phpcs:disable

        $table_name = $this->get_table_name();
        $user_id    = ! empty( $params['user_id'] ) ? $params['user_id'] : get_current_user_id();

        // Check if the user has a default wishlist.
        $default_wishlist = $this->get_collections(
            array(
                'user_id'    => $user_id,
                'is_default' => 1,
            )
        );
        if ( empty( $default_wishlist['data'] ) && 'publish' === $params['status'] ) {
            $params['is_default'] = 1;
        }

        $is_default = (int) $params['is_default'] ?? 0;

        $data = array(
            'user_id'     => $user_id,
            'url_code'    => ! empty( $params['url_code'] ) ? $params['url_code'] : $this->generate_url_code(),
            'name'        => $params['name'],
            'description' => wp_kses_post( $params['description'] ),
            'status'      => $params['status'],
            'is_default'  => $is_default,
            'is_public'   => $params['is_public'],
        );

        // Filter the data to update.
        $data = apply_filters( 'stwlite_collection_data_to_update', $data, $params );

        do_action( 'stwlite_before_save_collection', $data );

        // Update the default wishlist before adding a new one or updating an existing one.
        if ( 'publish' === $params['status'] && 1 === $is_default ) {
            $this->update_default_wishlist( $user_id );
        }

        $id = 0;
        if ( ! empty( $params['id'] ) ) {
            $id = (int) $params['id'];
            $wpdb->update( $table_name, $data, array( 'id' => $id ) );
        } else {
            $wpdb->insert( $table_name, $data );
            $id = $wpdb->insert_id;
        }

        // Update the default wishlist if no default wishlist is set.
        // Get the first collection by status publish.
        $user_wishlists = $this->get_collections(
            array(
                'user_id'    => $user_id,
                'status'     => 'publish',
                'is_default' => 1,
            )
        );
        
        // If no default wishlist is set, set the first collection as the default wishlist.
        if ( empty( $user_wishlists['data'] ) ) {
            // Get the first collection by status publish.
            $first_collection = $this->get_collections(
                array(
                    'user_id'    => $user_id,
                    'status'     => 'publish',
                    'is_default' => 0,
                )
            );

            if ( ! empty( $first_collection['data'] ) ) {
                // Make sure to reset the default wishlist before setting the new default wishlist.
                $this->update_default_wishlist( $user_id );

                // Set the new default wishlist.
                $this->update_default_wishlist( $user_id, 1, $first_collection['data'][0]->id );
            }
        }

        $items = $params['items'] ?? array();
        if ( ! empty( $items ) ) {
            $product_ids = array();
            foreach ( $items as $item ) {

                $product_id   = (int) $item['product_id'];
                $variation_id = isset( $item['variation_id'] ) ? (int) $item['variation_id'] : 0;

                $product = wc_get_product( $product_id );

                // If the caller passed a variation ID as product_id, split it into parent + variation.
                if ( $product && $product->is_type( 'variation' ) ) {
                    $variation_id = $product_id;
                    $product_id   = $product->get_parent_id();
                }

                $product_ids[] = $product_id;

                $this->save_collection_item(
                    array(
                        'id'            => $item['id'] ?? 0,
                        'collection_id' => $id,
                        'product_id'    => $product_id,
                        'variation_id'  => $variation_id,
                        'quantity'      => $item['quantity'] ?? 1,
                        'priority'      => $item['priority'] ?? 0,
                        'notes'         => $item['notes'] ?? '',
                    )
                );
            }

            // Remove the items that are not in the collection items.
            $this->delete_collection_items( $id, $product_ids );
        } else if ( ! empty( $params['admin_request'] ) && empty( $items ) ) {
            $this->delete_collection_items_by_admin( $id );
        }

        wp_cache_delete( 'collection_items_' . $id, STWLITE_CACHE_GROUP );
        // phpcs:enable

        $collection = $this->get_collection( $id );
        $data       = Collection::get_instance( $collection );

        do_action( 'stwlite_after_save_collection', $data, $params );

        // Filter the data.
        $data = apply_filters( 'stwlite_single_collection_data', $data );

        return $data;
    }

    /**
     * Update the user wishlists.
     *
     * @param int $user_id The user ID.
     * @param int $data_default The data to update the default wishlist.
     * @param int $where_id The where clause for the default wishlist.
     *
     * @since 1.0.0
     * @access public
     */
    public function update_default_wishlist( $user_id, $data_default = 0, $where_id = 0 ) {
        global $wpdb;

        $table_name = $this->get_table_name();

        // phpcs:disable

        $data = array(
            'is_default' => $data_default,
        );
        $where = array(
            'user_id'    => $user_id,
            'is_default' => 1,
        );
        if ( ! empty( $where_id ) ) {
            $where = array( 'id' => $where_id );
        }
        $wpdb->update(
            $table_name,
            $data,
            $where
        );

        // phpcs:enable

        wp_cache_delete( 'collections', STWLITE_CACHE_GROUP );

        return true;
    }

    /**
     * Save a collection item.
     *
     * @param array $params The parameters.
     * @param bool  $force_insert Whether to force insert the item.
     *
     * @since 1.0.0
     * @since 1.0.4 Added force insert parameter.
     * @access public
     *
     * @return array
     */
    public function save_collection_item( $params = array(), $force_insert = false ) {
        global $wpdb;

        $table_name = $this->get_collection_items_table_name();

        $data = array(
            'collection_id' => $params['collection_id'] ?? 0,
            'product_id'    => $params['product_id'] ?? 0,
            'variation_id'  => $params['variation_id'] ?? 0,
            'quantity'      => $params['quantity'] ?? 0,
            'priority'      => $params['priority'] ?? 0,
            'notes'         => $params['notes'] ?? '',
        );

        // Filter the data.
        $data = $this->helper_functions->filter_array_by_params( $data, $params );

        do_action( 'stwlite_before_save_collection_item', $data );

        // phpcs:disable
        if ( ! empty( $params['id'] ) ) {
            $wpdb->update( $table_name, $data, array( 'id' => $params['id'] ) );

            do_action( 'stwlite_after_update_collection_item', $data );

            return $params['id'];
        } else {

            
            // Check if the item already exists.
            $where_clause = "collection_id = %d AND product_id = %d";
            $where_args   = array( $params['collection_id'], $params['product_id'] );

            if ( ! empty( $params['variation_id'] ) ) {
                $where_clause .= " AND variation_id = %d";
                $where_args[]  = $params['variation_id'];
            }

            $exists = $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$table_name} WHERE {$where_clause}", $where_args ) );
            if ( $exists && ! $force_insert ) {
                $wpdb->update( $table_name, $data, array( 'id' => $exists ) );

                do_action( 'stwlite_after_update_collection_item', $data );

                return $exists;
            } else {
                $wpdb->insert( $table_name, $data );

                do_action( 'stwlite_after_insert_collection_item', $data );

                return $wpdb->insert_id;
            }

        }
        // phpcs:enable
    }

    /**
     * Move a collection item.
     *
     * @param array $params The parameters.
     *
     * @since 1.0.0
     * @access public
     *
     * @return int|null
     */
    public function move_collection_item( $params = array() ) {
        global $wpdb;

        $table_name = $this->get_collection_items_table_name();

        // Collection ID and ID is exist.
        if ( empty( $params['collection_id'] ) || empty( $params['id'] ) ) {
            return null;
        }

        $data = array(
            'collection_id' => $params['collection_id'] ?? 0,
        );

        // phpcs:disable

        $old_data = $wpdb->get_row( $wpdb->prepare( "SELECT id, product_id, collection_id, quantity FROM {$table_name} WHERE id = %d", $params['id'] ) );

        // make sure the old data is exists.
        if ( empty( $old_data ) ) {
            return null;
        }

        $exists = $wpdb->get_row( $wpdb->prepare( "SELECT id, quantity FROM {$table_name} WHERE collection_id = %d AND product_id = %d", $params['collection_id'], $old_data->product_id ) );

        $item_id = $old_data->id;
        if ( ! empty( $exists ) ) {
            $item_id = $exists->id;

            $data['quantity'] = (int) $exists->quantity + (int) $old_data->quantity;
            $wpdb->update( $table_name, $data, array( 'id' => $exists->id ) );

            // Delete the old item.
            $wpdb->delete( $table_name, array( 'id' => $old_data->id ) );
        } else {
            $data['quantity'] = (int) $old_data->quantity;
            $wpdb->update( $table_name, $data, array( 'id' => $old_data->id ) );
        }

        // phpcs:enable

        wp_cache_delete( 'collection_items_' . $old_data->collection_id, STWLITE_CACHE_GROUP );
        wp_cache_delete( 'collection_items_' . $params['collection_id'], STWLITE_CACHE_GROUP );

        do_action( 'stwlite_after_move_collection_item', $data, $item_id );

        return $item_id;
    }

    /**
     * Update a collection item.
     *
     * @param int $product_id The product ID.
     * @param int $collection_id The collection ID.
     * @param int $quantity The quantity.
     * @param int $variation_id The variation ID.
     *
     * @since 1.0.0
     * @since 1.0.4 Added variation ID.
     * @access public
     */
    public function update_collection_quantity_item( $product_id, $collection_id, $quantity = 0, $variation_id = 0 ) {
        global $wpdb;

        $table_name = $this->get_collection_items_table_name();

        do_action( 'stwlite_before_update_collection_quantity_item', $product_id, $collection_id, $quantity, $variation_id );

        // phpcs:disable
        $wpdb->update(
            $table_name,
            array( 'quantity' => $quantity ),
            array(
                'product_id'    => $product_id,
                'collection_id' => $collection_id,
                'variation_id'  => $variation_id,
            )
        );
        // phpcs:enable

        do_action( 'stwlite_after_update_collection_quantity_item', $product_id, $collection_id, $quantity, $variation_id );

        wp_cache_delete( 'collection_items_' . $collection_id, STWLITE_CACHE_GROUP );

        return true;
    }

    /**
     * Get the wishlist items.
     *
     * @param int   $collection_id The collection ID.
     * @param array $params The parameters.
     *
     * @since 1.0.0
     * @since 1.0.6 Search now matches SKU and GTIN/Barcode meta in addition to title.
     * @access public
     *
     * @return array
     */
    public function get_collection_items( $collection_id = 0, $params = array() ) {
        global $wpdb;

        $table_name = $this->get_collection_items_table_name();

        // Pagination.
        $page     = isset( $params['page'] ) ? intval( $params['page'] ) : 1;
        $per_page = isset( $params['posts_per_page'] ) ? intval( $params['posts_per_page'] ) : 10;

        $offset = ( $page - 1 ) * $per_page;

        $posts_table  = $wpdb->prefix . 'posts';
        $join         = "INNER JOIN {$posts_table} ON {$table_name}.product_id = {$posts_table}.ID AND {$posts_table}.post_status = 'publish'";
        $where        = '';
        $prepare_args = array();

        if ( ! empty( $collection_id ) ) {
            $where         .= " AND {$table_name}.collection_id = %d";
            $prepare_args[] = $collection_id;
        }

        if ( ! empty( $params['search'] ) ) {
            $like = '%' . $wpdb->esc_like( $params['search'] ) . '%';

            // Pre-fetch parent product IDs whose SKU or GTIN/Barcode meta matches
            // the term. Reuses Product_Search so both the admin "Add product"
            // search and the wishlist items search share the same meta key list
            // (extendable via the `stwlite_product_search_gtin_meta_keys` filter)
            // and the bounded LIKE lookup.
            $meta_keys = array_merge( array( '_sku' ), Product_Search::get_gtin_meta_keys() );
            $matched_product_ids = Product_Search::instance()->find_product_ids_by_meta( $params['search'], $meta_keys );

            if ( ! empty( $matched_product_ids ) ) {
                $id_placeholders = implode( ',', array_fill( 0, count( $matched_product_ids ), '%d' ) );
                $where          .= " AND ( {$posts_table}.post_title LIKE %s OR {$table_name}.product_id IN ({$id_placeholders}) )";
                $prepare_args[]  = $like;
                foreach ( $matched_product_ids as $matched_id ) {
                    $prepare_args[] = (int) $matched_id;
                }
            } else {
                $where         .= " AND {$posts_table}.post_title LIKE %s";
                $prepare_args[] = $like;
            }
        }

        // Filter the where clause.
        $where = apply_filters( 'stwlite_collection_items_where', $where, $params );

        $sort_column = ! empty( $params['sort_column'] ) ? $params['sort_column'] : 'sort_order';
        $sort_order  = ! empty( $params['sort_order'] ) ? $params['sort_order'] : 'ASC';

        // Default order by.
        $order_by = "{$table_name}.{$sort_column} {$sort_order}, {$table_name}.id DESC";

        if ( 'name' === $sort_column ) {
            $order_by = "{$posts_table}.post_title {$sort_order}, {$table_name}.sort_order ASC, {$table_name}.product_id ASC";
        }

        $order_by = apply_filters( 'stwlite_collection_items_order_by', $order_by, $params );

        $query_args   = $prepare_args;
        $query_args[] = $per_page;
        $query_args[] = $offset;

        // phpcs:disable

        $items = $wpdb->get_results( $wpdb->prepare( "SELECT {$table_name}.* FROM {$table_name} {$join} WHERE 1=1 {$where} ORDER BY {$order_by} LIMIT %d OFFSET %d", $query_args ) );
        $total = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT({$table_name}.id) FROM {$table_name} {$join} WHERE 1=1 {$where}", $prepare_args ) );

        // phpcs:enable

        $data = array_map(
            function ( $item ) {
                return Collection_Item::get_instance( $item );
            },
            $items
        );

        // Filter the data.
        $data = apply_filters( 'stwlite_collection_items_data', $data );

        $data = array(
            'data'        => $data,
            'total'       => (int) $total,
            'currentPage' => $page,
        );

        return $data;
    }

    /**
     * Get the products in wishlist.
     *
     * @param int   $product_id The product ID.
     * @param int   $collection_id The collection ID.
     * @param int   $user_id The user ID.
     * @param array $fields The fields to return.
     * @param bool  $items Whether to return the items.
     * @param bool  $return_single_product Whether to return a single product.
     * @param bool  $exclude_draft Whether to exclude items in draft collections.
     *
     * @since 1.0.0
     * @since 1.0.5 Added $exclude_draft parameter.
     * @access public
     */
    public function get_products_in_wishlist( $product_id = 0, $collection_id = 0, $user_id = 0, $fields = array(), $items = true, $return_single_product = false, $exclude_draft = false ) {
        global $wpdb;

        $table_name       = $this->get_table_name();
        $items_table_name = $this->get_collection_items_table_name();

        $where        = '';
        $prepare_args = array();

        if ( ! empty( $collection_id ) ) {
            $where         .= " AND {$items_table_name}.collection_id = %d";
            $prepare_args[] = $collection_id;
        }

        if ( ! empty( $product_id ) ) {
            $where         .= " AND {$items_table_name}.product_id = %d";
            $prepare_args[] = $product_id;
        }

        if ( ! empty( $user_id ) ) {
            $where         .= " AND {$table_name}.user_id = %d";
            $prepare_args[] = $user_id;
        }

        if ( $exclude_draft ) {
            $where .= " AND {$table_name}.status != 'draft'";
        }

        $join = "INNER JOIN {$table_name} ON {$items_table_name}.collection_id = {$table_name}.id";

        // Filter the where clause.
        $where = apply_filters( 'stwlite_get_products_in_wishlist_where', $where, $product_id, $collection_id, $user_id );

        $selected_fields = $items_table_name . '*';
        $table_use       = $items_table_name;
        if ( ! $items ) {
            $table_use = $table_name;
        }

        if ( ! empty( $fields ) ) {
            $selected_fields = implode(
                ',',
                array_map(
                    function ( $field ) use ( $table_use ) {
                        return "{$table_use}.{$field}";
                    },
                    $fields
                )
            );
        }

        // Filter the selected fields.
        $selected_fields = apply_filters( 'stwlite_get_products_in_wishlist_fields', $selected_fields, $fields, $items, $table_use );

        // phpcs:disable

        // Get the products.
        $products = array();
        if ( $return_single_product ) {
            $products = $wpdb->get_row( $wpdb->prepare( "SELECT {$selected_fields} FROM {$items_table_name} {$join} WHERE 1=1 {$where}", $prepare_args ) );
        } else {
            $products = $wpdb->get_results( $wpdb->prepare( "SELECT {$selected_fields} FROM {$items_table_name} {$join} WHERE 1=1 {$where}", $prepare_args ) );
        }

        // phpcs:enable

        // Filter the products.
        $products = apply_filters( 'stwlite_get_products_in_wishlist', $products, $collection_id, $user_id, $fields, $return_single_product );

        return $products;
    }

    /**
     * Get a collection item that belongs to a draft wishlist for the given user.
     *
     * Used when adding a product to a published wishlist: if the product already
     * lives in a draft collection we reassign it rather than inserting a duplicate.
     * Returns the first match only — if the same product is stored across multiple
     * draft wishlists, subsequent add-to-wishlist calls will drain them one at a time.
     *
     * @param int $product_id   The parent product ID (never a variation ID).
     * @param int $variation_id The variation ID; 0 for simple products.
     * @param int $user_id      The wishlist owner ID.
     *
     * @since 1.0.5
     * @access public
     *
     * @return object|null Row with `id` and `collection_id`, or null if not found.
     */
    public function get_item_in_draft_collection( $product_id, $variation_id, $user_id ) {
        global $wpdb;

        $table_name       = $this->get_table_name();
        $items_table_name = $this->get_collection_items_table_name();

        $variation_clause = '';
        $prepare_args     = array( (int) $product_id, (int) $user_id );

        if ( ! empty( $variation_id ) ) {
            $variation_clause = ' AND i.variation_id = %d';
            $prepare_args[]   = (int) $variation_id;
        } else {
            // Restrict to parent/simple-product rows so a shared product_id cannot
            // match a variation row from a different variation choice.
            $variation_clause = ' AND (i.variation_id = 0 OR i.variation_id IS NULL)';
        }

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $wpdb->get_row(
            $wpdb->prepare(
                "SELECT i.id, i.collection_id
                 FROM {$items_table_name} i
                 INNER JOIN {$table_name} c ON i.collection_id = c.id
                 WHERE i.product_id = %d
                   AND c.user_id = %d
                   AND c.status = 'draft'
                   {$variation_clause}
                 LIMIT 1",
                $prepare_args
            )
        );
        // phpcs:enable
    }

    /**
     * Get a collection item.
     *
     * @param int $product_id The product ID.
     * @param int $collection_id The collection ID.
     *
     * @since 1.0.0
     * @access public
     *
     * @return array
     */
    public function get_collection_item( $product_id, $collection_id ) {
        global $wpdb;

        // phpcs:disable
        $table_name = $this->get_collection_items_table_name();
        $item = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table_name} WHERE product_id = %d AND collection_id = %d", $product_id, $collection_id ) );
        // phpcs:enable

        return $item;
    }

    /**
     * Delete a collection.
     *
     * @param array $ids The collection IDs.
     *
     * @since 1.0.0
     * @access public
     *
     * @return int
     */
    public function delete_collections( $ids = array() ) {
        global $wpdb;

        $table_name = $this->get_table_name();

        $this->skipped_last_wishlist_ids = array();

        if ( empty( $ids ) ) {
            return 0;
        }

        $ids = array_map( 'absint', (array) $ids );
        $ids = array_filter( $ids );

        // Drop IDs that would leave their owner with zero wishlists.
        // auto_create_wishlist() recreates a default whenever a logged-in user
        // has no wishlists, so attempting to delete the last one is a no-op
        // from the user's perspective — they delete, then it reappears on
        // the next page load. See issue #77.
        $ids = $this->exclude_last_user_wishlist_ids( $ids );

        do_action( 'stwlite_before_delete_collections', $ids );

        if ( empty( $ids ) ) {
            do_action( 'stwlite_after_delete_collections', $ids, 0 );
            return 0;
        }

        // phpcs:disable

        $placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
        $sql          = "DELETE FROM {$table_name} WHERE id IN ($placeholders)";
        $deleted      = $wpdb->query( $wpdb->prepare( $sql, $ids ) );

        // phpcs:enable

        do_action( 'stwlite_after_delete_collections', $ids, $deleted );

        return $deleted;
    }

    /**
     * Filter out wishlist IDs whose deletion would leave the owning user
     * with zero wishlists. Records the dropped IDs in
     * $this->skipped_last_wishlist_ids so callers can surface a clear
     * "cannot delete the last wishlist" message.
     *
     * Wishlists owned by user_id 0 (e.g. orphaned guest wishlists) are not
     * protected — auto_create_wishlist() only fires for logged-in users.
     *
     * @since 1.0.8
     * @access private
     *
     * @param int[] $ids Sanitized, non-zero wishlist IDs.
     *
     * @return int[] The subset that is safe to delete.
     */
    private function exclude_last_user_wishlist_ids( $ids ) {
        global $wpdb;

        if ( empty( $ids ) ) {
            return $ids;
        }

        $table_name = $this->get_table_name();

        // phpcs:disable
        $placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
        $rows         = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT id, user_id, is_default FROM {$table_name} WHERE id IN ({$placeholders})",
                $ids
            )
        );
        // phpcs:enable

        if ( empty( $rows ) ) {
            return $ids;
        }

        $selected_by_user = array();
        foreach ( $rows as $row ) {
            $owner_id = (int) $row->user_id;
            if ( $owner_id <= 0 ) {
                continue;
            }
            $selected_by_user[ $owner_id ][] = array(
                'id'         => (int) $row->id,
                'is_default' => (int) $row->is_default,
            );
        }

        if ( empty( $selected_by_user ) ) {
            return $ids;
        }

        // Batch the per-user totals into a single query so bulk admin deletes
        // across many users don't trigger an N+1.
        $owner_ids          = array_keys( $selected_by_user );
        $owner_placeholders = implode( ',', array_fill( 0, count( $owner_ids ), '%d' ) );

        // phpcs:disable
        $count_rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT user_id, COUNT(*) AS total FROM {$table_name} WHERE user_id IN ({$owner_placeholders}) GROUP BY user_id",
                $owner_ids
            )
        );
        // phpcs:enable

        $totals_by_user = array();
        foreach ( (array) $count_rows as $count_row ) {
            $totals_by_user[ (int) $count_row->user_id ] = (int) $count_row->total;
        }

        $skipped = array();
        foreach ( $selected_by_user as $owner_id => $selected_rows ) {
            $total = isset( $totals_by_user[ $owner_id ] ) ? $totals_by_user[ $owner_id ] : 0;

            if ( $total - count( $selected_rows ) >= 1 ) {
                continue;
            }

            // Prefer the user's `is_default` row when present so the
            // explicitly-marked default survives a bulk delete; otherwise
            // fall back to the lowest id (typically the oldest).
            usort(
                $selected_rows,
                function ( $a, $b ) {
                    if ( $a['is_default'] !== $b['is_default'] ) {
                        return $b['is_default'] - $a['is_default'];
                    }
                    return $a['id'] - $b['id'];
                }
            );
            $skipped[] = $selected_rows[0]['id'];
        }

        if ( empty( $skipped ) ) {
            return $ids;
        }

        $this->skipped_last_wishlist_ids = $skipped;

        return array_values( array_diff( $ids, $skipped ) );
    }

    /**
     * Wishlist IDs the most recent delete_collections() call refused to
     * delete because each was its owner's last wishlist.
     *
     * @since 1.0.8
     * @access public
     *
     * @return int[]
     */
    public function get_skipped_last_wishlist_ids() {
        return $this->skipped_last_wishlist_ids;
    }

    /**
     * Filter wishlist IDs down to those whose owner column matches $user_id.
     * Used to enforce ownership before destructive operations on the
     * customer-facing REST endpoint, where the route's permission_callback
     * only requires a logged-in user. Admin checks must be applied by the
     * caller — this helper performs no privilege escalation.
     *
     * @since 1.0.8
     * @access public
     *
     * @param mixed $ids     Wishlist IDs to filter (array of ints or scalars).
     * @param int   $user_id Required owner user_id.
     *
     * @return int[] The subset of $ids owned by $user_id.
     */
    public function filter_ids_owned_by_user( $ids, $user_id ) {
        global $wpdb;

        $user_id = (int) $user_id;
        if ( $user_id <= 0 ) {
            return array();
        }

        $ids = array_filter(
            array_map( 'intval', (array) $ids ),
            function ( $id ) {
                return $id > 0;
            }
        );
        $ids = array_values( array_unique( $ids ) );

        if ( empty( $ids ) ) {
            return array();
        }

        $table_name   = $this->get_table_name();
        $placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );

        // phpcs:disable
        $owned = $wpdb->get_col(
            $wpdb->prepare(
                "SELECT id FROM {$table_name} WHERE user_id = %d AND id IN ({$placeholders})",
                array_merge( array( $user_id ), $ids )
            )
        );
        // phpcs:enable

        return array_map( 'intval', (array) $owned );
    }

    /**
     * Delete a collection item.
     *
     * @param int   $collection_id The collection ID.
     * @param array $product_ids   The product IDs.
     * @param bool  $reverse_condition The reverse condition.
     * @param array $variation_ids The variation IDs.
     *
     * @since 1.0.0
     * @access public
     *
     * @return int
     */
    public function delete_collection_items( $collection_id, $product_ids = array(), $reverse_condition = true, $variation_ids = array() ) {
        global $wpdb;

        $table_name = $this->get_collection_items_table_name();

        if ( ! empty( $product_ids ) ) {

            do_action( 'stwlite_before_delete_collection_items', $collection_id, $product_ids, $reverse_condition );

            $prepare_args = array( $collection_id );
            $product_ids  = array_map( 'absint', (array) $product_ids );
            $product_ids  = array_filter( $product_ids );
            $placeholders = implode( ',', $product_ids );

            $in_condition = "product_id IN ($placeholders)";
            if ( $reverse_condition ) {
                $in_condition = "product_id NOT IN ($placeholders)";
            }

            if ( ! empty( $variation_ids ) ) {
                $variation_ids          = array_map( 'absint', (array) $variation_ids );
                $variation_ids          = array_filter( $variation_ids );
                $variation_placeholders = implode( ', ', array_fill( 0, count( $variation_ids ), '%d' ) );
                $in_condition          .= " AND variation_id IN ($variation_placeholders)";
                $prepare_args           = array_merge( $prepare_args, $variation_ids );
            }

            // phpcs:disable

            $sql     = "DELETE FROM {$table_name} WHERE collection_id = %d AND {$in_condition}";
            $deleted = $wpdb->query( $wpdb->prepare( $sql, $prepare_args ) );

            // phpcs:enable

            do_action( 'stwlite_after_delete_collection_items', $collection_id, $product_ids, $reverse_condition, $deleted );

            return $deleted;
        }
    }

    /**
     * Delete a single collection item by its primary key id.
     *
     * @param int $item_id The collection_items row id.
     *
     * @since 1.0.5
     * @access public
     *
     * @return int|false Number of rows deleted, or false on error.
     */
    public function delete_collection_item_by_id( $item_id ) {
        global $wpdb;

        $item_id = (int) $item_id;
        if ( $item_id <= 0 ) {
            return 0;
        }

        $table_name = $this->get_collection_items_table_name();

        // phpcs:disable WordPress.DB.DirectDatabaseQuery
        return $wpdb->delete( $table_name, array( 'id' => $item_id ), array( '%d' ) );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery
    }

    /**
     * Delete a collection item by user.
     *
     * @param array $product_ids   The product IDs.
     * @param int   $user_id The user ID.
     * @param array $variation_ids The variation IDs.
     *
     * @since 1.0.0
     * @since 1.0.4 Added variation IDs.
     * @access public
     *
     * @return int
     */
    public function delete_collection_items_by_user( $product_ids = array(), $user_id = 0, $variation_ids = array() ) {
        global $wpdb;

        $table_name       = $this->get_table_name();
        $items_table_name = $this->get_collection_items_table_name();

        if ( ! empty( $product_ids ) ) {

            do_action( 'stwlite_before_delete_collection_items_by_user', $product_ids, $user_id, $variation_ids );

            $prepare_args = array();
            $product_ids  = array_map( 'absint', (array) $product_ids );
            $product_ids  = array_filter( $product_ids );
            $placeholders = implode( ',', $product_ids );

            $where = "{$items_table_name}.product_id IN ($placeholders)";

            $join = "INNER JOIN {$table_name} ON {$items_table_name}.collection_id = {$table_name}.id";

            if ( ! empty( $user_id ) ) {
                $where         .= " AND {$table_name}.user_id = %d";
                $prepare_args[] = $user_id;
            }

            // If variation IDs are provided, add them to the where clause.
            if ( ! empty( $variation_ids ) ) {
                $variation_ids          = array_map( 'absint', (array) $variation_ids );
                $variation_ids          = array_filter( $variation_ids );
                $variation_placeholders = implode( ',', array_fill( 0, count( $variation_ids ), '%d' ) );
                $where                 .= " AND {$items_table_name}.variation_id IN ($variation_placeholders)";
                $prepare_args           = array_merge( $prepare_args, $variation_ids );
            }

            $where = apply_filters( 'stwlite_delete_collection_items_by_user_where', $where, $product_ids, $user_id, $variation_ids );

            // phpcs:disable

            $sql     = "DELETE {$items_table_name} FROM {$items_table_name} {$join} WHERE {$where}";
            $deleted = $wpdb->query( $wpdb->prepare( $sql, $prepare_args ) );

            // phpcs:enable

            do_action( 'stwlite_after_delete_collection_items_by_user', $product_ids, $user_id, $deleted );

            return $deleted;
        }
    }

    /**
     * Delete all collection items.
     *
     * @param int $collection_id The collection ID.
     *
     * @since 1.0.0
     * @access public
     */
    public function delete_collection_items_by_admin( $collection_id ) {
        global $wpdb;

        $table_name = $this->get_collection_items_table_name();

        do_action( 'stwlite_before_delete_collection_items_by_admin', $collection_id );

        // phpcs:disable
        $wpdb->delete( $table_name, array( 'collection_id' => $collection_id ) );
        // phpcs:enable

        do_action( 'stwlite_after_delete_collection_items_by_admin', $collection_id );
    }

    /**
     * Update the wishlist item position.
     *
     * @param array $lists The lists.
     *
     * @since 1.0.0
     * @access public
     */
    public function update_wishlist_item_position( $lists = array() ) {
        global $wpdb;

        // phpcs:disable

        $table_name = $this->get_collection_items_table_name();

        do_action( 'stwlite_before_update_wishlist_item_position', $lists );

        foreach ( $lists as $key => $list ) {
            $wpdb->update( $table_name, array( 'sort_order' => $key ), array( 'id' => $list['id'] ) );
        }

        // phpcs:enable

        do_action( 'stwlite_after_update_wishlist_item_position', $lists );

        return true;
    }

    /**
     * Generate a unique URL code.
     *
     * @since 1.0.0
     * @access public
     * @return string
     */
    public function generate_url_code() {
        $url_code = wp_generate_password( 12, false, false );

        // Check if the URL code already exists.
        $exists = $this->check_if_url_code_exists( $url_code );

        // If the URL code already exists, generate a new one.
        if ( $exists ) {
            return $this->generate_url_code();
        }

        return $url_code;
    }

    /**
     * Normalize a date param to a MySQL DATETIME string, or null if invalid.
     *
     * Accepts YYYY-MM-DD or YYYY-MM-DD HH:MM:SS. A bare date with $end_of_day = true
     * is extended to 23:59:59 so the upper bound covers the full day.
     *
     * @param mixed $value      Raw param value.
     * @param bool  $end_of_day When true and the value is date-only, pad to 23:59:59.
     *
     * @since 1.0.6
     * @access private
     *
     * @return string|null
     */
    private static function normalize_date_param( $value, $end_of_day = false ) {
        if ( ! is_string( $value ) ) {
            return null;
        }

        $value = trim( $value );
        if ( '' === $value ) {
            return null;
        }

        // Date-only.
        if ( preg_match( '/^(\d{4})-(\d{2})-(\d{2})$/', $value, $matches )
            && checkdate( (int) $matches[2], (int) $matches[3], (int) $matches[1] ) ) {
            return $end_of_day ? "$value 23:59:59" : "$value 00:00:00";
        }

        // Full datetime.
        if ( preg_match( '/^(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2}):(\d{2})$/', $value, $matches )
            && checkdate( (int) $matches[2], (int) $matches[3], (int) $matches[1] )
            && (int) $matches[4] < 24 && (int) $matches[5] < 60 && (int) $matches[6] < 60 ) {
            return $value;
        }

        return null;
    }

    /**
     * Check if the URL code already exists.
     *
     * @param string $url_code The URL code.
     *
     * @since 1.0.0
     * @access public
     * @return bool
     */
    public function check_if_url_code_exists( $url_code ) {
        global $wpdb;

        $table_name = $this->get_table_name();

        // phpcs:disable

        $exists = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table_name} WHERE url_code = %s", $url_code ) );

        // phpcs:enable

        return $exists > 0;
    }
}
