<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Twilio\Rest\Client;

class AddsidColumnCron extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'sid-column:cron';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Add or remove sid column in did table for all client databases based on hardcoded logic';

    /**
     * The log file path.
     *
     * @var string
     */
    protected $logFilePath;

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
        $this->logFilePath = storage_path('app/sid-column-cron.txt');
    }

    /**
     * Log a message to the file with timestamp.
     *
     * @param string $message
     * @return void
     */
    private function logMessage(string $message)
    {
        $timestamp = date('Y-m-d H:i:s');
        file_put_contents($this->logFilePath, "[{$timestamp}] {$message}\n", FILE_APPEND);
    }

    /**
     * Start a new log section with date line.
     *
     * @return void
     */
    private function startLog()
    {
        $dateLine = "------ " . date('Y-m-d H:i:s') . " ------";
        file_put_contents($this->logFilePath, "\n{$dateLine}\n", FILE_APPEND);
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        // Start log section
        $this->startLog();
        $this->logMessage('Command started.');

        // Manually set this to true to add the column and update sids, false to drop the column
        $create = true; // Change to false to drop the column

        try {
            $this->info('Fetching all clients...');
            $this->logMessage('Fetching all clients...');
            $clients = \App\Model\Master\Client::all();
            $this->info('Clients fetched successfully.');
            $this->logMessage('Clients fetched successfully.');

            // Fetch Twilio data once if creating
            $twilioResults = [];
            if ($create) {
                $this->info('Fetching Twilio incoming phone numbers...');
                $this->logMessage('Fetching Twilio incoming phone numbers...');
                $sid = env('TWILIO_SID', 'AC1847558d695c1eaa63c1f8e5dc94bd22');
                $token = env('TWILIO_TOKEN', '61d025d7fc64af83f7395a76c4937d4a');

                if (empty($sid) || empty($token)) {
                    $this->error('Twilio credentials not set');
                    $this->logMessage('Twilio credentials not set');
                    return;
                }

                $twilio = new Client($sid, $token);
                $phoneNumbers = $twilio->incomingPhoneNumbers->read();

                foreach ($phoneNumbers as $record) {
                    $twilioResults[$record->phoneNumber] = $record->sid;
                }
                $this->info('Twilio data fetched successfully. Found ' . count($twilioResults) . ' entries.');
                $this->logMessage('Twilio data fetched successfully. Found ' . count($twilioResults) . ' entries.');
            }

            foreach ($clients as $client) {
                $clientId = $client->id;
                $this->info("Processing client {$clientId}...");
                $this->logMessage("Processing client {$clientId}...");

                $connection = "mysql_{$clientId}";

                try {
                    $this->info("Checking if 'sid' column exists in did table for client {$clientId}...");
                    $this->logMessage("Checking if 'sid' column exists in did table for client {$clientId}...");
                    $columns = DB::connection($connection)
                        ->select("SHOW COLUMNS FROM did LIKE 'sid'");

                    $columnExists = !empty($columns);
                    $this->info("'sid' column exists: " . ($columnExists ? 'Yes' : 'No'));
                    $this->logMessage("'sid' column exists: " . ($columnExists ? 'Yes' : 'No'));

                    if ($create) {
                        if (!$columnExists) {
                            $this->info("Adding 'sid' column to did table for client {$clientId}...");
                            $this->logMessage("Adding 'sid' column to did table for client {$clientId}...");
                            DB::connection($connection)
                                ->statement("ALTER TABLE did ADD COLUMN sid VARCHAR(100) NULL");
                            $this->info("Added 'sid' column successfully.");
                            $this->logMessage("Added 'sid' column successfully.");
                        } else {
                            $this->info("'sid' column already exists. No addition needed.");
                            $this->logMessage("'sid' column already exists. No addition needed.");
                        }

                        // Now update sid values based on cli
                        $this->info("Updating sid values for client {$clientId}...");
                        $this->logMessage("Updating sid values for client {$clientId}...");
                        $dids = DB::connection($connection)->table('did')->get();

                        $updatedCount = 0;
                        foreach ($dids as $did) {
                            $cli = $did->cli;
                            $normalizedCli = ltrim($cli, '+'); // Remove + if present
                            $matchedKey = null;

                            // Find matching key in twilioResults (with +)
                            foreach ($twilioResults as $key => $value) {
                                $normalizedKey = ltrim($key, '+');
                                if ($normalizedKey === $normalizedCli) {
                                    $matchedKey = $key;
                                    break;
                                }
                            }

                            if ($matchedKey) {
                                // Update the sid
                                DB::connection($connection)
                                    ->table('did')
                                    ->where('cli', $cli)
                                    ->update(['sid' => $twilioResults[$matchedKey]]);

                                // Unset the key-value pair
                                $sidValue = $twilioResults[$matchedKey];
                                unset($twilioResults[$matchedKey]);
                                $updatedCount++;
                                $this->info("Updated sid for cli {$cli} with value {$sidValue} and unset the pair.");
                                $this->logMessage("Updated sid for cli {$cli} with value {$sidValue} and unset the pair.");
                            }
                        }
                        $this->info("Updated {$updatedCount} records for client {$clientId}.");
                        $this->logMessage("Updated {$updatedCount} records for client {$clientId}.");
                    } else {
                        if ($columnExists) {
                            $this->info("Dropping 'sid' column from did table for client {$clientId}...");
                            $this->logMessage("Dropping 'sid' column from did table for client {$clientId}...");
                            DB::connection($connection)
                                ->statement("ALTER TABLE did DROP COLUMN sid");
                            $this->info("Dropped 'sid' column successfully.");
                            $this->logMessage("Dropped 'sid' column successfully.");
                        } else {
                            $this->info("'sid' column does not exist. No action needed.");
                            $this->logMessage("'sid' column does not exist. No action needed.");
                        }
                    }
                } catch (\Exception $e) {
                    $errorMessage = "Error processing client {$clientId}: " . $e->getMessage();
                    $this->error($errorMessage);
                    $this->logMessage($errorMessage);
                    // Continue to next client without breaking
                }

                $this->info("Finished processing client {$clientId}.");
                $this->logMessage("Finished processing client {$clientId}.");
            }

            $this->info('Command execution completed.');
            $this->logMessage('Command execution completed.');
        } catch (\Exception $e) {
            $errorMessage = "Error in SidColumnCron: " . $e->getMessage();
            $this->error($errorMessage);
            $this->logMessage($errorMessage);
            // Do not throw or show error beyond this, just output
        }
    }
}