관리-도구

편집 파일: ClientController.php

<?php

namespace App\Http\Controllers;

use App\Models\Client;
use App\Models\PaymentMethod;
use App\Models\EcommerceClient;
use App\Models\Setting;
use App\Models\Account;
use App\utils\helpers;
use App\Models\Quotation;
use Carbon\Carbon;
use App\Imports\CustomerImport;
use Maatwebsite\Excel\Facades\Excel;
use Illuminate\Support\Facades\Validator;
use Illuminate\Validation\Rule;
use Illuminate\Support\Facades\Auth;
use App\Models\SaleReturn;
use App\Models\PaymentSaleReturns;
use App\Models\Sale;
use App\Models\PaymentSale;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Hash;
use Illuminate\Http\Request;

class ClientController extends BaseController
{

//------------- Get ALL Customers -------------\\

public function index(request $request)
    {
        $this->authorizeForUser($request->user('api'), 'view', Client::class);
        // How many items do you want to display.
        $perPage = $request->limit;
        $pageStart = \Request::get('page', 1);
        // Start displaying items from this number;
        $offSet = ($pageStart * $perPage) - $perPage;
        $order = $request->SortField;
        $dir = $request->SortType;
        $helpers = new helpers();
        // Filter fields With Params to retrieve
        $columns = array(0 => 'name', 1 => 'code', 2 => 'phone', 3 => 'email');
        $param = array(0 => 'like', 1 => 'like', 2 => 'like', 3 => 'like');
        $data = array();
        $clients = Client::where('deleted_at', '=', null);

//Multiple Filter
        $Filtred = $helpers->filter($clients, $columns, $param, $request)
        // Search With Multiple Param
            ->where(function ($query) use ($request) {
                return $query->when($request->filled('search'), function ($query) use ($request) {
                    return $query->where('name', 'LIKE', "%{$request->search}%")
                        ->orWhere('code', 'LIKE', "%{$request->search}%")
                        ->orWhere('phone', 'LIKE', "%{$request->search}%")
                        ->orWhere('email', 'LIKE', "%{$request->search}%");
                });
            });
        $totalRows = $Filtred->count();
        if($perPage == "-1"){
            $perPage = $totalRows;
        }
        $clients = $Filtred->offset($offSet)
            ->limit($perPage)
            ->orderBy($order, $dir)
            ->get();

foreach ($clients as $client) {

$client_exist = EcommerceClient::where('deleted_at', '=', null)
            ->where('client_id', $client->id)->exists();

if($client_exist){
                $item['client_ecommerce'] = 'yes';
            }else{
                $item['client_ecommerce'] = 'no';
            }

$item['total_amount'] = DB::table('sales')
                ->where('deleted_at', '=', null)
                ->where('statut', 'completed')
                ->where('client_id', $client->id)
                ->sum('GrandTotal');

$item['total_paid'] = DB::table('sales')
                ->where('deleted_at', '=', null)
                ->where('statut', 'completed')
                ->where('client_id', $client->id)
                ->sum('paid_amount');

$item['due'] = $item['total_amount'] - $item['total_paid'];

$item['total_amount_return'] = DB::table('sale_returns')
                ->where('deleted_at', '=', null)
                ->where('client_id', $client->id)
                ->sum('GrandTotal');

$item['total_paid_return'] = DB::table('sale_returns')
                ->where('sale_returns.deleted_at', '=', null)
                ->where('sale_returns.client_id', $client->id)
                ->sum('paid_amount');

$item['return_Due'] = $item['total_amount_return'] - $item['total_paid_return'];

$item['id'] = $client->id;
            $item['name'] = $client->name;
            $item['phone'] = $client->phone;
            $item['tax_number'] = $client->tax_number;
            $item['code'] = $client->code;
            $item['email'] = $client->email;
            $item['country'] = $client->country;
            $item['city'] = $client->city;
            $item['adresse'] = $client->adresse;
            $item['is_royalty_eligible'] = $client->is_royalty_eligible;
            $item['points'] = $client->points;
            $data[] = $item;
        }

$company_info = Setting::where('deleted_at', '=', null)->first();
        $accounts = Account::where('deleted_at', '=', null)->orderBy('id', 'desc')->get(['id','account_name']);

$clientsWithoutEcommerce = \App\Models\Client::where('deleted_at', '=', null)
        ->whereNotIn('id', function($query){
            $query->select('client_id')->from('ecommerce_clients');
        })->count();

$payment_methods = PaymentMethod::whereNull('deleted_at')->get(['id', 'name']);

return response()->json([
            'clients' => $data,
            'company_info' => $company_info,
            'totalRows' => $totalRows,
            'clients_without_ecommerce' => $clientsWithoutEcommerce,
            'accounts' => $accounts,
            'payment_methods' => $payment_methods,
        ]);
    }

//------------- Store new Customer -------------\\

public function store(Request $request)
    {
        $this->authorizeForUser($request->user('api'), 'create', Client::class);

$this->validate($request, [
            'name' => 'required',
            ]
        );

if($request['is_royalty_eligible'] == '1' || $request['is_royalty_eligible'] == 'true'){
            $is_royalty_eligible = 1;
        }else{
            $is_royalty_eligible = 0;
        }

$client = Client::create([
            'name' => $request['name'],
            'code' => $this->getNumberOrder(),
            'adresse' => $request['adresse'],
            'phone' => $request['phone'],
            'email' => $request['email'],
            'country' => $request['country'],
            'city' => $request['city'],
            'tax_number' => $request['tax_number'],
            'is_royalty_eligible' => $is_royalty_eligible,
        ]);

return response()->json($client);
    }

//------------ function show -----------\\

public function show($id){
        //
        
    }

//------------- Update Customer -------------\\

public function update(Request $request, $id)
    {
        $this->authorizeForUser($request->user('api'), 'update', Client::class);

// Validate input for both Client and EcommerceClient
        $validated = $request->validate([
            'name'      => ['required', 'string', 'max:255'],
            'adresse'   => ['nullable', 'string', 'max:255'],
            'phone'     => ['nullable', 'string', 'max:50'],
            'email'     => [
                'nullable', 'email', 'max:255',
                // ensure ecommerce_clients.email unique per client (ignore current by client_id)
                Rule::unique('ecommerce_clients', 'email')->ignore($id, 'client_id'),
            ],
            'country'   => ['nullable', 'string', 'max:100'],
            'city'      => ['nullable', 'string', 'max:100'],
            'tax_number'=> ['nullable', 'string', 'max:100'],

// EcommerceClient-specific (optional)
            'username'  => [
                'nullable', 'string', 'max:100',
                Rule::unique('ecommerce_clients', 'username')->ignore($id, 'client_id'),
            ],
            'status'    => ['nullable', Rule::in(['active','inactive'])],
            'password'  => ['nullable', 'string', 'min:6'],

// flags
            'is_royalty_eligible' => ['nullable'],
        ]);

// Normalize boolean flag from various inputs: '1', 'true', true, etc.
        $isRoyaltyEligible = filter_var($request->input('is_royalty_eligible'), FILTER_VALIDATE_BOOLEAN) ? 1 : 0;

DB::transaction(function () use ($request, $id, $isRoyaltyEligible) {
            // 1) Update Client
            Client::whereKey($id)->update([
                'name'                => $request->input('name'),
                'adresse'             => $request->input('adresse'),
                'phone'               => $request->input('phone'),
                'email'               => $request->input('email'),
                'country'             => $request->input('country'),
                'city'                => $request->input('city'),
                'tax_number'          => $request->input('tax_number'),
                'is_royalty_eligible' => $isRoyaltyEligible,
            ]);

// 2) Upsert EcommerceClient linked by client_id
            $payload = [
                'username' => $request->input('username', $request->input('name')), // default to name if username absent
                'email'    => $request->input('email'),
            ];

if ($request->filled('password')) {
                $payload['password'] = Hash::make($request->input('password'));
            }

EcommerceClient::updateOrCreate(
                ['client_id' => $id], // lookup by client_id
                $payload
            );
        });

return response()->json(['success' => true]);
    }

//------------- delete client -------------\\

public function destroy(Request $request, $id)
    {
        $this->authorizeForUser($request->user('api'), 'delete', Client::class);

DB::transaction(function () use ($id) {
            $now = Carbon::now();

// Soft delete Client
            Client::whereKey($id)->update(['deleted_at' => $now]);

// Soft delete linked EcommerceClient (and optionally inactivate)
            EcommerceClient::where('client_id', $id)
                ->update([
                    'deleted_at' => $now,
                    'status'     => 0,
                ]);
        });

return response()->json(['success' => true]);
    }

//-------------- Delete by selection  ---------------\\

public function delete_by_selection(Request $request)
    {
        $this->authorizeForUser($request->user('api'), 'delete', Client::class);

$data = $request->validate([
            'selectedIds'   => ['required','array','min:1'],
            'selectedIds.*' => ['integer'],
        ]);

$ids = $data['selectedIds'];
        $now = Carbon::now();

DB::transaction(function () use ($ids, $now) {
            // Soft delete all selected Clients
            Client::whereIn('id', $ids)->update(['deleted_at' => $now]);

// Soft delete all linked EcommerceClient rows
            EcommerceClient::whereIn('client_id', $ids)
                ->update([
                    'deleted_at' => $now,
                    'status'     => 0,
                ]);
        });

return response()->json(['success' => true]);
    }

//------------- get Number Order Customer -------------\\

public function getNumberOrder()
    {
        $last = DB::table('clients')->latest('id')->first();

if ($last) {
            $code = $last->code + 1;
        } else {
            $code = 1;
        }
        return $code;
    }

//------------- Get Clients Without Paginate -------------\\

public function Get_Clients_Without_Paginate()
    {
        $clients = Client::where('deleted_at', '=', null)->get(['id', 'name']);
        return response()->json($clients);
    }

//------------- Get Clients get_client_store_data Paginate -------------\\

public function get_client_store_data($id)
    {
        $client = EcommerceClient::where('client_id', $id)->first();

$data['id']        = $client->id;
        $data['client_id'] = $client->client_id;
        $item['name']      = $client->username;
        $data['email']     = $client->email;
        $data['NewPassword']     = NULL;

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

public function import(Request $request)
    {
        $this->authorizeForUser($request->user('api'), 'customers_import', Client::class);

// 1) File validation that returns errors[] as array of strings
        $v = Validator::make($request->all(), [
            'customers' => 'required|file|mimes:xls,xlsx|max:20480', // 20MB
        ]);
        if ($v->fails()) {
            return response()->json([
                'status' => false,
                'message' => 'Validation failed',
                'errors' => $v->errors()->all(),  // <-- array of strings
            ], 422);
        }

$rows = Excel::toArray([], $request->file('customers'));
        $sheet = $rows[0] ?? [];
        if (empty($sheet)) {
            return response()->json([
                'status' => false,
                'message' => 'The imported file is empty.',
                'errors' => ['No data found in the uploaded file.'],
            ], 422);
        }

// Normalize rows (with/without headers)
        $first = $sheet[0] ?? [];
        $assocInput = is_array($first) && count(array_filter(array_keys($first), 'is_string')) > 0;

$normalized = [];
        if ($assocInput) {
            foreach ($sheet as $r) $normalized[] = $this->normalizeAssocRow($r);
        } else {
            $header = array_map(function ($h) { return $this->normalizeKey((string) $h); }, $first);
            for ($i = 1; $i < count($sheet); $i++) {
                $row = $sheet[$i];
                $assoc = [];
                foreach ($header as $idx => $key) $assoc[$key] = $row[$idx] ?? null;
                $normalized[] = $this->normalizeAssocRow($assoc);
            }
        }

$errors = [];
        $prepared = [];
        $codesInFile = [];
        $lineBase = 2; // data start line if first row is header

foreach ($normalized as $i => $row) {
            $line = $i + $lineBase;
            $name       = isset($row['name'])       ? trim((string)$row['name']) : '';
            $codeRaw    = $row['code'] ?? null;
            $email      = isset($row['email'])      ? trim((string)$row['email']) : '';
            $phone      = isset($row['phone'])      ? trim((string)$row['phone']) : '';
            $country    = isset($row['country'])    ? trim((string)$row['country']) : '';
            $city       = isset($row['city'])       ? trim((string)$row['city']) : '';
            $adresse    = isset($row['adresse'])    ? trim((string)$row['adresse']) : '';
            $tax_number = isset($row['tax_number']) ? trim((string)$row['tax_number']) : '';

if ($name === '') $errors[] = "Row {$line}: name is required.";

$code = null;
            if ($codeRaw === null || $codeRaw === '') {
                $errors[] = "Row {$line}: code is required and must be an integer.";
            } else if (is_numeric($codeRaw) && intval($codeRaw) == $codeRaw) {
                $code = intval($codeRaw);
            } else {
                $errors[] = "Row {$line}: code '{$codeRaw}' is not a valid integer.";
            }

if ($email !== '' && !filter_var($email, FILTER_VALIDATE_EMAIL)) {
                $errors[] = "Row {$line}: email '{$email}' is not valid.";
            }

if ($code !== null) {
                if (isset($codesInFile[$code])) {
                    $errors[] = "Row {$line}: duplicate code '{$code}' found in the file (also on row {$codesInFile[$code]}).";
                } else {
                    $codesInFile[$code] = $line;
                }
            }

$prepared[] = [
                'name'       => $name,
                'code'       => $code,
                'email'      => $email ?: null,
                'phone'      => $phone ?: null,
                'country'    => $country ?: null,
                'city'       => $city ?: null,
                'adresse'    => $adresse ?: null,
                'tax_number' => $tax_number ?: null,
            ];
        }

// DB duplicates
        $codes = array_values(array_filter(array_map(function ($r) { return $r['code']; }, $prepared), function ($v) { return $v !== null; }));
        if (!empty($codes)) {
            $dupes = Client::whereNull('deleted_at')->whereIn('code', $codes)->pluck('code')->all();
            foreach ($dupes as $dup) $errors[] = "code '{$dup}' already exists in the system.";
        }

if (!empty($errors)) {
            return response()->json([
                'status' => false,
                'message' => 'Validation failed',
                'errors' => $errors,   // <-- array of strings (UI now uses ONLY this)
            ], 422);
        }

// Insert
        $now = now();
        $insertRows = [];
        foreach ($prepared as $r) {
            $insertRows[] = [
                'name'       => $r['name'],
                'code'       => $r['code'],
                'email'      => $r['email'],
                'phone'      => $r['phone'],
                'country'    => $r['country'],
                'city'       => $r['city'],
                'adresse'    => $r['adresse'],
                'tax_number' => $r['tax_number'],
                'created_at' => $now,
                'updated_at' => $now,
            ];
        }

DB::transaction(function () use ($insertRows) {
            foreach (array_chunk($insertRows, 1000) as $chunk) {
                Client::insert($chunk);
            }
        });

return response()->json([
            'status'   => true,
            'imported' => count($insertRows),
        ]);
    }

private function normalizeAssocRow(array $row): array
    {
        $out = [];
        foreach ($row as $k => $v) {
            $key = $this->normalizeKey((string)$k);
            $out[$this->resolveSynonym($key)] = $v;
        }
        return $out;
    }

private function normalizeKey(string $key): string
    {
        $key = trim(mb_strtolower($key));
        return preg_replace('/[.\\s\\-]+/u', '_', $key);
    }

private function resolveSynonym(string $key): string
    {
        $map = [
            'fullname' => 'name', 'full_name' => 'name', 'client' => 'name', 'customer' => 'name',
            'customer_code' => 'code', 'client_code' => 'code',
            'address' => 'adresse', 'addr' => 'adresse', 'tax' => 'tax_number', 'taxnumber' => 'tax_number',
            'tax_no' => 'tax_number', 'vat' => 'tax_number',
        ];
        return isset($map[$key]) ? $map[$key] : $key;
    }

//------------- clients_pay_due -------------\\

public function clients_pay_due(Request $request)
     {
         $this->authorizeForUser($request->user('api'), 'pay_due', Client::class);
        
         if($request['amount'] > 0){
            $client_sales_due = Sale::where('deleted_at', '=', null) 
            ->where('statut', 'completed')
            ->where([
                ['payment_statut', '!=', 'paid'],
                ['client_id', $request->client_id]
            ])->get();

$paid_amount_total = $request->amount;

foreach($client_sales_due as $key => $client_sale){
                if($paid_amount_total == 0)
                break;
                $due = $client_sale->GrandTotal  - $client_sale->paid_amount;

if($paid_amount_total >= $due){
                    $amount = $due;
                    $payment_status = 'paid';
                }else{
                    $amount = $paid_amount_total;
                    $payment_status = 'partial';
                }

$payment_sale = new PaymentSale();
                $payment_sale->sale_id = $client_sale->id;
                $payment_sale->account_id =  $request['account_id']?$request['account_id']:NULL;
                $payment_sale->Ref = app('App\Http\Controllers\PaymentSalesController')->getNumberOrder();
                $payment_sale->date = Carbon::now();
                $payment_sale->payment_method_id = $request['payment_method_id'];
                $payment_sale->montant = $amount;
                $payment_sale->change = 0;
                $payment_sale->notes = $request['notes'];
                $payment_sale->user_id = Auth::user()->id;
                $payment_sale->save();

$account = Account::where('id', $request['account_id'])->exists();

if ($account) {
                    // Account exists, perform the update
                    $account = Account::find($request['account_id']);
                    $account->update([
                        'balance' => $account->balance + $amount,
                    ]);
                }

$client_sale->paid_amount += $amount;
                $client_sale->payment_statut = $payment_status;
                $client_sale->save();

$paid_amount_total -= $amount;
            }
        }
        
         return response()->json(['success' => true]);
 
     }

//------------- clients_pay_sale_return_due -------------\\

public function pay_sale_return_due(Request $request)
    {
        $this->authorizeForUser($request->user('api'), 'pay_sale_return_due', Client::class);
        
        if($request['amount'] > 0){
            $client_sell_return_due = SaleReturn::where('deleted_at', '=', null)
            ->where([
                ['payment_statut', '!=', 'paid'],
                ['client_id', $request->client_id]
            ])->get();

$paid_amount_total = $request->amount;

foreach($client_sell_return_due as $key => $client_sale_return){
                if($paid_amount_total == 0)
                break;
                $due = $client_sale_return->GrandTotal  - $client_sale_return->paid_amount;

$payment_sale_return = new PaymentSaleReturns();
                $payment_sale_return->sale_return_id = $client_sale_return->id;
                $payment_sale_return->account_id =  $request['account_id']?$request['account_id']:NULL;
                $payment_sale_return->Ref = app('App\Http\Controllers\PaymentSaleReturnsController')->getNumberOrder();
                $payment_sale_return->date = Carbon::now();
                $payment_sale_return->payment_method_id = $request['payment_method_id'];
                $payment_sale_return->montant = $amount;
                $payment_sale_return->change = 0;
                $payment_sale_return->notes = $request['notes'];
                $payment_sale_return->user_id = Auth::user()->id;
                $payment_sale_return->save();

$account = Account::where('id', $request['account_id'])->exists();

if ($account) {
                    // Account exists, perform the update
                    $account = Account::find($request['account_id']);
                    $account->update([
                        'balance' => $account->balance - $amount,
                    ]);
                }

$client_sale_return->paid_amount += $amount;
                $client_sale_return->payment_statut = $payment_status;
                $client_sale_return->save();

$paid_amount_total -= $amount;
            }
        }
        
        return response()->json(['success' => true]);

}

public function getPoints(Request $request, $id)
    {
        $this->authorizeForUser($request->user('api'), 'view', Client::class);
        $client = Client::find($id);

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

return response()->json([
            'success' => true,
            'points' => $client->points,
            'is_royalty_eligible' => $client->is_royalty_eligible,
            'name' => $client->name,
        ]);
    }

public function updatePoints(Request $request, $id)
    {
        $this->authorizeForUser($request->user('api'), 'view', Client::class);

$request->validate([
            'points' => 'required',
        ]);

$client = Client::findOrFail($id);
        $client->points = $request->points;
        $client->save();

return response()->json(['success' => true]);
    }

public function salesByClient(Request $request)
    {
        $request->validate([
            'id'     => 'required|integer|exists:clients,id',
            'limit'  => 'sometimes|integer',
            'page'   => 'sometimes|integer',
            'search' => 'sometimes|string|nullable',
        ]);

$this->authorizeForUser($request->user('api'), 'view', Client::class);

// Pagination
        $perPage  = (int) ($request->input('limit', 10));
        $page     = max(1, (int) $request->input('page', 1));
        $offSet   = ($page - 1) * ($perPage > 0 ? $perPage : 0);

$q = Sale::query()
            ->whereNull('deleted_at')
            ->with(['client:id,name', 'warehouse:id,name'])
            ->where('client_id', $request->id)
          
            // Search (Ref, statut, payment_statut, warehouse.name, client.name)
            ->when($request->filled('search'), function ($query) use ($request) {
                $s = $request->input('search');
                $query->where(function ($qr) use ($s) {
                    $qr->where('Ref', 'LIKE', "%{$s}%")
                      ->orWhere('statut', 'LIKE', "%{$s}%")
                      ->orWhere('payment_statut', 'LIKE', "%{$s}%")
                      ->orWhereHas('warehouse', function ($wq) use ($s) {
                          $wq->where('name', 'LIKE', "%{$s}%");
                      })
                      ->orWhereHas('client', function ($cq) use ($s) {
                          $cq->where('name', 'LIKE', "%{$s}%");
                      });
                });
            });

$totalRows = (clone $q)->count();

if ($perPage === -1) {
            $perPage = $totalRows;
            $offSet  = 0;
        }

$rows = $q->orderByDesc('id')
            ->offset($offSet)
            ->limit($perPage)
            ->get();

$data = [];
        foreach ($rows as $sale) {
            $item = [
                'id'              => $sale->id,
                'date'            => $sale->date,
                'Ref'             => $sale->Ref,
                'warehouse_name'  => optional($sale->warehouse)->name,
                'client_name'     => optional($sale->client)->name,
                'statut'          => $sale->statut,
                'GrandTotal'      => $sale->GrandTotal,
                'paid_amount'     => $sale->paid_amount,
                'due'             => (float)$sale->GrandTotal - (float)$sale->paid_amount,
                'payment_status'  => $sale->payment_statut,
                'shipping_status' => $sale->shipping_status,
            ];
            $data[] = $item;
        }

return response()->json([
            'totalRows' => $totalRows,
            'sales'     => $data,
        ]);
    }

/**
     * GET /api/reports/payments_client
     * Params: id (client_id, required), limit, page, search
     * Returns: { totalRows, payments: [] }
     */
    public function paymentsByClient(Request $request)
    {
        $request->validate([
            'id'     => 'required|integer|exists:sales,client_id', // at least one sale exists for this client; adjust if needed
            'limit'  => 'sometimes|integer',
            'page'   => 'sometimes|integer',
            'search' => 'sometimes|string|nullable',
        ]);

$this->authorizeForUser($request->user('api'), 'view', Client::class);

$perPage  = (int) ($request->input('limit', 10));
        $page     = max(1, (int) $request->input('page', 1));
        $offSet   = ($page - 1) * ($perPage > 0 ? $perPage : 0);

$payments = DB::table('payment_sales')
            ->whereNull('payment_sales.deleted_at')
            ->join('sales', 'payment_sales.sale_id', '=', 'sales.id')
            ->join('payment_methods', 'payment_sales.payment_method_id', '=', 'payment_methods.id')
            ->where('sales.client_id', $request->id)
            ->when($request->filled('search'), function ($query) use ($request) {
                $s = $request->input('search');
                $query->where(function ($qr) use ($s) {
                    $qr->where('payment_sales.Ref', 'LIKE', "%{$s}%")
                       ->orWhere('payment_sales.date', 'LIKE', "%{$s}%")
                       ->orWhere('payment_methods.name', 'LIKE', "%{$s}%");
                });
            })
            ->select(
                'payment_sales.id',
                'payment_sales.date',
                'payment_sales.Ref as Ref',
                'sales.Ref as Sale_Ref',
                'payment_methods.name as payment_method',
                'payment_sales.montant'
            );

$totalRows = (clone $payments)->count();

if ($perPage === -1) {
            $perPage = $totalRows;
            $offSet  = 0;
        }

$rows = $payments
            ->orderByDesc('payment_sales.id')
            ->offset($offSet)
            ->limit($perPage)
            ->get();

return response()->json([
            'payments'  => $rows,
            'totalRows' => $totalRows,
        ]);
    }

/**
     * GET /api/clients/{id}/brief
     * Minimal client info for the ledger header.
     */
    public function clientBrief(Request $request, $id)
    {
        $this->authorizeForUser($request->user('api'), 'view', Client::class);

$client = Client::query()
            ->select('id', 'name', 'email', 'phone', 'code', 'adresse', 'country', 'city', 'tax_number')
            ->whereNull('deleted_at')
            ->findOrFail($id);

// -------- SALES TOTALS --------
        $total_amount = DB::table('sales')
            ->whereNull('deleted_at')
            ->where('statut', 'completed')
            ->where('client_id', $client->id)
            ->sum('GrandTotal');

$total_paid = DB::table('sales')
            ->whereNull('deleted_at')
            ->where('statut', 'completed')
            ->where('client_id', $client->id)
            ->sum('paid_amount');

$sale_due = $total_amount - $total_paid;

// -------- RETURNS TOTALS --------
        $total_amount_return = DB::table('sale_returns')
            ->whereNull('deleted_at')
            ->where('client_id', $client->id)
            ->sum('GrandTotal');

$total_paid_return = DB::table('sale_returns')
            ->whereNull('deleted_at')
            ->where('client_id', $client->id)
            ->sum('paid_amount');

$return_due = $total_amount_return - $total_paid_return;

// -------- PAYMENTS TOTALS --------

$payments_total = DB::table('payment_sales')
        ->join('sales', 'payment_sales.sale_id', '=', 'sales.id')
        ->whereNull('payment_sales.deleted_at')
        ->whereNull('sales.deleted_at')
        ->where('sales.client_id', $client->id)
        ->sum('payment_sales.montant');

// -------- ATTACH STATS TO CLIENT --------
        $client->salesGrand     = $total_amount;
        $client->salesPaid      = $total_paid;
        $client->sale_due       = $sale_due;
        $client->return_due     = $return_due;
        $client->paymentsTotal  = $payments_total;

return response()->json($client);
    }

public function export(Request $request)
    {
        $request->validate([
            'id' => 'required|integer|exists:clients,id',
        ]);

$this->authorizeForUser($request->user('api'), 'view', Client::class);

$client = Client::select('id','name','email','phone','code','adresse','country','city','tax_number')
            ->whereNull('deleted_at')
            ->findOrFail($request->id);

// ---------------- GLOBAL TOTALS ----------------
        $total_amount = DB::table('sales')
            ->whereNull('deleted_at')
            ->where('statut', 'completed')
            ->where('client_id', $client->id)
            ->sum('GrandTotal');

$total_paid = DB::table('sales')
            ->whereNull('deleted_at')
            ->where('statut', 'completed')
            ->where('client_id', $client->id)
            ->sum('paid_amount');

$sale_due = $total_amount - $total_paid;

$total_amount_return = DB::table('sale_returns')
            ->whereNull('deleted_at')
            ->where('client_id', $client->id)
            ->sum('GrandTotal');

$total_paid_return = DB::table('sale_returns')
            ->whereNull('deleted_at')
            ->where('client_id', $client->id)
            ->sum('paid_amount');

$return_due = $total_amount_return - $total_paid_return;

$quotations_total = DB::table('quotations')
            ->whereNull('deleted_at')
            ->where('client_id', $client->id)
            ->sum('GrandTotal');

// Attach stats to client
        $client->salesGrand     = $total_amount;
        $client->salesPaid      = $total_paid;
        $client->sale_due       = $sale_due;
        $client->return_due     = $return_due;
        $client->paymentsTotal  = $payments_total;
        $client->quotationsTotal = $quotations_total;
        $client->netBalance     = $sale_due - $return_due;

// ---------------- FULL DATA (NO FILTERS) ----------------
        $sales = Sale::with('warehouse:id,name')
            ->whereNull('deleted_at')
            ->where('client_id', $client->id)
            ->orderByDesc('id')->get();

$payments = DB::table('payment_sales')
            ->join('sales','payment_sales.sale_id','=','sales.id')
            ->join('payment_methods','payment_sales.payment_method_id','=','payment_methods.id')
            ->whereNull('payment_sales.deleted_at')
            ->where('sales.client_id',$client->id)
            ->select(
                'payment_sales.date',
                'payment_sales.Ref',
                'sales.Ref as Sale_Ref',
                'payment_methods.name as payment_method',
                'payment_sales.montant'
            )
            ->orderByDesc('payment_sales.id')->get();

$quotations = Quotation::with('warehouse:id,name')
            ->whereNull('deleted_at')
            ->where('client_id', $client->id)
            ->orderByDesc('id')->get();

$returns = SaleReturn::with(['warehouse:id,name','sale:id,Ref'])
            ->whereNull('deleted_at')
            ->where('client_id', $client->id)
            ->orderByDesc('id')->get();

// ---------------- PDF ----------------
        $pdf = PDF::loadView('pdf.customer_ledger', compact(
            'client','sales','payments','quotations','returns'
        ))->setPaper('a4', 'portrait');

return $pdf->download("customer_ledger_{$client->id}.pdf");
    }

// ---------- Quotations ----------
    public function quotationsByClient(Request $request)
    {
        $request->validate([
            'id'     => 'required|integer|exists:clients,id',
            'limit'  => 'sometimes|integer',
            'page'   => 'sometimes|integer',
            'search' => 'sometimes|string|nullable',
        ]);

$this->authorizeForUser($request->user('api'), 'view', Client::class);

$perPage = (int) ($request->input('limit', 10));
        $page    = max(1, (int) $request->input('page', 1));
        $offSet  = ($page - 1) * ($perPage > 0 ? $perPage : 0);

$q = Quotation::query()
            ->with('client:id,name','warehouse:id,name')
            ->whereNull('deleted_at')
            ->where('client_id', $request->id)
            ->when($request->filled('search'), function ($query) use ($request) {
                $s = $request->input('search');
                $query->where(function ($qr) use ($s) {
                    $qr->where('Ref', 'LIKE', "%{$s}%")
                       ->orWhere('statut', 'LIKE', "%{$s}%")
                       ->orWhereHas('warehouse', fn($wq) => $wq->where('name', 'LIKE', "%{$s}%"))
                       ->orWhereHas('client', fn($cq) => $cq->where('name', 'LIKE', "%{$s}%"));
                });
            });

$totalRows = (clone $q)->count();

if ($perPage === -1) {
            $perPage = $totalRows;
            $offSet  = 0;
        }

$rows = $q->orderByDesc('id')
            ->offset($offSet)
            ->limit($perPage)
            ->get();

$data = [];
        foreach ($rows as $Quotation) {
            $data[] = [
                'id'             => $Quotation->id,
                'date'           => $Quotation->date,
                'Ref'            => $Quotation->Ref,
                'statut'         => $Quotation->statut,
                'warehouse_name' => optional($Quotation->warehouse)->name,
                'client_name'    => optional($Quotation->client)->name,
                'GrandTotal'     => $Quotation->GrandTotal,
            ];
        }

return response()->json([
            'quotations' => $data,
            'totalRows'  => $totalRows,
        ]);
    }

// ---------- Returns ----------
    public function returnsByClient(Request $request)
    {
        $request->validate([
            'id'     => 'required|integer|exists:clients,id',
            'limit'  => 'sometimes|integer',
            'page'   => 'sometimes|integer',
            'search' => 'sometimes|string|nullable',
        ]);

$this->authorizeForUser($request->user('api'), 'view', Client::class);

$perPage = (int) ($request->input('limit', 10));
        $page    = max(1, (int) $request->input('page', 1));
        $offSet  = ($page - 1) * ($perPage > 0 ? $perPage : 0);

$q = SaleReturn::query()
            ->with('sale:id,Ref','client:id,name','warehouse:id,name')
            ->whereNull('deleted_at')
            ->where('client_id', $request->id)
            ->when($request->filled('search'), function ($query) use ($request) {
                $s = $request->input('search');
                $query->where(function ($qr) use ($s) {
                    $qr->where('Ref', 'LIKE', "%{$s}%")
                       ->orWhere('statut', 'LIKE', "%{$s}%")
                       ->orWhere('payment_statut', 'LIKE', "%{$s}%")
                       ->orWhereHas('client', fn($cq) => $cq->where('name', 'LIKE', "%{$s}%"))
                       ->orWhereHas('sale', fn($sq) => $sq->where('Ref', 'LIKE', "%{$s}%"))
                       ->orWhereHas('warehouse', fn($wq) => $wq->where('name', 'LIKE', "%{$s}%"));
                });
            });

$totalRows = (clone $q)->count();

if ($perPage === -1) {
            $perPage = $totalRows;
            $offSet  = 0;
        }

$rows = $q->orderByDesc('id')
            ->offset($offSet)
            ->limit($perPage)
            ->get();

$data = [];
        foreach ($rows as $r) {
            $data[] = [
                'id'             => $r->id,
                'Ref'            => $r->Ref,
                'statut'         => $r->statut,
                'client_name'    => optional($r->client)->name,
                'sale_ref'       => $r->sale ? $r->sale->Ref : '---',
                'sale_id'        => $r->sale ? $r->sale->id : null,
                'warehouse_name' => optional($r->warehouse)->name,
                'GrandTotal'     => $r->GrandTotal,
                'paid_amount'    => $r->paid_amount,
                'due'            => (float)$r->GrandTotal - (float)$r->paid_amount,
                'payment_status' => $r->payment_statut,
            ];
        }

return response()->json([
            'totalRows'        => $totalRows,
            'returns_customer' => $data,
        ]);
    }

}