<?php

namespace App\Http\Controllers\Api;

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

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

class BankDetailController extends Controller
{
    /**
     * Display a listing of the resource.
     */
    public function index(Request $request)
    {
        $BankId = $request->query('bank_id');
        if(!$BankId){
            return response()->json(['error' => 'BankId is required'], 400);
        }
        // $result = BankDetail::with('bankList')->where('bank_id',$BankId)->orderBy('id','asc')->get();
        $result = BankDetail::where('bank_id',$BankId)->orderBy('sort_id','asc')->get();
        $bank = BankList::with('equipment.category')->where('id',$BankId)->get();

        return response()->json(['data' => $result, 'bank' => $bank], 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(BankDetail $bankDetail)
    {
        //
    }

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

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

    /**
     * Remove the specified resource from storage.
     */
    public function destroy(BankDetail $bankDetail)
    {
        DB::beginTransaction();
        try {
            $bankDetail->delete();
            DB::commit();
        } catch (Exception $e) {
            DB::rollback();
            throw $e;
        }
    }

    public function updateOrCrate(Request $request){
        DB::beginTransaction();
        try {
            $items = $request->items;
            $bankId = $request->bank_id;
            $eqCatId = $request->eq_cat_id;
            // 1. 取出目前資料庫中存在的資料
            $existingDetails = BankDetail::where('bank_id', $bankId)
                ->where('eq_cat_id', $eqCatId)
                ->whereNull('deleted_at')
                ->get();  

            // 2-1. 從前端傳來的 items 中取出 id
            $incomingIds = collect($items)->pluck('id')->filter()->toArray(); // 避免 null id
            // 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 = [
                    'bank_id'   => $item['bank_id'],
                    '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 就更新
                    BankDetail::updateOrCreate(['id' => $item['id']], $data);
                } else {
                    // 沒有 id 就新增
                    BankDetail::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 exportBankDetail(Request $request){
        DB::beginTransaction();
        try {  
            $bank_id = $request->input('bank_id');
            $eq_cat_id = $request->input('eq_cat_id');
            // 查出所有風險對照表
            $riskMap = DB::table('bank_risks')->pluck('level', 'id')->toArray();
            // 替換risk
            $result = BankDetail::where("bank_id", $bank_id)->where('eq_cat_id', $eq_cat_id)->where('deleted_at','=',null)->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;
        }
    }
}