<?php

namespace App\Console;

use Carbon\Carbon;
use App\ServiceCity;
use Illuminate\Support\Facades\DB;
use Illuminate\Console\Scheduling\Schedule;
use Illuminate\Foundation\Console\Kernel as ConsoleKernel;

class Kernel extends ConsoleKernel
{
    /**
     * The Artisan commands provided by your application.
     *
     * @var array
     */
    protected $commands = [
        //
    ];

    /**
     * Define the application's command schedule.
     *
     * @param  \Illuminate\Console\Scheduling\Schedule  $schedule
     * @return void
     */
    protected function schedule(Schedule $schedule)
    {
      $schedule->call(function () {
        $daysSpanish = [
            1 => 'Lunes',
            2 => 'Martes',
            3 => 'Miércoles',
            4 => 'Jueves',
            5 => 'Viernes',
            6 => 'Sábado',
            7 => 'Domingo',
        ];
        $city_services = ServiceCity::where('active',1)->get();
        $last_update = DB::connection('bi-db')->table('data_update')->where('id', 1)->first();
        DB::connection('bi-db')->table('data_update')->truncate();
        foreach (config('app.arrayCitiesDB') as $bd) {
            foreach ($city_services as $cityService) {
                if($cityService->name_database == $bd){
                    $corporate = DB::connection($bd)->table('corporate_identities')->where('id', 1)->first();
                    $orders = DB::connection($bd)->table('orders')
                                ->leftJoin('comments', 'orders.id', '=', 'comments.order_id')
                                ->leftJoin('addresses', 'orders.address_id', '=', 'addresses.id')
                                ->leftJoin('coverages', 'addresses.city_id', '=', 'coverages.city_id')
                                ->leftJoin('cities', 'coverages.city_id', '=', 'cities.id')
                                ->leftJoin('states', 'cities.state_id', '=', 'states.id')
                                ->leftJoin('users as delivery', 'orders.delivery_man_id', '=', 'delivery.id')
                                ->leftJoin('users as client', 'orders.client_id', '=', 'client.id')
                                ->leftJoin('sucursals', 'orders.current_sucursal_id', '=', 'sucursals.id')
                                ->select('orders.id as order_id','orders.payment_type_id', 'orders.creation_date','orders.order_type_id',
                                'orders.order_state_id','orders.delivery_man_id', 'addresses.city_id', 'coverages.id as coverage_id', 'cities.state_id','states.country_id',
                                'comments.qualification_client', 'delivery.first_name as delivery_first_name', 'delivery.last_name as delivery_last_name',
                                'sucursals.code as sucursal_code', 'sucursals.name as sucursal_name', 'sucursals.city_id as sucursal_city_id',
                                'sucursals.address_id as sucursal_address_id', 'sucursals.coverage_id as sucursal_coverage_id', 'client.document',
                                'client.first_name as client_first_name', 'client.last_name as client_last_name', 'client.rol_id')
                                ->where('creation_date', '>', $last_update->last_update)
                                ->get();
                    foreach ($orders as $order) {
                    $orderNew = DB::connection('bi-db')->table('order')->insertGetId([
                        "city_services_id" => $cityService->id,
                        "order_id" => $order->order_id,
                        "order_type" => $order->order_type_id,
                        "payment_method" => $order->payment_type_id,
                        "shipping_method" => null,
                        "sales_channel" => null,
                        "user_rating" => $order->qualification_client,
                        "order_state" => $order->order_state_id,
                        "id_courier_company" => null,
                        "courier_company" => null,
                        "id_delivery" => $order->delivery_man_id,
                        "name_delivery" => $order->delivery_first_name . ' ' . $order->delivery_last_name,
                        "type_vehicle" => null,
                    ]);

                    $sucursalNew = DB::connection('bi-db')->table('sucursal')->insertGetId([
                        "city_services_id" => $cityService->id,
                        "order_id" => $order->order_id,
                        "cod_internal" => null,
                        "description_company" => null,
                        "cod_sucursal" => $order->sucursal_code,
                        "description_sucursal" => $order->sucursal_name,
                        "country" => null,
                        "city" => $order->sucursal_city_id,
                        "address" => $order->sucursal_address_id,
                        "coverage_area" => $order->sucursal_coverage_id,
                        "lat" => null,
                        "lng" => null,
                    ]);

                    $clientNew = DB::connection('bi-db')->table('client')->insertGetId([
                        "city_services_id" => $cityService->id,
                        "order_id" => $order->order_id,
                        "document" => $order->document,
                        "name" => $order->client_first_name,
                        "surname" => $order->client_last_name,
                        "role_id" => $order->rol_id,
                        "gender" => null,
                        "age" => null,
                        "age_range" => null,
                        "model_device" => null,
                        "os_device" => null,
                        "version_os_device" => null,
                        "version_app" => null,
                        "lat" => null,
                        "lng" => null,
                    ]);

                    $dateNew = DB::connection('bi-db')->table('date')->insertGetId([
                        "city_services_id" => $cityService->id,
                        "order_id" => $order->order_id,
                        "date_creation" => $order->creation_date,
                        "year" => Carbon::createFromFormat('Y-m-d H:i:s', $order->creation_date)->year,
                        "month" => Carbon::createFromFormat('Y-m-d H:i:s', $order->creation_date)->month,
                        "day" => Carbon::createFromFormat('Y-m-d H:i:s', $order->creation_date)->day,
                        "hour" => Carbon::createFromFormat('Y-m-d H:i:s', $order->creation_date)->hour,
                        "text_day" => $daysSpanish[Carbon::createFromFormat('Y-m-d H:i:s', $order->creation_date)->dayOfWeek],
                        "fds" => null,
                        "week" => null,
                        "trimester" => null,
                        "delivery_time" => null,
                    ]);

                    $listProducts = DB::connection($bd)->table('order_products')->where('order_id', $order->order_id)->get();


                        foreach ($listProducts as $product) {
                            if($product->flash_price){
                                $final_price = $product->price - $product->flash_price;
                            }else if($product->percentage_discount){
                                $final_price = $product->price - (($product->price * $product->percentage_discount)/100);
                            }else{
                                $final_price = $product->price;
                            }

                            $subcategoryProducts = DB::connection($bd)->table('subcategory_products')->where('product_id', $order->order_id)->get();

                            $listCategories = '';
                            $listSubcategories = '';
                            foreach ($subcategoryProducts as $subcategoryProduct) {
                                $subcategory = DB::connection($bd)->table('subcategories')->where('id', $subcategoryProduct->subcategory_id)->first();
                                $category = DB::connection($bd)->table('categories')->where('name', $subcategory->name)->first();
                                if($category){
                                    $listCategories = $listCategories.$subcategory->name.', ';
                                }else{
                                    $listSubcategories = $listSubcategories.$subcategory->name.', ';
                                }
                            }

                            $productData = DB::connection($bd)->table('products')->where('id', $product->product_id)->first();
                            $productNew = DB::connection('bi-db')->table('product')->insertGetId([
                                "city_services_id" => $cityService->id,
                                "order_id" => $order->order_id,
                                "sku_company" => null,
                                "barcode" => null,
                                "description" => $productData->name,
                                "categories" => rtrim(trim($listCategories), ','),
                                "subcategories" => rtrim(trim($listSubcategories), ','),
                                "price" => $product->price,
                                "final_price" => $final_price,
                                "type_offer" => $product->status2x1 ? '2x1' : null,
                                "measurement" => $productData->sizing_units_id,
                                "brand" => $productData->brand_id,
                                "state_maturation" => null,
                                "size" => null,
                                "color" => null,
                            ]);

                            DB::connection('bi-db')->table('sales')->insert([
                                "city_services_id" => $cityService->id,
                                "order_global_id" => $order->order_id,
                                "order_id" => $orderNew,
                                "sucursal_id" => $sucursalNew,
                                "client_id" => $clientNew,
                                "date_id" => $dateNew,
                                "product_id" => $productNew,
                                "order_quantity" => $product->quantity,
                                "final_quantity" => null,
                                "variation_quantity" => null,
                                "sale_without_discount" => $product->quantity * $product->price,
                                "final_sale" => $product->quantity * $final_price,
                                "discount" => $product->price - $final_price,
                                "amount_paid" => null,
                            ]);
                        }
                    }
                }
            }
        }
        DB::connection('bi-db')->table('data_update')->insert([
        "last_update" =>  Carbon::now(),
        ]);
      })->daily();
    }

    /**
     * Register the commands for the application.
     *
     * @return void
     */
    protected function commands()
    {
        $this->load(__DIR__.'/Commands');

        require base_path('routes/console.php');
    }
}