<?php

namespace App\Console\Commands;

use App\Models\JobQueue;
use App\Models\LinesModel;
use App\Models\ProvinceAndCity\CityModel;
use App\Models\TransactionModel;
use App\Models\User;
use App\Models\WalletModel;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;

class JobFails implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    public function handle()
    {
        $jobFail = JobQueue::where('job_status', 'PENDING')->first();
        if ($jobFail) {
            if ($jobFail->job_type == 'TRANSACTIONS') {
                $this->transactionsImportToSqlServer($jobFail);
            } elseif ($jobFail->job_type == 'SAVE_LINES') {
                $this->deployLinesToServer($jobFail);
            }
        }
    }

    public function transactionsImportToSqlServer($jobFail)
    {
        try {
            JobQueue::where('id', $jobFail->id)->update([
                'job_status' => 'IN_PROGRESS'
            ]);
            //     {"cardId":0,"dateTime":"2023-07-12T12:34:56","price":5000,"preCredit":10000,"afterCredit":15000,"uc":1,"lineId":1,"qrId":1234567890,"isIncrease":true,"wallet_id":62,"transaction_id":830360463480370,"tracking_id":1234567890,"user_id":18}
            // تنظیم پارامترهای کانکشن به صورت داینامیک
            $host = $jobFail->ip_address;
            $port = '1433'; // پورت پیش‌فرض SQL Server
            $database = 'Ecity'; // نام دیتابیس
            $username = $jobFail->username;
            $password = $jobFail->password;

            // ایجاد نام کانکشن داینامیک


            $dynamicConnectionName = 'jobFailConnectionTransaction_' . $jobFail->json['transaction_id'];

            // حذف کانکشن موجود با همین نام (اگر وجود داشته باشد)
            DB::purge($dynamicConnectionName);

            // تنظیم کانفیگ برای کانکشن داینامیک
            config([
                "database.connections.$dynamicConnectionName" => [
                    'driver' => 'sqlsrv',
                    'host' => $host,
                    'port' => $port,
                    'database' => $database,
                    'username' => $username,
                    'password' => $password,
                    'charset' => 'utf8',
                    'prefix' => '',
                    'prefix_indexes' => true,
                    'encrypt' => 'no',
                    'trust_server_certificate' => env('DB_TRUST_SERVER_CERTIFICATE', 'true')
                ],
            ]);

            $select_transaction = TransactionModel::where('transaction_id', $jobFail->json['transaction_id'])->first();
            $select_wallet = WalletModel::where('id', $jobFail->json['wallet_id'])->first();

            $select_user = User::where('id', $jobFail->json['user_id'])->first();
            $sql_srv = DB::connection($dynamicConnectionName)->table('ACardT')->select('*')->where('Mobile', $select_user->phone)->get();

            $card_id = $sql_srv[0]->Code;
            $sql_srv = DB::connection($dynamicConnectionName)->table('ACardT')->where('Code', $card_id)->update([
                'CardPrice' => $select_wallet->credit,
            ]);


            $FKDevice = DB::connection($dynamicConnectionName)->table('ADeviceT')->where('Idnumber', 1)->get();

            $device_id = $FKDevice[0]->Idnumber;
            $FKPersonal = DB::connection($dynamicConnectionName)->table('PPersonelT')->where('Idnumber', 1)->get();
            $personel_id = $FKPersonal[0]->Idnumber;

            $op_type = 1;
            if ($jobFail->json['isIncrease']) {
                $op = 2;
            }

            $InsertTransaction = DB::connection($dynamicConnectionName)->table('AIncDecEtebarT')->insert([
                'FKCard' => $card_id,
                'DateTime' => Carbon::now(),
                'Price' => $jobFail->json['price'],
                'PreEtebar' => $jobFail->json['preCredit'],
                'RemEtebar' => $jobFail->json['afterCredit'],
                'FKADevice' => $device_id,
                'OP' => $op_type,
                'FKPersonel' => $personel_id,
                'Pay' => 0,
                'isValidRecord' => 0,
                'UC' => $jobFail->json['uc'],
                'PayCode' => null,
                'Cash' => 0,
                'RegDateTime' => Carbon::now(),
                'FKLine' => null,
                'LastOp' => null,
                'LastDeviçe' => null,
                'crc' => 1,
                'LastDevice' => null,
            ]);
            if ($InsertTransaction) {
                // عمل درج موفقیت‌آمیز بود
                // می‌توانید اینجا یک پیام موفقیت یا عملیات دیگر را انجام دهید

                JobQueue::where('id', $jobFail->id)->update([
                    'job_status' => 'COMPLETED'
                ]);
            } else {
                // عمل درج با شکست مواجه شد
                // می‌توانید اینجا یک پیام خطا یا عملیات دیگر را انجام دهید
                JobQueue::where('id', $jobFail->id)->update([
                    'job_status' => 'FAILED'
                ]);
            }

        } catch (\Exception $e) {
            JobQueue::where('id', $jobFail->id)->update([
                'job_status' => 'FAILED'
            ]);
        }
    }

    public function deployLinesToServer($data)
    {
        $city_id = $data->json['city_id'];
        $city = CityModel::where('id', $city_id)->first();

        if ($city) {
            JobQueue::where('id', $data->id)->update([
                'job_status' => 'IN_PROGRESS'
            ]);
            // Set up the database connection parameters dynamically
            $host = $city->ip_address;
            $port = '1433'; // Default SQL Server port
            $database = 'Ecity'; // Database name
            $username = $city->username;
            $password = $city->password;

            // Create a dynamic connection name
            $dynamicConnectionName = 'dynamic_sqlsrv_' . $city->id;

            // Purge any existing connection with the same name
            DB::purge($dynamicConnectionName);

            // Configure the dynamic connection
            config([
                "database.connections.$dynamicConnectionName" => [
                    'driver' => 'sqlsrv',
                    'host' => $host,
                    'port' => $port,
                    'database' => $database,
                    'username' => $username,
                    'password' => $password,
                    'charset' => 'utf8',
                    'prefix' => '',
                    'prefix_indexes' => true,
                    'encrypt' => 'no',
                    'trust_server_certificate' => env('DB_TRUST_SERVER_CERTIFICATE', 'true'),
                ],
            ]);

            try {
                // Fetch lines associated with the user card
                $lines = DB::connection($dynamicConnectionName)
                    ->table('PLineT')
                    ->select('Idnumber', 'Code', 'Title', DB::raw('geo.ToString() as s'), DB::raw('ISNULL(Price, 0) as Price'))
                    ->get();

                // Convert lines to array format for insertion
                $formattedLines = $lines->map(function ($line) use ($city, $dynamicConnectionName) {
                    $latlang = DB::connection($dynamicConnectionName)
                        ->table('PLineT')
                        ->select(DB::raw('geo.ToString() as s'))
                        ->where('Code', )
                        ->first();
                    echo $latlang;
// Extract the coordinates from the LINESTRING format
                    $coordinates = str_replace(['LINESTRING (', ')'], '', $latlang->s);

// Split the coordinates into an array
                    $coordinatesArray = explode(', ', $coordinates);

// Reformat the coordinates into an array of objects
                    $formattedCoordinates = array_map(function ($coordinate) {
                        list($lat, $long) = explode(' ', $coordinate);
                        return [
                            "lat" => $lat,
                            "long" => $long
                        ];
                    }, $coordinatesArray);

                    return $formattedCoordinates;
                    return [
                        'Idnumber' => (int)$line->Idnumber,
                        'Code' => (int)$line->Code,
                        'Title' => $line->Title,
                        'Price' => (int)$line->Price,
                        'uc' => $city->uc,
                        'city_id' => $city->id,
                        'locations' => $formattedCoordinates
                    ];
                });

                // Insert formatted lines into the database
                LinesModel::insert($formattedLines->toArray());
                JobQueue::where('id', $data->id)->update([
                    'job_status' => 'COMPLETED'
                ]);
            } catch (\Exception $e) {
                echo $e->getMessage();
                JobQueue::where('id', $data->id)->update([
                    'job_status' => 'FAILED'
                ]);
            }
        }

    }
}
