<?php
/**
 * Item Object Eloquent model
 *
 * @version 19/09/16
 * @author Martins Fridenbergs <martins.fridenbergs@mtcmedia.co.uk>
 */

namespace Mtc\Shop;

use Carbon\Carbon;
use DeliveryMethod;
use Mtc\Shop\Item\Size;
use Mtc\Shop\Item\Custom;
use Mtc\Shop\Item\CsvImport;
use Spatie\Sitemap\Tags\Url;
use Mtc\Shop\Assessment\Form;
use Mtc\Core\Keywords\Keyword;
use Mtc\Shop\Item\ItemsDisplayTag;
use Mtc\Shop\Assessment\Assessment;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Query\JoinClause;
use Mtc\Core\Keywords\KeywordCollection;
use Mtc\Shop\ActiveSubstance;
use Mtc\Shop\Item\IncompatibleMedication;
use Spatie\Sitemap\Contracts\Sitemapable;
use Mtc\Plugins\Refunds\Classes\RefundItem;
use MtcPharmacy\Bundles\Classes\BundleType;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Database\Eloquent\Relations\HasOne;
use Illuminate\Database\Eloquent\Relations\HasMany;
use Illuminate\Database\Eloquent\Relations\belongsTo;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;
use MtcPharmacy\Subscriptions\Classes\SubscriptionPreference;
use MtcPharmacy\QualifyingAnswers\Classes\QualifyingAnswersFilter;

/**
 * Item Object Eloquent model.
 * Implements Shop item main functionality
 *
 * @version 19/09/16
 * @author Martins Fridenbergs <martins.fridenbergs@mtcmedia.co.uk>
 *
 * @property Collection $images
 * @property KeywordCollection $keywords
 */
class Item extends Model implements Sitemapable
{
    const string TYPE_DOCTOR = 'doctor';
    const string TYPE_PHARMACY = 'pharmacy';
    const string TYPE_CONSULTATION = 'consultation';
    const string TYPE_PRIVATE_PRESCRIPTION = 'private_prescription';
    const string TYPE_PRESCRIPTION = 'prescription';

    /**
     * @var array The attributes that are mass assignable.
     */
    protected $fillable = [
        'epos_code',
        'name',
        'sub_id',
        'search_name',
        'description',
        'search_description',
        'price',
        'price_exvat',
        'sale_price',
        'sale_price_exvat',
        'sort_price',
        'heavy',
        'hidden',
        'stock',
        'vat_rate',
        'vat_deductable',
        'product_type',
        'id_check_required',
        'can_be_sold_separately',
        'basket_quantity_locked',
        'bundle_page_url',
        'is_groupbuy_container',
        'groupbuy_bundletype_id',
        'seo_title',
        'seo_keywords',
        'seo_description',
        'custom_field_set_id',
        'restriction_period_length',
        'restriction_per_period',
        'restriction_per_order',
        'restriction_limit_once',
        'disable_assessment_forms',
    ];

    protected $appends = [
        'url',
        'in_stock',
        'from_price',
        'recommended',
        'trustpilot_sku',
    ];

    public static $product_types = [
        'doctor' => 'Doctor item',
        'pharmacy' => 'Pharmacy item'
    ];

    public static $vat_codes = [
        'Z' => 0,
        'L' => 5,
        'S' => 20,
    ];

    // QUERY SCOPES
    /**
     * Scope - active()
     * Discards all hidden and deleted items
     *
     * @param \Illuminate\Database\Eloquent\Builder $query Query builder object
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function scopeActive($query)
    {
        return $query->where('hidden', 0)
            ->where('deleted', 0);
    }

    // RELATIONSHIPS
    /**
     * Define the relationship to item images
     * @return HasMany
     */
    public function images(): HasMany
    {
        return $this->hasMany(Item\Image::class, 'item_id');
    }

    /**
     * Define the relationship to item images
     * @return HasOne
     */
    public function defaultImage(): HasOne
    {
        return $this->hasOne(Item\Image::class, 'item_id')
            ->where('default', 1);
    }

    /**
     * Define the relationship to item sizes
     * @return HasMany
     */
    public function sizes(): HasMany
    {
        return $this->hasMany(Item\Size::class, 'item_id');
    }

    /**
     * Define the relationship to item sizes
     * @return BelongsToMany
     */
    public function deliveryMethods(): BelongsToMany
    {
        return $this->belongsToMany(DeliveryMethod::class, 'items_delivery_methods', 'item_id');
    }

    /**
     * Keywords relationship.
     *
     * @return BelongsToMany
     */
    public function keywords(): BelongsToMany
    {
        return $this->belongsToMany(Keyword::class, 'items_keywords', 'item_id');
    }

    /**
     * Categories relationship.
     *
     * @return BelongsToMany
     */
    public function categories(): BelongsToMany
    {
        return $this->belongsToMany(Category::class, 'items_categories', 'item_id', 'cat_id')
            ->orderBy('items_categories.order')
            ->orderBy('items_categories.id');
    }

    /**
     * Brands relationship.
     *
     * @return BelongsToMany
     */
    public function brands(): BelongsToMany
    {
        return $this->belongsToMany(Brand::class, 'items_brands', 'item_id');
    }

    /**
     * Define the relationship to Warning
     * @return BelongsToMany
     */
    public function warnings(): BelongsToMany
    {
        return $this->belongsToMany(Warning::class, 'warning_items');
    }

    /**
     * Define the relationship to Custom fields
     * @return hasOne
     */
    public function custom(): HasOne
    {
        return $this->hasOne(Custom::class);
    }

    /**
     * Define the relationship to Forms
     * @return belongsTo
     */
    public function form(): belongsTo
    {
        return $this->belongsTo(Form::class, 'form_id');
    }

    /**
     * Define the relationship to Assessments
     * @return HasMany
     */
    public function assessments(): HasMany
    {
        return $this->hasMany(Assessment::class);
    }

    /**
     * Define the relationship to Incompatible medications
     * @return HasMany
     */
    public function incompatible_medications(): HasMany
    {
        return $this->hasMany(IncompatibleMedication::class);
    }

    /**
     * Define the relationship to Ingredient
     * @return BelongsToMany
     */
    public function ingredients(): BelongsToMany
    {
        return $this->belongsToMany(Ingredient::class, 'items_ingredients');
    }


    public function groupbuy_bundletype(): BelongsTo
    {
        return $this->belongsTo(BundleType::class, 'groupbuy_bundletype_id');
    }


    public function display_tags(): HasMany
    {
        return $this->hasMany(ItemsDisplayTag::class, 'item_id');
    }


    public function filter_tags(): HasMany
    {
        return $this->hasMany(ItemsDisplayTag::class, 'item_id')->where('group_name', 'f');
    }


    public function active_substances(): BelongsToMany
    {
        return $this->belongsToMany(ActiveSubstance::class, 'item_active_substance');
    }


    public function subscriptionPreference()
    {
        return $this->hasOne(SubscriptionPreference::class, 'item_id');
    }

    public function hasSubcription(){
        return $this->subscriptionPreference()->where('is_allowed', true)->exists();
    }

    public function hasSubscription(){
        return $this->hasSubcription();
    }


    public static function getMatchingGroupBuyItems(Collection $items_to_match)
    {
        $groupbuy_containers = [];

        $containers = self::query()
            ->where('is_groupbuy_container', true)
            ->get()
        ;

        $containers = $containers->filter(function ($i) {
            return ($i->groupbuy_bundletype && $i->groupbuy_bundletype->items->count());
        });

        foreach ($containers as $container) {
            $container->shop_items = collect([]);
            foreach ($container->groupbuy_bundletype->items as $bundletype_item) {
                $container->shop_items->push($bundletype_item->shop_item);
            }
        }

        foreach ($items_to_match as $item_to_match) {
            foreach ($containers as $container) {
                $container->shop_items = $container->shop_items->filter(function ($i) use ($item_to_match) {
                    return ($i->id != $item_to_match->id);
                });
            }
        }

        foreach ($containers as $container) {
            if (!$container->shop_items->count()) {
                $groupbuy_containers[] = $container;
            }
        }

        return $groupbuy_containers;
    }


    public static function getWarnings($item_id)
    {
        if (empty($item_id)) {
            return [];
        }
        return (new \Mtc\Shop\Item)
            ->find($item_id)
            ->warnings()
            ->get()
            ->each(function ($warning) {
                $warning->type = \Mtc\Shop\Warning::$warning_types[$warning->warning_type];
            })
            ->toArray();
    }


    /**
     * @param bool $withCreate
     * @return array
     */
    public static function importProducts(bool $withCreate = false): array
    {
        $productImport = new CsvImport($withCreate);
        return $productImport->runImport();
    }


    public function importImageFromPath($path, $image_name_wo_extension, bool $add_new = true)
    {
        global $image_folders;

        // We don't have the knowledge of what extension is the image
        $image_name = $image_name_wo_extension . '.jpg';
        $full_path = SITE_PATH . '/' . $path . '/' . $image_name;
        if (!file_exists($full_path)) {
            $image_name = $image_name_wo_extension . '.png';
            $full_path = SITE_PATH . '/' . $path . '/' . $image_name;
            if (!file_exists($full_path)) {
                return false;
            }
        }

        // Do not replace image, if the item has few already
        if (!$add_new && $this->images()->count() > 1) {
            return false;
        }

        // This image already exists.
        if ($this->images()->where('name', $image_name)->count()) {
            return true;
        }

        foreach ($image_folders['product_folders'] as $folder_name => $resize_params) {
            copy_image($full_path, $image_name, $resize_params);
        }

        // Remove old images from server
        if (!$add_new) {
            foreach ($this->images as $old_image) {
                foreach ($image_folders as $folder_name => $resize_params) {
                    $old_image_path = SITE_PATH . '/' . $resize_params['path'] . '/' . $old_image->name;
                    if (file_exists($old_image_path)) {
                        unlink($old_image_path);
                    }
                }
            }
            $this->images()->delete();
        }
        $this->images()->create([
            'default' => '1',
            'name' => $image_name,
        ]);
        return true;
    }

    public static function exportProducts($params)
    {
        $rows = [];

        $items = self::getExportItemsFiltered($params);
        $items = $items->get();

        // For each order get it's items
        foreach ($items as $item) {
            $categories = $item->categories()->orderBy('sub_id')->get();
            $brand = $item->brands()->first();

            $base_row = [
                'Product ID' => $item->id,                                              //'Product ID',
                'Variation ID' => '',                                                   //'Variation ID',
                'Pip Code' => '',                                                       //'Pip Code',
                'Stock' => '',                                                          //'Stock'
                'Published' => $item->isHidden() ? 'no' : 'yes',                        //'Published',
                'Product Name' => $item->name,                                          //'Full Description',
                'Disable Assessment Forms' => $item->disable_assessment_forms,
                'Product Type' => $item->product_type,                                  //'Product Type'
                'Product Description' => $item->description,                            //'Overview',
                'Retail Price' => '',                                                   //'Retail Price',
                'Sale Price' => '',                                                     //'Sale Price',

                'Strength' => '',                                                       //'Strength',
                'Variation Name' => '',                                                 //'Size',
                'No of Labels' => '',                                                   //'No of Labels',
                'Pack Size' => '',                                                      //'Pack Size',
                'Label title' => '',                                                    //'Label title',
                'Quantity' => '',                                                       //'Quantity',

                'Main Category' => empty($categories[0]) ? '' : $categories[0]->name,   //'Main Class',
                'Category 1' => empty($categories[1]) ? '' : $categories[1]->name,      //'Subclass 1',
                'Category 2' => empty($categories[2]) ? '' : $categories[2]->name,      //'Subclass 2',
                'Category 3' => empty($categories[3]) ? '' : $categories[3]->name,      //'Subclass 3',
                'Brand name' => empty($brand) ? '' : $brand->name,                      //'Brand name',

                '[Meta] title' => $item->seo_title,
                '[Meta] keywords' => $item->seo_keywords,
                '[Meta] description' => $item->seo_description,

                'Warnings' => Warning::exportWarnings($item->id),                       //'Warnings',
            ];

            $cf_set = CustomFieldSet::getDefault();
            if ($cf_set) {
                foreach ($cf_set->fields as $cf_holder) {
                    $base_row["[Custom] {$cf_holder->field->name}"] = $item->custom->{$cf_holder->field->database_field};
                }
            }

            // Export SEO Schema custom fields
            $schema_fields = CustomField::query()->where('database_field', 'LIKE', 'schema_%')->get();
            foreach ($schema_fields as $schema_field) {
                $base_row["[SEO] {$schema_field->name}"] = $item->custom->{$schema_field->database_field} ?? '';
            }


            if ($item->sizes->isEmpty()) {
                // Product without sizes
                $row = $base_row;
                $row['Pip Code'] = $item->epos_code;
                $row['Stock'] = $item->stock;
                $row['Retail Price'] = $item->price;
                $row['Sale Price'] = $item->sale_price;
                $rows[] = $row;
            } else {
                //p($item->sizes);
                foreach ($item->sizes as $size) {
                    // Foreach size
                    $row = $base_row;
                    $row['Variation ID'] = $size->id;
                    $row['Pip Code'] = $size->PLU;
                    $row['Stock'] = $size->stock;
                    $row['Strength'] = $size->strength;
                    $row['Quantity'] = $size->quantity;
                    $row['Retail Price'] = $size->price;
                    $row['Sale Price'] = $size->sale_price;

                    $row['Variation Name'] = $size->size;
                    $row['No of Labels'] = $size->label_count;
                    $row['Pack Size'] = $size->pack_size;

                    if ($cf_set) {
                        foreach ($cf_set->fields as $cf_holder) {
                            $row["[Custom] {$cf_holder->field->name}"] = $size->custom->{$cf_holder->field->database_field};
                        }
                    }

                    $rows[] = $row;
                    // Export first variation only
                    if (!empty($params['first_variation'])) {
                        break;
                    }
                }
            }
        }

        $csv_rows = [];
        if ($rows) {
            $header_row = array_keys($rows[0]);
            $csv_rows[] = $header_row;
            $csv_rows = array_merge($csv_rows, $rows);
        }

        self::downloadCSV($csv_rows, 'products_export_' . date('Y-m-d H:i:s') . '.csv');
    }


    /**
     * Exports product CSV for Google Merchant Center Feed
     *
     * @param array $params
     */
    public static function exportGoogleFeedProducts(array $params)
    {
        set_time_limit(600);
        ini_set('memory_limit', '1G');

        global $image_folders;

        $rows = [];

        $columns = [
            'id',
            'title',
            'description',
            'link',
            'condition',
            'price',
            'sale_price',
            'image link',
            'availability',
            'brand',
            'gtin',
            'mpn',
            'google product category',
            'product_type',
            'shipping',
            'cost price',
            'total orders',
            'total number sold',
            'total sales',
        ];

        $rows[] = $columns;

        $items = self::getExportItemsFiltered($params);
        $items = $items
            ->where('hidden', '=', '0')
            ->with('brands')
            //->limit(10)
            ->get()
        ;

        $site_url = SITE_URL;

        // For each order get it's items
        foreach ($items as $item) {
            $brand = $item->brands()->first();
            $item_pod = new \Item();
            $item_pod->Get_Item($item->id);
            $item_sales = Order\Item::query()
                ->selectRaw('
                    order_items.*,
                    sum(order_items.price_paid * order_items.quantity) AS amount_paid,
                    sum(order_items.quantity) AS num_sold
                ')
                ->where('item_id', '=', $item->id)
                ->join('order', function ($join) {
                    $join->on('order.id', '=', 'order_items.order_id')
                        ->where('order.paid', '=', '1')
                        ->where('order.status', '!=', '6');
                });
            $item_refunds = RefundItem::query()
                ->where('item_id', '=', $item->id);

            $total_orders = (clone $item_sales)->groupBy('order.id')->get()->count();
            $num_sold = $item_sales->first()->num_sold;
            $amount_paid = $item_sales->first()->amount_paid;

            $base_row = [
                'id' => $item->epos_code,
                'title' => $item->name,
                'description' => strip_tags($item->description),
                'link' => $site_url . $item_pod->url,
                'condition' => 'New',
                'price' => $item->price,
                'sale_price' => $item->sale_price > 0 ? $item->sale_price : $item->price,
                'image link' => $site_url . '/' . ltrim($item->getImagePath('default', 'large'), '/'),
                'availability' => $item->stock > 0 ? 'In Stock' : 'Out Of Stock',
                'brand' => empty($brand) ? '' : $brand->name,
                'gtin' => (string) $item->custom->barcode,
                'mpn' => '',
                'google product category' => 'Health & Beauty > Health Care',
                'product_type' => '',
                'shipping' => '3.19',
                'cost price' => $item->custom->cog_cost,
                'total orders' => $total_orders,
                'total number sold' => $num_sold,
                'total sales' => $amount_paid - $item_refunds->get()->sum('amount_refunded'),
            ];

            if (!$item->sizes->isEmpty()) {
                foreach ($item->sizes as $size) {

                    $size_sales = Order\Item::query()
                        ->selectRaw('
                            order_items.*,
                            sum(order_items.price_paid * order_items.quantity) AS amount_paid,
                            sum(order_items.quantity) AS num_sold,
                            (SELECT sum(amount_refunded) FROM order_refund_items WHERE order_items.id = order_refund_items.order_item_id) as total_refunded
                        ')
                        ->where('sizeid', '=', $size->id)
                        ->join('order', function ($join) {
                            $join->on('order.id', '=', 'order_items.order_id')
                                ->where('order.paid', '=', '1')
                                ->where('order.status', '!=', '6');
                        });

                    $total_orders = (clone $size_sales)->groupBy('order.id')->get()->count();
                    $num_sold = $size_sales->first()->num_sold;
                    $amount_paid = $size_sales->first()->amount_paid;
                    $total_refunded = $size_sales->first()->total_refunded;

                    // Foreach size
                    $row = $base_row;
                    $row['id'] .= ($size->PLU ? "/{$size->PLU}" : '');
                    $row['title'] .= ' ' . ($item->sizes->count() > 1 ? $size->size : '');
                    $row['description'] .= ' ' . ($item->sizes->count() > 1 ? $size->size : '');
                    $row['price'] = $size->price;
                    $row['sale_price'] = $size->sale_price > 0 ? $size->sale_price : $size->price;
                    $row['availability'] = $size->stock > 0 ? 'In Stock' : 'Out Of Stock';
                    $row['gtin'] = (string) $size->custom->barcode;
                    $row['cost price'] = $size->custom->cog_cost;
                    $row['total orders'] = $total_orders;
                    $row['total number sold'] = $num_sold;
                    $row['total sales'] = $amount_paid - $total_refunded;

                    $rows[] = $row;
                }
            } else {
                $rows[] = $base_row;
            }
        }

        self::downloadCSV($rows, 'google_feed_export_' . date('Y-m-d H:i:s') . '.csv');
    }

    /**
     * Forces CSV download
     *
     * @param array $rows
     * @param string $filename
     */
    private static function downloadCSV(array $rows, string $filename)
    {
        $download_file = fopen($filename, 'w');
        foreach ($rows as $row) {
            fputcsv($download_file, $row, ',');
        }
        fclose($download_file);

        \Util::force_download($filename, file_get_contents($filename));
        unlink($filename);
        exit;
    }

    /**
     * Finds item assessment form first by item, then by category
     *
     * @return Form|null
     */
    public function findAssessmentForm()
    {
        $form_id = null;

        if (!$this->disable_assessment_forms) {

            $main_category = $this->getMainCategory();
            //$main_category = $this->getLastCategory();

            if ($main_category && $main_category->form_id) {
                $form_id = $main_category->form_id;
            }



        }

        return (new Form())->find($form_id);
    }


    public function findAllAssessmentForms(): array
    {
        $temp_forms = [];

        if ($this->form_id) {
            $temp_forms[] = (new Form())->find($this->form_id);
        }

        $main_category = $this->getMainCategory();
        if ($main_category && $main_category->form_id) {
            $temp_forms[] = (new Form())->find($main_category->form_id);
        }

        return $temp_forms;
    }

    /**
     * Apply filter to any product export so that it's not duplicated
     *
     * @param array $params
     * @return Builder
     */
    private static function getExportItemsFiltered(array $params): Builder
    {
        $items = self::query()
            ->select([
                'items.*',
            ])
            ->where('items.deleted', '=', '0')
            ->where('items.id', '>', '0')
            ->orderBy('items.id');

        if (!empty($params['name'])) {
            $items = $items->selectRaw(
                'MATCH(items.name) AGAINST(? IN BOOLEAN MODE ) AS match_name',
                [
                    str_replace('*', '', $params['name'])
                ]
            );
            $items = $items->whereRaw(
                'MATCH(items.name) AGAINST(? IN BOOLEAN MODE )',
                [
                    $params['name']
                ]
            );
        }

        if (!empty($params['epos_id'])) {
            $items = $items->selectRaw(
                'MATCH(items.epos_code) AGAINST (? IN BOOLEAN MODE ) as match_epos_code',
                [
                    str_replace('*', '', $params['epos_id'])
                ]
            );
            $items = $items->whereRaw(
                'MATCH(items.epos_code) AGAINST(? IN BOOLEAN MODE )',
                [
                    $params['epos_id']
                ]
            );
        }


        if ($params['lowstock'] != '') {
            $items = $items->leftJoin('items_sizes', 'items.id', '=', 'items_sizes.item_id')
                ->where(function ($where) {
                    $where->where(function ($where) {
                        $where->whereNotNull('items_sizes.id')
                            ->where('items_sizes.stock', '<', '2');
                    })->orWhere(function ($where) {
                        $where->whereNull('items_sizes.id')
                            ->where('items.stock', '<', '2');
                    });
                });
        }


        if (is_numeric($params['catid']) && $params['catid'] > 0) {
            $items->whereExists(function ($query) use ($params) {
                $query->select('*')
                    ->from('items_categories')
                    ->where('cat_id', '=', $params['catid'])
                    ->whereRaw('items.id = items_categories.item_id');
            });
        }

        if (!empty($params['product_type'])) {
            $items = $items->where('items.product_type', $params['product_type']);
        }

        if (!empty($params['published'])) {
            $items = $items->where('items.hidden', ($params['published'] === 'yes' ? '0' : '1'));
        }

        return $items;
    }


    public function isInStock(): bool
    {

        $is_in_stock = false;

        if ($this->sizes->count()) {

            $stock_array = $this->sizes->pluck('stock')->toArray();
            if (array_sum($stock_array)) {
                $is_in_stock = true;
            }

        } else {
            if ($this->stock > 0) {
                $is_in_stock = true;
            }
        }

        return $is_in_stock;
    }


    public function getFromPrice()
    {
        $min_price = $this->price;

        $sizes_prices = $this->sizes->pluck('price');
        if ($sizes_prices->count()) {
            $min_price = $sizes_prices->sort()->first();
        }

        return $min_price;
    }


    public function getAdminUrl($url_to_main_tab = true)
    {
        $url = "/shop/admin/products/item.php?id={$this->id}";

        if (!$url_to_main_tab) {
            $url = "?id={$this->id}";
        }

        return $url;
    }


    public function legacy()
    {
        return new \Item($this->id);
    }


    public function hasCompletedAssessment()
    {
        $has_completed = false;

        $completed_assessment = Assessment::getCompletedForItem($this);

        if ($completed_assessment) {
            $has_completed = true;
        }

        if ($this->product_type == self::TYPE_DOCTOR) {

            //var_dump($form->id); var_dump($completed_assessment->form->id); die;

            $qa_filter = QualifyingAnswersFilter::init($completed_assessment);
            if (in_array($this->id, $qa_filter->getRejectedItemIDs())) {
                $has_completed = false;
            }

        }

        return $has_completed;
    }


    public function getMainCategoryUrl()
    {
        $url = SITE_URL;

        $main_category = $this->getMainCategory();

        if ($main_category) {
            $url = $main_category->getLink();
        }

        return $url;
    }


    public function getMainCategory()
    {
        if ($this->relationLoaded('categories')) {
            return $this->getRelation('categories')->first();
        }

        return $this->categories()->first();
    }

    public function getLastCategory()
    {

        return $this->categories->last();

    }

    /**
     * Get all category IDs for this item.
     *
     * @return array
     */
    public function getAllCategoryIDs(): array
    {
        if ($this->relationLoaded('categories')) {
            return $this->getRelation('categories')->pluck('id')->toArray();
        }

        return $this->categories()->pluck('cat_id')->toArray();
    }


    public function assessmentIsComplete()
    {

        $complete = false;

        $category = $this->getMainCategory();
        if ($this->product_type == self::TYPE_DOCTOR) {
            //If doctor then only check the main category
            $assessment = new Assessment;
            $assessment_data = $assessment::query()
                ->where('form_id', $category->form_id)
                ->orderBy('created_at', 'desc')
                ->first();

            if (!empty($assessment_data)) {
                if ($assessment_data->is_completed == '1') {
                    $complete = true;
                }
            }

        } elseif ($this->product_type == self::TYPE_PHARMACY) {

            //If p med then check all category forms to see if they have been complete on any of them
            foreach ($this->categories as $cat) {

                $assessment = new Assessment;
                $assessment_data = $assessment::query()
                    ->where('form_id', $cat->form_id)
                    ->orderBy('created_at', 'desc')
                    ->first();

                if (!empty($assessment_data)) {
                    if ($assessment_data->is_completed == '1') {
                        $complete = true;
                    }
                }

            }

        }


        return $complete;
    }


    public function getProductCardData($is_category_page = false, $alternative_label = false, $assessment_display = false): array
    {

        $form = $this->findAssessmentForm();

        $add_to_basket_url = route('shop-addtobasket', [
            'id' => $this->id
        ]);


        $data = [
            'url' => $add_to_basket_url,
            'label' => "Add to basket",
            'browse_url' => $add_to_basket_url,
            'browse_label' => "Add to basket",
            'add_direct_to_basket' => true,
            'basket_url' => $add_to_basket_url,
            'add_to_cart_default_label' => 'Add to basket',
            'treatment_select_label' => 'Choose treatment',
            'pharmacy_item' => false,
            'assessment_complete' => $this->assessmentIsComplete()
        ];


        //Make sure all add to basket functionality is enabled for all products once the assessment is complete
        if ($assessment_display && $this->assessmentIsComplete()) {
            $form = false;
        }

        if ($form) {

            if ($this->product_type == self::TYPE_DOCTOR) {

                $main_category = $this->getLastCategory();

                if (!$is_category_page) {
                    $data['url'] = $main_category->url ?? '/';
                } else {
                    $data['url'] = route('assessment-form', [
                        'form_id' => $form->id,
                        'c' => $main_category->id
                    ]);
                }

            } elseif ($this->product_type == self::TYPE_PHARMACY) {

                $data['browse_label'] = 'Get started';
                $data['pharmacy_item'] = true;

                $params = [
                    'form_id' => $form->id,
                    'p' => $this->id,
                    's' => 0,
                    'q' => 1
                ];

                $data['url'] = route('assessment-form', $params);
                $data['browse_url'] = route('assessment-form', $params);

            } else {
                $data['url'] = route('assessment-form', [
                    'form_id' => $form->id,
                    'p' => $this->id
                ]);
            }

            if ($alternative_label) {
                $data['label'] = $alternative_label;
            } else {
                $data['label'] = "Get Started";
            }


            if ($this->product_type == self::TYPE_DOCTOR) {
                $data['browse_url'] = $this->url;
                $data['add_direct_to_basket'] = false;
                $data['browse_label'] = 'View product';
            } elseif ($this->product_type == self::TYPE_PHARMACY) {

                $data['add_direct_to_basket'] = true;

            } else {
                $data['add_direct_to_basket'] = true;
            }


        }

        return $data;
    }


    public function getUrlAttribute(): string
    {
        if (empty($this->id) || empty($this->name)) {
            return '';
        }

        $url = '/item/' . $this->id . '/' . clean_value(str_replace(' ', '-', trim($this->name))) . '.html';

        if (!empty($this->custom) && !empty($this->custom->slug)) {
            $url = '/medications/' . $this->custom->slug;
        }

        return $url;
    }


    public function getImageDefaultAttribute()
    {
        return $this->legacy()->image_default;
    }


    public function getInStockAttribute()
    {
        return (bool) $this->isInStock();
    }


    public function getFromPriceAttribute()
    {
        return (float) $this->getFromPrice();
    }


    public function getImageAttribute()
    {
        return $this->getImagePath('small');
    }


    public function getImagePath($type, $image_size = 'large')
    {
        global $image_folders;

        if (empty($image_folders['product_folders'][$image_size])) {
            return null;
        }

        $image_path = null;

        foreach ($this->images as $item_image) {
            if ($item_image->$type) {
                $image_path = "/{$image_folders['product_folders'][$image_size]['path']}/{$item_image->name}";

                if (!file_exists(SITE_PATH . '/' . $image_path)) {
                    $image_path = null;
                }
            }
        }

        if (!$image_path && $type != 'default') {
            $image_path = $this->getImagePath('default', $image_size);
        }

        return $image_path;
    }


    public function getRedirectURL(string $reason = '-')
    {
        return $this->legacy()->getRedirectURL($reason);
    }


    public function replicateWithRelations()
    {
        $clone = $this->replicate();
        $clone->save();

        if ($this->custom) {
            $custom_clone = $this->custom->replicate();
            $clone->custom()->save($custom_clone);
        }

        foreach ($this->sizes as $size) {
            $size_clone = $size->replicate();
            $clone->sizes()->save($size_clone);

            if ($size->custom) {
                $custom_size_clone = $size->custom->replicate();
                $size_clone->custom()->save($custom_size_clone);
            }
        }

        $ids_to_sync_with = $this->categories->pluck('id');
        $clone->categories()->sync($ids_to_sync_with);

        $ids_to_sync_with = $this->brands->pluck('id');
        $clone->brands()->sync($ids_to_sync_with);

        $ids_to_sync_with = $this->keywords->pluck('id');
        $clone->keywords()->sync($ids_to_sync_with);

        // seo
        // complimented
        // interactions

        return $clone;
    }


    public function updateSlug(string $slug)
    {
        $this->custom->slug = $slug;

        return $this->custom->save();
    }


    public function getRecommendedAttribute()
    {
        $is_recommended = false;

        /*
        $found_featured = FeaturedProducts::query()
            ->where('item_id', $this->id)
            ->first()
        ;
        */
        if (empty($this->custom)) {
            return false;
        }

        if ($this->custom->is_recommended == 'Yes') {
            $is_recommended = true;
        }

        return $is_recommended;
    }


    public function isCompletedAssessementRequired()
    {
        $is_required = false;

        $assessment_form = $this->findAssessmentForm();
        if ($assessment_form) {
            $is_required = true;
        }

        return $is_required;
    }


    public function getFirstTrustpilotSku(): string
    {
        $sku = '';

        $all_skus = $this->getTrustpilotSkus();
        if ($all_skus) {
            $sku = $all_skus[0];
        }

        return $sku;
    }

    public function getTrustpilotSkus(): array
    {
        $skus = [];

        if ($this->epos_code) {
            $skus[] = $this->epos_code;
        }

        if ($this->vs_parent_id) {
            $skus[] = $this->vs_parent_id;
        }

        foreach ($this->sizes as $size) {
            if ($size->vs_child_id) {
                $skus[] = $size->vs_child_id;
            }

            if ($size->PLU) {
                $skus[] = $size->PLU;
            }
        }

        return array_unique($skus);
    }


    public function getTrustpilotSkuAttribute(): string
    {
        $skus = $this->getTrustpilotSkus();

        return implode(',', $skus);
    }


    // To avoid conflicts with Eloquent's Model::$hidden.
    public function isHidden()
    {
        return $this->getAttribute('hidden');
    }

    public function toSitemapTag(): Url | string | array
    {
        return Url::create(config('app.url') . $this->url)
            ->setLastModificationDate(Carbon::create($this->updated))
            ->setChangeFrequency(Url::CHANGE_FREQUENCY_MONTHLY)
            ->setPriority(0.6)
        ;
    }

    /**
     * Search items by query
     *
     * @param $query
     * @return array|Builder[]|Collection|\Illuminate\Support\HigherOrderWhenProxy[]
     */
    public static function searchItems(string $query)
    {
        $q = trim($query);
        if ($q === '') {
            return collect();
        }

        // Split: first token = item query, second (optional) = size filter
        $parts     = preg_split('/\s+/', $q, -1, PREG_SPLIT_NO_EMPTY);
        $itemTerm  = $parts[0] ?? '';
        $sizeTerm  = $parts[1] ?? null;

        $isId      = ctype_digit($itemTerm);
        $itemLike  = '%' . self::escapeLike($itemTerm) . '%';
        $sizeLike  = $sizeTerm !== null ? '%' . self::escapeLike($sizeTerm) . '%' : null;

        return self::query()
            ->select([
                'items.id',
                'items.name',
                'items.description',
                'items.price',
                'items.stock',
                'items.hidden',
                'items.deleted',
                'items.epos_code',
                'items_sizes.id as size_id',
                'items_sizes.size',
                'items_sizes.price as size_price',
                'items_sizes.stock as size_stock',
                'items_sizes.PLU',
            ])
            ->leftJoin('items_sizes', function (JoinClause $join) {
                $join->on('items_sizes.item_id', '=', 'items.id')
                    ->where('items_sizes.stock', '>', 0);
            })
            ->where('items.deleted', 0)
            ->where('items.hidden', 0)
            ->where(function (Builder $w) use ($isId, $itemTerm, $itemLike) {
                if ($isId) {
                    $w->orWhere('items.id', (int) $itemTerm);              // bound param
                }
                $w->orWhere('items.name', 'LIKE', $itemLike)               // bound param
                ->orWhere('items.epos_code', 'LIKE', $itemLike)
                    ->orWhere('items.description', 'LIKE', $itemLike);
            })
            ->when($sizeLike !== null, function (Builder $q) use ($sizeLike) {
                // Only include sizes matching the second token (same as original)
                $q->where('items_sizes.size', 'LIKE', $sizeLike);           // bound param
            })
            ->orderBy('items.id')
            ->orderBy('items_sizes.order')
            ->limit(30)
            ->get();
    }

    /**
     * Escape characters significant to SQL LIKE. Parameters remain bound safely.
     */
    protected static function escapeLike(string $value): string
    {
        // escape backslash first, then % and _
        return addcslashes($value, '\\%_');
    }

    /**
     * Updates sort price based on size prices. If there are no sizes, defaults to product price
     * Takes lowest of sale price / regular price
     *
     * @param $id
     * @return void
     */
    public static function updateSortPrice($id): void
    {
        if (!$item = self::query()->with('sizes')->find($id)) {
            return;
        }
        $sizes = $item->sizes;
        if ($sizes->count() > 0) {
            $minSalePrice = $sizes->where('sale_price', '>', 0)->min('sale_price');
            if ($minSalePrice > 0) {
                $item->sort_price = min($minSalePrice, $sizes->min('price'));
            } else {
                $item->sort_price = $sizes->min('price');
            }
        } else {
            $item->sort_price = $item->price;
            if ($item->sale_price > 0 && $item->sale_price < $item->price) {
                $item->sort_price = $item->sale_price;
            }
        }
        $item->save();
    }


    /**
     * Resolves price per pill for each variation
     *
     * @return void
     */
    public function resolvePricePerPill(): void
    {
        $this->sizes->map(function (Size $size) {
            $size->price_per_pill = null;
            $quantity = (int) preg_replace('/\D/', '', $size->quantity);
            if (
                empty($quantity) ||
                $quantity === 1 ||
                strlen($quantity) !== strlen($size->quantity)
            ) {
                return $size;
            }

            $price = $size->sale_price ?: $size->price;

            $size->price_per_pill = number_format($price / $quantity, 2);
            return $size;
        });
    }



    /**
     * Adds the discounted pricing within subscriptionPreference and formats the values for JS
     *
     * @return void
     */
    public function mapSizePricesToSubscriptions()
    {
        if ($this->sizes->count() == 0) {
            $newSize = new Size();
            $newSize->id = 0;
            $newSize->item_id = $this->id;
            $newSize->size = 'One Size';
            $newSize->price = $this->price;
            $newSize->price_exvat = $this->price_exvat;
            $newSize->sale_price = $this->sale_price;
            $newSize->sale_price_exvat = $this->sale_price_exvat;
            $newSize->stock = $this->stock;

            $subscription_preference = SubscriptionPreference::query()->where('item_id', $this->id)->where('item_size_id', null)->first();
            if ($subscription_preference) {
                $newSize->subscriptionPreference = $subscription_preference;
            }

            $this->sizes->push($newSize);
        }
        $this->sizes = $this->sizes->map(function ($size) {

            $subscription_preference = $size->subscriptionPreference;

            if (!empty($subscription_preference)) {

                $is_allowed = $subscription_preference->is_allowed == '1';

                //Format for JS
                $subscription_preference->discount_percentage = (int) $subscription_preference->discount_percentage;
                $subscription_preference->can_be_paused = $subscription_preference->can_be_paused == '1';
                $subscription_preference->is_required = $subscription_preference->is_required == '1';
                $subscription_preference->item_id = (int) $subscription_preference->item_id;
                $subscription_preference->item_size_id = (int) $subscription_preference->item_size_id;
                $subscription_preference->max_number_of_issues = (int) $subscription_preference->max_number_of_issues;
                $subscription_preference->interval_multiplier = (int) $subscription_preference->interval_multiplier;


                //Calculate Discount Prices
                $discount_decimal = $subscription_preference->discount_percentage / 100;

                // Subscriptions are allowed if interval_multiplier is set
                // Discount is optional - if no discount, use original prices
                if ($subscription_preference->interval_multiplier > 0) {

                    if ($discount_decimal > 0) {
                        // Determine base price to apply discount to
                        // If there's a sale price, use that as the base, otherwise use regular price
                        $base_price = ($size->sale_price > 0 && $size->sale_price < $size->price)
                            ? $size->sale_price
                            : $size->price;

                        $base_price_exvat = ($size->sale_price_exvat > 0 && $size->sale_price_exvat < $size->price_exvat)
                            ? $size->sale_price_exvat
                            : $size->price_exvat;

                        // Apply subscription discount to the base price (which may already be a sale price)
                        // Use round() instead of number_format() to match basket calculation and ensure consistency
                        $sub_price = $base_price * (1 - $discount_decimal);
                        $subscription_preference->price = round($sub_price, 2);

                        $sub_price_exvat = $base_price_exvat * (1 - $discount_decimal);
                        $subscription_preference->price_exvat = round($sub_price_exvat, 2);

                        if ($size->price_per_pill !== null) {
                            $sub_price_per_pill = $size->price_per_pill * (1 - $discount_decimal);
                            $subscription_preference->price_per_pill = round($sub_price_per_pill, 2);
                        }

                        // Set sale_price to the base price (before subscription discount) for strikethrough display
                        // This shows what the price would be without the subscription discount
                        $subscription_preference->sale_price = (float) $base_price;
                        $subscription_preference->sale_price_ex_vat = (float) $base_price_exvat;
                    } else {
                        // No discount - use sale price if available, otherwise regular price
                        if ($size->sale_price > 0 && $size->sale_price < $size->price) {
                            $subscription_preference->price = (float) $size->sale_price;
                            $subscription_preference->price_exvat = (float) $size->sale_price_exvat;
                            // Show the original price as "was" price
                            $subscription_preference->sale_price = (float) $size->price;
                            $subscription_preference->sale_price_ex_vat = (float) $size->price_exvat;
                        } else {
                            $subscription_preference->price = (float) $size->price;
                            $subscription_preference->price_exvat = (float) $size->price_exvat;
                            $subscription_preference->sale_price = 0;
                            $subscription_preference->sale_price_ex_vat = 0;
                        }
                        $subscription_preference->price_per_pill = (float) $size->price_per_pill;
                    }

                } else {
                    // No interval set - subscription not properly configured
                    $is_allowed = false;
                }

                $subscription_preference->is_allowed = $is_allowed;

                $size->subscription_preference = $subscription_preference;

            }
            return $size;

        });

    }
}
