<?php

namespace App\Http\Controllers\Endline;

use App\Http\Controllers\Controller;

use Illuminate\Http\Request;

use Carbon\Carbon;

use Illuminate\Support\Facades\DB;

use Illuminate\Support\Facades\Storage;

use Illuminate\Support\Str;

use Illuminate\Support\Facades\Http;

use Illuminate\Support\Facades\Log;

use Illuminate\Support\Facades\Mail;

use Illuminate\Support\Facades\Session;

use Symfony\Component\HttpFoundation\BinaryFileResponse;
use Symfony\Component\HttpFoundation\StreamedResponse;
use Illuminate\Support\Facades\Cache;

class EndlineController extends Controller
{
 
    public function endline(Request $request)
    {
        $startTime = microtime(true);
        $ip = request()->ip();

        try {

            // ✅ 1. Try cache
            $lines = Cache::get('endline_lines');

            // ✅ 2. If cache miss → DB call
            if (!$lines) {

                $lines = DB::connection('sqlsrv')->select("
                    SELECT Line_No, Zone 
                    FROM Woven.dbo.GLOVES_LINE_MASTER 
                    WHERE UNIT_CODE=10 AND LIVE='y' 
                    GROUP BY Zone, Line_No
                ");

                // store cache (10 mins)
                Cache::put('endline_lines', $lines, 600);
            }

        } catch (\Exception $e) {

            // ❌ Cache/DB error → log with ping
            $pingResult = shell_exec("ping -n 2 $ip");

            writeLog("ERROR", $e->getMessage(), 0, $pingResult);

            $lines = [];
        }

        // ✅ 3. Execution time check
        $duration = microtime(true) - $startTime;

        if ($duration > 10) {

            $pingResult = shell_exec("ping -n 2 $ip");

            writeLog("SLOW", "Execution time exceeded 10 sec", $duration, $pingResult);
        }

        return view('Quality.Endline', compact('lines'));
    }

    public function lineDetails(Request $request)
    {
        $unit = $request->unit;
        $lines = DB::connection('sqlsrv')->select("
            SELECT Line_No, Zone 
            FROM Woven.dbo.GLOVES_LINE_MASTER 
            WHERE UNIT_CODE=? AND LIVE='y' 
            GROUP BY Zone, Line_No
        ", [$unit]);
        return response()->json([
            'success' => true,
            'data' => $lines
        ]);
    }

	public function logout(Request $request)
    {
        
        $request->session()->forget('endline_checker');
         $request->session()->flush(); 

        return redirect()->route('endline.home');
    }

    public function endline_tablepage(Request $request)
    {

  
         $emp_id   = Session::get('endline_checker.employee_id');
        $line_no  =  Session::get('endline_checker.line_no');

        
        $unit     = Session::get('endline_checker.outline');

        if ($unit == 17) {
            $unit = 14;
        }

        $fromdate = $request->fromdate ?? Carbon::now()->format('Y-m-d');
        $todate   = $request->todate ?? Carbon::now()->format('Y-m-d');
       
       

        $resultdata = DB::connection('sqlsrv')->select(
            "EXEC dbo.usp_Gut_Endline_QC_Summary
                @fdate = ?, 
                @Tdate = ?, 
                @Line  = ?, 
                @Unit  = ?",
            [$fromdate, $todate, $line_no, $unit]
        );

         $resultdata_hourly = DB::connection('sqlsrv')->select("
            EXEC [CHENNAI_ERP].[dbo].[Get_Endline_Hourly_Summary] @Line = ?,@Unit = ? 
        ", [$line_no, $unit]);
        //dd($resultdata);
       return view('Quality.Endline_tablepage', compact('resultdata', 'resultdata_hourly'));
    }

    Public function endlinetablepage(Request $request)
    {


         $emp_id   = Session::get('endline_checker.employee_id');
        $line_no  =  Session::get('endline_checker.line_no');
        $unit     = Session::get('endline_checker.outline');

     
        
       $fromdate = $request->fromdate ?? Carbon::now()->format('Y-m-d');
        $todate   = $request->todate ?? Carbon::now()->format('Y-m-d');
      
       

        $resultdata = DB::connection('sqlsrv')->select(
            "EXEC dbo.usp_Gut_Endline_QC_Summary
                @fdate = ?, 
                @Tdate = ?, 
                @Line  = ?, 
                @Unit  = ?",
            [$fromdate, $todate, $line_no, $unit]
        );

          $resultdata_hourly = DB::connection('sqlsrv')->select("
            EXEC [CHENNAI_ERP].[dbo].[Get_Endline_Hourly_Summary] @Line = ?,@Unit = ? 
        ", [$line_no, $unit]);
        //dd($resultdata);
       return view('Quality.Endline_tablepage', compact('resultdata', 'resultdata_hourly'));
    }

    public function endline_selection()
    { 

        $sessionid = Session::get('endline_checker.employee_id');

        
        if($sessionid == ''){
            return redirect()->route('endline.home');
        }

        return view('Quality.Endline_selection');
    }

    public function endline_defectselection()
    {
        $deflist = DB::connection('sqlsrv')
            ->table('woven.dbo.Woven_Rej_Master')
            ->whereNotNull('defect_code')
            ->orderBy('defect_code', 'asc')
            ->orderBy('Rej_Type', 'asc')
            ->get();
        return view('Quality.Endline_defectselection', compact('deflist'));
    }

    public function endline_finalselection()
    {
        return view('Quality.Endline_finalselection');
    }

    public function checkerDetails(Request $request)
    {
        // Validate that employee_id is present
        $employeeId = $request->input('employee_id');
        $lineNo = $request->input('line_no');
        $outline = $request->input('outline');
        $groupbatch = $request->input('groupbatch');

       

        $employeeData = null;

        // 1. Logic to fetch Employee details
        if (strlen($employeeId) == 15) {
            $employeeData = DB::connection('sqlsrv')
                ->table('VAAHINI_ERP_GAINUP.dbo.employeemas')
                ->select('Emplno as employee_id', 'Name as name', 'Deptcode as department', 'Unit as unit_code')
                ->where('Emplno', $employeeId)
                ->whereIn('Unit', [9, 10, 17, 14])
                ->first();
        } elseif (strlen($employeeId) == 24) {
            $employeeData = DB::connection('sqlsrv')->table('VAAHINI_ERP_GAINUP.dbo.employeemas as a')
            ->join('woven.dbo.Woven_Checker_Employee_RFID_Mapping as b', 'a.Emplno', '=', 'b.EmplNo')
            ->select(
                'a.Emplno as employee_id',
                'a.Name as name',
                'a.Deptcode as department',
                'a.Unit as unit_code'
            )
            ->where('b.RFID', $employeeId)
           
            ->first();
        }

        // 2. Check if employee exists
        if (!$employeeData) {
            return response()->json([
                'success' => false,
                'message' => 'Employee not found or unauthorized Unit'
            ], 404);
        }

        // 3. Upsert (Update or Insert) User Details
        // Using updateOrInsert prevents manual if/else checks
        DB::connection('sqlsrv')
            ->table('gut_qulty_endline_user_details')
            ->updateOrInsert(
                ['emp_no' => $employeeData->employee_id], // Unique key to check
                [
                    'unit_code'  => $employeeData->unit_code,
                    'status'     => 1,
                    'updated_at' => now(),
                    // 'created_at' => now() // updateOrInsert handles created_at intelligently if using Eloquent, 
                                            // but for Query Builder, you might need a custom check if created_at is mandatory.
                ]
            );

        // 4. Store in Session
        Session::put('endline_checker', [
            'employee_id' => $employeeData->employee_id,
            'name'        => $employeeData->name,
            'department'  => $employeeData->department,
            'unit_code'   => $employeeData->unit_code,
            'line_no'     => $lineNo,
            'outline'     => $employeeData->unit_code,
            'groupbatch'  => $groupbatch,
        ]);

        Session::put('endline_groupbatch', [
            'name' => $groupbatch
        ]);

        return response()->json([
            'success' => true,
            'data' => $employeeData
        ]);
    }


    public function get_defect_list(Request $request)
    {
        try {
            $garmentId = $request->garmentId;
            
            if (!$garmentId) {
                return response()->json([
                    'status' => 'error',
                    'message' => 'RFID is required'
                ], 400);
            }

            $defects = DB::connection('sqlsrv')
                ->table('Gut_qulty_endline_summary as s')
                ->join('Gut_End_line_Rejection_Details as d', 's.id', '=', 'd.MasterID')
                ->join('woven.dbo.Woven_Rej_Master as m', 'd.DefectID', '=', 'm.Rowid')
                ->where('s.id', function ($query) use ($garmentId) {
                    $query->select(DB::raw('MAX(id)'))
                        ->from('Gut_qulty_endline_summary')
                        ->where('Rfid', $garmentId);
                })
                ->pluck('m.Rej_Type')
                ->filter() 
                ->implode(', ');

                
                
            return response()->json([
                'status' => 'ok',
                'data' => [
                    'defect_list' => $defects ?: null
                ]
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'status' => 'error',
                'message' => 'Failed to fetch defect list',
                'error' => $e->getMessage()
            ], 500);
        }
    }
    public function convertRFIDtoEAN(Request $request)
    {
        try {
            $tagID = $request->tagID ?? '0';
        
            $unit =$request->unitno;
        $lineno = $request->lineNo ?? '0';;

            $rfidCheck = DB::connection('sqlsrv')->select(
                "SELECT TOP 1 Result FROM woven.dbo.Line_Prod_Details WHERE RFID = ?",
                [$tagID]
            );

            if (!empty($rfidCheck)) {
                // Access the first row of the result array
                $row = $rfidCheck[0];
                
                // Check the actual value of the 'Result' column
                if (isset($row->Result) && trim($row->Result) === 'PA') {
                    $message = 'RFID already scanned (PASS)';
                } else {
                    // Defaults to FAIL if Result is not 'PA' or is missing
                    $message = 'RFID already scanned (FAIL)';
                }

                return response()->json([
                    'status' => 'error',
                    'message' => $message
                ], 400);
            }

            if (!$tagID) {
                return response()->json([
                    'status' => 'error',
                    'message' => 'Tag ID is required'
                ], 400);
            }

            // ⚡ EPC → EAN
            $part1 = substr($tagID, 2, 7);
            $part2 = substr($tagID, 8, 7);

            $bin1 = '';
            foreach (str_split($part1) as $h) {
                $bin1 .= str_pad(decbin(hexdec($h)), 4, '0', STR_PAD_LEFT);
            }

            $bin2 = '';
            foreach (str_split($part2) as $h) {
                $bin2 .= str_pad(decbin(hexdec($h)), 4, '0', STR_PAD_LEFT);
            }

            $num4 = bindec(substr($bin1, 6, 20));
            $num9 = bindec(substr($bin2, 2, 24));

            $base = str_pad($num4, 7, '0', STR_PAD_LEFT) .
                    str_pad($num9, 6, '0', STR_PAD_LEFT);

            $odd = $even = 0;
            foreach (str_split($base) as $i => $d) {
                ($i % 2 === 0) ? $odd += $d : $even += $d;
            }

            $check = (10 - (($odd * 3 + $even) % 10)) % 10;
            $ean = substr($base . $check, 1, 13);

            // ⚡ Article Fetch
        


            $row = DB::connection('sqlsrv')
        ->table('woven.dbo.Decathlon_Artical_Excel as A')
        ->leftJoin('woven.dbo.Garments_item_master as B', 'A.Item Code', '=', 'B.itemcode')
        ->leftJoin('woven.dbo.Size as C', 'B.sizeid', '=', 'C.sizeid')
        ->select([
            DB::raw('A.[EAN Code] as ean'),
            DB::raw('C.[Size] as size'),
            DB::raw('A.[Item Code] as itemcode'),
            DB::raw('A.[Model Code] as model'),
            DB::raw('A.[cc] as IMMAN'),
            DB::raw('A.[Buyer Color] as color'),
            DB::raw('A.[Buyer Model Description] as ArticleNO'),
            DB::raw('A.[PCB] as PCB')
        ])
        ->where('A.EAN Code', $ean)
        ->first();

            


            if (!$row) {
                return response()->json([
                    'status' => 'error',
                    'message' => 'Invalid RFID / EAN not found-1'
                ], 404);
            }

            // Cleanly process the CC extraction from Buyer Size before hitting the Stored Procedure
            // if (!empty($row->size) && preg_match('/CC:\s*([A-Za-z0-9]+)/i', $row->size, $matches)) {
            //     $row->IMMAN = $matches[1];
            // } else {
            //     $row->IMMAN = '0'; // Default fallback
            // }

            // Clean trailing Excel floats (.0) so SQL Server BIGINT casting doesn't crash on "8760120.0"
            $safeModel = preg_replace('/\.0+$/', '', (string)$row->model);
            $safeItem  = preg_replace('/\.0+$/', '', (string)$row->itemcode);

            // ⚡ Stored Procedure Call
            $bundleData = DB::connection('sqlsrv')->select(
                "EXEC dbo.GetBundleWiseProduction
                    @UnitCode = ?, 
                    @LineNo = ?, 
                    @CCNo = ?, 
                    @Model = ?, 
                    @Size = ?",
                [
                    $unit,
                    $lineno,
                    $row->IMMAN,
                    $safeModel,
                    $row->size
                ]
            );

            if($bundleData == null){
                $bundleData = DB::connection('sqlsrv')->select(
                "EXEC dbo.GetBundleWiseProduction
                    @UnitCode = ?, 
                    @LineNo = ?, 
                    @CCNo = ?, 
                    @Model = ?, 
                    @Size = ?",
                [
                    $unit,
                    $lineno,
                    $row->IMMAN,
                    $safeModel,
                    $row->size
                ]
            );
                return response()->json([
                    'status' => 'error',
                    'message' => 'Invalid RFID / EAN not found',
                    'Eancode' => $ean
                ], 404);
            }

            // ✅ Final response data format designed to hydrate Javascript requirements
            $resultdata = [
                'id' => '',
                'IMMAN' => $row->IMMAN,
                'ccno' => $row->IMMAN,
                'model' => $safeModel,
                'color' => $row->color,
                'item' => $safeItem,
                'size' => $row->size,
                'bundle' => $bundleData ,
                'Eancode' => $ean,
            ];

            

            return response()->json([   
                'status' => 'ok',
                'ean' => $ean,
                'data' => $resultdata,
            ]);

        } catch (\Exception $e) {

            \Log::error('RFID Conversion Error', [
            'tagID'   => $request->tagID,
            'ean' => $ean,
            'message' => $e->getMessage(),
            'file'    => $e->getFile(),
            'line'    => $e->getLine(),
            'trace'   => $e->getTraceAsString()
        ]);


            return response()->json([
                'status' => 'error',
                'message' => 'Something went wrong',
                'error' => $e->getMessage()
            ], 500);
        }
    }

    public function convertRFIDtoEAN_test(Request $request)
    {
        try {
            $tagID = $request->tagID ?? '0';
        
            $unit =$request->unitno;
        $lineno = $request->lineNo ?? '0';;

            $rfidCheck = DB::connection('sqlsrv')->select(
                "SELECT TOP 1 Result FROM woven.dbo.Line_Prod_Details WHERE RFID = ?",
                [$tagID]
            );

            // if (!empty($rfidCheck)) {
            //     // Access the first row of the result array
            //     $row = $rfidCheck[0];
                
            //     // Check the actual value of the 'Result' column
            //     if (isset($row->Result) && trim($row->Result) === 'PA') {
            //         $message = 'RFID already scanned (PASS)';
            //     } else {
            //         // Defaults to FAIL if Result is not 'PA' or is missing
            //         $message = 'RFID already scanned (FAIL)';
            //     }

            //     return response()->json([
            //         'status' => 'error',
            //         'message' => $message
            //     ], 400);
            // }

            if (!$tagID) {
                return response()->json([
                    'status' => 'error',
                    'message' => 'Tag ID is required'
                ], 400);
            }

            // ⚡ EPC → EAN
            $part1 = substr($tagID, 2, 7);
            $part2 = substr($tagID, 8, 7);

            $bin1 = '';
            foreach (str_split($part1) as $h) {
                $bin1 .= str_pad(decbin(hexdec($h)), 4, '0', STR_PAD_LEFT);
            }

            $bin2 = '';
            foreach (str_split($part2) as $h) {
                $bin2 .= str_pad(decbin(hexdec($h)), 4, '0', STR_PAD_LEFT);
            }

            $num4 = bindec(substr($bin1, 6, 20));
            $num9 = bindec(substr($bin2, 2, 24));

            $base = str_pad($num4, 7, '0', STR_PAD_LEFT) .
                    str_pad($num9, 6, '0', STR_PAD_LEFT);

            $odd = $even = 0;
            foreach (str_split($base) as $i => $d) {
                ($i % 2 === 0) ? $odd += $d : $even += $d;
            }

            $check = (10 - (($odd * 3 + $even) % 10)) % 10;
            $ean = substr($base . $check, 1, 13);

            // ⚡ Article Fetch
        


            $row = DB::connection('sqlsrv')
        ->table('woven.dbo.Decathlon_Artical_Excel as A')
        ->leftJoin('woven.dbo.Garments_item_master as B', 'A.Item Code', '=', 'B.itemcode')
        ->leftJoin('woven.dbo.Size as C', 'B.sizeid', '=', 'C.sizeid')
        ->select([
            DB::raw('A.[EAN Code] as ean'),
            DB::raw('C.[Size] as size'),
            DB::raw('A.[Item Code] as itemcode'),
            DB::raw('A.[Model Code] as model'),
            DB::raw('A.[cc] as IMMAN'),
            DB::raw('A.[Buyer Color] as color'),
            DB::raw('A.[Buyer Model Description] as ArticleNO'),
            DB::raw('A.[PCB] as PCB')
        ])
        ->where('A.EAN Code', $ean)
        ->first();

            


            if (!$row) {
                return response()->json([
                    'status' => 'error',
                    'message' => 'Invalid RFID / EAN not found-1'
                ], 404);
            }

            // Cleanly process the CC extraction from Buyer Size before hitting the Stored Procedure
            // if (!empty($row->size) && preg_match('/CC:\s*([A-Za-z0-9]+)/i', $row->size, $matches)) {
            //     $row->IMMAN = $matches[1];
            // } else {
            //     $row->IMMAN = '0'; // Default fallback
            // }

            // Clean trailing Excel floats (.0) so SQL Server BIGINT casting doesn't crash on "8760120.0"
            $safeModel = preg_replace('/\.0+$/', '', (string)$row->model);
            $safeItem  = preg_replace('/\.0+$/', '', (string)$row->itemcode);

            // ⚡ Stored Procedure Call
            $bundleData = DB::connection('sqlsrv')->select(
                "EXEC dbo.GetBundleWiseProduction
                    @UnitCode = ?, 
                    @LineNo = ?, 
                    @CCNo = ?, 
                    @Model = ?, 
                    @Size = ?",
                [
                    $unit,
                    $lineno,
                    $row->IMMAN,
                    $safeModel,
                    $row->size
                ]
            );

            if($bundleData == null){
                $bundleData = DB::connection('sqlsrv')->select(
                "EXEC dbo.GetBundleWiseProduction
                    @UnitCode = ?, 
                    @LineNo = ?, 
                    @CCNo = ?, 
                    @Model = ?, 
                    @Size = ?",
                [
                    $unit,
                    $lineno,
                    $row->IMMAN,
                    $safeModel,
                    $row->size
                ]
            );
                return response()->json([
                    'status' => 'error',
                    'message' => 'Invalid RFID / EAN not found',
                    'Eancode' => $ean
                ], 404);
            }

            // ✅ Final response data format designed to hydrate Javascript requirements
            $resultdata = [
                'id' => '',
                'IMMAN' => $row->IMMAN,
                'ccno' => $row->IMMAN,
                'model' => $safeModel,
                'color' => $row->color,
                'item' => $safeItem,
                'size' => $row->size,
                'bundle' => $bundleData ,
                'Eancode' => $ean,
            ];

            

            return response()->json([   
                'status' => 'ok',
                'ean' => $ean,
                'data' => $resultdata,
            ]);

        } catch (\Exception $e) {

            \Log::error('RFID Conversion Error', [
            'tagID'   => $request->tagID,
            'ean' => $ean,
            'message' => $e->getMessage(),
            'file'    => $e->getFile(),
            'line'    => $e->getLine(),
            'trace'   => $e->getTraceAsString()
        ]);


            return response()->json([
                'status' => 'error',
                'message' => 'Something went wrong',
                'error' => $e->getMessage()
            ], 500);
        }
    }

    public function bundelCheckStatus(Request $request)
    {
        $ccno   = $request->ccno ;
        $model  = $request->model ;
        $size   = $request->size ;
        $lineno = $request->line;
        $empno  = $request->empno ?? null;
        $unit   = $request->unit;
        $type = (int) $request->type;
        $RFID = $request->garmentId;
        $ean_code = $request->Ean_no ?? $request->ean_no ?? '';
        $defects = $request->input('defects', []);

        $rej_type_id = !empty($defects)
            ? collect($defects)->pluck('id')->implode(',')
            : '';

        $rej_type_id_pass_first = !empty($defects)
            ? $defects[0]['id']
            : '';



        $currendate = date('Y-m-d');

        try {


        // dd($request->all());

            DB::connection('sqlsrv')->beginTransaction();
        

            // 🔹 1. Get Bundle
            $bundleData = DB::connection('sqlsrv')->select(
                "EXEC dbo.GetBundleWiseProduction 
                @UnitCode=?, @LineNo=?, @CCNo=?, @Model=?, @Size=?",
                [$unit, $lineno, $ccno, $model, $size]
            );

            if (empty($bundleData)) {
                return response()->json([
                    'status' => 'error',
                    'message' => 'No bundle data found'
                ], 404);
            }

            $bundle = end($bundleData);

            // 🔹 Extract bundle values
            $bundle_no = $bundle->Bundle_No;
            $sizeid    = $bundle->Sizeid;
            $buyerpo   = $bundle->BuyerPoNo;
            $cut_no    = $bundle->Cut_Order_No;
            $size      = $bundle->Size;
            $season    = $bundle->season ?? '';
            $bal_qty   = $bundle->Bal_Qty;

        if($type == 1 || $type == 3){

            $insertSewingProduction = DB::connection('sqlsrv')->select(
                "EXEC dbo.InsertSewingProduction
                    @Unit = ?, 
                    @LineNo = ?, 
                    @EmpNo = ?, 
                    @BundleNo = ?, 
                    @BuyerPoNo = ?, 
                    @CutOrdNo = ?, 
                    @Sizeid = ?, 
                    @Bal_Qty = ?,
                    @RFID = ?,
                    @Type = ?,
                    @Reject_id = ?,
                    @EanCode = ?",  
                
                [
                    $unit,
                    $lineno,
                    $empno,
                    $bundle_no,
                    $buyerpo,
                    $cut_no,
                    $sizeid,
                    $bal_qty,
                    $RFID,
                    $type,
                    $rej_type_id_pass_first,
                    $ean_code,
                
                
                ]
            );

        
            //dd($insertSewingProduction);

            if($insertSewingProduction[0]->status == 0){

        
                return response()->json([
                    'status' => false,
                    'message' => $insertSewingProduction[0]->message
                ], 500);
            }

        
        }
            

            // 🔹 7. Get Constraint ID
            $constraint = DB::connection('sqlsrv')
                ->table('woven.dbo.Woven_Production_Constraint_Tbl')
                ->where('Bundle_NO', $bundle_no)
                ->where('BuyerPoNo', $buyerpo)
                ->where('Cut_Ord_No', $cut_no)
                ->where('CCNo', $ccno)
                ->where('Sizeid', $sizeid)
                ->orderByDesc('ROWID')
                ->first();

            $constraint_id = $constraint->ROWID ?? null;

        
        

            $pass_status = match ($type) {
                1 => 'PASS',
                2 => 'REWORK',
                3 => 'REJECT',
                default => 'UNKNOWN'
            };

        

            // 🔹 10. Insert into summary table
        $summary_id = DB::connection('sqlsrv')
                ->table('Gut_qulty_endline_summary')
                ->insertGetId([
                    'Rfid'                     => $RFID,
                    'bundel_no'               => $bundle_no,
                    'line'                    => $lineno,
                    'bal_qty'                 => $bal_qty,
                    'sizeid'                  => $sizeid,
                    'season'                  => $season,
                    'sewing_prod_id'          => $insertSewingProduction[0]->master_id ?? null,
                    'sewing_prod_detail_id'   => $insertSewingProduction[0]->sew_id ?? null,
                    'sewing_constraint_id'    => $constraint_id,
                    'pass_status'             => $pass_status,
                    'created_at'              => now(),
                    'created_by'              => $empno,
                    'unit'                    => $unit,
                
                ]);

        if (in_array($type, [2, 3]) && !empty($defects)) {

                foreach ($defects as $defect) {
                    DB::connection('sqlsrv')
                        ->table('Gut_End_line_Rejection_Details')
                        ->insert([
                        
                            'MasterID'     => $summary_id,
                            'DefectID'      => $defect['id'],
                            'Defect_Status' => strtoupper(substr($defect['severity'], 0, 3)),
                            'Def_Type'      => $type == 2 ? 'REW' : 'REJ',
                        ]);
                }
            }

            DB::connection('sqlsrv')->commit();

            return response()->json([
                'status' => 'ok',
                'message' =>  'Success',
                'bundle' => [
                    'Bundle_No'   => $bundle_no,
                    'BuyerPoNo'   => $buyerpo,
                    'Cut_Order_No'=> $cut_no,
                    'Bal_Qty'     => $bal_qty
                ],
                'master_id' => $insertSewingProduction[0]->master_id ?? null,
                'sew_id'    => $insertSewingProduction[0]->sew_id ?? null,
                'constraint_id' => $constraint_id
            ]);


            return response()->json([
                    'status' => false,
                    'message' => $insertSewingProduction[0]->message
                ], 500);

        } catch (\Exception $e) {

            DB::connection('sqlsrv')->rollBack();

            return response()->json([
                'status' => 'error',
                'message' => $e->getMessage()
            ], 500);
        }
    } 

    public function defect_save(Request $request)
    {
        dd($request->all());
    }

    public function updateDefectCode()
    {
        try {

            $data = [
                ["defect"=>"BARTACK / பார்டாக்","defect_group"=>"Sewing","slno"=>"Main","prod_type"=>"Tapping / Non Taping"],
                ["defect"=>"BROKEN STITCH / உடைந்த தையல்","defect_group"=>"Sewing","slno"=>"Main","prod_type"=>"Tapping / Non Taping"],
                ["defect"=>"BUBBLES / பப்ள்ஸ்","defect_group"=>"Taping","slno"=>"Taping","prod_type"=>"Tapping"],
                ["defect"=>"BURNT THREAD","defect_group"=>"Taping","slno"=>"Taping","prod_type"=>"Tapping"],
                ["defect"=>"CENTER OUT / சென்டர் அவுட்","defect_group"=>"Taping","slno"=>"Taping","prod_type"=>"Tapping"],
                ["defect"=>"DAMAGE","defect_group"=>"Sewing","slno"=>"Main","prod_type"=>"Tapping / Non Taping"],
                ["defect"=>"DOUBLE TAPE / டபுள் டேப்","defect_group"=>"Taping","slno"=>"Taping","prod_type"=>"Tapping"],
                ["defect"=>"ELASTIC MISTAKE / எலாஸ்டிக் மிஸ்டேக்","defect_group"=>"Sewing","slno"=>"Others","prod_type"=>"Tapping / Non Taping"],
                ["defect"=>"INCOMPLETE / பாதியில் நிற்றது","defect_group"=>"Taping","slno"=>"Taping","prod_type"=>"Tapping"],
                ["defect"=>"JOINT STITCH / ஜாயிண்ட் ஸ்டிட்ச்","defect_group"=>"Sewing","slno"=>"Main","prod_type"=>"Tapping / Non Taping"],
                ["defect"=>"LABEL MISTAKE / லேபிள் மிஸ்டேக்","defect_group"=>"Sewing","slno"=>"Others","prod_type"=>"Tapping / Non Taping"],
                ["defect"=>"OPEN SEAM / ஓபன் சீம்","defect_group"=>"Sewing","slno"=>"Main","prod_type"=>"Tapping / Non Taping"],
                ["defect"=>"PEEL OFF / பீல் ஆஃப்","defect_group"=>"Taping","slno"=>"Taping","prod_type"=>"Tapping"],
                ["defect"=>"PRINTING MISTAKE / பிரிண்டிங் மிஸ்டேக்","defect_group"=>"Cutting","slno"=>"Others","prod_type"=>"Tapping / Non Taping"],
                ["defect"=>"RAW EDGE / பிசிறு","defect_group"=>"Sewing","slno"=>"Others","prod_type"=>"Tapping / Non Taping"],
                ["defect"=>"ROLLER MARK / ரோலர் மார்க்","defect_group"=>"Taping","slno"=>"Taping","prod_type"=>"Tapping"],
                ["defect"=>"RUN OFF STITCH / இரங்கிய தையல்","defect_group"=>"Sewing","slno"=>"Main","prod_type"=>"Tapping / Non Taping"],
                ["defect"=>"SEAM PUCKERING / சுருக்கம்","defect_group"=>"Taping","slno"=>"Taping","prod_type"=>"Tapping"],
                ["defect"=>"SEAM TWIST / சீம் ட்விஸ்ட்","defect_group"=>"Taping","slno"=>"Taping","prod_type"=>"Tapping"],
                ["defect"=>"SHADE / ஷேடு","defect_group"=>"Fabric","slno"=>"Others","prod_type"=>"Tapping / Non Taping"],
                ["defect"=>"SHINING MARK / ஷைனிங் மார்க்","defect_group"=>"Taping","slno"=>"Taping","prod_type"=>"Tapping"],
                ["defect"=>"SIZE JUMP / சைஸ் மாற்றம்","defect_group"=>"Sewing","slno"=>"Others","prod_type"=>"Tapping / Non Taping"],
                ["defect"=>"SKIP STITCH / ஸ்கிப் ஸ்டிட்ச்","defect_group"=>"Sewing","slno"=>"Main","prod_type"=>"Tapping / Non Taping"],
                ["defect"=>"STAIN / ஸ்டெயின்","defect_group"=>"Sewing","slno"=>"Main","prod_type"=>"Tapping / Non Taping"],
                ["defect"=>"STOPPAGE MARK / ஸ்டாப்பேஜ் மார்க்","defect_group"=>"Taping","slno"=>"Taping","prod_type"=>"Tapping"],
                ["defect"=>"SYMMETRICAL OUT / சிமெட்ரி அவுட்","defect_group"=>"Sewing","slno"=>"Others","prod_type"=>"Tapping / Non Taping"],
                ["defect"=>"TAPE DAMAGE OR BURST / வெடித்தது","defect_group"=>"Taping","slno"=>"Taping","prod_type"=>"Tapping"],
                ["defect"=>"TAPE FOLD / டேப் மடித்தல்","defect_group"=>"Taping","slno"=>"Taping","prod_type"=>"Tapping"],
                ["defect"=>"UNCUT THREAD / வெட்டப்படாத நூல்","defect_group"=>"Sewing","slno"=>"Main","prod_type"=>"Tapping / Non Taping"],
                ["defect"=>"FABRIC DEFECT / ஃபேப்ரிக் டிபெக்ட்","defect_group"=>"Fabric","slno"=>"Others","prod_type"=>"Tapping / Non Taping"],
                ["defect"=>"HIGH - LOW / அப் & டவுன்","defect_group"=>"Sewing","slno"=>"Others","prod_type"=>"Tapping / Non Taping"],
                ["defect"=>"UNCUT THREAD / அன் கட்டு திரெட்","defect_group"=>"Sewing","slno"=>"Main","prod_type"=>"Tapping / Non Taping"],
                ["defect"=>"OPERATION MISS / ஆப்ரேஷன் மிஸ்ஸிங்","defect_group"=>"Sewing","slno"=>"Others","prod_type"=>"Tapping / Non Taping"],
                ["defect"=>"DOUBLE TRACK / டபுள் ட்ராக்","defect_group"=>"Sewing","slno"=>"Others","prod_type"=>"Tapping / Non Taping"],
                ["defect"=>"TRIMMER CUT / ட்ரிம்மர் கட்ட்","defect_group"=>"Sewing","slno"=>"Others","prod_type"=>"Tapping / Non Taping"],
                ["defect"=>"NEEDLE HOLES / நீடில் ஹோல்ஸ்","defect_group"=>"Sewing","slno"=>"Others","prod_type"=>"Tapping / Non Taping"],
                ["defect"=>"PLEAT, PUCKERING / பிளீட், பக்கரிங்","defect_group"=>"Sewing","slno"=>"Main","prod_type"=>"Tapping / Non Taping"],
                ["defect"=>"OTHERS / மற்றவை","defect_group"=>"Sewing","slno"=>"Main","prod_type"=>"Tapping / Non Taping"],
                ["defect"=>"LOOSE STITCH / லூஸ் தையல்","defect_group"=>"Sewing","slno"=>"Others","prod_type"=>"Tapping / Non Taping"],
                ["defect"=>"SHADE VARIATION / ஷேடு வேறுபாடு","defect_group"=>"Cutting","slno"=>"Main","prod_type"=>"Tapping / Non Taping"]
            ];

            $map = [
                'Main' => 1,
                'Others' => 2,
                'Taping' => 3
            ];

            foreach ($data as $row) {

                $code = $map[$row['slno']] ?? null;
                $defect = $row['defect'];

        $english = trim(explode('/', $defect)[0]);


                DB::connection('sqlsrv')
                    ->table('woven.dbo.Woven_Rej_Master')
                    ->whereRaw("LTRIM(RTRIM(Rej_Type)) = ?", [trim($english)])
                    ->whereRaw("LTRIM(RTRIM(prod_type)) = ?", [trim($row['prod_type'])])
                    ->update([
                        'defect_code' => $code
                    ]);
            }

            return response()->json([
                'success' => true,
                'message' => 'All defect_code updated successfully'
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => $e->getMessage()
            ]);
        }
    }

    public function unitDetails(Request $request){
		
        $portal_type = $request->portaltype;
         $result = DB::connection('sqlsrv')->select("
            EXEC [CHENNAI_ERP].[dbo].[Woven_Unit_Proc] 
        ");


         $lineGroups = DB::connection('sqlsrv')
                        ->table('GP_line_groups')
                        ->orderBy('id', 'desc') 
                        ->get();

         return response()->json([
            'success' => true,
            'data' => $result,
            'line_groups' =>$lineGroups
        ]);
       
        


    }

    protected function executeAndLogQuery(
        $empId,
        $queryName,
        $sql,
        array $bindings = [],
        $cycleId = null,
        $route = 'Endline'
    ) {
        $startTime = microtime(true);

        try {

            $result = DB::connection('sqlsrv')->select($sql, $bindings);

            $endTime = microtime(true);

            $executionMs = round((microtime(true) - $startTime) * 1000, 2);

            DB::connection('sqlsrv')->insert(
                "INSERT INTO performance_querylog (emp_id, query_name, start_time, end_time, execution_time) VALUES (?, ?, ?, ?, ?)",
                [
                    $empId,
                    $queryName,
                    date('Y-m-d H:i:s', (int) $startTime),
                    date('Y-m-d H:i:s', (int) $endTime),
                    $endTime - $startTime,
                ]
            );

            $logTime = Carbon::now('Asia/Kolkata');
            $this->appendMerchLog(
                $logTime->format('Y-m-d H:i:s.v')
                . ' | step 2'
                . ' | route=' . $route
                . ' | cycleId=' . ($cycleId ?? '')
                . ' | query=' . $queryName
                . ' | queryMs=' . $executionMs
                . ' | empId=' . ($empId ?? '')
            );

            return $result;

        } catch (\Throwable $e) {

            $executionMs = round((microtime(true) - $startTime) * 1000, 2);

            Log::error('DB Procedure Failed', [
                'employee_id'  => $empId,
                'procedure'    => $queryName,
                'execution_ms' => $executionMs,
                'error'        => $e->getMessage(),
            ]);

            throw $e;
        }
    }

}