<?php

namespace App\Http\Controllers\Api;

use Exception;
use App\Http\Controllers\Controller;
use App\Models\EquipmentBank;
use App\Models\EquipmentCategory;
use App\Models\BankRisk;
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 EquipmentBankController extends Controller
{
    /**
     * Display a listing of the resource.
     */
    public function index(Request $request)
    {
        $eqCatId = $request->query('eq_cat_id');
        if(!$eqCatId){
            return response()->json(['error' => 'eq_cat_id is required'], 400);
        }

        $cate = EquipmentCategory::where('id',$eqCatId)->get();
        $result = EquipmentBank::with('category')->where('creator', Auth::user()->id)->where('eq_cat_id',$eqCatId)->orderBy('sort_id','asc')->get();
        return response()->json(['data' => $result , 'cate' => $cate], 200);
    }

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

    /**
     * Store a newly created resource in storage.
     */
    public function store(Request $request)
    {
        // 
    }

    /**
     * Display the specified resource.
     */
    public function show(EquipmentBank $equipmentBank)
    {
        //
    }

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

    /**
     * Update the specified resource in storage.
     */
    public function update(Request $request, String $id)
    {
        //
    }
    /**
     * Remove the specified resource from storage.
     */
    public function destroy(EquipmentBank $equipmentBank)
    {
        //刪除設備 - 檢查項目
        DB::beginTransaction();
        try {
            $equipmentBank->delete();
            DB::commit();
        } catch (Exception $e) {
            DB::rollBack();
            throw $e;
        }
        return response()->json(['message' => 'EquipmentBank deleted successfully'], 200);

    }

    public function updateOrCrate(Request $request){
        DB::beginTransaction();
        try {
            $items = $request->items;
            $eqCatId = $request->eq_cat_id;

            // 1. 取出目前資料庫中存在的資料
            $existingDetails = EquipmentBank::where('eq_cat_id', $eqCatId)
                ->whereNull('deleted_at')
                ->get();  
            // 2-1. 從前端傳來的 items 中取出 id
            $incomingIds = collect($items)->pluck('id')->filter()->toArray(); // 避免 null id
            // 2-2. 從前端傳來的 items 中取出 code，統一轉為大寫
            $incomingCodes = collect($items)->pluck('code')->map(function ($code) {
                return strtoupper(trim($code));
            })->toArray(); 

            // 3. 刪除資料庫中有，但前端沒傳回來的項目（即被刪除的）
            foreach ($existingDetails as $detail) {
                // if (!in_array(strtoupper($detail->code), $incomingCodes)) {
                //     $detail->delete();
                // }
                if(!in_array($detail->id, $incomingIds)) {
                    $detail->delete();
                }
            }

            $codes = collect($items)->pluck('code')->map(fn($c) => strtoupper(trim($c)));
            if ($codes->duplicates()->isNotEmpty()) {
                return response()->json(['status' => 'error', 'message' => '編號重複'], 422);
            }
            foreach ($items as $key => $item) {
                 $data = [
                    'eq_cat_id' => $item['eq_cat_id'],
                    'code'      => $item['code'],
                    'item'      => $item['item'],
                    'range'     => $item['range'] ?? null,
                    'range2'    => $item['range2'] ?? null,
                    'unit'      => $item['unit'] ?? null,
                    'remark'    => $item['remark'] ?? null,
                    'risk'      => $item['risk'],
                    'sort_id'   => $item['sort_id'],
                    'creator'   => Auth::user()->id,
                ];

                if (isset($item['id'])) {
                    // 有 id 就更新
                    EquipmentBank::updateOrCreate(['id' => $item['id']], $data);
                } else {
                    // 沒有 id 就新增
                    EquipmentBank::create($data);
                }
            }

            DB::commit();
            return response()->json(['status' => 'success']);
        } catch (Exception $e) {
            DB::rollback();
            throw $e;
            return response()->json(['status' => 'error', 'message' => $e->getMessage()], 500);
        }
    }

    public function importEquipmentBank(Request $request)
    {
        /*
            Excel匯入格式由昌裕先製作簡易版，後續有特殊需求再做更改        
        */
        DB::beginTransaction();
        try {
            $eq_cat_id = $request->input('eq_cat_id');
            $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('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 ($code !== '' && EquipmentBank::where('code' , $code)->where('eq_cat_id',$eq_cat_id)->exists()) {
                        $errors[] = "第 {$rowNumber} 列的「項目編號」已存在於資料庫中：{$code}";
                    }
                }
                if (!empty($errors)) {
                    return response()->json([
                        'status' => 'error',
                        'message' => '資料驗證失敗',
                        'errors' => $errors
                    ], 422);
                }

                //通過驗證才匯入
                $riskLevel = DB::table('bank_risks')->pluck('id','level')->toArray();
                $maxSort = EquipmentBank::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']);
                    EquipmentBank::create([
                        '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 exportEquipmentBank(Request $request){
        DB::beginTransaction();
        try {  
            $eq_cat_id = $request->input('eq_cat_id');
            // 查出所有風險對照表
            $riskMap = DB::table('bank_risks')->pluck('level', 'id')->toArray();
            // 替換risk
            $result = EquipmentBank::where("eq_cat_id", $eq_cat_id)->get();
            foreach ($result as &$item) {
                $item->risk = $riskMap[$item->risk] ?? null;
            }
            

            // 匯出設置
            $path = public_path("export.xlsx");
            $spreadsheet = IOFactory::load($path);
            $styleArray = [
                'font' => [
                    'bold' => true,
                ],
                'alignment' => [
                    'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                    'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
                    'indent' => 1,
                    'wrapText' => true,
                ],
                'borders' => [
                    'allBorders' => [
                        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                        'color' => ['argb' => '000000'],
                    ],
                ],
                'fill' => [
                  'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                  'startColor' => ['rgb' => 'DCE6F1'],
                ],
              ];

            $worksheet = $spreadsheet->getSheetByName('Excel');
            $spreadsheet->getActiveSheet()->setCellValue("A1", "項目編號");
            $spreadsheet->getActiveSheet()->setCellValue("B1", "檢查項目");
            $spreadsheet->getActiveSheet()->setCellValue("C1", "標準值下限");
            $spreadsheet->getActiveSheet()->setCellValue("D1", "標準值上限");
            $spreadsheet->getActiveSheet()->setCellValue("E1", "單位");
            $spreadsheet->getActiveSheet()->setCellValue("F1", "風險等級"); 
            $spreadsheet->getActiveSheet()->setCellValue("G1", "備註");
            $spreadsheet->getActiveSheet()->getStyle('A1:G2')->applyFromArray($styleArray);
            $columns = ["A","B","C","D","E","F","G"];
            foreach ($columns as $column) {
              $worksheet->getColumnDimension($column)->setWidth(20);
              $spreadsheet->getActiveSheet()->mergeCells($column."1:".$column."2");  
            }

            //用於定向當前該寫入資料的位置
            $rowCount = 3;
            $startCateRow = $rowCount; // 主項起始行

            foreach ($result as $value) {
                $spreadsheet->getActiveSheet()->setCellValue("A". $rowCount , $value->code);
                $spreadsheet->getActiveSheet()->setCellValue("B". $rowCount , $value->item);
                $spreadsheet->getActiveSheet()->setCellValue("C". $rowCount , $value->range);
                $spreadsheet->getActiveSheet()->setCellValue("D". $rowCount , $value->range2);
                $spreadsheet->getActiveSheet()->setCellValue("E". $rowCount , $value->unit);
                $spreadsheet->getActiveSheet()->setCellValue("F". $rowCount , $value->risk);
                $spreadsheet->getActiveSheet()->setCellValue("G". $rowCount , $value->remark);

                $rowCount++;
            }

            $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
            ob_start();
            $writer->save('php://output');
            $ExcelExport = ob_get_clean();
            $ExportExcelEncode = base64_encode($ExcelExport);
            $response = array(
                'name' => "exportBank",
                'file' => "data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,".$ExportExcelEncode
            );
            return response()->json($response);
        } catch (Exception $e) {
            DB::rollback();
            throw $e;
        }
    }
}