<?php

namespace App\Http\Controllers\Exports;

use App\Product;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

class ReportProducts implements FromCollection, WithHeadings, WithEvents, WithColumnFormatting
{

    // set the headings
    public function headings(): array
    {
        return [
            'PLU',
            'Codigo de barras',
            'Nombre',
            'Marca',
            'Unidades disponibles',
            'Maximo unidades por talla',
            'Precio',
            'Precio relampago',
            'Inicio precio relampago',
            'Vigencia precio relampago',
            'Hora limite relampago',
            'Descuento',
            'Inicio descuento',
            'Vigencia descuento',
            'Hora limite descuento',
            'Prioridad',
            'Validacion edad',
            'Inactivar',
            'atributos (separados por "|")',
            'valor/es (separados por comas ",")',
            'Precio adicional (separados por comas ",")',
            'sku (separados por comas ",")',
            'Unidades disponibles (separados por comas ",")',
            'Código interno PMI (separados por comas ",")',
            'Embalaje (separados por comas ",")',
            'Peso (separados por comas ",")',
            'EAN (separados por comas ",")',
            'Ancho (separados por comas ",")',
            'Largo (separados por comas ",")',
            'Alto (separados por comas ",")',
            'Observación (separados por comas ",")',
            'Posición principal (separados por comas ",")',
            'Patrón estiba (separados por comas ",")',
            'Maximo unidades por producto',
            'Enlace del producto',
            'Segmentación',
            'Precio segmentación'
        ];
    }

    // freeze the first row with headings
    public function registerEvents(): array
    {
        return [];
    }

    public function columnFormats(): array
    {
        return [
            'I' => NumberFormat::FORMAT_TEXT,
            'J' => NumberFormat::FORMAT_TEXT,
            'K' => NumberFormat::FORMAT_TEXT,
            'M' => NumberFormat::FORMAT_TEXT,
            'N' => NumberFormat::FORMAT_TEXT,
            'O' => NumberFormat::FORMAT_TEXT,
        ];
    }

    public function collection()
    {
        DB::statement("SET sql_mode = ''");
        $results = Product::select(
            'products.*',
            DB::raw('GROUP_CONCAT(DISTINCT(tags.name)) AS nameTags'),
            DB::raw('MAX(product_tags.special_price) AS special_price'),
        )
            ->with('productAttributes')
            ->leftjoin('product_tags', 'product_tags.product_id', '=', 'products.id')
            ->leftjoin('tags', function ($join) {
                $join->on('tags.id', '=', 'product_tags.tag_id')->where('tags.active', 1);
            })
            ->whereNull('products.deleted_at')
            ->groupBy('products.id')
            ->get();
        DB::statement("SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'");
        $data = [];
        foreach ($results as $product) {
            $attributes = $product->productAttributes->groupBy('attribute_id');
            $row_attributtes = "";
            $row_values = "";
            $row_price = "";
            $row_sku = "";
            $row_available_unit = "";
            $row_pmi = "";
            $row_packaging = "";
            $row_weight = "";
            $row_ean = "";
            $row_width = "";
            $row_length = "";
            $row_high = "";
            $row_observation = "";
            $row_main_position = "";
            $row_stowage_pattern = "";
            foreach ($attributes as $key => $attribute) {
                $row_attributtes = $row_attributtes . $key . "|";
                if ($row_values != "") {
                    $row_values = substr($row_values, 0, -1) . "|";
                }
                if ($row_price != "") {
                    $row_price = substr($row_price, 0, -1) . "|";
                }
                if ($row_sku != "") {
                    $row_sku = substr($row_sku, 0, -1) . "|";
                }
                if ($row_available_unit != "") {
                    $row_available_unit = substr($row_available_unit, 0, -1) . "|";
                }
                if ($row_pmi != "") {
                    $row_pmi = substr($row_pmi, 0, -1) . "|";
                }
                if ($row_packaging != "") {
                    $row_packaging = substr($row_packaging, 0, -1) . "|";
                }
                if ($row_weight != "") {
                    $row_weight = substr($row_weight, 0, -1) . "|";
                }
                if ($row_ean != "") {
                    $row_ean = substr($row_ean, 0, -1) . "|";
                }
                if ($row_width != "") {
                    $row_width = substr($row_width, 0, -1) . "|";
                }
                if ($row_length != "") {
                    $row_length = substr($row_length, 0, -1) . "|";
                }
                if ($row_high != "") {
                    $row_high = substr($row_high, 0, -1) . "|";
                }
                if ($row_observation != "") {
                    $row_observation = substr($row_observation, 0, -1) . "|";
                }
                if ($row_main_position != "") {
                    $row_main_position = substr($row_main_position, 0, -1) . "|";
                }
                if ($row_stowage_pattern != "") {
                    $row_stowage_pattern = substr($row_stowage_pattern, 0, -1) . "|";
                }
                foreach ($attribute as $item) {
                    $row_values = $row_values . $item->value . ",";
                    $row_price = $row_price . intval($item->price_additional) . ",";
                    $row_sku = $row_sku . $item->sku . ",";
                    $row_available_unit = $row_available_unit . $item->available_units . ",";
                    $row_pmi = $row_pmi . $item->pmi . ",";
                    $row_packaging = $row_packaging . $item->packaging . ",";
                    $row_weight = $row_weight . $item->weight . ",";
                    $row_ean = $row_ean . $item->ean . ",";
                    $row_width = $row_width . $item->width . ",";
                    $row_length = $row_length . $item->length . ",";
                    $row_high = $row_high . $item->high . ",";
                    $row_observation = $row_observation . $item->observation . ",";
                    $row_main_position = $row_main_position . $item->main_position . ",";
                    $row_stowage_pattern = $row_stowage_pattern . $item->stowage_pattern . ",";
                }
            }
            $row_attributtes = substr($row_attributtes, 0, -1);
            $row_values = substr($row_values, 0, -1);
            $row_price = substr($row_price, 0, -1);
            $row_sku = substr($row_sku, 0, -1);
            $row_available_unit = substr($row_available_unit, 0, -1);
            $row_pmi = substr($row_pmi, 0, -1);
            $row_packaging = substr($row_packaging, 0, -1);
            $row_weight = substr($row_weight, 0, -1);
            $row_ean = substr($row_ean, 0, -1);
            $row_width = substr($row_width, 0, -1);
            $row_length = substr($row_length, 0, -1);
            $row_high = substr($row_high, 0, -1);
            $row_observation = substr($row_observation, 0, -1);
            $row_main_position = substr($row_main_position, 0, -1);
            $row_stowage_pattern = substr($row_stowage_pattern, 0, -1);
            $data[] = array(
                $product->plu,
                $product->bar_code,
                $product->name,
                $product->brand ? $product->brand->name : '',
                $product->available_units,
                $product->max_units_per_order,
                $product->price,
                $product->flash_price,
                $product->start_flash_discount,
                $product->limit_flash_discount,
                $product->limit_hour_flash_discount,
                $product->percentage_discount,
                $product->start_discount,
                $product->limit_discount,
                $product->limit_hour_discount,
                $product->order,
                $product->validate_age,
                $product->active != 1 ? "X" : "",
                $row_attributtes,
                $row_values,
                $row_price,
                $row_sku,
                $row_available_unit,
                $row_pmi,
                $row_packaging,
                $row_weight,
                $row_ean,
                $row_width,
                $row_length,
                $row_high,
                $row_observation,
                $row_main_position,
                $row_stowage_pattern,
                $product->max_units_per_product,
                $product->product_link,
                $product->nameTags,
                $product->special_price
            );
        }
        return collect($data);
    }
}