<?php

namespace App\Console\Commands;

use App\Models\ApiKey\ApikeyModel;
use App\Models\JobQueue;
use App\Models\LinesModel;
use App\Models\ProvinceAndCity\CityModel;
use Carbon\Carbon;
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\Facades\Auth;
use Illuminate\Support\Facades\DB;

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

    public function handle()
    {
        LinesModel::truncate();
        JobQueue::where('job_type', 'SAVE_LINES')->where('job_status', '!=', 'COMPLETED')->delete();
        $city = CityModel::where('ip_address', '!=', '127.0.0.1')->get();
        foreach ($city as $city_key) {

            // Set up the database connection parameters dynamically
            $host = $city_key->ip_address;
            $port = '1433'; // Default SQL Server port
            $database = 'Ecity'; // Database name
            $username = $city_key->username;
            $password = $city_key->password;

            // Create a dynamic connection name
            $dynamicConnectionName = 'dynamic_sqlsrv_' . $city_key->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', 'Geo' ,DB::raw('ISNULL(Price, 0) as Price'))
                    ->get();

                // Process the collection to encode binary fields
                $processedData = $lines->map(function ($item) {
                    // Encode the 'Geo' field if it is not null
                    if (!is_null($item->Geo)) {
                        $item->Geo = base64_encode($item->Geo);
                    }
                    return $item;
                });

                // Convert the processed collection to JSON
                $jsonLocations = $processedData->toJson();
                // Convert lines to array format for insertion
                $formattedLines = $lines->map(function ($line) use ($city_key, $jsonLocations) {
                    return [
                        'Idnumber' => (int)$line->Idnumber,
                        'Code' => (int)$line->Code,
                        'Title' => $line->Title,
                        'Price' => (int)$line->Price,
                        'uc' => $city_key->uc,
                        'city_id' => $city_key->id,
                        'locations' => $jsonLocations
                    ];
                });

                // Insert formatted lines into the database
                LinesModel::insert($formattedLines->toArray());
            } catch (\Exception $e) {
                // Log the error and store information about the failed connection
                $cityIdJson = [
                    'city_id' => $city_key->id
                ];
                $jobFailModel = new JobQueue();
                $jobFailModel->job_type = 'SAVE_LINES';
                $jobFailModel->ip_address = $host;
                $jobFailModel->username = $username;
                $jobFailModel->password = $password;
                $jobFailModel->job_status = 'PENDING';
                $jobFailModel->json = $cityIdJson;
                $jobFailModel->save();
            }
        }

    }
}
