<?php

namespace Modules\Product\Repositories;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Mcamara\LaravelLocalization\Facades\LaravelLocalization;
use Modules\Product\Enums\ProductStockStatusEnum;
use Modules\Product\Interfaces\ProductRepositoryInterface;
use Modules\Product\Models\Product;
use Modules\Product\Models\ProductAttribute;
use Modules\Product\Models\ProductDescription;
use Modules\Product\Models\ProductStock;
use Modules\Product\Models\ProductTag;
use Modules\Product\Models\ProductWarehouse;

class ProductRepository implements ProductRepositoryInterface
{
    public function get(array $request)
    {
        $builder = Product::query()
            ->join('product_descriptions AS sd', 'products.id', 'sd.product_id')
            ->join('languages', 'languages.id', 'sd.language_id')
            ->where('local', LaravelLocalization::getCurrentLocale())
            ->select(['sd.title', 'products.*']);
        return $builder;
    }

    public function getStock(array $request)
    {
        return ProductStock::orderBy('id','DESC');
    }

    public function getItem(int $id)
    {
        return ProductDescription::where('product_id', $id)
            ->join('languages', 'languages.id', 'product_descriptions.language_id')
            ->join('products', 'products.id', 'product_descriptions.product_id')
            ->select(['product_descriptions.*', 'languages.local', 'products.*'])->get();
    }

    public function checkItem(int $id)
    {
        return Product::find($id);
    }


    public function getItemTrashed(int $id)
    {
        $product = Product::onlyTrashed()->find($id);
        return $product;
    }


    public function getItemByCode(string $code)
    {
        return Product::where('code', $code)->first();
    }

    public function getStockItem(int $id)
    {
        return ProductStock::find($id);
    }

    public function create(array $request): Product
    {
        $product = Product::create($request);
        return $product;
    }

    public function update(array $request, ?Product $product): Product
    {
        $product->update($request);

        return $product;
    }

    public function saveData(array $request, int $product_id): void
    {
        ProductDescription::where('product_id', $product_id)->delete();
        foreach (languages() as $lang) {
            $data = [
                'product_id' => $product_id,
                'language_id' => $lang->id,
                'title' => $request['title_' . $lang->local],
            ];
            ProductDescription::create($data);
        }
    }

    public function checkUUIDExists($uuid)
    {
        return ProductStock::where('uuid',$uuid)->first();
    }

    public function createStock(array $request)
    {
        return ProductStock::create($request);
    }

    public function StockBalance(array $request)
    {
        return ProductStock::query()
//            ->join('product_descriptions as pd','product_stocks.product_id','pd.product_id')
//            ->join('languages', 'languages.id', 'pd.language_id')
//            ->where('local', LaravelLocalization::getCurrentLocale())
//            ->select(['pd.title','product_stocks.*'])
            ->whereDate('date','>=',$request['start_date'])
            ->whereDate('date','<=',$request['end_date'])
            ->where('canteen_id',$request['canteen_id'])
            ->when(isset($request['product_id']), function ($query) use ($request) {
                $query->where('product_id', $request['product_id']);
            })
            ->when(isset($request['category_id']), function ($query) use ($request) {
                $query->where('category_id', $request['category_id']);
            })
            ->select(
                'product_id',
                DB::raw('SUM(CASE WHEN status = 1 THEN `count` ELSE 0 END) as qty_in'),
                DB::raw('SUM(CASE WHEN status = 1 THEN `price` ELSE 0 END) as price_in'),
                DB::raw('SUM(CASE WHEN status = 2 THEN `count` ELSE 0 END) as qty_out'),
                DB::raw('SUM(CASE WHEN status = 2 THEN `price` ELSE 0 END) as price_out'),
//                DB::raw('SUM(balance) as balance'),
//                DB::raw('SUM(balance_price) as balance_price'),
                DB::raw('SUBSTRING_INDEX(GROUP_CONCAT(balance ORDER BY date DESC), ",", 1) as balance'),
                DB::raw('SUBSTRING_INDEX(GROUP_CONCAT(balance_price ORDER BY date DESC), ",", 1) as balance_price'),
                DB::raw('MIN(date) as first_date'), // optional: get the first date
                DB::raw('SUBSTRING_INDEX(GROUP_CONCAT(original_count ORDER BY date ASC), ",", 1) as original_count'),
                DB::raw('SUBSTRING_INDEX(GROUP_CONCAT(original_price ORDER BY date ASC), ",", 1) as original_price')
            )
            ->groupBy('product_id')
            ->get();
    }

    public function ReportSales(array $request)
    {
        return ProductStock::query()
            ->whereDate('date','>=',$request['start_date'])
            ->whereDate('date','<=',$request['end_date'])
            ->where('canteen_id',$request['canteen_id'])
            ->when(isset($request['product_id']), function ($query) use ($request) {
                $query->where('product_id', $request['product_id']);
            })
            ->when(isset($request['category_id']), function ($query) use ($request) {
                $query->where('category_id', $request['category_id']);
            })
            ->where('status',ProductStockStatusEnum::MINUS->value)
            ->select(
                'product_id',
                DB::raw('SUM(count) as qty'),
                DB::raw('SUM(price) as price'),
            )
            ->groupBy('product_id')
            ->get();
    }

    /**
     * Remove the specified product from storage.
     * @param Product $product
     * @return void
     */
    public function destroy(Product $product): void
    {
        $product->delete();
    }

    /**
     * Remove several products by IDs.
     *
     * @param array $request
     * @return void
     */
    public function destroyAll(array $ids): void
    {
        Product::whereIn('id', $ids)->delete();
    }

    /**
     * Restore the specified product from storage.
     *
     * @param Product $product
     * @return void
     */
    public function restore(Product $product): void
    {
        $product->restore();
    }

    /**
     * Restore several products by IDs.
     *
     * @param array $request
     * @return void
     */
    public function restoreAll(array $ids): void
    {
        Product::whereIn('id', $ids)->onlyTrashed()->restore();
    }
}
