<?php

namespace App\Models;

use Carbon\Carbon;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Session;

class SchemaComptable extends Model
{
    use HasFactory;

    protected $fillable = [
        'num_schema',
        'nom_schema',
        'libelle_ops',
        'compte_debit',
        'nom_compte_debit',
        'devise',
        'module_id',
        'compte_credit',
        'nom_compte_credit',
    ];

    public static function OperationTransB2W($compte_des, $montant, $code_user, $sender_cod_producto, $nom_des,$tel_client,$code_agence)
    {
        try {
            Log::info("OperationB2W start... ");
            $compte_client = $sender_cod_producto == "CC003" ? '2511100' : '2511101';
            // Generate a new codification number
            $last_code = DB::connection('sqlsrv2')->table('CG.CG_CODIFIC_RESUMEN')->orderBy('NUM_CODIFICACION', 'DESC')->value('NUM_CODIFICACION');
            if (is_null($last_code)) {
                throw new \Exception("Error fetching last codification number.");
            }
            $new_code = $last_code + 1;

            $sender_compte_compta=Serveur::where('code_agence',$code_agence)->latest()->first()->compte_transfert ?? '3920000';
            try {
                self::insertCodificResumen($new_code, $sender_cod_producto, "DEBIT POUR TRANSFERT B2W", '40',2, false);
            } catch (\Throwable $e) {
                //throw $th;
                \App\Helpers\LogHandler::logError($e); // Log the error
                Log::error("OperationB2W failed insertCodificResumen: " . $e->getMessage());
                return false; // Return false
            }
            try {
                self::insertCodificDetalle($new_code, [
                    [
                        'CUENTA_CONTABLE' => $compte_client,
                        'TIP_MOVIMIENTO' => 'D',
                        'MON_MOVIMIENTO' => $montant,
                        'DETALLE' => "Transfert de {$nom_des} et N° {$compte_des} compte caisse vers compte mobile orange money n° {$tel_client} B2W",
                        'isRecipient' => false
                    ],
                    [
                        'CUENTA_CONTABLE' => $sender_compte_compta,
                        'TIP_MOVIMIENTO' => 'C',
                        'MON_MOVIMIENTO' => $montant,
                        'DETALLE' => "Transfert de {$nom_des} et N° {$compte_des} compte caisse vers compte mobile orange money n° {$tel_client} B2W",
                        'isRecipient' => false
                    ]
                ]);
            } catch (\Throwable $th) {
                //throw $th;
                \App\Helpers\LogHandler::logError($th); // Log the error
                Log::error("OperationB2W failed insertCodificDetalle: " . $th->getMessage());
                return false; // Return false
            }


            // Process for balance solde
            self::updateCompteComptableBalance($compte_client, $montant, $type = 'debit', $isRecipient = false);
            self::updateCompteComptableBalance($sender_compte_compta, $montant, $type = 'credit', $isRecipient = false);



            // Process client movements for sender and recipient
            $desc_ref=$tel_client;
            self::processClientMovement($compte_des, $montant, $code_user, $nom_des, "DEBIT POUR TRANSFERT B2W",$desc_ref, false);
             //UPDATE SOLDE CLIENT SET
             self::updateClientBalance($compte_des, $montant,'debit', false);

            // Update SERIES_X_EMPRESA for both connections
            self::updateSeriesEmpresa('CC_MOV_DIA', false);

            return true;

        } catch (\Throwable $e) {
            \App\Helpers\LogHandler::logError($e); // Log the error
            Log::error("OperationB2W failed: " . $e->getMessage());
            return false; // Return false
        }
    }

    public static function OperationTransB2WCancel($compte_des, $montant, $code_user, $sender_cod_producto, $nom_des,$tel_client,$code_agence)
    {
        try {
            $compte_client = $sender_cod_producto == "CC003" ? '2511100' : '2511101';

            // Generate a new codification number
            $last_code = DB::connection('sqlsrv2')->table('CG.CG_CODIFIC_RESUMEN')->orderBy('NUM_CODIFICACION', 'DESC')->value('NUM_CODIFICACION');
            if (is_null($last_code)) {
                throw new \Exception("Error fetching last codification number.");
            }
            $new_code = $last_code + 1;

            $sender_compte_compta=Serveur::where('code_agence',$code_agence)->latest()->first()->compte_transfert ?? '3920000';
            try {
                self::insertCodificResumen($new_code, $sender_cod_producto, "ANNULATION POUR TRANSFERT W2B", '41',2, false);
            } catch (\Throwable $e) {
                //throw $th;
                \App\Helpers\LogHandler::logError($e); // Log the error
                Log::error("OperationW2B failed insertCodificResumen: " . $e->getMessage());
                return false; // Return false
            }

            self::insertCodificDetalle($new_code, [
                [
                    'CUENTA_CONTABLE' => $sender_compte_compta,
                    'TIP_MOVIMIENTO' => 'D',
                    'MON_MOVIMIENTO' => $montant,
                    'DETALLE' => "Transfert de {$nom_des} et compte Orange money n° {$tel_client} vers compte caisse N° {$compte_des} W2B",
                    'isRecipient' => false
                ],
                [
                    'CUENTA_CONTABLE' => $compte_client,
                    'TIP_MOVIMIENTO' => 'C',
                    'MON_MOVIMIENTO' => $montant,
                    'DETALLE' => "Transfert de {$nom_des} et compte Orange money n° {$tel_client} vers compte caisse N° {$compte_des} W2B",
                    'isRecipient' => false
                ]
            ]);
            // Process for balance solde
            self::updateCompteComptableBalance($sender_compte_compta, $montant, $type = 'debit', $isRecipient = false);
            self::updateCompteComptableBalance($compte_client, $montant, $type = 'credit', $isRecipient = false);



            // Process client movements for sender and recipient
            $desc_ref=$tel_client;
            self::processClientMovement($compte_des, $montant, $code_user, $nom_des, "ANNULATION POUR TRANSFERT W2B",$desc_ref, false);
             //UPDATE SOLDE CLIENT SET
             self::updateClientBalance($compte_des, $montant,'credit', false);

            // Update SERIES_X_EMPRESA for both connections
            self::updateSeriesEmpresa('CC_MOV_DIA', false);

            return true;

        } catch (\Throwable $e) {
            \App\Helpers\LogHandler::logError($e); // Log the error
            Log::error("OperationW2B failed: " . $e->getMessage());
            return false; // Return false
        }
    }

    public static function OperationTransW2BCancel($compte_des, $montant, $code_user, $sender_cod_producto, $nom_des,$tel_client,$code_agence)
    {
        try {
            Log::info("OperationB2W start... ");
            $compte_client = $sender_cod_producto == "CC003" ? '2511100' : '2511101';
            // Generate a new codification number
            $last_code = DB::connection('sqlsrv2')->table('CG.CG_CODIFIC_RESUMEN')->orderBy('NUM_CODIFICACION', 'DESC')->value('NUM_CODIFICACION');
            if (is_null($last_code)) {
                throw new \Exception("Error fetching last codification number.");
            }
            $new_code = $last_code + 1;

            $sender_compte_compta=Serveur::where('code_agence',$code_agence)->latest()->first()->compte_transfert ?? '3920000';
            try {
                self::insertCodificResumen($new_code, $sender_cod_producto, "ANNULATION POUR TRANSFERT B2W", '40',2, false);
            } catch (\Throwable $e) {
                //throw $th;
                \App\Helpers\LogHandler::logError($e); // Log the error
                Log::error("OperationB2W failed insertCodificResumen: " . $e->getMessage());
                return false; // Return false
            }
            try {
                self::insertCodificDetalle($new_code, [
                    [
                        'CUENTA_CONTABLE' => $compte_client,
                        'TIP_MOVIMIENTO' => 'D',
                        'MON_MOVIMIENTO' => $montant,
                        'DETALLE' => "Transfert de {$nom_des} et N° {$compte_des} compte caisse vers compte mobile orange money n° {$tel_client} B2W",
                        'isRecipient' => false
                    ],
                    [
                        'CUENTA_CONTABLE' => $sender_compte_compta,
                        'TIP_MOVIMIENTO' => 'C',
                        'MON_MOVIMIENTO' => $montant,
                        'DETALLE' => "Transfert de {$nom_des} et N° {$compte_des} compte caisse vers compte mobile orange money n° {$tel_client} B2W",
                        'isRecipient' => false
                    ]
                ]);
            } catch (\Throwable $th) {
                //throw $th;
                \App\Helpers\LogHandler::logError($th); // Log the error
                Log::error("OperationB2W failed insertCodificDetalle: " . $th->getMessage());
                return false; // Return false
            }


            // Process for balance solde
            self::updateCompteComptableBalance($compte_client, $montant, $type = 'debit', $isRecipient = false);
            self::updateCompteComptableBalance($sender_compte_compta, $montant, $type = 'credit', $isRecipient = false);



            // Process client movements for sender and recipient
            $desc_ref=$tel_client;
            self::processClientMovement($compte_des, $montant, $code_user, $nom_des, "ANNULATION POUR TRANSFERT B2W",$desc_ref, false);
             //UPDATE SOLDE CLIENT SET
             self::updateClientBalance($compte_des, $montant,'debit', false);

            // Update SERIES_X_EMPRESA for both connections
            self::updateSeriesEmpresa('CC_MOV_DIA', false);

            return true;

        } catch (\Throwable $e) {
            \App\Helpers\LogHandler::logError($e); // Log the error
            Log::error("OperationB2W failed: " . $e->getMessage());
            return false; // Return false
        }
    }



    public static function OperationTransW2B($compte_des, $montant, $code_user, $sender_cod_producto, $nom_des,$tel_client,$code_agence)
    {
        try {
            $compte_client = $sender_cod_producto == "CC003" ? '2511100' : '2511101';

            // Generate a new codification number
            $last_code = DB::connection('sqlsrv2')->table('CG.CG_CODIFIC_RESUMEN')->orderBy('NUM_CODIFICACION', 'DESC')->value('NUM_CODIFICACION');
            if (is_null($last_code)) {
                throw new \Exception("Error fetching last codification number.");
            }
            $new_code = $last_code + 1;

            $sender_compte_compta=Serveur::where('code_agence',$code_agence)->latest()->first()->compte_transfert ?? '3920000';
            try {
                self::insertCodificResumen($new_code, $sender_cod_producto, "CREDIT POUR TRANSFERT W2B", '41',2, false);
            } catch (\Throwable $e) {
                //throw $th;
                \App\Helpers\LogHandler::logError($e); // Log the error
                Log::error("OperationW2B failed insertCodificResumen: " . $e->getMessage());
                return false; // Return false
            }

            self::insertCodificDetalle($new_code, [
                [
                    'CUENTA_CONTABLE' => $sender_compte_compta,
                    'TIP_MOVIMIENTO' => 'D',
                    'MON_MOVIMIENTO' => $montant,
                    'DETALLE' => "Transfert de {$nom_des} et compte Orange money n° {$tel_client} vers compte caisse N° {$compte_des} W2B",
                    'isRecipient' => false
                ],
                [
                    'CUENTA_CONTABLE' => $compte_client,
                    'TIP_MOVIMIENTO' => 'C',
                    'MON_MOVIMIENTO' => $montant,
                    'DETALLE' => "Transfert de {$nom_des} et compte Orange money n° {$tel_client} vers compte caisse N° {$compte_des} W2B",
                    'isRecipient' => false
                ]
            ]);
            // Process for balance solde
            self::updateCompteComptableBalance($sender_compte_compta, $montant, $type = 'debit', $isRecipient = false);
            self::updateCompteComptableBalance($compte_client, $montant, $type = 'credit', $isRecipient = false);



            // Process client movements for sender and recipient
            $desc_ref=$tel_client;
            self::processClientMovement($compte_des, $montant, $code_user, $nom_des, "CREDIT POUR TRANSFERT W2B",$desc_ref, false);
             //UPDATE SOLDE CLIENT SET
             self::updateClientBalance($compte_des, $montant,'credit', false);

            // Update SERIES_X_EMPRESA for both connections
            self::updateSeriesEmpresa('CC_MOV_DIA', false);

            return true;

        } catch (\Throwable $e) {
            \App\Helpers\LogHandler::logError($e); // Log the error
            Log::error("OperationW2B failed: " . $e->getMessage());
            return false; // Return false
        }
    }


    private static function sendError($responseCode = "E11", $requestId = "OR2403283ADW1F", $CBAReferenceNo = "240328140701702")
{
    $instance = new self();
    $responseMessage = $instance->getReturnCodeDescription($responseCode);

    $errorXml = '<?xml version="1.0" encoding="UTF-8"?>';
    $errorXml .= '<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">';
    $errorXml .= '<soap:Body>';
    $errorXml .= '<ns2:TransferStatusInquiryResponse xmlns:ns2="http://b2w.banktowallet.com/b2w">';
    $errorXml .= '<return>';
    $errorXml .= '<mmHeaderInfo>';
    $errorXml .= '<operatorCode>ORANGEMONEYML</operatorCode>';
    $errorXml .= '<requestId>' . $requestId . '</requestId>';
    $errorXml .= '<affiliateCode>EML</affiliateCode>';
    $errorXml .= '<responseCode>' . $responseCode . '</responseCode>';
    $errorXml .= '<responseMessage>' . $responseMessage . '</responseMessage>';
    $errorXml .= '</mmHeaderInfo>';
    $errorXml .= '<CBAReferenceNo>' . $CBAReferenceNo . '</CBAReferenceNo>';
    $errorXml .= '</return>';
    $errorXml .= '</ns2:TransferStatusInquiryResponse>';
    $errorXml .= '</soap:Body>';
    $errorXml .= '</soap:Envelope>';

    return response($errorXml, 500)
        ->header('Content-Type', 'application/xml');
}
private function getReturnCodeDescription($code)
{
    $errorCodes = [
        '000' => 'Success',
        '010' => 'Bank Technical Error',
        'E01' => 'Bank Technical Error',
        'E02' => 'Bank Technical Error',
        'E03' => 'Bank Technical Error',
        'E04' => 'Bank Technical Error',
        'E05' => 'Bank Technical Error',
        'E06' => 'Bank Technical Error',
        'E07' => 'Bank Technical Error',
        'E08' => 'Bank Technical Error',
        'E09' => 'Bank Technical Error',
        'E10' => 'Bank Technical Error',
        'E11' => 'Operation in Timeout. Use TransferStatusInquiry to check status',
        'E12' => 'Bank Technical Error',
        'E13' => 'Unauthorized Request on Account (crossed thresholds)',
        'E14' => 'Unauthorized Request on Account (crossed thresholds)',
        'E15' => 'Unauthorized Request on Account (crossed thresholds)',
        'E16' => 'Insufficient Balance',
        'E17' => 'Bank Technical Error',
        'E18' => 'Bank Technical Error',
        'E19' => 'Bank Technical Error',
        'E20' => 'Bank Technical Error',
        'E21' => 'Bank Technical Error',
        'E22' => 'Transaction Violates Account\'s Minimum Balance',
        'E35' => 'Bank Technical Error',
        'E99' => 'Bank Technical Error',
    ];

    // Return the description for the given code, or a default message if the code is not found
    return isset($errorCodes[$code]) ? $errorCodes[$code] : 'Unknown Error';
}


    public static function OperationTransMAMM($compte_exp, $montant, $code_user, $compte_des, $sender_cod_producto, $nom_exp, $nom_des)
    {
        try {
            $compte_client = $sender_cod_producto == "CC003" ? '2511100' : '2511101';

            // Generate a new codification number
            $last_code = DB::connection('sqlsrv2')->table('CG.CG_CODIFIC_RESUMEN')->orderBy('NUM_CODIFICACION', 'DESC')->value('NUM_CODIFICACION');
            if (is_null($last_code)) {
                throw new \Exception("Error fetching last codification number.");
            }
            $new_code = $last_code + 1;

            // Insert transfer details for sender
            $sender_compte_compta=Serveur::where('code_agence',Auth::user()->code_agence)->latest()->first()->compte_transfert ?? '3920000';
            self::insertCodificResumen($new_code, $sender_cod_producto, "Transfert de client CAECE sur compte MAMM", '40',2, false);
            self::insertCodificDetalle($new_code, [
                [
                    'CUENTA_CONTABLE' => $compte_client,
                    'TIP_MOVIMIENTO' => 'D',
                    'MON_MOVIMIENTO' => $montant,
                    'DETALLE' => "Envoi transfert de : {$nom_exp} vers {$nom_des} NEKA CAECE MAMMT",
                    'isRecipient' => false
                ],
                [
                    'CUENTA_CONTABLE' => $sender_compte_compta,
                    'TIP_MOVIMIENTO' => 'C',
                    'MON_MOVIMIENTO' => $montant,
                    'DETALLE' => "Transfert de : {$nom_exp} vers {$nom_des} receveur NEKA CAECE MAMM",
                    'isRecipient' => false
                ]
            ]);
            // Process for balance solde
            self::updateCompteComptableBalance($compte_client, $montant, $type = 'debit', $isRecipient = false);
            self::updateCompteComptableBalance($sender_compte_compta, $montant, $type = 'credit', $isRecipient = true);


            // Insert transfer details for recipient
            $last_code2 = DB::connection('sqlsrv3')->table('CG.CG_CODIFIC_RESUMEN')->orderBy('NUM_CODIFICACION', 'DESC')->value('NUM_CODIFICACION');
            $new_code2 = $last_code2 + 1;
            $recip_compte_compta=Serveur::where('code_agence',Session::get('code_agence3'))->latest()->first()->compte_transfert ?? '3920000';
            self::insertCodificResumen($new_code2, $sender_cod_producto, "Transfert de client CAECE sur compte MAMM", '41',2, true);
            self::insertCodificDetalle($new_code2, [
                [
                    'CUENTA_CONTABLE' => $recip_compte_compta,
                    'TIP_MOVIMIENTO' => 'D',
                    'MON_MOVIMIENTO' => $montant,
                    'DETALLE' => "Envoi transfert de : {$nom_exp} vers {$nom_des} NEKA CAECE MAMMT",
                    'isRecipient' => true
                ],
                [
                    'CUENTA_CONTABLE' => $compte_client,
                    'TIP_MOVIMIENTO' => 'C',
                    'MON_MOVIMIENTO' => $montant,
                    'DETALLE' => "Transfert de : {$nom_exp} vers {$nom_des} receveur NEKA CAECE MAMM",
                    'isRecipient' => true
                ]
            ]);
            // Process for balance solde
            self::updateCompteComptableBalance($recip_compte_compta, $montant, $type = 'debit', $isRecipient = false);
            self::updateCompteComptableBalance($compte_client, $montant, $type = 'credit', $isRecipient = true);


            // Process client movements for sender and recipient
            self::processClientMovement($compte_exp, $montant, $code_user, $nom_exp, "DÉBIT POUR TRANSFERT NEKACAECE MAMM", false);
            self::processClientMovement($compte_des, $montant, $code_user, $nom_des, "CRÉDIT POUR TRANSFERT NEKACAECE MAMM", true);
             //UPDATE SOLDE CLIENT SET
             self::updateClientBalance($compte_exp, $montant, false);
             self::updateClientBalance($compte_des, $montant, true);

            // Update SERIES_X_EMPRESA for both connections
            self::updateSeriesEmpresa('CC_MOV_DIA', false);
            self::updateSeriesEmpresa('CC_MOV_DIA', true);

        } catch (\Throwable $e) {
            \App\Helpers\LogHandler::logError($e); // Log the error
            Log::error("OperationTransMAMM failed: " . $e->getMessage());
            return response()->json([
                'status' => 'error',
                'message' => 'An error occurred during transaction processing.'
            ], 500);
        }
    }
    public static function OperationTransDADMD($compte_exp, $montant, $code_user, $compte_des, $sender_cod_producto, $nom_exp, $nom_des)
    {
        try {
            $compte_client = $sender_cod_producto == "CC003" ? '2511100' : '2511101';


            //----------------------------------------------------------------1----------------------------------------------------------------
            // Generate a new codification number
            $last_code = DB::connection('sqlsrv2')->table('CG.CG_CODIFIC_RESUMEN')->orderBy('NUM_CODIFICACION', 'DESC')->value('NUM_CODIFICACION');
            if (is_null($last_code)) {
                throw new \Exception("Error fetching last codification number.");
            }
            $new_code = $last_code + 1;
            // Insert transfer details for sender
            $sender_compte_compta=Serveur::where('code_agence',Auth::user()->code_agence)->latest()->first()->compte_transfert ?? '3920000';
            self::insertCodificResumen($new_code, $sender_cod_producto, "Transfert de client CAECE sur compte DADMD", '40',2, false);
            self::insertCodificDetalle($new_code, [
                [
                    'CUENTA_CONTABLE' => $compte_client,
                    'TIP_MOVIMIENTO' => 'D',
                    'MON_MOVIMIENTO' => $montant,
                    'DETALLE' => "Envoi transfert de : {$nom_exp} vers {$nom_des} NEKA CAECE DADMD",
                    'isRecipient' => false
                ],
                [
                    'CUENTA_CONTABLE' => $sender_compte_compta,
                    'TIP_MOVIMIENTO' => 'C',
                    'MON_MOVIMIENTO' => $montant,
                    'DETALLE' => "Transfert de : {$nom_exp} vers {$nom_des} receveur NEKA CAECE DADMD",
                    'isRecipient' => false
                ]
            ]);
            // Process for balance solde
            self::updateCompteComptableBalance($compte_client, $montant, $type = 'debit', $isRecipient = false);
            self::updateCompteComptableBalance($sender_compte_compta, $montant, $type = 'credit', $isRecipient = true);
            //----------------------------------------------------------------2----------------------------------------------------------------
            // Generate a new codification number
            $last_code2 = DB::connection('sqlsrv2')->table('CG.CG_CODIFIC_RESUMEN')->orderBy('NUM_CODIFICACION', 'DESC')->value('NUM_CODIFICACION');
            if (is_null($last_code2)) {
                throw new \Exception("Error fetching last codification number.");
            }
            $new_code2 = $last_code2 + 1;
             // Insert transfer details Compte transfert and liaison compte caisse
             $sender_compte_compta2=Serveur::where('code_agence',Auth::user()->code_agence)->latest()->first();
             self::insertCodificResumen($new_code2, $sender_cod_producto, "Transfert de client CAECE sur compte DADMD", '40',2, false);
             self::insertCodificDetalle($new_code2, [
                 [
                     'CUENTA_CONTABLE' => $sender_compte_compta2->compte_transfert ?? '3920000',
                     'TIP_MOVIMIENTO' => 'D',
                     'MON_MOVIMIENTO' => $montant,
                     'DETALLE' => "Envoi transfert de : {$nom_exp} vers {$nom_des} NEKA CAECE DADMD",
                     'isRecipient' => false
                 ],
                 [
                     'CUENTA_CONTABLE' => $sender_compte_compta2->compte_liaison ?? '3911002',
                     'TIP_MOVIMIENTO' => 'C',
                     'MON_MOVIMIENTO' => $montant,
                     'DETALLE' => "Transfert de : {$nom_exp} vers {$nom_des} receveur NEKA CAECE DADMD",
                     'isRecipient' => false
                 ]
             ]);
             // Process for balance solde
             self::updateCompteComptableBalance($sender_compte_compta2->compte_transfert ?? '3920000', $montant, $type = 'debit', $isRecipient = false);
             self::updateCompteComptableBalance($sender_compte_compta2->compte_liaison ?? '3911002', $montant, $type = 'credit', $isRecipient = true);

             //----------------------------------------------------------------3----------------------------------------------------------------
             // Generate a new codification number
             //----------------------------------------------------------------SERVEUR A----------------------------------------------------------------
            $last_code3 = DB::connection('sqlsrv2')->table('CG.CG_CODIFIC_RESUMEN')->orderBy('NUM_CODIFICACION', 'DESC')->value('NUM_CODIFICACION');
            if (is_null($last_code3)) {
                throw new \Exception("Error fetching last codification number.");
            }
            $new_code3 = $last_code3 + 1;
              // Insert transfer details Compte transfert and liaison compte caisse
              $sender_compte_compta3=Serveur::where('code_agence',Auth::user()->code_agence)->latest()->first();
              self::insertCodificResumen($new_code3, $sender_cod_producto, "Transfert de client CAECE sur compte DADMD", '40',2, false);
              self::insertCodificDetalle($new_code3, [
                  [
                      'CUENTA_CONTABLE' => $sender_compte_compta3->compte_liaison ?? '3911002',
                      'TIP_MOVIMIENTO' => 'D',
                      'MON_MOVIMIENTO' => $montant,
                      'DETALLE' => "Envoi transfert de : {$nom_exp} vers {$nom_des} NEKA CAECE DADMD",
                      'isRecipient' => false
                  ]
              ]);
              // Process for balance solde
              self::updateCompteComptableBalance($sender_compte_compta3->compte_liaison ?? '3911002', $montant, $type = 'debit', $isRecipient = false);
              //----------------------------------------------------------------SERVEUR B----------------------------------------------------------------
              $last_code3c = DB::connection('sqlsrv3')->table('CG.CG_CODIFIC_RESUMEN')->orderBy('NUM_CODIFICACION', 'DESC')->value('NUM_CODIFICACION');
              if (is_null($last_code3c)) {
                  throw new \Exception("Error fetching last codification number.");
                }
                $new_code3c = $last_code3c + 1;
                $recip_compte_compta3=Serveur::where('code_agence',Session::get('code_agence3'))->latest()->first();
              self::insertCodificResumen($new_code3c, $sender_cod_producto, "Transfert de client CAECE sur compte DADMD", '40',2, true);
              self::insertCodificDetalle($new_code3c, [
                [
                    'CUENTA_CONTABLE' => $recip_compte_compta3->compte_liaison ?? '3911001',
                    'TIP_MOVIMIENTO' => 'C',
                    'MON_MOVIMIENTO' => $montant,
                    'DETALLE' => "Transfert de : {$nom_exp} vers {$nom_des} receveur NEKA CAECE DADMD",
                    'isRecipient' => true
                ]
            ]);
              // Process for balance solde
              self::updateCompteComptableBalance($recip_compte_compta3->compte_liaison ?? '3911001', $montant, $type = 'credit', $isRecipient = true);
            //----------------------------------------------------------------4------------------------------------------------------------------------------------------------
            // Generate a new codification number
            $last_code4 = DB::connection('sqlsrv3')->table('CG.CG_CODIFIC_RESUMEN')->orderBy('NUM_CODIFICACION', 'DESC')->value('NUM_CODIFICACION');
            if (is_null($last_code4)) {
                throw new \Exception("Error fetching last codification number.");
            }
            $new_code4 = $last_code4 + 1;
             // Insert transfer details Compte transfert and liaison compte caisse
             $recip_compte_compta4=Serveur::where('code_agence',Session::get('code_agence3'))->latest()->first();
             self::insertCodificResumen($new_code4, $sender_cod_producto, "Transfert de client CAECE sur compte DADMD", '40',2, true);
             self::insertCodificDetalle($new_code4, [
                 [
                     'CUENTA_CONTABLE' => $recip_compte_compta4->compte_liaison ?? '3911002',
                     'TIP_MOVIMIENTO' => 'D',
                     'MON_MOVIMIENTO' => $montant,
                     'DETALLE' => "Envoi transfert de : {$nom_exp} vers {$nom_des} NEKA CAECE DADMD",
                     'isRecipient' => true
                 ],
                 [
                     'CUENTA_CONTABLE' => $recip_compte_compta4->compte_transfert ?? '3920000',
                     'TIP_MOVIMIENTO' => 'C',
                     'MON_MOVIMIENTO' => $montant,
                     'DETALLE' => "Transfert de : {$nom_exp} vers {$nom_des} receveur NEKA CAECE DADMD",
                     'isRecipient' => true
                 ]
             ]);
             // Process for balance solde
             self::updateCompteComptableBalance($recip_compte_compta4->compte_liaison ?? '3920000', $montant, $type = 'debit', $isRecipient = false);
             self::updateCompteComptableBalance($recip_compte_compta4->compte_transfert ?? '3911002', $montant, $type = 'credit', $isRecipient = true);


             //----------------------------------------------------------------5------------------------------------------------------------------------------------
            // Insert transfer details for recipient
            $last_code5 = DB::connection('sqlsrv3')->table('CG.CG_CODIFIC_RESUMEN')->orderBy('NUM_CODIFICACION', 'DESC')->value('NUM_CODIFICACION');
            $new_code5 = $last_code5 + 1;
            $recip_compte_compta5=Serveur::where('code_agence',Session::get('code_agence3'))->latest()->first()->compte_transfert ?? '3920000';
            self::insertCodificResumen($new_code5, $sender_cod_producto, "Transfert de client CAECE sur compte DADMD", '41',2, true);
            self::insertCodificDetalle($new_code5, [
                [
                    'CUENTA_CONTABLE' => $recip_compte_compta5,
                    'TIP_MOVIMIENTO' => 'D',
                    'MON_MOVIMIENTO' => $montant,
                    'DETALLE' => "Envoi transfert de : {$nom_exp} vers {$nom_des} NEKA CAECE DADMD",
                    'isRecipient' => true
                ],
                [
                    'CUENTA_CONTABLE' => $compte_client,
                    'TIP_MOVIMIENTO' => 'C',
                    'MON_MOVIMIENTO' => $montant,
                    'DETALLE' => "Transfert de : {$nom_exp} vers {$nom_des} receveur NEKA CAECE DADMD",
                    'isRecipient' => true
                ]
            ]);
            // Process for balance solde
            self::updateCompteComptableBalance($recip_compte_compta5, $montant, $type = 'debit', $isRecipient = true);
            self::updateCompteComptableBalance($compte_client, $montant, $type = 'credit', $isRecipient = true);


            // Process client movements for sender and recipient
            self::processClientMovement($compte_exp, $montant, $code_user, $nom_exp, "DÉBIT POUR TRANSFERT NEKACAECE DADMD", false);
            self::processClientMovement($compte_des, $montant, $code_user, $nom_des, "CRÉDIT POUR TRANSFERT NEKACAECE DADMD", true);
             //UPDATE SOLDE CLIENT SET
             self::updateClientBalance($compte_exp, $montant, false);
             self::updateClientBalance($compte_des, $montant, true);

            // Update SERIES_X_EMPRESA for both connections
            self::updateSeriesEmpresa('CC_MOV_DIA', false);
            self::updateSeriesEmpresa('CC_MOV_DIA', true);

        } catch (\Throwable $e) {
            \App\Helpers\LogHandler::logError($e);
            Log::error("OperationTransDADMD failed: " . $e->getMessage());
            return response()->json([
                'status' => 'error',
                'message' => 'An error occurred during transaction processing.'
            ], 500);
        }
    }

    public static function OperationTransFrais($montant, $sender_cod_producto)
    {
        try {
            $compte_client = $sender_cod_producto == "CC003" ? '2511100' : '2511101';

            // Generate a new codification number
            $last_code = DB::connection('sqlsrv2')->table('CG.CG_CODIFIC_RESUMEN')->orderBy('NUM_CODIFICACION', 'DESC')->value('NUM_CODIFICACION');
            if (is_null($last_code)) {
                throw new \Exception("Error fetching last codification number.");
            }
            $new_code = $last_code + 1;

            // Insert transfer details for sender
            $sender_compte_compta=7029200;
            self::insertCodificResumen($new_code, $sender_cod_producto, "FRAIS TRANSFERT ARGENT NEKA CAECE", '32','22', false);
            self::insertCodificDetalle($new_code, [
                [
                    'CUENTA_CONTABLE' => $compte_client,
                    'TIP_MOVIMIENTO' => 'D',
                    'MON_MOVIMIENTO' => $montant,
                    'DETALLE' => "FRAIS TRANSFERT ARGENT NEKA CAECE",
                    'isRecipient' => false
                ],
                [
                    'CUENTA_CONTABLE' => $sender_compte_compta,
                    'TIP_MOVIMIENTO' => 'C',
                    'MON_MOVIMIENTO' => $montant,
                    'DETALLE' => "FRAIS TRANSFERT ARGENT NEKA CAECE",
                    'isRecipient' => false
                ]
            ]);
            // Process for balance solde
            self::updateCompteComptableBalance($compte_client, $montant, $type = 'debit', $isRecipient = false);
            self::updateCompteComptableBalance($sender_compte_compta, $montant, $type = 'credit', $isRecipient = true);


        } catch (\Throwable $e) {
            \App\Helpers\LogHandler::logError($e);
            Log::error("OperationTransMAMM failed: " . $e->getMessage());
            return response()->json([
                'status' => 'error',
                'message' => 'An error occurred during transaction processing.'
            ], 500);
        }
    }
    public static function OperationTransFrais2($montant, $sender_cod_producto,$compte_compta)
    {
        try {
            $compte_client = $sender_cod_producto == "CC003" ? '2511100' : '2511101';

            // Generate a new codification number
            $last_code = DB::connection('sqlsrv2')->table('CG.CG_CODIFIC_RESUMEN')->orderBy('NUM_CODIFICACION', 'DESC')->value('NUM_CODIFICACION');
            if (is_null($last_code)) {
                throw new \Exception("Error fetching last codification number.");
            }
            $new_code = $last_code + 1;

            // Insert transfer details for sender
            $sender_compte_compta=7029200;
            self::insertCodificResumen($new_code, $sender_cod_producto, "FRAIS TRANSFERT ARGENT NEKA CAECE", '32','22', false);
            self::insertCodificDetalle($new_code, [
                [
                    'CUENTA_CONTABLE' => $compte_client,
                    'TIP_MOVIMIENTO' => 'D',
                    'MON_MOVIMIENTO' => $montant,
                    'DETALLE' => "FRAIS TRANSFERT ARGENT NEKA CAECE",
                    'isRecipient' => false
                ],
                [
                    'CUENTA_CONTABLE' => $compte_compta,
                    'TIP_MOVIMIENTO' => 'C',
                    'MON_MOVIMIENTO' => $montant,
                    'DETALLE' => "FRAIS TRANSFERT ARGENT NEKA CAECE",
                    'isRecipient' => false
                ]
            ]);
            // Process for balance solde
            self::updateCompteComptableBalance($compte_client, $montant, $type = 'debit', $isRecipient = false);
            self::updateCompteComptableBalance($compte_compta, $montant, $type = 'credit', $isRecipient = true);


        } catch (\Throwable $e) {
            \App\Helpers\LogHandler::logError($e);
            Log::error("OperationTransMAMM failed: " . $e->getMessage());
            return response()->json([
                'status' => 'error',
                'message' => 'An error occurred during transaction processing.'
            ], 500);
        }
    }

    // Helper method for updating SERIES_X_EMPRESA
    private static function updateSeriesEmpresa($serie, $isRecipient = false)
    {
        $connection = $isRecipient ? DB::connection('sqlsrv3') : DB::connection('sqlsrv2');
        $last_mov_id= $connection->table('CF.CF_SERIES_X_EMPRESA')->where('COD_SERIE','CC_MOV_DIA')->first()->VAL_SIGUIENTE;
            $last_mov_id_mov=  $connection->table('CC.CC_MOVIMTO_DIARIO')->select('NUM_MOVIMIENTO')->orderBy('NUM_MOVIMIENTO', 'desc')->first()->NUM_MOVIMIENTO;
            if($last_mov_id_mov>=$last_mov_id){
                $last_mov_id=$last_mov_id_mov;
            }
            $new_value=$last_mov_id;

        $connection->table('CF.CF_SERIES_X_EMPRESA')->where('COD_SERIE', $serie)->update(['VAL_SIGUIENTE' => $new_value]);
    }

    // Helper method for inserting into CG_CODIFIC_RESUMEN
    public static function insertCodificResumen($codificationNum, $productCode, $description, $transactionType, $subtip_trans, $isRecipient = false)
    {
        $connection = $isRecipient ? DB::connection('sqlsrv3') : DB::connection('sqlsrv2');
        $connection->table('CG.CG_CODIFIC_RESUMEN')->insert([
            'COD_EMPRESA' => '00001',
            'NUM_CODIFICACION' => $codificationNum,
            'COD_SISTEMA' => 'CC',
            'COD_PRODUCTO' => $productCode,
            'TIP_TRANSACCION' => $transactionType,
            'SUBTIP_TRANSAC' => $subtip_trans,
            'DES_CODIFICACION' => $description,
            'TIP_CODIFICACION' => 'A',
            'FEC_REGISTRO' => date('d-m-Y H:i:s'),
            'EST_CODIFICACION' => 'A',
            'FRECUENCIA' => '12',
            'DIA_ASIENTO' => '1'
        ]);
    }

    // Helper method for inserting into CG_CODIFIC_DETALLE
    public static function insertCodificDetalle($codificationNum, $details)
    {
        foreach ($details as $index => $detail) {
            $connection = $detail['isRecipient'] ? DB::connection('sqlsrv3') : DB::connection('sqlsrv2');
            $connection->table('CG.CG_CODIFIC_DETALLE')->insert([
                'COD_EMPRESA' => '00001',
                'NUM_CODIFICACION' => $codificationNum,
                'NUM_LINEA' => $index + 1,
                'CUENTA_CONTABLE' => $detail['CUENTA_CONTABLE'],
                'TIP_MOVIMIENTO' => $detail['TIP_MOVIMIENTO'],
                'MON_MOVIMIENTO' => $detail['MON_MOVIMIENTO'],
                'DETALLE' => $detail['DETALLE']
            ]);
        }
    }

    // Additional helper method to handle client movement
    public static function processClientMovement($account, $amount, $user_code, $user_name, $description,$desc_ref, $isRecipient = false)
    {
        $connection = $isRecipient ? DB::connection('sqlsrv3') : DB::connection('sqlsrv2');

        $last_mov_id= $connection->table('CF.CF_SERIES_X_EMPRESA')->where('COD_SERIE','CC_MOV_DIA')->first()->VAL_SIGUIENTE;
            $last_mov_id_mov=  $connection->table('CC.CC_MOVIMTO_DIARIO')->select('NUM_MOVIMIENTO')->orderBy('NUM_MOVIMIENTO', 'desc')->first()->NUM_MOVIMIENTO;
            if($last_mov_id_mov>=$last_mov_id){
                $last_mov_id=$last_mov_id_mov;
            }
        $mov_id = $last_mov_id + 1;

        $connection->table('CC.CC_MOVIMTO_DIARIO')->insert([
            'COD_EMPRESA' => '00001',
            'NUM_MOVIMIENTO' => $mov_id,
            'NUM_CUENTA' => $account,
            'COD_PRODUCTO' => "CC" . substr($account, 3, 3),
            'TIP_TRANSACCION' => $isRecipient ? '41' : '40',
            'SUBTIP_TRANSAC' => '1',
            'COD_SISTEMA' => 'CC',
            'FEC_MOVIMIENTO' => date('d-m-Y H:i:s'),
            'NUM_DOCUMENTO' => '0',
            'EST_MOVIMIENTO' => 'C',
            'IND_APL_CARGO' => 'N',
            'MON_MOVIMIENTO' => $amount,
            'DES_MOVIMIENTO' => $description,
            'SISTEMA_FUENTE' => 'CC',
            'NUM_MOV_FUENTE' => '0',
            'COD_AGENCIA' => substr($account, 0, 3),
            'COD_USUARIO' => $user_code,
            'DES_REFERENCIA' => $desc_ref
        ]);
    }
    public static function processClientMovement2($account, $amount, $user_code, $user_name, $description,$sub_transfert,$type_transfert, $isRecipient = false)
    {
        $connection = $isRecipient ? DB::connection('sqlsrv3') : DB::connection('sqlsrv2');

        $last_mov_id= $connection->table('CF.CF_SERIES_X_EMPRESA')->where('COD_SERIE','CC_MOV_DIA')->first()->VAL_SIGUIENTE;
            $last_mov_id_mov=  $connection->table('CC.CC_MOVIMTO_DIARIO')->select('NUM_MOVIMIENTO')->orderBy('NUM_MOVIMIENTO', 'desc')->first()->NUM_MOVIMIENTO;
            if($last_mov_id_mov>=$last_mov_id){
                $last_mov_id=$last_mov_id_mov;
            }
        $mov_id = $last_mov_id + 1;

        $connection->table('CC.CC_MOVIMTO_DIARIO')->insert([
            'COD_EMPRESA' => '00001',
            'NUM_MOVIMIENTO' => $mov_id,
            'NUM_CUENTA' => $account,
            'COD_PRODUCTO' => "CC" . substr($account, 3, 3),
            'TIP_TRANSACCION' => $type_transfert,
            'SUBTIP_TRANSAC' => $sub_transfert,
            'COD_SISTEMA' => 'CC',
            'FEC_MOVIMIENTO' => date('d-m-Y H:i:s'),
            'NUM_DOCUMENTO' => '0',
            'EST_MOVIMIENTO' => 'C',
            'IND_APL_CARGO' => 'N',
            'MON_MOVIMIENTO' => $amount,
            'DES_MOVIMIENTO' => $description,
            'SISTEMA_FUENTE' => 'CC',
            'NUM_MOV_FUENTE' => '0',
            'COD_AGENCIA' => substr($account, 0, 3),
            'COD_USUARIO' => $user_code,
            'DES_REFERENCIA' => ""
        ]);
    }




    public static function updateClientBalance($accountNum, $transferAmount,$type, $isRecipient = false)
{
    try {
        // Determine the correct database connection based on isRecipient
        $connection = $isRecipient ? DB::connection('sqlsrv3') : DB::connection('sqlsrv2');

        // Retrieve the current balance of the account
        $account = $connection->table('CC.CC_CUENTA_EFECTIVO')
            ->where('NUM_CUENTA', $accountNum)
            ->first();

        if (!$account) {
            throw new \Exception("Account not found.");
        }

        // Calculate the new balance based on whether the account is the recipient or the sender

        $newBalance = $type === 'credit'
        ? $account->SAL_DISPONIBLE + $transferAmount
        : $account->SAL_DISPONIBLE - $transferAmount;

        $averageBalance = $account->SAL_DISPONIBLE;

        // Update the account's balance and average balance in CC_CUENTA_EFECTIVO
        $connection->table('CC.CC_CUENTA_EFECTIVO')
            ->where('NUM_CUENTA', $accountNum)
            ->update([
                'SAL_DISPONIBLE' => $newBalance,
                'SAL_PROMEDIO' => $averageBalance
            ]);

        // Update the account balance in T_COMPTE
        $connection->table('T_COMPTE')
            ->where('NUM_CUENTA', $accountNum)
            ->update([
                'MONTANT' => $newBalance
            ]);


    } catch (\Throwable $e) {
            \App\Helpers\LogHandler::logError($e);
        Log::error("updateClientBalance failed: " . $e->getMessage());
        return response()->json([
            'status' => 'error',
            'message' => 'An error occurred while updating client balance.'
        ], 500);
    }
}


public static function updateCompteComptableBalance($compteClient, $amount, $type = 'credit', $isRecipient = false)
{
    try {
        // Determine the database connection based on isRecipient
        $connection = $isRecipient ? DB::connection('sqlsrv3') : DB::connection('sqlsrv2');

        // Determine which field to update based on the type
        $fieldToUpdate = $type === 'credit' ? 'Credit' : 'Debit';

        // Retrieve the current balance for the specified compte_client
        $lastSolde = $connection->table('MVSOLDECOURSPERIODE')
            ->where('CUENTA_CONTABLE', $compteClient)
            ->value($fieldToUpdate);

        if (is_null($lastSolde)) {
            throw new \Exception("Compte comptable not found or balance is null.");
        }

        // Calculate the new balance based on the type
        $newBalance = $type === 'credit'
            ? $lastSolde + $amount
            : $lastSolde + $amount;

        // Update the appropriate balance field in MVSOLDECOURSPERIODE
        $connection->table('MVSOLDECOURSPERIODE')
            ->where('CUENTA_CONTABLE', $compteClient)
            ->update([$fieldToUpdate => $newBalance]);


    } catch (\Throwable $e) {
            \App\Helpers\LogHandler::logError($e);
        Log::error("updateCompteComptableBalance failed: " . $e->getMessage());
        return response()->json([
            'status' => 'error',
            'message' => 'An error occurred while updating compte comptable balance.'
        ], 500);
    }
}



}
