<?php

namespace App\Http\Repositories;

// use App\Models\Chemical_base_inform;

use App\Helpers\UserHelper;
use App\Models\Chemical;
use App\Models\User;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;

class ChemicalBaseRepository
{
    function __construct(protected Chemical $chemicalbase) {}

    function all()
    {
        return $this->chemicalbase->all();
    }

    function store($chemical)
    {
        return $this->chemicalbase->insertGetId($chemical);
    }

    function find($id)
    {
        return $this->chemicalbase->with('SdsBase')->find($id);
    }

    function findcas($casno)
    {
        return $this->chemicalbase->where('cas_no', $casno)->first();
    }

    function update($id, $data)
    {
        return $this->chemicalbase->find($id)->update($data);
    }

    function destroy(array $ids)
    {
        $chemicalbases = $this->chemicalbase->with('SdsBase', 'ChemicalReview')->whereIn('id', $ids)->get();

        foreach ($chemicalbases as $chemicalbase) {

            $chemicalbase->SdsBase()->delete();

            // $chemicalbase->ChemicalReview()->delete();      

            $chemicalbase->delete();
        }


        return true;
    }

    function search($filters, $limit = 30000, $offset = 0)
    {
        $query = $this->chemicalbase->query();

        if (!empty($filters['cas_no'])) {
            $query->where('cas_no', 'like', '%' . $filters['cas_no'] . '%');
        }

        if (!empty($filters['ch_name'])) {
            $query->where('ch_name', 'like', '%' . $filters['ch_name'] . '%');
        }

        if (!empty($filters['en_name'])) {
            $query->where('en_name', 'like', '%' . $filters['en_name'] . '%');
        }

        if (!empty($filters['property'])) {
            $tablename = $filters['property'];

            $query->where($tablename, '1');
        }
        return $query->limit($limit)->offset($offset)->get();
    }

    function companyID()
    {
        $uid = Auth::user()->uuid;
        $member = User::where('uuid', $uid)->first();
        $parentCompanyId = $member->parent ? $member->parent->id : null;

        return $parentCompanyId;
    }

    public function UserCasNo(?string $searchTerm = null, ?array $specificCasNos = null): \Illuminate\Support\Collection
    {
        $query = $this->chemicalbase->select([
            'id', 'cas_no', 'ch_name', 'en_name', 'hazards', 'priority',
            'control', 'pioneer', 'water_poll', 'concerned', 'toxic_moenv','danger'
        ]);

        if (!empty($specificCasNos)) {
            // 情境1: 如果傳入了特定 CAS No. 列表 (用於編輯頁面精確匹配)
            // 執行 whereIn 查詢，取得這些特定 CAS No. 的完整資料，不限制筆數
            $query->whereIn('cas_no', $specificCasNos);

        } elseif ($searchTerm) {
            // 情境2: 如果傳入了關鍵字 (用於前端下拉選單的模糊搜尋)
            $query->where(function ($q) use ($searchTerm) {
                $q->where('cas_no', 'like', '%' . $searchTerm . '%')
                  ->orWhere('ch_name', 'like', '%' . $searchTerm . '%')
                  ->orWhere('en_name', 'like', '%' . $searchTerm . '%');
            });
            // 模糊搜尋只限制回傳前 100 筆資料
            $query->limit(100);
        } else {
            // 情境3: 如果沒有任何查詢參數 (例如，首次載入新增頁面時)
            // 預設回傳前 100 筆資料，或者您可以選擇回傳空集合
            $query->limit(100);
        }

        return $query->get();
    }

    function useReport($id)
    {
        return $this->chemicalbase->select('listNo', 'ch_name')->where('id', $id)->first();
    }

    public function getAllWithListNo()
    {
        return $this->chemicalbase->where('listNo', '!=', '')->get();
    }
}
