<?php
namespace Mtc\Core\Import;

use Carbon\Carbon;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;
use Mtc\Core\Images\ImageUploader;
use Mtc\Modules\Members\Models\Member;
use Mtc\Shop\Assessment\Assessment;
use Mtc\Shop\Assessment\Form;
use Mtc\Shop\Assessment\Question;
use Mtc\Shop\Category;
use Mtc\Shop\Item;
use Mtc\Shop\Order;

class Magento
{
    protected static $assessment_question_types = [
        0 => 'text',
        1 => 'yes_no',
        2 => 'variations',
        3 => 'variations',
        4 => 'yes_no',
        5 => 'date_of_birth',
        6 => 'variations',
        7 => 'image',
        8 => 'yes_no',
        9 => 'height_weight', // TODO
        10 => 'weight_assessment', // TODO
    ];

    protected static $order_status_map = [
        'approved_med'      => Order::STATUS_PROCESSING, // 2
        'awaiting_info'     => Order::STATUS_NO_RESPONSE, // 15
        'awaiting_otc'      => Order::STATUS_WAITING_FOR_RESPONSE, // 8
        'awating_info_otc'  => Order::STATUS_WAITING_FOR_RESPONSE, // 8
        'cancelled'         => Order::STATUS_CANCELLED, // 9
        'canceled'          => Order::STATUS_CANCELLED, // 9
        'closed'            => Order::STATUS_CANCELLED, // 4
        'complete'          => Order::STATUS_SHIPPED, // 4
        'disapprove_med'    => Order::STATUS_PROCESSING, // 3
        'dissaprove_med'    => Order::STATUS_PROCESSING, // 3
        'doctor'            => Order::STATUS_AWAITING_PRESCRIPTION_STATUS, // 12
        'pending'           => Order::STATUS_ORDER_RECEIVED, // 0
        'processing'        => Order::STATUS_PROCESSING, // 1

    ];

    //('approved_med', 1),
    //('awaiting_info', 552),
    //('awating_info_otc', 11),
    //('canceled', 1),
    //('closed', 3562),
    //('complete', 25284),
    //('dissaprove_med', 2),
    //('pending', 13),
    //('processing', 2);


    public function __construct(Command $command)
    {
        $this->command = $command;
    }

    /**
     * Import customers
     *
     */
    public function customers($first_id = null, $last_id = null)
    {
        $first_id = $first_id ?? 1;
        $last_id = $last_id ?? $first_id;

        $current_members_count = DB::connection()
            ->table('members')
            ->count();

        $customer_count = DB::connection('import')
            ->table('customer_entity')
            ->count();

        echo "Members count before: $current_members_count.\n";
        echo "Customers count to import: $customer_count.\n\n";

        DB::connection('import')
            ->table('customer_entity')
            ->where('entity_id', '>=', $first_id)
            ->where('entity_id', '<=', $last_id)
            ->orderBy('entity_id')
            ->chunk(100, function ($chunk) {
                $chunk->each(function ($customer) {
                    $date_of_birth_column = $this->fetchDB('customer_entity_datetime', [
                        'entity_id' => $customer->entity_id,
                        'attribute_id' => 11,
                    ]);

                    $gender = $this->fetchDB('customer_entity_int', [
                        'entity_id' => $customer->entity_id,
                        'attribute_id' => 18,
                    ]);

                    $gender_map = [
                        1 => 'Male',
                        2 => 'Female'
                    ];

                    $date_of_birth = null;
                    if ($date_of_birth_column) {
                        try {
                            $date_of_birth = Carbon::createFromFormat('Y-m-d H:i:s', $date_of_birth_column->value);
                        } catch (\Exception $exception) {
                            $date_of_birth = null;
                        }
                    }

                    //if (config('app.env') !== 'production') {
                    //    $customer->email = 'dev.' . $customer->email;
                    //}

                    echo("Processing [{$customer->email}] ... ");

                    $is_new_email = null;
                    $member = Member::query()->where('email', '=', $customer->email)->first();
                    if ($member) {
                        echo " FOUND ";
                        $is_new_email = false;

                    } else {
                        $member = new Member();
                        $member->fill([
                            'email' => $customer->email,
                            'dob_date' => $date_of_birth ? $date_of_birth->format('d') : null,
                            'dob_month' => $date_of_birth ? $date_of_birth->format('m') : null,
                            'dob_year' => $date_of_birth ? $date_of_birth->format('Y') : null,
                            'gender' =>  $gender_map[optional($gender)->value] ?? '',
                        ]);

                        echo " NEW ";
                        $is_new_email = true;
                    }

                    try {
                        $member->magento_entity_id = $customer->entity_id;
                        $member->save();
                        echo " SAVED -- mtc #{$member->id}\n";
                    } catch (\Exception $e) {
                        echo $e->getMessage() . "\n";
                        return false;
                    }

                    if (! $is_new_email) {
                        return true; // continue
                    }

                    $this->fetchDB('customer_address_entity', [
                        'parent_id' => $customer->entity_id
                    ], false)
                        ->each(function ($address_entity) use ($member) {

                            $country = $this->fetchDB('customer_address_entity_varchar', [
                                'entity_id' => $address_entity->entity_id,
                                'attribute_id' => 27
                            ]);
                            $postcode = $this->fetchDB('customer_address_entity_varchar', [
                                'entity_id' => $address_entity->entity_id,
                                'attribute_id' => 30
                            ]);
                            $city = $this->fetchDB('customer_address_entity_varchar', [
                                'entity_id' => $address_entity->entity_id,
                                'attribute_id' => 26
                            ]);
                            $first_name = $this->fetchDB('customer_address_entity_varchar', [
                                'entity_id' => $address_entity->entity_id,
                                'attribute_id' => 20
                            ]);
                            $last_name = $this->fetchDB('customer_address_entity_varchar', [
                                'entity_id' => $address_entity->entity_id,
                                'attribute_id' => 22
                            ]);
                            $company_name = $this->fetchDB('customer_address_entity_varchar', [
                                'entity_id' => $address_entity->entity_id,
                                'attribute_id' => 24
                            ]);
                            $address_line = $this->fetchDB('customer_address_entity_text', [
                                'entity_id' => $address_entity->entity_id,
                                'attribute_id' => 25
                            ]);
                            $region = $this->fetchDB('customer_address_entity_varchar', [
                                'entity_id' => $address_entity->entity_id,
                                'attribute_id' => 28
                            ]);

                            if ($company_name) {
                                $address1 = $company_name;
                                $address2 = $address_line;
                            } else {
                                $address1 = $address_line;
                                $address2 = '';
                            }

                            $member->addressBilling()
                                ->create([
                                    'firstname' => $first_name->value ?? '',
                                    'lastname' => $last_name->value ?? '',
                                    'address1' => $address1->value ?? '',
                                    'address2' => $address2->value ?? '',
                                    'city' => $city->value ?? '',
                                    'state' => $region->value ?? '',
                                    'postcode' => $postcode->value ?? '',
                                    'country' => $country->value ?? '',
                                ]);
                        });
                });
            });
    }



    public function categories()
    {
        $customer_count = DB::connection('import')
            ->table('catalog_category_flat_store_1')
            ->count();

        $progress_bar = $this->command->getOutput()->createProgressBar($customer_count);
        $progress_bar->setFormat('%current%/%max% [%bar%] %percent:3s%% Elapsed:%elapsed:6s% Estimated:%estimated:-6s%');

        DB::connection('import')
            ->table('catalog_category_flat_store_1')
            ->orderBy('entity_id')
            ->chunk(100, function ($chunk) use ($progress_bar) {
                $chunk->each(function ($category_entity) use ($progress_bar) {
                    if ($category_entity->entity_id < 2) {
                        return;
                    }


                    $category = Category::query()->where('name', '=', $category_entity->name)->first();
                    if ($category) {
                        echo "Found form '{$category_entity->name}' [#{$category->id}]. \n";
                    } else {
                        echo "COULD NOT find form '{$category_entity->name}'. \n";
                    }

                    return true;


                    $category = new Category;
                    $category->id = $category_entity->entity_id;
                    $category->fill([
                        'name' => $category_entity->name,
                        'old_slug' => $category_entity->url_key,
                        'slug' => $category_entity->url_key,
                        'sub_id' => $category_entity->parent_id > 2 ? $category_entity->parent_id : 0,
                        'order' => $category_entity->position ?? 0,
                        'description' => $category_entity->description ?? '',
                        'is_online_doctor' => $category_entity->display_mode === 'PRODUCTS_AND_PAGE' ? 1 : 0,
                        'image' => $category_entity->thumbnail ?? '',
                    ]);
                    if (!empty($category->thumbnail)) {
                        $full_url = 'https://cdn.xxxxxxxxx.com/media/catalog/category/resized/510x340/' . $category->image;
                        $this->downloadImage('category_images', $category->image, $full_url);
                    }
                    $category->save();

                    $progress_bar->advance();
                });
            });
    }


    // match by epos_code / sku
    public function products($first_id = null, $last_id = null)
    {
        // Run parents only first, then child products after (to avoid parent-id-not-found type of situations).
        $parents_only = true;

        $first_id = $first_id ?? 1;
        $last_id = $last_id ?? $first_id;

        echo "Products. First ID {$first_id} -- Last ID {$last_id}.\n";

        $customer_count = DB::connection('import')
            ->table('catalog_product_entity')
            ->count();

        $pack_size_attributes = DB::connection('import')
            ->table('eav_attribute')
            ->where('frontend_label', 'like', 'Pack Size%')
            ->pluck('attribute_id');

        $strength_size_attributes = DB::connection('import')
            ->table('eav_attribute')
            ->where('frontend_label', 'like', 'strength%')
            ->pluck('attribute_id');

        $quantity_size_attributes = DB::connection('import')
            ->table('eav_attribute')
            ->where('frontend_label', 'like', 'quantity%')
            ->pluck('attribute_id');

        DB::connection('import')
            ->table('catalog_product_entity')
            ->where('entity_id', '>=', $first_id)
            ->where('entity_id', '<=', $last_id)
            ->orderBy('entity_id')
            ->chunk(100, function ($chunk) use ($pack_size_attributes, $strength_size_attributes, $quantity_size_attributes, $parents_only) {
                $chunk->each(function ($product) use ($pack_size_attributes, $strength_size_attributes, $quantity_size_attributes, $parents_only) {

                    $is_child = $this->fetchDB('catalog_product_relation', [
                        'child_id' => $product->entity_id,
                    ]);

                    if ($parents_only && $is_child) {
                        return true; // continue
                    }

                    $stock = $this->fetchDB('cataloginventory_stock_item', [
                        'product_id' => $product->entity_id,
                    ]);

                    if ($stock) {
                        $stock_value = $stock->is_in_stock ? $stock->qty : 0;
                    } else {
                        $stock_value = 999999;
                    }

                    $name = $this->fetchDB('catalog_product_entity_varchar', [
                        'entity_id' => $product->entity_id,
                        'attribute_id' => 71
                    ]);

                    $description = $this->fetchDB('catalog_product_entity_text', [
                        'entity_id' => $product->entity_id,
                        'attribute_id' => 72
                    ]);

                    $price = $this->fetchDB('catalog_product_entity_decimal', [
                        'entity_id' => $product->entity_id,
                        'attribute_id' => 75
                    ]);

                    $msrp = $this->fetchDB('catalog_product_entity_decimal', [
                        'entity_id' => $product->entity_id,
                        'attribute_id' => 120
                    ]);

                    $sale_price = $this->fetchDB('catalog_product_entity_decimal', [
                        'entity_id' => $product->entity_id,
                        'attribute_id' => 76
                    ]);

                    $weight = $this->fetchDB('catalog_product_entity_decimal', [
                        'entity_id' => $product->entity_id,
                        'attribute_id' => 80
                    ]);


                    if ($is_child) {
                        $quantity_entity = DB::connection('import')
                            ->table('catalog_product_entity_int')
                            ->where('entity_id', $product->entity_id)
                            ->whereIn('attribute_id', $quantity_size_attributes)
                            ->first();

                        if ($quantity_entity) {
                            $quantity = $this->fetchDB('eav_attribute_option_value', [
                                'option_id' => $quantity_entity->value
                            ]);
                        }

                        $strength_entity = DB::connection('import')
                            ->table('catalog_product_entity_int')
                            ->where('entity_id', $product->entity_id)
                            ->whereIn('attribute_id', $strength_size_attributes)
                            ->first();

                        if ($strength_entity) {
                            $strength = $this->fetchDB('eav_attribute_option_value', [
                                'option_id' => $strength_entity->value
                            ]);
                        }

                        $pack_size_entity = DB::connection('import')
                            ->table('catalog_product_entity_int')
                            ->where('entity_id', $product->entity_id)
                            ->whereIn('attribute_id', $pack_size_attributes)
                            ->first();

                        if ($pack_size_entity) {
                            $pack_size = $this->fetchDB('eav_attribute_option_value', [
                                'option_id' => $pack_size_entity->value
                            ]);
                        }

                        $mtc_size = Item\Size::query()->where('PLU', '=', $product->sku)->first();
                        if ($mtc_size) {
                            echo "Found size sku '{$mtc_size->PLU}' [#{$mtc_size->id}]. \n";
                            $mtc_size->magento_entity_id = $product->entity_id;
                            $mtc_size->save();

                            return true; // continue
                        }

                        echo "COULD NOT find size sku '{$product->sku}' -- entity_id: $product->entity_id. \n";

                        $mtc_size = new Item\Size;

                        $item_id = Item::query()->where('magento_entity_id', '=', $is_child->parent_id)->first()->id;

                        $mtc_size->fill([
                                'item_id' => $item_id,
                                'size' => $name->value ?? '',
                                'price' => $price->value ?? 0,
                                'quantity' => $pack_size->value ?? '',
                                'strength' => $strength->value ?? '',
                                'price_exvat' => $price ? ($price->value / 1.2) : 0,
                                'sale_price' => $sale_price->value ?? 0,
                                'sale_price_exvat' => $sale_price ? ($sale_price->value / 1.2) : 0,
                                'PLU' => $product->sku,
                                'stock' => $stock_value,
                            ]);

                        $mtc_size->magento_entity_id = $product->entity_id;
                        $mtc_size->save();

                        return true;
                    }

                    $url_slug = $this->fetchDB('catalog_product_entity_varchar', [
                        'entity_id' => $product->entity_id,
                        'attribute_id' => 97
                    ]);


                    $manufacturer_data = $this->fetchDB('catalog_product_entity_int', [
                        'entity_id' => $product->entity_id,
                        'attribute_id' => 81
                    ]);

                    $brand = $this->fetchDB('eav_attribute_option_value', [
                        'option_id' => $manufacturer_data->value ?? 0
                    ]);

                    $meta_title = $this->fetchDB('catalog_product_entity_varchar', [
                        'entity_id' => $product->entity_id,
                        'attribute_id' => 82
                    ]);
                    $meta_keyword = $this->fetchDB('catalog_product_entity_text', [
                        'entity_id' => $product->entity_id,
                        'attribute_id' => 83
                    ]);
                    $meta_description = $this->fetchDB('catalog_product_entity_varchar', [
                        'entity_id' => $product->entity_id,
                        'attribute_id' => 84
                    ]);

                    $media_gallery = $this->fetchDB('catalog_product_entity_media_gallery', [
                        'entity_id' => $product->entity_id,
                        'attribute_id' => 88
                    ], false);

                    $status = $this->fetchDB('catalog_product_entity_int', [
                        'entity_id' => $product->entity_id,
                        'attribute_id' => 102
                    ]);


                    $categories = $this->fetchDB('catalog_category_product', [
                        'product_id' => $product->entity_id,
                    ], false);

                    $contents = $this->fetchDB('catalog_product_entity_text', [
                        'entity_id' => $product->entity_id,
                        'attribute_id' => 239
                    ]);

                    $indication = $this->fetchDB('catalog_product_entity_text', [
                        'entity_id' => $product->entity_id,
                        'attribute_id' => 237
                    ]);

                    $warnings = $this->fetchDB('catalog_product_entity_text', [
                        'entity_id' => $product->entity_id,
                        'attribute_id' => 238
                    ]);

                    $price_object = $this->fetchDB('catalog_product_index_price_idx', [
                        'entity_id' => $product->entity_id,
                        'customer_group_id' => 1
                    ]);

                    $question_list = $this->fetchDB('catalog_product_entity_varchar', [
                        'entity_id' => $product->entity_id,
                        'attribute_id' => 225
                    ]);

                    $product_type = $this->fetchDB('catalog_product_entity_int', [
                        'entity_id' => $product->entity_id,
                        'attribute_id' => 143
                    ]);

                    $type = '';
                    if ($product_type && $product_type->value == 40) {
                        $type = 'doctor';
                    } elseif ($product_type && $product_type->value == 41) {
                        $type = 'pharmacy';
                    }


                    $item = Item::query()->where('epos_code', '=', $product->sku)->first();
                    if ($item) {
                        echo "Found Item '{$item->epos_code}' [#{$item->id}]. \n";
                        $item->magento_entity_id = $product->entity_id;
                        $item->save();

                        return true; // continue
                    }

                    echo "COULD NOT find Item '{$product->sku}' -- entity_id: {$product->entity_id}. \n";
                    $item = new Item;

                    $item->epos_code = $product->sku ?? '';
                    $item->hidden = 1; // $status ? $status->value != 1 : 0;
                    $item->name = $name->value ?? '';
                    $item->description = $description->value ?? '';
                    $item->price = $price->value ?? 0;
                    $item->price_exvat = $item->price / 1.2;
                    $item->vat_rate = optional($price_object)->tax_class_id === 2 ? 20 : 0;
                    $item->sale_price = $sale_price->value ?? 0;
                    $item->sale_price_exvat = $item->sale_price / 1.2;
                    $item->seo_title = $meta_title->value ?? '';
                    $item->seo_keywords = $meta_keyword->value ?? '';
                    $item->seo_description = $meta_description->value ?? '';
                    $item->form_id = $question_list->value ?? 0; // ????????????????????????????????????????????????
                    $item->product_type = $type;
                    $item->stock = $stock_value;

                    $item->magento_entity_id = $product->entity_id;
                    $item->save();

                    if ($url_slug && Item\Custom::query()->where('slug', $url_slug->value)->exists()) {
                        $url_slug->value .= '--imported-from-magento';
                    }

                    $item->custom()
                        ->create([
                            'slug' => $url_slug->value ?? '',
                            'shipper_weight' => $weight->value ?? 0,
                            'manufacturer' => $brand->value ?? '',
                            'contents' =>   $contents->value ?? '',
                            'indication' =>   $indication->value ?? '',
                            'caution' =>   $warnings->value ?? '',
                        ]);

                    //$media_gallery->each(function ($image_record, $index) use ($item) {
                    //    $name = str_replace('/', '', $image_record->value);
                    //    $item->images()
                    //        ->create([
                    //            'name' => $name,
                    //            'default' => $index == 0,
                    //            'hover' => 1,
                    //        ]);

                    //    $full_url = 'https://cdn.xxxxxxxxx.com//media/catalog/product' . $image_record->value;
                    //    $this->downloadImage('product_folders', $name, $full_url);
                    //});

                    // Does it matter what category the product is in??
                    // $item->categories()->sync($categories->pluck('category_id'));

                });
            });
    }


    public function assessment_forms()
    {
        $customer_count = DB::connection('import')
            ->table('medicine_category')
            ->count();

        $this->command->info('Importing Forms');

        DB::connection('import')
            ->table('medicine_category')
            ->orderBy('category_id')
            ->chunk(100, function ($chunk) {
                $chunk->each(function ($category_entity) {

                    $form = Form::query()->where('name', '=', $category_entity->category_name)->first();

                    if ($form) {
                        //echo "Found form '{$category_entity->category_name}' [#{$form->id}]. \n";
                    } else {
                        //echo "COULD NOT find form '{$category_entity->category_name}'. \n";
                        //$form->id = $category_entity->category_id;
                        //$form->name = $category_entity->category_name;
                        //$form->is_active = $category_entity->is_active;
                        //$form->save();
                    }

                    $questions_for_this_form = $this->fetchDB('medicine_category_question', [
                        'category_id' => $category_entity->category_id,
                    ], false)->pluck('question_id');

                    DB::connection('import')
                        ->table('medicine_question')
                        ->whereIn('question_id', $questions_for_this_form)
                        ->get()
                        ->each(function ($question_entity) use ($form) {
                            if ($question_entity->is_active == 0) {
                                return;
                            }

                            $question = Question::query()->where('magento_question_id', '=', $question_entity->question_id)->first();

                            if ($question) {
                                return true; // continue
                                echo "Found question '{$question_entity->question_id}' [#{$question->id}]. \n";
                                $question->magento_question_id = $question_entity->question_id;
                                $question->save();
                                return true; // continue
                            }

                            echo "COULD NOT find question '{$question_entity->question_id}'. \n";
                            echo "COULD NOT find question '{$question_entity->question}'. \n";

                            $question = new Question;
                            $question->magento_question_id = $question_entity->question_id;

                            $options = explode(',', $question_entity->answer_option);
                            $question->fill([
                                'form_id' => $form->id,
                                'question_type' => self::$assessment_question_types[$question_entity->question_type],
                                'question_text' => $question_entity->question,
                                'is_required' => $question_entity->requiry,
                                'variations' => !empty($options) ? json_encode($options) : '',
                                'is_multiple' => $question_entity->question_type == 3 ? 1 : 0,
                                'is_active' => 0,
                                'sort' => $question_entity->question_order,
                            ]);
                            $question->save();

                            //if (!empty($question_entity->message)) {
                            //    if ($question_entity->question_type == 1) {
                            //        $incorrect_answers = $question_entity->answer_option_exactly === 'Yes' ? ['No'] : ['Yes'];
                            //    } else {
                            //        $correct_answers = explode(',', $question_entity->answer_option_exactly);
                            //        $incorrect_answers = array_diff($options, $correct_answers);
                            //    }
                            //    foreach ($incorrect_answers as $answer) {
                            //        $message = new Question;
                            //        $message->fill([
                            //            'form_id' => $form->id,
                            //            'question_type' => 'information',
                            //            'question_text' => $question_entity->message,
                            //            'is_required' => 0,
                            //            'variations' => '',
                            //            'is_active' => $question_entity->is_active,
                            //            'show_if_question' => $question->id,
                            //            'show_if_answer' => $answer,
                            //            'sort' => $question_entity->question_order + 1,
                            //        ]);
                            //        $message->save();
                            //    }
                            //}

                            //if ($question_entity->question_type == 4) {
                            //    $message = new Question;
                            //    $message->fill([
                            //        'form_id' => $form->id,
                            //        'question_type' => 'text',
                            //        'question_text' => '',
                            //        'is_required' => 0,
                            //        'variations' => '',
                            //        'is_active' => $question_entity->is_active,
                            //        'show_if_question' => $question->id,
                            //        'show_if_answer' => 'Yes',
                            //        'sort' => $question_entity->question_order + 1,
                            //    ]);
                            //    $message->save();
                            //}

                            //if (!empty($question_entity->message_correct_answer)) {
                            //    $correct_answers = explode(',', $question_entity->answer_option_exactly);
                            //    foreach ($correct_answers as $answer) {
                            //        $message = new Question;
                            //        $message->fill([
                            //            'form_id' => $form->id,
                            //            'question_type' => 'information',
                            //            'question_text' => $question_entity->message_correct_answer,
                            //            'is_required' => 0,
                            //            'variations' => '',
                            //            'is_active' => $question_entity->is_active,
                            //            'show_if_question' => $question->id,
                            //            'show_if_answer' => $answer,
                            //            'sort' => $question_entity->question_order + 1,
                            //        ]);
                            //        $message->save();
                            //    }
                            //}

                            //if ($question_entity->question_type == 8) {
                            //    $correct_answers = explode(',', $question_entity->answer_option_exactly);
                            //    foreach ($correct_answers as $answer) {
                            //        $message = new Question;
                            //        $message->fill([
                            //            'form_id' => $form->id,
                            //            'question_type' => 'text',
                            //            'question_text' => $question_entity->yesno_label,
                            //            'is_required' => $question_entity->requiry,
                            //            'variations' => '',
                            //            'is_active' => $question_entity->is_active,
                            //            'show_if_question' => $question->id,
                            //            'show_if_answer' => 'Yes',
                            //            'sort' => $question_entity->question_order + 1,
                            //        ]);
                            //        $message->save();
                            //    }
                            //}
                        });

                });
            });
    }


    public function assessment_answers($first_id = null, $last_id = null)
    {
        $first_id = $first_id ?? 1;
        $last_id = $last_id ?? $first_id;

        echo "Answers. First ID {$first_id} -- Last ID {$last_id}.\n";

        DB::connection('import')
            ->table('medicine_answer')
            ->orderBy('answer_id')
            //->where('creation_time', '>', '2018-11-16')
            ->where('answer_id', '>=', $first_id)
            ->where('answer_id', '<=', $last_id)
            ->chunk(100, function ($chunk) {
                $chunk->each(function ($answer_entity) {

                    echo "Importing answer_id: {$answer_entity->answer_id} ... ";


                    $is_answer_imported = DB::table('assessment_answers_magento')->where('magento_answer_id', '=', $answer_entity->answer_id)->count();

                    $mtc_order = Order::query()
                        ->where('order_ref', '=', $answer_entity->order_id)
                        ->first()
                    ;

                    if (! $mtc_order) {
                        echo "COULD NOT fine mtc order with order_ref: {$answer_entity->order_id}! \n";
                        die();
                    }

                    if (! $is_answer_imported) {
                        $res = DB::table('assessment_answers_magento')
                            ->insert([
                                'mtc_order_id' => $mtc_order->id,
                                'magento_order_id' => $answer_entity->order_id,
                                'magento_answer_id' => $answer_entity->answer_id,
                                'magento_answer' => $answer_entity->answer,
                                'magento_creation_time' => $answer_entity->creation_time,
                                'magento_update_time' => $answer_entity->update_time,
                            ]);
                        echo "{$res}.";
                    } else {
                        echo " ALREADY IMPORTED ";
                    }

                    echo "\n";


                    return true; // continue


                    $answer_data = json_decode($answer_entity->answer, true);

                    foreach ($answer_data as $form_name => $products) {

                        $form = Form::query()->where('name', $form_name)->first();
                        if (! $form) {
                            echo "COULD NOT FIND form name: >>>{$form_name}<<< (answer_id: {$answer_entity->answer_id})\n";
                            continue;
                        }

                        $assessment = new Assessment;
                        $assessment->member_id = $answer_entity->customer_id;
                        $assessment->form_id = $form->id;
                        $assessment->is_completed = 1;
                        $assessment->save();

                        $order_items = Order\Item::query()
                            ->whereHas('order', function ($order_query) use ($answer_entity) {
                                return $order_query->where('order_ref', $answer_entity->order_id);
                            })->get();

                        collect($products)
                            ->each(function ($answer_data, $product_name) use ($order_items, $assessment, $form) {
                                $order_items->filter(function ($item) use ($product_name) {
                                    return in_array($product_name, [$item->item_name, $item->size]);
                                })
                                    ->each(function ($item) use ($assessment) {
                                        $item->assessment_id = $assessment->id;
                                        $item->save();
                                        $assessment->item_id = $item->item_id;
                                        $assessment->save();
                                    });

                                foreach ($answer_data as $old_question_value => $values) {
                                    $question = Question::query()
                                        ->where('old_id', $old_question_value)
                                        ->orWhere('question_text', $old_question_value)
                                        ->first();
                                    if (is_array($values) && count($values) == 1) {
                                        $value = $values[0];
                                    }
                                    if (is_string($values)) {
                                        $value = $values;
                                    }

                                    if (!empty($value)) {
                                        $assessment->answers()
                                            ->create([
                                                'assessment_id' => $assessment->id,
                                                'form_id' => $form->id,
                                                'question_id' => $question->id ?? 0,
                                                'answer' => $value
                                            ]);
                                    } else {
                                        $assessment->answers()
                                            ->create([
                                                'assessment_id' => $assessment->id,
                                                'form_id' => $form->id,
                                                'question_id' => $question->id ?? 0,
                                                'answer' => json_encode($value)
                                            ]);
                                    }
                                }
                            });
                    }

                });
            });
    }


    private function addOrderNotes(\Mtc\Shop\Order $order)
    {
        $order_notes = $this->fetchDB('sales_flat_order_status_history', [
            'parent_id' => $order->magento_entity_id,
        ], false);

        foreach ($order_notes as $order_note) {
            if ($order_note->comment) {
                $res = $order->notes()->create([
                    'magento_entity_id' => $order_note->entity_id,
                    'note' => "[{$order_note->created_at}] {$order_note->comment}",
                    'timestamp' => $order_note->created_at,
                ]);
            }
        }
    }


    /**
     * Order data import
     */
    public function orders($first_id = null, $last_id = null)
    {
        $first_id = $first_id ?? 1;
        $last_id = $last_id ?? $first_id;

        $orders_count = DB::connection('import')
            ->table('sales_flat_order')
            ->count();

        DB::connection('import')
            ->table('sales_flat_order')
            ->where('entity_id', '>=', $first_id)
            ->where('entity_id', '<=', $last_id)
            ->orderBy('entity_id')
            ->chunk(100, function ($chunk) {
                $chunk->each(function ($order_entity) {

                    $status = $this->getOrderStatus($order_entity->status);

                    echo("Processing [{$order_entity->increment_id}] ... ");

                    $order = Order::query()->where('order_ref', '=', $order_entity->increment_id)->first();
                    if ($order) {

                        // $this->addOrderNotes($order);

                        //if (! is_null($order->magento_entity_id)) {
                        //    echo "\n";
                        //    return true; // continue
                        //}

                        echo " FOUND mtc #{$order->id}, magento #{$order_entity->entity_id} ... ";
                        //$order->magento_entity_id = $order_entity->entity_id;

                        if ($order->status != $status) {
                            echo " updating order status from {$order->status} to {$status} ... ";
                            $order->status = $status;
                        }

                        $order->save();
                        echo " SAVED.\n";

                        return true; // continue
                    }

                    echo " NEW ... ";
                    $order = new Order;

                    $member_id = Member::query()->where('magento_entity_id', '=', $order_entity->customer_id)->first()->id;

                    $order->fill([
                        'paid' => 1,
                        'date' => $order_entity->created_at,
                        'delivery_method_id' => 0, // TODO
                        'delivery_name' => $order_entity->shipping_description ?? '',
                        'delivery_cost' => $order_entity->shipping_amount,
                        'cost_total' => $order_entity->grand_total,
                        'cost_total_exvat' => $order_entity->grand_total - $order_entity->tax_amount,
                        'status' => $status,
                        'member' => $member_id,
                        'order_ref' => $order_entity->increment_id,
                        'magento_entity_id' => $order_entity->entity_id,
                    ]);

                    $order->magento_entity_id = $order_entity->entity_id;
                    $order->save();

                    echo " SAVED -- mtc #{$order->id}\n";


                    $details = $this->fetchDB('sales_flat_order_address', [
                        'parent_id' => $order_entity->entity_id,
                    ]);

                    $order->info()
                        ->create([
                            'email' => $details->email ?? $order_entity->customer_email,
                            'contact_no' => $details->telephone
                        ]);

                    $this->fetchDB('sales_flat_order_address', [
                        'parent_id' => $order_entity->entity_id,
                    ], false)->each(function ($address_entity) use ($order) {
                        $order->address()
                            ->create([
                                'type' => $address_entity->address_type,
                                'firstname' => $address_entity->firstname,
                                'lastname' => $address_entity->lastname,
                                'address1' => $address_entity->street,
                                'city' => $address_entity->city,
                                'postcode' => $address_entity->postcode ?? '',
                                'state' => $address_entity->region ?? '',
                                'country' => $address_entity->country_id, // country code, e.g. GB
                            ]);
                    });


                    $this->fetchDB('sales_flat_order_payment', [
                        'parent_id' => $order_entity->entity_id,
                    ], false)->each(function ($payment_entity) use ($order) {

                        DB::table('order_protx')
                            ->insert([
                                'order_id' => $order->id,
                                'Amount' => $payment_entity->amount_paid ?? $payment_entity->amount_ordered,
                                'TxAuthNo' => $order->id,
                                'VendorTxCode' => $order->id,
                            ]);
                    });

                    $this->fetchDB('sales_flat_order_status_history', [
                        'parent_id' => $order_entity->entity_id,
                    ], false)->each(function ($state_entity) use ($order) {

                        DB::table('order_status')
                            ->insert([
                                'order_id' => $order->id,
                                'status' => $this->getOrderStatus($state_entity->entity_name),
                                'timestamp' => $state_entity->created_at
                            ]);
                    });


                    echo "Items for mtc order #{$order->id}, entity_id: {$order_entity->entity_id} ... ";

                    $order_items = $this->fetchDB('sales_flat_order_item', [
                        'order_id' => $order_entity->entity_id,
                    ], false);

                    $order_items->reject(function ($order_item) {
                        return !empty($order_item->parent_item_id); // ??????????????????????????? 'simple' products only?
                    })->each(function ($order_item) use ($order, $order_items) {

                        echo " Product Type: {$order_item->product_type}, sku: {$order_item->sku} ... ";

                        $mtc_size_id = 0;
                        $size = $order_items->where('parent_item_id', $order_item->item_id)->first();

                        $mtc_size = Item\Size::query()->where('PLU', '=', $order_item->sku)->first();
                        if ($mtc_size) {
                            $mtc_size_id = $mtc_size->id;
                            $mtc_item_id = $mtc_size->item_id;
                        }

                        $mtc_item = Item::query()->where('epos_code', '=', $order_item->sku)->first();
                        if ($mtc_item) {
                            $mtc_size_id = 0;
                            $mtc_item_id = $mtc_item->id;
                        }
                        
                        $order->items()
                            ->create([
                                'item_id' => $mtc_item_id,
                                'sizeid' => $mtc_size_id,
                                'PLU' => $order_item->sku,
                                'item_name' => $order_item->name,
                                'item_price' => $order_item->price,
                                'item_price_exvat' => $order_item->price - $order_item->tax_amount,
                                'quantity' => $order_item->qty_ordered,
                                'size' => $size->name ?? '',
                                'colour' => '',
                                'price_paid' => $order_item->price,
                                'price_paid_exvat' => $order_item->price - $order_item->tax_amount,
                                'nhs_prescription' => 0,
                                'assessment_id' => 0,
                            ]);

                        echo " DONE.";
                    });
                });
            });

    }


    public function matchAssessmentToProducts()
    {

    }

    /**
     * Quick fetch method for table
     *
     * @param $table
     * @param array $wheres
     * @param bool $first
     * @return mixed
     */
    private function fetchDB($table, $wheres = [], $first = true)
    {
        $query = DB::connection('import')
            ->table($table)
            ->where($wheres);

        return $first ? $query->first() : $query->get();
    }

    /**
     * Download image to its size locations
     *
     * @param $sizes
     * @param $file_name
     * @param $url
     */
    private function downloadImage($sizes, $file_name, $url)
    {
        include base_path('shop/includes/image_folders.php');

        $data = file_get_contents($url);
        foreach ($image_folders[$sizes] as $size) {
            $save_path = str_replace('uploads/', '', $size['path']) . '/' . $file_name;
            Storage::disk('uploads')->put($save_path, $data);

            $new_file_path = base_path('uploads/' . $save_path);
            ImageUploader::processImage($file_name, $new_file_path, pathinfo($file_name, PATHINFO_EXTENSION), $size);
        }
    }

    /**
     * @param $state
     * @return int
     */
    private function getOrderStatus($state)
    {
        return self::$order_status_map[$state] ?? 0;
    }
}
