<?php

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use App\Models\BankList;
use App\Models\BankDetail;
use App\Models\Equipment;
use App\Models\EquipmentBank;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx; 



class BankListController extends Controller
{
    /**
     * Display a listing of the resource.
     */
    public function index()
    {
        $result = BankList::with(["equipment.category.banks" , "equipment.factory"])->where("creator", Auth::user()->id)->get();
        return response()->json(['data' => $result ], 200);
    }

    /**
     * Show the form for creating a new resource.
     */
    public function create()
    {
        //
    }

    /**
     * Store a newly created resource in storage.
     */
    public function store(Request $request)
    {
        DB::beginTransaction();
        try {
            $user_id = Auth::user()->id;
            $eq_ids = $request->input('name');
            $eq_cat_id = $request->input('eq_cat_id');

            $equipmentBank = EquipmentBank::where("eq_cat_id" , $eq_cat_id)->where('creator', $user_id)->get();
            foreach ($eq_ids as $eq_id) {
                $name = Equipment::where("id",$eq_id)->value("name");
                $bank = BankList::create([
                    'name' => $name . "_自動檢查",
                    'eq_id' => $eq_id,
                    'creator' => Auth::user()->id,
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);
                foreach ($equipmentBank as $detail) {
                    BankDetail::create([
                        'bank_id' => $bank->id,
                        'eq_cat_id' => 1,
                        'code' => $detail->code,
                        'item' => $detail->item,
                        'range' => $detail->range,
                        'range2' => $detail->range2,
                        'unit' => $detail->unit,
                        'risk' => $detail->risk,
                        'sort_id' => $detail->sort_id,
                        'remark' => $detail->remark,
                        'creator' => Auth::user()->id,
                        'created_at' => now(),
                        'updated_at' => now(),
                    ]);
                }
            }
            DB::commit();
        }  catch (Exception $e) {
            DB::rollback();
            throw $e;
        }
    }

    /**
     * Display the specified resource.
     */
    public function show(BankList $bankList)
    {
        //
    }

    /**
     * Show the form for editing the specified resource.
     */
    public function edit(BankList $bankList)
    {
        //
    }

    /**
     * Update the specified resource in storage.
     */
    public function update(Request $request, BankList $bankList)
    {
        //
    }

    /**
     * Remove the specified resource from storage.
     */
    public function destroy(BankList $bankList)
    {
        DB::beginTransaction();
        try {
            $bankList->load('dispatches');
            if ($bankList->dispatches && $bankList->dispatches->count() > 0) {
                return response()->json(["message" => "該檢核表單有建置派工單，無法刪除"], 400);
            }
            $bankList->delete();
            BankDetail::where('bank_id',$bankList->id)->delete();
            DB::commit();
        } catch (Exception $e) {
            DB::rollback();
            throw $e;
        }
    }

    public function getEquipment(Request $request){
        $eq_cat_id = $request->query('categoryId');
        $result = Equipment::with("category.banks")->where('creator', Auth::user()->id)->where("eq_cat_id", $eq_cat_id)->get();

        return response()->json(["data" => $result], 200);
    }

    public function importBank(Request $request){
        /*
            流程如下：
                1. 用設備id抓設備類別的id
                2. 建立banklist資料
                3. 匯入detail資料
        */
        
        DB::beginTransaction();
        try {
            $bank_name = $request->input("bank_name");
            $eq_id = $request->input("equipment_id");
            $eq_cat_id = Equipment::where('id',$eq_id)->value('eq_cat_id');
            //建置BankList資料
            $bank = BankList::create([
                'name' => $bank_name,
                'eq_id' => $eq_id,
                'creator' => Auth::user()->id,
                'created_at' => now(),
                'updated_at' => now(),
            ]);  

            $file = $request->file('BankFile');
            $spreadsheet = IOFactory::load($file->getPathname());
            $worksheet = $spreadsheet->getActiveSheet();
            for($i=0;$i<$spreadsheet->getSheetCount();$i++){
                $worksheet = $spreadsheet->getSheet($i);
                $highestRow = $worksheet->getHighestRow(); // 總行數
                $highestColumn = $worksheet->getHighestColumn(); // 總列數
                $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // e.g. 5
                $nextHighestColumn = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($highestColumnIndex + 1);
        
                $lines = $highestRow - 2;
                if ($lines <= 0) {
                    return response()->json(['error' => 'Excel表格中沒有資料，請根據範例調整'], 400);
                }
                // $lastRow = $worksheet->getHighestDataRow(); // 總行數
                $lastRow = $worksheet->getHighestDataRow('A');
                $dataArray = $worksheet->rangeToArray(
                  'A3:G' . $lastRow,    // The worksheet range that we want to retrieve
                  NULL,        // Value that should be returned for empty cells
                  TRUE,        // Should formulas be calculated (the equivalent of getCalculatedValue() for each cell)
                  TRUE,        // Should values be formatted (the equivalent of getFormattedValue() for each cell)
                  TRUE         // Should the array be indexed by cell row and cell column
                );
                
                //先對EXCEL資料進行驗證
                $requiredFields = ['A' => '項目編號' , 'B' => '檢查項目' , 'F' => "風險等級"];
                $errors= []; $seenCodes = [];
                foreach ($dataArray as $rowIndex => $row) {
                    $rowNumber = $rowIndex; //Excel實際行數
                    
                    foreach ($requiredFields as $columnKey => $columnName) {
                        $value = trim($row[$columnKey] ?? '');
                        if ($value === '') {
                            $errors[] = "第 {$rowNumber} 列的「{$columnName}」欄位為空";
                        }
                    }

                    $code = trim($row['A'] ?? '');

                    // 檢查 Excel 裡是否重複
                    if(in_array($code, $seenCodes)){
                        $errors[] = "第 {$rowNumber} 列的「項目編號」在 Excel 中重複：{$code}";
                    }
                    $seenCodes[] = $code;
                }
                if (!empty($errors)) {
                    return response()->json([
                        'status' => 'error',
                        'message' => '資料驗證失敗',
                        'errors' => $errors
                    ], 422);
                }

                //通過驗證才匯入
                $riskLevel = DB::table('bank_risks')->pluck('id','level')->toArray();
                $maxSort = BankDetail::where('bank_id', $bank->id)->where('eq_cat_id', $eq_cat_id)->max('sort_id');
                $sort_id = $maxSort ? $maxSort + 1 : 1;
                foreach ($dataArray as $key => $value) {
                    $levelName = trim($value['F']);
                    BankDetail::create([
                        'bank_id' => $bank->id,
                        'eq_cat_id' => $eq_cat_id,
                        'code' => trim($value['A']),
                        'item' => $value['B'],
                        'range' => $value['C'],
                        'range2' => $value['D'],
                        'unit' => $value['E'],
                        'risk' => $riskLevel[$levelName],
                        'sort_id' => $sort_id,
                        'remark' => $value['G'],
                        'creator' => Auth::user()->id,
                        'created_at' => now(),
                        'updated_at' => now(),
                    ]);
                    $sort_id++;
                }

            }
            DB::commit();
        } catch (Exception $e) {
            DB::rollback();
            throw $e;
        }
    }

    public function manualBank (Request $request) {
        DB::beginTransaction();
        try {
            $bank_name = $request->input('bank_name');
            $eq_id = $request->input('id');
            BankList::create([
                'name' => $bank_name,
                'eq_id' => $eq_id,
                'creator' => Auth::user()->id,
                'created_at' => now(),
                'updated_at' => now(),
            ]);
            DB::commit();
        } catch (Exception $e) {
            DB::rollback();
            throw $e;
        }
    }
}