<?php

namespace App\Http\Controllers;

use App\Models\SubLedgerMaster;
use App\Models\LedgerMaster;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Validator;
use Illuminate\Support\Facades\Log;

class SubLedgerMasterController extends Controller
{
    public function index(Request $request)
    {
        try {
            $query = SubLedgerMaster::query();

            if ($request->has('ledger_id')) {
                $query->whereJsonContains('Lg_Id', (int) $request->ledger_id);
            }

            if ($request->has('status')) {
                $query->where('SL_Status', $request->boolean('status'));
            }

            // ✅ NEW FILTER FOR SL_TYPE
            if ($request->has('sl_type')) {
                $query->where('SL_Type', $request->sl_type);
            }

            if ($request->filled('search')) {
                $query->search($request->search);
            }

            if ($request->has('Is_cnl_cheq')) {
                $value = strtoupper($request->Is_cnl_cheq);
                if (in_array($value, ['Y', 'N'])) {
                    $query->where('Is_cnl_cheq', $value);
                }
            }

            if ($request->has('is_party')) {
                $input = $request->get('is_party');
                $isParty = in_array(strtolower($input), ['y', '1', 'true'], true) ? 'Y' : 'N';

                $ledgerIds = LedgerMaster::whereHas('ledgerGroup', function ($groupQuery) use ($isParty) {
                    $groupQuery->where('Gr_Prt', $isParty);
                })->pluck('Lg_Id')->toArray();

                if (empty($ledgerIds)) {
                    $query->whereRaw('1 = 0');
                } else {
                    $query->where(function ($q) use ($ledgerIds) {
                        foreach ($ledgerIds as $lid) {
                            $q->orWhereJsonContains('Lg_Id', $lid);
                        }
                    });
                }
            }

// Filter by bank verification
if ($request->has('Is_Bank_Verified')) {

    $value = strtoupper($request->Is_Bank_Verified);

    if (in_array($value, ['Y', 'N', 'R'])) {
        $query->where('Is_Bank_Verified', $value);
    }
}


// Filter records having complete bank details
if ($request->has('Is_Bank_Details')) {

    $value = strtolower($request->Is_Bank_Details);

    if (in_array($value, ['y', '1', 'true'])) {

        $query->whereNotNull('Bank_Name')
            ->where('Bank_Name', '!=', '')

            ->whereNotNull('Branch_Name')
            ->where('Branch_Name', '!=', '')

            ->whereNotNull('Account_Holder_Name')
            ->where('Account_Holder_Name', '!=', '')

            ->whereNotNull('Account_Number')
            ->where('Account_Number', '!=', '')

            ->whereNotNull('IFSC_Code')
            ->where('IFSC_Code', '!=', '')

            ->whereNotNull('Account_Type')
            ->where('Account_Type', '!=', '');
    }
}

            $sortBy = $request->get('sort_by', 'created_at');
            $sortOrder = $request->get('sort_order', 'desc');
            $query->orderBy($sortBy, $sortOrder);

            $perPage = $request->get('per_page', 15);
            $subLedgers = $query->paginate($perPage);

            $allLedgerIds = $subLedgers->flatMap(function ($item) {
                return is_array($item->Lg_Id) ? $item->Lg_Id : [];
            })->unique()->values();

            $ledgersData = [];
            if ($allLedgerIds->isNotEmpty()) {
                $ledgersData = LedgerMaster::whereIn('Lg_Id', $allLedgerIds)
                    ->get([
                        'Lg_Id',
                        'Lg_Code',
                        'Lg_Name',
                        'Lg_Address',
                        'Lg_City',
                        'Lg_Pin',
                        'Lg_Phone',
                        'Lg_Email',
                        'Lg_Status',
                    ])
                    ->keyBy('Lg_Id')
                    ->map(function ($ledger) {
                        return [
                            'Lg_Id' => $ledger->Lg_Id,
                            'Lg_Code' => $ledger->Lg_Code ?? null,
                            'Lg_Name' => $ledger->Lg_Name ?? null,
                            'Lg_Address' => $ledger->Lg_Address ?? null,
                            'Lg_City' => $ledger->Lg_City ?? null,
                            'Lg_Pin' => $ledger->Lg_Pin ?? null,
                            'Lg_Phone' => $ledger->Lg_Phone ?? null,
                            'Lg_Email' => $ledger->Lg_Email ?? null,
                            'Lg_Status' => (bool) ($ledger->Lg_Status ?? false),
                        ];
                    })
                    ->toArray();
            }

            $subLedgers->getCollection()->transform(function ($item) use ($ledgersData) {
                $ledgerDetails = [];
                $ledgerNames = [];

                foreach ($item->Lg_Id ?? [] as $lid) {
                    if (isset($ledgersData[$lid])) {
                        $ledgerDetails[] = $ledgersData[$lid];
                        $ledgerNames[] = $ledgersData[$lid]['Lg_Name'] ?? 'Ledger #' . $lid;
                    } else {
                        $ledgerDetails[] = [
                            'Lg_Id' => $lid,
                            'Lg_Name' => 'Ledger Not Found',
                            'Lg_Code' => null,
                            'Lg_Status' => false
                        ];
                        $ledgerNames[] = 'Ledger Not Found';
                    }
                }

                return (object) [
                    'SL_Id' => $item->SL_Id,
                    'Lg_Id' => $item->Lg_Id ?? [],
                    'ledger_names' => $ledgerNames,
                    'ledgers' => $ledgerDetails,
                    'SL_Code' => $item->SL_Code,
                    'SL_Name' => $item->SL_Name,
                    'SL_Address' => $item->SL_Address,
                    'SL_City' => $item->SL_City,
                    'SL_Pin' => $item->SL_Pin,
                    'SL_Phone' => $item->SL_Phone,
                    'SL_Email' => $item->SL_Email,
                    'SL_Cont_Pers' => $item->SL_Cont_Pers,
                    'SL_Cr_Lim' => (float) $item->SL_Cr_Lim,
                    'SL_Draw_Pwr' => (float) $item->SL_Draw_Pwr,
                    'formatted_credit_limit' => $item->formatted_credit_limit,
                    'formatted_drawing_power' => $item->formatted_drawing_power,
                    'SL_Status' => (bool) $item->SL_Status,
                    'SL_Type' => $item->SL_Type, // ✅ ADDED
                    'sl_type_text' => $item->sl_type_text, // ✅ ADDED
                    'Is_cnl_cheq' => $item->Is_cnl_cheq,
                    'status_text' => $item->status_text,
                    'PAN_Number' => $item->PAN_Number,
                    'PAN_Verified' => (bool) $item->PAN_Verified,
                    'PAN_Verified_Date' => $item->PAN_Verified_Date?->toDateString(),
                    'GSTIN' => $item->GSTIN,
                    'GSTIN_Verified' => (bool) $item->GSTIN_Verified,
                    'GSTIN_Verified_Date' => $item->GSTIN_Verified_Date?->toDateString(),
                    'GST_Exempt_Declaration' => $item->GST_Exempt_Declaration,
                    'MSME_Udyam_Registration_No' => $item->MSME_Udyam_Registration_No,
                    'CIN_LLPIN' => $item->CIN_LLPIN,
                    'Shop_Act_Trade_License_No' => $item->Shop_Act_Trade_License_No,
                    'TAN' => $item->TAN,
                    'Is_Bank_Verified' => $item->Is_Bank_Verified,
                    'Bank_Name' => $item->Bank_Name,
                    'Branch_Name' => $item->Branch_Name,
                    'Account_Holder_Name' => $item->Account_Holder_Name,
                    'Account_Number' => $item->Account_Number,
                    'IFSC_Code' => $item->IFSC_Code,
                    'Account_Type' => $item->Account_Type,
                    'GST_Incd' => (int) $item->GST_Incd,
                    'Documents' => $item->Documents,
                    'created_at' => $item->created_at?->toDateTimeString(),
                    'updated_at' => $item->updated_at?->toDateTimeString(),
                ];
            });

            return response()->json([
                'success' => true,
                'data' => $subLedgers,
            ]);
        } catch (\Exception $e) {
            Log::error('SubLedger index error: ' . 'Something went wrong. Please try again later.', ['trace' => $e->getTraceAsString()]);
            return response()->json(['success' => false, 'message' => 'Server error'], 500);
        }
    }

    public function getAll(Request $request)
    {
        try {
            $query = SubLedgerMaster::query();

            if ($request->has('ledger_id')) {
                $query->whereJsonContains('Lg_Id', (int) $request->ledger_id);
            }

            if ($request->has('status')) {
                $query->where('SL_Status', $request->boolean('status'));
            }

            // ✅ NEW FILTER FOR SL_TYPE
            if ($request->has('sl_type')) {
                $query->where('SL_Type', $request->sl_type);
            }

            if ($request->filled('search')) {
                $query->search($request->search);
            }

            if ($request->has('Is_cnl_cheq')) {
                $value = strtoupper($request->Is_cnl_cheq);
                if (in_array($value, ['Y', 'N'])) {
                    $query->where('Is_cnl_cheq', $value);
                }
            }

            $sortBy = $request->get('sort_by', 'created_at');
            $sortOrder = $request->get('sort_order', 'desc');
            $query->orderBy($sortBy, $sortOrder);

            $subLedgers = $query->get();

            $allLedgerIds = $subLedgers->flatMap(function ($item) {
                return is_array($item->Lg_Id) ? $item->Lg_Id : [];
            })->unique()->values();

            $ledgersData = [];
            if ($allLedgerIds->isNotEmpty()) {
                $ledgersData = LedgerMaster::whereIn('Lg_Id', $allLedgerIds)
                    ->get([
                        'Lg_Id',
                        'Lg_Code',
                        'Lg_Name',
                        'Lg_Address',
                        'Lg_City',
                        'Lg_Pin',
                        'Lg_Phone',
                        'Lg_Email',
                        'Lg_Status',
                    ])
                    ->keyBy('Lg_Id')
                    ->map(function ($ledger) {
                        return [
                            'Lg_Id' => $ledger->Lg_Id,
                            'Lg_Code' => $ledger->Lg_Code ?? null,
                            'Lg_Name' => $ledger->Lg_Name ?? null,
                            'Lg_Address' => $ledger->Lg_Address ?? null,
                            'Lg_City' => $ledger->Lg_City ?? null,
                            'Lg_Pin' => $ledger->Lg_Pin ?? null,
                            'Lg_Phone' => $ledger->Lg_Phone ?? null,
                            'Lg_Email' => $ledger->Lg_Email ?? null,
                            'Lg_Status' => (bool) ($ledger->Lg_Status ?? false),
                        ];
                    })
                    ->toArray();
            }

            $formatted = $subLedgers->map(function ($item) use ($ledgersData) {
                $ledgerDetails = [];
                $ledgerNames = [];

                foreach ($item->Lg_Id ?? [] as $lid) {
                    if (isset($ledgersData[$lid])) {
                        $ledgerDetails[] = $ledgersData[$lid];
                        $ledgerNames[] = $ledgersData[$lid]['Lg_Name'] ?? 'Ledger #' . $lid;
                    } else {
                        $ledgerDetails[] = [
                            'Lg_Id' => $lid,
                            'Lg_Name' => 'Ledger Not Found',
                            'Lg_Code' => null,
                            'Lg_Status' => false
                        ];
                        $ledgerNames[] = 'Ledger Not Found';
                    }
                }

                return [
                    'SL_Id' => $item->SL_Id,
                    'Lg_Id' => $item->Lg_Id ?? [],
                    'ledger_names' => $ledgerNames,
                    'ledgers' => $ledgerDetails,
                    'SL_Code' => $item->SL_Code,
                    'SL_Name' => $item->SL_Name,
                    'SL_Address' => $item->SL_Address,
                    'SL_City' => $item->SL_City,
                    'SL_Pin' => $item->SL_Pin,
                    'SL_Phone' => $item->SL_Phone,
                    'SL_Email' => $item->SL_Email,
                    'SL_Cont_Pers' => $item->SL_Cont_Pers,
                    'SL_Cr_Lim' => (float) $item->SL_Cr_Lim,
                    'SL_Draw_Pwr' => (float) $item->SL_Draw_Pwr,
                    'formatted_credit_limit' => $item->formatted_credit_limit,
                    'formatted_drawing_power' => $item->formatted_drawing_power,
                    'SL_Status' => (bool) $item->SL_Status,
                    'SL_Type' => $item->SL_Type, // ✅ ADDED
                    'sl_type_text' => $item->sl_type_text, // ✅ ADDED
                    'Is_cnl_cheq' => $item->Is_cnl_cheq,
                    'status_text' => $item->status_text,
                    'PAN_Number' => $item->PAN_Number,
                    'PAN_Verified' => (bool) $item->PAN_Verified,
                    'PAN_Verified_Date' => $item->PAN_Verified_Date?->toDateString(),
                    'GSTIN' => $item->GSTIN,
                    'GSTIN_Verified' => (bool) $item->GSTIN_Verified,
                    'GSTIN_Verified_Date' => $item->GSTIN_Verified_Date?->toDateString(),
                    'GST_Exempt_Declaration' => $item->GST_Exempt_Declaration,
                    'MSME_Udyam_Registration_No' => $item->MSME_Udyam_Registration_No,
                    'CIN_LLPIN' => $item->CIN_LLPIN,
                    'Shop_Act_Trade_License_No' => $item->Shop_Act_Trade_License_No,
                    'TAN' => $item->TAN,
                    'Is_Bank_Verified' => $item->Is_Bank_Verified,
                    'Bank_Name' => $item->Bank_Name,
                    'Branch_Name' => $item->Branch_Name,
                    'Account_Holder_Name' => $item->Account_Holder_Name,
                    'Account_Number' => $item->Account_Number,
                    'IFSC_Code' => $item->IFSC_Code,
                    'Account_Type' => $item->Account_Type,
                    'GST_Incd' => (int) $item->GST_Incd,
                    'Documents' => $item->Documents,
                    'created_at' => $item->created_at?->toDateTimeString(),
                    'updated_at' => $item->updated_at?->toDateTimeString(),
                ];
            });

            return response()->json([
                'success' => true,
                'count' => $formatted->count(),
                'data' => $formatted,
            ]);
        } catch (\Exception $e) {
            Log::error('SubLedger getAll error: ' . 'Something went wrong. Please try again later.', [
                'trace' => $e->getTraceAsString()
            ]);
            return response()->json([
                'success' => false,
                'message' => 'Server error'
            ], 500);
        }
    }

    public function store(Request $request)
    {
        $validator = Validator::make(
            $request->all(),
            SubLedgerMaster::createRules(),
            SubLedgerMaster::validationMessages()
        );

        if ($validator->fails()) {
            return response()->json([
                'success' => false,
                'errors' => $validator->errors()
            ], 422);
        }

        $data = $request->all();

        unset($data['Documents']);

        $data['GST_Incd'] = $request->GST_Incd ?? 1;
        $data['Is_cnl_cheq'] = $request->Is_cnl_cheq ?? 'N';
        $data['Is_Bank_Verified'] = $request->Is_Bank_Verified ?? 'N';

        // ✅ Set default SL_Type if not provided
        if (!isset($data['SL_Type'])) {
            $data['SL_Type'] = '1'; // Default to Vend
        }

        $record = SubLedgerMaster::create($data);

        if ($request->hasFile('Documents')) {
            $record->storeDocuments(
                $request->file('Documents'),
                $request->Fnc_Id
            );
        }

        return response()->json([
            'success' => true,
            'data' => $record->fresh(),
            'message' => 'Sub-ledger created successfully'
        ], 201);
    }

    public function show($id)
    {
        $record = SubLedgerMaster::find($id);

        if (!$record) {
            return response()->json([
                'success' => false,
                'message' => 'Not found'
            ], 404);
        }

        $ledgerIds = $record->Lg_Id ?? [];
        $ledgers = \App\Models\LedgerMaster::whereIn('Lg_Id', $ledgerIds)->get();

        $gstText = match((int)$record->GST_Incd) {
            0 => 'No',
            1 => 'Yes',
            2 => 'Optional',
            default => 'Unknown'
        };

        $data = $record->toArray();
        $data['GST_Incd'] = (int) $record->GST_Incd;
        $data['Documents'] = $record->Documents;
        $data['Is_cnl_cheq'] = $record->Is_cnl_cheq;
        $data['Is_Bank_Verified'] = $record->Is_Bank_Verified;
        $data['GST_Incd_text'] = $gstText;
        $data['sl_type_text'] = $record->sl_type_text; // ✅ ADDED

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

public function update(Request $request, $id)
{
    $record = SubLedgerMaster::find($id);

    if (!$record) {
        return response()->json([
            'success' => false,
            'message' => 'Not found'
        ], 404);
    }

    $rules = SubLedgerMaster::updateRules($id);

    // Prevent SL_Code update
    unset($rules['SL_Code']);

    $validator = Validator::make(
        $request->all(),
        $rules,
        SubLedgerMaster::validationMessages()
    );

    if ($validator->fails()) {
        return response()->json([
            'success' => false,
            'errors' => $validator->errors()
        ], 422);
    }

    $data = $request->all();

    // Remove unwanted fields
    unset($data['Documents']);
    unset($data['SL_Code']);

    // GST Included
    if ($request->exists('GST_Incd')) {
        $data['GST_Incd'] = (int) $request->GST_Incd;
    }

    // Cancelled cheque flag
    if ($request->exists('Is_cnl_cheq')) {
        $data['Is_cnl_cheq'] = $request->input('Is_cnl_cheq', 'N');
    }

    // SL Type
    if ($request->exists('SL_Type')) {
        $data['SL_Type'] = $request->input('SL_Type');
    }

    $bankFields = [
        'Bank_Name',
        'Branch_Name',
        'Account_Holder_Name',
        'Account_Number',
        'IFSC_Code',
        'Account_Type'
    ];

    // Only check when already verified
    if ($record->Is_Bank_Verified === 'Y') {

        $bankDetailsChanged = false;

        foreach ($bankFields as $field) {

            // Check only fields sent in request
            if ($request->has($field)) {

                $oldValue = trim((string) $record->$field);
                $newValue = trim((string) $request->$field);

                // Compare old and new value
                if ($oldValue !== $newValue) {
                    $bankDetailsChanged = true;
                    break;
                }
            }
        }

        // If any bank detail changed then mark unverified
        if ($bankDetailsChanged) {
            $data['Is_Bank_Verified'] = 'N';
        }
    }

    // Fill and save
    $record->fill($data);
    $record->save();

    // Documents update
    if ($request->Documents) {
        $record->updateDocuments(
            $request->Documents,
            $request->Fnc_Id
        );
    }

    return response()->json([
        'success' => true,
        'data' => $record->fresh(),
        'message' => 'Updated successfully'
    ]);
}

    public function destroy($id)
    {
        $record = SubLedgerMaster::find($id);

        if (!$record) {
            return response()->json(['success' => false, 'message' => 'Not found'], 404);
        }

        $record->delete();

        return response()->json([
            'success' => true,
            'message' => 'Deleted successfully'
        ]);
    }

    public function deactivate($id)
    {
        try {
            $subLedger = SubLedgerMaster::find($id);

            if (!$subLedger) {
                return response()->json([
                    'success' => false,
                    'message' => 'Sub-ledger not found'
                ], 404);
            }

            $subLedger->deactivate();

            return response()->json([
                'success' => true,
                'message' => 'Sub-ledger deactivated successfully',
                'data' => $subLedger->fresh()
            ]);
        } catch (\Exception $e) {
            Log::error('Deactivate error: ' . 'Something went wrong. Please try again later.');
            return response()->json([
                'success' => false,
                'message' => 'Failed to deactivate',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }

    public function activate($id)
    {
        try {
            $subLedger = SubLedgerMaster::find($id);

            if (!$subLedger) {
                return response()->json([
                    'success' => false,
                    'message' => 'Sub-ledger not found'
                ], 404);
            }

            $ledgerIds = $subLedger->Lg_Id ?? [];
            $hasActiveLedger = false;

            if (!empty($ledgerIds)) {
                $hasActiveLedger = LedgerMaster::whereIn('Lg_Id', $ledgerIds)
                    ->where('Lg_Status', true)
                    ->exists();
            }

            if (!$hasActiveLedger && !empty($ledgerIds)) {
                return response()->json([
                    'success' => false,
                    'message' => 'Cannot activate: No active parent ledger found'
                ], 400);
            }

            $subLedger->activate();

            return response()->json([
                'success' => true,
                'message' => 'Sub-ledger activated successfully',
                'data' => $subLedger->fresh()
            ]);
        } catch (\Exception $e) {
            Log::error('Activate error: ' . 'Something went wrong. Please try again later.');
            return response()->json([
                'success' => false,
                'message' => 'Failed to activate',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }

    public function getByLedger($ledgerId)
    {
        try {
            $ledger = LedgerMaster::find($ledgerId);

            if (!$ledger) {
                return response()->json([
                    'success' => false,
                    'message' => 'Ledger not found'
                ], 404);
            }

            $subLedgers = SubLedgerMaster::whereJsonContains('Lg_Id', (int) $ledgerId)
                ->active()
                ->orderBy('SL_Code')
                ->get();

            return response()->json([
                'success' => true,
                'data' => [
                    'ledger' => [
                        'Lg_Id' => $ledger->Lg_Id,
                        'Lg_Code' => $ledger->Lg_Code ?? null,
                        'Lg_Name' => $ledger->Lg_Name ?? null,
                    ],
                    'sub_ledgers' => $subLedgers
                ],
                'message' => 'Sub-ledgers retrieved successfully'
            ]);
        } catch (\Exception $e) {
            Log::error('getByLedger error: ' . 'Something went wrong. Please try again later.');
            return response()->json([
                'success' => false,
                'message' => 'Failed to retrieve sub-ledgers',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }

    public function bulkStore(Request $request)
    {
        try {
            $validator = Validator::make($request->all(), [
                'sub_ledgers' => 'required|array|min:1',
                'sub_ledgers.*.Lg_Id' => 'required|array|min:1',
                'sub_ledgers.*.Lg_Id.*' => 'exists:ledger_master,Lg_Id',
                'sub_ledgers.*.SL_Code' => 'nullable|string|max:50',
                'sub_ledgers.*.SL_Name' => 'required|string|max:150',
                'sub_ledgers.*.SL_Address' => 'nullable|string',
                'sub_ledgers.*.SL_City' => 'nullable|string|max:50',
                'sub_ledgers.*.SL_Pin' => 'nullable|string|max:20',
                'sub_ledgers.*.SL_Phone' => 'nullable|string|max:15',
                'sub_ledgers.*.SL_Email' => 'nullable|email|max:50',
                'sub_ledgers.*.SL_Cont_Pers' => 'nullable|string|max:50',
                'sub_ledgers.*.SL_Cr_Lim' => 'nullable|numeric|min:0',
                'sub_ledgers.*.SL_Draw_Pwr' => 'nullable|numeric|min:0',
                'sub_ledgers.*.SL_Status' => 'nullable|boolean',
                'sub_ledgers.*.SL_Type' => 'nullable|in:1,2', // ✅ ADDED VALIDATION
            ], SubLedgerMaster::validationMessages());

            if ($validator->fails()) {
                return response()->json([
                    'success' => false,
                    'errors' => $validator->errors()
                ], 422);
            }

            $created = [];
            $errors = [];

            foreach ($request->input('sub_ledgers', []) as $index => $data) {
                try {
                    if (!empty($data['SL_Code']) && SubLedgerMaster::where('SL_Code', $data['SL_Code'])->exists()) {
                        $errors[] = ['index' => $index, 'message' => 'SL_Code already exists'];
                        continue;
                    }

                    $ledgerIds = $data['Lg_Id'] ?? [];
                    if (!empty($ledgerIds)) {
                        $activeCount = LedgerMaster::whereIn('Lg_Id', $ledgerIds)
                            ->where('Lg_Status', true)
                            ->count();

                        if ($activeCount === 0) {
                            $errors[] = ['index' => $index, 'message' => 'No active ledger found'];
                            continue;
                        }
                    }

                    // ✅ Set default SL_Type if not provided
                    if (!isset($data['SL_Type'])) {
                        $data['SL_Type'] = '1';
                    }

                    $subLedger = SubLedgerMaster::create($data);
                    $created[] = $subLedger;
                } catch (\Exception $e) {
                    $errors[] = [
                        'index' => $index,
                        'message' => 'Something went wrong. Please try again later.'
                    ];
                }
            }

            $statusCode = count($created) > 0 ? 201 : 422;

            return response()->json([
                'success' => count($created) > 0,
                'created_count' => count($created),
                'failed_count' => count($errors),
                'created' => $created,
                'errors' => $errors,
            ], $statusCode);
        } catch (\Exception $e) {
            Log::error('Bulk store error: ' . 'Something went wrong. Please try again later.');
            return response()->json([
                'success' => false,
                'message' => 'Bulk creation failed',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }

    public function getStatistics(Request $request)
    {
        try {
            $ledgerId = $request->query('ledger_id');

            $query = SubLedgerMaster::query();

            if ($ledgerId) {
                $query->whereJsonContains('Lg_Id', (int) $ledgerId);
            }

            $stats = $query->selectRaw('
                COUNT(*) as total_count,
                SUM(CASE WHEN SL_Status = 1 THEN 1 ELSE 0 END) as active_count,
                SUM(CASE WHEN SL_Status = 0 THEN 1 ELSE 0 END) as inactive_count,
                COALESCE(SUM(SL_Cr_Lim), 0) as total_credit_limit,
                COALESCE(SUM(SL_Draw_Pwr), 0) as total_drawing_power,
                COALESCE(AVG(SL_Cr_Lim), 0) as avg_credit_limit
            ')->first();

            return response()->json([
                'success' => true,
                'data' => $stats,
                'message' => 'Statistics retrieved successfully'
            ]);
        } catch (\Exception $e) {
            Log::error('Statistics error: ' . 'Something went wrong. Please try again later.');
            return response()->json([
                'success' => false,
                'message' => 'Failed to fetch statistics',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }


    //  public function getByGSTIN($gstin)
    // {
    //     $subLedger = SubLedgerMaster::where('GSTIN', $gstin)->first();

    //     // If GSTIN not found
    //     if (!$subLedger) {
    //         return response()->json([
    //             'status' => true,
    //             'message' => 'N/A',
    //             'data' => null
    //         ], 200);
    //     }

    //     // If GSTIN found
    //     return response()->json([
    //         'status' => true,
    //         'message' => 'Record Found',
    //         'data' => [
    //             'SL_Id' => $subLedger->SL_Id,
    //             'SL_Code' => $subLedger->SL_Code,
    //             'SL_Name' => $subLedger->SL_Name,
    //             'SL_Address' => $subLedger->SL_Address,
    //             'SL_City' => $subLedger->SL_City,
    //             'SL_Pin' => $subLedger->SL_Pin,
    //             'SL_Phone' => $subLedger->SL_Phone,
    //             'SL_Email' => $subLedger->SL_Email,
    //             'GSTIN' => $subLedger->GSTIN,
    //             'PAN_Number' => $subLedger->PAN_Number,
    //             'Bank_Name' => $subLedger->Bank_Name,
    //             'Account_Number' => $subLedger->Account_Number,
    //             'IFSC_Code' => $subLedger->IFSC_Code,
    //         ]
    //     ], 200);
    // }

public function getByGSTIN(Request $request)
{
    try {

        // Get GSTIN from request
        $gstin = trim(strtoupper($request->query('gstin')));

        // Check empty GSTIN
        if (empty($gstin)) {
            return response()->json([
                'success' => false,
                'message' => 'GSTIN is required',
                'data' => null
            ], 400);
        }

        // Find record
        $subLedger = SubLedgerMaster::withTrashed()
            ->whereRaw('UPPER(TRIM(GSTIN)) = ?', [$gstin])
            ->first();

        // If record not found
        if (!$subLedger) {
            return response()->json([
                'success' => true,
                'message' => 'GSTIN N/A',
                'data' => null
            ], 200);
        }

        // Return record
        return response()->json([
            'success' => true,
            'message' => 'Record Found',
            'data' => [
                'SL_Id' => $subLedger->SL_Id,
                'SL_Code' => $subLedger->SL_Code,
                'SL_Name' => $subLedger->SL_Name,
                'SL_Address' => $subLedger->SL_Address,
                'SL_City' => $subLedger->SL_City,
                'SL_Pin' => $subLedger->SL_Pin,
                'SL_Phone' => $subLedger->SL_Phone,
                'SL_Email' => $subLedger->SL_Email,
                'SL_Cont_Pers' => $subLedger->SL_Cont_Pers,
                'GSTIN' => $subLedger->GSTIN,
                'PAN_Number' => $subLedger->PAN_Number,
                'Bank_Name' => $subLedger->Bank_Name,
                'Branch_Name' => $subLedger->Branch_Name,
                'Account_Holder_Name' => $subLedger->Account_Holder_Name,
                'Account_Number' => $subLedger->Account_Number,
                'IFSC_Code' => $subLedger->IFSC_Code,
                'Account_Type' => $subLedger->Account_Type,
                'SL_Status' => $subLedger->SL_Status,
                'created_at' => $subLedger->created_at,
                'updated_at' => $subLedger->updated_at,
            ]
        ], 200);

    } catch (\Exception $e) {

        return response()->json([
            'success' => false,
            'message' => 'Server Error',
            'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
        ], 500);

    }
}


public function bulkUpdate(Request $request)
{
    $validator = Validator::make($request->all(), [
        'records' => 'required|array|min:1',
        'records.*.SL_Id' => 'required|exists:subledg_master,SL_Id',
    ]);

    if ($validator->fails()) {
        return response()->json([
            'success' => false,
            'errors' => $validator->errors()
        ], 422);
    }

    DB::beginTransaction();

    try {

        $updatedRecords = [];

        foreach ($request->records as $item) {

            $id = $item['SL_Id'];

            $record = SubLedgerMaster::find($id);

            if (!$record) {
                continue;
            }

            // Validation rules
            $rules = SubLedgerMaster::updateRules($id);

            // Prevent SL_Code update
            unset($rules['SL_Code']);

            $rowValidator = Validator::make(
                $item,
                $rules,
                SubLedgerMaster::validationMessages()
            );

            if ($rowValidator->fails()) {

                DB::rollBack();

                return response()->json([
                    'success' => false,
                    'message' => "Validation failed for SL_Id: {$id}",
                    'errors' => $rowValidator->errors()
                ], 422);
            }

            $data = $item;

            unset($data['Documents']);
            unset($data['SL_Code']);

            // GST Included
            if (array_key_exists('GST_Incd', $data)) {
                $data['GST_Incd'] = (int) $data['GST_Incd'];
            }

            // Cancelled cheque flag
            if (array_key_exists('Is_cnl_cheq', $data)) {
                $data['Is_cnl_cheq'] = $data['Is_cnl_cheq'] ?? 'N';
            }

            // SL Type
            if (array_key_exists('SL_Type', $data)) {
                $data['SL_Type'] = $data['SL_Type'];
            }

            // =====================================================
            // Auto reset bank verification if bank details changed
            // =====================================================

            $bankFields = [
                'Bank_Name',
                'Branch_Name',
                'Account_Holder_Name',
                'Account_Number',
                'IFSC_Code',
                'Account_Type'
            ];

            if ($record->Is_Bank_Verified === 'Y') {

                $bankDetailsChanged = false;

                foreach ($bankFields as $field) {

                    if (array_key_exists($field, $data)) {

                        $oldValue = trim((string) $record->$field);
                        $newValue = trim((string) $data[$field]);

                        if ($oldValue !== $newValue) {
                            $bankDetailsChanged = true;
                            break;
                        }
                    }
                }

                if ($bankDetailsChanged) {
                    $data['Is_Bank_Verified'] = 'N';
                }
            }

            // Update record
            $record->fill($data);
            $record->save();

            // Documents update
            if (!empty($item['Documents'])) {

                $record->updateDocuments(
                    $item['Documents'],
                    $item['Fnc_Id'] ?? null
                );
            }

            $updatedRecords[] = $record->fresh();
        }

        DB::commit();

        return response()->json([
            'success' => true,
            'message' => 'Bulk update completed successfully',
            'data' => $updatedRecords
        ]);

    } catch (\Exception $e) {

        DB::rollBack();

        return response()->json([
            'success' => false,
            'message' => 'Something went wrong. Please try again later.'
        ], 500);
    }
}


}
