<?php

namespace App\Http\Controllers\API;

use App\Http\Resources\ProductResource;
use App\Models\NotificationDetail;
use App\Http\Controllers\API\BaseController as BaseController;
use App\Models\User;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Str;
use Carbon\Carbon;
use Exception;

class MicrolineplanController extends BaseController
{

    public function microplanning(Request $request){

        $fromdate = $request->input('from_date');
        $todate = $request->input('to_date', $request->input('from_date'));

        $result = DB::select("exec CHENNAI_ERP.dbo.Micro_Planning_Input");
        
        return $this->sendResponse($result, 'details');

    }
    
    public function microplaneddata(Request $request){

        $data = DB::table('micro_line_plan_detail as d')
        ->join('micro_line_plan_header as h', 'h.id', '=', 'd.header_id')
        ->select(
            'h.ocn_no',
            'd.line_no',
            'd.planned_date',
            'd.planned_qty',
            'd.no_mac',
            'd.no_opt'
        )
        ->get();

    return response()->json($data);

    }


    public function insertOrUpdateMicroPlantemp(Request $request){
        // Decode the details array if it's a JSON string
        $details = $request->details;
        $details = is_string($details) ? json_decode($details, true) : $details;

        $selected_date = 


    
        $plan_startdate = Carbon::parse($request->plan_date);
        $plan_enddate = Carbon::parse($request->plan_enddate);

        // Calculate the difference in days (inclusive)
        //$noOfDays = $plan_enddate->diffInDays($plan_startdate) + 1;

        // Validate basic input
        if (!$request->factory_id || !$request->unit_id || !$request->plan_date || !$request->created_by || !is_array($details)) {
            return response()->json(['status' => 'error', 'message' => 'Invalid or missing input data.'], 422);
        }

        try {
            DB::beginTransaction();
            //plan date is factory exit date in macro plan
            // Step 1: Upsert header
            $headerId = DB::table('micro_line_plan_header')
                ->where('factory_id', $request->factory_id)
                ->where('unit_id', $request->unit_id)
                ->whereDate('plan_date', Carbon::now()->format('Y-m-d'))
                ->value('id');

            if (!$headerId) {
                $headerId = DB::table('micro_line_plan_header')->insertGetId([
                    'factory_id' => $request->factory_id,
                    'unit_id' => $request->unit_id,
                    'item_name'=>$request->item_name,
                    'order_qty'=>$request->order_qty,
                    'ship_date'=>$request->ship_date,
                    'ocn_no'=>$request->ocn_no,
                    'plan_date' => $request->plan_date,
                    'created_by' => $request->created_by,
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);
            }

            // Step 2: Get existing details for comparison
            $existingDetails = DB::table('micro_line_plan_detail')
                ->where('header_id', $headerId)
                ->select('id', 'unit_code', 'line_no', 'color_name', 'planned_date', 'planned_qty')
                ->get()
                ->keyBy(function ($item) {
                    return "{$item->unit_code}_{$item->line_no}_{$item->color_name}_{$item->planned_date}";
                });

            $toInsert = [];
            $toUpdate = [];
        

            foreach ($details as $item) {
                $key = "{$item['unit_code']}_{$item['line_no']}_{$item['color_name']}_{$item['planned_date']}";

                if (isset($existingDetails[$key])) {
                    $existing = $existingDetails[$key];
                    if ((int)$existing->planned_qty !== (int)$item['planned_qty']) {
                        $toUpdate[] = [
                            'id' => $existing->id,
                            'planned_qty' => $item['planned_qty'],
                            'updated_at' => now(),
                        ];
                    }
                } else {
                    $startDate = Carbon::parse($item['planned_date']);

                    $selected_date = $item['selected_dates'] ;
                    $noOfDays = count($selected_date);

                

                    for ($i = 0; $i < $noOfDays; $i++) {

                        

                    $toInsert[] = [
                        'header_id' => $headerId,
                        'unit_code' => $item['unit_code'],
                        'line_no' => $item['line_no'],
                        'color_name' => $item['color_name'],
                        'planned_date' => $item['planned_date'],
                        'plan_enddate' =>Carbon::parse($selected_date[$i]),
                        'planned_qty' => $item['planned_qty'],
                        'created_by' => $request->created_by,
                        'created_at' => now(),
                        'updated_at' => now(),
                    ];
                }


                }
            }

            // Step 3: Insert new
            if (!empty($toInsert)) {
                DB::table('micro_line_plan_detail')->insert($toInsert);
            }

            // Step 4: Update changed
            foreach ($toUpdate as $update) {
                DB::table('micro_line_plan_detail')
                    ->where('id', $update['id'])
                    ->update([
                        'planned_qty' => $update['planned_qty'],
                        'updated_at' => $update['updated_at']
                    ]);
            }

            DB::commit();

            return response()->json([
                'status' => 'success',
                'header_id' => $headerId,
                'inserted' => count($toInsert),
                'updated' => count($toUpdate),
                'skipped' => count($details) - count($toInsert) - count($toUpdate),
            ]);

        } catch (\Exception $e) {
            DB::rollBack();
            Log::error('InsertOrUpdateMicroPlan Error: ' . $e->getMessage());
            return response()->json(['status' => 'error', 'message' => $e->getMessage()], 500);
        }
    }


// public function insertOrUpdateMicroPlan(Request $request)
// {
//     // dd($request->input()); // Uncomment for debugging

//     // Ensure input is an array
//     $data = $request->all();
//     $data = is_string($data) ? json_decode($data, true) : $data;
//     if (!is_array($data)) {
//         return response()->json(['status' => 'error', 'message' => 'Invalid input data.'], 422);
//     }

//     try {
//         DB::beginTransaction();

//         $results = []; // To store results for each entry

//         foreach ($data as $entry) {
//             $details = $entry['details'] ?? [];
//             $details = is_string($details) ? json_decode($details, true) : $details;

//             // Validate basic input for this entry
//             if (!$entry['factory_id'] || !$entry['unit_id'] || !$entry['plan_date'] || !$entry['created_by'] || !is_array($details)) {
//                 $results[] = ['status' => 'error', 'message' => 'Invalid or missing input data for an entry.', 'entry' => $entry];
//                 continue;
//             }

//             $plan_startdate = Carbon::parse($entry['plan_date']);
//             $plan_enddate = Carbon::parse($entry['plan_enddate']);

//             // Step 1: Upsert header
//             $headerId = DB::table('micro_line_plan_header')
//                 ->where('factory_id', $entry['factory_id'])
//                 ->where('unit_id', $entry['unit_id'])
//                 ->where('ocn_no', 'GIL\OCN' . $entry['ocn_no'])
//                 ->whereDate('plan_date', $entry['plan_date'])
//                 ->value('id');

//             if (!$headerId) {
//                 $headerId = DB::table('micro_line_plan_header')->insertGetId([
//                     'factory_id' => $entry['factory_id'],
//                     'unit_id' => $entry['unit_id'],
//                     'item_name' => $entry['item_name'],
//                     'order_qty' => $entry['order_qty'],
//                     'ship_date' => $entry['ship_date'],
//                     'ocn_no' => 'GIL\OCN' . $entry['ocn_no'],
//                     'plan_date' => $entry['plan_date'],
//                     'created_by' => $entry['created_by'],
//                     'created_at' => now(),
//                     'updated_at' => now(),
//                 ]);
//             }

//             // Step 2: Get existing details for comparison
//             $existingDetails = DB::table('micro_line_plan_detail')
//                 ->where('header_id', $headerId)
//                 ->select('id', 'unit_code', 'line_no', 'color','item_id','color_id', 'planned_date', 'planned_qty')
//                 ->get()
//                 ->keyBy(function ($item) {
//                     return "{$item->unit_code}_{$item->line_no}_{$item->item_id}_{$item->color_id}_{$item->planned_date}";
//                 });

//             $toInsert = [];
//             $toUpdate = [];

//             foreach ($details as $item) {
//                 $key = "{$item['unit_code']}_{$item['line_no']}_{$item['item_id']}_{$item['color_id']}_{$item['planned_date']}"; // Use color_id for key

//                 if (isset($existingDetails[$key])) {
//                     $existing = $existingDetails[$key];
//                     if ((int)$existing->planned_qty !== (int)$item['planned_qty']) {
//                         $toUpdate[] = [
//                             'id' => $existing->id,
//                             'planned_qty' => $item['planned_qty'],
//                             'updated_at' => now(),
//                         ];
//                     }
//                 } else {
//                     $selectedDates = $item['selected_dates'] ?? [];
//                     $planEndDate = !empty($selectedDates) ? Carbon::parse(end($selectedDates)) : Carbon::parse($item['planned_date']);

//                     $toInsert[] = [
//                         'header_id' => $headerId,
//                         'unit_code' => $item['unit_code'],
//                         'line_no' => $item['line_no'],
//                         'color_name' => $item['color'] ?? $item['color_id'], // Fallback to color_id if color is missing
//                         'planned_date' => $item['planned_date'],
//                         'plan_enddate' => $planEndDate,
//                         'planned_qty' => $item['planned_qty'],
//                         'item_id' => $item['item_id'],
//                         'color_id' => $item['color_id'],
//                         'item' => $item['item'] ?? $item['item_id'], // Fallback to item_id if item is missing
//                         'color' => $item['color'] ?? $item['color_id'], // Fallback to color_id if color is missing
//                         'created_by' => $entry['created_by'],
//                         'created_at' => now(),
//                         'updated_at' => now(),
//                     ];
//                 }
//             }

//             // Step 3: Insert new
//             $insertedCount = 0;
//             if (!empty($toInsert)) {
//                 DB::table('micro_line_plan_detail')->insert($toInsert);
//                 $insertedCount = count($toInsert);
//             }

//             // Step 4: Update changed
//             $updatedCount = 0;
//             foreach ($toUpdate as $update) {
//                 DB::table('micro_line_plan_detail')
//                     ->where('id', $update['id'])
//                     ->update([
//                         'planned_qty' => $update['planned_qty'],
//                         'updated_at' => $update['updated_at']
//                     ]);
//                 $updatedCount++;
//             }

//             $results[] = [
//                 'status' => 'success',
//                 'header_id' => $headerId,
//                 'inserted' => $insertedCount,
//                 'updated' => $updatedCount,
//                 'skipped' => count($details) - $insertedCount - $updatedCount,
//                 'ocn_no' => $entry['ocn_no']
//             ];
//         }

//         DB::commit();

//         return response()->json(['status' => 'success', 'results' => $results]);

//     } catch (\Exception $e) {
//         DB::rollBack();
//         Log::error('InsertOrUpdateMicroPlan Error: ' . $e->getMessage());
//         return response()->json(['status' => 'error', 'message' => $e->getMessage()], 500);
//     }
// }


public function insertOrUpdateMicroPlan(Request $request)
{
    //dd('jjjjj');
    // Decode and validate input
    $data = $request->all();
    $data = is_string($data) ? json_decode($data, true) : $data;
    if (!is_array($data)) {
        return response()->json(['status' => 'error', 'message' => 'Invalid input data.'], 422);
    }

    try {
        DB::beginTransaction();
        $results = [];

        foreach ($data as $keydatas => $entry) {
            $details = $entry['details'] ?? [];
            $details = is_string($details) ? json_decode($details, true) : $details;

            if (!isset($entry['factory_id'], $entry['unit_id'], $entry['plan_date'], $entry['created_by']) || !is_array($details)) {
                $results[] = ['status' => 'error', 'message' => 'Missing required fields in entry.', 'entry' => $entry];
                continue;
            }

            $plan_startdate = Carbon::parse($entry['plan_date']);
            $plan_enddate = isset($entry['plan_enddate']) ? Carbon::parse($entry['plan_enddate']) : null;

            // Step 1: Upsert header
            $ocn_no = 'GIL\\OCN' . $entry['ocn_no'];
            $headerId = DB::table('micro_line_plan_header')
                //->where('factory_id', $entry['factory_id'])
                ->where('unit_id', $entry['unit_id'])
                ->whereDate('plan_date', Carbon::now()->format('Y-m-d'))
                ->value('id');

            if (!$headerId && empty($headerId)) {
                $headerId = DB::table('micro_line_plan_header')->insertGetId([
                    'factory_id' => 3,
                    'unit_id' => $entry['unit_id'],
                    'item_name' => $entry['item_name'] ?? '',
                    'order_qty' => $entry['order_qty'] ?? 0,
                    'ship_date' => $entry['ship_date'] ?? null,
                    'plan_date' => Carbon::now()->format('Y-m-d'),
                    'created_by' => $entry['created_by'],
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);
            }

            // Step 2: Get existing details
            $existingDetails = DB::table('micro_line_plan_detail')
                ->where('header_id', $headerId)
                ->select('id', 'unit_code', 'line_no', 'item_id', 'color_id', 'planned_date', 'planned_qty', 'ocn_no')
                ->get()
                ->keyBy(function ($item) {
                    return "{$item->unit_code}_{$item->line_no}_{$item->item_id}_{$item->color_id}_{$item->ocn_no}";
                });

            $insertedCount = 0;
            $updatedCount = 0;
            $alreadyDeleted = [];
            $deletedIds = [];

            foreach ($details as $item) {

                if (!isset($item['unit_code'], $item['line_no'], $item['planned_date'], $item['planned_qty'], $item['item_id'], $item['color_id'])) {
                    continue;
                }

                $ocnKey = 'GIL\\OCN' . $entry['ocn_no'];

                $isSplit = isset($item['is_split']) ? $item['is_split'] : 'false';
                $taskstatus = isset($item['taskstatus']) ? $item['taskstatus'] : '0';

                $deleteKey = "{$item['item_id']}_{$item['color_id']}_{$ocnKey}_{$isSplit}";
                $plannerdeleteKey = "{$item['item_id']}_{$item['line_no']}_{$item['color_id']}_{$ocnKey}_{$item['planner_opr']}";
                $plannerdragKey = "{$item['item_id']}_{$item['line_no']}_{$item['color_id']}_{$ocnKey}_{$item['taskisdragg']}";
                //echo '<pre>';
                //print_r($plannerdeleteKey);
                //echo $deleteKey. '--'. $keydatas;
                
               
               
                if ($isSplit == 'true'  && !isset($alreadyDeleted[$deleteKey])) {

                   

                  // echo(count($alreadyDeleted)) .'<br>';

                    // Mark as deleted
                    $alreadyDeleted[$deleteKey] = true;
                    $existingDetails = [];
           
                    $planDetailIds = DB::table('micro_line_plan_detail')
                            ->where('item_id', $item['item_id'])
                            ->where('color_id', $item['color_id'])
                            ->where('ocn_no', $ocnKey)
                            ->where('unit_code', $item['unit_code'])
                            ->pluck('id')
                            ->toArray();
                            //dd($planDetailIds);

                        if (!empty($planDetailIds)) {
                            // Collect for output
                            $deletedIds = array_merge($deletedIds, $planDetailIds);

                            // Delete related data
                            DB::table('micro_plan_machine')
                                ->whereIn('plan_detail_id', $planDetailIds)
                                ->delete();

                            DB::table('micro_plan_operator')
                                ->whereIn('plan_detail_id', $planDetailIds)
                                ->delete();

                            DB::table('micro_line_plan_detail')
                                ->whereIn('id', $planDetailIds)
                                ->delete();
                        }

                        //dd($planDetailIds);
                }


                if ($item['planner_opr'] != '0'  && !isset($alreadyDeleted[$plannerdeleteKey])) {

                    // echo(count($alreadyDeleted)) .'<br>';
  
                      // Mark as deleted
                      $alreadyDeleted[$plannerdeleteKey] = true;
                      $existingDetails = [];
             
                      $planDetailIds = DB::table('micro_line_plan_detail')
                              ->where('item_id', $item['item_id'])
                              ->where('color_id', $item['color_id'])
                              ->where('ocn_no', $ocnKey)
                              ->where('line_no',$item['line_no'] )
                              ->where('unit_code', $item['unit_code'])
                              ->pluck('id')
                              ->toArray();
                              //dd($planDetailIds);
  
                          if (!empty($planDetailIds)) {
                              // Collect for output
                              $deletedIds = array_merge($deletedIds, $planDetailIds);
  
                              // Delete related data
                              DB::table('micro_plan_machine')
                                  ->whereIn('plan_detail_id', $planDetailIds)
                                  ->delete();
  
                              DB::table('micro_plan_operator')
                                  ->whereIn('plan_detail_id', $planDetailIds)
                                  ->delete();
  
                              DB::table('micro_line_plan_detail')
                                  ->whereIn('id', $planDetailIds)
                                  ->delete();
                          }
  
                          //dd($planDetailIds);
                  }if($item['taskisdragg'] != '0' && !isset($alreadyDeleted[$plannerdragKey]) && $taskstatus != '0' ){

                    $alreadyDeleted[$plannerdragKey] = true;
                    $existingDetails = [];
                  

                   
                    

                    $exitinglinechange = DB::table('micro_line_plan_detail')
                    
                    ->where('id', $item['taskstatus'])
                    
                    ->first();
                    
                   


                  
           if(!empty($plannerdragKey)){

          

            $input = explode('_',$plannerdragKey);

          if($exitinglinechange == ''){
                    $planDetaildragIds = DB::table('micro_line_plan_detail')
                            ->where('item_id', $input['0'])
                            ->where('color_id', $input['2'])
                            ->where('ocn_no', $ocnKey)
                           ->where('line_no',$input['1'])
                            ->where('unit_code',$item['unit_code'])
                           
                            ->pluck('id')
                            ->toArray();}else{

                                $planDetaildragIds = DB::table('micro_line_plan_detail')
                                ->where('item_id', $exitinglinechange->item_id)
                                ->where('color_id',$exitinglinechange->color_id)
                                ->where('ocn_no', $ocnKey)
                               ->where('line_no',$exitinglinechange->line_no)
                                ->where('unit_code',$item['unit_code'])
                                ->pluck('id')
                                ->toArray();


                                // $planmargeDetails =DB::table('micro_line_plan_detail')
                                // ->where('item_id', $exitinglinechange->item_id)
                                // ->where('color_id',$exitinglinechange->color_id)
                                // ->where('ocn_no', $ocnKey)
                                // ->where('unit_code',$item['unit_code'])
                                // ->groupBy('line_no')
                                // ->pluck('id')
                                // ->toArray();

                                // if(count($planmargeDetails) > 1){

                                // }



                            }
                           //dd($planDetaildragIds);

                        if (!empty($planDetaildragIds)) {
                            // Collect for output
                            $deletedIds = array_merge($deletedIds, $planDetaildragIds);

                            // Delete related data
                            DB::table('micro_plan_machine')
                                ->whereIn('plan_detail_id', $planDetaildragIds)
                                ->delete();

                            DB::table('micro_plan_operator')
                                ->whereIn('plan_detail_id', $planDetaildragIds)
                                ->delete();
                            $toBeDeletedRows = DB::table('micro_line_plan_detail')->whereIn('id', $planDetaildragIds)->get();
                            foreach ($toBeDeletedRows as $deletedRow) {
                                $this->logMicroDetail('DELETE', $deletedRow->id, $deletedRow, $entry['created_by']);
                            }

                            DB::table('micro_line_plan_detail')
                                ->whereIn('id', $planDetaildragIds)
                                ->delete();
                        }

                    }

                  }

                $selectedDates = $item['selected_dates'] ?? [];
                $ocnKey = 'GIL\\OCN' . $entry['ocn_no'];
                $lookupKey = "{$item['unit_code']}_{$item['line_no']}_{$item['item_id']}_{$item['color_id']}_{$ocnKey}";

               

                

                if (isset($existingDetails[$lookupKey])) {
                    $existing = $existingDetails[$lookupKey];
                    foreach ($selectedDates as $selectedDate) {
                    // if($ocnKey== 'GIL\OCN04388'){
                    //     dd('test');
                    // }
                        

                       
                        if ((int)$existing->planned_qty !== (int)$item['planned_qty']) {
                            DB::table('micro_line_plan_detail')
                                ->where('id', $existing->id)
                                ->update([
                                    // 'planned_qty' => $item['planned_qty'],
                                    // 'planner_opr' => $item['planner_opr'],
                                    // 'planner_qty' => $item['planner_qty'],
                                    // 'no_opr' => $item['planner_opr'],
                                    // 'ocn_no' => $ocnKey,
                                    'updated_at' => now(),
                                    'task_type' => '7'.'-'.$item['taskisdragg'].'-'.(int)$existing->planned_qty.'-'.(int)$item['planned_qty'],
                                    'updated_by' => $entry['created_by']
                                    // 'line_no' => $item['line_no'],
                                    // 'item_id' => $item['item_id'],
                                    // 'color_id' => $item['color_id'],
                                    // 'color_name' => $item['color_name'] ?? '',
                                    // 'planned_date' => Carbon::parse($selectedDates[0]),
                                    // 'plan_enddate' => Carbon::parse($selectedDate),
                                ]);
                            $updatedCount++;
                            $this->logMicroDetail('UPDATE', $existing->id, $existing, $entry['created_by']);
                            //echo '<pre>';
                            //print_r(Carbon::parse($selectedDate)->toDateString());
                        }

                    } 
                    // if($item['planner_opr'] == '5'){
                    //     dd('stop');
                    // }
                    
                } else {
                    if($item['planner_qty']){
                        $totalQty = $item['is_split_unit'] == 0 ? $item['planned_qty'] : $item['is_split_unit'];
                        $maxPerDay = $item['planner_qty'];
                        $target_day_qty = $item['planner_qty'];

                    }else{
                        $totalQty = $item['is_split_unit'] == 0 ? $item['planned_qty'] : $item['is_split_unit'];
                        $maxPerDay = $item['per_day_qty'];
                        $target_day_qty = $item['per_day_qty'];
                       

                    }
                    
                    foreach ($selectedDates as $selectedDate) {
                        
                        if ($totalQty >= $maxPerDay) {
                            $qty = $maxPerDay;
                        } else {
                            $qty = $totalQty; // whatever is remaining
                        }

                        if($item['planner_qty'] != 0 ){
                            $production_bal_saw = $totalQty < $item['planner_qty'] ? $totalQty : $item['planner_qty'];
                        }else{
                            $production_bal_saw = $totalQty < $item['per_day_qty'] ? $totalQty : $item['per_day_qty'];
                        }

                       

                    //   $exitingrowcheck = DB::table('micro_line_plan_detail')
                    //   ->where('item_id', $item['item_id'])
                    //   ->where('color_id', $item['color_id'])
                    //   ->where('ocn_no', $ocnKey)
                    //   ->where('production_bal_sew', $totalQty < $item['per_day_qty'] ? $totalQty : $item['per_day_qty'])
                    //   ->count();

                    //   if($exitingrowcheck > 0){

                    //     DB::table('micro_line_plan_detail')
                    //     ->where('item_id', $item['item_id'])
                    //     ->where('color_id', $item['color_id'])
                    //     ->where('ocn_no', $ocnKey)
                    //     ->where('production_bal_sew', $totalQty < $item['per_day_qty'] ? $totalQty : $item['per_day_qty'])
                    //     ->update([
                            
                    //         'updated_at' => now(),
                    //         'line_no' => $item['line_no'],
                    //         'planned_date' => Carbon::parse($selectedDates[0]),
                    //         'plan_enddate' => Carbon::parse($selectedDate),
                    //     ]);

                    //   }
                    

                    $isSplit = !empty($item['is_split']) ? (int)$item['is_split'] : 0;

                        $detailId = DB::table('micro_line_plan_detail')->insertGetId([
                            'line_no' => $item['line_no'],
                            'header_id' => $headerId,
                            'unit_code' => $item['unit_code'],
                            'line_name' => $item['line_name'] ?? null,
                            
                            'item_id' => $item['item_id'],
                            'color_id' => $item['color_id'],
                            'color_name' => $item['color_name'] ?? '',
                            'planned_date' => Carbon::parse($selectedDates[0]),
                            'plan_enddate' => Carbon::parse($selectedDate),
                            'planned_qty' => ($item['is_split_unit'] == 0) ? ($item['planned_qty'] > 0 ? $item['planned_qty'] : 100) : $item['is_split_unit'],
                            'created_by' => $entry['created_by'],
                            'no_mach' => $item['no_of_machines'] ?? null,
                            'no_opr' => $item['no_operators'] ,
                            'item' => $item['item'],
                            'per_day_qty' => $item['per_day_qty'] ?? null,
                            'New_Targ_Qty' => $target_day_qty,
                            'color' => $item['color'],
                            'ocn_no' => $ocnKey,
                            'ck_date' => $item['ck_date'],
                            'fi_date' => $item['FI_date'],
                            'planner_opr' => $item['planner_opr'] ?? null,
                            'planner_qty' => $item['planner_qty'] ?? null,
                            'buyer' => $item['buyer'] ?? null,
                            'priority' => $keydatas,
                            'created_at' => now(),
                            'updated_at' => now(),
                            'efficiency' => $item['planner_efficiency'] ?? Null,
                            'production_bal_sew' => $production_bal_saw,
                            'task_type' => ($isSplit == 0) ? ($item['taskisdragg'] ?? 0) : 3

                        ]);
                        $newDetail = DB::table('micro_line_plan_detail')->where('id', $detailId)->first();
                        $this->logMicroDetail('INSERT', $detailId, $newDetail, $entry['created_by']);

                        $totalQty -= $qty;

                        // Stored procedures
                        $procResultsmech = DB::select("exec vaahini_erp_gainup.dbo.Mch_Type_Grade_Details_Proc @Ocn = ?, @Itemid = ?, @Colorid = ?, @Type = ?", [
                            $ocnKey,
                            $item['item_id'],
                            $item['color_id'],
                            $item['type'] ?? 'Mch'
                        ]);

                        $procResultsopr = DB::select("exec vaahini_erp_gainup.dbo.Mch_Type_Grade_Details_Proc @Ocn = ?, @Itemid = ?, @Colorid = ?, @Type = ?", [
                            $ocnKey,
                            $item['item_id'],
                            $item['color_id'],
                            $item['type'] ?? 'Opr'
                        ]);

                        foreach ($procResultsmech as $row) {
                            DB::table('micro_plan_machine')->insert([
                                'plan_detail_id' => $detailId,
                                'ocn' => $row->Order_No ?? null,
                                'type' => $row->Type ?? null,
                                'cnt' => $row->Cnt ?? null,
                            ]);
                        }

                        foreach ($procResultsopr as $row) {
                            DB::table('micro_plan_operator')->insert([
                                'plan_detail_id' => $detailId,
                                'ocn' => $row->Order_No ?? null,
                                'type' => $row->Type ?? null,
                                'cnt' => $row->Cnt ?? null,
                            ]);
                        }

                        $insertedCount++;
                    }
                }
            }

            $results[] = [
                'status' => 'success',
                'header_id' => $headerId,
                'inserted' => $insertedCount,
                'updated' => $updatedCount,
                'skipped' => count($details) - $insertedCount - $updatedCount,
                'deleted' => $alreadyDeleted,
                'ocn_no' => $entry['ocn_no'],
                'deleted' => $alreadyDeleted,
                'deleted_ids' => $deletedIds,
            ];
        }

        DB::commit();

        return response()->json([
            'status' => 'success',
            'summary' => [
                'total' => count($results),
                'successful' => collect($results)->where('status', 'success')->count(),
                'errors' => collect($results)->where('status', 'error')->count(),
            ],
            'results' => $results,
        ]);
    } catch (\Exception $e) {
        DB::rollBack();
        Log::error('InsertOrUpdateMicroPlan Error: ' . $e->getMessage());
        return response()->json(['status' => 'error', 'message' => $e->getMessage()], 500);
    }
}






public function inser_backup_23(Request $request)
{
    
    // Decode and validate input
    $data = $request->all();
    $data = is_string($data) ? json_decode($data, true) : $data;
    if (!is_array($data)) {
        return response()->json(['status' => 'error', 'message' => 'Invalid input data.'], 422);
    }

    try {
        DB::beginTransaction();
        $results = [];

        foreach ($data as $keydatas => $entry) {
            $details = $entry['details'] ?? [];
            $details = is_string($details) ? json_decode($details, true) : $details;

            if (!isset($entry['factory_id'], $entry['unit_id'], $entry['plan_date'], $entry['created_by']) || !is_array($details)) {
                $results[] = ['status' => 'error', 'message' => 'Missing required fields in entry.', 'entry' => $entry];
                continue;
            }

            $plan_startdate = Carbon::parse($entry['plan_date']);
            $plan_enddate = isset($entry['plan_enddate']) ? Carbon::parse($entry['plan_enddate']) : null;

            // Step 1: Upsert header
            $ocn_no = 'GIL\\OCN' . $entry['ocn_no'];
            $headerId = DB::table('micro_line_plan_header')
                //->where('factory_id', $entry['factory_id'])
                ->where('unit_id', $entry['unit_id'])
                ->whereDate('plan_date', Carbon::now()->format('Y-m-d'))
                ->value('id');

            if (!$headerId && empty($headerId)) {
                $headerId = DB::table('micro_line_plan_header')->insertGetId([
                    'factory_id' => 3,
                    'unit_id' => $entry['unit_id'],
                    'item_name' => $entry['item_name'] ?? '',
                    'order_qty' => $entry['order_qty'] ?? 0,
                    'ship_date' => $entry['ship_date'] ?? null,
                    'plan_date' => Carbon::now()->format('Y-m-d'),
                    'created_by' => $entry['created_by'],
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);
            }

            // Step 2: Get existing details
            $existingDetails = DB::table('micro_line_plan_detail')
                ->where('header_id', $headerId)
                ->select('id', 'unit_code', 'line_no', 'item_id', 'color_id', 'planned_date', 'planned_qty', 'ocn_no')
                ->get()
                ->keyBy(function ($item) {
                    return "{$item->unit_code}_{$item->line_no}_{$item->item_id}_{$item->color_id}_{$item->ocn_no}";
                });

            $insertedCount = 0;
            $updatedCount = 0;
            $alreadyDeleted = [];
            $deletedIds = [];

            foreach ($details as $item) {

                if (!isset($item['unit_code'], $item['line_no'], $item['planned_date'], $item['planned_qty'], $item['item_id'], $item['color_id'])) {
                    continue;
                }

                $ocnKey = 'GIL\\OCN' . $entry['ocn_no'];

                $isSplit = isset($item['is_split']) ? $item['is_split'] : 'false';

                $deleteKey = "{$item['item_id']}_{$item['color_id']}_{$ocnKey}_{$isSplit}";
                $plannerdeleteKey = "{$item['item_id']}_{$item['line_no']}_{$item['color_id']}_{$ocnKey}_{$item['planner_opr']}";
                $plannerdragKey = "{$item['item_id']}_{$item['line_no']}_{$item['color_id']}_{$ocnKey}_{$item['taskisdragg']}";
                //echo '<pre>';
                //print_r($alreadyDeleted);
                //echo $deleteKey. '--'. $keydatas;
                
               
               
                if ($isSplit == 'true'  && !isset($alreadyDeleted[$deleteKey])) {

                  // echo(count($alreadyDeleted)) .'<br>';

                    // Mark as deleted
                    $alreadyDeleted[$deleteKey] = true;
                    $existingDetails = [];
           
                    $planDetailIds = DB::table('micro_line_plan_detail')
                            ->where('item_id', $item['item_id'])
                            ->where('color_id', $item['color_id'])
                            ->where('ocn_no', $ocnKey)
                            ->pluck('id')
                            ->toArray();
                            //dd($planDetailIds);

                        if (!empty($planDetailIds)) {
                            // Collect for output
                            $deletedIds = array_merge($deletedIds, $planDetailIds);

                            // Delete related data
                            DB::table('micro_plan_machine')
                                ->whereIn('plan_detail_id', $planDetailIds)
                                ->delete();

                            DB::table('micro_plan_operator')
                                ->whereIn('plan_detail_id', $planDetailIds)
                                ->delete();

                            DB::table('micro_line_plan_detail')
                                ->whereIn('id', $planDetailIds)
                                ->delete();
                        }

                        //dd($planDetailIds);
                }


                if ($item['planner_opr'] != '0'  && !isset($alreadyDeleted[$plannerdeleteKey])) {

                    // echo(count($alreadyDeleted)) .'<br>';
  
                      // Mark as deleted
                      $alreadyDeleted[$plannerdeleteKey] = true;
                      $existingDetails = [];
             
                      $planDetailIds = DB::table('micro_line_plan_detail')
                              ->where('item_id', $item['item_id'])
                              ->where('color_id', $item['color_id'])
                              ->where('ocn_no', $ocnKey)
                              ->where('line_no',$item['line_no'] )
                              ->pluck('id')
                              ->toArray();
                              //dd($planDetailIds);
  
                          if (!empty($planDetailIds)) {
                              // Collect for output
                              $deletedIds = array_merge($deletedIds, $planDetailIds);
  
                              // Delete related data
                              DB::table('micro_plan_machine')
                                  ->whereIn('plan_detail_id', $planDetailIds)
                                  ->delete();
  
                              DB::table('micro_plan_operator')
                                  ->whereIn('plan_detail_id', $planDetailIds)
                                  ->delete();
  
                              DB::table('micro_line_plan_detail')
                                  ->whereIn('id', $planDetailIds)
                                  ->delete();
                          }
  
                          //dd($planDetailIds);
                  }if($item['taskisdragg'] != '0' && !isset($alreadyDeleted[$plannerdragKey]) && $item['taskstatus'] != '0' ){

                    $alreadyDeleted[$plannerdragKey] = true;
                    $existingDetails = [];

                    $exitinglinechange = DB::table('micro_line_plan_detail')
                    //->where('factory_id', $entry['factory_id'])
                    ->where('id', $item['taskstatus'])
                   
                    ->first();

                  
           
                    $planDetaildragIds = DB::table('micro_line_plan_detail')
                            ->where('item_id', $exitinglinechange->item_id)
                            ->where('color_id', $exitinglinechange->color_id)
                            ->where('ocn_no', $ocnKey)
                            ->where('line_no', $exitinglinechange->line_no)
                           
                            ->pluck('id')
                            ->toArray();
                           // dd($planDetaildragIds);

                        if (!empty($planDetaildragIds)) {
                            // Collect for output
                            $deletedIds = array_merge($deletedIds, $planDetaildragIds);

                            // Delete related data
                            DB::table('micro_plan_machine')
                                ->whereIn('plan_detail_id', $planDetaildragIds)
                                ->delete();

                            DB::table('micro_plan_operator')
                                ->whereIn('plan_detail_id', $planDetaildragIds)
                                ->delete();

                            DB::table('micro_line_plan_detail')
                                ->whereIn('id', $planDetaildragIds)
                                ->delete();
                        }



                  }

                $selectedDates = $item['selected_dates'] ?? [];
                $ocnKey = 'GIL\\OCN' . $entry['ocn_no'];
                $lookupKey = "{$item['unit_code']}_{$item['line_no']}_{$item['item_id']}_{$item['color_id']}_{$ocnKey}";

                if (isset($existingDetails[$lookupKey])) {
                    $existing = $existingDetails[$lookupKey];
                    foreach ($selectedDates as $selectedDate) {

                       
                        if ((int)$existing->planned_qty !== (int)$item['planned_qty']) {
                            DB::table('micro_line_plan_detail')
                                ->where('id', $existing->id)
                                ->update([
                                    'planned_qty' => $item['planned_qty'],
                                    'planner_opr' => $item['planner_opr'],
                                    'planner_qty' => $item['planner_qty'],
                                    'no_opr' => $item['planner_opr'],
                                    'ocn_no' => $ocnKey,
                                    'updated_at' => now(),
                                    'line_no' => $item['line_no'],
                                    'item_id' => $item['item_id'],
                                    'color_id' => $item['color_id'],
                                    'color_name' => $item['color_name'] ?? '',
                                    'planned_date' => Carbon::parse($selectedDates[0]),
                                    'plan_enddate' => Carbon::parse($selectedDate),
                                ]);
                            $updatedCount++;
                            //echo '<pre>';
                            //print_r(Carbon::parse($selectedDate)->toDateString());
                        }

                    } if($item['planner_opr'] == '5'){
                        dd('stop');
                    }
                    
                } else {
                    $totalQty = $item['is_split_unit'] == 0 ? $item['planned_qty'] : $item['is_split_unit'];
                        $maxPerDay = $item['per_day_qty'];
                    foreach ($selectedDates as $selectedDate) {
                        
                        if ($totalQty >= $maxPerDay) {
                            $qty = $maxPerDay;
                        } else {
                            $qty = $totalQty; // whatever is remaining
                        }

                    //   $exitingrowcheck = DB::table('micro_line_plan_detail')
                    //   ->where('item_id', $item['item_id'])
                    //   ->where('color_id', $item['color_id'])
                    //   ->where('ocn_no', $ocnKey)
                    //   ->where('production_bal_sew', $totalQty < $item['per_day_qty'] ? $totalQty : $item['per_day_qty'])
                    //   ->count();

                    //   if($exitingrowcheck > 0){

                    //     DB::table('micro_line_plan_detail')
                    //     ->where('item_id', $item['item_id'])
                    //     ->where('color_id', $item['color_id'])
                    //     ->where('ocn_no', $ocnKey)
                    //     ->where('production_bal_sew', $totalQty < $item['per_day_qty'] ? $totalQty : $item['per_day_qty'])
                    //     ->update([
                            
                    //         'updated_at' => now(),
                    //         'line_no' => $item['line_no'],
                    //         'planned_date' => Carbon::parse($selectedDates[0]),
                    //         'plan_enddate' => Carbon::parse($selectedDate),
                    //     ]);

                    //   }


                       

                        $detailId = DB::table('micro_line_plan_detail')->insertGetId([
                            'header_id' => $headerId,
                            'unit_code' => $item['unit_code'],
                            'line_no' => $item['line_no'],
                            'item_id' => $item['item_id'],
                            'color_id' => $item['color_id'],
                            'color_name' => $item['color_name'] ?? '',
                            'planned_date' => Carbon::parse($selectedDates[0]),
                            'plan_enddate' => Carbon::parse($selectedDate),
                            'planned_qty' => $item['is_split_unit'] == 0 ? $item['planned_qty'] : $item['is_split_unit'],
                            'created_by' => $entry['created_by'],
                            'no_mach' => $item['no_of_machines'] ?? null,
                            'no_opr' => $item['no_operators'] ,
                            'item' => $item['item'],
                            'per_day_qty' => $item['per_day_qty'] ?? null,
                            'color' => $item['color'],
                            'ocn_no' => $ocnKey,
                            'ck_date' => $item['ck_date'],
                            'fi_date' => $item['FI_date'],
                            'planner_opr' => $item['planner_opr'] ?? null,
                            'planner_qty' => $item['planner_qty'] ?? null,
                            'buyer' => $item['buyer'] ?? null,
                            'priority' => $keydatas,
                            'created_at' => now(),
                            'updated_at' => now(),
                            'production_bal_sew' => $totalQty < $item['per_day_qty'] ? $totalQty : $item['per_day_qty']
                        ]);

                        $totalQty -= $qty;

                        // Stored procedures
                        $procResultsmech = DB::select("exec vaahini_erp_gainup.dbo.Mch_Type_Grade_Details_Proc @Ocn = ?, @Itemid = ?, @Colorid = ?, @Type = ?", [
                            $ocnKey,
                            $item['item_id'],
                            $item['color_id'],
                            $item['type'] ?? 'Mch'
                        ]);

                        $procResultsopr = DB::select("exec vaahini_erp_gainup.dbo.Mch_Type_Grade_Details_Proc @Ocn = ?, @Itemid = ?, @Colorid = ?, @Type = ?", [
                            $ocnKey,
                            $item['item_id'],
                            $item['color_id'],
                            $item['type'] ?? 'Opr'
                        ]);

                        foreach ($procResultsmech as $row) {
                            DB::table('micro_plan_machine')->insert([
                                'plan_detail_id' => $detailId,
                                'ocn' => $row->Order_No ?? null,
                                'type' => $row->Type ?? null,
                                'cnt' => $row->Cnt ?? null,
                            ]);
                        }

                        foreach ($procResultsopr as $row) {
                            DB::table('micro_plan_operator')->insert([
                                'plan_detail_id' => $detailId,
                                'ocn' => $row->Order_No ?? null,
                                'type' => $row->Type ?? null,
                                'cnt' => $row->Cnt ?? null,
                            ]);
                        }

                        $insertedCount++;
                    }
                }
            }

            $results[] = [
                'status' => 'success',
                'header_id' => $headerId,
                'inserted' => $insertedCount,
                'updated' => $updatedCount,
                'skipped' => count($details) - $insertedCount - $updatedCount,
                'deleted' => $alreadyDeleted,
                'ocn_no' => $entry['ocn_no'],
                'deleted' => $alreadyDeleted,
                'deleted_ids' => $deletedIds,
            ];
        }

        DB::commit();

        return response()->json([
            'status' => 'success',
            'summary' => [
                'total' => count($results),
                'successful' => collect($results)->where('status', 'success')->count(),
                'errors' => collect($results)->where('status', 'error')->count(),
            ],
            'results' => $results,
        ]);
    } catch (\Exception $e) {
        DB::rollBack();
        Log::error('InsertOrUpdateMicroPlan Error: ' . $e->getMessage());
        return response()->json(['status' => 'error', 'message' => $e->getMessage()], 500);
    }

}

    public function MicroPlanNew(Request $request)
    {

        
    // Decode and validate input
    $data = $request->all();
    $data = is_string($data) ? json_decode($data, true) : $data;
    if (!is_array($data)) {
        return response()->json(['status' => 'error', 'message' => 'Invalid input data.'], 422);
    }

    try {
        DB::beginTransaction();
        $results = [];

        foreach ($data as $keydatas => $entry) {
            $details = $entry['details'] ?? [];
            $details = is_string($details) ? json_decode($details, true) : $details;

            if (!isset($entry['factory_id'], $entry['unit_id'], $entry['plan_date'], $entry['created_by']) || !is_array($details)) {
                $results[] = ['status' => 'error', 'message' => 'Missing required fields in entry.', 'entry' => $entry];
                continue;
            }

            $plan_startdate = Carbon::parse($entry['plan_date']);
            $plan_enddate = isset($entry['plan_enddate']) ? Carbon::parse($entry['plan_enddate']) : null;

            // Step 1: Upsert header
            $ocn_no = 'GIL\\OCN' . $entry['ocn_no'];
            $headerId = DB::table('micro_line_plan_header')
                //->where('factory_id', $entry['factory_id'])
                ->where('unit_id', $entry['unit_id'])
                ->whereDate('plan_date', Carbon::now()->format('Y-m-d'))
                ->value('id');

            if (!$headerId && empty($headerId)) {
                $headerId = DB::table('micro_line_plan_header')->insertGetId([
                    'factory_id' => 3,
                    'unit_id' => $entry['unit_id'],
                    'item_name' => $entry['item_name'] ?? '',
                    'order_qty' => $entry['order_qty'] ?? 0,
                    'ship_date' => $entry['ship_date'] ?? null,
                    'plan_date' => Carbon::now()->format('Y-m-d'),
                    'created_by' => $entry['created_by'],
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);
            }

            // Step 2: Get existing details
            $existingDetails = DB::table('micro_line_plan_detail')
                ->where('header_id', $headerId)
                ->select('id', 'unit_code', 'line_no', 'item_id', 'color_id', 'planned_date', 'planned_qty', 'ocn_no')
                ->get()
                ->keyBy(function ($item) {
                    return "{$item->unit_code}_{$item->line_no}_{$item->item_id}_{$item->color_id}_{$item->ocn_no}";
                });

            $insertedCount = 0;
            $updatedCount = 0;
            $alreadyDeleted = [];
            $deletedIds = [];

            foreach ($details as $item) {

                if (!isset($item['unit_code'], $item['line_no'], $item['planned_date'], $item['planned_qty'], $item['item_id'], $item['color_id'])) {
                    continue;
                }

                $ocnKey = 'GIL\\OCN' . $entry['ocn_no'];

                $isSplit = isset($item['is_split']) ? $item['is_split'] : 'false';

                $deleteKey = "{$item['item_id']}_{$item['color_id']}_{$ocnKey}_{$isSplit}";
                $plannerdeleteKey = "{$item['item_id']}_{$item['line_no']}_{$item['color_id']}_{$ocnKey}_{$item['planner_opr']}";
                $plannerdragKey = "{$item['item_id']}_{$item['line_no']}_{$item['color_id']}_{$ocnKey}_{$item['taskisdragg']}";
                //echo '<pre>';
                //print_r($alreadyDeleted);
                //echo $deleteKey. '--'. $keydatas;
                
               
               
                if ($isSplit == 'true'  && !isset($alreadyDeleted[$deleteKey])) {

                  // echo(count($alreadyDeleted)) .'<br>';

                    // Mark as deleted
                    $alreadyDeleted[$deleteKey] = true;
                    $existingDetails = [];
           
                    $planDetailIds = DB::table('micro_line_plan_detail')
                            ->where('item_id', $item['item_id'])
                            ->where('color_id', $item['color_id'])
                            ->where('ocn_no', $ocnKey)
                            ->pluck('id')
                            ->toArray();
                            //dd($planDetailIds);

                        if (!empty($planDetailIds)) {
                            // Collect for output
                            $deletedIds = array_merge($deletedIds, $planDetailIds);

                            // Delete related data
                            DB::table('micro_plan_machine')
                                ->whereIn('plan_detail_id', $planDetailIds)
                                ->delete();

                            DB::table('micro_plan_operator')
                                ->whereIn('plan_detail_id', $planDetailIds)
                                ->delete();

                            DB::table('micro_line_plan_detail')
                                ->whereIn('id', $planDetailIds)
                                ->delete();
                        }

                        //dd($planDetailIds);
                }


                if ($item['planner_opr'] != '0'  && !isset($alreadyDeleted[$plannerdeleteKey])) {

                    // echo(count($alreadyDeleted)) .'<br>';
  
                      // Mark as deleted
                      $alreadyDeleted[$plannerdeleteKey] = true;
                      $existingDetails = [];
             
                      $planDetailIds = DB::table('micro_line_plan_detail')
                              ->where('item_id', $item['item_id'])
                              ->where('color_id', $item['color_id'])
                              ->where('ocn_no', $ocnKey)
                              ->where('line_no',$item['line_no'] )
                              ->pluck('id')
                              ->toArray();
                              //dd($planDetailIds);
  
                          if (!empty($planDetailIds)) {
                              // Collect for output
                              $deletedIds = array_merge($deletedIds, $planDetailIds);
  
                              // Delete related data
                              DB::table('micro_plan_machine')
                                  ->whereIn('plan_detail_id', $planDetailIds)
                                  ->delete();
  
                              DB::table('micro_plan_operator')
                                  ->whereIn('plan_detail_id', $planDetailIds)
                                  ->delete();
  
                              DB::table('micro_line_plan_detail')
                                  ->whereIn('id', $planDetailIds)
                                  ->delete();
                          }
  
                          //dd($planDetailIds);
                  }if($item['taskisdragg'] != '0' && !isset($alreadyDeleted[$plannerdragKey]) && $item['taskstatus'] != '0' ){

                    $alreadyDeleted[$plannerdragKey] = true;
                    $existingDetails = [];

                    $exitinglinechange = DB::table('micro_line_plan_detail')
                    //->where('factory_id', $entry['factory_id'])
                    ->where('id', $item['taskstatus'])
                   
                    ->first();

                  
           
                    $planDetaildragIds = DB::table('micro_line_plan_detail')
                            ->where('item_id', $exitinglinechange->item_id)
                            ->where('color_id', $exitinglinechange->color_id)
                            ->where('ocn_no', $ocnKey)
                            ->where('line_no', $exitinglinechange->line_no)
                           
                            ->pluck('id')
                            ->toArray();
                           // dd($planDetaildragIds);

                        if (!empty($planDetaildragIds)) {
                            // Collect for output
                            $deletedIds = array_merge($deletedIds, $planDetaildragIds);

                            // Delete related data
                            DB::table('micro_plan_machine')
                                ->whereIn('plan_detail_id', $planDetaildragIds)
                                ->delete();

                            DB::table('micro_plan_operator')
                                ->whereIn('plan_detail_id', $planDetaildragIds)
                                ->delete();

                            DB::table('micro_line_plan_detail')
                                ->whereIn('id', $planDetaildragIds)
                                ->delete();
                        }



                  }

                $selectedDates = $item['selected_dates'] ?? [];
                $ocnKey = 'GIL\\OCN' . $entry['ocn_no'];
                $lookupKey = "{$item['unit_code']}_{$item['line_no']}_{$item['item_id']}_{$item['color_id']}_{$ocnKey}";

                if (isset($existingDetails[$lookupKey])) {
                    $existing = $existingDetails[$lookupKey];
                    foreach ($selectedDates as $selectedDate) {

                       
                        if ((int)$existing->planned_qty !== (int)$item['planned_qty']) {
                            DB::table('micro_line_plan_detail')
                                ->where('id', $existing->id)
                                ->update([
                                    'planned_qty' => $item['planned_qty'],
                                    'planner_opr' => $item['planner_opr'],
                                    'planner_qty' => $item['planner_qty'],
                                    'no_opr' => $item['planner_opr'],
                                    'ocn_no' => $ocnKey,
                                    'updated_at' => now(),
                                    'line_no' => $item['line_no'],
                                    'item_id' => $item['item_id'],
                                    'color_id' => $item['color_id'],
                                    'color_name' => $item['color_name'] ?? '',
                                    'planned_date' => Carbon::parse($selectedDates[0]),
                                    'plan_enddate' => Carbon::parse($selectedDate),
                                ]);
                            $updatedCount++;
                            //echo '<pre>';
                            //print_r(Carbon::parse($selectedDate)->toDateString());
                        }

                    } if($item['planner_opr'] == '5'){
                        dd('stop');
                    }
                    
                } else {
                    $totalQty = $item['is_split_unit'] == 0 ? $item['planned_qty'] : $item['is_split_unit'];
                        $maxPerDay = $item['per_day_qty'];
                    foreach ($selectedDates as $selectedDate) {
                        
                        if ($totalQty >= $maxPerDay) {
                            $qty = $maxPerDay;
                        } else {
                            $qty = $totalQty; // whatever is remaining
                        }

                    //   $exitingrowcheck = DB::table('micro_line_plan_detail')
                    //   ->where('item_id', $item['item_id'])
                    //   ->where('color_id', $item['color_id'])
                    //   ->where('ocn_no', $ocnKey)
                    //   ->where('production_bal_sew', $totalQty < $item['per_day_qty'] ? $totalQty : $item['per_day_qty'])
                    //   ->count();

                    //   if($exitingrowcheck > 0){

                    //     DB::table('micro_line_plan_detail')
                    //     ->where('item_id', $item['item_id'])
                    //     ->where('color_id', $item['color_id'])
                    //     ->where('ocn_no', $ocnKey)
                    //     ->where('production_bal_sew', $totalQty < $item['per_day_qty'] ? $totalQty : $item['per_day_qty'])
                    //     ->update([
                            
                    //         'updated_at' => now(),
                    //         'line_no' => $item['line_no'],
                    //         'planned_date' => Carbon::parse($selectedDates[0]),
                    //         'plan_enddate' => Carbon::parse($selectedDate),
                    //     ]);

                    //   }


                       

                        $detailId = DB::table('micro_line_plan_detail')->insertGetId([
                            'header_id' => $headerId,
                            'unit_code' => $item['unit_code'],
                            'line_no' => $item['line_no'],
                            'item_id' => $item['item_id'],
                            'color_id' => $item['color_id'],
                            'color_name' => $item['color_name'] ?? '',
                            'planned_date' => Carbon::parse($selectedDates[0]),
                            'plan_enddate' => Carbon::parse($selectedDate),
                            'planned_qty' => $item['is_split_unit'] == 0 ? $item['planned_qty'] : $item['is_split_unit'],
                            'created_by' => $entry['created_by'],
                            'no_mach' => $item['no_of_machines'] ?? null,
                            'no_opr' => $item['no_operators'] ,
                            'item' => $item['item'],
                            'per_day_qty' => $item['per_day_qty'] ?? null,
                            'color' => $item['color'],
                            'ocn_no' => $ocnKey,
                            'ck_date' => $item['ck_date'],
                            'fi_date' => $item['FI_date'],
                            'planner_opr' => $item['planner_opr'] ?? null,
                            'planner_qty' => $item['planner_qty'] ?? null,
                            'buyer' => $item['buyer'] ?? null,
                            'priority' => $keydatas,
                            'created_at' => now(),
                            'updated_at' => now(),
                            'production_bal_sew' => $totalQty < $item['per_day_qty'] ? $totalQty : $item['per_day_qty']
                        ]);

                        $totalQty -= $qty;

                        // Stored procedures
                        $procResultsmech = DB::select("exec vaahini_erp_gainup.dbo.Mch_Type_Grade_Details_Proc @Ocn = ?, @Itemid = ?, @Colorid = ?, @Type = ?", [
                            $ocnKey,
                            $item['item_id'],
                            $item['color_id'],
                            $item['type'] ?? 'Mch'
                        ]);

                        $procResultsopr = DB::select("exec vaahini_erp_gainup.dbo.Mch_Type_Grade_Details_Proc @Ocn = ?, @Itemid = ?, @Colorid = ?, @Type = ?", [
                            $ocnKey,
                            $item['item_id'],
                            $item['color_id'],
                            $item['type'] ?? 'Opr'
                        ]);

                        foreach ($procResultsmech as $row) {
                            DB::table('micro_plan_machine')->insert([
                                'plan_detail_id' => $detailId,
                                'ocn' => $row->Order_No ?? null,
                                'type' => $row->Type ?? null,
                                'cnt' => $row->Cnt ?? null,
                            ]);
                        }

                        foreach ($procResultsopr as $row) {
                            DB::table('micro_plan_operator')->insert([
                                'plan_detail_id' => $detailId,
                                'ocn' => $row->Order_No ?? null,
                                'type' => $row->Type ?? null,
                                'cnt' => $row->Cnt ?? null,
                            ]);
                        }

                        $insertedCount++;
                    }
                }
            }

            $results[] = [
                'status' => 'success',
                'header_id' => $headerId,
                'inserted' => $insertedCount,
                'updated' => $updatedCount,
                'skipped' => count($details) - $insertedCount - $updatedCount,
                'deleted' => $alreadyDeleted,
                'ocn_no' => $entry['ocn_no'],
                'deleted' => $alreadyDeleted,
                'deleted_ids' => $deletedIds,
            ];
        }

        DB::commit();

        return response()->json([
            'status' => 'success',
            'summary' => [
                'total' => count($results),
                'successful' => collect($results)->where('status', 'success')->count(),
                'errors' => collect($results)->where('status', 'error')->count(),
            ],
            'results' => $results,
        ]);
    } catch (\Exception $e) {
        DB::rollBack();
        Log::error('InsertOrUpdateMicroPlan Error: ' . $e->getMessage());
        return response()->json(['status' => 'error', 'message' => $e->getMessage()], 500);
    }
    }



    public function newinsertOrUpdateMicroPlan(Request $request)
    {
    
        
        $details = $request->details;
        $details = is_string($details) ? json_decode($details, true) : $details;
    
        
    
       
        $plan_startdate = Carbon::parse($request->plan_date);
        $plan_enddate = Carbon::parse($request->plan_enddate);
    
        // Calculate the difference in days (inclusive)
        //$noOfDays = $plan_enddate->diffInDays($plan_startdate) + 1;
    
        // Validate basic input
        if (!$request->factory_id || !$request->unit_id || !$request->plan_date || !$request->created_by || !is_array($details)) {
            return response()->json(['status' => 'error', 'message' => 'Invalid or missing input data.'], 422);
        }
    
        try {
            DB::beginTransaction();
            //plan date is factory exit date in macro plan
            // Step 1: Upsert header
            $headerId = DB::table('clone_micro_line_plan_header')
                ->where('factory_id', $request->factory_id)
                ->where('unit_id', $request->unit_id)
                ->whereDate('plan_date', $request->plan_date)
                ->value('id');
    
            if (!$headerId) {
                $headerId = DB::table('clone_micro_line_plan_header')->insertGetId([
                    'factory_id' => $request->factory_id,
                    'unit_id' => $request->unit_id,
                    'item_name'=>$request->item_name,
                    'order_qty'=>$request->order_qty,
                    'ship_date'=>$request->ship_date,
                    'ocn_no'=>'GIL\OCN'.$request->ocn_no,
                    'plan_date' => $request->plan_date,
                    'created_by' => $request->created_by,
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);
            }
    
            // Step 2: Get existing details for comparison
            $existingDetails = DB::table('clone_micro_line_plan_detail')
                ->where('header_id', $headerId)
                ->select('id', 'unit_code', 'line_no', 'color_name', 'planned_date', 'planned_qty')
                ->get()
                ->keyBy(function ($item) {
                    return "{$item->unit_code}_{$item->line_no}_{$item->color_name}_{$item->planned_date}";
                });
    
            $toInsert = [];
            $toUpdate = [];
           
    
            foreach ($details as $item) {
                $key = "{$item['unit_code']}_{$item['line_no']}_{$item['color_name']}_{$item['planned_date']}";
    
                if (isset($existingDetails[$key])) {
                    
                    $existing = $existingDetails[$key];
                    if ((int)$existing->planned_qty !== (int)$item['planned_qty']) {
                        $toUpdate[] = [
                            'id' => $existing->id,
                            'planned_qty' => $item['planned_qty'],
                            'updated_at' => now(),
                        ];
                    }
                } else {
                    $startDate = Carbon::parse($item['planned_date']);
    
                    $selected_date = $item['selected_dates'] ;
                    $noOfDays = count($selected_date);
    
              
    
                    for ($i = 0; $i < $noOfDays; $i++) {
                       
                    $toInsert[] = [
                        'header_id' => $headerId,
                        'unit_code' => $item['unit_code'],
                        'line_no' => $item['line_no'],
                        'color_name' => $item['color_name'],
                        'planned_date' => $item['planned_date'],
                        'plan_enddate' =>Carbon::parse($selected_date[$i]),
                        'planned_qty' => $item['planned_qty'],
                        'no_mach' => $entry['no_of_machines'] ?? null,
                        'no_opr' => $entry['no_operators']?? null,
                        'created_by' => $request->created_by,
                        'created_at' => now(),
                        'updated_at' => now(),
                    ];
                }
    
    
                }
            }
    
            // Step 3: Insert new
            if (!empty($toInsert)) {
                DB::table('clone_micro_line_plan_detail')->insert($toInsert);
            }
    
            // Step 4: Update changed
            foreach ($toUpdate as $update) {
                DB::table('clone_micro_line_plan_detail')
                    ->where('id', $update['id'])
                    ->update([
                        'planned_qty' => $update['planned_qty'],
                        'updated_at' => $update['updated_at']
                    ]);
            }
    
            DB::commit();
    
            return response()->json([
                'status' => 'success',
                'header_id' => $headerId,
                'inserted' => count($toInsert),
                'updated' => count($toUpdate),
                'skipped' => count($details) - count($toInsert) - count($toUpdate),
            ]);
    
        } catch (\Exception $e) {
            DB::rollBack();
            Log::error('InsertOrUpdateMicroPlan Error: ' . $e->getMessage());
            return response()->json(['status' => 'error', 'message' => $e->getMessage()], 500);
        }
    }
public function microplaningfilter(Request $request){
    // DB::connection('sqlsrv_secondary')->getPdo();
    //     echo "SQL Server connected successfully";
    //     exit;
    $fromdate = $request->input('from_date');
    $todate =  $request->input('to_date');
    $unitcode = $request->input('unit_code');

    $headerCount = DB::table('micro_line_plan_header')
    ->where('plan_date', Carbon::now()->format('Y-m-d'))
    ->count();

    // DB::table('micro_line_plan_header')
    //             ->where('id', 364)
    //             ->update([
    //                 'created_at' => Carbon::now()->format('Y-m-d'),
    //                 'updated_at' => now()
    //             ]);

    if ($headerCount == 0) {
        //dd($request->input());
        //return $this->autoplanupdate($request); // Calls the method only if no headers exist for today
    }


  

    $result = DB::select("exec Micro_Planning_Input @Unit_Code = ? , @Fdate = ? , @Tdate = ? ", [$unitcode,$fromdate ,$todate]);
    $linedate = DB::select("exec vaahini_erp_gainup.dbo.NH_Days_Mic_Plan_Proc  @Fdate = ? , @Tdate = ? ", [$fromdate ,$todate]);
    $linedetails= DB::select("exec vaahini_erp_gainup.dbo.LP_Unitwise_Lines_Proc  @Unit = ? ", [$unitcode]);
    $swlinedetails= DB::select("exec vaahini_erp_gainup.dbo.LP_Unitwise_Lines_Proc_NewLine  @Unit = ? ", [$unitcode]);
    
    $data = DB::select("exec CHENNAI_ERP.dbo.View_For_Micro_Plan  @unit_ID = ? ", [$unitcode]);
   

    $resultbacklog = DB::select("exec Micro_Planning_Input_N @Unit_Code = ? ", [$unitcode]);
    
    // $data = DB::table('micro_line_plan_detail as d')
    //     ->join('micro_line_plan_header as h', 'h.id', '=', 'd.header_id')
    //     ->select(
    //         'd.id as detail_id',
    //         'd.ocn_no',
    //         'd.line_no',
    //         'd.planned_date as plan_date',
    //         'd.planned_qty',
    //         'd.fi_date as ship_date', // this date change in raj
    //         'd.planned_date as startdate',
    //         'd.plan_enddate as enddate',
    //         'd.item_id',
    //         'd.color_id',
    //         'd.item as Item',
    //         'd.color as Color',
    //         'd.no_mach',
    //         'd.no_opr',
    //         'd.ck_date',
    //         'd.fi_date as FI_date',

    //         'd.color as Color'

    //     )->where('h.unit_id','=',$unitcode)
    //     ->get();

        // $data = [];
        // foreach ($plandata as $pd) {
        //     $machineData = DB::table('micro_plan_machine')
        //         ->where('plan_detail_id', $pd->detail_id)
        //         ->get();
    
        //     $opData = DB::table('micro_plan_operator')
        //         ->where('plan_detail_id', $pd->detail_id)
        //         ->get();
    
        //     $data[] = [
        //         'plandata' => $pd,
        //         'machine'  => $machineData,
        //         'opr'      => $opData,
        //     ];
        // }

        $mach = 'Mch';
        $opr = 'OPr';
    
        // Machine data using stored procedure
        
        
    return $this->sendResponse(['linedate'=>$linedate,'linedetails'=>$linedetails,'ocndetails'=>$result,'plandata'=>$data,'pobacklog'=>$resultbacklog,'sftlinedetails'=>$swlinedetails], 'details');

}

public function newmicroplaningfilter(Request $request){
    $fromdate = $request->input('from_date');
    $todate =  $request->input('to_date');
    $unitcode = $request->input('unit_code');

    $result = DB::select("exec Micro_Planning_Input @Unit_Code = ? , @Fdate = ? , @Tdate = ? ", [$unitcode,$fromdate ,$todate]);
    $linedate = DB::select("exec vaahini_erp_gainup.dbo.NH_Days_Mic_Plan_Proc  @Fdate = ? , @Tdate = ? ", [$fromdate ,$todate]);
    $linedetails= DB::select("exec vaahini_erp_gainup.dbo.LP_Unitwise_Lines_Proc  @Unit = ? ", [$unitcode]);

    $plandata = DB::table('clone_micro_line_plan_detail as d')
        ->join('clone_micro_line_plan_header as h', 'h.id', '=', 'd.header_id')
        ->select(
            'd.id as detail_id', 
            'd.line_no',
            'd.planned_date',
            'd.planned_qty',
            'h.ship_date',
            'd.planned_date as startdate',
            'd.plan_enddate as enddate',
            'd.item_id as itemid' ,
            'd.color_id as colorid'

        )->where('h.unit_id','=',$unitcode)
        ->get();

        $data = [];
    foreach ($plandata as $pd) {
        $machineData = DB::table('micro_plan_machine')
            ->where('plan_detail_id', $pd->detail_id)
            ->get();

        $opData = DB::table('micro_plan_operator')
            ->where('plan_detail_id', $pd->detail_id)
            ->get();

        $data[] = [
            'plandata' => $pd,
            'machine'  => $machineData,
            'opr'      => $opData,
        ];
    }

       
        
    return $this->sendResponse(['linedate'=>$linedate,'linedetails'=>$linedetails,'ocndetails'=>$result,'plandata'=>$data], 'details');

}
public function unitdata(Request $request){
    $unitresult = DB::select('exec chennai_erp.dbo.Unit_Selection_Withdate_Proc');
    $sortedResult = collect($unitresult)->sortBy('Unit')->values()->all();
    return $this->sendResponse($sortedResult, 'details');
}

public function ocnsearch(Request $request){
    $result = DB::select("exec CHENNAI_ERP.dbo.View_For_Micro_Plan_N @unit_ID = NULL");
    return $this->sendResponse($result, 'details');
}




public function ordersummary(Request $request){
   
    $orderno = ($request->filled('orderno') && $request->get('orderno') !== 'GIL\\') 
    ? 'GIL\OCN'.$request->get('orderno') 
    : null;
    $itemid = $request->input('itemid');
    $colorid = $request->input('colorid');
    $unitid = $request->input('unit_code');

    //unit_code=7&orderno=04451&itemid=1007&colorid=877
    $ordersummary = DB::select('exec vaahini_erp_gainup.dbo.Order_Summary_For_Mic_Plan_Proc  @Ocn = ?  , @Itemid = ? , @Colorid = ?,@U_Code=? ' , [$orderno , $itemid , $colorid, $unitid]);
    return $this->sendResponse($ordersummary, 'details');
}

    public function ocnlineplan(Request $request){
        $orderno = ($request->filled('ocn_no') && $request->get('ocn_no') !== 'GIL\\') 
        ? 'GIL\\OCN'.$request->get('ocn_no') 
        : null;
        $unitid = $request->input('unit_code');

        $plandetails = DB::select('exec vaahini_erp_gainup.dbo.Macro_Plan_New_Proc  @Type = ?  ,@Month_Name = ? , @Unit_Code = ?, @Capacity_Type = ? ,@Ocn = ?  ' , ['LinePlan' , NULL , $unitid ,NULL, $orderno]);
    
        return $this->sendResponse($plandetails, 'details');
       
        
    }


    public function ocnitemlinePlan(Request $request){
        $orderno = ($request->filled('ocn_no') && $request->get('ocn_no') !== 'GIL\\') 
        ? 'GIL\\OCN'.$request->get('ocn_no') 
        : null;
        $itemid = $request->input('itemid') ?? NULL;
        $unitid = $request->input('unit_code');

      

        $plandetails = DB::select('exec vaahini_erp_gainup.dbo.Macro_Plan_New_Proc  @Type = ?  ,@Month_Name = ? , @Unit_Code = ?, @Capacity_Type = ? ,@Ocn = ?,  @Itemid = ?' , ['LinePlan_N' , NULL , $unitid ,NULL, $orderno,$itemid]);
    
        return $this->sendResponse($plandetails, 'details');
       
        
    }

    public function deleteMicroPlan(Request $request)
    {
        try {
            $ocn_no = $request->filled('ocn_no') && $request->get('ocn_no') !== 'GIL\\' 
                ? 'GIL\\OCN' . $request->get('ocn_no') 
                : null;
    
            $item_id = $request->input('item_id');
            $color_id = $request->input('color_id');
            $unit_id = $request->input('unit_id');
            $lineno = $request->input('line_no');
            $unitid = $request->input('unitid');

            //dd($request->input());
    
            // Validate input
            if (!$ocn_no || !$item_id || !$color_id || !$unit_id || !$lineno) {
                return response()->json(['error' => 'Missing required fields'], 400);
            }

            $toBeDeletedRows = DB::table('micro_line_plan_detail')->where('ocn_no', $ocn_no)->where('unit_code',$unit_id)->get();

            foreach ($toBeDeletedRows as $deletedRow) {
                $this->logMicroDetail('DELETE', $deletedRow->id, $deletedRow, 47781);
            }
    
            // Begin transaction
            DB::beginTransaction();
    
            // Get header record
           
    
            // Build the query
            $query = DB::table('micro_line_plan_detail')
               
                 //->where('item_id', $item_id)
               // ->where('color_id', $color_id)
                ->where('unit_code', $unit_id)
                ->where('ocn_no', $ocn_no);
    
            // Optional: Debug SQL query
            // 
    
            // Execute delete
            $deleted = $query->delete();


            $querymachine = DB::table('micro_plan_machine')
               
               
                ->where('ocn', $ocn_no);
    
            // Optional: Debug SQL query
            // dd($query->toSql(), $query->getBindings());
    
            // Execute delete
            $deletedmachine = $querymachine->delete();

           

            $queryopr = DB::table('micro_plan_operator')
               
               
            ->where('ocn', $ocn_no);

        // Optional: Debug SQL query
        // dd($query->toSql(), $query->getBindings());

        // Execute delete
        $deletedoper = $queryopr->delete();
    
            if ($deleted) {
                DB::commit();
                return response()->json(['message' => 'Task deleted successfully and restored to backlog'], 200);
            } else {
                DB::rollBack();
                return response()->json(['error' => 'Task not found in database'], 404);
            }
        } catch (\Exception $e) {
            DB::rollBack();
            Log::error("Failed to delete micro plan: {$e->getMessage()}");
            return response()->json(['error' => 'Failed to delete task'], 500);
        }
    }


   public function deleteMicroPlanSingle(Request $request)
{
    try {
        $ocn_no = $request->filled('ocn_no') && $request->get('ocn_no') !== 'GIL\\'
            ? 'GIL\\OCN' . $request->get('ocn_no')
            : null;

        $item_id = $request->input('item_id');
        $color_id = $request->input('color_id');
        $unit_id = $request->input('unit_id');
        $line_no = $request->input('line_no');

        if (!$ocn_no || !$item_id || !$color_id || !$unit_id || !$line_no) {
            return response()->json(['error' => 'Missing required fields'], 400);
        }

        // Get all plan detail IDs
        $planDetailIds = DB::table('micro_line_plan_detail')
            ->where('item_id', $item_id)
            
            ->where('unit_code', $unit_id)
            ->where('ocn_no', $ocn_no)
            ->pluck('id');

        if ($planDetailIds->isEmpty()) {
            return response()->json(['error' => 'Task not found in database'], 404);
        }

        // Delete in chunks to handle large number of records
        $chunkSize = 1000; // you can increase/decrease depending on server
        $planDetailIds->chunk($chunkSize)->each(function ($chunk) {
            DB::table('micro_plan_machine')->whereIn('plan_detail_id', $chunk)->delete();
            DB::table('micro_plan_operator')->whereIn('plan_detail_id', $chunk)->delete();
            DB::table('micro_line_plan_detail')->whereIn('id', $chunk)->delete();
        });

        return response()->json(['message' => 'Task deleted successfully and restored to backlog'], 200);

    } catch (\Exception $e) {
        Log::error("Failed to delete micro plan: {$e->getMessage()}");
        return response()->json(['error' => 'Failed to delete task', 'details' => $e->getMessage()], 500);
    }
}


    public function machinedetails(Request $request)
    {
        $unit = $request->input('unit');
        $mach = 'Mch';
        $opr = 'OPr';
    
        // Machine data using stored procedure
        $machdata = DB::select(
            'exec VAAHINI_ERP_GAINUP.DBO.Operator_MCh_Cnt_Unit_Proc @Unit_Code = ?, @Arg = ?',
            [$unit, $mach]
        );
    
        // Operator data using same stored procedure but different params
        $oprdata = DB::select(
            'exec VAAHINI_ERP_GAINUP.DBO.Operator_MCh_Cnt_Unit_Proc @Unit_Code = ?, @Arg = ?',
            [$unit, $opr]
        );
    
        return $this->sendResponse([
            'machine' => $machdata,
            'operator' => $oprdata
        ], 'Details fetched successfully.');
    }


    public function operatorupdate(Request $request){
        $item_id = $request->input('itemid');
        $color_id = $request->input('colorid');
        $ocnno = $request->input('ocn_no');
        $taskstatus = $request->input('taskstatus');
        $Isdragg = $request->input('taskisdragg');
        $line_no = substr($request->input('lineno'), 1);
        $planned_opr = $request->input('planned_tailor_qty');
        $unitcode = $request->input('unit_code');

        $updated = DB::table('micro_line_plan_detail')
        ->where('ocn_no', "GIL\\".$ocnno)
        ->where('item_id', $item_id)
        ->where('color_id', $color_id)
        ->where('line_no', $line_no)
        ->update([
            'planner_opr' => $planned_opr,
            'updated_at' => now()
        ]);
    
    if ($updated) {
        return response()->json(['status' => 'success', 'message' => 'Operator updated.']);
    } else {
        return response()->json(['status' => 'fail', 'message' => 'No matching record found or no changes made.']);
    }

    }

     
    public function autoplanupdate(Request $request)
    {
        $unitresult = DB::select('exec vaahini_erp_gainup.dbo.Unit_Selection_Proc');
        $sortedResult = collect($unitresult)->sortBy('Unit')->values()->all();
    
        foreach ($unitresult as $unitdata) {
            $headerId = DB::table('micro_line_plan_header')
                ->where('unit_id', $unitdata->Unit_Code)
                ->whereDate('plan_date', Carbon::now()->format('Y-m-d'))
                ->value('id');
    
            // Insert header if not exists
            if (!$headerId) {
                $headerId = DB::table('micro_line_plan_header')->insertGetId([
                    'factory_id' => 3,
                    'unit_id' => $unitdata->Unit_Code,
                    'item_name' => '',
                    'order_qty' => 0,
                    'ship_date' => Carbon::now()->format('Y-m-d'),
                    'plan_date' => NULL,
                    'created_by' => 0,
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);
            }
    
            $plandata = DB::select("exec CHENNAI_ERP.dbo.View_For_Micro_Plan @unit_ID = ?", [$unitdata->Unit_Code]);
    
            $toInsert = [];
    
            foreach ($plandata as $plans) {
                // Skip if color is null
                if (empty($plans->Color)) {
                    continue;
                }


              
    
                $existing = DB::table('micro_line_plan_detail')
                    ->where('unit_code', $unitdata->Unit_Code)
                    ->where('line_no', $plans->line_no)
                    ->where('planned_date', $plans->startdate)
                    ->where('item_id', $plans->item_id)
                    ->where('color_id', $plans->color_id)
                    ->first();
    
               // if (!empty($existing)) {
                    $toInsert[] = [
                        'header_id'     => $headerId,
                        'unit_code'     => $plans->factory_id,
                        'line_no'       => $plans->line_no,
                        'color_name'    => $plans->Color,
                        'planned_date'  => $plans->startdate,
                        'planned_qty'   => $plans->planned_qty,
                        'created_by'    => 0,
                        'created_at'    => now(),
                        'updated_at'    => now(),
                        'plan_enddate'  => $plans->plan_enddate,
                        'color_id'      => $plans->color_id,
                        'item_id'       => $plans->item_id,
                        'item'          => $plans->Item,
                        'color'         => $plans->Color,
                        'no_mach'       => $plans->no_mach,
                        'no_opr'        => $plans->no_opr,
                        'ocn_no'        => $plans->ocn_no,
                        'priority'      => 0,
                        'ck_date'       => $plans->ck_date,
                        'fi_date'       => $plans->FI_Date,
                        'planner_qty'   => $plans->planner_qty,
                        'planner_opr'   => $plans->planner_opr,
                        'cron_status'   => '2',
                        'per_day_qty'   => $plans->per_day_qty
                    ];
                }
            //}
   // dd($toInsert);
            if (!empty($toInsert)) {
                $chunks = array_chunk($toInsert, 75); // stay under 2100 parameter limit
    
                foreach ($chunks as $chunk) {
                    foreach ($chunk as $row) {
                        $detailId = DB::table('micro_line_plan_detail')->insertGetId($row);
    
                        // Procedure for Machine types
                        $procResultsmech = DB::select("exec vaahini_erp_gainup.dbo.Mch_Type_Grade_Details_Proc @Ocn = ?, @Itemid = ?, @Colorid = ?, @Type = ?", [
                            $row['ocn_no'],
                            $row['item_id'],
                            $row['color_id'],
                            'Mch'
                        ]);
    
                        foreach ($procResultsmech as $mach) {
                            DB::table('micro_plan_machine')->insert([
                                'plan_detail_id' => $detailId,
                                'ocn'            => $mach->Order_No ?? null,
                                'type'           => $mach->Type ?? null,
                                'cnt'            => $mach->Cnt ?? null,
                            ]);
                        }
    
                        // Procedure for Operator types
                        $procResultsopr = DB::select("exec vaahini_erp_gainup.dbo.Mch_Type_Grade_Details_Proc @Ocn = ?, @Itemid = ?, @Colorid = ?, @Type = ?", [
                            $row['ocn_no'],
                            $row['item_id'],
                            $row['color_id'],
                            'Opr'
                        ]);
    
                        foreach ($procResultsopr as $opr) {
                            DB::table('micro_plan_operator')->insert([
                                'plan_detail_id' => $detailId,
                                'ocn'            => $opr->Order_No ?? null,
                                'type'           => $opr->Type ?? null,
                                'cnt'            => $opr->Cnt ?? null,
                            ]);
                        }
                    }
                }
            }
    
            DB::table('micro_line_plan_header')
                ->where('id', $headerId)
                ->update([
                    'plan_date' => Carbon::now()->format('Y-m-d'),
                    'updated_at' => now()
                ]);
        }
    
        return response()->json(['status' => 'success', 'message' => 'Auto update completed.']);

        return $this->microplaningfilter($request);
    }
    
    
    
    public function workingdays(Request $request){
        $fromdate = $request->input('from_date');
        $todate =  $request->input('to_date');
        $result = DB::select("exec vaahini_erp_gainup.dbo.NH_Days_Mic_Plan_Proc  @Fdate = ? , @Tdate = ? ", [$fromdate ,$todate]);
        return $this->sendResponse($result, 'details');

    }
    

    public function autoplanupdate12(Request $request)
    {
        $unitresult = DB::select('exec vaahini_erp_gainup.dbo.Unit_Selection_Proc');
        $sortedResult = collect($unitresult)->sortBy('Unit')->values()->all();
    
        foreach ($unitresult as $unitdata) {
            $headerId = DB::table('clone_micro_line_plan_header')
                ->where('unit_id', $unitdata->Unit_Code)
                ->whereDate('plan_date', Carbon::now()->format('Y-m-d'))
                ->value('id');
    
            // Insert header if not exists
            if (!$headerId) {
                $headerId = DB::table('clone_micro_line_plan_header')->insertGetId([
                    'factory_id' => 3,
                    'unit_id' => $unitdata->Unit_Code,
                    'item_name' => '',
                    'order_qty' => 0,
                    'ship_date' => Carbon::now()->format('Y-m-d'),
                    'plan_date' => NULL,
                    'created_by' => 0,
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);
            }
    
            $plandata = DB::select("exec CHENNAI_ERP.dbo.View_For_Micro_Plan @unit_ID = ?", [$unitdata->Unit_Code]);
    
            $toInsert = [];
    
            foreach ($plandata as $plans) {
                // Skip if color is null
                if (empty($plans->Color)) {
                    continue;
                }


              
    
                $existing = DB::table('clone_micro_line_plan_detail')
                    ->where('unit_code', $unitdata->Unit_Code)
                    ->where('line_no', $plans->line_no)
                    ->where('planned_date', $plans->startdate)
                    ->where('item_id', $plans->item_id)
                    ->where('color_id', $plans->color_id)
                    ->first();
    
               // if (!empty($existing)) {
                    $toInsert[] = [
                        'header_id'     => $headerId,
                        'unit_code'     => $plans->factory_id,
                        'line_no'       => $plans->line_no,
                        'color_name'    => $plans->Color,
                        'planned_date'  => $plans->startdate,
                        'planned_qty'   => $plans->planned_qty,
                        'created_by'    => 0,
                        'created_at'    => now(),
                        'updated_at'    => now(),
                        'plan_enddate'  => $plans->plan_enddate,
                        'color_id'      => $plans->color_id,
                        'item_id'       => $plans->item_id,
                        'item'          => $plans->Item,
                        'color'         => $plans->Color,
                        'no_mach'       => $plans->no_mach,
                        'no_opr'        => $plans->no_opr,
                        'ocn_no'        => $plans->ocn_no,
                        'priority'      => 0,
                        'ck_date'       => $plans->ck_date,
                        'fi_date'       => $plans->FI_Date,
                        'planner_qty'   => $plans->planner_qty,
                        'planner_opr'   => $plans->planner_opr,
                        'cron_status'   => '2',
                        'per_day_qty'   => $plans->per_day_qty
                    ];
                }
            //}
   // dd($toInsert);
            if (!empty($toInsert)) {
                $chunks = array_chunk($toInsert, 75); // stay under 2100 parameter limit
    
                foreach ($chunks as $chunk) {
                    foreach ($chunk as $row) {
                        $detailId = DB::table('clone_micro_line_plan_detail')->insertGetId($row);
    
                        // Procedure for Machine types
                        $procResultsmech = DB::select("exec vaahini_erp_gainup.dbo.Mch_Type_Grade_Details_Proc @Ocn = ?, @Itemid = ?, @Colorid = ?, @Type = ?", [
                            $row['ocn_no'],
                            $row['item_id'],
                            $row['color_id'],
                            'Mch'
                        ]);
    
                        foreach ($procResultsmech as $mach) {
                            DB::table('clone_micro_plan_machine')->insert([
                                'plan_detail_id' => $detailId,
                                'ocn'            => $mach->Order_No ?? null,
                                'type'           => $mach->Type ?? null,
                                'cnt'            => $mach->Cnt ?? null,
                            ]);
                        }
    
                        // Procedure for Operator types
                        $procResultsopr = DB::select("exec vaahini_erp_gainup.dbo.Mch_Type_Grade_Details_Proc @Ocn = ?, @Itemid = ?, @Colorid = ?, @Type = ?", [
                            $row['ocn_no'],
                            $row['item_id'],
                            $row['color_id'],
                            'Opr'
                        ]);
    
                        foreach ($procResultsopr as $opr) {
                            DB::table('clone_micro_plan_operator')->insert([
                                'plan_detail_id' => $detailId,
                                'ocn'            => $opr->Order_No ?? null,
                                'type'           => $opr->Type ?? null,
                                'cnt'            => $opr->Cnt ?? null,
                            ]);
                        }
                    }
                }
            }
    
            DB::table('clone_micro_line_plan_header')
                ->where('id', $headerId)
                ->update([
                    'plan_date' => Carbon::now()->format('Y-m-d'),
                    'updated_at' => now()
                ]);
        }
    
        //return response()->json(['status' => 'success', 'message' => 'Auto update completed.']);

        return $this->microplaningfilter($request);
    }


    public function insertOrUpdateMicroPlan_New_29(Request $request)
{
    // Decode and validate input
    $data = $request->all();
    $data = is_string($data) ? json_decode($data, true) : $data;
    if (!is_array($data)) {
        return response()->json(['status' => 'error', 'message' => 'Invalid input data.'], 422);
    }

    try {
        DB::beginTransaction();
        $results = [];

        foreach ($data as $keydatas => $entry) {
            $details = $entry['details'] ?? [];
            $details = is_string($details) ? json_decode($details, true) : $details;

            if (!isset($entry['factory_id'], $entry['unit_id'], $entry['plan_date'], $entry['created_by']) || !is_array($details)) {
                $results[] = ['status' => 'error', 'message' => 'Missing required fields in entry.', 'entry' => $entry];
                continue;
            }

            $plan_startdate = Carbon::parse($entry['plan_date']);
            $plan_enddate = isset($entry['plan_enddate']) ? Carbon::parse($entry['plan_enddate']) : null;

            // Step 1: Upsert header
            $ocn_no = 'GIL\\OCN' . $entry['ocn_no'];
            $headerId = DB::table('micro_line_plan_header')
                ->where('unit_id', $entry['unit_id'])
                ->whereDate('plan_date', Carbon::now()->format('Y-m-d'))
                ->value('id');

            if (!$headerId) {
                $headerId = DB::table('micro_line_plan_header')->insertGetId([
                    'factory_id' => 3,
                    'unit_id' => $entry['unit_id'],
                    'item_name' => $entry['item_name'] ?? '',
                    'order_qty' => $entry['order_qty'] ?? 0,
                    'ship_date' => $entry['ship_date'] ?? null,
                    'plan_date' => Carbon::now()->format('Y-m-d'),
                    'created_by' => $entry['created_by'],
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);
            }

            // Step 2: Get existing details with planned_date in the key
            $existingDetails = DB::table('micro_line_plan_detail')
                ->where('header_id', $headerId)
                ->select('id', 'unit_code', 'line_no', 'item_id', 'color_id', 'planned_date', 'planned_qty', 'ocn_no')
                ->get()
                ->keyBy(function ($item) {
                    return "{$item->unit_code}_{$item->line_no}_{$item->item_id}_{$item->color_id}_{$item->ocn_no}_" . Carbon::parse($item->planned_date)->format('Y-m-d');
                });

            $insertedCount = 0;
            $updatedCount = 0;
            $skippedCount = 0;
            $alreadyDeleted = [];
            $deletedIds = [];

            foreach ($details as $item) {
                if (!isset($item['unit_code'], $item['line_no'], $item['planned_date'], $item['planned_qty'], $item['item_id'], $item['color_id'])) {
                    $skippedCount++;
                    continue;
                }

                $ocnKey = 'GIL\\OCN' . $entry['ocn_no'];
                $isSplit = isset($item['is_split']) ? $item['is_split'] : 'false';
                $taskstatus = isset($item['taskstatus']) ? $item['taskstatus'] : '0';

                // Handle deletions for split units or planner operations
                $deleteKey = "{$item['item_id']}_{$item['color_id']}_{$ocnKey}_{$isSplit}";
                $plannerdeleteKey = "{$item['item_id']}_{$item['line_no']}_{$item['color_id']}_{$ocnKey}_{$item['planner_opr']}";
                $plannerdragKey = "{$item['item_id']}_{$item['line_no']}_{$item['color_id']}_{$ocnKey}_{$item['taskisdragg']}";

                if ($isSplit == 'true' && !isset($alreadyDeleted[$deleteKey])) {
                    $alreadyDeleted[$deleteKey] = true;
                    $planDetailIds = DB::table('micro_line_plan_detail')
                        ->where('item_id', $item['item_id'])
                        ->where('color_id', $item['color_id'])
                        ->where('ocn_no', $ocnKey)
                        ->pluck('id')
                        ->toArray();

                    if (!empty($planDetailIds)) {
                        $deletedIds = array_merge($deletedIds, $planDetailIds);
                        DB::table('micro_plan_machine')->whereIn('plan_detail_id', $planDetailIds)->delete();
                        DB::table('micro_plan_operator')->whereIn('plan_detail_id', $planDetailIds)->delete();
                        DB::table('micro_line_plan_detail')->whereIn('id', $planDetailIds)->delete();
                    }
                }

                if ($item['planner_opr'] != '0' && !isset($alreadyDeleted[$plannerdeleteKey])) {
                    $alreadyDeleted[$plannerdeleteKey] = true;
                    $planDetailIds = DB::table('micro_line_plan_detail')
                        ->where('item_id', $item['item_id'])
                        ->where('color_id', $item['color_id'])
                        ->where('ocn_no', $ocnKey)
                        ->where('line_no', $item['line_no'])
                        ->pluck('id')
                        ->toArray();

                    if (!empty($planDetailIds)) {
                        $deletedIds = array_merge($deletedIds, $planDetailIds);
                        DB::table('micro_plan_machine')->whereIn('plan_detail_id', $planDetailIds)->delete();
                        DB::table('micro_plan_operator')->whereIn('plan_detail_id', $planDetailIds)->delete();
                        DB::table('micro_line_plan_detail')->whereIn('id', $planDetailIds)->delete();
                    }
                }

                if ($item['taskisdragg'] != '0' && !isset($alreadyDeleted[$plannerdragKey]) && $taskstatus != '0') {
                    $alreadyDeleted[$plannerdragKey] = true;
                    $exitinglinechange = DB::table('micro_line_plan_detail')
                        ->where('id', $item['taskstatus'])
                        ->first();

                    if (!empty($exitinglinechange)) {
                        $planDetaildragIds = DB::table('micro_line_plan_detail')
                            ->where('item_id', $exitinglinechange->item_id)
                            ->where('color_id', $exitinglinechange->color_id)
                            ->where('ocn_no', $ocnKey)
                            ->where('line_no', $exitinglinechange->line_no)
                            ->pluck('id')
                            ->toArray();

                        if (!empty($planDetaildragIds)) {
                            $deletedIds = array_merge($deletedIds, $planDetaildragIds);
                            DB::table('micro_plan_machine')->whereIn('plan_detail_id', $planDetaildragIds)->delete();
                            DB::table('micro_plan_operator')->whereIn('plan_detail_id', $planDetaildragIds)->delete();
                            DB::table('micro_line_plan_detail')->whereIn('id', $planDetaildragIds)->delete();
                        }
                    }
                }else if($item['taskisdragg'] == '0' && !isset($alreadyDeleted[$plannerdragKey]) ){

                    $alreadyDeleted[$plannerdragKey] = true;
                    


                }

                $selectedDates = $item['selected_dates'] ?? [];
                $totalQty = $item['is_split_unit'] == 0 ? $item['planned_qty'] : $item['is_split_unit'];
                $maxPerDay = $item['planner_qty'] ?: $item['per_day_qty'];

                foreach ($selectedDates as $selectedDate) {
                    // Create a unique key including planned_date
                    $lookupKey = "{$item['unit_code']}_{$item['line_no']}_{$item['item_id']}_{$item['color_id']}_{$ocnKey}_" . Carbon::parse($selectedDate)->format('Y-m-d');

                    // Skip if record already exists for this date
                    if (isset($existingDetails[$lookupKey])) {
                        $existing = $existingDetails[$lookupKey];
                        if ((int)$existing->planned_qty !== (int)$item['planned_qty']) {
                            DB::table('micro_line_plan_detail')
                                ->where('id', $existing->id)
                                ->update([
                                    'updated_at' => now(),
                                    'task_type' => '7-' . $item['taskisdragg'] . '-' . (int)$existing->planned_qty . '-' . (int)$item['planned_qty'],
                                    'updated_by' => $entry['created_by']
                                ]);
                            $updatedCount++;
                        } else {
                            $skippedCount++;
                        }
                        continue;
                    }

                    // Calculate quantity for the day
                    $qty = $totalQty >= $maxPerDay ? $maxPerDay : $totalQty;
                    $production_bal_sew = $totalQty < $maxPerDay ? $totalQty : $maxPerDay;
                    $totalQty -= $qty;

                    if ($qty <= 0) {
                        $skippedCount++;
                        continue;
                    }

                    // Insert new record
                    $isSplit = !empty($item['is_split']) ? (int)$item['is_split'] : 0;
                    $detailId = DB::table('micro_line_plan_detail')->insertGetId([
                        'line_no' => $item['line_no'],
                        'header_id' => $headerId,
                        'unit_code' => $item['unit_code'],
                        'item_id' => $item['item_id'],
                        'color_id' => $item['color_id'],
                        'color_name' => $item['color'] ?? '',
                        'planned_date' => Carbon::parse($selectedDates[0]),
                        'plan_enddate' => Carbon::parse($selectedDate),
                        'planned_qty' => ($item['is_split_unit'] == 0) ? ($item['planned_qty'] > 0 ? $qty : 100) : $item['is_split_unit'],
                        'created_by' => $entry['created_by'],
                        'no_mach' => $item['no_of_machines'] ?? null,
                        'no_opr' => $item['no_operators'],
                        'item' => $item['item'],
                        'per_day_qty' => $item['per_day_qty'] ?? null,
                        'color' => $item['color'],
                        'ocn_no' => $ocnKey,
                        'ck_date' => $item['ck_date'],
                        'fi_date' => $item['FI_date'],
                        'planner_opr' => $item['planner_opr'] ?? null,
                        'planner_qty' => $item['planner_qty'] ?? null,
                        'buyer' => $item['buyer'] ?? null,
                        'priority' => $keydatas,
                        'created_at' => now(),
                        'updated_at' => now(),
                        'production_bal_sew' => $production_bal_sew,
                        'task_type' => ($isSplit == 0) ? ($item['taskisdragg'] ?? 0) : 3
                    ]);

                    // Stored procedures
                    $procResultsmech = DB::select("exec vaahini_erp_gainup.dbo.Mch_Type_Grade_Details_Proc @Ocn = ?, @Itemid = ?, @Colorid = ?, @Type = ?", [
                        $ocnKey,
                        $item['item_id'],
                        $item['color_id'],
                        $item['type'] ?? 'Mch'
                    ]);

                    $procResultsopr = DB::select("exec vaahini_erp_gainup.dbo.Mch_Type_Grade_Details_Proc @Ocn = ?, @Itemid = ?, @Colorid = ?, @Type = ?", [
                        $ocnKey,
                        $item['item_id'],
                        $item['color_id'],
                        $item['type'] ?? 'Opr'
                    ]);

                    foreach ($procResultsmech as $row) {
                        DB::table('micro_plan_machine')->insert([
                            'plan_detail_id' => $detailId,
                            'ocn' => $row->Order_No ?? null,
                            'type' => $row->Type ?? null,
                            'cnt' => $row->Cnt ?? null,
                        ]);
                    }

                    foreach ($procResultsopr as $row) {
                        DB::table('micro_plan_operator')->insert([
                            'plan_detail_id' => $detailId,
                            'ocn' => $row->Order_No ?? null,
                            'type' => $row->Type ?? null,
                            'cnt' => $row->Cnt ?? null,
                        ]);
                    }

                    $insertedCount++;
                }
            }

            $results[] = [
                'status' => 'success',
                'header_id' => $headerId,
                'inserted' => $insertedCount,
                'updated' => $updatedCount,
                'skipped' => $skippedCount,
                'deleted' => $alreadyDeleted,
                'ocn_no' => $entry['ocn_no'],
                'deleted_ids' => $deletedIds,
            ];
        }

        DB::commit();

        return response()->json([
            'status' => 'success',
            'summary' => [
                'total' => count($results),
                'successful' => collect($results)->where('status', 'success')->count(),
                'errors' => collect($results)->where('status', 'error')->count(),
            ],
            'results' => $results,
        ]);
    } catch (\Exception $e) {
        DB::rollBack();
        Log::error('InsertOrUpdateMicroPlan Error: ' . $e->getMessage());
        return response()->json(['status' => 'error', 'message' => $e->getMessage()], 500);
    }
}
private function logMicroDetail($action, $planDetailId, $data, $userId)
{
    DB::table('micro_line_plan_detail_log')->insert([
        'action_type' => $action, // 'INSERT', 'UPDATE', 'DELETE'
        'plan_detail_id' => $planDetailId,
        'detail_json' => json_encode($data),
        'created_by' => $userId,
        'created_at' => now(),
    ]);
}

public function plannedvsactual(Request $request){   
    $unitid = $request->input('unit_code') ?? null;
    $unitid=null;
    $plandetails = DB::select('exec chennai_erp.dbo.Planned_Vs_Actual_Proc @Unit_Code = ? ' , [$unitid]);
    return $this->sendResponse($plandetails, 'details');
}


public function lineplanReport(Request $request){
     $unitid = $request->input('unit_code') ?? null;
     //$unitid = $request->filled('unit_code') ? (int) $request->input('unit_code') : null;
//dd($unitid);
     $fromdate = $request->input('from_date');
     $todate = $request->input('to_date', $request->input('from_date'));
    if ($unitid != 'null') {
         
    $unitid = (int)$request->input('unit_code');
    $sql = 'exec chennai_erp.dbo.Line_Planning_Report_Proc @Fdate = ?, @Tdate = ?, @Unit_Code = ?';
    $params = [$fromdate, $todate, $unitid];
    } else {
        $sql = 'exec chennai_erp.dbo.Line_Planning_Report_Proc @Fdate = ?, @Tdate = ?';
        $params = [$fromdate, $todate];
    }

$plandetails = DB::select($sql, $params);

    return $this->sendResponse($plandetails, 'details');

}

public function plandelete(Request $request){

    try {
        $ocn_no = $request->filled('ocn_no') && $request->get('ocn_no') !== 'GIL\\' 
            ? 'GIL\\OCN' . $request->get('ocn_no') 
            : null;
			$unitid = $request->input('unitid');

        $item_id = $request->input('item_id');
       
        
        // Begin transaction
        DB::beginTransaction();
		$microplandetail = DB::table('micro_line_plan_detail')
						->where('ocn_no', $ocn_no)
						->first();

		if ($microplandetail) {
			return response()->json([
				'error' => 'Please delete your micro plan � this unit is planned ' . $microplandetail->unit_code
			], 404);
		}       

        // Build the query
        $query = DB::table('planning_assign')
           
            
            ->where('ocn_no', $ocn_no);

        
        $deleted = $query->delete();
        if ($deleted) {
            DB::commit();
            return response()->json(['message' => 'Plan deleted successfully and restored to backlog'], 200);
        } else {
            DB::rollBack();
            return response()->json(['error' => 'Plan not found in database'], 404);
        }
    } catch (\Exception $e) {
        DB::rollBack();
        Log::error("Failed to delete micro plan: {$e->getMessage()}");
        return response()->json(['error' => 'Failed to delete task'], 500);
    }

}

public function loginUpdate(Request $request)
    {

         DB::table('user_logins')
            ->where('is_online', 0) // only online users
            ->where('last_login_at', '<', Carbon::now()->subMinutes(15)) // ⬅️ check 15 mins old
            ->update([
                'is_online'      => 1,
                
            ]);
       
            DB::table('user_logins')->insert([
                'empid'          => $request->emp_id,
                'is_online'      => false,
                'last_logout_at' => now(),
                'last_login_at'=>now(),
                'created_at'     => now(),
                'updated_at'     => now(),
            ]);

            $offlineusers =  DB::table('user_logins')
            ->where('is_online', 0) // only online users
            //->where('last_login_at', '<', Carbon::now()->subMinutes(15))
            ->where('empid',64441)
            ->first();

            if($offlineusers != ''){
                $status = true;
                $msg = 'planner Planned Board';
               
            }else{
                 $status = true;
                 
                   $msg = 'planner offline';
                
            }
        return response()->json([
            'status'  => true,
            'message' => $msg,
            'empid'   => $request->emp_id
        ]);
    }
   
   
}