<?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;

class EndlineController extends Controller
{
    public function endline()
    {
        $lines = DB::connection('sqlsrv')->select("
            SELECT 
                cc_no,
                output_type,
                group_name,
                date_from,
                date_to,
                unit_id,
                line_no,
                allocated_line_id
            FROM CHENNAI_ERP.dbo.GP_line_groups
            WHERE allocated_line_id != ''
        ");

        //dd($data);
        return view('Quality.Endline', compact('lines'));
    }

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

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

    public function endline_tablepage()
    {
         $emp_id   = Session::get('endline_checker.employee_id');
        $line_no  =  1;
        $unit     = 10;
        $fromdate = $request->fromdate ?? Carbon::now()->format('Y-m-d');
        $todate   = $request->todate ?? Carbon::now()->format('Y-m-d');
        //dd($emp_id, $line_no, $unit);


        $resultdata = DB::connection('sqlsrv')->select(
            "EXEC dbo.usp_Gut_Endline_QC_Summary 
                @fdate = ?, 
                @Tdate = ?, 
                @Line  = ?, 
                @Unit  = ?",
            [$fromdate, $todate, $line_no, $unit]
        );
//dd($resultdata);
         $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()
    {
         $emp_id   = Session::get('endline_checker.employee_id');
        $line_no  =  1;
        $unit     = 10;
        //dd($emp_id, $line_no, $unit);


        $resultdata = DB::connection('sqlsrv')->select("
            EXEC [CHENNAI_ERP].[dbo].[Get_Endline_Quality_Dashboard] @Line = ?,@Unit = ? ,@Empo = ?
        ", [$line_no, $unit, $emp_id]);


          $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('prod_type')
            ->orderBy('defect_code', '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', '30396061C01B808000187652')
            ->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'     => $outline,
            '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 ?: 'No Defects found'
            ]
        ]);

    } 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 =10;
       $lineno = 1;

        $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'
            ], 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
            ]
        );

        // ✅ 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) {
        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 = 1;
    $empno  = $request->empno ?? null;
    $unit   = 10;
    $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());
}

}