<?php

namespace App\Exports;

use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;

class VarianceInventoryLinesExport implements FromCollection, WithHeadings, WithMapping
{
    protected $inventorySessionWarehouseId;
    protected $data;

    public function __construct($inventorySessionWarehouseId)
    {
        $this->inventorySessionWarehouseId = $inventorySessionWarehouseId;
        $this->data = $this->buildData();
    }

    protected function buildData()
    {
        // Get expected quantity per barcode
        $expected = DB::table('inventory_lines')
            ->where('inventory_session_warehouse_id', $this->inventorySessionWarehouseId)
            ->select('barcode', DB::raw('SUM(qty) as expected_qty'), 'cost', 'is_created')
            ->groupBy('barcode', 'cost', 'is_created')
            ->get()
            ->keyBy('barcode');

        // Get counted quantity and zones per barcode
        $counted = DB::table('inventory_line_counts as ilc')
            ->join('inventory_lines as il', 'il.id', '=', 'ilc.inventory_line_id')
            ->join('inventory_zones as z', 'ilc.inventory_zone_id', '=', 'z.id')
            ->where('z.inventory_session_warehouse_id', $this->inventorySessionWarehouseId)
            ->select(
                'il.barcode',
                DB::raw('SUM(ilc.counted_qty) as counted_qty'),
                DB::raw('GROUP_CONCAT(DISTINCT CONCAT(z.prefix, z.number) ORDER BY z.number ASC SEPARATOR ", ") as zones')
            )
            ->groupBy('il.barcode')
            ->get()
            ->keyBy('barcode');

        // Get descriptions from inventory_masters
        $descriptions = DB::table('inventory_masters')
            ->select('barcode', 'description', 'codein', 'colour', 'size', 'style')
            ->whereIn('barcode', $expected->keys()->merge($counted->keys())->unique())
            ->get()
            ->keyBy('barcode');

        // Combine rows with mismatches only
        return $expected->keys()->merge($counted->keys())->unique()->filter(function ($barcode) use ($expected, $counted) {
            $e = $expected[$barcode]->expected_qty ?? 0;
            $c = $counted[$barcode]->counted_qty ?? 0;
            return $e != $c;
        })->map(function ($barcode) use ($expected, $counted, $descriptions) {
            $e = $expected[$barcode] ?? null;
            $c = $counted[$barcode]->counted_qty ?? 0;
            $zones = $counted[$barcode]->zones ?? '-';
            $desc = $descriptions[$barcode] ?? (object) [];

            return (object) [
                'barcode' => $barcode,
                'description' => $desc->description ?? '',
                'codein' => $desc->codein ?? '',
                'colour' => $desc->colour ?? '',
                'size' => $desc->size ?? '',
                'style' => $desc->style ?? '',
                'is_created' => $e?->is_created ?? 0,
                'cost' => $e?->cost ?? 0,
                'expected_qty' => $e?->expected_qty ?? 0,
                'counted_qty' => $c,
                'zones' => $zones,
            ];
        });
    }

    public function collection()
    {
        return $this->data;
    }

    public function headings(): array
    {
        return [
            'Barcode',
            'Description',
            'Codein',
            'Colour',
            'Size',
            'Style',
            'Created',
            'Unit Cost',
            'Expected Qty',
            'Expected Cost',
            'Counted Qty',
            'Counted Cost',
            'Variance Qty',
            'Variance Cost',
            'Zones Found In',
        ];
    }

    public function map($line): array
    {
        $unitCost = $line->cost;
        $expectedQty = $line->expected_qty ?? 0;
        $countedQty = $line->counted_qty ?? 0;

        $expectedCost = $unitCost * $expectedQty;
        $countedCost = $unitCost * $countedQty;
        $varianceQty = $countedQty - $expectedQty;
        $varianceCost = $varianceQty * $unitCost;

        return [
            $line->barcode,
            $line->description,
            $line->codein,
            $line->colour,
            $line->size,
            $line->style,
            $line->is_created ? 'Yes' : 'No',
            $unitCost,
            $expectedQty,
            $expectedCost,
            $countedQty,
            $countedCost,
            $varianceQty,
            $varianceCost,
            $line->zones
        ];
    }
}